Kohët e fundit, ne diskutuam përdorimin e funksionit FILTER.XML për të importuar të dhëna XML nga Interneti - detyra kryesore për të cilën ky funksion, në fakt, synohet. Megjithatë, gjatë rrugës, një tjetër përdorim i papritur dhe i bukur i këtij funksioni është shfaqur - për ndarjen e shpejtë të tekstit ngjitës në qeliza.
Le të themi se kemi një kolonë të dhënash si kjo:
Sigurisht, për lehtësi, do të doja ta ndaja në kolona të veçanta: emri i kompanisë, qyteti, rruga, shtëpia. Ju mund ta bëni këtë në një mori mënyrash të ndryshme:
- përdorim Teksti sipas kolonave nga skeda Data (Të dhënat - Teksti në kolona) dhe shkoni tre hapa Analizuesi i tekstit. Por nëse të dhënat ndryshojnë nesër, do të duhet të përsërisni të gjithë procesin përsëri.
- Ngarkoni këto të dhëna në Power Query dhe ndajini atje, dhe më pas ngarkoni përsëri në fletë dhe më pas përditësoni pyetjen kur të dhënat ndryshojnë (gjë që tashmë është më e lehtë).
- Nëse keni nevojë të përditësoni menjëherë, atëherë mund të shkruani disa formula shumë komplekse për të gjetur presje dhe për të nxjerrë tekstin midis tyre.
Dhe mund ta bëni atë në mënyrë më elegante dhe të përdorni funksionin FILTER.XML, por çfarë lidhje ka ai me të?
Funksioni FILTER.XML merr si argument fillestar një kod XML - tekst i shënuar me etiketa dhe atribute të veçanta, dhe më pas e analizon atë në përbërësit e tij, duke nxjerrë fragmentet e të dhënave që na duhen. Kodi XML zakonisht duket diçka si kjo:
Në XML, çdo element i të dhënave duhet të jetë i mbyllur në etiketa. Një etiketë është një tekst (në shembullin e mësipërm është menaxheri, emri, fitimi) i mbyllur në kllapa këndore. Etiketat vijnë gjithmonë në çifte – hapen dhe mbyllen (me një të pjerrët të shtuar në fillim).
Funksioni FILTER.XML mund të nxjerrë lehtësisht përmbajtjen e të gjitha etiketave që na duhen, për shembull, emrat e të gjithë menaxherëve dhe (më e rëndësishmja) t'i shfaqë të gjitha menjëherë në një listë. Pra, detyra jonë është të shtojmë etiketa në tekstin burimor, duke e kthyer atë në kod XML të përshtatshëm për analiza të mëvonshme nga funksioni FILTER.XML.
Nëse marrim si shembull adresën e parë nga lista jonë, atëherë do të na duhet ta kthejmë atë në këtë ndërtim:
Kam thirrur etiketën globale të hapjes dhe mbylljes së të gjithë tekstit t, dhe etiketat që inkuadrojnë çdo element janë s., por mund të përdorni çdo emërtim tjetër - nuk ka rëndësi.
Nëse heqim shënimet dhe ndërprerjet e rreshtave nga ky kod - plotësisht, meqë ra fjala, opsionale dhe të shtuara vetëm për qartësi, atëherë e gjithë kjo do të kthehet në një rresht:
Dhe tashmë mund të merret relativisht lehtë nga adresa e burimit duke zëvendësuar presjet në të me disa etiketa duke përdorur funksionin ZUBVENDITSUESI (ZËVENDËSUES) dhe ngjitja me simbolin & në fillim dhe në fund të etiketave hapëse dhe mbyllëse:
Për të zgjeruar gamën që rezulton horizontalisht, ne përdorim funksionin standard TRANSP (TRANSPOZOJ), duke e mbështjellë formulën tonë në të:
Një veçori e rëndësishme e gjithë këtij dizajni është se në versionin e ri të Office 2021 dhe Office 365 me mbështetje për vargje dinamike, nuk kërkohen gjeste të veçanta për të hyrë - thjesht futni dhe klikoni mbi Shkruani – formula vetë zë numrin e qelizave që i nevojiten dhe gjithçka funksionon me zhurmë. Në versionet e mëparshme, ku nuk kishte ende grupe dinamike, do t'ju duhet së pari të zgjidhni një numër të mjaftueshëm qelizash boshe përpara se të futni formulën (mundeni me një diferencë), dhe pasi të krijoni formulën, shtypni shkurtoren e tastierës Ctrl+Ndryshim+Shkruanipër ta futur atë si një formulë grupi.
Një mashtrim i ngjashëm mund të përdoret kur ndani tekstin e mbërthyer së bashku në një qelizë përmes një ndërprerjeje rreshti:
Dallimi i vetëm me shembullin e mëparshëm është se në vend të presjes, këtu ne zëvendësojmë karakterin e padukshëm të ndërprerjes së linjës Alt + Enter, i cili mund të specifikohet në formulë duke përdorur funksionin CHAR me kodin 10.
- Hollësitë e punës me ndërprerjet e linjës (Alt + Enter) në Excel
- Ndani tekstin sipas kolonave në Excel
- Zëvendësimi i tekstit me SUBSTITUTE