Cómo hacer análisis de datos de regresión en Excel. análisis de regresión en excel

El cambio en la característica resultante y se debe a la variación en la característica del factor x. La proporción de varianza explicada por la regresión en la varianza total de la característica resultante. caracteriza el coeficiente de determinación R 2. Para una relación lineal, el coeficiente de determinación es igual al cuadrado del coeficiente de correlación:

R 2 = r xy 2 , donde r xy es el coeficiente de correlación.

Por ejemplo, el valor de R 2 = 0,83 significa que en el 83% de los casos los cambios en x conducen a cambios en y. En otras palabras, la precisión al seleccionar la ecuación de regresión es alta.

Calculado para evaluar la calidad del ajuste de la ecuación de regresión. Para modelos aceptables, se sugiere que el coeficiente de determinación sea superior al 50%. Los modelos con un coeficiente de determinación superior al 80% pueden considerarse bastante buenos. El valor del coeficiente de determinación R 2 = 1 significa una relación funcional entre las variables.

En caso regresión no lineal El coeficiente de determinación se calcula con esta calculadora. Con regresión múltiple, el coeficiente de determinación se puede encontrar a través del servicio de Regresión múltiple
En general, el coeficiente de determinación se encuentra mediante la fórmula: o
Regla para sumar variaciones:
,
¿Dónde está la suma total de las desviaciones al cuadrado?
- la suma de las desviaciones al cuadrado debidas a la regresión (“explicada” o “factorial”);
- suma residual de desviaciones al cuadrado.

Usando esta calculadora en línea puedes calcular coeficiente de determinación y se comprueba su importancia (solución de ejemplo).

Instrucciones. Especifique la cantidad de datos de entrada. La solución resultante se guarda en un archivo de Word. También se crea automáticamente una plantilla para probar la solución en Excel.

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 sólo unos pocos clics. De hecho, se explicará 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 inversiones 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 sólo 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 una predicción. 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.

El análisis de regresión es uno de los métodos más populares de investigación estadística. Se puede utilizar para establecer el grado de influencia de las variables independientes sobre la variable dependiente. Microsoft Excel cuenta con herramientas diseñadas para realizar este tipo de análisis. Veamos qué son y cómo utilizarlos.

Conexión del paquete de análisis

Pero, para utilizar la función que le permite realizar análisis de regresión, primero debe activar el paquete de análisis. Solo entonces aparecerán en la cinta de Excel las herramientas necesarias para este procedimiento.

1. Vaya a la pestaña "Archivo".
2. Vaya a la sección "Configuración".
3. Se abre la ventana Opciones de Excel. Vaya a la subsección "Complementos".
4. En la parte inferior de la ventana que se abre, mueva el interruptor en el bloque "Administración" a la posición "Complementos de Excel", si está en una posición diferente. Haga clic en el botón "Ir".
5. Se abre una ventana de complementos de Excel disponibles. Marque la casilla junto a "Paquete de análisis". Haga clic en el botón "Aceptar".

Ahora, cuando vayamos a la pestaña "Datos", en la cinta del bloque de herramientas "Análisis" veremos un nuevo botón: "Análisis de datos".

Tipos de análisis de regresión

Hay varios tipos de regresiones:

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

Hablaremos con más detalle sobre cómo realizar el último tipo de análisis de regresión en Excel más adelante.

Regresión lineal en Excel

A continuación, a modo de ejemplo, se muestra una tabla que muestra la temperatura media diaria del aire exterior y el número de clientes de la tienda para el día laborable correspondiente. Averigüemos mediante el análisis de regresión exactamente cómo las condiciones climáticas en forma de temperatura del aire pueden afectar la asistencia a un establecimiento minorista.

La ecuación general de regresión lineal es la siguiente: Y = a0 + a1x1 +…+ akhk. En esta fórmula, Y significa la variable sobre la que intentamos estudiar la influencia de los factores. En nuestro caso, este es el número de compradores. El valor de x son los diversos factores que influyen en la variable. Los parámetros a son los coeficientes de regresión. Es decir, son ellos quienes determinan la importancia de un factor en particular. El índice k denota el número total de estos mismos factores.

Análisis de resultados de análisis.

Los resultados del análisis de regresión se muestran en forma de tabla en el lugar especificado en la configuración.

Uno de los principales indicadores es el R cuadrado. Indica la calidad del modelo. En nuestro caso, este coeficiente es 0,705 o alrededor del 70,5%. Este es un nivel de calidad aceptable. Una dependencia inferior a 0,5 es mala.

Otro indicador importante se encuentra en la celda en la intersección de la fila "Intersección Y" y la columna "Coeficientes". Esto indica qué valor tendrá Y, y en nuestro caso, este es el número de compradores, con todos los demás factores iguales a cero. En esta tabla, este valor es 58,04.

El valor en la intersección de las columnas "Variable X1" y "Coeficientes" muestra el nivel de dependencia de Y de X. En nuestro caso, este es el nivel de dependencia del número de clientes de la tienda con la temperatura. Un coeficiente de 1,31 se considera un indicador de influencia bastante alto.

Como puede ver, utilizando Microsoft Excel es bastante fácil crear una tabla de análisis de regresión. Pero sólo una persona capacitada puede trabajar con los datos resultantes y comprender su esencia.

Nos alegra haber podido ayudarle a resolver el problema.

Haga su pregunta en los comentarios, describiendo en detalle la esencia del problema. Nuestros especialistas intentarán responder lo más rápido posible.

¿Te ayudó este artículo?

El método de regresión lineal nos permite describir la línea recta que mejor se ajusta a una serie de pares ordenados (x, y). La ecuación de una línea recta, conocida como ecuación lineal, se da a continuación:

ŷ - valor esperado de y para un valor dado de x,

x - variable independiente,

a - segmento en el eje y de una línea recta,

b es la pendiente de la recta.

La siguiente figura ilustra gráficamente este concepto:

La figura de arriba muestra la línea descrita por la ecuación ŷ =2+0.5x. La intersección con el eje y es el punto en el que la línea cruza el eje y; en nuestro caso, a = 2. La pendiente de la línea, b, la relación entre la subida de la línea y la longitud de la línea, tiene un valor de 0,5. Una pendiente positiva significa que la línea sube de izquierda a derecha. Si b = 0, la línea es horizontal, lo que significa que no existe relación entre las variables dependientes e independientes. En otras palabras, cambiar el valor de x no afecta el valor de y.

ŷ e y a menudo se confunden. La gráfica muestra 6 pares ordenados de puntos y una recta, según la ecuación dada.

Esta figura muestra el punto correspondiente al par ordenado x = 2 e y = 4. Tenga en cuenta que el valor esperado de y según la línea en incógnita= 2 es ŷ. Esto lo podemos confirmar con la siguiente ecuación:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

El valor de y representa el punto real y el valor de ŷ es el valor esperado de y usando una ecuación lineal para un valor dado de x.

El siguiente paso es determinar la ecuación lineal que mejor coincida con el conjunto de pares ordenados, de esto hablamos en el artículo anterior, donde determinamos la forma de la ecuación usando el método de mínimos cuadrados.

Usar Excel para definir la regresión lineal

Para utilizar la herramienta de análisis de regresión integrada en Excel, debe activar el complemento Paquete de análisis. Puedes encontrarlo haciendo clic en la pestaña Archivo -> Opciones(2007+), en el cuadro de diálogo que aparece OpcionesSobresalir ir a la pestaña Complementos. en el campo Control elegir ComplementosSobresalir y haga clic Ir. En la ventana que aparece, marque la casilla junto a paquete de análisis, hacer clic DE ACUERDO.

en la pestaña Datos en el grupo Análisis aparecerá un nuevo botón Análisis de datos.

Para demostrar cómo funciona el complemento, usemos datos de un artículo anterior, donde un chico y una chica comparten una mesa en el baño. Ingrese los datos de nuestro ejemplo de bañera en las Columnas A y B de la hoja en blanco.

Ir a la pestaña Datos, en el grupo Análisis hacer clic Análisis de datos. En la ventana que aparece Análisis de datos seleccionar Regresión como se muestra en la figura y haga clic en Aceptar.

Establezca los parámetros de regresión necesarios en la ventana. Regresión como se muestra en la imagen:

Hacer clic DE ACUERDO. La siguiente figura muestra los resultados obtenidos:

Estos resultados son consistentes con los que obtuvimos al hacer nuestros propios cálculos en el artículo anterior.

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 fue introducido en las matemáticas por Francis Galton 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:

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 = a0 + a1×1 +…+аkxk, donde хi son las variables que influyen, ai son los coeficientes de regresión y k es el número de factores.

Para esta tarea, 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.

Regresión lineal 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 es 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.

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.

Análisis de regresión en Microsoft Excel: la guía más completa sobre el uso de MS Excel para resolver problemas de análisis de regresión en el campo del análisis empresarial. Konrad Carlberg explica claramente las cuestiones teóricas, cuyo conocimiento le ayudará a evitar muchos errores tanto al realizar usted mismo el análisis de regresión como al evaluar los resultados de los análisis realizados por otras personas. Todo el material, desde correlaciones simples y pruebas t hasta análisis múltiples de covarianza, se basa en ejemplos del mundo real y va acompañado de procedimientos detallados paso a paso.

El libro analiza las peculiaridades y controversias de las funciones de regresión de Excel, examina las implicaciones de cada opción y argumento y explica cómo aplicar de manera confiable métodos de regresión en áreas que van desde la investigación médica hasta el análisis financiero.

Konrad Carlberg. Análisis de regresión en Microsoft Excel. – M.: Dialéctica, 2017. – 400 p.

Descarga la nota en formato o, ejemplos en formato

Capítulo 1: Evaluación de la variabilidad de los datos

Los estadísticos tienen muchas medidas de variación a su disposición. Uno de ellos es la suma de las desviaciones al cuadrado de los valores individuales del promedio. En Excel, se utiliza la función CUADRADO() para esto. Pero la variación se utiliza con más frecuencia. La dispersión es el promedio de las desviaciones al cuadrado. La varianza es insensible a la cantidad de valores en el conjunto de datos en estudio (mientras que la suma de las desviaciones al cuadrado aumenta con la cantidad de mediciones).

Excel ofrece dos funciones que devuelven varianza: DISP.G() y DISP.V():

• Utilice la función DISP.G() si los valores a procesar forman una población. Es decir, los valores contenidos en el rango son los únicos valores que le interesan.
• Utilice la función DISP.B() si los valores a procesar forman una muestra de una población mayor. Se supone que existen valores adicionales cuya varianza también se puede estimar.

Si una cantidad, como una media o un coeficiente de correlación, se calcula a partir de una población, se denomina parámetro. Una cantidad similar calculada a partir de una muestra se llama estadística. Contando desviaciones del promedio en un conjunto dado, obtendrás una suma de desviaciones al cuadrado de menor magnitud que si las contaras a partir de cualquier otro valor. Una afirmación similar es válida para la varianza.

Cuanto mayor sea el tamaño de la muestra, más preciso será el valor estadístico calculado. Pero no existe un tamaño de muestra menor que el tamaño de la población del cual pueda estar seguro de que el valor estadístico coincide con el valor del parámetro.

Digamos que tienes un conjunto de 100 alturas cuya media difiere de la media poblacional, sin importar cuán pequeña sea la diferencia. Al calcular la varianza de una muestra, obtendrá un valor, digamos 4. Este valor es menor que cualquier otro valor que se pueda obtener calculando la desviación de cada uno de los 100 valores de altura en relación con cualquier valor que no sea el promedio de la muestra. , incluso en relación con el promedio real de la población. Por lo tanto, la varianza calculada será diferente y menor de la varianza que obtendría si de alguna manera descubriera y utilizara un parámetro de población en lugar de una media muestral.

La suma media de cuadrados determinada para la muestra proporciona una estimación más baja de la varianza poblacional. La varianza calculada de esta manera se llama desplazado evaluación. Resulta que para eliminar el sesgo y obtener una estimación insesgada, basta con dividir la suma de las desviaciones al cuadrado no por norte, Dónde norte- tamaño de la muestra, y norte – 1.

Magnitud norte – 1 se llama número (número) de grados de libertad. Hay diferentes formas de calcular esta cantidad, aunque todas implican restar algún número del tamaño de la muestra o contar el número de categorías en las que caen las observaciones.

La esencia de la diferencia entre las funciones DISP.G() y DISP.V() es la siguiente:

• En la función VAR.G(), la suma de cuadrados se divide por el número de observaciones y, por tanto, representa una estimación sesgada de la varianza, la media verdadera.
• En la función DISP.B(), la suma de cuadrados se divide por el número de observaciones menos 1, es decir por el número de grados de libertad, lo que proporciona una estimación más precisa e insesgada de la varianza de la población de la que se extrajo la muestra.

Desviación estándar desviación estándar, SD) – es la raíz cuadrada de la varianza:

Al elevar al cuadrado las desviaciones se transforma la escala de medida en otra métrica, que es el cuadrado de la original: metros - en metros cuadrados, dólares - en dólares cuadrados, etc. La desviación estándar es la raíz cuadrada de la varianza y, por tanto, nos devuelve a las unidades de medida originales. Lo que sea más conveniente.

A menudo es necesario calcular la desviación estándar después de que los datos hayan sido sometidos a alguna manipulación. Y aunque en estos casos los resultados son sin duda desviaciones estándar, se les suele llamar errores estándar. Existen varios tipos de errores estándar, incluido el error estándar de medición, el error estándar de proporciones y el error estándar de la media.

Supongamos que recopiló datos de altura de 25 hombres adultos seleccionados al azar en cada uno de los 50 estados. A continuación, calcula la altura promedio de los hombres adultos en cada estado. Los 50 valores medios resultantes, a su vez, pueden considerarse observaciones. A partir de esto se podría calcular su desviación estándar, que es error estándar de la media. Arroz. 1. compara la distribución de 1250 valores individuales brutos (datos de altura para 25 hombres en cada uno de los 50 estados) con la distribución de los promedios de 50 estados. La fórmula para estimar el error estándar de la media (es decir, la desviación estándar de las medias, no las observaciones individuales):

¿Dónde está el error estándar de la media? s– desviación estándar de las observaciones originales; norte– número de observaciones en la muestra.

Arroz. 1. La variación en los promedios de un estado a otro es significativamente menor que la variación en las observaciones individuales.

En estadística, existe una convención sobre el uso de letras griegas y latinas para representar cantidades estadísticas. Se acostumbra denotar los parámetros de la población general con letras griegas y las estadísticas muestrales con letras latinas. Por tanto, cuando hablamos de la desviación estándar de la población, la escribimos como σ; si se considera la desviación estándar de la muestra, entonces usamos la notación s. En cuanto a los símbolos para designar promedios, no concuerdan tan bien entre sí. La media poblacional se denota con la letra griega μ. Sin embargo, el símbolo X̅ se utiliza tradicionalmente para representar la media muestral.

puntuación z expresa la posición de una observación en la distribución en unidades de desviación estándar. Por ejemplo, z = 1,5 significa que la observación está a 1,5 desviaciones estándar de la media. Término puntuación z utilizado para evaluaciones individuales, es decir para dimensiones asignadas a elementos de muestra individuales. El término utilizado para referirse a dichas estadísticas (como el promedio estatal) puntuación z:

donde X̅ es la media muestral, μ es la media poblacional, es el error estándar de las medias de un conjunto de muestras:

donde σ es el error estándar de la población (mediciones individuales), norte– tamaño de la muestra.

Digamos que trabaja como instructor en un club de golf. Has podido medir la distancia de tus tiros durante un largo período de tiempo y sabes que el promedio es 205 yardas y la desviación estándar es 36 yardas. Le ofrecen un nuevo palo, alegando que aumentará su distancia de golpe en 10 yardas. Le pides a cada uno de los siguientes 81 clientes del palo que realicen un tiro de prueba con un palo nuevo y registren su distancia de swing. Resultó que la distancia media con el nuevo palo era de 215 yardas. ¿Cuál es la probabilidad de que una diferencia de 10 yardas (215 – 205) se deba únicamente a un error de muestreo? O para decirlo de otra manera: ¿Cuál es la probabilidad de que, en pruebas más extensas, el nuevo palo no demuestre un aumento en la distancia de bateo sobre el promedio existente a largo plazo de 205 yardas?

Podemos comprobar esto generando una puntuación z. Error estándar de la media:

Luego puntuación z:

Necesitamos encontrar la probabilidad de que la media muestral esté a 2,5σ de la media poblacional. Si la probabilidad es pequeña, entonces las diferencias no se deben al azar, sino a la calidad del nuevo club. Excel no tiene una función preparada para determinar la probabilidad de puntuación z. Sin embargo, puede utilizar la fórmula =1-NORM.ST.DIST(z-score,TRUE), donde la función NORM.ST.DIST() devuelve el área bajo la curva normal a la izquierda de la puntuación z (Figura 2).

Arroz. 2. La función NORM.ST.DIST() devuelve el área bajo la curva a la izquierda del valor z; Para ampliar la imagen, haga clic derecho sobre ella y seleccione Abrir imagen en nueva pestaña

El segundo argumento de la función NORM.ST.DIST() puede tomar dos valores: VERDADERO – la función devuelve el área del área bajo la curva a la izquierda del punto especificado por el primer argumento; FALSO: la función devuelve la altura de la curva en el punto especificado por el primer argumento.

Si no se conocen la media poblacional (μ) y la desviación estándar (σ), se utiliza el valor t (ver detalles). Las estructuras de puntuación z y puntuación t difieren en que la desviación estándar s obtenida de los resultados de la muestra se utiliza para encontrar la puntuación t en lugar del valor conocido del parámetro poblacional σ. La curva normal tiene una forma única y la forma de la distribución del valor t varía según el número de grados de libertad df. grados de libertad) de la muestra que representa. El número de grados de libertad de la muestra es igual a norte – 1, Dónde norte- tamaño de la muestra (Fig. 3).

Arroz. 3. La forma de las distribuciones t que surgen en los casos en que se desconoce el parámetro σ difiere de la forma de la distribución normal

Excel tiene dos funciones para la distribución t, también llamada distribución de Student: STUDENT.DIST() devuelve el área bajo la curva a la izquierda de un valor t dado, y STUDENT.DIST.PH() devuelve el área a la bien.

Capítulo 2. Correlación

La correlación es una medida de dependencia entre elementos de un conjunto de pares ordenados. La correlación se caracteriza Coeficientes de correlación de Pearson–r. El coeficiente puede tomar valores en el rango de –1,0 a +1,0.

Dónde Sx Y S y– desviaciones estándar de variables incógnita Y Y, S xy– covarianza:

En esta fórmula, la covarianza se divide por las desviaciones estándar de las variables. incógnita Y Y, eliminando así de la covarianza los efectos de escala relacionados con la unidad. Excel utiliza la función CORREL(). El nombre de esta función no contiene los elementos calificativos Г y В, que se utilizan en los nombres de funciones como STANDARDEV(), VARIANCE() o COVARIANCE(). Aunque el coeficiente de correlación muestral proporciona una estimación sesgada, el motivo del sesgo es diferente que en el caso de la varianza o la desviación estándar.

Dependiendo de la magnitud del coeficiente de correlación general (a menudo indicado por la letra griega ρ ), coeficiente de correlación r produce una estimación sesgada, cuyo efecto aumenta a medida que disminuye el tamaño de la muestra. Sin embargo, no intentamos corregir este sesgo de la misma manera que, por ejemplo, lo hicimos al calcular la desviación estándar, cuando sustituimos no el número de observaciones, sino el número de grados de libertad en la fórmula correspondiente. En realidad, el número de observaciones utilizadas para calcular la covarianza no tiene ningún efecto sobre la magnitud.

El coeficiente de correlación estándar está diseñado para usarse con variables que están relacionadas entre sí mediante una relación lineal. La presencia de no linealidad y/o errores en los datos (valores atípicos) conducen a un cálculo incorrecto del coeficiente de correlación. Para diagnosticar problemas de datos, se recomienda crear diagramas de dispersión. Este es el único tipo de gráfico en Excel que trata los ejes horizontal y vertical como ejes de valores. Un gráfico de líneas define una de las columnas como eje de categorías, lo que distorsiona la imagen de los datos (Fig. 4).

Arroz. 4. Las líneas de regresión parecen iguales, pero compara sus ecuaciones entre sí.

Las observaciones utilizadas para construir el gráfico de líneas están dispuestas equidistantes a lo largo del eje horizontal. Las etiquetas de división a lo largo de este eje son solo etiquetas, no valores numéricos.

Aunque la correlación a menudo significa que existe una relación de causa y efecto, no se puede utilizar para demostrar que ese sea el caso. Las estadísticas no se utilizan para demostrar si una teoría es verdadera o falsa. Para excluir explicaciones contrapuestas para los resultados observacionales, ponga experimentos planificados. Las estadísticas se utilizan para resumir la información recopilada durante dichos experimentos y para cuantificar la probabilidad de que la decisión tomada sea incorrecta dada la base de evidencia disponible.

Capítulo 3: Regresión simple

Si dos variables están relacionadas entre sí, de modo que el valor del coeficiente de correlación excede, digamos, 0,5, entonces en este caso es posible predecir (con cierta precisión) el valor desconocido de una variable a partir del valor conocido de la otra. . Para obtener valores de precios previstos con base en los datos que se muestran en la Fig. 5, puede utilizar cualquiera de los varios métodos posibles, pero es casi seguro que no utilizará el que se muestra en la Fig. 5. Aún así, deberías familiarizarte con él, porque ningún otro método te permite demostrar la conexión entre correlación y predicción tan claramente como éste. En la figura. 5 en el rango B2:C12 muestra una muestra aleatoria de diez casas y proporciona datos sobre el área de cada casa (en pies cuadrados) y su precio de venta.

Arroz. 5. Los valores previstos de los precios de venta forman una línea recta.

Encuentre las medias, las desviaciones estándar y el coeficiente de correlación (rango A14:C18). Calcule las puntuaciones z del área (E2:E12). Por ejemplo, la celda E3 contiene la fórmula: =(B3-$B$14)/$B$15. Calcule las puntuaciones z del precio previsto (F2:F12). Por ejemplo, la celda F3 contiene la fórmula: =ЕЗ*$В$18. Convierta las puntuaciones z a precios en dólares (H2:H12). En la celda NZ la fórmula es: =F3*$C$15+$C$14.

Tenga en cuenta que el valor predicho siempre tiende a desplazarse hacia la media de 0. Cuanto más cerca de cero esté el coeficiente de correlación, más cerca de cero estará la puntuación z predicha. En nuestro ejemplo, el coeficiente de correlación entre el área y el precio de venta es 0,67 y el precio previsto es 1,0 * 0,67, es decir 0,67. Esto corresponde a un exceso de un valor por encima de la media igual a dos tercios de una desviación estándar. Si el coeficiente de correlación fuera igual a 0,5, entonces el precio previsto sería 1,0 * 0,5, es decir 0,5. Esto corresponde a un exceso de un valor por encima de la media igual a sólo media desviación estándar. Siempre que el valor del coeficiente de correlación difiera del valor ideal, es decir mayor que -1,0 y menor que 1,0, la puntuación de la variable predicha debe estar más cerca de su media que la puntuación de la variable predictora (independiente) de la suya propia. Este fenómeno se llama regresión a la media o simplemente regresión.

Excel tiene varias funciones para determinar los coeficientes de una ecuación de línea de regresión (llamada línea de tendencia en Excel) y =kx + b. para determinar k cumple la función

=PENDIENTE(valores_y_conocidos, valores_x_conocidos)

Aquí en es la variable predicha, y incógnita– variable independiente. Debes seguir estrictamente este orden de variables. La pendiente de la recta de regresión, el coeficiente de correlación, las desviaciones estándar de las variables y la covarianza están estrechamente relacionados (Figura 6). La función INTERMEPT() devuelve el valor interceptado por la línea de regresión en el eje vertical:

= LÍMITE (valores_y_conocidos, valores_x_conocidos)

Arroz. 6. La relación entre desviaciones estándar convierte la covarianza en un coeficiente de correlación y la pendiente de la recta de regresión.

Tenga en cuenta que el número de valores xey proporcionados como argumentos para las funciones SLOPE() e INTERCEPT() deben ser los mismos.

En el análisis de regresión, se utiliza otro indicador importante: R 2 (R-cuadrado), o el coeficiente de determinación. Determina qué contribución a la variabilidad general de los datos hace la relación entre incógnita Y en. En Excel, existe una función llamada CVPIERSON(), que toma exactamente los mismos argumentos que la función CORREL().

Se dice que dos variables con un coeficiente de correlación distinto de cero entre ellas explican la varianza o han explicado la varianza. La varianza normalmente explicada se expresa como porcentaje. Entonces R 2 = 0,81 significa que se explica el 81% de la varianza (dispersión) de dos variables. El 19% restante se debe a fluctuaciones aleatorias.

Excel tiene una función TENDENCIA que facilita los cálculos. Función TENDENCIA():

• acepta los valores conocidos que usted proporciona incógnita y valores conocidos en;
• calcula la pendiente de la recta de regresión y la constante (intersección);
• devuelve valores predichos en, determinado aplicando una ecuación de regresión a valores conocidos incógnita(Figura 7).

La función TREND() es una función de matriz (si no ha encontrado este tipo de funciones antes, la recomiendo).

Arroz. 7. El uso de la función TREND() le permite acelerar y simplificar los cálculos en comparación con el uso de un par de funciones SLOPE() e INTERCEPT()

Para ingresar la función TENDENCIA() como una fórmula matricial en las celdas G3:G12, seleccione el rango G3:G12, ingrese la fórmula TENDENCIA(NW:C12;B3:B12), presione y mantenga presionadas las teclas y solo después de eso presione la tecla . Tenga en cuenta que la fórmula está entre llaves: ( y ). Así es como Excel te dice que esta fórmula se interpreta como una fórmula matricial. No ingrese los paréntesis usted mismo: si intenta ingresarlos usted mismo como parte de una fórmula, Excel tratará su entrada como una cadena de texto normal.

La función TREND() tiene dos argumentos más: nuevos_valores_x Y constante. El primero le permite hacer un pronóstico para el futuro y el segundo puede forzar que la línea de regresión pase por el origen (un valor de VERDADERO le dice a Excel que use la constante calculada, un valor de FALSO le dice a Excel que use una constante = 0 ). Excel le permite dibujar una línea de regresión en un gráfico para que pase por el origen. Comience dibujando un diagrama de dispersión, luego haga clic derecho en uno de los marcadores de la serie de datos. Seleccione el elemento en el menú contextual que se abre. Agregar una línea de tendencia; seleccione una opción Lineal; si es necesario, desplácese hacia abajo en el panel, marque la casilla Configurar intersección; Asegúrese de que su cuadro de texto asociado esté configurado en 0.0.

Si tienes tres variables y quieres determinar la correlación entre dos de ellas eliminando la influencia de la tercera, puedes utilizar correlación parcial. Suponga que está interesado en la relación entre el porcentaje de residentes de una ciudad que han completado sus estudios universitarios y el número de libros en las bibliotecas de la ciudad. Recogiste datos de 50 ciudades, pero... El problema es que ambos parámetros pueden depender del bienestar de los residentes de una ciudad en particular. Por supuesto, es muy difícil encontrar otras 50 ciudades que se caractericen por exactamente el mismo nivel de bienestar de sus residentes.

Al utilizar métodos estadísticos para controlar la influencia de la riqueza tanto en el apoyo financiero de la biblioteca como en la asequibilidad de la universidad, se podría obtener una cuantificación más precisa de la fuerza de la relación entre las variables que le interesan, es decir, el número de libros y el número de libros. graduados. Esta correlación condicional entre dos variables, cuando los valores de otras variables son fijos, se denomina correlación parcial. Una forma de calcularlo es utilizar la ecuación:

Dónde rC.B. . W.- coeficiente de correlación entre las variables Universidad y Libros con la influencia (valor fijo) de la variable Riqueza excluida; rC.B.- coeficiente de correlación entre las variables Universidad y Libros; rCW- coeficiente de correlación entre las variables Universidad y Bienestar; rB.W.- coeficiente de correlación entre las variables Libros y Bienestar.

Por otro lado, la correlación parcial se puede calcular a partir del análisis de residuos, es decir diferencias entre los valores predichos y los resultados asociados de las observaciones reales (ambos métodos se presentan en la Fig. 8).

Arroz. 8. Correlación parcial como correlación de residuos.

Para simplificar el cálculo de la matriz de coeficientes de correlación (B16:E19), utilice el paquete de análisis Excel (menú Datos –> Análisis –> Análisis de datos). De forma predeterminada, este paquete no está activo en Excel. Para instalarlo, vaya al menú. Archivo –> Opciones –> Complementos. En la parte inferior de la ventana abierta OpcionesSobresalir encontrar el campo Control, seleccionar ComplementosSobresalir, haga clic Ir. Marque la casilla junto al complemento Paquete de análisis. Haga clic en A análisis de datos, seleccione la opción Correlación. Especifique $B$2:$D$13 como intervalo de entrada, marque la casilla Etiquetas en la primera línea., especifique $B$16:$E$19 como intervalo de salida.

Otra posibilidad es determinar una correlación semiparcial. Por ejemplo, está investigando los efectos de la altura y la edad sobre el peso. Por lo tanto, tiene dos variables predictivas: altura y edad, y una variable predictiva: peso. Quiere excluir la influencia de una variable predictora sobre otra, pero no sobre la variable predictora:

donde H – Altura, W – Peso, A – Edad; El índice del coeficiente de correlación semiparcial utiliza paréntesis para indicar qué variable se elimina y de qué variable. En este caso, la notación W(H.A) indica que el efecto de la variable Edad se elimina de la variable Altura, pero no de la variable Peso.

Puede parecer que el tema que se está discutiendo no es de gran importancia. Después de todo, lo que más importa es la precisión con la que funciona la ecuación de regresión general, mientras que el problema de las contribuciones relativas de las variables individuales a la varianza total explicada parece ser de importancia secundaria. Sin embargo, esto está lejos de ser el caso. Una vez que empiezas a preguntarte si vale la pena usar una variable en una ecuación de regresión múltiple, la cuestión se vuelve importante. Puede influir en la evaluación de la exactitud de la elección del modelo para el análisis.

Capítulo 4. Función LINEST()

La función LINEST() devuelve 10 estadísticas de regresión. La función LINEST() es una función de matriz. Para ingresarlo, seleccione un rango que contenga cinco filas y dos columnas, escriba la fórmula y haga clic en (Figura 9):

ESTIMACIÓN LINEAL(B2:B21,A2:A21,VERDADERO,VERDADERO)

Arroz. 9. Función LINEST(): a) seleccione el rango D2:E6, b) ingrese la fórmula como se muestra en la barra de fórmulas, c) haga clic

La función ESTIMACIÓN LINEAL() devuelve:

• coeficiente de regresión (o pendiente, celda D2);
• segmento (o constante, celda E3);
• errores estándar del coeficiente de regresión y constante (rango D3:E3);
• coeficiente de determinación R 2 para regresión (celda D4);
• error estándar de estimación (celda E4);
• Prueba F para regresión completa (celda D5);
• número de grados de libertad para la suma residual de cuadrados (celda E5);
• suma de cuadrados de regresión (celda D6);
• suma residual de cuadrados (celda E6).

Veamos cada una de estas estadísticas y cómo interactúan.

error estándar en nuestro caso, es la desviación estándar calculada para los errores de muestreo. Es decir, se trata de una situación en la que la población general tiene una estadística y la muestra tiene otra. Al dividir el coeficiente de regresión por el error estándar, se obtiene un valor de 2,092/0,818 = 2,559. En otras palabras, un coeficiente de regresión de 2,092 está a dos errores estándar y medio de cero.

Si el coeficiente de regresión es cero, entonces la mejor estimación de la variable predicha es su media. Dos errores estándar y medio es bastante grande y se puede suponer con seguridad que el coeficiente de regresión para la población es distinto de cero.

Puede determinar la probabilidad de obtener un coeficiente de regresión muestral de 2,092 si su valor real en la población es 0,0 utilizando la función

STUDENT.DIST.PH (criterio t = 2,559; número de grados de libertad = 18)

En general, el número de grados de libertad = n – k – 1, donde n es el número de observaciones y k es el número de variables predictoras.

Esta fórmula devuelve 0,00987 o se redondea al 1%. Nos dice que si el coeficiente de regresión para la población es 0%, entonces la probabilidad de obtener una muestra de 20 personas para la cual el coeficiente de regresión estimado es 2,092 es un modesto 1%.

La prueba F (celda D5 en la Fig. 9) realiza las mismas funciones en relación con la regresión completa que la prueba t en relación con el coeficiente de regresión simple por pares. La prueba F se utiliza para probar si el coeficiente de determinación R 2 para una regresión es lo suficientemente grande como para rechazar la hipótesis de que en la población tiene un valor de 0,0, lo que indica que no hay varianza explicada por el predictor y la variable predicha. Cuando solo hay una variable predictiva, la prueba F es exactamente igual a la prueba t al cuadrado.

Hasta ahora hemos analizado las variables de intervalo. Si tiene variables que pueden tomar múltiples valores, representando nombres simples, por ejemplo, Hombre y Mujer o Reptil, Anfibio y Pez, represéntelas como un código numérico. Estas variables se denominan nominales.

Estadísticas R2 cuantifica la proporción de varianza explicada.

Error estándar de estimación. En la figura. La Figura 4.9 presenta los valores predichos de la variable Peso, obtenidos en base a su relación con la variable Altura. El rango E2:E21 contiene los valores residuales de la variable Peso. Más precisamente, estos residuos se denominan errores, de ahí el término error estándar de estimación.

Arroz. 10. Tanto R 2 como el error estándar de la estimación expresan la precisión de los pronósticos obtenidos mediante regresión.

Cuanto menor sea el error estándar de la estimación, más precisa será la ecuación de regresión y más se esperará que cualquier predicción producida por la ecuación coincida con la observación real. El error estándar de estimación proporciona una manera de cuantificar estas expectativas. El peso del 95% de las personas con una determinada altura estará en el rango:

(altura * 2,092 – 3,591) ± 2,092 * 21,118

Estadística F es la relación entre la varianza entre grupos y la varianza dentro del grupo. Este nombre fue introducido por el estadístico George Snedecor en honor a Sir, quien desarrolló el análisis de varianza (ANOVA, Análisis de Varianza) a principios del siglo XX.

El coeficiente de determinación R 2 expresa la proporción de la suma total de cuadrados asociada a la regresión. El valor (1 – R 2) expresa la proporción de la suma total de cuadrados asociados con los residuos - errores de pronóstico. La prueba F se puede obtener usando la función ESTIMACIÓN LINEAL (celda F5 en la Fig. 11), usando sumas de cuadrados (rango G10:J11), usando proporciones de varianza (rango G14:J15). Las fórmulas se pueden estudiar en el archivo Excel adjunto.

Arroz. 11. Cálculo del criterio F

Cuando se utilizan variables nominales, se utiliza codificación ficticia (Figura 12). Para codificar valores conviene utilizar los valores 0 y 1. La probabilidad F se calcula mediante la función:

F.DIST.PH(K2;I2;I3)

Aquí, la función F.DIST.PH() devuelve la probabilidad de obtener un criterio F que obedezca a la distribución F central (Fig. 13) para dos conjuntos de datos con los números de grados de libertad dados en las celdas I2 e I3. , cuyo valor coincide con el valor indicado en la celda K2.

Arroz. 12. Análisis de regresión utilizando variables ficticias

Arroz. 13. Distribución F central en λ = 0

Capítulo 5. Regresión múltiple

Cuando pasa de una regresión simple por pares con una variable predictora a una regresión múltiple, agrega una o más variables predictoras. Almacene los valores de las variables predictoras en columnas adyacentes, como las columnas A y B en el caso de dos predictores, o A, B y C en el caso de tres predictores. Antes de ingresar una fórmula que incluya la función ESTIMACIÓN LINEAL(), seleccione cinco filas y tantas columnas como variables predictoras haya, más una más para la constante. En el caso de una regresión con dos variables predictoras, se puede utilizar la siguiente estructura:

ESTIMACIÓN LINEAL(A2: A41; B2: C41;;VERDADERO)

Lo mismo ocurre en el caso de tres variables:

ESTIMADO LINEAL(A2:A61,B2:D61,;VERDADERO)

Supongamos que desea estudiar los posibles efectos de la edad y la dieta sobre los niveles de LDL, lipoproteínas de baja densidad, que se cree que son responsables de la formación de placas ateroscleróticas que causan aterotrombosis (Fig. 14).

Arroz. 14. Regresión múltiple

El R 2 de la regresión múltiple (reflejado en la celda F13) es mayor que el R 2 de cualquier regresión simple (E4, H4). La regresión múltiple utiliza múltiples variables predictoras simultáneamente. En este caso, R2 casi siempre aumenta.

Para cualquier ecuación de regresión lineal simple con una variable predictora, siempre habrá una correlación perfecta entre los valores predichos y los valores de la variable predictora porque la ecuación multiplica los valores predictores por una constante y agrega otra constante a cada producto. Este efecto no persiste en la regresión múltiple.

Mostrando los resultados devueltos por la función LINEST() para regresión múltiple (Figura 15). Los coeficientes de regresión se generan como parte de los resultados devueltos por la función LINEST() en orden inverso de las variables(G – H – I corresponde a C – B – A).

Arroz. 15. Los coeficientes y sus errores estándar se muestran en orden inverso en la hoja de trabajo.

Los principios y procedimientos utilizados en el análisis de regresión de variable predictora única se adaptan fácilmente para tener en cuenta múltiples variables predictivas. Resulta que gran parte de esta adaptación depende de eliminar la influencia de las variables predictoras entre sí. Este último está asociado con correlaciones parciales y semiparciales (Fig. 16).

Arroz. 16. La regresión múltiple se puede expresar mediante la regresión por pares de residuos (consulte el archivo Excel para ver las fórmulas)

En Excel, existen funciones que proporcionan información sobre las distribuciones t y F. Las funciones cuyos nombres incluyen la parte DIST, como STUDENT.DIST() y F.DIST(), toman una prueba t o una prueba F como argumento y devuelven la probabilidad de observar un valor específico. Las funciones cuyos nombres incluyen la parte OBR, como STUDENT.INR() y F.INV(), toman un valor de probabilidad como argumento y devuelven un valor de criterio correspondiente a la probabilidad especificada.

Como buscamos valores críticos de la distribución t que corta los bordes de sus regiones de cola, pasamos el 5% como argumento a una de las funciones STUDENT.INV(), que devuelve el valor correspondiente a esta probabilidad. (Figuras 17, 18).

Arroz. 17. Prueba t de dos colas

Arroz. 18. Prueba t de una cola

Al establecer una regla de decisión para la región alfa de una sola cola, se aumenta el poder estadístico de la prueba. Si realiza un experimento y está seguro de que tiene todas las razones para esperar un coeficiente de regresión positivo (o negativo), entonces debe realizar una prueba de una sola cola. En este caso, la probabilidad de tomar la decisión correcta al rechazar la hipótesis de un coeficiente de regresión cero en la población será mayor.

Los estadísticos prefieren utilizar el término prueba dirigida en lugar del término prueba de una sola cola y término prueba no dirigida en lugar del término prueba de dos colas. Se prefieren los términos dirigido y no dirigido porque enfatizan el tipo de hipótesis más que la naturaleza de las colas de la distribución.

Un enfoque para evaluar el impacto de los predictores basado en la comparación de modelos. En la figura. La Figura 19 presenta los resultados de un análisis de regresión que prueba la contribución de la variable Dieta a la ecuación de regresión.

Arroz. 19. Comparar dos modelos probando diferencias en sus resultados.

Los resultados de la función LINEST() (rango H2:K6) están relacionados con lo que yo llamo el modelo completo, que hace una regresión de la variable LDL en las variables Dieta, Edad y HDL. El rango H9:J13 presenta cálculos sin tener en cuenta la variable predictora Dieta. A esto lo llamo el modelo limitado. En el modelo completo, el 49,2% de la varianza en la variable dependiente LDL fue explicada por las variables predictoras. En el modelo restringido, sólo el 30,8% del LDL se explica por las variables Edad y HDL. La pérdida en R 2 debido a la exclusión de la variable Dieta del modelo es de 0,183. En el rango G15:L17 se realizan cálculos que muestran que solo existe una probabilidad de 0,0288 de que el efecto de la variable Dieta sea aleatorio. En el 97,1% restante la dieta tiene efecto sobre el LDL.

Capítulo 6: Supuestos y precauciones para el análisis de regresión

El término "supuesto" no está definido de forma suficientemente estricta, y la forma en que se utiliza sugiere que si no se cumple el supuesto, entonces los resultados de todo el análisis son, como mínimo, cuestionables o posiblemente inválidos. En realidad, este no es el caso, aunque ciertamente hay casos en los que violar un supuesto cambia fundamentalmente el panorama. Supuestos básicos: a) los residuos de la variable Y se distribuyen normalmente en cualquier punto X a lo largo de la recta de regresión; b) Los valores de Y dependen linealmente de los valores de X; c) la dispersión de los residuales es aproximadamente la misma en cada punto X; d) no hay dependencia entre los residuos.

Si los supuestos no juegan un papel significativo, los estadísticos dicen que el análisis es robusto ante la violación del supuesto. En particular, cuando se utiliza la regresión para comprobar las diferencias entre las medias de los grupos, el supuesto de que los valores de Y (y, por tanto, los residuos) están distribuidos normalmente no juega un papel importante: las pruebas son robustas ante violaciones del supuesto de normalidad. Es importante analizar los datos mediante gráficos. Por ejemplo, incluido en el complemento. Análisis de datos herramienta Regresión.

Si los datos no cumplen con los supuestos de la regresión lineal, existen otros enfoques además de la regresión lineal a su disposición. Uno de ellos es la regresión logística (Fig. 20). Cerca de los límites superior e inferior de la variable predictiva, la regresión lineal produce predicciones poco realistas.

Arroz. 20. Regresión logística

En la figura. La Figura 6.8 muestra los resultados de dos métodos de análisis de datos destinados a examinar la relación entre el ingreso anual y la probabilidad de comprar una vivienda. Obviamente, la probabilidad de realizar una compra aumentará a medida que aumenten los ingresos. Los gráficos facilitan la detección de las diferencias entre los resultados que la regresión lineal predice la probabilidad de comprar una casa y los resultados que podría obtener utilizando un enfoque diferente.

En el lenguaje de los estadísticos, rechazar la hipótesis nula cuando en realidad es cierta se denomina error de tipo I.

en el complemento Análisis de datos ofrece una herramienta conveniente para generar números aleatorios, lo que permite al usuario especificar la forma deseada de la distribución (por ejemplo, Normal, Binomial o Poisson), así como la media y la desviación estándar.

Diferencias entre funciones de la familia STUDENT.DIST(). A partir de Excel 2010, hay disponibles tres formas diferentes de la función que devuelven la proporción de la distribución a la izquierda y/o a la derecha de un valor de prueba t determinado. La función STUDENT.DIST() devuelve la fracción del área bajo la curva de distribución a la izquierda del valor de la prueba t que especifique. Digamos que tiene 36 observaciones, por lo que el número de grados de libertad para el análisis es 34 y el valor de la prueba t = 1,69. En este caso la fórmula

DISTR.ESTUDIANTE(+1.69,34,VERDADERO)

devuelve el valor 0,05 o 5% (Figura 21). El tercer argumento de la función STUDENT.DIST() puede ser VERDADERO o FALSO. Si se establece en VERDADERO, la función devuelve el área acumulada bajo la curva a la izquierda de la prueba t especificada, expresada como una proporción. Si es FALSO, la función devuelve la altura relativa de la curva en el punto correspondiente a la prueba t. Otras versiones de la función STUDENT.DIST() - STUDENT.DIST.PH() y STUDENT.DIST.2X() - toman solo el valor de la prueba t y el número de grados de libertad como argumentos y no requieren especificar un tercero argumento.

Arroz. 21. El área sombreada más oscura en la cola izquierda de la distribución corresponde a la proporción del área bajo la curva a la izquierda de un valor positivo grande de la prueba t

Para determinar el área a la derecha de la prueba t, utilice una de las fórmulas:

1 — DISTRIBUCIÓN ESTIODENTE (1, 69;34;VERDADERO)

DISTRITO.ESTUDIANTIL.PH(1.69;34)

Toda el área bajo la curva debe ser 100%, por lo que restar de 1 la fracción del área a la izquierda del valor de la prueba t que devuelve la función da la fracción del área a la derecha del valor de la prueba t. Quizás le resulte preferible obtener directamente la fracción de área que le interesa utilizando la función STUDENT.DIST.PH(), donde PH significa la cola derecha de la distribución (Fig. 22).

Arroz. 22. 5% región alfa para prueba direccional

El uso de las funciones STUDENT.DIST() o STUDENT.DIST.PH() implica que ha elegido una hipótesis de trabajo direccional. La hipótesis de trabajo direccional combinada con establecer el valor alfa en 5% significa que se coloca todo el 5% en la cola derecha de las distribuciones. Sólo tendrás que rechazar la hipótesis nula si la probabilidad del valor de la prueba t que obtengas es del 5% o menos. Las hipótesis direccionales generalmente dan como resultado pruebas estadísticas más sensibles (esta mayor sensibilidad también se denomina mayor poder estadístico).

En una prueba no dirigida, el valor alfa permanece en el mismo nivel del 5%, pero la distribución será diferente. Como se deben permitir dos resultados, la probabilidad de un falso positivo debe distribuirse entre las dos colas de la distribución. Generalmente se acepta distribuir esta probabilidad por igual (Fig. 23).

Usando el mismo valor de la prueba t obtenido y el mismo número de grados de libertad que en el ejemplo anterior, use la fórmula

DISTRITO.ESTUDIANTIL.2Х(1.69;34)

Sin ningún motivo en particular, la función STUDENT.DIST.2X() devuelve el código de error #¡NUM! si se le proporciona un valor de prueba t negativo como primer argumento.

Si las muestras contienen diferentes cantidades de datos, utilice la prueba t de dos muestras con diferentes variaciones incluidas en el paquete. Análisis de datos.

Capítulo 7: Uso de la regresión para probar diferencias entre medias de grupos

Las variables que aparecían anteriormente bajo el nombre de variables predictoras se denominarán variables de resultado en este capítulo y se utilizará el término variables factoriales en lugar del término variables predictoras.

El enfoque más simple para codificar una variable nominal es codificación ficticia(Figura 24).

Arroz. 24. Análisis de regresión basado en codificación ficticia

Cuando se utiliza codificación ficticia de cualquier tipo, se deben seguir las siguientes reglas:

• El número de columnas reservadas para datos nuevos debe ser igual al número de niveles de factor menos
• Cada vector representa un nivel de factor.
• Los sujetos de uno de los niveles, que suele ser el grupo de control, se codifican con 0 en todos los vectores.

La fórmula de las celdas F2:H6 =EST.LINEAL(A2:A22,C2:D22,;TRUE) devuelve estadísticas de regresión. A modo de comparación, en la Fig. La Figura 24 muestra los resultados del ANOVA tradicional devuelto por la herramienta. ANOVA unidireccional complementos Análisis de datos.

Codificación de efectos. En otro tipo de codificación llamada codificación de efectos, La media de cada grupo se compara con la media de las medias del grupo. Este aspecto de la codificación de efectos se debe al uso de -1 en lugar de 0 como código para el grupo, que recibe el mismo código en todos los vectores de código (Figura 25).

Arroz. 25. Codificación de efectos

Cuando se utiliza codificación ficticia, el valor constante devuelto por LINEST() es la media del grupo al que se le asignan códigos cero en todos los vectores (normalmente el grupo de control). En el caso de codificación de efectos, la constante es igual a la media global (celda J2).

El modelo lineal general es una forma útil de conceptualizar los componentes del valor de una variable de resultado:

Y ij = μ + α j + ε ij

El uso de letras griegas en esta fórmula en lugar de letras latinas enfatiza el hecho de que se refiere a la población de la cual se extraen las muestras, pero se puede reescribir para indicar que se refiere a muestras extraídas de una población determinada:

Y ij = Y̅ + a j + e ij

La idea es que cada observación Y ij pueda verse como la suma de los tres componentes siguientes: el gran promedio, μ; efecto del tratamiento j, y j; valor e ij, que representa la desviación del indicador cuantitativo individual Y ij del valor combinado del promedio general y el efecto del j-ésimo tratamiento (Fig. 26). El objetivo de la ecuación de regresión es minimizar la suma de cuadrados de los residuos.

Arroz. 26. Observaciones descompuestas en componentes de un modelo lineal general.

Análisis factorial. Si la relación entre la variable de resultado y dos o más factores se estudia simultáneamente, entonces en este caso hablamos de utilizar el análisis factorial. Agregar uno o más factores a un ANOVA unidireccional puede aumentar el poder estadístico. En el análisis de varianza unidireccional, la varianza en la variable de resultado que no puede atribuirse a un factor se incluye en el cuadrado medio residual. Pero bien puede ser que esta variación esté relacionada con otro factor. Entonces esta variación puede eliminarse del error cuadrático medio, cuya disminución conduce a un aumento en los valores de la prueba F y, por lo tanto, a un aumento en el poder estadístico de la prueba. Superestructura Análisis de datos Incluye una herramienta que procesa dos factores simultáneamente (Fig. 27).

Arroz. 27. Herramienta Análisis de varianza bidireccional con repeticiones del Paquete de Análisis

La herramienta ANOVA utilizada en esta figura es útil porque devuelve la media y la varianza de la variable de resultado, así como el valor del contador, para cada grupo incluido en el diseño. en la mesa Análisis de varianza muestra dos parámetros que no están presentes en la salida de la versión de factor único de la herramienta ANOVA. Preste atención a las fuentes de variación. Muestra Y columnas en las líneas 27 y 28. Fuente de variación columnas hace referencia al género. Fuente de variación Muestra se refiere a cualquier variable cuyos valores ocupan líneas diferentes. En la figura. 27 valores para el grupo KursLech1 están en las líneas 2-6, el grupo KursLech2 está en las líneas 7-11 y el grupo KursLechZ está en las líneas 12-16.

El punto principal es que ambos factores, Género (etiquete Columnas en la celda E28) y Tratamiento (etiquete Muestra en la celda E27), se incluyen en la tabla ANOVA como fuentes de variación. Los medios para los hombres son diferentes a los de las mujeres, y esto crea una fuente de variación. Las medias para los tres tratamientos también difieren, lo que proporciona otra fuente de variación. También existe una tercera fuente, Interacción, que hace referencia al efecto combinado de las variables Género y Tratamiento.

Capítulo 8. Análisis de covarianza

El análisis de covarianza, o ANCOVA (análisis de covariación), reduce el sesgo y aumenta el poder estadístico. Permítanme recordarles que una de las formas de evaluar la confiabilidad de una ecuación de regresión son las pruebas F:

F = Regresión MS/MS residual

donde MS (Mean Square) es el cuadrado medio, y los índices de Regresión y Residual indican los componentes de regresión y residual, respectivamente. El MS residual se calcula mediante la fórmula:

MS Residual = SS Residual / df Residual

donde SS (Suma de cuadrados) es la suma de cuadrados y df es el número de grados de libertad. Cuando se agrega covarianza a una ecuación de regresión, una parte de la suma total de cuadrados no se incluye en SS ResiduaI, sino en SS Regression. Esto conduce a una disminución del Residual de SS y, por tanto, del Residual de MS. Cuanto menor sea el residuo de MS, mayor será la prueba F y más probabilidades habrá de rechazar la hipótesis nula de que no hay diferencia entre las medias. Como resultado, se redistribuye la variabilidad de la variable de resultado. En ANOVA, cuando no se tiene en cuenta la covarianza, la variabilidad se convierte en error. Pero en ANCOVA, parte de la variabilidad previamente atribuida al término de error se asigna a una covariable y pasa a formar parte de la Regresión SS.

Considere un ejemplo en el que el mismo conjunto de datos se analiza primero mediante ANOVA y luego mediante ANCOVA (Figura 28).

Arroz. 28. El análisis ANOVA indica que los resultados obtenidos de la ecuación de regresión no son confiables

El estudio compara los efectos relativos del ejercicio físico, que mejora la fuerza muscular, y el ejercicio cognitivo (realizar crucigramas), que estimula la actividad cerebral. Los sujetos fueron asignados aleatoriamente a dos grupos para que ambos grupos estuvieran expuestos a las mismas condiciones al comienzo del experimento. Después de tres meses, se midió el rendimiento cognitivo de los sujetos. Los resultados de estas mediciones se muestran en la columna B.

El rango A2:C21 contiene los datos de origen pasados ​​a la función LINEST() para realizar análisis utilizando codificación de efectos. Los resultados de la función LINEST() se dan en el rango E2:F6, donde la celda E2 muestra el coeficiente de regresión asociado con el vector de impacto. La celda E8 contiene la prueba t = 0,93 y la celda E9 prueba la confiabilidad de esta prueba t. El valor contenido en la celda E9 indica que la probabilidad de encontrar la diferencia entre las medias de los grupos observada en este experimento es del 36% si las medias de los grupos son iguales en la población. Pocos consideran que este resultado sea estadísticamente significativo.

En la figura. La Figura 29 muestra lo que sucede cuando agrega una covariable al análisis. En este caso, agregué la edad de cada sujeto al conjunto de datos. El coeficiente de determinación R 2 para la ecuación de regresión que utiliza la covariable es 0,80 (celda F4). El valor de R 2 en el rango F15:G19, en el que repliqué los resultados de ANOVA obtenidos sin la covariable, es sólo 0,05 (celda F17). Por lo tanto, una ecuación de regresión que incluye la covariable predice valores para la variable Puntuación Cognitiva con mucha más precisión que usar el vector de Impacto solo. Para ANCOVA, la probabilidad de obtener por casualidad el valor de la prueba F que se muestra en la celda F5 es inferior al 0,01%.

Arroz. 29. ANCOVA trae una imagen completamente diferente