Štatistické funkcie Excelu: Výukový program s príkladmi, časť tretia

Excel poskytuje širokú škálu štatistických funkcií, ktoré vykonávajú výpočty od strednej hodnoty až po zložitejšie funkcie štatistického rozdelenia a trendovej čiary.

V tomto článku sa budeme hlbšie zaoberať štatistickými funkciami Excelu na výpočet funkcií trendových čiar.

Upozorňujeme, že niektoré štatistické funkcie boli zavedené v najnovších verziách Excelu, a preto nie sú dostupné v starších verziách.

Odhadovaný čas čítania: 12 minúty

Funkcie trendovej čiary

Forecast

Funkcia Forecast v Exceli predpovedá budúci bod na lineárnej trendovej čiare prispôsobený danej množine hodnôt x a y.

syntax

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

predmetov

• x: Číselná hodnota x, pre ktorú chcete predpovedať novú hodnotu y.
• known_y's: Pole známych hodnôt y
• known_x's: Pole známych hodnôt x

Všimnite si, že dĺžka poľa known_x musí byť rovnaký ako known_y a rozptyl known_x nemusí to byť nula.

príklad

V nasledujúcej tabuľke je funkcia FORECAST Excel sa používa na predpovedanie ďalšieho bodu pozdĺž najlepšej priamky prostredníctvom série známych hodnôt x a y (uložených v bunkách F2:F7 a G2:G7).

Ako je znázornené v bunke F7 tabuľky, funkcia na výpočet očakávanej hodnoty y pri x=7 je :=FORECAST( 7, G2:G7, F2:F7 )

To dáva výsledok 32.666667 .

Intercept

Medzi prognostickými funkciami Excelu nájdeme napr Intercept. Funkcia Intercept programu Excel vypočíta priesečník (hodnotu v priesečníku osi y) lineárnej regresnej čiary cez danú množinu hodnôt x a y.

syntax

= INTERCEPT( known_y's, known_x's )

predmetov

• known_y's: Pole známych hodnôt y
• known_x's: Pole známych hodnôt x

Všimnite si, že dĺžka poľa known_x musí byť rovnaký ako known_y a rozptyl known_x nemusí to byť nula.

príklad

Nasledujúca tabuľka zobrazuje príklad funkcie Intercept programu Excel použitý na výpočet bodu, v ktorom lineárna regresná priamka prechádza cez known_x a known_y (uvedené v bunkách F2:F7 a G2:G7) pretína os y.

Le known_x a known_y sú vynesené do grafu v tabuľke.

Ako je znázornené v bunke F9 tabuľky, vzorec pre funkciu Intercept je :=INTERCEPT( G2:G7, F2:F7 )

ktorý dáva výsledok 2.4 .

Slope

Ďalšou veľmi zaujímavou predikčnou funkciou je Slope (Slope) Excel vypočíta sklon lineárnej regresnej priamky cez daný súbor hodnôt x a y.

Syntax funkcie je:

syntax

= SLOPE( known_y's, known_x's )

predmetov

• known_y's: Pole známych hodnôt y
• known_x's: Pole známych hodnôt x

Všimnite si, že dĺžka poľa known_x musí byť rovnaký ako known_y a rozptyl known_x nemusí to byť nula.

príklad

Nasledujúca tabuľka zobrazuje príklad funkcie Slope (sklon) programu Excel použitý na výpočet sklonu lineárnej regresnej priamky cez known_x a known_y, v bunkách F2:F7 a G2:G7.

Le known_x a known_y sú vynesené do grafu v tabuľke.

Ako je znázornené v bunke F9 tabuľky, vzorec pre funkciu Intercept je :=SLOPE( G2:G7, F2:F7 )

ktorý dáva výsledok 4.628571429.

Trend

Veľmi zaujímavou funkciou prognózy Excelu je TREND Excel (Trend) vypočítava lineárnu trendovú čiaru cez danú množinu hodnôt y a (voliteľne) danú množinu hodnôt x.

Funkcia potom rozšíri lineárnu trendovú čiaru, aby vypočítala ďalšie hodnoty y pre ďalšiu množinu nových hodnôt x.

Syntax funkcie je:

syntax

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

predmetov

• known_y's: Pole známych hodnôt y
• [known_x's]: Jedno alebo viac polí známych hodnôt x. Toto je voliteľný argument, ktorý, ak je zadaný, by mal mať rovnakú dĺžku ako množina known_y's. Ak sa vynechá, súbor [known_x's] nadobúda hodnotu {1, 2, 3, …}.
• [new_x's]: Voliteľný argument poskytujúci jedno alebo viacero polí číselných hodnôt predstavujúcich množinu nových hodnôt x, pre ktoré chcete vypočítať zodpovedajúce nové hodnoty y. Každé pole [new_x's] by sa malo zhodovať s poľom [known_x's]. Ak argument [new_x's] je vynechaný, je nastavený na hodnotu [known_x's].
• [cena]: Voliteľný logický argument určujúci, či je konštanta 'b' v lineárnej rovnici y = m x + b , musí byť vynútene rovné nule. sám [náklady] je PRAVDA (alebo ak je tento argument vynechaný), s konštantou b sa zaobchádza normálne;
• sám [náklady] je FALSE, konštanta b je nastavená na 0 a rovnica s priamkou sa stane y = mx .

príklad

V nasledujúcej tabuľke sa funkcia Excel Trend používa na rozšírenie radu hodnôt x a y, ktoré ležia na priamke y = 2x + 10. Známe hodnoty x a y sú uložené v bunkách A2-B5 hárku a sú zobrazené aj v tabuľkovom grafe.

Všimnite si, že nie je nevyhnutné, aby dané body sedeli presne pozdĺž priamky y = 2x + 10 (hoci v tomto príklade áno). Funkcia Trend v Exceli nájde najvhodnejší riadok pre akúkoľvek sadu hodnôt, ktoré zadáte.

Funkcia Trend používa metódu najmenších štvorcov na nájdenie najvhodnejšej čiary a potom ju použije na výpočet nových hodnôt y pre nové poskytnuté hodnoty x.

V tomto príklade sú hodnoty [new_x's] sú uložené v bunkách A8-A10 a na nájdenie nových zodpovedajúcich hodnôt y sa v bunkách B8-B10 použila funkcia Trend programu Excel. Ako je znázornené na riadku vzorcov, vzorec je := TREND( B2:B5, A2:A5, A8:A10 )

Vidíte, že funkcia Trend v riadku vzorcov je uzavretá v zátvorkách { }. To znamená, že funkcia bola zadaná ako vzorec poľa .

Growth

Medzi prognostickými funkciami Excelu nájdeme napr Growth. Funkcia Growth Excel vypočíta krivku exponenciálneho rastu prostredníctvom danej sady hodnôt y a (voliteľné) jednej alebo viacerých sád hodnôt x. Funkcia potom rozšíri krivku a vypočíta ďalšie hodnoty y pre ďalšiu množinu nových hodnôt x.

Syntax funkcie je:

syntax

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

predmetov

• known_y's: Pole známych hodnôt y
• [known_x's]: Jedno alebo viac polí známych hodnôt x. Toto je voliteľný argument, ktorý, ak je zadaný, by mal mať rovnakú dĺžku ako množina known_y's. Ak sa vynechá, súbor [known_x's] nadobúda hodnotu {1, 2, 3, …}.
• [new_x's]: Množina nových hodnôt x, pre ktoré funkcia vypočíta zodpovedajúce nové hodnoty y. Ak sa vynechá, predpokladá sa, že súbor [new_x's] sa rovná [known_x's] a funkcia vráti hodnoty y, ktoré ležia na vypočítanej krivke exponenciálneho rastu.
• [cena]: Voliteľný logický argument určujúci, či je konštanta 'b' v lineárnej rovnici y = b * m^x , musí byť vynútene rovné 1. Ak [náklady] je PRAVDA (alebo ak je tento argument vynechaný), s konštantou b sa zaobchádza normálne; sám [náklady] je FALSE, konštanta b je nastavená na 1 a rovnica s priamkou sa stane y = mx .

príklad

V nasledujúcej tabuľke sa funkcia rastu v Exceli používa na rozšírenie radu hodnôt x a y, ktoré ležia na krivke exponenciálneho rastu y = 5 * 2^x. Tieto sú uložené v bunkách A2-B5 tabuľky a zobrazia sa aj v tabuľkovom grafe.

Funkcia Growth vypočíta exponenciálnu rastovú krivku, ktorá najlepšie zodpovedá zadaným známym hodnotám x a y. V tomto jednoduchom príklade je najvhodnejšou krivkou exponenciálna krivka y = 5 * 2^x.

Keď Excel vypočíta rovnicu exponenciálnej rastovej krivky, môže ju použiť na výpočet nových hodnôt y pre nové hodnoty x uvedené v bunkách A8-A10.

V tomto príklade sú hodnoty [new_x's] sú uložené v bunkách A8-A10 a funkcia Growth Excel bol vložený do buniek B8-B10. Ako je znázornené na riadku vzorcov, vzorec pre to je:=Growth( B2:B5, A2:A5, A8:A10 )

Môžete vidieť, že funkcia Rast v riadku vzorcov je uzavretá v zátvorkách { }. To znamená, že funkcia bola zadaná ako vzorec poľa .

Všimnite si, že hoci body vo vyššie uvedenom príklade presne zapadajú pozdĺž krivky y = 5 * 2^x, nie je to podstatné. Funkcia Growth Excel nájde najvhodnejšiu krivku pre akúkoľvek sadu hodnôt, ktoré zadáte.

Finančné funkcie

efekt

Funkcia Effect Excel vráti efektívnu ročnú úrokovú sadzbu pre danú nominálnu úrokovú sadzbu a daný počet zložených období za rok.

Efektívna ročná úroková sadzba

Efektívna ročná úroková miera je miera úroku, ktorá zahŕňa kapitalizáciu úrokov a často sa používa na porovnanie finančných pôžičiek s rôznymi podmienkami kapitalizácie.

Efektívna ročná úroková sadzba sa vypočíta pomocou nasledujúcej rovnice:

holubica nominal_rate je nominálna úroková miera e npery je počet zložených období za rok.

Syntax funkcie je:

syntax

= EFFECT( nominal_rate, npery )

predmetov

• nominal_rate: nominálna úroková sadzba (musí to byť číselná hodnota medzi 0 a 1)
• npery: Počet zložených období za rok (musí byť kladné celé číslo).

príklad

Nasledujúca tabuľka zobrazuje tri príklady funkcie Excel Effect:

Ak výsledok funkcie Effect zobrazuje ako desatinné číslo alebo zobrazuje 0 %, oba tieto problémy sú pravdepodobne spôsobené formátovaním bunky obsahujúcej funkciu Effect.

Preto je možné problém vyriešiť formátovaním bunky v percentách s desatinnými miestami.

Robiť to:

1. Vyberte bunky, ktoré chcete formátovať ako percento.
2. Otvorte dialógové okno „Formát buniek“ jedným z nasledujúcich spôsobov:
   • Kliknite pravým tlačidlom myši na vybratú bunku alebo rozsah a vyberte možnosť Formátovať bunky… z kontextového menu;
   • Kliknite na spúšťač dialógového okna v skupine čísel na karte Domov Excel stuha;
   • Použite klávesovú skratku CTRL-1 (t. j. vyberte kláves CTRL a podržte ho stlačený a vyberte kláves „1“ (jeden)).
3. V dialógovom okne „Formátovať bunky“:
   • Uistite sa, že karta číslo v hornej časti dialógového okna.
   • vybrať Percento zo zoznamu Kategórie na ľavej strane dialógového okna .Tým sa na pravej strane začiarkavacieho políčka zobrazia ďalšie možnosti, ktoré vám umožnia vybrať počet desatinných miest, ktoré sa majú zobraziť.
   • Po výbere počtu desatinných miest, ktoré chcete zobraziť, kliknite OK .

Nominal

Funkcia Nominal Excel vráti nominálnu úrokovú sadzbu pre danú efektívnu úrokovú sadzbu a daný počet zložených období za rok.

Syntax funkcie je:

syntax

= NOMINAL( effect_rate, npery )

predmetov

• effect_rate: Efektívna úroková miera (číselná hodnota medzi 0 a 1).
• npery: Počet zložených období za rok (musí byť kladné celé číslo).

príklad

V nasledujúcej tabuľke je funkcia Nominal Excel sa používa na výpočet nominálnej úrokovej sadzby troch úverov s rôznymi podmienkami.

Ercole Palmeri