Ediciones ENI Editorial | líder en informática
Tablas dinámicas
Introducción
Cuando necesita obtener información rápidamente, Excel le permite obtener tablas de resumen sin tener que diseñar fórmulas complejas. El método que se debe utilizar es crear tablas dinámicas.
Los datos de origen de una tabla dinámica deben tener siempre la misma estructura:
-
Una fila representa un registro en la base de datos.
-
Una columna representa un campo.
-
La primera fila debe contener los títulos (nombres de campo). Para poder hacer referencias cruzadas a los datos, la base de datos debe contener al menos dos campos para cruzar, más un campo de datos numéricos.
Para evitar problemas al crear sus tablas dinámicas, siga estas indicaciones:
-
No combine celdas en la fila de encabezado.
-
No debe haber dos campos con el mismo nombre.
-
Un nombre de campo no debe estar vacío.
-
La base de datos no debe tener una fila o columna vacía.
-
No introduzca filas de subtotales en la base de datos.
-
En las columnas de valores numéricos, escriba un cero en las celdas vacías.
Los informes de tablas dinámicas se componen de cinco partes:
-
Zona de filtrado
-
Zona de valores
-
Zona de etiqueta de fila
-
Zona de etiquetas de columna
-
Zona de totales
Crear una tabla dinámica sencilla
Los datos de origen
Vamos a crear la tabla estadística a partir del archivo GestProduccion.xlsx.
Los datos de la hoja RVLProd son los siguientes.
La tabla muestra el registro anual de la producción de una pequeña confitería artesanal.
Este resumen anual debería permitirnos calcular diferentes elementos:
-
Producción total de cada línea
-
Producción total por operario
-
Tiempo de inactividad total por tipo de fallo
-
Productividad media por operario
Diseño de tabla dinámica
Nuestro primer objetivo es calcular la producción total de cada línea. En este caso, bastará con cruzar dos datos: la línea y el número de unidades producidas.
Coloque el cursor en una celda del origen de datos, A1 por ejemplo.
En la pestaña Insertar- grupo Tablas, haga clic en Tabla dinámica:
Aparece la ventana Tabla dinámica desde la tabla o el rango:
El rango de datos que se va a analizar se selecciona automáticamente y aparece rodeado por una línea discontinua en movimiento. La ubicación Nueva hoja de cálculo aparece seleccionada por defecto.
Haga clic en Aceptar directamente.
Excel crea una nueva hoja de cálculo con la zona de informe a la izquierda.
El cuadro de diálogo Campos de tabla dinámica aparece en el lado derecho de la ventana.
Arrastre el campo LÍNEA a la zona Filas y el campo Num Total Unidades Producidas a la zona ∑ Valores.
Por defecto, se ha asignado la función SUMA al campo Num Total de Unidades Producidas. La tabla dinámica se inserta instantáneamente en la hoja.
Formato de tabla dinámica
Excel ha creado automáticamente las etiquetas de nuestra tabla dinámica dinámica. Si lo desea, puede editarlas y aplicar el a los números el formato Separador de miles...
Actualizar una tabla dinámica
Caso 1: El rango de origen tiene el mismo número de filas
Cuando se modifica un dato de una tabla de Excel que contiene fórmulas, estas se vuelven a calcular automáticamente. En una tabla dinámica, cuando cambian los datos de la base de datos, las tablas dinámicas creadas a partir de dicho origen no se actualizan automáticamente. Por lo tanto, necesitamos actualizar las tablas dinámicas si los datos de origen cambian.
Haga clic en la tabla dinámica.
En la pestaña Analizar tabla dinámica - grupo Datos, haga clic en Actualizar y, a continuación, en Actualizar (AltF5) o Actualizar todo (CtrlAltF5) si tiene varias tablas dinámicas.
Si añade filas al final de la base de datos, no se reflejarán en la actualización.
Caso 2: Se ha cambiado el número de filas en el rango de origen
Por lo general, el rango de origen no está fijado. Si exporta los nuevos datos mensualmente, el número de filas en el rango de origen aumentará cada mes. Por lo tanto, el rango de origen se debe modificar manualmente.
Haga clic en la tabla dinámica.
Analizar tabla dinámica - grupo Datos - Cambiar origen de datos
Excel muestra las coordenadas del rango de datos de origen tal y como estaban cuando se diseñó la tabla dinámica.
Cambie el número de la última fila...
Crear una tabla dinámica utilizando datos horarios
Insertar tabla
Ahora queremos obtener el tiempo de producción real promedio para cada línea y para cada operario.
Regrese a la hoja RLVPRod y coloque el cursor en una celda del origen.
En la pestaña Insertar - grupo Tablas, haga clic en Tabla dinámica.
Haga clic en Aceptar directamente.
Arrastre el campo OPERADOR a la zona Filas, el campo LÍNEA a la zona Columnas y el campo TIEMPO PRODUCCIÓN REAL a la zona ∑ Valores.
Excel usó la función Número para resumir el campo TIEMPO PRODUCCIÓN REAL. Por lo tanto, es necesario modificar la función que se va a utilizar.
Despliegue el menú del campo Cuenta de TIEMPO PRODUCCIÓN REAL y seleccione Configuración de campo de valor.
Seleccione la función Promedio y modifique el nombre del campo:
Luego haga clic en el botón Formato de número, en la categoría Hora, haga clic en el formato 13:30 y luego confirme pulsando en Aceptar.
Vuelva a hacer clic en Aceptar para volver a la tabla dinámica.
Aplicar un formato condicional
Vuelva a la primera tabla dinámica que creó.
Supongamos que queremos mostrar sobre un fondo naranja los nombres de las filas con una tasa de pérdida de más del 6%. El formato condicional se aplicará a la primera tabla dinámica. Deshaga...
Crear un cuadro de mando con varias tablas dinámicas
Objetivo
A partir del archivo TD-CuadroMando.xlsx vamos a construir un cuadro de mando para hacer seguimiento en tiempo real de nuestros beneficios y gastos.
A continuación, se muestran las primeras filas de la hoja de datos. Los apuntes contables de gastos y ventas aparecen en la misma lista.
Nuestro cuadro de mando debe permitirnos saber, para todo el año o para uno o varios meses:
-
los gastos totales por apunte,
-
la facturación por cliente,
-
los 5 mejores clientes,
-
el margen generado.
A continuación, se muestra un ejemplo:
Preparación
A lo largo del año, el número de filas de nuestra lista de apuntes contables va a aumentar. Por lo tanto, es esencial crear una serie de datos variables:
En la pestaña Fórmulas - grupo Nombres definidos, haga clic en Administrador de nombres y, a continuación, haga clic en Nuevo.
Escriba el nombre APUNTES y, a continuación, escriba la fórmula en el menú Se refiere a:
Confirme pulsando en Aceptar.
Facturación por cliente
Coloque el cursor en la celda A12 de la hoja CuadroMando.
Desactive la visualización de las líneas de cuadrícula (pestaña Vista - grupo Mostrar).
En la pestaña Insertar - grupo Tablas, haga clic en Tabla dinámica.
Introduzca los distintos datos consultando la siguiente pantalla:
Confirme pulsando en Aceptar.
Arrastre los campos a las diferentes zonas y luego aplique el filtro BENEFICIOS de la lista de la celda B10.
Haga clic con el botón derecho del ratón en uno de los números y luego en Formato de número - Contabilidad...
Conclusión
En este capítulo, nos ha parecido útil presentar un ejemplo de cómo usar tablas dinámicas en un cuadro de mando. Esta solución, que utiliza tablas dinámicas, tiene algunas ventajas pero también algunos inconvenientes que deberá tener en cuenta a la hora de utilizarla.
En primer lugar, destaca la posibilidad de utilizar algunas funcionalidades que serían más difíciles de implementar en un cuando de mando construido únicamente a partir de fórmulas.
-
El uso de segmentos y líneas de tiempo permitirá filtrar la información de origen de las tablas dinámicas de forma sencilla y rápida.
-
Un nuevo elemento de resumen introducido en el rango de origen se integrará automáticamente en una nueva fila de la tabla dinámica.
-
Las agrupaciones por fechas son más fáciles de construir que con fórmulas.
-
Al hacer doble clic en un valor de resumen, se creará una nueva hoja que mostrará los detalles de las entradas relacionadas con dicho valor.
También existen desventajas:
-
Es necesario actualizar manualmente la tabla dinámica para que las nuevas filas añadidas al rango de origen se tengan en cuenta.
-
A diferencia de las fórmulas, la actualización de los cálculos y resúmenes no se hace automáticamente cuando se cambian los datos o se agregan nuevos datos. Debe...