Conte valores numéricos exclusivos com base em critérios no Excel
Na planilha do Excel, você pode ter um problema de contagem do número de valores numéricos exclusivos com base em uma condição específica. Por exemplo, como posso contar os valores de Qty exclusivos do produto “T-shirt” do relatório conforme a captura de tela abaixo mostrada? Neste artigo, mostrarei algumas fórmulas para realizar essa tarefa no Excel.
- Conte valores numéricos exclusivos com base em critérios no Excel 2019, 2016 e anteriores
- Conte valores numéricos exclusivos com base em critérios no Excel 365
Conte valores numéricos exclusivos com base em critérios no Excel 2019, 2016 e anteriores
No Excel 2019 e em versões anteriores, você pode combinar as funções SUM, FREQUENCY e IF para criar uma fórmula para contar valores únicos com base em critérios, a sintaxe genérica é:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: O intervalo de células para corresponder aos critérios que você especificou;
- criteria: A condição na qual você deseja contar os valores exclusivos;
- range: O intervalo de células com valores exclusivos 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, consulte a captura de tela:
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 IF retorna o valor na coluna C se o produto na coluna A for “T-shirt”, o resultado é uma matriz como esta: {FALSE; 300; 500; FALSE; 400; FALSE; 300; FALSE; FALSE; FALSE; 350}.
- FREQUENCY(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 FREQÜÊNCIA é usada para contar cada um dos valores numéricos na lista da matriz e retornar o resultado como este: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0; XNUMX} .
- --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Teste se cada valor na matriz é maior que 0 e obtenha o resultado como este: {FALSO; VERDADEIRO; VERDADEIRO; VERDADEIRO; VERDADEIRO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}. E então, o sinal duplo negativo converte os TRUEs e FALSEs em 1s e 0s, retornando uma matriz como esta: {0; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0; XNUMX; XNUMX}.
- SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Finalmente, use a função SUM para somar esses valores e obter o número total: 4.
Dicas:
Se você deseja contar os valores únicos com base em mais de uma condição, você só precisa adicionar outros critérios à fórmula com o caractere *:
Conte valores numéricos exclusivos com base em critérios no Excel 365
No Excel 365, a combinação das funções ROWS, UNIQUE e FILTER pode ajudar a contar valores numéricos exclusivos com base em critérios, a sintaxe genérica é:
- range: O intervalo de células com valores exclusivos a serem contados.
- criteria_range: O intervalo de células para corresponder aos critérios que você especificou;
- criteria: A condição na qual você deseja contar os valores exclusivos;
Copie ou insira a seguinte fórmula em uma célula e pressione Entrar chave para retornar o resultado, veja a captura de tela:
Explicação da fórmula:
=ROWS(UNIQUE(FILTER(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}.
- FILTER(C2:C12,A2:A12=E2): A função FREQÜÊNCIA é usada para contar cada um dos valores numéricos na lista da matriz e retornar o resultado como este: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0; XNUMX} .
- UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Aqui, a função UNIQUE é usada para extrair valores exclusivos da matriz da lista para obter este resultado: {300; 500; 400; 350}.
- ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): A função ROWS retorna o número de linhas com base em um intervalo de células ou matriz, portanto, 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, você só precisa adicionar outros critérios à fórmula com * caractere como este:
Função relativa usada:
- SOMA:
- A função SUM do Excel retorna a soma dos valores fornecidos.
- FREQÜÊNCIA:
- A função FREQUENCY calcula a freqüência com que os valores ocorrem dentro de uma faixa de valores e, em seguida, retorna uma matriz vertical de números.
- LINHAS:
- A função ROWS retorna o número de linhas em uma determinada referência ou matriz.
- EXCLUSIVO:
- A função UNIQUE retorna uma lista de valores exclusivos em uma lista ou intervalo.
- FILTRO:
- A função FILTER ajuda a filtrar uma gama de dados com base nos critérios que você define.
Mais artigos:
- Conte valores numéricos exclusivos ou datas em uma coluna
- Supondo que você tenha uma lista de números que contém algumas duplicatas, agora, você deseja contar o número dos valores únicos ou os valores aparecem apenas uma vez na lista, como mostrado abaixo na captura de tela. Neste artigo, falaremos sobre algumas fórmulas úteis para resolver essa tarefa no Excel de forma rápida e fácil.
- Contar todas as correspondências / duplicatas entre duas colunas
- Comparar duas colunas de dados e contar todas as correspondências ou duplicatas nas duas colunas pode ser uma tarefa comum para a maioria de nós. Por exemplo, você tem duas colunas de nomes, alguns nomes aparecem na primeira e na segunda colunas, agora, você deseja contar todos os nomes correspondidos (as correspondências localizadas em qualquer lugar dentro das duas colunas) entre duas colunas, conforme a captura de tela mostrada abaixo, neste tutorial apresentará algumas fórmulas para atingir esse objetivo no Excel.
- Contar o número de células é igual a um de muitos valores
- Supondo que eu tenha uma lista de produtos na coluna A, agora, desejo obter o número total de produtos específicos Maçã, Uva e Limão listados no intervalo C4: C6 da coluna A, conforme a captura de tela abaixo. Normalmente, no Excel, as funções CONT.SE e CONT.SE não funcionarão neste cenário. Neste artigo, vou falar sobre como resolver esse trabalho de forma rápida e fácil com a combinação das funções SUMPRODUCT e COUNTIF.
As melhores ferramentas de produtividade para escritório
Kutools for Excel - ajuda você a se destacar da multidão
Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.
- Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
- Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
- Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
- Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
