Jak wyodrębnić ostatni komponent z ciągu znaków?
- Autor: Mirosław Zalewski
- 29 czerwca 2013
- Komentarzy: 1
Niektóre komórki zawierają kilka elementów oddzielonych od siebie separatorem. Najprostszym przykładem jest imię oddzielone od nazwiska spacją. Artykuł opisuje trzy sposoby na wyodrębnienie ostatniego komponentu z takiego ciągu znaków: wyrażenie regularne, funkcję USUŃ.ZBĘDNE.ODSTĘPY oraz funkcję ZNAJDŹ.
Przede wszystkim, komórki takie można podzielić ze względu na dwie cechy: (1) czy separator może pojawić się na końcu tekstu, czy nie? oraz (2) czy separator jest tylko jednym znakiem, czy kilkoma? Skrzyżowanie tych kryteriów daje cztery przypadki, które przedstawia zrzut ekranu poniżej.
We wszystkich z nich ostatnim komponentem jest słowo „elementów”; jego uzyskanie jest naszym ostatecznym celem. Do wykonania tego zadania wykorzystamy trzy różne techniki: wyrażenie regularne, formułę wykorzystującą funkcję USUŃ.ZBĘDNE.ODSTĘPY oraz formułę wykorzystującą funkcję ZNAJDŹ.
Wyrażenie regularne
Często najszybszym i najprostszym sposobem jest wykorzystanie wyrażeń regularnych. Niestety, nie uwzględniają one późniejszych zmian w kolumnie źródłowej. Jeżeli dodamy nowe pozycje lub zmienimy już istniejące, całą operację będziemy musieli powtórzyć.
Wpierw musimy skopiować nasze teksty do nowej kolumny. Następnie wybieramy z menu Edycja → Znajdź i zamień.... Pojawi się nowe okno, w którym klikamy Więcej opcji i zaznaczamy Wyrażenie regularne oraz Tylko bieżące zaznaczenie.
Jeżeli mamy pewność, że separator nie pojawi się na końcu tekstu, w polu Szukaj wpisujemy: .*/ (w miejscu ukośnika wstawiamy nasz separator — niezależnie czy liczy on tylko jeden, czy kilka znaków). Pole Zamień na pozostawiamy puste i wciskamy Zamień wszyst..
W ten sposób usuniemy wszystkie znaki od początku tekstu do ostatniego wystąpienia separatora. Innymi słowy: pozostawimy jedynie fragment po ostatnim separatorze.
Oczywiście wyrażenie to nie sprawdzi się, gdy separator występuje na końcu któregoś z ciągów znaków. W takim wypadku w polu Szukaj należy wpisać: .*@@(((?!@@).)+)(@@)?, zaś w polu Zamień na: $1. Sposób ten zadziała wyłącznie w LibreOffice 4.0 i nowszych.
Początek tego wyrażenia niczym nie różni się od poprzedniego — obejmuje fragment od pierwszego znaku do przedostatniego separatora. (?!@@) to negative lookahead – zapewnia on, że na danej pozycji nie występuje separator. Kropka oznacza dowolny znak, zaś plus — przynajmniej jednokrotne wystąpienie wyrażenia przed nim. Cały zapis ((?!@@).)+ można luźno przetłumaczyć na „dowolny znak, po którym nie występuje separator, jeden lub więcej razy”. Dzięki ujęciu w nawias można się do niego odwołać w polu Zamień na. Całą formułę kończy separator, po którym występuje znak zapytania. Oznacza on „zero lub jeden raz”, dzięki czemu ostatni separator jest opcjonalny.
Gdy nasz separator liczy tylko jeden znak, skorzystać możemy z trochę prostszego wyrażenia: .*/([^/]+)/?. Zasada jego działania jest identyczna jak powyżej, tylko zamiast negative lookahead korzystamy z negacji zakresu — [^/] oznacza „każdy znak, który nie jest ukośnikiem”. W polu Zamień na ponownie umieszczamy $1.
Funkcja USUŃ.ZBĘDNE.ODSTĘPY
Zacznijmy od samej formuły. W najprostszej postaci wygląda następująco (tak jak wcześniej, w miejscu wytłuszczonego ukośnika należy wstawić wykorzystywany separator):
=USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(PODSTAW(A3;"/";POWT(" ";100));100))
Jej działanie jest bardzo proste. Przy pomocy funkcji PODSTAW zamienia w tekście źródłowym separator na powtórzoną sto razy spację. Następnie pobiera sto znaków z końca tak zmodyfikowanego tekstu. W ten sposób zwraca ostatni komponent, ale zawierający na początku wiele odstępów. Usuwamy je przy pomocy funkcji USUŃ.ZBĘDNE.ODSTĘPY, która likwiduje białe znaki na początku i na końcu ciągu znaków.
Cała formuła opiera się na założeniu, że ostatni komponent będzie liczył mniej niż 100 znaków. Jeżeli jest dłuższy, jego początek zostanie pominięty. Oczywiście możemy tę liczbę dowolnie zwiększać, jednak okupimy to szybkością działania arkusza oraz ilością zajmowanej przez niego pamięci operacyjnej. Może mieć to znaczenie przy naprawdę dużych zbiorach danych.
Niestety, powyższa formuła nie jest przygotowana do obsługi ciągów znaków kończących się separatorem. To ograniczenie możemy obejść poprzez jego zamianę na spację i usunięcie odstępów z uzyskanego w ten sposób tekstu jeszcze przed wstawieniem stokrotnie powtórzonej spacji:
=USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(PODSTAW(B3;"/";" "));" ";POWT(" ";100));100))
Tak skonstruowana formuła posiada jeszcze jedną wadę. Otóż zwróci niepoprawne wyniki w sytuacji, gdy w ostatnim komponencie występują spacje.
Aby temu zapobiec, ponownie wykorzystamy funkcję PODSTAW. Wpierw zamienimy odstęp na jakiś unikatowy, niewystępujący w tekście symbol (np. trzy pionowe kreski — „|||”). Następnie wykonamy wszystkie niezbędne przekształcenia, a na końcu zamienimy nasz unikatowy symbol z powrotem na spacje.
Odpowiednia formuła znajduje się poniżej:
=PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(PODSTAW(PODSTAW(B3;" ";"|||");"/";" "));" ";POWT(" ";100));100));"|||";" ")
Funkcja ZNAJDŹ
Ostatnim sposobem jest wykorzystanie funkcji ZNAJDŹ. Ponownie rozpoczniemy od podstawowej formuły. Sposób jej działania został szczegółowo opisany w artykule o pobieraniu nazwy bieżącego pliku.
=PRAWY(B2;DŁ(B2)-ZNAJDŹ("|||";PODSTAW(B2;"/";"|||";DŁ(B2) - DŁ(PODSTAW(B2;"/";"")))))
W przeciwieństwie do formuły wykorzystującej funkcję USUŃ.ZBĘDNE.ODSTĘPY, ten sposób nie zakłada określania maksymalnej liczby znaków ostatniego komponentu. Jednak jego wadą jest niski poziom czytelności i prostoty. Maleje on dodatkowo, kiedy separator może wystąpić na końcu tekstu lub liczy więcej niż jeden znak.
Przyjrzyjmy się wpierw sytuacji, gdy separator kończy tekst. Możemy go usunąć wykorzystując funkcje LEWY oraz DŁ. Ponieważ nie wszystkie ciągi znaków kończą się separatorem, nie możemy tego zrobić jednym ciągiem. Wykorzystamy więc funkcję JEŻELI — przy jej pomocy sprawdzimy, czy zawartość komórki kończy się separatorem. Jeśli tak, zmodyfikujemy ją odpowiednio; jeśli nie, pozostawimy ją bez zmian.
=JEŻELI(PRAWY(B3;DŁ("/"))="/";LEWY(B3;DŁ(B3)-DŁ("/"));B3)
Powyższa formuła została napisana w taki sposób, aby wystarczyło wpisać separator w wytłuszczonych miejscach. Jeżeli nie chcemy kilkukrotnie obliczać jego długości, możemy po prostu wpisać odpowiednią liczbę.
Następnym krokiem jest umieszczenie tej formuły w miejscu odwołania do komórki z tekstem w wersji podstawowej:
=PRAWY(JEŻELI(PRAWY(B2;DŁ("/"))="/";LEWY(B2;DŁ(B2)-DŁ("/"));B2);DŁ(JEŻELI(PRAWY(B2;DŁ("/"))="/";LEWY(B2;DŁ(B2)-DŁ("/"));B2))-ZNAJDŹ("|||";PODSTAW(JEŻELI(PRAWY(B2;DŁ("/"))="/";LEWY(B2;DŁ(B2)-DŁ("/"));B2);"/";"|||";DŁ(JEŻELI(PRAWY(B2;DŁ("/"))="/";LEWY(B2;DŁ(B2)-DŁ("/"));B2)) - DŁ(PODSTAW(JEŻELI(PRAWY(B2;DŁ("/"))="/";LEWY(B2;DŁ(B2)-DŁ("/"));B2);"/";"")))))
Dotychczasowe formuły sprawdzą się tylko wtedy, gdy separator liczy jeden znak. Jeżeli jest dłuższy, trzeba dokonać kilku poprawek; dla uproszczenia tymczasowo założymy, że separator nigdy nie występuje na końcu tekstu.
Aby poznać liczbę separatorów w tekście, od jego całkowitej długości odejmujemy długość bez separatorów. Otrzymany wynik jest więc ilorazem długości jednego separatora oraz ich liczby (usunięcie z tekstu dwóch separatorów trzyznakowych skróci jego długość o sześć znaków). Żeby otrzymać wartość, na której naprawdę nam zależy, musimy go podzielić przez liczbę znaków w separatorze:
(DŁ(B2) - DŁ(PODSTAW(B2;"@@";"")))/DŁ("@@")
Wykorzystana funkcja ZNAJDŹ zwraca pozycję pierwszego znaku ostatniego separatora. W rezultacie funkcja PRAWY zwraca również cały separator za wyjątkiem pierwszego znaku. Dlatego od drugiego jej argumentu musimy dodatkowo odjąć liczbę znaków w separatorze pomniejszoną o jeden:
ZNAJDŹ("|||"; […])-(DŁ("@@")-1
Po naniesieniu tych poprawek otrzymujemy formułę działającą również na separatorach wieloznakowych:
=PRAWY(B4;DŁ(B4)-ZNAJDŹ("|||";PODSTAW(B4;"@@";"|||";(DŁ(B4) - DŁ(PODSTAW(B4;"@@";"")))/DŁ("@@")))-(DŁ("@@")-1))
Jeżeli dodatkowo chcemy zapewnić obsługę separatorów na końcu tekstu, musimy w miejscu odwołań do komórek z tekstem umieścić podaną wcześniej formułę warunkową:
=PRAWY(JEŻELI(PRAWY(B5;DŁ("@@"))="@@";LEWY(B5;DŁ(B5)-DŁ("@@"));B5);DŁ(JEŻELI(PRAWY(B5;DŁ("@@"))="@@";LEWY(B5;DŁ(B5)-DŁ("@@"));B5))-ZNAJDŹ("|||";PODSTAW(JEŻELI(PRAWY(B5;DŁ("@@"))="@@";LEWY(B5;DŁ(B5)-DŁ("@@"));B5);"@@";"|||";(DŁ(JEŻELI(PRAWY(B5;DŁ("@@"))="@@";LEWY(B5;DŁ(B5)-DŁ("@@"));B5)) - DŁ(PODSTAW(JEŻELI(PRAWY(B5;DŁ("@@"))="@@";LEWY(B5;DŁ(B5)-DŁ("@@"));B5);"@@";"")))/DŁ("@@")))-(DŁ("@@")-1))
Jednak osobiście uważam, że lepiej jest poświęcić kilka kolumn na przetrzymywanie obliczeń tymczasowych. W ten sposób nie tylko formuła zyska na czytelności, ale również jej obliczenie powinno mniej obciążyć zasoby komputera.
Geniusz, dziękuję! ^^