Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Работа с текстом
Дальше: Ссылки. Функция ГИПЕРССЫЛКА / HYPERLINK
Регулярные выражения
Google Таблица с примерами: Регулярные выражения
Регулярные выражения (Regular Expressions или RegExp) позволяют решать довольно сложные задачи по поиску, извлечению и замене текстовых строк. Они нужны для решения задач с динамическими текстовыми строками.
Для тривиальных задач с фиксированным текстом (например, когда нужно просто найти/заменить определенное слово) подходят и обычные текстовые функции Excel или Google Таблиц (как НАЙТИ / FIND или ПОДСТАВИТЬ / SUBSTITUTE) и окно «Найти и заменить».
Если нужно вырезать первые шесть символов (допустим, если в полном почтовом адресе всегда указан индекс, всегда в начале и всегда из шести символов), подойдет ЛЕВСИМВ / LEFT, которая извлекает заданное количество символов из начала текстовой строки в ячейке.
Кот Лемур, автор многих примеров в этой книге, изучает книгу «Регулярные выражения» Джеффри Фридла. Помимо нее он рекомендует книгу Бена Форта «Изучаем регулярные выражения»
А вот для более сложных задач обычных функций уже может не хватить:
— извлечь дату из текстового описания транзакции;
— извлечь индекс из полного почтового адреса;
— проверить, есть ли в ячейке артикул товара;
— или извлечь этот самый артикул;
— найти товары с двумя или тремя портами HDMI в описании (2xHDMI или 3xHDMI), но не одним или более чем тремя (не 1xHDMI или что-то еще).
И тут на помощь могут прийти «регулярки». С помощью языка регулярных выражений можно задать паттерн, маску, шаблон — символьную строку, которой будут соответствовать определенные текстовые значения.
Регулярные выражения поддерживаются разными языками программирования (например, JavaScript, Java, Perl, PHP) и разными инструментальными средствами (например, Word или Google Таблицы). К сожалению, в Excel нет встроенных функций для работы с «регулярками», но можно создать пользовательскую функцию на VBA. А вот в Google Таблицах такие функции есть «из коробки». Их три (названия у этих функций только на английском):
• REGEXMATCH для проверки текста на соответствие регулярному выражению (возвращает ИСТИНА / TRUE или ЛОЖЬ / FALSE);
• REGEXREPLACE для замены части текста, соответствующей регулярному выражению, на другой текст;
• REGEXEXTRACT для извлечения фрагмента текста, который соответствует регулярному выражению.
Кроме того, в Google Таблицах можно фильтровать данные с условием, заданным в виде регулярного выражения, с помощью функции QUERY.
ЯЗЫК РЕГУЛЯРНЫХ ВЫРАЖЕНИЙ
Давайте рассмотрим основные элементы, из которых собираются шаблоны — регулярные выражения.
Здесь в примерах мы будем обозначать полужирным регулярные выражения, а полужирным курсивом — совпадения с ними в тексте.
Просто текст без всяких знаков — это вполне себе регулярное выражение, хоть и очень простое.
Лемур
Это совпадение с конкретным словом «Лемур» в тексте — именно в таком регистре (регулярные выражения чувствительны к регистру).
Привет, Лемур!
_____________________
. (точка) — любой символ. Например, «Ро. линг» — две конкретные буквы, далее любой символ, далее еще четыре конкретных буквы. Такое регулярное выражение будет соответствовать и фамилии «Роулинг», и другим вариантам написания с ошибкой (например, «Ровлинг»), и вообще любому варианту написания, где на третьей позиции будет любой символ.
Ро. линг
В некоторых ячейках была опечатка в фамилии Роулинг — она была указана как Ровлинг, а то и как Роуллинг.
«Роуллинг» нашему регулярному выражению не соответствует — на месте точки может быть только один символ.
Если вам нужно явным образом указать непосредственно символ точки в регулярном выражении, используйте обратную косую черту: \.
_____________________
[символы] — символы в квадратных скобках — это набор из нескольких символов, которые могут быть на соответствующей позиции в текстовой строке.
Например:
Ле[мд]ур — это один из вариантов «Лемур» или «Ледур», но не что-то иное.
Если кот вытворял что-то эдакое, мы могли в шутку пожурить его: "Лемур-Ледур", но быстро прощали любимого Ле Мура.
Можно использовать дефис, чтобы обозначать диапазоны символов. Например, [0–6] и [0123456] идентичны.
[^символы] — если перед набором символов добавить «крышечку» (^), то это будет отрицанием, то есть любым символом, кроме перечисленных в квадратных скобках.
_____________________
\d — любая цифра. Идентично выражению [0–9].
Регистр здесь имеет значение. \D — это уже любой символ, кроме цифры.
_____________________
\w — любая латинская буква в любом регистре, любая цифра или нижнее подчеркивание.
\W — любой символ, кроме перечисленных (то есть «не \w», не латинская буква, не цифра и не подчеркивание).
_____________________
\s — любой пробельный символ (непечатаемые символы: пробел, перевод строки, табуляция и другие).
\S — соответственно, любой не пробельный символ.
_____________________
| (вертикальная черта) — один из вариантов, «или — или». Например:
USD|EUR
Функция REGEXMATCH в Google Таблицах возвращает ИСТИНА / TRUE, если в ячейке (ссылка на нее — первый аргумент функции) есть текст, соответствующий регулярному выражению (указанному во втором аргументе этой функции)
_____________________
? (знак вопроса) — ничего или один указанный символ.
Например:
Ле\s?Мур
Такому выражению будут соответствовать и варианты с одним переносом строки, и с одним пробелом «Ле Мур», и слитное написание «ЛеМур». Варианты с несколькими пробелами не подойдут.
Слитное написание «Лемур» не будет соответствовать такому выражению — из-за регистра. Чтобы учесть варианты и «ЛеМур», и «Лемур», можно указать обе буквы в квадратных скобках, напомним: это означает один символ из набора.
_____________________
+ (плюс) — одно или несколько повторений символа.
Например, \s + — один или более пробелов.
Ле\s + Мур — будет соответствовать вариантам «Ле Мур», «Ле Мур» (много пробелов между словами), «Ле Мур» с переносом строки между словами, но не вариантам «ЛеМур» или «Лемур».
_____________________
* (звездочка) — ноль, одно или несколько повторений символа.
Если заменить в предыдущем примере плюс на звездочку, то такому регулярному выражению
Ле\s*Мур
будет соответствовать и вариант «ЛеМур» (но не «Лемур» — из-за регистра).
_____________________
{количество повторений} — в фигурных скобках можно задать точное число повторений символа. Например, \d{4} — четыре любые цифры.
Кроме того, можно задавать диапазон:
{1, 5} — от одного до пяти повторений;
{4,} — четыре и более повторений.
Например, Ро.{1,2}линг — это и Роулинг, и Ровлинг, и Ро линг, и Роуллинг. Но не Ролинг.
_____________________
^ («крышечка», или, как этот символ называет Лемур, карет) — начало текстовой строки.
Например, ^HP — “HP” в начале строки.
_____________________
(знак доллара) — конец текстовой строки.
Например, \d + $ — число в конце строки.
Извлекаем число в конце строки (функция REGEXEXTRACT в Google Таблицах не проверяет соответствие регулярному выражению, а извлекает соответствующее значение, если оно есть).
Там, где в конце строки не число, возвращается ошибка #N/A.
Без знака доллара (\d +) будет извлекаться первое число в тексте.
_____________________
(?i) — делает выражение нечувствительным к регистру.
Например, (?i)Лемур.
Это выражение будет соответствовать слову «Лемур» в любом регистре.
РЕГУЛЯРНЫЕ ВЫРАЖЕНИЯ В ОКНЕ ПОИСКА В GOOGLE ТАБЛИЦАХ
Выше мы обсуждали окно «Найти и заменить» — самое время напомнить, что в Google Таблицах в нем тоже можно использовать регулярные выражения! Для этого достаточно включить галочку «Поиск с использованием регулярных выражений» (Search using regular expressions).
ФИЛЬТРАЦИЯ ДАННЫХ С ПОМОЩЬЮ РЕГУЛЯРНЫХ ВЫРАЖЕНИЙ В ФУНКЦИИ QUERY GOOGLE ТАБЛИЦ
Чтобы фильтровать данные в Google Таблицах с помощью функции QUERY, используйте слово MATCHES в кляузе WHERE. После него через пробел следует регулярное выражение в апострофах. Все строки, в которых в указанном столбце будет встречаться регулярное выражение, будут возвращаться функцией QUERY:
=QUERY(диапазон; "WHERE столбец MATCHES 'регулярное выражение' ")
Например, если нужно извлечь все товары, в названиях которых встречается 2xHDMI или 3xHDMI, регулярное выражение будет выглядеть так:
. + [23]xHDMI. +
(любой текст + 2 или 3 + xHDMI + любой текст)
А функция целиком будет выглядеть так (в примере названия товаров находятся в столбце A):
=QUERY(!A: F;"Select * where A matches '. + [23]xHDMI.' ")
Назад: Работа с текстом
Дальше: Ссылки. Функция ГИПЕРССЫЛКА / HYPERLINK
