„Excel“ statistinės funkcijos: pamoka su pavyzdžiais, trečia dalis

„Excel“ siūlo platų statistinių funkcijų spektrą, kurios atlieka skaičiavimus nuo vidurkio iki sudėtingesnių statistinio pasiskirstymo ir tendencijų linijos funkcijų.

Šiame straipsnyje mes gilinsimės į „Excel“ statistines funkcijas, skirtas skaičiuoti tendencijų linijos funkcijas.

Atminkite, kad kai kurios statistinės funkcijos buvo pateiktos naujausiose „Excel“ versijose, todėl jos nepasiekiamos senesnėse versijose.

Numatomas skaitymo laikas: 12 minuti

Trendline funkcijos

Forecast

„Excel“ prognozės funkcija numato būsimą linijinės tendencijos linijos tašką, atitinkantį nurodytą x ir y reikšmių rinkinį.

sintaksė

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

dalykų

• x: skaitmeninė x reikšmė, kuriai norite numatyti naują y reikšmę.
• known_y's: žinomų y reikšmių masyvas
• known_x's: žinomų x reikšmių masyvas

Atkreipkite dėmesį, kad masyvo ilgis known_x turi būti toks pat kaip ir known_y ir dispersija known_x jis neturi būti nulis.

pavyzdys

Šioje skaičiuoklėje funkcija FORECAST „Excel“ naudojama norint numatyti papildomą tašką išilgai geriausiai tinkančios tiesios linijos per žinomų x ir y reikšmių serijas (saugomas langeliuose F2:F7 ir G2:G7).

Kaip parodyta skaičiuoklės langelyje F7, funkcija, skirta apskaičiuoti numatomą y reikšmę, kai x=7 yra :=FORECAST( 7, G2:G7, F2:F7 )

Tai duoda rezultatą 32.666667 .

Intercept

Tarp „Excel“ prognozavimo funkcijų randame Intercept. „Excel“ pertraukos funkcija apskaičiuoja linijinės regresijos linijos pertrauką (vertę y ašies sankirtoje) per nurodytą x ir y reikšmių rinkinį.

sintaksė

= INTERCEPT( known_y's, known_x's )

dalykų

• known_y's: žinomų y reikšmių masyvas
• known_x's: žinomų x reikšmių masyvas

Atkreipkite dėmesį, kad masyvo ilgis known_x turi būti toks pat kaip ir known_y ir dispersija known_x jis neturi būti nulis.

pavyzdys

Šioje skaičiuoklėje pateikiamas funkcijos pavyzdys Intercept „Excel“ naudojamas taškui, kuriame tiesinės regresijos linija eina per known_x ir known_y (išvardytos langeliuose F2:F7 ir G2:G7) kerta y ašį.

Le known_x ir known_y yra pavaizduoti skaičiuoklėje esančiame grafike.

Kaip parodyta skaičiuoklės langelyje F9, funkcijos Intercept formulė yra :=INTERCEPT( G2:G7, F2:F7 )

kuris duoda rezultatą 2.4 .

Slope

Kita labai įdomi prognozavimo funkcija yra nuolydis (Slope) „Excel“ apskaičiuoja tiesinės regresijos linijos nuolydį per nurodytą x ir y reikšmių rinkinį.

Funkcijos sintaksė yra tokia:

sintaksė

= SLOPE( known_y's, known_x's )

dalykų

• known_y's: žinomų y reikšmių masyvas
• known_x's: žinomų x reikšmių masyvas

Atkreipkite dėmesį, kad masyvo ilgis known_x turi būti toks pat kaip ir known_y ir dispersija known_x jis neturi būti nulis.

pavyzdys

Šioje skaičiuoklėje pateikiamas funkcijos pavyzdys Slope (nuolydis) programoje „Excel“, naudojama tiesinės regresijos linijos nuolydžiui apskaičiuoti known_x ir known_y, langeliuose F2:F7 ir G2:G7.

Le known_x ir known_y yra pavaizduoti skaičiuoklėje esančiame grafike.

Kaip parodyta skaičiuoklės langelyje F9, funkcijos Intercept formulė yra :=SLOPE( G2:G7, F2:F7 )

kuris duoda rezultatą 4.628571429.

Trend

Labai įdomi „Excel“ prognozės funkcija yra TREND „Excel“ (tendencija) apskaičiuoja tiesinę tendencijos liniją per nurodytą y reikšmių rinkinį ir (pasirinktinai) nurodytą x reikšmių rinkinį.

Tada funkcija išplečia tiesinę tendencijos liniją, kad apskaičiuotų papildomas y vertes papildomam naujų x reikšmių rinkiniui.

Funkcijos sintaksė yra tokia:

sintaksė

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

dalykų

• known_y's: žinomų y reikšmių masyvas
• [known_x's]: vienas ar daugiau žinomų x reikšmių masyvų. Tai yra neprivalomas argumentas, kuris, jei pateikiamas, turi būti tokio pat ilgio kaip ir rinkinys known_y's. Jei praleista, rinkinys [known_x's] įgauna reikšmę {1, 2, 3, …}.
• [nauji_x]: pasirenkamas argumentas, suteikiantis vieną ar daugiau skaitinių reikšmių masyvų, atspindinčių naujų x reikšmių rinkinį, kuriam norite apskaičiuoti atitinkamas naujas y reikšmes. Kiekvienas [nauji_x] turėtų atitikti masyvą [known_x's]. Jei argumentas [nauji_x] yra praleistas, jis yra lygus [known_x's] .
• [kaina]: pasirenkamas loginis argumentas, nurodantis, ar konstanta „b“ tiesinėje lygtyje y = m x + b , turi būti priverstinai lygus nuliui. Savarankiškai [kaina] yra TRUE (arba jei šis argumentas praleistas), konstanta b traktuojama įprastai;
• Savarankiškai [kaina] yra FALSE, konstanta b nustatoma į 0 ir tiesiosios linijos lygtis tampa y = mx .

pavyzdys

Šioje skaičiuoklėje programa „Excel Trend“ naudojama norint išplėsti x ir y reikšmių seriją, esančią tiesėje y = 2x + 10. Žinomos x ir y reikšmės saugomos A2-B5 langeliuose. skaičiuoklėje ir taip pat rodomi skaičiuoklės diagramoje.

Atkreipkite dėmesį, kad nebūtina, kad pateikti taškai tiksliai atitiktų tiesią liniją y = 2x + 10 (nors šiame pavyzdyje jie tinka). „Excel“ funkcija „Trend“ suras geriausiai tinkančią eilutę bet kokiam jūsų pateiktų verčių rinkiniui.

Funkcija Trend naudoja mažiausiųjų kvadratų metodą, kad surastų geriausiai tinkančią liniją, o tada naudoja ją apskaičiuodama naujas pateiktų naujų x verčių y vertes.

Šiame pavyzdyje reikšmės [nauji_x] yra saugomi langeliuose A8–A10, o „Excel“ funkcija Trend buvo naudojama langeliuose B8–B10, norint rasti naujas atitinkamas y reikšmes. Kaip parodyta formulės juostoje, formulė yra := TENDENCIJA (B2:B5, A2:A5, A8:A10)

Matote, kad funkcija Trend formulės juostoje yra skliaustuose { }. Tai rodo, kad funkcija buvo įvesta kaip masyvo formulė .

Growth

Tarp „Excel“ prognozavimo funkcijų randame Growth. Funkcija Growth „Excel“ apskaičiuoja eksponentinį augimo kreivę per nurodytą y reikšmių rinkinį ir (neprivaloma) vieną ar daugiau x reikšmių rinkinių. Tada funkcija išplečia kreivę, kad apskaičiuotų papildomas y vertes papildomam naujų x reikšmių rinkiniui.

Funkcijos sintaksė yra tokia:

sintaksė

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

dalykų

• known_y's: žinomų y reikšmių masyvas
• [known_x's]: vienas ar daugiau žinomų x reikšmių masyvų. Tai yra neprivalomas argumentas, kuris, jei pateikiamas, turi būti tokio pat ilgio kaip ir rinkinys known_y's. Jei praleista, rinkinys [known_x's] įgauna reikšmę {1, 2, 3, …}.
• [nauji_x]: naujų x reikšmių rinkinys, kuriam funkcija apskaičiuoja atitinkamas naujas y reikšmes. Jei praleista, daroma prielaida, kad rinkinys [nauji_x] yra lygus [known_x's] ir funkcija grąžina y reikšmes, esančias apskaičiuotoje eksponentinio augimo kreivėje.
• [kaina]: pasirenkamas loginis argumentas, nurodantis, ar konstanta „b“ tiesinėje lygtyje y = b * m^x , turi būti lygus 1. Jei [kaina] yra TRUE (arba jei šis argumentas praleistas), konstanta b traktuojama įprastai; Savarankiškai [kaina] yra FALSE, konstanta b nustatoma į 1 ir tiesiosios linijos lygtis tampa y = mx .

pavyzdys

Toliau pateiktoje skaičiuoklėje „Excel“ augimo funkcija naudojama norint išplėsti x ir y reikšmių, kurios yra eksponentinės augimo kreivėje y = 5 * 2^x, seriją. Jie saugomi skaičiuoklės langeliuose A2–B5 ir taip pat rodomi skaičiuoklės diagramoje.

Augimo funkcija apskaičiuoja eksponentinį augimo kreivę, kuri geriausiai atitinka pateiktas žinomas x ir y vertes. Šiame paprastame pavyzdyje geriausiai tinkanti kreivė yra eksponentinė kreivė y = 5 * 2^x.

Kai „Excel“ apskaičiuoja eksponentinės augimo kreivės lygtį, ji gali ją naudoti apskaičiuodama naujas y reikšmes naujoms x reikšmėms, pateiktoms langeliuose A8–A10.

Šiame pavyzdyje reikšmės [new_x's] yra saugomi langeliuose A8-A10 ir funkcija Growth „Excel“ buvo įterptas į langelius B8–B10. Kaip parodyta formulės juostoje, formulė yra:=Growth( B2:B5, A2:A5, A8:A10 )

Galite matyti, kad augimo funkcija formulės juostoje yra skliaustuose { }. Tai rodo, kad funkcija buvo įvesta kaip masyvo formulė .

Atkreipkite dėmesį, kad nors taškai aukščiau pateiktame pavyzdyje tiksliai atitinka kreivę y = 5 * 2^x, tai nėra būtina. Funkcija Growth „Excel“ suras geriausiai tinkančią kreivę bet kokiam jūsų pateiktų verčių rinkiniui.

Finansinės funkcijos

poveikis

Funkcija Effect Programa „Excel“ grąžina faktinę metinę palūkanų normą pagal nurodytą nominalią palūkanų normą ir tam tikrą sudėtinių laikotarpių skaičių per metus.

Efektyvi metinė palūkanų norma

Efektyvi metinė palūkanų norma yra palūkanų matas, apimantis palūkanų kapitalizaciją ir dažnai naudojamas finansinėms paskoloms su skirtingomis kapitalizacijos sąlygomis palyginti.

Efektyvi metinė palūkanų norma apskaičiuojama pagal šią lygtį:

balandis nominal_rate yra nominali palūkanų norma e npery yra sudėtinių laikotarpių skaičius per metus.

Funkcijos sintaksė yra tokia:

sintaksė

= EFFECT( nominal_rate, npery )

dalykų

• nominal_rate: nominali palūkanų norma (turi būti skaitinė reikšmė nuo 0 iki 1)
• npery: sudėtinių laikotarpių skaičius per metus (turi būti teigiamas sveikasis skaičius).

pavyzdys

Šioje skaičiuoklėje pateikiami trys „Excel“ efekto funkcijos pavyzdžiai:

Jei funkcijos rezultatas Effect rodomas kaip dešimtainis arba rodomas 0%, abi šios problemos greičiausiai kyla dėl langelio, kuriame yra funkcija, formatavimo Effect.

Todėl problemą galima išspręsti suformatavus langelį procentais, skaičiais po kablelio.

Padaryti tai:

1. Pasirinkite langelius, kuriuos norite formatuoti procentais.
2. Atidarykite dialogo langą „Format Cells“ vienu iš šių būdų:
   • Dešiniuoju pelės mygtuku spustelėkite pasirinktą langelį arba diapazoną ir pasirinkite parinktį Formatuoti langelius… iš kontekstinio meniu;
   • Skirtuko numerių grupėje spustelėkite dialogo lango paleidimo priemonę Pagrindinis Excel juostelė;
   • Naudokite spartųjį klavišą CTRL-1 (t. y. pasirinkite klavišą CTRL ir, laikydami jį nuspaudę, pasirinkite klavišą „1“ (vienas)).
3. Dialogo lange „Ląstelių formatavimas“:
   • Įsitikinkite, kad kortelė skaičius dialogo lango viršuje pasirinktas.
   • pasirinkti Procentas iš sąrašo Kategorija kairėje dialogo lango pusėje .Dešinėje žymimojo laukelio pusėje bus pateiktos papildomos parinktys, leidžiančios pasirinkti skaičių po kablelio, kurį norite rodyti.
   • Pasirinkę skaičių po kablelio, kurį norite rodyti, spustelėkite OK .

Nominal

Funkcija Nominal „Excel“ grąžina nominalią palūkanų normą tam tikrai efektyviai palūkanų normai ir tam tikram sudėtinių laikotarpių skaičiui per metus.

Funkcijos sintaksė yra tokia:

sintaksė

= NOMINAL( effect_rate, npery )

dalykų

• effect_rate: efektyvi palūkanų norma (skaitinė reikšmė nuo 0 iki 1).
• npery: sudėtinių laikotarpių skaičius per metus (turi būti teigiamas sveikasis skaičius).

pavyzdys

Šioje skaičiuoklėje funkcija Nominal „Excel“ naudojama trijų paskolų su skirtingomis sąlygomis nominaliajai palūkanų normai apskaičiuoti.

Ercole Palmeri