01.10.2015. linest statistika primjer - Statističke funkcije Excel

http://ic.ims.hr/office/excel2003/funkcije/statisticke/linest.html 1/4

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 Search This Web Site

Adsense sponzor

LINEST

Izračunava statistiku pravca pomoću metode najmanjih kvadrata, kako bi se izračunao pravac koji najbolje odgovara podacima, i prikazuje polje kojeopisuje 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, im 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 bilokojeg 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 jednogretka 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.

Statistika Opisse1;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).

r2Koeficijent 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šenojkorelaciji (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.

FF statistika, ili F-promatrana vrijednost. Koristite F statistiku kako bi odredili da li se promatrani odnosi između zavisnih i nezavisnih varijabli događajuslučajno.

dfStupnjevi slobode. Upotrebljavajte stupnjeve slobode za pomoć kod traženja kritičnih vrijednosti za F u statističkoj tablici. Usporedite vrijednosti koje nalaziteu 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 ovujednadž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)

Google oglasi ► Excel formula ► Excel 2007 ► Microsoft excel ► Excel office

01.10.2015. linest statistika primjer - Statističke funkcije Excel

http://ic.ims.hr/office/excel2003/funkcije/statisticke/linest.html 2/4

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čuniza 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šimpodacima. 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) 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 nagrafikonu 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 zbrojkvadrata je zbroj kvadrata trenutnih vrijdenosti y (bez oduzimanja prosječnih vrijednosti y od svake pojedinačne vrijednosti y). Regresijski zbrojkvadrata, ssreg, se može dobiti iz: ssreg = sstotal - ssresid. Što je manji rezidualni zbroj kvadrata, u usporedbi s ukupnim zbrojem kvadrata, to jeveća vrijednost koeficijenta determinacije, r2, koji je pokazivač koliko točno jednadžba nastala regresijskom analizom objašnjava odnos izmeđuvarijabli. 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 uprisutnosti drugih X stupaca. Drugim riječima, uklanjanjem jednog ili više X stupaca mogle bi se predvidjeti Y vrijednosti koje su jednako točne. Utom slučaju suvišne X stupce bi trebalo izostaviti iz regresijskog modela. Taj fenomen se zove "kolinearnost" jer se svaki suvušan X stupac 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 stupacaukloni 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 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 setakođ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 samonule i jedinice koji su pokazatelj pripada li ili ne subjekt eksperimenta određenoj grupi. Ako je const = TRUE ili izostavljena, LINEST umeće dodatni Xstupac 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 nijei 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 vrijednostimogu 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'si 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 kojeguklonimo 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 razdvajanjeredaka okomite crte. Znakovi razdjelnika mogu biti drugačiji, ovisno o regionalnim postavkama u regionalnim postavkama ili regionalnimodrednicama 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đivanjejednadžbe.

Primjer 1 Nagib i odsječak na osi Y

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 zatimpritisnite 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 nay 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.

01.10.2015. linest statistika primjer - Statističke funkcije Excel

http://ic.ims.hr/office/excel2003/funkcije/statisticke/linest.html 3/4

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 sformulom. 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 ikoja 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đunezavisnih 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čkaanaliza 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 vrijednostimau 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 je4,53. Budući da je F = 459,753674 puno viša od 4,53, malo je vjerojatno da se tako visokavrijednost F pojavila slučajno. (Uz Alpha = 0,05 hipotezu da nema odnosa između known_y's iknown_x's treba odbaciti kada F premaši kritičnu razinu, 4.53). Pomoću Excelove funkcije FDISTmož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

01.10.2015. linest statistika primjer - Statističke funkcije Excel

http://ic.ims.hr/office/excel2003/funkcije/statisticke/linest.html 4/4

izračunate pomoću Excelove funkcije FDIST, možete zaključiti da je regresijska jednadžbakorisna za predviđanje procijenjenih vrijednosti poslovnih zgrada na ovom području. Nezaboravite da je od važno upotrebljavati točne vrijednosti v1 i v2, koje ste izračunali uprethodnom odlomku.

Primjer 5 Izračunavanje t-statistike

Druga provjera pretpostavke će odrediti da li je svaki koeficijent nagiba koristan kod procjenevrijednosti imovine uredske zgrade u primjeru 3. Na primjer, kako bi provjerili statističkuznač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-promatranavrijednost:

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 koristaza procjenjivanje vrijednosti poslovne zgrade u primjeru 3. Donja tablica pokazuje apsolutnevrijednosti 4 t-promatrane vrijednosti.

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

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šteneu regresijskoj jednadžbi korisne pri predviđanju vrijednosti uredskih zgrada na tom području.

Adsense sponzor

Adsense sponzor

Adsense sponzor

.

COPYRIGHT - 2008 - IvanC - ic.ims.hr

Google oglasi

► Microsoft excel courses

► Ms office excel 2003

► Office excel on line

Start DownloadConvert Any File to a PDF. Get the Free From Doc to Pdf App!