Resumo
Em certos casos, você pode usar SOMASE como uma fórmula de pesquisa para recuperar um valor numérico. No exemplo mostrado, a fórmula em G6 é:
=SUMIFS(sales,region,G4,quarter,G5)
onde região (B5: B20), trimestre (C5: C20) e vendas (D5: D20) são intervalos nomeados.
O resultado são as vendas do terceiro trimestre para a região Central, 127.250.
Explicação
Se você é novo na função SUMIFS, pode encontrar uma visão geral básica com muitos exemplos aqui.
A função SUMIFS foi projetada para somar valores numéricos com base em um ou mais critérios. Em casos específicos, entretanto, você pode usar SUMIFS para "pesquisar" um valor numérico que atenda aos critérios exigidos. As principais razões para fazer isso são simplicidade e rapidez.
No exemplo mostrado, temos dados de vendas trimestrais para quatro regiões. Começamos dando SUMIFS um intervalo de soma e a primeira condição, que testa a região para o valor em G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- O intervalo de soma é vendas (D5: D20)
- O intervalo de critérios 1 é a região (B5: B20)
- O critério 1 é G4 ("Central")
Em seguida, adicionamos o segundo par de intervalo / critério, que verifica o trimestre:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- O intervalo de critérios 2 é um quarto (C5: C20)
- O critério 2 é G5 ("Q3")
Com esses critérios, SUMIFS retorna 127.250, o número de vendas do terceiro trimestre central.
O comportamento de SUMIFS é somar todos os valores correspondentes. No entanto, como há apenas um valor correspondente, o resultado é igual ao valor em si.
Abaixo, examinamos várias opções de fórmulas de pesquisa.
Opções de fórmula de pesquisa
Esta seção analisa brevemente outras opções de fórmula que produzem o mesmo resultado. Com exceção de SUMPRODUCT (na parte inferior), essas são fórmulas de pesquisa mais tradicionais que localizam a posição do valor de destino e retornam o valor naquele local.
Com VLOOKUP
Infelizmente, VLOOKUP não é uma boa solução para esse problema. Com uma coluna auxiliar, é possível construir uma fórmula VLOOKUP para corresponder a vários critérios (exemplo aqui), mas é um processo complicado que requer que você mexa nos dados de origem.
Com INDEX e MATCH
INDEX e MATCH são uma combinação de pesquisa muito flexível que pode ser usada para todos os tipos de problemas de pesquisa e este exemplo não é exceção. Com INDEX e MATCH, podemos pesquisar as vendas por região e trimestre com uma fórmula de matriz como esta:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
O truque com essa abordagem é usar a lógica booleana com operações de array dentro da função MATCH para construir um array de 1s e 0s como array de pesquisa. Então, podemos pedir à função MATCH para encontrar o número 1. Depois que a matriz de pesquisa é criada, a fórmula é resolvida para:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Com apenas 1 restante na matriz de pesquisa, MATCH retorna uma posição de 11 para a função INDEX e INDEX retorna o número de vendas naquela posição, 127.250.
Para obter mais detalhes, consulte: ÍNDICE e CORRESPONDÊNCIA com vários critérios
Com XLOOKUP
XLOOKUP é uma nova função flexível no Excel que pode manipular matrizes nativamente. Com XLOOKUP, podemos usar exatamente a mesma abordagem que com INDEX e MATCH, usando lógica booleana e operações de matriz para criar uma matriz de pesquisa:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Depois que as operações de matriz são executadas, a fórmula é resolvida para:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
E XLOOKUP retorna o mesmo resultado acima, 127.250.
Mais: XLOOKUP com vários critérios
Com LOOKUP
A função LOOKUP é uma função mais antiga do Excel que muitas pessoas nem conhecem. Um dos principais pontos fortes do LOOKUP é que ele pode manipular matrizes nativamente. No entanto, LOOKUP tem alguns pontos fracos distintos:
- Não pode ser bloqueado no "modo de correspondência exata"
- Sempre assume que os dados de pesquisa estão classificados, AZ
- Sempre retorna uma correspondência aproximada (se a correspondência exata não puder ser encontrada)
No entanto, o LOOKUP pode ser usado para resolver esse problema da seguinte maneira:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
que simplifica para:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Se a situação não atender a ambos os requisitos, SUMIFS não é uma boa escolha.
