Saltar al contenido principal
LibreTexts Español

13.11: Cómo usar Microsoft Excel® para el análisis de regresión

  • Page ID
    151026
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    Esta sección de este capítulo está aquí en reconocimiento de que lo que ahora estamos pidiendo requiere mucho más que un cálculo rápido de una relación o una raíz cuadrada. De hecho, el uso del análisis de regresión era casi inexistente antes de mediados del siglo pasado y no se convirtió realmente en una herramienta ampliamente utilizada hasta quizás finales de los sesenta y principios de los 70, incluso entonces la capacidad computacional de las máquinas IBM más grandes es risible según los estándares actuales. En los primeros días los programas fueron desarrollados por los investigadores y compartidos. No había mercado para algo llamado “software” y ciertamente nada se llamaba “aplicaciones”, un participante en el mercado con solo unos pocos años de antigüedad.

    Con la llegada de la computadora personal y la explosión de un mercado vital de software tenemos una serie de paquetes de regresión y análisis estadístico para elegir. Cada uno tiene sus méritos. Hemos elegido Microsoft Excel debido a la amplia disponibilidad tanto en los campus universitarios como en el mercado postuniversitario. Stata es una alternativa y tiene características que serán importantes para un estudio econométrico más avanzado si eliges seguir este camino. Existen paquetes aún más avanzados, pero normalmente requieren que el analista realice una cantidad significativa de programación para realizar su análisis. El objetivo de esta sección es demostrar cómo usar Excel para ejecutar una regresión y luego hacerlo con un ejemplo de una versión simple de una curva de demanda.

    El primer paso para hacer una regresión usando Excel es cargar el programa en su computadora. Si tienes Excel tienes el Analysis ToolPak aunque es posible que no lo tengas activado. El programa requiere una cantidad significativa de espacio por lo que no se carga automáticamente.

    Para activar la herramienta de análisis siga estos pasos:

    Haga clic en “Archivo” > “Opciones” > “Complementos” para abrir un menú del complemento “Toolpaks”. Seleccione “Herramientas de análisis” y haga clic en “IR” junto a “Administrar: complementos de excel” cerca de la parte inferior de la ventana. Esto abrirá una nueva ventana donde haga clic en “Herramientas de análisis” (asegúrese de que haya una marca de verificación verde en la casilla) y luego haga clic en “Aceptar”. Ahora debería haber una pestaña Análisis debajo del menú de datos. Estos pasos se presentan en las siguientes capturas de pantalla.

    Figura\(\PageIndex{17}\)

    Figura\(\PageIndex{18}\)

    Figura\(\PageIndex{19}\)

    Figura\(\PageIndex{20}\)

    Haga clic en “Datos” luego en “Análisis de datos” y luego haga clic en “Regresión” y “Aceptar”. Enhorabuena, has llegado a la ventana de regresión. La ventana te pide tus entradas. Al hacer clic en el cuadro junto a los\(X\) rangos\(Y\) y, podrá usar la función de clic y arrastrar de Excel para seleccionar sus rangos de entrada. Excel tiene una peculiaridad extraña y esa es la función de clic y caída requiere que las variables independientes, las\(X\) variables, estén todas juntas, lo que significa que forman una sola matriz. Si tus datos están configurados con la\(Y\) variable entre dos columnas de\(X\) variables Excel no te permitirá usar click y arrastrar. Como ejemplo, digamos que la Columna A y la Columna C son variables independientes y la Columna B es la\(Y\) variable, la variable dependiente. Excel no le permitirá hacer clic y soltar los rangos de datos. La solución es mover la columna con la\(Y\) variable a la columna A y luego puede hacer clic y arrastrar. El mismo problema vuelve a surgir si se quiere ejecutar la regresión con solo algunas de las\(X\) variables. Deberá configurar la matriz para que todas las\(X\) variables que desee retroceder estén en una matriz estrechamente formada. Estos pasos se presentan en las siguientes tomas de escena.

    Figura\(\PageIndex{21}\)

    Figura 13.22

    Una vez que haya seleccionado los datos para su análisis de regresión y le haya dicho a Excel cuál es la variable dependiente (\(Y\)) y cuáles son los objetos\(X\) de valor independientes, tiene varias opciones en cuanto a los parámetros y cómo se mostrará la salida. Consulte la Figura de captura de pantalla en la\(\PageIndex{22}\) sección “Entrada”. Si marca la casilla “etiquetas” el programa colocará la entrada en la primera columna de cada variable como su nombre en la salida. Se puede ingresar un nombre real, como precio o ingresos en un análisis de demanda, en la fila uno de la hoja de cálculo de Excel para cada variable y se mostrará en la salida.

    El nivel de significación también puede ser establecido por el analista. Esto no cambiará el estadístico t calculado, llamado t stat, sino que alterará el valor p para el estadístico t calculado. También alterará los límites de los intervalos de confianza para los coeficientes. Siempre se presenta un intervalo de confianza del 95 por ciento, pero con un cambio en esto también obtendrás otros niveles de confianza para los intervalos.

    Excel también te permitirá suprimir la intercepción. Esto obliga al programa de regresión a minimizar la suma residual de cuadrados bajo la condición de que la línea estimada deba pasar por el origen. Esto se hace en los casos en los que no hay sentido en el modelo a algún valor distinto de cero, cero para el inicio de la línea. Un ejemplo es una función de producción económica que es una relación entre el número de unidades de un insumo, digamos horas de trabajo, y la producción. No hay sentido de salida positiva con cero trabajadores.

    Una vez que se ingresan los datos y se realizan las elecciones, haga clic en Aceptar y los resultados se enviarán a una nueva hoja de trabajo separada por defecto. El resultado de Excel se presenta de una manera típica de otros programas de paquetes de regresión. El primer bloque de información da las estadísticas generales de la regresión: Múltiple\(R\),\(R\) Cuadrado, y el\(R\) cuadrado ajustado por grados de libertad, que es el que se quiere reportar. También se obtiene el error estándar (de la estimación) y el número de observaciones en la regresión.

    El segundo bloque de información se titula ANOVA que significa Análisis de varianza. Nuestro interés en esta sección es la columna marcada\(F\). Esta es la\(F\) estadística calculada para la hipótesis nula de que todos los coeficientes son iguales a cero verso la alternativa de que al menos uno de los coeficientes no sea igual a cero. Esta prueba de hipótesis se presentó en 13.4 bajo “¿Qué tan buena es la ecuación?” La siguiente columna da el valor de p para esta prueba bajo el título “Significancia F”. Si el valor p es menor que digamos 0.05 (el\(F\) estadístico calculado está en la cola) podemos decir con 90% de confianza que no podemos aceptar las hipótesis nulas de que todos los coeficientes son iguales a cero. Esto es algo bueno: significa que al menos uno de los coeficientes es significativamente diferente de cero por lo tanto sí tienen un efecto sobre el valor de\(Y\).

    El último bloque de información contiene las pruebas de hipótesis para el coeficiente individual. Los coeficientes estimados, la intercepción y las pendientes, se listan primero y luego cada error estándar (del coeficiente estimado) seguido por la estadística t (estadística t de student calculada para la hipótesis nula de que el coeficiente es igual a cero). Comparamos la estadística t y el valor crítico de la t del estudiante, dependiendo de los grados de libertad, y determinamos si tenemos evidencia suficiente para rechazar el nulo sobre el que la variable no tiene efecto\(Y\). Recordemos que hemos establecido la hipótesis nula como el status quo y nuestra afirmación de que sabemos lo que causó el\(Y\) cambio está en la hipótesis alternativa. Queremos rechazar el status quo y sustituir nuestra versión del mundo, la hipótesis alternativa. La siguiente columna contiene los valores p para esta prueba de hipótesis seguidos del límite superior e inferior estimado del intervalo de confianza del parámetro de pendiente estimada para varios niveles de confianza establecidos por nosotros al inicio.

    Estimando la demanda de rosas

    Aquí hay un ejemplo del uso del programa Excel para ejecutar una regresión para un caso concreto en particular: estimar la demanda de rosas. Estamos tratando de estimar una curva de demanda, que desde la teoría económica esperamos que ciertas variables afecten la cantidad de bien que compramos. La relación entre el precio de un bien y la cantidad demandada es la curva de demanda. Más allá de eso tenemos la función de demanda que incluye otras variables relevantes: el ingreso de una persona, el precio de los bienes sustitutos, y tal vez otras variables como la temporada del año o el precio de los bienes de cortesía. La cantidad demandada será nuestra\(Y\) variable, y Precio de rosas, Precio de claveles e Ingresos serán nuestras variables independientes, las\(X\) variables.

    Por todas estas variables la teoría nos dice la relación esperada. Para el precio del bien en cuestión, rosas, la teoría predice una relación inversa, la curva de demanda negativamente inclinada. La teoría también predice la relación entre la cantidad demandada de un bien, aquí rosas, y el precio de un sustituto, claveles en este ejemplo. La teoría predice que esta debe ser una relación positiva o directa; a medida que cae el precio del sustituto sustituimos lejos de las rosas al sustituto más barato, los claveles. Una reducción en el precio del sustituto genera una reducción en la demanda del bien analizado, rosas aquí. La reducción genera reducción es una relación positiva. Para los bienes normales, la teoría también predice una relación positiva; a medida que aumentan nuestros ingresos compramos más del bien, rosas. Esperamos estos resultados porque eso es lo que se predice por cien años de teoría económica e investigación. Esencialmente estamos probando estas hipótesis centenarias. Los datos recopilados fueron determinados por el modelo que se está probando. Este siempre debería ser el caso. No se está haciendo estadísticas inferenciales arrojando una montaña de datos a una computadora y pidiendo a la máquina una teoría. Teoría primero, sigue la prueba.

    Estos datos aquí son los precios promedio nacionales y el ingreso es el ingreso personal per cápita de la nación. La cantidad demandada es el total de ventas anuales nacionales de rosas. Estos son datos anuales de series temporales; estamos rastreando el mercado de rosas para Estados Unidos de 1984-2017, 33 observaciones.

    Por la peculiar forma en que Excel requiere cómo se ingresan los datos en el paquete de regresión lo mejor es tener las variables independientes, precio de rosas, precio de claveles e ingresos uno al lado del otro en la hoja de cálculo. Una vez ingresados tus datos en la hoja de cálculo siempre es bueno mirar los datos. Examine el rango, las medias y las desviaciones estándar. Utilice su comprensión de las estadísticas descriptivas desde la primera parte de este curso. En grandes conjuntos de datos no podrás “escanear” los datos. El Análisis ToolPac facilita la obtención del rango, la media, las desviaciones estándar y otros parámetros de las distribuciones. También puedes obtener rápidamente las correlaciones entre las variables. Examinar en busca de valores atípicos. Revisar la historia. ¿Pasó algo? ¿Fue aquí una huelga laboral, cambio en las tarifas de importación, algo que hace insólitas estas observaciones? No tome los datos sin duda. Puede haber habido un errata en alguna parte, quien sabe sin revisión.

    Vaya a la ventana de regresión, ingrese los datos y seleccione el nivel de confianza del 95% y haga clic en “Aceptar”. Puede incluir las etiquetas en el rango de entrada si ha puesto un título en la parte superior de cada columna, pero asegúrese de hacer clic en el cuadro “etiquetas” en la página principal de regresión si lo hace.

    El resultado de regresión debe aparecer automáticamente en una nueva hoja de trabajo.

    Figura\(\PageIndex{23}\)

    El primer resultado presentado es el R-Square, una medida de la fuerza de la correlación entre\(Y\) y\(X_1\)\(X_2\), y\(X_3\) tomada como grupo. Nuestro R-cuadrado aquí de 0.699, ajustado por grados de libertad, significa que 70% de la variación en Y, demanda de rosas, puede explicarse por variaciones en\(X_1\),\(X_2\), y\(X_3\), Precio de rosas, Precio de claveles e Ingresos. No existe una prueba estadística para determinar la “significancia” de un\(R^2\). Por supuesto\(R^2\) se prefiere un mayor, pero realmente es la significación de los coeficientes lo que determinará el valor de la teoría que se está probando y que pasará a formar parte de cualquier discusión de política si se demuestra que son significativamente diferentes de cero.

    Mirando el tercer panel de salida podemos escribir la ecuación como:

    \[Y=b_{0}+b_{1} X_{1}+b_{2} X_{2}+b_{3} X_{3}+e\nonumber\]

    donde\(b_0\) está la intercepción,\(b_1\) es el coeficiente estimado sobre el precio de las rosas, y b 2 es el coeficiente estimado sobre el precio de los claveles,\(b_3\) es el efecto estimado del ingreso y e es el término de error. La ecuación está escrita en letras romanas indicando que estos son los valores estimados y no los parámetros poblacionales,\(\beta\)'s.

    Nuestra ecuación estimada es:

    \[\text { Quantity of roses sold }=183,475-1.76 \text { Price of roses }+1.33 \text { Price of carnations }+3.03 \text { Income }\nonumber\]

    Primero observamos que los signos de los coeficientes son los esperados de la teoría. La curva de demanda es descendente con el signo negativo para el precio de las rosas. Además los signos tanto del precio de los claveles como de los coeficientes de ingresos son positivos como se esperaría de la teoría económica.

    Interpretar los coeficientes puede decirnos la magnitud del impacto de un cambio en cada variable sobre la demanda de rosas. Es la capacidad de hacer esto lo que hace que el análisis de regresión sea una herramienta tan valiosa. Los coeficientes estimados nos dicen que un incremento del precio de las rosas en un dólar conducirá a una reducción de 1.76 en el número de rosas compradas. El precio de los claveles parece jugar un papel importante en la demanda de rosas ya que vemos que aumentar el precio de los claveles en un dólar aumentaría la demanda de rosas en 1.33 unidades ya que los consumidores sustituirían lejos de los claveles ahora más caros. De igual manera, aumentar el ingreso per cápita en un dólar conducirá a un incremento de 3.03 unidades en las rosas compradas.

    Estos resultados están en línea con las predicciones de la teoría económica respecto a las tres variables incluidas en esta estimación de la demanda de rosas. Es importante tener primero una teoría que prediga la significancia o al menos la dirección de los coeficientes. Sin una teoría que probar, esta herramienta de investigación no es mucho más útil que los coeficientes de correlación que aprendimos antes.

    No podemos detenernos ahí, sin embargo. Primero tenemos que verificar si nuestros coeficientes son estadísticamente significativos desde cero. Establecimos una hipótesis de:

    \[H_{0} : \beta_{1}=0\nonumber\]

    \[H_{\mathrm{a}} : \beta_{1} \neq 0\nonumber\]

    para los tres coeficientes de la regresión. Recordemos desde antes que no podremos decir definitivamente que nuestro estimado\(b_1\) es la población real real de\(\beta_1\), sino más bien solo eso con\((1-\alpha) \%\) nivel de confianza que no podemos rechazar la hipótesis nula de que nuestro estimado\(\beta_1\) es significativamente diferente de cero. El analista está haciendo una afirmación de que el precio de las rosas provoca un impacto en la cantidad demandada. En efecto, que cada una de las variables incluidas incide en la cantidad de rosas demandadas. Por lo tanto, el reclamo se encuentra en las hipótesis alternativas. Se necesitará una probabilidad muy grande, 0.95 en este caso, para derrocar la hipótesis nula, el status quo, eso\(\beta = 0\). En todas las pruebas de hipótesis de regresión la afirmación está en la alternativa y la afirmación es que la teoría ha encontrado una variable que tiene un impacto significativo en la\(Y\) variable.

    El estadístico de prueba para esta hipótesis sigue la fórmula estandarizadora familiar que cuenta el número de desviaciones estándar\(t\),, que el valor estimado del parámetro\(b_1\),, está lejos del valor hipotético,\(\beta_0\), que es cero en este caso:

    \[t_{c}=\frac{b_{1}-\beta_{0}}{S_{b_{1}}}\nonumber\]

    La computadora calcula este estadístico de prueba y lo presenta como “t stat”. Puede encontrar este valor a la derecha del error estándar de la estimación del coeficiente. El error estándar del coeficiente para\(b_1\) está\(S_{b_1}\) en la fórmula. Para llegar a una conclusión comparamos este estadístico de prueba con el valor crítico del estudiante\(t\) en grados de libertad\(n-3-1 =29\), y alfa = 0.025 (nivel de significancia del 5% para una prueba de dos colas). Nuestra\(t\) estadística para\(b_1\) es aproximadamente 5.90 que es mayor a 1.96 (el valor crítico que buscamos en la tabla t), por lo que no podemos aceptar nuestras hipótesis nulas de ningún efecto. Concluimos que Price tiene un efecto significativo porque el valor t calculado está en la cola. Realizamos la misma prueba para b2 y b3. Para cada variable, encontramos que no podemos aceptar la hipótesis nula de no relación porque las estadísticas t calculadas están en la cola para cada caso, es decir, mayores que el valor crítico. Se ha determinado que todas las variables en esta regresión tienen un efecto significativo en la demanda de rosas.

    Estas pruebas nos indican si un coeficiente individual es o no significativamente diferente de cero, pero no aborda la calidad general del modelo. Hemos visto que la R cuadrada ajustada por grados de libertad indica que este modelo con estas tres variables explica 70% de la variación en la cantidad de rosas demandadas. También podemos realizar una segunda prueba del modelo tomado en su conjunto. Esta es la\(F\) prueba presentada en la sección 13.4 de este capítulo. Debido a que se trata de una regresión múltiple (más de una X), utilizamos la\(F\) prueba -para determinar si nuestros coeficientes afectan colectivamente\(Y\). La hipótesis es:

    \[H_{0} : \beta_{1}=\beta_{2}=\ldots=\beta i=0\nonumber\]

    \[H_a: "\text{at least one of the} \beta_i \text{ is not equal to 0}"\nonumber\]

    Bajo la sección ANOVA de la salida encontramos el\(F\) estadístico calculado para esta hipótesis. Para este ejemplo la\(F\) estadística es 21.9. Nuevamente, comparar el\(F\) estadístico calculado con el valor crítico dado nuestro nivel de significación deseado y los grados de libertad nos permitirá llegar a una conclusión.

    La mejor manera de llegar a una conclusión para esta prueba estadística es usar la regla de comparación de valores p. El valor p es el área en la cola, dado el\(F\) estadístico calculado. En esencia la computadora está encontrando el\(F\) valor en la tabla para nosotros y calculando el valor p. En el Resumen Salida bajo “significancia F” se encuentra esta probabilidad. Para este ejemplo, se calcula que es 2.6\(X\) 10-5, o 2.6 luego moviendo los decimales cinco lugares hacia la izquierda. (.000026) Este es un nivel de probabilidad casi infinitesimal y ciertamente es menor que nuestro nivel alfa de .05 para un nivel de significancia del 5 por ciento.

    Al no poder aceptar las hipótesis nulas concluimos que esta especificación de este modelo tiene validez porque al menos uno de los coeficientes estimados es significativamente diferente de cero. Dado que\(F\) -calculado es mayor que\(F\) -crítico, no podemos aceptar H0, lo que significa que\(X_1\),\(X_2\) y\(X_3\) juntos tiene un efecto significativo sobre\(Y\).

    El desarrollo de maquinaria informática y el software útil para la investigación académica y empresarial ha permitido responder preguntas que hace apenas unos años ni siquiera pudimos formular. Los datos están disponibles en formato electrónico y pueden trasladarse a su lugar para su análisis de maneras y a velocidades inimaginables hace una década. La gran magnitud de los conjuntos de datos que hoy en día se pueden utilizar para la investigación y el análisis nos da una mayor calidad de resultados que en días pasados. Incluso con solo una hoja de cálculo de Excel podemos realizar investigaciones de muy alto nivel. Esta sección te da las herramientas para llevar a cabo algunas de estas investigaciones muy interesantes con el único límite siendo tu imaginación.


    This page titled 13.11: Cómo usar Microsoft Excel® para el análisis de regresión is shared under a CC BY 4.0 license and was authored, remixed, and/or curated by OpenStax via source content that was edited to the style and standards of the LibreTexts platform; a detailed edit history is available upon request.