Cómo contar elementos únicos (count distinct) en una tabla dinámica de Excel. Tres métodos, para diferentes versiones de Excel. Obtenga el libro de trabajo gratuito para seguirlo
Gracias a Roger Govier, que creó este tutorial y el libro de trabajo.
Cuento de elementos únicos
Si está utilizando una tabla dinámica para resumir sus datos, puede tener preguntas como:
- ¿Cuántos clientes únicos (distintos) hicieron compras en cada región?
- ¿Cuántos productos únicos se vendieron en cada tienda?
- ¿Cuántos vendedores únicos vendieron cada producto por región?
- En Excel 2013 y posteriores, cree un Modelo de Datos
- En Excel 2010, y versiones posteriores, utilice una técnica que «Pivote la tabla pivotante».
- O, en versiones anteriores, añada una nueva columna a los datos de origen, y Utilice CountIf.
- Si el complemento Power Pivot está instalado, utilícelo para mostrar un recuento distinto
- Método 1: En Excel 2013 y posteriores, añada los datos de origen de la tabla dinámica al Modelo de Datos, y se puede hacer un recuento único fácilmente.
- Método 2: En Excel 2010 y posteriores, utilice la técnica «pivotar un pivote».
- Método 3: En versiones anteriores de Excel, añada una columna a los datos de origen para colocar un 1 en las celdas de una fila donde el valor es único, y un 0 en cualquier otra celda. A continuación, la suma de esta columna adicional proporciona el valor único.
- Método 4: Utilice PowerPivot para crear la tabla dinámica y utilice sus funciones para crear un recuento único. Vea los detalles a continuación.
- Seleccione una celda en la tabla de datos de origen.
- En la parte inferior del cuadro de diálogo Crear tabla dinámica, añada una marca de verificación a «Añadir estos datos al modelo de datos»
- Haga clic en Aceptar
- En la tabla pivotante, añada Región al área Fila.
- Añada estos 3 campos al área Valores — Persona, Unidades, Valor
- El campo Persona contiene texto, por lo que tiene como valor predeterminado Recuento de Persona. El recuento muestra el número total de transacciones en cada región, no un recuento único de personas
- Haga clic con el botón derecho en uno de los valores del campo Persona
- Haga clic en Configuración del campo de valor
- En la lista Resumir campo de valor por, desplácese hasta la parte inferior, y haga clic en Recuento Distinto, luego haga clic en Aceptar
- Crear una Tabla Pivotante a partir de estos datos, con Región y Persona en el área Filas
- Añadir Unidades y Valor en el área Valores. Dado que Persona es un campo de texto, la tabla dinámica lo mostrará automáticamente como «Count of».
- Configure la tabla dinámica con el diseño de informe Tabular
- Configure todas las etiquetas de Elementos para que se repitan en cada fila.
- Cambie los Títulos, para eliminar la «Suma de»
TIP: escriba el nombre del campo original, con un carácter de espacio al final, por ejemplo, «unidades» - Seleccione la celda B2, que es el encabezamiento de la columna Región de la tabla pivotante
- Haga clic en la barra de fórmulas y escriba un nombre para la celda — pvtInicio
- Pulse Intro, para completar el nombre
- En la hoja Datos, en la celda M2, introduce esta fórmula, para calcular la última fila de la columna Unidades de la tabla dinámica:
=MATCH(9.9E+307,Method_1!D:D,1)
- Para nombrar esa celda, seleccione la celda M2, y en el Cuadro de Nombre, escriba PvtEnd, y pulse Enter.
- En la pestaña Fórmulas de la cinta de opciones, haga clic en Definir nombre
- En el cuadro de diálogo Nuevo nombre, introduzca el nombre, misDatos
- En el desplegable Alcance, seleccione Libro de trabajo
- En el cuadro Se refiere a, escriba esta fórmula:
=pvtInicio:ÍNDICE(¡Método_1!$E:$E, PvtEnd) - Haga clic en Aceptar, para crear el rango con nombre
No hay una función incorporada de «Recuento único» en una tabla dinámica normal, pero en este tutorial, verá cómo obtener un recuento único de artículos en una tabla dinámica.
Este vídeo muestra los pasos para mostrar un recuento distinto con el Modelo de Datos.
Introducción
A menudo tenemos grandes conjuntos de datos que queremos analizar con la ayuda de una Tabla dinámica, pero sólo queremos contar las ocurrencias únicas de algunos elementos de datos. Desgraciadamente, las tablas dinámicas nunca han tenido la facilidad incorporada de contar valores únicos.
En este ejemplo, el archivo de muestra tiene 4999 registros que muestran las ventas de productos, con la región y el nombre del vendedor. Los primeros registros se muestran en la siguiente captura de pantalla. Puede descargar el archivo de ejemplo en el siguiente enlace.
Crear una tabla dinámica a partir de los datos nos daría algo como lo siguiente, si añadimos Persona al área de Valores de la tabla dinámica y lo resumimos con Recuento.
Sin embargo, esto nos está dando un recuento de todas las transacciones, no el recuento del número único de personas que hicieron esas transacciones dentro de cada Región.
Obtener un recuento único
Hay soluciones que puede utilizar, para obtener un recuento único:
A continuación se describen los cuatro métodos, junto con una comparación de rendimiento entre los métodos 2 y 3.
1. Añadir al modelo de datos – Excel 2013 y posteriores
En Excel 2013, si añade los datos de origen de una tabla dinámica al modelo de datos del libro, es fácil crear un recuento único.
NOTA: esta técnica crea una tabla dinámica basada en OLAP, que tiene algunas limitaciones, como la ausencia de agrupación y de campos calculados o elementos calculados. Si necesita estas características restringidas, pruebe el método «pivotar un pivote» en su lugar.
Crear un recuento único (Distinct Count)
Para crear la tabla pivotante, siga estos pasos:
Para configurar el diseño de la tabla dinámica, siga estos pasos:
Para obtener un recuento único de personas en cada región, siga estos pasos:
El campo Persona cambia, y en lugar de mostrar el recuento total de transacciones, muestra un recuento distinto de los nombres de los vendedores.
2. Pivotar la Pivot – Excel 2010
Para obtener un recuento único de vendedores por región en Excel 2010, podemos producir un Informe Pivotante, y luego «Pivotar la Pivot» para obtener nuestro resultado final.
Crear la Primera Tabla Pivotante
Ahora parece una tabla de datos normal, pero es un resumen de la tabla de datos completa original con 4.999 filas.
Crear un rango con nombre para la celda inicial
A continuación, nombrará la primera celda de la tabla dinámica.
Cree un rango con nombre para la fila final
El siguiente paso es calcular la ubicación de la última fila de la tabla pivotante. Utilizaremos la función MATCH, con un número muy grande, para encontrar el último número de la columna D, y obtener su número de fila. Esta fórmula sólo funcionará en una columna con números.
Crear un Rango Nombrado Dinámico
A continuación, crearemos un Rango Nombrado dinámico, para hacer referencia a esta nueva tabla. Este rango comenzará en la celda llamada pvtstart (la celda de encabezamiento de la Región), y terminará en la última fila de datos de la columna E (el rango nombrado, PvtEnd).
Crear la segunda tabla dinámica
Por último, cree una segunda tabla dinámica, basada en el rango dinámico, misDatos.
Ponga Región en el área de Filas, y Persona, Unidades y Valor en el área de Valores
Esto muestra el número de vendedores únicos por regiones como se muestra a continuación. Por ejemplo, en la primera tabla dinámica, la región Este mostró 8 nombres únicos en la columna de persona, y ese es el recuento en esta tabla dinámica.
Refrescar las Tablas Dinámicas
Naturalmente, como hay 2 Tablas Dinámicas involucradas en esta solución, ambas tienen que ser refrescadas después de que cualquier dato haya sido añadido o cambiado en la tabla fuente. Es esencial que la primera PT se actualice primero, seguida de una actualización de la tabla dinámica final.
Puede refrescar las tablas dinámicas manualmente, o utilizar amacro. Para utilizar una macro, añada uno de los siguientes procedimientos a la hoja con su Tabla dinámica final.
A) Si ambas tablas dinámicas están en la misma hoja, utilice este código:
Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh Me.PivotTables(2).PivotCache.Refresh End Sub
B) Si las tablas dinámicas están en hojas diferentes, utilice este código (sustituya el nombre de su hoja actual por «yoursheetname»):
Private Sub Worksheet_Activate() Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh Me.PivotTables(1).PivotCache.Refresh End Sub
(Opcional) Nombrar el rango de datos de la tabla dinámica
Si está utilizando macros para actualizar las tablas dinámicas, también podría utilizar una macro para restablecer el rango nombrado myData después de cada actualización. Añada el siguiente procedimiento a su libro de trabajo y, a continuación, llame a este procedimiento SetmyData en los procedimientos Worksheet_Activate.
Sub SetmyData()'Change "PT_A" to whatever name'you have given to your first Pivot Table'that is to be used as the source'for for the final Pivot Table'Also change the sheet name'from "Method_1" to'your sheet name if it is differentDim PTName As StringDim ShName As StringPTName = "PT_A"ShName = "Method_1"ThisWorkbook.Names.Add _ Name:="myData", _ RefersTo:=Sheets(ShName) _ .PivotTables(PTName) _ .TableRange1End Sub
3. Utilice COUNTIF — Excel 2007 y anteriores
En versiones anteriores de Excel, las tablas dinámicas no tienen la opción de repetir las etiquetas de las filas. Para obtener un recuento único de vendedores por región, puede añadir una nueva columna en los datos de origen, e introducir una fórmula COUNTIF
Puede descargar el archivo de ejemplo en el siguiente enlace.
Agregar la fórmula COUNTIF
Siga estos pasos, para agregar la fórmula:
En los datos de origen, agregue un nuevo encabezado de columna — «Único» — en la celda J1
En la celda J2, introduzca esta fórmula, y cópiela hasta la última fila de datos:
=IF(COUNTIF($E$5:E5,E5)>1,0,1)
La primera referencia del rango ($E$5) es Absoluta, y la segunda es relativa (E5), por lo que a medida que copiamos la fórmula hacia abajo en la página, así el rango se expandirá de $E$5:E5 a $E$5:E6, luego $E$5:E7 y así sucesivamente.
En cada fila estamos comprobando cuántas veces se ha producido el valor de la columna E (Persona), hasta la fila actual. Si es mayor de una vez, el resultado es cero, por lo que esa persona no se vuelve a contar. Esto nos permite ver el número de veces únicas dentro del conjunto de datos que ha aparecido el nombre de cada Persona.
Con la columna extra añadida, los datos tendrían este aspecto. La segunda instancia de «Harry» devuelve un cero en la columna Único.
NOTA: Si hay personas con el mismo nombre vendiendo en diferentes Regiones:
- En Excel 2007 o posterior: Utilice COUNTIFS, para comprobar múltiples criterios. Por ejemplo, =IF(COUNTIFS($D$5:$D5,D5,$E$5:E5,E5)=1,1,0)
- En Excel 2003 o anterior, concatene los nombres de la Región y de la Persona en una nueva columna y, a continuación, utilice la fórmula COUNTIF para encontrar valores Únicos en esa columna concatenada.
Crear una tabla dinámica
Para ver los recuentos únicos en una tabla dinámica:
- Cree una tabla dinámica a partir de estos datos, con Región en el área Filas
- Añada Único, Unidades y Valor en el área Valores.
- Cambie el encabezado «Único» por «Recuento de persona» o «Persona»
- Modelo de datos: Descargue el libro de trabajo de ejemplo del Modelo de Datos, que tiene el ejemplo del Método 1 (Modelo de Datos). El archivo está comprimido, y en formato xlsx. El archivo no contiene macros.
- Sin Modelo de Datos: Descargue el libro de trabajo de ejemplo sin Modelo de datos, que contiene ejemplos para el Método 2 (pivotar un pivote) y el Método 3 (columna en datos de origen). El archivo está comprimido y en formato xlsb. El archivo no contiene macros.
- Video de Power Pivot: Para seguir el vídeo de Power Pivot en el método 4, descargue el archivo de ejemplo que se utilizó para el vídeo
Podemos ver que hay 30 personas que hicieron las Ventas, y el número de cada una que están en cada Región, y si elegimos expandir cualquier Región entonces veríamos los totales individuales de cada Persona.
Comparación del rendimiento
Si es posible, utilice el método «pivote a pivote», que es mucho más rápido.
Método COUNTIF
Utilizar una fórmula COUNTIF en los datos de origen funciona, y nos da nuestro resultado deseado, y en un conjunto de datos relativamente pequeño el método es aceptable. Pero, es muy costoso en términos de tiempo de procesamiento, y en conjuntos de datos muy grandes puede ser extremadamente lento.
Usando «FastExcel» de Charles Williams para calcular el tiempo que tarda el libro de trabajo en calcular da el resultado que se muestra a continuación, donde el tiempo que se tarda en recalcular la hoja de Datos es de 224 milisegundos.
Método Pivot a Pivot
El método «Pivot a Pivot» también produjo el resultado correcto, con un recuento único de Persona, como se requiere. Además, no depende de columnas adicionales en los datos de origen, ni de la introducción de ninguna fórmula nueva.
El libro de trabajo resultante es más pequeño, y de nuevo utilizando «FastExcel» de Charles Williams para calcular el tiempo de cálculo de la hoja de datos es un increíblemente rápido 1.2 milisegundos
Con conjuntos de datos pequeños el tiempo de recálculo puede no ser crítico, pero cuando se trata de conjuntos de datos más grandes de 300.000 a 400.000 filas, entonces estas diferencias se convertirían en muy materiales.
Power Pivot
Si tiene instalado el complemento PowerPivot, puede utilizarlo para mostrar un recuento distinto para un campo. Este vídeo muestra los pasos para crear una tabla dinámica de Power Pivot, y añadir un campo con el recuento único.
Para las instrucciones escritas, vea los pasos de Excel 2013 en mi blog de tablas dinámicas. También hay instrucciones para Excel 2010.
Archivo de ejemplo: Para seguir el vídeo, descargue el archivo de ejemplo que se utilizó para este vídeo
Obtenga los archivos de ejemplo
Tutoriales relacionados
Nombrar rangos
Cuenta única en PowerPivot
Archivos de ejemplo – Roger Govier
Acerca del desarrollador
Roger Govier es un MVP de Excel con sede en el Reino Unido que realiza encargos en Excel y VBA para clientes de todo el mundo. Aunque disfruta del reto intelectual que supone resolver problemas con funciones de hoja de cálculo, Roger afirma ser intrínsecamente perezoso, por lo que siempre busca una forma rápida y sencilla de ofrecer soluciones sólidas y viables.
Encuentra más tutoriales y archivos de ejemplo de Roger aquí: Archivos de ejemplo – Roger Govier
Puedes contactar con Roger en: [email protected]
Roger Govier
