Fórmula genérica
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Resumen
Para contar valores de texto únicos en un rango, puede usar una fórmula que use varias funciones: FRECUENCIA, COINCIDIR, FILA y SUMPRODUCTO En el ejemplo que se muestra, la fórmula en F5 es:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
que devuelve 4, ya que hay 4 nombres únicos en B5: B14.
Nota: Otra forma de contar valores únicos es usar la función CONTAR.SI. Esta es una fórmula mucho más simple, pero puede ejecutarse lentamente en grandes conjuntos de datos. Con Excel 365, puede usar una fórmula más simple y rápida basada en UNIQUE.
Explicación
Esta fórmula es más complicada que una fórmula similar que usa FRECUENCIA para contar valores numéricos únicos porque FRECUENCIA no funciona con valores no numéricos. Como resultado, una gran parte de la fórmula simplemente transforma los datos no numéricos en datos numéricos que FREQUENCY puede manejar.
Trabajando de adentro hacia afuera, la función COINCIDIR se usa para obtener la posición de cada elemento que aparece en los datos:
MATCH(B5:B14,B5:B14,0)
El resultado de MATCH es una matriz como esta:
(1;1;1;4;4;6;6;6;9;9)
Como COINCIDIR siempre devuelve la posición de la primera coincidencia, los valores que aparecen más de una vez en los datos devuelven la misma posición. Por ejemplo, debido a que "Jim" aparece 3 veces en la lista, aparece en esta matriz 3 veces como el número 1.
Esta matriz se alimenta a FREQUENCY como argumento data_array . El argumento bins_array se construye a partir de esta parte de la fórmula:
ROW(B5:B14)-ROW(B5)+1)
que crea una lista secuencial de números para cada valor en los datos:
(1;2;3;4;5;6;7;8;9;10)
En este punto, FREQUENCY se configura así:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY devuelve una matriz de números que indican un recuento de cada número en la matriz de datos, organizado por bin. Cuando ya se ha contado un número, FREQUENCY devolverá cero. Esta es una característica clave en el funcionamiento de esta fórmula. El resultado de FREQUENCY es una matriz como esta:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Nota: FREQUENCY siempre devuelve una matriz con un elemento más que bins_array .
Ahora podemos reescribir la fórmula así:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
A continuación, verificamos valores mayores que cero (> 0), lo que convierte los números en VERDADERO o FALSO, luego usamos un doble negativo (-) para convertir los valores VERDADERO y FALSO en 1 y 0. Ahora tenemos:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Finalmente, SUMPRODUCT simplemente suma los números y devuelve el total, que en este caso es 4.
Manejo de celdas en blanco
Las celdas vacías en el rango harán que la fórmula devuelva un error # N / A. Para manejar celdas vacías, puede usar una fórmula de matriz más complicada que usa la función SI para filtrar valores en blanco:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Nota: agregar IF convierte esto en una fórmula de matriz que requiere control-shift-enter.
Para obtener más información, consulte esta página.
Otras formas de contar valores únicos
Si tiene Excel 365, puede usar la función ÚNICA para contar valores únicos con una fórmula mucho más simple.
Una tabla dinámica también es una forma excelente de contar valores únicos.
