En más de una ocasión nos vemos en la necesidad de contar los valores únicos contenidos en una columna de datos y una alternativa para realizar este tipo de operación es utilizar una fórmula matricial para hacer el recuento. En el pasado he escrito sobre dicho método y puedes leer todo el detalle en el artículo titulado Contar valores únicos en Excel.
Aunque podemos resolver esta problemática con una fórmula, a veces necesitamos contar dichos valores únicos utilizando una tabla dinámica, así que en esta ocasión te mostraré un método para realizar esa tarea. Para nuestro primer ejemplo utilizaré los mismos datos del artículo antes mencionado y a partir de ahí elaboraremos algunos casos más complejos. Los datos con los que comenzaré a trabajar son los siguientes:
Nuestro primer objetivo será contar las ciudades únicas contenidas en la columna Ciudad así que comenzaré por crear una tabla dinámica basada en el rango A1:B10 y la colocaré en la misma hoja donde se encuentran los datos.
Problema al contar valores únicos en tablas dinámicas
Una vez creada la tabla dinámica arrastraré el campo Ciudad al área Etiquetas de fila y además al área Valores. De manera predeterminada la tabla dinámica me mostrará el recuento de cada uno de los elementos.
Como puedes observar, la tabla dinámica hace un conteo de todas las veces que aparece cada uno de los elementos dentro de la columna Ciudad y no una cuenta de los valores únicos. Hasta la versión de Excel 2010 no existía una manera de contar valores únicos en una tabla dinámica así que para nuestro ejemplo será necesario crear una columna auxiliar que nos ayude a realizar ese tipo de cuenta. En una sección posterior te mostraré cómo puedes resolver este problema utilizando Excel 2013.
Fórmula para encontrar valores únicos
Sin más preámbulo te mostraré la fórmula que colocaré en la celda C2 y que nos ayudará a contar los valores únicos en la tabla dinámica:
=SI(SUMAPRODUCTO((B$2:B2=B2)*1)>1,0,1)
Una vez que hayas ingresado esta fórmula en la celda C2 podrás copiarla hacia abajo para obtener el siguiente resultado:
El objetivo de la función SUMAPRODUCTO es decirnos cuántas veces aparece un valor a partir de la celda B2 y hasta la celda actual. Por ejemplo, para la celda C4 nos dirá que la palabra “México” aparece una sola vez en el rango B2:B4. La misma función en la celda C10, nos dirá que la palabra “México” aparece dos veces en el rango B2:B10.
El resultado de la función SUMAPRODUCTO es evaluado por la función SI para saber si es mayor a uno. De esta manera, para la celda C4 obtenemos el valor uno porque la palabra “México” aparece una sola vez en el rango B2:B4. Sin embargo para la celda C10 obtenemos el valor cero porque la palabra México aparece más de una vez en el rango B2:B10.
En resumidas cuentas, nuestra fórmula devolverá el valor uno para la primera ocurrencia de cualquier elemento dentro de la columna C y para las apariciones subsecuentes del mismo elemento devolverá el valor cero.
Valores únicos en tabla dinámica
Ahora que tenemos lista nuestra columna auxiliar podemos incluirla en la tabla dinámica y colocarla dentro del área de Valores para obtener el resultado deseado:
De esta manera nuestra columna auxiliar nos ayudará a contar los valores únicos dentro de una tabla dinámica en Excel.
Excel 2013: Contar valores únicos en tabla dinámica
Tal como lo mencioné anteriormente, si estás utilizando Excel 2013 podrás configurar la tabla dinámica para obtener el recuento de los valores únicos de un campo sin necesidad de crear una columna auxiliar. Para que este método funcione adecuadamente debes seguir los siguientes pasos. Al momento de crear la tabla dinámica deberás asegurarte de marcar la opción “Agregar estos datos al Modelo de datos”.
Una vez creada la tabla dinámica deberás agregar el campo Ciudad al área Filas y al área Valores tal como lo hicimos en el ejemplo anterior y como resultado tendrás una tabla dinámica como la siguiente:
En este momento la tabla dinámica se ve exactamente igual que en nuestro ejemplo anterior ya que está realizando el recuento de todas las apariciones de cada elemento. Para poder contar los valores únicos tenemos que cambiar el tipo de cálculo del campo así que, comenzamos haciendo clic sobre el campo Ciudad ubicado en el área de Valores y seleccionamos la opción “Configuración de campo de valor”.
Se mostrará un cuadro de diálogo y dentro de la pestaña “Resumir valores por” deberás seleccionar la opción “Recuento distinto”.
Al pulsar el botón Aceptar la tabla dinámica se actualizará y mostrará el conteo de valores únicos de la columna Ciudad tal como lo observas en la siguiente imagen.
Es importante mencionar que al elegir la opción “Agregar estos datos al Modelo de datos” se creará un tipo especial de objeto conocido como Tabla dinámica OLAP que en estricto sentido es diferente a las tablas dinámicas normales y notarás algunas diferencias entre ambos tipos de objetos. Para el propósito de este artículo, dichas diferencias no tienen impacto alguno y podremos contar los valores únicos en la tabla dinámica sin mayor dificultad.
Beneficios del método con fórmula
Uno de los beneficios de utilizar el método con fórmula para contar valores únicos en una tabla dinámica es que podemos adecuar dicha fórmula para encontrar valores únicos considerando múltiples columnas. Para explicar mejor a lo que me refiero modificaré un poco los datos de nuestro ejemplo de la siguiente manera:
Nuestro objetivo será encontrar el número de combinaciones únicas formadas por las columnas Nombre, Apellido y Ciudad. Haciendo una revisión visual podemos observar que nuestros datos contienen cinco combinaciones únicas entre las tres columnas así que nuestro reto será crear una fórmula que nos ayude a obtener dicho resultado automáticamente.
La fórmula que utilizaremos será muy parecida a la del ejemplo anterior con la diferencia de que incluiremos todas las columnas involucradas en la generación de los valores únicos. La fórmula a utilizar para nuestra columna auxiliar será la siguiente:
=SI(SUMAPRODUCTO((A$2:A2=A2)*(B$2:B2=B2)*(C$2:C2=C2))>1,0,1)
Utilizamos las mismas funciones que en nuestra fórmula original. En el caso de la función SI tenemos los mismos argumentos así que lo único que ha cambiado son los rangos multiplicados dentro de la función SUMAPRODUCTO. Observa que en lugar de multiplicar por el número uno, ahora se multiplican los rangos respectivos para las columnas A, B y C. Al introducir esta fórmula en la celda D2 y copiarla hacia abajo obtenemos el siguiente resultado:
Al agregar dicha columna auxiliar a nuestra tabla dinámica obtendremos los valores únicos, que para este ejemplo he organizado por Ciudad:
De esta manera la fórmula puede ayudarnos a obtener los valores únicos utilizando cualquier cantidad de columnas. La receta es muy sencilla: si necesitamos obtener los valores únicos basados en una sola columna, entonces deberás utilizar la fórmula original que hace la multiplicación del rango por uno. Pero si los valores únicos están basados en dos o más columnas, en lugar de multiplicar por uno, deberás incluir los rangos de cada una de las columnas involucradas y multiplicarlos entre sí.
Finalmente te dejo el vínculo de descarga para el libro de trabajo que he utilizado en este artículo de manera que puedas probar por tu cuenta los dos ejemplos realizados.