Trucos con Tablas Dinámicas

Trucos con Tablas Dinámicas

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.

 

Generar tablas dinámica como Tabla

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.

Insertar Escala de Tiempo

 

Escala de tiempo en tablas dinámicas

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

 

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)

 

Dashboard de tablas dinámicas

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

Mapas Coropléticos en Tablas Dinámicas

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)

 

Conectar paneles de segmentación en Excel

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

 

Agrupar por décadas en Tablas Dinámicas

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 tablas en Excel

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:

Conveetir datos en tabla en Excel

Crear tabla en Excel

Cambiar nombre a las Tablas

Agregar Tablas en hoja nueva

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.

 

Relaciones al Vincular Tablas Dinámicas mediante claves principales

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.

Relaciones entre tablas para generar tabla dinámica

 

Escalas de Color o Mapa de Calor

 

Escalas de color en tablas dinámicas

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

 

Formato condicional con 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.

 

Agregar tabla a modelo de datos con PowerPivot

Crear relaciones entre tablas en Powerpivot

Tablas relacionadas en PowerPivot

Agregar datos al modelo

Tabla dinámica con 2 tablas conectadas

Mostrar Páginas (Hojas) con Filtros de Informe

 

Mostrar páginas con filtros de infome a partir del filtro de la tabla dinámica

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)

Repetir etiquetas de elementos 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.

 

Agrupar una variable numérica por intervalos en una tabla dinámica

 

Quitar función ImportarDatosDinámicos()

 

Quitar función importar datos dinámicos

 

Configuración de Segmentación de Datos: No tener en cuenta registros ‘en blanco’

Configuración de la segmentación

 

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

Respetar ancho de columnas al autoajustar

 

estamatica@gmail.com