Las tablas dinámicas de Excel son increíbles (sé que lo menciono cada vez que escribo sobre tablas dinámicas, pero es cierto).
Con un conocimiento básico y un poco de arrastrar y soltar, puede realizar una gran cantidad de trabajo en unos pocos segundos.
Si bien se puede hacer mucho con unos pocos clics en las tablas dinámicas, hay algunas cosas que necesitarían algunos pasos adicionales o un poco de trabajo.
Y una de esas cosas es contar valores distintos en una tabla dinámica.
En este tutorial, le mostraré cómo contar valores distintos, así como valores únicos en una tabla dinámica de Excel.
Pero antes de pasar a cómo contar valores distintos, es importante comprender la diferencia entre "recuento distinto" y "recuento único".
Recuento distinto frente al recuento único
Si bien estos pueden parecer lo mismo, no es.
A continuación se muestra un ejemplo en el que hay un conjunto de datos de nombres y he enumerado nombres únicos y distintos por separado.
Valores / nombres únicos son aquellos que solo ocurren una vez. Esto significa que todos los nombres que se repiten y tienen duplicados no son únicos. Los nombres únicos se enumeran en la columna C en el conjunto de datos anterior
Valores / nombres distintos son aquellos que ocurren al menos una vez en el conjunto de datos. Entonces, si un nombre aparece tres veces, aún se cuenta como un nombre distinto. Esto se puede lograr eliminando los valores / nombres duplicados y manteniendo todos los distintos. Los nombres distintos se enumeran en la columna B del conjunto de datos anterior.
Según lo que he visto, la mayoría de las veces cuando las personas dicen que quieren obtener el recuento único en una tabla dinámica, en realidad se refieren a un recuento distinto, que es lo que estoy cubriendo en este tutorial.Cuente valores distintos en la tabla dinámica de Excel
Suponga que tiene los datos de ventas como se muestra a continuación:
Haga clic aquí para descargar el archivo de ejemplo y siga
Con el conjunto de datos anterior, supongamos que desea encontrar la respuesta a las siguientes preguntas:
- ¿Cuántos representantes de ventas hay en cada región (que no es más que el recuento distinto de representantes de ventas en cada región)?
- ¿Cuántos representantes de ventas vendieron la impresora en2021-2022?
Si bien las tablas dinámicas pueden resumir instantáneamente los datos con unos pocos clics, para obtener el recuento de valores distintos, deberá realizar algunos pasos más.
Si está usando Excel 2013 o versiones posteriores, hay una funcionalidad incorporada en la tabla dinámica que le brinda rápidamente el recuento distintivo. Y si estás usando Excel 2010 o versiones anteriores a esa, tendrá que modificar los datos de origen agregando una columna auxiliar.En este tutorial se tratan los dos métodos siguientes:
- Agregar una columna auxiliar en el conjunto de datos original para contar valores únicos (funciona en todas las versiones).
- Agregar los datos a un modelo de datos y usar la opción Distinct Count (disponible en Excel 2013 y versiones posteriores).
Hay un tercer método que Roger muestra en este artículo (que él llama el método Pivot the Pivot Table).
¡Empecemos!
Agregar una columna auxiliar en el conjunto de datos
Nota: Si está utilizando Excel 2013 y versiones superiores, omita este método y pase al siguiente (ya que utiliza una función de tabla dinámica incorporada: Recuento distinto).Esta es una manera fácil de contar valores distintos en la tabla dinámica, ya que solo necesita agregar una columna auxiliar a los datos de origen. Una vez que haya agregado una columna auxiliar, puede usar este nuevo conjunto de datos para calcular el recuento distinto.
Si bien esta es una solución fácil, este método tiene algunos inconvenientes (que se tratan más adelante en este tutorial).
Primero, déjame mostrarte cómo agregar una columna de ayuda y obtener un recuento distinto.
Supongamos que tengo el conjunto de datos como se muestra a continuación:
Agregue la siguiente fórmula en la Columna F y aplíquela a todas las celdas que tienen datos en las columnas adyacentes.
= SI (CONTAR.SI ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)
La fórmula anterior usa la función CONTAR.SI para contar el número de veces que aparece un nombre en la región dada. Además, tenga en cuenta que el rango de criterios es $ C $ 2: C2 y $ B $ 2: B2. Esto significa que sigue expandiéndose a medida que avanza por la columna.
Por ejemplo, en la celda E2, los rangos de criterios son $ C $ 2: C2 y $ B $ 2: B2 y en la celda E3 estos rangos se expanden a $ C $ 2: C3 y $ B $ 2: B3.
Esto asegura que la función CONTAR.SI cuenta la primera instancia de un nombre como 1, la segunda instancia del nombre como 2, y así sucesivamente.
Como solo queremos obtener los nombres distintos, se usa la función SI, que devuelve 1 cuando aparece un nombre para una región por primera vez y devuelve 0 cuando vuelve a aparecer. Esto asegura que solo se cuenten los nombres distintos y no las repeticiones.
A continuación se muestra cómo se vería su conjunto de datos cuando haya agregado la columna de ayuda.
Ahora que hemos modificado los datos de origen, podemos usar esto para crear una tabla dinámica y usar la columna auxiliar para obtener el recuento distinto del representante de ventas en cada región.
A continuación se muestran los pasos para hacer esto:
- Seleccione cualquier celda del conjunto de datos.
- Haga clic en la pestaña Insertar.
- Haga clic en Tabla dinámica (o use el atajo de teclado - ALT + N + V)
- En el cuadro de diálogo Crear tabla dinámica, asegúrese de que la tabla / rango sea correcta (e incluya la columna auxiliar) y "Nueva hoja de trabajo" seleccionada.
- Haga clic en Aceptar.
Los pasos anteriores insertarían una nueva hoja que tiene la tabla dinámica.
Arrastre el campo "Región" en el área Filas y el campo "Recuento D" en el área Valores.
Obtendrá una tabla dinámica como se muestra a continuación:
Ahora puede cambiar el encabezado de la columna de "Suma del recuento D" a "Representante de ventas".
Inconvenientes de usar una columna auxiliar:
Si bien este método es bastante sencillo, debo resaltar algunos inconvenientes que vienen con la modificación de los datos de origen en una tabla dinámica:
- La fuente de datos con la columna auxiliar no es tan dinámica como una tabla dinámica. Si bien puede dividir y cortar los datos de la forma que desee con una tabla dinámica, cuando usa una columna de ayuda, pierde una parte de esa capacidad. Supongamos que agrega una columna de ayuda para obtener el recuento de un representante de ventas distinto en cada región. Ahora, ¿qué pasa si también desea obtener el recuento distintivo de representantes de ventas que venden impresoras? Tendrá que volver a los datos de origen y modificar la fórmula de la columna auxiliar (o agregar una nueva columna auxiliar).
- Dado que está agregando más datos a la fuente de la tabla dinámica (que también se agrega a la caché dinámica), esto puede llevar a un tamaño mayor del archivo de Excel.
- Dado que estamos usando una fórmula de Excel, es posible que su libro de trabajo de Excel sea lento en caso de que tenga miles de filas de datos.
Agregar datos al modelo de datos y resumir con un recuento distinto
La tabla dinámica agregó una nueva funcionalidad en Excel 2013 que le permite obtener el recuento distinto al resumir el conjunto de datos.
En caso de que esté usando una versión anterior, no podrá usar este método (como debería intentar agregar la columna de ayuda como se muestra en el método anterior a este).
Suponga que tiene un conjunto de datos como se muestra a continuación y desea obtener el recuento del representante de ventas único en cada región.
A continuación se muestran los pasos para obtener un valor de recuento distinto en la tabla dinámica:
- Seleccione cualquier celda del conjunto de datos.
- Haga clic en la pestaña Insertar.
- Haga clic en Tabla dinámica (o use el atajo de teclado - ALT + N + V)
- En el cuadro de diálogo Crear tabla dinámica, asegúrese de que la tabla / rango sea correcta y que la nueva hoja de trabajo esté seleccionada.
- Marque la casilla que dice: "Agregar estos datos al modelo de datos"
- Haga clic en Aceptar.
Los pasos anteriores insertarían una nueva hoja que tiene la nueva tabla dinámica.
Arrastre la Región en el área Filas y Representante de ventas en el área Valores. Obtendrá una tabla dinámica como se muestra a continuación:
La tabla dinámica anterior proporciona el recuento total del representante de ventas en cada región (y no el recuento distinto).
Para obtener el recuento distinto en la tabla dinámica, siga los pasos a continuación:
- Haga clic con el botón derecho en cualquier celda de la columna "Recuento de representantes de ventas".
- Haga clic en Configuración del campo de valor
- En el cuadro de diálogo Configuración del campo de valor, seleccione "Recuento distinto" como tipo de cálculo (es posible que deba desplazarse hacia abajo en la lista para encontrarlo).
- Haga clic en Aceptar.
Notará que el nombre de la columna cambia de "Recuento de representantes de ventas" a "Recuento distinto de representantes de ventas". Puedes cambiarlo a lo que quieras.
Algunas cosas que sabe cuando agrega sus datos al modelo de datos:
- Si guarda sus datos en el modelo de datos y luego abre en una versión anterior de Excel, le mostrará una advertencia: "Algunas funciones de la tabla dinámica no se guardarán". Es posible que no vea el recuento distintivo (y el modelo de datos) cuando se abre en una versión anterior que no lo admite.
- Cuando agrega sus datos a un modelo de datos y crea una tabla dinámica, no mostrará las opciones para agregar campos calculados y columnas calculadas.
Haga clic aquí para descargar el archivo de ejemplo
¿Qué sucede si desea contar valores únicos (y no valores distintos)?
Si desea contar valores únicos, no tiene ninguna funcionalidad incorporada en la tabla dinámica y tendrá que depender únicamente de las columnas auxiliares.
Recuerde: los valores únicos y los valores distintos no son lo mismo. Haga clic aquí para conocer la diferencia.Un ejemplo podría ser cuando tiene el siguiente conjunto de datos y desea saber cuántos representantes de ventas son únicos para cada región. Esto significa que operan solo en una región específica y no en las demás.
En tales casos, debe crear una de más de una columna auxiliar.
Para este caso, la siguiente fórmula funciona:
= SI (SI (CONTARADOS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2) / CONTAR.SI ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)
La fórmula anterior verifica si el nombre de un representante de ventas aparece solo en una región o en más de una región. Lo hace contando el número de apariciones de un nombre en una región y dividiéndolo por el número total de apariciones del nombre. Si el valor es menor que 1, indica que el nombre aparece en dos o más de dos regiones.
En caso de que el nombre aparezca en más de una región, devuelve un 0; de lo contrario, devuelve uno.
La fórmula también verifica si el nombre se repite en la misma región o no. Si se repite el nombre, solo la primera instancia del nombre devuelve el valor 1 y todas las demás instancias devuelven 0.
Esto puede parecer un poco complejo, pero nuevamente depende de lo que esté tratando de lograr.
Por lo tanto, si desea contar valores únicos en una tabla dinámica, use columnas auxiliares y, si desea contar valores distintos, puede usar la funcionalidad incorporada (en Excel 2013 y superior) o puede usar una columna auxiliar.
Haga clic aquí para descargar el archivo de ejemplo
También le pueden gustar los siguientes tutoriales de tablas dinámicas:
- Cómo filtrar datos en una tabla dinámica en Excel
- Cómo agrupar fechas en tablas dinámicas en Excel
- Cómo agrupar números en una tabla dinámica en Excel
- Cómo aplicar formato condicional en una tabla dinámica en Excel
- Rebanadores en tabla dinámica de Excel
- Cómo actualizar la tabla dinámica en Excel
- Eliminar una tabla dinámica en Excel
