#ExcelPedroWave

En el artículo anterior hice un análisis de la plantilla enviada por la usuaria del foro.todoexcel.com solicitando ayuda para calcular automáticamente las ubicaciones de los empleados, según sus capacidades, en los turnos del Servicio de Urgencias de un hospital. Se puede leer en el siguiente enlace:

Turnos Urgencias Hospital Análisis Fase 2

En este artículo pasaré a la siguiente fase del proyecto, intentando aportar algo de valor preparando un prototipo inicial en Excel, que se puede descargar más abajo.

Fase 3 de Ejecución del Proyecto

En la fase 3 de un proyecto de desarrollo de software se pasa a la ejecución del proyecto Turnos Urgencias Hospital, que para mí es la fase más interesante y divertida pues se debe resolver el problema planteado cumpliendo los requisitos definidos en el plan del proyecto.

En esta fase intenté llevar a la práctica el plan del proyecto comentado en la fase 2, aún sabiendo que había preguntas sin responder, que planteé en mis dos primeros mensajes en el tema del foro.

La fase de ejecución en un proyecto con hojas de cálculo Excel es la más entretenida y agradable para mí, ya que puedo construir cualquier proceso o cálculo partiendo de una hoja en blanco, con la ayuda de mis conocimientos, experiencia, imaginación e ingenio, que para eso soy ingeniero, y puedo hacerlo con múltiples herramientas, al ser Excel lo más parecido a una navaja suiza:

1. Editando fórmulas de Excel, que llaman a funciones de Excel con los argumentos apropiados.
2. Empleando la potencia de resumen y metamorfosis de las tablas dinámicas y gráficos dinámicos con origen en tablas de Excel.
3. Usando segmentaciones de datos y escalas de tiempos conectadas con las tablas dinámicas.
4. Aprovechando complementos de Excel, como Solver, para realizar cálculos complejos.
5. Programando macros en Visual Basic for Applications - VBA, creando subrutinas y funciones.
6. Llamando a las API (Interfaz de Programación de Aplicaciones) de Windows para VBA, que son un conjunto de métodos que permiten la interacción directa con el sistema operativo.
7. Obteniendo datos de diversas fuentes de datos con Power Query.
8. Creando un modelo de datos con Power Pivot.
9. Geolocalizando información con Power Map.
10. Mostrando tablas, gráficos, mapas y KPIs con Power View.
11. Generando informes interactivos y dinámicos con Power BI. Esta herramienta es la única que no está incluida en Excel y es una aplicación aparte totalmente gratuita para desarrollar cuadros e informes.

Para este proyecto se aplicarán las 5 primeras herramientas, intentando explicar el por qué y el para qué de cada una de ellas en la resolución de este problema.

Se trata de tener un avance en forma de prototipo de lo que se quiere conseguir: obtener automáticamente las rotaciones en las ubicaciones del Servicio de Urgencias de un hopital para cada turno, de mañana, tarde y noche.

Prototipo inicial

El prototipo inicial, con el primer intento de cálculo de ubicaciones en los turnos, se puede descargar más abajo y contiene 5 hojas:

1. Requisitos: Con el primer mensaje del foro con este tema, con los datos de la ayuda solicitada, indicando el problema y sus limitaciones. Básicamente son los requerimientos del proyecto.
2. CAPACIDADES: Con la tabla de capacidades de cada empleado.
3. TURNOS: Con el cálculo de turnos según capacidades.
4. FEBRERO: Turnos del mes de febrero enviados por la solicitante de ayuda.
5. 03-21: Calendario resumen provisional de turnos para marzo.

No voy a hablar aquí de la hoja 1 con los Requisitos, ni de la hoja 4 con los turnos de FEBRERO, tampoco detallaré las hojas de CAPACIDADES ni la hoja resumen del mes de marzo 03-21, que está a medio construir. Lo importante está en la hoja TURNOS, donde intento hacer el cálculo con el complemento de Excel: Solver.

Hoja 3. TURNOS

Esta hoja de TURNOS está en fase experimental, intentando el cálculo de las ubicaciones de los turnos con el complemento de Excel Solver. La siguiente imagen muestra cómo se ha implementado el cálculo.

Voy a explicar el caso del turno de Noche (marcado con la letra: N), tal y como está filtrada la tabla de la imagen superior. Los turnos de Mañana (M) y Tarde (T) son similares en su ejecución.

Lo primero ha sido crear una tabla denominada TABLA_TURNOS, en el rango B3:Q64, con los 61 empleados fijos a los que se le asignará una ubicación según su capacidad en cada turno de cada día. Al resto de empleados se les ha dejado para cuando encuentre una solución a los empleados fijos.

Las columnas Fila y Personal indican uno de los empleados fijos en cada fila.

Las columnas de CAPACIDADES, en el rango D3:J64, se obtienen de las capacidades de cada empleado y atendiendo a los siguientes requisitos enumerados por Toyce:

• Observación: hay gente fija de allí, que si está de turno, siempre tiene que ponerse allí. Hay otra gente, que no tiene que ir allí nunca y otras personas, que pueden ir a observación si hace falta rellenar, porque no haya personas fijas en el turno.
• Reanimación: no hay gente fija pero sí que hay gente que puede estar allí y gente que de ninguna forma.
• Respiratorio (COVID): hay determinadas personas (pocas), que por problemas médicos, no pueden trabajar allí (en éste caso, tampoco en reanimación)
• CA, CB y SP: puede pasar todo el mundo, salvo las personas fijas de observación.

La columna DE SERVICIO está marcada con un 1 si el empleado está de servicio, en caso contrario se marca con un 0 y a ese empleado no se le asignará un turno.

Las 3 columnas de TURNOS de Mañanas, Tardes y Noches, son las que tiene que calcular el complemento Solver, en el rango L4:N64, con las variables a cambiar que son un total de 3 turnos x 61 empleados = 183 variables a resolver, cerca del límite posible de Solver, por lo que no será posible incrementar el número de empleados con los sustitutos, etc.

La columna TOTAL, en el rango O4:O64, indica si al empleado le toca turno ese día.

La columna TURNO, en el rango P4:P64, traduce el turno a las letras: M; T; N.

La columna CAPACIDAD, en el rango Q4:Q64, está sin resolver aún y servirá para asignar la ubicación a cada empleado.

Otro de los requisitos, enumerados por Toyce, son los puestos de trabajo y número de personas necesarias en cada turno:

• De mañanas, en total 14: 3 en observación, 2 en reanimación, 2 en respiratorio, 2 en CA, 3 en CB, 2 en SP.
• De tardes, en total 15: 3 en observación, 2 en reanimación, 2 en respiratorio, 2 en CA, 3 en CB, 3 en SP.
• De noches, en total 9: 3 en observación, 1 en reanimación, 1 en respiratorio, 1 en CA, 2 en CB, 1 en SP.

Estos requisitos están contemplados en las filas 71 a 81. Ver la siguiente imagen:

En color amarillo de fondo están los datos que se pueden modificar, de acuerdo con los requisitos de asignación de ubicaciones en cada turno. El resto de valores son fórmulas auxiliares para el cálculo en cada turno.

En las filas 67 a 69 se calcula el Total de los 3 turnos. En el rango L67:N68 se definen las restricciones definidas en el complemento Solver para cumplir los requisitos de cada turno. En la fila 67 están los valores fijados por los requisitos y en la fila 68 están los valores de las variables que calculará Solver, que deben coincidir con los valores definidos por el usuario.

Ahora ya tenemos definidos todos los datos que hacen falta para aplicar el complemento Solver.

Complemento Excel: Solver

El motivo por el que hago uso del complemento Solver es porque el problema planteado tiene muchas variables y muchas condiciones, que se pueden traducir por restricciones, y la solución es compleja, puede no ser la óptima e incluso puede no existir una solución al problema para los turnos de un día concreto, en cuyo caso habrá que crear un algoritmo en lenguaje VBA para que las macros salgan al rescate de este proyecto. De paso me sirve para refrescar mis conocimientos del complemento Solver y mostrar un ejemplo de su uso a los visitantes de mi blog.

Lo primero es saber si tenemos activado el complemento Solver para lo que, si no se conoce cómo hacerlo, hay que visitar la siguiente página: 

Carga del complemento Solver

Entre los Complementos disponibles, Solver debe estar chequeado para tenerlo activado, como aparece en la siguiente imagen:

En la sección de Análisis del menú de Datos ahora aparecerá un botón para ejecutar el complemento Solver. Se puede ver a la derecha del todo en la siguiente imagen:

Haciendo clic en el botón Solver aparece la ventana de Parámetros de Solver:

En el siguiente enlace se explica para qué sirven estos parámetros:

Definir y resolver un problema con Solver

Vamos a ver con algo de detalle los parámetros necesarios para el problema de turnos a resolver:

• Establecer objetivo: Se hace referencia a una celda con una fórmula, en este caso la celda nombrada: SOLVER_Valor =TURNOS!$O$69 que contiene la fórmula: =SUMA(L69:N69) 

• Para: Valor de: 3. La suma anterior valdrá 3 cuando coincida el número de ubicaciones de cada turno con las requeridas.

• Cambiando las celdas de variables: $L$4:$N$64 es el rango de las variables a resolver.

• Sujeto a las restricciones: Se han agregado 7 restricciones:
• $L$4:$N$64 <= 1 - Las variables deben ser 1 o 0 al ser entero la siguiente restricción.
• $L$4:$N$64 = entero - Las variables deben ser números enteros.
• $L$67 = $L$68 - Deben coincidir las ubicaciones de mañanas.
• $M$67 = $M$68 - Deben coincidir las ubicaciones de tardes.
• $N$67 = $N$68 - Deben coincidir las ubicaciones de noches.
• $O$4:$O$64 <= 1 - El total de turnos de cada empleado debe ser 1 o 0.
• $O$67 = $O$68 - Debe coincidir la suma de ubicaciones de los 3 turnos.

• Convertir variables sin restricciones en no negativas. Se marca para que las variables sean 0 o positivas.

• Método de resolución: Evolutionary - Se elige este método porque hay muchas restricciones dependientes de fórmulas, por lo que no se puede hacer una estimación lineal ni no lineal suavizada.

A la derecha del método de resolución, hacer clic en el botón de Opciones, seleccionando el panel: Evolutionary que permite definir las opciones de resolución.

Las opciones principales sobre las que actuar son:

• Tamaño de población: Cuanto más alto mejor solución y más tarda en dar una respuesta.
• Tiempo máximo sin mejora: En segundos.

Estas opciones se deben configurar con el método de prueba y error, hasta alcanzar los resultados esperados ya que para las estimaciones no hay un resultado exacto, pueden obtenerse resultados que no son óptimos e incluso, una vez alcanzado el tiempo máximo no conseguir ningún resultado.

Si te has descargado la plantilla, que se encuentra más abajo, ahora puedes ejecutar el Complemento Solver haciendo clic en el botón: Resolver

Al cabo de unos segundos, y de haber intentado resolver más de 1.000 subproblemas, aparece la ventana de Resultados de Solver:

Abajo se lee lo siguiente:

Solver no puede mejorar la solución actual. Se cumplen todas las restricciones.

Este es un buen resultado pues Solver ha conseguido encontrar una solución al problema, aunque no sea la óptima.

Manteniendo chequeado: Conservar solución de Solver y haciendo clic en el botón Aceptar, el conjunto de variables a resolver se copia en el rango definido de variables dentro de la hoja TURNOS.

También hay un botón para Guardar escenario...

También se pueden solicitar Informes de esquema, que serán emitidos en nuevas hojas de cálculo, y que servirán para analizar el método y los resultados obtenidos, cosa que es conveniente hacer al menos una vez, cada vez que se cambien los parámetros de Solver.

Cuando aparezca un mensaje informativo avisando que Solver no encuentra una solución, nos quedan dos intentos, el primero seguir cambiando los parámetros de Solver, el segundo buscar la solución manualmente como se ha hecho habitualmente antes de intentar automatizar el proceso de cálculo.

Para mecanizar el cálculo, y no tener que pasar por los Parámetros de Solver cada vez que hagamos el cálculo, he añadido en la hoja TURNOS un botón: Resolver Turnos, como se puede ver en la siguiente imagen.

Al hacer clic en ese botón, se activa el evento BotónResolverTurnos_Click de la hoja TURNOS que ejecuta la macro: ResolverTurnos

Esta macro se encuentra en el módulo: ModResolverTurnos y borra el rango Turnos_Rotaciones antes de ejecutar el complemento Solver mediante estas dos instrucciones:

SolverOk SetCell:=Range("SOLVER_Valor"),        MaxMinVal:=3, ValueOf:=3, ByChange:= Range("SOLVER_Turnos"), Engine:=3, EngineDesc:="Evolutionary"

SolverSolve

Estas dos funciones del complemento Solver funcionarán únicamente si se hace referencia a Solver en el Proyecto VBA. Para ello:

1. Abre el Editor VBA con las teclas: Alt + F11
2. En el menú Herramientas, selecciona: Referencias...
3. En Referencias disponibles: comprobar que está marcado: Solver
4. Si se produce un error al hacer clic en el botón Resolver Turnos, se debe desmarcar y volver a marcar la referencia a Solver, para que haga referencia a la versión de Excel instalada en el sistema. Ver el AVISO de más abajo.

Ahora aparecerá en el Proyecto una Referencia a SOLVER.XLAM, como muestra la siguiente imagen:

AVISO: En la plantilla descargada la referencia a Solver está tomada de la versión de Excel 2010 por lo que, al abrir la plantilla en otra versión de Excel, por ejemplo en Excel 365, debe cambiarse la referencia a Solver, lo que se hace desmarcando esa referencia y volviendo a marcarla de nuevo, con lo que ya no se producirán errores al ejecutar la macro que lanza el botón: Resolver Turnos

También he añadido un desplegable para elegir el año y otro para elegir el mes, que no están operativos.

Cuando funcione bien el cálculo con Solver, los resultados obtenidos podrán ser copiados a la hoja del mes de marzo: 03-21 mediante una macro a desarrollar...

Hoja 2. CAPACIDADES

Partiendo de la plantilla original, lo primero que hice fue modificar la tabla de CAPACIDADES, en la hoja del mismo nombre, para añadir nombres ficticios del personal del Servicio de Urgencias.

Con ello se tiene el nombre y la identificación del personal en las dos primeras columnas de la tabla y sus capacidades en las demás columnas, marcadas con una letra x.

Hoja 5. Mes 03-21

Esta hoja es un anticipo del resultado que se quiere obtener para el mes de marzo, con los turnos calculados para cada día del mes y está en construcción, por lo que no lo explicaré aquí. Tiene esta apariencia:

Descarga del prototipo

Descarga este prototipo inicial desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Ayuda web rotación puestos trabajo - PW1.xlsm 

Abre el archivo, permite la edición y habilita el contenido y las macros para probar el complemento Solver en el primer intento de resolución inacabado del cálculo de ubicaciones en los turnos del Servicio de Urgencias de un hospital.

Conclusiones

Cronología de los 4 primeros mensajes en el foro.todoexcel.com:

• El viernes 12 de febrero se publicó el tema solicitando ayuda: Urgencias hospital-Rotación puestos trabajo a turnos y con limitaciones
• El lunes 15 de febrero inicié y planifiqué el proyecto y le hice algunas preguntas a Toyce.
• El martes 16 de febrero, por la mañana, comencé la fase 3 de ejecución y le consulté a Toyce un par de dudas.
• El martes 16 de febrero, por la noche, me llegó la contestación de Toyce que decía: "Olvídate del archivo que subí (no sé cómo eliminarlo ahora), porque lo he modificado totalmente."
• El miércoles 17 de febrero, por la mañana, leí lo anterior, abandoné la fase 3 de ejecución que he publicado en este artículo, y decidí no adjuntar este prototipo en el foro (por lo que es la primera vez que lo publico, aunque lo deje sin acabar) y me planteé la continuidad de este proyecto.

Este proyecto continuará en un nuevo artículo, ¿o no?

Lo sabrás si abres el siguiente enlace:

Turnos Urgencias Hospital - Fase de Cambios