sábado, 30 de julio de 2011

Teoría de Colas con #Excel

Descargar el fichero: colas.xlsx

La Teoría de colas es una especialidad de la Investigación Operativa que nos permite estudiar el comportamiento de servicios atendidos en los que se producen esperas. Es el caso típico de una cola en un banco, o un microprocesador que debe atender una lista de trabajos. En este artículo vamos a considerar un servicio médico de una consulta donde un único médico ha de atender a una serie de pacientes que llegan a lo largo de la mañana. Se trata de determinar mediante Simulación de Montecarlo los usuarios que se encuentran en la cola en cada momento.

En primer lugar hemos de recoger datos. Realizamos un estudio observando dos variables:

  1. Datos históricos de tiempo entre llegadas (minutos)
  2. Datos de tiempos de servicio (minutos)



Organizamos esta información en forma de tabla de frecuencias.


Necesitaremos generar una serie de números aleatorios para poder efectuar la simulación de Montecarlo.


Con la información anterior procedemos a crear la tabla de la Hoja2.


Trabajamos con el formato de celda personalizado h:mm. El inconveniente que tiene esta forma de trabajar es que en realidad Excel trabaja con decimales. Así un minuto que en este formato sería 0:01 si lo mostramos en formato general arroja un valor de 0,000694444444444444. Esto supone que al establecer igualdades o desigualdades es frecuente obtener errores. Por ejemplo, si queremos saber si dos celdas contienen el mismo valor expresado en horas y minutos (hh:mm) es posible que veamos lo mismo, por ejemplo 0:23 (que son 23 minutos), pero si lo tuviéramos en formato general es posible que difieran en el último decimal y esto hace que Excel nos diga que no son iguales, lo cual acarrea errores importantes en nuestros cálculos finales.

Para evitar estos errores hemos creado las columnas K y L (de color azul) que en realidad son las mismas que las columnas D e I, pero con un redondeo a 7 decimales. De esta forma podemos comparar tiempos sin estar preocupados por el error del último decimal.

La columna que realmente supone una explicación detallada es la última columna que determina el número de usuarios que hay en la cola en cada momento.

Tomemos como ejemplo la celda M20 cuya fórmula es:

{=SUMA(--(K20<=($L$6:L19)))}

Los corchetes indican que es una fórmula matricial. Los corchetes no debemos ponerlos nosotros, los pondrá Excel al validar la expresión. Después de escribir la fórmula no debemos pulsar ENTER para validarla, sino que, por tratarse de una fórmula matricial, se han de pulsar simultáneamente las tres teclas siguientes:

CONTROL+MAYUSCULAS+ENTER

Para ampliar información sobre fórmulas matriciales puede consultar el siguiente post:

http://trucosexcel.blogspot.com/2008/11/funciones-matriciales-en-excel.html

La celda M20 calcula cuantos usuarios hay en la cola en ese momento. Esto se hace considerando cuantos usuarios previos a él aún no han salido de la consulta cuando el llega.

Simulación de Montecarlo

Los aleatorios que se presentan en este ejemplo no cambian, esto es, no se generan nuevos aleatorios. Si quisiéramos efectuar el método de simulación de Montecarlo de una forma más dinámica deberíamos generar nuevos aleatorios, y con ellos calcular las diferentes filas de esta tabla. Cuantos más aleatorios generemos mejor establecidas quedaran luego las medias de tiempos de espera y usuarios en cola que es lo que realmente deseamos estudiar con estos casos.

9 comentarios:

María Luisa Eslava dijo...

Escelente. Muchas gracias!!!

Luis Mezones dijo...

Muy interesante, mil gracias.

30 de julio 2011

italianocasisa dijo...

Buen aporte muchas gracias, para estudiar y analizar.

muchas gracias

daniel dijo...

Buenas me podrian explicar la logica de la formula matricial, quisiera saber su funcionamiento

Ricardo dijo...

Y si tengo multiples servidores, digamos 3.

Epatricio dijo...

Buen aporte, como calculo cuantas posiciones de atencion deberia tener? si ya tengo la cantidad de clientes promedio por dia y hora, tiempos de atencion y espera promedios? puedo usar solver para realizar este dimensionamiento? Gracias.

Alf dijo...

Por que la formula lleva esos dos -- después de primer (

y podría por favor explicarnos el significado de la formula, por ejemplo el valor de la celda c312 se compara, se suma, etc?

=SUMA(--(C312<=($E$2:E312)))

Gracias y saludos.

Adolfo Aparicio dijo...

Hola Alf.

La fórmula lleva los signos -- que son dos signos menos seguidos. Uno de ellos multiplica por -1, para convertir en numéricos los Verdaderos y Falsos. El otro menos vuelve a multiplicar por -1 para evitar signos negativos. El resultado es que tenemos un array lleno de números unos y ceros en lugar de Verdaderos y Falsos.

La fórmula empleada es una fórmula matricial. Véanse las llaves {}. Es aconsejable ver la Etiqueta, en este mismo blog, denominada 'función matricial'.

Un saludo.

Alf dijo...

Muchisimas gracias, ahora si lo entiendo, fuerte abrazo!