Munkalapnevek listázása tartományba

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

A munkalapnevek listázására leginkább VBA, azaz makró megoldásokat lehet találni. De a minap egy érdekes megoldásra bukkantam itt, amit szeretnék veletek megosztani.

Tulajdonképpen ez is makró, de nem VBA, hanem EXCEL4 makró. Az Excel4 makrók függvények képében még megtalálhatók a mai EXCEL verziókban is, kompatibilitási okokból. A ma használt Visual Basic, mint a makrók alapja, az EXCEL 5-ös verziójában mutatkozott be.

MUNKAFÜZETET.VESZ ( ) – GET.WORKBOOK ( )

Ez az EXCEL4 makró függvény a megadott munkafüzetről szolgáltat információt. Hogy milyen infót kérünk, azt az argumentumában kell megadni Összesen 38 féle információt tudunk kérni.

Ebből most 3-at mutatok meg, és a megoldáshoz kettőt fogunk használni. A részletes leírást erről a függvényről ezen a linken találod.

MUNKAFÜZETET.VESZ(1)

Egy vízszintes tömbben visszaadja a munkafüzet munkalapjainak listáját. Sajnos a lapnevek elé odateszi a munkafüzet nevét is. tehát a tömb valahogy így néz ki:

[Füzetnév]lap1;[Füzetnév]lap2;[Füzetnév]lap3; stb...

Ha a füzetnevet ki tudnánk venni minden tag elől, akkor meg is lennénk a lapnevek listájával.

MUNKAFÜZETET.VESZ(16)

Visszaadja a munkafüzet nevét. Ezt fogjuk kivenni az előbbi listából úgy, hogy a kezdő és a záró kapcsos zárójelet, és közötte a munkalapnevet kicseréljük üres sztringre.

=HELYETTE(MUNKAFÜZETET.VESZ(1);"["&MUNKAFÜZETET.VESZ(16)&"]";"")

MUNKAFÜZETET.VESZ(38)

Visszaadja az aktuális munkalap nevét. Ezt csak ideírtam, nem használjuk ehhez a feladathoz.

Első lépés, a név hozzáadása a függvényhez.

Ezt a Képletek szalag Névkezelő parancsával lehet megtenni.

Vízszintes lista a tartományba

1. Jelöljünk ki annyi cellát egymás mellett, amennyi munkalapunk van

2. Írjuk be az előbb létrehozott nevet: =munkalapok_listája

3. Ctrl-Shift+Enter (CSE)

Eredményképpen a lapnevek megjelennek egymás mellett

Függőleges lista

Az előző képletet egy TRANSZPONÁLÁS (TRANSPOSE) függvénybe kell zárni.

1. Jelöljünk ki annyi cellát egymás alatt, amennyi munkalapunk van

2. Írjuk be a transzponáló képletet: =TRANSZPONÁLÁS(munkalapok_listája)

3. Ctrl-Shift+Enter (CSE)

Változások kezelése

Ez a megoldás alapból nem VOLATILE, azaz nem frissíti automatikusan a listát, ha változás áll be a munkalapok között (törlés, átnevezés, sorrend, stb..)

Ezt úgy oldhatjuk meg, hogy a képlethez hozzáadunk egy olyan VOLATILE függvényt, ami nem módosítja az eredményt, viszont minden módosuláskor újraszámol. Erre a célra a T(MOST()) kifejezés tökéletesen megfelel, mert a MOST függvény minden változáskor újraszámol. A T() függvény üres szöveget ad vissza, ha az argumentuma nem szöveg. A MOST függvény dátumot ad, tehát a kettő együtt üres sztringet fűz a munkalapnévhez, azaz nem módosítja azt, viszont dinamikussá teszi.

Tehát ha ezt akarjuk, akkor a névkezelőben módosítsuk a képletet:

=HELYETTE(MUNKAFÜZETET.VESZ(1);"["&MUNKAFÜZETET.VESZ(16)&"]";"")&T(MOST())

Így a lista követni fogja a következő műveleteket:

• Átnevezés
• Törlés
• Sorrend változtatás

Makróbarát munkafüzet

Ha a képletet alkalmazzuk, és a változásokat követni szeretnénk (mert vannak), akkor a munkafüzetet csak makróbarát munkafüzetként (*.xlsm) lehet menteni.

Makrótlanítás

Ha  a munkafüzetben nem várható struktúrális változás, vagy csak ritkán, akkor érdemes a generált listát értékké konvertálni. A képlet megmarad a névkezelőben, és bármikor alkalmazhatjuk újra.

Letölthető gyakorló munkafüzet

Munkalapok_listaja

Végül a működés videón