Formula Recta De Regresion En Excel
Mina Spartin
Muchas leyes fsicas obedecen un comportamiento lineal, que quiere decir que una variable depende de otra como una funcin de primer grado. As, por ejemplo, la ley de Hooke nos relaciona la fuerza aplicada a un muelle con la elongacin de ste respecto a la posicin de equilibrio)
Esta es una ley terica, que se apoya en evidencias experimentales. Esto quiere decir que si, para un muelle dado, medimos la elongacin para distintas masas concretas, deberamos obtener una serie de puntos alineados. A partir de la recta que pasa por estos puntos podemos determinar la constante k del muelle o su longitud de equilibrio, por ejemplo.
Sin embargo, solo dos medidas puede ser demasiado poco. Si han salido mal, por la razn que sea, lo que se deduzca de ellas estar igualmente mal. Por ello, es preferible hacer ms medidas, cuantas ms mejor. De esta forma, los posibles errores se reducen, ya que si en algunos hemos medido de ms, en otros se medir de menos, y el efecto de una mala medida se amortigua.
El mtodo de los mnimos cuadrados consiste entonces en hallar los valores de a y b que hacen mnima esta suma de cuadrados. De ah el nombre del mtodo. El resultado de esta minimizacin produce los resultados que se ven en las secciones siguientes.
Supongamos que tenemos un resorte del cual vamos suspendiendo diferentes pesos y medimos, para cada pesa, la longitud total del muelle, empleando una regla graduada en milmetros. Obtenemos, tras una serie de medidas, la siguiente tabla:
De una tabla como esta hay que resaltar que en la cabecera hay que indicar las unidades y el error; se entiende que lo que se ponga en la cabecera se aplica a todas las celdas de la columna. Solo si las unidades o errores fueran diferentes para cada dato habra que indicarlo en cada celda
Para hacer nuestro anlisis, abrimos un fichero nuevo de Excel, en el cual introducimos los valores. Por correccin (aunque no hace falta para los clculos; evita confusiones) aadimos una cabecera con las magnitudes y unidades.
Antes de hacer ningn clculo, conviene hacer una grfica de los datos, que refinaremos ms tarde. El objeto de esta grfica es comprobar si los puntos estn ms o menos alineados o si hay que descartar alguno que se salga de la tendencia.
En este ejemplo, los lmites de los ejes por defecto que aplica el programa no son los apropiados para ver con claridad si los datos estn alineados. Esta es una leccin permanente: hacer una grfica no consiste en aceptar de forma acrtica los resultados de la aplicacin. Consiste en ir fijando los parmetros adecuadamente hasta que salga la grfica deseada.
En nuestro caso, pinchamos con el botn de la derecha del ratn en uno de los nmeros del eje de ordenadas. Esto hace aparecer la opcin "Dar formato al eje...". Dentro de esta, podemos seleccionar los lmites de los ejes. Puesto que nuestros datos valen como mnimo 15.6 y como mximo 19.4 tomamos como mnimo un valor fijo de 15 y como mximo uno fijo de 20. De esta forma se ve mucho ms claro.
En las prcticas de laboratorio de fsica, los comportamientos lineales son muy precisos por lo que el coeficiente de correlacin est muy cerca de 1 casi siempre. En este caso, la verdadera informacin la obtenemos de cunto se acerca a la unidad. Para ello, en lugar de redondearlo a partir de un clculo de errores, lo que hacemos es conservar tantos nueves como contenga tras el punto decimal y la primera cifra que no sea un 9. As una r = 0.992 sera una recta menos alineada que r = 0.99997, ya que la primera es de solo "dos nueves" y la segunda de "cuatro nueves".
El clculo anterior de la pendiente hay que refinarlo calculando la incertidumbre de sta. Esta incertidumbre se debe tanto al hecho de que cada dato es incierto (por las limitaciones propias del proceso de medida) como al hecho de que se trata de una estimacin estadstica. Para que fuera exacta deberamos reunir infinitos datos.
Ahora corresponde redondear empleando las mismas tcnicas que para cualquier otra magnitud con incertidumbre. Escribimos el valor de la magnitud y el de su incertidumbre uno bajo el otro, alineando los puntos decimales
Una parte importante de todo clculo de rectas de mejor ajuste es su representacin grfica. De hecho, en las prcticas de Fsica se entiende que siempre que se pida calcular una recta por mnimos cuadrados, hay que adjuntar la grfica correspondiente.
El trazado de una grfica tiene unas normas de presentacin especficas, que hay que respetar. No puede uno limitarse a aceptar lo que produce Excel sin ms. El objeto de una grfica no es simplemente ilustrar un fenmeno. Se trata de una herramienta muy til. Para ello, debe presentar toda la informacin necesaria de forma clara.
Recordemos que ya habamos trazado una grfica de los puntos experimentales. Para ampliarla la movemos a una hoja nueva. Pulsando con el botn de la derecha en una zona libre de la grfica aparece un men contextual. Mediante la opcin "Mover grfico" (o "Ubicacin...") podemos elegir el cambiarlo de hoja. De este modo obtenemos una grfica que ocupa toda una pgina
En una representacin sobre papel milimetrado, la posicin de los puntos se puede leer fcilmente a partir de su posicin en la cuadrcula. En una grfica producida por ordenador se aplica el mismo principio. Debe haber una cuadrcula que permita localizar de forma sencilla los valores aproximados de los datos experimentales, sin necesidad de indicar los valores numricos explcitamente.
El primer paso para trazar la cuadrcula consiste en establecer los lmites de los ejes. En una grfica no hay ninguna obligacin de que el punto (0,0) est en la esquina. Por el contrario, lo ms probable es que ni aparezca en la grfica. Los lmites de la grfica se eligen tomando dos valores ms o menos "redondos", uno de los cuales est algo por debajo del menor dato y otro algo por encima del mayor.
En Excel, para fijar los lmites, marcamos en un nmero del eje que queremos modificar y elegimos la opcin "Dar formato a eje...". En el formulario que aparece seleccionamos la opcin de fijar manualmente los lmites superior e inferior, as como las divisiones principales y secundarias (las principales corresponden a la cuadrcula gruesa y la secundaria a la fina).
Los puntos experimentales deben ser claramente visibles. Por ello, no es conveniente un marcador (el smbolo usado para marcarlos), que sea un punto gordo, pues no deja clara la posicin exacta del dato.
Por ello, debemos cambiar el formato de la serie de datos (pulsando con el botn derecho en uno de los puntos experimentales y eligiendo "Dar formato a serie de datos...") y elegir un marcador en forma de aspa o similar, que sea adems de un color oscuro, para que se va bien al imprimirlo. Existen muchas opciones que pueden modificarse (color del marcador, grosor de sus lneas, tamao del marcador, etc.)
Por ltimo, queda trazar la recta de mejor ajuste. En principio, puede hacerse a partir de los datos de a y b hallados antes. Sin embargo, en Excel es mucho ms sencillo: basta pulsar de nuevo en un punto experimental con el botn derecho del ratn y elegir la opcin "Agregar lnea de tendencia...". Se abre entonces un men de opciones que nos permiten elegir el tipo de ajuste (lineal, en la prctica totalidad de los casos), el color de la lnea, su grosor, etc.
La recta de mejor ajuste debe ser claramente visible y debe dejar ver los marcadores de los puntos experimentales, por lo que hay que buscar que su color sea diferente y que los marcadores sobresalgan claramente de la lnea.
Dentro de los modelos causales o asociativos encontramos el anlisis de regresin o regresin lineal, que es un mtodo con enfoque cuantitativo que nos permite pronosticar la demanda. Agrupa una variable dependiente (la demanda) con una o ms variables independientes a travs de una ecuacin lineal.
El objetivo del anlisis de regresin como mtodo causal es pronosticar la demanda a partir de una o ms causas (variables independientes), las cuales pueden ser por ejemplo el tiempo, precios del producto o servicio, precios de la competencia, economa del pas, acciones del gobierno o fomentos publicitarios.
El anlisis de regresin es pertinente cuando se evidencia una tendencia en los datos histricos del pronstico. Cmo? Dibuja los datos de meses anteriores, los de la demanda real. Si lo ests haciendo en Excel 2010 o superior, da click derecho sobre la serie de datos graficados y luego click en agregar lnea de tendencia. Si no, simplemente analiza la tendencia de los datos, es creciente o decreciente?
Entre ms cercano se encuentre el coeficiente de correlacin a +1 o -1 ms fuerte ser la tendencia y ms apropiado ser aplicar un modelo de regresin lineal. Por ejemplo si la correlacin es igual a 1, observaremos que la relacin entre las variables es directamente proporcional, en el sentido que si uno aumenta, la otra tambin lo har.
Lo primero es estimar los parmetros. Lo podemos lograr con el mtodo de mnimos cuadrados, que nos permite encontrar la recta que mejor se ajusta a un conjunto de datos dados. En nuestro caso, este conjunto est dado por las ventas trimestrales (variable dependiente). La variable independiente es el tiempo. Vamos entonces a la siguiente frmula para determinar a y b:
Fjate que el valor de b vara de 63,64 a 63,657 siendo este ltimo el resultado si se toman todos los decimales de las variables que participan en su calculo. Para tener resultados ms fiables usamos el valor de b calculado con todos los decimales, tal como lo aprecias cuando hallamos a. Fjate adems que pasa lo mismo con el valor calculado de a y el valor que se obtiene cuando en la operacin estn todos los decimales.
Una medida apropiada para medir el error en regresin lineal es el error estndar de estimacin (Sy,x), que nos permite determinar la variabilidad en torno a la recta de regresin.
La siguiente es una plantilla en excel para pronosticar demanda a travs del mtodo de anlisis de regresin. Escribe nicamente sobre las celdas de color blanco: el periodo (numrico) y la demanda de cada uno. Las celdas de color gris son de clculo automtico.
d3342ee215