Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
Кроме того, можно задавать диапазон:
{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);
ДД
