Ediciones ENI Editorial | líder en informática
Profundizar en el análisis
Introducción
En el capítulo anterior hemos creado tablas dinámicas en las que hemos cruzado dos campos. Ahora vamos a abordar la creación de tablas dinámicas avanzadas. Este capítulo nos va a permitir consolidar los conocimientos adquiridos en el capítulo anterior y también aprender técnicas de personalización de las tablas dinámicas.
|
Conocimientos necesarios |
Creación y edición de tablas dinámicas |
|
Lo que va a aprender |
Filtrar la información Mostrar/ocultar determinados datos Aplicar, modificar y crear estilos de formato Aplicar un formato condicional Utilizar los segmentos Insertar una línea de tiempo |
Cruzar más de dos datos
Los datos de origen
Para disponer de información variada, vamos a utilizar el archivo AlquilerMateriales.xlsx que se puede descargar desde la página Información. Los datos que se muestran en la hoja Alquiler son como los que puede ver a continuación. La última fila introducida es la fila 341.
La tabla representa la lista de los alquileres realizados por una empresa de alquiler de material para diversos fines.
Las facturas se emiten según las siguientes reglas:
-
Particular: facturación el día que se devuelve material
-
Otros clientes: facturación el último día del mes
Si el material devuelto necesita una reparación o rehabilitación, los gastos de reparación se pueden facturar al cliente. En este caso, el importe de los gastos se suma al importe del alquiler para obtener el importe total que se ha de facturar. Todos los importes mostrados son sin impuestos.
Los tipos de material que se pueden alquilar son:
-
Limpieza
-
Compresor de aire
-
Carpintería
-
Andamios
-
Soldadura
-
Iluminación
-
Electricidad
-
Fontanería
Los tipos de cliente son:
-
Profesional
-
Empresa
-
Particular
Los comerciales (en el mostrador) son:
-
FABIO
-
ANDREA
-
FERNANDO
-
LUCAS
-
MATEO
Creación de la tabla dinámica
Nuestro primer objetivo es calcular el importe total de los alquileres por tipo de material y por tipo de cliente. Queremos poder afinar nuestra estadística mostrando también (por tipo de material y por tipo de cliente) la cifra de negocios logrado por comercial.
Abra el libro AlquilerMateriales.xlsx.
Inserte el cursor en una celda del origen; por ejemplo, A1.
En la pestaña Insertar - grupo Tablas, haga clic en el botón Tabla dinámica.
Aparece la ventana Crear tabla dinámica:
Haga clic en Aceptar.
Arrastre el campo FAMILIA PRODUCTOS a la sección Filas.
Arrastre el campo TIPO CLIENTE a la sección Columnas; a continuación, seleccione el campo COMERCIAL y sitúelo debajo de FAMILIA PRODUCTOS en la sección Filas.
Por último, coloque el campo TOTAL FACTURA en la sección Σ Valores.
A medida que coloque los campos en las diferentes secciones, la tabla dinámica se va creando y adaptando automáticamente a la inserción de nuevos campos.
La lista de campos de la tabla dinámica debe quedar como...
Dar formato a la tabla dinámica
Modificar la disposición de la tabla dinámica
Ya hemos tratado las técnicas sencillas de formato de informes de tablas dinámicas. Ahora vamos a profundizar en la personalización de un informe.
En alguna ocasión, puede tener la necesidad de modificar la visualización de los campos en la tabla dinámica.
Vuelva a la hoja Alquileres y cree rápidamente en una nueva hoja la siguiente tabla dinámica.
En este momento, la tabla muestra la cifra de negocios por familia de productos y por comercial.
Añadamos a esta tabla el tipo de cliente. Tenemos dos posibilidades: incluir este campo en las Filas o en las Columnas. Lo más lógico sería agregarlo a las Filas, pero en este ejercicio vamos a situarlo en las Columnas, debajo del campo COMERCIAL.
El riesgo aquí es que la tabla sea menos legible, pero podemos probarlo.
Haga clic en la tabla dinámica.
En la lista de campos, sitúe el campo TIPO CLIENTE a la sección COLUMNAS, debajo del campo COMERCIAL:
Su tabla dinámica tiene ahora más columnas que filas, lo que no mejora la legibilidad.
La siguiente captura de pantalla muestra las primeras columnas de nuestra tabla dinámica.
Modifique la disposición de los encabezados de columnas: realice clic derecho en la tabla y a continuación, haga clic en Opciones de tabla dinámica. En la pestaña Diseño y formato, marque Combinar y centrar celdas con etiquetas.
Haga clic en Aceptar.
La tabla dinámica se ha reorganizado. Cuando toda la información está expandida, los comerciales se centran respecto a los tipos de clientes.
Mostrar/ocultar determinada información
En función del tipo de presentación que desee obtener, es posible ocultar algunos componentes de la tabla dinámica: los botones de expandir y contraer, así como los títulos de campo.
Ocultar los botones de expandir y contraer:
Haga clic en una celda de la tabla dinámica.
Pestaña Análisis de tabla dinámica - grupo Mostrar, haga clic en el botón Botones +/-.
Los botones de expandir y contraer han desaparecido.
Para ocultar/mostrar el detalle de un tipo de material, basta con hacer doble clic en el nombre del tipo de material.
Ocultar los encabezados de fila y columna:...
Los segmentos: convertir sus tablas dinámicas en interactivas
Utilidad
Los segmentos son elementos visuales que permiten filtrar los datos de las tablas dinámicas de manera interactiva e intuitiva. Insertando un segmento, puede utilizar botones para filtrar y segmentar rápidamente los datos de modo que se muestre solo la información deseada. Mediante la combinación de segmentos puede afinar sus análisis.
Técnica
Vamos a utilizar nuestra última tabla dinámica para poner en práctica la técnica de los segmentos.
Haga clic en la tabla dinámica.
En la pestaña Análisis de tabla dinámica, grupo Filtrar, haga clic en el botón Insertar Segmentación de datos.
Aparece la ventana Insertar segmentación de datos.
Marque solo FAMILIA PRODUCTOS y haga clic en Aceptar.
El segmento definido con el tipo de material aparece al lado de la tabla dinámica. Por defecto, se seleccionan todos los elementos y la tabla dinámica no se filtra.
Aplicar un filtro a una tabla dinámica es muy sencillo; basta con seleccionar el elemento deseado.
Para obtener las estadísticas de los alquileres de material de soldadura, haga clic en el botón correspondiente.
La tabla dinámica se actualiza instantáneamente.
Para que la tabla dinámica resuma los datos sumados de varias familias, haga clic en el botón...
Las escalas de tiempo: filtrar sus tablas dinámicas por periodo
Utilidad
En lugar de diseñar múltiples filtros en sus campos de fechas, Excel le permite añadir a su tabla dinámica un rango cronológico en que los elementos representan cada uno una unidad de tiempo. Los elementos de la escala de tiempo se representan en el eje horizontal.
Técnica
Antes de utilizar esta funcionalidad, elimine los segmentos añadidos a nuestra última tabla dinámica.
Haga clic en la tabla dinámica.
En la pestaña Análisis de tabla dinámica, grupo Filtrar, haga clic en el botón Insertar escala de tiempo.
Excel le ofrece solo los campos que tienen fechas.
Marque el campo FECHA FACTURA y haga clic en Aceptar.
La escala de tiempo FECHA FACTURA se inserta al lado de la tabla dinámica. Por defecto, los periodos de tiempo se miden en meses.
No dude en arrastrar una de las esquinas para hacer más grande su escala de tiempo.
A continuación, se representan los diferentes elementos de una escala de tiempo:
Una vez ha definido su escala de tiempo, ya está listo para filtrar por periodo en uno de los cuatro niveles de tiempo (años, trimestres, meses o días).
Para modificar el periodo de tiempo, haga clic en la pequeña flecha situada a la derecha del nivel de tiempo y seleccione el nivel deseado.
Para aumentar el periodo...
Personalizar el diseño predeterminado de las tablas dinámicas
Utilidad
Si necesita utilizar el mismo diseño para todas sus tablas dinámicas, puede resultar útil establecer un diseño predeterminado.
Crear un diseño por defecto
Haga clic en la pestaña Archivo - Opciones. En el cuadro de diálogo Opciones de Excel seleccione Datos en el panel de la izquierda y haga clic en el botón Editar diseño predeterminado.
Haga clic en Opciones de tabla dinámica.
En la pestaña Diseño y formato, marque la opción Combinar y centrar celdas con etiquetas.
Confirme haciendo clic sucesivamente en los tres botones Aceptar.
Importar un diseño personalizado
Haga clic en una tabla dinámica cuyo diseño personalizado vaya a usarse como ejemplo.
Haga clic en la pestaña Archivo - Opciones. En el cuadro de diálogo Opciones de Excel seleccione Datos en el panel de la izquierda y haga clic en el botón Editar diseño predeterminado.
Confirme haciendo clic sucesivamente en los dos botones Aceptar.
Restablecer los valores predeterminados de Excel
Haga clic en la pestaña Archivo - Opciones. En el cuadro de diálogo Opciones de Excel seleccione Datos en el panel de la izquierda y haga clic en el botón Editar diseño predeterminado.
Haga clic en Restablecer valores...
