Excel 2013 INDEX / MATCH vs. SUMIFS eficiência para procurar valores com vários parâmetros

Question

Muitas vezes eu acho que preciso procurar um valor em uma tabela usando vários parâmetros. Por exemplo, em esta tabela , tenho duas colunas que contêm dois níveis de categorias (por exemplo, a categoria "Comida" com subcategorias "Mercearia", "Restaurantes", etc.).

Se eu quiser fazer algo como calcular os% de meus gastos com alimentos que foram para restaurantes, eu precisaria retirar o valor para alimentos / mantimentos e dividi-los pelo total de todos os meus registros de alimentos. Eu poderia recuperar o valor desse parâmetro de duas maneiras:

• Gere uma coluna "chave" que é a concatenação das duas categorias, e faça e INDEX / MATCH para encontrar o valor apropriado
  • No exemplo "chave": INDEX ($ E $ 3: $ E $ 9, MATCH ("FoodRestaurants", $ B $ 3: $ B $ 9,0)))
• Use SUMIFS () para somar todos os valores com os rótulos "Food" e "Groceries" em cada uma das respectivas colunas
  • Na tabela original: SUMIFS ($ D $ 3: $ D $ 9, $ B $ 3: $ B $ 9, "Food", $ C $ 3: $ C $ 9, "Restaurants")

De uma perspectiva de usabilidade, eu gostaria de me inclinar para o segundo desses dois, pois é mais fácil de construir e mais fácil de ler mais tarde. No entanto, minha preocupação é que, para pastas de trabalho muito grandes, ela poderia se tornar muito menos eficiente do que a abordagem INDEX / MATCH. Alguém tem alguma idéia da eficiência relativa dessas duas operações? Pontos de bônus se houver outro método melhor que o outro.

    

Answer 1

Se você precisar de um método diferente, use o sumproduct da seguinte forma:

=SUMPRODUCT((B3:B9="Food")*(C3:C9="Groceries")*D3:D9) e Where B3:B9="Food" will give 1 if True e Also C3:C9="Groceries" will give 1 if True

E o Sumproduct adicionará os valores dos produtos 1 * 1 * em 2015

Para comparar o Índice / Correspondência vs Sumifs, clique em aqui