Telefonszámunk: 1-472-0679

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

2013-02-13 - horvimi - Megtekintések száma: 7,575 - Kategória: Képletek, Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010

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))

A képletet tömbképletként kell bevinni, azaz ENTER helyett a Ctrl+Shift+ENTER-rel kell lezárni!

{=MAX(IF(A2:A9=”A”;B2:B9))}

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.

  • Feltételes Maximum vagy Minimum kiválasztás képlet

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