Dinamikus tömbfüggvények Excelben – Hatalmas újítás

A dinamikus tömbképletek, dinamikus tömbfüggvények hatalmas újítás az Excelben. Nincs többé Ctrl Shift Enter, könnyedén dolgozhatsz egyszerre több értékkel egy képletben, függvényben. Ez a nagyszerű újítás teljesen megváltoztatja az eddigi munkát Excelben, nézzük a részleteket!

Az Excelben a tömb (angolul array) gyakori kifejezés, lényegében értékek sorozatát jelenti. Excelben a tömbök kapcsos zárójelben szerepelnek. Például egy SZUM függvényen, ha több cellát jelölsz ki, az már egy tömb.

Oszlopokban pontosvessző, az elválasztó sorokban \.

• Egy tömb oszlopokban pl: {10;12;14}
• Egy tömb sorokban pl: {10\12\14}

(Az elválasztók rendszerbeállításoktól függenek, érdemes ellenőrizni, hogy a Te rendszeredben, hogy szerepel). A tömbökről bővebben olvashatsz az Excel tömbfüggvények bejegyzésünkben

A feladatban használt fájlt töltsd le innen: Dinamikus tömbfüggvények bevezető

A korábbi Excelekben is tudtunk tömbképleteket, tömbfüggvényeket használni, ezeket Ctrl + Shift + Enterrel kellett rögzíteni. Dorka tömbfüggvényes példáján keresztül ismételve:

A maximum változást szeretném megkapni, ehhez a MAX függvénybe a záró értékekből vonom ki a nyitó értékeket, de ezt a régi verzióban Ctrl + Shift + Enterrel kell bezárni. Bezárás után a szerkesztőlécen láthatod a kapcsos zárójeleket.

A Microsoft 365 előfizetésben nincs szükség a Ctrl + Shift + Enterre, csak zárd be Enterrel. Ugyanúgy tömbképletként tárolja, csak nem látod a kapcsos zárójeleket Nézzük meg közelebbről ezeket a dinamikus tömböket

A dinamikus tömbök csak az Excel 365 verzióban érhetők el (Microsoft 365 előfizetés) és az Excel 2021 verzióban.

Az új verzióban egy másik beépített függvénymotor működik, így nincs szükség a Ctrl + Shift + Enter lenyomására és a cellák kijelölésére. Az Excel megvizsgálja az eredményt, és ha több értéket kell visszaadnia, ki fogja bontani annyi cellába, amennyibe szükséges. Óriási, nem?

Az újításnak része, hogy új, kifejezetten dinamikus tömbfüggvényeket is kaptunk, noha a korábbi függvények is képesek erre a kibontásra és a tömbök kezelésére (lásd fenti MAX példa). Ezek az új függvények:

Ezekről később még lesz szó részletesebben egy bejegyzésben.

A dinamikus tömbképletek és függvények az Excelben automatikusan kibomlanak (spill) annyi cellába, amennyi szükséges az adatoknak. Ha változik egy adat, automatikusan megváltozik az eredmény is.

A kibontott értékeket egy kék keret fogja körbevenni, ez a kibontott tartomány.

Csakúgy, mint hagyományos tömbképletek esetében, itt is igaz az, hogy a tömb egyes elemei önmagukban nem törölhetők, nem módosíthatók.

#KIBONTÁS! hibával fogsz találkozni, ha olyan helyre írod a függvényt, ahol nincs hely kibontani azt, vagyis nemüres cella blokkolja a kibontást. Amint törlöd a blokkoló cellákat, automatikusan ki fog bomlani a képlet/függvény.

Próbáljuk ki a TRANSZPONÁLÁS függvénnyel

Egy régi Excelekben is meglévő tömbfüggvény a TRANSZPONÁLÁS (TRANSPOSE). A régebbi Excelekben is működik, nézzük, hogy hogyan:

1. Jelölj ki annyi cellát, amennyi az eredményekhez szükséges (a példában a három fejlécet szeretném egy sorban látni, tehát 3 cellát jelölök ki)
2. Egyenlőségjel után írd be a függvényt = TRANSZPONÁLÁS(
3. Jelöld ki a fejléceket
4. Majd Ctrl + Shift + Enter együttes lenyomásával rögzítsd

Transzponáljuk a fejléceket az alábbi példában:

Az eredményben láthatod, hogy mindhárom cellát kitöltötte és kapcsos zárójelek közé tette a függvényt. Minden cellában ugyanazt a függvényt látod, ami egyrészt egységes, másrészt viszont nehéz lehet értelmezni.

Ha valamelyik cellát megpróbálod törölni, nem fogja engedni:

Ha Microsoft 365 előfizetésed van, akkor nincs szükséged a cellák kijelölésére és a Ctrl + Shift + Enterre. Próbáld ki így is.

Csak az első cellát jelöltem ki és Enterrel rögzítem.

Láthatod, hogy kék kerettel veszi körbe, ez jelöli a tömb határait, illetve az is látszik, hogy az első cella alatti cellákban látszik ugyan a függvény, de csak halványan. Valójában a függvény csak az első cellában szerepel. Törölni ezt sem lehet elemenként, csak egyben.

Hivatkozni úgy tudsz rá, hogy az első cellát megadod, majd a # karaktert használod. Akkor is ezt fogod látni a cellában, ha a teljes kibontott tartományt kijelölöd.
Hivatkozás tömbre: =H22#

Ezt a hivatkozást használhatod más függvényben is, például megszámolhatod a „darab” tartalmú cellákat. A függvény: =DARABTELI(H22#;”darab”)

Az eredmény:

A dinamikus tömbökkel és az új függvényekkel egyszerűbbé válik az Excel használata például ezeken a területeken:

• egyedi értékek listázása
• egyedi értékek megszámolása
• értékek szűrése, kinyerése
• részleges egyező értékek listázása
• Kevésbé szükséges a dollárjelek használata (abszolút és vegyes hivatkozások)

Ez utóbbira nézzünk egy szorzótábla példát. Korábban ezt dollárjelekkel, vegyes hivatkozásokkal kellett megoldani. Ez most lényegesen leegyszerűsödik. A képlet:
=A31:A40*B30:K30

Az eredmény:

A képlet itt is csak az első cellában szerepel, a többibe kibontja, és halványan látszik a szerkesztőlécen a képlet. Nincs szükség dollárjelekre! Mondjuk ez még nem jelenti azt, hogy nem kell ismerned a használatukat :) , de megkönnyíthetik a munkád!

Mivel megújul a tömbök kezelése az Excelben, egyre fontosabb a tömbműveletek ismerete és használata. Tömbművelet olyan művelet, amely egy logikai vizsgálatot vagy matematikai műveletet hajt végre egy tömbön. Nézzük ezt lépésenként!

Vizsgáljuk meg például, hogy mely sorokban egyenlő az értékesítő Petrával

Ennyire egyszerű. Csak beírod a cellába, behivatkozod az egész tartományt és Enterrel rögzíted. Az Excel automatikusan kibontja.

Nézzük meg a darabszámra, azokra, amelyek nagyobbak mint 5.

Az eredmény:

Nézzük a kettőt együtt!

Az eredmény:

Ezt teszi még egyszerűbbé a SZŰRŐ függvény (FILTER), ami kifejezetten tömbfüggvény, nézzük meg ugyanezen a példán A függvény:

=SZŰRŐ(A47:C51;(A47:A51=”Petra”)*(C47:C51>5))

Egyetlen cellába beírva, majd Enterrel rögzítve az eredményt automatikusan kibontja:

Ezekről a dinamikus tömbfüggvényekről hamarosan teszek fel részletesebb ismertetőt. Addig is ismerkedj ezzel az újdonsággal az Excelben ezzel a bejegyzéssel! 