Egy feltétel alapján keresel értékeket a táblából, de egynél több eredményre lenne szükséged? Ilyenkor nem segít az FKERES függvény, helyette mutatok egy sokkal jobb függvényt! Ez a dinamikus tömbfüggvény a Microsoft 365 előfizetésekben és Excel 2021 verzióban elérhető, de ha még nem ilyennel rendelkezel, akkor is érdemes megismerned, ki tudja, mikor cserélik le munkahelyeden az Excelt a legújabbra. 😉

Excel SZŰRŐ függvény bemutatása

A SZŰRŐ függvény (angolul FILTER függvény) a Microsoft 365 előfizetésben elérhető és az Excel 2021 verzióban, dinamikus tömbfüggvény.

Nézzük először az előnyeit:

  1. Biztosan ismered az FKERES-t, esetleg az XKERES-t használod már, ezekkel viszont csak egyetlen eredményt tudsz visszaadni. Jó lehetőség lehet még több eredmény visszaadására a Speciális sző, de ez nem dinamikus.
  2. A SZŰRŐ függvény egyszerűen egybeágyazható más függvényekkel, például SZUM, vagy DARAB függvénnyel kombinálhatod az eredményeket.
  3. Anélkül szűrhetsz, hogy a szűrőt újra be kellene állítanod. Dinamikus, így ha változtatod a feltételt a külső cellában, automatikusan frissül az eredmény.
  4. Egyszerű megírni 😊 Mutatom!

Maga a függvény így épül fel:

=SZŰRŐ(tömb; belefoglalás; [ha_üres])

=FILTER (array, include, [if_empty])

tömb: Itt kell megadnod a tartományt, amit szűrni szeretnél.

belefoglalás: Ez lényegében egy logikai vizsgálat, a tartomány azon oszlopának értékeit vizsgáljuk, ahonnan a feltétel származik, ezeket viszonyítjuk a feltételhez. (mindjárt egyértelműbb lesz)

ha_üres: Itt megadhatod, hogy mit írjon ki eredményül, ha nem talál eredményt.

Most pedig jöjjön a konkrét példa!

Excel SZŰRŐ függvény példa

A feladatot töltsd le innen: Excel SZŰRŐ függvény

Ezek az adataink, termékek, értékesítők és a bevétel.

Excel SZŰRŐ függvény példa

Az F6-os cellában szerepel Tamás, itt egy legördülő lista található, abból választottam.

Az E10-es cellába kezdjük el írni a függvényt:

=SZŰRŐ(A7:C30; -> Ez a tartomány, amiben szűrni fogunk

=SZŰRŐ(A7:C30;B7:B30=F6 –> Ez a logikai vizsgálat. Az Értékesítő oszlop értékeit vizsgáljuk, hogy hol egyenlők Tamással.

=SZŰRŐ(A7:C30;B7:B30=F6;”nincs adat”) –> Írja ki, hogy nincs adat, ha nem található érték

nincs adat

Semmit nem kell dollározni, nincs Ctrl + Shift + Enter, csak egyszerűen Enterrel zárd

Excel SZŰRŐ függvény

Bámulatos, ugye? :D

Az eredményt adó tartományt az összes előfordulást tartalmazza. A számformátumokat, mint láthatod, nem hozza, utólag szükséges beállítanod.

Válassz most egy másik értékesítőt a legördülőből:

Excel SZŰRŐ függvény hiba

Semmit nem kellett átállítani, függvényt átírni, egyszerűen kiválasztasz egy másik kritériumot és máris megvannak az eredmények.

Nagyon szuper! :)

Excel SZŰRŐ függvény hiba

Ha a függvény nem tudja kibontani az adatokat, mert egy nem üres cella blokkolja, akkor a #KIBONTÁS hiba fog megjelenni.

Excel SZŰRŐ függvény hiba
Excel SZŰRŐ függvény kibontás

A függvényt nem kell törölnöd, csak azt a cellatartalmat, ami blokkolja, és a függvény rögtön kijavul, kibontja az értékeket.

Excel SZŰRŐ függvény beágyazása

Számoljuk ki az értékesítőhöz tartozó összes bevételt. Ehhez a SZŰRŐ függvényt a SZUM függvénybe fogjuk beágyazni. F7-es cellában:

=SZUM(SZŰRŐ(C7:C30;B7:B30=F6))

Excel SZŰRŐ függvény beágyazása

A SZŰRŐ függvényben a tömb a bevétel oszlopa, a belefoglalásnál pedig a B oszlopot vizsgáljuk, B7:B30=F6. Az utolsó argumentumot nem töltöttem ki, így is működik.

Az eredmény Tamara összes értékesítésének bevétele.

Eredmény

Ez tehát a SZŰRŐ függvény alapjaiban véve! Töltsd le a fájlt, gyakorold, hiszen szuper függvényt kaptunk az Excelbe 😊