Feltétel szerinti szélső érték – MAXIF vagy MINIF

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

Az Excel újabb verzióiban megjelentek a feltétel szerinti átlagszámítást végző függvények, de feltétel szerinti maximum vagy minimum számításhoz már ügyeskedni kell, mert direkt függvény még nem áll rendelkezésre.

A neten több megoldást is lehet találni a problémára. Ezek közül a tesztelések során, az itt ismertetésre kerülő az, amelyik minden esetben helyesen működik, még akkor is, ha a kiválasztandó számok pl. negatív értékek.

Feladatok

• Képlettel határozzuk meg az alábbi táblázatból a legnagyobb értéket az „A” kategóriában!
• Az érték oszlopban melyik a legnagyobb vagy legkisebb páros szám?
• stb…

Megoldás segédoszloppal, két lépésben

Elsőként nézzük meg azt a képletet, amellyel megállapíthatjuk a legnagyobb értéket az „A” típusok közül.

Ezt papíron, vagy szemmel úgy csinálnánk, hogy kikeressük az összes „A” típusú sort, és a hozzájuk tartozó értékek közül kiválogatjuk a legnagyobbat. A fenti táblázattal könnyű a helyzetünk, mert sorba van rendezve típus szerint, és ráadásul csak 8 sora van.

A „C” oszlopt használva segéd oszlopként, a következő képletet írjuk a C2 cellába, majd lehúzhatjuk a teljes oszlopra:

=IF(A2="A";B2)

Eszerint, ha az „A” oszlopban „A” érték van, akkor a „C” oszlopba írja be a „B” oszlopból az értéket. Különben ágat nem adunk meg a HA (IF) függvénynek, ekkor automatikusan  a hamis ág FALSE érték lesz.

Ezek után nincs más dolgunk, mint egy külön cellába lekérni a „C” oszlop maximumát vagy minimumát.

Megoldás tömbképlettel

Ez a megoldás azt használja ki, hogy az Excel képes tömbökkel is dolgozni. Próbáljuk meg, hogy a fenti táblázat alapján egy üres cellába beírjuk ezt:

=A2:A9="A"

majd ENTER helyett nyomjuk meg az F9 billentyűt!

Ezt fogjuk látni:

Azaz a tartomány minden elemére kiértékelte az egyenlőséget, és egy logikai tömböt adott vissza az eredménnyel.

Ctrl-Z-vel visszatérhetünk a képlethez, és egészítsük ki a következőre:

=IF(A2:A9="A";B2:B9)

A HA (IF) függvény megvizsgálja az egyenlőséget az „A” oszlop elemein, és ha egyezőséget talál, akkor visszaadja a „B” oszlop megfelelő elemét, különben pedig FALSE értéket. Próbáljuk megint az F9-es trükköt:

Nincs más hátra, mint ennek a tömbnek venni a MAX vagy a MIN elemét.

=MAX(IF(A2:A9="A";B2:B9))

Megoldás Adatbázis függvénnyel

Ehhez ismerni kell a kritériumtartomány fogalmát, amit itt írtam le.

1. A cikk elején lévő ábrán látható, hogy az A1:B9 tartományban van az adatbázis (Típus és értékek)
2. Egy külön tartományban megadjuk a feltételt fejléccel együtt. Jelen esetben a Típus=”A” feltételt így adjuk meg az I1:I2 tartományba:
   
3. Az eredménycellába megadjuk a függvényt:

=AB.MAX(A1:B9;2;I1:I2)

Letölthető minta munkafüzet

MAX-IF.xlsx

Demo VIDEO

Ami működés közben mutatja a két megoldást, és bónuszként tartalmazza azt is, hogy hogyan tudjuk kiválasztani egy tartományból a legnagyobb vagy legkisebb páros számot.