Como contar valores únicos com base em múltiplos critérios no Excel?
Em muitos cenários práticos, muitas vezes é necessário não apenas contar valores, mas determinar quantos itens únicos atendem a certas condições dentro de seus dados. Por exemplo, você pode querer descobrir a contagem de diferentes produtos que um vendedor específico vendeu ou quantos pedidos únicos foram feitos dentro de um certo período de tempo. Lidar com essas tarefas de forma eficiente no Excel requer familiarizar-se com fórmulas adequadas, recursos avançados como tabelas dinâmicas ou até soluções personalizadas em VBA. Neste artigo, exploraremos vários métodos práticos para contar valores únicos com base em um ou mais critérios, com instruções passo a passo e dicas.
Contar valores únicos com base em um critério
Contar valores únicos com base em duas datas fornecidas
Contar valores únicos com base em dois critérios
Contar valores únicos com base em três critérios
Contar valores únicos com Tabela Dinâmica (Contagem Distinta, Excel 2013+)
Contar valores únicos com Código VBA (para casos complexos/automatizados)
Contar valores únicos com base em um critério
Vamos considerar um caso comum: você deseja contar quantos produtos diferentes foram vendidos por Tom. Este método é adequado quando você tem um conjunto de dados simples e pretende avaliar a singularidade com base em uma única condição, como os registros de vendas de uma pessoa. É direto, mas exige o uso cuidadoso de fórmulas matriciais.
Para este cenário, insira a seguinte fórmula em uma célula em branco (por exemplo, célula G2):
=SOMA(SE("Tom"=$C$2:$C$20;1/(CONT.SE.S($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20));0))
Depois de digitar a fórmula, pressione Ctrl + Shift + Enter (não apenas Enter) para confirmá-la como uma fórmula matricial. As chaves aparecerão ao redor da fórmula na barra de fórmulas, e você verá o resultado instantaneamente, conforme mostrado abaixo:
Nota:
- “Tom” é o critério que você deseja usar para filtrar os resultados. Você pode substituir "Tom" por uma referência a outra célula (por exemplo, $F$2), se quiser mais flexibilidade.
- $C$2:$C$20 contém os nomes dos vendedores a serem avaliados.
- $A$2:$A$20 é a coluna de produtos para a qual você deseja contagens únicas.
- Se o intervalo de dados mudar, lembre-se de ajustar as referências de acordo.
Dica: Se estiver usando o Excel 365 ou Excel 2019 e posterior, você pode tentar usar as funções ÚNICO e FILTRAR para fórmulas mais simples.
Se você encontrar algum erro #DIV/0!, verifique novamente os critérios e certifique-se de que seus intervalos tenham o mesmo comprimento.
Contar valores únicos com base em duas datas fornecidas
Quando você precisa encontrar o número de itens únicos dentro de um intervalo de datas específico, por exemplo, todos os produtos únicos vendidos entre 01/09/2016 e 30/09/2016, você pode aplicar essa abordagem. Isso é especialmente útil ao analisar tendências de dados entre períodos específicos, como mensais, trimestrais ou intervalos de datas personalizados. No entanto, tenha cuidado com a formatação de data; ela deve corresponder aos valores de data em sua planilha.
Coloque a seguinte fórmula em uma célula em branco onde você deseja exibir o resultado:
=SOMA(SE($D$2:$D$20<=DATA(2016;9;30)*($D$2:$D$20>=DATA(2016;9;1));1/CONT.SE.S( $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATA(2016;9;30);$D$2:$D$20; ">="&DATA(2016;9;1)));0)
Pressione Ctrl + Shift + Enter após inserir a fórmula para executá-la como uma fórmula matricial. A captura de tela abaixo demonstra o resultado:
Nota:
- 2016,9,1 e 2016,9,30 são os critérios de data inicial e final. Você pode modificar esses valores conforme necessário, ou até usar referências de células para filtros de data dinâmicos.
- $D$2:$D$20 contém as entradas de data a serem verificadas.
- $A$2:$A$20 é novamente a coluna de itens ou produtos que você deseja contar de forma única.
- Certifique-se de que suas datas estejam armazenadas como datas válidas do Excel, não como strings de texto. Se seu resultado não aparecer como esperado, confirme a formatação de data e os intervalos.
Dica: Use DATA(ano, mês, dia) para evitar problemas com a formatação de data regional. Ao usar intervalos dinâmicos, considere usar intervalos nomeados para maior clareza.
Contar valores únicos com base em dois critérios
Suponha que você deseje analisar apenas os produtos que Tom vendeu em setembro, combinando nome e um intervalo de datas em sua contagem única. Esse cenário é comum para avaliações de desempenho baseadas em período ou análises segmentadas. À medida que seus critérios aumentam, a fórmula fica mais complexa, e a atenção à precisão dos dados torna-se ainda mais importante.
Insira a fórmula abaixo em qualquer célula em branco, como H2:
=SOMA(SE(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATA(2016;9;30)*($D$2:$D$20>=DATA(2016;9;1)));1/CONT.SE.S($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATA(2016;9;30);$D$2:$D$20; ">="&DATA(2016;9;1)));0)
Depois de digitar a fórmula, confirme com Ctrl + Shift + Enter. Você deve ver a contagem única imediatamente; confira a ilustração a seguir:
Notas:
- “Tom” é o critério de nome, enquanto “2016,9,1” e “2016,9,30” são os limites do intervalo de datas. Ajuste conforme necessário ou torne-os dinâmicos com referências de células.
- $C$2:$C$20 é a coluna de funcionários (ou outro primeiro critério); $D$2:$D$20 é a coluna de datas; $A$2:$A$20 contém os itens únicos a serem contados.
- Os intervalos devem ter todos o mesmo comprimento para evitar erros.
Se você deseja usar condições de “ou”, como contar produtos únicos vendidos por Tom ou na região Sul, você pode usar a seguinte fórmula. Isso permite condições de pesquisa mais amplas, embora os resultados possam se sobrepor se os dados atenderem a ambos os critérios:
=SOMA(--(FREQUÊNCIA(SE(("Tom"=$C$2:$C$20)+("Sul"=$B$2:$B$20); CONT.SE($A$2:$A$20; "<"&$A$2:$A$20); ""); CONT.SE($A$2:$A$20; "<"&$A$2:$A$20))>0))
Não se esqueça de pressionar Ctrl + Shift + Enter. Você verá os resultados conforme mostrado abaixo:
Dica: Ao aplicar critérios OR, esteja ciente da possível contagem dupla se o mesmo registro atender a ambas as condições. Para grandes conjuntos de dados, o desempenho pode ser afetado.
Contar valores únicos com base em três critérios
Às vezes, sua análise pode exigir três ou mais condições, como determinar produtos únicos vendidos por Tom em setembro apenas na região Norte. Isso é comum em análises de dados multidimensionais para relatórios ou insights comerciais direcionados. O gerenciamento cuidadoso das referências é essencial para lidar com essa lógica composta.
Coloque esta fórmula matricial em uma célula em branco (por exemplo, I2):
=SOMA(SE(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATA(2016;9;30))*($D$2:$D$20>=DATA(2016;9;1))*("Norte"=$B$2:$B$20);1/CONT.SE.S($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATA(2016;9;30); $D$2:$D$20; ">="&DATA(2016;9;1); $B$2:$B$20; "Norte"));0)
Pressione Ctrl + Shift + Enter para finalizar. Aqui está um resultado de amostra para referência:
Para condições avançadas, verifique se todos os intervalos estão consistentes e que os tipos de dados (por exemplo, data e texto) estão corretos. Desalinhamentos podem causar erros ou resultados enganosos.
Dicas:
- Se você encontrar problemas de desempenho em grandes conjuntos de dados, considere dividir a fórmula ou usar a solução de Tabela Dinâmica do Excel.
- Intervalos nomeados ou referenciar células para todos os critérios melhora a legibilidade e reduz erros de fórmulas.
- Para uso frequente, considere registrar essas fórmulas em referências de células nomeadas ou funções personalizadas.
Contar valores únicos com Tabela Dinâmica (Contagem Distinta, Excel 2013+)
Para usuários do Excel 2013 ou posterior, as Tabelas Dinâmicas oferecem uma alternativa interativa e sem fórmulas para contar valores únicos em um ou vários critérios. O recurso Contagem Distinta ajuda você a resumir e filtrar grandes conjuntos de dados de forma eficiente, tornando esse método especialmente adequado para ambientes dinâmicos baseados em relatórios. No entanto, observe que versões anteriores do Excel não suportam a função Contagem Distinta dentro de Tabelas Dinâmicas.
Como usar este método:
- Selecione seu conjunto de dados e vá para Inserir > Tabela Dinâmica.
- Na caixa de diálogo Criar Tabela Dinâmica, escolha onde colocar a Tabela Dinâmica, marque a caixa "Adicionar esses dados ao Modelo de Dados" e clique em OK.
- Arraste o campo que você deseja contar de forma única (por exemplo, Produto) para a área Valores. Por padrão, ele será exibido como "Contagem de...".
- Clique no campo na área Valores e selecione Configurações de Campo de Valor.
- Na janela pop-up, role para baixo e selecione Contagem Distinta (Esta opção está disponível apenas no Excel 2013 ou posterior e aparece quando a Tabela Dinâmica é criada com a opção "Adicionar esses dados ao Modelo de Dados" habilitada.).
- Adicione seus campos de critérios (por exemplo, Vendedor, Região, Data) para os filtros ou áreas de Linhas/Colunas para aplicar condições únicas ou múltiplas.
- Sua Tabela Dinâmica agora exibirá a contagem única de valores filtrados pelos critérios escolhidos.
Vantagens: Altamente visual, fácil de ajustar filtros sem editar fórmulas e adequado para relatórios interativos.
Limitações: Não disponível no Excel 2010 ou anterior; adicionar novos dados requer a atualização manual da Tabela Dinâmica.
Dica prática: Sempre certifique-se de que os dados de origem não tenham duplicatas dentro do mesmo registro, se isso não for intencional. Se você notar que a opção Contagem Distinta está ausente, recrie a Tabela Dinâmica e marque a opção “Adicionar esses dados ao Modelo de Dados”.
Contar valores únicos com Código VBA (para casos complexos/automatizados)
Às vezes, você pode precisar contar valores únicos automaticamente com base em vários critérios, especialmente ao lidar com conjuntos de dados muito grandes ou ao repetir a análise frequentemente. Uma macro VBA é ideal para essas situações, pois pode processar rapidamente diferentes lógicas — incluindo filtros multi-condicionais — sem intervenção manual após a configuração. No entanto, o VBA é mais avançado do que os recursos regulares do Excel, então é melhor usado por usuários confortáveis com macros ou com necessidades analíticas contínuas.
Passos da operação:
- Pressione Alt + F11 para abrir o editor VBA. No editor, selecione Inserir > Módulo para criar um novo módulo.
- Copie e cole o seguinte código VBA no módulo:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Feche o editor VBA e retorne à sua planilha. Pressione Alt + F8, selecione ContarÚnicoComCritérios e execute a macro.
- Siga os prompts de entrada para especificar os intervalos e critérios de acordo com seus dados. O resultado aparecerá na célula que você escolher e também como uma caixa de mensagem.
Explicação de parâmetros e notas:
- Esta macro está configurada atualmente para um critério. Para estendê-la para múltiplos critérios, modifique a lógica Se ... Então dentro do loop.
- Sempre salve sua pasta de trabalho antes de executar macros, pois as alterações não podem ser desfeitas.
- Habilite macros nas configurações do Excel se você encontrar erros de execução.
- Este método funciona bem para dados maiores ou atualizados frequentemente, onde fórmulas manuais seriam complicadas.
Benefícios: Altamente personalizável e automatizável, lida com grandes e variáveis conjuntos de dados de forma eficiente. Adequado para necessidades avançadas ou fluxos de trabalho repetitivos.
Desvantagens: Requer permissões de macro, e iniciantes podem precisar de tempo para se familiarizar com as operações VBA.
Ao trabalhar com contagens de valores únicos com base em critérios, sempre confirme suas referências de intervalo e certifique-se de que todas as colunas de critérios estejam alinhadas em tamanho. Intervalos incompatíveis são uma fonte comum de erros ou resultados incorretos. Se as fórmulas retornarem resultados inesperados, verifique problemas ocultos de formatação ou células em branco. Para cenários críticos de desempenho, Tabelas Dinâmicas e VBA fornecem alternativas robustas às fórmulas matriciais. Escolha a solução mais adequada para o seu nível de conforto e a complexidade de seu conjunto de dados. Lembre-se, Kutools para Excel fornece utilitários e atalhos adicionais que podem simplificar muitas dessas tarefas para maior eficiência em pastas de trabalho complexas.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins