Чтобы фильтровать данные в 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 скрывается целое число. Датой его делает форматирование (первая дата в 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);
ДД короткое обозначение месяца ГГ(ГГ) (например, 1 июн 2022).
Знать про это нужно, ведь, если вы видите числа там, где должны быть даты, дело может быть только в числовом форматировании: нужно поменять формат на «Дату» или любой другой формат даты и/или времени. И благодаря тому что «под капотом» даты — число, с ним можно проводить арифметические операции (об этом — через пару абзацев).
Если к дате и времени применить числовой формат, мы увидим соответствующие им числа.
Даты в формулах можно использовать:
• как константы, указывая в кавычках в одном из стандартных форматов ("01.01.2021", "01/01/2021", "2021-01-01", "ГГГГ/ММ/ДД");
• ссылаясь на ячейки, где даты хранятся, — уже без кавычек.
С датами можно производить операции вычитания и сложения:
• вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней);
• прибавить к дате число и получить дату, которая наступит через соответствующее количество дней.
У этих функций нет аргументов, потому что их результат ни от чего не зависит (сегодняшняя дата всегда остается таковой). Поэтому у них скобки для аргументов всегда остаются пустыми (но ввести их все равно необходимо, тем самым показав Excel, что мы подразумеваем здесь функцию, а не что-то другое, например текст или ссылки на ячейки):
=СЕГОДНЯ()
=ТДАТА()
Обе входят в число волатильных функций: это значит, что они пересчитываются при любом изменении в книге Excel или документе Google Таблиц.
Если вам нужно вставить текущую дату как значение, чтобы она не пересчитывалась и не менялась в будущем, воспользуйтесь сочетанием клавиш Ctrl +;(^ +;).
А для вставки текущего времени как значения — сочетанием Ctrl + Shift +:( +;).
Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст сотрудника), воспользуйтесь функцией РАЗНДАТ / DATEDIF. При ее вводе не будут отображаться аргументы, но не обращайте на это внимания — она работает во всех версиях приложения.
Вот ее синтаксис:
=РАЗНДАТ(дата_начала; дата_окончания; единица измерения)
Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.
Единица измерения задается в кавычках. Есть следующие возможные варианты:
• d — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
• m — число полных месяцев в периоде;
• y — число полных лет в периоде;
• md — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
• ym — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
• yd — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).
Соответственно, чтобы вычислить возраст человека на текущую дату, подойдет следующая формула:
=РАЗНДАТ(ссылка на ячейку с датой рождения; СЕГОДНЯ(); "y")
=DATEDIF(ссылка на ячейку с датой рождения; TODAY(); "y")
В Google Таблицах функция РАЗНДАТ / DATEDIF тоже есть — ее аргументы будут отображаться в подсказке при вводе функции.
Есть функции, извлекающие только один элемент даты. Это бывает удобно, чтобы создать отдельный столбец с номерами недель или месяцев, например, и по нему фильтровать/анализировать данные — в соответствующем разрезе.
Обратите внимание, что на выходе эти функции возвращают числа (кроме КОНМЕСЯЦА / EOMONTH), а не даты. Если вы хотите отображать в ячейке, например, только день недели или номер месяца __, но в значении этой ячейки хотите сохранить дату, то лучше воспользоваться числовыми форматами, чтобы оставить в ячейке значение, но отображать только отдельный параметр.