La regresión es una herramienta de análisis que utilizamos para analizar grandes cantidades de datos y realizar pronósticos y predicciones en Microsoft Excel.
¿Quiere predecir el futuro? No, no vamos a aprender astrología. Nos gustan los números y hoy aprenderemos el análisis de regresión en Excel.
Para predecir estimaciones futuras, estudiaremos:
- ANÁLISIS DE REGRESIÓN CON FUNCIONES EXCEL (BÚSQUEDA DE REGRESIÓN MANUAL)
- ANÁLISIS DE REGRESIÓN UTILIZANDO EL COMPLEMENTO TOOLPAK DE ANÁLISIS DE EXCEL
- TABLA DE REGRESIÓN EN EXCEL
Vamos a hacerlo…
Guión:
Supongamos que vende refrescos. Cuán genial será si puedes predecir:
- ¿Cuántos refrescos se venderán el próximo año según los datos del año anterior?
- ¿Qué campos deben enfocarse?
- ¿Y cómo puede aumentar sus ventas cambiando su estrategia?
Será increíblemente rentable. ¿Verdad? … Lo sé. Entonces empecemos.
Tienes 11 registros de vendedores y refrescos vendidos.
Ahora, basándose en estos datos, desea predecir el número de vendedores necesarios para lograr 2000 ventas de refrescos.
La ecuación de regresión es una herramienta para realizar estimaciones tan precisas. Para hacerlo, primero debemos conocer la regresión.
ANÁLISIS DE REGRESIÓN CON FUNCIONES EXCEL (BÚSQUEDA DE REGRESIÓN MANUAL)
Esta parte le hará comprender la regresión mejor que simplemente decir el procedimiento de regresión de Excel.
Introducción:
Regresión lineal simple:
El estudio de la relación entre dos variables se denomina Regresión lineal simple. Donde una variable depende de la otra variable independiente. La variable dependiente a menudo se llama por nombres como Variable impulsada, de respuesta y de destino. Y la variable independiente a menudo se pronuncia como una variable impulsora, predictiva o simplemente independiente. Estos nombres los describen claramente.
Ahora comparemos esto con su escenario. Quiere saber el número de vendedores necesarios para lograr 2000 ventas. Entonces, aquí, la variable dependiente es el número de vendedores y la variable independiente es la venta de refrescos.
La variable independiente se denota principalmente como X y variable dependiente como y.
En nuestro caso se venden refrescos X y el numero de vendedores es y.
Si queremos saber cuántos refrescos se venderán si nombramos 200 vendedores, entonces el escenario será al revés.
Hacia adelante.
La matemática "simple" de la ecuación de regresión lineal:
Bueno, no es sencillo. Pero Excel lo hizo fácil de hacer.
Necesitamos predecir el número requerido de vendedores para los 11 casos para obtener la 12ª predicción más cercana.
Digamos:
Refresco vendido es X
El número de vendedores es y
El predicho y (número de vendedores) también llamado Ecuación de regresión, sería
| x * Pendiente + Intercepción (relájate, lo tengo cubierto) |
Ahora debes estar preguntándote dónde está estadística obtendrá la pendiente y la intersección. No se preocupe, Excel tiene funciones para ellos. No es necesario que aprenda a encontrar la pendiente e interceptarla manualmente.
Si quieres, prepararé un tutorial aparte para eso. Házmelo saber en la sección de comentarios. Estas son algunas herramientas importantes de análisis de datos.
Pasemos ahora a nuestro cálculo:
Paso 1: Prepara esta pequeña mesa
Paso 2: Encuentra la pendiente de la recta de regresión
La función de Excel para pendientes es
| = PENDIENTE (conocidos_y, conocidos_x) |
Tus conocidos están dentro del rango B2: B12 y known_x's están dentro del rango C2: C12
En la celda B16, escribe la fórmula a continuación
| = PENDIENTE (B2: B12, C2: C12) |
(Nota: La pendiente también se llama coeficiente de x en la ecuación de regresión)
Conseguirás 0.058409. Redondea hasta 2 dígitos decimales y obtendrás 0.06.
Paso 3: Hallar la intersección de la línea de regresión
La función de Excel para la intersección es
| =INTERCEPT (conocidos_y, conocidos_x) |
Sabemos lo que es nuestro x y y conocidas
En la celda B17, escribe esta fórmula
| = INTERCEPTAR (B2: B12, C2: C12) |
Obtendrá un valor de -1,1118969. Resumen a 2 dígitos decimales. Conseguirás -1.11.
Nuestra ecuación de regresión lineal es = x * 0.06 + (-1.11). Ahora podemos predecir fácilmente y dependiendo del objetivo x.
Paso 4: En D2 escribe la fórmula a continuación
| =C2* $ B $ 16 + $ B $ 17(Ecuación de regresión) |
Obtendrás un valor de 13.55.
Seleccione D2 a D13 y presione CTRL + D para completar la fórmula en el rango D2: D13
En la celda D13 tiene el número requerido de vendedores.
| Por lo tanto, para lograr el objetivo de 2000 Ventas de refrescos, necesita una estimación de 115,71 vendedores o digamos 116, ya que es ilegal cortar a los humanos en pedazos. |
Ahora, con esto, puede realizar fácilmente un análisis Y si … en Excel. Simplemente cambie el número de ventas y le mostrará muchos vendedores que se necesitarán para lograr ese objetivo de ventas.
Juega para descubrirlo:
¿Cuánta mano de obra necesita para aumentar las ventas?
¿Cuántas ventas aumentarán si aumenta sus vendedores?
Haga su estimación más confiable:
Ahora sabe que necesita 116 vendedores para realizar 2000 ventas.
En analítica, nada se dice y se cree. Debe dar un porcentaje de confiabilidad en su estimación. Es como dar un certificado de su ecuación.
Fórmula del coeficiente de correlación:
Lo siguiente que se le preguntará es cuánto están relacionadas estas dos variables. En términos estáticos, debe indicar el coeficiente de correlación.
La función de Excel para la correlación es
| = CORREL (matriz1, matriz2) |
En su caso, known_x's y Know_y's son array1 y array2 independientemente.
En B18 ingrese esta fórmula
| = CORREL ((B2: B12, C2: C12) |
Usted tendrá 0.919090. Formatee la celda B2 en porcentaje. Ahora tienen 92% de correlación.
Ahora, que es esto 92% medio. Significa que hay 92% de posibilidades de ventas aumentan si aumenta el número de vendedores y 92% de ventas disminuyen si disminuye el número de vendedores. Se llama Coeficiente de correlación positiva.
R Escudero (R 2):
El valor de R Squire le dice en qué porcentaje su ecuación de regresión no es una casualidad. Cuánto es exacto por los datos proporcionados.
La función de Excel para R squire es RSQ.
| RSQ (conocidos_y, conocidos_x) |
En nuestro caso, obtendremos el valor de R squire en la celda B19.
En B19 ingrese esta fórmula
| = RSQ (B2: B12, C2: C12) |
Entonces tenemos el 84% del valor de r Cuadrado. Lo cual es una muy buena explicación de nuestra regresión. Dice que el 84% de nuestros datos no es casualidad. Y (número de vendedores) depende mucho de X (ventas de refrescos).
Hay muchas otras pruebas que podemos hacer con estos datos para asegurar nuestra regresión. Pero manualmente será un procedimiento largo y complejo. Es por eso que Excel proporciona Analysis Toolpak. Con esta herramienta podemos hacer este análisis de regresión en segundos.
REGRESIÓN EN EXCEL UTILIZANDO EL COMPLEMENTO DE ANALYSIS TOOLPAK DE EXCEL
Si ya sabe qué son las ecuaciones de regresión y solo desea obtener los resultados rápidamente, esta parte es para usted. Pero si desea comprender las ecuaciones de regresión fácilmente, desplácese hacia arriba hasta ANÁLISIS DE REGRESIÓN CON FUNCIONES DE EXCEL (BÚSQUEDA DE REGRESIÓN MANUAL).
Excel proporciona una gran cantidad de herramientas para el análisis en su paquete de herramientas de análisis. De forma predeterminada, no está disponible en la pestaña Datos. Necesitas agregarlo. Así que agreguemos primero.
Agregar el paquete de herramientas de análisis a Excel 2016
Si no sabe dónde está el análisis de datos en Excel, siga estos pasos
Paso 1: vaya a Opciones de Excel: ¿Expediente? Opciones? Complementos
Paso 2: haga clic en Complementos. Verá una lista de complementos disponibles.
Seleccione Analysis ToolPak y, en la parte inferior de la ventana, busque administrar. En administrar, seleccione Complementos de Excel y haga clic en IR.
Se abrirá la ventana de complementos. Aquí, seleccione Analysis ToolPak. Luego haga clic en el botón Aceptar.
Ahora puede acceder a todas las funciones de ToolPak de análisis de datos desde la pestaña Datos.
Uso de Analysis ToolPak para regresión
Paso 1: Vaya a la pestaña Datos, busque Análisis de datos. Luego haz clic en él.
Aparecerá un cuadro de diálogo.
Paso 2: busque "Regresión" en la lista de Herramientas de análisis y presione el botón Aceptar.
La regresión Aparecerá la ventana de entrada. Verá varias opciones de entrada disponibles. Pero por ahora, solo nos concentraremos en el rango Y y el rango X, dejando todo lo demás por defecto.
Paso 4: Proporcione aportes:
No. de vendedores es Y
Las ventas de refrescos son X
Por eso
- Rango Y = B2: B11
Y
- Rango X = C2: C11
Para el rango de salida, he seleccionado E4 en la misma hoja. Puede seleccionar una nueva hoja de trabajo para obtener resultados en una nueva hoja de trabajo en el mismo libro de trabajo o en un libro completamente nuevo. Cuando haya terminado con sus entradas, presione el botón Aceptar.
Resultados:
Se le brindará una variedad de información de sus datos. No se abrume. No es necesario consumir todos los platos.
Solo trataremos aquellos resultados que nos ayudarán a estimar el número requerido de vendedores.
Paso 5: Conocemos la ecuación de regresión para la estimación de y, es decir
x * Pendiente + Intercepción
Solo necesitamos ubicar Pendiente y Interceptar en resultados.
Y aquí están.
El coeficiente de intersección se menciona claramente.
La pendiente se escribe como "X Variable 1’, Algunas veces también se menciona como el coeficiente de X. Redondea y obtendremos -1,11 como intercepción y 0.06 como pendiente.
Paso 6: A partir de los resultados, podemos impulsar la ecuación de regresión. Y eso seria
= x * (0,06) + (-1,11)
Prepara esta tabla en Excel.
Por ahora, X es 2000, que se encuentra en la celda E2.
En la celda F2 ingrese esta fórmula
= E2 * F21 + F20
Obtendrás un resultado de 115.7052757.
Redondearlo nos dará 116 de vendedores requeridos.
De modo que hemos aprendido a formar la ecuación de regresión manualmente y utilizando Analysis ToolPak. ¿Cómo puedes usar esta ecuación para estimar estadísticas futuras?
Ahora comprendamos el resultado de la regresión proporcionado por Analysis Toolpak.
Comprensión de la salida de regresión:
No hay ningún beneficio si realiza un análisis de regresión con el paquete de herramientas de análisis en Excel y no puede interpretar su significado.
Sección de resumen:
Como sugiere el nombre, es un resumen de los datos.
-
- R múltiple: indica cómo se ajusta la ecuación de regresión a los datos. También se le llama coeficiente de correlación.
En nuestro caso, es 0.919090619 o 0.92 (redondeo). Esto significa que hay un 92% de posibilidades de un aumento en las ventas si aumentamos nuestro recuento de vendedores.
-
- R Cuadrado: indica la confiabilidad de la regresión encontrada. Nos dice cuántas observaciones forman parte de nuestra línea de regresión. En nuestro caso, es 0.844727566 o 0.85. Significa que nuestra regresión se ajusta al 85%.
- Cuadrado R ajustado: El cuadrado ajustado es solo una versión más probada del cuadrado R. Principalmente útil en análisis de regresión múltiple.
- Error estándar: Mientras que R. Squire le dice cuántos puntos de datos caen cerca de la línea de regresión, el error estándar le dice qué tan lejos puede ir un punto de datos desde la línea de regresión.
En nuestro caso, es 6.74.
- Observación: este es simplemente el número de observaciones, que es 11 en nuestro ejemplo.
Sección Anova:
Esta sección apenas se utiliza en regresión lineal.
- df. Es un grado de libertad. Se utiliza al calcular la regresión manualmente.
- SS. Suma de cuadrados. Es solo una suma de cuadrados de variaciones. Se usa para encontrar los valores de R. Squire.
- SRA. Esto significa valor al cuadrado.
- Y 5. F y significancia de F. Si la significancia de F (valor p de la pendiente) es menor que la prueba F, entonces puede descartar la hipótesis nula y probar su hipótesis. En lenguaje simple, puede concluir que hay algún efecto de x sobre y cuando se cambia.
En nuestro caso, F es 48,96264 y la significancia de F es 0,000063. Significa que nuestra regresión se ajusta a los datos.
Sección de regresión:
En esta sección, tenemos los dos valores más importantes para nuestra ecuación de regresión.
- Intercepción: Aquí tenemos una intersección que indica dónde intercepta x en Y. Esta es una parte importante de la ecuación de regresión. Es -1,11 en nuestro caso.
- X variable 1 (Pendiente). También llamado coeficiente de x. Define la tangente de la recta de regresión.
TABLA DE REGRESIÓN EN EXCEL
En Excel, es fácil trazar un gráfico de regresión. Solo sigue estos pasos. Para agregar el gráfico de regresión en Excel 2016, 2013 y 2010, siga estos sencillos pasos.
Paso 1. Tenga sus x conocidas en la primera columna y conozca las y en la segunda.
En nuestro caso, sabemos que las x conocidas son refrescos vendidos. Y los conocidos son vendedores.
Paso 2. Seleccione su rango de x e y conocido.
Paso 3: Vaya a la pestaña Insertar y haga clic en el gráfico de dispersión.
Tendrá una tabla que se ve así.
Paso 4. Agregue la línea de tendencia: Vaya al diseño y localice la opción de línea de tendencia en la sección de análisis.
En la opción Línea de tendencia, haga clic en Línea de tendencia lineal.
Tendrá su gráfico con este aspecto.
Este es tu gráfico de regresión.
Ahora, si agrega los datos a continuación y amplía los datos seleccionados. Verá un cambio en su gráfico.
Para nuestro ejemplo, agregamos 2000 al refresco vendido y dejamos los vendedores en blanco. Y cuando extendamos el rango del gráfico, esto es lo que tendremos.
Dará el número requerido de vendedores para realizar 2000 ventas de refrescos en forma gráfica. Que está ligeramente por debajo de 120 en el gráfico. Y a partir de nuestra ecuación de regresión, sabemos que es 116.
En este artículo, traté de cubrir todo en Análisis de regresión de Excel. Expliqué la regresión en Excel 2016. La regresión en Excel 2010 y Excel 2013 es la misma que en Excel 2016.
Para cualquier consulta adicional sobre este tema, use la sección de comentarios. Hacer una pregunta, dar una opinión o simplemente mencionar mis errores gramaticales. Todo es bienvenido. No dude en utilizar la sección de comentarios.
Cómo calcular la función MODO en Excel
Cómo calcular la función media en Excel
Cómo crear un gráfico de desviación estándar
Estadísticas descriptivas en Microsoft Excel 2016
Cómo utilizar la función NORMDIST de Excel
Cómo usar el diagrama y análisis de Pareto
Articulos populares:
50 atajos de Excel para aumentar su productividad
Cómo utilizar la función BUSCARV en Excel
Cómo usar la función CONTAR.SI en Excel 2016
Cómo utilizar la función SUMIF en Excel