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

Em uma planilha do Excel, você pode enfrentar o problema de contar o número de valores numéricos únicos com base em uma condição específica. Por exemplo, como posso contar os valores únicos de 'Qty' do produto 'Camiseta' no relatório, conforme mostrado na captura de tela abaixo? Neste artigo, mostrarei algumas fórmulas para realizar essa 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, você pode combinar as funções SOMA, FREQUÊNCIA e SE para criar uma fórmula que conte valores únicos com base em critérios. A sintaxe genérica é:

• intervalo_critérios: O intervalo de células para corresponder ao critério especificado;
• critério: A condição com base na qual você deseja contar valores únicos;
• intervalo: O intervalo de células com valores únicos a serem contados.

Por favor, aplique a fórmula abaixo em uma célula em branco e pressione Ctrl + Shift + Enter para obter o resultado correto, veja a captura de tela:

Explicação da fórmula:

=SOMA(--(FREQUÊNCIA(SE(A2:A12=E2,C2:C12),C2:C12)>0))

• SE(A2:A12=E2,C2:C12): Esta função SE retorna o valor na coluna C se o produto na coluna A for “Camiseta”, o resultado é um array como este: {FALSO;300;500;FALSO;400;FALSO;300;FALSO;FALSO;FALSO;350}.
• FREQUÊNCIA(SE(A2:A12=E2,C2:C12),C2:C12)= FREQUÊNCIA({FALSO;300;500;FALSO;400;FALSO;300;FALSO;FALSO;FALSO;350},{200;300;500;350;400;450;300;550;200;260;350}): A função FREQUÊNCIA é usada para contar cada um dos valores numéricos na lista de arrays e retornar o resultado como este: {0;2;1;1;1;0;0;0;0;0;0;0}.
• --(FREQUÊNCIA(SE(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 no array é maior que 0 e obtém o resultado como este: {FALSO;VERDADEIRO;VERDADEIRO;VERDADEIRO;VERDADEIRO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}. Em seguida, o sinal negativo duplo converte VERDADEIROs e FALSOSs em 1s e 0s, retornando um array como este: {0;1;1;1;1;0;0;0;0;0;0;0}.
• SOMA(--(FREQUÊNCIA(SE(A2:A12=E2,C2:C12),C2:C12)>0))=SOMA({0;1;1;1;1;0;0;0;0;0;0;0}): Finalmente, use a função SOMA para somar esses valores e obter o total: 4.

Dicas:

Se você quiser contar os valores únicos com base em mais de uma condição, basta adicionar outros critérios à fórmula com o caractere *:

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 pode ajudar a contar valores numéricos únicos com base em critérios. A sintaxe genérica é:

• intervalo: O intervalo de células com valores únicos a serem contados.
• intervalo_critérios: O intervalo de células para corresponder ao critério especificado;
• critério: A condição com base na qual você deseja contar valores únicos;

Por favor, copie ou insira a seguinte fórmula em uma célula e pressione a tecla Enter para retornar o resultado, veja a captura de tela:

Explicação da fórmula:

=LINHAS(ÚNICO(FILTRAR(C2:C12,A2:A12=E2)))

• A2:A12=E2: Esta expressão verifica se o valor na célula E2 existe no intervalo A2:A12 e obtém este resultado: {FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO}.
• FILTRAR(C2:C12,A2:A12=E2): A função FREQUÊNCIA é usada para contar cada um dos valores numéricos na lista de arrays e retornar o resultado como este: {0;2;1;1;1;0;0;0;0;0;0;0}.
• ÚNICO(FILTRAR(C2:C12,A2:A12=E2))=ÚNICO({300;500;400;300;350}): Aqui, a função ÚNICO é usada para extrair valores únicos da lista de arrays para obter este resultado: {300;500;400;350}.
• LINHAS(ÚNICO(FILTRAR(C2:C12,A2:A12=E2)))=LINHAS({300;500;400;350}): A função LINHAS retorna o número de linhas com base em um intervalo de células ou array, então o resultado é: 4.

Dicas:

1. Se o valor correspondente não existir no intervalo de dados, você obterá um valor de erro. Para substituir o valor de erro por 0, aplique a seguinte fórmula:

2. Para contar os valores únicos com base em mais de uma condição, basta adicionar outros critérios à fórmula com o caractere * assim:

Função relacionada usada:

Mais artigos: