Montando horarios con las hojas de cálculo de Google

Imagen de encabezado, personaje sosteniendo hoja de cálculo y horarios en primer plano.
¡Ah, esos benditos horarios de clase previos al inicio de curso! La sal de la vida del coordinador de estudios de cualquier centro de formación. Sin ellos nada sería igual, ¿verdad?

Este artículo habla precisamente de esos (malditos, hasta que se aprueban) horarios. Y de hojas de cálculo de Google, ¡por supuesto!

Pero antes de comenzar te cuento lo que no vas a encontrar aquí, para que no haya malentendidos entre nosotros, y ya si eso tú mismo decides si te quedas conmigo o no durante los próximos minutos. 

Veamos, aquí no hay:
  • Un tinglado mágico que vomite los horarios tras masticar una lista interminable de restricciones de entrada, como hacen con mayor o menor grado de eficacia ciertos generadores de horarios que puedes encontrar por ahí, algunos de código abierto, otros (la mayoría), comerciales.
  • Una plantilla enchufar-y-listo universal.
Puedo sentir a través de esas enormes perturbaciones en la Fuerza tu decepción al otro lado de la pantalla.

Pues vaya, ¿entonces de qué va esto?

Pues va de ver cómo podemos utilizar esas hojas de cálculo de Google de nuestras entretelas para al menos, y como dice la gente moderna, “reducir la fricción” a la hora de resolver el sudoku que suele suponer la creación de horarios. Osea, para morir un poquito menos en el intento gracias a un uso razonablemente astuto de algunas de sus herramientas y funciones.

Y me sigo haciendo preguntas. ¿A quién va dirigido este artículo?

Pues yo diría que a aquellos usuarios de las hojas de cálculo de Google, con un nivel de destreza en su manejo digamos que algo así como intermedio, intrigados por el modo en que otro frikihojacalculante las usa para reducir la complejidad del problema anteriormente descrito.

Como todos sabemos, cualquier horario que se precie consta de clases. ¿Y qué es una clase? Pues (ya sé, con algunos matices) podríamos decir que es la reunión de docente y alumnado en un espacio y tiempo determinados para trabajar la materia impartida en el grupo correspondiente a una determinada especialidad.

Tres conjuntos de entidades / recursos primordiales pues desde un punto de vista organizativo:
  • Grupos
  • Profesorado
  • Espacios
Por espacios me refiero a las aulas físicas, en principio, aunque podría tratarse también, claro está, de espacios virtuales en la medida en que pudieran ser recursos susceptibles de agotamiento.

La aproximación rudimentaria, habitual e inmediata a lo hora de resolver el delicado puzzle de la creación de los horarios del centro, una vez se ha renunciado al uso de programas específicos para su concepción, suele consistir en lanzar impetuosamente el editor de hojas de cálculo para montar una serie de tablas como esta, en las que se representan las clases de cada uno de los grupos del centro.

Hoja de cálculo con horario de grupo.
Un horario de-los-de-toda-la-vida.

Pero claro, interesa tener otras tantas tablitas que muestren de manera consolidada el horario de cada profesor. Y también la ocupación de las aulas, por supuesto. Ambas cosas resultan de extraordinaria utilidad para evitar andar saltando de horario de grupo en horario de grupo para comprobar si determinado profe tiene clase en un momento determinado o si cierta aula está disponible para alguna necesidad que pueda ir surgiendo.

Todo muy obvio hasta aquí, ¿verdad?

Pero el camino suele ser largo y oscuro y alberga horrores como eso de tener que ir copiando y pegando interminables elementos rectangulares de colores de una hoja a otra hasta que las cosas encajan. Y meter la pata está a la orden del día: solapamientos en los horarios de un docente (alguien debería legalizar su clonación), descuadres en el número de horas de clase semanales por asignatura requeridos por la normativa, colisiones en el uso de las aulas... Un infierno, oiga.

Bien, pues por ir concretando, lo que te voy a explicar a continuación es cómo puedes usar las hojas de cálculo de Google para:
  1. Generar automáticamente, a partir de los horarios de grupo como el de la tabla de aquí arriba, los horarios específicos de docentes y aulas.
  2. Introducir mecanismos que te permitan, en cierta medida, manejar el error, reduciendo así en lo posible la probabilidad de liarla pardisima.
Igual con un dibujillo se ve más claro:

Diagrama de la solución que vamos a construir.
De los horarios de grupo a los de docentes y aulas, automáticamente.

Adelanto que la solución que alcanzaremos no va a ser óptima, pero al concluir el artículo estarás en mejor posición para evitar ser devorado por el proceso de preparación de tus horarios anuales. Y tal vez hayas aprendido por el camino una o dos cosas que no sabías sobre hojas de cálculo, que siempre viene bien.

Vamos a por ello.

Horarios chachis

Nos acompañará en el viaje una hoja de cálculo que he preparado con datos de ejemplo llamada Horarios de FP chachis. Puedes abrirla desde ya para ir destripándolo mientras lees lo que viene a continuación.

Icono de Google Sheets.

Esta hoja de cálculo (o libro, como suelen decir los que vienen de Microsoft Excel), consta de varias hojas o pestañas:
  • Configuración. Una hoja con elementos auxiliares que resultarán de utilidad en el resto de hojas, básicamente listas de elementos.
  • DAM1 / DAM2 / DAW1 / DAW2. El meollo del asunto, se trata de las hojas que contienen los horarios de una serie de grupos. Cuatro me han parecido más que suficientes para contarte esta película. En mi caso he escogido —no me preguntes por qué— las especialidades de ciclos formativos de grado superior de Desarrollo de aplicaciones multiplataformaDesarrollo de aplicaciones web (primer y segundo curso).
  • Por profesor. Esta hoja contiene varias tablas amontonadas con los horarios consolidados de cada profe. Se podrían haber dispuesto fácilmente en hojas independientes, pero yo he optado por tenerlos todos reunidos. A tu gusto.
  • Por aula. Misma historia, pero por lo que hace al uso de las aulas.

La hoja auxiliar

Muchos tinglados montados sobre hojas de cálculo se benefician de contar con una hoja auxiliar en la que disponer cosillas que resultan de utilidad para el funcionamiento del conjunto.  

En nuestro caso, usaremos una hoja denominada Configuración.

Hoja auxiliar con listas de elementos.
Listas de elementos en la hoja auxiliar (Configuración).

En ella escribiremos:
  • Los nombres y códigos (iniciales, típicamente) de todos los docentes (columnas A y B).
  • Los números que identifican las aulas del centro (columna D).
Estas listas de elementos alimentarán ciertos intervalos con validación de datos en las hojas DAMx / DAWx. De este modo evitaremos errores al escoger docente y aula de cada asignatura (módulo profesional en el caso de FP).

Para facilitar su selección crearemos tres intervalos con nombre: Profes (Configuración!A3:A), Profes_Ampliado (Configuración!A3:B) y Aulas (Configuración!D3:D).
Intervalos con nombre.
También verás que contamos con una lista que enumera los códigos de cada módulo y especialidad, separados por “/” (columna F), y otra anexa que tan solo contiene la indicación del módulo y, nuevamente, el carácter  delimitador “/” (columna G).

Estas listas se construyen de manera dinámica mediante fórmulas matriciales, con ArrayFormula, a partir de la información que se introducida en las hojas correspondientes a los horarios de cada grupo, que veremos en unos instantes.

Fórmula para generar la lista de módulos diferenciada por grupos (especialidades).
Generación de la lista de módulos diferenciada por grupos (especialidades).

Fórmula para generar la lista de módulos únicos.
Generación de la lista de módulos únicos.

Fíjate, cada una de las fórmulas anteriores dispone de instrucciones claramente identificables para traerse los datos que necesita de cada una de las cuatro hojas que contienen los horarios grupales. ¿Más grupos? Solo tienes que replicarlas.

Utiliza CTRL (o ALT) + INTRO y espacios para darles a tus fórmulas un aspecto más inteligible. Créeme, tu “yo del futuro” te lo agradecerá.

Como puedes apreciar se utilizan nuevamente, esta vez en las fórmulas, los utilísimos intervalos con nombre para apuntar a los rangos de datos situados en las pestañas de los horarios de cada grupo que contienen los códigos de los módulos.


Vale, pero estas listas ¿para qué sirven?

Pues sirven para no tener que andar ajustando manualmente el color de fondo de cada celda en las pestañas de grupos, profesores y aulas, lo que nos ahorrará faena y facilitará el uso de una clave de colores global para destacar las clases de cada módulo de un modo homogéneo.

¿Pero cómo?

Centrémonos en la columna G. Aquí tenemos la lista completa de módulos únicos, sin diferenciar por especialidad. Se han creado tantas reglas de formato condicional a lo alto de la columna G como módulos únicos. El criterio de activación en todas ellas es “el texto comienza por”... el código de cada módulo, seguido del carácter “/”.

Reglas de formato condicional para colorear las celdas en función del nombre del módulo.

Una vez creadas, ya solo tienes que utilizar la herramienta de copiar formato para aplicar todas las reglas definidas, por ejemplo en la celda G3, a cualquier otro intervalo de datos en la hoja de cálculo de una vez.

Animación que muestra cómo se pueden aplicar reglas de formato condicional copiando y pegando el formato.
Reutilizando astutamente el formato condicional a partir de una clave de colores predefinida.

Pueden existir módulos con el mismo nombre en distintas especialidades. Y en ese caso la estrategia adoptada aplicaría el mismo color a todos ellos. Esto tal vez no sea buena idea en las tablas de horarios de docentes y aulas, donde podrían aparecer en celdas más o menos próximas.

Para evitarlo podemos aplicar más formato condicional (¡menudo empacho!) sobre los elementos de la lista de módulos en la columna F, en la que quedan caracterizados por el grupo en el que se imparten, y utilizarla como origen en la copia de formato.  A mí no me ha parecido necesario y por tanto no lo he hecho, pero dicho queda y ahí tienes esa lista por si te apetece usarla.

Por último, y ya para cerrar esta sección, me gustaría comentar que bien podríamos haber dispuesto en esta hoja auxiliar una lista global de módulos formativos, para luego seleccionarlos mediante celdas con reglas de validación, del mismo modo que hemos hecho con aulas y docentes, en las hojas dedicadas a los horarios de cada grupo. No obstante me ha parecido más natural optar por la solución propuesta y registrar manualmente los módulos de cada grupo en sus respectivas hojas.

Los horarios de los grupos

Vamos a echarle ahora un vistazo a la pestaña DAM1. DAM2, DAW1 y DAW2 son idénticas tanto por lo que hace a su estructura como a las fórmulas empleadas.

Hoja de horario grupal.
Horario grupal. Se muestran destacadas las celdas en las que se han aplicado fórmulas.

Veámoslo más de cerca.

Las celdas en la fila 1 y en el intervalo A2:F11 son datos de entrada: especialidad, curso, código, relación de módulos y horas semanales. Nada que objetar por el momento, ¿verdad?

Datos de entrada para la elaboración del horario del grupo.
Algunos datos de entrada.

Los intervalos de datos con encabezados Docente y Aula, justo a su derecha, usan validación de datos para desplegar las listas respectivas, que se generan a partir de los intervalos que ya conocemos en la hoja Configuración, que facilitan la selección de profes y aulas para cada clase.

Listas desplegables para seleccionar docente y aula.
Listas desplegables para seleccionar docente y aula.

Las iniciales de los profesores se obtienen a partir del docente seleccionado para impartir cada módulo (columna G) y de la lista de profesores ampliada, también en la hoja Configuración.

Fórmula para obtener las iniciales del docente.
Fórmula para obtener las iniciales del docente.

El código que se usará para etiquetar de manera unívoca cada una de las franjas horarias semanales de clase se genera concatenando el código de la asignatura, las iniciales del docente y el aula, por ejemplo:

LMI/RW@A3

Los caracteres separadores (/, @) están colocados ahí de manera totalmente deliberada, dado que son necesarios para aplicar formato condicional a las celdas —eso ya lo sabemos— y también para… bueno, para otra cosa que veremos en un rato.

Fórmula para obtener las etiquetas de las clases.
Fórmula para obtener las etiquetas de las clases.

Al igual que ocurría en la hoja Configuración, en ambos casos se usan fórmulas matriciales situadas en la fila de encabezado, que como puedes ver establecen el texto del mismo, para posibilitar el uso de la herramienta Datos → Ordenar intervalos sin romper el cálculo.

Para reflejar que un módulo es impartido por varios docentes y/o en varias aulas simplemente se duplica la fila correspondiente (sin repetir el nº de horas semanales).


Módulos con varios docentes y aulas de impartición.

En la celda E13 se calcula la suma de las horas semanales de cada módulo, pero como esto es trivial me lo salto.

Las celdas en el intervalo en el que se indica el horario semanal en franjas de media hora disponen de validación contra las etiquetas de la columna K. La casilla Mostrar la lista desplegable de la celda está desactivada para ocultar el conocido indicador con forma de triángulo invertido. En mi opinión ocupa un espacio innecesario en la celda y queda bastante feote visualmente cuando se apelotonan muchos de ellos, pero sorprendentemente basta con hacer doble clic sobre cualquier celda para que la lista se despliegue normalmente.

Validación de datos para seleccionar las clases en el horario.
Validación de datos (con mini truco) en la tabla del horario.

Ahora hay que contabilizar las horas impartidas por cada profesor en cada asignatura y las horas totales semanales de cada una de ellas. Las fórmulas se complican un poco, así que reducimos ahora la velocidad.

Necesitamos tres cosas:
  1. Obtener una lista de todas las combinaciones clase / profe que aparezcan en el horario. Sin elementos repetidos, claro está.
  2. Sumar las veces que aparece cada elemento de la lista anterior y dividir entre dos dado que mis franjas son de 30 minutos para obtener el número de horas semanales de cada clase. Resulta inmediato modificar esto en el caso de que necesites franjas de 15 minutos, o en tu caso sean siempre de 60, por mencionar dos situaciones típicas.
  3. Obtener una lista, de nuevo única, de los módulos de la especialidad y calcular las horas totales semanales correspondientes de cada módulo.
Todo esto lo vamos a lograr en las celdas B29, C29, D29 y E29. Y, de postre, aplicaremos algún que otro formato condicional mágico que hará saltar todas las alarmas cuando las horas semanales de algún módulo o las totales del grupo  no cuadren con lo que dice en la normativa.

[1] Obtener la lista semanal de clases

El código o selector que identifica las clases contiene información acerca del módulo, de la asignatura y de la clase. Solo nos interesan los dos primeros elementos. Para quedarnos con ellos usaremos la ma-ra-vi-llo-sa función REGEXEXTRACT.

Ayuda contextual de la función REGEXEXTRACT.

Esta función recibe un primer parámetro, que es la cadena o cadenas de texto de las que queremos extraer algún elemento. Y un segundo que es una expresión absolutamente marciana que representa un patrón de texto contra el que se va a comparar el primer parámetro.  Los que coincidan ¡a la saca! A este patrón es a lo que se le llama expresión regular. Y se construye utilizando un lenguaje de símbolos muy sofisticado con el que se pueden llegar a producir  patrones extremadamente complejos (y potentes).

Este artículo no pretende ser en modo alguno un tutorial sobre el uso de expresiones regulares, pero no puedo dejar pasar la oportunidad de recomendarte que visites regexr.com, una herramienta brutal para familiarizarte con estos engendros superpoderosos. Son adictivos, te lo advierto.

Sí, lo sé, las expresiones regulares son el mejor amigo del amante de las hojas de cálculo y probablemente también el peor enemigo de su cordura. Pero hay algunos trucos rápidos que te puedo contar para que supervitamines tus fórmulas a partir de ahora.

Por ejemplo, el patrón “.+” representa una secuencia de uno o más caracteres. Y este otro “.+@” coincide con una secuencia de uno o más caracteres seguidos del símbolo @.

Además, si encerramos una parte de la expresión regular entre paréntesis, lo que estaremos haciendo es pedirle a la función que nos devuelva solo la parte de la cadena de texto que casa con el patrón en su interior.

Seguro que ahora ya no hace falta que te diga qué hace esta expresión regular, ¿a que no?

(.+)@.+

Te saco de dudas, por si acaso: Lo de arriba devuelve la parte del código de la clase que contiene únicamente información sobre el módulo y el docente. ¡Justo lo que queríamos!

=REGEXEXTRACT("LMI/RW@A3";"(.+)@.+") → LMI/RW

Si aplicamos esta función a todo el intervalo de celdas del horario obtendremos como resultado una estructura matricial de exactamente el mismo número de filas y columnas que el intervalo de datos original. Pero queremos una lista de elementos, es decir, lo que viene a ser una columna.

Eso lo lograremos con la no muy conocida función FLATTEN. Y digo eso de poco conocida porque hasta hace poco no estaba documentada, como cuento aquí.  Esta función combina todos los valores que se le pasan como parámetro en una única columna.

Y el resto es historia… Un poquito de UNIQUE por aquí (que, por cierto, también esconde algún secreto), otro de SORT por allá y un oportuno, aunque no estrictamente necesario,  SI.ERROR… y ¡fórmula finiquitada!

Fórmula para obtener la lista única de clases (módulo y docente) semanales.
Fórmula para obtener la lista única de clases (módulo y docente) semanales.

[2] Calcular las horas semanales de cada clase

Nada que deba causar demasiada extrañeza. Se tira de CONTAR.SI y su carácter comodín (*) para rastrear las veces que aparece cada código de clase en la tabla horaria, código que, recordemos, incluye el identificador del aula como sufijo.

Fórmula para obtener las horas semanales de las clases (módulo / docente).
Fórmula para obtener las horas semanales de las clases (módulo / docente).

[3] Calcular las horas semanales de cada módulo

Primero se obtiene la lista de módulos de la especialidad de manera inmediata.

Fórmula para obtener la lista única de módulos.
Dos viejos conocidos, ArrayFormula y UNIQUE.

Para a continuación contar el número de bloques de 30 minutos y realizar un cálculo análogo al efectuado sobre las clases.

Fórmula para obtener las horas semanales de los módulos formativos.
Fórmula para obtener las horas semanales de los módulos formativos.

Además, en las celdas C38 y E38 se totalizan las horas semanales por clase y módulo, a modo de tranquilizadora comprobación.

Y ya por fin, se obtienen las horas de clase por docente (y nuevamente la suma total), indexando sus iniciales en la lista de horas semanales por clase gracias a tres fórmulas encadenadas.

Fórmula para obtener los nombres de los docentes.
Nombres de los docentes.

Fórmula para obtener las iniciales de los docentes.
[2] Iniciales de los docentes.

Fórmula para obtener  la suma de horas de las clases de cada docente.
[3] Suma de horas de las clases de cada docente.

¿Y qué pasaría si metiéramos la pata a la hora de diseñar el horario y asignamos un número incorrecto de horas semanales a alguno de los módulos? Comprobémoslo.

El color de algunas celdas se modifican tras equivocar el número de horas de algunos módulos.
¡Alarmas visuales en acción!

Algunas de las celdas de la hoja se han encendido como un árbol de Navidad para indicar que las clases de SI e IT1 tienen 30 minutos menos de lo que deberían en el horario semanal.

Esto se ha conseguido aplicando un puñado de reglas de formato condicional a las celdas que aparecen en rojo. En algunas de ellas se compara el valor calculado con la referencia en la celda E13.

Pero me parece más destacable la regla de formato que colorea en rojo la carga horaria semanal de aquellos módulos a los que no se les ha asignado el número correcto de horas. Aquí se usa una fórmula personalizada un poquito más complicada, que opera sobre el intervalo E29:E35.

=E29<>BUSCARV(D29;{$F$3:$F$11\$E$3:$E$11}; 2;FALSO)

Esta fórmula compara el valor calculado para las horas semanales de cada módulo, a partir del horario, con el número de horas que marca la normativa, según está recogido en el intervalo E3:F11.

Dado que BUSCARV solo permite recuperar valores (buscamos el nº de horas) en columnas situadas a la derecha de la primera por la izquierda en el intervalo de búsqueda, es necesario montar al vuelo una matriz que tenga la estructura adecuada, con el código del módulo en su primera columna y las horas en la segunda.

Esto se consigue fácilmente mediante la expresión matricial {$F$3:$F$11\$E$3:$E$11}. Las referencias a ambos rangos deben ser absolutas para que queden fijados y que al aplicar la fórmula de formato condicional sobre cada una de las celdas de E29:E35 se utilice siempre la información contenida en E3:F11.

Tomar este tipo de precauciones cuesta bien poco y ayuda mucho, dado que con ellas puedes dotar de mayor robustez a tus modelos de cálculo. ¡El formato condicional es tu amigo, úsalo!

Los horarios del profesorado

Uno de nuestros objetivos de diseño era que los horarios de los profesores se generasen automáticamente a partir de sus clases en los horarios de cada uno de los grupos en los que tienen docencia.

Corre raudo a la pestaña Por profesor para ver si lo hemos conseguido (spoiler: va a ser que sí ✌️).

Lo que encontrarás en ella es una larga serie de tablas con idéntica estructura, apiladas verticalmente, en las que han aparecido reunidas todas las clases asignadas a cada uno de nuestros docentes en las pestañas de los horarios grupales de DAM1, DAM2, DAW1 y DAW2 que manejamos en este ejemplo. Enseguida veremos cómo, no temas.

Hoja de horarios del profesorado.
Horarios del profesorado. ¡Alguien debería decirle a PB que no curre tanto!

Sobre cada tabla se ha aplicado quirúrgicamente la función de agrupación de filas (Datos → Agrupar filas) de modo que es posible plegar y desplegar convenientemente el horario de clase de cada docente, manteniendo en todo momento visible el número de horas totales impartidas.

Comando de agrupar filas o columnas.

Esta treta facilita además la generación de nuevas tablas horarias para profes adicionales. Pero no adelantemos acontecimientos y destripemos una de estas tablas, por ejemplo, la de JB (Jeff Buckley 🎙). Si es que todas son idénticas.

Comenzamos por la parte superior, donde una lista desplegable, cortesía una vez más de la función de validación de datos, permite seleccionar cómodamente el nombre del docente. Una función BUSCARV que a estas alturas del tinglado ya no merece mayor atención se encarga de recuperar en la celda contigua sus iniciales, que necesitaremos en un momento. Recuerda, hoja Configuración, rangos con nombre, etc. etc... cositas que ya sabemos.

Lista desplegable de selección del profe.
Lista desplegable de selección del profe.

Apreciarás que el horario está dividido en dos secciones, una correspondiente a las clases de la mañana y otra para las de la tarde. Esta decisión de diseño nos permite eludir la necesidad de disponer de una tabla horaria de día completo en las hojas de los horarios de cada grupo, cuando lo habitual es que las clases sean bien de mañana, bien de tarde. Economía de espacio, lo llaman.

Eso sí, esto hará que necesitemos dos fórmulas matriciales, por suerte idénticas en lo esencial, para consolidar las clases de nuestro (amado) Jeff dispersas en múltiples hojas.

La primera fórmula, matricial ella, se encuentra en la celda correspondiente a la clase en la franja horaria de 8:00 a 8:30 del lunes. Claro, justo en la esquina superior izquierda del intervalo de datos que esperamos que rellene, ¡qué previsible!

Fórmula que consolida las clases matutinas del docente.
Consolidación de las clases matutinas del docente.

Esta fórmula consta de tantas secciones, dispuestas en líneas consecutivas, como grupos (hojas) con horarios de mañana tenemos en nuestro sistema. Evidentemente, hay que identificarlos de manera manual, DAM1 y DAM2 en el ejemplo que nos ocupa. Por suerte contamos con nuestros apreciados rangos con nombre.

Cada una de estas secciones hace exactamente lo mismo:

1️⃣ Recorre todas las celdas de cada una de las tablas horarias:

=ArrayFormula(...)

2️⃣ Determina si la clase en cada celda está impartida por nuestro profe. Si es que no, devolverá una cadena vacía...

SI(REGEXEXTRACT(Horario_DAM1;".+/(.+)@.+")=F92;...;"")

3️⃣ ...Y si es que sí, retornará una versión modificada del código utilizado para identificar la clase en el que se han sustituido las iniciales del profesor por el código del grupo y, además, se utilizan paréntesis para indicar el aula utilizada en lugar del símbolo “@”. Atención, usamos aquí lo aprendido sobre expresiones regulares y el operador de concatenación (&) para extraer y unir secuencias de caracteres y montarnos así la cadena de texto resultante como más rabia nos dé.

REGEXEXTRACT(Horario_DAM1;"(.+)/.+") &
 "/DAM1" &
" (" & REGEXEXTRACT(Horario_DAM1;".+@(.+)") & ")"

A nuestra fórmula tan solo le queda concatenar (&) el resultado de cada una de las secciones (líneas) que la conforman para lograr el resultado esperado, que tendrá esta pinta, por ejemplo:

DWC/DAW2 (A9)

¿Y si por error hemos generado un solapamiento de clases? Calma, también podemos con eso... ¡sigue leyendo!

La segunda fórmula, ab-so-lu-ta-men-te análoga, la encontraremos en la celda de la clase de 15:00 a 15:30, también del lunes. Y es igualita, con la salvedad de que en esta ocasión se exploran los horarios de los grupos vespertinos, esto es, las hojas DAW1 y DAW2.

Fórmula que consolida las clases vespertinas del docente.
Consolidación de las clases vespertinas del docente

Solo queda sumar todas las horas, o lo que es lo mismo, contar el número de celdas del horario que no están vacías y dividir entre 2.

Tabla de horas semanales de un docente.
Horas semanales del docente.

De manera análoga, otra fórmula, de trazo grueso esta vez, totaliza las horas de todos los profes en la parte superior de la hoja.

Cálculo de las horas semanales de todo el profesorado.
Horas semanales de todo el profesorado.

¡Ah, y seguro que te has dado cuenta! Se ha reutilizado el mismísimo formato condicional aplicado a las tablas de los horarios de grupo, copiándolo desde la pestaña Configuración.

Reglas de formato condicional en los horarios de los docentes
Reutilizando las reglas de formato condicional en los horarios de los docentes.

¿No ves nada raro en esas reglas de formato condicional, ahí a la derecha? Fíjate en la primera. Te la había escondido en las capturas anteriores para no marear con cosas que no venían a cuento unos cuantos párrafos más atrás.

Una regla de formato condicional adicional.

Esta regla utiliza una fórmula personalizada especialmente diseñada para poner el grito en el cielo (colorear el texto de la celda en rojo) ante otro posible error en los horarios.

¿Qué error?

Muy fácil: Cualquier solapamiento de clases en el horario de uno de nuestros docentes, algo indeseable en tanto no sea posible la clonación profesorial o nuestros ilustres formadores y formadoras carezcan del místico don de la ubicuidad del Hechicero Supremo.

¿Cómo se manifiesta este error?

Comprobémoslo asignando clases simultáneas en DAM1 y DAM2 a nuestra Beth Gibbons (GB) particular los lunes de 13:00 a 14:00. Aunque como es de sobra conocido Beth Gibbons casi, casi roza la divinidad cuando interpreta Roads, por el momento sigue sin ser capaz de impartir dos clases a la vez, que yo sepa.

Profesora con solapamiento de clases.
Profesora con solapamiento de clases.

La fórmula que consolida las clases de Beth encuentra diligentemente que tiene faena tanto en DAM1 (IT1) como en DAM2 (IT2) a la misma hora, de 13:00 a 14:00. Y como consecuencia del modo en que está diseñada (la fórmula, no Beth), muestra las cadenas de texto que representan a ambas clases, concatenadas, en las celdas del horario.

Por su parte, la fórmula personalizada asociada a la regla de formato condicional que nos ocupa se aprovecha de que en estas circunstancias la cadena de texto en el interior de la celda contendrá dos barras (/.../), en lugar de solo una, para cazar el error simplemente contándolas y comprobando si aparecen en número superior a 1.

=CONTAR.SI(ArrayFormula(EXTRAE(B4;SEQUENCE(1;LARGO(B4);1);1));"/")>1

Se usa la sorprendente función SEQUENCE dentro de una expresión matricial para desmenuzar, carácter a carácter, la secuencia de texto y a continuación una función de recuento condicional de lo más normalita para contabilizar el número de barras de marras.

Vale, todo correcto. ¿Pero qué pasa si necesitamos hacer hueco para más profes?

Eso está chupado. Solo tienes que colapsar la última tabla, seleccionar el resto de sus filas visibles, copiarlas y pegarlas al final de la hoja. A continuación escoge a tu nuevo profe usando el desplegable y complácete observando cómo aparecen sus clases, con sus celdas de colorines y tal (si es que ya se las has asignado, claro).

Animación en la que se copia y pega la tabla de un profesor para acomodar uno nuevo en la tabla.
¡Tenemos profesora nueva en el equipo!

¡Venga, vamos a por más!

La ocupación de las aulas

Hemos podido mostrar de manera consolidada, no sin ciertas piruetas, la verdad, los horarios de nuestro profesorado.

Ahora vamos a hacer algo similar con el uso de las aulas. Afortunadamente tenemos a estas alturas  un buen trecho andado y lo que queda ya es solo cosa de aplicar las técnicas descritas en el apartado anterior con un leve, pero certero, giro de guión.

Seguro que la hoja Por aula te va a resultar familiar, aunque en esta ocasión he dispuesto las aulas agrupadas en filas con dos tablas por aquello de que en la variedad está el gusto.

Tablas de clases semanales por aula.
Clases semanales por aula.

¿Qué tiene en común esta hoja con la que recoge los horarios semanales del profesorado? Pues unas cuantas cosas, a saber:
  • Se aplica el mismo formato condicional para colorear las celdas correspondientes a cada día de la semana y franja horaria.
  • Se utilizan las mismas fórmulas para totalizar las horas de clase semanales por aula y en total.
  • Se consolidan las clases impartidas en cada aula utilizando sendas fórmulas matriciales para las sesiones de mañana y de tarde. Las fórmulas utilizadas (en L 8:00 y 15:00) funcionan de un modo idéntico a las que montamos en las tablas horarias del profesorado. La única diferencia es que esta vez la cadena de texto que se construye no tiene como sufijo el aula entre paréntesis sino las iniciales del docente (lógico). Y como muestra un botón sacado de nuestro ilustre profe de programación en Java (aunque también domina otros lenguajes y/o lenguas, incluido el tlhIngan Hol),  Perry Blake: PRG/DAM1 (PB).
Permíteme un par de capturillas de pantalla porque no me quedo tranquilo, aunque ya sé que eres mayorcito y sabes irte a la hoja de cálculo que te he facilitado en los albores de este artículo para apreciar los detalles por ti mismo.

Fórmula para consolidar las clases matutinas por aula.
Consolidación de las clases matutinas por aula.

Fórmula para consolidar las clases vespertinas por aula.
Consolidación de las clases vespertinas por aula.

Y lo cierto es que, aunque note lo creas, no tengo nada más que decir sobre este particular.

Balance, limitaciones y siguientes pasos

A lo largo de este artículo hemos visto cómo sacar provecho de las herramientas características de las hojas de cálculo de Google para hacer más eficiente, y espero que algo menos indoloro para ti, el proceso de confección de horarios.

De un modo más específico:
  • Hemos usado el formato condicional para dotar a nuestros horarios de un aspecto homogéneo y, al mismo tiempo, lanzar alertas visuales cuando la información introducida tiene pinta de ser errónea.
  • Hemos generado cómodas listas desplegables gracias a la función de validación de datos allí donde ha sido oportuno para facilitar la selección de elementos (profesores, aulas, clases), reduciendo así la posibilidad de que se deslicen errores en la introducción de datos.
  • Hemos usado fórmulas, a menudo de tipo matricial y tirando de expresiones regulares, para generar los horarios del profesorado y de las aulas de manera automática a partir de las tablas horarias generales de cada grupo.
  • Hemos recurrido a la definición de intervalos con nombre para simplificar las fórmulas empleadas y la creación de listas desplegables.
  • Hemos utilizado la función de agrupar filas para disponer de vistas colapsables de los horarios del profesorado.
Confío en que este pequeño recorrido haya resultado de tu interés y, tal vez, te haya dotado de algunos recursos que no conocías para hacer un uso más eficiente de esta extraordinaria —y adictiva— herramienta que son las hojas de cálculo de Google.

Pero como la senda de la autocomplacencia conduce a la mediocridad, no podemos concluir sin hablar de los problemas y limitaciones que estas técnicas presentan a la hora de liarse a preparar horarios.

El primero y principal es, evidentemente, su fragilidad.

Resulta relativamente fácil que algo se rompa cuando quien maneja estas hojas de cálculo no es quien las ha creado. Y a veces cuando lo es, también, para qué engañarnos.

Existen elementos dependientes, numerosas partes móviles que pueden desencadenar un error o, lo que es peor, un resultado erróneo que no dé muestras de serlo, cuando son manipuladas de manera incorrecta. Por esta razón resulta vital introducir tests de autodiagnóstico (ese formato condicional) para tratar de que nuestros castillos de fórmulas sean resilientes.

Pero bueno, este es al fin y al cabo un problema característico de las hojas de cálculo, que son seres sensibles y delicados. Su mayor virtud es probablemente también su debilidad más notable.

El segundo problema que acarrea esta forma de hacer las cosas es la escalabilidad.

Como has podido comprobar, algunas fórmulas, como las que generan las listas de módulos o, especialmente, las que consolidan los horarios del profesorado y de las aulas deben ser alimentadas con las tablas horarias de cada grupo. Y a medida que estos se hacen más numerosos, las fórmulas engordan y crecen en complejidad hasta convertirse en artefactos poco manejables, potencialmente monstruosos. 

¡Explosión de complejidad!

Para gestionar esto no queda otra que ser extremadamente pulcro y aseado al construir esas fórmulas: escribirlas en varias líneas, usar la indentación... Todo ayuda, pero inevitablemente llega un momento en que la cosa acaba haciéndose demasiado engorrosa y poco manejable, a pesar de todo el cuidado que se haya puesto para evitarlo.

¿Y cómo de escalable es esto? Bueno, con una versión de esta hoja de cálculo un tanto más sofisticada yo me las apaño para gestionar 12 unidades y un equipo humano de unas 20 personas. Si eso es todo lo que tienes que manejar, probablemente esta aproximación te resulte viable y útil.

En centros más grandes y/o cuando no es posible segmentar la generación de horarios de manera estanca por áreas o niveles de un tamaño manejable, no queda otra que recurrir a soluciones, qué duda cabe, más profesionales.

Y aquí hablamos ya de herramientas de generación de horarios que tiran de algoritmos más o menos inteligentes para, a partir de un conjunto de parámetros de entrada que caracterizan el problema, llegar a los mejores horarios posibles.

Hablamos también de aplicaciones específicas para la gestión académica y administrativa del centro, de almacenar la información en bases de datos (y debes saber que las hojas de cálculo no lo son), de evitar islas de información y, en general, de perseguir una gestión digital de los procesos internos más flexible, automatizada y, en definitiva, inteligente.

La realidad de muchos centros es no obstante testaruda y posiblemente quede aún alejada de todo eso. No hay más remedio entonces que buscar soluciones parcialmente satisfactorias hasta poder alcanzar ese estadio evolutivo de desarrollo tecnológico avanzado.

Pero, como dice la canción, ¡basta de lamentos! Te propongo ahora por dónde seguir, si has llegado hasta aquí.

La continuación natural de todo lo que te he contado tiene que ser la generación, a partir de los horarios que ya tenemos en esas familiares y tranquilizadoras estructuras tabulares denominadas tablas, de eventos en calendarios de Google Calendar.

Estos calendarios pueden publicarse de manera inmediata en la intranet de la organización, como me consta se hace en algún que otro centro que conozco, para facilitar el acceso a la información que contienen. Y así, amigo o amiga, se cierra el círculo de la producción y difusión de contenidos relevantes para los miembros del equipo de un modo automatizado.

Pero para eso tenemos que abandonar la seguridad de las hojas de cálculo y abrazar Apps Script, ese excitante entorno de desarrollo que facilita la automatización de procesos dentro del ecosistema de Google Workspace.

El primer paso para lograr este objetivo bien pudiera consistir en extraer una lista estructurada de clases a partir de las tablas de horarios semanales, como propongo en esta función personalizada para hojas de cálculo.

Extracción de lista de eventos a partir de tabla horaria semanal usando una función personalizada Apps Script.
Primer paso hacia la generación automática de horarios en Google Calendar.

¿Y el segundo paso?

El segundo paso es otra historia y, como me gusta decir, será contada en otra ocasión 😏.




Comentarios