Generar datos aleatorios condicionados en excel
Publicado por Xelad (1 intervención) el 07/06/2017 21:02:34
Buenas tardes.
Estoy intentando crear una hoja de cálculo capaz de generar un cuadrante de turnos de trabajo que cumpla con algunas condiciones, y me he topado con un obstáculo que no logro salvar: condicionar los resultados a premisas necesarias para un reparto equitativo del trabajo.
El planteamiento es el siguiente: hay N personas (pongamos 3) entre las que repartir los turnos (por simplificar, 1 turno por día, de lunes a viernes). Si a cada persona le asignamos un número entero, con la función =REDONDEAR(ALEATORIO.ENTRE(1-3;0)) logramos que la celda en cuestión muestre un número entero aleatorio entre 1 y 3, por tanto asignando el puesto al trabajador de forma también aleatoria.
Hasta ahí bien. El problema llega con que para que el personal acepte el reparto han de cumplirse tres condiciones:
A. A final de trimestre, el número de turnos debe estar repartido de forma equitativa (todos el mismo número de turnos, +/- 1).
B. Los turnos de los viernes son especialmente duros por tanto queremos que al final del trimestre, todos tengan aproximadamente el mismo número de viernes +/- 1.
C. Si un trabajador solicita un día libre digamos "el martes 2 de abril" para ir a la boda de su prima o "el jueves 4 de marzo es festivo" y se queda sin cubrir, el generador debe respetarlo, y ajustar el resto de resultados. Es decir, tener una forma de bloquear celdas (turnos sin cubrir), excluir a un trabajador de un turno concreto (días libres y eventualidades), y por conveniencia, poder forzar un turno concreto a un trabajador (es el único día de esa semana que puede acudir o situaciones similares), todo ello "a voluntad" y que el resto de valores aleatorios se ajusten a lo anterior.
Si alguna de las anteriores son incompatibles entre ellas, debe haber una jerarquía para que Excel sepa a cual dar prioridad. Probablemente lo más razonable sea C > A > B.
No es tarea fácil. Al menos cuando me he puesto a intentar darle sentido a las fórmulas, no he logrado pasar de la asignación aleatoria de días. El problema es cumplir las condiciones...
Por lo que he podido leer puede que sea necesario usar macros o scripts incluso, pero mi capacidad no alcanza a tal nivel. Agradecería mucho si alguien puede orientarme o conoce alguna solución más sencilla.
Muchas gracias de antemano por vuestro tiempo. La verdad que creo que es una tarea titánica y entendería que nadie se tome la molestia de resolver, pero ha llegado un punto que no solo la necesidad, sino la curiosidad me ha impulsado a buscar ayuda.
Un saludo, y gracias de nuevo!
PD: Si... Siempre está la opción de hacer la cuenta de la vieja e ir contando y poniendo a dedo, pero tras enfrentarme a eso varias veces, es un consumo de tiempo enorme además de que la plantilla nunca está del todo conforme por X detalle en el que no pensaste... Una hoja capaz de crear el cuadrante de forma "automática" sería sencillamente genial.
PD2: en la imagen dejo el ejemplo de lo que llevo hecho. Como he comentado, he usado la fórmula =REDONDEAR(ALEATORIO.ENTRE(1-3;0)) para generar los valores aleatorios y para realizar los contajes para saber el total de guardias y el total de viernes con =CONTAR.SI(rango;valor de interés).
Estoy intentando crear una hoja de cálculo capaz de generar un cuadrante de turnos de trabajo que cumpla con algunas condiciones, y me he topado con un obstáculo que no logro salvar: condicionar los resultados a premisas necesarias para un reparto equitativo del trabajo.
El planteamiento es el siguiente: hay N personas (pongamos 3) entre las que repartir los turnos (por simplificar, 1 turno por día, de lunes a viernes). Si a cada persona le asignamos un número entero, con la función =REDONDEAR(ALEATORIO.ENTRE(1-3;0)) logramos que la celda en cuestión muestre un número entero aleatorio entre 1 y 3, por tanto asignando el puesto al trabajador de forma también aleatoria.
Hasta ahí bien. El problema llega con que para que el personal acepte el reparto han de cumplirse tres condiciones:
A. A final de trimestre, el número de turnos debe estar repartido de forma equitativa (todos el mismo número de turnos, +/- 1).
B. Los turnos de los viernes son especialmente duros por tanto queremos que al final del trimestre, todos tengan aproximadamente el mismo número de viernes +/- 1.
C. Si un trabajador solicita un día libre digamos "el martes 2 de abril" para ir a la boda de su prima o "el jueves 4 de marzo es festivo" y se queda sin cubrir, el generador debe respetarlo, y ajustar el resto de resultados. Es decir, tener una forma de bloquear celdas (turnos sin cubrir), excluir a un trabajador de un turno concreto (días libres y eventualidades), y por conveniencia, poder forzar un turno concreto a un trabajador (es el único día de esa semana que puede acudir o situaciones similares), todo ello "a voluntad" y que el resto de valores aleatorios se ajusten a lo anterior.
Si alguna de las anteriores son incompatibles entre ellas, debe haber una jerarquía para que Excel sepa a cual dar prioridad. Probablemente lo más razonable sea C > A > B.
No es tarea fácil. Al menos cuando me he puesto a intentar darle sentido a las fórmulas, no he logrado pasar de la asignación aleatoria de días. El problema es cumplir las condiciones...
Por lo que he podido leer puede que sea necesario usar macros o scripts incluso, pero mi capacidad no alcanza a tal nivel. Agradecería mucho si alguien puede orientarme o conoce alguna solución más sencilla.
Muchas gracias de antemano por vuestro tiempo. La verdad que creo que es una tarea titánica y entendería que nadie se tome la molestia de resolver, pero ha llegado un punto que no solo la necesidad, sino la curiosidad me ha impulsado a buscar ayuda.
Un saludo, y gracias de nuevo!
PD: Si... Siempre está la opción de hacer la cuenta de la vieja e ir contando y poniendo a dedo, pero tras enfrentarme a eso varias veces, es un consumo de tiempo enorme además de que la plantilla nunca está del todo conforme por X detalle en el que no pensaste... Una hoja capaz de crear el cuadrante de forma "automática" sería sencillamente genial.
PD2: en la imagen dejo el ejemplo de lo que llevo hecho. Como he comentado, he usado la fórmula =REDONDEAR(ALEATORIO.ENTRE(1-3;0)) para generar los valores aleatorios y para realizar los contajes para saber el total de guardias y el total de viernes con =CONTAR.SI(rango;valor de interés).
Valora esta pregunta
0
