Contar valores numéricos únicos com base em critérios no Excel

Na folha de cálculo do Excel, poderá deparar-se com um desafio ao tentar contar o número de valores numéricos únicos com base numa condição específica. Por exemplo, como contar os valores únicos da coluna «Quantidade» correspondentes ao produto «T-shirt» no relatório apresentado na imagem seguinte? Neste artigo, mostramos algumas fórmulas eficazes para realizar esta tarefa no Excel.

• Contar valores numéricos únicos com base em critérios no Excel 2019, 2016 e versões anteriores
• Contar valores numéricos únicos com base em critérios no Excel 365

Contar valores numéricos únicos com base em critérios no Excel 2019, 2016 e versões anteriores

No Excel 2019 e versões anteriores, pode combinar as funções SOMA, FREQUÊNCIA e SE para criar uma fórmula que conte o número de valores únicos num intervalo com base em determinados critérios. A sintaxe genérica é:

• criteria_range: O intervalo de células a comparar com os critérios que especificou;
• criteria: A condição com base na qual pretende Contar o número de valores únicos em um intervalo;
• range: O intervalo de células que contém os valores únicos a contar.

Aplique a fórmula abaixo numa célula vazia e prima Ctrl + Shift + Enterpara obter o resultado correto, conforme ilustrado:

Explicação da fórmula:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>,0))

• IF(A2:A12=E2,C2:C12): Esta função SE devolve o valor da coluna C sempre que o produto na coluna A for «T-shirt». O resultado é uma matriz como esta: {FALSO;300;500;FALSO;400;FALSO;300;FALSO;FALSO;FALSO;350}.
• FREQUÊNCIA(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): A função FREQUÊNCIA conta quantas vezes cada valor numérico aparece na matriz e devolve o resultado neste formato: {0;2;1;1;1;0;0;0;0;0;0;0}.
• --(FREQUÊNCIA(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Testa se cada valor na matriz é superior a 0, obtendo o seguinte resultado: {FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}. Em seguida, o duplo sinal negativo converte os valores VERDADEIRO e FALSO em 1 e 0, devolvendo uma matriz como esta: {0;1;1;1;1;0;0;0;0;0;0;0}.
• SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0,1,1,1,1,0,0,0,0,0,0,0}): Por fim, utilize a função SOMA para somar esses valores e obter o total: 4.

Dicas:

Se pretender contar os valores únicos com base em mais do que uma condição, basta adicionar outros critérios à fórmula com o carácter *:

Contar valores numéricos únicos com base em critérios no Excel 365

No Excel 365, a combinação das funções LINHAS, ÚNICO e FILTRAR permite contar valores numéricos únicos com base em critérios. A sintaxe genérica é:

• range: O intervalo das células com valores únicos a serem contados.
• criteria_range: O intervalo de células a comparar com os critérios que especificou;
• criteria: A condição com base na qual pretende Contar o número de valores únicos em um intervalo;

Copie ou introduza a seguinte fórmula numa célula e prima Enterpara obter o resultado, conforme ilustrado:

Explicação da fórmula:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

• A2:A12=E2: Esta expressão verifica se o valor da célula E2 existe no intervalo A2:A12 e devolve este resultado: {FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO}.
• FILTRAR(C2:C12,A2:A12=E2): A função FREQUÊNCIA conta a ocorrência de cada valor numérico na matriz e devolve o resultado no seguinte formato: {0;2;1;1;1;0;0;0;0;0;0;0}.
• ÚNICO(FILTRAR(C2:C12,A2:A12=E2))=UNIQUE({300,500,400,300,350}): Aqui, a função ÚNICO extrai os valores únicos da matriz resultante e devolve este resultado: {300;500;400;350}.
• LINHAS(ÚNICO(FILTRAR(C2:C12,A2:A12=E2)))=ROWS({300,500,400,350}): A função LINHAS devolve o número de linhas com base num intervalo de células ou numa matriz, pelo que o resultado é: 4.

Dicas:

1. Se o valor correspondente não existir no Intervalo de Dados, será devolvido um valor de erro. Para substituir esse erro por 0, utilize a seguinte fórmula:

2. Para contar valores únicos com base em múltiplas condições, basta adicionar outros critérios à fórmula utilizando o carácter *, como no exemplo seguinte:

Função relacionada utilizada:

Mais artigos: