Резюме
Фильтрация и поиск в Excel могут показаться игрой на удачу. Функции Regex изменяют это. Теперь я могу точно определить, что мне нужно — сложные шаблоны, частичные совпадения или извлечение структурированных данных — без особых усилий.
Что такое Регулярные выражения?
Регулярные выражения (Regex) — это шаблон, используемый для поиска в строках текста или последовательностях символов. Задумывались ли вы когда-нибудь, как сайты определяют, что введенный вами адрес электронной почты на странице входа недействителен? Это пример использования регулярного выражения для проверки адреса электронной почты.
Регулярные выражения присутствуют не только в Excel — их можно найти во многих текстовых редакторах, языках программирования, командных утилитах, средах разработки и даже в конкуренте Excel — Google Таблицах.
Как правило, использование Регулярных выражений может показаться сложным, и так оно и есть, если вы хотите максимально раскрыть их потенциал, однако не нужно быть программистом, чтобы эффективно ими пользоваться. В некоторых случаях достаточно знать всего несколько символов и основных шаблонов. Я постараюсь объяснить это максимально просто, чтобы вы могли сразу начать их использовать.
Вот символы, которые мы будем использовать в этом руководстве:
Символ
Описание
—
Указывает диапазон символов в скобках.
^
Соответствует началу строки.
$
Соответствует концу строки.
.
Соответствует любому символу, кроме символа новой строки.
*
Соответствует 0 или более предыдущих символов.
+
Соответствует 1 или более предыдущим символам.
()
Группирует совпадающие символы в одно целое.
[]
Соответствует любому из символов, находящимся в скобках.
[^]
Соответствует любому символу, который не находится в скобках.
{n}
Соответствует точно n вхождениям предыдущего символа.
{n,}
Соответствует n или более вхождениям предыдущего символа.
Простые шаблоны регулярных выражений, которые можно создать с помощью этих символов, включают:
Шаблон Regex
Описание
[0-9]
Соответствует одной цифре от 0 до 9.
[a-zA-Z0-9]
Составной диапазон, который соответствует одному символу от нижнего a до z, от верхнего A до Z и от 0 до 9.
^pro
Соответствует любой строке, начинающейся с pro.
[^$]
Соответствует любому символу, который не является $.
(con)
Группа по шаблону con.
a{3,}
Соответствует 3 или более вхождениям буквы a (например, a, aa или aaa).
Функции Regex — это предопределенные формулы Excel, которые можно использовать для определения шаблона для поиска и манипуляции строками текста. В данный момент существует три функции regex, и мы рассмотрим, как пользоваться каждой из них отдельно и в сочетании с другими функциями.
Поиск Шаблонов
Первая функция, которую мы рассмотрим, это REGEXTEST. Она принимает текстовую строку, по которой вы хотите осуществить поиск, и шаблон regex, используя который, выполняет поиск совпадений. Функция вернет либо True, либо False.
Синтаксис функции REGEXTEST выглядит следующим образом:
Первые два параметра, string_to_search и regex_pattern_to_use, являются очевидными. Параметр [case_sensitivity] является необязательным — все, что помещается в квадратные скобки в описании синтаксиса Excel, является опциональным — и обозначает, хотите ли вы, чтобы поиск был регистронезависимым (0) или регистрозависимым (1). По умолчанию используется регистрозависимый поиск.
В нашем примере мы будем использовать REGEXTEST, чтобы проверить, ввел ли пользователь действительный адрес электронной почты, используя следующую формулу:
Здесь мы проверяем ячейку B3, чтобы увидеть, содержит ли она адрес электронной почты, используя шаблон regex ниже:
Если я вставлю формулу в ячейку C3 и введу john.doe@example.com в ячейку B3, она вернет True, поскольку это соответствует шаблону адреса электронной почты.
Извлечение Дополнительных Данных С Помощью Regex
Следующая функция, которую мы рассмотрим, это REGEXEXTRACT. Она возвращает подстроку (часть строки), которая соответствует предоставленному шаблону regex.
Синтаксис функции REGEXEXTRACT выглядит следующим образом:
Продолжая с примером с электронной почтой, давайте добавим формулу в ячейку B4, чтобы извлечь имя пользователя из адреса электронной почты.
Вот как будет выглядеть формула:
В этой формуле мы извлекаем все, что находится перед символом @ в введенном адресе электронной почты в B3.
Поиск и Замена с Помощью Regex
Последняя функция regex, которую мы рассмотрим, это REGEXREPLACE. Эта функция схожа с функцией REPLACE в Excel, но также поддерживает regex. Она принимает текстовую строку, которую вы хотите изменить, и проверяет, существует ли подстрока, соответствующая заданному шаблону regex. Если она находит такое совпадение, то заменяет его на предоставленную строку замены.
Синтаксис функции REGEXREPLACE выглядит следующим образом:
Вот важные параметры, на которые следует обратить внимание в этой функции:
Вот пример использования функции для замены части имени пользователя адреса электронной почты на другую текстовую строку:
При этом значение ячейки B3 — это john.doe@example.com, и после того, как мы введем формулу выше в ячейку C3, она вернет jane.doe@example.com.
Совмещение Regex с Другими Функциями
Вы также можете комбинировать функции regex с другими функциями в Excel. Например, вы можете объединить функцию REGEXTEST с оператором IF в Excel, чтобы отобразить соответствующее сообщение в зависимости от результата.
Вот пример формулы:
Эта формула использует оператор IF, чтобы проверить, действителен ли адрес электронной почты, введенный в ячейку B6, а затем отображает Это действительный адрес электронной почты!, если это TRUE, или Адрес электронной почты недействителен!, если это FALSE. Кроме того, вы можете сочетать это с функцией FIND для быстрого поиска данных в Excel.
Это не является исчерпывающим руководством по регулярным выражениям — для этого потребовалось бы несколько статей. Тем не менее, это хороший способ начать использовать RegEx в Excel. Возможности применения ограничены только вашей фантазией.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете читать наши материалы в:
- Telegram: https://t.me/gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru
