Hogar › Servicios › Cálculo de ecuación de regresión en excel. análisis de regresión en excel

Cálculo de ecuación de regresión en excel. análisis de regresión en excel

El análisis de regresión es un método de investigación estadística que le permite mostrar la dependencia de un parámetro particular de una o más variables independientes. En la era anterior a la informática, su uso era bastante difícil, especialmente cuando se trataba de grandes volúmenes de datos. Hoy, después de haber aprendido a crear regresión en Excel, puede resolver problemas estadísticos complejos en solo un par de minutos. A continuación se muestran ejemplos específicos del campo de la economía.

Tipos de regresión

Este concepto en sí se introdujo en las matemáticas en 1886. La regresión ocurre:

lineal;
parabólico;
sosegado;
exponencial;
hiperbólico;
demostrativo;
logarítmico.

Ejemplo 1

Consideremos el problema de determinar la dependencia del número de miembros del equipo que renuncian del salario promedio en 6 empresas industriales.

Tarea. En seis empresas se analizó el salario mensual medio y el número de empleados que renunciaban voluntariamente. En forma tabular tenemos:


		Número de personas que abandonan	Salario
			30.000 rublos
			35.000 rublos
			40.000 rublos
			45.000 rublos
			50.000 rublos
			55.000 rublos
			60.000 rublos

Para la tarea de determinar la dependencia del número de trabajadores que renuncian del salario promedio en 6 empresas, el modelo de regresión tiene la forma de la ecuación Y = a 0 + a 1 x 1 +...+a k x k, donde x i son los variables influyentes, a i son los coeficientes de regresión y k es el número de factores.

Para este problema, Y es el indicador de empleados que renuncian y el factor que influye es el salario, que denotamos por X.

Usando las capacidades del procesador de hojas de cálculo de Excel.

El análisis de regresión en Excel debe ir precedido de la aplicación de funciones integradas a los datos tabulares existentes. Sin embargo, para estos fines es mejor utilizar el muy útil complemento "Analysis Pack". Para activarlo necesitas:

desde la pestaña "Archivo" vaya a la sección "Opciones";
en la ventana que se abre, seleccione la línea "Complementos";
haga clic en el botón “Ir” ubicado debajo, a la derecha de la línea “Administración”;
Marque la casilla junto al nombre "Paquete de análisis" y confirme sus acciones haciendo clic en "Aceptar".

Si todo se hace correctamente, el botón requerido aparecerá en el lado derecho de la pestaña "Datos", ubicada encima de la hoja de cálculo de Excel.

en excel

Ahora que tenemos a mano todas las herramientas virtuales necesarias para realizar cálculos econométricos, podemos empezar a solucionar nuestro problema. Para hacer esto:

Haga clic en el botón "Análisis de datos";
en la ventana que se abre, haga clic en el botón "Regresión";
en la pestaña que aparece, ingrese el rango de valores para Y (el número de empleados que renuncian) y para X (sus salarios);
Confirmamos nuestras acciones presionando el botón “Ok”.

Como resultado, el programa completará automáticamente una nueva hoja de cálculo con datos de análisis de regresión. ¡Prestar atención! Excel le permite configurar manualmente la ubicación que prefiera para este propósito. Por ejemplo, podría ser la misma hoja donde se encuentran los valores Y y X, o incluso un nuevo libro de trabajo diseñado específicamente para almacenar dichos datos.

Análisis de resultados de regresión para R cuadrado

En Excel, los datos obtenidos durante el procesamiento de los datos en el ejemplo considerado tienen la forma:

En primer lugar, debes prestar atención al valor de R cuadrado. Representa el coeficiente de determinación. En este ejemplo, R-cuadrado = 0,755 (75,5%), es decir, los parámetros calculados del modelo explican la relación entre los parámetros considerados en un 75,5%. Cuanto mayor sea el valor del coeficiente de determinación, más adecuado será el modelo seleccionado para una tarea específica. Se considera que describe correctamente la situación real cuando el valor de R cuadrado es superior a 0,8. Si R cuadrado<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Análisis de probabilidades

El número 64,1428 muestra cuál será el valor de Y si todas las variables xi en el modelo que estamos considerando se ponen a cero. En otras palabras, se puede argumentar que el valor del parámetro analizado también está influenciado por otros factores que no están descritos en un modelo específico.

El siguiente coeficiente -0,16285, ubicado en la celda B18, muestra el peso de la influencia de la variable X sobre Y. Esto significa que el salario mensual promedio de los empleados dentro del modelo considerado afecta el número de personas que abandonan con un peso de -0,16285, es decir el grado de su influencia es completamente pequeño. El signo "-" indica que el coeficiente es negativo. Esto es obvio, ya que todo el mundo sabe que cuanto mayor es el salario en la empresa, menos personas expresan el deseo de rescindir el contrato de trabajo o renunciar.

Regresión múltiple

Este término se refiere a una ecuación de relación con varias variables independientes de la forma:

y=f(x 1 +x 2 +…x m) + ε, donde y es la característica resultante (variable dependiente), y x 1, x 2,…x m son características de los factores (variables independientes).

Estimación de parámetros

Para la regresión múltiple (MR), se lleva a cabo mediante el método de mínimos cuadrados (OLS). Para ecuaciones lineales de la forma Y = a + b 1 x 1 +…+b m x m + ε construimos un sistema de ecuaciones normales (ver más abajo)

Para comprender el principio del método, considere el caso de dos factores. Entonces tenemos una situación descrita por la fórmula

De aquí obtenemos:

donde σ es la varianza del atributo correspondiente reflejado en el índice.

OLS es aplicable a la ecuación MR en una escala estandarizada. En este caso obtenemos la ecuación:

en el que t y, t x 1, ... t xm son variables estandarizadas, cuyos valores promedio son iguales a 0; β i son los coeficientes de regresión estandarizados y la desviación estándar es 1.

Tenga en cuenta que todos los β i en este caso se especifican como normalizados y centralizados, por lo que su comparación entre sí se considera correcta y aceptable. Además, es habitual descartar factores descartando aquellos con los valores de βi más bajos.

Problema que utiliza la ecuación de regresión lineal

Supongamos que tenemos una tabla de dinámica de precios para un producto específico N durante los últimos 8 meses. Es necesario tomar una decisión sobre la conveniencia de comprar un lote a un precio de 1.850 rublos/tonelada.


número de mes	nombre del mes	precio del producto sustantivo, masculino—
		1750 rublos por tonelada
		1755 rublos por tonelada
		1767 rublos por tonelada
		1760 rublos por tonelada
		1770 rublos por tonelada
		1790 rublos por tonelada
		1810 rublos por tonelada
		1840 rublos por tonelada

Para resolver este problema en el procesador de hojas de cálculo de Excel, es necesario utilizar la herramienta "Análisis de datos", ya conocida por el ejemplo presentado anteriormente. A continuación, seleccione la sección "Regresión" y configure los parámetros. Hay que recordar que en el campo “Intervalo de entrada Y” se debe ingresar un rango de valores para la variable dependiente (en este caso, precios de bienes en meses específicos del año), y en el campo “Intervalo de entrada X” - para la variable independiente (número de mes). Confirme la acción haciendo clic en "Aceptar". En una hoja nueva (si así se indica) obtenemos datos para la regresión.

Utilizándolos, construimos una ecuación lineal de la forma y=ax+b, donde los parámetros a y b son los coeficientes de la línea con el nombre del número del mes y los coeficientes y líneas "intersección Y" de la hoja con los resultados del análisis de regresión. Por tanto, la ecuación de regresión lineal (LR) para la tarea 3 se escribe como:

Precio del producto N = 11.714* número de mes + 1727,54.

o en notación algebraica

y = 11,714 x + 1727,54

Análisis de resultados

Para decidir si la ecuación de regresión lineal resultante es adecuada se utilizan los coeficientes de correlación múltiple (MCC) y de determinación, así como el test de Fisher y el test t de Student. En la hoja de cálculo de Excel con resultados de regresión, se denominan R múltiple, R cuadrado, estadístico F y estadístico t, respectivamente.

KMC R permite evaluar la cercanía de la relación probabilística entre las variables independientes y dependientes. Su alto valor indica una conexión bastante fuerte entre las variables "Número de mes" y "Precio del producto N en rublos por 1 tonelada". Sin embargo, la naturaleza de esta relación sigue siendo desconocida.

El cuadrado del coeficiente de determinación R2 (RI) es una característica numérica de la proporción de la dispersión total y muestra la dispersión de qué parte de los datos experimentales, es decir, Los valores de la variable dependiente corresponden a la ecuación de regresión lineal. En el problema que nos ocupa, este valor es igual al 84,8%, es decir, los datos estadísticos se describen con un alto grado de precisión mediante la DE resultante.

El estadístico F, también llamado prueba de Fisher, se utiliza para evaluar la importancia de una relación lineal, refutando o confirmando la hipótesis de su existencia.

(Prueba de Student) ayuda a evaluar la importancia del coeficiente para un término desconocido o libre de una relación lineal. Si el valor de la prueba t > tcr, entonces se rechaza la hipótesis sobre la insignificancia del término libre de la ecuación lineal.

En el problema considerado para el término libre, utilizando herramientas de Excel, se obtuvo que t = 169.20903, y p = 2.89E-12, es decir, tenemos probabilidad cero de que se rechace la hipótesis correcta sobre la insignificancia del término libre. . Para el coeficiente de la incógnita t=5,79405 y p=0,001158. En otras palabras, la probabilidad de que se rechace la hipótesis correcta sobre la insignificancia del coeficiente para una incógnita es del 0,12%.

Por tanto, se puede argumentar que la ecuación de regresión lineal resultante es adecuada.

El problema de la viabilidad de comprar un paquete de acciones.

La regresión múltiple en Excel se realiza utilizando la misma herramienta de Análisis de datos. Consideremos un problema de aplicación específico.

La dirección de la empresa NNN debe decidir sobre la conveniencia de adquirir una participación del 20% en MMM JSC. El costo del paquete (SP) es de 70 millones de dólares estadounidenses. Los especialistas de NNN han recopilado datos sobre transacciones similares. Se decidió evaluar el valor de la participación accionaria según parámetros tales, expresados en millones de dólares americanos, como:

cuentas por pagar (VK);
volumen de facturación anual (VO);
cuentas por cobrar (VD);
costo de los activos fijos (COF).

Además, se utiliza el parámetro de la deuda salarial de la empresa (V3 P) en miles de dólares estadounidenses.

Solución utilizando el procesador de hojas de cálculo Excel.

En primer lugar, debe crear una tabla de datos de origen. Se parece a esto:

llame a la ventana "Análisis de datos";
seleccione la sección "Regresión";
en el cuadro “Intervalo de entrada Y”, ingrese el rango de valores de las variables dependientes de la columna G;
Haga clic en el icono con una flecha roja a la derecha de la ventana "Intervalo de entrada X" y resalte el rango de todos los valores de las columnas B, C, D, F en la hoja.

Marque el elemento "Nueva hoja de trabajo" y haga clic en "Aceptar".

Obtener un análisis de regresión para un problema determinado.

Estudio de resultados y conclusiones.

"Recopilamos" la ecuación de regresión a partir de los datos redondeados presentados arriba en la hoja de cálculo de Excel:

SP = 0,103*SOF + 0,541*VO - 0,031*VK +0,405*VD +0,691*VZP - 265,844.

En una forma matemática más familiar, se puede escribir como:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Los datos de MMM JSC se presentan en la tabla:

Sustituyéndolos en la ecuación de regresión, obtenemos una cifra de 64,72 millones de dólares estadounidenses. Esto significa que no vale la pena comprar acciones de MMM JSC, ya que su valor de 70 millones de dólares está bastante inflado.

Como puede ver, el uso del procesador de hojas de cálculo Excel y la ecuación de regresión permitió tomar una decisión informada sobre la viabilidad de una transacción muy específica.

Ahora ya sabes qué es la regresión. Los ejemplos de Excel discutidos anteriormente lo ayudarán a resolver problemas prácticos en el campo de la econometría.

Esta es la forma más común de mostrar la dependencia de una variable de otras, por ejemplo, ¿cómo nivel del PIB del tamaño inversión extranjera o de Tasa de préstamo del Banco Nacional o de Precios de recursos energéticos clave..

El modelado permite mostrar la magnitud de esta dependencia (coeficientes), gracias a lo cual se puede realizar una previsión directa y realizar algún tipo de planificación en base a estas previsiones. Además, con base en el análisis de regresión, es posible tomar decisiones de gestión dirigidas a estimular causas prioritarias que influyan en el resultado final; el propio modelo ayudará a identificar estos factores prioritarios;

Vista general del modelo de regresión lineal:

Y=a 0 +a 1 x 1 +...+a k x k

Dónde a - parámetros de regresión (coeficientes), incógnita - factores que influyen, k - número de factores del modelo.

Datos iniciales

Entre los datos iniciales, necesitamos un determinado conjunto de datos que representen varios valores consecutivos o interconectados del parámetro final Y (por ejemplo, PIB) y el mismo número de valores de los indicadores cuya influencia estamos estudiando ( por ejemplo, inversión extranjera).

La figura anterior muestra una tabla con estos mismos datos iniciales, Y es un indicador de la población económicamente activa, y el número de empresas, el monto de inversión en capital y los ingresos de los hogares son factores influyentes, es decir, X.

Con base en la figura, también se puede llegar a la conclusión errónea de que el modelado solo puede tratarse de series de tiempo, es decir, series de momentos registradas secuencialmente en el tiempo, pero este no es el caso con el mismo éxito, se puede modelar en términos de estructura; , por ejemplo, los valores indicados en la tabla se pueden desglosar no por año, sino por región.

Para construir modelos lineales adecuados, es deseable que los datos de origen no tengan fuertes caídas o colapsos, en tales casos es recomendable realizar un suavizado, pero hablaremos de suavizado la próxima vez;

Paquete de análisis

Los parámetros de un modelo de regresión lineal también se pueden calcular manualmente utilizando el método de mínimos cuadrados ordinarios (OLS), pero esto lleva bastante tiempo. Esto se puede calcular un poco más rápido usando el mismo método usando fórmulas en Excel, donde el programa mismo hará los cálculos, pero aún tendrás que ingresar las fórmulas manualmente.

Excel tiene un complemento Paquete de análisis, que es una herramienta bastante poderosa para ayudar al analista. Este conjunto de herramientas, entre otras cosas, puede calcular parámetros de regresión utilizando el mismo método de mínimos cuadrados, con solo unos pocos clics. De hecho, se analizará más adelante cómo utilizar esta herramienta.

Activar el paquete de análisis

De forma predeterminada, este complemento está deshabilitado y no lo encontrará en el menú de pestañas, por lo que veremos paso a paso cómo activarlo.

En Excel, arriba a la izquierda, active la pestaña Archivo, en el menú que se abre, busque el elemento Opciones y haga clic en él.

En la ventana que se abre, a la izquierda, busque el elemento Complementos y actívalo, en esta pestaña en la parte inferior habrá una lista de control desplegable, donde por defecto estará escrito complementos de excel, habrá un botón a la derecha de la lista desplegable Ir, debes hacer clic en él.

Una ventana emergente le pedirá que seleccione los complementos disponibles; en ella deberá marcar la casilla; Paquete de análisis y al mismo tiempo, por si acaso, Encontrar una solución(también algo útil), y luego confirme su elección haciendo clic en el botón DE ACUERDO.

Instrucciones para encontrar parámetros de regresión lineal utilizando el paquete de análisis

Después de activar el complemento Analysis Pack, siempre estará disponible en la pestaña del menú principal Datos debajo del enlace Análisis de datos

En la ventana de herramientas activa Análisis de datos de la lista de posibilidades buscamos y seleccionamos Regresión

A continuación, se abrirá una ventana para configurar y seleccionar datos de origen para calcular los parámetros del modelo de regresión. Aquí es necesario indicar los intervalos de los datos iniciales, es decir, el parámetro que se describe (Y) y los factores que influyen en él (X), como se muestra en la figura siguiente, el resto de parámetros, en principio, son opcionales de configurar;

Después de seleccionar los datos de origen y hacer clic en el botón Aceptar, Excel produce cálculos en una nueva hoja del libro activo (a menos que se haya configurado lo contrario en la configuración), estos cálculos se ven así:

Las celdas clave están llenas de amarillo; estas son a las que debe prestar atención en primer lugar. Los demás parámetros importantes también son importantes, pero su análisis detallado probablemente requiera una publicación separada.

Entonces, 0,865 - Este R 2- coeficiente de determinación, que muestra que el 86,5% de los parámetros calculados del modelo, es decir, el modelo en sí, explican la dependencia y los cambios en el parámetro en estudio - Y de los factores estudiados - X. Si es exagerado, entonces Este es un indicador de la calidad del modelo. y cuanto más alto sea, mejor. Está claro que no puede ser superior a 1 y se considera bueno cuando R 2 es superior a 0,8, y si es inferior a 0,5, entonces se puede cuestionar con seguridad la razonabilidad de dicho modelo.

Ahora pasemos a coeficientes del modelo:
2079,85 - Este un 0- un coeficiente que muestra cuál será Y si todos los factores utilizados en el modelo son iguales a 0, se entiende que esto es una dependencia de otros factores no descritos en el modelo;
-0,0056 - un 1- un coeficiente que muestra el peso de la influencia del factor x 1 en Y, es decir, el número de empresas dentro de un modelo determinado afecta el indicador de la población económicamente activa con un peso de sólo -0,0056 (un grado de influencia bastante pequeño ). El signo menos muestra que esta influencia es negativa, es decir, cuantas más empresas, menos población económicamente activa, por paradójico que pueda ser su significado;
-0,0026 - un 2- coeficiente de influencia del volumen de inversión de capital sobre el tamaño de la población económicamente activa, según el modelo esta influencia también es negativa;
0,0028 - un 3- coeficiente de influencia del ingreso de la población sobre el tamaño de la población económicamente activa, aquí la influencia es positiva, es decir, según el modelo, un aumento en el ingreso contribuirá a un aumento en el tamaño de la población económicamente activa.

Recopilemos los coeficientes calculados en el modelo:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

En realidad, este es un modelo de regresión lineal, que para los datos iniciales utilizados en el ejemplo se ve exactamente así.

Estimaciones y pronósticos del modelo.

Como ya hemos comentado anteriormente, el modelo está construido no solo para mostrar la magnitud de la dependencia del parámetro estudiado de los factores que influyen, sino también para que, conociendo estos factores que influyen, sea posible hacer un pronóstico. Hacer este pronóstico es bastante simple; sólo necesita sustituir los valores de los factores influyentes en lugar de las X correspondientes en la ecuación del modelo resultante. En la siguiente figura, estos cálculos se realizan en Excel en una columna separada.

Los valores reales (los que ocurrieron en la realidad) y los valores calculados según el modelo en una misma figura se muestran en forma de gráficos para mostrar la diferencia, y por tanto el error del modelo.

Repito una vez más, para hacer un pronóstico mediante un modelo es necesario que se conozcan los factores que influyen, y si hablamos de una serie de tiempo y, en consecuencia, un pronóstico para el futuro, por ejemplo, para el próximo año o mes, entonces no siempre es posible saber cuáles serán los factores que influirán en este mismo futuro. En tales casos, también es necesario hacer un pronóstico de los factores que influyen; la mayoría de las veces esto se hace mediante un modelo autorregresivo, un modelo en el que los factores que influyen son el objeto en estudio y el tiempo, es decir, la dependencia del indicador. se basa en lo que fue en el pasado.

Veremos cómo construir un modelo autorregresivo en el próximo artículo, pero ahora supongamos que sabemos cuáles serán los valores de los factores influyentes en el período futuro (en el ejemplo, 2008) y sustituyendo estos valores. En los cálculos obtendremos nuestra previsión para 2008.

Muestra la influencia de algunos valores (independientes, independientes) sobre la variable dependiente. Por ejemplo, ¿cómo depende el número de población económicamente activa del número de empresas, los salarios y otros parámetros? O: ¿cómo afectan las inversiones extranjeras, los precios de la energía, etc. al nivel del PIB?

El resultado del análisis le permite resaltar prioridades. Y en base a los factores principales, predecir, planificar el desarrollo de áreas prioritarias y tomar decisiones de gestión.

La regresión ocurre:

lineal (y = a + bx);

· parabólica (y = a + bx + cx 2);

· exponencial (y = a * exp(bx));

· potencia (y = a*x^b);

· hiperbólico (y = b/x + a);

logarítmico (y = b * 1n(x) + a);

· exponencial (y = a * b^x).

Veamos un ejemplo de cómo construir un modelo de regresión en Excel e interpretar los resultados. Tomemos el tipo de regresión lineal.

Tarea. En seis empresas se analizó el salario mensual medio y el número de empleados que renunciaban. Es necesario determinar la dependencia del número de empleados que renuncian del salario medio.

El modelo de regresión lineal se ve así:

Y = a 0 + a 1 x 1 +…+a k x k.

Donde a son coeficientes de regresión, x son variables influyentes, k es el número de factores.

En nuestro ejemplo, Y es el indicador de empleados que renuncian. El factor que influye son los salarios (x).

Excel tiene funciones integradas que pueden ayudarlo a calcular los parámetros de un modelo de regresión lineal. Pero el complemento "Paquete de análisis" lo hará más rápido.

Activamos una poderosa herramienta analítica:

1. Haga clic en el botón "Office" y vaya a la pestaña "Opciones de Excel". "Complementos".

2. En la parte inferior, debajo de la lista desplegable, en el campo "Administrar" habrá una inscripción "Complementos de Excel" (si no está allí, haga clic en la casilla de verificación a la derecha y seleccione). Y el botón "Ir". Hacer clic.

3. Se abre una lista de complementos disponibles. Seleccione "Paquete de análisis" y haga clic en Aceptar.

Una vez activado, el complemento estará disponible en la pestaña Datos.

Ahora hagamos el análisis de regresión en sí.

1. Abra el menú de la herramienta “Análisis de datos”. Seleccione "Regresión".

2. Se abrirá un menú para seleccionar valores de entrada y opciones de salida (dónde mostrar el resultado). En los campos de los datos iniciales indicamos el rango del parámetro descrito (Y) y el factor que influye en él (X). El resto no podrá completarse.

3. Después de hacer clic en Aceptar, el programa mostrará los cálculos en una hoja nueva (puede seleccionar un intervalo para mostrar en la hoja actual o asignar la salida a un nuevo libro).

En primer lugar, prestamos atención al R cuadrado y a los coeficientes.

R cuadrado es el coeficiente de determinación. En nuestro ejemplo: 0,755 o 75,5%. Esto significa que los parámetros calculados del modelo explican el 75,5% de la relación entre los parámetros estudiados. Cuanto mayor sea el coeficiente de determinación, mejor será el modelo. Bueno, por encima de 0,8. Malo: menos de 0,5 (un análisis de este tipo difícilmente puede considerarse razonable). En nuestro ejemplo – “no está mal”.

El coeficiente 64,1428 muestra cuál será Y si todas las variables del modelo considerado son iguales a 0. Es decir, el valor del parámetro analizado también está influenciado por otros factores no descritos en el modelo.

El coeficiente -0,16285 muestra el peso de la variable X sobre Y. Es decir, el salario mensual promedio dentro de este modelo afecta el número de personas que abandonan con un peso de -0,16285 (este es un pequeño grado de influencia). El signo “-” indica un impacto negativo: cuanto mayor es el salario, menos personas renuncian. Lo cual es justo.

El análisis de regresión y correlación son métodos de investigación estadística. Estas son las formas más comunes de mostrar la dependencia de un parámetro de una o más variables independientes.

A continuación, utilizando ejemplos prácticos específicos, consideraremos estos dos análisis muy populares entre los economistas. También daremos un ejemplo de obtención de resultados al combinarlos.

Análisis de regresión en Excel

El resultado del análisis le permite resaltar prioridades. Y en base a los factores principales, predecir, planificar el desarrollo de áreas prioritarias y tomar decisiones de gestión.

La regresión ocurre:

lineal (y = a + bx);
parabólico (y = a + bx + cx 2);
exponencial (y = a * exp(bx));
potencia (y = a*x^b);
hiperbólico (y = b/x + a);
logarítmico (y = b * 1n(x) + a);
exponencial (y = a * b^x).

Veamos un ejemplo de cómo construir un modelo de regresión en Excel e interpretar los resultados. Tomemos el tipo de regresión lineal.

El modelo de regresión lineal se ve así:

Y = a 0 + a 1 x 1 +…+a k x k.

Donde a son coeficientes de regresión, x son variables influyentes, k es el número de factores.

En nuestro ejemplo, Y es el indicador de empleados que renuncian. El factor que influye son los salarios (x).

Excel tiene funciones integradas que pueden ayudarlo a calcular los parámetros de un modelo de regresión lineal. Pero el complemento "Paquete de análisis" lo hará más rápido.

Activamos una poderosa herramienta analítica:

Una vez activado, el complemento estará disponible en la pestaña Datos.

Ahora hagamos el análisis de regresión en sí.

En primer lugar, prestamos atención al R cuadrado y a los coeficientes.

Análisis de correlación en Excel

El análisis de correlación ayuda a determinar si existe una relación entre los indicadores en una o dos muestras. Por ejemplo, entre el tiempo de funcionamiento de una máquina y el coste de las reparaciones, el precio del equipo y la duración del funcionamiento, la altura y el peso de los niños, etc.

Si hay una conexión, entonces un aumento en un parámetro conduce a un aumento (correlación positiva) o una disminución (negativa) del otro. El análisis de correlación ayuda al analista a determinar si el valor de un indicador se puede utilizar para predecir el posible valor de otro.

El coeficiente de correlación se denota por r. Varía de +1 a -1. La clasificación de correlaciones para diferentes áreas será diferente. Cuando el coeficiente es 0, no existe una relación lineal entre muestras.

Veamos cómo encontrar el coeficiente de correlación usando Excel.

Para encontrar coeficientes emparejados, se utiliza la función CORREL.

Objetivo: Determinar si existe relación entre el tiempo de operación de un torno y el costo de su mantenimiento.

Coloque el cursor en cualquier celda y presione el botón fx.

En la categoría “Estadística”, seleccione la función CORREL.
Argumento “Array 1” - el primer rango de valores – tiempo de funcionamiento de la máquina: A2:A14.
Argumento “Array 2” - segundo rango de valores – costo de reparación: B2:B14. Haga clic en Aceptar.

Para determinar el tipo de conexión, es necesario observar el número absoluto del coeficiente (cada campo de actividad tiene su propia escala).

Para el análisis de correlación de varios parámetros (más de 2), es más conveniente utilizar "Análisis de datos" (el complemento "Paquete de análisis"). Debe seleccionar la correlación de la lista y designar la matriz. Todo.

Los coeficientes resultantes se mostrarán en la matriz de correlación. Como esto:

Análisis de correlación y regresión.

En la práctica, estas dos técnicas suelen utilizarse juntas.

Ejemplo:

Ahora los datos del análisis de regresión se han hecho visibles.

La línea de regresión es un reflejo gráfico de la relación entre fenómenos. Puedes construir muy claramente una línea de regresión en Excel.

Para hacer esto necesitas:

1.Abre Excel

2.Cree columnas de datos. En nuestro ejemplo, construiremos una línea de regresión, o relación, entre la agresividad y la duda en los niños de primer grado. En el experimento participaron 30 niños, los datos se presentan en la tabla de Excel:

1 columna - número de asunto

2 columnas - agresividad en puntos

3 columnas - dudas sobre uno mismo en puntos

3. Luego debe seleccionar ambas columnas (sin el nombre de la columna), hacer clic en la pestaña insertar , elegir lugar y elija el primero de los diseños propuestos puntear con marcadores .

4. Entonces tenemos una plantilla para la línea de regresión, la llamada diagrama de dispersión. Para ir a la línea de regresión, debe hacer clic en la figura resultante, presionar tabulador constructor, encontrar en el panel diseños de gráficos y elige METRO A ket9 , también dice f(x)

5. Entonces, tenemos una línea de regresión. El gráfico también muestra su ecuación y el cuadrado del coeficiente de correlación.

6. Ya solo queda agregar el nombre de la gráfica y el nombre de los ejes. Además, si lo desea, puede eliminar la leyenda, reducir el número de líneas de cuadrícula horizontales (pestaña disposición , entonces neto ). Los cambios y configuraciones básicos se realizan en la pestaña. Disposición