Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

* (звездочка) — ноль, одно или несколько повторений символа.

Если заменить в предыдущем примере плюс на звездочку, то такому регулярному выражению

Ле\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

Файл с примером: ГИПЕРССЫЛКА.xlsx

В Excel можно вставить ссылку на другой лист, на отправку электронной почты конкретному адресату, на сайт — для этого достаточно нажать Ctrl + K (это сочетание работает и во многих других приложениях) или щелкнуть правой кнопкой и выбрать в контекстном меню «Ссылка» (Link).

Это будет «фиксированная» ссылка, вставленная вручную. Конечно, таким образом можно вставить несколько ссылок и, например, создать в книге Excel оглавление (со ссылками на все листы).

Если листов много, вручную проделывать это для каждого листа будет мучительно, поэтому можно использовать макрос, который формирует оглавление на отдельном листе автоматически, — его код в конце книги в главе «Несколько слов о макросах».

Ну а если нужно «склеивать» ссылки из разных значений, которые хранятся в ячейках, пригодится функция ГИПЕРССЫЛКА / HYPERLINK.

Синтаксис функции:

• первый аргумент — собственно ссылка (это может быть и ссылка на ячейку со ссылкой, и ссылка, полученная с помощью формулы, например с помощью объединения нескольких текстовых строк);

• второй аргумент — как она будет отображаться в ячейке (он необязательный; если его пропустить, в ячейке будет отображаться сама ссылка).

Эта функция превращает текст в гиперссылку. Благодаря тому что это именно функция, можно использовать текстовые значения из разных ячеек для формирования большого количества ссылок с переменной частью, например ссылки на отправку письма каждому сотруднику из таблицы с имейлами.

Для создания такой ссылки нужно соединить две части: постоянную для всех ссылок — это обработчик «mailto:» и переменную — конкретные имейлы из ячеек таблицы:

="mailto: " & ссылка на ячейку с имейлом