Autor Tema: Usando regresión lineal para relacionar dos variables... con excel...  (Leído 9173 veces)

0 Usuarios y 1 Visitante están viendo este tema.

Desconectado firepic

  • Colaborador
  • PIC24H
  • *****
  • Mensajes: 1130
    • JC Servicios
Este tema surgió originalmente en este hilo:
http://www.todopic.com.ar/foros/index.php?topic=21831.0

Pero decidí abrir este nuevo hilo aquí porque esto es una "técnica" que podemos usar en algunos de nuestros proyectos....  cuando tenemos un sensor del que obtenemos un valor variable (generalmente tensión) y éste es proporcional a otra variable que deseamos medir (temperatura, presión, lo que sea)... si hacemos varias medidas con valores conocidos de la variable que deseamos medir, y lo graficamos... y vemos que la gráfica es bastante cercana a una línea recta... entonces podemos usar este método de "regresión lineal" para obtener una ecuación matemática que relaciona ambas variables, y usar esta ecuación en nuestro programa (claro, eso si el fabricante del sensor no proporciona la relación matemática, de lo contrario obviamente usamos la que él nos da).

Bueno, la idea es obtener la ecuación de una recta y=mx+b.... entonces hay que conseguir "m" y "b"... las calculadoras tienen un modo llamado REG... que permiten calcular regresion lineal y logarítmica, proporcionándonos los valores de m y b... en el hilo original pueden encontrar una breve explicación de cómo usar este modo... sin embargo, Excel también lo hace.... tal vez un poco más sencillo para los que estamos acostumbrados a las PCs... explico el método:

En primer lugar, copiamos los valores de X y Y, y graficamos (para asegurarnos que el comportamiento es lineal y puede usarse este tipo de regresión).
Acá pueden ver ese primer paso, como verán estoy usando los datos que dió Alex de la temperatura en ºC (sería la variable X o independiente) y tensión en mV (sería la variable Y o dependiente):


(No hagan caso de la tercera columna que contiene ceros, eso era un experimento que estaba haciendo).

Luego, nos paramos en una celda (yo usé la B17) y escribimos el nombre de la función: "ESTIMACION.LINEAL" y abrimos un paréntesis...
En lo que hagamos eso nos saldrá abajo el nombre de la función con los argumentos que debemos introducir, con el orden en que deben ir.

El primer parámetro es "conocido_y"... entonces seleccionamos todos los valores de Y, en este caso los valores de tensión tabulados... y colocamos ";"
El segundo parámetro es "conocido_x"... seleccionamos todos los valores de X, acá los valores de temperatura.... y escribimos ";;" (se escriben dos para omitir el tercer argumento, "constante", de esta forma la función cogerá el valor por defecto que es el que nos interesa, para que nos de tanto el valor de la pendiente como del punto de intersección con el eje y).
El cuarto parámetro (como dijimos, hemos omitido el tercero) es "estadística".... escribimos "FALSO" y cerramos el paréntesis... y listo, le damos "enter" o "intro"...
Antes de haberle dando "enter" debimos haber visto algo así:



Cuando le damos enter nos aparecerá un valor en la celda... pero aún nos falta...
Seleccionamos la celda en la que escribimos la ecuación y la celda adyacente a la derecha, de esta forma:



Después de haber seleccionado las dos celdas, presionamos la tecla F2, y luego le damos CTRL+SHIFT+ENTER....
En lo que hagamos eso deben aparecer dos valores en las celdas seleccionadas, así:



Esos valores corresponden a la pendiente (m) y el punto de intersección con el eje y (b), tal de completar la ecuación y=mx+b...
Ahora que tenemos los valores de las dos constantes, podemos obtener valores calculados de y a partir de la variable independiente x...
Hagamos entonces una ecuación para ello, en la columna a la derecha de los valores de Y. Yo la llamé "y=mx+b"....
La función que tienen que escribir es la que se muestra acá:



Justamente, vamos a hacer mx+b... donde m y b son los valores obtenidos anteriormente, y X los valores de temperatura tabulados.
Como m y b son constantes, debemos darle F4 después de seleccionar la celda correspondiente. Cuando hagamos esto aparecerán unos símbolos de "$" que significan que la celda es una constante en la ecuación.
Ahora arrastramos hacia abajo para que las demás celdas adquieran la misma ecuación y se coloquen los valores correspondientes.
Si graficamos ahora los valores de Y calculados versus los valores de X que ya teníamos, tenemos ahora la siguiente gráfica:



La de la izquierda es la gráfica a partir de los valores reales muestreados, y la de la derecha a partir de los valores calculados con la ecuación de regresión lineal.
¿Bastante parecidas no? Claro, un ligero error, pero aceptable. Cuando el error es muy grande hay que descartar este método de regresión y usar otra del tipo exponencial, logarítmica, etc...

Bueno, como dice el conejo: esto es todo amigos!  :D
Saludos, nos leemos!  :mrgreen:
« Última modificación: 15 de Junio de 2008, 16:33:08 por firepic »
"Por la presunción solo se ocasiona una lucha, pero con los que consultan juntos hay sabiduría" (Proverbios 13:10).
Visita Mi Sitio Web

Desconectado firepic

  • Colaborador
  • PIC24H
  • *****
  • Mensajes: 1130
    • JC Servicios
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #1 en: 15 de Junio de 2008, 16:43:17 »
Olvidé dejarles el archivo de excel...
Desde aquí lo pueden descargar: http://www.4shared.com/file/51265104/4c948d12/Regresion_Lineal.html
Saludos, nos leemos!  :mrgreen:
"Por la presunción solo se ocasiona una lucha, pero con los que consultan juntos hay sabiduría" (Proverbios 13:10).
Visita Mi Sitio Web

Desconectado raraavis

  • PIC10
  • *
  • Mensajes: 47
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #2 en: 15 de Junio de 2008, 21:50:37 »
Gracias firepic !    :-/


Excelente explicación, ahora si entendí todo. y gracias por el ejemplo de Excel.

Saludos.
« Última modificación: 15 de Junio de 2008, 22:50:50 por raraavis »

Desconectado firepic

  • Colaborador
  • PIC24H
  • *****
  • Mensajes: 1130
    • JC Servicios
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #3 en: 15 de Junio de 2008, 22:17:38 »
Gracias raraavis, me alegra que te haya gustado y lo hayas entendido.
Saludos, nos leemos!  :mrgreen:
"Por la presunción solo se ocasiona una lucha, pero con los que consultan juntos hay sabiduría" (Proverbios 13:10).
Visita Mi Sitio Web

Desconectado Nocturno

  • Administrador
  • DsPIC33
  • *******
  • Mensajes: 17891
    • MicroPIC
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #4 en: 16 de Junio de 2008, 01:35:43 »
Yo suelo usar otro método y creo que es mucho más rápido:
1.- Se pinta el gráfico tal y como ya lo haces tú
2.- Botón derecho sobre la curva para abrir el menú contextual
3.- Seleccionas: "Agregar línea de tendencia"
4.- En la ventana que se abre, seleccionas el tipo de línea de tendencia (lineal, exponencial, logarítmica,...) y marcas el check "Presentar ecuación en el gráfico"

Y ya está, al darle a Aceptar, tendremos la línea de tendencia y la ecuación visible.
Un saludo desde Sevilla, España.
Visita MicroPIC                                                                                        ɔ!doɹɔ!ɯ ɐʇ!s!ʌ

Desconectado firepic

  • Colaborador
  • PIC24H
  • *****
  • Mensajes: 1130
    • JC Servicios
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #5 en: 16 de Junio de 2008, 09:15:35 »
Gracias Nocturno!
Yo conocía ese método... pero cuando hice el ejemplito y traté de hacerlo así, me salía deshabilitada la opción "Agregar línea de tendencia"... no sabía por qué...
Ahora sí sé. Era porque tenía el tipo de gráfico incorrecto.
El tipo de gráfico debe ser este:



Yo tenía seleccionado el que está a la derecha del resaltado. Por eso no funcionaba.
Ahora sí lo he podido hacer con el método que tú usas, pero me ha surgido una duda. Fíjate el resultado que obtengo:



La gráfica con los valores de X dados y los Y calculados es muy distinta que la original... estaré haciendo algo mal? Hay alguna opción que hay que configurar primero?
Podrías explicarlo, oh gran nocturno?

Saludos, nos leemos!  :mrgreen:
"Por la presunción solo se ocasiona una lucha, pero con los que consultan juntos hay sabiduría" (Proverbios 13:10).
Visita Mi Sitio Web

Desconectado firepic

  • Colaborador
  • PIC24H
  • *****
  • Mensajes: 1130
    • JC Servicios
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #6 en: 16 de Junio de 2008, 09:47:16 »
Y por cierto, es extraño que los valores de "m" y "b" que proporciona el método que tú mencionas sean diferentes a los que calcula la función estimacion.lineal...
Deberían ser iguales!  :shock: Porque si es la misma regresión lineal, entonces debe haber una única linea recta que describa la regresión, con una única pendiente y un única intersección con la ordenada... y ya me cansé de escribir la palabra "única"  :D

Bueno maestro nocturno, si sabes algo relacionado con este punto... sería bueno lo aclares porfis...  :lol:
Saludos, nos leemos!  :mrgreen:
"Por la presunción solo se ocasiona una lucha, pero con los que consultan juntos hay sabiduría" (Proverbios 13:10).
Visita Mi Sitio Web

Desconectado Nocturno

  • Administrador
  • DsPIC33
  • *******
  • Mensajes: 17891
    • MicroPIC
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #7 en: 16 de Junio de 2008, 11:42:05 »
Es así porque las X que considera la fórmula automática son 1,2,3..., n
Como las X de tu fórmula manual sí utiliza 10,20,40,... la tuya está saliendo mejor que la automática.
Un saludo desde Sevilla, España.
Visita MicroPIC                                                                                        ɔ!doɹɔ!ɯ ɐʇ!s!ʌ

Desconectado firepic

  • Colaborador
  • PIC24H
  • *****
  • Mensajes: 1130
    • JC Servicios
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #8 en: 16 de Junio de 2008, 12:50:44 »
Gracias por la respuesta, maestro nocturno!
Coloqué valores a la temperatura 1,2,3... y ahora sí se corresponde perfecto!  :-/

Entonces cuando tenemos valores de x 1,2,3... hacerlo por tu método, usando la herramienta en el gráfico de "Agregar línea de tendencia"...
Cuando los valores de x no son 1,2,3...usar la ecuación de estimacion.lineal para conseguir la pendiente y la intersección con la ordenada...

Te parece buena conclusión? O se puede realizar alguna configuración especial para que la herramienta "Agregar línea de tendencia" tome los valores de X reales?
Saludos, nos leemos!  :mrgreen:
"Por la presunción solo se ocasiona una lucha, pero con los que consultan juntos hay sabiduría" (Proverbios 13:10).
Visita Mi Sitio Web

Desconectado Nocturno

  • Administrador
  • DsPIC33
  • *******
  • Mensajes: 17891
    • MicroPIC
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #9 en: 16 de Junio de 2008, 16:55:55 »
Supongo que se podrá, pero no he podido investigarlo.
Un saludo desde Sevilla, España.
Visita MicroPIC                                                                                        ɔ!doɹɔ!ɯ ɐʇ!s!ʌ

Desconectado jorgeaf_59

  • PIC10
  • *
  • Mensajes: 26
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #10 en: 17 de Junio de 2008, 22:08:26 »
Hola, es mi mi primer mensaje al foro, realmente se aprende muchísimo leyendo los aportes de tantos desinteresados colaboradores.
Volviendo al tema del mensaje, creo que hay un pequeño detalle: para independizarse de si los valores del eje X están equiespaciados,  probaría con el tipo de gráfico Dispersión XY.
Saludos,

Jorge

Desconectado firepic

  • Colaborador
  • PIC24H
  • *****
  • Mensajes: 1130
    • JC Servicios
Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #11 en: 17 de Junio de 2008, 22:45:50 »
Bienvenido al foro jorge!
Pues eso se llama entrar por la puerta grande. Le has ganado de mano al maestro nocturno  :D
Es en broma nocturno, para ganarte tí no sé qué hay que hacer... será fumarse una, porque por algo eres un "héroe todopic"  :lol:
Bueno pero gracias por ese dato jorge...
Funcionó perfecto.
Pues usé la gráfica de "Dispersión XY" y luego realicé los pasos de nocturno, y listo! Los valores de la ecuación de m y b son los mismos que había calculado con estimacion.lineal.
Aquí dejo un pantallazo del resultado:



Excelente este dato para todos!  :-/
Ok saludos, nos leemos!  :mrgreen:
"Por la presunción solo se ocasiona una lucha, pero con los que consultan juntos hay sabiduría" (Proverbios 13:10).
Visita Mi Sitio Web

Desconectado Nocturno

  • Administrador
  • DsPIC33
  • *******
  • Mensajes: 17891
    • MicroPIC
* Re: Usando regresión lineal para relacionar dos variables... con excel...
« Respuesta #12 en: 18 de Junio de 2008, 02:28:43 »
Buen dato, Jorge. Muchas gracias
Un saludo desde Sevilla, España.
Visita MicroPIC                                                                                        ɔ!doɹɔ!ɯ ɐʇ!s!ʌ


 

anything