Telefonszámunk: 1-472-0679

Előrejelzés az Excel 2016 eszközeivel 1. rész

2018-10-15 - horvimi - Megtekintések száma: 182 - Kategória: Függvények
Hivatkozott Excel verzio: Excel 2016 Office365

Nemrég egy nagyvállalat munkatársai arra kértek, hogy röviden foglaljam össze számukra az Excel 2016 újdonságait, mert most fognak áttérni 2010-ről. Végül is itt az ideje, ha már megjelent a 2019-es verzió. Az újdonságok között kiemelt figyelmet szenteltünk az új előrejelző függvényeknek, és az Előrejelzési munkalapnak.

Az üzleti előrejelzés valami olyasmi, hogy múltbéli eseményeket, adatsorokat elemezve következtetéseket, jóslatokat állítsunk elő a jövőre nézve. A korábbi Excel verziókban erre csak egy függvény állt rendelkezésre, ami lineáris regressziós módszert alkalmazott. Ő volt (van) az

ELŐREJELZÉS vagy FORECAST

Na de jött az Excel 2016 és az Office 365 és bevezetett új függvényeket és egy ezeket egybefogó eszközt. Ezekről lesz most szó.

Alapfogalmak

Idősor
Minden adatpont (Y) egy időponthoz (X) tartozik. Az idősor folyamatos, az időpontok közötti távolság azonos

Szezonalitás
Az adatpontok értékeiben megfigyelhető periódikusan ismétlődő mintázat, kilengés.

Periódus
A szezonalitás mintázatának hossza, azaz mennyi adatpont vagy időpont után kezd ismétlődni

Konfidencia intervallum
A becsült vagy előrejelzett érték milyen intervallumon belül, milyen tűréssel valósulhat meg + / – irányban

Hiányzó időpontok /adatpontok
Az idősor a feltételezés szerint folyamatos. (Órák, Napok, hetek, hónapok, stb…)
Ha hiányzik egy időpont, és ezzel együtt egy adatpont, akkor azt lehet helyettesíteni:
– Nullával, vagy
– Megadhatjuk, hogy a megelőző és a következő két adat aggregációjával (Átlag, max, Min, Median..)

ELŐREJELZÉS.LINEÁRIS – FORECAST.LINEAR

Ez az utódja a lineáris regressziós függvénynek. Tekintsük a következő múltbéli adatokat:

előrejelzéslinearis

Szeretnénk megjósolni, hogy 2017 júniustól mi várható. Csak annyi a teendőnk, hogy az első zöld színű, jövőbeni adatponttól egy függvényt írunk, ami az aktuális időpontból, és a múltbéli adatokból jósol.

linearis-fuggveny

ELŐREJELZÉS.ESIM – FORECAST.ETS

Ha az adatsorunk szezonális jellegű, azaz vannak benne periodikusan ismétlődő kilengések, például karácsonyi szezon vagy fekete péntek, nyári uborkaszezon és hasonlók, akkor ezt a függvényt érdemes használni, mert ez felismeri a szezonalitást. De ezt szabályozhatjuk is.

 

FONTOS!

Minél több historikus adatunk áll rendelkezésre, annál biztosabb lehet az előrejelzés.

adatok-szezonális

Több éves múltbéli adatsor áll rendelkezésre, amiben az látszik, hogy október-november-december erősebb, mint a többi hónap.

A jövőbeni mennyiségek képletéhez használjuk az exponenciális simítás módszerét, azaz:

szezonális-függvény

ELŐREJELZÉS.ESIM.KONFINT – FORECAST.ETS.CONFINT

Ez az érték mondja meg, hogy a jósolt adat milyen hibahatáron belül várható plusz/mínusz. Ezt a függvényt használjuk az első előrejelzés sortól a Konfidencia oszlopban

Az argumentumok ugyanazok, mint az előbbinél, ezért csak az újat magyarázom.

konfidencia

Felső és Alsó határ

Ezeket úgy kapjuk meg, ha az eredetileg számított becslést az előző lépésben kiszámolt konfidencia értékkel csökkentjük, illetve növeljük.  Így minden jövőbeni időponthoz jósolhatunk egy legvalószínűbb értéket, egy attól lefelé eltérő és egy felfelé eltérő értéket.

előrejelzés-adatok

Látható, hogy a jósolt adatsorban az október-november-december időszak szintén kiugró.

Előrejelzés ábrázolása

Ha a kalkulált adatokból rajzoltatunk egy vonaldiagramot, akkor valami hasonlót kapunk.

előrejelzés-diagram

Látható, hogy 2018 januárig csak a tény adatok vannak, utána látható a három vonal a várható, a felső és az alsó határ vonalakkal.

Letölthető munkafüzet

Elorejelzes-megoldas

És persze mindez Videón is megtekinthető

Vélemény, hozzászólás?