Neste artigo, aprenderemos como procurar correspondências exatas usando a função SUMPRODUCT no Excel.
Cenário:
Em palavras simples, ao trabalhar com tabelas de dados, às vezes precisamos pesquisar valores com letras maiúsculas e minúsculas no Excel. As funções de pesquisa, como VLOOKUP ou MATCH, não encontram a correspondência exata, pois essas funções não diferenciam maiúsculas de minúsculas. Suponha que você trabalhe com dados em que 2 nomes são diferenciados com base na distinção entre maiúsculas e minúsculas, ou seja, Jerry e JERRY são dois nomes próprios diferentes. Você pode realizar tarefas como operações em vários intervalos usando a fórmula explicada abaixo.
Como resolver o problema?
Para este problema, seremos obrigados a usar a função SUMPRODUCT e a função EXACT. Agora faremos uma fórmula da função. Aqui, temos uma tabela e precisamos encontrar os valores correspondentes à correspondência exata na tabela do Excel. A função SUMPRODUCT retorna a SOMA dos valores TRUE correspondentes (como 1) e ignora os valores correspondentes aos valores FALSE (como 0) na matriz retornada
Fórmula genérica:
| = SUMPRODUCT (- (EXACT (lookup_value, lookup_array)), (return_array)) |
lookup_value: valor a ser pesquisado.
lookup_array: array de pesquisa para valor de pesquisa.
return_array: array, valor retornado correspondente ao array de pesquisa.
-: Negation (-) char altera os valores, TRUEs ou 1s para FALSEs ou 0s e FALSEs ou 0s para TRUEs ou 1s.
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo. Aqui temos os dados de Quantidade e Preço dos produtos recebidos nas datas. Se algum produto for comprado duas vezes, ele terá 2 nomes. Aqui, precisamos encontrar a contagem de itens para todos os itens essenciais. Então, para isso, atribuímos 2 listas como a lista recebida e os itens essenciais exigidos em uma coluna para a fórmula. Agora usaremos a fórmula abaixo para obter a Quantidade de "leite" da tabela.
Use a fórmula:
| = SUMPRODUTO (- (EXATO (F5, B3: B11)), (C3: C11)) |
F6: procurar valor na célula F6
B3: B11: procurar array é itens
C3: C11: array de retorno é Quantidade
-: Negation (-) char altera os valores, TRUEs ou 1s para FALSEs ou 0s e FALSEs ou 0s para TRUEs ou 1s.
Aqui, o intervalo é dado como referência de célula. Pressione Enter para obter a quantidade.
Como você pode ver, 58 é a quantidade correspondente ao valor "leite" na célula B5 e não o "Leite" na célula B9. Você deverá procurar o valor "Leite" se precisar da quantidade "54" na célula C9.
Você pode pesquisar o preço correspondente ao valor "leite" usando a fórmula abaixo.
Use a fórmula:
| = SUMPRODUTO (- (EXATO (F5, B3: B11)), (D3: D11)) |
F6: procurar valor na célula F6
B3: B11: procurar array é itens
D3: D11: a matriz de retorno é o preço
Aqui temos o 58 é o Preço correspondente ao valor "leite" na célula B5 e não o "Leite" na célula B9. Você terá que procurar o valor "Leite" se precisar do Preço "15,58" na célula D9.
Valores únicos na matriz de pesquisa
Da mesma forma, você pode encontrar os valores exclusivos usando a fórmula. Aqui, o valor de pesquisa "EGGS" usado como exemplo.
Na imagem mostrada acima, obtivemos os valores ajustando a mesma fórmula. Mas o problema ocorre se ele tiver um valor exclusivo e você não estiver procurando o valor de pesquisa adequado. Como aqui, usando o valor "Pão" na fórmula para pesquisar na tabela.
A fórmula retorna 0 como quantidade e preço, mas isso não significa que a quantidade e o preço do pão sejam 0. É apenas que a fórmula retorna 0 como valor quando o valor que você está procurando não é encontrado. Portanto, use esta fórmula apenas para procurar a correspondência exata.
Você também pode realizar pesquisas de correspondências exatas usando as funções INDEX e MATCH no Excel. Saiba mais sobre como fazer pesquisa sensível a maiúsculas e minúsculas usando a função INDEX & MATCH no Excel. Você também pode procurar as correspondências parciais usando os curingas no Excel.
Aqui estão algumas notas observacionais mostradas abaixo.
Notas:
- A fórmula funciona apenas para procurar a correspondência exata.
- A função SUMPRODUCT considera os valores não numéricos como 0s.
- A função SUMPRODUCT considera o valor lógico TRUE como 1 e False como 0.
- A matriz do argumento deve ter o mesmo tamanho, caso contrário, a função retornará um erro.
- A função SUMPRODUCT retorna o valor correspondente aos valores TRUE na matriz retornada após obter produtos individuais na matriz correspondente.
- Operadores como é igual a ( = ), menor que igual a ( <= ), Maior que ( > ) ou diferente de () pode ser executado dentro de uma fórmula aplicada, apenas com números.
Espero que este artigo sobre como pesquisar correspondência exata usando a função SUMPRODUCT no Excel seja explicativo. Encontre mais artigos sobre fórmulas de contagem aqui. Se você gostou de nossos blogs, compartilhe 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-nos no site de e-mail
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
Use INDEX e MATCH para pesquisar o valor : A fórmula INDEX-MATCH é usada para pesquisar de forma dinâmica e precisa um valor em determinada tabela. Esta é uma alternativa para a função VLOOKUP e supera as deficiências da função VLOOKUP.
Soma por grupos OFFSET em linhas e colunas : A função OFFSET pode ser usada para somar grupos de células dinamicamente. Esses grupos podem estar em qualquer lugar da planilha.
Como recuperar cada enésimo valor em um intervalo no Excel: Usando a função OFFSET do Excel, podemos recuperar valores de linhas ou colunas alternadas. Esta fórmula usa a ajuda da função ROW para alternar nas linhas e da função COLUMN para alternar nas colunas.
Como usar a função OFFSET no Excel : A função OFFSET é uma função Excel poderosa que é subestimada por muitos usuários. Mas os especialistas conhecem o poder da função OFFSET e como podemos usar esta função para fazer algumas tarefas mágicas na fórmula do Excel. Aqui estão os fundamentos da função OFFSET.
Como usar curingas no excel : Conte células que combinam frases usando os curingas no Excel
Artigos populares
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores de diferentes intervalos e planilhas no Excel.
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 no Excel. A função CONT.SE é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores com condições específicas.