Regresijska analiza u Microsoft Excelu je najopsežniji vodič za korištenje MS Excela za rješavanje problema regresijske analize u poslovnoj inteligenciji. Konrad Carlberg jasno objašnjava teorijske probleme čije će vam poznavanje pomoći da izbjegnete mnoge pogreške kako sami radite regresijsku analizu, tako i kada ocjenjujete rezultate analiza koje provode drugi ljudi. Sav materijal, od jednostavnih korelacija i t-testova do višestruke analize kovarijance, temelji se na stvarnim primjerima i popraćen je detaljnim opisom relevantnih postupaka korak po korak.

Ova knjiga raspravlja o detaljima Excelovih regresijskih funkcija, ispituje implikacije svake opcije i svakog argumenta te objašnjava kako pouzdano primijeniti tehnike regresije u područjima u rasponu od medicinskog istraživanja do financijske analize.

Konrad Carlberg. Regresijska analiza u Microsoft Excelu. - M.: Dijalektika, 2017. - 400 str.

Preuzmite bilješku u ili formatu, primjere u formatu

Poglavlje 1. Procjena varijabilnosti podataka

Statističari imaju na raspolaganju mnogo pokazatelja varijabilnosti (varijabilnosti). Jedan od njih je zbroj kvadrata odstupanja pojedinačnih vrijednosti od srednje vrijednosti. Excel za to koristi funkciju SQUADROT(). Ali češće se koristi disperzija. Varijanca je srednja vrijednost kvadrata odstupanja. Varijanca je neosjetljiva na broj vrijednosti u skupu podataka koji se proučava (dok se zbroj kvadrata odstupanja povećava s brojem mjerenja).

Excel nudi dvije funkcije koje vraćaju varijancu: VARP.D() i VARP.V():

• Koristite funkciju VAR.G() ako vrijednosti koje treba obraditi čine populaciju. Odnosno, vrijednosti sadržane u rasponu su jedine vrijednosti koje vas zanimaju.
• Koristite funkciju VAR.V() ako vrijednosti koje treba obraditi čine uzorak iz veće populacije. Pretpostavlja da postoje dodatne vrijednosti čiju varijansu također možete procijeniti.

Ako se vrijednost kao što je srednja vrijednost ili koeficijent korelacije izračunava na temelju opće populacije, tada se naziva parametrom. Slična vrijednost izračunata na temelju uzorka naziva se statistika. Odbrojavanje odstupanja od prosjeka u ovom skupu dobit ćete zbroj kvadrata odstupanja manjeg iznosa nego da ih brojite od bilo koje druge vrijednosti. Slična izjava vrijedi i za disperziju.

Što je veličina uzorka veća, to je izračunata vrijednost statistike točnija. Ali ne postoji uzorak manji od veličine populacije za koji možete biti sigurni da vrijednost statistike odgovara vrijednosti parametra.

Recimo da imate skup od 100 visina čija se srednja vrijednost razlikuje od srednje vrijednosti stanovništva, koliko god razlika bila mala. Kada izračunate varijancu za uzorak, dobit ćete neku vrijednost, recimo 4. Ova vrijednost je manja od bilo koje druge vrijednosti koja se može dobiti izračunavanjem odstupanja svake od 100 vrijednosti rasta od bilo koje vrijednosti osim uzorka prosjek, uključujući pravu srednju vrijednost za opću populaciju. Stoga će se izračunata varijanca razlikovati, i to u manjoj mjeri, od varijance koju biste dobili da ste nekako znali i koristili ne srednju vrijednost uzorka, već parametar populacije.

Srednji zbroj kvadrata određen za uzorak daje nižu procjenu varijance populacije. Ovako izračunata varijanca naziva se raseljeni evaluacija. Ispada da je za uklanjanje pristranosti i dobivanje nepristrane procjene dovoljno podijeliti zbroj kvadrata odstupanja ne s n, gdje n je veličina uzorka, i n - 1.

Vrijednost n - 1 naziva se broj (broj) stupnjeva slobode. Postoje različiti načini za izračunavanje ove vrijednosti, iako svi uključuju ili oduzimanje nekog broja od veličine uzorka ili prebrojavanje broja kategorija u koje promatranja spadaju.

Bit razlike između funkcija DISP.G() i DISP.V() je sljedeća:

• U funkciji VARI.G() zbroj kvadrata podijeljen je s brojem opažanja i stoga predstavlja pristranu procjenu varijance, pravu sredinu.
• U funkciji VAR.B() zbroj kvadrata se dijeli s brojem opažanja minus 1, tj. brojem stupnjeva slobode, što daje točniju, nepristranu procjenu varijance populacije iz koje je uzorak izvučen.

standardna devijacija (engleski) standardna devijacija, SD) je kvadratni korijen varijance:

Kvadratura odstupanja prevodi mjernu ljestvicu u drugu metriku, a to je kvadrat izvorne: metri - u kvadratne metre, dolari - u kvadratne dolare, itd. Standardna devijacija je kvadratni korijen varijance i tako nas vraća na izvorne jedinice. Što je zgodnije.

Često je potrebno izračunati standardnu ​​devijaciju nakon što su podaci podvrgnuti nekoj manipulaciji. I premda su u tim slučajevima rezultati nedvojbeno standardne devijacije, obično se nazivaju standardne greške. Postoji nekoliko tipova standardnih pogrešaka, uključujući standardnu ​​pogrešku mjerenja, standardnu ​​pogrešku proporcije i standardnu ​​pogrešku srednje vrijednosti.

Recimo da prikupljate podatke o visini 25 nasumično odabranih odraslih muškaraca u svakoj od 50 država. Zatim izračunate prosječnu visinu odraslih muškaraca u svakoj državi. Rezultirajućih 50 srednjih vrijednosti mogu se zauzvrat smatrati opažanjima. Iz ovoga možete izračunati njihovu standardnu ​​devijaciju, koja je standardna pogreška srednje vrijednosti. Riža. 1. omogućuje vam da usporedite distribuciju 1250 izvornih pojedinačnih vrijednosti (podaci o visini 25 muškaraca u svakoj od 50 država) s distribucijom prosječnih vrijednosti od 50 država. Formula za procjenu standardne pogreške srednje vrijednosti (tj. standardne devijacije srednjih vrijednosti, a ne pojedinačnih opažanja):

gdje je standardna pogreška srednje vrijednosti; s je standardna devijacija izvornih opažanja; n je broj opažanja u uzorku.

Riža. 1. Varijacija u prosječnim vrijednostima od države do države je mnogo manja od varijacije pojedinačnih opažanja

U statistici postoji konvencija o upotrebi grčkih i latinskih slova za označavanje statističkih veličina. Uobičajeno je da se parametri opće populacije označavaju grčkim slovima, a statistike uzorka latiničnim slovima. Stoga, ako govorimo o standardnoj devijaciji populacije, zapisujemo je kao σ; ako se uzme u obzir standardna devijacija uzorka, tada koristimo oznaku s. Što se tiče simbola za prosjeke, oni se međusobno ne slažu tako dobro. Srednja vrijednost stanovništva označava se grčkim slovom μ. Međutim, simbol X̅ tradicionalno se koristi za predstavljanje srednje vrijednosti uzorka.

z-score izražava položaj opažanja u distribuciji u jedinicama standardne devijacije. Na primjer, z = 1,5 znači da je promatranje udaljeno 1,5 standardnih devijacija od srednje vrijednosti, prema višim vrijednostima. Termin z-score koristi se za pojedinačne evaluacije, t.j. za mjerenja koja se pripisuju pojedinim elementima uzorka. Za takve statistike (npr. državni prosjek) koristi se pojam. z-vrijednost:

gdje je X̅ srednja vrijednost uzorka, μ je srednja vrijednost opće populacije, standardna pogreška srednje vrijednosti skupa uzoraka:

gdje je σ standardna pogreška opće populacije (pojedinačna mjerenja), n je veličina uzorka.

Pretpostavimo da ste instruktor golfa. Već duže vrijeme možete mjeriti domet udarca i znate da je prosjek 205 jardi, a standardna devijacija 36 jardi. Ponuđen vam je novi klub, tvrdeći da će povećati vaš domet za 10 jardi. Zamolite svakog od sljedećeg 81 posjetitelja kluba da se isproba s novim klubom i zabilježi njihov raspon. Pokazalo se da je prosječni domet udarca s novom palicom 215 jardi. Kolika je vjerojatnost da je razlika od 10 jardi (215 - 205) posljedica isključivo pogreške uzorkovanja? Ili drugačije rečeno, kolika je vjerojatnost da, u većem testu, novi klub neće pokazati povećanje dometa u odnosu na trenutni dugoročni prosjek od 205 jardi?

To možemo testirati generiranjem z-vrijednosti. Standardna pogreška srednje vrijednosti:

Tada z-vrijednost:

Moramo pronaći vjerojatnost da će srednja vrijednost uzorka biti 2,5σ udaljena od srednje vrijednosti populacije. Ako je vjerojatnost mala, onda razlike nisu posljedica slučajnosti, već kvalitete novog kluba. U Excelu ne postoji gotova funkcija za određivanje vjerojatnosti z-scorea. Međutim, možete koristiti formulu =1-NORM.ST.DIST(z-vrijednost, TRUE), gdje NORM.ST.DIST() vraća područje ispod normalne krivulje lijevo od z-vrijednosti (slika 2) .

Riža. 2. Funkcija NORM.S.DIST() vraća područje ispod krivulje lijevo od z-vrijednosti; Da biste povećali sliku, kliknite desnom tipkom miša na nju i odaberite Otvorite sliku u novoj kartici

Drugi argument funkcije NORM.S.DIST() može imati dvije vrijednosti: TRUE - funkcija vraća područje ispod krivulje lijevo od točke specificirane prvim argumentom; FALSE - Funkcija vraća visinu krivulje u točki zadanoj prvim argumentom.

Ako srednja vrijednost (μ) i standardna devijacija (σ) populacije nisu poznati, koristi se t-vrijednost (vidi ). Strukture z- i t-skora razlikuju se po tome što se standardna devijacija s dobivena iz rezultata uzorka koristi za pronalaženje t-vrijednosti, a ne poznate vrijednosti parametra populacije σ. Normalna krivulja ima jedan oblik, a oblik raspodjele t-vrijednosti varira ovisno o broju stupnjeva slobode df (od engleskog. stupnjevi slobode) uzorka koji predstavlja. Broj stupnjeva slobode uzorka je n - 1, gdje n- veličina uzorka (slika 3).

Riža. 3. Oblik t-raspodjela koje nastaju kada je parametar σ nepoznat razlikuje se od oblika normalne distribucije

Excel ima dvije funkcije za t-distribuciju, koja se naziva i Studentova t-distribucija: STUDENT.DIST() vraća područje ispod krivulje lijevo od zadane t-vrijednosti, a STUDENT.DIST.Tx() desno.

Poglavlje 2. Korelacija

Korelacija je mjera ovisnosti između elemenata skupa uređenih parova. Korelacija je karakterizirana Pearsonovi koeficijenti korelacije– r. Koeficijent može imati vrijednosti u rasponu od -1,0 do +1,0.

gdje S x I Sy su standardne devijacije varijabli x I Y, Sxy– kovarijanca:

U ovoj formuli kovarijanca je podijeljena standardnim devijacijama varijabli x I Y, čime se uklanjaju efekti skaliranja koji se odnose na jedinicu iz kovarijance. Excel koristi funkciju CORREL(). Naziv ove funkcije ne sadrži kvalificirajuće elemente G i C, koji se koriste u nazivima funkcija kao što su STDEV(), VARV() ili COVARIANCE(). Iako koeficijent korelacije uzorka daje pristranu procjenu, razlog za pristranost je drugačiji nego u slučaju varijance ili standardne devijacije.

Ovisno o veličini općeg koeficijenta korelacije (često se označava grčkim slovom ρ ), koeficijent korelacije r daje pristranu procjenu, s učinkom pristranosti koji raste sa smanjenjem veličine uzorka. Ipak, ne pokušavamo ispraviti ovu pristranost na isti način kao što smo, na primjer, to učinili prilikom izračuna standardne devijacije, kada smo u odgovarajuću formulu zamijenili ne broj opažanja, već broj stupnjeva slobode. U stvarnosti, broj promatranja korištenih za izračunavanje kovarijance nema utjecaja na veličinu.

Standardni koeficijent korelacije dizajniran je za korištenje s varijablama koje su međusobno povezane linearnim odnosom. Prisutnost nelinearnosti i/ili pogrešaka u podacima (odbojnosti) dovode do pogrešnog izračuna koeficijenta korelacije. Za dijagnosticiranje problema s podacima preporučuju se dijagrami raspršenja. Ovo je jedina vrsta grafikona u Excelu koja horizontalnu i okomitu os tretira kao osi vrijednosti. Linijski grafikon, s druge strane, definira jedan od stupaca kao os kategorije, što iskrivljuje sliku podataka (slika 4.).

Riža. 4. Čini se da su regresijske linije iste, ali usporedite njihove jednadžbe jedna s drugom

Opažanja korištena za izradu linijskog grafikona jednako su udaljena duž horizontalne osi. Oznake podjela duž ove osi samo su oznake, a ne numeričke vrijednosti.

Iako korelacija često znači da postoji uzročna veza, ne može se koristiti kao dokaz da postoji. Statistika se ne koristi za dokazivanje je li teorija istinita ili netočna. Kako bi se isključila konkurentna objašnjenja rezultata promatranja stavite planirane eksperimente. Statistika se također koristi za sažimanje informacija prikupljenih tijekom takvih eksperimenata i za kvantificiranje vjerojatnosti da odluka može biti pogrešna s obzirom na bazu dokaza.

Poglavlje 3 Jednostavna regresija

Ako su dvije varijable povezane, tako da je vrijednost koeficijenta korelacije veća od, recimo, 0,5, tada je moguće (s određenom točnošću) predvidjeti nepoznatu vrijednost jedne varijable iz poznate vrijednosti druge. Za dobivanje predviđenih vrijednosti cijene, na temelju podataka datih na sl. 5, možete koristiti bilo koji od nekoliko mogućih načina, ali gotovo sigurno nećete koristiti onaj prikazan na sl. 5. Ipak, trebali biste ga pročitati, jer vam nijedan drugi način ne dopušta tako jasno pokazati odnos između korelacije i predviđanja kao ovaj. Na sl. 5, u rasponu B2:C12, slučajni je uzorak od deset kuća i daje podatke o površini svake kuće (u četvornim stopama) i njezinoj prodajnoj cijeni.

Riža. 5. Predviđanja prodajnih cijena čine ravnu liniju

Pronađite srednje vrijednosti, standardne devijacije i koeficijent korelacije (raspon A14:C18). Izračunajte z-rezultate površine (E2:E12). Na primjer, ćelija E3 sadrži formulu: =(B3-$B$14)/$B$15. Izračunajte z-rezultate prognozirane cijene (F2:F12). Na primjer, ćelija F3 sadrži formulu: =E3*$B$18. Pretvorite z-rezultate u cijene u dolarima (H2:H12). U ćeliji HZ, formula je: =F3*$C$15+$C$14.

Imajte na umu da se predviđena vrijednost uvijek pomakne prema srednjoj vrijednosti od 0. Što je koeficijent korelacije bliži nuli, to je predviđeni z-score bliži nuli. U našem primjeru koeficijent korelacije između površine i prodajne cijene je 0,67, a prognozirana cijena je 1,0*0,67, t.j. 0,67. To odgovara višku vrijednosti u odnosu na prosječnu vrijednost, jednakom dvije trećine standardne devijacije. Kada bi koeficijent korelacije bio jednak 0,5, tada bi prognozirana cijena bila 1,0 * 0,5, tj. 0.5. To odgovara višku vrijednosti iznad prosječne vrijednosti, jednakom samo polovici standardne devijacije. Kad god se vrijednost koeficijenta korelacije razlikuje od idealne, t.j. veći od -1,0 i manji od 1,0, procjena prediktorske varijable trebala bi biti bliža svojoj srednjoj vrijednosti od procjene prediktorske (nezavisne) varijable njezinoj vlastitoj. Taj se fenomen naziva regresija na srednju vrijednost ili jednostavno regresija.

U Excelu postoji nekoliko funkcija za određivanje koeficijenata jednadžbe regresijske linije (u Excelu se to naziva linija trenda) y=kx + b. Za utvrđivanje k služi funkciji

=SLOPE(poznate_y-vrijednosti; poznate_x-vrijednosti)

Ovdje na je predviđena varijabla, i x je nezavisna varijabla. Morate strogo slijediti ovaj redoslijed varijabli. Nagib regresijske linije, koeficijent korelacije, standardne devijacije varijabli i kovarijansa su usko povezani (slika 6.). Funkcija INTERCEPT() vraća vrijednost odrezanu linijom regresije na okomitoj osi:

= INTERCUT(poznate_y-vrijednosti; poznate_x-vrijednosti)

Riža. 6. Omjer između standardnih devijacija pretvara kovarijaciju u koeficijent korelacije i nagib regresijske linije

Imajte na umu da broj vrijednosti x i y danih funkcijama SLOPE() i INTERCEPT() kao argumentima mora biti isti.

U regresijskoj analizi koristi se još jedan važan pokazatelj - R 2 (R-kvadrat), odnosno koeficijent determinacije. Određuje kakav doprinos ukupnoj varijabilnosti podataka daje odnos između x I na. Excel za to ima funkciju QVPIRSON(), koja uzima potpuno iste argumente kao i funkcija CORREL().

Kaže se da dvije varijable s koeficijentom korelacije različitom od nule objašnjavaju varijancu ili imaju objašnjenje varijance. Obično se objašnjena varijanca izražava u postocima. Tako R 2 = 0,81 znači da je objašnjeno 81% varijance (raspršenosti) dviju varijabli. Preostalih 19% posljedica je slučajnih fluktuacija.

Excel ima funkciju TREND koja pojednostavljuje izračune. TREND() funkcija:

• uzima poznate vrijednosti koje dajete x i poznate vrijednosti na;
• izračunava nagib regresijske linije i konstantu (segment);
• vraća prediktivne vrijednosti na određena primjenom regresijske jednadžbe na poznate vrijednosti x(slika 7).

Funkcija TREND() je funkcija niza (ako se prije niste susreli s takvim funkcijama, preporučam je).

Riža. 7. Korištenje funkcije TREND() omogućuje vam da ubrzate i pojednostavite izračune u usporedbi s korištenjem para funkcija SLOPE() i INTERCEPT()

Za unos funkcije TREND() kao formule polja u ćelije G3:G12, odaberite raspon G3:G12, unesite formulu TREND (SZ:S12;VZ:B12), pritisnite i držite tipke i tek tada pritisnite tipku . Imajte na umu da je formula zatvorena u vitičaste zagrade: ( i ). Ovako vam Excel govori da se ova formula percipira kao formula polja. Nemojte sami unositi zagrade: ako ih sami pokušate unijeti kao dio formule, Excel će vaš unos tretirati kao normalan tekstualni niz.

Funkcija TREND() ima još dva argumenta: nove_vrijednosti_x I konst. Prvi vam omogućuje da izgradite prognozu za budućnost, a drugi može prisiliti regresijsku liniju da prođe kroz ishodište (vrijednost TRUE govori Excelu da koristi izračunatu konstantu, FALSE vrijednost - konstanta = 0). Excel vam omogućuje da nacrtate liniju regresije na grafikonu tako da prolazi kroz ishodište. Započnite crtanjem dijagrama raspršenja, a zatim desnom tipkom miša kliknite jednu od oznaka serije podataka. Odaberite stavku u kontekstnom izborniku koji se otvori. Dodajte liniju trenda; odaberite opciju Linearna; ako je potrebno, pomaknite se prema dolje po ploči, potvrdite okvir Postavite raskrižje; provjerite je li pridruženi tekstni okvir postavljen na 0.0.

Ako imate tri varijable i želite odrediti korelaciju između dvije od njih, isključujući utjecaj treće, možete koristiti djelomična korelacija. Pretpostavimo da vas zanima odnos između postotka stanovnika grada koji su završili fakultet i broja knjiga u gradskim knjižnicama. Prikupili ste podatke za 50 gradova, ali... Problem je što oba ova parametra mogu ovisiti o dobrobiti stanovnika pojedinog grada. Naravno, vrlo je teško pronaći drugih 50 gradova koje karakterizira potpuno ista razina blagostanja stanovnika.

Primjenom statističkih metoda za uklanjanje utjecaja bogatstva i na knjižničnu potporu i na fakultetsko obrazovanje, mogli biste bolje kvantificirati odnos između varijabli koje vas zanimaju, odnosno broja knjiga i broja diplomiranih studenata. Ova uvjetna korelacija između dvije varijable, kada su vrijednosti drugih varijabli fiksne, naziva se djelomična korelacija. Jedan od načina za izračunavanje je korištenje jednadžbe:

Gdje rCB . W- koeficijent korelacije između varijabli College (Follege) i Books (Books) s isključenim utjecajem (fiksna vrijednost) varijable Wealth (Wealth); rCB- koeficijent korelacije između varijabli Fakultet i Knjige; rCW- koeficijent korelacije između varijabli College i Welfare; rb.w.- koeficijent korelacije između varijabli Knjige i Dobrobit.

S druge strane, parcijalna korelacija može se izračunati na temelju rezidualne analize, tj. razlike između predviđenih vrijednosti i njihovih povezanih stvarnih opažanja (obje metode su prikazane na slici 8).

Riža. 8. Djelomična korelacija kao rezidualna korelacija

Da biste pojednostavili izračun matrice koeficijenata korelacije (B16: E19), koristite paket za analizu Excel (izbornik Podaci –> Analiza –> Analiza podataka). Prema zadanim postavkama, ovaj paket nije aktivan u Excelu. Da biste ga instalirali, prođite kroz izbornik Datoteka –> Parametri –> dodaci. Na dnu prozora koji se otvara Parametriexcel pronađite polje Kontrolirati, Odaberi dodaciexcel, kliknite Ići. Označite okvir pored dodatka Paket analize. Kliknite A Analiza podataka, odaberite opciju Poveznica. Navedite $B$2:$D$13 kao interval unosa, potvrdite okvir Oznake u prvom redu, navedite $B$16:$E$19 kao izlazni interval.

Druga mogućnost je definiranje poludjelomične korelacije. Na primjer, istražujete utjecaj visine i dobi na težinu. Dakle, imate dvije prediktorske varijable, visinu i dob, i jednu prediktorsku varijablu, težinu. Želite isključiti utjecaj jedne varijable prediktora na drugu, ali ne i na varijablu prediktora:

gdje je H - Height (Height), W - Weight (Weight), A - Age (Age); Indeks poludjelomičnog koeficijenta korelacije koristi zagrade kako bi označio koja se varijabla eliminira i iz koje varijable. U ovom slučaju, oznaka W(H.A) označava da se učinak varijable Dob uklanja iz varijable Visina, ali ne i iz varijable Težina.

Moglo bi se steći dojam da tema o kojoj se raspravlja nije od bitne važnosti. Uostalom, najvažnije je koliko točno funkcionira opća regresijska jednadžba, dok se čini da je problem relativnih doprinosa pojedinih varijabli ukupnoj objašnjenoj varijansi sekundaran. Međutim, to nije slučaj. Čim počnete razmišljati hoćete li ili ne koristiti bilo koju varijablu u jednadžbi višestruke regresije, pitanje postaje važno. Može utjecati na ocjenu ispravnosti izbora modela za analizu.

Poglavlje 4. Funkcija LINEST().

Funkcija LINEST() vraća 10 statistika regresijske analize. Funkcija LINEST() je funkcija niza. Za unos odaberite raspon koji sadrži pet redaka i dva stupca, upišite formulu i pritisnite (slika 9):

LINEST(B2:B21,A2:A21,TRUE,TRUE)

Riža. 9. Funkcija LINEST(): a) odaberite raspon D2:E6, b) unesite formulu kao što je prikazano na traci formule, c) kliknite

Funkcija LINEST() vraća:

• koeficijent regresije (ili nagib, ćelija D2);
• segment (ili konstanta, stanica E3);
• standardne pogreške koeficijenta regresije i konstanti (raspon D3:E3);
• koeficijent determinacije R 2 za regresiju (ćelija D4);
• standardna pogreška procjene (ćelija E4);
• F-test za potpunu regresiju (stanica D5);
• broj stupnjeva slobode za preostali zbroj kvadrata (ćelija E5);
• regresijski zbroj kvadrata (ćelija D6);
• rezidualni zbroj kvadrata (ćelija E6).

Pogledajmo svaku od ovih statistika i njihove interakcije.

standardna pogreška u našem slučaju, ovo je standardna devijacija izračunata za greške uzorkovanja. Odnosno, radi se o situaciji u kojoj opća populacija ima jednu statistiku, a uzorak drugu. Dijeljenje koeficijenta regresije sa standardnom pogreškom daje vrijednost od 2,092/0,818 = 2,559. Drugim riječima, koeficijent regresije 2,092 udaljen je dvije i pol standardne pogreške od nule.

Ako je koeficijent regresije nula, tada je najbolja procjena predviđene varijable njezina srednja vrijednost. Dvije i pol standardne pogreške je prilično velik broj i možete sa sigurnošću pretpostaviti da koeficijent regresije za populaciju ima vrijednost različitu od nule.

Možete odrediti vjerojatnost dobivanja koeficijenta regresije uzorka od 2,092 ako je njegova stvarna vrijednost u populaciji 0,0 pomoću funkcije

STUDENT.DIST.PH (t-test = 2.559; broj stupnjeva slobode = 18)

Općenito, broj stupnjeva slobode = n - k - 1, gdje je n broj opažanja, a k broj prediktorskih varijabli.

Ova formula vraća vrijednost od 0,00987 ili zaokruženu na 1%. To nam govori sljedeće: ako je koeficijent regresije za populaciju 0%, onda je vjerojatnost dobivanja uzorka od 20 osoba za koje je izračunata vrijednost regresijskog koeficijenta 2,092 skromnih 1%.

F-test (ćelija D5 na slici 9) obavlja istu funkciju u odnosu na punu regresiju kao i t-test u odnosu na koeficijent jednostavne parne regresije. F-test se koristi za testiranje je li koeficijent determinacije R 2 za regresiju doista dovoljno velik da odbaci hipotezu da ima vrijednost od 0,0 u populaciji, što ukazuje na odsutnost varijance objašnjene prediktorskom i prediktorskom varijablom . Kada postoji samo jedna prediktorska varijabla, F-test je točno jednak kvadratu t-testa.

Do sada smo razmatrali intervalne varijable. Ako imate varijable koje mogu poprimiti više vrijednosti koje su jednostavna imena, kao što su muškarac i žena ili gmaz, vodozemac i riba, predstavite ih kao numerički kod. Takve se varijable nazivaju nominalnim.

R2 statistika kvantificira udio objašnjene varijance.

Standardna pogreška procjene. Na sl. U tablici 4.9 prikazane su predviđene vrijednosti varijable Weight, dobivene na temelju njezinog odnosa s varijablom Height. Raspon E2:E21 sadrži vrijednosti reziduala za varijablu Težina. Točnije, ovi ostaci se nazivaju pogreškama - otuda slijedi izraz standardna pogreška procjene.

Riža. 10. I R 2 i standardna pogreška procjene izražavaju točnost predviđanja dobivenih pomoću regresije

Što je manja standardna pogreška procjene, to je regresijska jednadžba točnija i što bliže očekujete da se bilo kakvo predviđanje iz jednadžbe podudara sa stvarnim opažanjem. Standardna pogreška procjene daje način za kvantificiranje ovih očekivanja. Težina 95% ljudi određene visine bit će u rasponu:

(visina * 2,092 - 3,591) ± 2,092 * 21,118

F-statistika je omjer međugrupne varijance prema unutargrupnoj varijansi. Ovaj naziv uveo je statističar George Snedecor u čast Sir, koji je razvio analizu varijance (ANOVA, Analysis of Variance) početkom 20. stoljeća.

Koeficijent determinacije R 2 izražava udio ukupnog zbroja kvadrata povezanih s regresijom. Vrijednost (1 - R 2) izražava udio ukupnog zbroja kvadrata povezanih s ostacima - pogreškama predviđanja. F-test se može dobiti pomoću funkcije LINEST (ćelija F5 na slici 11), korištenjem zbroja kvadrata (raspon G10:J11), korištenjem frakcija varijance (raspon G14:J15). Formule se mogu proučavati u priloženoj Excel datoteci.

Riža. 11. Izračun F-kriterija

Pri korištenju nominalnih varijabli koristi se lažno kodiranje (slika 12). Za kodiranje vrijednosti prikladno je koristiti vrijednosti 0 i 1. Vjerojatnost F izračunava se pomoću funkcije:

F.DIST.PH(K2;I2;I3)

Ovdje funkcija F.DIST.RT() vraća vjerojatnost dobivanja F-testa nakon središnje F-distribucije (slika 13) za dva skupa podataka sa stupnjevima slobode danim u ćelijama I2 i I3, vrijednost što je isto kao vrijednost navedena u ćeliji K2.

Riža. 12. Regresijska analiza korištenjem lažnih varijabli

Riža. 13. Centralna F-distribucija za λ = 0

Poglavlje 5 Višestruka regresija

Kada prijeđete s jednostavne parne regresije s jednom varijablom prediktora na višestruku regresiju, dodajete jednu ili više prediktorskih varijabli. Pohranite vrijednosti varijable prediktora u susjedne stupce, kao što su stupci A i B za dva prediktora ili A, B i C za tri prediktora. Prije unosa formule koja uključuje funkciju LINEST(), odaberite pet redaka i onoliko stupaca koliko ima prediktorskih varijabli, plus još jedan za konstantu. U slučaju regresije s dvije prediktorske varijable, može se koristiti sljedeća struktura:

LINEST(A2: A41; B2: C41;; TRUE)

Slično, u slučaju tri varijable:

LINEST(A2:A61;B2:D61;;TRUE)

Recimo da želite proučiti mogući učinak dobi i prehrane na razine LDL-a, lipoproteina niske gustoće za koje se smatra da su odgovorni za stvaranje aterosklerotskih plakova koji uzrokuju aterotrombozu (slika 14.).

Riža. 14. Višestruka regresija

R 2 višestruke regresije (prikazano u ćeliji F13) veći je od R 2 bilo koje jednostavne regresije (E4, H4). Višestruka regresija koristi više prediktorskih varijabli u isto vrijeme. U ovom slučaju, R2 se gotovo uvijek povećava.

Za bilo koju jednostavnu linearnu regresijsku jednadžbu s jednom prediktorskom varijablom, uvijek će postojati savršena korelacija između vrijednosti prediktora i vrijednosti prediktorske varijable, budući da se u takvoj jednadžbi vrijednosti prediktora množe s jednom konstantom i svakom proizvodu dodaje se još jedna konstanta. Ovaj učinak nije sačuvan u višestrukoj regresiji.

Prikaz rezultata koje vraća LINEST() za višestruku regresiju (slika 15). Koeficijenti regresije prikazuju se kao dio rezultata koje vraća LINEST() obrnutim redoslijedom varijabli(G–H–I odgovara C–B–A).

Riža. 15. Koeficijenti i njihove standardne pogreške prikazani su obrnutim redoslijedom na radnom listu.

Načela i postupci koji se koriste u regresijskoj analizi s jednom varijablom prediktora lako se prilagođavaju za više prediktorskih varijabli. Pokazalo se da velik dio ove prilagodbe ovisi o eliminiranju utjecaja prediktorskih varijabli jedne na drugu. Potonje je povezano s privatnim i poluprivatnim korelacijama (slika 16.).

Riža. 16. Višestruka regresija može se izraziti kroz uparenu regresiju ostataka (pogledajte formule u Excel datoteci)

U Excelu postoje funkcije koje pružaju informacije o t- i F-distribucijama. Funkcije čiji nazivi uključuju dio DIST, kao što su STUDENT.DIST() i F.DIST(), uzimaju t- ili F-test kao argument i vraćaju vjerojatnost promatranja navedene vrijednosti. Funkcije čija imena uključuju OBR dio, kao što su STUDENT.INV() i F.INV(), uzimaju vrijednost vjerojatnosti kao argument i vraćaju vrijednost kriterija koja odgovara navedenoj vjerojatnosti.

Budući da tražimo kritične vrijednosti t-distribucije koja odsiječe rubove njegovih repnih regija, prosljeđujemo 5% kao argument jednoj od funkcija STUDENT.INV(), koja vraća vrijednost koja odgovara ovoj vjerojatnosti (Sl. 17, 18).

Riža. 17. Dvostrani t-test

Riža. 18. Jednostrani t-test

Uspostavljanjem pravila odlučivanja u slučaju jednostrane alfa regije, povećavate statističku snagu testa. Ako ste, kada započnete eksperiment, sigurni da imate sve razloge za očekivati ​​pozitivan (ili negativan) koeficijent regresije, tada biste trebali provesti jednostrani test. U tom će slučaju vjerojatnost da ćete donijeti ispravnu odluku, odbacivši hipotezu o nultom koeficijentu regresije u populaciji, biti veća.

Statističari radije koriste taj izraz usmjereni test umjesto termina test s jednim repom i termin neusmjereni test umjesto termina dvostrani test. Izrazi usmjereni i neusmjereni su poželjniji jer naglašavaju vrstu hipoteze, a ne prirodu repova distribucije.

Pristup procjeni utjecaja prediktora na temelju usporedbe modela. Na sl. 19 prikazuje rezultate regresijske analize koja testira doprinos varijable prehrane regresijskoj jednadžbi.

Riža. 19. Usporedba dva modela provjeravanjem razlika u njihovim rezultatima

Rezultati LINEST() (raspon H2:K6) povezani su s onim što ja nazivam punim modelom, koji regresira LDL varijablu na prehrani, dobi i HDL-u. U rasponu H9:J13, izračuni su prikazani bez uzimanja u obzir prediktorske varijable Dijeta. Ja to zovem limitiranim modelom. U punom modelu, 49,2% varijance u LDL ovisnoj varijable objašnjava se prediktorskim varijablama. U ograničenom modelu, samo 30,8% LDL-a objašnjava se dobi i HDL-om. Gubitak R 2 zbog isključenja varijable Dijeta iz modela je 0,183. U rasponu G15:L17 napravljeni su izračuni koji pokazuju da je samo s vjerojatnošću od 0,0288 utjecaj varijable Dijeta slučajan. U preostalih 97,1% dijeta utječe na LDL.

Poglavlje 6. Pretpostavke i upozorenja u vezi s regresijskom analizom

Pojam "pretpostavka" nije striktno definiran, a način na koji se koristi sugerira da ako pretpostavka nije ispunjena, onda su rezultati cijele analize u najmanju ruku upitni ili eventualno nevažeći. Zapravo, to nije tako, iako zasigurno postoje slučajevi u kojima kršenje pretpostavke iz temelja mijenja sliku. Glavne pretpostavke su: a) reziduali varijable Y normalno su raspoređeni u bilo kojoj točki u X duž regresijske linije; b) Y vrijednosti su linearno ovisne o X vrijednostima; c) varijanca reziduala je približno ista u svakoj točki X; d) nema veze između ostataka.

Ako pretpostavke ne igraju značajnu ulogu, statističari govore o robusnosti analize u odnosu na kršenje pretpostavke. Konkretno, kada koristite regresiju za testiranje razlika između srednjih vrijednosti grupe, pretpostavka da su vrijednosti Y - a time i reziduali - normalno raspoređeni, ne igra značajnu ulogu: testovi su robusni s obzirom na kršenje pretpostavka normalnosti. Važno je analizirati podatke pomoću grafikona. Na primjer, uključeno u dodatak Analiza podataka alat Regresija.

Ako podaci ne odgovaraju pretpostavkama linearne regresije, na raspolaganju su vam i drugi nelinearni pristupi. Jedna od njih je logistička regresija (slika 20). U blizini gornje i donje granice prediktorske varijable, linearna regresija rezultira nerealnim predviđanjima.

Riža. 20. Logistička regresija

Na sl. Na slici 6.8 prikazani su rezultati dviju metoda analize podataka kojima je cilj istražiti odnos između godišnjeg prihoda i vjerojatnosti kupnje kuće. Očito, vjerojatnost kupnje će se povećati s povećanjem prihoda. Grafikoni olakšavaju uočavanje razlika između rezultata koji predviđaju vjerojatnost kupnje kuće pomoću linearne regresije i rezultata koje biste mogli dobiti koristeći drugačiji pristup.

Statističkim jezikom rečeno, odbacivanje nulte hipoteze kada je ona zapravo istinita naziva se greškom tipa I.

U dodatku Analiza podataka nudi zgodan alat za generiranje slučajnih brojeva, dopuštajući korisniku da odredi željeni oblik distribucije (na primjer, normalan, binom ili Poisson), kao i srednju vrijednost i standardnu ​​devijaciju.

Razlike između funkcija obitelji STUDENT.DIST(). Počevši od Excela 2010, dostupna su tri različita oblika funkcije koja vraćaju dio distribucije lijevo i/ili desno od zadane vrijednosti t-testa. Funkcija STUDENT.DIST() vraća udio površine ispod krivulje distribucije lijevo od vrijednosti t-testa koju navedete. Recimo da imate 36 opažanja, pa je broj stupnjeva slobode za analizu 34, a vrijednost t-testa 1,69. U ovom slučaju, formula

STUDENT.DIST(+1,69;34;TRUE)

vraća vrijednost 0,05, odnosno 5% (slika 21). Treći argument za STUDENT.DIST() može biti TRUE ili FALSE. Ako je postavljeno na TRUE, funkcija vraća kumulativnu površinu ispod krivulje lijevo od zadanog t-testa, izraženu kao razlomak. Ako je FALSE, funkcija vraća relativnu visinu krivulje u točki koja odgovara t-testu. Druge verzije funkcije STUDENT.DIST() - STUDENT.DIST.PX() i STUDENT.DIST.2X() - uzimaju samo vrijednost t-testa i broj stupnjeva slobode kao argumente i ne zahtijevaju treći argument .

Riža. 21. Tamnije osjenčano područje u lijevom repu distribucije odgovara udjelu površine ispod krivulje lijevo od velike pozitivne vrijednosti t-testa.

Da biste odredili područje desno od t-testa, koristite jednu od formula:

1 - STUDENT.DIST (1, 69; 34; TRUE)

STUDENT.DIST.PH(1.69;34)

Ukupna površina ispod krivulje mora biti 100%, pa oduzimanjem od 1 udjela površine lijevo od vrijednosti t-testa koju vraća funkcija daje se ulomak površine desno od vrijednosti t-testa. Možda će vam biti poželjnije da izravno dobijete dio površine koji vas zanima pomoću funkcije STUDENT.DIST.RH(), gdje RH znači desni rep distribucije (slika 22).

Riža. 22. 5% alfa područja za usmjereno ispitivanje

Korištenje funkcija STUDENT.DIST() ili STUDENT.DIST.PH() implicira da ste odabrali usmjerenu radnu hipotezu. Hipoteza usmjerenog rada, u kombinaciji s postavljanjem alfa vrijednosti na 5%, znači da stavljate svih 5% u desni rep distribucija. Nul hipotezu ćete morati odbaciti samo ako je vjerojatnost da se dobije vrijednost vašeg t-testa 5% ili manja. Hipoteze usmjerenja obično rezultiraju osjetljivijim statističkim testovima (ova veća osjetljivost se također naziva veća statistička snaga).

S neusmjerenim testom, alfa vrijednost ostaje na istoj razini od 5%, ali će distribucija biti drugačija. Budući da morate dopustiti dva ishoda, vjerojatnost lažno pozitivnog mora biti raspoređena između dva repa distribucije. Općenito je prihvaćeno da se ta vjerojatnost ravnomjerno raspoređuje (slika 23).

Koristeći istu dobivenu vrijednost t-testa i isti broj stupnjeva slobode kao u prethodnom primjeru, upotrijebite formulu

STUDENT DIST.2X(1,69;34)

Bez posebnog razloga, funkcija STUDENT.DIST.2X() vraća kod pogreške #NUM! ako joj je dana negativna vrijednost t-testa kao prvi argument.

Ako uzorci sadrže različite brojeve podataka, upotrijebite t-test s dva uzorka s različitim varijacijama uključenim u paket Analiza podataka.

Poglavlje 7 Korištenje regresije za testiranje razlika između grupnih srednjih vrijednosti

Varijable koje su se ranije nazivale prediktivnim varijablama u ovom poglavlju nazivat će se varijablama ishoda, a umjesto prediktorskih varijabli koristit će se izraz faktorske varijable.

Najjednostavniji pristup kodiranju nominalne varijable je lažno kodiranje(slika 24).

Riža. 24. Regresijska analiza temeljena na lažnom kodiranju

Kada koristite lažno kodiranje bilo koje vrste, treba se pridržavati sljedećih pravila:

• Broj stupaca rezerviranih za nove podatke mora biti jednak broju minus razina faktora
• Svaki vektor predstavlja jednu razinu faktora.
• Ispitanici na jednoj razini, koja je često kontrolna skupina, primaju kod 0 na svim vektorima.

Formula u ćelijama F2:H6 =LINEST(A2:A22;C2:D22;;TRUE) vraća statistiku regresije. Za usporedbu, na sl. 24 prikazuje rezultate tradicionalne analize varijance koju vraća alat Jednosmjerna analiza varijance nadgradnje Analiza podataka.

Kodiranje učinaka. U drugoj vrsti kodiranja tzv kodiranje efekata, srednja vrijednost svake skupine uspoređuje se sa srednjom sredinom skupine. Ovaj aspekt kodiranja učinaka nastaje zbog upotrebe -1 umjesto 0 kao koda za grupu koja prima isti kod u svim vektorima koda (Slika 25).

Riža. 25. Kodiranje efekta

Kada se koristi lažno kodiranje, vrijednost konstante koju vraća LINEST() je srednja vrijednost grupe kojoj su dodijeljeni nulti kodovi u svim vektorima (obično kontrolnoj skupini). U slučaju kodiranja učinaka, konstanta je jednaka ukupnom prosjeku (ćelija J2).

Opći linearni model je koristan način za konceptualizaciju komponenti vrijednosti rezultirajuće varijable:

Y ij = μ + α j + ε ij

Upotreba grčkih slova umjesto latinskih u ovoj formuli naglašava činjenicu da se odnosi na populaciju iz koje su uzorci uzeti, ali se može prepisati kako bi se naznačilo da se odnosi na uzorke izvučene iz objavljene populacije:

Y ij = Y̅ + a j + e ij

Ideja je da se svako opažanje Y ij može promatrati kao zbroj sljedeće tri komponente: ukupna srednja vrijednost, μ; učinak obrade j, i j; vrijednost e ij , koja predstavlja odstupanje pojedinačnog kvantitativnog pokazatelja Y ij od kombinirane vrijednosti ukupne srednje vrijednosti i učinka j-tog tretmana (slika 26). Cilj regresijske jednadžbe je minimizirati zbroj kvadrata reziduala.

Riža. 26. Opažanja razložena na komponente općeg linearnog modela

Faktorska analiza. Ako se istražuje odnos između rezultirajuće varijable i dva ili više čimbenika u isto vrijeme, tada se u ovom slučaju govori o korištenju faktorske analize. Dodavanje jednog ili više čimbenika jednosmjernoj analizi varijance može povećati statističku snagu. U jednosmjernoj ANOVA, varijacija varijable ishoda koja se ne može pripisati faktoru uključena je u rezidualni srednji kvadrat. No može biti da je ova varijacija s povezana s drugim čimbenikom. Tada se ova varijacija može ukloniti iz srednje kvadratne pogreške, čije smanjenje dovodi do povećanja vrijednosti F-testa, a time i do povećanja statističke snage testa. nadgradnja Analiza podataka uključuje alat koji omogućuje obradu dvaju čimbenika istovremeno (slika 27).

Riža. 27. Alat Dvosmjerna analiza varijance s paketom analize ponavljanja

Alat za analizu varijance koji se koristi na ovoj slici je koristan jer vraća srednju vrijednost i varijancu rezultirajuće varijable, kao i vrijednost brojača za svaku skupinu uključenu u dizajn. Stol Analiza varijance prikazuje dva parametra koja nisu u izlazu jednosmjerne verzije alata ANOVA. Obratite pažnju na izvore varijacija Uzorak I stupaca u redovima 27 i 28. Izvor varijacije stupaca odnosi se na spol. Izvor varijacije Uzorak odnosi se na bilo koju varijablu čije vrijednosti zauzimaju različite retke. Na sl. 27, vrijednosti za grupu CourseLech1 su u recima 2-6, grupa CourseLech2 su u redcima 7-11, a grupa CourseLech3 u redcima 12-16.

Glavna poanta je da su i spol (označeni stupci u ćeliji E28) i tretman (označen Uzorak u ćeliji E27) uključeni u ANOVA tablicu kao izvori varijacija. Prosjeci za muškarce razlikuju se od prosjeka za žene, a to stvara izvor varijacija. Prosjeci za tri tretmana također se razlikuju - evo još jednog izvora varijacija. Postoji i treći izvor, Interakcija, koji se odnosi na kombinirani učinak varijabli Spol i Tretman.

Poglavlje 8

Analiza kovarijance, ili ANCOVA (Analysis of Covarijation), smanjuje pristranost i povećava statističku moć. Dopustite mi da vas podsjetim da su jedan od načina za procjenu pouzdanosti regresijske jednadžbe F-testovi:

F = MS regresija/MS rezidualni

gdje je MS (Mean Square) srednji kvadrat, a indeksi Regresija i Residual označavaju regresiju i rezidualnu komponentu, respektivno. MS Residual se izračunava pomoću formule:

MS ostatak = SS rezidual / df ostatak

gdje je SS (Sum of Squares) zbroj kvadrata, a df je broj stupnjeva slobode. Kada regresijskoj jednadžbi dodate kovarijansu, neki udio ukupnog zbroja kvadrata nije uključen u SS ResiduaI, već u SS Regresiju. To dovodi do smanjenja SS Residual l , a time i MS Residual . Što je manji MS Residual, veći je F-test i veća je vjerojatnost da ćete odbaciti nultu hipotezu da nema razlike između srednjih vrijednosti. Kao rezultat toga, preraspodijelite volatilnost rezultirajuće varijable. U ANOVA, kada se kovarijacija ne uzme u obzir, varijabilnost prelazi u pogrešku. Ali u ANCOVA, dio varijabilnosti koji je prethodno pripisan pogrešci pripisuje se kovarijati i postaje dio SS regresije.

Razmotrimo primjer u kojem se isti skup podataka analizira prvo s ANOVA, a zatim s ANCOVA (Slika 28).

Riža. 28. ANOVA analiza pokazuje da su rezultati dobiveni pomoću regresijske jednadžbe nepouzdani

Studija uspoređuje relativne učinke tjelesnog vježbanja, koje razvija mišićnu snagu, i kognitivne vježbe (križaljke) koja aktivira moždanu aktivnost. Ispitanici su nasumično raspoređeni u dvije skupine tako da su na početku eksperimenta obje skupine bile u istim uvjetima. Nakon tri mjeseca mjerene su kognitivne karakteristike ispitanika. Rezultati ovih mjerenja prikazani su u stupcu B.

Raspon A2:C21 sadrži početne podatke proslijeđene funkciji LINEST() za izvođenje analize korištenjem kodiranja učinaka. Rezultati funkcije LINEST() prikazani su u rasponu E2:F6, gdje ćelija E2 prikazuje koeficijent regresije povezan s vektorom utjecaja. Ćelija E8 sadrži t-test = 0,93, a ćelija E9 testira pouzdanost ovog t-testa. Vrijednost u ćeliji E9 pokazuje da je vjerojatnost nailaska na razliku između srednjih vrijednosti skupine promatranih u ovom eksperimentu 36% ako su srednje vrijednosti skupine jednake u populaciji. Samo rijetki smatraju da je ovaj rezultat statistički značajan.

Na sl. Slika 29 prikazuje što se događa kada se kovarijat doda analizi. U ovom slučaju, dodao sam dob svakog subjekta u skup podataka. Koeficijent determinacije R 2 za regresijsku jednadžbu koja koristi kovarijat je 0,80 (ćelija F4). Vrijednost R 2 u rasponu F15:G19, u kojem sam reproducirao rezultate ANOVA dobivene bez korištenja kovarijate, iznosi samo 0,05 (ćelija F17). Stoga, regresijska jednadžba koja uključuje kovarijatu predviđa vrijednosti varijable kognitivnog rezultata mnogo točnije nego koristeći samo vektor utjecaja. Za ANCOVA, vjerojatnost slučajnog dobivanja vrijednosti F-testa prikazane u ćeliji F5 manja je od 0,01%.

Riža. 29. ANCOVA vraća potpuno drugačiju sliku

Statistička obrada podataka može se provesti i pomoću dodatka PAKET ANALIZE(slika 62).

Od predloženih stavki odaberite stavku " REGRESIJA” i kliknite na nju lijevom tipkom miša. Zatim kliknite OK.

Prozor prikazan na sl. 63.

Alat za analizu « REGRESIJA» koristi se za uklapanje grafa u skup opažanja korištenjem metode najmanjih kvadrata. Regresija se koristi za analizu učinka na jednu zavisnu varijablu vrijednosti jedne ili više neovisnih varijabli. Na primjer, na sportsku izvedbu sportaša utječe nekoliko čimbenika, uključujući dob, visinu i težinu. Moguće je izračunati stupanj utjecaja svakog od ova tri čimbenika na izvedbu jednog sportaša, a zatim pomoću dobivenih podataka predvidjeti učinak drugog sportaša.

Alat Regresija koristi tu funkciju LINEST.

Dijaloški okvir REGRESS

Oznake Odaberite potvrdni okvir ako prvi redak ili prvi stupac raspona unosa sadrži naslove. Poništite ovaj potvrdni okvir ako nema zaglavlja. U tom će se slučaju automatski generirati prikladna zaglavlja za podatke izlazne tablice.

Razina pouzdanosti Označite potvrdni okvir za uključivanje dodatne razine u tablicu ukupnih rezultata. U odgovarajuće polje unesite razinu pouzdanosti koju želite primijeniti, uz zadanu razinu pouzdanosti od 95%.

Konstanta - nula Označite okvir kako bi linija regresije prolazila kroz ishodište.

Izlazni raspon Unesite referencu na gornju lijevu ćeliju izlaznog raspona. Dodijelite najmanje sedam stupaca za izlaznu tablicu rezultata, koja će uključivati: rezultate analize varijance, koeficijente, standardnu ​​pogrešku izračuna Y, standardne devijacije, broj opažanja, standardne pogreške za koeficijente.

Novi radni list Označite ovaj okvir za otvaranje novog radnog lista u radnoj knjizi i umetanje rezultata analize počevši od ćelije A1. Ako je potrebno, unesite naziv za novi list u polje nasuprot odgovarajućeg položaja radio gumba.

Nova radna knjiga Označite ovaj okvir za stvaranje nove radne knjige u kojoj će se rezultati dodati na novi list.

Ostaci Označite potvrdni okvir da biste uključili ostatke u izlaznu tablicu.

Standardizirani ostaci Označite potvrdni okvir za uključivanje standardiziranih ostataka u izlaznu tablicu.

Grafikon ostatka Označite okvir za iscrtavanje reziduala za svaku neovisnu varijablu.

Fit Plot Označite potvrdni okvir za iscrtavanje predviđenih vrijednosti naspram promatranih vrijednosti.

Grafikon normalne vjerojatnosti Označite okvir za crtanje normalne vjerojatnosti.

Funkcija LINEST

Za izračune odaberite ćeliju u kojoj želimo prikazati prosječnu vrijednost kursorom i pritisnite tipku = na tipkovnici. Zatim u polju Naziv navedite željenu funkciju, na primjer PROSJEČAN(slika 22).

Funkcija LINEST izračunava statistiku za niz koristeći metodu najmanjih kvadrata kako bi izračunao ravnu liniju koja najbolje odgovara dostupnim podacima, a zatim vraća niz koji opisuje rezultirajuću ravnu liniju. Također možete kombinirati funkciju LINEST s drugim funkcijama za izračunavanje drugih vrsta modela koji su linearni u nepoznatim parametrima (čiji su nepoznati parametri linearni), uključujući polinomske, logaritamske, eksponencijalne i nizove stepena. Budući da se vraća niz vrijednosti, funkcija mora biti navedena kao formula polja.

Jednadžba za ravnu liniju je:

y=m 1 x 1 +m 2 x 2 +…+b (u slučaju nekoliko raspona x vrijednosti),

gdje je zavisna vrijednost y funkcija nezavisne vrijednosti x, vrijednosti m su koeficijenti koji odgovaraju svakoj nezavisnoj varijabli x, a b je konstanta. Imajte na umu da y, x i m mogu biti vektori. Funkcija LINEST vraća niz (mn;mn-1;…;m 1 ;b). LINEST može također vratiti dodatnu statistiku regresije.

LINEST(poznate_y-vrijednosti; poznate_x-vrijednosti; konst; statistika)

Poznate_y vrijednosti - skup y vrijednosti koje su već poznate za relaciju y=mx+b.

Ako polje poznatog_y ima jedan stupac, tada se svaki stupac niza poznatog_x tumači kao zasebna varijabla.

Ako polje poznatog_y ima jedan red, tada se svaki red niza poznatog_x tumači kao zasebna varijabla.

Poznate_x vrijednosti - izborni skup x vrijednosti koje su već poznate za relaciju y=mx+b.

Poznati_x niz može sadržavati jedan ili više skupova varijabli. Ako se koristi samo jedna varijabla, tada arrays_known_y_values ​​i known_x_values ​​mogu biti bilo kojeg oblika - sve dok imaju istu dimenziju. Ako se koristi više od jedne varijable, tada poznati_y mora biti vektor (to jest, visok jedan redak ili širina jednog stupca).

Ako je niz_poznat_x izostavljen, tada se pretpostavlja da je ovaj niz (1;2;3;...) iste veličine kao niz_poznat_y.

Const je logička vrijednost koja određuje da li konstanta b mora biti 0.

Ako je argument "const" TRUE ili izostavljen, tada se konstanta b normalno vrednuje.

Ako je argument "const" FALSE, tada se pretpostavlja da je vrijednost b 0, a vrijednosti m se biraju na način da je zadovoljen odnos y=mx.

Statistika je Booleova vrijednost koja pokazuje treba li vratiti dodatnu statistiku regresije.

Ako je statistika TRUE, LINEST vraća dodatnu statistiku regresije. Vraćeni niz će izgledati ovako: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Ako je statistika FALSE ili je izostavljena, LINEST vraća samo koeficijente m i konstantu b.

Dodatna regresijska statistika. (Tablica 17)

Vrijednost	Opis
se1,se2,...,sen	Standardne vrijednosti pogreške za koeficijente m1,m2,...,mn.
seb	Standardna pogreška za konstantu b (seb = #N/A ako je 'const' FALSE).
r2	Koeficijent determinacije. Stvarne vrijednosti y se uspoređuju s vrijednostima dobivenim iz jednadžbe pravocrtne linije; na temelju rezultata usporedbe izračunava se koeficijent determinizma, normaliziran od 0 do 1. Ako je jednak 1, postoji potpuna korelacija s modelom, tj. nema razlike između stvarne i procijenjene vrijednosti ​od god. Inače, ako je koeficijent determinizma 0, nema smisla koristiti jednadžbu regresije za predviđanje y vrijednosti. Za više informacija o tome kako izračunati r2, pogledajte "Napomene" na kraju ovog odjeljka.
sey	Standardna pogreška za procjenu y.
F	F-statistička ili F-opažena vrijednost. F statistika se koristi za određivanje je li promatrani odnos između ovisnih i nezavisnih varijabli slučajan.
df	Stupnjevi slobode. Stupnjevi slobode korisni su za pronalaženje F-kritičnih vrijednosti u statističkoj tablici. Da biste odredili razinu pouzdanosti modela, morate usporediti vrijednosti u tablici s F-statistikom koju vraća LINEST. Pogledajte "Napomene" na kraju ovog odjeljka za više informacija o izračunu df. Primjer 4 u nastavku pokazuje upotrebu F i df.
ssreg	Regresijski zbroj kvadrata.
ssresid	Preostali zbroj kvadrata. Za više informacija o izračunavanju ssreg i ssresid, pogledajte "Napomene" na kraju ovog odjeljka.

Slika ispod prikazuje redoslijed kojim se vraćaju dodatne statistike regresije (slika 64).

Bilješke:

Svaka ravna linija može se opisati svojim nagibom i sjecištem s y-osom:

Nagib (m): da biste odredili nagib pravca, koji se obično označava s m, trebate uzeti dvije točke na pravci (x 1 ,y 1) i (x 2 ,y 2); nagib će biti jednak (y 2 -y 1) / (x 2 -x 1).

Y-presjek (b): y-presjek pravca, koji se obično označava s b, je vrijednost y za točku u kojoj pravac siječe y-os.

Ravnocrtna jednadžba ima oblik y=mx+b. Ako su poznate vrijednosti m i b, tada se bilo koja točka na liniji može izračunati zamjenom vrijednosti y ili x u jednadžbu. Također možete koristiti funkciju TREND.

Ako postoji samo jedna nezavisna varijabla x, možete izravno dobiti nagib i y-presjek koristeći sljedeće formule:

Nagib: INDEX(LINEST(poznati_y, poznati_x), 1)

Y-presretak: INDEX(LINEST(poznati_y, poznati_x), 2)

Točnost aproksimacije pomoću ravne linije izračunate funkcijom LINEST ovisi o stupnju raspršenosti podataka. Što su podaci bliži ravnoj liniji, to je model koji koristi LINEST točniji. Funkcija LINEST koristi metodu najmanjih kvadrata kako bi odredila što najbolje odgovara podacima. Kada postoji samo jedna nezavisna varijabla x, m i b se izračunavaju pomoću sljedećih formula:

gdje su x i y srednje vrijednosti uzorka, na primjer x = PROSJEČAN(poznati_x) i y = PROSJEČNI(poznati_y).

Funkcije uklapanja LINEST i LGRFPRIBL mogu izračunati ravnu ili eksponencijalnu krivulju koja najbolje odgovara podacima. Međutim, oni ne odgovaraju na pitanje koji je od dva rezultata prikladniji za rješavanje problema. Također možete izračunati funkciju TREND(poznate_y-vrijednosti; poznate_x-vrijednosti) funkciju za ravnu liniju ili funkciju GROWTH(poznate_y-vrijednosti; poznate_x-vrijednosti) funkciju za eksponencijalnu krivulju. Ove funkcije, ako su izostavljene iz argumenta new_x_values, vraćaju niz izračunatih vrijednosti y za stvarne vrijednosti x prema ravnoj liniji ili krivulji. Zatim možete usporediti izračunate vrijednosti sa stvarnim vrijednostima. Također možete izraditi grafikone za vizualnu usporedbu.

Prilikom provođenja regresijske analize, Microsoft Excel izračunava, za svaku točku, kvadrat razlike između predviđene vrijednosti y i stvarne vrijednosti y. Zbroj tih kvadrata razlika naziva se rezidualni zbroj kvadrata (ssresid). Microsoft Excel zatim izračunava ukupan zbroj kvadrata (sstotal). Ako je const = TRUE ili ako ovaj argument nije naveden, ukupni zbroj kvadrata bit će jednak zbroju kvadrata razlika stvarnih y vrijednosti i srednjih vrijednosti y. Ako je const = FALSE, zbroj kvadrata će biti jednak zbroju kvadrata stvarnih vrijednosti y (bez oduzimanja srednje vrijednosti y od kvocijenta y). Nakon toga, regresijski zbroj kvadrata može se izračunati na sljedeći način: ssreg = sstotal - ssresid. Što je manji rezidualni zbroj kvadrata, to je veća vrijednost koeficijenta determinizma r2, što pokazuje koliko dobro jednadžba dobivena regresijskom analizom objašnjava odnose između varijabli. Koeficijent r2 jednak je ssreg/sstotal.

U nekim slučajevima, jedan ili više X stupaca (neka vrijednosti Y i X budu u stupcima) nemaju dodatnu prediktivnu vrijednost u drugim stupcima X. Drugim riječima, brisanje jednog ili više X stupaca može rezultirati Y vrijednostima izračunati s istom preciznošću. U ovom slučaju, redundantni X stupci bit će isključeni iz regresijskog modela. Taj se fenomen naziva "kolinearnost" jer se redundantni stupci X mogu predstaviti kao zbroj nekoliko neredundantnih stupaca. LINEST provjerava kolinearnost i uklanja sve suvišne X stupce iz regresijskog modela ako ih pronađe. Uklonjeni X stupci mogu se identificirati u LINEST izlazu faktorom 0 i se vrijednosti 0. Uklanjanjem jednog ili više stupaca kao suvišnih mijenja se vrijednost df jer ovisi o broju X stupaca koji se stvarno koriste u svrhe predviđanja. Pogledajte primjer 4 u nastavku za više detalja o izračunavanju df. Kada se df promijeni zbog uklanjanja suvišnih stupaca, mijenjaju se i vrijednosti sey i F. Često se ne preporučuje korištenje kolinearnosti. Međutim, treba ga koristiti ako neki X stupci sadrže 0 ili 1 kao indikator koji pokazuje je li subjekt eksperimenta u zasebnoj skupini. Ako je const = TRUE ili ako ovaj argument nije naveden, LINEST umeće dodatni X stupac za simulaciju točke presjeka. Ako postoji stupac s vrijednostima 1 za muškarce i 0 za žene, a postoji stupac s vrijednostima 1 za žene i 0 za muškarce, tada se posljednji stupac uklanja jer se njegove vrijednosti mogu dobiti iz stupac "muški indikator".

Izračun df za slučajeve kada X stupaca nije uklonjeno iz modela zbog kolinearnosti je kako slijedi: ako postoji k poznatih_x stupaca i const = TRUE ili nije navedeno, tada je df = n - k - 1. Ako je const = FALSE, tada je df = n -k. U oba slučaja, uklanjanje X stupaca zbog kolinearnosti povećava vrijednost df za 1.

Formule koje vraćaju nizove moraju se unijeti kao formule polja.

Prilikom unosa niza konstanti kao argumenta known_x_values, na primjer, koristite točku i zarez za odvajanje vrijednosti u istom retku i dvotočku za razdvajanje redaka. Znakovi za razdvajanje mogu se razlikovati ovisno o postavkama u prozoru "Jezik i standardi" na upravljačkoj ploči.

Imajte na umu da vrijednosti y predviđene regresijskom jednadžbom možda neće biti točne ako su izvan raspona vrijednosti y koje su korištene za definiranje jednadžbe.

Glavni algoritam koji se koristi u funkciji LINEST, razlikuje se od glavnog algoritma funkcija NAGIB I ODJELJAK. Razlike između algoritama mogu dovesti do različitih rezultata za nesigurne i kolinearne podatke. Na primjer, ako su podatkovne točke argumenta poznatog_y 0, a podatkovne točke argumenta poznatog_x 1, tada:

Funkcija LINEST vraća vrijednost jednaku 0. Algoritam funkcije LINEST koristi se za vraćanje prikladnih vrijednosti za kolinearne podatke, u kojem slučaju se može pronaći barem jedan odgovor.

Funkcije SLOPE i INTERCEPT vraćaju pogrešku #DIV/0!. Algoritam funkcija SLOPE i INTERCEPT koristi se za pronalaženje samo jednog odgovora, au ovom slučaju može ih biti nekoliko.

Uz izračun statistike za druge vrste regresije, LINEST se može koristiti za izračunavanje raspona za druge vrste regresije unosom funkcija varijabli x i y kao niza varijabli x i y za LINEST. Na primjer, sljedeća formula:

LINEST(y-vrijednosti, x-vrijednosti^COLUMN($A:$C))

radi s jednim stupcem vrijednosti Y i jednim stupcem vrijednosti X kako bi izračunao aproksimaciju kocke (polinom 3. stupnja) sljedećeg oblika:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

Formula se može modificirati za izračunavanje drugih vrsta regresije, ali u nekim slučajevima su potrebne prilagodbe izlaznih vrijednosti i drugih statistika.

U excel postoji još brži i prikladniji način za crtanje linearne regresije (pa čak i glavne vrste nelinearnih regresija, vidi dolje). To se može učiniti ovako:

1) odaberite stupce s podacima x I Y(moraju biti tim redoslijedom!);

2) poziv Čarobnjak za karte i odaberite u grupi Tip – točkasta i odmah pritisnite Spreman;

3) bez poništavanja odabira dijagrama, odaberite stavku glavnog izbornika koja se pojavi Dijagram, u kojem biste trebali odabrati stavku Dodajte liniju trenda;

4) u dijaloškom okviru koji se pojavi linija trenda tab Tip Odaberi Linearna;

5) tab Parametri prekidač se može aktivirati Pokažite jednadžbu na grafikonu, što će vam omogućiti da vidite jednadžbu linearne regresije (4.4), u kojoj će se izračunati koeficijenti (4.5).

6) U istoj kartici možete aktivirati prekidač Stavite na dijagram vrijednost pouzdanosti aproksimacije (R^2). Ova vrijednost je kvadrat koeficijenta korelacije (4.3) i pokazuje koliko dobro izračunata jednadžba opisuje eksperimentalnu ovisnost. Ako R 2 je blizu jedinice, tada teorijska regresijska jednadžba dobro opisuje eksperimentalnu ovisnost (teorija se dobro slaže s eksperimentom), a ako R 2 je blizu nuli, onda ova jednadžba nije prikladna za opisivanje eksperimentalne ovisnosti (teorija se ne slaže s eksperimentom).

Kao rezultat izvođenja opisanih radnji, dobit ćete dijagram s regresijskim grafom i njegovom jednadžbom.

§4.3. Glavne vrste nelinearne regresije

Parabolička i polinomska regresija.

Parabolični ovisnost vrijednosti Y od vrijednosti x ovisnost izražena kvadratnom funkcijom (parabola 2. reda) naziva se:

Ova se jednadžba zove parabolička regresija Y na x. Parametri ali, b, iz pozvao koeficijenti paraboličke regresije. Izračun koeficijenata paraboličke regresije uvijek je glomazan pa se za izračune preporučuje korištenje računala.

Jednadžba (4.8) paraboličke regresije poseban je slučaj općenitije regresije koja se naziva polinom. polinom ovisnost vrijednosti Y od vrijednosti x naziva se ovisnost izražena polinomom n-ti red:

gdje su brojevi a i (i=0,1,…, n) se zovu koeficijenti polinomske regresije.

Regresija snage.

Vlast ovisnost vrijednosti Y od vrijednosti x naziva se ovisnost oblika:

Ova se jednadžba zove jednadžba regresije snage Y na x. Parametri ali I b pozvao koeficijenti regresije snage.

ln=ln a+b ln x. (4.11)

Ova jednadžba opisuje ravnu liniju u ravnini s logaritamskim koordinatnim osi ln x i ln. Stoga je kriterij primjenjivosti regresije potenciranja zahtjev da točke logaritama empirijskih podataka ln x i i ln i bili najbliži pravoj liniji (4.11).

eksponencijalna regresija.

uzorna(ili eksponencijalna) ovisnost količine Y od vrijednosti x naziva se ovisnost oblika:

(ili ). (4.12)

Ova se jednadžba zove eksponencijalna jednadžba(ili eksponencijalna) regresija Y na x. Parametri ali(ili k) I b pozvao eksponencijalna(ili eksponencijalna) regresija.

Ako uzmemo logaritam obje strane jednadžbe regresije moći, dobit ćemo jednadžbu

ln = x ln a+ln b(ili ln = k x+ln b). (4.13)

Ova jednadžba opisuje linearnu ovisnost logaritma jedne veličine ln o drugoj veličini x. Stoga je kriterij za primjenjivost regresije snage zahtjev da empirijski podaci ukazuju na istu veličinu x i i logaritmi druge vrijednosti ln i bili najbliži pravoj liniji (4.13).

logaritamska regresija.

Logaritamski ovisnost vrijednosti Y od vrijednosti x naziva se ovisnost oblika:

=a+b ln x. (4.14)

Ova se jednadžba zove logaritamska regresija Y na x. Parametri ali I b pozvao koeficijenti logaritamske regresije.

hiperbolička regresija.

Hiperbolični ovisnost vrijednosti Y od vrijednosti x naziva se ovisnost oblika:

Ova se jednadžba zove jednadžba hiperboličke regresije Y na x. Parametri ali I b pozvao koeficijenti hiperboličke regresije a određuju se metodom najmanjih kvadrata. Primjena ove metode dovodi do formula:

U formulama (4.16-4.17) zbrajanje se vrši preko indeksa i od jednog do broja opažanja n.

Nažalost, u excel ne postoji funkcija koja izračunava koeficijente hiperboličke regresije. U onim slučajevima kada se ne zna pouzdano da su izmjerene vrijednosti povezane inverznom proporcionalnošću, preporuča se tražiti jednadžbu regresije snage umjesto jednadžbe hiperboličke regresije, pa u excel postoji procedura za njegovo pronalaženje. Ako se između izmjerenih vrijednosti pretpostavi hiperbolička ovisnost, tada će se njezini regresijski koeficijenti morati izračunati pomoću pomoćnih proračunskih tablica i operacija zbrajanja pomoću formula (4.16-4.17).

Regresija u Excelu

Statistička obrada podataka može se provesti i pomoću dodatka Analysis paketa u podtočki izbornika "Usluga". U Excelu 2003, ako otvorite SERVIS, ne možemo pronaći karticu ANALIZA PODATAKA, a zatim kliknite lijevu tipku miša da otvorite karticu DODATCI i suprotna točka PAKET ANALIZE klikom na lijevu tipku miša stavite kvačicu (slika 17).

Riža. 17. Prozor DODATCI

Nakon toga jelovnik SERVIS pojavi se kartica ANALIZA PODATAKA.

U programu Excel 2007 za instalaciju ANALIZA PAKETA potrebno je kliknuti na gumb URED u gornjem lijevom kutu lista (slika 18a). Zatim kliknite na gumb EXCEL OPCIJE. U prozoru koji se pojavi EXCEL OPCIJE lijevi klik na stavku DODATCI a u desnom dijelu padajućeg popisa odaberite stavku PAKET ANALIZE. Zatim kliknite na u redu.

Opcije programa Excel Uredski gumb

Riža. 18. Instalacija ANALIZA PAKETA u Excelu 2007

Da biste instalirali Analysis Pack, kliknite gumb IĆI, na dnu otvorenog prozora. Prozor prikazan na sl. 12. Označite okvir pored PAKET ANALIZE. U kartici PODACI pojavit će se gumb ANALIZA PODATAKA(slika 19).

Od predloženih stavki odaberite stavku " REGRESIJA” i kliknite na nju lijevom tipkom miša. Zatim kliknite OK.

Prozor prikazan na sl. 21

Alat za analizu « REGRESIJA» koristi se za uklapanje grafa u skup opažanja korištenjem metode najmanjih kvadrata. Regresija se koristi za analizu učinka na jednu zavisnu varijablu vrijednosti jedne ili više neovisnih varijabli. Na primjer, na sportsku izvedbu sportaša utječe nekoliko čimbenika, uključujući dob, visinu i težinu. Moguće je izračunati stupanj utjecaja svakog od ova tri čimbenika na izvedbu jednog sportaša, a zatim pomoću dobivenih podataka predvidjeti učinak drugog sportaša.

Alat Regresija koristi tu funkciju LINEST.

Dijaloški okvir REGRESS

Oznake Odaberite potvrdni okvir ako prvi redak ili prvi stupac raspona unosa sadrži naslove. Poništite ovaj potvrdni okvir ako nema zaglavlja. U tom će se slučaju automatski generirati prikladna zaglavlja za podatke izlazne tablice.

Razina pouzdanosti Označite potvrdni okvir za uključivanje dodatne razine u tablicu ukupnih rezultata. U odgovarajuće polje unesite razinu pouzdanosti koju želite primijeniti, uz zadanu razinu pouzdanosti od 95%.

Konstanta - nula Označite okvir kako bi linija regresije prolazila kroz ishodište.

Izlazni raspon Unesite referencu na gornju lijevu ćeliju izlaznog raspona. Dodijelite najmanje sedam stupaca za izlaznu tablicu rezultata, koja će uključivati: rezultate analize varijance, koeficijente, standardnu ​​pogrešku izračuna Y, standardne devijacije, broj opažanja, standardne pogreške za koeficijente.

Novi radni list Označite ovaj okvir za otvaranje novog radnog lista u radnoj knjizi i umetanje rezultata analize počevši od ćelije A1. Ako je potrebno, unesite naziv za novi list u polje nasuprot odgovarajućeg položaja radio gumba.

Nova radna knjiga Označite ovaj okvir za stvaranje nove radne knjige u kojoj će se rezultati dodati na novi list.

Ostaci Označite potvrdni okvir da biste uključili ostatke u izlaznu tablicu.

Standardizirani ostaci Označite potvrdni okvir za uključivanje standardiziranih ostataka u izlaznu tablicu.

Grafikon ostatka Označite okvir za iscrtavanje reziduala za svaku neovisnu varijablu.

Fit Plot Označite potvrdni okvir za iscrtavanje predviđenih vrijednosti naspram promatranih vrijednosti.

Grafikon normalne vjerojatnosti Označite okvir za crtanje normalne vjerojatnosti.

Funkcija LINEST

Za izračune odaberite ćeliju u kojoj želimo prikazati prosječnu vrijednost kursorom i pritisnite tipku = na tipkovnici. Zatim u polju Naziv navedite željenu funkciju, na primjer PROSJEČAN(slika 22).

Riža. 22 Pronalaženje funkcija u Excelu 2003

Ako na terenu IME naziv funkcije se ne pojavljuje, zatim lijevom tipkom miša kliknite na trokut pored polja, nakon čega će se pojaviti prozor s popisom funkcija. Ako ove funkcije nema na popisu, kliknite lijevom tipkom miša na stavku na popisu OSTALE FUNKCIJE, pojavit će se dijaloški okvir. MASTER FUNKCIJE, u kojem pomoću okomitog pomicanja odaberite željenu funkciju, odaberite je kursorom i kliknite na u redu(slika 23).

Riža. 23. Čarobnjak za funkcije

Za traženje funkcije u Excelu 2007 u izborniku se može otvoriti bilo koja kartica, zatim za izračune kursorom odabrati ćeliju u kojoj želimo prikazati prosječnu vrijednost i pritisnuti tipku = na tipkovnici. Zatim u polju Naziv navedite funkciju PROSJEČAN. Prozor za izračun funkcije sličan je onom u Excelu 2003.

Također možete odabrati karticu Formule i kliknuti lijevom tipkom miša na gumb u " INSERT FUNCTION» (slika 24), pojavit će se prozor MASTER FUNKCIJE, čiji je prikaz sličan Excelu 2003. Također, u izborniku možete odmah odabrati kategoriju funkcija (nedavno korištene, financijske, logičke, tekstualne, datum i vrijeme, matematičke, druge funkcije), u kojima ćemo pretraživati za željenu funkciju.

Druge značajke Reference i nizovi Matematički

Riža. 24 Odabir funkcije u Excelu 2007

Funkcija LINEST izračunava statistiku za niz koristeći metodu najmanjih kvadrata kako bi izračunao ravnu liniju koja najbolje odgovara dostupnim podacima, a zatim vraća niz koji opisuje rezultirajuću ravnu liniju. Također možete kombinirati funkciju LINEST s drugim funkcijama za izračunavanje drugih vrsta modela koji su linearni u nepoznatim parametrima (čiji su nepoznati parametri linearni), uključujući polinomske, logaritamske, eksponencijalne i nizove stepena. Budući da se vraća niz vrijednosti, funkcija mora biti navedena kao formula polja.

Jednadžba za ravnu liniju je:

(u slučaju više raspona x vrijednosti),

gdje je zavisna vrijednost y funkcija nezavisne vrijednosti x, vrijednosti m su koeficijenti koji odgovaraju svakoj nezavisnoj varijabli x, a b je konstanta. Imajte na umu da y, x i m mogu biti vektori. Funkcija LINEST vraća niz . LINEST može također vratiti dodatnu statistiku regresije.

LINEST(poznate_y-vrijednosti; poznate_x-vrijednosti; konst; statistika)

Poznate_y vrijednosti - skup y vrijednosti koje su već poznate za relaciju.

Ako polje poznatog_y ima jedan stupac, tada se svaki stupac niza poznatog_x tumači kao zasebna varijabla.

Ako polje poznatog_y ima jedan red, tada se svaki red niza poznatog_x tumači kao zasebna varijabla.

Poznati_x je izborni skup x-ova koji su već poznati za relaciju.

Poznati_x niz može sadržavati jedan ili više skupova varijabli. Ako se koristi samo jedna varijabla, tada arrays_known_y_values ​​i known_x_values ​​mogu biti bilo kojeg oblika - sve dok imaju istu dimenziju. Ako se koristi više od jedne varijable, tada poznati_y mora biti vektor (to jest, visok jedan redak ili širina jednog stupca).

Ako je niz_poznat_x izostavljen, tada se pretpostavlja da je ovaj niz (1;2;3;...) iste veličine kao niz_poznat_y.

Const je logička vrijednost koja određuje da li konstanta b mora biti 0.

Ako je argument "const" TRUE ili izostavljen, tada se konstanta b normalno vrednuje.

Ako je argument "const" FALSE, tada se pretpostavlja da je vrijednost b 0, a vrijednosti m se biraju na način da je relacija zadovoljena.

Statistika je Booleova vrijednost koja pokazuje treba li vratiti dodatnu statistiku regresije.

Ako je statistika TRUE, LINEST vraća dodatnu statistiku regresije. Vraćeni niz će izgledati ovako: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Ako je statistika FALSE ili je izostavljena, LINEST vraća samo koeficijente m i konstantu b.

Dodatna statistika regresije.

Vrijednost Opis se1,se2,...,sen Standardne vrijednosti pogreške za koeficijente m1,m2,...,mn. seb Standardna pogreška za konstantu b (seb = #N/A ako je 'const' FALSE). r2 Koeficijent determinacije. Stvarne vrijednosti y se uspoređuju s vrijednostima dobivenim iz jednadžbe pravocrtne linije; na temelju rezultata usporedbe izračunava se koeficijent determinizma, normaliziran od 0 do 1. Ako je jednak 1, postoji potpuna korelacija s modelom, tj. nema razlike između stvarne i procijenjene vrijednosti ​od god. Inače, ako je koeficijent determinizma 0, nema smisla koristiti jednadžbu regresije za predviđanje y vrijednosti. Za više informacija o tome kako izračunati r2, pogledajte "Napomene" na kraju ovog odjeljka. sey Standardna pogreška za procjenu y. F F-statistička ili F-opažena vrijednost. F statistika se koristi za određivanje je li promatrani odnos između ovisnih i nezavisnih varijabli slučajan. df Stupnjevi slobode. Stupnjevi slobode korisni su za pronalaženje F-kritičnih vrijednosti u statističkoj tablici. Da biste odredili razinu pouzdanosti modela, morate usporediti vrijednosti u tablici s F-statistikom koju vraća LINEST. Pogledajte "Napomene" na kraju ovog odjeljka za više informacija o izračunu df. Primjer 4 u nastavku pokazuje upotrebu F i df. ssreg Regresijski zbroj kvadrata. ssresid Preostali zbroj kvadrata. Za više informacija o izračunavanju ssreg i ssresid, pogledajte "Napomene" na kraju ovog odjeljka.

Slika ispod prikazuje redoslijed kojim se vraćaju dodatne statistike regresije.

Bilješke:

Svaka ravna linija može se opisati svojim nagibom i sjecištem s y-osom:

Nagib (m): Da biste odredili nagib pravca, koji se obično označava s m, trebate uzeti dvije točke na liniji i ; nagib će biti .

Y-presjek (b): y-presjek pravca, koji se obično označava s b, je vrijednost y za točku u kojoj pravac siječe y-os.

Jednadžba ravne linije ima oblik . Ako su poznate vrijednosti m i b, tada se bilo koja točka na liniji može izračunati zamjenom vrijednosti y ili x u jednadžbu. Također možete koristiti funkciju TREND.

Ako postoji samo jedna nezavisna varijabla x, možete izravno dobiti nagib i y-presjek koristeći sljedeće formule:

Nagib: INDEX(LINEST(poznati_y, poznati_x), 1)

Y-presretak: INDEX(LINEST(poznati_y, poznati_x), 2)

Točnost aproksimacije pomoću ravne linije izračunate funkcijom LINEST ovisi o stupnju raspršenosti podataka. Što su podaci bliži ravnoj liniji, to je model koji koristi LINEST točniji. Funkcija LINEST koristi metodu najmanjih kvadrata kako bi odredila što najbolje odgovara podacima. Kada postoji samo jedna nezavisna varijabla x, m i b se izračunavaju pomoću sljedećih formula:

gdje su x i y srednje vrijednosti uzorka, na primjer x = PROSJEČAN(poznati_x) i y = PROSJEČNI(poznati_y).

Funkcije uklapanja LINEST i LGRFPRIBL mogu izračunati ravnu ili eksponencijalnu krivulju koja najbolje odgovara podacima. Međutim, oni ne odgovaraju na pitanje koji je od dva rezultata prikladniji za rješavanje problema. Također možete izračunati funkciju TREND(poznate_y-vrijednosti; poznate_x-vrijednosti) funkciju za ravnu liniju ili funkciju GROWTH(poznate_y-vrijednosti; poznate_x-vrijednosti) funkciju za eksponencijalnu krivulju. Ove funkcije, ako su izostavljene iz argumenta new_x_values, vraćaju niz izračunatih vrijednosti y za stvarne vrijednosti x prema ravnoj liniji ili krivulji. Zatim možete usporediti izračunate vrijednosti sa stvarnim vrijednostima. Također možete izraditi grafikone za vizualnu usporedbu.

Prilikom provođenja regresijske analize, Microsoft Excel izračunava, za svaku točku, kvadrat razlike između predviđene vrijednosti y i stvarne vrijednosti y. Zbroj tih kvadrata razlika naziva se rezidualni zbroj kvadrata (ssresid). Microsoft Excel zatim izračunava ukupan zbroj kvadrata (sstotal). Ako je const = TRUE ili ako ovaj argument nije naveden, ukupni zbroj kvadrata bit će jednak zbroju kvadrata razlika stvarnih y vrijednosti i srednjih vrijednosti y. Ako je const = FALSE, zbroj kvadrata će biti jednak zbroju kvadrata stvarnih vrijednosti y (bez oduzimanja srednje vrijednosti y od kvocijenta y). Nakon toga, regresijski zbroj kvadrata može se izračunati na sljedeći način: ssreg = sstotal - ssresid. Što je manji rezidualni zbroj kvadrata, to je veća vrijednost koeficijenta determinizma r2, što pokazuje koliko dobro jednadžba dobivena regresijskom analizom objašnjava odnose između varijabli. Koeficijent r2 jednak je ssreg/sstotal.

U nekim slučajevima, jedan ili više X stupaca (neka vrijednosti Y i X budu u stupcima) nemaju dodatnu prediktivnu vrijednost u drugim stupcima X. Drugim riječima, brisanje jednog ili više X stupaca može rezultirati Y vrijednostima izračunati s istom preciznošću. U ovom slučaju, redundantni X stupci bit će isključeni iz regresijskog modela. Taj se fenomen naziva "kolinearnost" jer se redundantni stupci X mogu predstaviti kao zbroj nekoliko neredundantnih stupaca. LINEST provjerava kolinearnost i uklanja sve suvišne X stupce iz regresijskog modela ako ih pronađe. Uklonjeni X stupci mogu se identificirati u LINEST izlazu faktorom 0 i se vrijednosti 0. Uklanjanjem jednog ili više stupaca kao suvišnih mijenja se vrijednost df jer ovisi o broju X stupaca koji se stvarno koriste u svrhe predviđanja. Pogledajte primjer 4 u nastavku za više detalja o izračunavanju df. Kada se df promijeni zbog uklanjanja suvišnih stupaca, mijenjaju se i vrijednosti sey i F. Često se ne preporučuje korištenje kolinearnosti. Međutim, treba ga koristiti ako neki X stupci sadrže 0 ili 1 kao indikator koji pokazuje je li subjekt eksperimenta u zasebnoj skupini. Ako je const = TRUE ili ako ovaj argument nije naveden, LINEST umeće dodatni X stupac za simulaciju točke presjeka. Ako postoji stupac s vrijednostima 1 za muškarce i 0 za žene, a postoji stupac s vrijednostima 1 za žene i 0 za muškarce, tada se posljednji stupac uklanja jer se njegove vrijednosti mogu dobiti iz stupac "muški indikator".

Izračun df za slučajeve kada X stupaca nije uklonjeno iz modela zbog kolinearnosti je kako slijedi: ako postoji k poznatih_x stupaca i const = TRUE ili nije navedeno, tada je df = n - k - 1. Ako je const = FALSE, tada je df = n -k. U oba slučaja, uklanjanje X stupaca zbog kolinearnosti povećava vrijednost df za 1.

Formule koje vraćaju nizove moraju se unijeti kao formule polja.

Prilikom unosa niza konstanti kao argumenta known_x_values, na primjer, koristite točku i zarez za odvajanje vrijednosti u istom retku i dvotočku za razdvajanje redaka. Znakovi za razdvajanje mogu se razlikovati ovisno o postavkama u prozoru "Jezik i standardi" na upravljačkoj ploči.

Imajte na umu da vrijednosti y predviđene regresijskom jednadžbom možda neće biti točne ako su izvan raspona vrijednosti y koje su korištene za definiranje jednadžbe.

Glavni algoritam koji se koristi u funkciji LINEST, razlikuje se od glavnog algoritma funkcija NAGIB I ODJELJAK. Razlike između algoritama mogu dovesti do različitih rezultata za nesigurne i kolinearne podatke. Na primjer, ako su podatkovne točke argumenta poznatog_y 0, a podatkovne točke argumenta poznatog_x 1, tada:

Funkcija LINEST vraća vrijednost jednaku 0. Algoritam funkcije LINEST koristi se za vraćanje prikladnih vrijednosti za kolinearne podatke, u kojem slučaju se može pronaći barem jedan odgovor.

Funkcije SLOPE i INTERCEPT vraćaju pogrešku #DIV/0!. Algoritam funkcija SLOPE i INTERCEPT koristi se za pronalaženje samo jednog odgovora, au ovom slučaju može ih biti nekoliko.

Uz izračun statistike za druge vrste regresije, LINEST se može koristiti za izračunavanje raspona za druge vrste regresije unosom funkcija varijabli x i y kao niza varijabli x i y za LINEST. Na primjer, sljedeća formula:

LINEST(y-vrijednosti, x-vrijednosti^COLUMN($A:$C))

radi s jednim stupcem vrijednosti Y i jednim stupcem vrijednosti X kako bi izračunao aproksimaciju kocke (polinom 3. stupnja) sljedećeg oblika:

Formula se može modificirati za izračunavanje drugih vrsta regresije, ali u nekim slučajevima su potrebne prilagodbe izlaznih vrijednosti i drugih statistika.

Po mom mišljenju, kao studentu, ekonometrija je jedna od najprimijenjenijih znanosti od svih s kojima sam se uspio upoznati u zidovima svog sveučilišta. Uz pomoć njega, doista, moguće je riješiti primijenjene probleme na razini poduzeća. Koliko će ta rješenja biti učinkovita, treće je pitanje. Zaključak je da će većina znanja ostati teorija, ali ekonometrija i regresijska analiza i dalje su vrijedni proučavanja s posebnom pažnjom.

Što objašnjava regresiju?

Prije nego počnemo razmatrati funkcije MS Excela koje nam omogućuju rješavanje ovih problema, želio bih vam na prste objasniti što, u biti, uključuje regresijsku analizu. Tako će vam biti lakše polagati ispit, a što je najvažnije, bit će zanimljivije proučavati predmet.

Nadamo se da ste upoznati s konceptom funkcije iz matematike. Funkcija je odnos između dvije varijable. Kada se jedna varijabla promijeni, nešto se događa drugoj. Mijenjamo X, odnosno Y mijenjamo. Funkcije opisuju različite zakone. Poznavajući funkciju, možemo zamijeniti proizvoljne vrijednosti za X i vidjeti kako se Y mijenja.

To je od velike važnosti, budući da je regresija pokušaj da se uz pomoć određene funkcije objasne naizgled nesustavni i kaotični procesi. Tako je, na primjer, moguće otkriti odnos između tečaja dolara i nezaposlenosti u Rusiji.

Ako se ovaj obrazac može otkriti, tada ćemo prema funkciji koju smo dobili tijekom izračuna moći napraviti prognozu kolika će biti stopa nezaposlenosti pri N-om tečaju dolara u odnosu na rublju.
Ovaj odnos će se zvati korelacija. Regresijska analiza uključuje izračun koeficijenta korelacije koji će objasniti čvrstoću odnosa između varijabli koje razmatramo (tečaj dolara i broj radnih mjesta).

Ovaj koeficijent može biti pozitivan ili negativan. Njegove vrijednosti kreću se od -1 do 1. Sukladno tome, možemo uočiti visoku negativnu ili pozitivnu korelaciju. Ako je pozitivan, onda će porast dolara biti praćen pojavom novih radnih mjesta. Ako je negativan, onda će porast tečaja biti praćen smanjenjem radnih mjesta.

Regresija je nekoliko vrsta. Može biti linearna, parabolična, eksponencijalna, eksponencijalna itd. Odabir modela vršimo ovisno o tome koja će regresija odgovarati konkretno našem slučaju, koji će model biti što bliži našoj korelaciji. Razmotrimo ga na primjeru problema i riješimo ga u MS Excelu.

Linearna regresija u MS Excelu

Za rješavanje problema linearne regresije potrebna vam je funkcija analize podataka. Možda vam nije omogućeno, pa ga morate aktivirati.

• Kliknite na gumb "Datoteka";
• Odaberite stavku "Opcije";
• Kliknite na pretposljednju karticu "Dodaci" na lijevoj strani;

• Ispod ćemo vidjeti natpis "Upravljanje" i gumb "Idi". Pritisnemo ga;
• Stavite kvačicu na "Paket analize";
• Pritisnemo "ok".

Primjer zadatka

Aktivirana je funkcija analize serije. Riješimo sljedeći problem. Imamo uzorak podataka za više godina o broju izvanrednih situacija na području poduzeća i broju zaposlenih radnika. Moramo identificirati odnos između ove dvije varijable. Postoji varijabla s objašnjenjem X, što je broj radnika, i varijabla s objašnjenjem, Y, što je broj hitnih slučajeva. Raspodijelimo početne podatke u dva stupca.

Idite na karticu "Podaci" i odaberite "Analiza podataka"

Odaberite "Regresija" s popisa koji se pojavi. U intervalima unosa Y i X odaberite odgovarajuće vrijednosti.

Pritisnemo "OK". Analiza je napravljena, a u novom listu ćemo vidjeti rezultate.

Najznačajnije vrijednosti za nas označene su na donjoj slici.

Višestruki R je koeficijent determinacije. Ima složenu formulu za izračun i pokazuje koliko možemo vjerovati našem koeficijentu korelacije. Sukladno tome, što je ta vrijednost veća, što je veće povjerenje, to je naš model u cjelini uspješniji.

Y-presjek i X1 presjek su koeficijenti naše regresije. Kao što je već spomenuto, regresija je funkcija i ima određene koeficijente. Dakle, naša funkcija će izgledati ovako: Y = 0,64 * X-2,84.

Što nam to daje? To nam daje priliku za predviđanje. Recimo da želimo zaposliti 25 radnika za poduzeće i trebamo otprilike zamisliti koliki će biti broj hitnih slučajeva. Tu vrijednost zamjenjujemo u našu funkciju i dobivamo rezultat Y = 0,64 * 25 - 2,84. Približno 13 izvanredno stanje će se dogoditi.

Pogledajmo kako to radi. Pogledajte sliku ispod. Stvarne vrijednosti za uključene zaposlenike zamjenjuju se u funkciju koju smo dobili. Pogledajte koliko su vrijednosti bliske stvarnim igračima.

Također možete izgraditi korelacijsko polje tako da označite područje y i x, kliknete na karticu "umetni" i odaberete dijagram raspršenja.

Točke su raštrkane, ali se općenito pomiču prema gore kao da je u sredini ravna linija. A ovu liniju možete dodati i tako da odete na karticu "Izgled" u MS Excelu i odaberete stavku "Linija trenda"

Dvaput kliknite na liniju koja se pojavi i vidjet ćete što je ranije rečeno. Možete promijeniti vrstu regresije ovisno o tome kako izgleda vaše korelacijsko polje.

Možda ćete otkriti da točke crtaju parabolu, a ne ravnu liniju, i možda bi vam bilo bolje da odaberete drugu vrstu regresije.

Zaključak

Nadamo se da vam je ovaj članak dao bolje razumijevanje što je regresijska analiza i čemu služi. Sve je to od velike praktične važnosti.