A közelmúltban tárgyaltuk a FILTER.XML függvény használatát XML adatok internetről történő importálására – ez a fő feladat, amelyre ez a funkció valójában szolgál. Útközben azonban ennek a funkciónak egy másik váratlan és gyönyörű alkalmazása is felmerült – a ragadós szöveg gyors cellákra osztására.
Tegyük fel, hogy van egy ilyen adatoszlopunk:
Természetesen a kényelem kedvéért külön oszlopokra szeretném osztani: cégnév, város, utca, ház. Ezt többféleképpen is megteheti:
- Felhasználás Szöveg oszloponként a lapról dátum (Adatok – szöveg oszlopokba) és menj három lépést Szövegelemző. De ha holnap megváltoznak az adatok, akkor az egész folyamatot újra meg kell ismételnie.
- Töltsd be ezeket az adatokat a Power Query-be és oszd el ott, majd töltsd vissza a munkalapra, majd frissítsd a lekérdezést, ha az adatok megváltoznak (ez már egyszerűbb).
- Ha menet közben frissítenie kell, írhat néhány nagyon összetett képletet, hogy megtalálja a vesszőket, és kivonja a köztük lévő szöveget.
És ezt elegánsabban is megteheti, és használhatja a FILTER.XML függvényt, de mi köze ehhez?
A FILTER.XML függvény kezdeti argumentumaként egy XML-kódot kap – speciális címkékkel és attribútumokkal megjelölt szöveget, majd elemzi azt összetevőire, kibontva a szükséges adattöredékeket. Az XML kód általában így néz ki:
XML-ben minden adatelemet címkékbe kell zárni. A címke valamilyen szöveg (a fenti példában ez menedzser, név, profit), szögletes zárójelek között. A címkék mindig párban jönnek – nyitó és záró (perjellel az elejére).
A FILTER.XML függvény könnyedén ki tudja bontani az összes szükséges címke tartalmát, például az összes menedzser nevét, és (ami a legfontosabb) egyszerre megjeleníti egy listában. Feladatunk tehát az, hogy a forrásszöveghez címkéket adjunk, a FILTER.XML függvény által utólagos elemzésre alkalmas XML kóddá alakítva.
Ha példának vesszük a listánk első címét, akkor ezt a konstrukciót kell alakítanunk:
Meghívtam a globális nyitó és bezáró összes szöveg címkét t, és az egyes elemeket keretező címkék s., de használhat bármilyen más elnevezést – ez nem számít.
Ha ebből a kódból eltávolítjuk a behúzásokat és a sortöréseket – mellesleg teljesen opcionálisan, és csak az áttekinthetőség kedvéért adjuk hozzá, akkor mindez sorrá alakul:
És a forráscímből már viszonylag könnyen megszerezhető, ha a benne lévő vesszőket pár címkére cseréljük funkció használatával HELYETTES (HELYETTES) és ragasztás a szimbólummal & a nyitó és záró címke elején és végén:
A kapott tartomány vízszintes bővítéséhez a standard funkciót használjuk TRANSP (TRASZPOZI), beletekerjük a képletünket:
Az egész kialakítás fontos jellemzője, hogy az Office 2021 és az Office 365 új verziójában, amely támogatja a dinamikus tömböket, nincs szükség speciális gesztusokra a bevitelhez – csak írja be, és kattintson a belép – maga a képlet elfoglalja a szükséges számú cellát, és minden lendülettel működik. A korábbi verziókban, ahol még nem voltak dinamikus tömbök, először ki kell jelölnie elegendő számú üres cellát a képlet beírása előtt (margóval megteheti), majd a képlet létrehozása után nyomja meg a billentyűparancsot Ctrl+műszak+beléphogy tömbképletként adja meg.
Hasonló trükk használható, amikor sortöréssel egy cellába ragadt szöveget választanak el:
Az egyetlen különbség az előző példához képest, hogy itt vessző helyett a láthatatlan Alt + Enter sortörés karaktert cseréljük ki, amit a képletben a CHAR függvény segítségével 10-es kóddal adhatunk meg.
- A sortörésekkel (Alt + Enter) végzett munka finomságai az Excelben
- Oszd fel a szöveget oszlopokra az Excelben
- Szöveg cseréje SUBSTITUTE-ra