Pesquisar e recuperar coluna inteira
Para pesquisar e recuperar uma coluna inteira correspondendo um valor específico, uma fórmula com INDEX e MATCH fará o trabalho.
Pesquisar e recuperar uma coluna inteira com base em um valor específico
Somar uma coluna inteira com base em um valor específico
Análise adicional de uma coluna inteira com base em um valor específico
Pesquisar e recuperar uma coluna inteira com base em um valor específico
Para obter uma lista das vendas do segundo trimestre (Q2) de acordo com a tabela acima, você pode primeiro usar a função MATCH para retornar a posição das vendas do Q2, que será usada pela função INDEX para recuperar os valores nessa posição.
Sintaxe genérica
=INDEX(intervalo_retorno,0,MATCH(valor_pesquisa,intervalo_pesquisa,0))
√ Nota: Esta é uma fórmula de matriz que requer que você a insira usando Ctrl + Shift + Enter.
- intervalo_retorno: O intervalo onde você deseja que a fórmula combinada retorne a lista de vendas do Q2. Aqui refere-se ao intervalo de vendas.
- valor_pesquisa: O valor que a fórmula combinada usa para encontrar as informações de vendas correspondentes. Aqui refere-se ao trimestre fornecido.
- intervalo_pesquisa: O intervalo de células onde deve ser feita a correspondência do valor_pesquisa. Aqui refere-se aos cabeçalhos dos trimestres.
- tipo_correspondência 0: Força a função MATCH a encontrar o primeiro valor que seja exatamente igual ao valor_pesquisa.
Para obter uma lista das vendas do Q2, copie ou insira a fórmula abaixo na célula I6, pressione Ctrl + Shift + Enter e, em seguida, clique duas vezes na célula e pressione F9 para obter o resultado:
=INDEX(C5:F11,0,MATCH("Q2",C4:F4,0))
Ou, use uma referência de célula para tornar a fórmula dinâmica:
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
Explicação da fórmula
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
- MATCH(I5,C4:F4,0): O tipo_correspondência 0 força a função MATCH a retornar a posição do Q2, o valor em I5, no intervalo C4:F5, que é 2.
- INDEX(C5:F11C5:F11,0,MATCH(I5,C4:F4,0)) = INDEX(C5:F11C5:F11,0,2): A função INDEX retorna todos os valores na segunda coluna do intervalo C5:F11 em uma matriz como esta: {7865;4322;8534;5463;3252;7683;3654}. Observe que, para tornar a matriz visível no Excel, você deve clicar duas vezes na célula onde inseriu a fórmula e, em seguida, pressionar F9.
Somar uma coluna inteira com base em um valor específico
Como agora temos a lista de vendas em mãos, obter o volume total de vendas do Q2 seria fácil para nós. Tudo o que precisamos fazer é adicionar a função SOMA à fórmula para somar todos os valores de vendas da lista.
Sintaxe genérica
=SUM(INDEX(intervalo_retorno,0,MATCH(valor_pesquisa,intervalo_pesquisa,0)))
Neste exemplo específico, para obter o volume total de vendas do Q2, copie ou insira a fórmula abaixo na célula I8 e pressione Enter para obter o resultado:
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
Explicação da fórmula
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
- MATCH(I5,C4:F4,0): O tipo_correspondência 0 força a função MATCH a retornar a posição do Q2, o valor em I5, no intervalo C4:F5, que é 2.
- INDEX(INDEX(C5:F11C5:F11,0,,0,MATCH(I5,C4:F4,0))) = INDEX(INDEX(C5:F11C5:F11,0,,0,2)): A função INDEX retorna todos os valores na segunda coluna do intervalo C5:F11 em uma matriz como esta: {7865;4322;8534;5463;3252;7683;3654}.
- SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0))) = SUM({7865;4322;8534;5463;3252;7683;3654}): A função SOMA soma todos os valores na matriz e, em seguida, obtém o volume total de vendas do Q2, $40.773.
Análise adicional de uma coluna inteira com base em um valor específico
Para processamento adicional na lista de vendas do Q2, você pode simplesmente adicionar outras funções como SOMA, MÉDIA, MÁXIMO, MÍNIMO, MAIOR, etc., à fórmula.
Por exemplo, para obter o volume médio de vendas durante o Q2, você pode usar a fórmula:
=MÉDIA(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
Para descobrir as maiores vendas durante o Q2, use uma das fórmulas abaixo:
=MÁXIMO(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
OU
=MAIOR(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)),1)
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 busca um valor específico em um intervalo de células e retorna a posição relativa desse valor.
Fórmulas relacionadas
Pesquisar e recuperar linha inteira
Para pesquisar e recuperar uma linha inteira de dados correspondendo um valor específico, você pode usar as funções ÍNDICE e CORRESP para criar uma fórmula de matriz.
Correspondência exata com ÍNDICE e CORRESP
Se você precisa encontrar informações listadas no Excel sobre um produto, filme ou pessoa específica, etc., deve aproveitar bem a 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 necessários.
Pesquisa sensível a maiúsculas e minúsculas
Você pode saber que pode combinar as funções ÍNDICE e CORRESP ou usar a função PROCV para procurar valores no Excel. No entanto, essas buscas não são sensíveis a maiúsculas e minúsculas. Assim, para realizar uma correspondência sensível a maiúsculas e minúsculas, você deve tirar proveito das funções EXATO e ESCOLHER.
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.