Este artigo falará sobre como obter todos os valores correspondidos de uma tabela e recuperá-los em células diferentes. Isso é semelhante a vários valores de pesquisa.
Fórmula Genérica
{= INDEX (nomes, PEQUENO (SE (grupos = nome_do_grupo, ROW (nomes) -MIN (LINHA (nomes)) + 1), COLUNAS (intervalos de expansão))), “--Lista termina--”)}
Muitas funções e variáveis !!!. Vamos ver o que são essas variáveis.
Nomes: Esta é a lista de nomes.
Grupos: A lista do grupo ao qual esses nomes também pertencem.
Nome do grupo: a referência do nome do grupo.
Intervalos de expansão: este é um intervalo de expansão que é usado para obter um número crescente quando copiado para a direita.
Exemplo: Extraia os nomes dos funcionários em diferentes colunas de acordo com sua empresa.
Digamos que você tenha uma tabela de funcionários agrupada de acordo com sua empresa. A primeira coluna contém os nomes dos funcionários e a segunda coluna contém o nome da empresa.
Agora precisamos colocar o nome de cada funcionário em colunas diferentes de acordo com sua empresa. Em outras palavras, precisamos desagrupá-los.
Aqui, nomeei A2: A10 como Funcionário e B2: B10 como Empresa, para que a fórmula seja fácil de ler.
Escreva esta fórmula de matriz em F2. Use CTRL + SHIFT + ENTER para inserir esta fórmula.
{= INDEX (Funcionário, PEQUENO (SE (Empresa = $ E2, ROW (Funcionário) -MIN (ROW (Funcionário)) + 1), COLUNAS ($ E $ 1: E1))), “- Lista termina--” )}
Copie esta fórmula em todas as células. Ele irá extrair cada nome individual nas diferentes colunas de acordo com seu grupo.
Como você pode ver na imagem acima, cada funcionário está segregado em células diferentes.
Então, como essa fórmula funciona?
Para entender a fórmula, vejamos a fórmula em G2
Que é = IFERROR (INDEX (Empregado, PEQUENO (SE (Empresa = $ E3,FILEIRA(Empregado) -MIN (ROW (Empregado)) + 1), COLUNAS ($ E $ 1: F2))), "- Fim da lista--")
A mecânica é simples e quase igual à fórmula múltipla VLOOKUP. O truque é obter o número índice de cada funcionário de grupos diferentes e passá-lo para a fórmula INDEX. Isso é feito por esta parte da fórmula.
E SE(Empresa=$ E3,FILEIRA(Empregado) -MIN (ROW (Empregado))+1):
Esta parte retorna um array de índices e false para o nome da empresa em $ E3, que contém “Rankwatch”.
{FALSO; 2; FALSO; 4; FALSO; FALSO; 7; FALSO; 9}.
Como? Vamos derrubar por dentro.
Aqui combinamos o nome da empresa em $ E3 com cada valor em Faixa de empresa (empresa = $ E3).
Isso retorna uma matriz de verdadeiro e falso. {FALSE;VERDADE;FALSO;VERDADE; FALSE; FALSE;VERDADE;FALSO;VERDADE}.
Agora a função IF executa suas instruções TRUE para TRUE, que é ROW (Funcionário) -MIN (ROW (Empregado)) + 1. Esta parte retorna esta parte retorna uma matriz de índices começando de 1 a um número de funcionários {1; 2; 3; 4; 5; 6; 7; 8; 9}. A função if mostra valores apenas para TRUE, que por sua vez nos dá {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
A fórmula atual é simplificada para
= IFERROR (INDEX (Employee, SMALL ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},COLUNAS ($ E $ 1: F2))), "- Fim da lista--"). Como sabemos, a função pequena retorna o menor valor enésimo de uma matriz. COLUNAS ($ E $ 1: F2) isso retorna 2. A função SMALL retorna o segundo menor valor da matriz acima, que é 4.
Agora a fórmula é simplificada = IFERROR (INDEX (Funcionário, 4), "- Fim da lista--"). Agora, a função INDEX simplesmente retorna o quarto nome de empregado array que nos dá “Sam”.
Então, sim, é assim que extrai nomes de grupos em colunas diferentes usando as funções INDEX, SMALL, ROW, COLUMNS e IF. Se você tiver alguma dúvida em relação a esta função ou se ela não estiver funcionando para você, deixe-me saber na seção de comentários abaixo.
⇬ Fazer download do arquivo:
Artigos relacionados:
VLOOKUP Valores Múltiplos
Use INDEX e MATCH para pesquisar o valor
Valor de pesquisa com vários critérios
Artigos populares:
A função VLOOKUP no Excel
CONT.SE no Excel 2016
Como usar a função SUMIF no Excel