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:
- 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.
- 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.
- 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.
- 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.
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
Semmit nem kell dollározni, nincs Ctrl + Shift + Enter, csak egyszerűen Enterrel zárd
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:
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.
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))
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.
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 😊