Szoftver

Excel: A legnagyobb érték meghatározása egy adott időszakon belül

Az adatokkal való munka során gyakran merül fel az igény, hogy egy meghatározott időintervallumon belül megtaláljuk a legnagyobb értéket. Legyen szó értékesítési adatokról, weboldal látogatottságról, hőmérsékleti mérésekről vagy bármilyen más, idősoros adathalmazról, ez a funkció kulcsfontosságú lehet a trendek azonosításában, a kiugró eredmények felismerésében vagy egyszerűen csak a teljesítmény értékelésében. Szerencsére a Microsoft Excel számos eszközt kínál ennek a feladatnak a hatékony elvégzésére.

Az alapok: Miért fontos ez és milyen adatstruktúrára van szükségünk?

Mielőtt belevágnánk a konkrét képletekbe és eszközökbe, érdemes tisztázni, miért is van szükségünk erre a funkcióra. Egy adott időszak legnagyobb értékének ismerete segíthet:

  • Teljesítménycsúcsok azonosításában: Mikor volt a legtöbb eladás? Melyik napon volt a legmagasabb a látogatottság?
  • Anomáliák észlelésében: Egy szokatlanul magas érték jelezhet hibát vagy egy különleges eseményt.
  • Összehasonlításban: Különböző időszakok csúcsértékeinek összevetése.
  • Jelentések készítésében: Fontos mutatóként szolgálhat a riportokban.

Ahhoz, hogy ezeket a számításokat el tudjuk végezni Excelben, legalább két oszlopra van szükségünk az adattáblánkban:

  1. Egy dátumokat tartalmazó oszlopra (pl. „Dátum”). Fontos, hogy ezek valódi Excel dátumformátumú értékek legyenek, ne csak szövegként bevitt dátumok.
  2. Egy numerikus értékeket tartalmazó oszlopra, amelyek közül a maximumot keressük (pl. „Bevétel”, „Látogatók száma”, „Hőmérséklet”).

Tegyük fel, hogy a következő egyszerűsített adattáblával dolgozunk:

És mi azt szeretnénk megtudni, hogy mi volt a legnagyobb érték például 2023. február 1. és 2023. március 31. között.

1. módszer: A MAXIFS függvény használata (Excel 2019 és újabb verziók) 💡

Az egyik legelegánsabb és legkönnyebben olvasható megoldás a MAXIFS függvény használata. Ez a függvény kifejezetten arra lett tervezve, hogy egy vagy több feltételnek megfelelő tartományban megkeresse a maximális értéket.

Szintaxis: MAXIFS(max_tartomány; feltétel_tartomány1; feltétel1; [feltétel_tartomány2; feltétel2]; ...)

  • max_tartomány: Az a tartomány, amelyben a maximális értéket keressük (pl. a „Bevétel” oszlop).
  • feltétel_tartomány1: Az a tartomány, amelyre az első feltételt alkalmazzuk (pl. a „Dátum” oszlop).
  • feltétel1: Az első feltétel, amit a feltétel_tartomány1-re alkalmazunk (pl. „>=2023.02.01”).
  • feltétel_tartomány2 (opcionális): Az a tartomány, amelyre a második feltételt alkalmazzuk (pl. szintén a „Dátum” oszlop).
  • feltétel2 (opcionális): A második feltétel, amit a feltétel_tartomány2-re alkalmazunk (pl. „<=2023.03.31”).

Alkalmazás a példánkra: Tegyük fel, hogy a dátumok az A2:A8 tartományban, az értékek pedig a B2:B8 tartományban vannak. A kezdő dátum (pl. 2023.02.01) az E1 cellában, a záró dátum (pl. 2023.03.31) pedig az F1 cellában található.

A képlet a következőképpen nézne ki: =MAXIFS(B2:B8; A2:A8; ">="&E1; A2:A8; "<="&F1)

Magyarázat:

  • B2:B8: Ebben a tartományban (az „Érték” oszlopban) keressük a maximumot.
  • A2:A8; ">="&E1: Az első feltétel az, hogy az A2:A8 tartományban (a „Dátum” oszlopban) lévő dátumok legyenek nagyobbak vagy egyenlőek az E1 cellában megadott kezdő dátumnál. Fontos az & operátor használata, amellyel összefűzzük a relációs jelet (>=) a cellahivatkozással.
  • A2:A8; "<="&F1: A második feltétel az, hogy az A2:A8 tartományban lévő dátumok legyenek kisebbek vagy egyenlőek az F1 cellában megadott záró dátumnál.

Ez a képlet a példaadataink alapján a 195-ös értéket adná vissza, mivel ez a legnagyobb érték a február 1. és március 31. közötti időszakban (135, 180, 160, 195 közül).

Előnyök:

  • Könnyen olvasható és érthető.
  • Nincs szükség tömbképletként való bevitelre (lásd később).
  • Hatékonyan kezeli a több feltételt.

Hátrányok:

  • Csak Excel 2019, Excel for Microsoft 365 és újabb verziókban érhető el. Régebbi verziókban más megoldást kell keresnünk.

2. módszer: Tömbképlet MAX és IF (HA) függvénnyel (Régebbi Excel verziókhoz is) ⚙️

Ha régebbi Excel verzióval dolgozik (pl. Excel 2016, 2013, 2010), a MAXIFS függvény nem áll rendelkezésre. Ilyenkor egy tömbképlet segítségével érhetjük el a kívánt eredményt, a MAX és IF (magyar Excelben HA) függvények kombinálásával.

Szintaxis (elv): {=MAX(IF(feltételek; értékek_tartománya))} Magyar Excelben: {=MAX(HA(feltételek; értékek_tartománya))}

Fontos: A tömbképleteket a beírás után nem Enter-rel, hanem Ctrl + Shift + Enter billentyűkombinációval kell jóváhagyni. Az Excel ekkor automatikusan kapcsos zárójelek {} közé helyezi a képletet (ezeket manuálisan nem szabad beírni!).

Alkalmazás a példánkra: A dátumok az A2:A8, az értékek a B2:B8 tartományban. A kezdő dátum az E1, a záró dátum az F1 cellában.

A képlet (magyar Excelben): =MAX(HA((A2:A8>=E1)*(A2:A8<=F1); B2:B8))

Bevitel után Ctrl + Shift + Enter-t kell nyomni! Ekkor így fog kinézni a szerkesztőlécen: {=MAX(HA((A2:A8>=E1)*(A2:A8<=F1); B2:B8))}

Magyarázat:

  • (A2:A8>=E1): Ez a rész egy logikai tömböt hoz létre. Minden olyan dátumra IGAZ (Excelben 1-ként értelmeződik a szorzásnál), amely nagyobb vagy egyenlő az E1 cellában lévő kezdő dátumnál, a többire HAMIS (0).
  • (A2:A8<=F1): Hasonlóan, ez is egy logikai tömböt hoz létre az F1 cellában lévő záró dátum alapján.
  • ((A2:A8>=E1)*(A2:A8<=F1)): A két logikai tömböt összeszorozzuk. A szorzás logikai ÉS műveletként funkcionál itt: csak akkor lesz az eredmény 1 (IGAZ), ha mindkét feltétel IGAZ. Ha bármelyik HAMIS (0), a szorzat 0 lesz. Ez a rész tehát egy olyan tömböt eredményez, amely 1-eseket tartalmaz azokra a sorokra, amelyek a megadott dátumintervallumba esnek, és 0-kat a többire.
  • HA((A2:A8>=E1)*(A2:A8<=F1); B2:B8): A HA függvény ezen a ponton azt vizsgálja, hogy a feltétel (1 vagy 0) igaz-e. Ha 1 (tehát a dátum az intervallumban van), akkor visszaadja a B2:B8 tömb megfelelő elemét (az adott sorban lévő értéket). Ha 0 (a dátum nincs az intervallumban), akkor a HA függvény alapértelmezetten HAMIS értéket adna vissza, amit a MAX függvény figyelmen kívül hagy a numerikus értékek keresésekor.
  • MAX(...): Végül a MAX függvény kiválasztja a legnagyobb értéket abból a tömbből, amelyet a HA függvény generált (ez a tömb csak az intervallumba eső értékeket tartalmazza, a többi helyen HAMIS van).

Előnyök:

  • Működik régebbi Excel verziókban is, ahol a MAXIFS nem elérhető.
  • Nagyon rugalmas, bonyolultabb feltételrendszerek is kialakíthatók vele.

Hátrányok:

  • A tömbképletek használata nehézkesebb lehet (Ctrl + Shift + Enter). Ha elfelejtjük, hibás eredményt kapunk.
  • Nagyobb adattáblák esetén lassabb lehet a kiértékelésük, mint a dedikált MAXIFS függvényé.
  • Kevésbé olvasható, mint a MAXIFS.

3. módszer: Az AGGREGATE függvény használata (Excel 2010 és újabb verziók) ➕➖✖️➗

Az AGGREGATE függvény egy rendkívül sokoldalú eszköz, amely képes különböző aggregációs műveleteket (mint pl. MAX, MIN, SUM, AVERAGE stb.) elvégezni egy tartományon, miközben lehetőséget ad bizonyos értékek (pl. hibák, rejtett sorok) figyelmen kívül hagyására. Ez a függvény kiválóan alkalmas a mi problémánkra is.

Szintaxis (a mi esetünkre releváns része): AGGREGATE(függvény_szám; beállítások; tömb; [k])

Magyar Excelben is AGGREGATE.

  • függvény_szám: Egy szám, amely meghatározza, melyik függvényt használjuk. A MAX függvénynek a 4-es vagy a LARGE (NAGY) függvénynek a 14-es kód felel meg (ha LARGE-ot használunk, a k argumentum 1 lesz a legnagyobb értékhez). Mi most a MAX-ot, tehát a 4-est használjuk.
  • beállítások: Egy szám, amely meghatározza, milyen értékeket hagyjon figyelmen kívül a függvény. Gyakran a 6-os opciót használjuk, ami azt jelenti: „Figyelmen kívül hagyja a hibaértékeket”. Más opciók is léteznek rejtett sorok, beágyazott SUBTOTAL és AGGREGATE függvények figyelmen kívül hagyására.
  • tömb: Az a tömb vagy tartomány, amelyen a számítást elvégezzük. Itt egy trükkös kifejezést fogunk használni a feltételek beépítésére.
  • [k]: Csak bizonyos függvény_szám értékeknél szükséges (pl. LARGE, SMALL). Ha a függvény_szám 4 (MAX), akkor ez az argumentum nem kell.

Alkalmazás a példánkra: A dátumok az A2:A8, az értékek a B2:B8 tartományban. A kezdő dátum az E1, a záró dátum az F1 cellában.

A képlet: =AGGREGATE(4; 6; B2:B8/((A2:A8>=E1)*(A2:A8<=F1)))

Magyarázat:

  • 4: A MAX függvényt használjuk.
  • 6: Figyelmen kívül hagyjuk a hibaértékeket. Ez kulcsfontosságú itt!
  • B2:B8/((A2:A8>=E1)*(A2:A8<=F1)): Ez a trükkös rész.
    • ((A2:A8>=E1)*(A2:A8<=F1)): Ez ugyanaz a logikai szorzás, mint a tömbképletnél. Eredménye egy tömb, ami 1-eseket tartalmaz a feltételnek megfelelő sorokra, és 0-kat a többire.
    • B2:B8 / ...: A B2:B8 tartományban lévő értékeket elosztjuk ezzel az 1-esekből és 0-kból álló tömbbel.
      • Ha egy érték a dátumtartományon belül van, akkor az adott B oszlopbeli értéket 1-gyel osztjuk (ami maga az érték).
      • Ha egy érték a dátumtartományon kívül van, akkor az adott B oszlopbeli értéket 0-val osztjuk. Ez #DIV/0! (osztás nullával) hibát eredményez!
  • Az AGGREGATE függvény 6-os beállítása miatt ezeket a #DIV/0! hibákat figyelmen kívül hagyja, és csak a „valódi” számértékek (amelyek megfeleltek a feltételnek) közül választja ki a maximumot.

Ez a képlet nem igényel Ctrl + Shift + Enter-t, normál Enter-rel működik.

Előnyök:

  • Nem szükséges Ctrl + Shift + Enter használata, mint a tömbképleteknél.
  • Rugalmasan kezeli a hibákat és opcionálisan a rejtett sorokat is.
  • Elérhető Excel 2010-től kezdve, így szélesebb körben használható, mint a MAXIFS. Részletes információk az AGGREGATE függvényről a Microsoft oldalán (angolul).

Hátrányok:

  • A képlet logikája (az osztás nullával trükk) elsőre talán kevésbé intuitív, mint a MAXIFS.

4. módszer: FILTER és MAX függvények kombinációja (Microsoft 365 Dinamikus Tömbök) ✨

Ha Ön Microsoft 365 előfizető, és hozzáfér a dinamikus tömb képességekhez, akkor a FILTER függvény segítségével egy még letisztultabb megoldást is alkalmazhat.

Szintaxis (elv): =MAX(FILTER(értékek_tartománya; feltételek))

Alkalmazás a példánkra: A dátumok az A2:A8, az értékek a B2:B8 tartományban. A kezdő dátum az E1, a záró dátum az F1 cellában.

A képlet (magyar Excelben): =MAX(FILTER(B2:B8; (A2:A8>=E1)*(A2:A8<=F1); "Nincs találat"))

Magyarázat:

  • FILTER(B2:B8; (A2:A8>=E1)*(A2:A8<=F1); "Nincs találat"):
    • B2:B8: Ez az a tartomány, amelyből az értékeket vissza akarjuk kapni (az „Érték” oszlop).
    • (A2:A8>=E1)*(A2:A8<=F1): Ez a feltételrész. Ugyanaz a logikai szorzás, mint a tömbképletnél, amely egy IGAZ/HAMIS (vagy 1/0) tömböt eredményez. A FILTER függvény azokat az elemeket adja vissza a B2:B8 tartományból, ahol ez a feltétel IGAZ.
    • "Nincs találat" (opcionális): Ez az érték jelenik meg, ha a FILTER függvény egyetlen sort sem talál, amely megfelelne a feltételeknek. Enélkül #CALC! hibát kapnánk ilyen esetben.
  • MAX(...): A MAX függvény egyszerűen veszi a FILTER által visszaadott (szűrt) értékek tömbjét, és megkeresi benne a legnagyobbat.

Előnyök:

  • Nagyon olvasható és logikus felépítésű.
  • Dinamikus: Ha az alapadatok vagy a feltételek változnak, az eredmény automatikusan frissül.
  • Nem igényel Ctrl + Shift + Enter-t.
  • Könnyen kezelhető az az eset, ha nincs a feltételnek megfelelő adat.

Hátrányok:

5. módszer: PivotTábla (Kimutatás) használata 🗓️

A PivotTáblák (magyar Excelben Kimutatások) kiválóan alkalmasak adatok összegzésére és elemzésére, beleértve a maximális értékek megtalálását is egy adott időszakon belül. Ez a módszer inkább interaktív elemzésre jó, és nem egyetlen cellában adja vissza az eredményt, hanem egy rugalmas jelentési struktúrát hoz létre.

Lépések:

  1. Jelölje ki az adattábláját (beleértve a fejlécet is, pl. A1:B8).
  2. Válassza a Beszúrás menüszalagon a PivotTable (Kimutatás) gombot.
  3. A megjelenő ablakban általában elegendő az alapértelmezett beállításokat elfogadni (új munkalapra hozza létre a PivotTáblát), majd kattintson az OK-ra.
  4. Megjelenik a PivotTable mezőlista panel. Itt a következőképpen konfigurálja a mezőket:
    • A „Dátum” mezőt húzza a „Sorok” (Rows) területre.
    • Az „Érték” mezőt húzza az „Értékek” (Values) területre. Alapértelmezetten valószínűleg Összeg - Érték (Sum of Érték) jelenik meg.
  5. Az „Értékek” területen kattintson a Összeg - Érték (vagy hasonló) elemre, és válassza az „Értékmező-beállítások…” (Value Field Settings…) opciót.
  6. A megjelenő ablakban az „Összegzés típusa” (Summarize value field by) listából válassza ki a „Max” opciót. Kattintson az OK-ra.
  7. Most a PivotTable minden dátumhoz megmutatja az aznapi maximális értéket (ha több érték lenne egy napra).
  8. Időszak szűrése:
    • Kattintson a „Sorcímkék” (Row Labels) melletti szűrő ikonra (▼).
    • Válassza a „Dátumszűrők” (Date Filters) opciót.
    • Itt választhat előre definiált időszakokat (pl. „Múlt hónap”) vagy az „Ezután…” (After…), „Előtt…” (Before…), vagy a legrugalmasabb „Között…” (Between…) opciót.
    • A „Között…” opciót választva megadhatja a kezdő és záró dátumot (pl. 2023.02.01 és 2023.03.31).
  9. A PivotTable most már csak a megadott időszak adatait mutatja, és a Végösszeg sorban (vagy ha csak egyetlen sor maradt a szűrés után, akkor abban a sorban) a periódus maximális értéke fog szerepelni, ha a megfelelő mezőbeállításokat (Max) használjuk. Ha a dátumokat nem csoportosítjuk tovább (pl. évekre, hónapokra), akkor a PivotTable minden egyes, a feltételnek megfelelő dátumhoz tartozó értéket listáz (ha az „Érték” mező továbbra is az Értékek területen van Max beállítással), és ezek közül a legnagyobbat kell vagy vizuálisan kiválasztani, vagy egy külön cellában a GETPIVOTDATA függvénnyel, vagy egyszerűen a PivotTable által mutatott végösszegből (ha az a maximális értékre van állítva) leolvasni.

A legegyszerűbb, ha a PivotTable-ben a „Dátum” mezőt a „Szűrők” (Filters) területre húzzuk, az „Érték” mezőt (Max beállítással) pedig az „Értékek” területre. Ekkor a munkalapon megjelenik egy dátumszűrő, ahol beállíthatjuk az időszakot, és a PivotTable egyetlen cellában mutatja a szűrt időszak maximális értékét.

Előnyök:

  • Interaktív, könnyen módosítható a dátumtartomány.
  • Nem igényel bonyolult képleteket.
  • Jól áttekinthető, további elemzésekre is alkalmas (pl. havi maximumok).

Hátrányok:

  • Nem egyetlen cellába írja a képletet, hanem egy külön objektumot hoz létre.
  • Ha egyetlen konkrét értéket szeretnénk egy cellában, akkor a GETPIVOTDATA függvénnyel kell esetleg „kihalászni” az értéket a PivotTable-ből, vagy a fentebb említett szűrő elrendezést használni.

6. módszer: Power Query (Adatok lekérdezése és átalakítása) használata (Excel 2010/2013 beépülőként, Excel 2016-tól beépítve) ⚡

A Power Query egy rendkívül hatékony adatátalakító eszköz az Excelen belül, amellyel komplex adatmanipulációs feladatokat is elvégezhetünk, beleértve a feltételes maximumkeresést.

Lépések (nagyon leegyszerűsítve):

  1. Jelölje ki az adattáblát.
  2. A Data (Adatok) menüszalagon válassza a From Table/Range (Táblázatból/tartományból) opciót. Ez megnyitja a Power Query szerkesztőt.
  3. A Power Query szerkesztőben a „Dátum” oszlop fejlécén lévő szűrő ikonra kattintva állítsa be a dátumszűrőt:
    • Válassza a Date Filters (Dátumszűrők) -> Between... (Között…) opciót.
    • Adja meg a kezdő és záró dátumot. Kattintson az OK-ra.
  4. Most már csak a megadott időszak adatai látszanak.
  5. Az „Érték” oszlop kijelölése után a Home (Kezdőlap) vagy Transform (Átalakítás) menüszalagon keresse meg a Statistics (Statisztika) csoportot, és válassza a Maximum opciót. (Alternatív megoldás: A Transform (Átalakítás) -> Group By (Csoportosítás) funkciót is használhatnánk, de egyetlen érték kinyerésére a Statistics -> Maximum egyszerűbb, miután az oszlopot kiválasztottuk és a többi oszlopot esetleg eltávolítottuk, vagy ha az Érték oszlopot választjuk ki, majd a Transform fülön a Statistics -> Maximum parancsot.) Egy másik, talán tisztább megközelítés Power Query-ben: a. A dátumszűrés után (3. lépés). b. Kattintson az „Érték” oszlop fejlécére, hogy kijelölje. c. A Transform (Átalakítás) lapon, a Number Column (Szám oszlop) csoportban kattintson a Statistics (Statisztika) legördülő menüre, és válassza a Maximum lehetőséget. Ez az egész táblázatot egyetlen értékre, a maximumra fogja redukálni.
  6. Az eredmény egyetlen cellában a maximális érték lesz.
  7. A Home (Kezdőlap) menüszalagon kattintson a Close & Load (Bezárás és betöltés) -> Close & Load To... (Bezárás és betöltés ide…) opcióra.
  8. Válassza ki, hogy a végeredményt (a maximális értéket) hova szeretné betölteni az Excel munkalapra (pl. egy táblázatba, ami csak egy cellát fog tartalmazni, vagy csak kapcsolatként).

Előnyök:

  • Rendkívül hatékony nagy adatmennyiségek esetén.
  • Az átalakítási lépések rögzülnek és frissíthetők.
  • Képes összetett adatforrásokkal és átalakításokkal is megbirkózni.

Hátrányok:

  • Külön interfészt használ, ami tanulást igényelhet.
  • Egyszerűbb feladatokra talán túlzás („ágyúval verébre”).

Melyik módszert válasszam? 🤔

  • Excel 2019 / Microsoft 365 (nem dinamikus tömbös): Használja a MAXIFS függvényt. Ez a legtisztább és legegyszerűbb.
  • Microsoft 365 (dinamikus tömbökkel): A MAX(FILTER(...)) kombináció rendkívül elegáns és hatékony.
  • Excel 2010, 2013, 2016 (vagy ha a MAXIFS nem opció): Az AGGREGATE függvény egy kiváló, Ctrl+Shift+Enter nélküli alternatíva. Ha ez sem elérhető vagy nem preferált, akkor a MAX és HA tömbképlet a megoldás.
  • Interaktív elemzéshez, riportáláshoz: A PivotTable (Kimutatás) a legjobb választás.
  • Nagy adatmennyiség, komplex átalakítások, automatizált adatfrissítés: A Power Query a legerősebb eszköz.

További tippek és megfontolások 💡

  • Dátumok helyes kezelése: Győződjön meg róla, hogy a dátumok valódi Excel dátumok, és a kezdő/záró dátumok is ebben a formátumban vannak megadva a képletekben vagy a hivatkozott cellákban.
  • Cellahivatkozások használata: A kezdő és záró dátumokat érdemes külön cellákba írni, és ezekre hivatkozni a képletekben (ahogy a példákban is tettük az E1 és F1 cellákkal). Így könnyen módosítható az időszak anélkül, hogy a képletet át kellene írni.
  • Nincsenek megfelelő adatok: Ha a megadott időszakban nincsenek adatok, vagy a feltételeknek egyetlen érték sem felel meg:
    • A MAXIFS és MAX(HA(...)) (ha nincs HA „hamis” ág) 0-t ad vissza, ha nincs találat, és az értékek között nincsenek negatív számok. Ha vannak negatív számok, akkor a legkisebb negatívot adhatja vissza hibásan, vagy #NUM! hibát is adhat bizonyos esetekben, ha a HA feltétele sosem teljesül és a MAX nem kap numerikus értéket.
    • Az AGGREGATE #NUM! hibát ad, ha nem talál érvényes értéket (mivel minden értéke #DIV/0! hiba lesz).
    • A FILTER (a példánkban megadott Nincs találat argumentummal) ezt az egyéni szöveget adja vissza, amit a MAX függvény #VALUE! hibaként értékelhet. Ezeket a helyzeteket az IFERROR (HIBAMENTES) függvénnyel kezelheti, pl. =IFERROR(MAXIFS(...); "Nincs adat").
  • Excel Táblázatok (Tables): Ha adatait formális Excel Táblázatként (Ctrl+T vagy Beszúrás -> Táblázat) kezeli, a képletek strukturált hivatkozásokat használhatnak (pl. Táblázat1[Érték]), amelyek dinamikusan bővülnek az adatok hozzáadásakor, és olvashatóbbá teszik a képleteket.

A megfelelő módszer kiválasztása mindig az adott Excel verziótól, az adatok mennyiségétől és struktúrájától, valamint a felhasználó Excel-ismeretétől függ. Reméljük, ez a részletes útmutató segít Önnek hatékonyan megtalálni a legnagyobb értéket egy adott időszakon belül az Excelben!

Ez is érdekelhet:

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

Az e-mail címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöltük