W końcu praca z tekstem staje się łatwiejsza dzięki nowym formułom w Excelu. Szczególne znaczenie ma to dla mniej zaawansowanych użytkowników Excela, którzy nie zawsze radzili sobie zagnieżdżaniem kilku formuł aby uzyskać założony efekt. Dziś przedstawię trzy formuły, które są bardzo proste i praktyczne. Są one na tyle świeże, że nie posiadają jeszcze pełnego tłumaczenia na polski. Mogą też być niedostępne w starszych wersjach Excela.
Spis Treści
Formuła TEKST.PO
Często dysponujemy danymi tekstowymi, które zawierają w jednej komórce cały zestaw informacji. Ich struktura dla człowieka jest bardzo łatwa do rozpoznania, ale już wyodrębnienie tych informacji może sprawiać problemy. Dla przykładu mamy nazwisko, imię i numer telefonu. Chcemy wyodrębnić telefon, zazwyczaj robiło się to za pomocą formuły SZUKAJ.TEKST, która wyszukiwała odpowiedni ciąg znaków i podawała jego pozycję. To z kolei było wykorzystywane do wyliczenia długości tekstu jaki miała wyciągnąć formuła PRAWY. Teraz w końcu mamy formułę, która załatwia to za jednym zamachem. Wystarczy podać po jakim ciągu znaków jest tekst który chcemy wyodrębnić z danych.
Jest to najprostsze zastosowanie formuły TEKST.PO. Jak widać na obrazku formuła posiada szereg dodatkowych argumentów, co jest charakterystyczne dla nowododawanych formuł. Od razu otrzymujemy bardzo szeroki obszar ich zastosowania. Poniżej opis parametrów:
- Text – tekst z jakiego wyodrębniamy dane
- Delimiter – ciąg znaków po którym wyodrębniona zostanie wartość
- instance_num – określa które wystąpienie szukanego ciągu znaków nas interesuje. Jeśli podamy wartość 2 to zostanie pominięte pierwsze wystąpienie. Jeśli podamy wartość ujemną to wyszukiwanie działa od końca tekstu i adekwatnie -1 da pierwsze wystąpienie od końca, a -2 drugie wystąpienie od końca tekstu.
- match_mode – określa czy ma być uwzględnione rozpoznawanie wielkości liter. Dla 0 wielkość liter ma znaczenie, dla 1 nie ma znaczenia.
- match_end – czy dopasować ogranicznik do końca tekstu. Oznacza to, że w przypadku nieznalezienia ciągu znaków dla parametru 0 (nie dopasowuj) otrzymamy błąd #N/D, a dla parametru 1 (dopasowuj) otrzymamy pusty ciąg znaków.
- if_not_found – wartość zwracana w przypadku nie znalezienia szukanego ciągu znaków. Coś czego brakuje w wielu starszych formułach Excela.
Formuła TEKST.PRZED
Wyodrębnianiem tekstu w drugą stronę zajmuje się formuła TEKST.PRZED. Jej sposób działania jest identyczny jak w formuły TEKST.PO.
A co jeżeli musimy wyodrębnić jakiś fragment tekstu ze środka tekstu? Dotychczas wykorzystywało się formułę FRAGMENT.TEKSTU, która jako parametry miała wyniki formuł SZUKAJ.TEKST. Dla poprawnych, kompletnych danych działało to dobrze. Natomiast brak opcji rozpoznawania wielkości liter, pomijania wystąpień albo podawania wartości w przypadku nieznalezienia ciągu znaków (którego wynikiem był błąd) bardzo komplikowało funkcję. Tu wystarczy połączyć formułę TEKST.PO oraz TEKST.PRZED z odpowiednimi parametrami aby zadziałała za każdym razem. Poniżej prosty przykład.
Formuła PODZIEL.TEKST
Często pracując na danych z plików tekstowych czy np. kopiowanych z internetu posiadamy zestaw danych połączonych ze sobą w jeden tekst. Dane są rozdzielane np. przecinkiem. Wyodrębnienie danych wymaga podzielanie tekstu według tego ogranicznika. Na ogół robi się to przez Tekst jako kolumny lub proste działanie w PowerQuery lub skomplikowaną funkcję tablicową. Pierwsze niestety jest statyczne, więc musimy je robić ręcznie za każdym razem gdy zmieniają się dane. Drugie i trzecie rozwiązanie jest dla bardziej zaawansowanych użytkowników.
Po tym jak Excel przeszedł rewolucję i wprowadzono dynamiczne formuły tablicowe, możliwości pracy z danymi bardzo się rozszerzyły. Teraz formuła zapisana w jednej komórce potrafi „rozlać” swoje wyniki na odpowiedni zakres komórek. Taką nową formułą jest właśnie PODZIEL.TEKST. Zobacz na poniższych danych jak proste jest teraz podzielenie danych na odpowiednie kolumny.
W tej formule otrzymaliśmy nawet opcję rozdzielenia danych na wiersze jeżeli tekst jest zbiorem elementów tablicy. W tym przypadku podział na kolumny jest po przecinku, a na wiersze po średniku
Poniżej opis wszystkich parametrów
- text – tekst jaki chcemy podzielić
- col_delimiter – znak(i) rozdzielające tekst na kolumny
- row_delimiter – znak(i) rozdzielające tekst na wiersze
- ignore_empty – czy ignorować puste komórki, przy wartości PRAWDA w ostatnim rekordzie w przykładzie Warszawa byłaby wpisana w kolumnę Telefon.
- match_mode – określa czy ma być uwzględnione rozpoznawanie wielkości liter. Dla 0 wielkość liter ma znaczenie, dla 1 nie ma znaczenia
- pad_with – wartość, gdy nie jest możliwe wyciągnięcie wartości dla podziału na wiersze i kolumny
Podsumowanie
Niezależnie od zawodu i zajmowanego stanowiska czy przydzielonego zadania wiele czasu poświęcamy na przekształcanie danych, w tym pracę z tekstem w Excelu. Narzędzia Microsoftu cały czas dostarczają nam nowych możliwości, które znacząco upraszczają i przyspieszają naszą pracę. Aby z nich skorzystać potrzeba dwóch rzeczy. Posiadać najnowszą wersję Excela, najlepiej mieć Office 365 (i jeśli to możliwe należeć do niejawnego programu testów) oraz śledzić informacje o nowościach dodawanych do pakietu Office. Jak już nie raz pisałem, osoby rozwijające pakiet Office nie chwalą się swoją pracą przez dziesiątki wyskakujących okienek z newsami, tutorialami itd. Na ogół musisz sam(a) poszukać informacji o tym co nowego zostało dodane. A te nowości to naprawdę duże kroki naprzód w efektywności naszej codziennej pracy.
