Information Technology | Economical IT » Üzleti informatikai alkalmazások

Informatika 2 ÜZLETI INFORMATIKAI ALKALMAZÁSOK 1/81 1. VISZONYSZÁMOK. 4 1.1 1.2 2. BÁZIS VISZONYSZÁM . 4 LÁNCVISZONYSZÁM . 5 ÁTLAGSZÁMÍTÁS . 7 2.1 SÚLYOZATLAN ÁTLAGOK 7 2.2 SÚLYOZOTT ÁTLAGOK 11 2.21 SÚLYOZOTT HARMONIKUS KÖZÉP 11 2.22 SÚLYOZOTT MÉRTANI KÖZÉP 13 2.23 SÚLYOZOTT SZÁMTANI KÖZÉP 14 2.24 SÚLYOZOTT NÉGYZETES KÖZÉP 15 3. GYAKORISÁGI ELOSZLÁSOK . 17 4. STATISZTIKAI HELYZETMUTATÓK ÉS SZÓRÓDÁSI MÉRİSZÁMOK . 21 4.1 RANGSOR 21 4.11 MEDIÁN 21 4.12 MÓDUSZ 22 4.13 KVARTILISEK 23 4.14 SZÓRÁS: 24 4.15 TERJEDELEM: 24 4.2 OSZTÁLYKÖZÖS GYAKORISÁGI SOR 25 4.21 MEDIÁN 25 4.22 MÓDUSZ 27 4.23 KVARTILISEK 28 4.24 SZÓRÁS 30 5. GYAKORISÁGI SOROK GRAFIKUS ÁBRÁZOLÁSA . 32 5.1 5.2 5.3 6. HISZTOGRAM . 32 OGIVA . 33 BOX & WHISKER’S ÁBRA . 33 LINEÁRIS TRENDSZÁMÍTÁS, LINEÁRIS REGRESSZIÓ . 35 6.1 6.2 6.3 7. IDİSOROK, LINEÁRIS TRENDEK . 35 MOZGÓÁTLAGOK . 40 LINEÁRIS REGRESSZIÓ . 45 PÉNZÜGYI

ALKALMAZÁSOK. 49 7.1 7.11 7.2 7.3 7.31 7.32 7.4 KAMATSZÁMÍTÁS . 49 KAMATLÁB ÁTSZÁMÍTÁSA MÁS IDİSZAKRA . 49 JELENÉRTÉK, JÖVİÉRTÉK. 50 HITELTÖRLESZTÉSEK . 52 FIXTİKÉS KONSTRUKCIÓ . 52 ANNUITÁSOS KONSTRUKCIÓ . 54 HITELLEFUTÁSOK MODELLEZÉSE . 58 2/81 7.41 KAMATLÁB VÁLTOZÁSA 61 7.42 FUTAMIDİ VÁLTOZÁSA 62 7.5 NEM ANNUITÁSOS KONSTRUKCIÓK 62 7.6 PROJECTEK ÖSSZEHASONLÍTÁSA 65 8. GAZDASÁG MATEMATIKAI ALKALMAZÁSOK. 70 8.1 VALÓSZÍNŐSÉGSZÁMÍTÁS, VALÓSZÍNŐSÉGI VÁLTOZÓK, VÁRHATÓ ÉRTÉK, SZÓRÁS SZÁMÍTÁSA . 70 8.11 KÉTVÁLTOZÓS VALÓSZÍNŐSÉGI ELOSZLÁSOK 71 8.2 NEVEZETES DISZKRÉT ELOSZLÁSFÜGGVÉNYEK 74 8.21 BINOMIÁLIS ELOSZLÁS: 75 8.22 HIPERGEOMETRIKUS ELOSZLÁS 75 8.23 POISSON ELOSZLÁS 76 8.3 NEVEZETES FOLYTONOS ELOSZLÁSFÜGGVÉNYEK 77 8.31 EXPONENCIÁLIS ELOSZLÁS 77 8.32 NORMÁLIS ELOSZLÁS 78 8.33 STANDARD NORMÁLIS ELOSZLÁS 78 9. FÜGGELÉK. 80 3/81 1. VISZONYSZÁMOK A viszonyszámok két érték

arányát fejezik ki. Általános alakban a felírásuk nagyon egyszerő: V= A B Ahol a számláló (A) a viszonyítás tárgya (amit viszonyítunk) és a nevezı (B) a viszonyítás alapja (amihez viszonyítunk). Megkülönböztetünk egymástól Bázis- és Láncviszonyszámokat. 1.1 Bázis viszonyszám Itt a sorban egymás után következı értékeket mindig egy adott, rögzített értékhez viszonyítjuk. Nézzük egy példán keresztül Az alábbi táblázatban látható, hogy egy cég egy adott évben mennyi rúzst adott el. Az üres cellákban arra vagyunk kíváncsiak, hogy az adott forgalmi értékek az egyes években, milyen arányban változtak az 1995-ös eladáshoz képest. A számításra nézve a következı összefüggést használjuk. b i = yi , yb ahol yi az aktuális periódusbeli adat és yb a viszonyítás alapját képezı érték. 4/81 Arra figyeljünk a megoldás során, hogy a nevezıben lévı cellát mindenképpen abszolút hivatkozással adjuk

meg. Ha az aktuális cellával készen vagyunk, a képletmásolás segítségével alkalmazzuk a kiszámítási metodikát a további cellákra is. Jól látható tehát, hogy az egyes években az eladott mennyiségek milyen arányban változtak az évek folyamán egy rögzített adatértékhez, bázisévhez képest (pl. 2005-ben a forgalom 1,91-szerese volt az 1995 évi forgalomnak) 1.2 Láncviszonyszám Ennél a típusú mutatónál az adott év adatát mindig az ıt megelızı és értékéhez viszonyítjuk. Mivel a legelsı adatérték estében nem ismerjük az ıt megelızıt, így ebben az esetben nem tudjuk a számítást elvégezni. Képletben: li = yi , ahol y i az aktuális adatérték, y i −1 a megelızı periódus értéke y i −1 Nézzük az elızı példát a fenti megközelítéssel 5/81 A megoldásnál figyeljünk arra, hogy a képletben mindkét cella relatív hivatkozással szerepeljen. A képletmásolás segítségével újfent adjunk a többi

cellának is értéket. Jól látható, hogy így az évrıl évre történı változásokat figyelhetjük meg a forgalomra nézve (pl. 2004-rıl 2005-re 30%-al csökkent a forgalom) 6/81 2. ÁTLAGSZÁMÍTÁS Adott feladattól függıen megkülönböztetünk harmonikus, mértani, számtani és négyzetes átlagot, valamint ezek súlyozatlan vagy súlyozott formáit. 2.1 Súlyozatlan átlagok Tekintsük a következı példát. Adott 25 ember testsúlyának adata Számítsuk ki valamennyi középértéket. A példában a következı függvényeket fogjuk használni:
=HARMEAN(Szám1; Szám2; )- A paraméterek harmonikus közepét adja.
=GEOMEAN(Szám1; Szám2; )- A paraméterek mértani közepét adja. 7/81
AVERAGE(Szám1; Szám2;)- A paraméterek számtani közepét adja.
Négyzetes közép: Itt nem lesz a fentiekhez hasonlóan egyszerő dolgunk, ugyanis a CALC nem tartalmaz olyan függvényt, ami ezt rögtön meghatározná. Lássuk, tehát az összefüggést, ami

alapján a négyzetes (quadratikus) átlag meghatározható: N ∑X Xq = i =1 N 2 i , ahol X i az adatsor egy eleme, N az adatelemek száma. Ha jobban belegondolunk, számos függvényre szükség lesz. Négyzetgyök-függvényre : =SQRT(szám) Szorzatösszeg függvényre: =SUMPRODUCT(Mátrix1; Mátrix2;) Ez utóbbi úgy mőködik, hogy a paraméterekben szereplı tartományok elemeit rendre egymással összeszorozza, és ezeket a szorzatokat végül összeadja. Egyedül arra kell figyelni, hogy az egyes paraméterekben meghatározott cellatartományok azonos hosszal, azaz darabszámmal rendelkezzenek. Számláló függvényre: =COUNT(Szám1; Szám2;) Természetesen összetett függvények alkalmazásakor lehet használni a billentyőzetet, azaz magunk gépeljük be a megfelelı függvények neveit és 8/81 adjuk meg a kért paramétereket, azonban ehhez fejbıl kellene tudnunk a soron következı paramétert, mivel a CALC ehhez nem ad ilyen jellegő segítséget. Használjuk

helyette a Függvénytündért. 1. lépés Hívjuk be az =SQRT() függvényt, majd a paraméter listája mellett kattintsunk a 2. lépés gombra! Keressük ki a listából a =SUMPRODUCT() függvényt, majd kattintsunk a Tovább gombra! Mátrix1 és Mátrix2 paraméternek adjuk meg a testsúlyok értékeit, hiszen akkor a függvény önmagukkal szorozza meg az egyes adattagokat, és végül összeadja a szorzatokat. Így kapjuk meg a képletben szereplı ∑X 2 i értékét. Ha a paramétereknek értéket adtunk, kattintsunk a Vissza gombra! 9/81 3. lépés Miután visszatértünk az SQRT() függvényhez, a Képlet ablakban kattintsunk a két záró zárójel közé és üssünk le egy „/” jelet, hiszen a kapott szorzatösszeget még le kell, hogy osszuk az elemek számával. Ez utóbbit a =COUNT() függvény használatával tehetjük meg. Ehhez keressük ki a listából, majd kattintsunk a Tovább gombra, majd a megjelenı függvény paraméterének (Szám1),

jelöljük ki a testsúlyok értékeit, majd kattintsunk az OK g o m b r a . A Képlet ablakban végül a következı összefüggést kell, hogy lássuk: Ha eltekintünk az összefüggésben szereplı cellahivatkozások értékétıl, akkor egy teljesen általános megoldást kapunk a négyzetes közép meghatározására vonatkozóan. =SQRT(SUMPRODUCT(Tartomány;Tartomány)/COUNT(Tartomány)) 10/81 2.2 Súlyozott átlagok A fentiekben láthattuk, hogyan lehet a súlyozatlan átlagokat kiszámítani. A következıkben a súlyozott átlagok számítását mutatjuk be egy nagyon egyszerő példán keresztül, ahol egy „szerencsés” hallgató érdemjegyeit láthatjuk. A kredit értékek mutatják, hogy az adott tantárgy mennyire értékes. Minél fontosabb, annál nagyobb a kredit, azaz a súly értéke. A súlyozott közepekre szintén nincs beépített függvény. 2.21 Súlyozott harmonikus közép k s h X = ∑f i =1 k i fi ∑ i =1 X i , ahol f i a súlyok egy

„i.” értéke, X i az adatok egy „i” értéke A számításra vonatkozóan ki kell, hogy egészítsük a táblázatunkat egy olyan oszloppal, ahol az adatértékek reciprokai találhatóak. Ugyanis a képlet nevezıjében pontosan azok szerepelnek megszorozva a súlyok értékeivel. A példában a jegyek reciprokai számára készítettünk egy új oszlopot. 11/81 Ha ezzel megvagyunk, nincs más dolgunk, mint egy tetszıleges cellába implementálni a fenti képletet természetesen cellahivatkozásokkal és függvényekkel. Szükségünk van a súlyok összegére (=SUM() függvény), illetve a súlyok (kredit) és az adatértékek (jegyek) reciprokainak szorzatösszegére (=SUMPRODUCT() függvény). Kattintsunk tehát egy tetszıleges cellába és hívjuk meg a =SUM() függvényt, a paraméterének adjuk meg a súlyok (kreditek) oszlopát, majd kattintsunk a Képlet ablakba, üssünk le egy „/” jelet, majd keressük ki a =SUMPRODUCT() függvényt, azután nyomjuk meg

a Tovább gombot. A megjelenı panelon az elsı1 paraméternek adjuk meg a jegyek reciprokának oszlopát, másodiknak pedig a kredit oszlopot. Ha a megfelelı összefüggést látjuk a Képlet ablakban, nyomjuk meg az OK gombot. Általánosan a következıképpen lehet meghatározni ezt az átlagot: =SUM(Súlyok)/SUMPRODUCT(Súlyok;Adatok reciproka) 1 Nincs jelentısége, hogy a jegyek vagy a kredit értékeket adjuk meg elsı vagy második paraméternek. 12/81 2.22 Súlyozott mértani közép Az összes átlagszámítási módszer közül talán ez a legnehezebben implementálható. k s fi f A képlet: X g = ∑ i ∏ X i , ahol f i a súlyérték és X i az adatérték. i =1 Ha jobban szemügyre vesszük az összefüggést láthatjuk, hogy nincs más teendınk, mint a példában venni az egyes jegyek értékeinek kredit nagyságú hatványait, ezen értékek szorzatát képezni, majd ebbıl egy olyan alapú gyököt vonni, ami a kreditek összegébıl áll. Az elme eme

középkori kínzása után, nézzük meg, hogyan lehet a CALC-ban ezt kivitelezni. Mivel a programban nincs olyan függvény, amely képes lenne egyidejőleg a hatványokat és azok szorzatát elıállítani, ezért létrehozunk egy segédoszlopot, melyben kiszámítjuk a jegyek kreditkitevıjő hatványait. Ehhez használjuk a „^” operátort A képletmásolással alkalmazzuk ezt a számítási metódust az oszlop többi celláján is. Az egyszerőség kedvéért számítsuk ki egy külön cellába a kreditek összegét. Mivel a CALC nem tartalmaz olyan függvényt, amely képes n-edik gyök képzésére, ezért kihasználjuk azt a tulajdonságot, hogy a gyök nem más, mint egy törtkitevıjő 1 hatvány. ( n x = x n ) Olyan függvényt szerencsére találunk2, mely egy számot képes egy tetszıleges hatványra emelni. Ez a =POWER(Alap;Hatvány) függvény A képletben szereplı szorzat kiszámítását a =PRODUCT(Szám1;Szám2;) függvény végzi. Kattintsunk egy tetszıleges

cellába és hívjuk meg a =POWER() függvényt Az Hatvány paraméternek adjuk meg a súlyok összegének reciprokát (1/Összeg cella)3, majd kattintsunk az Alap paraméter melletti gombra és keressük ki a =PRODUCT() függvényt és nyomjuk meg a Tovább gombot. 2 3 Ha a program nem rendelkezne ilyen függvénnyel, még mindig használhatnánk a „^” operátort. Ezért összegeztük a súlyok oszlopát, hogy ne legyen szükségünk még egy összegzı függvény beágyazására is. 13/81 1/B12 A =PRODUCT() függvény elsı paraméterének jelöljük ki azt az oszlopot melyben a jegyek kreditfokú hatványai szerepelnek és nyomjuk meg az OK gombot. A Képlet ablak itt is a végsı összefüggést tartalmazza. Általánosan felírva4, =POWER(PRODUCT(Adat-Súlyadikon tartomány);1/Súlyok összege) 2.23 Súlyozott számtani közép k s Ez talán a legegyszerőbben kiszámítható átlag. Képlete: X = ∑f X i i =1 k ∑f i . i i =1 Látható, hogy a nevezıben

egyszerően a súlyok összege szerepel, míg a számlálóban a súlyok és az adatértékek szorzatösszege szerepel. Használjuk ki, hogy az elıbbi átlag kiszámításánál egy különálló cellába meghatároztuk a súlyok összegét. Így megspórolunk egy beágyazott függvényt Kattintsunk ismét egy tetszıleges cellába, hívjuk meg a =SUMPRODUCT() függvényt. Elsı paraméterének jelöljük ki a kreditek oszlopát, másodiknak a jegyekét Majd kattintsunk a Képlet ablakba, üssünk le egy „/” jelet és írjuk be billentyőzetrıl a kreditek összegét tartalmazó cella címét (pl. B12), mivel kattintásra a program egy sípoló hangon kívül mással nem reagál. Feladatunk végeztével nyomjuk meg az OK gombot. 4 Természetesen, ha nem kívánunk újabb cellát bevonni a számításba, akkor a súlyok összegét =SUM() függvénnyel is elıállíthatjuk, és a képletbe ágyazhatjuk. 14/81 Általánosan felírva a következıhöz jutunk:

=SUMPRODUCT(Súly;Adatérték)/Súlyok összege) 2.24 Súlyozott négyzetes közép k s Képlete: X q = ∑f X i 2 i i =1 k ∑f i i =1 Látható, hogy itt is szükség lesz az =SQRT() függvényre, továbbá gondot okozhat, hogy az adatértékek négyzetét kell venni majd a súlyokkal történı szorzatát, végül ezek összegét. De ez csak ránézésre tőnik bonyolultnak, ugyanis a =SUMPRODUCT() függvény szélsı esetben 30 oszlopon is el tudja végezni a mőveletet. Kattintsunk ismét egy tetszıleges cellába és hívjuk meg az =SQRT() függvényt, majd kattintsunk a gombra és keressük ki a =SUMPRODUCT() függvényt. Elsı paramétere legyen a kreditek oszlopa, második és harmadik paramétere pedig a jegyek oszlopa. Ezek után kattintsunk a Képlet ablakban közvetlenül a =SUMPRODUCT() zárójele után, és üssünk le egy „/” jelet, majd írjuk be az összeg cella címét (pl. ismét B12) Arra nagyon ügyeljünk, hogy az osztás mővelete mindenképpen az

=SQRT() függvény belsejében legyen még, ugyanis a képlet szerint ez a mővelet a nevezıben lévı értékre is vonatkozik. 15/81 Általánosan megfogalmazva az összefüggést, a következıt kapjuk: =SQRT(SUMPRODUCT(Súly;Adatérték;Adatérték)/Súlyok összege) Természetesen, ahogyan korábban is szerepelt a megoldások között, itt is alkalmazhatjuk a súlyok összegzésére közvetlenül a képletbe ágyazva a =SUM() függvényt. 16/81 3. GYAKORISÁGI ELOSZLÁSOK Egy adatsorozat tetszıleges intervallumokra tördelése során szembesülhetünk azzal, hogy meg kell határozzuk, vajon hány adatelem esik a kívánt tartományokba. Statisztikában ezt a mutatót gyakoriságnak vagy abszolút gyakoriságnak nevezik és f i vel jelölik. Tekintsük a következı példát. Egy cégnél 150 ember dolgozik. Kíváncsiak vagyunk arra, hogy 45 év felett 5 éves periódusbeli bontásban 75 éves korig, hány foglalkoztatottunk van? Továbbá ezen intervallumok milyen

arányban oszlanak meg a dolgozók összlétszámához képest, valamint készítsünk egy összesítést, hogy az adott kategóriákig hány embert foglalkoztatunk. Ehhez a feladathoz a =FREQUENCY(Adatok;Osztályok) függvényt fogjuk használni. Ez egy úgynevezett tömbértékő függvény, vagyis a végeredmény nem egy konkrét érték lesz, hanem egy adattömb. Paraméterei a következık: Adatok: Az osztályokba sorolandó teljes adatmennyiség Osztályok: Olyan oszlop, mely az általunk definiálandó intervallumok kezdıértékeit tartalmazza. A függvény lényegében megszámolja, hogy hány olyan elem található a feldolgozandó adatok között, mely a meghatározott intervallumokba esik. Az osztályokat úgy kell meghatározzuk, hogy csak az intervallumok kezdeti értékeit írjuk a megfelelı cellákba. Tekintve, hogy a sorban rákövetkezı cella meghatározza az elızı intervallum hosszát, felmerülhet a kérdés, hogy mi lesz az utolsó cella utáni

tartomány hossza? A program úgy dolgozik, hogy az általunk készített osztályokat kibıvíti még egyel, mely azokra az elemekre vonatkozik, melyek nagyobbak, mint az utolsó osztály határolópontja. A következıképpen alkossuk meg a különbözı osztályokat: 17/81 Mindenképpen tüntessük fel azt a plusz osztályértéket, melyben a 75 évnél idısebbek találhatóak, mivel a függvény úgyis meghatározza ezt az értéket és szerencsétlen esetben olyan cellába nyúlik a számítás, ahova más értéket szántunk volna. Nyilván ennek az utolsó intervallumnak nem lesz felsı korlátja. De bennünket úgyis csak azok a dolgozók érdekelnek a példa szerint, akik 75 évnél fiatalabbak. Nézzük a megoldást. Álljunk a gyakoriságok oszlopában a legelsı cellájába és hívjuk meg a fenti függvényt. Az Adatok paraméternek jelöljük ki a dolgozók életkorait tartalmazó oszlopot, az Osztályok paraméternek pedig a életkorok intervallumainak oszlopát.

H a m e g f elelıen paramétereztük, az OK gomb megnyomására a függvény meghatározza az egyes intervallumokba esı elemek darabszámát. Ezt az oszlopot összegezve megkapjuk a dolgozóink számát. Ez az érték azért fontos, mert a feladat második része arra kérdez rá, hogy a kiszámított gyakoriságok milyen 18/81 arányt mutatnak az összlétszámhoz képest. Ezt a viszonyszámot relatív gyakoriságnak hívják és statisztikából jól ismert g i -vel jelölik. Számítása: gi = fi , ahol N ∑f i = N. Bıvítsük ki tehát a táblázatunkat egy újabb oszloppal. Arra mindenképpen ügyeljünk, hogy a gyakoriságok összegét tartalmazó cellát abszolút hivatkozással adjuk meg. A feladat kéri egy másik jól ismert mutató meghatározását, ez pedig a kumulált gyakoriság. Jelölése a statisztikában f i Számítása: f i = f i + f i−1 Látható, hogy a számítási metódus hasonlít kicsit a matematikából jól ismert rekurzív

sorozatokéhoz. Az aktuális (i-edik) kumulált gyakoriságot úgy kapjuk meg, hogy összeadjuk az aktuális gyakoriságot és az ıt megelızı kumulált gyakoriságot. Mivel a legelsı érték számításánál gondban lennénk, mivel nem tudjuk az ıt megelızı kumulált gyakoriság értékét, így azt nullának tekintjük. Ennek függvényében f1 = f1 , f 2 = f 2 + f1 és így tovább. Táblázatkezelı használatával nagyon könnyen kiszámítható ez a mutató Ehhez bıvítsük ismét egy oszloppal a táblázatunkat. Jelen esetben a kumulált gyakoriság megmutatja, hogy például hány 70 évnél fiatalabb dolgozónk van. A táblázatból könnyen kiolvasható, hogy 105 fı 19/81 Még egy mutatóról érdemes említést tenni, ez pedig a kumulált relatív gyakoriság. g i -vel jelölik és a g i = g i + g i −1 összefüggéssel számítjuj vagy az egyes kumulált gyakoriságokat osztjuk az elıfordulással . A CALC-beli megvalósítása nagyon hasonló az elıbb

ismertetett megoldáshoz, azzal a kis különbséggel, hogy itt a relatív gyakoriságok oszlopát kell alapul venni. 20/81 4. STATISZTIKAI HELYZETMUTATÓK ÉS SZÓRÓDÁSI MÉRİSZÁMOK Az elızı fejezetben tárgyalt gyakorisági mutatók mellett a leíró statisztikában igen lényegesek az úgynevezett helyzeti középértékek (pl. módusz, medián), illetve a szóródási mutatók, melyeket rangsorból és osztályközös gyakorisági sorból is számíthatunk. 4.1 Rangsor Tételezzük fel, hogy egy 25 fıs osztályban minden diák magasságát feljegyeztük. Számítsuk ki a megfelelı helyzeti- és szóródási mutatókat! Mielıtt még nekilátnánk a függvényvadászatnak, növekvı sorba kell rendezzük az értékeket (Adat menü Rendezés). 4.11 Medián A medián az a középérték, melynél az elemek fele kisebb, illetve a fele nagyobb. Akár a mérleg nyelve. A =MEDIAN(Szám1; Szám2;) függvényt hivatott ennek meghatározására. Kattintsunk egy tetszıleges

cellába és hívjuk meg a függvényt A Szám1 paraméternek jelöljük ki a magasságokat tartalmazó oszlopot, és kattintsunk az OK gombra. 21/81 4.12 Módusz A módusz a leggyakrabban elıforduló elem. A =MODE(Szám1; Szám2;) függvénnyel számítható ki. Az elızıhöz hasonlóan a Szám1 paraméternek adjuk meg a magasságok oszlopát. Ha jobban megfigyeljük a függvény mőködését láthatjuk, hogy azt az elemet választja ki, mely a rangsorban elsıként felel meg a leggyakoribb elemnek, hiszen pl. a 175 cmes, valamint a 197 cm-es értékekbıl szintén kettı van 22/81 4.13 Kvartilisek A kvartilisek a statisztikából jól ismert úgynevezett osztópontok. Elméleti síkon az adatainkat három megfelelıen megválasztott osztópont négy5 egyforma hosszúságú szakaszra bontja szét. A felezı ponttal már megismerkedtünk, és mediánnak hívták Természetesen ezekre az osztópontokra is létezik függvény a CALC-ban, ez pedig a következı.

=QUARTILE(Adatok;Típus) Paraméterei: Adatok: A teljes elemzendı adatsor. Típus: A kvartilis sorszáma, mely lehet 0,1,2,3,4. Ha 1-et, illetve 3-at adunk meg, akkor a negyedelı és a háromnegyedelı pontot kapjuk meg, ha 2-t, akkor a mediánt. A 0 és 4 esetében a minimumot illetve a maximumot adja vissza a függvény. Az egyszerőség kedvéért célszerő az ábrán látható táblázatot létrehozni: A kitöltésre nézve igen egyszerő dolgunk van, ugyanis nincs más dolgunk, mint az Érték oszlop elsı cellájába meghívni a =QUARTILE() függvényt és az Adatok paraméternek abszolút hivatkozással megadni a magasságok oszlopát, Típus pedig továbbá a paraméternek a Sorszámok megfelelı celláját relatív hivatkozással. Ezek után a képletmásoló funkció segítségével adjunk érétket a többi cellának is. 5 Innen a kvartilis elnevezés. 23/81 4.14 Szórás: A szórás megmutatja, hogy az egyes adatértékek átlagosan mennyivel

térnek el a számtani átlagtól. Nem mindegy azonban a végeredmény szempontjából, hogy egy teljes populációra nézve végezzük el a számítást, vagy egy mintavételezett adathalmaz szórását vizsgáljuk. Populáció szórása: =STDEVP(Szám1;Szám2;) Minta szórása: =STDEV(Szám1;Szám2;) Jelen esetben az adathalmazunkat tekinthetjük egy önálló populációnak, hiszen nem úgy szólt a feladat, hogy egy 30 fıs osztályból vettünk egy 25 elemő mintát és azoknak az egyedeknek tudjuk a magassági adatait. Itt a teljes sokaságot ismerjük Kattintsunk egy ismét egy tetszıleges cellába és hívjuk meg a =STDEVP() függvényt, majd a Szám1 paraméternek jelöljük ki a magasságok oszlopát, ezután nyomjuk meg az OK gombot. 4.15 Terjedelem: A terjedelem nem más, mint annak a tartománynak a hossza, melyen belül az adatértékek elhelyezkednek, azaz R = X Max − X Min . Ahol X Max a legnagyobb, X Min a legkisebb adatérték. A kvartilisekkel végzett

számítások után ezt igen egyszerő meghatározni. 24/81 4.2 Osztályközös gyakorisági sor A rangsorokkal ellentétben itt nem támaszkodhatunk a beépített függvényekre, ugyanis itt a helyzetmutatók számítása egy olyan algoritmust igényel, amit egy függvényben nehezen lehetne megoldani. Ezért a képletek alapján fogjuk a kívánt mutatókat meghatározni. Egy cégnél rögzítették (Gyakoriság oszlop), hogy a dolgozók mennyi privát telefonbeszélgetést folyattak a munkahelyükön naponta. Számítsuk ki a helyzeti- és szóródási mutatókat! A kumulált gyakoriságot a korábban ismertetett módszerrel számítottuk ki, az úgynevezett osztályközepek pedig voltaképpen az osztályközök (Idıtartam oszlop) felezıpontjai. 4.21 Medián A medián meghatározásához a következı algoritmust kell végrehajtanunk: 1. lépés Határozzuk meg az elemek felét, ∑f 2 i -t. Könnyen belátható, hogy esetünkben ez az érték 70. 2. lépés Keressük

meg azt a sort a táblázatban ahol f ≥ ∑f 2 i , tehát a kumulált gyakoriság érétke elıször nagyobb vagy egyenlı, mint az elemek fele, azaz 70. Ezt a sort vesszük alapul a medián kiszámításához (lásd ábra) 25/81 3. lépés A következı képlet alapján határozzuk meg a mediánt Me = X Me ,0 N − f i−1 + 2 ⋅ h Me f Me Ahol: X Me , 0 , a mediánt tartalmazó osztályköz alsó határa. (A példában 40). N = ∑ f i , az elemek száma (140). f Me a medián sorában lévı gyakoriság (jelenleg 35). h Me , a medián osztályközének hossza (20). f i−1 , a mediánt megelızı sorban lévı kumulált gyakoriság (63). Valamennyi képletben szereplı változó helyére cellahivatkozásokat tegyünk (az osztályköz hosszát beírhatjuk egy tetszıleges cellába), továbbá ne feledjük, hogy a matematikában a törtvonal zárójelet helyettesít, ezért a számlálót mindenképpen tegyük zárójelek közé. Végeredményként 44-et kapunk.

26/81 4.22 Módusz Sajnos itt sincs függvény, ami a segítségünkre sietne, úgyhogy jelen esetben is egy meghatározott lépéssorozatot kell végrehajtanunk a kívánt eredmény eléréséhez. 1. lépés Mivel a módusz a leggyakrabban elıforduló elem, ezért keressük meg azt a sort, ahol gyakorisági érték a legnagyobb. Ezt az ábrán látható sort vesszük alapul a módusz meghatározásához. 2. lépés A móduszra vonatkozó képlet alapján határozzuk meg a mutató értékét. Mo = X Mo ,0 + (f Mo f Mo − f Mo −1 ⋅ h Mo − f Mo −1 ) + (f Mo − f Mo +1 ) Ahol: X Mo , 0 , a móduszt tartalmazó osztályköz alsó határa. (A példában 20). f Mo , a móduszt tartalmazó sor gyakorisága (44). f Mo −1 , a móduszt megelızı sor gyakorisága (19). f Mo +1 , a móduszt követı sor gyakorisága (35). h Mo , a módusz osztályközének hossza (ismét 20). Ezek után nincs más dolgunk, mint átültetni a képletet a CALC világába. Jelen esetben se

feledkezzünk meg arról, hogy a tört számlálóját és nevezıjét tegyük zárójelbe. 3. lépés Alkalmazzuk a statisztikából tanult képletet: 27/81 4.23 Kvartilisek Az elsı és harmadik kvartilis meghatározásának menete gyakorlatilag megegyezik a medián kiszámításának metodikájával. Q1: 1. lépés Határozzuk meg az elemek negyedét, ∑f 4 i -t. Látható ismét, hogy esetünkben ez az érték 35. 2. lépés Keressük meg azt a sort a táblázatban ahol, f ≥ ∑f 4 i tehát a kumulált gyakoriság érétke elıször nagyobb vagy egyenlı, mint az elemek negyede, azaz 35. Lásd ábra 3. lépés Alkalmazzuk a statisztikából tanult képletet. Q1 = X q 0 N − f q −1 + 4 hq fq 28/81 Megoldásnak 27,3-at kapunk. Q3: 1. lépés Határozzuk meg az elemek háromnegyedét, azaz 3 ⋅ ∑ f i -t. Látható 4 ismét, hogy esetünkben ez az érték 105. 2. lépés Keressük meg azt a sort a táblázatban ahol, f ≥ 3 ⋅ ∑ f i tehát

a 4 kumulált gyakoriság érétke elıször nagyobb vagy egyenlı, mint az elemek negyede, azaz 105. Lásd ábra 3. lépés Alkalmazzuk a statisztikából tanult képletet Q 3 = X q0 3 N − f q −1 4 + hq fq 29/81 4.24 Szórás Osztályközös gyakoriságok esetén a szórásra szintén nincs függvény. Itt is a képletet vonatkozatjuk el a CALC-beli interpretációra: k ∑ f (X i σ= i −X ) 2 i =1 k ∑f i i =1 Amint látható szükségünk lesz az átlagra (természetesen súlyozott számtani átlagot fogunk számolni, ahol X i lesz az osztályközép). A számítást az alábbi ábra szemlélteti A szórás kiszámításához felveszünk egy segédoszlopot, ahol soronként az egyes ( ) 2 osztályközepek átlagtól való eltérésének négyzetét tároljuk, azaz X i − X . Ehhez használhatjuk a =POWER() függvényt vagy „^” operátort. 30/81 Amennyiben a „^” operátor használata mellett döntünk, a következıt vigyük be az F3as

cellába: =(C4-$E$14)^2 Ügyeljünk arra, hogy az átlagot itt is mindenképp abszolút hivatkozásként adjuk meg. Ettıl kezdve a számítással igen egyszerő dolgunk van, hiszen a gyakoriságoknak és az újonnan képzett eltérés-négyzet oszlopnak kell a szorzatösszegét venni, osztani az összgyakorisággal és végül gyököt vonni. Az implementációt viszont fordítva kezdjük Hívjuk meg az =SQRT() függvényt, majd kattintsunk a gombra, majd keressük meg a =SUMPRODUCT() függvényt és elsı paraméterének jelöljük ki a gyakoriságok oszlopát, másodiknak pedig az eltérések négyzetét tartalmazó oszlopot. Ezek után kattintsunk a Képlet ablakba, és a képzett szorzatösszeget osszuk le a gyakoriságok összegével, végül nyomjuk meg az OK gombot. Figyeljünk arra, hogy a Képlet ablakban nem mőködik a cellákra történı kattintás, ezért billentyőzetrıl kell a megfelelı (a példa esetében D10-es) cella címét beütni. Általánosan felírva a

számítást: =SQRT(SUMPRODUCT(Gyakoriság ; Eltérések négyzete)/Gyakoriságok összege) 31/81 5. GYAKORISÁGI SOROK GRAFIKUS ÁBRÁZOLÁSA 5.1 Hisztogram Hisztogramot úgy kapunk, ha a gyakorisági sorunkat, azaz az egyes osztályközök gyakoriságait oszlop diagramon ábrázoljuk. Ennek bemutatásához az egyik korábbi példa értékeit használjuk fel picit a cellaértékeket átalakítva, ugyanis a program nem tudja értelmezni diagramkészítés szempontjából az egyesített cellákat és azok értékeit. Az oszlopdiagram átalakítását úgy végezzük el a szükséges formázásokon kívül, hogy a az oszlopok szélességének 0%-ot adunk meg. 32/81 5.2 Ogiva Ogivát úgy kapunk, hogy ha az osztályközös gyakoriságok kumulált gyakorisági értékeit ábrázoljuk osztályközönként egy vonaldiagramon. Hasonlóan a hisztogramhoz, itt is a korábbi osztályközös példa értékeit vettük alapul. 5.3 Box & Whisker’s ábra Ez a típusú ábrázolási

rendszer egy adatsor (rangsor) értékeit hivatott szemléltetni oly módon, hogy a nevezetes értékeket, mint pl. Minimum, Q1, Medián, Q3, Maximum, egy vízszintes tengely mentén téglalapokban jeleníti meg, arányosan felosztva az egyenest. A CALC-ban nincs ilyen jellegő diagram, ezért ezt a Rajz eszköztár segítségével könnyedén elkészíthetjük. Használjuk hozzá a vonalkészítı és a szöveges jelölı dobozokat. Amint megrajzoltuk célszerő az eszköztáron található 33/81 gombbal kijelölni a teljes alakzatcsoportot és csoportba foglalni ıket (jobbkattintás a kijelölt alakzatokra, majd Csoportosítás Csoportosítás). Az eredmény: 34/81 6. LINEÁRIS TRENDSZÁMÍTÁS, LINEÁRIS REGRESSZIÓ 6.1 Idısorok, lineáris trendek A statisztikai mintavételek alapján történı becslések számítása igen érdekes és hasznos lehetıségekkel kecseget a gyakorlati felhasználások szempontjából. Az idısorok évekre visszamenı statisztikai adatok

alapján adhatnak becslést egy elkövetkezendı idıszakra nézve. Lényegében kétféle csoportosítási lehetısége van a trendszámításnak Az egyik az analitikus trendszámítás, itt az idısor alakulását egy matematikai függvénnyel próbáljuk meg leírni, a másik a mozgóátlagolás módszere, ahol egy tetszıleges periódushoz tartozó adatértéket a környezı idıszakok egy különleges átlagaként határozzuk meg. Tekintsük elıször az analitikus trendszámítás módszerét a következı példán keresztül: Egy lelkes programozó 10 éven keresztül vezette folyamatosan, hogy éves szinten mennyi pizzát fogyasztott. Szeretne becslést végezni arra nézve, hogy három év múlva mennyi pizzát fog várhatón bekebelezni. Jó étvágyú barátunk statisztikáját az alábbi táblázat tartalmazza. Statisztikai ismereteink alapján a trend egyenest a következı összefüggés alapján lehet meghatározni: ∧ Y t = b 0 + b1t Ahol: t , az idıpontot

jellemzı periódusok számszerősített értéke ∑ tYt − (∑ t ∑ y t ) / n b1 , a trend egyenes meredeksége és b1 = , ahol n 2 ∑ t 2 − (∑ t ) / n az adatsorok darabszáma. b 0 , az egyenesnek az y -tengelyre vonatkozó metszéspontja, ennek számítása: b 0 = y − b1t , ahol y és t átlagokat jelölnek. 35/81 A kívánt eredményt megkaphatjuk úgy is, ha a képletben szereplı valamennyi tagot külön-külön cellákba kiszámítjuk, és a végén határozzuk meg a kívánt paraméterek értékét. De a CALC-ban szerencsére létezik erre egy sokkal egyszerőbb megoldás Elıször ábrázoljuk a ponthalmazt grafikusan. Ehhez használjuk a Diagramtündért, ezen belül a PONTXY diagramtípust. A pontok elhelyezkedésébıl látható, hogy növekvı trendet követ a fogyasztás alakulása, méghozzá egy egyenes mentén. A következı függvények segítségével meg tudjuk határozni a fenti ponthalmazra legjobban illeszkedı lineáris függvényt. Meredekség:

=SLOPE(Y adatok; X adatok). Y-tengely metszéspontja: =INTERCEPT(Y adatok; X adatok) Becslés egy konkrét értékre nézve: =FORECAST(Érték; Y adatok; X adatok) A meredekség kiszámításához kattintsunk egy tetszıleges cellába, hívjuk meg a =SLOPE() függvényt, majd az y adatok paraméternek jelöljük ki a pizza fogyasztás oszlopát, x adatoknak az idıperiódusokat számláló t értékek oszlopát. Az Y-tengelyre vonatkozó metszéspont meghatározásához ismét kattintsunk egy tetszıleges cellába, alkalmazzuk az =INTERCEPT() függvényt. A paraméterezés ugyanaz, mint az elızı bekezdésben tárgyalt =SLOPE() függvény esetében. Ügyeljünk arra, hogy a kijelölt oszlopok fejlécet ne tartalmazzanak, kizárólag numerikus értékek szerepeljenek a cellahivatkozások között. 36/81 A megoldást az alábbi két ábra szemlélteti. Ettıl a ponttól kezdve ismerjük a lineáris függvényünk két meghatározó paraméterét, így fel tudjuk írni az egyenest:

∧ Y t = 11,6 + 6,7 ⋅ t Most határozzuk meg, hogy három évvel késıbb várhatóan mennyi pizzát fog évente rendelni a feladatban szereplı programozónk. Ehhez két lehetıség is a rendelkezésünkre áll, vagy a meglévı számított cellák alapján írjuk fel a fenti függvény implementált és behelyettesített alakját egy cellába, vagy alkalmazzuk a =FORECAST() függvényt. De bármelyiket is választjuk, elıször meg kell határozzuk, hogy a 2011-es év a jelen metrika szerint hányadik periódus lesz. Jelen esetben a keresett érték t = 13 . 37/81 Az alternatívák: A =FORECAST() függvény használatához kattintsunk egy tetszıleges cellába, majd az érték paraméterének adjuk meg a kívánt periódus ( t ) értékét (ezt lehet cellahivatkozással vagy önálló értékként), az y adatok paraméternek a pizza fogyasztás oszlopát, az x adatoknak pedig a fogyasztásokat. A függvény végül meghatározza, hogy várhatóan 98,1 db lesz 2011-ben a

pizza fogyasztás mértéke. Ha a már korábban meghatározott paraméterek (meredekség, tengelymetszet) alapján kívánunk dolgozni, akkor egy tetszıleges cellába írjuk be a képlet értékét cellahivatkozásokkal, azaz a példában =B16+B14*B22. Az eredmény ugyanaz.6 6 Az alábbi ábrán a képlet szempontjából szükségtelen sorokat elrejtettük. 38/81 Létezik egy harmadik megközelítés is arra vonatkozóan, hogy elıállítsuk a trend egyenest, bár ez kevésbé matematika orientált. Ha a diagramunkat szerkeszthetı állapotba hozzuk (duplakattintás), akkor a Beszúrás menü Statisztika menüpontjából nyíló panel segítségével trendvonalat (lineáris regressziós egyenest) illeszthetünk a ponthalmazra. Ezek után kattintsunk duplán a diagramon megjelent trend egyenesre és a megjelenı panel Vonal fülén az egyenes küllemét adhatjuk meg, míg az Egyenlet fülön a megfelelı jelölınégyzet használatával megjeleníthetjük a trend egyenes

egyenletét a diagramon. Arra azonban figyeljünk, hogy ennél a megoldásnál az X-tengely adatértékei nem az évek értékei, hanem a számláló oszlop ( t oszlop) értékei. Ebben az esetben jutunk csak korrekt végeredményhez. 39/81 Az ábrán látható, hogy a program a megfelelı képletet meghatározza. Arra azonban felhívnánk a Kedves Olvasó figyelmét, hogy a program pontatlanul számol, pl. a polinomiális regresszió esetében, tehát a lineáris trendeken kívüli számításokat ne ezzel a beépített modullal végezzük. 6.2 Mozgóátlagok Térjünk vissza a trendszámítás másik aspektusára, a mozgóátlagok módszerére. Ezt az eljárást akkor alkalmazzuk, amikor egy adatsoron érvényesül az úgynevezett szezonhatás, tehát az idıperiódus jellegét (tél, nyár stb.) is figyelembe kell venni a számításokkor. Tekintsük itt is egy példán keresztül a megoldás menetét. Az energiaszolgáltatók egy lelkes ügyfele négy éven át feljegyezte

a negyedéves gázszámláinak alakulását. Kíváncsiak vagyunk, hogy negyedévente milyen ütemben változik a fizetendı összeg. A kifizetésre került összegek az alábbi táblázatban láthatók A mozgóátlag számítás lényege, hogy minden periódushoz megpróbálja az elıtte és utána lévı idıpontokat figyelembe venni. Mivel jelen jegyzet keretein belül kizárólag négytagú mozgóátlagokkal foglalkozunk, ezért ennek számítása oly módon valósul meg, hogy egy kiválasztott periódus értékén kívül vesszük még az azt megelızı és az azt követı két értéket. Az így kapott öt elem közül az átlag számítás során az elsı és az 40/81 utolsó értéknek csak a felét vesszük bele az összegzésbe, majd osztjuk néggyel. Képletben általánosan felírva: y i−2 y + y i −1 + y i + y i +1 + i + 2 2 yi = 2 4 A könnyebb kezelhetıség és a jobb átláthatóság kedvéért alakítsuk át a táblázatunkat a következıre: Látható, hogy

az egyes negyedéves értékeket évenként egymás alá írtuk. Ez azért praktikus, mert a képletben lévı tulajdonságot, hogy az aktuális sorértéken kívül még a fölötte és az alatta lévı értékekre kell hivatkozni, könnyen továbbörökíthetjük. Azonban vegyük észre, hogy a mozgóátlagok számításakor (4MÁ oszlop) az elsı és utolsó két cella nem kaphat értéket, mert nem ismerjük az ıket megelızı, illetve a rákövetkezı értékeket. Ez a mozgóátlagolás vesztesége A következı ábrán látható az egyes cellák tartalma és számítás módja. = (C2 / 2 + C3 + C4 + C5 + C6 / 2 ) / 4 41/81 A cellahivatkozásokat mindenképp relatív hivatkozással adjuk meg, hiszen a képletmásoló segítségével így a többi cellának is érétket adhatunk. Az elsı és utolsó két cellába vagy ne írjunk semmit, vagy tegyünk egy „-” jelet. Ezzel megkaptuk a környezı periódusok alapján számolt dinamikus átlagokat. Meg kell nézzük még,

hogyan viszonyulnak az egyes negyedéves értékek az átlagukhoz. Azaz ki kell számoljuk a negyedévekre vonatkozó szezonalítási értékeket a következı üres oszlopba az alábbi képlet alapján. ∧ St = Yt 4MÁ Ügyeljünk arra, hogy minimum három tizedesjegyre kerekítsük a kapott értékeket. = C4 / D4 Miután ezzel a mőveletsorral megvagyunk, egy olyan táblázatot kell készítsünk a kiszámított értékekbıl, ahol jó látszanak az egyes negyedévekre vonatkozó szezonális hatások minden évben. Készítsük el tehát az alábbi ábrán látható táblázatot A táblázat üres mezıibe az évek és negyedévek találkozásihoz a megfelelı szezonális indexet írjuk be a fenti táblázatból. Ezt lehet egyesével bevinni, de nagyon meggyorsítja a mőveletet, ha a =TRANSPOSE(Mátrix1) függvényt használjuk, mely a Mátrix1 paraméterében kijelölt oszlopból egy sort készít. 42/81 Mivel ez a függvény ún. tömbértékő függvény, nem tudjuk a

képletmásolás segítségével a többi sorba átmásolni. Minden egyes sorban az I negyedév cellájába meg kell hívni és az adott évre vonatkozó tartományt ki kell jelölni a számára. Miután kitöltöttük a táblázat megfelelı celláit, nincs más dolgunk, mint az egyes negyedévek oszlopait átlagolni az =AVERAGE() függvény segítségével. Ezzel megkaptuk az ún. nyers szezonindexeket De még meg kell vizsgáljuk ezen szezonindexek átlagát (korrekciós tényezı). Akkor fogadhatjuk el ıket, ha az átlaguk egészen pontosan egy. Ezt szintén az =AVERAGE() függvénnyel végezzük el. Ha ez a számított érték nem egyenlı egyel, akkor tisztítanunk kell ıket, vagyis minden egyes nyers szezonindexet le kell osztanunk az elıbb említett korrekciós tényezıvel. 43/81 Amint látható is az ábrán a korrekciós tényezı egynél nagyobb, ezért elvégeztük a tisztítást. Végeredményül a következıt kaptuk: Elıször is észrevehetjük, hogy a

tisztított szezonindexekre nézve a korrekciós tényezı pontosan egy. Jelentésüket tekintve a szezonindexek dimenzió nélküli, akár százalékban kifejezhetı értékek. Például a IV negyedévre nézve azt tapasztalhatjuk, hogy a gázszámlák növekedési üteme évrıl évre átlagosan 11,5%, míg a III. negyedévben 49,8%-os csökkenés figyelhetı meg. 44/81 6.3 Lineáris regresszió A regresszió számítás esetében a trendekhez nagyon hasonlóan megpróbálunk egy a ponthalmazra legjobban illeszkedı görbét meghatározni. Lineáris regresszió esetében egy egyenest próbálunk felírni. Ha visszaemlékszünk az analitikus trendszámításra, akkor ott az idı függvényében ábrázoltuk az értékeket. Regressziós számítások esetén két változónk van, X és Y Egyiket független változónak, a másikat függı változónak tekintjük és a hozzájuk tartozó értékpárok alapján próbálunk meg következtetést levonni, illetve becslést végezni egy

tetszıleges X értékre vonatkozóan. Ha jobban belegondolunk, ennek a felírásnak egy speciális esete volt a trendek számítása, hiszen ott a független változó az idı volt. A trendszámításnál ismertetett képlet tehát a következıre módosul: yˆ = b1 ⋅ x + b 0 , ahol b1 = ∑ x y − (∑ x ⋅ ∑ y )/ n ∑ x − (∑ x ) / n i i i 2 i i 2 és b 0 = y − b1 ⋅ x i Természetesen itt is elıttünk a lehetıség, hogy a fenti képleteket „lefordítsuk” cellahivatkozásokra, de a trendeknél ismertetett eljárások itt is mőködnek. Nézzük ezt is egy példán keresztül. Egy lelkes hazai UFO rajongó egy éven keresztül feljegyezte, hogy havonta hányszor visz rózsát a párjának és ennek tükrében mennyi az otthonában tapasztalható „repülı csészealjak” észleléseinek a száma. Adjunk becslést arra vonatkozóan, hogy egy tetszıleges rózsamennyiségre nézve várhatóan mennyi lesz az „idegen” tárgyak havi észleléseinek

száma. 45/81 Tekintsük X változónak a rózsák számát és Y változónak az észleléseket. Elıször is ismét ábrázoljuk az értékeket egy PONTXY diagramon. A pontok elhelyezkedésébıl látható, hogy egyfajta linearitást követnek. Ehhez a meredeksége és az Y tengelyre vonatkozó tengelymetszetet - ugyanúgy, mint a lineáris trendeknél ismertetett módon -, itt is a =SLOPE() és az =INTERCEPT() függvényekkel határozzuk meg. Ha tisztában vagyunk azzal, hogy változóink (X és Y) milyen jelentéstartalommal bírnak, nem lesz gondunk a paraméterezéssel. Mindkét függvény esetén x adatoknak a hozott rózsák oszlopát adjuk meg, y adatoknak pedig a csészealjak oszlopát. 46/81 Határozzuk meg egy tetszıleges rózsamennyiségre nézve, hogy mennyi gyorsan mozgó idegen tárgy tarthat havonta UFO rajongónk felé. Ehhez kattintsunk egy tetszıleges cellába, majd vagy a =FORECAST() függvényt alkalmazzuk vagy az elıbbiekben kiszámolt két

paramétert. Jelen esetben az utóbbi megoldás kerül ismertetésre. Láthatjuk, hogy havi 7 szál rózsa esetén várhatóan 13,1-szer fog a példában szereplı egyén „nulladik típusú” találkozásokkal szembesülni. A ponthalmazra illesztett egyenes pedig a következıképpen fest. Azonban felmerül a kérdés, hogy mennyire erıs a kapcsolat a két változó között illetve, hogy hihetünk-e egyáltalán a regressziónknak, azaz megfelelıen illeszkedik az egyenes a ponthalmazra. A statisztikában erre különféle módszerek szolgálnak, hipotézisvizsgálat, különbözı mutató számok Jelen esetben két mutató értékét fogjuk meghatározni. Elsınek a Pearson-féle korrelációs együtthatót, mely a két változó közötti kapcsolat erısségét és irányát méri. 47/81 Az irányát a mutató elıjele szolgáltatja, a kapcsolat szorosságát az érték abszolútértéke jelzi. Ugyanis ha az egyhez közeli érték, akkor szorosnak mondjuk a kapcsolatot, ha

nullához közeli értéket kapunk, akkor gyengének. Az erre vonatkozó függvény: =PEARSON(1. adat;2 adat) A paraméterezésben nincs megkötés arra nézve, hogy melyik adatoszlopot melyik paraméternek feletessük meg. Látható, hogy a változók között igen szoros negatív kapcsolat áll fenn, tehát amint növeljük az egyik változó értékét, úgy csökken a másik. Ez természetesen látszik is a meredekség értékébıl is. A másik mutatószám a determinációs együttható, ami a voltaképpen a korrelációs együttható négyzete és az egyenes ponthalmazra vonatkozó illeszkedésének „jóságát” méri, ha 0,7 fölötti az érték jónak mondjuk a regressziós egyenest. Erre is létezik függvény: =RSQ(y adatok;x adatok). Amint megfelelıen paramétereztük a függvényt, már ellenırizhetjük is az eredményt. Látható, hogy egyhez igen közeli értéket kaptunk, tehát az egyenes jól illeszkedik a ponthalmazra. . 48/81 7. PÉNZÜGYI

ALKALMAZÁSOK 7.1 Kamatszámítás Kamatszámítás esetén általában a kamatos kamatot értjük7. Ez képletben kifejezve C n = C 0 ⋅ (1 + i ) n , ahol C 0 a kamatoztatni kívánt összeg n a periódusok száma i= p , ahol p a kamatláb „%”-ban meghatározott értéke 100 A CALC-ban hatványozás lévén használhatjuk a „^” operátort vagy a =POWER() függvényt. Azonban mivel egy képletet kell átültetni a táblázatkezelı környezetébe gyorsabb, ha az operátort alkalmazzuk. Azaz egy tetszıleges cellába írjuk a következıt: =Kezdeti érték*(1+Kamatláb)^Idıszakok száma 7.11 Kamatláb átszámítása más idıszakra Elıfordul, hogy tetszıleges idıbeli mértékegységben megadott kamatláb nem megfelelı a számításainkhoz, ezért át kell térjünk egy másik metrikára. Az alapelve a váltásnak, hogy azt a kamatlábat keressük, mely ugyanazt a növekményt eredményezi, de egységnyi idı alatt. C 0 ⋅ (1 + j) = C 0 ⋅ (1 + i ) n C 0 -al

egyszerősítünk, majd 1-t kivonunk. j = (1 + i ) − 1 , ahol j az új idıszakra vonatkozó kamatláb. n Pl. A havi kamatláb 1,5% Mennyi az éves? Mivel 1 év 12 hónap, ezért: j = (1 + 0,015) − 1 = 0,1956 = 19,56% 12 7 Létezik az egyszerő kamat konstrukciója C n = C 0 ⋅ (1 + n ⋅ i ) , de ezzel a jegyzet külön nem foglalkozik. 49/81 CALC-ban képletként: =(1+Kamatláb)^Periódusok száma-1 Fontos megjegyezni, hogy a Periódusok száma lehet tört érték is, ha egységnyi dimenzióról részegységekre térünk át. Pl ha évesrıl havi kamatra, vagy haviról napi kamatra váltunk át. Ilyenkor ezt az értéket zárójelek közé kell tenni (lásd alábbi ábra) 7.2 Jelenérték, Jövıérték Jelenérték: A jelenérték azt mutatja meg, hogy egy befektetés esetén az egyes jövıbeli törlesztések összesítve a jelen idıszakra vetítve mennyit érnek. Erre kínál megoldást a következı függvény: =PV(Kamatláb; Idıszakok száma; Részlet;

Jövıérték) Egy zenekar menedzsere különbözı promóciós anyagok segítségével remél havi garantált 100.000 Ft-os profitot 3 éven keresztül Mennyit fektessen reklámokba, ha ésszerően kíván eljárni évi 10%-os banki kamatláb mellett. Ne feledjük el, hogy a zenekar kap havonta 100.000 Ft-ot, így a Részlet pozitív, a Kamatlábat viszont át kell váltani, és ehhez használjuk fel a korábbiakban ismertetett eljárást. Ezek szerint 3.118646 Ft befektetése meghozhatja a kívánt fellendülést 50/81 Jövıérték: Egy befektetés jövıbeni értékét mutatja meg az aktuális piaci kamatláb figyelembe vételével, melyet állandónak tekintünk. Erre kínál megoldást a következı függvény: =FV(Kamatláb; Idıszakok száma; Részlet; Jelenérték) Egy befektetési alapba 10 éven át befizetünk minden évben 600.000 Ft-ot 11%os kamat mellett Mennyi pénzünk lesz a 10 év végén? Mivel a részletek és kamatláb ugyanarra az idıperiódusra

vonatkozik, nem kell átváltani ıket, azonban figyeljünk arra, hogy a Részlet negatív érték kell legyen. Egy bankba megfelelı betétjére lekötünk 1.000000 Ft-ot 2 évre évi 13,5%-os kamat mellett. Mennyi pénzünk lesz két év múlva? Mivel itt nem fizetünk részletet ezért ezt a paramétert nullára állítjuk, ellenben a Jelenértéknek meg kell adnunk az egyösszegő befizetést. 51/81 7.3 Hiteltörlesztések A következıkben a hiteltörlesztéseknek két változatát mutatjuk be. Az elsı a fixtıkés hiteltörlesztési konstrukció, a másik az annuitásos modell. Bármelyik típussal dolgozunk is, tisztában kell lennünk azzal, hogy a periódusonkénti törlesztı részlet két komponensbıl áll össze. A kamattörlesztés és a tıketörlesztés összegébıl. A tıketörlesztés a fennálló tartozásunk visszafizetésére fordítódik, a kamattörlesztés a fennmaradó hátralék után számított érték, a kölcsön „ára”. Azaz a Törlesztı

részlet = Tıketörlesztés + Kamattörlesztés A hiteltörlesztések másik fı szempontja, hogy az utolsó periódusbeli összeggel a tartozásunkat teljes egészében visszafizessük. Fontos továbbá az is, hogy egy törlesztési modellen belül a hasonló dimenziójú paraméterek azonos mértékegységben legyenek felírva. Pl ha a kamatláb éves mértékben van megadva, mégis havi törlesztı részleteket kívánunk számolni, akkor egyezı mértékegységre kell hozni a két értéket. Vagy a kamatlábat váltjuk át havi formára, vagy az idıperiódust váltjuk át éves nagyságrendre. 7.31 Fixtıkés konstrukció Itt a tıketörlesztés nagysága állandó minden periódusra nézve. Ezt úgy érjük el, hogy a tartozás nagyságát elosztjuk a periódusok számával, majd minden periódusra nézve kiszámítjuk a fennmaradó tartozásra vetített kamatot. Periódusonként a két érték összege adja majd a részlet nagyságát. Tekintsük a következı példát:

Versenyzongorát kívánunk venni és a bank a szükséges 8.000000 Ft-ot fixtıkés törlesztési konstrukcióban kínálja részünkre évi 7% -os kamattal 10 éves futamidıre. Határozzuk meg a havonta esedékes törlesztı részleteket és ábrázoljuk a kamat- és a tıketörlesztés alakulását. Mindenek elıtt váltsuk át a kamatlábat havi mértékegységre, az egyszerőség kedvéért osszuk el 12-vel. Majd hozzuk létre a következı táblázatos elrendezést Az oszlopok sorrendje nem kötött, ellenben praktikus mert balról jobbra haladva könnyedén kitölthetı a táblázat. 52/81 Ha megfelelıen kitöltöttük, a képletmásolás segítségével a 120. periódusig másoljuk végig a cellákat. Onnan tudjuk, hogy jól dolgoztunk, hogy a Tartozás oszlop utolsó cellájában 0 értéket kapunk. Majd készítsünk egy halmozott oszlopdiagramot a Periódusok alapján a Tıkeés Kamattörlesztés oszlopokról A diagram jól szemlélteti, hogy mivel a

tıketörlesztés minden periódusban ugyanakkora, ezáltal a tartozás fokozatosan ugyanazon értékkel csökken, tehát a tartozás nagysága után számított kamat mértéke lineárisan csökken. 53/81 7.32 Annuitásos konstrukció Ez egy igen kedvelt részletfizetési metodika. Az annuitás azt jelenti, hogy a törlesztı részlet nagysága minden periódusban állandó érték. Ne felejtsük el, hogy a részlet nagysága itt is egy összeg (Tıke- és kamattörlesztés). Ennek a matematika felírása helyett a CALC függvények használatával siet a segítségünkre. Ezek a következık: =PMT(Kamatláb; Idıszakok száma; Jelenérték; Jövıérték) A függvény egy annuitásos konstrukcióban kiszámítja a törlesztı részlet nagyságát. A paraméterei:
Kamatláb: A kamat értéke.
Idıszakok száma: Amennyi idıre felvettük a kölcsönt.
Jelenérték: A felvett kölcsön összege.
Jövıérték: Nem kötelezı paraméter. A törlesztés utolsó részletekor,

ha a részleten felül fizetünk még egy meghatározott összeget, akkor azt itt kell megadni. =PPMT(Kamatláb; Idıszak; Idıszakok száma; Jelenérték) A függvény egy annuitásos konstrukcióban a tıketörlesztés nagyságát adja vissza. A paramétereit tekintve a program furán kezeli ıket, mert a súgóban több paraméter szerepel, mint amennyit a Függvénytündér használ. A fenti listában lévık megfelelnek a valóságnak 54/81
Kamatláb: A kamat értéke.
Idıszak: Az a periódus, amelyre kiszámítjuk a tıketörlesztés nagyságát.
Idıszakok száma: Amennyi idıre felvettük a kölcsönt.
Jelenérték: A felvett kölcsön összege. =IPMT(Kamatláb; Idıszak; Idıszakok száma; Jelenérték) A függvény egy annuitásos konstrukcióban a kamattörlesztés nagyságát adja vissza. Itt is van egy kis „csúsztatás”, hiszen a tájékoztató szerint ez a függvény is tıketörlesztést számol
Kamatláb: A kamat értéke.
Idıszak: Az a

periódus, amelyre kiszámítjuk a kamattörlesztés nagyságát.
Idıszakok száma: Amennyi idıre felvettük a kölcsönt.
Jelenérték: A felvett kölcsön összege. 55/81 =RATE(Idıszakok száma; Részlet; Jelenérték; Jövıérték) Egy annuitásos modellben a kamatláb értékét számítja ki. A függvényre vonatkozó tájékoztató itt sem helyes, hiszen olyan paramétereket is említ, amelyek nem szerepelnek a tényleges függvényben.
Idıszakok száma: Amennyi idıre felvettük a kölcsönt.
Részlet: A fizetendı részlet nagysága.
Jelenérték: A felvett kölcsön összege.
Jövıérték: Nem kötelezı paraméter. A törlesztés utolsó részletekor, ha a részleten felül fizetünk még egy meghatározott összeget, akkor azt itt kell megadni. =NPER(Kamatláb; Részlet; Jelenérték; Jövıérték) A törlesztés idıtartamának értékét adja vissza a függvény.
Kamatláb: A kamatláb nagysága.
Részlet: A fizetendı részlet nagysága.


Jelenérték: A felvett kölcsön összege. 56/81
Jövıérték: Nem kötelezı paraméter. A törlesztés utolsó részletekor, ha a részleten felül fizetünk még egy meghatározott összeget, akkor azt itt kell megadni. Nézzük meg a különbözı függvényeket egy-egy példán keresztül: Egy banki konstrukció 10% éves kamatra kínál 20 év futamidıre 5.000000 Ft-ot Mennyi a havi törlesztı részlet? Mennyivel módosul a részlet nagysága, ha az utolsó részlettel együtt 500.000 Ft-ot fizetünk még ki? A megoldásban vegyük fel a megadott értékeket az átláthatóság kedvéért egy-egy külön cellába. Ügyeljünk arra, hogy az idıre nézve minden érték ugyanazon mértékegység rendszerben mozogjon. Ezért valamennyi paramétert havi bontásban fogjuk ábrázolni tekintve, hogy havi részleteket kér a feladat. A megoldáshoz használjuk a =PMT() függvényt. Mivel a függvény negatív értékkel tér vissza jelezvén, hogy az összeget mi fizetjük,

ezért tegyünk egy „-” a függvény neve elé az ábrán látható módon. A részlet annyiban módosul, ha az utolsó részlet mellett, kifizetünk 500.000 Ft-ot, hogy a függvény Jövıérték paraméterének megadjuk ezt az értéket, de negatív elıjellel. 57/81 Fel kívánunk venni 3.000000 Ft-ot, 10%-os éves kamat mellett, de csak havi 35000 Ft részletet tudunk vállalni. Mennyi ideig tart a törlesztés? A megoldáshoz az =NPER() függvényt használjuk. Mivel a mi fizetjük a törlesztést, ezért ennek elıjele negatív. Egy bank 5.000000 Ft-ot kínál 7 évre havi 19990 Ft-ért Mekkora az éves kamat nagysága? A megoldást a =RATE() függvény kínálja, de végeredmény havi kamatértéket fog tartalmazni. Ezt vagy megszorozzuk 12-vel vagy a korábban ismertetett átszámítási eljárást alkalmazzuk. 7.4 Hitellefutások modellezése Mivel ennél a konstrukciónál a kamat- és a tıketörlesztés összege az ami állandó, ezért nagyon érdekes

megvizsgálni és ábrázolni, hogy a két érték hogyan változik az idı függvényében. Felveszünk 5.000000 Ft-ot évi 6% kamat mellett 10 évre Modellezzük a kamat- és a tıketörlesztés alakulását. A megoldáshoz az =PPMT() és az =IPMT() függvényeket használjuk majd. Továbbá kihasználjuk azt a tulajdonságot, hogy a részlet nagysága a kettı összege. 58/81 Hozzuk létre a következı táblázatos elrendezést: Kezdetnek határozzuk meg a tıketörlesztések nagyságát. Kattintsunk a C10-es cellába és hívjuk meg a =PPMT() függvényt. Az egyes paraméterek meghatározása az ábra alapján nem okoz gondot, talán egy kivételével. Ez pedig az Idıszak Itt ugyanis azt kell a program számára általános szabályként rögzítenünk, hogy a törlesztési idıszak hányadik periódusában vagyunk éppen. Ezt úgy érjük el, hogy az aktuális oszlopban definiált évszámból kivonjuk a nulladik év, azaz 2008 értékét. Így a 2009 év oszlopában az

Idıszak értékére 1-et kapunk, majd 2010-ben 2-t és így tovább. Mivel a függvény negatív értéket ad vissza, így most is tegyünk egy „-” jelet a neve elé. Ne felejtsük el a megfelelı cellahivatkozásokat rögzíteni, tehát abszolút hivatkozásként megadni ıket (C7 kivételével)! Szerencsénkre, a kamattörlesztésre használandó =IPMT() függvény paraméterezése teljesen ugyanaz, mint a =PPMT() függvényé, így a C11-es cellába a fenti ábrán látható módon ismételjük meg a paraméterek megadását, csak ezt az =IPMT() függvényre vonatkozóan tegyük. Mivel a visszatérési érték itt is negatív, ezért „-” jelet itt se felejtsük el kitenni. A részlet nagysága nem más, mint az elıbb kiszámított két cella összege, ezért a C9-es cellába adjuk ıket össze. 59/81 A fennmaradó tartozás érétkét úgy kapjuk meg, hogy az elızı évbeli (tehát jelen esetben 2008) tartozásból levonjuk a tıketörlesztés nagyságát. Ha

megfelelıen paramétereztük az egyes cellákat, akkor a képletmásolóval húzzuk végig a sorokat egészen 2018-ig. Ha nullát látunk az utolsó oszlop, a Tartozás sorában, akkor jól dolgoztunk. Ábrázoljuk ismét kamat- és tıketörlesztés alakulását az idı függvényében egy kombinált oszlopdiagramon. 60/81 Látható, hogy az elsı néhány évben a kamattörlesztés aránya igen magas, a részlet nagyságához képest. Ez az arány 44,16%-ról indul jelen esetben és az utolsó évben ez a mutató már csak 5,66%! 7.41 Kamatláb változása Módosítsuk az elızı feladatot annyiban, hogy 6 év elteltével a bank úgy dönt, hogy megemeli a kamatlábat 10%-ra. Hogyan változik a törlesztés? Az elızı megoldást csak kicsit kell módosítsuk, ugyanis az elv a következı. Elkezdjük megoldani az alap feladatot, mint az elızı példában. Azonban mikor oda jutunk, hogy a képletmásolóval örökítjük a cellákban rögzített számítási metódusokat,

csak 2014-ig másoljuk a tulajdonságokat, a 2015-ös év már másképpen kap értéket, hiszen ettıl kezdıdıen változik a kamatláb. Úgy tekintünk a feladatra, mintha 2015-ben kezdenénk a hiteltörlesztéshez, az új kamatlábbal és azzal az összeggel, ami idáig tartozásként fennmaradt. A ciklusok száma a megmaradt idıszakok száma, azaz 4 év Ábrázoljuk ismét a kamat- és tıketörlesztéseket: A diagramról jól látszik, hogy 2015-ben módosítottunk a hitel körülményein, mégpedig oly módon, hogy a részlet megnıtt. 61/81 7.42 Futamidı változása Ismét módosítsuk a kezdeti feladatot annyiban, hogy 6 év elteltével kérelmezzük a hitel futamidejének meghosszabbítását a maradék 4 éven túl további 3 évvel. Hogyan változik a törlesztés? Jelen esetben sincs nehezebb dolgunk, mint az elızı feladat kapcsán, ugyanis az alap feladat megoldását, cellahivatkozásait itt is a képletmásoló segítségével a 2014. évig húzzuk, a 2015.

év értékei másképpen alakulnak Az elv ugyanaz, mint a kamatláb módosításánál, azaz elızı év tartozását vesszük fel egy kezdeti hitelkonstrukcióként, ugyanazzal a kamatlábbal, de a kibıvített futamidıvel, vagyis 7 évvel. Láthatjuk, hogy a törlesztı részlet csökkent. Ezt a mellékelt diagramon is láthatjuk 7.5 Nem annuitásos konstrukciók Amikor a törlesztés olyan jellegő, hogy sem a törlesztı részlet nagysága sem egyéb paraméter nem állandó a teljes futamidı alatt, akkor nyúlunk más számítási módszerhez. Tekintsük a következı példát. 62/81 Egy ismerısünk a következı feltételekkel adott kölcsön 8.000000 Ft-ot A kamatláb évi 7%. Az elsı három évben csak a kamatot fizetjük, majd a következı három évben egy meghatározott összeget, azután a rákövetkezı három évben ennek az alapösszegnek a kétszeresét, majd újabb egy évig az alapösszeg négyszeresét. Mekkora legyen ez az alapösszeg, hogy

visszafizessük a tartozásunkat? A fenti idıszakok szerint a futamidı 10 év. Ennek megfelelıen vegyük fel elıször a táblázatunkat, majd vegyünk fel egy tetszıleges cellába egy alapösszeget egy általunk kitalált kezdıértékkel. Ha az elsı három évben csak kamatot fizetünk, úgy a tıketörlesztés nulla és a kamatot a fennálló tartozás után fizetjük. A következı három évben már megjelenik ez az alapösszeg, itt nincs más teendınk, mint a törlesztı részletnek megadni abszolút hivatkozással ezt az összeget tartalmazó cellát. A kamattörlesztést még mindig a fennálló tartozás után számítjuk, egyedül a tıketörlesztés esetében van változás, mert itt ez az érték a részlet és a kamattörlesztés különbségeként áll elı. 63/81 A következı három év a számítás szempontjából nem sokban változik, egyedül abban, hogy a részlet nagysága az alapérték kétszerese lesz. A többi cella változatlanul ugyanúgy kap

értéket. Az utolsó év sem kimondottan változatos a számítási mőveletek tükrében, ugyanis csak a részletet kell módosítani úgy, hogy az alapérték négyszerese legyen. Minden egyébb cella kiszámítási módja változatlan. Amint láthatjuk a tartozás jelentısen egy negatív érték jelen esetben, mert az alapösszeget túl nagyra állítottuk be. Ez azonban cseppet sem baj, hiszen a CALC rendelkezik egy Célértékkeresés nevő funkcióval, mely egy általunk meghatározott értékre állít egy cellát feltéve, ha az valamely egyéb más cella alapján kap értéket. Ezt a funkciót az Eszközök Célértékkeresés alatt találjuk. A paraméterezése rendkívül egyszerő. A Képletcellába az a hivatkozás kerül melyet egy adott értékre (célérték) kívánunk beállítani, a Keresett érték az elıbb említett célérték, míg a Változó cella az, amelytıl függ a Képletcella. 64/81 Jelen esetben a Képletcella az utolsó periódusbeli

tartozás, ezt nullára akarjuk állítani, tehát a Keresett érték 0. Mivel az egész számítás egyedül az alapösszeg nagyságától függött, így a Változó cella ez az érték lesz. Ha az OK gombra kattintunk, a program rákérdez, hogy beillessze-e a megoldását vagy sem. Ha elfogadjuk, az alapösszeg egyenlı lesz 851085 Ft-tal 7.6 Projectek összehasonlítása Számtalanszor fordul elı, hogy különbözı befektetési lehetıségekkel találkozunk, és ezeket szeretnénk összehasonlítani, azaz arra vagyunk kíváncsiak, hogy a kettı vagy esetleg több befektetés közül melyik éri meg jobban, ha egyáltalán ezek nyereségesek. Mivel szembekerülhetünk olyan konstrukciókkal, melyek idıben nem azonos lefutásúak (egyik késıbb kezdıdik, mint a másik, vagy hosszabb-rövidebb idı alatt ígér megtérülést), ezért egy konkrét idıpontra vonatkozóan nézzük meg a hozamuk értékét. A pénzügyi gyakorlatban ez a viszonyítási periódus mindig a

jelenlegi idıpont. Ezért ezeknél a konstrukcióknál ún. nettó jelenértéket számítunk Ehhez a CALC-ban az =NPV(Kamatláb; Értrék1; Érték2; ) függvény szolgál. Azt tudni kell a függvényrıl, hogy a nulladik periódus pénzáramlását a függvény nem értelmezni, ezért azt a végén hozzá kell adni az eredményhez. Egy másik igen fontos függvény az =IRR(Érték; Becslés), mely egy project ún. belsı megtérülési rátáját számítja ki, vagyis azt a kamatláb értéket, amely mellett a project nullszaldós. 65/81 A befektetésekkel foglalkozó cég a következı ajánlatot teszi: most befektetünk 1.200000 Ft-ot, majd a következı három évben kapunk 450.000 Ft-ot, azután az utolsó évben 300.000 Ft-ot A piaci kamatláb 11% Döntsük el, vajon megéri-e a befektetés? Vigyük fel az adatokat elıjelhelyesen egy táblázatba azaz, ha mi fizetünk akkor az érték negatív, ellenben pozitív. Majd egy tetszıleges cellába híjuk meg az =NPV()

függvényt. A kamatlábat megadhatjuk numerikusan vagy cellahivatkozással, ellenben az Érték1 paraméternek csak az elsı évtıl kezdıdı adatértékeket adjuk meg. A nulladik periódus értékét vagy a Képlet ablakba kattintva vagy késıbb a szerkesztılécben adhatjuk hozzá a függvényhez. Látható, hogy a befektetés pozitív megtérüléssel járt és a befektetés hozadéka ennyit ér a jelen idıszakban. A belsı megtérülési ráta is egy fontos mutató. Az Érték paraméternek jelöljük ki a teljes projectbeli értékeket, a nulladik értékkel együtt! Mivel a függvény algoritmusa egy közelítı számítás sorozat (ún. iterációs számítás) segítségével határozza meg a kamatláb lehetséges értékét/értékeit, ezért szüksége van egy kiindulási értékre. Erre vonatkozik a Becslés paraméter. Ha nem adunk meg erre vonatkozóan értéket, akkor automatikusan 10%-tól indítja a számítást. 66/81 De nézzünk egy érdekesebb

alkalmazási terültet, amikor két vagy több projectet hasonlítunk össze. Egy befektetésekkel foglalkozó cég jelenleg két lehetıséget kínál. Az elsı (A) project esetében induláskor kapunk 1.500000 Ft-ot, majd 2 éven át fizetünk 300000 Ft-ot, egy évig 100.000 Ft-ot, azután 200000 Ft-ot, majd ismét újabb 1 évig 100000 Ft-ot, végül kapunk 900.000 Ft-ot A másik (B) project kezdetekor fizetünk 1800000 Ft-ot, a következı évben kapunk 3.000000 Ft-ot, majd 2 éven át fizetünk 1800000 Ft-ot, ezután 2 évig kapunk 1.500000 Ft-ot, végül 2000000 Ft-ot Vizsgáljuk meg, hogy különbözı piaci kamatlábak esetén melyik projectbe fektessünk! Elıször is vigyük fel az adatok egy táblázatba, majd egy újabb táblázatban rögzítsük az egyes kamatlábakhoz tartozó nettó jelenértékeket a két projectre nézve. Gondot okozhat, hogyha két project nem azonos idıpontban indul. Mivel a jelenlegi (0) idıpontra vonatkoztatva végezzük a számítást, ezért

mindkét esetben ugyanakkora cellatartományt jelöljünk ki, méghozzá az elsı periódustól kezdıdıen. Jelen esetben az =NPV() függvény Érték1 paraméterének jelöljük ki az elsı periódustól kezdve a cellákat egészen a 9.-ig, majd adjuk hozzá a 0 periódus értékét Ügyeljünk az abszolút hivatkozásokra! Igen praktikus a két project összehasonlítása céljából, ha ábrázoljuk az alakulásukat a kamatlábak függvényében egy PONTXY diagramon, ugyanis ebbıl jól látható, hogy a különbözı kamatláb változások hogyan befolyásolják a projectek alakulását. Az alábbi ábrán jól látható, hogy a két project alakulásának görbéi egy pontban metszik egymást. 67/81 Ahhoz, hogy korrekt módon meg tudjuk határozni, hogy mely kamatláb intervallumok esetében fektessünk A vagy B projectbe, tudunk kell a fenti metszéspont pontos értékét. A meghatározásukhoz felhasználjuk, hogy a görbék, azaz a projektek nettó jelenértékei ebben

a pontban egyenlık, vagyis a két projekt nettó jelenértékének különbsége ebben a pontban nulla. Vegyük fel egy új oszlopba a két projekt érétkeinek különbségét, majd abból számítsuk ki a különbség project nettó jelenértékeit az elızıkhöz hasonlóan és ábrázoljuk ezt a három oszlopot ugyanúgy a kamatláb függvényében egy új diagramon. Ha megfigyeljük a Különbség project nettó jelenértéke éppen ott metszi a vízszintes tengelyt, ahol az A és B project nettó jelenértékei megegyeznek. Ezt a zérushelyet a 68/81 különbség project belsı megtérülési rátájaként számíthatjuk ki (vagy a célértékkeresés funkcióval). Mivel pusztán egy zérushely van, ezért a Becslés paraméternek nem kell értéket adnunk. A fentiek tükrében levonhatjuk a következtetést miszerint, ha a kamatláb kisebb, mint 9,55% akkor a B projektbe fektetünk, ellenkezı esetben az A project-el jobban járunk. 69/81 8. GAZDASÁG MATEMATIKAI

ALKALMAZÁSOK 8.1 Valószínőségszámítás, valószínőségi változók, várható érték, szórás számítása A diszkrét valószínőségi változók esetében igen gyakran fordul elı, hogy meg kell határozni azok eloszlását. Ennek kivitelezéséhez a táblázatkezelı programok kiválóan alkalmazhatóak. A következıben egy matematikailag igen egyszerő feladatot fogunk tárgyalni. A hangsúly nem a feladat komplexitásán van, hanem azon, hogy hogyan valósítható meg annak megoldása egy tetszıleges táblázatkezelı rendszer környezetében. Vizsgáljuk két kocka dobásából származó összeget. Határozzuk meg a valószínőségi változók eloszlását, majd a várható értéket és a szórást! Tekintsük valószínőségi változóknak a dobások összegébıl képzett értékeket. Az egyes változókhoz tartozó valószínőségek kiszámítása a következıképpen történik. A dobások alkalmával összesen 36 értékpár képzıdhet. A

kívánt valószínőségek kiszámításához meg kell határozzuk, hogy az egyes valószínőségi változókban szereplı összegértékek hány darab értékpárból állhatnak elı. Ha ezeket ismerjük, nincs más teendınk, mint ezek arányát meghatározni, hiszen P(X i ) = Kedvezı esetek száma Összes eset Ehhez vegyünk fel egy olyan sort, melyben az egyes elıfordulások számát tároljuk, egy másik cellában tároljuk el az összes elıfordulás számát (ellenırzésként lehet összegzést végezni), valamint vegyük fel a valószínőségi változók értékeit szintén egy külön sorba, majd a szükséges valószínőségeket számítsuk ki egy újabb sorba, ahogyan az ábrán is látható. Ezt elég egy cellába elvégezni, mert a képletmásoló segítségével könnyen végzünk a többivel. Ezzel meghatároztuk a valószínőségi változók eloszlását n A matematikai formula szerint a várható érték: M (X ) = ∑ x i ⋅ p i , míg a szórás: i =1 (

) D(X ) = M X 2 − [M (X )] . 2 70/81 A várható érték kiszámítása nem okozhat gondot, hiszen a valószínőségi változók sorának és a hozzájuk kapcsolódó valószínőségeknek a szorzatösszege. Ez a =SUMPRODUCT() függvénnyel megoldható. A szórás kiszámításához viszont szükség van valószínőségi változók négyzetére is, melyet egy külön sorban rögzítünk. Továbbá ahhoz, hogy megkönnyítsük a szórásban ( ) szereplı kifejezés felírását, egy külön cellába meghatározzuk M X 2 értékét, mely az elıbb létrehozott sornak és a valószínőségek során a szorzatösszegeként áll elı. Ennél fogva a szórást egy külön cellába határozzuk meg, felhasználva az =SQRT() és a =POWER() függvényeket. Ez utóbbira azért van szükség, mert a képletben szerepel a várható érték négyzete is (természetesen a „^” operátor használata a megoldás szempontjából ugyanúgy releváns). A végsı megoldás az alábbi ábrán

látható. 8.11 Kétváltozós valószínőségi eloszlások Határozzuk meg az alábbi két valószínőségi változó (X és Y) perem- és együttes eloszlásait, valamint a korrelációs együttható értékét. Az alábbi táblázat mutatja az egyes változókhoz tartozó elıfordulások értékét. Az adott számításhoz szükségtelen sorokat vagy oszlopokat elrejtettünk, mert az ábrán zavaróan hatott volna. Ennek kapcsán a sorok számozása vagy az oszlopok alfabetikus értékei nem feltétlenül folytonosak az ábrákon. Ez a tény azonban nem befolyásolja a szükséges cellahivatkozások hiteles voltát. 71/81 A keresett valószínőségek számítása voltaképpen annyi, hogy megnézzük az egyes elıfordulások arányait az összeshez képest. A peremeloszlást az összeg sor és oszlop valószínőségi adják. A számítógépes megoldás tekintetében nincs más teendınk, mint felvenni egy hasonló fejlécezéső táblázatot és az elıfordulások helyére

azok arányát kiszámítani. Ezzel meghatároztuk az együttes és peremeloszlások értékét. A korrelációs együttható kiszámítása kicsit nehezebb feladat, ugyanis a következı összefüggés alapján lehet meghatározni: R ( X, Y) = Cov(X, Y ) , ahol Cov(X, Y ) = M (X ⋅ Y ) − M (X ) ⋅ M (Y ) a kovariancia D(X ) ⋅ D(Y ) értéke, mely a kapcsolat irányát határozza meg. D(X ) és D(Y ) a két változó szórása Elıször a várható értékeket határozzuk meg a =SUMPRODUCT() függvénnyel a két változóra nézve: Az M (X ⋅ Y ) meghatározása következı összefüggés alapján határozható meg: M (X ⋅ Y ) = ∑ X i ⋅ Yj ⋅ p ij . Ennek kiszámításához egy segédtáblázatot veszünk igénybe, 72/81 melybe meghatározzuk az egyes változók szorzatát. Itt az alábbi ábrán látható ún vegyes hivatkozást kell alkalmazni. Csak a szükséges sorokat, illetve oszlopokat rögzítsük a képletben. Ezután a valószínőségeket tartalmazó

táblázatnak és az újonnan készített táblázatnak képezzük a szorzatösszegét. Az egyes változók szórásainak meghatározásához szükség lesz a változók négyzetére, melyeket egy külön sorban illetve oszlopban tárolunk el (ezt is érdemes cellahivatkozásokkal elkészíteni). Miután kiszámítottuk a változók négyzeteit, ( ) ( ) határozzuk meg M X 2 és M Y 2 értékét egy-egy külön cellába a megfelelı sorok, illetve oszlopok szorzatösszegeként. A cellahivatkozásokat az alábbi ábra mutatja be A következı lépésben határozzuk meg a változókra vonatkozó szórások mértékét, mert ezek után a korrelációs együtthatót nem nehéz elıállítani. Az egy változós valószínőségi eloszlásokhoz hasonlóan határozzuk meg a szórások nagyságát. Az összefüggés ugyanúgy D(X ) = M (X 2 ) − [M (X )] , 2 illetve D(Y ) = M (Y 2 ) − [M (Y )] . Az alábbi ábrán láthatóak szükséges függvények és 2 cellahivatkozások.

73/81 A kovariancia felírása és utána a korrelációs együttható meghatározása szerencsére már nem nehéz feladat, hiszen az összes képletben szereplı érték ismert és önálló cellák tárolják ıket. Mivel a korrelációs együttható értéke nullához igen közeli (-0,14), ezért elmondhatjuk, hogy a két változó között igen gyenge negatív irányú kapcsolat áll fent. 8.2 Nevezetes diszkrét eloszlásfüggvények Jó néhány matematikai probléma kapcsán a valószínőségi változók számának felírása elég hosszadalmas (pl. nagy számok esetén), így az eloszlás meghatározása nem történhet a fenti táblázatos módszerrel, helyette a kiszámítás metodikáját rögzítették függvényekbe, melyek a valószínőségi változó értékétıl függnek. Diszkrét esetben a következı függvényeket tárgyaljuk: 74/81 8.21 Binomiális eloszlás: Amikor egy eseménynek illetve annak ellentettjének az alakulását vizsgáljuk (ettıl a

„bi” elıtag, azaz kétállapotú), akkor használjuk ezt az eloszlást. CALC-ban a =B(Kísérletek; Sp; K 1; K 2)8. Ahol a Kísérletek paraméter jelenti az elvégzett kísérletek számát, az Sp paraméter adja meg a sikeres kísérletek valószínőségét, K 1 illetve K 2 paraméterek a kísérletek számának alsó és felsı korlátai, ahol K 2 opcionálisan adható meg. Egy kockával 20 dobást végzünk. Mi a valószínősége, hogy pontosan 5-ször dobunk 6ost? 8.22 Hipergeometrikus eloszlás Itt egy alaphalmazban lévı megkülönbözetett elemekre történı választásra vonatkozóan számíthatunk valószínőséget. A CALC-beli függvény: =HYPGEOMDIST(X; N minta; Sikeres; N sokaság) Ahol:
X: A kiválasztott megkülönbözetett tulajdonságú elemek száma.
N minta: Az összes választás száma
Sikereses: A megkülönbözetett elemek száma az alapsokaságban
N sokaság: Az alapsokaság elemszáma. 8 A függvény elnevezésekor a programozók

valószínőleg fukarkodtak a karakterekkel 75/81 Nézzük egy példán keresztül. A Gonosz Mostoha a 25 almájából véletlenül csak 23-at mérgezett meg. Számítsuk ki, hogy mi annak a valószínősége, hogyha Hófehérke választ egy almát a kosárból, akkor a Királyfinak jelenése lesz az üvegkoporsónál? Amint látható, a Királyfi megjelenésének várható valószínősége 92%! 8.23 Poisson eloszlás A Poisson eloszlás nagyon hasonlóan viselkedik a binomiális eloszláshoz, csak ezt a típust akkor használjuk, ha nagy az elemszám és kicsi a valószínőség. Képletben: P (X = k ) = λk − λ e , ahol M (X ) = λ = n ⋅ p k! A CALC erre vonatkozó függvénye: =POISSON(Szám;Középérték;C) Ahol:
Szám: Az a kedvezı elıfordulás, melynek a valószínőségét kerssük.
Középérték: A várható érték, avagy a képletben szereplı λ paraméter.
C: Bináris paraméter, ha 1 akkor az eloszlás függvény értékét számítja, ha 0 a

sőrőségfüggvényét. Jockey Ewing félévente 2500 próbafúrást végez. Annak a valószínősége, hogy egy fúrásnál feltör az olaj 0,01. Mennyi a valószínősége, hogy félévente pontosan 20-szor talál olajat? A számítás elvégzéséhez λ paraméter érékét kell meghatározni, mely 2500*0,01. 76/81 Ennek a valószínősége 19%. 8.3 Nevezetes folytonos eloszlásfüggvények 8.31 Exponenciális eloszlás Használatára nézve, fıként élettartamok becslésére alkalmas. Sőrőségfüggvénye: f (x ) = λ ⋅ e − λ⋅x , ahol M (X ) = CALC-ban: 1 . λ =EXPONDIST(Szám; Lambda; C) Ahol:
Szám: Az érték melynél az eloszlást számoljuk.
A képletben szereplı λ paraméter.
C: Ha 0 sőrőségfüggvényként számol, ha 1 az eloszlás függvény érétkét adja vissza a program. Egy motoros csizma átlagos élettartam 6 év. Mi a valószínősége, hogy 4 évnél korábban tönkremegy? Jelen esetben λ=1/6. 77/81 8.32 Normális eloszlás A

leggyakoribb eloszlások egyike. Sokféle matematikai probléma modellezhetı vele =NORMDIST(Szám; Középérték; STDEV; C) Ahol:
Szám: Az érték, melynél az eloszlást kiszámítjuk.
Középérték: A várható érték, átlag nagysága.
STDEV: A szórás értéke.
C: Ha 0 sőrőségfüggvény, ha 1 eloszlásfüggvény. Egy diszkoszvetı átlaogsan54,5-re hajítja a korongját 6 méter szórással. Mennyi a valószínősége, hogy 50 méternél rövidebb a dobás? 8.33 Standard normális eloszlás Ezt a függvényt alkalmazzák, pl. hipotézis vizsgálatok során a statisztikában Lényege, hogy egy olyan speciális esete a normális eloszlásnak, ahol a várható érték nulla, a szórás pedig 1. Természetesen nagyon ritka az olyan rendszer, mely teljesíti ezt a kritériumot, ezért a megfelelı paramétereket át kell alakítani. Ezt az eljárást standardizálásnak vagy más néven normalizálásnak nevezik és egy un. Z-értéket állítunk elı Ezt a

mőveletet a =STANDARDIZE(Szám; Középérték, STDEV) függvény végzi. A paraméterek jelentése ugyanaz, mint a normális eloszlásnál. 78/81 Ellenben a létrejövı értéket tudjuk behelyettesíteni a =NORMSDIST(Szám) függvénybe, ahol a Szám paraméter a normalizált Z érték. Nézzük az elıbbi példa megoldását ezzel az eloszlással. Elıször normalizálunk, és csak utána helyettesítünk be az eloszlás függvénybe. 79/81 9. FÜGGELÉK A következı táblázat tartalmazza azoknak a függvényeknek a felsorolását, melyek a jegyzetben elıfordultak, illetve azok EXCEL-beli elıfordulásait. CALC függvények EXCEL függvények =HARMEAN(Szám1; Szám2; ) =HARM.KÖZÉP(Szám1; Szám2; ) =GEOMEAN(Szám1; Szám2; ) =MÉRTANI.KÖZÉP(Szám1; Szám2;) =AVERAGE(Szám1; Szám2;) =ÁTLAG(Szám1; Szám2;) =SQRT(szám) =GYÖK(szám) =SUMPRODUCT(Mátrix1; Mátrix2;) =SZORZATÖSSZEG(Tömb1;Tömb2;) =COUNT(Szám1; Szám2;) =DARAB(Érték1; Érték2;)

=SUM(Szám1; Szám2;) =SZUM(Szám1; Szám2;) =POWER(Alap;Hatvány) =HATVÁNY(Szám;Kitevı) =PRODUCT(Szám1;Szám2;) =SZORZATSzám1;Szám2;) =FREQUENCY(Adatok;Osztályok) =GYAKORISÁG(Adattömb; Csoporttömb) =MEDIAN(Szám1; Szám2;) =MEDIÁN(Szám1; Szám2;) =MODE(Szám1; Szám2;) =MÓDUSZ(Szám1; Szám2;) =QUARTILE(Adatok;Típus) =KVARTILIS(Tömb;Kvart) =STDEVP(Szám1;Szám2;) =SZÓRÁSP(Szám1;Szám2;) =STDEV(Szám1;Szám2;) =SZÓRÁS(Szám1;Szám2;) =SLOPE(Y adatok; X adatok) =MEREDEKSÉG(Y adatok; X adatok) =INTERCEPT(Y adatok; X adatok) = METSZ(Y adatok; X adatok) =FORECAST(Érték; Y adatok; X adatok) =ELİREJELZÉS(X; Imert y; Ismert x) 80/81 =TRANSPOSE(Mátrix1) =TRANSZPONÁLÁS(Tömb) =PEARSON(1. adat;2 adat) =PEARSON(Tömb1; Tömb2) =RSQ(y adatok;x adatok) =RNÉGYZET(ismert y;ismert x) =PV(Kamatláb; Idıszakok száma; Részlet; Jövıérték) =MÉ(Ráta;Idıszakok száma;Részlet; Jövıbeli érték;Típus) =FV(Kamatláb; Idıszakok

száma; Részlet; Jelenérték) =JBÉ(Ráta;Idıszakok száma;Részlet; Mai érték; Típus) =PMT(Kamatláb; Idıszakok száma; Jelenérték; Jövıérték) =RÉSZLET(Ráta;Idıszakok száma; Mai érték; Jövıbeli érték;Típus) =PPMT(Kamatláb; Idıszak; Idıszakok száma; Jelenérték) =PRÉSZLET(Ráta;Idıszak;Idıszakok száma; Mai érték; Jövıbeli érték;Típus) =IPMT(Kamatláb; Idıszak; Idıszakok száma; Jelenérték) =RRÉSZLET(Ráta;Idıszak;Idıszakok száma; Mai érték; Jövıbeli érték;Típus) =RATE(Idıszakok száma; Részlet; Jelenérték; Jövıérték) =RÁTA(Idıszakok száma;Részlet; Mai érték; Jövıbeli érték;Típus;Becslés) =NPER(Kamatláb; Részlet; Jelenérték; Jövıérték) =PER.SZÁM(ráta;részlet;mai érték; jövıbeli érték;típus) =NPV(Kamatláb; Értrék1; Érték2; ) =NMÉ(Ráta;Érték1;Érték2;.) =IRR(Érték; Becslés) =BMR(Értékek;Becslés) =B(Kísérletek; Sp; K 1; K 2) =BINOM.ELOSZLÁS(Sikeresek;

Kísérletek; Siker valószínősége;Eloszlásfv) =HYPGEOMDIST(X; N minta; Sikeres; N sokaság) =HIPERGEOM.ELOSZLÁS(Minta s; Hány minta; Sokaság s;Sokaság mérete) =POISSON(Szám;Középérték;C) =POISSON(X;Középérték;Eloszlásfv) =EXPONDIST(Szám; Lambda; C) =EXP.ELOSZLÁS(X;Lambda;Eloszlásfv) =NORMDIST(Szám; Középérték; STDEV; C) =NORM.ELOSZL(X;Középérték;Szórás; Eloszlásfv) =STANDARDIZE(Szám; Középérték, STDEV) NORMALIZÁLÁS(x;középérték;szórás) =NORMSDIST(Szám) =STNORMELOSZL(Z) 81/81