Az Excel egyik legnagyobb ugrása az elmúlt években a dinamikus tömbfüggvények megjelenése volt. De miért fontos ez, és mit jelent a gyakorlatban?
Mi az a dinamikus tömb?
Leegyszerűsítve: egyetlen képlet képes több értéket visszaadni és alkalmazkodni az adatok változásához
Ahhoz, hogy igazán megértsd a dinamikus tömböket, érdemes egy lépést visszalépni.
A „régi” Excelben, ha egy tartományra hivatkoztál (például A2:B6), akkor a képleted nem tudott mit kezdeni vele önmagában. Vagy csak az első cellát vette figyelembe, vagy egy függvénybe kellett „becsomagolni” (pl. SZUM / SUM), hogy értelmezni tudja. (Pl. =SZUM(A2:B6)
A dinamikus tömbök ezt teljesen megváltoztatták.
Egy képlet → több eredmény
Ma már ezt is beírhatod egy cellába:
=A2:B6
A dinamikus tömbök egyik legnagyobb előnye, hogy nem kell képleteket másolni. Nézd meg a videót vagy olvass tovább.
Az Excel az egész tartományt „kibontja” a több cellára. Ez azt jelenti, hogy:
- a képlet csak egy cellában van (bal felső sarok)
- az eredmény viszont több cellában jelenik meg
- a többi cellában is látod halványan a képletet (de itt nem szerkeszthető)
- vizuálisan kijelöli (kék keret + halvány háttér)
- mindig egy egységként kezeli
Fontos szabály: csak a bal felső cellát tudod szerkeszteni.
Ha a tartományban bármelyik cellába beleírsz valamit, vagy már korábban szerepelt ott érték, az Excel hibát dob: #KIBONTÁS! vagy angolul #SPILL!
Ez azt jelenti: „Nem tudom kibontani az eredményt, mert útban van valami.”
Tartományokkal számolás – alap szint
Teljesen alap szinten is használhatod például:
=B2:B6*1,27
Ez automatikusan kiszámolja az összes értéket — egyetlen képlettel.
Ugyanez ha 2 oszlopot akarsz soronként összeszorozni:
=A2:A6*B2:B6
Ez korábban csak bonyolult tömbképlettel volt megoldható.
A # (kettőskereszt) szerepe
Ha egy dinamikus tömb eredményére szeretnél hivatkozni, megjelenik egy új jelölés:
=G2#
Ez azt jelenti: A G2 cellából kiinduló teljes dinamikus tartomány. Nem kell tudnod, meddig tart — az Excel automatikusan követi.
Ez az egyik legnagyobb előny:
✔ nem kell fix tartományokat megadni
✔ a képlet alkalmazkodik az adatokhoz
Ennek köszönhetően lehet egyészen dinamikus és automatizált táblázatokat összeépíteni. Ha jól csinálod, nem kell bővíteni, másolgatni a képleteket. Kevesebb a hiba, nem marad ki egyetlen sor sem, és nem csúszik el egy tartomány. Kevesebb manuális lépés, mert automatikusan frissül minden.
Persze ehhez meg kell ismerni az új függvényeket is, és be kell gyakorolni.
Gyere a VIP Klubba, ahol 5 órányi dinamikus tömbök és függvények oktatóanyag vár.
A legfontosabb dinamikus tömbfüggvények
- SZŰRÉS (FILTER) Adott feltételek alapján kiszűri az adatokat egy tartományból.
- RENDEZÉS (SORT) Egy tartományt sorba rendez (növekvő vagy csökkenő sorrendben).
- RENDEZÉS.SZERINT (SORTBY) Egy tartományt egy másik oszlop alapján rendez.
- EGYEDI (UNIQUE) Eltávolítja az ismétlődéseket, csak az egyedi értékeket adja vissza.
- SOROZAT (SEQUENCE) Automatikusan generál számsorozatot (pl. 1-től 100-ig).
- VÉLETLEN.TÖMB (RANDARRAY) Véletlenszámokat generál egy tömbben.
- XKERES (XLOOKUP) Modern keresőfüggvény, amely dinamikus tömbbel is működik.
- XMATCH (XMATCH) Egy érték pozícióját adja meg, fejlettebb MATCH változat.
- ELVESZ (TAKE) Egy tömb elejéről vagy végéről vesz ki sorokat/oszlopokat.
- ELDOB (DROP) Egy tömb elejéről vagy végéről hagy el sorokat/oszlopokat.
- OSZLOPOK.KIVÁLASZTÁSA (CHOOSECOLS) Kiválasztott oszlopokat ad vissza egy tömbből.
- SOROK.KIVÁLASZTÁSA (CHOOSEROWS) Kiválasztott sorokat ad vissza egy tömbből.
- SOROKBA.TÖRDEL (WRAPROWS) Egy listát több sorba rendez.
- OSZLOPOKBA.TÖRDEL (WRAPCOLS) Egy listát több oszlopba rendez.
- SORBA (TOROW) Tömböt egyetlen sorba alakít.
- OSZLOPBA (TOCOL) Tömböt egyetlen oszlopba alakít.
Mikor érdemes használni?
Ha:
- listákat szűrsz
- riportokat készítesz
- ismétlődéseket szűrsz
- dinamikusan változó adatokkal dolgozol
Viszont fontos tudni, hogy csak a 2019-es Excel verziótól elérhető, a korábbi verziók nem támogatják a dinamikus tömböket.
Automatizálás
Hasonlóan új, szemléletváltó és automatizációs eszköz a Power Query, különösen akkor hasznos, ha több helyről érkeznek rendszeresen adatok, amelyeknek feldolgozása, átalakítása rengeteg időt visz el. Érdemes rászánni 2 délután a workshopjainkon, és jobban megismerni.
Általában az Excel automatizálási lehetőségeiről itt írtam.
Ha pedig szeretnél folyamatosan hasznos tippeket kapni, és a felmerülő kérdésekhez gyors iránymuatást, akkor szeretettel várlak a az ExcelTitok VIP Klubban, ahol a dinamikus tömbfüggvényekhez is találsz 5 órányi oktatási anyagot.
