Niedawno omawialiśmy wykorzystanie funkcji FILTER.XML do importowania danych XML z Internetu – głównego zadania, do którego ta funkcja jest de facto przeznaczona. Po drodze pojawiło się jednak inne nieoczekiwane i piękne zastosowanie tej funkcji – do szybkiego dzielenia lepkiego tekstu na komórki.
Załóżmy, że mamy taką kolumnę danych:
Oczywiście dla wygody chciałbym podzielić go na osobne kolumny: nazwa firmy, miasto, ulica, dom. Możesz to zrobić na kilka różnych sposobów:
- Zastosowanie Tekst według kolumn z zakładki Dane (Dane — Tekst do kolumn) i przejdź trzy kroki Parser tekstu. Ale jeśli jutro ulegną zmianie dane, będziesz musiał powtórzyć cały proces.
- Załaduj te dane do dodatku Power Query i podziel je tam, a następnie prześlij je z powrotem do arkusza, a następnie zaktualizuj zapytanie, gdy dane ulegną zmianie (co już jest łatwiejsze).
- Jeśli potrzebujesz aktualizacji w locie, możesz napisać bardzo złożone formuły, aby znaleźć przecinki i wyodrębnić tekst między nimi.
I można to zrobić bardziej elegancko i skorzystać z funkcji FILTER.XML, ale co to ma z tym wspólnego?
Funkcja FILTER.XML otrzymuje jako swój argument początkowy kod XML — tekst oznaczony specjalnymi znacznikami i atrybutami, a następnie analizuje go na składniki, wyodrębniając potrzebne nam fragmenty danych. Kod XML zwykle wygląda mniej więcej tak:
W XML każdy element danych musi być ujęty w znaczniki. Znacznik to tekst (w powyższym przykładzie jest to menedżer, nazwisko, zysk) ujęty w nawiasy ostre. Tagi zawsze występują parami – otwierające i zamykające (z ukośnikiem dodanym na początku).
Funkcja FILTER.XML może w łatwy sposób wyodrębnić zawartość wszystkich potrzebnych nam tagów, na przykład nazwiska wszystkich menedżerów i (co najważniejsze) wyświetlić je wszystkie naraz na jednej liście. Naszym zadaniem jest więc dodanie tagów do tekstu źródłowego, przekształcając go w kod XML nadający się do późniejszej analizy przez funkcję FILTER.XML.
Jeśli jako przykład weźmiemy pierwszy adres z naszej listy, będziemy musieli przekształcić go w tę konstrukcję:
Wywołałem globalne otwieranie i zamykanie wszystkich tagów tekstowych t, a tagi otaczające każdy element to s., ale możesz użyć dowolnych innych oznaczeń – to nie ma znaczenia.
Jeśli usuniemy z tego kodu wcięcia i łamania wierszy – zupełnie przy okazji, opcjonalne i dodane tylko dla jasności, to wszystko to zamieni się w wiersz:
A można go już stosunkowo łatwo uzyskać z adresu źródłowego, zastępując w nim przecinki kilkoma tagami korzystanie z funkcji ZASTĄPIĆ (ZASTĄPIĆ) i sklejenie z symbolem & na początku i na końcu tagów otwierających i zamykających:
Aby rozszerzyć wynikowy zakres w poziomie, korzystamy ze standardowej funkcji TRANSP (TRANSPONOWAĆ), owijając w nią naszą formułę:
Ważną cechą całego projektu jest to, że w nowej wersji Office 2021 i Office 365 z obsługą tablic dynamicznych nie są wymagane żadne specjalne gesty do wprowadzania danych – wystarczy wejść i kliknąć Wchodzę – sama formuła zajmuje tyle komórek, ile potrzebuje i wszystko działa z hukiem. W poprzednich wersjach, gdzie nie było jeszcze tablic dynamicznych, przed wprowadzeniem formuły trzeba było najpierw zaznaczyć odpowiednią liczbę pustych komórek (można z marginesem), a po utworzeniu formuły nacisnąć skrót klawiaturowy Ctrl+Shift+Wchodzęaby wprowadzić go jako formułę tablicową.
Podobną sztuczkę można zastosować podczas oddzielania tekstu sklejonego w jedną komórkę za pomocą podziału wiersza:
Jedyną różnicą w stosunku do poprzedniego przykładu jest to, że zamiast przecinka zastępujemy tutaj niewidoczny znak łamania linii Alt + Enter, który można określić w formule za pomocą funkcji ZNAK kodem 10.
- Subtelności pracy z podziałami wierszy (Alt + Enter) w Excelu
- Podziel tekst według kolumn w Excelu
- Zamiana tekstu na SUBSTITUTE