Fórmula genérica
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Resumo
Para contar valores de texto exclusivos em um intervalo, você pode usar uma fórmula que usa várias funções: FREQUENCY, MATCH, ROW e SUMPRODUCT. No exemplo mostrado, a fórmula em F5 é:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
que retorna 4, pois há 4 nomes exclusivos em B5: B14.
Nota: Outra forma de contar valores únicos é usar a função CONT.SE. Esta é uma fórmula muito mais simples, mas pode ser executada lentamente em grandes conjuntos de dados. Com o Excel 365, você pode usar uma fórmula mais simples e rápida com base no UNIQUE.
Explicação
Esta fórmula é mais complicada do que uma fórmula semelhante que usa FREQÜÊNCIA para contar valores numéricos exclusivos porque FREQÜÊNCIA não funciona com valores não numéricos. Como resultado, uma grande parte da fórmula simplesmente transforma os dados não numéricos em dados numéricos que FREQÜÊNCIA pode manipular.
Trabalhando de dentro para fora, a função MATCH é usada para obter a posição de cada item que aparece nos dados:
MATCH(B5:B14,B5:B14,0)
O resultado de MATCH é uma matriz como esta:
(1;1;1;4;4;6;6;6;9;9)
Como MATCH sempre retorna a posição da primeira correspondência, os valores que aparecem mais de uma vez nos dados retornam a mesma posição. Por exemplo, como "Jim" aparece 3 vezes na lista, ele aparece nesta matriz 3 vezes como o número 1.
Esta matriz é alimentada em FREQUENCY como o argumento data_array . O argumento bins_array é construído a partir desta parte da fórmula:
ROW(B5:B14)-ROW(B5)+1)
que cria uma lista sequencial de números para cada valor nos dados:
(1;2;3;4;5;6;7;8;9;10)
Neste ponto, FREQUENCY é configurada assim:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQÜÊNCIA retorna uma matriz de números que indica uma contagem para cada número na matriz de dados, organizada por bin. Quando um número já foi contado, FREQUENCY retornará zero. Esta é uma característica chave na operação desta fórmula. O resultado de FREQUENCY é uma matriz como esta:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Nota: FREQUENCY sempre retorna uma matriz com um item a mais do que bins_array .
Agora podemos reescrever a fórmula desta forma:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Em seguida, verificamos se há valores maiores que zero (> 0), o que converte os números em VERDADEIRO ou FALSO, então usamos um duplo negativo (-) para converter os valores VERDADEIRO e FALSO em 1s e 0s. Agora temos:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Por fim, SUMPRODUCT simplesmente soma os números e retorna o total, que neste caso é 4.
Tratamento de células em branco
As células vazias no intervalo farão com que a fórmula retorne um erro # N / A. Para lidar com células vazias, você pode usar uma fórmula de matriz mais complicada que usa a função IF para filtrar valores em branco:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Observação: adicionar IF transforma isso em uma fórmula de matriz que requer control-shift-enter.
Para obter mais informações, consulte esta página.
Outras maneiras de contar valores únicos
Se você tiver o Excel 365, poderá usar a função UNIQUE para contar valores exclusivos com uma fórmula muito mais simples.
Uma tabela dinâmica também é uma excelente maneira de contar valores exclusivos.
