Procurando por uma maneira mais eficiente de comparar várias linhas do que COUNTIFS e MINIFS

Question

Eu tenho uma planilha com centenas de milhares de linhas em várias planilhas, usando várias colunas de VLOOKUPs em cada planilha, além de algumas colunas de fórmulas como COUNTIFS e SUMIFS que examinam todas as linhas de uma planilha sempre que calculam. Aumenta meu processador toda vez que ele calcula, e às vezes leva tanto tempo que eu posso preparar uma xícara de café antes de terminar.

Eu já tornei muito menos terrível usando MATCH ("*", [column], - 1) para encontrar a última linha e gerar dinamicamente o intervalo em vez de apenas referenciar a coluna inteira, e substituindo o VLOOKUPs com INDEX MATCHes. Existem apenas duas fórmulas que não consigo descobrir como melhorar.

Aqui está uma aproximação dos dados com os quais estou trabalhando:

+--------+-------+----------+--------+
| Group  | Item  | Quantity | Round  |
+--------+-------+----------+--------+
| Group1 | Item1 |        2 | 1      |
| Group1 | Item2 |        2 | 2      |
| Group2 | Item1 |        2 | 1      |
| Group2 | Item2 |        3 | 2      |
| Group2 | Item3 |        2 | unused |
+--------+-------+----------+--------+

Para o primeiro problema, quero ver se todos os itens do grupo têm a mesma quantidade. A versão antiga usava essa fórmula:

=COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,C:C,C2)

Posso realizar a mesma coisa sem usar o COUNTIFS? Está trabalhando com um pivot dos dados que provavelmente melhorará o desempenho em vez de trabalhar com os dados brutos em si? Ou é o melhor que posso fazer para substituir as referências da coluna inteira pelos intervalos dinâmicos calculados pelo MATCH?

Para o segundo problema, estou tentando encontrar o valor mínimo "redondo" para o grupo. Fórmula antiga:

=MINIFS(D:D,A:A,A2)

Costumava ser uma fórmula de matriz -

={MIN(IF(A:A,A2),IF(P:P>0,P:P))}

- por isso fiquei super animado quando o MINIFS foi adicionado ao Excel. Mas há algo melhor do que o MINIFS que eu possa usar para obter essa resposta?

    

Answer 1

Você pode fazer isso totalmente com uma tabela dinâmica. Na minha experiência, uma tabela dinâmica (uma vez criada e armazenada em cache) é muito mais rápida do que as fórmulas sumif / countif, mas depende do tamanho do seu conjunto de dados.

Crie uma tabela dinâmica nos seus dados da seguinte forma:

• Selecione seus dados, clique no botão Tabela dinâmica, clique em OK
• Arraste "Grupo" para a caixa de linhas
• Arraste "Arredondar" para a caixa de colunas, clique nele, clique em "Configurações do campo de valor", selecione "Min" e clique em OK - o seu mínimo para cada grupo
• Arraste "Quantidade" para a caixa de colunas, clique nela, clique em "Configurações do campo de valor", selecione "Min" e clique em OK
• Arraste "Quantidade" para a caixa de colunas, clique nela, clique em "Configurações do campo de valor", selecione "Max" e clique em OK

Se "Min of Quantity" e "Max of Quantity" forem iguais, você sabe que todos os itens do grupo têm a mesma quantidade. Você pode criar uma fórmula para o lado da tabela dinâmica para avaliar isso para você.

Captura de tela de como ele deve ficar no Excel