✅ Preparación de datos de origen para la tabla dinámica

Tabla de contenido

¿Cuál es un buen diseño para los datos de origen de la tabla dinámica?
Errores comunes que se deben evitar al preparar los datos de origen
Ejemplos de diseños de datos de origen incorrectos
[CASO DE ESTUDIO] Conversión de datos con formato incorrecto en datos de origen listos para una tabla dinámica

Tener los datos en el formato correcto es un paso crucial para crear una tabla dinámica sólida y sin errores. Si no se hace de la manera correcta, puede terminar teniendo muchos problemas con su tabla dinámica.

¿Cuál es un buen diseño para los datos de origen de la tabla dinámica?

Echemos un vistazo a un ejemplo de buena fuente de datos para una tabla dinámica.

Esto es lo que lo convierte en un buen diseño de datos de origen:

La primera fila contiene encabezados que describen los datos de las columnas.
Cada columna representa una categoría de datos única. Por ejemplo, la columna C solo tiene datos de producto y la columna D y solo datos de mes.
Cada fila es un registro que representaría una instancia de la transacción o venta.
Los encabezados de datos son únicos y no se repiten en ningún lugar del conjunto de datos. Por ejemplo, si tiene números de Ventas para cuatro trimestres en un año, NO debe nombrarlos a todos como Ventas. En su lugar, asigne a estos encabezados de columna nombres únicos, como Ventas Q1, Ventas Q2, etc.
- Si no tiene títulos únicos, aún puede seguir adelante y crear una tabla dinámica y Excel los haría únicos automáticamente agregando un sufijo (como Ventas, Ventas2, Ventas3). Sin embargo, esa sería una forma terrible de preparar y usar una tabla dinámica.

Errores comunes que se deben evitar al preparar los datos de origen

No debe haber columnas en blanco en los datos de origen. Éste es fácil de detectar. Si tiene una columna en blanco en los datos de origen, no podrá crear una tabla dinámica. Mostrará un error como se muestra a continuación.
No debe haber celdas o filas en blanco en los datos de origen. Si bien puede crear con éxito una tabla dinámica a pesar de tener celdas o filas en blanco, hay muchos efectos secundarios que pueden afectarlo más tarde en el día.
- Por ejemplo, supongamos que tiene una celda en blanco en la columna de ventas. Si crea una tabla dinámica con estos datos y coloca el campo de ventas en el área de columnas, le mostrará el RECUENTO y no la SUMA. Eso se debe a que Excel interpreta que toda la columna tiene datos de texto (solo por una sola celda en blanco).
Aplicar formato relevante a las celdas en los datos de origen. Por ejemplo, si tiene fechas (que se almacenan como números de serie en el backend en Excel), aplique uno de los formatos de fecha aceptables. Esto lo ayudaría a crear la tabla dinámica y usar la fecha como uno de los criterios para resumir, agrupar y ordenar los datos.
- Si tiene un par de segundos, intente esto. Formatee las fechas en su tabla dinámica como números y luego cree una tabla dinámica con estos datos. Ahora, en la tabla dinámica, seleccione el campo de fecha y vea qué sucede. Lo pondrá automáticamente en el área de valores. Eso es porque su tabla dinámica no sabe que estas son fechas. Los interpreta como números.
No incluya totales de columna, totales de filas, promedios, etc., como parte de los datos de origen. Una vez que tenga la tabla dinámica, podrá obtenerla fácilmente más adelante.
Siempre cree una tabla de Excel y luego utilícela como fuente para una tabla dinámica. Esto es más una buena práctica y no una trampa. Su tabla dinámica funcionaría bien con una fuente de datos que no sea también una tabla de Excel. La ventaja de Excel Table es que puede ajustar los datos en expansión. Si agrega más filas al conjunto de datos, no es necesario que ajuste los datos de origen una y otra vez. Simplemente puede actualizar la tabla dinámica y automáticamente contabilizará las nuevas filas agregadas a los datos de origen.

Ejemplos de diseños de datos de origen incorrectos

Echemos un vistazo a algunos malos ejemplos de diseños de datos de origen.

Diseño de datos de origen incorrecto: ejemplo 1

Esta es una forma común de mantener los datos, ya que es fácil de seguir y comprender. Hay dos problemas con esta disposición de datos:

No obtienes la imagen completa. Por ejemplo, puede ver que las ventas de Mid West en el primer trimestre son 2924300. Pero, ¿es una venta única o varias ventas? Si tiene cada registro disponible en una fila separada, puede hacer un mejor análisis.
Si continúa y crea una tabla dinámica usando esto (que puede), obtendrá diferentes campos para diferentes trimestres. Algo como se muestra a continuación:

Diseño de datos de origen incorrecto: ejemplo 2

Esta representación de datos puede ser bien recibida por la administración y el público de las presentaciones de PowerPoint, pero no es adecuada para crear una tabla dinámica.

Nuevamente, este es el tipo de resumen que puede crear fácilmente usando una tabla dinámica. Entonces, incluso si eventualmente desea tal apariencia para sus datos, mantenga los datos de origen en un formato listo para Pivot y cree esta vista usando la Tabla Pivot.

Diseño de datos de origen incorrecto: ejemplo 3

De nuevo, esta es una salida que se puede obtener fácilmente utilizando una tabla dinámica. Pero no se puede utilizar para crear una tabla dinámica.

Hay celdas en blanco en el conjunto de datos y los trimestres se distribuyen como encabezados de columna.

Además, la región se especifica en la parte superior, aunque debería formar parte de todos los registros.

[CASO DE ESTUDIO] Conversión de datos con formato incorrecto en datos de origen listos para una tabla dinámica

A veces, puede obtener un conjunto de datos que no es adecuado para usarse como datos de origen para la tabla dinámica. En tal caso, es posible que no tenga más remedio que convertir los datos en un formato de datos compatible con Pivot.

A continuación, se muestra un ejemplo de diseño de datos incorrecto:

Ahora puede utilizar las funciones de Excel o la consulta dinámica para convertir estos datos en un formato que se pueda utilizar como datos de origen para la tabla dinámica.

Veamos cómo funcionan estos dos métodos.

Método 1: usar fórmulas de Excel

Veamos cómo usar las funciones de Excel para convertir estos datos en un formato listo para tablas dinámicas.

Cree un encabezado de columna único para todas las categorías del conjunto de datos original. En este ejemplo, sería Región, Trimestre y Ventas.
En la celda debajo del encabezado de la Región, use la siguiente fórmula: = INDICE ($ A $ 2: $ A $ 5, REDONDEO (FILAS ($ A $ 2: A2) / COUNTA ($ B $ 1: $ E $ 1), 0))
- Arrastre la fórmula hacia abajo y repetirá todas las regiones.
En la celda debajo del encabezado Trimestre, use la siguiente fórmula: = ÍNDICE ($ B $ 1: $ E $ 1, REDONDEO (MOD (FILAS ($ A $ 2: A2), CONTAR ($ B $ 1: $ E $ 1) +0.1) , 0))
- Arrastre la fórmula hacia abajo y repetirá todos los trimestres.
En el encabezado debajo de Ventas, use la siguiente fórmula: = INDICE ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Arrástrelo hacia abajo para obtener todos los valores. Esta fórmula utiliza los datos de Región y Trimestre como valores de búsqueda y devuelve el valor de ventas del conjunto de datos original.

Ahora puede utilizar estos datos resultantes como datos de origen para la tabla dinámica.

Haga clic aquí para descargar el archivo de ejemplo.

Método 2: usar Power Query

Power Query tiene una función que puede convertir fácilmente este tipo de datos en formato de datos Pivot ready.

Si usa Excel 2016, las funciones de Power Query estarán disponibles en la pestaña Datos en el grupo Obtener y transformar. Si usa Excel 2013 o versiones anteriores, puede usarlo como complemento.

Aquí hay una excelente guía sobre la instalación de Power Query de Jon desde Excel Campus.

Nuevamente, considerando que tiene los datos formateados como se muestra a continuación:

Estos son los pasos para convertir los datos de origen al formato listo para tablas dinámicas:

Convierta los datos en una tabla de Excel. Seleccione el conjunto de datos y vaya a Insertar -> Tablas -> Tabla.
En el cuadro de diálogo Insertar tabla, asegúrese de que esté seleccionado el rango correcto y haga clic en Aceptar. Esto convertirá los datos tabulares en una tabla de Excel.
En Excel 2016, vaya a Datos -> Obtener y transformar -> De la tabla.
- Si está utilizando el complemento Power Query en una versión anterior, vaya a Power Query -> Datos externos -> De la tabla.
En el editor de consultas, seleccione las columnas que desea anular la división. En este caso, estos son los de los cuatro trimestres. Para seleccionar todas las columnas, mantenga presionada la tecla Shift y luego seleccione la primera columna y luego la última columna.
Dentro del Editor de consultas, vaya a Transformar -> Cualquier columna -> Desvivar columnas. Esto convertirá los datos de la columna en un formato compatible con la tabla dinámica.
Power Query da nombres genéricos a las columnas. Cambie estos nombres por los que desee. En este caso, cambie Atributo a Trimestre y Valor a Ventas.
En el Editor de consultas, vaya a Archivo -> Cerrar y cargar. Esto cerrará el cuadro de diálogo del Editor de Power Query y creará una hoja de trabajo separada que tendrá los datos con columnas sin divisiones.