Excel statistiskās funkcijas: apmācība ar piemēriem, trešā daļa

Programma Excel nodrošina plašu statistikas funkciju klāstu, kas veic aprēķinus no vidējā līdz sarežģītākām statistiskā sadalījuma un tendenču līnijas funkcijām.

Šajā rakstā mēs iedziļināsimies Excel statistikas funkcijās tendenču līniju funkciju aprēķināšanai.

Lūdzu, ņemiet vērā, ka dažas statistikas funkcijas tika ieviestas jaunākajās Excel versijās, tāpēc tās nav pieejamas vecākās versijās.

Paredzamais lasīšanas laiks: 12 minūti

Trendline funkcijas

Forecast

Programmas Excel funkcija Prognoze paredz nākotnes punktu lineārā tendenču līnijā, kas atbilst noteiktai x un y vērtību kopai.

sintakse

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

priekšmetus

• x: skaitliska x vērtība, kurai vēlaties paredzēt jaunu y vērtību.
• known_y's: zināmu y vērtību masīvs
• known_x's: zināmu x vērtību masīvs

Ņemiet vērā, ka masīva garums known_x jābūt tādam pašam kā no known_y un dispersiju known_x tai nav jābūt nullei.

piemērs

Nākamajā izklājlapā funkcija FORECAST Programma Excel tiek izmantota, lai paredzētu papildu punktu pa vispiemērotāko taisni, izmantojot zināmu x un y vērtību sēriju (kas tiek saglabātas šūnās F2:F7 un G2:G7).

Kā parādīts izklājlapas šūnā F7, funkcija paredzamās y vērtības aprēķināšanai pie x=7 ir :=FORECAST( 7, G2:G7, F2:F7 )

Tas dod rezultātu 32.666667 .

Intercept

Starp Excel prognozēšanas funkcijām mēs atrodam Intercept. Programmas Excel funkcija Intercept aprēķina lineārās regresijas līnijas nogriezni (vērtību y ass krustpunktā) noteiktai x un y vērtību kopai.

sintakse

= INTERCEPT( known_y's, known_x's )

priekšmetus

• known_y's: zināmu y vērtību masīvs
• known_x's: zināmu x vērtību masīvs

Ņemiet vērā, ka masīva garums known_x jābūt tādam pašam kā no known_y un dispersiju known_x tai nav jābūt nullei.

piemērs

Nākamajā izklājlapā ir parādīts funkcijas piemērs Intercept Excel izmanto, lai aprēķinātu punktu, kur lineārās regresijas līnija caur known_x un known_y (norādīts šūnās F2:F7 un G2:G7) krustojas ar y asi.

Le known_x un known_y ir attēloti izklājlapas grafikā.

Kā parādīts izklājlapas šūnā F9, funkcijas Pārtveršanas formula ir :=INTERCEPT( G2:G7, F2:F7 )

kas dod rezultātu 2.4 .

Slope

Vēl viena ļoti interesanta prognozēšanas funkcija ir slīpums (Slope) Programma Excel aprēķina lineārās regresijas līnijas slīpumu, izmantojot doto x un y vērtību kopu.

Funkcijas sintakse ir šāda:

sintakse

= SLOPE( known_y's, known_x's )

priekšmetus

• known_y's: zināmu y vērtību masīvs
• known_x's: zināmu x vērtību masīvs

Ņemiet vērā, ka masīva garums known_x jābūt tādam pašam kā no known_y un dispersiju known_x tai nav jābūt nullei.

piemērs

Nākamajā izklājlapā ir parādīts funkcijas piemērs Slope (slīpums) programmā Excel, ko izmanto, lai aprēķinātu lineārās regresijas līnijas slīpumu cauri known_x un known_y, šūnās F2:F7 un G2:G7.

Le known_x un known_y ir attēloti izklājlapas grafikā.

Kā parādīts izklājlapas šūnā F9, funkcijas Pārtveršanas formula ir :=SLOPE( G2:G7, F2:F7 )

kas dod rezultātu 4.628571429.

Trend

Ļoti interesanta Excel prognožu funkcija ir TREND Programma Excel (Trend) aprēķina lineāro tendences līniju, izmantojot noteiktu y vērtību kopu un (pēc izvēles) noteiktu x vērtību kopu.

Pēc tam funkcija paplašina lineāro tendenču līniju, lai aprēķinātu papildu y vērtības papildu jaunu x vērtību kopai.

Funkcijas sintakse ir šāda:

sintakse

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

priekšmetus

• known_y's: zināmu y vērtību masīvs
• [known_x's]: viens vai vairāki zināmu x vērtību masīvi. Šis ir neobligāts arguments, kuram, ja tiek nodrošināts, ir jābūt tādam pašam garumam kā komplektam known_y's. Ja tas ir izlaists, kopa [known_x's] iegūst vērtību {1, 2, 3, …}.
• [jaunie_x]: neobligāts arguments, kas nodrošina vienu vai vairākus skaitlisko vērtību masīvus, kas attēlo jaunu x vērtību kopu, kurai vēlaties aprēķināt atbilstošās jaunās y vērtības. Katrs [jauni_x] jāatbilst masīvam [known_x's]. Ja arguments [jauni_x] ir izlaists, tas ir iestatīts vienāds ar [known_x's] .
• [izmaksas]: neobligāts loģiskais arguments, kas norāda, vai lineārajā vienādojumā ir konstante “b”. y = m x + b , jābūt vienādam ar nulli. Pats [izmaksāt] ir TRUE (vai, ja šis arguments ir izlaists), konstante b tiek apstrādāta normāli;
• Pats [izmaksāt] ir FALSE, konstante b ir iestatīta uz 0 un taisnās līnijas vienādojums kļūst y = mx .

piemērs

Nākamajā izklājlapā funkcija Excel Trend tiek izmantota, lai paplašinātu x un y vērtību sēriju, kas atrodas uz taisnes y = 2x + 10. Zināmās x un y vērtības tiek saglabātas šūnās A2-B5 izklājlapā un tiek parādīti arī izklājlapas diagrammā.

Ņemiet vērā, ka nav svarīgi, lai norādītie punkti precīzi atbilstu taisnei y = 2x + 10 (lai gan šajā piemērā tā ir). Programmas Excel funkcija Trend atradīs vispiemērotāko līniju jebkurai jūsu norādītajai vērtību kopai.

Funkcija Trend izmanto mazāko kvadrātu metodi, lai atrastu vislabāko atbilstības līniju, un pēc tam izmanto to, lai aprēķinātu jaunās y vērtības jaunajām x vērtībām.

Šajā piemērā vērtības [jauni_x] tiek glabāti šūnās A8–A10, un šūnās B8–B10 ir izmantota Excel funkcija Trend, lai atrastu jaunās atbilstošās y vērtības. Kā parādīts formulas joslā, formula ir := TENDENCE( B2:B5, A2:A5, A8:A10)

Jūs redzat, ka funkcija Trend formulas joslā ir ievietota iekavās { }. Tas norāda, ka funkcija tika ievadīta kā masīva formula .

Growth

Starp Excel prognozēšanas funkcijām mēs atrodam Growth. Funkcija Growth Programma Excel aprēķina eksponenciālās izaugsmes līkni, izmantojot noteiktu y vērtību kopu un (pēc izvēles) vienu vai vairākas x vērtību kopas. Pēc tam funkcija paplašina līkni, lai aprēķinātu papildu y vērtības papildu jaunu x vērtību kopai.

Funkcijas sintakse ir šāda:

sintakse

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

priekšmetus

• known_y's: zināmu y vērtību masīvs
• [known_x's]: viens vai vairāki zināmu x vērtību masīvi. Šis ir neobligāts arguments, kuram, ja tiek nodrošināts, ir jābūt tādam pašam garumam kā komplektam known_y's. Ja tas ir izlaists, kopa [known_x's] iegūst vērtību {1, 2, 3, …}.
• [jaunie_x]: jaunu x vērtību kopa, kurai funkcija aprēķina atbilstošās jaunās y vērtības. Ja tas ir izlaists, tiek pieņemts, ka kopa [jauni_x] ir vienāds ar [known_x's] un funkcija atgriež y vērtības, kas atrodas uz aprēķinātās eksponenciālās augšanas līknes.
• [izmaksas]: neobligāts loģiskais arguments, kas norāda, vai lineārajā vienādojumā ir konstante “b”. y = b * m^x , jābūt vienādam ar 1. Ja [izmaksāt] ir TRUE (vai, ja šis arguments ir izlaists), konstante b tiek apstrādāta normāli; Pats [izmaksāt] ir FALSE, konstante b ir iestatīta uz 1 un taisnās līnijas vienādojums kļūst y = mx .

piemērs

Nākamajā izklājlapā Excel augšanas funkcija tiek izmantota, lai paplašinātu x un y vērtību sēriju, kas atrodas uz eksponenciālās izaugsmes līknes y = 5 * 2^x. Tie tiek saglabāti izklājlapas šūnās A2–B5 un tiek parādīti arī izklājlapas diagrammā.

Izaugsmes funkcija aprēķina eksponenciālās augšanas līkni, kas vislabāk atbilst zināmajām x un y vērtībām. Šajā vienkāršajā piemērā vislabāk atbilstošā līkne ir eksponenciālā līkne y = 5 * 2^x.

Kad programma Excel aprēķina eksponenciālās augšanas līknes vienādojumu, tā var to izmantot, lai aprēķinātu jaunās y vērtības jaunajām x vērtībām, kas norādītas šūnās A8-A10.

Šajā piemērā vērtības [new_x's] tiek saglabāti šūnās A8-A10 un funkcija Growth Excel ir ievietota šūnās B8-B10. Kā parādīts formulu joslā, šī formula ir:=Growth( B2:B5, A2:A5, A8:A10)

Varat redzēt, ka funkcija Izaugsme formulas joslā ir ievietota iekavās { }. Tas norāda, ka funkcija tika ievadīta kā masīva formula .

Ņemiet vērā: lai gan punkti iepriekš minētajā piemērā precīzi atbilst līknei y = 5 * 2^x, tas nav būtiski. Funkcija Growth Programma Excel atradīs vispiemērotāko līkni jebkurai jūsu norādītajai vērtību kopai.

Finanšu funkcijas

efekts

Funkcija Effect Programma Excel atgriež efektīvo gada procentu likmi noteiktai nominālajai procentu likmei un noteiktam salikšanas periodu skaitam gadā.

Efektīvā gada procentu likme

Efektīvā gada procentu likme ir procentu mērs, kas ietver procentu kapitalizāciju, un to bieži izmanto, lai salīdzinātu finanšu aizdevumus ar dažādiem kapitalizācijas nosacījumiem.

Efektīvā gada procentu likme tiek aprēķināta, izmantojot šādu vienādojumu:

balodis nominal_rate ir nominālā procentu likme e npery ir salikšanas periodu skaits gadā.

Funkcijas sintakse ir šāda:

sintakse

= EFFECT( nominal_rate, npery )

priekšmetus

• nominal_rate: nominālā procentu likme (jābūt skaitliskai vērtībai no 0 līdz 1)
• npery: salikšanas periodu skaits gadā (jābūt pozitīvam veselam skaitlim).

piemērs

Šajā izklājlapā ir parādīti trīs Excel Effect funkcijas piemēri:

Ja funkcijas rezultāts Effect tiek parādīts kā decimālskaitlis vai 0%, abas šīs problēmas, iespējams, ir saistītas ar tās šūnas formatējumu, kurā ir funkcija Effect.

Tāpēc problēmu var atrisināt, formatējot šūnu procentos ar decimālzīmēm.

Lai to izdarītu:

1. Atlasiet šūnas, kuras formatēt procentos.
2. Atveriet dialoglodziņu “Šūnu formatēšana”, izmantojot kādu no šīm metodēm:
   • Ar peles labo pogu noklikšķiniet uz atlasītās šūnas vai diapazona un atlasiet opciju Formatēt šūnas… no konteksta izvēlnes;
   • Cilnes numuru grupā noklikšķiniet uz dialoglodziņa palaidēja Sākumlapa Excel lente;
   • Izmantojiet īsinājumtaustiņus CTRL-1 (t.i., atlasiet taustiņu CTRL un, turot to nospiestu, atlasiet taustiņu “1” (viens).
3. Dialoglodziņā “Šūnu formatēšana”:
   • Pārliecinieties, ka karte Skaits dialoglodziņa augšdaļā ir atlasīts.
   • atlasīt Procenti no saraksta Kategorija dialoglodziņa kreisajā pusē .Tas parādīs papildu opcijas izvēles rūtiņas labajā pusē, ļaujot atlasīt ciparu aiz komata skaitu, ko vēlaties parādīt.
   • Kad esat atlasījis attēlojamo decimālzīmju skaitu, noklikšķiniet uz OK .

Nominal

Funkcija Nominal Programma Excel atgriež nominālo procentu likmi noteiktai efektīvajai procentu likmei un noteiktam salikšanas periodu skaitam gadā.

Funkcijas sintakse ir šāda:

sintakse

= NOMINAL( effect_rate, npery )

priekšmetus

• effect_rate: efektīvā procentu likme (skaitliskā vērtība no 0 līdz 1).
• npery: salikšanas periodu skaits gadā (jābūt pozitīvam veselam skaitlim).

piemērs

Nākamajā izklājlapā funkcija Nominal Excel tiek izmantots, lai aprēķinātu nominālo procentu likmi trim aizdevumiem ar dažādiem termiņiem.

Ercole Palmeri