Ediciones ENI Editorial | líder en informática
El cuadro de mando avanzado seguimiento de horas
Presentación
Datos de origen
Como propietario de una pequeña empresa de construcción de casas de madera, desea realizar un seguimiento de las horas trabajadas por sus empleados. En la empresa, trabajan tres categorías de personal: obreros tradicionales, carpinteros y, posiblemente, trabajadores temporales.
En la hoja Horas del libro SeguimientoObras.xlsx, su asistente registra las horas trabajadas por cada persona diariamente.
Desea saber en tiempo real:
-
el número de horas trabajadas al mes en cada obra,
-
el número de horas de trabajadas con mal tiempo al mes,
-
el número de horas trabajadas por cada categoría de empleado.
Número de horas mensuales trabajadas en cada obra
La hoja EstadMens contiene tablas en las que se deben insertar las fórmulas.
Cada día, el rango de datos de origen aumentará a medida que se agreguen nuevas filas. En este caso, es una buena idea utilizar rangos dinámicos para los cálculos, cuyos tamaños se corresponderán con el número exacto de filas introducidas. Esto evitará tener que seleccionar rangos mucho más grandes en las fórmulas, para garantizar que estas tengan en cuenta los datos que se hayan añadido.
Para definir un rango dinámico es necesario utilizar la función de cálculo DESREF y definir un rango con nombre.
La sintaxis de la función se detalla a continuación:
=DESREF(Referencia; Filas;Columnas;Alto;Ancho)
Referencia: La referencia en la que desea basar el desplazamiento. .
Filas: Es el número de filas, hacia arriba o hacia abajo, al que desea que haga referencia la celda superior izquierda.
Columnas: Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia la celda superior izquierda del resultado.
Alto: Es el alto, en número de filas, que se desea que tenga la referencia devuelta.
Ancho: Es el ancho, en número de columnas...
El cuadro de mando
Presentación
Nuestro objetivo es representar gráficamente:
-
Las horas trabajadas acumuladas con respecto a las horas planificadas y debe ser posible seleccionar la obra representada, utilizando una lista desplegable.
-
La distribución de horas entre las diferentes categorías de empleados.
El cuadro de mando también debe mostrar el número total de horas trabajadas, así como la tasa de horas trabajadas en comparación con las horas planificadas.
Por último, habrá que insertar dos "botones" para permitir la navegación entre las hojas del libro.
Consideraremos aquí que la empresa está cerrada en agosto, por lo que las horas estimadas se repartirán en 11 meses.
Tablas iniciales
Antes de diseñar los gráficos, será necesario preparar las tablas.
La hoja CM ya contiene la lista desplegable (pestaña Datos - Validación de datos) en C5 y la estructura de las tablas de origen de los gráficos.
Las fórmulas:
La fórmula de la celda E28 debe mostrar el total de horas planificadas para la obra seleccionada en C5.
Haga clic en E28.
Escriba la fórmula: =INDICE(EstadMens!$C$4:$D$4;1;COINCIDIR(CM!C5;EstadMens!$C$9:$D$9; 0))
Escriba F28: =SI(E28=0;0;C42/E28) y formatee la celda como Porcentaje)
HORAS TRABAJADAS:
|
C30 |
{=SUMA((MES(Fechas)=COINCIDIR($B30;$B$30:$B$41;0))*(Obras=C$5)*(NumHorasTrab))} (validar la fórmula con CtrlMayúsIntro) |
ACUMULADO REAL:
|
D30 |
=SI(C30=0; NOD(); SUMA($C$30:C30)) La función NOD() le permitirá evitar mostrar en el gráfico valores de meses que aún no han llegado. |
HORAS PREVISTAS:
|
E30 |
=SI(B30<>"Agosto";$E$28/11;0) |
ACUMULADO PREVISTO:
|
F30 |
=SUMA($E$30:E30) |
Copie estas últimas cuatro fórmulas hacia abajo (CtrlD).
TOTAL DE HORAS TRABAJADAS:
|
C42 |
=SUMA(C30:C41) |
TOTAL DE HORAS TRABAJADAS POR TIPO DE PERSONAL:...
Hojas de navegación
Los botones de la hoja CM
En la pestaña Insertar - grupo Ilustraciones, despliegue el menú Formas y haga clic en la forma Rectángulo de esquina redondeada.
Dibuje un rectángulo en las filas 1 y 2.
En el grupo Formato de forma - Estilos de forma, despliegue el menú Más y seleccione Efecto intenso - Rojo, Énfasis 2.
Escriba el texto ESTADÍSTICAS MENSUALES y modifique su formato.
Haga clic con el botón derecho del ratón en la forma y seleccione Vínculo.
En el apartado Vincular a, seleccione Lugar de este documento, seleccione la hoja EstadMens y a continuación, haga clic en Aceptar.
Repita estos pasos para vincular el segundo botón a la hoja Horas.
El botón de la hoja EstadMens
Las hojas EstadMens y Horas deben contener un enlace a la hoja CM (Cuadro de Mando). Para no sobrecargar estas hojas, el enlace se presentará como una flecha hacia la izquierda.
En la pestaña Insertar - grupo Ilustraciones, despliegue el menú Formas y, en Flechas de bloque, haga clic en la forma Flecha: hacia la izquierda.
Dibuje una flecha pequeña en la celda B1.
En el grupo Formato de forma - Estilos de forma, despliegue el menú Más y seleccione Efecto intenso - Aguamarina, Énfasis 5.
Haga clic con el botón derecho del ratón en la flecha y seleccione...
Protección de las tablas
Interés
Como se explica en el capítulo Definir el cuadro de mando, se pueden aplicar dos tipos de protección a las hojas de cálculo de Excel:
-
Protección de elementos clave de las hojas de cálculo: títulos, fórmulas, gráficos, formato, etc., para evitar cambios no deseados.
-
Protección con contraseña del libro para evitar que lo abran personas no autorizadas.
La creación de los cuadros de mando llevará tiempo. Este trabajo no se puede echar a perder por una mala utilización. De hecho, mientras no se haya implementado una protección, siempre es posible introducir datos en lugar de una fórmula o eliminar una fórmula.
La protección de las hojas de cálculo consiste en definir qué acciones puede realizar un usuario.
Inicialmente, todas las celdas de una hoja están bloqueadas. Sin embargo, esta protección solo está activa cuando la hoja está protegida. Antes de aplicar cualquier tipo de protección, todas las zonas de entrada de datos deben estar desbloqueadas.
Por lo tanto, se debe proceder de la siguiente manera:
-
Desbloquear todos los rangos de entrada de datos.
-
Proteger la hoja.
Proteger la hoja CM
Acceda a la hoja CM.
La única celda de esta hoja que se puede modificar es la celda que contiene la lista desplegable de sitios de construcción...