Horarios y calendarios de guardias: Técnicas avanzadas en Calc
Un usuario del Foro ha solicitado ayuda a la hora de crear una hoja de Calc que desea utilizar como formulario para crear calendarios y horarios de guardias en un colegio.
Supongamos que durante unas horas al día (desde las 15:00 a las 19:00 horas) unos profesores (Álvaro, Mavi, Juan, Ana, ...) se quedan de guardia controlando a los chavales.
Necesitamos crear un calendario que se expondrá en la sala donde para un determinado mes podamos ver las guardias asignadas a los profesores.
También necesitamos un calendario para cada profesor con su horario de ese mes.
A ser posible, todo debe automatizarse al máximo, para no tener que trabajar mucho
Cómo funciona
La idea es que seleccionando en una celda el mes y el año del que queremos componer el calendario, tan sólo tengamos que definir a qué profesor se asigna la guardia cada hora/día de ese mes.
- En las celdas A2 y B2 de la primera hoja de cálculo llamada GUARDIAS seleccionamos el mes y año deseado.
- Posteriormente asignamos en esa misma hoja las horas a los profesores en la lista de datos Guardias.
- Para obtener el calendario de cada profesor, cambiamos a la hoja PROFESORES, donde elegimos el profesor desde la primera celda y mandamos a imprimir.
- Para obtener el calendario mensual, cambiamos a la hoja MES, donde mandamos a imprimir.
Y nada más
Nota: Para facilitar la comprensión, las hojas de cálculo las nombramos en MAYÚSCULAS, mientras que las listas de datos contenidas en ellas las nombramos en minúsculas.
Los datos
Nuestro libro de hojas de cálculo contiene una hoja que llamamos GUARDIAS. Esta hoja nos permite definir el mes y el año del que deseamos crear los calendarios para los profesores y el calendario mensual.
Una vez indicados estos dos datos podremos definir para cada fecha de ese mes y para cada hora de ese día a qué profesor asignamos.
Técnicas avanzadas utilizadas en las celdas mes y año
Para la selección del mes hemos creado un desplegable (desde Datos > Validez...>Intervalo de celdas) con los nombres de los meses que se han creado en el rango de celdas A8:A19.
Para la selección del año hemos creado otro desplegable con los números de los años que se han creado en el rango de celdas B22:B25. La celda extra pintada en color amarillo contiene un comentario que nos recuerda que si queremos añadir más años, insertemos una celda justo por encima de esta última , con el fin de que se autoajusten los rangos de validación de datos.
-
Los números de años válidos se actualizan automáticamente, dado que la celda F22 contiene una fórmula =AÑO(HOY())-1 que calcula el año anterior al actual. Las dos celdas justo debajo de esta contienen sendas fórmulas, =CeldaAnterior+1, lo que nos permite crear fácilmente un rango de 3 ejercicios, desde el anterior al año actual hasta el siguiente.
Justo el mismo sistema hemos aplicado para crear las listas desplegables tanto en la lista de datos Guardias de la hoja GUARDIAS como en la hoja PROFESORES, utilizando el rango de celdas A22:A33. Otra celda extra pintada en color amarillo contiene un comentario que nos recuerda que si queremos añadir más profesores, insertemos una celda justo por encima de esa última.
También hemos recogido el rango de horas de guardias para crear listas desplegables en la lista de datos Guardias, utilizando el rango de celdas B28:B33. Otra celda extra en color amarillo con otro comentario como recordatorio por si queremos añadir más horas.
Técnicas avanzadas utilizadas en las celdas de las guardias
Con el fin de facilitar la selección de horas y de profesores hemos creado celdas desplegables en la lista de datos de definicion de guardias.
Para la selección de las horas hemos creado un desplegable (desde Datos > Validez...>Intervalo de celdas) con las horas válidas de guardias que se han creado en el rango de celdas B28:B33. La celda extra pintada en color amarillo contiene un comentario que nos recuerda que si queremos añadir más horas, insertemos una celda justo por encima de esta última , con el fin de que se autoajusten los rangos de validación de datos.
El mismo procedimiento hemos seguido con las celdas que contendrán los nombres de los profesores. Para facilitar la selección cuando la celda esté vacía, hemos dejado una celda en blanco a propósito en la lista de celdas que define la validación de datos (el rango A22:A33).
Tanto los calendarios mensual como de profesores presentan un total de 6 semanas. La primera de ellas que se va a presentar en los calendarios es la semana a la que pertenece el primer día del mes.
Así, si el mes comienza en jueves, la primera semana presenta desde los tres últimos días del mes pasado, y los dos primeros del mes actual.
Para calcular las fechas iniciales que se presentarán en cada mes hemos utilizado el rango de celdas B8:B19.
Cada celda contiene una fórmula al estilo de: =FECHA($B$2;COINCIDIR(A19;$A$8:$A$19;0);1)-DIASEM(FECHA($B$2;COINCIDIR(A19;$A$8:$A$19;0);1);3), donde:
- $B$2 es la celda que contiene el año elegido
- COINCIDIR(A19;$A$8:$A$19;0) calcula para cada mes, el número ordinal del mes, aprovechando la celda que contiene su nombre y la matriz que forman las celdas con los nombres de todos los meses.
- La función FECHA($B$2;COINCIDIR(A19;$A$8:$A$19;0);1) calcula por lo tanto el primer día del mes.
- A la fecha calculada le restamos el número ordinal de esa fecha que corresponde al día de la semana, y que calculamos con DIASEM(FECHA($B$2;COINCIDIR(A19;$A$8:$A$19;0);1);3). Así, si el primer día del mes es jueves, le restamos 3 a la fecha del primer día del mes, con lo que conseguimos la primera fecha que debemos incluir en el calendario.
Para componer fácilmente la lista de datos de guardias:
- La celda F4 que es la primera fecha de nuestra tabla contiene una función BUSCARV que localiza, según el mes seleccionado, la fecha inicial correspondiente.
- El rango de celdas F5:F9 lo hemos compuesto con fórmulas simples, que nos han permitido con un simple extender las fórmulas de las 5 celdas seleccionadas en conjunto, componer fácilmente la tabla de fechas necesaria para crear las 6 semanas representadas en cada calendario.
NOTA: En el ejemplo se han coloreado manualmente de forma distinta los grupos de fechas que componen cada día. - Las celdas G4:G8 que contienen las horas han sido rellenadas con un simple Copiar>Pegado especial> Pegar vínculos. De esta forma, si se cambian las definiciones de horas, se cambian automaticamente en esta tabla.
NOTA: En el ejemplo posteriormente se ha eliminado el contenido de algunas de estas celdas a propósito.
Para facilitar la navegación por la lista de datos:
- Hemos hecho clic sobre la celda D4 para a continuación, desde Ventana > Inmovilizar dejar bloqueadas las tres primeras columnas y las 3 primeras filas.
Columnas CLAVE-PROFESORES y CLAVE-SEMANAS
- Utilizando el operador & (también podríamos utilizar la función CONCATENAR) y la función TEXTO hemos creado las claves necesarias que utilizarán posteriormente en los calendarios de las hojas PROFESORES y MES las funciones BUSCARV que encontrarán los nombres de los profesores y qué fecha/hora tienen guardia. Dado que BUSCARV precisa que las columnas en las que se busca sean las primeras de la lista de datos, las hemos situado a la izquierda de la lista de datos Guardias.
- La hoja MES precisa localizar el nombre del profesor asignado a una determinada fecha y hora. Se utilizarán las claves de la segunda columna de esta tabla, que hemos creado con fórmulas del estilo de =TEXTO(F4;"AAMMDD")&TEXTO(G4;"HHMM"), ya que se buscará una fecha/hora.
- La hoja PROFESORES precisa localizar las guardias que tiene un determinado profesor en una determinada fecha y hora. Se utilizarán las claves de la primera columna de esta tabla, que hemos creado con fórmulas del estilo de =E4&H4, que aprovecha la clave creada de fecha/hora de la columna siguiente, añadiendo el nombre del profesor, y evitando con ello que se repita el cálculo.
Para rellenar fácilmente las celdas de los profesores:
- Copia el rango de celdas A23:A30 y con Pegado Especial > Textos en el rango de celdas H14:H153 rellena fácilmente las celdas con los nombres de los profesores. Evidentemente, esto sólo para hacer pruebas
NOTA: En el ejemplo posteriormente se ha eliminado el contenido de algunas de estas celdas a propósito.
La hoja PROFESORES
Esta hoja ha sido preparada para obtener el calendario y horario que se entregará a cada profesor con sus guardias.
La celda A1 ha sido configurada mediante validación de datos con un desplegable para seleccionar fácilmente el nombre del profesor. El origen de esta lista es el rango de celdas A23:A33 correspondientes de la hoja Guardias.
La celda A6 toma la primera fecha posible de la hoja GUARDIAS, situada en F4; el resto de fechas se componen sumando 1 a esa celda (para los martes, miércoles,...) o sumando 7 a esa celda (para el lunes de la semana siguiente, etc.)
El título también lo hemos compuesto utilizando las fechas contenidas en A6 y en M41, así como el título creado en la celda GUARDIAS.D2, utilizando el operador de concatenación &.
Técnicas avanzadas utilizadas en la hoja PROFESORES
Tras seleccionar el nombre del profesor, las celdas B7:B11, E7:E11, etc. buscan si en la lista de datos Guardias de la hoja GUARDIAS se ha dado de alta esa fecha, hora y nombre de profesor; en caso de que la búsqueda sea afirmativa, mostrará la palabra GUARDIA.
Para realizar la búsqueda hemos utilizado en B7 la siguiente fórmula: =SI(ESERROR(BUSCARV(TEXTO(A$6;"AAMMDD")&TEXTO(A7;"HHMM")&$A$1;$GUARDIAS.$D$4:$H$154;4;FALSO()));"";"GUARDIA"), en donde:
- Se han utilizado referencias mixtas y (A$6) y absolutas ($A$1) para facilitar el copiar-pegar la fórmula en las columnas E, H, K y N, así como para extender o copiar-pegar la fórmula en las filas 8 a 11.
- Se ha compuesto la clave a buscar utilizando el año, mes, día (A$6), hora y minuto (A7) de la guardia, y el nombre del profesor ($A$1).
- La clave es utilizada por una función BUSCARV que en la lista de datos Guardias localiza si existe un nombre asignado a esa fecha/hora. Se han utilizado referencias absolutas para definir la tabla de búsqueda ($GUARDIAS.$D$4:$H$154).
- Si la búsqueda no tiene éxito, BUSCARV devuelve un error #N/D; en caso contrario, devolverá el nombre del profesor.
- La función ESERROR nos devolverá VERDADERO en caso de que no haya sido asignada una guardia a ese profesor en esa fecha/hora, o FALSO en caso contrario.
- Esta respuesta es utilizada por la función SI para determinar si tiene que escribir la palabra GUARDIA o no escribir nada ("") en esa celda.
Para que las celdas que contengan la palabra GUARDIA como resultado de la búsqueda se muestren con el fondo de color naranja se ha utilizado formateo condicional. Hemos aplicado a la celda B7 el siguiente formato condicional:
El estilo 05 Guardia fué creado previamente; símplemente indicamos que presentase el fondo de la celda de color naranja.
Una vez hemos preparado la celda B7 podemos ahora copiarla y pegarla o extenderla para completar la hoja, con mínimos cambios.
- Así, para componer la primera semana, la fórmula utilizada en B7 se ha extendido a las celdas B8:B11, para luego copiarlas-pegarlas en E7:E11, H7:H11, etc.
- Para las siguiente semanas se ha pegado la celda copiada en la primera de la semana, y se ha corregido la fórmula; por ejemplo, para hemos corregido la pegada en B14, pues ahora tiene que leer la fecha desde A13; luego se ha extendido y copiado y pegado como hicimos en la primera semana.
- En el resto de semanas se han hecho las mismas correcciones y procedimientos.
Finalmente hemos seleccionado desde la celda A1 hasta la N46, y hemos definido el rango de impresión desde Formato > Imprimir rangos > Definir...
+info sobre referencias relativas, mixtas y absolutas en: Referencias a celdas y rangos en OpenOffice Calc
+info sobre formato avanzado de celdas y formateo condicional en: Formato avanzado de celdas en OpenOffice Calc
La hoja MES
Esta hoja ha sido preparada para obtener el calendario y horario que se colgará en el panel de anuncios de la sala donde se hacen las guardias.
La celda A1 símplemente presenta el título creado en la celda GUARDIAS.D2, utilizando el operador de concatenación &.
La celda A6 toma la primera fecha posible de la hoja GUARDIAS, situada en F4; el resto de fechas se componen sumando 1 a esa celda (para los martes, miércoles,...) o sumando 7 a esa celda (para el lunes de la semana siguiente, etc.), al igual que hicimos anteriormente.
Técnicas avanzadas utilizadas en la hoja MES
Las celdas de la hoja buscan en la lista de datos Guardias las coincidencias con la clave compuesta con la fecha y la hora, y presentan el nombre del profesor asignado.
Para realizar la búsqueda hemos utilizado en E7 la siguiente fórmula: =BUSCARV(TEXTO(D$6;"AAMMDD")&TEXTO(D7;"HHMM");$GUARDIAS.$E$4:$H$154;4;FALSO()), en donde:
- Se han utilizado referencias mixtas y (D$6) para facilitar el copiar-pegar la fórmula en las columnas H, K y N, así como para extender o copiar-pegar la fórmula en las filas 8 a 11.
- Se ha compuesto la clave a buscar utilizando el año, mes, día (D$6), hora y minuto (D7) de la guardia.
- La clave es utilizada por una función BUSCARV que en la lista de datos Guardias localiza el nombre asignado a esa fecha/hora. Se han utilizado referencias absolutas para definir la tabla de búsqueda ($GUARDIAS.$D$4:$H$154).
- Si la búsqueda no tiene éxito, BUSCARV devuelve un error #N/D; en caso contrario, devolverá el nombre del profesor. Sea cual sea el valor devuelto, se presenta en la celda.
Queremos que las celdas que contengan un nombre de profesor como resultado de la búsqueda se muestren con el fondo de color naranja se ha utilizado formateo condicional.
Si por el contrario presentan un mensaje de error, cambiamos el color de la letra al mismo que el color del fondo de la celda: blanco. De esta forma, hacemos invisible el mensaje de error.
Para conseguir este efecto hemos aplicado a la celda E7 el siguiente formato condicional:
Los estilos 06 CeldaConError y 05 Guardia fueron creados previamente.
Una vez hemos preparado la celda E7 podemos ahora copiarla y pegarla o extenderla para completar la hoja, con mínimos cambios.
- Así, para componer la primera semana, la fórmula utilizada en E7 se ha extendido a las celdas E8:E11, para luego copiarlas-pegarlas en H7:H11, K7:K11, etc.
- Para las siguiente semanas se ha pegado la celda copiada en la primera de la semana, y se ha corregido la fórmula; por ejemplo, hemos corregido la pegada en E15, pues ahora tiene que leer la fecha desde A14; luego se ha extendido y copiado y pegado como hicimos en la primera semana.
- En el resto de semanas se han hecho las mismas correcciones y procedimientos.
Finalmente hemos seleccionado desde la celda A1 hasta la N51, y hemos definido el rango de impresión desde Formato > Imprimir rangos > Definir...
Evitar que se modifiquen celdas por error
Para evitar que se modifiquen celdas por error, hemos protegido las hojas, siguiendo el siguiente guión:
- En la hoja MES hemos protegido la hoja completa desde Herramientas > Proteger documento > Hoja... y Aceptar.
- En la hoja PROFESORES la celda A1 ha sido desbloqueada desde Formato > Celdas... > Protección de celda > desmarcar Protegido y Aceptar, para luego proteger la hoja desde Herramientas > Proteger documento > Hoja... y Aceptar.
- En la hoja GUARDIAS los rangos de celdas A2:B2, A23:A33, B28:B33 y G14:H154 han sido desbloqueados desde Formato > Celdas... > Protección de celda > desmarcar Protegido y Aceptar. Recomendamos ocultar las columnas D y E, que nosotros hemos dejado a la vista a propósito. Finalmente, puedes proteger la hoja desde Herramientas > Proteger documento > Hoja... y Aceptar.
+info en Ocultar, mostrar y proteger en OpenOffice Calc
NOTA: Al proteger la hoja puedes opcionalmente indicar una contraseña. Si la olvidas, despídete de poder modificar las celdas protegidas. No será posible.
Imprimir las hojas.
Activa desde Archivo > Vista preliminar. Desde Formato de página se han hecho los ajustes pertinentes para el encabezado y pie de página, y para que cada hoja se imprima ocupando el máximo ancho de la página.
Si desde Ver > Escala activas el zoom en Ajusta de ancho y alto, podrás fácilmente previsualizar las dos páginas.
+info sobre vista preliminar e impresión: Vista preliminar e impresión en OpenOffice Calc
NOTA: Si enviamos a imprimir se imprimirán siempre las dos páginas, una para la hoja de PROFESORES y otra para la de MES. Para imprimir sólo la de PROFESORES, desde Archivo > Imprimir... fuerza a imprimir sólo la página 1
Personalizar el formato de las celdas
Se han definido estilos para las celdas que contienen los títulos de las hojas, los de las semanas, las fechas, y la palabra GUARDIA o el nombre del profesor.
Para modificarlos deberás desproteger previamente todas las hojas y mostrar el panel de estilos y formateo (símplemente, pulsa F11) o actívarlo desde la barra de herramientas lateral.
+info sobre trabajar con estilos en Calc: Estilos de formato de celdas en OpenOffice Calc
Descargar el libro de Calc utilizado en este ejemplo
Puedes descargarte la plantilla o modelo de libro de Calc creado para este ejemplo desde aquí:
Descargar Modelo Horarios y Calendarios de Guardias