¿Te has encontrado alguna vez ante una lista interminable de datos en Excel y te has preguntado cuántos valores únicos realmente hay? Perfecto, porque hoy te traemos una guía práctica y efectiva para contar esos valores únicos y distintos de manera sencilla. Ya sea a través de fórmulas ingeniosas o haciendo uso de tablas dinámicas, aprenderás a desglosar tus datos y obtener información valiosa al instante. Acompáñanos en este recorrido que convertirá a Excel en tu mejor aliado para el análisis de datos. ¡Comencemos!
¿Quieres aprender a destacar los valores únicos y distintos dentro de tus datos en Excel? ¡Estás en el lugar correcto! En este artículo, te mostraremos cómo utilizar fórmulas o tablas dinámicas para contar valores sin repetición. Ya sea que estés trabajando en un proyecto personal o profesional, dominar esta técnica te brindará una visión clara y precisa de tus datos. ¡No pierdas más tiempo buscando soluciones complicadas! Sigue leyendo y descubre cómo puedes ahorrar tiempo y mejorar la eficiencia en tu manejo de datos en Excel.
En este tutorial, aprenderá cómo contar valores únicos en Excel con fórmulas y cómo obtener un recuento automático de valores distintos en una tabla dinámica. También analizaremos una serie de ejemplos de fórmulas para contar nombres únicos, textos, números, valores únicos que distinguen entre mayúsculas y minúsculas y más.
Cuando trabaja con un gran conjunto de datos en Excel, es posible que a menudo necesite saber cuántos duplicar y único Los valores están ahí. Y a veces, es posible que desee contar sólo el distinto (valores diferentes.
Si ha visitado este blog con regularidad, ya conoce la fórmula de Excel para contar duplicados. Y hoy vamos a explorar diferentes formas de contar valores únicos en Excel. Pero para mayor claridad, definamos primero los términos.
- Único valores: estos son los valores que aparecen en la lista solo una vez.
- Distinto valores: todos estos son valores diferentes en la lista, es decir, valores únicos más 1calle apariciones de valores duplicados.
La siguiente captura de pantalla demuestra la diferencia:
>Y ahora, veamos cómo puedes contar valores únicos y distintos en Excel usando fórmulas y funciones de tabla dinámica.
Cómo contar valores únicos en Excel
Esta es una tarea común que todos los usuarios de Excel deben realizar de vez en cuando. Tiene una lista de datos y necesita averiguar la cantidad de valores únicos en esa lista. ¿Cómo haces eso? Más fácil de lo que piensas 🙂 A continuación encontrarás algunas fórmulas para contar valores únicos de diferentes tipos.
Contar valores únicos en una columna
Supongamos que tiene una columna de nombres en su hoja de cálculo de Excel y necesita contar nombres únicos en esa columna. La solución es utilizar la función SUMA en combinación con SI y CONTAR.SI:
=SUMA(SI(CONTAR.SI(rango, rango)=1,1,0))
Nota. Esta es una fórmula matricial, así que asegúrese de presionar Ctrl + Mayús + Intro para completarlo. Una vez que haga esto, Excel encerrará automáticamente la fórmula entre {llaves} como en la captura de pantalla a continuación. En ningún caso debes escribir las llaves manualmente, eso no funcionará.
En este ejemplo, contamos nombres únicos en el rango A2:A10, por lo que nuestra fórmula toma la siguiente forma:
=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))
>Más adelante en este tutorial, analizaremos algunas otras fórmulas para contar valores únicos de diferentes tipos. Y debido a que todas esas fórmulas son variaciones de la fórmula básica de valores únicos de Excel, tiene sentido desglosar la fórmula anterior para que pueda comprender completamente cómo funciona y modificarla para sus datos. Si alguien no está interesado en los tecnicismos, puede pasar directamente al siguiente ejemplo de fórmula.
Cómo funciona la fórmula de recuento de valores únicos de Excel
Como puede ver, se utilizan 3 funciones diferentes en nuestra fórmula de valores únicos: SUMA, SI y CONTAR. Mirando desde adentro hacia afuera, esto es lo que hace cada función:
- La función CONTAR.SI cuenta cuántas veces aparece cada valor individual en el rango especificado.
En este ejemplo, COUNTIF(A2:A10,A2:A10) devuelve la matriz {1;2;2;1;2;2;2;1;2}.
- La función SI evalúa cada valor en la matriz devuelta por CONTAR.SI, mantiene todos los unos (valores únicos) y reemplaza todos los demás valores con ceros.
Entonces, la función IF(COUNTIF(A2:A10,A2:A10)=1,1,0) se convierte IF(1;2;2;1;2;2;2;1;2) = 1,1,0, que se convierte en la matriz {1;0;0;1;0;0;0;1;0} donde 1 es un valor único y 0 es un valor duplicado.
- Finalmente, la función SUMA suma los valores en la matriz devuelta por IF y genera el número total de valores únicos, que es exactamente lo que queríamos.
Consejo. Para ver cómo se evalúa una parte específica de su fórmula de valores únicos de Excel, seleccione esa parte en la barra de fórmulas y presione el botón F9 llave.
Cuente valores de texto únicos en Excel
Si su lista de Excel contiene valores numéricos y de texto, y desea contar solo valores de texto únicos, agregue la función ISTEXT a la fórmula matricial discutida anteriormente:
=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
Como sabe, la función ISTEXT de Excel devuelve VERDADERO si un valor evaluado es texto, FALSO en caso contrario. Desde el asterisco
funciona como operador AND en fórmulas de matriz, la función SI devuelve 1 solo si un valor es texto y único, 0 en caso contrario. Y después de que la función SUMA sume todos los unos, obtendrá un recuento de valores de texto únicos en el rango especificado. No olvides presionar Ctrl + Mayús + Intro
>Contando valores de texto únicos en Excel
Como puede ver en la captura de pantalla anterior, la fórmula devuelve el número total de valores de texto únicos, excluyendo celdas en blanco, números, valores lógicos de VERDADERO y FALSO, y errores.
Cuente valores numéricos únicos en Excel
=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
>
Contando valores numéricos únicos en Excel Nota.
Dado que Microsoft Excel almacena fechas y horas como números de serie, también se cuentan.
Cuente valores únicos que distinguen entre mayúsculas y minúsculas en Excel
=IF(SUM((–EXACT($A$2:$A$10,A2)))=1,»Unique»,»Dupe»)
Si su tabla contiene datos que distinguen entre mayúsculas y minúsculas, la forma más sencilla de contar valores únicos sería crear una columna auxiliar con la siguiente fórmula matricial para identificar elementos duplicados y únicos:
=COUNTIF(B2:B10, «unique»)
>Contando valores únicos que distinguen entre mayúsculas y minúsculas en ExcelCuente valores distintos en Excel (único y 1 calle
ocurrencias duplicadas)
Para obtener un recuento de valores distintos en una lista, utilice la siguiente fórmula:=SUMA(1/CONTAR.SI(rango ,rango
)) Recuerde, es una fórmula matricial y, por lo tanto, debe presionar la tecla Ctrl + Mayús + Intro
acceso directo en lugar de la habitual pulsación de tecla Intro.
Alternativamente, puede usar la función SUMAPRODUCTO y completar la fórmula de la forma habitual presionando la tecla Enter:=SUMAPRODUCTO(1/CONTAR.SI(rango ,rango
))
=SUM(1/COUNTIF(A2:A10,A2:A10))
Por ejemplo, para contar los distintos valores en el rango A2:A10, puede optar por:
=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
>Contando valores distintos en Excel
Cómo funciona la fórmula distinta de Excel {2;2;3;1;2;2;3;1;3}Como ya sabes, utilizamos la función CONTAR.SI para saber cuántas veces aparece cada valor individual en el rango especificado. En el ejemplo anterior, el resultado de la función CONTAR.SI es la siguiente matriz:
. 1/COUNTIF(A2:A10,A2:A10)) Después de eso, se realizan una serie de operaciones de división, donde cada valor de la matriz se utiliza como divisor con 1 como dividendo. Esto convierte todos los valores duplicados en números fraccionarios correspondientes al número de apariciones duplicadas. Por ejemplo, si un valor aparece 2 veces en la lista, genera 2 elementos en la matriz con un valor de 0,5 (1/2=0,5). Y si un valor aparece 3 veces, produce 3 elementos en la matriz con un valor de 0,3(3). En nuestro ejemplo, el resultado de {0.5;0.5;0.3(3);1;0.5;0.5;0.3(3);1;0.3(3)}es la matriz
.
¿No tiene mucho sentido hasta ahora? Esto se debe a que aún no hemos aplicado la función SUMA/SUMAPRODUCTO. Cuando una de estas funciones suma los valores de la matriz, la suma de todos los números fraccionarios de cada elemento individual siempre da 1, sin importar cuántas apariciones de ese elemento existan en la lista. Y debido a que todos los valores únicos aparecen en la matriz como 1 (1/1=1), el resultado final devuelto por la fórmula es el número total de todos los valores diferentes en la lista.
Fórmulas para contar valores distintos de diferentes tipos. Como es el caso decontando valores únicos en Excel
puede utilizar variaciones de la fórmula distinta de recuento básica de Excel para manejar tipos de valores específicos, como números, texto y valores que distinguen entre mayúsculas y minúsculas. Recuerde que todas las fórmulas siguientes son fórmulas matriciales y requieren presionarCtrl + Mayús + Intro
.
Cuente valores distintos ignorando celdas vacías
Si una columna donde desea contar valores distintos puede contener celdas en blanco, debe agregar una función SI que verificará si hay espacios en blanco en el rango especificado (la fórmula distinta básica de Excel analizada anteriormente devolvería el error #DIV/0 en este caso):=SUMA(SI(rango<>«»,1/CONTAR.SI(rango ,rango
), 0))
=SUM(IF(A2:A10<>»»,1/COUNTIF(A2:A10, A2:A10), 0))
>La fórmula para contar valores distintos ignorando las celdas vacías
Fórmula para contar valores de texto distintos
Para contar valores de texto distintos en una columna, usaremos el mismo enfoque que acabamos de usar para excluir celdas vacías.
Como puede adivinar fácilmente, simplemente incorporaremos la función ISTEXT en nuestra fórmula distinta de recuento de Excel:=SUM(SI(ESTEXTO(rango),1/CONTAR.SI(rango ,rango
),»»))
=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),»»))
Y aquí hay un ejemplo de fórmula de la vida real:
Fórmula para contar números distintos.
Para contar valores numéricos distintos (números, fechas y horas), utilice la función ESNÚMERO:=SUM(SI(ESNÚMERO(rango),1/CONTAR.SI(rango ,rango
),»»))
=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),»»))
Por ejemplo, para contar todos los números diferentes en el rango A2:A10, use la siguiente fórmula:
Cuente valores distintos que distinguen entre mayúsculas y minúsculas en Excel De manera similar a contar valores únicos que distinguen entre mayúsculas y minúsculas, la forma más sencilla de contar valores distintos que distinguen entre mayúsculas y minúsculas es agregar una columna auxiliar con la fórmula matricial que identifica valores únicos, incluidas las primeras apariciones duplicadas. La fórmula es básicamente la misma que solíamos utilizar.contar valores únicos que distinguen entre mayúsculas y minúsculas
=IF(SUM((–EXACT($A$2:$A2,$A2)))=1,»Distinct»,»»)
con un pequeño cambio en una referencia de celda que marca una gran diferencia: Como recuerdas, todas las fórmulas matriciales en Excel requieren presionarCtrl + Mayús + Intro
.
=COUNTIF(B2:B10, «distinct»)
>Contando valores distintos que distinguen entre mayúsculas y minúsculas en Excel Si no hay forma de agregar una columna auxiliar a su hoja de trabajo, puede usar el siguiente complejo fórmula matricial
=SUM(IFERROR(1/IF($A$2:$A$10<>»», FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), «»), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))
para contar valores distintos que distinguen entre mayúsculas y minúsculas sin crear una columna adicional:
Cuente filas únicas y distintas en Excel
Contar filas únicas/distintas en Excel es similar a contar valores únicos y distintos, con la única diferencia de que usa la función CONTAR.SI en lugar de CONTAR.SI, que le permite especificar varias columnas para verificar valores únicos.
Por ejemplo, para contar nombres únicos o distintos según los valores de las columnas A (nombre) y B (apellido), utilice una de las siguientes fórmulas:
=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))
Fórmula para contar filas únicas:
=SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))
>Contar filas únicas y distintas en Excel
Naturalmente, no está limitado a contar filas únicas basadas solo en dos columnas, la función CONTAR.SI de Excel puede procesar hasta 127 pares de rango/criterio.
Cuente valores distintos en Excel usando una tabla dinámica Las últimas versiones de excel 2013 y excel 2016 tiene una característica especial que permite contar valores distintos automáticamente en una tabla dinámica. La siguiente captura de pantalla da una idea de cómo funciona Excel. Recuento distinto
>Recuento distinto en una tabla dinámica
>
>
.
>Desplácese hacia abajo hasta la última opción y seleccione Recuento distintivo.
¡Hecho! La tabla dinámica recién creada mostrará el recuento distinto como se muestra en la primera captura de pantalla de esta sección. Consejo. Después de actualizar sus datos de origen, recuerde actualizar la tabla dinámica para actualizar el recuento distinto. Para actualizar una tabla dinámica, simplemente haga clic en el Actualizar botón en el Analizar pestaña, en la Datos
grupo. Así es como se cuentan valores distintos y únicos en Excel. Si alguien quiere ver más de cerca las fórmulas analizadas en este tutorial, puede descargar el ejemplo.Libro de trabajo único de recuento de Excel
.
Abbass: ¡Totalmente de acuerdo, chanca! A mí también me pasó lo mismo, pasé horas contando valores únicos manualmente y fue un dolor de cabeza. Desde que aprendí a usar tablas dinámicas, todo se ha vuelto un paseo. Este artículo es justo lo que necesitaba para refrescarme el tema. ¡Gracias por la info!
Joseba aitor: ¡Totalmente, chaval! A mí me pasó algo similar cuando tenía que preparar un informe y la liaba contando valores únicos en una hoja kilométrica. Desde que me enteré de las fórmulas y las tablas dinámicas, todo ha sido un paseo. Este artículo es oro puro, ¡mil gracias por compartirlo!
Me encanta este tema, ¡es súper útil! Recuerdo que me volví loco tratando de contar valores únicos en una hoja enorme de Excel para un proyecto, y después de ver este artículo, usar la tabla dinámica me hizo la vida mucho más fácil. ¡Gracias por compartirlo! – chanca
Andrey: ¡Qué buen artículo, chanca y abbass! A mí me pasó algo similar, una vez gasté un montón de tiempo tratando de sacar un conteo manual en Excel para una presentación y al final casi me vuelvo loco. Desde que descubrí las fórmulas y las tablas dinámicas, todo ha sido más rápido y fácil. ¡Gracias por compartir estos tips, son un salvavidas!
Ordoñez: ¡Buenísimo el artículo, me ha salvado las papas! La última vez que necesité contar valores únicos, me metí en un lío impresionante buscando entre un montón de datos. Desde que uso tus trucos con tablas dinámicas, es pan comido. ¡Gracias por compartir, crack!