Funcionalidades avanzadas

1. Resúmenes y cálculos

La hoja SeguimientoStock de este libro registra todos los movimientos de stocks (en kgs) relativos a las materias primas utilizadas en una fábrica de pasteles con frutos secos.

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

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

 Cree la tabla dinámica a partir de la hoja Seguimientostock, utilizando el cuadro de diálogo Campos de tabla dinámica.

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

Al arrastrar los campos CANT. ENTRADA y CANT. SALIDA a la zona Σ VALORES, Excel utiliza automáticamente la función Cuenta porque estos campos tienen celdas vacías. Si las celdas vacías se hubieran sustituido por ceros, se habría aplicado la función Suma.

 Modifique en estos dos campos la Configuración de campo de valor, abra la lista del primer campo y haga clic en la opción Configuración de campo de valor.

 Seleccione la función Suma e introduzca CANT. ENTRADA TOTAL en la sección Nombre personalizado.

 Modifique el segundo campo como en la siguiente captura.

 Finalmente, modifique el texto de la etiqueta de las filas: Productos - Nº Lote.

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

El total general indica el peso total de materias primas que han entrado y que han salido. Vamos a considerar que esto no es necesariamente 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 las 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.

1. Objetivo

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

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

2. Creación de las tablas de análisis

a. 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 real (sin las pérdidas).

• La producción por hora.

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

DURACIÓN: =C2-B2

PROD REAL: =E2-F2

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

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

1. Definición de rangos dinámicos

Este libro contiene dos hojas. La hoja AGENTES presenta la lista de personas autorizadas a trabajar en un recinto cerrado. La hoja EXTRACTOHORAS proviene de un archivo de fichaje que registra las horas de entradas y salidas de cada agente del recinto cerrado.

El recinto cerrado en el que trabajan las personas comenzó a funcionar el 1 de enero de 2017. Debe hacer un seguimiento en tiempo real del tiempo acumulado que ha pasado cada agente en el recinto cerrado, con el fin de que no intervengan más los agentes que hayan excedido su cuota de horas autorizadas.

Las primeras filas de la hoja AGENTES:

Las primeras filas de la hoja TURNOHORAS:

El número de filas de la hoja TURNOHORAS aumentará cada día. Puede haber nuevos agentes a los que se les pida intervenir. Nos conviene configurar dos rangos dinámicos.

AGENTES:

HORARIOS:

2. 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 del agente. Este código es único en la hoja AGENTES. En la hoja TURNOHORAS este código aparece tantas veces como el agente haya entrado o salido del recinto. 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 de cada agente en el recinto, vamos a añadir una columna para cada intervención....

1. Los datos de origen

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

2. 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 5000 filas y de 12 columnas.