Neste artigo, aprenderemos como recuperar um preço de uma lista que corresponda aos critérios de categoria e item no Excel.
Cenário:
É fácil encontrar um valor com um critério em uma tabela, para isso poderíamos simplesmente usar PROCV. Mas quando você não tem um único critério de coluna em seus dados e precisa encontrar por meio de vários critérios de colunas para corresponder a um valor, VLOOKUP não ajuda.
Fórmula genérica para categoria e item
| = ÍNDICE (intervalo_procuração, CORRESPONDÊNCIA (1, ÍNDICE ((intervalo_item1 = intervalo_produtos) * (intervalo_categoria2 = intervalo_categorias), 0,1), 0)) |
intervalo_procurado: é o intervalo do qual você deseja recuperar o valor.
Criteria1, Criteria2, Criteria N: Estes são os critérios que você deseja corresponder no intervalo1, intervalo2 e intervalo N. Você pode ter até 270 critérios - pares de intervalo.
Intervalo1, intervalo2, intervaloN: esses são os intervalos nos quais você corresponderá aos seus respectivos critérios
Exemplo :
Tudo isso pode ser confuso de entender. Vamos entender como usar a função usando um exemplo. Aqui temos uma tabela de dados. Quero puxar o nome do cliente usando data de reserva, construtor e área. Portanto, aqui tenho três critérios e um intervalo de pesquisa.
Escreva esta fórmula na célula I4 e pressione Enter.
| = ÍNDICE (E2: E16, CORRESPONDÊNCIA (1, ÍNDICE ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0)) |
Já sabemos como funcionam as funções INDEX e MATCH no EXCEL, então não vamos explicar isso aqui. Vamos falar sobre o truque que usamos aqui.
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16): A parte principal é esta. Cada parte desta declaração retorna uma matriz de verdadeiro falso.
Quando os valores booleanos são multiplicados, eles retornam uma matriz de 0 e 1. A multiplicação funciona como um operador AND. Hense quando todos os valores são verdadeiros, ele retorna 1 senão 0
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16) Isso retornará completamente
Que vai se traduzir em
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): A função INDEX retornará a mesma matriz ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) para função MATCH como matriz de pesquisa.
MATCH (1, INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): a função MATCH irá procurar 1 na matriz {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. E retornará o número de índice do primeiro 1 encontrado na matriz. Que é 8 aqui.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0)): Finalmente, INDEX retornará o valor do intervalo dado (E2: E16) no índice encontrado (8).
Se você puder pressionar CTRL + SHIFT + ENTER conseqüentemente, poderá eliminar a função INDEX interna. Basta escrever esta fórmula e pressionar CTRL + SHIFT + ENTER.
Fórmula
| = ÍNDICE (E2: E16, CORRESPONDÊNCIA (1, (I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0)) |
Fórmula de matriz genérica para pesquisa de múltiplos critérios
| = INDEX (intervalo_procura, CORRESPONDÊNCIA (1, (critérios1 = intervalo1) * (critérios2 = intervalo2) * (critériosN = intervaloN), 0)) |
Aqui estão todas as notas de observação usando a fórmula no Excel
Notas :
- Use o Vlookup se você tiver um critério para corresponder, é uma prática comum.
- Você pode adicionar mais linhas e colunas na matriz de pesquisa.
- Os argumentos de texto devem ser fornecidos entre aspas ("").
- A tabela VLOOKUP deve ter lookup_array na coluna mais à esquerda ou na primeira coluna.
- O índice col não pode ser 1, pois é o array de pesquisa
- O argumento 0 é usado para o valor de correspondência exata. Use 1 para o valor aproximado de correspondência.
- A função retorna o erro # N / A, se o valor de pesquisa não for encontrado na matriz de pesquisa. Portanto, detecte o erro, se necessário.
Espero que este artigo sobre Como recuperar um preço de uma lista que corresponda aos critérios de categoria e item no Excel seja explicativo. Encontre mais artigos sobre como calcular valores e fórmulas Excel relacionadas aqui. Se você gostou de nossos blogs, compartilhe-os com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Como recuperar o preço mais recente no Excel : É comum atualizar os preços em qualquer negócio e usar os preços mais recentes para qualquer compra ou venda é uma obrigação. Para recuperar o preço mais recente de uma lista no Excel, usamos a função LOOKUP. A função LOOKUP obtém o preço mais recente.
Função VLOOKUP para calcular nota no Excel : Para calcular as notas IF e IFS não são as únicas funções que você pode usar. A VLOOKUP é mais eficiente e dinâmica para tais cálculos condicionais. Para calcular notas usando VLOOKUP, podemos usar esta fórmula.
17 coisas sobre VLOOKUP do Excel : VLOOKUP é mais comumente usado para recuperar valores correspondentes, mas VLOOKUP pode fazer muito mais do que isso. Aqui estão 17 coisas sobre VLOOKUP que você deve saber para usar com eficácia.
PROCURAR o primeiro texto de uma lista no Excel : A função VLOOKUP funciona bem com caracteres curinga. Podemos usar isso para extrair o primeiro valor de texto de uma determinada lista no Excel. Aqui está a fórmula genérica.
Data LOOKUP com último valor na lista : Para recuperar a data que contém o último valor, usamos a função LOOKUP. Esta função verifica a célula que contém o último valor em um vetor e usa essa referência para retornar a data.
Artigos populares :
50 atalhos do Excel para aumentar sua produtividade : Torne suas tarefas mais rápidas no Excel. Esses atalhos o ajudarão a aumentar a eficiência do trabalho no Excel.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.