Teoría de Colas

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:

• Funciones matriciales en Excel

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.

Control de almacén y pedidos

Descargar el fichero: pedidos.xlsx

Controle su almacén y gestione sus pedidos de forma sencilla en Excel. Veremos un caso simple de control de Stocks y un avisador de realización de pedidos. Si su almacen es realmente complejo, no pretenda gestionarlo con Excel, para ello existen en el mercado programas específicos, y si desea crear su propia aplicación ha de utilizar una base de datos relacional tipo Access, a nivel de microinformática, u otra base de datos profesional de mayor capacidad. Piense también en el número de personas que gestionarán el programa, si han de ser varias personas de forma simultánea, es posible que le interese una aplicación que corra en página web, con una base de datos profesional que soporte el manejo de datos, tipo MySql.


Veamos nuestro caso sencillo gestionado con Excel.

La primera tabla se denomina "Control de Pedidos". En ella tenemos las referencias, las fechas del último pedido y de la recepción prevista. El usuario ha de marcar con una x cuando se ha recibido el pedido.

Disponemos de una columna que indica si debemos reclamar el pedido. Si la fecha actual es superior a la de recepción prevista se ha de Reclamar el pedido. Salvo que ya se hubiera recibido.

La segunda tabla se denomina "Almacén". En ella hemos de considerar las dos últimas tablas de Entradas y Salidas para determinar cuantos artículos están disponibles en cada momento en el almacén. Disponemos de una columna donde el usuario indica el "Stock mínimo" admisible. Si las unidades en almacén son menores de las indicadas se muestran en la columna contígua unos símbolos que indican la urgencia de realizar o reclamar el pedido.

La excepción a esta tabla amarilla la pone el caso en el que el stock en almacen sea cero. En este caso, la marca utilizada es de color rojo >>>>. El color se consigue con Formato Condicional y la fórmula utilizada para la celda G22 es:

=SI(C22=0;">>>>";BUSCARV(+C22/F22;tablaminimos;2))

Las tablas que recogen las Entradas y Salidas por cada referencia las hemos organizado por trimestres. Lógicamente necesitamos para las Entradas una columna "Inicial". Estos valores iniciales se han de corresponder con los valores de cierre del año anterior.

Juego: el que quite la última piedra, pierde

Descargar el fichero: piedras.xlsm

Juguemos en Excel. Podemos programar una macro que nos permita jugar contra el ordenador a un juego que consiste en ir quitando piedras y donde el último que quite es el que pierde.

Al iniciar el juego, el ordenador genera de forma aleatoria un conjunto de piedras que oscilan entre 9 como mínimo y 30 como máximo. Al pulsar sobre el botón denominado 'Nuevo Juego' se generan las piedras iniciales que hay en el montón. Gráficamente, este montón inicial de piedras se muestra mediante celdas de color verde de la fila 9.

Ud. juega contra el ordenador. Se dispone de un montón de piedras. Cada jugador quita 1, 2 o 3 piedras en cada tirada. Comienza quitando Ud.. El último en quitar pierde.

Son varias macros enlazadas las que nos permiten que el juego funcione.

Código:

Private tuquitas, quitadass As Integer
Private mi, tu As Integer
Private n, yoquito As Integer
Sub piedras()
Dim c As Integer
Worksheets("Hoja1").Activate
Range("Q2").ClearContents
Worksheets(2).Range(Cells(9, 3), Cells(39, 32)).Clear
Randomize
n = Int(Rnd * 22 + 9)
Worksheets(2).Range(Cells(9, 3), Cells(9, n + 2)).Interior.Color = RGB(0, 255, 0)
Worksheets(2).Range(Cells(9, 3), Cells(9, n + 2)).Borders.Color = RGB(0, 0, 0)
Range("ah23").Value = n
Range("B9").Value = "Piedras Iniciales"
Range("A1").Select
ActiveWorkbook.Names.Add Name:="jugadas", RefersToR1C1:="=Hoja1!R10C2:R39C2"
Worksheets(2).Range("jugadas").ClearContents
tu = 1: mi = 2
End Sub
Public Sub Quito1()
quitadass = 1
Call quitadas
End Sub
Public Sub Quito2()
quitadass = 2
Call quitadas
End Sub
Public Sub Quito3()
quitadass = 3
Call quitadas
End Sub
Sub maquina()
yoquito = (n - 1) Mod 4
If yoquito = 0 Then
  yoquito = Int(Rnd * 3 + 1)
End If
n = n - yoquito
If n <= 0 Then
  Range("Q2").Value = "TU GANAS. FELICIDADES!!!"
Else
  Worksheets(2).Range("jugadas").Cells(mi, 1).Value = "Maquina quita " & yoquito
  Range("ah23").Value = n
  Call colores("Rojo")
  mi = mi + 2
End If
End Sub
Sub colores(micolor As String)
If micolor = "Amarillo" Then
  Worksheets(2).Range(Cells(9 + tu, 3), Cells(9 + tu, n + 2)).Interior.Color = RGB(255, 255, 0)
  Worksheets(2).Range(Cells(9 + tu, 3), Cells(9 + tu, n + 2)).Borders.Color = RGB(0, 0, 0)
ElseIf micolor = "Rojo" Then
  Worksheets(2).Range(Cells(9 + mi, 3), Cells(9 + mi, n + 2)).Interior.Color = RGB(255, 0, 0)
  Worksheets(2).Range(Cells(9 + mi, 3), Cells(9 + mi, n + 2)).Borders.Color = RGB(0, 0, 0)
End If
End Sub
Sub quitadas()
Worksheets(2).Range("jugadas").Cells(tu, 1).Value = "Tu quitas " & quitadass
n = n - quitadass
If n <= 0 Then
  Range("Q2").Value = "LA MÁQUINA GANA"
Else
  Call colores("Amarillo")
  tu = tu + 2
  Call maquina
End If
End Sub

¿Cuál es la clave del juego?

 Para ganar el juego se ha de .....

Spoiler. Al igual que al hablar de cine o de novelas, en las siguientes líneas desvelaremos el secreto del juego. Este aviso se hace por si usted desea descubrir por sí mismo cómo ganar a la máquina.

Para ganar el juego, se han de quitar en cada tirada un número de piedras tal que las que queden en el montón, menos una, sean múltiplo de cuatro. Esto se puede ver en la celda AI23 donde se utiliza la fórmula siguiente: =RESIDUO(AH23-1;4)

Truco

La celda AI23 oculta su contenido debido a que utiliza tinta blanca sobre fondo blanco. Podemos utilizar un truco mientras jugamos. Se trata de pulsar con el ratón sobre el indicador de columna AI, de esta forma queda seleccionada toda la columna AI, lo cual permite ver ligeramente el contenido de la celda AI23.

¡Espero que disfrutes con el juego de la última piedra!

Programar una función de redondeo

Descargar el fichero: redondear.xlsm

Redondear un número es una tarea común que podemos hacer con la fórmula de Excel REDONDEAR. En éste post nos proponemos programar una función en VBA, mediante una macro, que produzca el redondeo.

Redondear a cero decimales

Comencemos redondeando a cero decimales. Así el número 3,14159 (¡me suena este número!) se puede redondear a cero decimales como 3. Y el número 2,7182818 (y éste ¿me debería sonar?) se puede redondear a cero decimales como 3.

Código:

Function Redondealo(n As Double) As Double
If n >= Int(n) + 0.5 Then
  Redondealo = Int(n) + 1
Else
  Redondealo = Int(n)
End If
End Function

El primer número visto es pi que se obtiene con la función de Excel

=PI()

El segundo número visto es e, que es la base de los logaritmos neperianos. También podemos decir que es la exponencial de 1. En Excel se puede calcular con la siguiente expresión:

=EXP(1)

Si aplicamos la función definida por el usuario Redondealo obtendremos en ambos casos el valor de 3.

=Redondealo(PI())
=Redondealo(EXP(1))

Redondear invocando la función de Excel

Todas las funciones disponibles en Excel se pueden invocar mediante una macro. El código requiere que la función a la que llamemos esté escrita en inglés. Para obtener un listado de la funciones en inglés puede consultar el siguiente enlace.

Glosario: Traducción de funciones

La función REDONDEAR en español equivale a la función ROUND en inglés. La expresión que hemos de utilizar en VBA para invocar a esta función es la siguiente:

Application.WorksheetFunction.round(número, precisión)

Código:

Function SuRedondeo(numero, precision)
SuRedondeo = Application.WorksheetFunction.Round(numero, precision)
End Function

Programemos la función

Con la función Redondealo fuimos capaces de redondear a cero decimales. En esta ocasión vamos a introducir la variable p que recoge la precisión del redondeo. La función se llama MiRedondeo y el código es el siguiente.

Código:

Function MiRedondeo(n As Double, p As Single) As Double
If n * 10 ^ p >= Int(n * 10 ^ p) + 0.5 Then
  MiRedondeo = Int(n * 10 ^ p + 1) / (10 ^ p)
Else
  MiRedondeo = Int(n * 10 ^ p) / (10 ^ p)
End If
End Function

Podemos ver el resultado aplicado a Pi y a e.