В Excel есть базовые функции для работы с текстом — например, СЧЁТЕСЛИ посчитает количество ячеек, в которых есть определенное слово; ЛЕВСИМВ выделит несколько символов в начале строки. Но эти функции вряд ли помогут, если текст сильно отличается — например, в искомых ячейках нет общего корня, или количество символов везде разное. Зато такие задачи можно решить при помощи регулярных выражений, и в «Гугл-таблицах» есть функции, которые с ними работают.
Обычно функции Excel и «Гугл-таблиц» идентичны, но не в этот раз: материал из этого урока вы сможете использовать только в «таблицах».
Что такое регулярные выражения?
Это шаблоны, которые позволяют нам искать составленный по ним текст. Например, почтовый адрес состоит из латинских букв и цифр, символа @, ещё букв или цифр, точки, и, как правило, двух-трёх букв. С помощью регулярного выражения мы можем записать это так:
[A-z0-9]+@[A-z0-9]+\.[A-z]{2,3}
В этой записи есть два вида символов. Одни указывают на то, какие символы могут встречаться в тексте, а другие — сколько раз эти символы могут повторяться.
Для демонстрации принципов работы регулярных выражений мы будем использовать сайт regex101.com. Интерфейс сайта прост: в верхнем поле для ввода нужно написать регулярное выражение, а в нижнем — текст, для которого вы будете это выражение применять.
Фрагменты текста, соответствующие регулярному выражению, подсвечиваются, а в строке, где написано REGULAR EXPRESSION, будет написано, сколько таких совпадений есть в тексте.
Как обозначать символы
Разберем основы регулярных выражений на примере разных форм слова «яблоко». Например, таких:
яблоко
яблоки
яблоками
яблока
яблок
Самый простой случай — это когда мы в качестве регулярного выражения используем сам текст, который хотим найти. Например, регулярное выражение «яблоко» найдёт текст «яблоко».
Если мы хотим указать, что на определенном месте может встретиться один из символов, то мы должны использовать квадратные скобки.
Регулярное выражение «яблок[ои]» найдёт как слово яблоко, так и слово яблоки. Символы, из которых мы будем выбирать, пишутся в скобках друг за другом без пробелов. Если на определённом месте могут стоять буквы а, я или ю, то мы запишем: [аяю]
В скобках можно указывать диапазон символов — это удобно для алфавитов. Например, все русские буквы, кроме ё — это [А-я]. Маленькие буквы — [а-я], большие — [А-Я].
С буквой ё есть проблемы, потому что ее код в кодировке стоит особняком от остальных русских букв. Если вы хотите захватить и букву ё, надо написать такое выражение: [А-яЁё]. Соответственно, все латинские буквы — это [A-z], все цифры — [0-9].
Если вам нужен любой символ кроме тех, которые указаны в скобках, то первым символом в скобках должен быть ^. Например, выражение яблок[^о] не будет искать слово яблоко.
Также есть специальные символы, которые обозначают сразу какую-то группу символов. Точка — это вообще любой символ, \d — это цифра, \s — это любой пробельный символ.
Как обозначать количество повторений
Теперь рассмотрим, как в регулярных выражениях обозначать количество повторений.
Знак вопроса после символа говорит нам о том, что символ может встретиться или не встретиться в регулярном выражении (1 или 0 раз). Плюс — что он встречается хотя бы раз. Звёздочка — символ может встретиться любое количество раз или не встретиться вовсе.
Также можно задать конкретное количество повторений — в фигурных скобках. “яблок[а-я]{2}” — буква должна встретиться ровно 2 раза, “яблок[а-я]{0,2}” — от 0 до 2 раз, “яблок[а-я]{2,}” — от 2 и более раз.
Все специальные символы, которые мы упомянули, — это, например, скобки, знак вопроса, точка, должны экранироваться, если вы хотите их найти, а не использовать их функцию. Экранировать — это значит ставить перед символом обратный слеш. Так, чтобы найти выражение, в котором есть именно точка (а не любой символ), нужно писать “\.”
Иногда регулярные выражения применяют в нетривиальных задачах, и там нужно изрядно поломать голову, каким шаблоном можно описать группу текстовых строк. На этом уроке мы не будем залезать в «дебри», тем более, что функционал регулярных выражений в «Гугл-таблицах» несколько урезан. Мы собрали маленькую шпаргалку по регулярным выражениям, которой вы можете пользоваться при решении задач. А если вы хотите углубиться в текстовый анализ, используйте лучше Python: у «Мастерской» выходил урок об этом.
Функции регулярных выражений в «Гугл-таблицах»
В «Гугл-таблицах» есть три функции для работы с регулярными выражениями. Мы освоим их на примерах из рабочей тетради к этому уроку.
На первом листе даны названия мётел из «Гарри Поттера». Предположим, мы хотим найти только те из них, в которых упомянут год выпуска. В этом нам поможет функция REGEXMATCH — она проверяет, есть ли в ячейке таблицы часть строки, которая удовлетворяет регулярному выражению.
Синтаксис у неё такой: первый аргумент функции — это ячейка с текстом (или сам текст в кавычках), второй — регулярное выражение, которое мы будем искать. Мы хотим найти год выпуска, для этого подойдет выражение “[0-9]{4}”.
Функция REGEXEXTRACT работает аналогичным образом, только выделяет из ячейки первую подстроку, которая соответствует регулярному выражению. Если бы в ячейке год был написан несколько раз, то мы смогли бы выделить только первый из них.
В последней ячейке вместо года выпуска появится ошибка — она скажет нам о том, что года выпуска в изначальных данных нет.
Теперь вообразим себя цензорами, и удалим из метел годы выпуска. Нам понадобится функция REGEXREPLACE. Она позволяет заменять регулярные выражения на любой набор символов или удалять их. К привычным двум аргументам добавится третий — строка, на которую мы будем заменять регулярное выражение.
Аналогичным образом работает «поиск и замена» в гугл-таблицах, если поставить галочку на пункте «регулярные выражения».
Чистим датасет ООН
Давайте теперь перейдём к реальным задачам. На сайте ООН есть данные о том, сколько людей в разные годы страдали ожирением. На втором листе в каждой ячейке написан не только процент, который получили исследователи, но и доверительный интервал — это значит, что в значении может быть погрешность, и в реальности оно может быть в пределах этого интервала.
Но пока в ячейках нет конкретного числа, работать мы с ними не сможем. Исправим это при помощи регулярных выражений и функции «поиск и замена» (Ctrl + F / Cmd + F на Mac, затем три точки в появившемся окошке). Наша задача — убрать числа в квадратных скобках.
В новом окне ставим галочку на «Поиск с использованием регулярных выражений», в окошке поиска пишем само регулярное выражение: “ \[.*\]”, а в окошке замены — ничего (мы просто удалим эту часть строки).
Разберем регулярное выражение посимвольно. Сначала идет пробел, затем экранированная открывающая квадратная скобка (если бы мы не поставили слэш, то скобка не нашлась бы — это служебный символ), затем — точка (любой символ), звездочка (любое количество символов — мы не знаем, сколько точно цифр в скобках), затем — экранированная закрывающая квадратная скобка.
Если вы работаете с числами в русской локали, то перед подсчетами нужно еще заменить десятичные точки на запятые. Это тоже можно сделать через «поиск и замену»
Переводим декларации в машиночитаемую форму
На третьем листе представлены декларации сотрудников Ростеха, которые выложены на сайте ведомства. В первой колонке у нас смешаны разные сущности — тут написано и имя чиновника, и каким членам семьи принадлежит имущество. Если мы захотим, например, автоматическим путём посчитать, сколько площади недвижимости у каждой семьи, то нужно в каждой строчке написать фамилию семьи чиновника, которой принадлежит это имущество. Решить эту задачу можно также при помощи регулярных выражений.
Сначала очистим датасет от лишних строк. В исходном файле есть пустые строки, помеченные синим цветом, и мы можем отфильтровать их. Выделим один любой столбец (для этого нужно нажать на его название — например, C), и включим фильтр («Данные» —> «Создать фильтр»). Нажмем на иконку фильтра, выберем пункт «Фильтровать по цвету», цвет заливки — белый.
Скопируем отфильтрованную таблицу на новый лист, и заодно избавимся от объединенных ячеек. Вставим ее через правый клик мыши, «Специальная вставка» —> «Только значения». Эту операцию также можно проделать комбинацией клавиш Ctrl + Shift + V (или Command + Shift + V на Mac).
Определимся с нашей задачей. Мы хотим посчитать, сколько метров недвижимости у каждой семьи. Тогда мы можем смело отсечь часть таблицы после столбца G. Также приведем таблицу в каноничную форму — уберем первую пустую строчку, переместим заголовки столбцов E, F и G в первую строчку, и удалим вторую пустую строчку. В результате получится, что у нас одна заголовочная строчка, и она — первая.
Теперь продолжим с фамилиями. Наша задача — написать их в каждой строчке, которая к ним относится. Создадим новый столбец, в котором будем колдовать с регулярными выражениями. Наведитесь на столбец B, кликните правой кнопкой мыши, затем «Вставить столбец справа».
Во второй строчке нового столбца напишем формулу, которая проверит, что в соседнем столбце находится имя чиновника. Формула может быть такой:
=REGEXMATCH(B2;"[А-Я][а-я]+ [А-Я][а-я]+ [А-Я][а-я]+")
Модифицируем эту формулу. Если в ячейке находится имя чиновника, то извлечем его. В противном случае — пока ничего не будем извлекать (пустые кавычки).
=IF(REGEXMATCH(B2;"[А-Я][а-я]+ [А-Я][а-я]+ [А-Я][а-я]+");REGEXEXTRACT(B2;"[А-Я][а-я]+ [А-Я][а-я]+ [А-Я][а-я]+");"")
Последний штрих — вместо пустых кавычек укажем значение ячейки, которое находится на строку выше. Если в соседнем столбце указано имя чиновника, то функция заберет это имя. Если нет — продублирует то имя, которое было написано выше.
=IF(REGEXMATCH(B2;"[А-Я][а-я]+ [А-Я][а-я]+ [А-Я][а-я]+");REGEXEXTRACT(B2;"[А-Я][а-я]+ [А-Я][а-я]+ [А-Я][а-я]+");C1)
Получившуюся формулу растягиваем автозаполнением до конца таблицы.
Теперь мы можем составить сводную таблицу, чтобы посчитать сумму площадей недвижимости у чиновников. Про такие таблицы у «Мастерской» тоже был отдельный урок, здесь мы не будем подробно останавливаться на расчетах.
На этом все. Если у вас возникли вопросы по уроку — пишите в наш чат в Telegram.