Cómo hacer análisis de regresión En Excel
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:
enlace: # link1head [ANÁLISIS DE REGRESIÓN UTILIZANDO FUNCIONES DE EXCEL (BÚSQUEDA DE REGRESIÓN MANUAL)] enlace: # link2head [ANÁLISIS DE REGRESIÓN UTILIZANDO EL EXCEL ‘S ANALYSIS TOOLPAK ADD-IN]
* enlace: # link3head [TABLA DE REGRESIÓN EN EXCEL]
Hagámoslo …
Escenario:
Supongamos que vende refrescos. Qué 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 puedes incrementar tus ventas cambiando tu estrategia?
Será increíblemente rentable. ¿Verdad? … Lo sé. Entonces empecemos.
Tienes 11 registros de vendedores y refrescos vendidos.
Ahora, con base 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 hacer estimaciones tan cercanas. Para hacerlo, primero debemos conocer la regresión.
ANÁLISIS DE REGRESIÓN USANDO FUNCIONES DE 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. Desea 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 la variable dependiente como y. En nuestro caso, los refrescos se venden xy el número de vendedores es y.
Si queremos saber cuántos refrescos se venderán si nombramos a 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:
El refresco vendido es x
El número de vendedores es y
La y predicha (número de vendedores), también llamada Ecuación de regresión, sería
|
* (relax, I’ve got it covered) |
Ahora debe estar preguntándose dónde el stat 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.
Ahora pasemos a nuestro cálculo:
Paso 1: Prepara esta pequeña mesa
Paso 2: Encuentre la pendiente de la línea de regresión
La función de Excel para pendientes es
|
=SLOPE(known_y’s,known_x’s) |
Sus conocidos_y están en el rango B2: B12 y los conocidos_x están en el rango C2: C12
En la celda B16, escriba la fórmula a continuación
|
=SLOPE(B2:B12, C2:C12) |
(_Nota: La pendiente también se llama coeficiente de x en la ecuación de regresión) _ Obtendrá 0.058409. Redondea hasta 2 dígitos decimales y obtendrás 0.06.
Paso 3: Encuentre la intersección de la línea de regresión
La función de Excel para la intersección es
|
=INTERCEPT(known_y’s, known_x’s)* |
Sabemos cuáles son nuestras x e y conocidas En la celda B17, escriba esta fórmula
|
=INTERCEPT(B2:B12, C2:C12)* |
Obtendrá un valor de -1,1118969. Resumen a 2 dígitos decimales. Obtendrá -1.11. Nuestra ecuación de regresión lineal es = x0.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 (Regression Equation) |
Obtendrá 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.
|
Hence, to achieve the target of 2000 Soft Drink Sales, you need an estimate of 115.71 salesmen or say 116 since it is illegal to cut humans into pieces. |
Ahora, con esto, puede realizar fácilmente 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 la analítica, no se dice ni se cree nada. Debe dar un porcentaje de confiabilidad en su estimación. Es como dar un certificado de su ecuación.