Como contar o número de valores únicos num intervalo com base em múltiplos critérios no Excel?
Em muitos cenários práticos, não basta apenas contar valores — é essencial determinar quantos elementos únicos satisfazem determinadas condições nos seus dados. Por exemplo, talvez queira saber quantos produtos diferentes foram vendidos por um determinado vendedor ou quantas encomendas únicas foram efetuadas num dado período. Executar estas tarefas de forma eficiente no Excel exige familiaridade com fórmulas adequadas, funcionalidades avançadas como Tabelas Dinâmicas ou até soluções personalizadas em VBA. Neste artigo, exploramos vários métodos práticos para contar o número de valores únicos num intervalo com base num ou mais critérios, com instruções passo a passo e dicas úteis.
Contar o número de valores únicos em um intervalo com base num critério
Contar o número de valores únicos em um intervalo com base em duas datas fornecidas
Contar o número de valores únicos em um intervalo com base em dois critérios
Contar o número de valores únicos em um intervalo com base em três critérios
Contar o número de valores únicos em um intervalo com base num critério
Considere um cenário comum: pretende contar quantos produtos diferentes foram vendidos pelo Tom. Este método é ideal para conjuntos de dados simples, quando precisa avaliar a unicidade com base numa única condição — como os registos de vendas de uma única pessoa. É direto, mas exige atenção no uso de fórmulas matriciais.
Para este cenário, introduza a seguinte fórmula numa célula vazia (por exemplo, célula G2):
=SUM(IF(«Tom»=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20)),0))
Depois de digitar a fórmula, prima Ctrl + Shift + Enter (e não apenas Enter) para confirmá-la como uma fórmula matricial. As chavetas aparecerão automaticamente à volta da fórmula na Barra de Fórmulas e verá imediatamente o resultado, tal como ilustrado abaixo:
Nota:
- «Tom» é a condição que pretende utilizar para filtrar os resultados. Pode substituir «Tom» por uma referência a outra célula (por exemplo, $F$2) para obter maior flexibilidade.
- $C$2:$C$20 contém os nomes dos vendedores a serem avaliados.
- $A$2:$A$20 é a coluna de produtos relativamente à qual pretende obter contagens únicas.
- Se o seu intervalo de dados mudar, lembre-se de atualizar as referências em conformidade.
Dica: Se utilizar o Excel 365 ou o Excel 2019 e versões posteriores, experimente usar as funções UNIQUE e FILTER para obter fórmulas mais simples.
Se encontrar erros #DIV/0!, verifique novamente os critérios e certifique-se de que os seus intervalos têm o mesmo comprimento.
Contar o número de valores únicos em um intervalo com base em duas datas fornecidas
Quando precisar de determinar o número de elementos únicos num determinado intervalo de datas — por exemplo, todos os produtos únicos vendidos entre 1/9/2016 e 30/9/2016 — pode aplicar esta abordagem. É especialmente útil na análise de tendências de dados em períodos específicos, como mensais, trimestrais ou intervalos de datas personalizados. Contudo, certifique-se de que a formatação das datas corresponde aos valores de data da sua folha de cálculo.
Introduza a seguinte fórmula numa célula vazia onde pretenda apresentar o resultado:
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Prima Ctrl + Shift + Enter após introduzir a fórmula para a executar como uma fórmula matricial. A imagem seguinte demonstra o resultado:
Nota:
- 2016,9,1 e 2016,9,30 são as datas de início e de término. Pode ajustá-las conforme necessário ou até utilizar referências a células para criar filtros de data dinâmicos.
- $D$2:$D$20 contém as datas a verificar.
- $A$2:$A$20 é novamente a coluna com os artigos ou produtos cuja contagem única pretende efetuar.
- Certifique-se de que as suas datas estão armazenadas como datas válidas do Excel e não como texto. Caso o resultado não seja o esperado, verifique a formatação das datas e os intervalos utilizados.
Dica: Use a função DATA(ano, mês, dia) para evitar problemas com a formatação regional das datas. Ao trabalhar com intervalos dinâmicos, opte por intervalos com nome para garantir maior clareza.
Contar o número de valores únicos em um intervalo com base em dois critérios
Imagine que pretende analisar apenas os produtos vendidos pelo Tom em setembro, combinando o nome e um intervalo de datas na sua contagem única. Este cenário é comum em avaliações periódicas de desempenho ou análises segmentadas. À medida que os seus critérios se expandem, a fórmula torna-se mais complexa, tornando ainda mais essencial a atenção à precisão dos dados.
Introduza a fórmula abaixo em qualquer célula vazia, por exemplo H2:
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Após digitar a fórmula, confirme-a com Ctrl + Shift + Enter. Verá imediatamente a contagem única — consulte a ilustração seguinte:
Notas:
- «Tom» é o critério de nome, enquanto «2016,9,1» e «2016,9,30» definem os limites do intervalo de datas. Ajuste-os conforme necessário ou torne-os dinâmicos com referências a células.
- $C$2:$C$20 é a coluna dos colaboradores (ou outro primeiro critério); $D$2:$D$20 é a coluna das datas; e $A$2:$A$20 contém os elementos únicos a contar.
- Os intervalos devem ter todos o mesmo comprimento para prevenir erros.
Se pretender utilizar condições “ou” — por exemplo, para contar produtos únicos vendidos pelo Tom ou na região Sul — pode usar a seguinte fórmula. Isto permite critérios de pesquisa mais abrangentes, embora os resultados possam sobrepor-se caso os dados satisfaçam ambos os critérios:
=SOMA(--(FREQUÊNCIA(SE((«Tom»=$C$2:$C$20)+(«South»=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
Não se esqueça de premir Ctrl + Shift + Enter. Verá os resultados tal como ilustrado abaixo:
Dica: Ao aplicar critérios OR, esteja atento à possível contagem duplicada se o mesmo registo satisfizer ambas as condições. Em conjuntos de dados grandes, o desempenho poderá ser afetado.
Contar o número de valores únicos em um intervalo com base em três critérios
Por vezes, a sua análise poderá exigir três ou mais condições — por exemplo, identificar os produtos únicos vendidos pelo Tom em setembro, exclusivamente na região Norte. Esta situação é comum na Análise de Dados multidimensionais, especialmente para relatórios ou informações empresariais segmentadas. Uma gestão cuidadosa das referências é essencial ao trabalhar com esta lógica composta.
Introduza esta fórmula matricial numa célula vazia (por exemplo, I2):
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(«North»=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Prima Ctrl + Shift + Enter para concluir. Eis um resultado de exemplo para referência:
Para condições avançadas, verifique cuidadosamente se todas as gamas são consistentes e se os tipos de dados — como datas e texto — estão corretos, pois desalinhamentos podem causar erros ou resultados enganosos.
Dicas:
- Se encontrar problemas de desempenho com conjuntos de dados grandes, considere dividir a fórmula ou utilizar a solução Tabela Dinâmica do Excel.
- Utilizar intervalos com nome ou referenciar células em todos os critérios melhora a legibilidade e reduz erros nas fórmulas.
- Para utilização frequente, considere guardar estas fórmulas em referências de células com nome ou em funções personalizadas.
Contar o número de valores únicos em um intervalo com Tabela Dinâmica (Contagem Distinta, Excel 2013+)
Para utilizadores do Excel 2013 ou posterior, as Tabelas Dinâmicas oferecem uma alternativa interativa e sem fórmulas para contar o número de valores únicos num intervalo com um ou vários critérios. A funcionalidade Contagem Distinta permite-lhe resumir e filtrar grandes conjuntos de dados de forma eficiente, tornando este método especialmente adequado para ambientes dinâmicos orientados por relatórios. Contudo, note que versões anteriores do Excel não suportam a função Contagem Distinta nas Tabelas Dinâmicas.
Como utilizar este método:
- Selecione o 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, assinale a caixa "Adicionar estes dados ao Modelo de Dados" e, em seguida, clique em OK.
- Arraste o campo cuja contagem única pretende obter (por exemplo, Produto) para a área Valores. Por predefinição, aparecerá como «Contagem de...».
- Clique no campo na área Valores e selecione Campo de Valor: Configurações de Campo.
- Na caixa de diálogo que surge, percorra até ao fundo e selecione Contagem Distinta. (Esta opção só está disponível no Excel 2013 ou posterior e aparece quando a Tabela Dinâmica é criada com a opção «Adicionar estes dados ao Modelo de Dados» ativada.)
- Adicione os seus campos de critérios (por exemplo, Vendedor, Região, Data) às áreas Filtros ou Linhas/Colunas para aplicar condições únicas ou múltiplas.
- A sua Tabela Dinâmica apresentará agora a contagem única de valores, filtrada de acordo com os critérios selecionados.
Vantagens: Altamente visual, fácil de ajustar filtros sem editar fórmulas e ideal para relatórios interativos.
Limitações: Não disponível no Excel 2010 ou versões anteriores; a adição de novos dados exige a atualização manual da Tabela Dinâmica.
Dica prática: Certifique-se sempre de que os dados na origem não contêm duplicados dentro do mesmo registo, a menos que sejam intencionais. Se verificar que a opção Contagem Distinta está em falta, recrie a Tabela Dinâmica e ative a opção «Adicionar estes dados ao Modelo de Dados».
Contar o número de valores únicos em um intervalo com código VBA (para casos complexos/automatizados)
Por vezes, poderá precisar de contar automaticamente o número de valores únicos num intervalo com base em diversos critérios — especialmente ao trabalhar com conjuntos de dados muito grandes ou ao repetir frequentemente a mesma análise. Nesses casos, uma macro VBA é a solução ideal, pois processa rapidamente lógicas complexas, incluindo filtragem com múltiplas condições, sem necessidade de intervenção manual após a configuração inicial. Contudo, o VBA é mais avançado do que as funcionalidades habituais do Excel, sendo por isso mais adequado a utilizadores já familiarizados com macros ou com necessidades analíticas contínuas.
Passos operacionais:
- Prima Alt + F11 para abrir o editor do 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 do VBA e regresse à sua folha de cálculo. Prima Alt + F8, selecione CountUniqueWithCriteria e execute a macro.
- Siga as instruções indicadas para definir os intervalos e critérios de acordo com os seus dados. O resultado será apresentado na célula que selecionar e também numa caixa de mensagem.
Explicação dos parâmetros e notas:
- Esta macro está atualmente configurada para um único critério. Para a adaptar a vários critérios, basta modificar a lógica
If ... Thendentro do ciclo. - Guarde sempre a sua pasta de trabalho antes de executar macros, pois as alterações efetuadas não podem ser anuladas.
- Ative as macros nas definições do Excel se encontrar erros de execução.
- Este método é ideal para conjuntos de dados maiores ou frequentemente atualizados, onde fórmulas manuais seriam incómodas.
Benefícios: Altamente personalizável e automatizável, trata eficientemente conjuntos de dados grandes e em constante mudança. Ideal para necessidades avançadas ou fluxos de trabalho repetitivos.
Desvantagens: Requer permissões de macro e os principiantes poderão precisar de algum tempo para se familiarizarem com operações em VBA.
Ao trabalhar com contagens de valores únicos com base em critérios, verifique sempre as suas referências de intervalo e certifique-se de que todas as colunas de critérios têm dimensões alinhadas. Intervalos com tamanhos incompatíveis são uma fonte frequente de erros ou resultados incorretos. Se as fórmulas devolverem resultados inesperados, confirme a existência de problemas ocultos de formatação ou células vazias. Em cenários críticos em termos de desempenho, as Tabelas Dinâmicas e o VBA oferecem alternativas robustas às fórmulas matriciais. Escolha a solução que melhor se adapta ao seu nível de conforto e à complexidade do seu conjunto de dados. Lembre-se de que o Kutools para Excel disponibiliza utilitários e atalhos adicionais que simplificam muitas destas tarefas, potenciando ainda mais a eficiência em livros de trabalho complexos.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos