What was my surprise when I found a problem that I did not expect with the
decimal number formats
representing the date and time.
Insignificant thing if you're not very meticulous! Or not?
Displays date and time serial numbers as date values, according to the
type and locale (location) that you specify. Date formats that begin
with an asterisk (*) respond to changes in regional date and time
settings that are specified in Control Panel. Formats without an
asterisk are not affected by Control Panel settings.
What a mess I've gotten myself into! Or not?
Format and value of dates and times
In A2 cell I write the date: 03/20/2021 23:59:58
Its decimal value is displayed with 15 digits: 44275.9999768519
The integer part is the serial number representing the number of days since
January 1, 1900. The integer number corresponds to the day 03/20/2021: 44275
The decimal part is the fraction of a day. The decimal value 0.9999768519 is
equivalent to 23:59:58 in hh:mm:ss format, with hh:hours; mm: minutes; ss:
seconds.
Decimal accuracy
Excel can display the time as a decimal number with 15 digits significant,
as we're going to see by adding up the decimal part of the hours, minutes
and seconds:
it's 23 hours, 59 minutes and 58 seconds as a decimal part of a day.
If you like numbers like me, you'll see that sum with 15 digits
0.999976851851851 being the last figure being 1 in place of a 2. This is
because Excel represents 15 digits but secretly stores 17 significant
digits, what the calculated sum will be:
0.95833333333333333000 it's 23 hours.
0.04097222222222222200 it's 59 minutes.
0.00067129629629629629 it's 58 seconds.
The sum of the previous 3 decimal places with 17 digits of precision is the
decimal:
0.99997685185185184 which is represented as the time 23:59:58 and that is
rounded to 15-digit display: 0.999976851851852
Bill Jelen, one of Excel's top gurus, known worldwide as
MrExcel, wrote an excellent article warning of some of the problems derived from
the internal accuracy of the Excel calculation engine. Access the
following link in English to study it:
On this important issue of Excel's internal accuracy, I will return to talk
below. Keep reading this article to the last one to find out more about the
accuracy of dates.
Case you're interested in it! Or not?
Random dates and times
In A3 cell I write the following formula and drag it down:
=A2+RAND()/100000
What I do is add a very small random value to a date and time, divided by
one hundred thousand, so that the date and time increment is minimal.
In this image you can see the result obtained in the 'Random' sheet,
which you can download below:
We look at the cells marked with red background color, in which we are
calculated the integer part of the date.
Unexpected thing! Or not?
You can verify that the day represented in column A does not match, the
03/21/2021, with day 03/20/2021 in column B, which is equivalent to the
integer part of the date, equal to 44275. This problem occurs 2 times in
this random execution, but can occur several more times.
Undesirable thing! Or not?
Problem with inaccurate dates
When dates are randomly generated, the decimal part of a date close to one,
makes the format of date numbers in Excel overflows, showing the next day,
when the integer part of the serial number of the date corresponds to the
previous day.
Inexplicable thing! Or not?
The following image shows the decimal accuracy of various formats dates that
generate overflow in the 'Dates' sheet.
In red background color are marked the dates shown for those that do not
agrees on its corresponding day, i.e. date serial number which is integer
part . Unforgivable thing! Or not?
you've seen that Excel's calculation engine stores and calculates
decimal places more accurately (17 digits) than the one represented (15
digits). You already knew that! Or not?
With the decimals of the dates the same thing also happens. Dates can have
more decimal places than the dates displayed, which creates a problem when
decimal digits are close to the unit, as we can see with this example:
44275.9999999999 with the serial number of 5 figures 44275, represents the
day 03/20/2021
but shows the day 03/21/2021, due to internal rounding of Excel's
numeric date format.
Dangerous thing! Or not?
Proposed solution for overflowing dates
The solution proposed by Bill Jelen, in his article on the internal accuracy of the
Excel calculation engine, can also be applied to this new problem
with date format.
The problem can be solved by rounding the date down to 5 decimal places,
with this formula:
=ROUNDDOWN([@Date],5) = 44275.99999
with only 10 digits of precision, 5 for the day and another 5 for the
decimal part of the day, which is displayed as the correct date and
time: 03/20/2021 23:59:59 Desired thing! Or not?
Overflow dates template
Download the template I've written this article with since
Microsoft OneDrive, or play with the template embedded in my blog
from here:
Inexplicably the 4 buttons on the bottom right of the embedded Excel have
not responded for a few days, so the file cannot be downloaded with the
Download button. I have filed an issue incident to the
Microsoft Support and and I have commented about in the
Microsoft Excel Tech Community here, but we already know that things at the palace are going slowly...
Truth or not truth?
Finally the buttons respond! It's been 11 days with no response due
to poor OneDrive service with the embedded workbooks into blogs or webs. The
issue has lasted from January 5 to January 16, 2023.
It will happen again? Surely yes!
ATTENTION:Excel Online does not allow recalculating with the F9 key. In
the 'Random' sheet, you can modify the A2 cell and it
will recalculate everything.
Better thing! Or not?
NOTE:
Microsoft Excel is designed around the
IEEE 754 specification, and the IEEE 754 specification has some limitations.
The limitations of this problem and the solution to the problem can be found
in the following article:
I'll soon publish the randomly generated date table and see what motivated
this simple exercise. Curious thing! Or not?
You can now read the blog post that inspired me when I detected the problem
with overflowed dates when I was preparing a macro to randomly fill a
million visits to my blog.
Post in Spanish easily translatable into more than 100 languages with
the Google box at the top left of this post:
Cuál fue mi sorpresa cuando encontré un problema que no me esperaba con
los
formatos de números decimales
que representan la fecha y hora.
¡Cosa insignificante si no eres muy meticuloso! ¿o no?
Mi descubrimiento me dice que las fechas almacenadas como decimales no
siempre se corresponden con las
fechas visualizadas con formato de fecha. ¡Cosa nunca vista! ¿o no?
Muestra los números de serie que representan fechas y horas como
valores de fecha, según el tipo y la configuración regional
(ubicación) especificados. Los formatos de fecha que comienzan con un
asterisco (*) responden a cambios de la configuración regional de
fecha y hora, que se especifican en el Panel de control. Los formatos
sin asterisco no se ven afectados por la configuración del Panel de
control.
¡Vaya lío en el que me he metido! ¿o no?
Formato y valor de fechas y horas
En la celda A2 escribo la fecha: 20/03/2021 23:59:58
Su valor decimal se visualiza con 15 dígitos: 44275,9999768519
La parte entera es el número de serie que representa el número de días
transcurridos desde el 1 de enero de 1900. Al día 20/03/2021 le
corresponde el número entero: 44275
La parte decimal es la fracción de un día. El valor decimal
0,9999768519 equivale a 23:59:58 en formato hh:mm:ss, siendo hh:
horas; mm: minutos; ss: segundos.
Precisión decimal
Excel puede mostrar la hora como un número decimal con 15 dígitos
significativos, como vamos a comprobar sumando la parte decimal de las
horas, los minutos y los segundos:
0,958333333333333 = 23/24 son 23 horas.
0,0409722222222222 = 59/60/24 son 59 minutos.
0,000671296296296296 = 58/60/60/24 son 58 segundos.
son 23 horas, 59 minutos y 58 segundos como parte decimal de un día.
Si te gustan los números como a mí, verás que esa suma con 15 dígitos
significativos sería de 0,999976851851851 siendo la última cifra un 1 en
lugar de un 2. Esto se debe a que Excel representa 15 dígitos
significativos pero almacena secretamente 17 dígitos significativos, por
lo que la suma calculada será:
0,95833333333333333000 como 23 horas.
0,04097222222222222200 como 59 minutos.
0,00067129629629629629 como 58 segundos.
La suma de los 3 decimales anteriores con 17 dígitos de precisión es el
decimal:
0,99997685185185184 que se representa como la hora 23:59:58 y que se
visualiza redondeado a 15 dígitos decimales: 0,999976851851852
Bill Jelen, uno de los máximos gurús de Excel, conocido mundialmente como
MrExcel, escribió un excelente artículo avisando de alguno de los problemas
derivados de la precisión interna del motor de cálculo de Excel.
Accede al siguiente enlace en inglés para estudiarlo:
Sobre este importante asunto de la precisión interna de Excel volveré a
hablar más abajo. Sigue leyendo este artículo hasta el final para saber
más sobre la precisión de las fechas.
¡Caso de que te interese! ¿o no?
Fechas y horas aleatorias
En la celda A3 escribo la siguiente fórmula y la arrastro hacia abajo:
=A2+ALEATORIO()/100000
Lo que hago es sumar a una fecha y hora un valor aleatorio muy pequeño,
dividido por cien mil, para que el incremento de fecha y hora sea mínimo.
En esta imagen puedes ver el resultado obtenido en la hoja
'Aleatorias', que puedes descargar más abajo:
Nos fijamos en las celdas marcadas con color de fondo rojo, en las que se
ha calculado la parte entera de la fecha.
¡Cosa inesperada! ¿o no?
Se puede comprobar que no coincide el día representado en la columna A, el
21/03/2021, con el día 20/03/2021 de la columna B, que equivale a la parte
entera de la fecha, igual a 44275. Este problema ocurre 2 veces en esta
ejecución aleatoria, pero puede ocurrir varias veces más.
¡Cosa indeseable! ¿o no?
Problema con fechas imprecisas
Cuando se generan fechas aleatoriamente, la parte decimal de una fecha
próxima a uno, hace que el formato de números de fecha en Excel se
desborde, mostrando el día siguiente, cuando la parte entera del número de
serie de fecha se corresponde con el día anterior.
¡Cosa inexplicable! ¿o no?
En la siguiente imagen, de la hoja 'Fechas', se aprecia la
precisión decimal de varios formatos de fechas que generan desbordamiento.
En color de fondo rojo están marcadas las fechas mostradas para las que no
coincide su día correspondiente, o sea su número de serie que es su parte
entera. ¡Cosa imperdonable! ¿o no?
habrás visto que el motor de cálculo de Excel almacena y calcula
los decimales con más precisión (17 dígitos) que la representada (15
dígitos). ¡Cosa que ya sabías! ¿o no?
Con los decimales de las fechas también pasa lo mismo. Las fechas
almacenadas pueden tener más decimales que las fechas mostradas, lo que
genera un problema cuando los dígitos decimales están próximos a la
unidad, como podemos comprobar con este ejemplo:
44275,9999999999 con el número de serie de 5 cifras 44275, representa el
día 20-03-2021
pero muestra el día 21/03/2021 00:00:00, debido al redondeo interno
del formato numérico de fechas de Excel.
¡Cosa peligrosa! ¿o no?
Solución propuesta para fechas desbordadas
La solución propuesta por Bill Jelen, en su artículo sobre la precisión interna del
motor de cálculo de Excel, se puede aplicar también a este nuevo
problema con el formato de fechas.
Se consigue resolver el problema redondeando la fecha hacia abajo a 5
decimales, con esta fórmula:
=REDONDEAR.MENOS([@Fecha];5) = 44275,99999
con sólo 10 dígitos de precisión, 5 para el día y otros 5 para la parte
decimal del día, que se visualiza como la fecha y hora
correctas: 20/03/2021 23:59:59 ¡Cosa deseada! ¿o no?
Plantilla de fechas desbordadas
Descarga la plantilla con la que he escrito este artículo desde
Microsoft OneDrive, o juega con la plantilla incrustada en mi blog
desde aquí:
ATENCIÓN:Excel Online no permite recalcular con la tecla F9. En la
hoja 'Aleatorias' se puede modificar la celda A2 y se
recalculará todo. ¡Cosa mejorable! ¿o no?
Próximamente publicaré la tabla de fechas generadas aleatoriamente y verás
qué motivó este simple ejercicio. ¡Cosa curiosa! ¿o no?
Ya puedes leer el artículo que me sirvió de inspiración al detectar el
problema con las fechas desbordadas cuando estaba preparando una macro
para rellenar aleatoriamente un millón de visitas a mi blog:
Realmente no existe una fase de cambios en un proyecto, pero los cambios
en los requisitos se producen continuamente durante todas las fases de un
proyecto mientras dura su desarrollo.
Todos los que nos dedicamos al desarrollo de software nos encontramos con
cambios indeseados en las especificaciones de los proyectos, que los
clientes o usuarios finales quieren introducir en cualquier momento para
intentar solucionar su problema (o para crear nuevos problemas) a lo que,
si queremos continuar con el proyecto, habrá que dar cumplida respuesta en
tiempo y forma, para intentar que los planes y plazos del proyecto se
cumplan o, lo que es más normal, para avisar cuanto antes que se va a
tener que retrasar la entrega del proyecto si no se incrementan el
presupuesto y los recursos, estando incluidos los esfuerzos
extraordinarios no recompensados al equipo de desarrollo.
Son decisiones que hay que tomar de manera conjunta y consensuada entre el
cliente, el jefe del proyecto y los equipos de desarrollo. Cualquier
cambio de los requisitos, o modificación de los requerimientos, obliga a
repensar el proyecto.
Como el proyecto para resolver las ubicaciones en los turnos del
Servicio de Urgencias de un hospital es un proyecto abierto a las
necesidades del usuario, ocurrió esto mismo cuando recibí el siguiente
mensaje en el foro de la solicitante de ayuda, en respuesta a mis dudas.
Puedes leer el mensaje completo de Toyce en el foro con este tema::
3- Olvídate del archivo que subí (no sé cómo eliminarlo ahora),
porque lo he modificado totalmente.
He creado una base de datos, con ayuda de un vídeo de youtube, que
creo que sirve mucho mejor y facilitará las cosas, aunque ya no sé
seguir.
Solapamiento de las fases en la gestión de proyectos
Ningún proyecto de desarrollo software se parece, y eso es lo que hace
interesante dedicarse a la programación. En lo que sí se parecen todos los
proyectos es en que siempre hay cambios en las especificaciones técnicas,
lo que obliga a actualizar el diseño funcional y el diseño técnico durante
las fases de desarrollo del proyecto.
Lo normal es tener que hacer ajustes en la planificación cuando ya se ha
comenzado con la ejecución del proyecto. Si hay un contrato de por medio,
cualquier cambio posible estará planificado de antemano, si no es así,
habrá que negociar la repercusión de los cambios, como es el caso de este
proyecto.
Las fases de un proyecto se pueden y se deben solapar para hacerlo ágil y
para que el objetivo se cumpla en plazo y forma, como se puede ver en la
siguiente imagen:
Comprobamos en la imagen que las
Fases de Ejecución y de Control siempre se solapan, pues es
imprescindible probar los prototipos mientras se están desarrollando. Al
tema de las pruebas a realizar para controlar el proyecto le dedicaré un
artículo aparte...
Cambios solicitados
Ya había pasado por las fases de Inicio, de Planificación y había
comenzado la fase de Ejecución cuando me llegó el mensaje anterior,
que me obligaba a replantearme la continuidad del proyecto, o volver a
replanificarlo todo, perdiendo todo el tiempo dedicado a la planificación
y ejecución anterior. Como comprenderás querido lector o lectora, tengo
derecho a quejarme amargamente por haber cambiado las reglas del juego a
mitad de la partida.
La solicitante de la ayuda quería que empezara de nuevo, cuando ya había
dedicado dos mañanas a preparar un prototipo preliminar con el complemento
Solver de Excel, que aún no había enviado, y además
Toyce decía esto:
2- Ten en cuenta que en esto de Excel (y en general, en todo lo
relacionado con la informática) soy totalmente autodidacta.
Dicho esto, de Excel sé hacer lo justo, así que eso de "SOLVER" no
tengo ni idea de qué es, pero lo investigaré. Por eso, lo primero,
pedir perdón si lo que necesito no son macros, pero es que mirando
por el foro vi que hacían cosas parecidas a lo que yo necesito.
3- Olvídate del archivo que subí (no sé cómo eliminarlo ahora),
porque lo he modificado totalmente.
He creado una base de datos, con ayuda de un vídeo de YouTube, que
creo que sirve mucho mejor y facilitará las cosas, aunque ya no sé
seguir.
Y en su mensaje explicaba detalladamente los nuevos requisitos a cumplir
en el proyecto.
Mi idea inicial fue dejar el proyecto y entregarle el prototipo inicial
que ya había hecho en la Fase 3 de Ejecución, pero por otro lado
comprendí enseguida que los cambios eran interesantes ¡y profundos! y yo
podía ayudar a llevarlos a cabo. Para colmo escribió esto:
Por cierto, perdona que no te he dicho nada antes pero no sabes
cuánto te agradezco tu ayuda. Lo consigamos hacer o no, mil
gracias!!!
Sobre todo de mi parte, pero también de parte de todas mis
compañeras.
Gracias, gracias y gracias!!!
Respuesta a los cambios
Al leer este mensaje ya no tuve dudas de que tenía que continuar con el
proyecto, pues iba a intentar ayudar a un colectivo esencial, muy
agradecido por cualquier ayuda que reciba de la sociedad (pues creo
firmemente que no hacemos lo suficiente para intentar
no colapsar la Sanidad Pública, procurando no contagiar ni
contagiarnos del coronavirus y poniéndonos las vacunas que nos
correspondan contra el Covid-19 en cuanto nos toque).
Al menos con este proyecto intentaremos poner un poco de orden en los
turnos de Urgencias de un hospital, por lo que le respondí así:
Como has cambiado las premisas, todo lo que he hecho hasta ahora no
vale para nada y tengo que volver a analizar tu problema y ver si
puedo resolverlo con el nuevo enunciado.
Al cambiar el archivo original deberías haber creado otro hilo
nuevo de mensajes. Para los analistas de datos no hay nada peor que
intentar resolver un problema y que te cambien sus premisas cuando
ya tienes una posible solución. Es como estar haciendo un examen y
que te cambien las preguntas a mitad del examen.
Y le plantee mis dudas para poder continuar y, para no perder el día
esperando respuesta, volví a planificar de nuevo el proyecto, analizando
la nueva plantilla que Toyce me había enviado, y pasé a otra
Fase de Ejecución distinta y nueva, que describo en el
siguiente artículo en este enlace:
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:
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:
Mostrando tablas, gráficos, mapas y KPIs con
Power View.
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:
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.
CAPACIDADES: Con la tabla de capacidades de cada empleado.
TURNOS: Con el cálculo de turnos según capacidades.
FEBRERO: Turnos del mes de febrero enviados por la solicitante
de ayuda.
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:
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:
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:
Estas dos funciones del complemento Solver funcionarán únicamente si se
hace
referencia a Solver en el Proyecto VBA. Para ello:
Abre el Editor VBA con las teclas: Alt + F11
En el menú Herramientas, selecciona: Referencias...
En Referencias disponibles: comprobar que está marcado:
Solver
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:
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.
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?
Para acometer la planificación de la Fase 2 del proyecto
Turnos Urgencias Hospital tuve que analizar la plantilla que envió
la solicitante de ayuda, y este análisis es el que describiré en este
artículo.
Si quieres entender mejor la teoría para hacer un buen
análisis y planificación de un proyecto de desarrollo software, te
remito a Internet, por ejemplo al siguiente enlace:
En el blog he escrito dos artículos hablando del proyecto de
cálculo de turnos y ubicaciones en el Servicio de Urgencias de un
hospital en tiempos de pandemia. Los artículos están dedicados a las dos primeras fases del proyecto
Turnos Urgencias Hospital:
En la Fase 1 tomé la decisión de iniciar este proyecto porque
merece la pena prestar ayuda desinteresada a los trabajadores esenciales
de la sanidad que están luchando en primera línea contra la
Covid-19
y qué mejor que automatizar en Excel algunas de sus tareas para que
dediquen su tiempo a curar y salvar vidas.
En la Fase 2 planifiqué los objetivos y el plan del proyecto, su
alcance y los recursos necesarios, fundamentalmente tiempo y dedicación de
un autojubilado con conocimientos de Excel.
El objetivo principal es automatizar la rotación de las ubicaciones
en cada turno, usando fórmulas de Excel, macros VBA o el complemento
Solver.
En cada hito se entregará un prototipo funcional con el
problema parcialmente resuelto. Ese prototipo lo valorará la solicitante
de ayuda para corregirlo y mejorarlo en la siguiente entrega hasta obtener
la solución deseada.
Análisis
Antes de pasar a la fase 3 de ejecución del proyecto, hay que
analizar la plantilla original recibida en el primer mensaje solicitando
ayuda.
Como esta plantilla está en el
foro.todoexcel.com, quien quiera descargarla tendrá que registrarse en ese foro, accediendo
a este enlace:
Esta figura está sacada de la plantilla, con la hoja de turnos de cada
empleado por día del mes.
Análisis de la plantilla
Para poder planificar este proyecto tuve que hacer un análisis de la
plantilla:
Ayuda web rotación puestos trabajo.xlsm
Su extensión .xlsm indica que contiene macros. Como el remitente es
desconocido, hay que abrir el archivo sin habilitar su contenido para que
no se ejecuten macros malintencionadas.
Abrimos el editor Visual Basic (VBA) con las teclas Alt + F11.
En el Módulo1 hay una subrutina ASIGNA sin ningún comentario en la
macro, cosa poco profesional, pero con la primera instrucción creando una
nueva Colección, cosa fuera del alcance de un usuario novato de macros y,
por supuesto, fuera de la grabadora de macros.
Hace referencia al rango de CAPACIDADES, pero no está claro el
rango de operación. Usa funciones aleatorias para identificar a los
empleados con las capacidades necesarias e intenta que sean únicos en cada
rotación. En la última instrucción copia la matriz de resultados obtenidos
en el rando de operación.
Como no parece haber macros peligrosas, nada más que para los datos que
sobreescriba, se puede volver a abrir el archivo habilitando el contenido
y las macros.
Seleccionando la hoja de FEBRERO y ejecutando la macro ASIGNA desaparecen
los días y los turnos de todos los empleados. ¡No asigna turnos ni
ubicaciones!
Análisis de las hojas
Hay 3 hojas en el libro. La tercera hoja está vacía. La primera hoja
contiene una tabla con las Capacidades según puesto, con la columna
1 con el número de empleado y en las demás columnas sus capacidades,
marcadas con una letra x, como se aprecia en la siguiente figura:
La segunda hoja contiene los
turnos de cada empleado en el mes de febrero, con una columna para
cada día del mes. Marca si el turno es M de Mañana, T de Tarde y N de
Noche. Cuando un empleado no puede acudir a su puesto por incapacidad
temporal lo marca como MIT, TIT o NIT, y ese día no se pueden asignar
ubicaciones a ese empleado. Si hay un signo de punto como M., T. o N.
quiere decir que tiene jornada reducida y el resto de jornada lo hará otro
empleado...
Esta hoja de febrero no tiene formatos condicionales para colorear celdas
ni las columnas de sábado y domingo que aparecen con dos colores de
relleno distintos. Tampoco hay ninguna fórmula ya que buscando con "Ir a Especial..." y seleccionando "Celdas con fórmulas", no se encuentran celdas
con fórmulas. Tampoco hay fórmulas en la hoja con las Capacidades.
Una vez analizadas las dos hojas de esta plantilla, se comprueba que se
deben rellenar manualmente, tanto para añadir puestos de empleados, como
para añadir o actualizar sus capacidades y también para indicar sus turnos
de trabajo cada día del mes.
Análisis de la macro original
Lo raro es que siendo tan elemental la plantilla, y sin ninguna fórmula,
tenga una macro de nivel experto. Por lo que me dediqué a indagar de dónde
había salido esa macro que no se corresponde con la plantilla de turnos.
Al final del tema solicitando ayuda, hay 4 temas similares, por lo
que investigando cada uno, encuentro que el tema "Macro rotación de puestos de trabajo - NO CUADRANTE-" contiene una plantilla con la misma macro que buscaba:
DISTRIBUCION.xlsm del usuario Goldfinger, en el siguiente enlace:
Esa macro tiene una hoja de OPERACIONES con un botón llamado
"Calcula" que obtiene empleados para rotar los puestos cada 2 horas
en una jornada de 8 horas. Lo que no consigue es que en un turno de 2
horas los empleados sean únicos, ya que alguna vez repite el mismo nombre
y, lo deja en blanco cuando no hay ningún empleado para un puesto con esa
capacidad.
Conclusiones del análisis
Toyce, la usuaria que pedía ayuda para
obtener las ubicaciones en los turnos del Servicio de Urgencias de un
hospital, hizo lo que hace un usuario principiante de Excel, copiar y pegar
código de Internet sin saber si es lo que necesita para resolver un
problema y, sobre todo, sin saber cómo adaptarlo a su problema particular.
No sólo eso, compruebo que la plantilla que envió Toyce está creada
a partir de la plantilla de Goldfinger, pues en las
propiedades de los dos archivos su contenido fue creado el mismo día: 19-12-2016, cuando Goldfinger respondió a otra consulta del foro. La
aportación de Toyce fue modificar las capacidades originales y
añadir la hoja de turnos de febrero del Servicio de Urgencias hospitalarias.
Compruebo que hay mucho por hacer para automatizar esta plantilla, pues la
macro suministrada no tiene nada que ver con el cálculo a realizar y no
hay ningún tratamiento de datos en la plantilla original.
Vídeo con el análisis
Puedes ver el análisis descrito en el siguiente vídeo que he publicado en
YouTube:
En el próximo vídeo pasaré a la fase 3 de ejecución de este
proyecto, intentando resolver este problema aplicando el complemento de
Excel: Solver
Atentos a las siguientas entregas de este proyecto, que creo que puede ser
interesante e instructivo y puede prestar un buen servicio a los
sanitarios de este país.
Si no lo has leído, es conveniente comenzar por leerlo antes de continuar.
En esta imagen se muestra la hoja de
Capacidades según puesto enviada por Toyce, la solicitante
de ayuda:
La primera columna de esta tabla es un número que representa los nombres
ficticios de cada empleado y cada una de las otras columnas son las
capacidades reconocidas para cada empleado, para poder ubicarlo en los
turnos según sus capacidades, marcadas con una letra x.
Parece ser que con una hoja de turnos de mañana, tarde y noche por día y
mes, en cada cambio de turno de los empleados, a los que se incorporan en
el nuevo turno se le asigna manualmente una ubicación según sus
capacidades personales, hasta cubrir todas las ubicaciones de ese turno en
el Servicio de Urgencias, lo que nadie sabe como organizar...
Fase 2 del Proyecto Turnos Urgencias Hospital
Al haber tomado la decisión de iniciar el proyecto en la Fase 1, estamos
en la
Fase 2: Planificación
En esta fase se hace un análisis más detallado del proyecto iniciado con
el fin de:
Determinar los objetivos del proyecto.
Definir el alcance esperado del proyecto.
Calcular los recursos que se dedicarán al proyecto: tiempo, personal,
presupuesto…
Redactar el plan del proyecto.
A estas dos primeras fases suelen dedicarse pocos recursos, y aún se está
decidiendo si el proyecto será viable o no. Sólo se pasará a la fase 3 si
durante la planificación se estima oporturno, en caso contrario se puede
abandonar el proyecto en esta fase, sin grandes pérdidas, únicamente el
tiempo dedicado al inicio y la planificación del proyecto.
A mí me gusta planificar en sentido inverso, comenzando por el plan del
proyecto.
Plan del proyecto Turnos Urgencias Hospital
Un buen plan de proyecto comienza por responder lo más verazmente a estas
cuestiones:
¿Qué se quiere obtener?
Se quieren determinar automáticamente las rotaciones en las ubicaciones
del Servicio de Urgencias de un hopital de los
Técnicos en Cuidados Auxiliares de Enfermería - TCAE para cada turno, de mañana, tarde y noche.
¿Por qué?
Porque cada día cambian de puesto de trabajo de forma personal con un
montón de condiciones que nadie sabe como organizar manualmente, y menos
de forma automática si no se conoce Excel a nivel experto.
¿Qué necesidades hay?
La necesidad de automatizar el proceso de rotación de las ubicaciones en
cada turno, con macros VBA o con el complemento de Excel:
Solver.
¿Qué dudas hay antes de empezar?
Asegurar que las limitaciones iniciales no cambian más adelante,
consultando a Toyce, la solicitante de ayuda, que debe responder
a las siguientes dudas:
¿Por qué las personas fijas de observación tienen capacidades para las
que no deben pasar?
¿Qué diferencia hay entre lss capacidades: Observación y Opción Obs?
¿Qué jornada tienen?
¿Cómo se distribuyen los turnos de mañana, tarde y noche?
¿Quién hace fines de semana?
Todas estas preguntas las hice en dos mensajes en el foro donde se planteó
el problema:
Automatizar la plantilla enviada por la solicitante de ayuda.
Generar automáticamente las rotaciones semanales de las ubicaciones en
cada turno.
Las tareas del proyecto serán la programación de fórmulas en Excel, de
macros en VBA y del complemento de Excel
Solver, para obtener varios prototipos que serán probados por el usuario final
hasta su aceptación como solución al problema encargado.
Cada prototipo entregado será un hito del desarrollo del proyecto,
planificado con
metodología ágil, lo que permitirá ajustar las soluciones propuestas en cada prototipo a
los requisitos y necesidades planteados en cada entrega. Después de cada
fase se decidirá si es la solución final o si se continua con el proyecto.
No se pasará a la siguiente fase hasta que el usuario final haga pruebas
del prototipo y mandé un informe de los aciertos, fallos y mejoras
propuestas para la siguiente fase.
Alcance esperado del proyecto Turnos Urgencias Hospital
Entregar un prototipo funcional en la fase final con la rotación de
ubicaciones automatizada para cada turno en un período semanal.
Obtener información de primera mano de cómo se organiza el Servicio de
Urgencias de un hospital para poder desarrollar futuros proyectos.
Recursos invertidos en el proyecto Turnos Urgencias Hospital
Esta aspecto es el que menos me preocupa por lo siguiente:
Personal: Soy el único que se va a dedicar a este proyecto y
únicamente tengo que coordinarme con la solicitante de la ayuda. Tengo
experiencia demostrada en ponerme grandes retos resueltos en Excel, como
se puede apreciar si lees mi blog.
Tiempo: Estoy autojubilado y asumo este proyecto con la principal
intención de ayudar con mis conocimientos de Excel y VBA. La intención
secundaria es que me sirva de inspiración para escribir estos artículos
en mi blog. El tiempo dedicado no es ningún problema, ya que lo invierto
en mi entretenimiento preferido: las hojas de cálculo... ¿Quién puede
decir lo mismo en un proyecto profesional?
Presupuesto: La parte económica se puede valorar en el desgaste
de material informático, la electricidad consumida, los consumos de
datos de la fibra compartida con la familia y la parte proporcional de
inversión en la suscripción anual a MS Office 365. Todos esos gastos son
perfectamente asumibles ya que, si no me dedicara a este proyecto, me
dedicaría de manera altruista a cualquier otro que tuviera
que ver con Excel.
Conclusiones
La comunicación con el cliente, en este caso la solicitante de ayuda, será
a través del foro en el siguiente enlace:
Para descargar la plantilla Excel, que adjuntó Toyce a su
mensaje, deberás registrarte en el foro:
https://foro.todoexcel.com/
ya que ese archivo no es de mi propiedad.
Para hacernos una idea del galimatías que se maneja para gestionar los
turnos del Servicio de Urgencias, la hoja de cálculo con los turnos de
febrero tiene esta apariencia informal:
En el siguiente artículo explico al análisis de la plantilla enviada por Toyce, con el que he podido planificar este proyecto, en el siguiente enlace:
Hipotenusas de cuaternas pitagóricas
-
Una cuaterna pitagórica es la expresión algebraica del teorema de Pitágoras
en tres dimensiones, es decir, un conjunto de cuatro números enteros
positivo...
Cómo acelerar hojas de cálculo Excel grandes
-
Técnicas para optimizar grandes hojas de cálculo Excel Autor: Álvaro Marín
Es para mí un auténtico placer colaborar con el blog de Yolanda, y hablar
en E...
Let, Lambda, and py_Eval
-
I have covered the new Excel Let and Lambda functions before at: Two New
Functions; Let and Lambda Eval and Let examples Lambda and VBA The Lambda
function...
Avoid These Power Query Pitfalls with Simple Steps
-
Power Query is an extremely powerful tool for efficient data analysis and
decision-making processes. However, even the most experienced among us can
someti...
Conversor PGN de ajedrez mejorado
-
🔝*Select the language of this blog post in the Google box at the top left.
*
Esta es la *4ª versión del Conversor PGN de ajedrez* que hago, cada uno ...
What's New in Excel (March 2024)
-
Welcome to our March 2024 update.
This month, export to CSV and show details in PivotTables are now available
in Excel for the web. Also, creating Power...
TikTok’s search evolution
-
2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are
more likely to rely on TikTok than Google as a search engine. More than
half of...
Data Validation Selection Popup Tool – Contextures
-
With the free Data Validation Select Popup (DVSP) tool from Contextures,
you can add popup lists to Excel workbooks. Set the rules you’d like for
the pop-u...
Interés compuesto con Excel
-
En la imagen, se presenta un esquema de las distintas funciones Excel para
calcular el valor del dinero en el tiempo mediante la capitalización
compuesta...
Unblocking and Enabling Macros
-
When Windows detects that a file has come from a computer other than the
one you're using, it marks the file as coming from the web, and blocks the
file....
-
Aprendiendo Unreal Engine 5. ¿Por dónde empiezo?
Desde la presentación de la Comunidad Oficial de desarrolladores de Unreal
Engine de Málaga, he recibido m...
Navigating Outlook Favorites
-
I have these four favorites defined in Outlook: From the inbox, I could hit
Shift+F6 to get into the Favorites area but sometimes I would end up in no
man’...
Office Scripts: Trabajando con Tablas
-
[image: Office Scripts: Trabajando con Tablas]
Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño
vicio)... pero hay que liberar l...
Progress on the Block Protocol
-
Since the 1990s, the web has been a publishing place for human-readable
documents. Documents published on the web are in HTML. HTML has a little
bit of… Re...
London Excel Meetup Workbooks
-
The workbooks used in my presentation on “Analytical and Interactive
Dashboards in Excel” at the London Excel Meetup, September 3, 2020
Dimensiones-Propiedades de perfiles de acero (IMCA)
-
Saludos compañeros, en esta oportunidad compartimos esta Planilla Excel que
contiene las Propiedades y dimensiones de diferentes tipos de perfiles de
acero...
Mis metas son seguir superando nuevos retos en Excel y compartirlos en mi blog, para sacarle todo el poder a esta excelente herramienta multiusos, tan usada y a la vez tan incomprendida, para así poder mejorar nuestros conocimientos de Excel.
