Trucos con Tablas Dinámicas en Microsoft Excel
Las Tablas Dinámicas en Excel (además de los Gráficos Dinámicos), en cualquier versión del software desde la 2013, son una de las herramientas más potentes que se puede encontrar dentro de una Hoja de Cálculo de Microsoft Office. Además de imprescindibles para hacer en minutos el trabajo que, de otra manera, nos llevaría horas, incluso días, son el núcleo principal de cualquier curso de Excel de nivel medio o avanzado, junto con las Macros. Funcionan a modo de resúmenes de datos de filtros con criterio. Con ese post, se trata de sacar a la luz opciones no tan conocidas de esta herramienta tan esencial para gestionar una base de datos, en la que los valores de las variables de algún modo se repiten, esto es, trucos y tips a tener en cuenta a la hora de llevar a cabo tablas dinámicas.
En primer lugar, siempre la mejor manera de actuar a la hora de crear una Tabla Dinámica, es partir de una Tabla ( a modo de bases de datos), que convertimos previamente en Tabla para Excel (Cinta de Opciones Insertar), en la cual reservamos la primera fila para el nombre de los campos o variables que van a intervenir, y a partir de la 2ª fila empezamos a introducir nuestros registros, es decir, siempre nuestro número de casos será el número de filas menos 1. De esta manera, nos aseguramos que al introducir un registro/caso nuevo, con sólo hacer clic en ‘Actualizar‘ en la cinta Analizar de Tabla Dinámica, este caso quedará reflejado en los resultados de la misma. Todo esto, nos evita el ‘cambiar origen de datos’, cada vez que inserta una nueva fila en la tabla original, en el caso que no se definiera nuestra base de datos como tabla de Excel. Es importante que la tabla esté ‘limpia’, sin celdas combinadas, que desvirtúan el que nuestra tabla funcione a la manera de una tabla de Access.
Índice del Artículo
Insertar Escala de Tiempo
Se pueden utilizar paneles especiales para segmentar a partir de una variable con formato de fecha, como es el panel (slicer) de la escala de tiempo, en que la tabla dinámica aparece filtrada por años, trimestres, meses e incluso días. como se puede comprobar en esta captura de pantalla.
Convertir valores numéricos a texto dentro de la Tabla Dinámica
En las tablas dinámicas no se pueden mostrar valores de texto (variables categóricas) de manera predeterminada (por defecto), aunque podemos conseguir esta funcionalidad a través del formato ‘Personalizada’ de celda, introduciendo en el cuadro de Tipo un tipo de condicional especial, como el de la captura de encima.
Paneles o DashBoard (Cuadro de Mando Integral)
Un Dashboard es un informe/reporte interactivo que contiene tablas y gráficos dinámicos, segmentadores, conexiones de informe,etc. Se pueden generar Dashboard a partir de la combinación de las diversas herramientas que conforman las Tablas Dinámicas, como son los propios Gráficos Dinámicos, Paneles de Segmentación de Datos o Slicers, etc. El resultado también se puede guardar de una manera estática como PDF, para consolidar un informe de trabajo, a partir de las propias Tablas Dinámicas.
Mapa Coroplético
Con los Mapas Coropléticos en Tablas Dinámicas, se pueden representar/comparar como mapas de calor las ubicaciones de la tabla respecto a los valores de la variable numérica a contrastar (ventas, importes, recuentos, promedios, etc).
Conectar Paneles de Segmentación (Slicers)
A través de ‘Opciones‘ del propio objeto panel de segmentación, se pueden interconectar este tipo de filtros, haciendio clic en la opción ‘Conexiones de informe’, eligiendo las tablas dinámicas que se que quieran combinar, filtrando las categorías de cada filtro de manera simultánea en todas las Tablas, con tan solo hacer clic en una opción de filtro o varias.
Campos Calculados
Existe la posibilidad de crear nuevas variables a partir de las ya existentes, además de introducir/generar fórmulas dentro de la propia Tabla Dinámica, con la opción de ‘Insertar campo calculado‘, la única diferencia con una función normal de Excel, es que en lugar de hacer llamada/as a una/s celda/s de la hoja de cálculo del libro de Excel que estemos utilizando, se utilizan las propias variables o campos de la Tabla original de partida. Se pueden introducir funciones de tipo condicional o condicional anidado, con tan sólo sustituir el nombre de la celda (ejemplo: B5), por el nombre de la variable.
Campos Fecha en Tablas Dinámicas
Los campos del tipo Fecha, permiten la agrupación por trimestres, meses, años, días…arrastrar el campo fecha con las mismas, de la tabla original, y utilizar el menú contextual que aparece con el botón derecho para Agrupar o Desagrupar, con sus respectivas opciones.
Agrupar por Décadas
Se pueden agrupar por décadas los años en Tablas Dinámicas, arrastrando el campo ‘años’ a filas de la tabla dinámica, situando el foco de entrada haciendo clic en cualquier año, y con botón derecho aparece el menú contextual de ‘Agrupar’, y poder obtener por ejemplo una tabla de frecuencias de valores de una variable por décadas, con la opción ‘recuento’ en ‘campo calculado’.
Cálculo del % de la diferencia de un año respecto a otro
Sobre la variable de tipo numérico, opción ‘Configuración de campo’: ‘Mostrar valores como’ (pestaña) y en el desplegable seleccionar la opción ‘% de la diferencia de’. Elegir el año que se toma como base para el cálculo del número índice.
Vincular Tablas Dinámicas
Se parte de datos estructurados a modo de tablas en Excel, que aún no son tablas propiamente dichas. Se convierten, ahora sí en tablas de Excel, poniendo el foco de entrada en cada una de las 2 tablas y dándoles un nombre a estas tablas:
Siempre a partir de 2 Tablas con un campo coincidente (un concepto semejante al de campo clave en Access para relacionar tablas), como puede ser un Código o una Referencia de producto, existe la posibilidad de vincular Tablas Dinámicas y conseguir cualquier cálculo a partir de la fusión de las mismas.
A la hora de establecer Relaciones entre tablas, es la tabla auxiliar es la que se coloca primero. la columna es la de la clave principal, campo común a ambas tablas, que suele ser un código, una referencia, la variable que lee un detector de código de barras, etc.
Escalas de Color o Mapa de Calor
Genera un gradiente de color que fluctúa entre verde y el rojo, en función de las cantidades que resultan de los campos de tipo numérico que se colocan en la sección de ‘Valores’ de la tabla dinámica creada, sin tener en cuenta totales y subtotales.
Formato condicional ICONOS
A partir de los valores de un rango, podemos hacer que la celda aparezca un determinado icono u otro, como sucede cuando empleamos un formato condicional a la hora de formatear o representar unos valores.
Conectar Tablas Dinámicas a través de campo en común con POWERPIVOT
Conectar 2 tablas, al modo de las relaciones heredadas de Access, para consolidad la información en una única tabla dinámica, que se alimenta de esas 2 o más tablas, siempre a partir de un campo clave común que las identifica de manera única.
Mostrar Páginas (Hojas) con Filtros de Informe
Existe la opción de obtener el informe para cada hoja del libro de Excel a través de las categorías de filtrado (una hoja para cada elemento del filtro), que genera Mostrar páginas con filtros de informe a partir del propio filtro de la tabla dinámica, en Opciones de Tabla Dinámica…
Repetir etiquetas de elementos (categorías de variables)
Agrupar en Intervalos una variable numérica
Agrupar una variable numérica por intervalos en una tabla dinámica por filas o columnas.
Quitar función ImportarDatosDinámicos()
Configuración de Segmentación de Datos: No tener en cuenta registros ‘en blanco’
Opciones de Tabla Dinámica
Haciendo clic con botón derecho (menú contextual) en Opciones de Tabla Dinámica en cualquier celda de la misma, previamente generada, nos aparece un cuadro de diálogo de diversas opciones que podemos configurar, como sería lo que queremos que aparezca en las celdas vacías.
Respetar ancho de columna al Autoajustar
