ESTIMACIÓ.LINEAL
La funció ESTIMACIÓ.LINEAL dona una matriu de les estadístiques per a la recta més ajustada a les dades especificades, utilitzant el mètode dels mínims quadrats.
ESTIMACIÓ.LINEAL(valors-y-coneguts; valors-x-coneguts; intersecció-y-no-zero; més-estadístics)
valors-y-coneguts: un conjunt que conté els valors y coneguts. valors-y-coneguts ha de contenir valors numèrics o valors de data/hora. Si només hi ha un conjunt de valors x coneguts, valors-y-coneguts pot tenir qualsevol mida. Si hi ha més d’un conjunt de valors x coneguts, valors-y-coneguts pot ser una columna que contingui els valors o una fila que contingui els valors, però no ambdues coses.
valors-x-coneguts: un conjunt opcional que conté els valors x coneguts. valors-x-coneguts ha de contenir valors numèrics o valors de data/hora. Si s’omet, s’assumeix que és un conjunt de la mateixa mida que valors-y-coneguts que comença per 1; per exemple: 1, 2, 3, si hi ha tres elements a valors-y-coneguts. Si només hi ha un conjunt de valors x coneguts, valors-x-coneguts (si està especificat) ha de tenir la mateixa mida que valors-y-coneguts. Si hi ha més d’un conjunt de valors x coneguts, es considera que cada fila/columna de valors-x-coneguts és un conjunt, i la mida de cada fila/columna ha de ser la mateixa que la de la fila/columna de valors-y-coneguts.
intersecció-y-no-zero: un valor modal opcional que especifica com s’ha de calcular la intersecció y (la constant b).
normal (1, CERT o omès): el valor de la intersecció y (la constant b) s’ha de calcular com es fa normalment.
forçar valor 0 (0 o FALS): el valor de la intersecció y (la constant b) ha de ser zero forçosament.
més-estadístics: un valor modal opcional que especifica si s’ha d’obtenir informació estadística addicional.
cap estadística addicional (0, FALS o omès): no inclou estadístics de regressió addicionals en la matriu obtinguda.
estadístiques addicionals (1 o CERT): obté estadístics de regressió addicionals en la matriu obtinguda.
Notes
Els valors resultants de la funció es presenten en una matriu. Una manera de llegir els valors de la matriu és utilitzar la funció ÍNDEX. Pots posar entre parèntesis la funció ESTIMACIÓ.LINEAL dins la funció ÍNDEX: =ÍNDEX(ESTIMACIÓ.LINEAL(valors-y-coneguts; valors-x-coneguts; intersecció-y; més-estadístics);y;x), on y i x són, respectivament, l’índex de fila i de columna del valor desitjat.
Si no s’obtenen estadístics addicionals (més-estadístics és FALS), la matriu obtinguda té una fila de profunditat. El nombre de columnes és igual al nombre de conjunts de valors-x-coneguts més 1. Conté les pendents de les rectes (un valor per a cada fila/columna de valors x) en ordre invers (el primer valor està relacionat amb l’última fila/columna de valors x) i, després, el valor de b (la intersecció).
Si s’obtenen estadístics addicionals (més-estadístics és CERT), la matriu conté cinc files. Consulta la informació addicional sobre aquesta matriu que hi ha immediatament després dels exemples.
Exemples |
|---|
Donada la taula següent de valors-y-coneguts (cel·les A2:A6) i valors-x-coneguts (cel·les B2:B6): |
A | B | |
|---|---|---|
1 | Y | X |
2 | 0 | -1 |
3 | 8 | 10 |
4 | 9 | 12 |
5 | 4 | 5 |
6 | 1 | 3 |
=ÍNDEX(ESTIMACIÓ.LINEAL(A2:A6; B2:B6; 1; 0); 1) dona aproximadament 0,752707581227437, a partir d’un valor normal (1) per a intersecció-y-no-zero. Aquest és el pendent de la recta més ajustada perquè hem indicat que volíem el primer valor de la matriu resultant d’ÍNDEX i només hem especificat un conjunt de valors-x-coneguts. =ÍNDEX(ESTIMACIÓ.LINEAL(A2:A6; B2:B6; 1; 0); 2) dona aproximadament 0,0342960288808646, que és b (la intersecció per a la línia més ajustada). S’ha obtingut la intersecció perquè hem indicat que volíem el segon valor de la matriu resultant d’ÍNDEX, que seria el segon valor perquè només hem especificat un conjunt de valors-x-coneguts. |
Contingut de la matriu d’estadístics addicionals
ESTIMACIÓ.LINEAL pot incloure informació estadística addicional en la matriu resultant de la funció. Per a l’explicació següent, suposem que hi ha cinc conjunts de valors x coneguts, a més dels valors y coneguts. Suposem també que els valors de valors-x-coneguts són en cinc files o cinc columnes de la taula. D’acord amb aquestes dades, la matriu que dona ESTIMACIÓ.LINEAL contindria els valors següents.
1 | 2 | 3 | 4 | 5 | 6 | |
|---|---|---|---|---|---|---|
1 | S5 | S4 | S3 | S2 | S1 | b |
2 | SE5 | SE4 | SE3 | SE2 | SE1 | SEb |
3 | C | SEy | ||||
4 | F | GL | ||||
5 | R1 | R2 |
La fila 1, columna 1, conté S5 (el pendent del cinquè conjunt de valors-x-coneguts) continuant fins a la columna 5, que contindria S1 (el pendent del primer conjunt de valors-x-coneguts). Tingues en compte que el pendent relacionat amb cada un dels conjunts de valors-x-coneguts s’obté en ordre invers.
L’última cel·la de la fila 1 conté b, la intersecció y per als valors x coneguts. En el nostre exemple seria la fila 1, columna 6.
La fila 2, columna 1, conté SE5 (l’error estàndard per al coeficient associat al cinquè conjunt de valors-x-coneguts) continuant fins a la columna 5, que contindria SE1 (el coeficient d’error estàndard del primer conjunt de valors-x-coneguts). Aquests valors s’obtenen en ordre invers; és a dir, si hi ha cinc conjunts de valors x coneguts, el valor del cinquè conjunt ocupa la primera posició a la matriu. Aquest és el mateix ordre en què s’obtenen els valors del pendent.
L’última cel·la de la fila 2 conté SEb, l’error estàndard associat al valor de la intersecció y (b). En el nostre exemple seria la fila 2, columna 6.
La fila 3, columna 1, conté C, el coeficient de determinació. Aquest estadístic compara els valors y estimats amb els reals. Si és 1, no hi ha diferència entre el valor y estimat i el valor y real. Aquest cas s’anomena correlació perfecta. Si el coeficient de determinació és 0, no hi ha correlació i l’equació de regressió especificada no permet predir cap valor y.
La fila 3, columna 2, conté SEy, l’error estàndard associat a l’estimació del valor y.
La fila 4, columna 1, conté F, el valor F observat. El valor F observat es pot utilitzar per determinar si la relació observada entre les variables dependent i independent es produeix a l’atzar.
La fila 4, columna 2, conté GL, els graus de llibertat. Utilitza l’estadístic de graus de llibertat per determinar un nivell de confiança.
La fila 5, columna 1, conté R1, la suma de quadrats de la regressió.
La fila 5, columna 2, conté R2, la suma de quadrats residual.
A continuació s’indiquen algunes qüestions que cal recordar en relació amb la matriu d’estadístics addicionals:
Tant és que els valors x i y coneguts siguin en files o en columnes. En tots dos casos la matriu obtinguda s’ordena en files, tal com mostra la taula.
L’exemple assumeix que hi ha cinc conjunts de valors x coneguts. Si n’hi hagués més o menys de cinc, el nombre de columnes de la matriu obtinguda canviaria de forma corresponent (sempre és igual al nombre de conjunts de valors x coneguts més 1), però el nombre de files romandria constant.
Si no s’especifiquen estadístics addicionals en els arguments d’ESTIMACIÓ.LINEAL, la matriu obtinguda només és igual a la primera fila.