Excel statisztikai függvények: oktatóanyag példákkal, harmadik rész

Az Excel statisztikai függvények széles skáláját kínálja, amelyek számításokat végeznek az átlagtól a bonyolultabb statisztikai eloszlási és trendvonal-függvényekig.

Ebben a cikkben részletesebben megvizsgáljuk az Excel trendvonalfüggvények kiszámítására szolgáló statisztikai függvényeit.

Kérjük, vegye figyelembe, hogy néhány statisztikai függvényt az Excel legújabb verzióiban vezettek be, ezért a régebbi verziókban nem érhetők el.

Becsült olvasási idő: 12 minuti

Trendvonal funkciók

Forecast

Az Excel előrejelzési függvénye előrejelzi a jövőbeli pontot egy lineáris trendvonalon, amely illeszkedik egy adott x és y értékkészlethez.

szintaxis

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

tantárgyak

• x: Numerikus x-érték, amelyhez új y-értéket szeretne megjósolni.
• known_y's: Ismert y értékek tömbje
• known_x's: Ismert x értékek tömbje

Vegye figyelembe, hogy a tömb hossza known_x azonosnak kell lennie a known_y és a varianciája known_x nem kell nullának lennie.

példa

A következő táblázatban a függvény FORECAST Az Excel egy további pont előrejelzésére szolgál a legjobban illeszkedő egyenes mentén ismert x és y értékek sorozatán keresztül (az F2:F7 és G2:G7 cellákban tárolva).

Amint az a táblázat F7 cellájában látható, az x=7-nél várt y érték kiszámítására szolgáló függvény :=FORECAST( 7, G2:G7, F2:F7 )

Ez adja az eredményt 32.666667 .

Intercept

Az Excel előrejelző funkciói között találjuk a Intercept. Az Excel Intercept függvénye kiszámítja a lineáris regressziós egyenes metszéspontját (az y tengely metszéspontjában lévő értéket) egy adott x és y értékhalmazban.

szintaxis

= INTERCEPT( known_y's, known_x's )

tantárgyak

• known_y's: Ismert y értékek tömbje
• known_x's: Ismert x értékek tömbje

Vegye figyelembe, hogy a tömb hossza known_x azonosnak kell lennie a known_y és a varianciája known_x nem kell nullának lennie.

példa

A következő táblázat egy példát mutat a függvényre Intercept az Excel kiszámításához használt pont, ahol a lineáris regressziós egyenes át a known_x és known_y (az F2:F7 és G2:G7 cellákban felsorolva) metszi az y tengelyt.

Le known_x és known_y a táblázatban a grafikonon vannak ábrázolva.

Amint az a táblázat F9 cellájában látható, az Elfogó függvény képlete a következő :=INTERCEPT( G2:G7, F2:F7 )

ami az eredményt adja 2.4 .

Slope

Egy másik nagyon érdekes előrejelző függvény a meredekség (Slope) Az Excel kiszámítja a lineáris regressziós egyenes meredekségét egy adott x és y értékhalmazon keresztül.

A függvény szintaxisa a következő:

szintaxis

= SLOPE( known_y's, known_x's )

tantárgyak

• known_y's: Ismert y értékek tömbje
• known_x's: Ismert x értékek tömbje

Vegye figyelembe, hogy a tömb hossza known_x azonosnak kell lennie a known_y és a varianciája known_x nem kell nullának lennie.

példa

A következő táblázat egy példát mutat a függvényre Slope (meredeksége) az Excel segítségével kiszámítható a lineáris regressziós egyenes meredeksége known_x és a known_y, az F2:F7 és G2:G7 cellákban.

Le known_x és known_y a táblázatban a grafikonon vannak ábrázolva.

Amint az a táblázat F9 cellájában látható, az Elfogó függvény képlete a következő :=SLOPE( G2:G7, F2:F7 )

ami az eredményt adja 4.628571429.

Trend

Egy nagyon érdekes Excel előrejelzési funkció a IRÁNYZAT Az Excel (Trend) kiszámítja a lineáris trendvonalat egy adott y értékkészleten és (opcionálisan) egy adott x érték halmazán keresztül.

A függvény ezután kiterjeszti a lineáris trendvonalat, hogy további y értékeket számítson ki egy további új x értékkészlethez.

A függvény szintaxisa a következő:

szintaxis

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

tantárgyak

• known_y's: Ismert y értékek tömbje
• [known_x's]: Egy vagy több ismert x érték tömbje. Ez egy opcionális argumentum, amelynek meg kell egyeznie a halmaz hosszúságával known_y's. Ha kihagyjuk, a [known_x's] az {1, 2, 3, …} értéket veszi fel.
• [új_x-ek]: Opcionális argumentum, amely egy vagy több numerikus értékek tömbjét adja meg, amelyek új x-értékek halmazát reprezentálják, amelyekhez ki szeretné számítani a megfelelő új y-értékeket. Minden tömb [új_x-ek] meg kell egyeznie a [known_x's]. Ha az érv [új_x-ek] kimaradt, akkor értéke [known_x's] .
• [költség]: Opcionális logikai argumentum, amely megadja, hogy a lineáris egyenletben a 'b' konstans y = m x + b , nullával egyenlőnek kell lennie. Maga [költség] értéke IGAZ (vagy ha ezt az argumentumot kihagyjuk), a b konstans a szokásos módon kerül kezelésre;
• Maga [költség] értéke HAMIS, a b konstans értéke 0, és az egyenes egyenlet a következő lesz y = mx .

példa

A következő táblázatban az Excel Trend függvénye az y = 2x + 10 egyenesen elhelyezkedő x és y értékek sorozatának kiterjesztésére szolgál. Az ismert x és y értékeket az A2-B5 cellákban tároljuk. a táblázatban és a táblázatgrafikonon is megjelennek.

Vegyük észre, hogy nem lényeges, hogy a megadott pontok pontosan illeszkedjenek az y = 2x + 10 egyenes mentén (bár ebben a példában igen). Az Excel Trend funkciója megtalálja a legmegfelelőbb sort az Ön által megadott értékkészletekhez.

A Trend függvény a legkisebb négyzetek módszerét használja a legjobb illeszkedési vonal megtalálásához, majd ennek segítségével kiszámítja az új y értékeket a megadott új x értékekhez.

Ebben a példában a [új_x-ek] az A8-A10 cellákban vannak tárolva, és az Excel Trend függvényét használták a B8-B10 cellákban az új megfelelő y értékek megtalálásához. Ahogy a képletsávon látható, a képlet az := TREND( B2:B5, A2:A5, A8:A10 )

Látja, hogy a Trend függvény a képletsorban kapcsos zárójelek között van { }. Ez azt jelzi, hogy a függvény a következő néven lett megadva tömbképlet .

Growth

Az Excel előrejelző funkciói között találjuk a Growth. A funkció Growth Az Excel kiszámítja az exponenciális növekedési görbét egy adott y értékkészleten és (opcionális) egy vagy több x érték halmazán keresztül. A függvény ezután kiterjeszti a görbét, hogy további y értékeket számítson ki egy további új x értékkészlethez.

A függvény szintaxisa a következő:

szintaxis

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

tantárgyak

• known_y's: Ismert y értékek tömbje
• [known_x's]: Egy vagy több ismert x érték tömbje. Ez egy opcionális argumentum, amelynek meg kell egyeznie a halmaz hosszúságával known_y's. Ha kihagyjuk, a [known_x's] az {1, 2, 3, …} értéket veszi fel.
• [új_x-ek]: Új x értékek halmaza, amelyhez a függvény kiszámítja a megfelelő új y értékeket. Ha kihagyjuk, akkor feltételezzük, hogy a [új_x-ek] egyenlő azzal, hogy [known_x's], és a függvény a számított exponenciális növekedési görbén lévő y értékeket adja vissza.
• [költség]: Opcionális logikai argumentum, amely megadja, hogy a lineáris egyenletben a 'b' konstans y = b * m^x , 1-gyel egyenlőnek kell lennie. Ha [költség] értéke IGAZ (vagy ha ezt az argumentumot kihagyjuk), a b konstans a szokásos módon kerül kezelésre; Maga [költség] értéke HAMIS, a b konstans értéke 1, és az egyenes egyenlet a következő lesz y = mx .

példa

A következő táblázatban az Excel növekedési függvénye az y = 5 * 2^x exponenciális növekedési görbén lévő x és y értékek sorozatának kiterjesztésére szolgál. Ezek a táblázat A2-B5 celláiban tárolódnak, és megjelennek a táblázat diagramján is.

A növekedési függvény kiszámítja azt az exponenciális növekedési görbét, amely a legjobban illeszkedik a megadott ismert x és y értékekhez. Ebben az egyszerű példában a legjobban illeszkedő görbe az y = 5 * 2^x exponenciális görbe.

Miután az Excel kiszámította az exponenciális növekedési görbe egyenletét, felhasználhatja az új y értékek kiszámítására az A8-A10 cellákban megadott új x értékekhez.

Ebben a példában a [new_x's] az A8-A10 cellákban és a függvényben tárolódnak Growth Az Excel beillesztésre került a B8-B10 cellákba. Ahogy a képletsoron látható, ennek képlete:=Growth( B2:B5, A2:A5, A8:A10 )

Látható, hogy a Növekedés függvény a képletsorban kapcsos zárójelek között van { }. Ez azt jelzi, hogy a függvény a következő néven lett megadva tömbképlet .

Megjegyzendő, hogy bár a fenti példa pontjai pontosan illeszkednek az y = 5 * 2^x görbe mentén, ez nem lényeges. A funkció Growth Az Excel megtalálja a legmegfelelőbb görbét bármely megadott értékkészlethez.

Pénzügyi funkciók

Hatás

A funkció Effect Az Excel egy adott névleges kamatláb és adott számú kamatozási periódus esetén az effektív éves kamatlábat adja vissza.

Effektív éves kamatláb

Az effektív éves kamatláb a kamat mértéke, amely magában foglalja a kamatkapitalizációt, és gyakran használják a különböző tőkésítési feltételekkel rendelkező pénzügyi kölcsönök összehasonlítására.

Az effektív éves kamatlábat a következő egyenlet segítségével számítjuk ki:

galamb nominal_rate a névleges kamatláb e npery a kompaundálási időszakok száma évente.

A függvény szintaxisa a következő:

szintaxis

= EFFECT( nominal_rate, npery )

tantárgyak

• nominal_rate: A névleges kamatláb (0 és 1 közötti számértéknek kell lennie)
• npery: Az összetett időszakok száma évente (pozitív egész számnak kell lennie).

példa

A következő táblázat három példát mutat be az Excel Effect függvényre:

Ha a függvény eredménye Effect decimális vagy 0%-ot mutat, mindkét probléma valószínűleg a függvényt tartalmazó cella formázásából adódik Effect.

Ezért a probléma megoldható a cella százalékos formázásával, tizedesjegyekkel.

Ezt csináld meg:

1. Válassza ki a százalékosan formázni kívánt cellákat.
2. Nyissa meg a „Cellák formázása” párbeszédpanelt a következő módszerek egyikével:
   • Kattintson a jobb gombbal a kijelölt cellára vagy tartományra, és válassza ki a lehetőséget Cellák formázása… a helyi menüből;
   • Kattintson a párbeszédpanel-indítóra a lap Számcsoportosításában Kezdőlap Excel szalag;
   • Használja a billentyűparancsot CTRL-1 (azaz válassza ki a CTRL billentyűt, és tartsa lenyomva, válassza az „1” (egy) billentyűt).
3. A „Cellák formázása” párbeszédpanelen:
   • Győződjön meg róla, hogy a kártya szám a párbeszédpanel tetején van kiválasztva.
   • Válassza ki a Százalék a listáról Kategória a párbeszédpanel bal oldalán .Ez további lehetőségeket jelenít meg a jelölőnégyzet jobb oldalán, lehetővé téve, hogy kiválassza a megjeleníteni kívánt tizedesjegyek számát.
   • Miután kiválasztotta a megjeleníteni kívánt tizedesjegyek számát, kattintson a gombra OK .

Nominal

A funkció Nominal Az Excel a névleges kamatlábat adja vissza egy adott effektív kamatlábhoz és adott számú kamatozási periódushoz évente.

A függvény szintaxisa a következő:

szintaxis

= NOMINAL( effect_rate, npery )

tantárgyak

• effect_rate: Az effektív kamatláb (0 és 1 közötti számérték).
• npery: Az összetett időszakok száma évente (pozitív egész számnak kell lennie).

példa

A következő táblázatban a függvény Nominal Az Excel segítségével három, eltérő futamidejű hitel nominális kamatlába számítható ki.

Ercole Palmeri