Cuando tenemos una cantidad de datos importante, debemos plantearnos formas eficientes para obtener información de los mismos. Normalmente, lo que necesitamos hacer es agrupar dichos datos en categorías para hacer una agregación de los mismos y, a menudo, analizar también su tendencia en el tiempo. Una de las formas mas sencillas y efectivas es realizar este análisis de datos con tablas dinámicas.
Una tabla dinámica es una herramienta integrada en Excel con la que podemos, de forma totalmente flexible, realizar la categorización de los datos y obtener una serie de medidas con simplemente arrastrar y soltar los identificadores de dichos datos en las zonas adecuadas.
Veremos, mediante un ejemplo, que requisitos deben cumplir nuestros datos con respecto a la forma en que debemos presentarlos y después construiremos una tabla dinámica a partir de los mismos para comprender la potencia y sencillez de esta herramienta.
Formato de los datos
El formato que deben presentar los datos a analizar es muy sencillo, aunque muchas veces, lamentablemente, no los tengamos o nos los pasen así.
Hay cierta tendencia demasiado extendida, especialmente cuando no se tiene un gran conocimiento de Excel, a presentar los datos de forma discontinua. Bloques de datos, con lineas en blanco separando diferentes agrupaciones, a menudo encabezadas por títulos identificativos de qué representan los datos que vienen a continuación.
Nada mas erróneo que este método. Eso puede ser muy bonito para presentar informes de datos ya agregados y «cocinados» para ser expuestos a alguien después de su análisis, pero no sirven de nada como «materia prima» a analizar.
Unos buenos datos para exprimir y poder realizar un correcto análisis de datos con tablas dinámicas, tienen que ocupar todo un rango continuo, con una primera fila de encabezados que identifique que dato vamos a ver en dicha columna. Y no hay que tener nada mas. Eso es lo necesario para poder empezar a sacar chispas a nuestros datos, además de suficiente.
Lo que nunca deberemos hacer es algo como lo siguiente:
Con datos en un formato de este tipo, deberemos dedicar mucho tiempo a configurarlos correctamente para su posterior análisis. Lo mas lógico es planificarlo ya desde el principio y, en todo caso, nunca usar Excel como si de un procesador de texto se tratase. Puede interesarte leer Excel no es un editor de texto
Buena práctica: los campos de agregación por periodo
Como ves en la tabla de «datos buenos», tenemos un listado de todas las transacciones de venta, con el detalle de artículo, fecha, cantidad, precio e importe. En la mayoría de las ocasiones, cuando agrupemos datos para su análisis, querremos ver su evolución en el tiempo (ventas de cada artículo, por mes). Para ello, es una muy buena práctica, insertar unos campos para calcular el año y el mes de cada registro. En este ejemplo, los insertaremos a la izquierda de la fecha en las columnas nuevas C y D, pero podrían ir donde mas te guste, es si, respetando el requisito de continuidad en los datos.
Si no sabes como hacerlo, estos son los pasos a seguir:
- Colócate en la cabecera de la columna C. Pulsa botón derecho y selecciona Insertar. Repitelo, para insertar dos columnas.
- Como te ha desplazado los datos hacia la derecha, ahora las columnas C y D están vacías.
- Selecciona la celda C1 y escribe el título del campo: «Año». En D1 vamos a poner «Mes»
- En C2 escribe «=Año(E2)» y en D2 «=Mes(E2)«
- Seleccionad C2 y D2 y ponte en la esquina inferior derecha de D2, en el cuadrito verde. Cuando el cursor cambie de una cruz blanca gruesa a una cruz negra mas fina, haz doble click para copiar esas fórmilas a todo el rango de tus datos.
- Comprueba que está todo correcto y listo.
Creando la tabla dinámica desde cero
Ahora que ya tenemos los datos de una forma apropiada, podemos insertar una tabla dinámica. Para ello, estándo posicionados en cualquier celda dentro de nuestro bloque de datos, iremos a Insertar > Tabla dinámica
esto nos abrirá una ventana de diálogo para que definamos varias opciones para la creación de nuestra tabla dinámica.
Aquí debemos definir varias cosas. En el cuadro 1 tenemos que indicar que datos queremos analizar. Como ves, al abrir este cuadro estando posicionados en una de las celdas en que se ubican nuestros datos, por defecto Excel detecta el rango completo de datos y nos lo propone marcando la opción «Seleccione una tabla o rango» e indicando el rango de nuestros datos.
Aparecen otras dos opciones a las que, de momento, no vamos a hacer mucho casos, pero para que no te quedes con las ganas, te diré que la primera «Utilice una fuente de datos externa» nos permite importar datos de otra fuente como, por ejemplo, un fichero de texto o csv, una base de datos, una URL, etc sin necesidad de tener los datos cargos en nuestra hoja.
La segunda, «Usar el modelo de datos de este libro» se utiliza con PowerPivot que es un complemento que viene con Excel y que sirve para análisis de datos. Similar a lo que vamos a ver en este artículo con tablas dinámicas, pero con múltiples tablas entre las que se pueden definir relaciones y donde se pueden personalizar las medidas a obtener mediante DAX, un lenguaje específico para ello.
Con respecto a las opciones del cuadro 2, sirven para indicar donde queremos ubicar la tabla dinámica. Las opciones son Nueva hoja de cálculo, que es la mas recomendable o en una ubicación de una Hoja de cálculo existente. Esa ubicación definirá la esquina superior izquierda a partir de la cual se creará la tabla dinámica.
La última opción (Agregar estos datos al Modelo de datos), tampoco la usaremos porque está relacionada con PowerPivot.
Con las opciones seleccionadas, hacemos click en Aceptar y vemos que se crea una nueva hoja donde aparece una especie de imagen que dice «TablaDinámica1» (1) y, a la derecha de la hoja, se visualiza una nueva zona titulada Campos de tabla dinámica (2).
Insertando y organizando los datos
Como vemos en la imagen, tenemos dos zonas claramente diferenciadas. La zona señalada como 1 es donde visualizaremos los datos a medida que vayamos configurando nuestra vista. En la zona 2 es donde deberemos configurar lo que queremos ver para poder hacer nuestro análisis de datos con tablas dinámicas. Vamos a ver como funciona esta zona 2.
Como se puede ver, hay dos subzonas dentro de ella. En la de arriba se pueden ver los campos disponibles para crear nuestra tabla dinámica y que, si te fijas, corresponde al encabezado de cada columna de datos. En la parte de abajo, podemos ver que hay cuatro áreas:
- Filtros
- Columnas
- Filas
- Valores
Vamos a crear nuestra tabla e iremos aprendiendo para que es cada una de estas zonas, que verás es realmente sencillo.
Filas
Clicka en la zona de arriba sobre Código Producto y arrástralo para soltarlo en la zona Filas
Vemos que nos aparece, en la columna A, el listado de todos los diferentes artículos de nuestros datos de venta. Una fila por cada artículo diferente. Por eso es la zona Filas.
Columnas
Vamos a arrastrar ahora el campo Mes a la zona Columnas.
Nos ha puesto cada mes en una columna, generando una tabla con los artículos en las filas y los meses en las columnas.
Valores
Arrastremos ahora el campo Importe a la zona Valores, siguiendo el mismo procedimiento de arrastrar y soltar.
Vemos dos cosas interesantes. La primera es que nos ha llenado de números la tabla. Esos números corresponden con la suma de importes de cada artículo para cada mes. La segunda cosa interesante, a la que además, debemos prestar mucha atención, es que en la zona Valores a la que hemos arrastrado el campo Importe, pone Suma de Importe, y no solo el nombre del campo Importe. Eso nos indica que lo que está haciendo es calcular la suma de los valores de ese campo.
Para entenderlo mejor, si cogemos el valor que hay en la celda intersección entre el artículo 4 y el mes Enero, por ejemplo, lo que ha hecho Excel es:
- Aplicar un filtro a nuestros datos para seleccionar aquellas lineas que corresponden al artículo 4. Y solo a ese.
- Aplicar un filtro, a todas las lineas que pasan el filtro previo, para que, además cumplan que el campo mes es 1.
- Sumar los valores del campo Importe de los datos que han pasado los filtros
Filtros
Cojamos el campo Año ahora y arrastrémoslo al área que dice Filtro. Veremos que encima de la tabla se inserta Año y (Todas). Esto nos indica que podemos filtrar los datos por el campo Año, que en este momento tiene seleccionadas Todas las opciones posibles, es decir, no se está aplicando ningún filtro. Como nuestros datos de origen tienen las ventas de los años 2018 y 2019, quiere decir que estamos sumando a Enero (mes 1) las ventas tanto del 2018 como del 2019, que no creo que sea lo que queremos…
Vamos a separar los datos de cada año. Tenemos varias opciones:
- Usar el filtro y seleccionar uno de los años. Con esto, al filtrado de datos realizado en base a la fila y columna en que nos encontremos dentro del área de Valores, se añade el seleccionado en el campo o campos del filtro.
- Agregar el campo año al área de columnas. Lo podemos poner encima del campo mes, con lo que veremos todos los meses de cada año en columnas consecutivas antes de cambiar de año:
- O debajo del mes, es decir primero los meses y después el año, con lo que veremos Enero de 2018 y 2019, Febrero de 2018 y 2019 y así, sucesivamente.
Conclusión
Con esto hemos creado nuestra tabla para poder realizar el análisis de datos con tablas dinámicas, en este caso de los datos de ventas por artículo y periodo.
Como ves, es muy sencilla su creación. Solo con partir de unos datos configurados de forma correcta, el resto es prácticamente arrastrar y soltar los distintos campos.
En este punto, te recomiendo que juegues un poco con los campos y los muevas por las zonas de FIltros, Columnas, Filas y Valores y veas lo que va pasando. En la segunda parte de este artículo, profundizaremos un poco mas en opciones no tan evidentes así como en algunos problemas típicos de las tablas dinámicas.
Si te ha gustado o tienes alguna duda de lo tratado hasta ahora, deja tu comentario mas abajo o envía tu consulta al foro.
