¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Cuadros de mando
  3. Tablas dinámicas
Extrait - Cuadros de mando Gestione su información para optimizar la toma de decisiones con Excel (versiones Microsoft 365, 2019, 2021)
Extractos del libro
Cuadros de mando Gestione su información para optimizar la toma de decisiones con Excel (versiones Microsoft 365, 2019, 2021) Volver a la página de compra del libro

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.

Images/cap8_2.png

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:

Images/cap8_3.png

Aparece la ventana Tabla dinámica desde la tabla o el rango:

Images/cap8_3_b.png

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.

Images/cap8_3_c.png

 Arrastre el campo LÍNEA a la zona Filas y el campo Num Total Unidades Producidas a la zona  Valores.

Images/cap8_4.png

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.

Images/cap8_13.png

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.

Images/cap8_13_b.png

 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.

Images/cap8_14.png

 Seleccione la función Promedio y modifique el nombre del campo:

Images/cap8_15.png

 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.

Images/cap8_15_b.png

 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.

Images/cap8_18.png

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:

Images/cap8_23.png

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:

Images/cap8_18_c.png

 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:

Images/cap8_20.png

 Confirme pulsando en Aceptar.

 Arrastre los campos a las diferentes zonas y luego aplique el filtro BENEFICIOS de la lista de la celda B10.

Images/cap8_20_b.png

 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.

  1. 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.

  2. Un nuevo elemento de resumen introducido en el rango de origen se integrará automáticamente en una nueva fila de la tabla dinámica.

  3. Las agrupaciones por fechas son más fáciles de construir que con fórmulas.

  4. 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:

  1. Es necesario actualizar manualmente la tabla dinámica para que las nuevas filas añadidas al rango de origen se tengan en cuenta.

  2. 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...