Fórmula genérica
=SUMPRODUCT(1/COUNTIF(data,data))
Resumo
Para contar o número de valores exclusivos em um intervalo de células, você pode usar uma fórmula baseada nas funções CONT.SE e SUMPRODUCT. No exemplo mostrado, a fórmula em F6 é:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Explicação
Trabalhando de dentro para fora, CONT.SE é configurado para valores no intervalo B5: B14, usando todos esses mesmos valores como critérios:
COUNTIF(B5:B14,B5:B14)
Como fornecemos 10 valores para os critérios, obtemos uma matriz com 10 resultados como este:
(3;3;3;2;2;3;3;3;2;2)
Cada número representa uma contagem - "Jim" aparece 3 vezes, "Sue" aparece 2 vezes e assim por diante.
Esta matriz é configurada como um divisor com 1 como numerador. Após a divisão, obtemos outro array:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Quaisquer valores que ocorram apenas uma vez no intervalo aparecerão como 1s, mas os valores que ocorrem várias vezes aparecerão como valores fracionários que correspondem ao múltiplo. (ou seja, um valor que aparece 4 vezes nos dados gerará 4 valores = 0,25).
Por fim, a função SUMPRODUCT soma todos os valores do array e retorna o resultado.
Tratamento de células em branco
Uma maneira de lidar com células em branco ou vazias é ajustar a fórmula da seguinte maneira:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Ao concatenar uma string vazia ("") aos dados, evitamos que zeros acabem na matriz criada por CONT.SE quando há células em branco nos dados. Isso é importante porque um zero no divisor fará com que a fórmula lance um erro # DIV / 0. Funciona porque o uso de uma string vazia ("") para os critérios contará as células vazias.
No entanto, embora esta versão da fórmula não gere um erro # DIV / 0 com células em branco, ela incluirá células em branco na contagem. Se você deseja excluir células em branco da contagem, use:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Isso tem o efeito de cancelar a contagem de células em branco, tornando o numerador zero para as contagens associadas.
Desempenho lento?
Esta é uma fórmula legal e elegante, mas calcula muito mais lentamente do que as fórmulas que usam FREQÜÊNCIA para contar valores únicos. Para conjuntos de dados maiores, você pode mudar para uma fórmula baseada na função FREQUENCY. Esta é uma fórmula para valores numéricos e outra para valores de texto.
