Jak korzystać z funkcji Regex programu Excel, aby zwiększyć skuteczność wyszukiwania

Filtrowanie i wyszukiwanie w programie Excel nie jest prostym zadaniem. Funkcje Regex to zmienią. Teraz możesz dokładnie określić, czego potrzebujesz — złożonych wzorców, częściowych dopasowań lub wyodrębniania ustrukturyzowanych danych — bez żadnego wysiłku.

Spis treści

• Czym jest Regex?
• Wyszukaj wzory
• Dodatkowe dane przy użyciu Regex
• Znajdź i zamień za pomocą wyrażenia regularnego
• Łączenie Regex z innymi funkcjami

Czym jest Regex?

Regex to rodzaj wzorca używanego do wyszukiwania pasujących elementów w tekście lub ciągu znaków. Czy zastanawiałeś się kiedyś, w jaki sposób strony internetowe mogą poinformować Cię, że szablon wiadomości e-mail, który podałeś na stronie logowania, jest nieprawidłowy? Poniżej znajduje się przykład wzorca wyrażenia regularnego wykorzystującego podpis e-mail w akcji.

Wyrażenia regularne nie są unikalne dla programu Excel — są dostępne w wielu edytorach tekstu, językach programowania, narzędziach wiersza poleceń, środowiskach IDE, a nawet w konkurencyjnym dla programu Excel programie Arkusze Google .

Wyrażenia regularne mogą wydawać się skomplikowane, ale takie są, jeśli chcesz wykorzystać ich pełen potencjał. Nie musisz jednak być programistą, żeby używać ich efektywnie. W niektórych przypadkach wystarczy znajomość kilku podstawowych symboli i wzorów. Niniejszy przewodnik zostanie napisany w możliwie najprostszy sposób, aby ułatwić Ci rozpoczęcie korzystania z niego.

W tym przewodniku będziemy używać następujących symboli:

Proste wzorce wyrażeń regularnych, które można utworzyć przy użyciu tych symboli, obejmują:

Funkcje Regex to predefiniowane formuły programu Excel, które można wykorzystać do zdefiniowania wzorca wyszukiwania i manipulowania ciągami tekstowymi. Obecnie istnieją 3 funkcje regex. Pokażemy jak ich używać oddzielnie i w połączeniu z innymi funkcjami.

Wyszukaj wzory

Pierwszą funkcją, którą omówimy jest REGEXTEST. Funkcja ta przyjmuje ciąg tekstowy, który chcesz wykorzystać do wyszukiwania, oraz wzorzec wyrażenia regularnego, a następnie używa drugiego wzorca, aby znaleźć dopasowania w pierwszym wzorcu. Funkcja zwróci wartość True (Prawda) lub False (Fałsz).

Składnia funkcji REGEXTEST jest następująca:

REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

Pierwsze dwa parametry, string_to_search i regex_pattern_to_use, nie wymagają wyjaśnień. Parametr [case_sensitivity] jest opcjonalny — wszystko ujęte w nawiasy kwadratowe w przypadku składni programu Excel jest opcjonalne — i wskazuje, czy wyszukiwanie ma uwzględniać wielkość liter (0), czy nie (1). Domyślnie wielkość liter ma znaczenie.

W przykładzie zostanie użyta formuła REGEXTEST, aby sprawdzić, czy użytkownik podał prawidłowy adres e-mail:

REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

Tutaj przeszukujemy komórkę B3, aby sprawdzić, czy zawiera ona adres e-mail, korzystając z poniższego wzorca wyrażenia regularnego:

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

Jeśli wpiszesz formułę w komórce C3 i wpiszesz john.doe@example.com w komórce B3, formuła zwróci wartość True, ponieważ pasuje ona do podpisu e-mail.

Dodatkowe dane przy użyciu Regex

Przyjrzyjmy się teraz funkcji REXEXEXTRACT. Funkcja ta zwraca podciąg (część ciągu) odpowiadający podanemu wzorcowi wyrażenia regularnego.

Składnia funkcji REXEXEXTRACT jest następująca:

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

Kontynuując przykład z wiadomością e-mail, dodajmy formułę do komórki B4, aby wyodrębnić nazwę użytkownika z adresu e-mail.

Wzór wyglądałby następująco:

=REGEXEXTRACT(B3, "([^@]+)")

W tej formule wyodrębniamy wszystko, co znajduje się przed symbolem @ w adresie e-mail wprowadzonym w komórce B3.

Znajdź i zamień za pomocą wyrażenia regularnego

Ostatnią funkcją wyrażenia regularnego, którą omówimy jest REGEXREPLACE. Funkcja ta jest podobna do funkcji REPLACE programu Excel, ale obsługuje także wyrażenia regularne. Przyjmuje ciąg tekstowy, który chcesz zmodyfikować i sprawdza, czy jakikolwiek podciąg pasuje do zdefiniowanego wzorca wyrażenia regularnego. Jeśli zostanie znaleziony, zastępuje ten ciąg podanym ciągiem zastępczym.

Składnia funkcji REGEXREPLACE jest następująca:

REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

Oto ważne parametry, na które należy zwrócić uwagę w tej funkcji:

• string_to_modify : Ciąg tekstowy, który chcesz zmodyfikować.
• replacement_string : Ciąg, którym należy zastąpić podciąg.
• number_of_occurrences : Dokładna liczba wystąpień, które chcesz zastąpić.

Oto przykład użycia funkcji w celu zastąpienia części nazwy użytkownika w wiadomości e-mail innym ciągiem tekstowym:

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

Wartość B3 to john.doe@example.com i po wprowadzeniu powyższej formuły do ​​komórki C3 zwrócona zostanie wartość jane.doe@example.com.

Łączenie Regex z innymi funkcjami

Funkcje wyrażeń regularnych można również łączyć z innymi funkcjami w programie Excel. Można na przykład połączyć funkcję REGEXTEST z poleceniem IF programu Excel i wyświetlić odpowiednie komunikaty na podstawie wyników.

Oto przykładowy wzór:

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

Ta formuła używa instrukcji JEŻELI, aby sprawdzić, czy adres e-mail wprowadzony w komórce B6 jest prawidłowy, a następnie wyświetla komunikat: To jest prawidłowy adres e-mail! jeśli TRUE lub adres e-mail jest nieprawidłowy! jeśli FAŁSZ . Dodatkowo możesz połączyć tę formułę z funkcją ZNAJDŹ, aby szybko znaleźć dane w programie Excel.

To dobry sposób na rozpoczęcie korzystania z wyrażeń regularnych w programie Excel. Możliwości i przypadki użycia ograniczone są jedynie Twoją wyobraźnią.