Pronóstico de intervalos en excel para análisis de regresión. Konrad Carlberg. Análisis de regresión en Microsoft Excel. Regresión lineal en Excel

28 Oct

¡Buenas tardes, queridos lectores del blog! Hoy hablaremos de regresiones no lineales. La solución a las regresiones lineales se puede ver en ENLACE.

este método Se utiliza principalmente en modelos y pronósticos económicos. Su objetivo es observar e identificar dependencias entre dos indicadores.

Los principales tipos de regresiones no lineales son:

• polinomio (cuadrático, cúbico);
• hiperbólico;
• sosegado;
• demostrativo;
• logarítmico

También se puede utilizar varias combinaciones. Por ejemplo, para el análisis de series de tiempo en banca, seguros y estudios demográficos, se utiliza la curva de Gompzer, que es un tipo de regresión logarítmica.

Al realizar pronósticos utilizando regresiones no lineales, lo principal es averiguar el coeficiente de correlación, que nos mostrará si existe una relación estrecha entre dos parámetros o no. Como regla general, si el coeficiente de correlación es cercano a 1, entonces existe una conexión y el pronóstico será bastante preciso. Una vez más elemento importante regresiones no lineales es el error relativo promedio ( A ), si está en el intervalo<8…10%, значит модель достаточно точна.

Aquí es donde probablemente terminemos el bloque teórico y pasemos a los cálculos prácticos.

Tenemos una tabla de ventas de automóviles durante un período de 15 años (llamémoslo X), el número de pasos de medición será el argumento n, también tenemos ingresos para estos períodos (llamémoslo Y), necesitamos predecir qué los ingresos serán en el futuro. Construyamos la siguiente tabla:

Para estudiar, necesitaremos resolver la ecuación (dependencia de Y de X): y=ax 2 +bx+c+e. Esta es una regresión cuadrática por pares. En este caso, aplicamos el método de mínimos cuadrados para encontrar los argumentos desconocidos: a, b, c. Conducirá a un sistema de ecuaciones algebraicas de la forma:

Para resolver este sistema utilizaremos, por ejemplo, el método de Cramer. Vemos que las sumas incluidas en el sistema son coeficientes de las incógnitas. Para calcularlos, agregaremos varias columnas a la tabla (D,E,F,G,H) y firmaremos según el significado de los cálculos: en la columna D elevaremos x al cuadrado, en E lo elevaremos al cubo, en F multiplicaremos los exponentes x e y, en H elevamos x al cuadrado y multiplicamos por y.

Obtendrá una tabla del formulario llena con los elementos necesarios para resolver la ecuación.

Formemos una matriz. A sistema que consta de coeficientes para incógnitas en los lados izquierdos de las ecuaciones. Coloquémoslo en la celda A22 y llamémoslo " A=". Seguimos el sistema de ecuaciones que elegimos para resolver la regresión.

Es decir, en la celda B21 debemos colocar la suma de la columna donde elevamos el indicador X a la cuarta potencia - F17. Simplemente hagamos referencia a la celda: "=F17". A continuación, necesitamos la suma de la columna donde se elevó X al cubo - E17, luego vamos estrictamente de acuerdo con el sistema. Por lo tanto, necesitaremos completar toda la matriz.

De acuerdo con el algoritmo de Cramer, escribiremos una matriz A1 similar a A, en la que, en lugar de los elementos de la primera columna, se deben colocar los elementos de los lados derechos de las ecuaciones del sistema. Es decir, la suma de la columna X al cuadrado multiplicada por Y, la suma de la columna XY y la suma de la columna Y.

También necesitaremos dos matrices más, llamémoslas A2 y A3, en las que la segunda y tercera columnas consistirán en los coeficientes de los lados derechos de las ecuaciones. La imagen será así.

Siguiendo el algoritmo elegido, necesitaremos calcular los valores de los determinantes (determinantes, D) de las matrices resultantes. Usemos la fórmula MOPRED. Colocaremos los resultados en las celdas J21:K24.

Calcularemos los coeficientes de la ecuación de Cramer en las celdas opuestas a los determinantes correspondientes usando la fórmula: a(en la celda M22) - “=K22/K21”; b(en la celda M23) - “=K23/K21”; Con(en la celda M24) - “=K24/K21”.

Obtenemos nuestra ecuación deseada de regresión cuadrática pareada:

y=-0,074x 2 +2,151x+6,523

Evaluemos la cercanía de la relación lineal utilizando el índice de correlación.

Para calcular, agregue una columna adicional J a la tabla (llamémosla y*). El cálculo será el siguiente (según la ecuación de regresión que obtuvimos): “=$m$22*B2*B2+$M$23*B2+$M$24.” Coloquémoslo en la celda J2. Todo lo que queda es arrastrar el marcador de autocompletar hasta la celda J16.

Para calcular las sumas (promedio Y-Y) 2, agregue las columnas K y L a la tabla con las fórmulas correspondientes. Calculamos el promedio de la columna Y usando la función PROMEDIO.

En la celda K25 colocaremos la fórmula para calcular el índice de correlación - “=ROOT(1-(K17/L17))”.

Vemos que el valor de 0,959 es muy cercano a 1, lo que significa que existe una estrecha relación no lineal entre las ventas y los años.

Queda por evaluar la calidad del ajuste de la ecuación de regresión cuadrática resultante (índice de determinación). Se calcula utilizando la fórmula del índice de correlación al cuadrado. Es decir, la fórmula en la celda K26 será muy simple: “=K25*K25”.

El coeficiente de 0,920 es cercano a 1, lo que indica una alta calidad de ajuste.

El último paso es calcular el error relativo. Agreguemos una columna e ingresemos la fórmula allí: “=ABS((C2-J2)/C2), ABS - módulo, valor absoluto. Dibuja el marcador hacia abajo y en la celda M18 muestra el valor promedio (PROMEDIO), asigna un formato de porcentaje a las celdas. El resultado obtenido - 7,79% está dentro de los valores de error aceptables.<8…10%. Значит вычисления достаточно точны.

Si surge la necesidad, podemos construir un gráfico usando los valores obtenidos.

Se adjunta un archivo de ejemplo: ¡ENLACE!

Categorías:// del 28/10/2017

Análisis de regresión V 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, podrías 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 usar 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 en ella clic derecho ratón 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 S x 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 cuantificación la probabilidad de que la decisión tomada sea incorrecta dada la 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. 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 tienen la varianza explicada. 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 del par de funciones SLOPE() e INTERCEPT()

Para ingresar la función TENDENCIA() como fórmula matricial en las celdas G3:G12, seleccione el rango G3:G12, ingrese la fórmula TENDENCIA (NW:S12;V3: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 le dice que esta fórmula se percibe 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 en la ventana que se abre menú contextual párrafo 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 las bibliotecas como en la asequibilidad de las universidades, se podría obtener una cuantificación más precisa de la fuerza de la relación entre las variables de interés, es decir, el número de libros y el número de 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 en este caso la designació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 más importante es la precisión con la que funciona. ecuación general regresión, 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. regresión múltiple, el problema 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 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 mejor estimación 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 lo siguiente: 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 valor calculado El coeficiente de regresión es 2,092, 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 comprobar si el coeficiente de determinación R2 para la regresión tiene suficiente ponchada, permitiéndonos rechazar la hipótesis de que en la población tiene un valor de 0.0, lo que indica la ausencia de 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, imagínelos como 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 simple ecuación lineal En una regresión con una variable predictora, siempre habrá una correlación perfecta entre los valores predichos y los valores de la variable predictora, ya que en dicha ecuación los valores predictores se multiplican por una constante y se obtiene otra constante. añadido 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 la observación. valor especificado. 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.

mientras buscamos valores críticos distribuciones t que cortan 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 (Fig. 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 que acepte la decisión correcta, rechazando la hipótesis sobre coeficiente cero La regresión de 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 la influencia 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 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 quizás 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 están en dependencia lineal de valores X; c) la dispersión de los residuos 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 detectar 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 propuesto herramienta útil 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 Versiones de Excel 2010 tres disponibles diferentes formas una función que devuelve la fracción de la distribución a la izquierda y/o a la derecha de valor establecido prueba t. 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 — DISTR.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 proporciona como primer argumento! valor negativo prueba t.

Si las muestras contienen numero diferente 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 el mismo que la media del grupo al que se asignan todos los vectores. códigos cero(normalmente este es el grupo de control). En el caso de codificación de efectos, la constante es igual a la media global (celda J2).

General modelo lineal - manera útil conceptualización de los componentes del valor de la variable resultante:

Y ij = μ + α j + ε ij

Usar en esta fórmula letras griegas en lugar de latín, enfatiza el hecho de que se refiere a la población de la que se extraen las muestras, pero se puede reescribir para indicar que se refiere a las muestras extraídas de la población publicada:

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 j-ésimo tratamiento(Figura 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 se puede eliminar 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 ocupen varias cuerdas. 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 ecuación de regresión son 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 regresión y componentes residuales 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 probable será 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 con ANOVA y luego con 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 desarrolla 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 36% si las medias del grupo 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

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.Crear 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, haga clic en la figura resultante y presione 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 lineas horizontales cuadrículas (pestaña disposición , entonces neto ). Los cambios y configuraciones básicos se realizan en la pestaña. Disposición

La línea de regresión se construyó en MS Excel. Ahora puedes agregarlo al texto de la obra.

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 supone que el coeficiente de determinación debe ser 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 dependencia funcional entre 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 caso 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 almacena en archivo de palabra. También se crea automáticamente una plantilla para probar la solución en Excel.

Para modelos estadísticos en muchos casos es necesario determinar la exactitud del pronóstico. Esto se hace mediante cálculos especiales en Microsoft Excel y se utilizará el coeficiente de determinación. Se denota como R^2.

Los modelos estadísticos se pueden dividir en niveles de calidad dependiendo del coeficiente. Modelos de 0,8 a 1 buena calidad, los modelos de calidad suficiente tienen un nivel de 0,5 a 0,8, y mala calidad tiene un rango de 0 a 0,5.

Método para determinar la precisión utilizando la función KVPIRSON.

EN función lineal el coeficiente de determinación será igual al cuadrado coeficiente de correlación. Se puede calcular usando función especial. Primero, creemos una tabla con datos.

Luego debe seleccionar el lugar donde se mostrará el resultado del cálculo y hacer clic en el botón Insertar función.

Después de esto, se abrirá una ventana especial. La categoría debe seleccionarse "Estadística" y seleccionar QPIRSON. Esta función le permite determinar el coeficiente de correlación con respecto a la función de Pearson, respectivamente. valor cuadrado coeficiente de correlación = coeficiente de determinación.

Luego de confirmar la acción, aparecerá una ventana en la que deberás ingresar " Valores conocidos X" y "Valores Y conocidos". Haga clic en el campo "Valores Y conocidos" con el ratón y seleccione los datos de la columna Y en la ventana de trabajo. Acción similar Esto lo hacemos con otro campo, seleccionando datos de la tabla X.

Como resultado de estas acciones, el valor del coeficiente de determinación se mostrará en la celda que previamente se seleccionó para mostrar el resultado.

Determinación del coeficiente de determinación si la función no es lineal.

Si la función no es lineal, el kit de herramientas de Excel también le permite calcular el coeficiente utilizando la herramienta Regresión. Se puede encontrar en el paquete de análisis de datos. Pero primero debes activar este paquete yendo a la sección "Archivo" y abriendo "Opciones" en la lista.

Después de esto, podrá ver una nueva ventana en la que debe seleccionar "Complementos" en el menú y, en el campo especial para administrar complementos, seleccionar "Complementos de Excel" y acceder a ellos.

Después de ir a Complementos de Excel, aparecerá una nueva ventana. En él se pueden ver los complementos disponibles para el usuario. Marque la casilla junto a "Paquete de análisis" y confirme la acción.

Puede encontrarlo en la sección "Datos", luego de ir a ella, haga clic en "Análisis de datos" en el lado derecho de la pantalla.

Después de abrirlo, seleccione "Regresión" de la lista y confirme la acción.

Después de esto, aparecerá una nueva ventana en la que podrá realizar ajustes. Los datos de entrada le permiten configurar el valor de los intervalos X e Y; simplemente seleccione las celdas de argumento correspondientes de otro argumento. En el campo nivel de confiabilidad puede configurar indicador requerido. Las opciones de salida le permiten especificar dónde se mostrará el resultado. Si, por ejemplo, selecciona mostrar en la hoja actual, primero debe seleccionar el elemento "Intervalo de salida" y hacer clic en el área de la ventana principal donde se mostrará el resultado en el futuro y las coordenadas de la celda. se mostrará en el campo correspondiente. Al final confirmamos la acción.

El resultado aparecerá en la ventana de trabajo. Como estamos calculando el coeficiente de determinación, necesitamos el coeficiente R en los resultados. Si miras el valor, verás que se refiere a la mejor calidad.

Método para determinar el coeficiente de determinación de una línea de tendencia.

Habiendo creado una tabla con los valores correspondientes, creamos un gráfico. Para dibujar una línea de tendencia en él, debe hacer clic en el gráfico, es decir, en el área donde se dibuja la línea. En la parte superior de la barra de herramientas, seleccione la sección "Diseño" y en ella seleccione "Línea de tendencia". Después de eso, en contexto este ejemplo Seleccione "Aproximación exponencial" de la lista.

La línea de tendencia se mostrará en el gráfico como una curva de color negro.

Para mostrar el coeficiente de determinación, debe hacer clic derecho en la curva negra y seleccionar "Formatear línea de tendencia" de la lista.

Después de esto, aparecerá una nueva ventana. En él debe marcar la casilla y seleccionar acción requerida(que se muestra en la captura de pantalla). Gracias a esto, el coeficiente se mostrará en el gráfico. Una vez hecho esto, cierre la ventana.

Después de cerrar la ventana de formato de línea de tendencia, podrá ver el valor del coeficiente de determinación en la ventana de trabajo.

Si el usuario necesita un tipo diferente de línea de tendencia, en la ventana "Formato de línea de tendencia" puede seleccionarlo. No olvide configurarlo antes al crear una línea de tendencia en la sección "Diseño" o en el menú contextual. Además, no olvide marcar la casilla de la función R^2.

Como resultado, puede ver el cambio en la línea de tendencia y el número de confianza.

Después de ver diferentes variaciones de las líneas de tendencia, el usuario puede determinar cuál es la más adecuada para él, ya que el indicador de confiabilidad puede cambiar dependiendo de la línea elegida. El coeficiente máximo es uno, lo que significa máxima confiabilidad, pero no siempre es posible alcanzar este valor.

Por tanto, se consideraron varios métodos para encontrar el coeficiente de determinación. El usuario puede elegir el más óptimo para sus propósitos.