2 Formas de calcular el recuento distinto con tablas dinámicas
Finalidad: Aprenda dos formas de resolver el desafío de análisis de datos, el cálculo del recuento distinto, con tablas dinámicas.
Nivel de habilidad: Intermedio
Video Tutorial
Descarga el archivo de Excel
He incluido tanto el archivo original como el de la solución para que lo descargues aquí:
Desafío de análisis de datos.xlsx (16.1 KB)
Desafío de análisis de datos – Solución de tabla dinámica FINAL.xlsx (118.4 KB)
Cuento de filas únicas
En este post, vamos a echar un vistazo a dos formas diferentes de hacer un recuento distinto utilizando tablas dinámicas. Estos dos métodos fueron presentados como soluciones al reto de análisis de datos que puedes encontrar aquí:
Desafío de análisis de datos de Excel
Para resumir el desafío, queremos crear un informe resumido del recuento de acuerdos por etapa, pero hay múltiples filas por acuerdo en los datos de CRM. Así que tenemos que encontrar una manera de crear un recuento distinto (contando filas únicas) para cada acuerdo para que podamos sumarlos.
Por cierto, ¡gracias a todos los que enviaron una solución al desafío de datos! Hubo un montón de grandes presentaciones.
Solución #1 – Usando una columna de ayuda
Lo bueno de esta solución es que se puede utilizar en cualquier versión de Excel.
Empieza por convertir tus datos en una Tabla de Excel. Para ello, sólo tienes que seleccionar cualquier celda del conjunto de datos, y hacer clic en Formato como tabla en la pestaña Inicio. Haz clic con el botón derecho del ratón en el formato de tabla que desees y selecciona Aplicar y Borrar formato.
Haga clic en Aceptar cuando aparezca la ventana Formato como tabla.
Ahora que sus datos están en formato de Tabla, añada una columna de ayuda a la derecha de la tabla y etiquétela Recuento de tratos. Utilice la función COUNTIF, siendo el rango la columna ID de la operación y el criterio la celda de la columna ID de la operación que se corresponde con la fila en la que se encuentra.
La fórmula devolverá el número de filas para cada número de ID de la operación. Si dividimos la fórmula en el número 1, obtendremos fracciones en cada una de esas celdas que al sumarlas contarán una entrada por cada trato.
El cambio en la fórmula se puede ver en verde aquí:
=1/COUNTIF(,])
Ahora que tenemos estas fracciones que nos darán un recuento distinto cuando creemos nuestra tabla dinámica, podemos seguir adelante y crear la tabla dinámica eligiendo Tabla dinámica en la pestaña Insertar.
Para crear nuestro informe resumido utilizando la nueva tabla dinámica, ponemos la Etapa de Ventas en el área de Filas y el Recuento de Tratos en el área de Suma de Valores.
Esto nos dará el informe resumen que buscamos, con un recuento de tratos en cada etapa de venta.
Lo bueno de usar una tabla dinámica es que a medida que añadimos o eliminamos entradas de datos de origen, podemos actualizar la tabla dinámica («Alt + F5») para incluir esos cambios.
Solución # 2 – Usar Power Pivot
Esta solución sólo está disponible para las versiones de Excel que son 2013 o posteriores para Windows.
Seguimos queriendo nuestros datos formateados como una Tabla de Excel, pero no necesitamos una columna de ayuda para esta solución.
Esta vez, cuando creemos nuestra tabla dinámica, vamos a marcar la casilla que dice Añadir esta tabla al Modelo de datos. (Modelo de datos es otro término para PowerPivot.)
Cuando construyas tu tabla dinámica esta vez, vas a arrastrar el ID de trato al área de Suma de valores.
Eso inicialmente nos da números que no queremos en nuestro informe de resumen. Para solucionarlo, queremos hacer clic con el botón derecho del ratón en la cabecera de la columna Suma de ID de la transacción y seleccionar Configuración del campo de valores. Esto abrirá una ventana donde podemos elegir Recuento Distinto como tipo de cálculo.
La función Recuento Distinto recorre la columna ID de la operación y nos da un recuento de los valores únicos, por lo que nuestro informe de resumen tendrá el mismo aspecto que en la Solución nº 1.
Comparando las dos soluciones
Ambas soluciones son estupendas porque se pueden actualizar cuando se añaden nuevos datos a la tabla de origen.
La ventaja de la Solución #1 es que se puede hacer en cualquier versión de Excel. Dicho esto, si está ejecutando 2013 o posterior en Windows, la solución # 2 es la opción superior. Esto se debe a que la solución # 1 se pone torpe cuando se trata de filtrar los datos hacia abajo (por ejemplo, para un determinado producto) o utilizar rebanadores para diseccionar los datos más.
Si quieres aprender más sobre el uso de las tablas dinámicas, tengo una entrada de blog independiente que puedes consultar aquí: Introduction to Pivot Tables and Dashboards.
Otras soluciones
Hubo un montón de otras grandes soluciones al desafío que se presentaron. Incluyeron el uso de Power Query y nuevas funciones dinámicas. Echaremos un vistazo a esos en futuros posts, pero quería empezar con estos dos porque eran más universales en términos de acceso a la versión de Excel.