Funkcija LINEST

Šajā rakstā paskaidrota funkcijas LINEST formulas sintakse un lietošana programmā Microsoft Excel.

Apraksts

Funkcija LINEST aprēķina taisnes statistiku, izmantojot “mazāko kvadrātu” metodi ar datiem saskaņotas taisnes aprēķināšanai, un pēc tam atgriež taisni raksturojošu masīvu. Lai aprēķinātu statistiku citiem modeļu tipiem, kas nezināmajos parametros ir lineāri, ieskaitot polinoma, logaritmiskās, eksponenciālās un pakāpju sērijas, funkciju LINEST var arī kombinēt ar citām funkcijām. Šī funkcija atgriež vērtību masīvu, tāpēc tā ir jāievada kā masīva formula. Šajā rakstā pēc piemēriem ir instrukcijas.

Taisnes vienādojums ir:

y = mx + b

–vai–

y = m1x1 + m2x2 + ... + b

ja ir vairāki x vērtību diapazoni, kur atkarīgās y vērtības ir neatkarīgo x vērtību funkcija. Attiecīgi m vērtības ir katras x vērtības koeficienti un b ir konstanta vērtība. Ievērojiet, ka y, x un m var būt vektori. Funkcijas LINEST atgrieztais masīvs ir {mn,mn-1,...,m1,b}. Funkcija LINEST var atgriezt arī regresijas papildu statistiku.

Sintakse

LINEST(zināmie_y, [zināmie_x], [konst], [statist])

Funkcijas LINEST sintaksei ir šādi argumenti.

Sintakse

• known_y Obligāts. Zināmo y vērtību kopa attiecībā y = mx + b.

  • Ja known_y diapazons ir vienā kolonnā, katra known_x kolonna tiek interpretēta kā atsevišķs mainīgais.
  • Ja known_y diapazonu ietver viena rinda, katra known_x rinda tiek interpretēta kā atsevišķs mainīgais.

• known_x Neobligāts. X vērtību kopa, kura, iespējams, jau ir zināma attiecībā y = mx + b.

  • known_x diapazons var ietvert vienu vai vairākas mainīgo kopas. Ja tiek izmantots tikai viens mainīgais, known_y un known_x var būt jebkuras formas diapazoni, ja vien to izmēri ir vienādi. Ja tiek izmantoti vairāki mainīgie, known_y jābūt vektoram (tas ir, diapazonam, kura augstums ir viena rinda vai kura platums ir viena kolonna).
  • Ja known_x ir izlaists, tiek pieņemts, ka šī masīva {1,2,3,...} izmērs ir tāds pats kā known_y.

• konst Neobligāts. Loģiskā vērtība, kas norāda, vai konstanti b iestatīt vienādu ar 0.

  • Ja konst ir TRUE vai tiek izlaists, b tiek aprēķināts kā parasti.
  • Ja konst ir FALSE, b tiek uzstādīta vērtība 0 un m ērtības tiek pielāgotas, lai ietilptu y = mx.

• Statistika Neobligāts. Loģiskā vērtība, kas norāda, vai atgriezt papildu regresijas statistiku.

  • Ja statist ir TRUE, LINEST atgriež regresijas papildu statistiku; Rezultātā atgrieztais masīvs ir {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r², sey; F,df; ssreg,ssresid}.
  • Ja statist ir FALSE vai izlaista, tad funkcija LINEST atgriež tikai m koeficientus un konstanti b.
    Regresijas papildu statistika ir šāda.

Statistika	Apraksts
se1,se2,...,sen	Koeficientu m1,m2,...,mn standarta kļūdu vērtības.
seb	Konstantes b (seb = #N/A, ja konstante ir FALSE) standarta kļūdas vērtība.
r2	Noteikšanas koeficients. Salīdzina y aptuvenās vērtības ar faktiskajām un diapazonus ar vērtību no 0 līdz 1. Ja ir 1, tad piemērā  ir pilnīga korelācija — nav atšķirības starp y aptuveno un faktisko vērtību. Ja otrā proporcijas malējā loceklī noteikšanas koeficients ir 0, tad y vērtības noteikšanai korelācijas vienādojums nav noderīgs. Informāciju par2 aprēķināšanu skatiet turpmāk šīs tēmas sadaļā Piebildes.
sey	Aptuvenās y vērtības standarta kļūda.
F	F statistika vai F novērotā vērtība. Izmantojiet F statistiku, lai noteiktu, vai novērotā attiecība starp atkarīgajiem un neatkarīgajiem mainīgajiem parādās nejauši.
df	Brīvības pakāpes. Izmantojiet brīvības pakāpes, lai statistiskajā tabulā atrastu F kritiskās vērtības. Salīdziniet tabulā atrastās vērtības ar LINEST atgriezto F statistiku, lai noteiktu modeļa ticamības līmeni. Informāciju par df aprēķināšanu skatiet turpmāk šīs tēmas sadaļā Piebildes. 4. piemērā parādīta F un df izmantošana.
ssreg	Kvadrātu regresijas summa.
ssresid	Kvadrātu starpības summa. Informāciju par ssreg un ssresid aprēķināšanu skatiet turpmāk šīs tēmas “Piebildēs”.

Attēlā redzama kārtība, kādā tiek atgriezta regresijas papildu statistika.

Piebildes

• Jebkuru taisni var raksturot ar slīpni un y krustpunktu:
  Slīpums (m):
  Lai atrastu līnijas slīpumu, ko bieži raksta kā m, ņemiet divus punktus uz līnijas (x1,y1) un (x2,y2); Slīpums ir vienāds ar (y2 - y1)/(x2 - x1).
  Y krustpunkts (b):
  Taisnes y krustpunkts, ko parasti pieraksta kā b, ir y vērtība punktā, kur taisne krusto y asi.
  Taisnes vienādojums ir y = mx + b. Ja m un b vērtības ir zināmas, tad var aprēķināt jebkuru taisnes punktu, ievietojot vienādojumā y vai x vērtību. Var izmantot arī funkciju TREND.

• Ja ir tikai viens neatkarīgs mainīgais x, tad iegūt slīpnes un y krustpunkta vērtības var tieši, izmantojot šādas formulas:
  Slīpums:
  =INDEX(LINEST(known_y,known_x),1)
  Y krustpunkts:
  =INDEX(LINEST(known_y,known_x),2)

• Ar funkciju LINEST aprēķinātas taisnes precizitāte ir atkarīga no datu izkaisījuma pakāpes. Jo lineārāki dati, jo precīzāks funkcijas LINEST modelis. Datu vislabākā piemērojuma noteikšanai funkcija LINEST izmanto mazāko kvadrātu metodi. Ja ir tikai viens neatkarīgs mainīgais x, tad m un b aprēķini pamatojas uz šādām formulām:
  
  
  kur x un y ir parauga vidējais; t.i., x = AVERAGE(zināmie) un y = AVERAGE(known_y).

• Līniju un līknes pielāgošanas funkcijas LINEST un LOGEST var aprēķināt labāko taisni vai eksponenciālo līkni, kas atbilst jūsu datiem. Tomēr jums ir jāizlemj, kurš no šiem diviem rezultātiem vislabāk atbilst jūsu datiem. Taisnei var aprēķināt TREND(known_y, known_x s) vai eksponenciālai līknei GROWTH(known_y, known_x). Šīs funkcijas bez new_x argumenta atgriež to y vērtību masīvu, kas prognozētas pa līniju vai līkni jūsu faktiskajos datu punktos. Pēc tam var salīdzināt prognozētās vērtības ar faktiskajām vērtībām. Iespējams, vēlēsities izveidot diagrammu tās abas, lai vizuāli salīdzinātu.

• Regresijas analīzē programma Excel aprēķina katra punkta kvadrātisko atšķirību starp punkta aptuveno un faktisko y vērtību. Šo kvadrātisko atšķirību summu sauc par kvadrātu starpības summu ssresid. Pēc tam programma Excel aprēķina kvadrātu kopsummu sstotal. Ja arguments konst = TRUE vai izlaists, tad kvadrātu kopsumma ir faktisko un vidējo y vērtību summas kvadrātu atšķirība. Ja arguments konst = FALSE, tad kvadrātu kopsumma ir faktisko y vērtību kvadrātu summa (neatņemot vidējo y vērtību no katras atsevišķās y vērtības). Tad kvadrātu regresijas summu ssreg var atrast šādi: ssreg = sstotal - ssresid. Jo mazāka ir kvadrātu atlikusī summa, salīdzinot ar kopējo kvadrātu summu, jo lielāka ir noteikšanas koeficienta r² vērtība, kas ir rādītājs tam, cik labi vienādojums, kas izriet no regresijas analīzes, izskaidro attiecības starp mainīgajiem. r² ir vienāds ar ssreg/sstotal.

• Dažos gadījumos vienai vai vairākām X kolonnām (pieņemsim, ka Y un X atrodas kolonnās) citu X kolonnu klātbūtnē var nebūt papildu prognozējošās vērtības. Citiem vārdiem sakot, vienas vai vairāku X kolonnu dzēšana var novest pie prognozētajām Y vērtībām, kas ir tikpat precīzas. Šādā gadījumā šīs liekās X kolonnas ir jāizlaiž no regresijas modeļa. Šo parādību sauc par kolinearitāti, jo jebkuru lieku X kolonnu var izteikt kā nelieko X kolonnu daudzkārtņu summu. Funkcija LINEST pārbauda kolinearitāti un, tās identificējot, no regresijas modeļa noņem liekās X kolonnas. Noņemtās X kolonnas LINEST rezultātos var atpazīt kā tādas, kurām papildus 0 se vērtībām ir koeficienti 0. Ja viena vai vairākas kolonnas tiek noņemtas kā liekas, tiek ietekmēta df, jo df ir atkarīga no X kolonnu skaita, kas faktiski tiek izmantotas prognozēšanas nolūkos. Detalizētu informāciju par df aprēķināšanu skatiet 4. piemērā. Ja df tiek mainīts, jo tiek noņemtas liekās X kolonnas, tiek ietekmētas arī sey un F vērtības. Kolinearitātei praksē vajadzētu būt salīdzinoši retai. Tomēr viens gadījums, kad tas ir visticamāk, ir tad, ja dažās X kolonnās ir tikai 0 un 1 vērtības kā rādītāji tam, vai eksperimenta subjekts ir vai nav konkrētas grupas loceklis. Ja konst = TRUE vai tiek izlaista, funkcija LINEST efektīvi ievieto papildu X kolonnu ar visām 1 vērtībām, lai modelētu krustpunktu. Ja jums ir kolonna ar 1 katrai tēmai, ja vīrietis ir vīrietis, vai 0, ja nav, un jums ir arī kolonna ar 1 katrai tēmai, ja sieviete ir sieviete, vai 0, ja nav, šī pēdējā kolonna ir lieka, jo ierakstus tajā var iegūt, atņemot ierakstu kolonnā "vīriešu indikators" no ieraksta papildu kolonnā, kurās ir visas vērtības, kas pievienotas funkcijai LINEST .

• Ja kolinearitātes dēļ no modeļa netiek noņemta neviena X kolonna, df vērtība tiek aprēķināta šādi: ja ir k kolonnas ar known_x un konst = TRUE vai tiek izlaista, df = n – k – 1. Ja konst = FALSE, tad df = n - k. Abos gadījumos katra X kolonna, kas tika noņemta, kolinearitātes ietekmē palielina df vērtību par 1.

• Ievadot masīva konstanti (piemēram, known_x) kā argumentu, izmantojiet komatus, lai atdalītu vērtības, kas atrodas tajā pašā rindā, un semikolus, lai atdalītu rindas. Atdalītājrakstzīmes var atšķirties atkarībā no reģionālajiem iestatījumiem.

• Ievērojiet, ka regresijas vienādojuma iepriekš noteiktās y vērtības var nebūt derīgas, ja tās atrodas ārpus vienādojuma aprēķināšanai izmantotā y vērtību diapazona.

• Funkcijas LINEST pamatā esošais algoritms atšķiras no funkcijās SLOPE un INTERCEPT pamatā esošā algoritma. Atšķirība starp šiem algoritmiem var novest pie atšķirīgiem rezultātiem, ja dati ir nenoteikti un kolineāri. Piemēram, ja known_y argumenta datu punkti ir 0 un known_x argumenta datu punkti ir 1:

  • funkcija LINEST atgriež vērtību 0. Funkcijas LINEST algoritms ir paredzēts saprātīgu rezultātu atgriešanai kolineāriem datiem, un šajā gadījumā var atrast vismaz vienu atbildi.
  • SLOPE un INTERCEPT atgriež #DIV/0! Ja norādītā pozīcija atrodas pirms lauka pirmā vienuma vai aiz lauka pēdējā vienuma, formula radīs kļūdu #REF!. SLOPE un INTERCEPT funkciju algoritms ir paredzēts, lai meklētu tikai vienu atbildi, un šajā gadījumā var būt vairākas atbildes.

• Papildus tam, ka funkcija LOGEST tiek lietota, lai aprēķinātu statistiku citiem regresijas tipiem, funkciju LINEST var lietot, lai aprēķinātu citu regresijas tipu diapazonu, ievadot mainīgo x un y funkcijas kā x un y sērijas funkcijai LINEST. Piemēram, šāda formula:
  =LINEST(yvalues, xvalues^COLUMN($A:$C))
  darbojas, kad ir viena kolonna y-vērtībām un viena kolonna x-vērtībām, lai aprēķinātu formas trešās pakāpes (secības 3 polinomu) aproksimāciju:
  y = m1*x + m2*x^2 + m3*x^3 + b
  Lai aprēķinātu citus regresijas tipus, formulu var koriģēt, bet dažos gadījumos ir jākoriģē izvades vērtības un cita statistika.

• F testa vērtība, ko atgriež funkcija LINEST, atšķiras no F testa vērtības, ko atgriež funkcija FTEST. LINEST atgriež statistisko F, bet FTEST atgriež varbūtību.

Piemēri

1. piemērs. Slīpne un Y krustpunkts

Nokopējiet šīs tabulas parauga datus un ielīmējiet tos jaunas Excel darblapas šūnā A1. Lai formulas parādītu rezultātus, atlasiet tos, nospiediet taustiņu F2 un pēc tam Enter. Ja nepieciešams, varat koriģēt kolonnas platumu, lai redzētu visus datus.

Zināmais y	Zināmais x
1	0
9	4
5	2
7	3
Rezultāts (slīpums)	Rezultāts (y veida krustpunkts)
2	1
Formula (masīva formula šūnās A7:B7)
=LINEST(A2:A5;B2:B5;;FALSE)

2. piemērs. Vienkārša lineāra regresija

Nokopējiet šīs tabulas parauga datus un ielīmējiet tos jaunas Excel darblapas šūnā A1. Lai formulas parādītu rezultātus, atlasiet tos, nospiediet taustiņu F2 un pēc tam Enter. Ja nepieciešams, varat koriģēt kolonnas platumu, lai redzētu visus datus.

Mēnesis	Apgrozījums
1	3100 €
2	4500 €
3	4400 €
4	5400 €
5	7500 €
6	8100 €
Formula	Rezultāts
=SUM(LINEST(B1:B6; A1:A6)*{9,1})	11 000 €
	Aprēķina pārdošanas novērtējumu devītajā mēnesī, pamatojoties uz 1.–6. mēneša pārdošanas datiem.

3. piemērs. Salikta lineāra regresija

Nokopējiet šīs tabulas parauga datus un ielīmējiet tos jaunas Excel darblapas šūnā A1. Lai formulas parādītu rezultātus, atlasiet tos, nospiediet taustiņu F2 un pēc tam Enter. Ja nepieciešams, varat koriģēt kolonnas platumu, lai redzētu visus datus.

Platība (x1)	Biroji (x2)	Ieejas (x3)	Vecums (x4)	Noteiktā vērtība (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 €
-234,2371645
13,26801148
0,996747993
459,7536742
1732393319
Formula (dinamiskā masīva formula, ievadīta šūnā A19)
=LINEST(E2:E12;A2:D12;TRUE;TRUE)

4. piemērs. F un r² statistikas izmantošana

Iepriekšējā piemērā noteikšanas koeficients jeb r² ir 0,99675 (skatiet LINEST izvades šūnu A17), kas norādīs uz stingru attiecību starp neatkarīgajiem mainīgajiem un pārdošanas cenu. Šo F statistiku var izmantot, lai noteiktu, vai rezultāti, piemēram, augsta r2 vērtība, ir parādījušies nejauši.

Pieņemsim, ka starp mainīgajiem faktiski nav saistības un uzzīmētais 11 biroju ēku neparastais piemērs liek statistiskajai analīzei rādīt stipru saikni. Varbūtībai, ka kļūdaini tiek pieņemta neesoša attiecība, tiek izmantots termins “Alpha”.

F un df vērtības funkcijas LINEST izvadē var izmantot, lai novērtētu iespējamības, ka lielāka F vērtība parādīsies nejauši. F var salīdzināt ar kritiskajām vērtībām publicētajās F sadales tabulās, vai arī programmā Excel var izmantot FDIST funkciju, lai aprēķinātu lielākas F vērtības iespējamību, kas rodas nejauši. Atbilstošajam F sadalījumam ir v1 un v2 brīvības pakāpes. Ja n ir datu punktu skaits un konst = TRUE vai izlaists, tad v1 = n – df – 1 un v2 = df. (Ja konst = FALSE, tad v1 = n – df un v2 = df.) Funkcija FDIST — ar sintaksi FDIST(F,v1,v2) — atgriezīs lielākas F vērtības nejaušības dēļ. Šajā piemērā df = 6 (šūna B18) un F = 459,753674 (šūna A18).

Pieņemot, ka alfa vērtība ir 0,05, v1 = 11 – 6 – 1 = 4 un v2 = 6, F kritiskais līmenis ir 4,53. Tā kā F = 459,753674 ir daudz augstāks par 4,53, ir ļoti maz ticams, ka tik augsta F vērtība radās nejauši. (Ja alfa = 0,05, hipotēze, ka nav saistības starp known_y un known_x , ir jānoraida, ja F pārsniedz kritisko līmeni 4,53.) Programmā Excel var izmantot funkciju FDIST , lai iegūtu varbūtību, ka tik augsta F vērtība ir parādījusies nejauši. Piemēram, FDIST(459,753674; 4; 6) = 1,37E-7, varbūtība ir ļoti maza. Atrodot tabulā F kritisko līmeni vai izmantojot funkciju FDIST , var secināt, ka regresijas vienādojums ir noderīgs šajā rajonā esošo biroju ēku vērtības noteikšanai. Atcerieties, ka ir ļoti svarīgi izmantot pareizās v1 un v2 vērtības, kas tika aprēķinātas iepriekšējā punktā.

5. piemērs. t statistikas aprēķināšana

Vēl viens hipotēzes tests noteiks, vai katrs slīpuma koeficients ir noderīgs 3. piemērā minēto biroju ēku vērtības noteikšanai. Piemēram, lai pārbaudītu vecuma koeficienta statistisko nozīmību, dalījiet -234,24 (vecuma slīpuma koeficients) ar 13,268 (vecuma koeficientu aptuvenā standarta kļūda šūnā A15). Tālāk norādīta t novērotā vērtība:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

Ja t absolūtā vērtība ir pietiekami augsta, var secināt, ka slīpuma koeficients ir noderīgs 3. piemērā minēto biroju ēkas vērtības noteikšanai. Šajā tabulā parādītas 4 t novērotās absolūtās vērtības.

Aplūkojot tabulu statistikas rokasgrāmatā, redzēsit, ka divpusējs kritiskais t ar 6 brīvības pakāpēm un Alpha = 0,05 ir 2,447. Šo kritisko vērtību var atrast arī, izmantojot programmas Excel funkciju TINV. TINV(0,056) = 2,447. t absolūtās vērtības (17,7) dēļ, kas ir lielāka par 2,447, vecums ir svarīgs mainīgais biroju ēku vērtības noteikšanai. Līdzīgi var pārbaudīt jebkuru citu neatkarīga mainīgā statistisko nozīmību. Turpmāk norādītas t novērotās vērtības katram neatkarīgam mainīgajam.

Mainīgais	t novērotā vērtība
Platība	5.1
Biroju skaits	31.3
Ieeju skaits	4.8
Vecums	17.7

Visām šīm vērtībām absolūtā vērtība ir lielāka par 2.447; tāpēc visi korelācijas vienādojumā izmantotie mainīgie ir noderīgi šajā rajonā esošo biroju ēku vērtības noteikšanai.