Röviden
Ezt fogja megtanulni
- Egy vagy több feltétel alapján automatikusan frissülő részlistát készít.
- Ismétlődés nélküli, rendezett értéklistát állít elő.
- Kezeli a kiömlési hibát és az üres eredményt.
Gyakorlat nélkül nincs biztos tudás
Dinamikus tömbök gyakorlófájl
Töltse le a közérthető mintaállományt, nyissa meg a Rendelések adatlapot, majd oldja meg a cikk példáit Excelben. A munkafüzet gyakorlóteret, megoldási mintákat és önellenőrzést is tartalmaz.
1. Értse meg, hogyan működik a kiömlés
A dinamikus tömbfüggvények egyetlen cellába írt képletből több cellányi eredményt adnak vissza. Az eredmény lefelé és jobbra „kiömlik”, a kiömlött tartományt pedig vékony keret jelzi. Csak a bal felső cella tartalmazza a képletet, a többi cella szürkén mutatja azt.
Ha a kiömlés útjában már van adat, a képlet #KIÖMLÖTT! hibát ad. Ilyenkor ürítse ki az útban lévő cellákat. A kiömlött tartományra a # jellel hivatkozhat: ha az eredmény az N5 cellában kezdődik, a =DARAB2(N5#) képlet az aktuális találatok számát adja.
- A függvények a Microsoft 365-ben és az Excel 2021-től érhetők el.
- Excel-táblázaton belül nem ömölhet ki eredmény, ezért a képletet a táblázat mellé írja.
- A forrás legyen Excel-táblázat, hogy az új sorok is bekerüljenek.
2. Szűrjön képlettel egy feltétel szerint
A SZŰRŐ első argumentuma a visszaadandó adat, a második egy ugyanolyan magas feltételoszlop, a harmadik pedig az a szöveg, amely akkor jelenik meg, ha nincs találat. A feltételt érdemes egy külön cellából olvasni, így a lista egy legördülő menüvel váltható. A mintaállomány Rendelések lapján a táblázat neve Datakozeprendelesek; írja az L1 cellába például a Kelet régiót.
A harmadik argumentumot ne hagyja el: nélküle üres eredménynél #SZÁMÍTÁS! hiba jelenik meg, ami a riport olvasóját összezavarja.
=SZŰRŐ(Datakozeprendelesek;Datakozeprendelesek[Régió]=L1;"Nincs találat")3. Kapcsoljon össze több feltételt
A SZŰRŐ feltételeiben a szorzás ÉS kapcsolatot, az összeadás VAGY kapcsolatot jelent. Minden feltételt tegyen zárójelbe, mert az Excel az összehasonlítás előtt végezné el a műveletet.
Az alábbi képlet az L1 cellában megadott régió azon rendeléseit adja vissza, amelyek nettó értéke eléri az L2 cellában megadott összeget, csökkenő érték szerint rendezve. A SORBA.RENDEZ második argumentuma a rendezendő oszlop sorszáma az eredményben (a Nettó érték a tizedik oszlop), a harmadik -1 értéke a csökkenő sorrend.
=SORBA.RENDEZ(SZŰRŐ(Datakozeprendelesek;(Datakozeprendelesek[Régió]=L1)*(Datakozeprendelesek[Nettó érték]>=L2);"Nincs találat");10;-1)4. Készítsen ismétlődés nélküli listát
Az EGYEDI egy oszlop minden értékét egyszer adja vissza. Ez ideális összesítő táblák sorfejlécéhez: mellé egy SZUMHA képlet a kiömlött tartományra hivatkozva minden ügyfél forgalmát kiszámítja, és új ügyfél esetén a táblázat magától bővül.
Az EGYEDI harmadik argumentuma IGAZ értékkel csak azokat az elemeket adja vissza, amelyek pontosan egyszer szerepelnek. Így például gyorsan megtalálhatók az egyszeri vásárlók.
=SORBA.RENDEZ(EGYEDI(Datakozeprendelesek[Ügyfél]))Ellenőrzőlista
Gyakori hibák, amelyeket érdemes elkerülni
- Adat a kiömlési tartomány útjában, ami #KIÖMLÖTT! hibát okoz.
- A „nincs találat” argumentum elhagyása a SZŰRŐ függvényben.
- Zárójel nélküli feltételek több feltételes szűrésnél.
- Rögzített tartomány használata Excel-táblázat helyett, így az új sorok kimaradnak.
A cikk szerzője
Nemes Péter Bence Excel-oktató
Saját, több évtizedes szakmai gyakorlat alapján tanítja az Excelt az alapoktól a Power Query és Power Pivot megoldásokig. Az EAIO – Excel All-in-One nyilvántartott felnőttképzőként működik.
- Élő online oktatás és személyes visszajelzés
- Felnőttképzési nyilvántartási szám: B/2021/000239
Gyakorlás oktatóval
Excel Középhaladó tanfolyam
A középhaladó képzésen a dinamikus tömbfüggvényekkel kézi szűrés és másolás nélkül frissülő riportlapok készülnek.
Részletek a képzésrőlHasznos volt?
Ossza meg az útmutatót
Küldje tovább annak, akinek a munkáját ez a módszer megkönnyítheti.