Note: The other languages of the website are Google-translated. Back to English

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

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 é:

{=SUM(--(FREQUENCY(IF(criteria_range=criteria,range),range)>0))}
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:

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


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 *:

=SUM(--(FREQUENCY(IF((criteria,_range1=criteria1)* (criteria,_range2=criteria2)*…,range),range)>0))

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 é:

=ROWS(UNIQUE(FILTER(range,criteria_range=criteria)))
  • 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:

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


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:

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

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:

=ROWS(UNIQUE(FILTER(range,(criteria_range1=criteria1)* (criteria_range2=criteria2)*…)))

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.
Faixa de opções do Excel (com Kutools para Excel instalado)

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.
Captura de tela do Excel (com guia Office instalado)
Comentários (0)
Ainda não há classificações. Seja o primeiro a avaliar!
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL