Pesquisar e recuperar a linha inteira
Para pesquisar e recuperar uma linha inteira de dados ao combinar um valor específico, você pode usar as funções ÍNDICE e CORRESP para criar uma fórmula de matriz.
Pesquisar e recuperar uma linha inteira com base em um valor específico
Somar uma linha inteira com base em um valor específico
Análise adicional de uma linha inteira com base em um valor específico
Pesquisar e recuperar uma linha inteira com base em um valor específico
Para obter a lista de vendas feitas por Jimmy de acordo com a tabela acima, você pode primeiro usar a função CORRESP para retornar a posição das vendas feitas por Jimmy, que será fornecida ao ÍNDICE para recuperar os valores nessa posição.
Sintaxe genérica
=ÍNDICE(intervalo_retorno;CORRESP(valor_pesquisa;intervalo_pesquisa;0);0)
√ Nota: Esta é uma fórmula de matriz que requer que você a insira com Ctrl + Shift + Enter.
- intervalo_retorno: O intervalo que contém a linha inteira que você deseja retornar. Aqui se refere ao intervalo de vendas.
- valor_pesquisa: O valor que a fórmula combinada usa para encontrar suas informações de vendas correspondentes. Aqui se refere ao vendedor dado.
- intervalo_pesquisa: O intervalo de células onde corresponder o valor_pesquisa. Aqui se refere ao intervalo de nomes.
- tipo_correspondência 0: Força a CORRESP a encontrar o primeiro valor que seja exatamente igual ao valor_pesquisa.
Para obter a lista de vendas feitas por Jimmy, 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:
=ÍNDICE(C5:F11;CORRESP("Jimmy";B5:B11;0);0)
Ou use uma referência de célula para tornar a fórmula dinâmica:
=ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0)
Explicação da fórmula
=ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0)
- CORRESP(I5;B5:B11;0): O tipo_correspondência 0 força a função CORRESP a retornar a posição de Jimmy, o valor em I5, no intervalo B5:B11, que é 7, já que Jimmy está na 7ª posição.
- ÍNDICE(C5:F11C5:F11;CORRESP(I5;B5:B11;0);0) = ÍNDICE(C5:F11C5:F11;7;0): A função ÍNDICE retorna todos os valores na 7ª linha do intervalo C5:F11 em uma matriz como esta: {6678,3654,3278,8398}. 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 linha inteira com base em um valor específico
Agora que temos todas as informações de vendas feitas por Jimmy, para obter o volume anual de vendas feitas por Jimmy, podemos simplesmente adicionar a função SOMA à fórmula para somar todos os valores de vendas da lista.
Sintaxe genérica
=SOMA(ÍNDICE(intervalo_retorno;CORRESP(valor_pesquisa;intervalo_pesquisa;0);0))
Neste exemplo, para obter o volume anual de vendas feitas por Jimmy, copie ou insira a fórmula abaixo na célula I7 e pressione Enter para obter o resultado:
=SOMA(ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0))
Explicação da fórmula
=SOMA(ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0))
- CORRESP(I5;B5:B11;0): O tipo_correspondência 0 força a função CORRESP a retornar a posição de Jimmy, o valor em I5, no intervalo B5:B11, que é 7, já que Jimmy está na 7ª posição.
- ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0) = ÍNDICE(C5:F11;7;0): A função ÍNDICE retorna todos os valores na 7ª linha do intervalo C5:F11 em uma matriz como esta: {6678,3654,3278,8398}. 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.
- SOMA(ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0));0) = SOMA({6678,3654,3278,8398}): A função SOMA soma todos os valores na matriz e, em seguida, obtém o volume anual de vendas feitas por Jimmy, $22.008.
Análise adicional de uma linha inteira com base em um valor específico
Para processamento adicional nas vendas feitas por Jimmy, você pode simplesmente adicionar outras funções, como SOMA, MÉDIA, MÁXIMO, MÍNIMO, GRANDE, etc., à fórmula.
Por exemplo, para obter a média das vendas trimestrais de Jimmy, você pode usar a fórmula:
=MÉDIA(ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0))
Para descobrir as vendas mais baixas feitas por Jimmy, use uma das fórmulas abaixo:
=MÍNIMO(ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0))
OU
=PEQUENO(ÍNDICE(C5:F11;CORRESP(I5;B5:B11;0);0);1)
Funções relacionadas
A função ÍNDICE do Excel retorna o valor exibido com base em uma posição dada 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
Pesquisar e recuperar coluna inteira
Para pesquisar e recuperar uma coluna inteira ao combinar um valor específico, uma fórmula ÍNDICE e CORRESP fará isso por você.
Correspondência exata com ÍNDICE e CORRESP
Se você precisar encontrar 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.
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, as pesquisas não são sensíveis a maiúsculas e minúsculas. Portanto, 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.