IZBORNIK HOME FORUM ACCESS EXCEL WORD KAZALO
 

HOME

1 AVEDEV
2 AVERAGE
3 AVERAGEA
4 BETADIST
5 BETAINV
6 BINOMDIST
7 CHIDIST
8 CHIINV
9 CHITEST
10 CONFIDENCE
11 CORREL
12 COUNT
13 COUNTA
14 COUNTBLANK
15 COUNTIF
16 COVAR
17 CRITBINOM
18 DEVSQ
19 EXPONDIST
20 FDIST
21 FINV
22 FISHER
23 FISHERINV
24 FORECAST
25 FREQUENCY
26 FTEST
27 GAMMADIST
28 GAMMAINV
29 GAMMALN
30 GEOMEAN
31 GROWTH
32 HARMEAN
33 HYPGEOMDIST
34 INTERCEPT
35 KURT
36 LARGE
37 LINEST
38 LOGEST
39 LOGINV
40 LOGNORMDIST
41 MAX
42 MAXA
43 MEDIAN
44 MIN
45 MINA
46 MODE
47 NEGBINOMDIST
48 NORMDIST
49 NORMINV
50 NORMSDIST
51 NORMSINV
52 PEARSON
53 PERCENTILE
54 PERCENTRANK
55 PERMUT
56 POISSON
57 PROB
58 QUARTILE
59 RANK
60 RSQ
61 SKEW
62 SLOPE
63 SMALL
64 STANDARDIZE
65 STDEV
66 STDEVA
67 STDEVP
68 STDEVPA
69 TDIST
70 TINV
71 TREND
72 TRIMMEAN
73 TTEST
74 VAR
75 VARA
76 VARP
77 VARPA
78 WEIBULL
79 ZTEST
   
  COPYRIGHT - 2006 - IC
   
  .

MICROSOFT EXCEL

STATISTIČKE FUNKCIJE U  MS EXCEL-u 2003






LINEST

Izračunava statistiku pravca pomoću metode najmanjih kvadrata, kako bi se izračunao pravac koji najbolje odgovara podacima, i prikazuje polje koje opisuje pravac. Budući da funkcija prikazuje polje vrijednosti, mora se unijeti kao formula polja.

Jednadžba pravca je:

y = mx + b ili

y = m1x1 + m2x2 + ... + b (ako postoji više raspona vrijednosti x)

gdje je zavisna varijabla y funkcija nezavisnih varijabli x. Vrijednosti m su odgovarajući koeficijenti za svaku vrijednost x, a b je konstanta. Uočite da y, x, i m mogu biti vektori. Polje koje LINEST vraća je {mn;mn-1;...;m1;b}. LINEST može također vratiti dodatnu regresijsku statistiku.

Sintaksa

LINEST(known_y's;known_x's;const;stats)

Known_y's    je skup y-values koje već znate u odnosu y = mx + b.

  • Ako je polje known_y's u jednom stupcu, svaki se stupac od known_x's interpretira kao zasebna varijabla.
  • Ako je polje known_y's u jednom retku, svaki se redak od known_x's interpretira kao zasebna varijabla.

Known_x's    je mogući skup x-vrijednosti koje već znate u odnosu y = mx + b.

  • Polje known_x's može uključiti jedan ili više skupova varijabli. Ako je korištena samo jedna varijabla, known_y's i known_x's mogu biti rasponi bilo kojeg oblika, sve dok imaju jednake dimenzije. Ako je korišteno više od jedne varijable, known_y's mora biti vektor (tj. raspon s visinom jednog retka ili širinom jednog stupca).
  • Ako je known_x's ispušten, pretpostavlja se da je polje {1;2;3;...}, koje je iste veličine kao i known_y's.

Const    je logička vrijednost koja određuje je li konstanta b jednaka 0.

  • Ako je const TRUE ili ispuštena, b je izračunat normalno.
  • Ako je const FALSE, b se postavlja na 0 a vrijednosti m se prilagođuju tako da bude y = mx.

Stats    je logička vrijednost, koja navodi da li će se kao rezultat vratiti dodatne statistike regresije.

  • Ako je stats TRUE, LINEST vraća dodatnu regresijsku statistiku, tako da je vraćeno polje {mn;mn-1;...;m1;b|sen;sen-1;...;se1;seb|r2;sey|F;df|ssreg;ssresid}.
  • Ako je stats FALSE ili izostavljen, LINEST vraća samo koeficijent m i konstantu b.

Dodatna regresijska statistika kako slijedi.

NAPOMENA: Kod ove funkcije postoje neke situacije i mogućnosti da Vam ne radi određeni izraz formule pa je potrebno napraviti preinake u slučaju da imate niz.

npr: =LINEST(B1:B13;C1:C13^{1,2,3,4,5,6}).
Da bi formula funkcionirala potrebno je umjesto zareza staviti kosu crtu u lijevo ili obrnuta kosa crta (backslash ili '\')
Tako da slijedi ARRAY formula =LINEST(B1:B13;C1:C13^{1\2\3\4\5\6}).

Statistika Opis
se1;se2;...;sen Vrijednosti standardne pogreške za koeficijente m1;m2;...;mn.
seb Vrijednost standardne pogreške za konstantu b (seb = #N/D kada je konst FALSE).
r2 Koeficijent determinacije. Uspoređuje procijenjene i stvarne vrijednosti y, i poprima vrijednost u intervalu od 0 do 1. Ako je 1, tada se radi o savršenoj korelaciji (povezanosti) u uzorku — nema razlike između procijenjene i stvarne y vrijednosti. Kao druga suprotnost, ako je koeficijent determinacije 0, regresijska jednadžba nije pogodna za predviđanje y vrijednosti. Za informaciju o tome kako se računa r2, pogledajte “Napomene” kasnije u ovoj temi.
sey Standardna pogreška za procijenjenu y vrijednost.
F F statistika, ili F-promatrana vrijednost. Koristite F statistiku kako bi odredili da li se promatrani odnosi između zavisnih i nezavisnih varijabli događaju slučajno.
df Stupnjevi slobode. Upotrebljavajte stupnjeve slobode za pomoć kod traženja kritičnih vrijednosti za F u statističkoj tablici. Usporedite vrijednosti koje nalazite u tablici s F statistikom koju vraća funkcija LINEST, kako bi odredili razinu pouzdanosti modela. Ako želite saznati kako se izračunava df, pogledajte "Napomene" u ovoj temi. U primjer 4 dolje prikazana je upotreba F i df.
ssreg Regresijski zbroj kvadrata.
ssresid Rezidualni zbroj kvadrata. Ako želite saznati kako se izračunavaju ssreg i ssresid, pogledajte "Napomene" u ovoj temi.

Sljedeći prikazi pokazuju red kojim se vraća dodatna regresijska statistika.

Napomene

  • Neki pravac možete opisati pomoću nagiba i odsječka na osi y:

    Nagib (m):
    Kako bi našli nagib pravca, označen kao m, uzmite dvije točke na pravcu, (x1,y1) i (x2,y2); nagib je jednak (y2 - y1)/(x2 - x1).

    Odsječak na osi y(b):
    Odsječak na osi y pravca, označen kao b, je vrijednost y u točki gdje pravac presijeca os y.

    Jednadžba pravca je y = mx + b. Kada znate vrijednosti m i b, možete izračunati svaku točku na pravcu uvrštavanjem vrijednosti y ili x u ovu jednadžbu. Možete koristiti i funkciju TREND.

  • Kada imate samo jednu nezavisnu varijablu x, možete odrediti vrijednosti nagiba i odsječka na osi y izravno pomoću sljedećih formula:

    Nagib:
    =INDEX(LINEST(poznati_y-i,poznati_x-ovi),1)

    Odsječak na osi y:
    INDEX(LINEST(poznati_y-i,poznati_x-ovi),2)

  • Točnost pravca izračunatog pomoću LINEST ovisi o stupnju raspršenja vaših podataka. Što su podaci više linearni, to je i model LINEST točniji. LINEST koristi metodu najmanjih kvadrata za određivanje najbolje prilagođenosti podacima. Kada imate samo jednu nezavisnu varijablu x, izračuni za m i b temelje se na sljedećim formulama:

    gdje su x i y srednje vrijednosti uzorka, tj. x = AVERAGE(known x's) and y = AVERAGE(known_y's).

  • Funkcije LINEST i LOGEST za prilagođavanje pravcu ili krivulji mogu izračunati najbolji pravac ili eksponencijalnu krivulju koja odgovara vašim podacima. Međutim, morate odlučiti koji od dva rezultata više odgovara vašim podacima. Možete izračunati TREND(known_y's;known_x's) za pravac, ili GROWTH(known_y's;known_x's) za eksponencijalnu krivulju. Ove funkcije, bez argumenata new_x's, vraćaju polje y-vrijednosti predviđenih uzduž pravca ili krivulje prema vašim podacima. Tada možete usporediti predviđene vrijednosti s trenutnim vrijednostima. Možete ih obje iscrtati na grafikonu kako bi ih vizualno usporedili.
  • U regresijskoj analizi, Microsoft Excel za svaku točku računa kvadrat razlike između procijenjene vrijednosti y za tu točku i trenutne vrijednosti y. Zbroj kvadrata ovih razlika zove se rezidualni zbroj kvadrata, ssresid. Microsoft Excel tada računa ukupni zbroj kvadrata, ssstotal. Kada je const = TRUE ili je ispuštena, ukupni zbroj kvadrata je zbroj razlika između trenutne vrijednosti y i prosjeka vrijednosti y. Kada je const = FALSE ukupni zbroj kvadrata je zbroj kvadrata trenutnih vrijdenosti y (bez oduzimanja prosječnih vrijednosti y od svake pojedinačne vrijednosti y). Regresijski zbroj kvadrata, ssreg, se može dobiti iz: ssreg = sstotal - ssresid. Što je manji rezidualni zbroj kvadrata, u usporedbi s ukupnim zbrojem kvadrata, to je veća vrijednost koeficijenta determinacije, r2, koji je pokazivač koliko točno jednadžba nastala regresijskom analizom objašnjava odnos između varijabli. r2 je jednak ssreg/sstotal.
  • U nekim slučajevima jedan ili više X stupaca (pretpostavimo da su vrijednosti Y i X u stupcima) možda nemaju dodatnu vrijednost predviđanja u prisutnosti drugih X stupaca. Drugim riječima, uklanjanjem jednog ili više X stupaca mogle bi se predvidjeti Y vrijednosti koje su jednako točne. U tom slučaju suvišne X stupce bi trebalo izostaviti iz regresijskog modela. Taj fenomen se zove "kolinearnost" jer se svaki suvušan X stupac može izraziti kao zbroj više potrebnih X stupaca. LINEST provjerava kolinearnost i nakon što prepozna suvišne X stupce, uklanja ih iz regresijskog modela. Uklonjeni X stupci se mogu prepoznati u LINEST izlaznoj tablici kao one koje imaju koeficijente i vrijednosti "se" 0. Ako se jedan ili više stupaca ukloni kao suvišan, promijenit će se vrijednost df zato što df ovisi o broju X stupaca koji se upotrebljava za predviđanje. Ako želite saznati više o izračunavanju vrijednosti df, pogledajte primjer 4. Ako se vrijednost df promijeni zato što su uklonjeni suvišni X stupci, vrijednosti sey i F će se također promijeniti. Kolinearnost je relativno rijetka u praksi. Ipak postoji slučaj u kojem je upotreba češća, a to je kada neki X stupci sadrže samo nule i jedinice koji su pokazatelj pripada li ili ne subjekt eksperimenta određenoj grupi. Ako je const = TRUE ili izostavljena, LINEST umeće dodatni X stupac sa samim jedinicama da bi modelirao presjek. Ako imate stupac u kojem vrijednost 1 znači da je subjekt muškog spola, a vrijednost 0 da nije i još jedan stupac u kojem vrijednost 1 znači da je subjekt ženskog spola, a vrijednost 0 da nije, drugi stupac je suvišan, jer se njegove vrijednosti mogu dobiti tako da oduzmemo unos u stupcu "muškarci" od svih jedinica u stupcu koji je dodala funkcija LINEST.
  • Ako se zbog kolinearnosti iz modela ne ukloni niti jedan X stupac, vrijednost df se izračunava ovako: ako postoje k stupci s vrijednostima known_x's i const = TRUE ili izostavljenom, onda je df = n - k - 1. Ako je const = FALSE, onda je df = n - k. U oba slučaja vrijedi da se za svaki stupac kojeg uklonimo zbog kolinearnosti, df poveća za 1.
  • Formule koje vraćaju polje moraju biti unesene kao formula polja.
  • Kada za argument unosite polje konstanti poput known_x's, za razdvajanje vrijednosti u istom retku koristite točka-zareze, a za razdvajanje redaka okomite crte. Znakovi razdjelnika mogu biti drugačiji, ovisno o regionalnim postavkama u regionalnim postavkama ili regionalnim odrednicama u upravljačkoj ploči.
  • Uočite da y-vrijednosti predviđene regresijskom jednadžbom mogu biti nevaljane ako su izvan raspona y-vrijednosti korištenih za određivanje jednadžbe.

Primjer 1   Nagib i odsječak na osi Y

LINEST Izračunava statistiku pravca pomoću metode najmanjih kvadrata

Napomena  Formula u ovom primjeru mora se unijeti kao formula polja. Označite raspon A7:B7 počevši od ćelije s formulom. Pritisnite tipku F2 a zatim pritisnite tipke CTRL+SHIFT+ENTER. Ako se formula ne unese kao formula polja, jedini rezultati bit će 2.

Kada se unese kao polje, prikazat će se nagib (2) i odsječak na osi y (1).

Primjer 2   Jednostavna linearna regresija

Općenito, SUM({m;b}*{x;1}) jednako mx + b, procijenjena y-vrijednost za zadanu x-vrijednost. Također možete koristiti funkciju TREND.

Primjer 3   Višestruka linearna regresija

Pretpostavimo da je jedan graditelj odlučio kupiti skupinu malih uredskih zgrada u poslovnoj četvrti.

Graditelj može koristiti višestruku linearnu regresijsku analizu kako bi procijenio vrijednost zgrade na tom području temeljenu na sljedećim varijablama.

Varijabla Odnosi se na
y Vrijednost imovine uredske zgrade
x1 Površina prostora u kvadratima
x2 Broj ureda
x3 Broj ulaza
x4 Starost zgrade u godinama

Ovaj primjer pretpostavlja da postoji linearni odnos između svake nezavisne varijable (x1, x2, x3, i x4) i zavisne varijable (y), vrijednosti uredskih zgrada u četvrti.

Graditelj slučajno odabire uzorak od 11 uredskih zgrada od mogućih 1.500 i dobiva sljedeće podatke. "Pola ulaza" znači ulaz samo za dostavu.

Napomena  Formula u ovom primjeru mora se unijeti kao formula polja. Nakon kopiranja primjera na radni list, označite raspon A14:E18 počevši od ćelije s formulom. Pritisnite tipku F2 a zatim pritisnite tipke CTRL+SHIFT+ENTER. Ako se formula ne unese kao formula polja, jedini rezultati bit će -234,2371645.

Kada se unese kao polje, prikazat će se sljedeća statistika regresije. Ovim ključem odredite željenu statistiku.

Jednadžba višestruke regresije, y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b, sada može biti poznata ako se u nju uvrste vrijednosti iz 14. retka:

y = 27,64*x1 + 12 530*x2 + 2 553x3+ 234,24*x4 + 52 318

Graditelj sada može procijeniti vrijednost imovine uredske zgrade na istom području koja ima površinu 2.500 kvadratnih metara, tri ureda, i dva ulaza i koja je stara 25 godina, pomoću sljedeće jednadžbe:

y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158 261 Kn

Ili možete kopirati sljedeću tablicu u ćeliju A21 ogledne radne knjige.

Možete također koristiti funkciju TREND kako bi izračunali ovu vrijednost.

Primjer 4   Upotreba F i r2 statistike

U prethodnom primjeru, koeficijent determinacije, ili r2, je 0,99675 (pogledajte ćeliju A17 u izlazu za LINEST), što ukazuje na čvrsti odnos između nezavisnih varijabli i prodajne cijene. Možete koristiti F statistiku kako biste utvrdili jesu li se ovi rezultati, uz tako visoku vrijednost r2, dogodili slučajno.

Pretpostavimo na trenutak da u stvari nema odnosa između varijabli, ali ste izvukli rijedak uzorak 11 uredskih zgrada koji je prouzročio da je statistička analiza pokazala tako ustaljen odnos. Pojam "Alfa" koristi se za vjerojatnost netočnosti u zaključku o postojanju odnosa.

F i df u LINEST izlazu se mogu upotrijebiti da se procijeni vjerojatnost da se viša F vrijednost pojavi slučajno. F se može usporediti s kritičnim vrijednostima u objavljenim tablicama F-raspodjele ili možete upotrijebiti Excelov FDIST da biste izračunali vjerojatnost da se slučajno pojavi viša F vrijednost. Odgovarajuća F raspodjela ima v1 i v2 stupnjeve slobode. Ako je n broj točaka podataka, a const = TRUE ili izostavljena, onda je v1 = n - df - 1, a v2 = df. (Ako je const = FALSE onda je v1 = n – df and v2 = df.) Excelov FDIST (F,v1,v2) vraća vjerojatnost da će se slučajno pojaviti viša F vrijednost. U primjeru 4, df = 6 (ćelija B18), a F = 459,753674 (ćelija A18)

Uz pretpostavku da je vrijednost Alpha 0,05 v1 = 11 – 6 – 1 = 4, a v2 = 6, kritična razina F je 4,53. Budući da je F = 459,753674 puno viša od 4,53, malo je vjerojatno da se tako visoka vrijednost F pojavila slučajno. (Uz Alpha = 0,05 hipotezu da nema odnosa između known_y's i known_x's treba odbaciti kada F premaši kritičnu razinu, 4.53). Pomoću Excelove funkcije FDIST možete dobiti vjerojatnost da se tako visoka F vrijednost pojavila slučajno. FDIST(459,753674, 4, 6) = 1,37E-7, što je izuzetno mala vjerojatnost. Ako nađete kritičnu razinu od F u tablici ili je izračunate pomoću Excelove funkcije FDIST, možete zaključiti da je regresijska jednadžba korisna za predviđanje procijenjenih vrijednosti poslovnih zgrada na ovom području. Ne zaboravite da je od važno upotrebljavati točne vrijednosti v1 i v2, koje ste izračunali u prethodnom odlomku.

Primjer 5   Izračunavanje t-statistike

Druga provjera pretpostavke će odrediti da li je svaki koeficijent nagiba koristan kod procjene vrijednosti imovine uredske zgrade u primjeru 3. Na primjer, kako bi provjerili statističku značajnost koeficijenta starosti zgrade, podijelite -234,24 (koeficijent nagiba starosti) s 13,268 (procijenjena standardna pogreška koeficijenta starosti zgrade u ćeliji A15). Slijedi t-promatrana vrijednost:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Ako je apsolutna vrijednost t dovoljno visoka, može se zaključiti da je koeficijent nagiba korista za procjenjivanje vrijednosti poslovne zgrade u primjeru 3. Donja tablica pokazuje apsolutne vrijednosti 4 t-promatrane vrijednosti.

Ako se služite tablicom u statističkom priručniku, naći ćete da je t-kritična vrijednost, za two tailed pojedinačni test, uz 6 stupnjeva slobode i Alfa = 0,05, jednaka 2,447. Ta kritična vrijednost se može dobiti i pomoću Excelove TINV funkcije. TINV(0,05,6) = 2,447. Kako je apsolutna vrijednost od t, 17,7, veća od 2,447, starost je važna varijabla kod procjene vrijednosti imovine uredske zgrade. Svakoj se od preostalih nezavisnih varijabli može provjeriti statistička značajnost na sličan način. Slijede t-promatrane vrijednosti za svaku nezavisnu varijablu.

Varijabla t-promatrana vrijednost
Površina prostora 5,1
Broj ureda 31,3
Broj ulaza 4,8
Starost 17,7

Sve ove vrijednosti imaju apsolutnu vrijednost veću od 2,447; stoga, sve su varijable korištene u regresijskoj jednadžbi korisne pri predviđanju vrijednosti uredskih zgrada na tom području.








.

Ova web stranica koristi COOKIES - COPYRIGHT 2008 - 2021 - IvanC - ic.ims.hr