Legfontosabb adatelemző függvények az Excelben IV.

Az Excel egyik legnagyobb ereje abban rejlik, hogy képes nagy mennyiségű adatot gyorsan és rugalmasan feldolgozni. Sok esetben azonban nem elegendő egyszerűen összegezni vagy átlagolni egy adatoszlopot – olyan megoldásra van szükségünk, amely képes csak bizonyos feltételeknek megfelelő értékeket összesíteni, például: „Mennyit adtunk el Budapesten?” vagy csak a kiválasztott csoport alapján számolni átlagot, például: „Mi az átlagos óraszám a hétvégi műszakokban?”

Ezekhez a gyakorlati kérdésekhez az Excel négy kulcsfüggvényt kínál: SZUMHA, SZUMHATÖBB, ÁTLAGHA és ÁTLAGHATÖBB

Ezek a függvények egyaránt jól használhatók pénzügyi, humánerőforrás, logisztikai, oktatási vagy bármilyen adattáblázatos elemzésnél, ahol szűrni szeretnénk az adatokat a számítás előtt.

SZUMHA

A SZUMHA függvény (SUMIF) egy feltételes összegző függvény az Excelben. Lehetővé teszi, hogy egy tartományból csak azokat az értékeket add össze, amelyek egy megadott feltételnek megfelelnek. Akkor hasznos, ha egyetlen szűrési szempont szerint szeretnél részösszeget számolni, például „összes bevétel Budapestről” vagy „összes eladás 2024-ben”.

Szintaxis

=SZUMHA(feltételezett_tartomány; feltétel; [összeg_tartomány])

Mire használható?

• Részösszegek számítása szűrés nélkül (pl. adott városból származó eladások összege).
• Költségek, bevételek összegzése kategóriánként vagy típusonként.
• Automatikus pénzügyi, értékesítési vagy készletjelentések készítése.
• Vizsgapontszámok vagy értékelések összesítése feltételek szerint.
• Dashboardokon dinamikus értékmegjelenítés egy adott szűrő alapján.

Példa

=SZUMHA(B2:B5; „Budapest”; C2:C5)

Eredmény: 27000

Ez a képlet összeadja a C2:C5 tartomány azon értékeit, ahol a B oszlopban „Budapest” szerepel.

Előnyei

• Egyszerű, gyors módja a feltételes összeadásnak.
• Szűrés vagy Pivot-tábla nélkül készíthetsz részösszesítést.
• Használható számokkal, szövegekkel és dátumokkal is.
• Kombinálható cellahivatkozásokkal (pl. „=SZUMHA(B2:B100; E1; C2:C100)”).
• Hatékony megoldás, ha csak egy feltételt vizsgálsz.

SZUMHATÖBB

A SZUMHATÖBB függvény (SUMIFS) az Excel egyik leghasznosabb összegző függvénye, amely több feltétel alapján végzi el az értékek összeadását. Akkor érdemes használni, ha több szűrési szempont szerint szeretnél számolni, például: „Összes bevétel Budapestről júniusban” vagy „Fizetések összege csak az értékesítési részlegen dolgozó férfiak esetén”.

Szintaxis

=SZUMHATÖBB(összeg_tartomány; feltétel_tartomány1; feltétel1; [feltétel_tartomány2; feltétel2]; …)

Mire használható?

• Részösszegek számítása több szempont alapján.
• Összesített költség/befogadás/termelés különböző csoportosítások mentén (pl. időszak, helyszín, kategória).
• Komplex pénzügyi riportok és dashboardok készítése.
• Projektalapú vagy időszakos teljesítmények mérése.
• Szűrt adattartományokból gyors, pontos összegzés.

=SZUMHATÖBB(D2:D5; B2:B5; „Budapest”; C2:C5; „június”)

Eredmény: 27000

Ez a képlet összeadja a D2:D5 tartomány azon értékeit, ahol a város „Budapest” ÉS a hónap „június”.

Előnyei

• Több feltétel egyidejű szűrésére is alkalmas.
• Pontos és célzott összegzést tesz lehetővé – nincs szükség manuális szűrésre.
• Használható számokra, szövegekre és dátumokra is.
• Ideális dinamikus jelentésekhez és irányítópultokhoz.
• Kombinálható cellahivatkozásokkal: „>”&A1, B1, stb.
• Nagy adatmennyiség esetén is gyorsan működik.

ÁTLAGHA

Az ÁTLAGHA függvény (AVERAGEIF) az Excelben lehetővé teszi, hogy csak azoknak az értékeknek számold ki az átlagát, amelyek egy adott feltételnek megfelelnek. Akkor hasznos, ha például azt szeretnéd megtudni, hogy mennyi az átlagos bevétel Budapestről, vagy mennyi volt az átlagos pontszám azoknál, akik elérték a 60 pontot.

Szintaxis

=ÁTLAGHA(feltételezett_tartomány; feltétel; [átlagolandó_tartomány])

Mire használható?

• Átlagos érték számítása egy adott csoporton belül (pl. adott város, kategória, hónap).
• Kizárólag bizonyos feltételeknek megfelelő értékek statisztikai elemzése.
• Teljesítmények, eredmények, árak, pontszámok csoportos vizsgálata.
• Hibás vagy irreleváns adatok kizárása az átlagból.
• Pénzügyi vagy HR jelentések finomabb statisztikai elemzése.

=ÁTLAGHA(B2:B5; „Budapest”; C2:C5)

Eredmény: 15000

A függvény a Budapest városokhoz tartozó bevételek (15000, 12000, 18000) átlagát számolja ki.

Előnyei

• Egyszerű módja a feltételes átlagolásnak – nincs szükség szűrésre.
• Nagyon jól használható szöveges vagy numerikus feltételekkel is.
• Képes cellahivatkozásokkal is dolgozni (pl. „>”&E1 vagy A1).
• Használható dinamikus riportokban, kimutatások helyett is.
• Időt takarít meg, különösen nagy adathalmazok esetén.

ÁTLAGHATÖBB

Az ÁTLAGHATÖBB függvény (AVERAGEIFS) lehetővé teszi, hogy több feltétel alapján számold ki az értékek átlagát az Excelben. Akkor érdemes használni, ha például azt szeretnéd megtudni, hogy mennyi az átlagos bevétel Budapestről június hónapban, vagy mennyi a pontszám átlaga női dolgozók körében 2024-ben.

Szintaxis

=ÁTLAGHATÖBB(átlagolandó_tartomány; feltétel_tartomány1; feltétel1; [feltétel_tartomány2; feltétel2]; …)

Mire használható?

• Összetett, több szempont szerinti átlagolás (pl. város + hónap, nem + korcsoport).
• Riportokban és dashboardokban szegmentált elemzés készítése.
• Statisztikai elemzés: részlegek, időszakok, kategóriák szerinti bontásban.
• Kimutatásokhoz hasonló eredmények gyors lekérdezése.
• Dinamikus vizualizációk (pl. slicerrel vezérelt átlagértékek).

=ÁTLAGHATÖBB(D2:D5; B2:B5; „Budapest”; C2:C5; „június”)

Eredmény: 13500

A képlet azokat a sorokat választja ki, ahol a város Budapest és a hónap június, majd ezek bevételeinek (15000 és 12000) átlagát számolja ki.

Előnyei

• Több feltétel egyidejű vizsgálata átlagoláskor.
• Megbízható és pontos eredmény szűrt tartományokból.
• Könnyen kombinálható más függvényekkel vagy cellahivatkozásokkal.
• Nincs szükség külön szűrésre vagy segédtáblára.
• Ideális jelentésekhez, dashboardokhoz, statisztikai kimutatásokhoz.

A post-sorozat többi tagja

• Legfontosabb adatelemző függvények az Excelben I.
• Legfontosabb adatelemző függvények az Excelben II.
• Legfontosabb adatelemző függvények az Excelben III.
• Legfontosabb adatelemző függvények az Excelben IV.
• Legfontosabb adatelemző függvények az Excelben V.
• Legfontosabb adatelemző függvények az Excelben VI.