Exceli statistilised funktsioonid: õpetus koos näidetega, kolmas osa

Excel pakub laias valikus statistilisi funktsioone, mis teostavad arvutusi alates keskmisest kuni keerukamate statistilise jaotuse ja trendijoone funktsioonideni.

Selles artiklis uurime põhjalikumalt Exceli statistilisi funktsioone trendijoonte funktsioonide arvutamiseks.

Pange tähele, et mõned statistilised funktsioonid võeti kasutusele Exceli viimastes versioonides ja seetõttu pole need vanemates versioonides saadaval.

Eeldatav lugemisaeg: 12 minutit

Trendijoone funktsioonid

Forecast

Exceli funktsioon Prognoos ennustab lineaarse trendijoone tulevase punkti, mis sobib antud x ja y väärtuste komplektiga.

süntaks

= FORECAST( x, known_y's, known_x's )

teemasid

• x: numbriline x-väärtus, mille jaoks soovite ennustada uut y-väärtust.
• known_y's: teadaolevate y väärtuste massiiv
• known_x's: teadaolevate x väärtuste massiiv

Pange tähele, et massiivi pikkus known_x peab olema sama, mis known_y ja dispersioon known_x see ei pea olema null.

näide

Järgmises arvutustabelis funktsioon FORECAST Excelit kasutatakse täiendava punkti ennustamiseks piki kõige paremini sobivat sirgjoont teadaolevate x- ja y-väärtuste seeria kaudu (salvestatud lahtritesse F2:F7 ja G2:G7).

Nagu on näidatud arvutustabeli lahtris F7, on funktsioon eeldatava y väärtuse arvutamiseks x=7 korral :=FORECAST( 7, G2:G7, F2:F7 )

See annab tulemuse 32.666667 .

Intercept

Exceli prognoosimisfunktsioonide hulgast leiame Intercept. Exceli funktsioon Intercept arvutab lineaarse regressioonijoone lõikepunkti (väärtuse y-telje lõikepunktis) antud x- ja y-väärtuste komplekti lõikes.

süntaks

= INTERCEPT( known_y's, known_x's )

teemasid

• known_y's: teadaolevate y väärtuste massiiv
• known_x's: teadaolevate x väärtuste massiiv

Pange tähele, et massiivi pikkus known_x peab olema sama, mis known_y ja dispersioon known_x see ei pea olema null.

näide

Järgmine arvutustabel näitab funktsiooni näidet Intercept Exceli arvutamiseks kasutatakse punkti, kus lineaarne regressioonijoon läbib known_x ja known_y (loetletud lahtrites F2:F7 ja G2:G7) lõikub y-teljega.

Le known_x ja known_y on joonistatud arvutustabelis olevale graafikule.

Nagu on näidatud arvutustabeli lahtris F9, on funktsiooni Intercept valem järgmine :=INTERCEPT( G2:G7, F2:F7 )

mis annab tulemuse 2.4 .

Slope

Teine väga huvitav ennustusfunktsioon on kalle (Slope) Excel arvutab lineaarse regressioonijoone kalde läbi antud x ja y väärtuste komplekti.

Funktsiooni süntaks on:

süntaks

= SLOPE( known_y's, known_x's )

teemasid

• known_y's: teadaolevate y väärtuste massiiv
• known_x's: teadaolevate x väärtuste massiiv

Pange tähele, et massiivi pikkus known_x peab olema sama, mis known_y ja dispersioon known_x see ei pea olema null.

näide

Järgmine arvutustabel näitab funktsiooni näidet Slope (kalle), mida kasutatakse läbi lineaarse regressioonijoone kalde arvutamiseks known_x ja known_y, lahtrites F2:F7 ja G2:G7.

Le known_x ja known_y on joonistatud arvutustabelis olevale graafikule.

Nagu on näidatud arvutustabeli lahtris F9, on funktsiooni Intercept valem järgmine :=SLOPE( G2:G7, F2:F7 )

mis annab tulemuse 4.628571429.

Trend

Väga huvitav Exceli prognoosifunktsioon on TREND Excel (Trend) arvutab lineaarse trendijoone läbi antud y väärtuste komplekti ja (valikuliselt) antud x väärtuste komplekti.

Seejärel laiendab funktsioon lineaarset trendijoont, et arvutada täiendavad y väärtused täiendavale x-väärtuste komplektile.

Funktsiooni süntaks on:

süntaks

= TREND( known_y's, [known_x's], [new_x's], [const] )

teemasid

• known_y's: teadaolevate y väärtuste massiiv
• [known_x's]: üks või mitu teadaolevate x väärtuste massiivi. See on valikuline argument, mis, kui see on esitatud, peaks olema sama pikk kui komplekt known_y's. Kui see on välja jäetud, siis komplekt [known_x's] saab väärtuse {1, 2, 3, …}.
• [uued_x-d]: valikuline argument, mis pakub ühte või mitut arvväärtuste massiivi, mis esindavad uute x-väärtuste komplekti, mille jaoks soovite arvutada vastavad uued y-väärtused. Iga massiiv [uus_x] peaks vastama massiivile [known_x's]. Kui argument [uus_x] jäetakse välja, see on võrdne [known_x's] .
• [kulu]: valikuline loogiline argument, mis täpsustab, kas lineaarvõrrandis on konstant „b”. y = m x + b , peab olema võrdne nulliga. Ise [hind] on TRUE (või kui see argument on välja jäetud), käsitletakse konstanti b normaalselt;
• Ise [hind] on VÄÄR, konstandi b väärtuseks määratakse 0 ja sirgjoone võrrand muutub y = mx .

näide

Järgmises tabelis kasutatakse Exceli Trend funktsiooni, et laiendada rida x ja y väärtusi, mis asuvad sirgel y = 2x + 10. Teadaolevad x ja y väärtused salvestatakse lahtritesse A2-B5 arvutustabelit ja kuvatakse ka arvutustabeli graafikul.

Pange tähele, et ei ole oluline, et antud punktid sobiksid täpselt piki sirget y = 2x + 10 (kuigi selles näites sobivad). Exceli funktsioon Trend leiab teie esitatud väärtuste jaoks kõige paremini sobiva rea.

Funktsioon Trend kasutab kõige sobivama joone leidmiseks vähimruutude meetodit ja seejärel uute y väärtuste arvutamiseks esitatud uutele x väärtustele.

Selles näites on [uus_x] on salvestatud lahtritesse A8–A10 ja uute vastavate y väärtuste leidmiseks on lahtrites B8–B10 kasutatud Exceli funktsiooni Trend. Nagu valemiribal näidatud, on valem := TREND( B2:B5, A2:A5, A8:A10)

Näete, et valemiriba funktsioon Trend on sulgudes { }. See näitab, et funktsioon sisestati kui massiivi valem .

Growth

Exceli prognoosimisfunktsioonide hulgast leiame Growth. Funktsioon Growth Excel arvutab eksponentsiaalse kasvukõvera antud y väärtuste komplekti ja (valikuline) ühe või mitme x väärtuste komplekti kaudu. Funktsioon laiendab seejärel kõverat, et arvutada täiendavad y väärtused täiendavale uutele x väärtustele.

Funktsiooni süntaks on:

süntaks

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

teemasid

• known_y's: teadaolevate y väärtuste massiiv
• [known_x's]: üks või mitu teadaolevate x väärtuste massiivi. See on valikuline argument, mis, kui see on esitatud, peaks olema sama pikk kui komplekt known_y's. Kui see on välja jäetud, siis komplekt [known_x's] saab väärtuse {1, 2, 3, …}.
• [uued_x-d]: uute x väärtuste komplekt, millele funktsioon arvutab vastavad uued y väärtused. Kui see jäetakse välja, eeldatakse, et hulk [uus_x] on võrdne [known_x's] ja funktsioon tagastab y väärtused, mis asuvad arvutatud eksponentsiaalse kasvu kõveral.
• [kulu]: valikuline loogiline argument, mis täpsustab, kas lineaarvõrrandis on konstant „b”. y = b * m^x , peab olema võrdne 1-ga. Kui [hind] on TRUE (või kui see argument on välja jäetud), käsitletakse konstanti b normaalselt; Ise [hind] on VÄÄR, konstandi b väärtuseks määratakse 1 ja sirgjoone võrrand muutub y = mx .

näide

Järgmises arvutustabelis kasutatakse Exceli kasvufunktsiooni, et laiendada x-i ja y-väärtuste seeriat, mis asuvad eksponentsiaalsel kasvukõveral y = 5 * 2^x. Need salvestatakse arvutustabeli lahtritesse A2–B5 ja kuvatakse ka arvutustabeli diagrammil.

Kasvufunktsioon arvutab eksponentsiaalse kasvukõvera, mis sobib kõige paremini teadaolevate x ja y väärtustega. Selles lihtsas näites on kõige sobivam kõver eksponentsiaalkõver y = 5 * 2^x.

Kui Excel arvutab eksponentsiaalse kasvukõvera võrrandi, saab ta seda kasutada uute y väärtuste arvutamiseks lahtrites A8-A10 esitatud uute x väärtuste jaoks.

Selles näites on [new_x's] on salvestatud lahtritesse A8-A10 ja funktsiooni Growth Excel on sisestatud lahtritesse B8-B10. Nagu valemiribal näidatud, on selle valem:=Growth( B2:B5, A2:A5, A8:A10 )

Näete, et kasvufunktsioon valemiribal on sulgudes { }. See näitab, et funktsioon sisestati kui massiivi valem .

Pange tähele, et kuigi ülaltoodud näite punktid sobivad täpselt piki kõverat y = 5 * 2^x, ei ole see oluline. Funktsioon Growth Excel leiab teie esitatud väärtuste jaoks kõige paremini sobiva kõvera.

Finantsfunktsioonid

Mõju

Funktsioon Effect Excel tagastab tegeliku aastase intressimäära antud nominaalintressimäära ja teatud arvu liitperioodide kohta aastas.

Efektiivne aastane intressimäär

Efektiivne aastane intressimäär on intressi mõõt, mis hõlmab intressikapitalisatsiooni ja mida kasutatakse sageli erinevate kapitalisatsioonitingimustega finantslaenude võrdlemiseks.

Efektiivne aastane intressimäär arvutatakse järgmise võrrandi abil:

tuvi nominal_rate on nominaalne intressimäär e npery on liitmisperioodide arv aastas.

Funktsiooni süntaks on:

süntaks

= EFFECT( nominal_rate, npery )

teemasid

• nominal_rate: nominaalne intressimäär (peab olema numbriline väärtus vahemikus 0 kuni 1)
• npery: liitmisperioodide arv aastas (peab olema positiivne täisarv).

näide

Järgmisel tabelis on kolm näidet Exceli efekti funktsioonist.

Kui funktsiooni tulemus Effect kuvatakse kümnendkohana või 0%, mõlemad probleemid on tõenäoliselt tingitud funktsiooni sisaldava lahtri vormingust Effect.

Seetõttu saab probleemi lahendada, vormindades lahtri protsentides, kümnendkohtadega.

Selleks tehke järgmist.

1. Valige lahtrid, mida vormindada protsentides.
2. Avage dialoogiboks "Vorminda lahtrid", kasutades ühte järgmistest meetoditest.
   • Paremklõpsake valitud lahtrit või vahemikku ja valige suvand Lahtrite vormindamine… kontekstimenüüst;
   • Klõpsake vahekaardi numbrite rühmas dialoogiboksi käivitajat Avaleht Exceli lint;
   • Kasutage klaviatuuri otseteed CTRL-1 (st valige CTRL-klahv ja hoidke seda all, valige klahv "1" (üks)).
3. Dialoogiboksis "Vorminda lahtreid" tehke järgmist.
   • Veenduge, et kaart number dialoogiboksi ülaosas on valitud.
   • valima Protsentuaalne nimekirjast Kategooria dialoogiboksi vasakus servas .See kuvab märkeruudu paremas servas lisavalikud, mis võimaldavad teil valida kuvatavate kümnendkohtade arvu.
   • Kui olete valinud kuvatavate kümnendkohtade arvu, klõpsake nuppu OK .

Nominal

Funktsioon Nominal Excel tagastab nominaalse intressimäära antud efektiivse intressimäära ja teatud arvu liitperioodide kohta aastas.

Funktsiooni süntaks on:

süntaks

= NOMINAL( effect_rate, npery )

teemasid

• effect_rate: efektiivne intressimäär (numbriline väärtus vahemikus 0 kuni 1).
• npery: liitmisperioodide arv aastas (peab olema positiivne täisarv).

näide

Järgmises arvutustabelis funktsioon Nominal Exceli abil arvutatakse kolme erineva tähtajaga laenu nominaalne intressimäär.

Ercole Palmeri