¡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. Funcionalidades avanzadas
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

Funcionalidades avanzadas

Introducción

Ahora que ya se ha familiarizado con las técnicas de creación de tablas dinámicas, vamos a profundizar en estos conocimientos. En este capítulo va a poner en práctica técnicas avanzadas. Para disponer de casos específicos y variados, utilizaremos cinco archivos de origen:

  • SeguimientoStocks.xlsx, con el que va a crear estadísticas de los stocks de materias primas por número de lote.

  • GestiónProducción.xlsx, que recoge los datos de producción de una empresa que prepara tartas.

  • HorariosParadaMáquinas.xlsx, que gestiona las horas de parada de las máquinas en una empresa industrial.

  • Participantes.xlsx, que mantiene la lista de deportistas que participan a menudo en carreras nacionales.

  • VentasVehículos, con las ventas de vehículos realizadas en diversas agencias.

Estas cinco tablas le van a permitir crear tablas dinámicas variadas.

Conocimientos necesarios

Técnicas de creación de tablas dinámicas

Lo que va a aprender

Campos calculados

Utilización de rangos dinámicos

Agrupamientos por fecha

Gestión de orígenes múltiples

Gestión de relaciones

Cálculo de horas

Clasificación por tramos

Lo que va a aprender

Conexiones de informes

Porcentajes en relación con

Segmentos compartidos

Seguimiento de stocks de materias primas

Resúmenes y cálculos

 Abra el archivo SeguimientoStocks.xlsx.

images/OST10_111.png

La hoja SeguimientoStock de este libro registra todos los movimientos de stocks (en kg) relativos a las materias primas utilizadas en una fábrica agroalimentaria.

La gestión de los stocks se realiza por materia prima con un subtotal por número de lotes.

Total de entradas-salidas por materia y número de lote

 Cree la tabla dinámica a partir de la hoja Seguimientostock, basándose en la siguiente imagen del panelCampos de tabla dinámica.

images/OST10_112.png

La tabla muestra los movimientos de stock por producto, detallados por lote.

Cambie las etiquetas de las columnas A, B y C a PRODUCTOS-Lotes n.º, CANT. ENTRADA TOTAL, CANT.SALIDA TOTAL.

Este es el aspecto de las primeras filas de la tabla insertada:

images/OST10_113.png

El total general indica el peso total de materias primas que han entrado y que han salido. Esto no es importante, ya que estamos sumando pesos de productos diferentes.

 Haga clic derecho en la tabla dinámica y haga clic en la opción Opciones de tabla dinámica; en la pestaña Totales y filtros, desmarque la opción Mostrar totales generales de las columnas.

Un primer análisis rápido nos muestra que algunos lotes se han agotado. El objetivo ahora es conservar solo los lotes de los que quede stock.

La cantidad en stock de cada lote se puede calcular de la siguiente forma: Cantidad entrada total - Cantidad salida total.

Excel nos permite integrar fórmulas en nuestras tablas dinámicas; los campos insertados de esta manera se llaman Campos calculados.

Insertar un campo calculado

 Haga clic en la tabla dinámica.

 En la pestaña Análisis de tabla dinámica, grupo Cálculos, haga clic en el botón Campos, elementos y conjuntos, y a continuación, en la opción Campo calculado.

images/OST10_114.png

Excel abre el cuadro de diálogo Insertar campo calculado:

images/OST10_115.png

 Introduzca el nombre del campo y la fórmula de cálculo tal y como muestra la siguiente imagen:

images/OST10_116.png

Para insertar los campos CANT. ENTRADA y CANT. SALIDA en la sección Fórmula, puede hacer doble clic en el nombre del campo, en la lista de campos.

 Haga clic en el botón Sumar y haga clic en Aceptar.

El campo calculado Cantidad Stock se ha añadido a la lista de campos; el panel tiene...

Seguimiento de la producción

Objetivo

Usted es responsable de producción en una fábrica de tartas. Su objetivo es:

  • Calcular la producción real anual de cada una de las tartas.

  • Conocer el tiempo de producción medio (en horas) por productos acabados, calculado sobre datos de dos años, para ajustar su precio de venta.

  • Conocer el porcentaje de pérdida medio por producto.

  • Comparar la productividad del año 2021 con la de 2020.

 Abra el libro GestiónProducción.xlsx.

El libro contiene una hoja (PROD) que contiene los registros de producción de los dos años. Cada fila de la tabla de origen detalla la fabricación de una tarta.

A continuación, puede ver las primeras filas de la hoja PROD.

images/OST10_136.png

Creación de las tablas de análisis

Inserción de nuevas fórmulas

Antes de empezar nuestros cálculos estadísticos, son necesarios algunos cálculos previos. Debemos calcular:

  • La duración de fabricación del producto.

  • La producción por hora.

 Añada en la hoja las columnas y fórmulas detalladas a continuación.

images/CAP05IMG27.png

DURACIÓN: =D2-C2

PROD. POR HORA: =I2/J2/24 (dé formato Número - Usar separador de miles)

 Copie las fórmulas hacia abajo hasta la última fila de la tabla.

Producción real anual

Construyamos la primera tabla dinámica: producción real de cada tarta....

Horarios de parada de máquinas

Definición de rangos dinámicos

 Abra el archivo HorariosParadaMáquinas.xlsx.

Este libro contiene dos hojas. La hoja MÁQUINAS presenta la lista de las herramientas mecánicas disponibles en los talleres. La hoja RELEVOHORAS proviene de un archivo de fichaje que registra las horas de parada de las máquinas.

Las primeras filas de la hoja MÁQUINAS:

images/OST10_148.png

Las primeras filas de la hoja RELEVOHORAS:

images/OST10_149.png

El número de filas de la hoja RELEVOHORAS aumentará cada día y es posible que se invierta en una nueva máquina. Puede haber nuevas MÁQUINAS a las que se les pida intervenir. Nos conviene configurar dos rangos dinámicos.

MÁQUINAS:

 En la pestaña Fórmulas, grupo Nombres definidos, haga clic en el botón Administrador de nombres o bien utilice el atajo CtrlF3.

 En el cuadro de diálogo Administrador de nombres, haga clic en el botón Nuevo.

Introduzca el nombre del rango (ListaMaquinas) e introduzca la fórmula en la sección Se refiere a:

=DESREF(MÁQUINAS!$A$1;0;0;CONTARA(MÁQUINAS!$A$1:$A$200);2)

 Haga clic en Aceptar.

HORARIOS:

 En el cuadro de diálogo Administrador de nombres, haga clic en el botón Nuevo.

Introduzca el nombre del rango (ListaHorarios) e introduzca la fórmula en la sección Se refiere a:

=DESREF(RELEVOHORAS!$A$1;0;0;CONTARA(RELEVOHORAS!$A$1:$A$10000);5)

 Haga clic en Aceptar.

 Haga clic en Cerrar.

Preparación del modelo de datos

La tabla dinámica se debe crear a partir de dos hojas de cálculo. Los datos de las dos hojas se relacionan mediante el código de la máquina. Este código es único en la hoja MÁQUINAS. En la hoja RELEVOHORAS este código aparece tantas veces como se haya detenido la máquina. Este conjunto de datos constituye un modelo de datos.

Un modelo de datos constituye un método que permite integrar en un libro de Excel datos provenientes de diferentes tablas para crear un origen de datos relacional.

Para calcular el tiempo total de parada de cada máquina, vamos a añadir una columna para cada intervención.

 En la hoja RELEVOHORAS, celda E2, introduzca la fórmula =D2-C2.

 Para que nuestra tabla dinámica pueda sumar las duraciones a partir del modelo de datos, dé...

Participantes deportivos

Los datos de origen

 Abra el archivo Participantes.xlsx.

Cada fila de nuestra tabla de origen hace referencia a un participante y al número de puntos que ha conseguido en diferentes competiciones.

images/OST10_175.png

Rango dinámico en filas y columnas

Como se pueden añadir nuevos participantes, vamos a definir un rango dinámico para estos datos. Por seguridad, cuando defina un rango dinámico, le aconsejamos que tenga en cuenta que tanto el número de filas como el número de columnas puede variar. Esto le permitirá no tener que redefinir los rangos de origen cuando haya una modificación.

La función DESREF deberá tener en cuenta un rango cuyo alto y ancho sean variables. Consideremos un máximo de 1000 filas y de 10 columnas.

 En la pestaña Fórmulas, grupo Nombres definidos, haga clic en el botón Administrador de nombres, o utilice el atajo de teclado CtrlF3.

 En el cuadro de diálogo Administrador de nombres, haga clic en el botón Nuevo.

 Escriba el nombre del rango (ListaParticipantes) e introduzca la fórmula en la sección Se refiere a:

=DESREF(Lista!$A$1;0;0;CONTARA(Lista!$A$1:$A$1000);CONTARA(Lista!$A$1:$J$1))

images/OST10_176.png

 Haga clic en Aceptar y a continuación, en Cerrar.

El segundo parámetro CONTARA(Lista!$A$1:$J$1) calcula el número de celdas no vacías en el rango A1:J1. Para no distorsionar...

Compartir un segmento entre varias tablas dinámicas

Objetivo

Cuando sus análisis requieren la elaboración de varias tablas dinámicas, probablemente desee que se aplique un filtro a una tabla, para que se aplique automáticamente a todas las demás.

Resulta sencillo compartir un segmento entre diferentes tablas dinámicas; se trata de establecer una conexión desde el segmento a una tabla dinámica. Cuando modifica el segmento compartido, las tablas dinámicas conectadas se actualizan automáticamente.

Se pueden usar dos tipos de segmentos:

  • Segmentos locales (conectados a una sola tabla dinámica).

  • Segmentos compartidos (conectados a varias tablas).

Una tabla dinámica puede contener tanto segmentos locales como compartidos.

En este libro, ya hemos puesto en práctica el método de creación de un segmento. Para estudiar el uso compartido de un segmento, utilizaremos nuestras dos tablas dinámicas actuales.

Cuando activa un filtro en una de las tablas, dicho filtro no se aplica automáticamente a la segunda. Para solucionar este problema, vamos a modificar el diseño de nuestras tablas. 

 En primer lugar, elimine el filtro por sexo en cada una de las tablas dinámicas.

 Agregue un segmento sobre el campo sexo en la primera tabla.

images/OST10_187.png

 Seleccione el segmento.

 Pestaña Segmentación - grupo Segmentación de datos...

Venta de vehículos

Los datos de origen

 Abra el archivo VentaVehículos.xlsx.

Cada fila corresponde a la venta de un automóvil en una agencia.

images/OST10_191.png

Diferencia en % con relación a

Deseamos conocer la variación en porcentaje de cada agencia en relación con la mejor.

En primer lugar, debemos calcular el número de vehículos vendidos en cada agencia.

 Elabore la tabla dinámica siguiente.

images/OST10_192.png

Barcelona es la agencia que ha vendido más vehículos.

 Haga clic derecho en uno de los valores de la tabla dinámica y seleccione Configuración de campo de valor.

 En la pestaña Mostrar valores como, seleccione % de la diferencia de

 Seleccione el campo AGENCIA y el elemento base BARCELONA.

images/OST10_193.png

 Confirme haciendo clic en Aceptar.

Obtenemos el porcentaje de variación por agencia y vehículo.

images/OST10_194.png

 Confirme haciendo clic en Aceptar.

Para dar por finalizada nuestra estadística, cambie las etiquetas y elimine la visualización de los totales de las columnas.

images/OST10_195.png