Analizuj tekst za pomocą wyrażeń regularnych (RegExp) w programie Excel

Jednym z najbardziej czasochłonnych i frustrujących zadań podczas pracy z tekstem w programie Excel jest rozbiór gramatyczny zdania – parsowanie alfanumerycznej „owsianki” na składniki i wydobywanie z niej potrzebnych nam fragmentów. Na przykład:

• wyodrębnienie kodu pocztowego z adresu (dobrze jeśli kod pocztowy jest zawsze na początku, ale co jeśli nie?)
• odnalezienie numeru i daty faktury z opisu wpłaty na wyciągu bankowym
• ekstrakcja NIP z pstrokatych opisów spółek z listy kontrahentów
• wyszukaj numer samochodu lub numer artykułu w opisie itp.
  

Zwykle w takich przypadkach, po półgodzinnym mozolnym przeszukiwaniu tekstu ręcznie, zaczynają przychodzić do głowy myśli, aby jakoś zautomatyzować ten proces (zwłaszcza jeśli danych jest dużo). Istnieje kilka rozwiązań o różnym stopniu złożoności-efektywności:

• Zastosowanie wbudowane funkcje tekstowe Excel do wyszukiwania-wycinania-klejenia tekstu: LEVSIMV (LEWO), PRAWO (DOBRZE), PSTR (Środek), STsEPIT (POWIĄZAĆ) i jego analogi, POŁĄCZYĆ (TEKST POŁĄCZENIA), DOKŁADNY (DOKŁADNY) itp. Ta metoda jest dobra, jeśli w tekście jest wyraźna logika (na przykład indeks jest zawsze na początku adresu). W przeciwnym razie formuły stają się znacznie bardziej skomplikowane, a czasami nawet formuły tablicowe, co znacznie spowalnia w przypadku dużych tabel.
• Korzystanie z jak operator podobieństwa tekstu z Visual Basic opakowanego w funkcję niestandardowego makra. Pozwala to na zaimplementowanie bardziej elastycznego wyszukiwania za pomocą symboli wieloznacznych (*, #,? itp.) Niestety to narzędzie nie może wyodrębnić pożądanego podciągu z tekstu – sprawdź tylko, czy jest w nim zawarty.

Oprócz powyższego istnieje jeszcze jedno podejście, które jest bardzo dobrze znane w wąskich kręgach zawodowych programistów, webdeveloperów i innych techników – jest to wyrażenia regularne (Wyrażenia regularne = RegExp = „regexps” = „regular”). Mówiąc prosto, RegExp to język, w którym znaki specjalne i reguły służą do wyszukiwania niezbędnych podciągów w tekście, wyodrębniania ich lub zastępowania innym tekstem. Wyrażenia regularne to bardzo potężne i piękne narzędzie, które o rząd wielkości przewyższa wszystkie inne sposoby pracy z tekstem. Wiele języków programowania (C#, PHP, Perl, JavaScript…) i edytorów tekstu (Word, Notepad++…) obsługuje wyrażenia regularne.

Microsoft Excel niestety nie obsługuje RegExp po wyjęciu z pudełka, ale można to łatwo naprawić za pomocą VBA. Otwórz Edytor Visual Basic z zakładki wywoływacz (Deweloper) lub skrót klawiaturowy inny+F11. Następnie włóż nowy moduł przez menu Wstaw – moduł i skopiuj tam tekst następującej funkcji makra:

Funkcja publiczna RegExpExtract(tekst jako ciąg, wzorzec jako ciąg, element opcjonalny jako liczba całkowita = 1) jako ciąg w przypadku błędu GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Wzorzec regex.Global = True If regex.Test (Tekst) Następnie ustaw dopasowania = regex.Execute(Text) RegExpExtract = match.Item(Item - 1) Zakończ funkcję End If ErrHandl: RegExpExtract = CVErr(xlErrValue) Zakończ funkcję  

Możemy teraz zamknąć Edytor Visual Basic i wrócić do programu Excel, aby wypróbować naszą nową funkcję. Jego składnia jest następująca:

=RegExpExtract( Tekst ; Wzór ; Element )

gdzie

• txt – komórka z tekstem, który sprawdzamy i z którego chcemy wydobyć potrzebny nam podciąg
• wzorzec – maska ​​(wzór) do wyszukiwania podciągów
• Pozycja – numer sekwencyjny podciągu do wyodrębnienia, jeśli jest ich kilka (jeśli nie podano, to wyświetlane jest pierwsze wystąpienie)

Najciekawszą rzeczą jest tutaj oczywiście Pattern – szablonowy ciąg znaków specjalnych „w języku” RegExp, który określa co dokładnie i gdzie chcemy znaleźć. Oto najbardziej podstawowe, od których możesz zacząć:

Jeśli szukamy określonej liczby znaków, na przykład sześciocyfrowego kodu pocztowego lub wszystkich trzyliterowych kodów produktów, to przychodzimy na ratunek kwantyfikatory or kwantyfikatory to specjalne wyrażenia określające liczbę znaków do przeszukania. Kwantyfikatory są stosowane do znaku, który jest przed nim:

Przejdźmy teraz do najciekawszej części – analizy zastosowania stworzonej funkcji i tego, czego dowiedzieliśmy się o wzorcach na praktycznych przykładach z życia.

Wyodrębnianie liczb z tekstu

Na początek przeanalizujmy prosty przypadek – z owsianki alfanumerycznej trzeba wydobyć pierwszą liczbę, np. moc zasilaczy bezprzerwowych z cennika:

Logika wyrażenia regularnego jest prosta: d oznacza dowolną cyfrę, a kwantyfikator + mówi, że ich liczba powinna wynosić jeden lub więcej. Podwójny minus przed funkcją jest potrzebny, aby „w locie” przekonwertować wyodrębnione znaki na pełną liczbę z liczby jako tekstu.

Kod pocztowy

Na pierwszy rzut oka wszystko jest tutaj proste – szukamy dokładnie sześciu cyfr pod rząd. Używamy znaku specjalnego d dla cyfry i kwantyfikatora 6 {} za ilość znaków:

Możliwa jest jednak sytuacja, gdy po lewej stronie indeksu w linii znajduje się kolejny duży zestaw liczb w rzędzie (numer telefonu, NIP, konto bankowe itp.) Wtedy nasz sezon regularny wyciągnie pierwszych 6 cyfry z niego, czyli nie będzie działać poprawnie:

Aby temu zapobiec, musimy dodać modyfikator wokół krawędzi naszego wyrażenia regularnego b oznaczający koniec słowa. To wyjaśni Excelowi, że potrzebny nam fragment (indeks) powinien być oddzielnym słowem, a nie częścią innego fragmentu (numeru telefonu):

Telefon

Problem ze znalezieniem numeru telefonu w tekście polega na tym, że jest tak wiele możliwości wpisywania numerów – z myślnikami i bez, ze spacjami, z lub bez kodu regionu w nawiasach itd. Dlatego moim zdaniem łatwiej najpierw usuń wszystkie te znaki z tekstu źródłowego za pomocą kilku zagnieżdżonych funkcji ZASTĄPIĆ (ZASTĄPIĆ)tak, że skleja się w jedną całość, a potem z prymitywnym regularnym d{11} wyciągnij 11 cyfr z rzędu:

ITN

Tutaj sprawa jest trochę bardziej skomplikowana, ponieważ NIP (w naszym kraju) może być 10-cyfrowy (dla osób prawnych) lub 12-cyfrowy (dla osób fizycznych). Jeśli nie znajdujesz wady szczególnie, to całkiem możliwe, że będziesz zadowolony z regularnego d{10,12}, ale ściśle mówiąc, wyciągnie wszystkie liczby od 10 do 12 znaków, czyli błędnie wpisane 11 cyfr. Bardziej poprawne byłoby użycie dwóch wzorców połączonych operatorem logicznym OR | (pionowy pasek):

Należy pamiętać, że w zapytaniu najpierw szukamy liczb 12-bitowych, a dopiero potem liczb 10-bitowych. Jeśli napiszemy nasze wyrażenie regularne na odwrót, wyciągnie ono dla wszystkich, nawet długich 12-bitowych numerów TIN, tylko pierwszych 10 znaków. Oznacza to, że po wyzwoleniu pierwszego warunku dalsza weryfikacja nie jest już wykonywana:

To jest podstawowa różnica między operatorem | ze standardowej funkcji logicznej programu Excel OR (OR), gdzie zmiana kolejności argumentów nie zmienia wyniku.

SKU produktów

W wielu firmach unikalne identyfikatory są przypisywane towarom i usługom – artykułom, kodom SAP, SKU itp. Jeśli w ich zapisie jest logika, to można je łatwo wyciągnąć z dowolnego tekstu za pomocą wyrażeń regularnych. Na przykład, jeśli wiemy, że nasze artykuły zawsze składają się z trzech wielkich liter angielskich, łącznika i kolejnej trzycyfrowej liczby, to:

Logika szablonu jest prosta. [AZ] – oznacza dowolne wielkie litery alfabetu łacińskiego. Następny kwantyfikator 3 {} mówi, że ważne jest dla nas, że są dokładnie trzy takie litery. Po myślniku czekamy na trzy cyfry, więc dodajemy na końcu d{3}

Kwoty gotówkowe

W podobny sposób jak w poprzednim akapicie możesz również wyciągnąć ceny (koszty, VAT…) z opisu towaru. Jeśli na przykład kwoty pieniężne są oznaczone myślnikiem, to:

Wzór d z kwantyfikatorem + wyszukuje dowolną liczbę do łącznika i d{2} poszuka pensów (dwucyfrowych).

Jeśli potrzebujesz wyodrębnić nie ceny, ale podatek VAT, możesz użyć trzeciego opcjonalnego argumentu naszej funkcji RegExpExtract, która określa numer porządkowy elementu do wyodrębnienia. I oczywiście możesz zastąpić funkcję ZASTĄPIĆ (ZASTĄPIĆ) w wynikach należy połączyć łącznik ze standardowym separatorem dziesiętnym i dodać podwójny minus na początku, aby Excel zinterpretował znaleziony podatek VAT jako normalną liczbę:

Numery rejestracyjne samochodów

Jeśli nie bierzesz pojazdów specjalnych, przyczep i innych motocykli, standardowy numer samochodu jest analizowany zgodnie z zasadą „litera – trzy cyfry – dwie litery – kod regionu”. Ponadto kod regionu może być 2- lub 3-cyfrowy, a jako litery używane są tylko te, które wyglądem przypominają alfabet łaciński. Zatem poniższe wyrażenie regularne pomoże nam wyodrębnić liczby z tekstu:

Czas

Aby wyodrębnić czas w formacie GG:MM, odpowiednie jest następujące wyrażenie regularne:

Po fragmencie okrężnicy [0-5]d, jak łatwo się domyślić, ustawia dowolną liczbę z zakresu 00-59. Przed dwukropkiem w nawiasach działają dwa wzorce oddzielone logicznym OR (pionową kreską):

• [0-1]d – dowolna liczba z zakresu 00-19
• 2[0-3] – dowolna liczba z zakresu 20-23

Do uzyskanego wyniku można dodatkowo zastosować standardową funkcję Excela CZAS (ZESPÓŁ)przekonwertować go na format czasu zrozumiały dla programu i nadający się do dalszych obliczeń.

Sprawdzanie hasła

Załóżmy, że musimy sprawdzić poprawność listy haseł wymyślonych przez użytkowników. Zgodnie z naszymi zasadami hasła mogą zawierać tylko litery angielskie (małe lub wielkie) oraz cyfry. Spacje, podkreślenia i inne znaki interpunkcyjne są niedozwolone.

Sprawdzanie można zorganizować za pomocą następującego prostego wyrażenia regularnego:

W rzeczywistości przy takim wzorze wymagamy, aby między początkiem (^) i koniec ($) w naszym tekście były tylko znaki ze zbioru podanego w nawiasach kwadratowych. Jeśli musisz również sprawdzić długość hasła (na przykład co najmniej 6 znaków), to kwantyfikator + można zastąpić interwałem „sześć lub więcej” w formularzu {6,}:

Miasto z adresu

Powiedzmy, że musimy pobrać miasto z paska adresu. Pomoże w tym zwykły program, wyodrębniający tekst z „g”. do następnego przecinka:

Przyjrzyjmy się bliżej temu wzorowi.

Jeśli przeczytałeś powyższy tekst, to już zrozumiałeś, że niektóre znaki w wyrażeniach regularnych (kropki, gwiazdki, znaki dolara itp.) mają specjalne znaczenie. Jeśli musisz szukać tych znaków samodzielnie, są one poprzedzone odwrotnym ukośnikiem (czasami nazywanym ekranowanie). Dlatego szukając fragmentu „g”. musimy pisać w wyrażeniu regularnym Pan jeśli szukamy plusa, to + itd.

Kolejne dwa znaki w naszym szablonie, kropka i gwiazdka kwantyfikatora, oznaczają dowolną liczbę dowolnych znaków, tj. dowolną nazwę miasta.

Na końcu szablonu znajduje się przecinek, ponieważ szukamy tekstu od „g”. do przecinka. Ale w tekście może być kilka przecinków, prawda? Nie tylko po mieście, ale także po ulicy, domach itp. Na którym z nich zatrzyma się nasza prośba? Po to jest znak zapytania. Bez tego nasze wyrażenie regularne wyciągnęłoby możliwie najdłuższy ciąg:

W przypadku wyrażeń regularnych taki wzorzec jest „chciwy”. Aby poprawić sytuację, potrzebny jest znak zapytania – czyni on kwantyfikator, po którym jest „skąpy” – a nasze zapytanie przenosi tekst tylko do pierwszego przecinka licznika po „g.”:

Nazwa pliku z pełnej ścieżki

Inną bardzo powszechną sytuacją jest wyodrębnienie nazwy pliku z pełnej ścieżki. Pomoże tu proste wyrażenie regularne formularza:

Sztuczka polega na tym, że wyszukiwanie tak naprawdę przebiega w odwrotnym kierunku – od końca do początku, bo na końcu naszego szablonu jest $i szukamy wszystkiego przed nim, aż do pierwszego odwrotnego ukośnika od prawej. Ukośnik odwrotny jest zmieniany, tak jak kropka w poprzednim przykładzie.

PS

„Pod koniec” Chcę wyjaśnić, że wszystkie powyższe elementy stanowią niewielką część wszystkich możliwości, jakie dają wyrażenia regularne. Istnieje wiele znaków specjalnych i zasad ich używania, a na ten temat napisano całe książki (polecam chociaż tę na początek). W pewnym sensie pisanie wyrażeń regularnych jest niemal sztuką. Prawie zawsze wymyślone wyrażenie regularne można ulepszyć lub uzupełnić, czyniąc je bardziej eleganckim lub zdolnym do pracy z szerszym zakresem danych wejściowych.

Aby analizować i analizować wyrażenia regularne innych osób lub debugować własne, istnieje kilka wygodnych usług online: RegEx101, RegExr i więcej

Niestety nie wszystkie funkcje klasycznych wyrażeń regularnych są obsługiwane w VBA (np. wyszukiwanie wsteczne czy klasy POSIX) i mogą działać z cyrylicą, ale myślę, że to, co jest, wystarczy za pierwszym razem, aby cię zadowolić.

Jeśli nie jesteś nowy w temacie i masz coś do udostępnienia, zostaw wyrażenia regularne przydatne podczas pracy w programie Excel w komentarzach poniżej. Jeden umysł jest dobry, ale dwa buty to para!

• Zastępowanie i czyszczenie tekstu za pomocą funkcji SUBSTITUTE
• Wyszukiwanie i wyróżnianie znaków łacińskich w tekście
• Wyszukaj najbliższy podobny tekst (Iwanow = Iwonow = Iwanow itp.)