LINEST (funkcija LINEST)

Šajā rakstā paskaidrota funkcijas LINEST sintakse un lietošana programmā Microsoft Excel. Saites uz papildinformāciju par diagrammu veidošanu un regresijas analīzi atrodamas sadaļā Skatiet arī.

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

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

    • Ja argumentu zināmie_y diapazons atrodas vienā kolonnā, tad katra zināmo_x kolonna tiek interpretēta kā atsevišķs mainīgais.

    • Ja argumentu zināmie_y diapazonu ietver viena rinda, tad katra zināmo_x rinda tiek interpretēta kā atsevišķs mainīgais.

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

    • Argumentu zināmie_x diapazons var ietvert vienu vai vairākas mainīgo kopas. Ja tiek izmantots tikai viens mainīgais, zināmie_y un zināmie_x var būt jebkuras formas diapazoni, ja vien to izmēri ir vienādi. Ja tiek izmantoti vairāki mainīgie, zināmajiem_y jābūt vektoram (tas ir, diapazonam, kura augstums ir viena rinda vai kura platums ir viena kolonna).

    • Ja zināmie_x tiek izlaisti, tiek pieņemts, ka šī masīva {1,2,3,...} izmērs ir tāds pats kā zināmajiem_y.

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

    • Ja konst ir TRUE vai izlaista, tad b tiek aprēķināta kā parasti.

    • Ja konst ir FALSE, tad b tiek iestatīta vienāda ar 0 un m vērtības tiek pielāgotas y = mx.

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

    • Ja statist ir TRUE, tad LINEST atgriež regresijas papildu statistiku, tādējādi atgrieztais masīvs ir {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}.

    • Ja statist ir FALSE vai izlaista, tad 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 konst 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 par r2 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 statistikas tabulā atrastu kritiskās F vērtības. Salīdziniet tabulā atrastās vērtības ar funkcijas LINEST atgriezto F statistiku, lai noteiktu modeļa drošuma līmeni. Informāciju par df aprēķināšanu skatiet turpmāk šīs tēmas sadaļā Piebildes. 4. piemērā parādīts F un df lietojums.

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.

Cilnes Noformējums sadaļas SmartArt rīki grupa Izveidot grafiku

Piebildes

  • Jebkuru taisni var raksturot ar slīpni un y krustpunktu:

    Slīpne (m):
    Lai atrastu taisnes slīpni, kas bieži pierakstīta kā m, izvēlieties divus taisnes punktus (x1,y1) un (x2,y2); slīpne ir vienāda ar (y2 - y1)/(x2 - x1).

    Y krustpunkts (b):
    Taisnes y krustpunkts, ko parasti pieraksta kā b, ir y vērtība jebkurā 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īpne:
    =INDEX(LINEST(zināmie_y,zināmie_x),1)

    Y krustpunkts:
    =INDEX(LINEST(zināmie_y,zināmie_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:

    Vienādojums

    Vienādojums

    kur x un y ir piemēra vidējais, t.i., x = AVERAGE(zināmie_x) un y = AVERAGE(zināmie_y).

  • Taisnes un līknes pielāgošanas funkcijas LINEST un LOGEST var aprēķināt datiem atbilstošāko taisni vai eksponentlīkni. Ir jāizlemj, kurš no diviem rezultātiem vislabāk saskan ar datiem. Taisni var aprēķināt ar funkciju TREND (zināmie_y,zināmie_x) un eksponentlīkni — ar funkciju GROWTH (zināmie_y, zināmie_x). Bez argumenta jauni_x šīs funkcijas atgriež y vērtību masīvu, ko paredz taisnes vai līknes faktiskie datu punkti. Tad var salīdzināt iepriekšnoteiktās vērtības ar faktiskajām vērtībām. Iespējams izveidot diagrammu abu vizuālam salīdzinājumam.

  • 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 (bez vidējās y vērtības atņemšanas no katras atsevišķas y vērtības). Tad kvadrātu regresijas summu ssreg var atrast šādi: ssreg = sstotal - ssresid. Jo mazāka ir kvadrātu starpības summa, salīdzinot ar kvadrātu kopsummu, jo lielāka ir noteikšanas koeficienta vērtība r2, kas rāda, cik veiksmīgi regresijas analīzes vienādojums izskaidro mainīgo attiecības. r2 ir vienāds ar ssreg/sstotal.

  • Dažos gadījumos vienai vai vairākām X kolonnām (pieņemot, ka Y un X ir kolonnās) var nebūt iepriekš noteiktas papildu vērtības citās X kolonnās. Citiem vārdiem sakot, vienas vai vairāku X kolonnu izslēgšana var novest pie vienādi precīzām iepriekšnoteikto Y vērtībām. Šādā gadījumā regresijas modelī liekās X kolonnas ir jāizlaiž. Šo parādību dēvē par “kolinearitāti”, jo jebkuru lieko X kolonnu var izteikt kā vairāku nelieko X kolonnu summu. Funkcija LINEST meklē kolinearitāti un noņem no regresijas modeļa jebkuras liekas X kolonnas, tikko tādas tiek identificētas. Funkcijas LINEST izvadē noņemtās X kolonnas var atpazīt pēc koeficientiem 0 papildus vērtībai še 0. Ja viena vai vairākas kolonnas ir noņemtas kā liekas, tad tiek ietekmēts df, jo tas ir atkarīgs no iepriekš paredzēta izmantotā faktiskā X kolonnu skaita. Informāciju par df aprēķināšanu skatiet turpmāk 4. piemērā. Ja lieko X kolonnu noņemšanas rezultātā izmainās df, tad tiek ietekmētas arī sey un F vērtības. Praktiski kolinearitātei ir jāparādās samērā reti. Taču ir viens gadījums, kad tā varētu parādīties, proti, ja dažas X kolonnas vērtības 0 un 1 satur tikai kā indikatorus, kā arī ja eksperimenta objekts ir vai nav noteiktas grupas loceklis. Ja const = TRUE vai izlaista, funkcija LINEST ievieto papildu X kolonnu ar visām vērtībām 1, lai modelētu krustpunktu. Ja kolonna ir ar vērtību 1 katram objektam, ja tas ir vīrietis, vai vērtību 0, ja nav, un ir nepieciešama kolonna ar objekta vērtību 1, ja tā ir sieviete, un 0, ja nav, tad šī pēdējā kolonna ir lieka, jo ievadnes tajā var iegūt, atņemot ievadni kolonnā “vīrieša indikators” no ievadnes papildu kolonnā, kurā funkcija LINEST ir pievienojusi visas vērtības 1.

  • Ja kolinearitātes dēļ no modeļa netiek noņemta neviena X kolonna, tad df vērtība tiek aprēķināta šādi: ja ir zināmo_xk kolonnas un konst = TRUE vai izlaista, tad 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.

  • Formulas, kas atgriež masīvus, ir jāievada kā masīvu formulas.

    Piezīme.:  Programmā Excel Online nevar izveidot masīvu formulas.

  • Ievadot masīva konstanti (piemēram, zināmos_x) kā argumentu, izmantojiet komatus vienā rindā esošu vērtību atdalīšanai, bet semikolus — rindu atdalīšanai. 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 argumenta zināmie_y datu punkti ir 0 un argumenta zināmie_x 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.

    • Funkcija SLOPE un INTERCEPT atgriež kļūdu #DIV/0!. Funkcijas SLOPE un INTERCEPT algoritms ir paredzēts tikai vienas atbildes meklēšanai, un šajā gadījumā var būt vairāk kā viena atbilde.

  • Papildus tam, ka funkcija LOGEST tiek lietota, lai aprēķinātu statistiku citiem regresijas tipiem, funkciju LOGEST var lietot, lai aprēķinātu citu regresijas tipu diapazonu, ievadot mainīgo x un y funkcijas kā x un y sērijas funkcijai LOGEST. 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 (masīva formula, ievadīta diapazonā A14:A18)

=LINEST(E2:E12;A2:D12;TRUE;TRUE)

4. piemērs. F un r2 statistikas izmantošana

Iepriekšējā piemērā noteikšanas koeficients jeb r2 ir 0,99675 (skatiet funkcijas 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”.

Vērtības F un df funkcijas LINEST izvadē var izmantot, lai novērtētu varbūtību, ka nejauši parādās augstāka F vērtība. F var salīdzināt ar kritiskajām vērtībām publicētajās F izplatīšanas tabulās, vai lielākas F vērtības nejaušas parādīšanās varbūtības aprēķināšanai var izmantot programmas Excel funkciju FDIST. 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 augstākas F vērtības nejaušas parādīšanas varbūtību. Šajā piemērā df = 6 (šūna B18) un F = 459,753674 (šūna A18).

Pieņemot, ka Alpha vērtība ir 0,05, v1 = 11 – 6 – 1 = 4 un v2 = 6, F kritiskais līmenis ir 4,53. F = 459,753674 ir daudz lielāks nekā 4.53, tātad, ir maz ticams, ka tik liela F vērība ir parādījusies nejauši (ja Alpha = 0.05, tad pieņēmums, ka starp zināmajiem_y un zināmajiem_x nav sakarības, ir noraidāms, ja F pārsniedz kritisko līmeni 4.53). Izmantojot programmas Excel funkciju FDIST, var iegūt varbūtību, ka šāda lieluma F vērtība ir parādījusies nejauši. Piemēram, funkcijai FDIST(459,753674, 4, 6) = 1,37E-7 ir ļoti maza varbūtība. Atrodot F kritisko līmeni tabulā 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 svarīgi izmantot pareizas iepriekšējā rindkopā aprēķinātas v1 un v2 vērtības.

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

Cits pieņēmuma tests noteiks, vai katrs slīpnes 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, izdaliet -234,24 (vecuma slīpnes koeficientu) ar 13,268 (šūnas A15 vecuma koeficientu paredzamo standarta kļūdu). Šāda ir t novērotā vērtība:

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

Ja t absolūtā vērtība ir pietiekami liela, tad var secināt, ka slīpnes koeficients ir noderīgs 3. piemērā minēto biroju ēku 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.

Paplašiniet savas prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×