viernes, 2 de diciembre de 2016

Algoritmo de Dijkstra, en busca de la ruta más corta

Aplicaremos hoy la herramienta Solver para solucionar un problema muy típico.
¿cuál es la ruta más corta entre dos puntos?.
Para esto analizaremos el Algoritmo de Dijkstra (lee algo más en Wikipedia)


Nuestro punto de partida es conocer la distribución de los puntos y distintos nodos intermedios entre el Inicio y el Final del camino.
Para nuestro ejemplo tomaremos el siguiente mapa:

Algoritmo de Dijkstra, en busca de la ruta más corta


Lo más importante ahora, puesto que vamos a aplicar la herramienta Solver es plantear un modelo correcto.
Detallamos, paso a paso, cada uno de las posibles combinaciones para avanzar por las distintas rutas.
Así, manualmente, comenzando desde el punto de Inicio detallamos cada punto, y así sucesivamente en cada nodo:
Inicio => A
Inicio => B
Inicio => C
A => D
A => C
B => C
B => E
C => D
C => E
D => Final
E => Final

Lo vemos trasladado a nuestra hoja de cálculo:

Algoritmo de Dijkstra, en busca de la ruta más corta



Para facilitar el trabajo he asignado nombres definidos a los rangos de trabajo:
Desde =Ruta!$I$3:$I$13
Distancia_entre_puntos =Ruta!$L$3:$L$13
Hasta =Ruta!$J$3:$J$13
Ruta_elegida =Ruta!$K$3:$K$13


Concretamos en la celda K15 la que será nuestra función objetivo:
=SUMAPRODUCTO(Ruta_elegida;Distancia_entre_puntos)

Dicha celda será en Solver nuestra celda a minimizar.


Finalmente, para completar nuestro modelo, incorporamos un rango donde disponer las restricciones. En estas restricciones se debe verificar que en cada nodo intermedio el número de entradas sea igual al de salidas, excepto al punto de Inicio y punto Final que solo de salir y entrar una única vez.
Este se consigue sumando los valores que devolverá Solver en K3:K13 (serán nuestras celdas cambiantes).

Algoritmo de Dijkstra, en busca de la ruta más corta


la fórmula empleada en este rango de restricciones, que recoge la idea es:
=SUMAR.SI(Hasta;$I19;Ruta_elegida)-SUMAR.SI(Desde;$I19;Ruta_elegida)

jueves, 24 de noviembre de 2016

VBA: Detectar si una Celda ha cambiado de valor

Una lectoar preguntaba por una manera de detectar si una celda ha cambiado de valor:
[...] me gustaría guardar el valor actual de la celda para luego compararlo con la misma celda, y resaltar si el valor cambió, son valores numéricos y me gustaría identificar si alguno cambió [...]

La idea según explica nuestra lectora es detectar cuándo se ha modificado o cambiado de valor una celda dentro de un rango definido (en nuestro ejemplo A1:B10).

Para ello emplearemos el evento de hoja Worksheet_Change que hará posible verificar la variación.
Por otro lado haremos uso del tipo de variable Static, que permite fijar el valor antes de los cambios.


El procedimiento es en sí simple (al menos en la idea).
Si guardo en memoria los valores antes del cambio, y tras un posible cambio compruebo celda a celda si el nuevo valor comparado con el anterior, sabremos si efectivamente se ha producido o no dicho cambio.


Si tenemos esta situación inicial en nuestro rango A1:B10:

VBA: Detectar si una Celda ha cambiado de valor



En la ventana de código de nuestra hoja de trabajo incluimos el siguiente evento:

'Al definir las variables antes del procedimiento,
'trabajamos con ellas como si fueran 'estáticas' (Static)
'i.e., mantienen el valor en memoria para comprobar si se ha producido un cambio...
Dim inicio As Long
Dim ValorAnterior() As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fila As Long, col As Long
Dim contador As Long
'definimo el rango sobre el que aplicar el control
Dim RangoTrabajo As Range
Set RangoTrabajo = Range("A1:B10")

If inicio = 0 Then
    ValorAnterior = RangoTrabajo
    inicio = 1
    Exit Sub
End If

'si no trabajamos sobre el rango descrito salimos del procedimiento
If Intersect(Target, RangoTrabajo) Is Nothing Then Exit Sub

'iniciamos contador
contador = 1
'recorremos el rango de trabajo
'comprobando si los valores anteriores difieren de los actuales
'después del cambio
'recorremos las filas del rango A1:B10
For fila = LBound(ValorAnterior, 1) To UBound(ValorAnterior, 1)
    'recorremos las columnas del rango A1:B10
    For col = LBound(ValorAnterior, 2) To UBound(ValorAnterior, 2)
        valor1 = RangoTrabajo(contador)         'acual valor de la celda(después cambio)
        valor2 = ValorAnterior(fila, col)       'anterior valor de la celda (antes cambio)
        'si difieren entonces lanzamos mensaje
        If valor1 <> valor2 Then
            MsgBox "La Celda " & RangoTrabajo(contador).Address & " ha cambiado del valor " & valor2 & " al nuevo valor " & valor1
        End If
        contador = contador + 1
    Next col
Next fila

'cargamos la matriz con un nuevo valor
ValorAnterior = RangoTrabajo
End Sub



Si tras insertar nuestro código y realizar algún cambio comprobamos el efecto.
Por ejemplo cambiamos la celda B3, de un valor 100 a introducir el valor 1313. El evento Worksheet_Change actuará y lanzará el siguiente mensaje:

VBA: Detectar si una Celda ha cambiado de valor



Cada vez que entramos en el rango A1:B10 se produce una carga de datos en memoria, para poder así comparar con la siguiente vez que accedamos a dicho rango...

martes, 22 de noviembre de 2016

La herramienta Previsión o Pronóstico en Excel 2016

Una de las incorporaciones a la última versión de Excel 2016 es la herramienta de Previsión.
Esta herramienta nos permite realizar un cálculo, en base al histórico dado, de los valores pronosticados para fechas futuras.

Por ejemplo, a partir de estos valores de Ventas hasta octubre:

La herramienta Previsión o Pronóstico en Excel 2016


Como observamos la herramienta de Previsión nos construye una estimación para las fechas futuras extrapolando en función de los valores históricos.
Este gráfico se basa en una nueva hoja generada por la herramienta donde se realizan de manera automática una serie de cálculos necesarios para representar en el gráfico los valores estimados.


Veamos los sencillo pasos para llegar a nuestro gráfico de previsión.

Seleccionamos nuestra fuente de datos, rango A1:B13; y desde la ficha Datos > grupo Previsión > botón Previsión (en Excel 2016!!). Al presionar aparecerá la venta desde donde configurar nuestra previsión:



Aspectos relevantes a configurar:
1- en primer lugar la fecha donde queremos acabe la previsión.
En el ejemplo acabamos el 01/01/2017.

2- En segundo lugar, dentro de las opciones, la Fecha donde deseamos comience nuestra estimación.
En nuestro ejemplo la última fecha con dato: 01/10/2016.

3- Otro aspecto importante es el que indica cómo rellenaremos los puntos que faltan; donde se ofrecen dos posibilidades:
* cero (refleja en el gráfico los valores en cero, para esos datos sin valor)
* interpolación (se representan datos interpolados. Tiene efecto cuando faltan datos intermedios entre los distintos puntos de la serie de valores).
Una buena elección suele ser marcar 'interpolados'.

4- De igual forma, en caso de puntos de la serie repetidos, dispondremos con la opción de Agregar duplicados con la operación que deseamos ver.
Una buena decisión es seleccionar la opción de Promedio.

5-Finalmente, de especial, sería marcar la opción 'Incluir estadísticas de previsión'; incorporándose en la nueva hoja que se generará, una segunda tabla con cálculos de las principales funciones estadísticas de pronóstico (PRONOSTICO.ETS).


Con todos los aspectos configurados a nuestro gusto aceptamos, y vemos cómo se genera la nueva hoja de trabajo:



Observamos en la nueva hoja que se ha generado una primera tabla con los datos originales...
añadiendo tres nuevos campos:
-Previsión(Ventas)
-Límite de confianza inferior(Ventas)
-Límite de confianza superior(Ventas)
con valores en las fechas configuradas (en el ejemplo desde el 01/10/2016 hasta el 01/01/2017).
Para octubre repite el dato existente en las tres nuevas series, mientras que para los otros dos meses (noviembre y diciembre) inserta las siguientes funciones, según el nuevo campo.
-Previsión(Ventas): =PRONOSTICO.ETS(A12;$B$2:$B$11;$A$2:$A$11;1;1)
-Límite de confianza inferior(Ventas): =C12-PRONOSTICO.ETS.CONFINT(A12;$B$2:$B$11;$A$2:$A$11;0,95;1;1)
-Límite de confianza superior(Ventas): =C12+PRONOSTICO.ETS.CONFINT(A12;$B$2:$B$11;$A$2:$A$11;0,95;1;1)

Son todas fórmulas autogeneradas, por lo que no necesitan más explicaciones... Baste decir que esta función PRONOSTICO.ETS.CONFINT devuelve un intervalo de confianza para el valor previsto en una fecha futura específica.
Se toma un intervalo de confianza según el dado en la configuración del Pronóstico.
Por ejemplo, un intervalo de confianza de 95% significa que se espera que el 95% de los puntos futuros caigan dentro de este radio según el resultado pronosticado de PRONOSTICO.ETS (con distribución normal).
Un tamaño del intervalo más pequeño implicaría más confianza en la predicción de este punto específico.

Estas tres nuevas series son las que se reflejan, obviamente, en nuestro gráfico.


Por último, si hubiéramos seleccionado la opción de 'Incluir estadísticas de previsión' veríamos la segunda de las tablas (ver imagen anterior), la cual presenta la función PRONOSTICO.ETS.STAT:
Alpha = 0,50 =PRONOSTICO.ETS.STAT($B$2:$B$11;$A$2:$A$11;1;1;1)
Beta = 0,00 =PRONOSTICO.ETS.STAT($B$2:$B$11;$A$2:$A$11;2;1;1)
Gamma = 0,00 =PRONOSTICO.ETS.STAT($B$2:$B$11;$A$2:$A$11;3;1;1)
MASE = 0,92 =PRONOSTICO.ETS.STAT($B$2:$B$11;$A$2:$A$11;4;1;1)
SMAPE = 0,19 =PRONOSTICO.ETS.STAT($B$2:$B$11;$A$2:$A$11;5;1;1)
MAE = 386,46 =PRONOSTICO.ETS.STAT($B$2:$B$11;$A$2:$A$11;6;1;1)
RMSE = 444,77 =PRONOSTICO.ETS.STAT($B$2:$B$11;$A$2:$A$11;7;1;1)

Vemos que cambia el tercer argumento, mostrando el 'tipo de estadístico' valores entre 1 y 8. Su significado sería:
1 =Parámetro alfa del algoritmo de ETS:= Devuelve el parámetro de valor base (un valor más alto otorga más peso a los puntos de datos recientes).
2 =Parámetro beta del algoritmo de ETS := Devuelve el parámetro de valor de tendencia (un valor más alto otorga más peso a la tendencia reciente).
3 =Parámetro gamma del algoritmo de ETS:= Devuelve el parámetro de valor de estacionalidad (un valor más alto otorga más peso al período estacional reciente).
4 =Métrica MASE:= Devuelve la métrica de errores elevados absolutos medios (es una medición de la precisión de los pronósticos).
5 =Métrica SMAPE:= Devuelve la métrica simétrica de errores porcentuales absolutos medios (es una medición de precisión basada en errores de porcentaje).
6 =Métrica MAE:= Devuelve la métrica simétrica de errores porcentuales absolutos medios (es una medición de precisión basada en errores de porcentaje).
7 =Métrica ECM:= Devuelve la métrica raíz de errores cuadráticos medios (es una medición de las diferencias entre los valores pronosticados y los valores observados.
8 =Tamaño de paso detectado:= Devuelve el tamaño de paso detectado en la línea de tiempo histórica.


Conceptos estadísticos que se quedan fuera de esta explicación.. pero que en todo caso requieren por parte del usuario nociones probabilísticas.

En cualquier caso, comprobamos que la herramienta de Previsión es de muy fácil aplicación, y no requiere a priori altos conocimientos de la ciencia estadística.