Mesclar tabelas com ÍNDICE e CORRESP
Suponha que você tenha duas ou mais tabelas com uma coluna em comum, no entanto, os dados nas colunas comuns não estão listados na mesma ordem. Nesse caso, para mesclar, combinar ou unir as tabelas enquanto faz a correspondência dos dados, você pode usar a ajuda das funções ÍNDICE e CORRESP.
Como mesclar tabelas com ÍNDICE e CORRESP?
Para unir a tabela 1 e a tabela 2 e combinar todas as informações em uma nova tabela, como mostrado na captura de tela acima, você deve primeiro copiar os dados da tabela 1 ou tabela 2 para a nova tabela (aqui eu copiei os dados da tabela 1, veja a captura de tela abaixo). Tomando o primeiro ID do Aluno 23201 na nova tabela como exemplo, as funções ÍNDICE e CORRESP ajudarão você a recuperar sua pontuação e classificação da seguinte forma: CORRESP retorna a posição da linha do ID do aluno que corresponde ao 23201 na tabela 2. A informação da linha será fornecida ao ÍNDICE para recuperar o valor na interseção dessa linha e da coluna especificada (coluna de pontuação ou classificação).
Sintaxe genérica
=ÍNDICE(tabela_retorno;CORRESP(valor_pesquisa;matriz_pesquisa;0);num_col)
√ Nota: Como já preenchemos as informações da tabela 1, agora só precisamos recuperar os dados correspondentes da tabela 2.
- tabela_retorno: A tabela da qual retornar as pontuações dos alunos. Aqui se refere à tabela 2.
- valor_pesquisa: O valor usado para corresponder as informações na tabela_retorno. Aqui se refere ao valor do ID do aluno na nova tabela.
- matriz_pesquisa: O intervalo de células com os valores a serem comparados com o valor_pesquisa. Aqui se refere à coluna de ID do aluno na tabela_retorno.
- num_col: O número da coluna que indica de qual coluna da tabela_retorno retornar as informações correspondentes.
- 0: O tipo_de_correspondência 0 força a função CORRESP a realizar uma correspondência exata.
Para recuperar os dados correspondentes da tabela 2 e combinar todas as informações na nova tabela, por favor, copie ou insira as fórmulas abaixo nas células F16 e G16, e pressione Enter para obter os resultados:
Célula F16 (Pontuação)
=ÍNDICE($F$5:$H$11;CORRESP(C16;$F$5:$F$11;0);2)
Célula G16 (Classificação)
=ÍNDICE($F$5:$H$11;CORRESP(C16;$F$5:$F$11;0);3)
√ Nota: Os sinais de cifrão ($) acima indicam referências absolutas, o que significa que a tabela_retorno e a matriz_pesquisa na fórmula não mudarão quando você mover ou copiar as fórmulas para outras células. No entanto, nenhum sinal de cifrão foi adicionado ao valor_pesquisa, pois você deseja que ele seja dinâmico. Após inserir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células abaixo.
Explicação da fórmula
Aqui usamos a fórmula abaixo como exemplo:
=ÍNDICE($F$5:$H$11;CORRESP(C16;$F$5:$F$11;0);2)
- CORRESP(C16;$F$5:$F$11;0): O tipo_de_correspondência 0 força a função CORRESP a realizar uma correspondência exata. A função então retorna a posição do valor de correspondência de 23201 (o valor em C16) na matriz de pesquisa $F$5:$F$11. Assim, a função retornará 3, já que seu valor de correspondência está na 3ª posição no intervalo.
- ÍNDICE($F$5:$H$11$F$5:$H$11;CORRESP(C16;$F$5:$F$11;0);22) = ÍNDICE($F$5:$H$11$F$5:$H$11;3;22): A função ÍNDICE retorna o valor na interseção da 3ª linha e 2ª coluna da tabela de retorno $F$5:$H$11, que é 91.
Funções relacionadas
A função ÍNDICE do Excel retorna o valor exibido com base em uma posição específica de um intervalo ou matriz.
A função CORRESP do Excel procura um valor específico em um intervalo de células e retorna a posição relativa desse valor.
Fórmulas relacionadas
Correspondência exata com ÍNDICE e CORRESP
Se você precisar descobrir as informações listadas no Excel sobre um produto, filme ou pessoa específica, etc., deve fazer bom uso da combinação das funções ÍNDICE e CORRESP.
Correspondência aproximada com ÍNDICE e CORRESP
Há momentos em que precisamos encontrar correspondências aproximadas no Excel para avaliar o desempenho dos funcionários, classificar as notas dos alunos, calcular o frete com base no peso, etc. Neste tutorial, falaremos sobre como usar as funções ÍNDICE e CORRESP para recuperar os resultados de que precisamos.
Pesquisar valor de correspondência mais próxima com vários critérios
Em alguns casos, você pode precisar procurar o valor de correspondência mais próxima ou aproximada com base em mais de um critério. Com a combinação de ÍNDICE, CORRESP e SE, você pode fazer isso rapidamente no Excel.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.