ESTIMACION.LINEAL
La función ESTIMACION.LINEAL devuelve una matriz de las estadísticas para la línea recta que mejor se ajuste a los datos dados, utilizando el método de mínimos de cuadrados.
ESTIMACION.LINEAL(valores-y-conocidos, valores-x-conocidos, intersección-y-no-cero, estadísticas-adic.)
valores-y-conocidos: una colección que contiene los valores “y” conocidos. valores-y-conocidos debe contener valores numéricos o valores de fecha/hora. Si sólo hay una colección de valores “x” conocidos, valores-y-conocidos puede ser de cualquier tamaño. Si hay más de una colección de valores “x” conocidos, valores-y-conocidos puede ser una columna que contenga los valores o una fila que contenga los valores, pero no ambas.
valores-x-conocidos: una colección opcional que contiene los valores “x” conocidos. valores-x-conocidos debe contener valores numéricos o valores de fecha/hora. Si el valor se omite, se asumirá que es un conjunto del mismo tamaño que valores-y-conocidos que empieza por 1, por ejemplo, 1, 2, 3 si hay tres valores-y-conocidos. Si sólo hay un conjunto de valores “x” conocidos, valores-x-conocidos, si se especifica, debe ser del mismo tamaño que valores-y-conocidos. Si hay más de un conjunto de valores “x” conocidos, se considera que cada fila/columna de valores-x-conocidos es un conjunto, y el tamaño de cada fila/columna debe ser el mismo que el tamaño de la fila/columna de valores-y-conocidos.
intersección-y-no-cero: un valor modal opcional que especifica cómo debe calcularse la intersección de Y (constante B).
normal (1, VERDADERO u omitido): el valor de la intersección “y” (constante “b”) debe calcularse de manera normal.
forzar valor 0 (0, FALSO): el valor de la intersección “y” (constante “b”) debe ser 0.
estadísticas-adic.: un valor modal opcional que especifica si debe devolverse información estadística adicional.
sin estadísticas adici. (0, FALSO u omitido): no devolver estadísticas de regresión adicionales en la matriz devuelta.
estadísticas adicionales (1, VERDADERO): devuelve estadísticas de regresión adicionales en la matriz devuelta.
Notas
Los valores devueltos por la función están almacenados en una matriz. Un método para leer los valores de la matriz es utilizar la función INDICE. Puedes englobar la función ESTIMACION.LINEAL con la función INDICE: =INDICE(ESTIMACION.LINEAL(valores-y-conocidos, valores-x-conocidos, intersección-y, estadísticas-adic.), y, x) donde “y” y “x” son el índice de fila y columna del valor deseado.
Si no se devuelven estadísticas adicionales (estadísticas-adic. es FALSO), la matriz devuelta tiene una fila de profundidad. El número de columnas es igual al número de conjuntos de valores-x-conocidos más 1. Contiene las pendientes de línea (un valor por cada fila/columna de valores “x”) en orden inverso (el primer valor relacionado con la última fila/columna de valores “x”) y después el valor para “b”, la intersección.
Si se devuelven estadísticas adicionales (estadísticas-adic. es VERDADERO), la matriz contiene cinco filas. Consulta la información adicional de esta matriz que se proporciona justo después de los ejemplos.
Ejemplos |
|---|
Dada la tabla siguiente de valores- Y-conocidos (celdas A2:A6) y valores-X-conocidos (celdas B2:B6): |
A | B | |
|---|---|---|
1 | Y | X |
2 | 0 | -1 |
3 | 8 | 10 |
4 | 9 | 12 |
5 | 4 | 5 |
6 | 1 | 3 |
=INDICE(ESTIMACION.LINEAL(A2:A6,B2:B6,1,0),1) devuelve aproximadamente 0.752707581227437, dado un valor (1) normal para intersección-y-no-cero. Esta es la pendiente de la línea más adecuada, pues especificamos que queríamos que INDICE devolviese el primer valor de la matriz y sólo especificamos un conjunto de valores-x-conocidos. =INDICE(ESTIMACION.LINEAL(A2:A6,B2:B6,1,0),2) devuelve aproximadamente 0.0342960288808646, que es b (la intersección para la línea más adecuada). Se obtuvo la intersección porque especificamos que queríamos que INDICE devolviese el segundo valor de la matriz, lo cual sería el segundo valor porque sólo especificamos un conjunto de valores-x-conocidos. |
Contenido de la matriz de estadísticas adicionales
ESTIMACION.LINEAL puede incluir información estadística adicional en la matriz devuelta por la función. Para los propósitos de la siguiente explicación, supón que hay cinco conjuntos de valores “x” conocidos, además de los valores “y” conocidos. Supón también que los valores-x-conocidos están en cinco filas o en cinco columnas. De acuerdo con estos datos, la matriz que devuelve ESTIMACION.LINEAL contendría los valores siguientes.
1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
1 | S5 | S4 | S3 | S2 | S1 | b |
2 | SE5 | SE4 | SE3 | SE2 | SE1 | SEb |
3 | C | SEy | ||||
4 | M | DF | ||||
5 | R1 | R2 |
Fila 1, columna 1 contiene S5 (la pendiente del quinto conjunto de valores-x-conocidos) y continúa por la columna 5, que contendría S1 (la pendiente del primer conjunto de valores-x-conocidos). Observa que la pendiente relacionada con cada uno de los conjuntos de valores-x-conocidos se devuelve en orden inverso.
La última celda de la fila 1 contiene b, la intersección y para los valores x conocidos. En nuestro ejemplo, esto se correspondería con la fila 1 de la columna 6.
Fila 2, columna 1 contiene SE5 (el error estándar del coeficiente asociado con el quinto conjunto de valores-x-conocidos) y continúa por la columna 5, que contendría SE1 (el coeficiente del error estándar para el primer conjunto de valores-x-conocidos). Estos valores son devueltos en orden inverso, es decir, si hay cinco conjuntos de valores “x” conocidos, el valor del quinto conjunto es el primero devuelto en la matriz. Es el mismo modo en que son devueltos los valores de pendiente.
La última celda de la fila 2 contiene SEb, el error estándar asociado con el valor de la intersección-y (b). En nuestro ejemplo, esto se correspondería con la fila 2 de la columna 6.
Fila 3, columna 1 contiene C, el coeficiente de determinación. Esta estadística compara valores “y” estimados y reales. Si es 1, no hay diferencia entre el valor “y” estimado y el real. Esto se conoce como correlación perfecta. Si el coeficiente de determinación es 0, no hay correlación y la ecuación de regresión dada no ayuda a predecir un valor “y”.
Fila 3, columna 2 contiene SEy, el error estándar asociado con la estimación del valor y.
Fila 4, columna 1 contiene F, el valor F observado. El valor F observado puede utilizarse como ayuda para determinar si la relación observada entre las variables dependientes y las independientes se produce por azar.
Fila 4, columna 2 contiene DF, los grados de libertad. Utiliza la estadística de grados de libertad como ayuda para determinar un nivel de confianza.
Fila 5, columna 1 contienes R1, la suma de regresión de los cuadrados.
Fila 5, columna 2 contienes R2, la suma residual de los cuadrados.
A continuación se indican una serie de aspectos que hay que tener en cuenta sobre la matriz de estadísticas adicionales:
Es indiferente que los valores “x” e “y” conocidos estén en filas o en columnas. En cualquier caso, la matriz devuelta se ordena en filas, como se muestra en la tabla.
El ejemplo supone cinco conjuntos de valores “x” conocidos. Si hubiera más o menos de cinco, el número de columnas de la matriz devuelta cambiaría de forma correspondiente (siempre es igual al número de conjuntos de valores “x” conocidos más 1), pero el número de filas permanecería constante.
Si no se especifican estadísticas adicionales en los argumentos de ESTIMACION.LINEAL, la matriz devuelta sólo es igual a la primera fila.