Excel - Generar datos aleatorios condicionados en excel

   
Vista:

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).
Prueba-calendario
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de wordexperto.com

Generar datos aleatorios condicionados en excel

Publicado por wordexperto.com (1512 intervenciones) el 07/06/2017 21:35:43
Hola Xelad:
Esto parece la típica tarea para Solver. Incluso en los ejemplos viene uno de asignación de turnos. Tienes que activar este complemento. Los ejemplos están en C:\Program Files (x86)\Microsoft Office\Office12\SAMPLES\SOLVSAMP.XLS. Donde Office12 es número de versión.
https://wordexperto.com/blog/
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de wordexperto.com

Generar datos aleatorios condicionados en excel

Publicado por wordexperto.com (1512 intervenciones) el 08/06/2017 10:23:09
Muy buena la solución de Juan con vba.
Yo, sugerí una solución con Solver y aquí la subo.
https://wordexperto.com/blog/
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Generar datos aleatorios condicionados en excel

Publicado por JuanC juanc2942@gmail.com (1228 intervenciones) el 07/06/2017 23:14:24
me puse a jugar un rato y salió esto...

https://www.sendspace.com/file/1zumfh

me faltó contemplar la condición de los feriados, no lo hice porque no tengo del todo claro el funcionamiento real con sus variantes...
espero te sirva igual...
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Revisar política de publicidad