Jelen cikket annak szentelem, hogy sorra vegyünk néhány olyan Excel függvényt, melyek jó szolgálatot tehetnek egy riport alapjának elkészítése során.
Megírását leginkább az inspirálta, hogy gyakran szembesülök azzal a problémával, miszerint sokan nem paraméterezik fel megfelelően a riportjuk alapjául szolgáló tábláikat (függvények alkalmazása helyett abszolút módon ráhivatkoznak egy adott cellára, gondolván, hogy annak pozíciója fix és sosem fog megváltozni). Mindez azt eredményezheti, hogy a riport értékei elcsúszhatnak.
Ezek elkerülése végett ajánlatos az Excel függvénytárában rendelkezésre álló lehetőségeket alkalmazni, még akkor is, ha a riport alap egy bizonyos értéke csupán egy konkrét mezőből táplálkozik (azaz nem szükséges semmiféle művelet alkalmazása).
Az elkövetkezőkben az alábbi hasznos függvények használatát és gyakorlati alkalmazását fogjuk sorra venni:
- SZUMHATÖBB (SUMIFS)
- DARABHATÖBB (COUNTIFS)
- ÁTLAGHATÖBB (AVERAGEIFS)
Az említett függvények megadott feltételek teljesülése esetén elvégzik a szükséges műveletet (összegzés, megszámoltatás, átlagszámítás).
Ezek mind olyan függvények, melyeknek létezik az Excelben az „egyfeltételes” verziója is (SZUMHA, DARABHA, ÁTLAGHA). Gyakorlati szempontból azonban a „többfeltételes” verziók gyakrabban használatosak, mivel ezek működnek úgy is, ha csak egy paraméter feltételt adunk meg.
Mint az már megszokottá vált, egy gyakorlati példa felvetéssel kezdjük az áttekintést.
Az Autó Kft. vezetősége szeretné látni, hogy
- az egyes kollégák hány autót értékesítettek,
- mekkora összeget (bevételt) jelentett ez a cég számára értékesítőnként,
- illetve mekkora volt az egy autóra fordított átlagos költési hajlandóság a vevők oldaláról
2018Q1-ben havi bontásban.
A rendelkezésre álló alapadatok a következők:
A feladatunk tehát az lesz, hogy a vezetőség felé publikálandó riport alaptábláit létrehozzuk, melyek megfelelő alapot szolgáltatnak a bemutatandó prezentációhoz szükséges diagramok összeállításához.
A három kérdés megválaszolására három eltérő függvényt fogunk használni. Az első esetében a DARABHATÖBB, a másodiknál a SZUMHATÖBB, míg az utolsó esetben az ÁTLAGHATÖBB függvény lesz a használatos.
1. kérdés: Az egyes kollégák havonta hány autót értékesítettek? (DARABHATÖBB)
Mivel az első és a második kérdés megválaszolása csak a függvény használatában tér el egymástól, hiszen mindkettő esetében azonos bontás alkalmazására van szükség (értékesítő személy és időszak), így ezekre egy alaptáblát fogunk létrehozni.
A darabszám (db oszlop) meghatározására a DARABHATÖBB függvényt használjuk, melynek szintaktikája a következő:
=DARABHATÖBB(kritériumtartomány1; kritérium1; kritériumtartomány2; kritérium2; …)
A kritériumtartomány esetében a függvény egy tartományt vár, míg a kritérium esetében egy konkrét értéket (cellát). A függvény tulajdonképpen megvizsgálja, hogy az adott kritérium hányszor fordul elő a megadott kritériumtartományban. Több feltétel megadása esetében pedig "ÉS" kapcsolat keretében vizsgálja meg a függvény, hogy hány egyed felel meg az együttes feltételeknek.
Az első kritériumtartomány a példánkban az alap adatokat tartalmazó táblázat értékesítő kódra vonatkozó cellái lesznek, tehát az első táblázat legelső oszlopának értékei. Maga a kritérium pedig konkrétan az az értékesítő kód, amit meg szeretnénk számoltatni, azaz a második táblázatban szereplő, értékesítő személyek kódja (pl. második sornak és első oszlopnak a metszete).
Mivel így még csak azt kapjuk meg, hogy az adott értékesítő hány autót értékesített 2018Q1-ben, így még egy feltétel megadására szükség van ahhoz, hogy ezt havi bontásban lássuk.
A második kritériumtartomány megadásához – hasonlóan az elsőhöz – az alap adatokat tartalmazó első táblázatot fogjuk használni, azonban most az értékesítési időszakra vonatkozó tartományt szükséges megadnunk, azaz a negyedik oszlop értékeit. A kritérium pedig a második táblázat, megszámoltatandó időszaka (évhó) lesz (pl. második sornak és második oszlopnak a metszete).
Mindezek eredményeképpen előállt a kívánt adat az eladott mennyiséget illetően, azaz, hogy az egyes értékesítők hány autót értékesítettek a különböző hónapokban.
2. kérdés: Mekkora összeget (bevételt) jelentett ez a cég számára havi bontásban értékesítőnként? (SZUMHATÖBB)
Az összeg (összeg oszlop) meghatározásához a SZUMHATÖBB függvényt hívjuk segítségül, mely a következőképpen épül fel:
=SZUMHATÖBB(összegtartomány; kritériumtartomány1; kritérium1; kritériumtartomány2; kritérium2; ...)
A SZUMHATÖBB függvény hasonlóképpen működik, mint a DARABHATÖBB, annyi különbséggel, hogy itt az első megadandó paraméter az a tartomány, amit összegezni szükséges a megadott feltételek teljesülése esetében.
Az összegtartomány esetében az alap adatokat tartalmazó táblát használjuk, annak is azt a részét, amelyben az összegzendő értékek szerepelnek, azaz a harmadik oszlop árra vonatkozó értékeit.
A kritériumtartományra, valamint a kritériumra vonatkozó részek megegyeznek a DARABHATÖBB függvénynél alkalmazottnál, így ezek nem kerülnek részletezésre újra.
3. kérdés: Mekkora volt az egy autóra fordított átlagos havi költési hajlandóság a vevők oldaláról? (ÁTLAGHATÖBB)
A harmadik, s egyben utolsó lépésben pedig az egy autóra fordított átlagos költési hajlandóságot határozzuk meg havi bontásban (átlag oszlop).
Ehhez az ÁTLAGHATÖBB függvényt fogjuk használni annak ellenére, hogy csupán egy feltételt adunk meg.
A függvény általános felépítése az alábbiak szerint néz ki:
=ÁTLAGHATÖBB(átlag_tartomány; kritériumtartomány1; kritérium1; kritériumtartomány2; kritérium2; ...)
Mivel a függvény felépítése teljes mértékben megegyezik a SZUMHATÖBB függvényével, ezért ennek a hivatkozási rendszerét sem fogjuk teljes részletességében megnézni.
Az átlag tartományba az átlagolandó értékeket szükséges megadni, jelen esetben az árat, mely tükrözi, hogy a vásárlók mennyit hajlandók fizetni egy személyautóért. A kritériumtartomány ebben az esetben is az alap adatokat tartalmazó tábla időszakra vonatkozó értékei lesznek, a kritérium pedig az az időszak, amire átlagolni szeretnénk az árat, azaz a harmadik táblázat első oszlopában szereplő valamely hónap.
Azok kedvéért, akik vizuálisan is szeretik látni a megvalósítást:
Megjegyzés: Ha homályos a videó, érdemes állítani a képminőségen.