¡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. Análisis eficaz de datos
  3. Profundizar en el análisis
Extrait - Análisis eficaz de datos Con tablas dinámicas de Excel (versiones 2019 y Office 365)
Extractos del libro
Análisis eficaz de datos Con tablas dinámicas de Excel (versiones 2019 y Office 365) Volver a la página de compra del libro

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.

images/OST10_059.png

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:

images/OST10_060.png

 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. 

images/OST10_074.png

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:

images/OST10_075.png

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.

images/OST10_076.png

 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.

images/OST10_077.png

 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.

images/OST10_078.png

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 +/-.

images/OST10_079.png

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.

images/OST10_092.png

Aparece la ventana Insertar segmentación de datos.

images/OST10_093.png

 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.

images/OST10_094.png

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.

images/OST10_095.png

 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.

images/OST10_099.png

Excel le ofrece solo los campos que tienen fechas.

 Marque el campo FECHA FACTURA y haga clic en Aceptar.

images/OST10_100.png

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.

images/OST10_101.png

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:

images/OST10_102.png

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.

images/OST10_103.png

 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

images/OST10_108.png

 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.

images/OST10_109.png

 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.

images/OST10_110.png

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