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!

Mi az a tömb?

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ő

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

Tömbfüggvény a korábbi Excelekben

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:

Dinamikus tömbök régebbi Excelben

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.

Tömbfüggvények

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

Dinamikus tömbök, dinamikus tömbfüggvények a Microsoft 365 előfizetésben

A dinamikus tömbök csak az Excel 365 verzióban érhetők el (Microsoft 365 előfizetés), korábbi verziókban sajnos nem.

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:

Függvény angol neveFüggvény magyar neveLeírás
FILTERSZŰRŐTartományt vagy tömböt szűr
RANDARRAYVÉLETLENTÖMBVéletlenszerű számok tömbjét adja
SEQUENCESORSZÁMLISTASzámsort ad vissza
SORTSORBA.RENDEZTartományt vagy tömböt rendez
SORTBYRENDEZÉS.ALAP.SZERINTTartományt vagy tömböt rendez a megfelelő tartományban vagy tömbben
UNIQUEEGYEDIEgy tartomány vagy tömb egyedi értékeit adja vissza

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

Dinamikus tömbök és függvények működése Excelben

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

Dinamikus tömbfüggvények – TRANSZPONÁLÁS függvény

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:

Transzponálás függvény

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:

Tömbfüggvény hiba

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.

Tömbképlet Office 365

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

Tömbfüggvény eredmény

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.

Dinamikus tömbfüggvények – Kibontott tartomány függvényben

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#

Tömbhivatkozás

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”)

Tömb hivatkaozása

Az eredmény:

Tömfüggvény Excel

Tömbfüggvények egyszerűbb használata

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

Tömbképlet szorzótábla

Az eredmény:

Tömbképlet kibomlik

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!

Tömbműveletek fontosak

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

Tömbműbelet1

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

Tömbképlet2

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

Tömbképlet3

Az eredmény:

Tömbképlet eredmény

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

Tömbműveletek

Az eredmény:

Tömbműveletek eredmény

Dinamikus tömbfüggvény – SZŰRŐ függvé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))

Dinamikus tömbfüggvény szűrő függvény

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

szűrő függvény

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! 