Como Calcular La Ecuacion De La Recta De Regresion En Excel
Leana Eckes
En este artculo se describen la sintaxis de la frmula y el uso de la funcin ESTIMACION.LINEAL en Microsoft Excel. Encontrar vnculos con ms informacin sobre cmo crear grficos y realizar un anlisis de regresin en la seccin Vea tambin.
La funcin ESTIMACION.LINEAL calcula las estadsticas de una lnea con el mtodo de los "mnimos cuadrados" para calcular la lnea recta que mejor se ajuste a los datos y despus devuelve una matriz que describe la lnea. Tambin puede combinar ESTIMACION.LINEAL con otras funciones para calcular las estadsticas de otros tipos de modelos que son lineales en los parmetros desconocidos, incluidas series polinmicas, logartmicas, exponenciales y de potencias. Debido a que esta funcin devuelve una matriz de valores, debe ser especificada como frmula de matriz. Encontrar las instrucciones correspondientes tras los ejemplos de este artculo.
si hay varios rangos de valores x, donde los valores y dependientes son funcin de los valores x independientes. Los valores m son coeficientes que corresponden a cada valor x, y b es un valor constante. Observe que y, x y m pueden ser vectores. La matriz que devuelve la funcin ESTIMACION.LINEAL es mn,mn-1,...,m1,b. ESTIMACION.LINEAL tambin puede devolver estadsticas de regresin adicionales.
El coeficiente de determinacin. Compara los valores y calculados y reales, y los rangos con valor de 0 a 1. Si es 1, hay una correlacin perfecta en la muestra, es decir, no hay diferencia entre el valor y calculado y el valor y real. En el otro extremo, si el coeficiente de determinacin es 0, la ecuacin de regresin no es til para predecir un valor y. Para obtener informacin sobre cmo se calcula2 , vea la seccin "Observaciones" ms adelante en este tema.
Grados de libertad. Use los grados de libertad para encontrar valores F crticos en una tabla estadstica. Compare los valores que encuentre en la tabla con la estadstica F devuelta por ESTIMACION.LINEAL para determinar un nivel de confianza para el modelo. Para obtener informacin sobre el clculo de df, vea la seccin "Observaciones" ms adelante en este tema. El ejemplo 4 muestra el uso de F y df.
La ecuacin de una lnea recta es y = mx + b. Si conoce los valores de m y b, puede calcular cualquier punto de la lnea insertando el valor y o x en esa ecuacin. Tambin puede usar la funcin TENDENCIA.
La exactitud de la lnea calculada por la funcin ESTIMACION.LINEAL depende del grado de dispersin de los datos. Cuanto ms lineales sean los datos, ms exacto ser el modelo de ESTIMACION.LINEAL. ESTIMACION.LINEAL usa el mtodo de los mnimos cuadrados para determinar el mejor ajuste para los datos. Si solo tiene una variable x independiente, los clculos para m y b se basan en las frmulas siguientes:
Las funciones de ajuste de lnea y curva ESTIMACION.LINEAL y ESTIMACION.LOGARI PUEDEN calcular la mejor lnea recta o curva exponencial que se ajuste a sus datos. Sin embargo, tiene que decidir cul de los dos resultados se ajusta mejor a sus datos. Puede calcular TENDENCIA(known_y,known_x) para una lnea recta o CRECIMIENTO(known_y, known_x) para una curva exponencial. Estas funciones, sin el argumento de la new_x , devuelven una matriz de valores y pronosticados a lo largo de esa lnea o curva en los puntos de datos reales. Despus, puede comparar los valores pronosticados con los valores reales. Es posible que desee representarlos en un grfico para realizar una comparacin visual.
En el anlisis de regresin, Excel calcula para cada punto la diferencia al cuadrado entre el valor y calculado para ese punto y su valor y real. La suma de estas diferencias al cuadrado se denomina suma de los cuadrados residual, ssresid. Excel calcula a continuacin la suma total de los cuadrados, sstotal. Si omite el argumento constante o es VERDADERO, la suma total de los cuadrados es la suma de las diferencias al cuadrado entre los valores y reales y la media de los mismos. Cuando el argumento constante = FALSO, la suma total de los cuadrados es la suma de los cuadrados de los valores y reales (sin restar el valor y medio de cada valor y individual). Puede hallar la suma de regresin de los cuadrados, ssreg, a partir de ssreg = sstotal - ssresid. Cuanto menor sea la suma residual de los cuadrados, en comparacin con la suma total de los cuadrados, mayor ser el valor del coeficiente de determinacin, r2, que es un indicador de lo bien que la ecuacin resultante del anlisis de regresin explica la relacin entre las variables. El valor de r2 es igual a ssreg/sstotal.
Cuando especifique como argumento una constante de matriz (como conocido_x), use comas para separar los valores contenidos en una misma fila y puntos y comas para separar las filas. Los caracteres separadores pueden ser diferentes segn la configuracin regional.
El algoritmo subyacente usado en la funcin ESTIMACION.LINEAL es diferente del algoritmo subyacente usado en las funciones PENDIENTE e INTERSECCION.EJE. La diferencia entre estos algoritmos puede producir resultados distintos cuando los datos son indeterminados y colineales. Por ejemplo, si los puntos de datos del argumento conocido_y son 0 y los puntos de datos del argumento conocido_x son 1:
ESTIMACION.LINEAL devuelve un valor 0. El algoritmo de la funcin ESTIMACION.LINEAL est diseado para devolver resultados razonables para los datos colineales y, en este caso, se puede encontrar al menos una respuesta.
Adems de usar ESTIMACION.LOGARITMICA para calcular estadsticas para otros tipos de regresin, puede usar ESTIMACION.LINEAL para calcular un rango de tipos de regresin diferentes escribiendo funciones de las variables x e y como series x e y para ESTIMACION.LINEAL. Por ejemplo, la frmula siguiente:
El valor de la prueba F que devuelve la funcin ESTIMACION.LINEAL y el que devuelve la funcin PRUEBA.F son diferentes. ESTIMACION.LINEAL devuelve la estadstica F, mientras que PRUEBA.F devuelve la probabilidad.
Copie los datos del ejemplo en la siguiente tabla y pguelos en la celda A1 de una nueva hoja de clculo de Excel. Para que las frmulas muestren los resultados, seleccinelas, presione F2 y luego ENTRAR. Si lo necesita, puede ajustar el ancho de las columnas para ver todos los datos.
Copie los datos del ejemplo en la siguiente tabla y pguelos en la celda A1 de una nueva hoja de clculo de Excel. Para que las frmulas muestren los resultados, seleccinelas, presione F2 y luego ENTRAR. Si lo necesita, puede ajustar los anchos de la columna para ver todos los datos.
En el ejemplo anterior, el coeficiente de determinacin, o r2, es 0,99675 (vea la celda A17 en el resultado de ESTIMACION.DE.LNEA), que indicara una relacin estrecha entre las variables independientes y el precio de venta. Puede usar la estadstica F para determinar si estos resultados, con este valor r2 tan alto, se produjeron por azar.
Suponga por un momento que en realidad no existe relacin entre las variables, pero que ha extrado una muestra peculiar de 11 edificios de oficinas que hace que el anlisis estadstico demuestre una relacin marcada. El trmino "alfa" se usa para la probabilidad de llegar a la conclusin errnea de que existe una relacin.
Otra prueba hipottica determinar si cada coeficiente de la pendiente es til para calcular el valor tasado de un edificio de oficinas del ejemplo 3. Por ejemplo, para probar si el coeficiente de antigedad es significativo estadsticamente, divida -234,24 (coeficiente de la pendiente de antigedad) por 13,268 (el error estndar calculado de los coeficientes de antigedad en la celda A15). El siguiente es el valor t observado:
Si el valor absoluto de t es suficientemente alto, puede deducirse que el coeficiente de la pendiente es til para calcular el valor tasado del edificio de oficinas del ejemplo 3. La siguiente tabla muestra los valores absolutos de los 4 valores t observados.
Si consulta una tabla de un manual de estadstica, observar que el valor t crtico, de dos colas, con 6 grados de libertad y alfa = 0,05 es 2,447. Este valor crtico puede encontrarse tambin con la funcin DISTR.T.INV de Excel. DISTR.T.INV(0,05,6) = 2,447. Puesto que el valor absoluto de t, 17,7, es superior a 2,447, la antigedad es una variable importante para calcular el valor tasado de un edificio de oficinas. El significado estadstico de cada una de las dems variables independientes puede probarse de forma similar. Los siguientes son los valores t observados para cada una de las variables independientes.
Todos estos valores tienen un valor absoluto superior a 2,447; por tanto, todas las variables usadas en la ecuacin de regresin son tiles para predecir el valor tasado de los edificios de oficinas de esta rea.
La minera de datos estaba en desuso en SQL Server 2017 Analysis Services y ahora se ha interrumpido en SQL Server 2022 Analysis Services. La documentacin no se actualiza para las caractersticas en desuso e interrumpidas. Para ms informacin, consulte Compatibilidad con versiones anteriores de Analysis Services.
El algoritmo de regresin lineal de Microsoft es una variacin del algoritmo de rboles de decisin de Microsoft que le ayuda a calcular una relacin lineal entre una variable dependiente e independiente y, a continuacin, usar esa relacin para la prediccin.
Cada punto de datos del diagrama tiene un error asociado con su distancia con respecto a la lnea de regresin. Los coeficientes a y b de la ecuacin de regresin ajustan el ngulo y la ubicacin de la recta de regresin. Puede obtener la ecuacin de regresin ajustando a y b hasta que la suma de los errores asociados a todos los puntos alcance su valor mnimo.
Hay otros tipos de regresin que utilizan varias variables y tambin hay mtodos no lineales de regresin. Sin embargo, la regresin lineal es un mtodo til y conocido para modelar una respuesta a un cambio de algn factor subyacente.
d3342ee215