Contar valores únicos no Excel usando a função CONT.SE

Neste tutorial, você aprenderá como contar valores exclusivos no Excel usando fórmulas (funções CONT.SE e SUMPRODUCT).

Como contar valores únicos no Excel

Digamos que temos um conjunto de dados conforme mostrado abaixo:

Para os fins deste tutorial, nomearei o intervalo A2: A10 como NAMES. No futuro, usaremos esse intervalo nomeado nas fórmulas.

Consulte também: Como criar intervalos nomeados no Excel.

Neste conjunto de dados, há uma repetição na faixa NAMES. Para obter a contagem de nomes exclusivos deste conjunto de dados (A2: A10), podemos usar uma combinação das funções CONT.SE e SUMPRODUCT, conforme mostrado abaixo:

= SUMPRODUCT (1 / COUNTIF (NAMES, NAMES))

Como essa fórmula funciona?

Vamos analisar esta fórmula para obter um melhor entendimento:

• CONT.SE (NOMES, NOMES)
  • Esta parte da fórmula retorna uma matriz. No exemplo acima, seria {2; 2; 3; 1; 3; 1; 2; 3; 2}. Os números aqui indicam quantas vezes um valor ocorre em um determinado intervalo de células.
    Por exemplo, o nome é Bob, que ocorre duas vezes na lista, portanto, ele retornaria o número 2 para Bob. Da mesma forma, Steve ocorre três vezes e, portanto, 3 é retornado para Steve.
• 1 / CONTADOR (NOMES, NOMES)
  • Esta parte da fórmula retornaria uma matriz - {0,5; 0,5; 0,33333333333333; 1; 0,33333333333333; 1; 0,5; 0,33333333333333; 0,5}
    Como dividimos 1 pelo array, ele retorna esse array.
    Por exemplo, o primeiro elemento da matriz retornada acima foi 2. Quando 1 é dividido por 2, ele retorna 0,5.
• SUMPRODUCT (1 / COUNTIF (NAMES, NAMES))
  • SUMPRODUCT simplesmente adiciona todos esses números. Observe que se Bob ocorrer duas vezes na lista, a matriz acima retornará .5 sempre que o nome de Bob aparecer na lista. Da mesma forma, como Steve aparece três vezes na lista, a matriz retorna .3333333 sempre que o nome de Steve aparece. Quando somarmos os números para cada nome, ele sempre retornará 1. E se somarmos todos os números, ele retornará a contagem total de nomes exclusivos na lista.

Esta fórmula funciona bem até que você não tenha nenhuma célula em branco no intervalo. Mas se você tiver células em branco, ele retornará um # DIV / 0! erro.

Como lidar com células EM BRANCO?

Vamos primeiro entender por que ele retorna um erro quando há uma célula em branco no intervalo. Suponha que temos o conjunto de dados conforme mostrado abaixo (com a célula A3 em branco):

Agora, se usarmos a mesma fórmula usada acima, a parte CONT.SE da fórmula retorna uma matriz {2; 0; 3; 1; 3; 1; 2; 3; 1}. Como não há texto na célula A3, sua contagem é retornada como 0.

E como estamos dividindo 1 por todo este array, ele retorna um # DIV / 0! erro.

Para lidar com esse erro de divisão no caso de células em branco, use a fórmula abaixo:

= SUMPRODUCT ((1 / COUNTIF (NAMES, NAMES & ””)))

Uma mudança que fizemos nesta fórmula é a parte dos critérios da função CONT.SE. Usamos NAMES & ”” em vez de NAMES. Ao fazer isso, a fórmula retornaria a contagem de células em branco (anteriormente ela retornava 0 onde havia uma célula em branco).

NOTA: Esta fórmula contaria células em branco como um valor exclusivo e o retornaria no resultado.

No exemplo acima, o resultado deve ser 5, mas retorna 6, pois a célula em branco é contada como um dos valores exclusivos.

Aqui está a fórmula que cuida das células em branco e não conta no resultado final:

= SUMPRODUCT ((NAMES ””) / COUNTIF (NAMES, NAMES & ””))

Nesta fórmula, em vez de 1 como numerador, usamos NOMES ””. Isso retorna uma matriz de TRUEs e FALSEs. Ele retorna FALSE sempre que houver uma célula em branco. Como TRUE é igual a 1 e FALSE é igual a 0 nos cálculos, as células em branco não são contadas, pois o numerador é 0 (FALSE).

Agora que temos o esqueleto básico da fórmula pronto, podemos dar um passo adiante e contar diferentes tipos de dados.

Como contar valores únicos no Excel que são texto

Usaremos o mesmo conceito discutido acima para criar a fórmula que contará apenas valores de texto únicos.

Esta é a fórmula que contará os valores de texto exclusivos no Excel:

= SUMPRODUCT ((ISTEXT (NAMES) / COUNTIF (NAMES, NAMES & ””)))

Tudo o que fizemos foi usar a fórmula ISTEXT (NAMES) como numerador. Ele retorna TRUE quando a célula contém texto e FALSE se não contém. Não contará células em branco, mas contará células que possuem uma string vazia (“”).

Como contar valores únicos no Excel que são numéricos

Aqui está a fórmula que contará os valores numéricos únicos no Excel

= SUMPRODUCT ((ISNUMBER (NAMES)) / COUNTIF (NAMES, NAMES & ””))

Aqui, estamos usando ISNUMBER (NAMES) como numerador. Ele retorna TRUE quando a célula contém tipo de dados numéricos e FALSE se não contém. Não conta células em branco.