Pesquisar e recuperar a linha inteira
Para pesquisar e recuperar uma linha inteira de dados combinando um valor específico, você pode usar o ÍNDICE e JOGO funções para criar uma fórmula de matriz.
Procure e recupere uma linha inteira com base em um valor específico
Some uma linha inteira com base em um valor específico
Análise adicional de uma linha inteira com base em um valor específico
Procure e recupere uma linha inteira com base em um valor específico
Para obter o lista de vendas feitas por Jimmy de acordo com a tabela acima, você pode primeiro usar a função MATCH para retornar a posição das vendas feitas por Jimmy, e que serão alimentadas para INDEX para recuperar os valores na posição.
Sintaxe genérica
=INDEX(return_range,MATCH(lookup_value,lookup_array,0),0)
√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + mudança + Entrar.
- intervalo_de_retorno: O intervalo que contém toda a linha que você deseja retornar. Aqui se refere à faixa de vendas.
- valor_pesquisa: O valor que a fórmula de combinação usou para encontrar suas informações de vendas correspondentes. Aqui se refere ao vendedor fornecido.
- lookup_array: O intervalo de células onde corresponder o lookup_value. Aqui se refere ao intervalo de nomes.
- match_type 0: Força MATCH a encontrar o primeiro valor que é exatamente igual ao lookup_value.
Para obter a lista de vendas feitas por Jimmy, copie ou insira a fórmula abaixo na célula I6, pressione Ctrl + mudança + Entrare, em seguida, clique duas vezes na célula e pressione F9 para obter o resultado:
= INDEX (C5: F11,PARTIDA("Jimmy",B5: B11, 0), 0)
Ou use uma referência de célula para tornar a fórmula dinâmica:
= INDEX (C5: F11,PARTIDA(I5,B5: B11, 0), 0)
Explicação da fórmula
=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)
- CORRESPONDÊNCIA (I5, B5: B11,0): O match_type 0 força a função MATCH a retornar a posição de Jimmy, o valor em I5, no intervalo B5: B11, o que é 7 desde Jimmy é no 7posição.
- ÍNDICE(C5: F11,CORRESPONDÊNCIA (I5, B5: B11,0), 0) = ÍNDICE (C5: F11,7, 0): A função INDEX retorna todos os valores no 7a 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.
Some 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 de vendas anual feito por Jimmy, podemos simplesmente adicionar a função SUM à fórmula para somar todos os valores de vendas da lista.
Sintaxe genérica
=SUM(INDEX(return_range,MATCH(lookup_value,lookup_array,0),0))
Neste exemplo, para obter o volume de vendas anual feito por Jimmy, copie ou insira a fórmula abaixo na célula I7 e pressione Entrar para obter o resultado:
= SOMA (ÍNDICE (C5: F11,PARTIDA(I5,B5: B11, 0), 0))
Explicação da fórmula
=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))
- CORRESPONDÊNCIA (I5, B5: B11,0): O match_type 0 força a função MATCH a retornar a posição de Jimmy, o valor em I5, no intervalo B5: B11, o que é 7 desde Jimmy é no 7posição.
- ÍNDICE(C5: F11,CORRESPONDÊNCIA (I5, B5: B11,0), 0) = ÍNDICE(C5: F11,7, 0): A função INDEX retorna todos os valores no 7a 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.
- SUM (ÍNDICE(C5: F11,CORRESPONDÊNCIA (I5, B5: B11,0)), 0) = SOMA (6678,3654,3278,8398 {}): A função SUM soma todos os valores da matriz e, em seguida, obtém o volume de vendas anual feito 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 SUM, AVERAGE, MAX, MIN, LARGE, etc. à fórmula.
Por exemplo, para obter o média das vendas de Jimmy para cada trimestre, você pode usar a fórmula:
= MÉDIA (ÍNDICE (C5: F11,PARTIDA(I5,B5: B11, 0), 0))
Para descobrir o vendas mais baixas feitas por Jimmy, use uma das fórmulas abaixo:
= MIN (ÍNDICE (C5: F11,PARTIDA(I5,B5: B11, 0), 0))
OR
= PEQUENO (ÍNDICE (C5: F11,PARTIDA(I5,B5: B11, 0), 0),1)
Funções relacionadas
A função INDEX do Excel retorna o valor exibido com base em uma determinada posição de um intervalo ou matriz.
A função MATCH do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
Fórmulas Relacionadas
Pesquisar e recuperar a coluna inteira
Para pesquisar e recuperar uma coluna inteira combinando um valor específico, uma fórmula INDEX e MATCH fará um favor a você.
Correspondência exata com INDEX e MATCH
Se você precisa descobrir as informações listadas no Excel sobre um produto específico, filme ou pessoa, etc., você deve fazer um bom uso da combinação das funções INDEX e MATCH.
Correspondência aproximada com INDEX e MATCH
Há momentos em que precisamos encontrar correspondências aproximadas no Excel para avaliar o desempenho dos funcionários, dar notas aos alunos, calcular a postagem com base no peso, etc. Neste tutorial, falaremos sobre como usar as funções INDEX e MATCH para recuperar o resultados que precisamos.
Pesquisa que diferencia maiúsculas de minúsculas
Você deve saber que pode combinar as funções INDEX e MATCH ou usar a função VLOOKUP para pesquisar valores no Excel. No entanto, as pesquisas não diferenciam maiúsculas de minúsculas. Portanto, para realizar uma correspondência com distinção entre maiúsculas e minúsculas, você deve aproveitar as funções EXACT e CHOOSE.
As melhores ferramentas de produtividade para escritório
Kutools for Excel - ajuda você a se destacar da multidão
Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.
- Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
- Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
- Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
- Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do 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 (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
