En este tutorial, aprenderá a contar valores únicos en Excel utilizando fórmulas (funciones COUNTIF y SUMPRODUCT).
Glosario
Cómo contar valores únicos en Excel
Digamos que tenemos un conjunto de datos como el que se muestra a continuación:
Para el propósito de este tutorial, nombraré el rango A2:A10 como NOMBRES. En adelante usaremos este rango de nombres en las fórmulas.
Ver también: Cómo crear Named Ranges en Excel.
En este conjunto de datos, hay una repetición en el rango de NOMBRES. Para obtener el recuento de nombres únicos de este conjunto de datos (A2:A10), podemos usar una combinación de funciones de CONTEO y SUBPRODUCTO como se muestra a continuación:
=SUMPRODUCTO(1/COUNTIF(NOMBRES,NOMBRES))
¿Cómo funciona esta fórmula?
Desglosemos esta fórmula para entenderla mejor:
- COUNTIF(NOMBRES, NOMBRES)
- Esta parte de la fórmula devuelve una matriz. En el ejemplo anterior, sería {2;2;3;1;3;1;2;3;2}. Los números aquí indican cuántas veces ocurre un valor en el rango de celdas dado. Por ejemplo, el nombre es Bob, que aparece dos veces en la lista, por lo que devolvería el número 2 para Bob. De manera similar, Steve aparece tres veces y por lo tanto el 3 es devuelto por Steve.
- 1/COUNTIF(NOMBRES, NOMBRES)
- Esta parte de la fórmula devolvería una matriz – {0.5;0.5;0.3333333333333;1;0.333333333333333;1;0.5;0.333333333333333;0.5} Como hemos dividido 1 por la matriz, devuelve esta matriz. Por ejemplo, el primer elemento de la matriz devuelto arriba fue 2. Cuando 1 se divide por 2, devuelve .5.
- SUMPRODUCT(1/COUNTIF(NOMBRES,NOMBRES))
- SUMPRODUCTO simplemente suma todos estos números. Note que si Bob aparece dos veces en la lista, la matriz anterior devuelve .5 donde sea que el nombre de Bob haya aparecido en la lista. De manera similar, como Steve aparece tres veces en la lista, la matriz devuelve .3333333 siempre que el nombre de Steve aparezca. Cuando añadimos los números de cada nombre, siempre devolverá 1. Y si añadimos todos los números, devolvería el total de nombres únicos en la lista.
Esta fórmula funciona bien hasta que no haya ninguna celda en blanco en el rango. Pero si tienes alguna celda en blanco, devolvería un error #DIV/0!
¿Cómo manejar las células en blanco?
Primero entendamos por qué devuelve un error cuando hay una celda en blanco en el rango. Supongamos que tenemos el conjunto de datos como se muestra a continuación (con la celda A3 en blanco):
Ahora, si usamos la misma fórmula que usamos arriba, la parte COUNTIF de la fórmula devuelve una matriz {2;0;3;1;3;1;2;3;1}. Como no hay texto en la celda A3, su conteo es devuelto como 0.
Y como estamos dividiendo 1 por todo el conjunto, devuelve un error #DIV/0!
Para manejar este error de división en caso de celdas en blanco, use la siguiente fórmula:
=SUMPRODUCTO((1/COUNTIF(NOMBRES,NOMBRES&»»))
Un cambio que hemos hecho a esta fórmula es la parte del criterio de la función COUNTIF. Hemos usado NOMBRES&»» en lugar de NOMBRES. Haciendo esto, la fórmula devolvería el recuento de celdas en blanco (antes devolvía 0 cuando había una celda en blanco).
NOTA: Esta fórmula contaría las celdas en blanco como un valor único y lo devolvería en el resultado.
En el ejemplo anterior, el resultado debería ser 5, pero devuelve 6 ya que la celda en blanco se cuenta como uno de los valores únicos.
Aquí está la fórmula que se encarga de las celdas en blanco y no la cuenta en el resultado final:
=SUMPRODUCTO((NOMBRES<»»)/COUNTIF(NOMBRES,NOMBRES&»»))
En esta fórmula, en lugar de 1 como numerador, hemos usado NOMBRES<»». Esto devuelve un conjunto de VERDADES y FALSAS. Devuelve FALSO siempre que hay una celda en blanco. Dado que VERDADERO equivale a 1 y FALSO equivale a 0 en los cálculos, las celdas en blanco no se cuentan ya que el numerador es 0 (FALSO).
Ahora que tenemos listo el esqueleto básico de la fórmula, podemos ir un paso más allá y contar diferentes tipos de datos.
Cómo contar valores únicos en Excel que son texto
Utilizaremos el mismo concepto discutido anteriormente para crear la fórmula que sólo contará los valores de texto que sean únicos.
Aquí está la fórmula que contará los valores de texto únicos en Excel:
=SUMPRODUCTO((ISTEXT(NOMBRES)/COUNTIF(NOMBRES,NOMBRES&»»))
Todo lo que hemos hecho es usar la fórmula ISTEXT(NOMBRES) como numerador. Devuelve VERDADERO cuando la celda contiene texto, y FALSO si no lo contiene. No contará las celdas en blanco, sino que contará las celdas que tengan una cadena vacía («»).
Cómo contar los valores únicos en Excel que son numéricos
Aquí está la fórmula que contará los valores numéricos únicos en Excel
=SUMPRODUCTO((NOMBRE(NOMBRES))/CONTIF(NOMBRES,NOMBRES&»»))
Aquí, estamos usando ISNUMBER(NAMES) como el numerador. Devuelve VERDADERO cuando la celda contiene n