ESTIMACION.LINEAL

Calcula las estadísticas de una línea utilizando el método de "mínimos de cuadrados" para calcular la línea recta que mejor se ajuste a los datos y devuelve una matriz que describe la línea. Debido a que esta función devuelve una matriz de valores, debe ser introducida como una fórmula de matrices.

La ecuación para la línea es:

y = mx + b o

y = m1x1 + m2x2 + ... + b (si hay varios rangos de valores X)

donde el valor Y dependiente es función de los valores X independientes. Los valores m son coeficientes que corresponden a cada valor X, y b es un valor constante. Observe que Y, X y m pueden ser vectores. La matriz que devuelve ESTIMACION.LINEAL es {mn,mn-1,...,m1,b}. ESTIMACION.LINEAL también puede devolver estadísticas de regresión adicionales.

Sintaxis

ESTIMACION.LINEAL(conocido_y,conocido_x,constante,estadística)

Conocido_y     es el conjunto de valores de y que se conocen en la relación y = mx+b.

  • Si la matriz definida por el argumento conocido_y ocupa una sola columna, cada columna de conocido_x se interpreta como una variable separada.
  • Si la matriz definida por el argumento conocido_y ocupa una sola fila, cada fila de conocido_x se interpreta como una variable separada.

Conocido_x     es un conjunto opcional de valores x en la relación y = mx+b.

  • La matriz definida por el argumento conocido_x puede incluir uno o varios conjuntos de variables. Si se usa una sola variable, conocido_y y conocido_x pueden ser rangos con cualquier forma, siempre y cuando sus dimensiones sean iguales. Si se usa más de una variable, conocido_y tiene que ser un vector (es decir, un rango compuesto por una fila o por una columna).
  • Si se omite conocido_x, se asume que ésta es la matriz {1;2;3;...} que tiene el mismo tamaño que conocido_y.

Constante     es un valor lógico que especifica si se ha de forzar a la constante b a ser igual a 0.

  • Si el argumento constante es VERDADERO o se omite, b se calcula normalmente.
  • Si constante es FALSO, b se establece como igual a 0 y los valores m se ajustan para encajar en y = mx.

Estadística     es un valor lógico que especifica si se deberán devolver estadísticas de regresión adicionales.

  • Si estadística es VERDADERO, ESTIMACION.LINEAL devuelve las estadísticas de regresión adicionales, de forma que la matriz devuelta es {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.
  • Si estadística es FALSO o se omite, ESTIMACION.LINEAL sólo devuelve los coeficientes m y la constante b.

Las estadísticas de regresión adicional son las que se indican a continuación.

Estadística Descripción
se1,se2,...,sen Los valores de error estándar para los coeficientes m1,m2,...,mn.
seb El valor de error estándar para la constante b (seb = #N/A cuando constante es FALSO).
r2 El coeficiente de determinación. Compara los valores y estimados y reales, y los rangos con valor de 0 a 1. Si es 1, hay una correlación perfecta en la muestra, es decir, no hay diferencia entre el valor y estimado y el valor y real. En el otro extremo, si el coeficiente de determinación es 0, la ecuación de regresión no es útil para predecir un valor y. Para obtener información sobre el cálculo de r2, consulte la sección de "Observaciones" más adelante en este mismo tema.
sey El error estándar para la estimación y.
F La estadística F o valor F observado. Utilice la estadística F para determinar si la relación observada entre las variables dependientes e independientes se produce por azar.
df Grados de libertad. Utilice los grados de libertad para encontrar valores F críticos en una tabla estadística. Compare los valores que encuentre en la tabla con la estadística F devuelta por ESTIMACION.LINEAL para determinar un nivel de confianza para el modelo. Para obtener información sobre el cálculo de df, consulte el apartado "Observaciones" más adelante en este mismo tema. El ejemplo 4 muestra el uso de F y df.
ssreg La suma de regresión de los cuadrados.
ssresid La suma residual de los cuadrados. Para obtener información sobre el cálculo de ssreg y ssresid, consulte la sección "Observaciones" más adelante en este mismo tema.

La ilustración siguiente muestra el orden en que se devuelven las estadísticas de regresión adicionales.

Hoja de cálculo

Observaciones

  • Puede describir cualquier línea recta con la pendiente y la intercepción Y:

Pendiente (m):
Para hallar la pendiente de una línea, frecuentemente indicada por m, tome dos puntos de la línea, (x1,y1) y (x2,y2): la pendiente es igual a (y2 - y1)/(x2 - x1).

Intercepción Y (b):
La intercepción Y de una línea, frecuentemente indicada por b, es el valor de Y en el punto en que la línea cruza el eje X.

La ecuación de una línea recta es y = mx + b. Cuando conozca los valores de m y b podrá calcular cualquier punto de la línea insertando el valor Y o el valor X en esa ecuación. También puede utilizar la función TENDENCIA.

  • Si sólo tiene una variable X independiente, puede obtener los valores de la pendiente y de la intercepción Y directamente utilizando las fórmulas siguientes:

Pendiente:
=INDICE(ESTIMACION.LINEAL(conocido_y,conocido_x),1)

Intersección Y:
=INDICE(ESTIMACION.LINEAL(conocido_y,conocido_x),2)

  • La exactitud de la línea calculada por ESTIMACION.LINEAL depende del grado de dispersión de los datos. Cuanto más lineales sean los datos, más exacto será el modelo ESTIMACION.LINEAL. ESTIMACION.LINEAL utiliza el método de mínimos de los cuadrados para determinar el mejor ajuste para los datos. Si sólo tiene una variable X dependiente, los cálculos para m y b se basan en las fórmulas siguientes:

Ecuación

Ecuación

donde x e y son medias de muestras, es decir, x = PROMEDIO(conocido_x) e y = PROMEDIO(conocido_y).

  • Las funciones de ajuste de línea y de curva ESTIMACION.LINEAL y ESTIMACION.LOGARITMICA pueden calcular la línea recta o la curva exponencial que mejor se ajuste a los datos. Sin embargo, debe decidir cuál de los dos resultados se ajusta mejor. Puede calcular TENDENCIA(conocido_y,conocido_x) para una línea recta o CRECIMIENTO(conocido_y,conocido_x) para una curva exponencial. Estas funciones, sin el argumento nuevo_x, devuelven una matriz de valores Y pronosticados en la línea o curva en los puntos de datos reales. Después puede comparar los valores pronosticados con los valores reales. Puede crear un gráfico con ambos para realizar una comparación visual.
  • En el análisis de regresión, Microsoft Excel calcula para cada punto el cuadrado de la diferencia entre el valor Y estimado para ese punto y su valor Y real. La suma de estas diferencias cuadradas se denomina suma de los cuadrados residual, ssresid. Microsoft Excel calcula a continuación la suma total de los cuadrados, sstotal. Cuando const=VERDADERO, o se omite, la suma total de los cuadrados es la suma de las diferencias al cuadrado entre los valores Y reales y la media de los mismos. Cuando const=FALSO, la suma total de los cuadrados es la suma de los cuadrados de los valores Y reales (sin restar el valor Y medio de cada valor Y individual). La suma de regresión de los cuadrados, ssreg, puede hallarse a partir de ssreg = sstotal - ssresid). Cuanto menor sea la suma residual de los cuadrados, en comparación con la suma total de los cuadrados, mayor será el valor del coeficiente de determinación, r2, que es un indicador de hasta qué punto la ecuación resultante del análisis de regresión explica la relación entre las variables. r2 es igual a ssreg/sstotal.
  • En algunos casos, una o varias de las columnas X (supongamos que Y y X están en columnas) pueden no tener valor predictivo adicional en presencia de las otras columnas X. En otras palabras, eliminar una o varias columnas X puede conducir a valores Y pronosticados que son igualmente exactos. En ese caso, estas columnas X redundantes deberían omitirse del modelo de regresión. Este fenómeno se denomina “colinealidad” porque cualquier columna X redundante se puede expresar como una suma de múltiplos de las columnas X no redundantes. ESTIMACION.LINEAL comprueba la colinealidad y elimina cualquier columna X redundante X del modelo de regresión cuando las identifica. Las columnas X eliminadas pueden reconocerse en la salida ESTIMACION.LINEAL como aquéllas con coeficientes 0 así como con se 0. Si una o varias columnas se eliminan por redundantes, entonces df se ve afectado porque df depende del número de columnas X utilizadas con fines predictivos. Para obtener más información sobre el cálculo de df, consulte el ejemplo 4 más abajo. Si df se modifica porque se han eliminado las columnas X redundantes, los valores de sey y F también se verán afectados. La colinealidad debería ser relativamente insólita en la práctica. No obstante, un caso en el que es más probable que se produzca es cuando algunas columnas X contienen sólo 0 y 1 como indicadores de si un sujeto de un experimento pertenece o no a un grupo en concreto. Si const = VERDADERO o se omite, ESTIMACION.LINEAL inserta una columna X adicional de todo 1 para dar forma a la intersección. Si tiene una columna con un 1 para cada sujeto que sea varón, o 0 si no lo es, y tiene también una columna con un 1 para cada sujeto que sea mujer, o 0 si no lo es, esta última columna es redundante porque las entradas de la misma se pueden obtener de restar la entrada de la columna "indicador de varón" de la entrada de la columna adicional de todo 1 agregada mediante ESTIMACION.LINEAL.
  • df se calcula de la siguiente manera cuando no se elimina ninguna columna X del modelo debido a la colinealidad: si hay k columnas de conocido_x y const = VERDADERO u omitida, entonces df = n – k – 1. Si const = FALSO, entonces df = n - k. En ambos casos, cada columna X eliminada debido a la colinealidad aumenta df en 1.
  • Las fórmulas que devuelven matrices deben introducirse como fórmulas matriciales.
  • Cuando introduzca una constante matricial, por ejemplo conocido_x, como un argumento, use punto y coma para separar los valores de una misma fila y barra inversa (\) para separar las filas. Los separadores pueden diferir dependiendo de las especificaciones locales en Configuración regional o en Opciones regionales en el Panel de control.
  • Observe que los valores Y pronosticados por la ecuación de regresión pueden no ser válidos si quedan fuera del rango de los valores Y empleados para determinar la ecuación.

Ejemplo 1   Pendiente e intercepción Y

El ejemplo puede resultar más fácil si lo copia en una hoja de cálculo en blanco.

Mostrar¿Cómo?

  1. Cree un libro o una hoja de cálculo en blanco.
  2. Seleccione el ejemplo en el tema de Ayuda. No seleccione los encabezados de fila o de columna. 

Seleccionar un ejemplo de la Ayuda

Seleccionar un ejemplo de la Ayuda
  1. Presione CTRL+C.
  2. En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.
  3. Para alternar entre ver los resultados y ver las fórmulas que devuelven los resultados, presione CTRL+` (acento grave) o, en el menú Herramientas, elija Auditoría de fórmulas y, a continuación, haga clic en Modo de auditoría de fórmulas.
 
1
2
3
4
5
A B
Valor de y conocido Valor de x conocido
1 0
9 4
5 2
7 3
Fórmula Fórmula
=ESTIMACION.LINEAL(A2:A5,B2:B5,,FALSO)

 Nota   La fórmula del ejemplo debe escribirse como fórmula matricial. Después de copiar el ejemplo en una hoja de cálculo en blanco, seleccione el rango A7:B7 a partir de la celda de fórmula. Presione F2 y, a continuación, CTRL+MAYÚS+ENTRAR. Si la fórmula no se introduce en formato matricial, el resultado sencillo es 2.

Cuando se escribe como una matriz, se devuelve la pendiente (2) y la intersección Y (1).

Ejemplo 2   Regresión lineal simple

El ejemplo puede resultar más fácil si lo copia en una hoja de cálculo en blanco.

Mostrar¿Cómo?

  1. Cree un libro o una hoja de cálculo en blanco.
  2. Seleccione el ejemplo en el tema de Ayuda. No seleccione los encabezados de fila o de columna. 

Seleccionar un ejemplo de la Ayuda

Seleccionar un ejemplo de la Ayuda
  1. Presione CTRL+C.
  2. En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.
  3. Para alternar entre ver los resultados y ver las fórmulas que devuelven los resultados, presione CTRL+` (acento grave) o, en el menú Herramientas, elija Auditoría de fórmulas y, a continuación, haga clic en Modo de auditoría de fórmulas.
 
1
2
3
4
5
6
7
A B
Mes Ventas
1 3100
2 4500
3 4400
4 5400
5 7500
6 8100
Fórmula Descripción (Resultado)
=SUMA(ESTIMACION.LINEAL(B2:B7, A2:A7)*{9,1}) Calcula las ventas del noveno mes (11000)

En general, SUMA({m,b}*{x,1}) igual a mx + b, el valor Y estimado para un valor X dado. También puede utilizar la función TENDENCIA.

Ejemplo 3   Regresión lineal múltiple

Suponga que un programador comercial está pensando en adquirir un grupo de pequeños edificios de oficinas en un distrito comercial conocido.

El programador puede utilizar el análisis de regresión lineal múltiple para estimar el valor de un edificio de oficinas en un área determinada basándose en las variables siguientes.

Variable Indica
y Valor tasado del edificio de oficinas
x1 Superficie en metros cuadrados
x2 Número de oficinas
x3 Número de entradas
x4 Antigüedad del edificio en años

Este ejemplo supone que existe una relación de línea recta entre cada variable independiente (x1, x2, x3, y x4) y la variable dependiente (y), el valor de los edificios de oficinas en esa área.

El programador elige al azar una muestra de 11 edificios de oficinas de 1.500 edificios posibles y obtiene los datos siguientes. "Media entrada" significa una entrada sólo para entregas.

El ejemplo puede resultar más fácil si lo copia en una hoja de cálculo en blanco.

Mostrar¿Cómo?

  1. Cree un libro o una hoja de cálculo en blanco.
  2. Seleccione el ejemplo en el tema de Ayuda. No seleccione los encabezados de fila o de columna. 

Seleccionar un ejemplo de la Ayuda

Seleccionar un ejemplo de la Ayuda
  1. Presione CTRL+C.
  2. En la hoja de cálculo, seleccione la celda A1 y presione CTRL+V.
  3. Para alternar entre ver los resultados y ver las fórmulas que devuelven los resultados, presione CTRL+` (acento grave) o, en el menú Herramientas, elija Auditoría de fórmulas y, a continuación, haga clic en Modo de auditoría de fórmulas.
 
1
2
3
4
5
6
7
8
9
10
11
12
A B C D E
Superficie (x1) Oficinas (x2) Entradas (x3) Antigüedad (x4) Valor tasado (y)
2310 2 2 20 142.000
2333 2 2 12 144.000
2356 3 1,5 33 151.000
2379 3 2 43 150.000
2402 2 3 53 139.000
2425 4 2 23 169.000
2448 2 1,5 99 126.000
2471 2 2 34 142.900
2494 3 3 23 163.000
2517 4 4 55 169.000
2540 2 3 22 149.000
Fórmula
=ESTIMACION.LINEAL(E2:E12,A2:D12,VERDADERO,VERDADERO)

 Nota   La fórmula del ejemplo debe escribirse como fórmula matricial. Después de copiar el ejemplo en una hoja de cálculo en blanco, seleccione el rango A14:E18 a partir de la celda de fórmula. Presione F2 y, a continuación, CTRL+MAYÚS+ENTRAR. Si la fórmula no se introduce en formato matricial, el resultado sencillo es -234,2371645.

Cuando se introduce como una matriz, se devuelven las siguientes estadísticas de regresión. Utilice esta clave para identificar las estadísticas deseadas.

Hoja de cálculo

Ahora puede obtenerse la ecuación de regresión múltiple, y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b, utilizando los valores de la fila 14:

y = 27,64*x1 + 12.530*x2 + 2.553*x3 - 234,24*x4 + 52.318

Ahora el programador puede calcular el valor tasado de un edificio de oficinas en la misma zona con 2.500 metros cuadrados, tres oficinas, dos entradas y una antigüedad de 25 años, utilizando la ecuación siguiente:

y = 27,64*2.500 + 12.530*3 + 2.553*2-234,24*25 + 52.318 = 158.261 $

O bien, puede copiar la tabla siguiente a la celda A21 del libro de ejemplo.

Superficie (x1) Oficinas (x2) Entradas (x3) Antigüedad (x4) Valor tasado (y)
2500 3 2 25 =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14

También puede utilizar la función TENDENCIA para calcular este valor.

Ejemplo 4   Utilizar las estadísticas F y r2

En el ejemplo anterior, el coeficiente de determinación, o r2, es 0,99675 (consulte la celda A17 en el resultado de ESTIMACION.LINEAL), que indicaría una relación marcada entre las variables independientes y el precio de venta. Puede utilizar la estadística F para determinar si estos resultados, con un valor r2 tan alto, se produjeron por azar.

Suponga por un momento que en realidad no existe relación entre las variables, pero que ha extraído una muestra peculiar de 11 edificios de oficinas que hace que el análisis estadístico demuestre una relación marcada. El término "alfa" se utiliza para la probabilidad de llegar a la conclusión errónea de que existe una relación.

F y df en la salida ESTIMACION.LINEAL se pueden utilizar para determinar la probabilidad de que se produzca por azar un valor F más elevado. F se puede comparar con los valores críticos de las tablas de distribución F publicadas o DISTR.F de Excel se puede utilizar para calcular la probabilidad de que se produzca por azar un valor F superior. La distribución F apropiada tiene los grados de libertad v1 y v2. Si n es el número de puntos de datos y const = VERDADERO o se omite, entonces v1 = n – df – 1 y v2 = df. (Si const = FALSO, entonces v1 = n – df y v2 = df.) DISTR.F(F,v1,v2) de Excel devolverá la probabilidad de que se produzca al azar un valor F superior. En el ejemplo 4, df = 6 (celda B18) y F = 459.753674 (celda A18).

Suponiendo un valor alfa de 0,05, v1 = 11 – 6 – 1 = 4 y v2 = 6, el valor crítico de F es 4,53. Puesto que F = 459,753674 es mucho más elevado que 4,53, es extremadamente improbable que un valor F tan elevado se produzca por azar. (Con Alfa = 0,05, la hipótesis de que no hay relación entre conocido_y y conocido_x hay que rechazarla cuando F sobrepasa el nivel crítico, 4,53.) Con DISTR.F de Excel se puede obtener la probabilidad de que un valor F tan elevado se produzca por azar. DISTR.F(459,753674. 4. 6) = 1,37E-7, una probabilidad extremadamente pequeña. Se puede concluir, bien buscando el nivel crítico de F en una tabla, o bien utilizando DISTR.F de Excel, que la ecuación de regresión es útil para predecir el valor tasado de los edificios de oficinas de este área. Recuerde que es vital utilizar los valores correctos de v1 y v2 calculados en el párrafo anterior.

Ejemplo 5   Calcular la estadística t

Otra prueba hipotética determinará si cada coeficiente de la pendiente es útil para estimar el valor tasado de un edificio de oficinas del ejemplo 3. Por ejemplo, para probar si el coeficiente de antigüedad tiene significado estadístico, divida -234,24 (coeficiente de la pendiente de antigüedad) entre 13,268 (el error estándar estimado de los coeficientes de antigüedad en la celda A15). El siguiente es el valor t observado:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Si el valor absoluto de t es suficientemente alto, puede deducirse que el coeficiente de la pendiente es útil para calcular el valor tasado del edificio de oficinas del ejemplo 3. La tabla muestra los valores absolutos de los 4 valores t observados.

Si consulta una tabla de un manual de estadística, observará que el valor t crítico, de dos colas, con 6 grados de libertad y alfa = 0,05 es 2,447. Este valor crítico puede encontrarse también utilizando la función DISTR.T.INV de Excel. DISTR.T.INV (0,05.6) = 2,447. Puesto que el valor absoluto de t, 17,7, es superior a 2,447, la antigüedad es una variable importante para estimar el valor tasado de un edificio de oficinas. El significado estadístico de cada una de las demás variables independientes puede probarse de forma similar. Los siguientes son los valores t observados para cada una de las variables independientes.

Variable valor t observado
Superficie 5,1
Número de oficinas 31,3
Número de entradas 4,8
Edad 17,7

Todos estos valores tienen un valor absoluto superior a 2,447; por tanto, todas las variables utilizadas en la ecuación de regresión son útiles para predecir el valor tasado de los edificios de oficinas de esta área.

 
 
Corresponde a:
Excel 2003