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

символа. Например, 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: " & ссылка на ячейку с имейлом

Такая формула «склеит» обработчик (указывающий, что это ссылка на создание письма электронной почты) и адрес из ячейки. Чтобы сделать ее ссылкой, нужно «обернуть» эту конструкцию в функцию ГИПЕРССЫЛКА. А во втором аргументе этой функции можно указать любой текст, который мы хотим видеть на месте ссылки в ячейке:

=ГИПЕРССЫЛКА("mailto: " & ссылка на ячейку с имейлом; "Письмо")

Смотрите также «Отправка писем по отфильтрованным адресам формулой» в главе про динамические массивы — там мы используем функцию ГИПЕРССЫЛКА, чтобы формировать ссылку на отправку писем сразу по группе адресов, отобранных функцией ФИЛЬТР.

Функция ПРЕОБР / CONVERT

Файл с примером: ПРЕОБР.xlsx

Эта функция преобразует значение (первый аргумент — число) из одной единицы измерения (второй аргумент — текст) в другую (третий аргумент — текст):

=ПРЕОБР (значение; исходная единица измерения; конечная единица измерения)

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

Например, "km" (километр), "byte" (байт), "uk_pt" (английская пинта), "ha" (гектар).

Пример применения в Google Таблицах. В Excel функция работает аналогично

Можно использовать префиксы "Т" (тера), "G" (гига), "M" (мега) и подобные. Например, "Tbyte" — терабайт.

Единиц измерения много. Расстояние, время, вес и масса, давление, сила, энергия, мощность, температура, объем, скорость, площадь, биты и байты.

Полный список единиц можно посмотреть в справке — и в Google Таблицах, и в Excel.

Работа с датами

Файл с примерами: Даты.xlsx

ДАТЫ И ВРЕМЯ В EXCEL И GOOGLE ТАБЛИЦАХ

За любой датой в Excel скрывается целое число. Датой его делает форматирование (первая дата в Excel — это 1 января 1900 года, и ей соответствует порядковый номер 1). Благодаря этому возможны вычисления с датами: один день — единица, а значит, можно прибавить к дате 7 дней и получить дату через неделю. Или вычесть из одной даты другую и получить разницу в днях между ними.

Аналогично со временем: если одна единица — это день, то часть единицы (число от 0 до 1) — время, то есть часть дня. Например, 0,5 = 12:00 (половина дня), а 0,75 = 18:00.

Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:

ДД.ММ.ГГГГ (например, 01.06.2022);

ДД/ММ/ГГГГ (например, 01/06/2022);

ГГГГ-ММ-ДД (например, 2022-06-01);

ГГГГ/ММ/ДД (например, 2022/06/01);

ДД название месяца ГГ(ГГ) (например, 1 июнь 22);

ДД