martes, 21 de agosto de 2018

Análisis de Regresión Lineal Múltiple-Excel

Estudiaremos hoy un herramienta interesante específica del Análisis de datos en Excel, y pensada para la estimación de resultados...

Supongamos tenemos una serie de datos histórica que refleja el 'Coste de mano de obra' de una empresa durante los últimos once meses, con datos del 'número de empleados' por mes, así como de 'horas reales trabajadas':

Análisis de Regresión Lineal Múltiple-Excel



El objetivo es estimar el dato Coste de mano de obra de diciembre-2018 sabiendo que tendremos 11 empleados contratados que trabajarán un número de 350 horas... y encontrar la ecuación de regresión lineal múltiple.

Coste mano de obra = constante + parámetro1*Num_empleados + parámetro2*Horas_Trabajadas


Para tal fin emplearemos una herramienta contenida en el 'pack' de Análisis de datos (ficha Datos > Análisis)
OJO!, requiere tener el complemento instalado!!.

Dentro de las herramientas de análisis buscaremos la Regresión

Análisis de Regresión Lineal Múltiple-Excel



Al abrir la herramienta nos pedirá completar algunos parámetros:
Sección Entrada
1-Rango Y de entrada: E2:E13 que corresponde al rango de celdas con el histórico de Coste de mano obra
2-Rango X de entrada: C2:D13 que corresponde con nuestras dos variables: empleados y horas trabajadas
3-Rótulos: marcado ya que en los rangos anteriores incluimos los encabezados
4-Nivel de confianza: sin marcar por que nos vale el nivel al 95% (o el alfa=0,05)
5-Constante igual a cero: sin marcar por que nos interesa una constante en nuestra futura ecuación

Sección Salida
1-Rango de salida: G1 será la celda superior izquierda del rango destino

No marcaremos ninguna otra opción para no recargar el análisis...

Análisis de Regresión Lineal Múltiple-Excel



Tras aceptar vemos los siguientes resultados...



De especial relevancia para el análisis e interpretación de nuestra regresión lineal múltiple son los datos marcados en las Estadísticas de la regresión, que paso a resumir:

Coeficiente de correlación múltiple:= 0,95804402
Coeficiente de determinación R^2:= 0,917848345
R^2 ajustado:= 0,897310431
Error típico:= 765,9173652

El cálculo mostrado para el coef de correlación múltiple (R) será la raíz cuadrada del Coeficiente de determinación R^2, y este último viene del cociente entre la 'suma de los cuadrados de la regresión' entre 'la suma de los cuadrados del total'...
Aunque para los casos de regresión lineal múltiple es conveniente fijarse en el R^2 ajustado, ya que este es la medida que define el porcentaje explicado por la varianza de la regresión en relación con la varianza de la variable explicada, esto es, lo mismo que el R^2, pero con una diferencia, y es que el R^2 ajustado tiene en cuenta el tamaño muestral y la inclusión de variables.

Este R^2 ajustado y el R^2 'normal' debe ser un valor entre 0 y 1.. cuanto más próximo a 1 mejor reflejaría una correlación.

Por otra parte es importante que el error típico sea lo más bajo posible.


Otras variables relevantes son dentro de la sección de ANOVA (Análisis de varianza):
F:= 44,69043718
Valor crítico de F:= 4,55476E-05

Este valor nos aporta la significación (si es o no relevante el análisis obtenido); siempre que el valor crítico sea inferior al alfa del estudio (en nuestro caso alfa=0,05 = 1-0,95)

En nuestro caso es netamente inferior a 0,05, con un valor crítico de casi cero; con lo que concluimos que a nivel global es significativo.


De la última sección del análisis nos quedaremos con los coeficientes de la ecuación de regresión:
Intercepción:= -2528,239987 -el valor de la constante de la ecuación buscada
Núm Empleados:= 1513,960316 -el parámetro para la primera variable
Horas Trabajadas:= 0,63819145 -el parámetro para la segunda variable

con esto construimos nuestra ecuación buscada:
Coste mano de obra = -2528,24 + 1513,96*Num_Empleados + 0,64*Horas_trabajadas

ecuación con la que podríamos estimar datos futuros...


Pero para certificar que nuestra ecuación es válida tenemos que fijarnos, al menos, en otro parámetro que nos aportará información sobre la significación local
Estadístico t
Intercepción:= -1,206087721
Núm Empleados:= 3,187709856
Horas Trabajadas:= 0,052624141

y de manera más relevante
Probabilidad
Intercepción:= 0,262239309
Núm Empleados:= 0,01284757 - valores inferiores a alfa (alfa=0,05) serán significativos (Significativa)
Horas Trabajadas:= 0,959321713 - valores inferiores a alfa (alfa=0,05) serán significativos (NO significativa)

En nuestro ejemplo solo la variable 'Num empleados' es significativa!!, así pues, esta lectura nos lleva a la conclusión que podríamos prescindir de ella en el análisis de nuestros datos...


Lanzaremos entonces de nuevo el proceso solo para la variable independiente 'Num empleados' obteniendo los siguientes resultados:

Análisis de Regresión Lineal Múltiple-Excel



Al hacer el mismo análisis sobre los datos relevantes obtenidos leemos que:

Datos marcados en las Estadísticas de la regresión, que paso a resumir:
Coeficiente de correlación múltiple:= 0,958029179
Coeficiente de determinación R^2:= 0,917819907
R^2 ajustado:= 0,908688786
Error típico:= 722,2387906

Dentro de la sección de ANOVA (Análisis de varianza):
F:= 100,5155735
Valor crítico de F:= 3,50246E-06

que indica la significación a nivel global del estudio.

Finalmente recuperamos los coeficientes de la ecuación de regresión:
Intercepción:= -2559,704918
Núm Empleados:= 1537,442623

con lo que nuestra ecuación buscada será:
Coste mano de obra = -2559.70 + 1537,44*Num_Empleados

y los valores que determinan la significación local:
Estadístico t
Intercepción:= -1,351072511
Núm Empleados:= 10,02574553


y de manera más relevante
Probabilidad
Intercepción:= 0,209655471
Núm Empleados:= 3,50246E-06 - valores inferiores a alfa (alfa=0,05) serán significativos

Que nos indica que en este caso, la variable 'Num empleados' sí es significativa.


En definitiva, tras analizar ambos análisis determinamos que el mejor es el segundo donde correlacionamos únicamente el coste de mano de obra con el número de empleados a través de la ecuación:
Coste mano de obra = -2559.70 + 1537,44*Num_Empleados

sabiendo que si es significativo a nivel global y local, que tiene un R^2 ajustado muy alto y un error típico muy bajo...


Podemos entonces estimar cuál será el coste de mano de obra en diciembre suponiendo empleemos a 11 trabajadores al 95%, en la celda E14:
=-2559,7+1537,44*C14

Análisis de Regresión Lineal Múltiple-Excel

jueves, 16 de agosto de 2018

VBA: Derivada de una función lineal de una variable en Excel

Veremos una manera de obtener la ecuación derivada a partir de una función linea de una sola variable...
Obviamente las restricciones son altas, y por ese motivo nos limitaremos a una sola variable.

El ejemplo se basa en el concepto de Newton de función derivada (leer más aquí)

En nuestro desarrollo veremos el ejemplo sobre la función:
f(x) = 2x2-1x

(que para los principiantes, su función derivada deberá ser f'(x)= 4x-1



Comenzaremos generando con programación nuestras funciones personalizadas.

Una de ellas ya se explicó en este blog (leer aquí).

La UDF 'FuncionesLineales' permite tratar una ecuación escrita como texto en una celda como una ecuación 'de verdad'... y es la base del cálculo diferencial que veremos a continuación.


La segunda de las UDF programadas (llamada 'Derivada') replica el límite o cociente de Newton:

límite cuando h tiende a cero de (f(a+h)-f(a))/ h


Insertaremos las siguientes funciones en un módulo estándar:

Function Derivada(ecuacion As String, punto As Double)
Dim h As Double
Dim fa As Double, fah As Double
h = 0.000000000001

'reoplicaremos el cálculo del límiete cuadno se tiende a cero
'nos aprovecharemos de nuestra UDF qeu convierte en funciones operables
'ecuaciones escritas en una celda
fa = FuncionesLineales(ecuacion, punto)
fah = FuncionesLineales(ecuacion, punto + h)

'y ua vea calculado los valores en los puntos 'a+h' y en 'a'
'devolvemos el dato a la función
Derivada = (fah - fa) / h

End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function FuncionesLineales(fx As String, valor_x As Double) As Double
'http://excelforo.blogspot.com/2018/05/vba-una-funcion-para-gobernarlas-todas.html

'aplicamos dos reemplazamientos
'1- para cambiar la variable por el valor concreto asignado
'2- tenemos en cuenta el cambio de configuración de los separadores decimales
'(recuerda que en el entorno del editor de VB el separador decimal es el punto, y no la coma!!)

func = Replace(Replace(fx, "x1", valor_x), ",", ".")

'retornamos el valor a la función creada
FuncionesLineales = Application.Evaluate(func)

End Function



Trabajemos con nuestras funciones.
En A12:A32 añadimos valores desde -5 a 5 en intervalos de 0,5.
En B12:B32 añadimos el valor de f(x), es decir, insertamos nuestra función:
=FuncionesLineales($B$9;$A12)

Y en C12:C32 el valor en cada punto de la derivada f'(x) insertando nuestra función UDF:
=Derivada($B$9;A12)

recuerda que en B9 tenemos escrita nuestra función f(x)=(2*x1^2)-1*x1


Con los valores de la función derivada para cada punto conseguidos y conocidos esos puntos (la x de nuestra equciación), podemos sabe y componer nuestra ecuación derivada, lo que haremos aplicando la función ESTIMACION.LINEAL

Así pues seleccionamos las celdas E13:F13 y escribimos:
=REDONDEAR(ESTIMACION.LINEAL(C12:C32;A12:A32;VERDADERO);2)

es decir, indicamos como argumento de 'conocidas_y' el rango con los valores de la derivada (C12:C32)
y como 'conocido_x' al de las equis (A12:A32)

Redondearemos el resultado para no arrastrar decimales innecesarios a la ecuación derivada..

Validamos matricialmente!!! (presionamos Ctrñ+Mayusc+Enter).

VBA: Derivada de una función lineal de una variable en Excel



Con ese resultado ya sabemos la pendiente y constante de nuestra recta derivada (f'(x) = m x + b)
Siendo 4 la pendiente y -1 la constante, y por tanto nuestra ecuación derivada buscada será:

f'(x)=4x-1

Lo que buscábamos...

Componiéndola con fórmulas en la celda F15 será:
=CONCATENAR(TEXTO(E13;"0,00");"x";SI(F13>0;"+"&TEXTO(F13;"0,00");SI(F13<0;TEXTO(F13;"0,00");"")))


Recuerda que este proceso no es extrapolable a todo tipo de funciones!!

martes, 14 de agosto de 2018

Ábaco Soroban en Excel

Construiremos hoy un ábaco japonés (llamado también Soroban). muy similar en funcionamiento al ábaco romano, siendo acaso este último algo más completo, puesto que incluía partes fraccionadas...

¿Qué necesitamos?
1- funciones sencillas del tipo condicional SI y una función SUMAPRODUCTO para verificar el correcto funcionamiento.
2- un formato condicional
3- controles de formulario (NO ActiveX), sin programación...

Ábaco Soroban en Excel



El primer paso es incorporar los controles de formulario de tipo 'control de número' en grupos de cinco en las celdas: B5:F5 y B9:F9

Estos controles los asociaremos a cada una de las celdas sobre las que se sitúa, esto es, el control en la celda B5 se vincula a B5:

Ábaco Soroban en Excel



Y así sucesivamente para los 10 controles.

Los controles de la fila superior B5:F5 deberán tener como propiedades:
Valor mínimo: 0
Valor máximo: 1


Mientras que los controles de las celdas B9:F9 deben ser:
Valor mínimo: 0
Valor máximo: 4


Los controles superiores representarán la parte de arriba del ábaco japones, que mostrarán los incrementos/decrementos de los múltiplos de 5 (5, 50, 500, 5000, 50000, etc)
Y los controles inferiores, la parte de abajo del ábaco, que muestra los aumentos/disminuciones de unidades, decenas, centenas, millares, etc...


Vamos a por la parte de funciones, así pues en el rango superior B6:F7 insertamos la fórmula:
=SI(B$5+1<=2-FILAS(B$6:B6);0;SI(B$5>2-FILAS(B$6:B6);B$4;""))

Con esta fórmula conseguimos que cuando pulsemos el control hacia arriba el valor de la cuenta correspondiente baje.
En este caso he optado por añadir formulación para que si no está pulsado la 'cuenta' aparezca como cero y solo si está pulsada la cuenta baje con el valor asignado.
Fíjate en el ejemplo siguiente donde se representa el número 5.050 y donde la cuenta de 5.000 y la de 50 están bajadas

Ábaco Soroban en Excel



De forma similar formulamos la parte inferior del ábaco en el rango B10:F14 incluyendo la fórmula:
=SI(B$9+1=FILAS(B$10:B10);B$8;""))

Ajustándose a lo que necesitamos, es decir, que cuando subimos la 'cuenta' aparezca el valor de ésta, y cuando está bajada muestre un cero.
En el ejemplo representamos el número 1.234

Ábaco Soroban en Excel



Para verificar la corrección del uso aplicamos en la celda I6 la fórmula:
=SUMAPRODUCTO(B4:F4;B5:F5)+SUMAPRODUCTO(B8:F8;B9:F9)

donde simplemente multiplicamos el valor representativo de las cuentas (B4:F4 y B8:F8) por el número de cuentas subidos o seleccionados en cada caso...


Terminada la formulación aplicaremos un formato condicional con fórmula, para ello seleccionaremos el rango B6:F7 y B10:F14
Nos aseguraremos que la celda activa sea B6 y añadiremos la fórmula:
=Y(B6<>"";B6>0)

con el formato deseado...

Ábaco Soroban en Excel



Listos para calcular con nuestro ábaco soroban.

Por ejemplo para mostrar el 13.131 subiríamos/bajaríamos las cuentas...

Ábaco Soroban en Excel