Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
Допустим, мы хотим сформировать их в один столбец, тогда числом строк здесь будет продолжительность периода (конец периода минус начало периода и плюс один день, если мы хотим включать последнюю дату периода), столбец будет один, шаг — 1 (один день). Начало последовательности — это первая дата периода:
=ПОСЛЕД(конец периода-начало периода + 1; 1; начало периода; 1)
Все даты текущего месяца
Допустим, нам нужны все даты текущего месяца в строку. Понадобится такая конструкция:
=ПОСЛЕД(1 строка; число дней в месяце; дата начала месяца; шаг=1)
Первый день текущего месяца можно получить так (текущий год + текущий месяц + первое число):
=ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)
А количество дней в месяце — так:
=ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0))
Функция КОНМЕСЯЦА / EOMONTH возвращает последнюю дату месяца, а функция ДЕНЬ / DAY возвращает количество дней у этой даты.
Остается соединить это в одну конструкцию:
=ПОСЛЕД(1; ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0));ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1);1)
Новый тип ссылок # (ссылка на ячейку с формулой массива)
Файл с примером: Справочник магазинов — ссылка с решеткой.xlsxВместе с динамическими массивами в Excel появился и новый тип ссылки — на ячейку, в которой формула возвращает динамический массив, то есть массив, размер которого может меняться.
Например, вам нужно сделать выпадающий список с уникальными значениями из таблицы — допустим, чтобы выбирать город из списка магазинов «ЛеМура» и формировать ссылку на отправку писем только в магазины этого города.
Мы можем получить список всех городов (без дубликатов) с помощью функции УНИК / UNIQUE. Но его размеры могут измениться в будущем. То есть сослаться на диапазон G2:G18 в проверке данных или формуле нельзя: в будущем какой-то город может исчезнуть из таблицы или появятся новые, и тогда функция будет возвращать результат, занимающий меньший или больший диапазон.
Для таких случаев и появился новый тип ссылок — с решеткой после адреса ячейки. Например, A2# означает «ссылка на все значения, которые будет возвращать формула массива, введенная в ячейке A2». А уж каким будет размер этого массива, мы заранее не знаем.
Получается, что, если мы хотим выпадающий список с уникальными значениями, можно сослаться на ячейку с функцией УНИК из проверки данных.
Теперь, когда в таблице будут удаляться или добавляться новые города, изменится результат, возвращаемый функцией УНИК в ячейке G2, а значит, и в выпадающем списке в ячейке с проверкой данных будет актуальный список.
Отправка писем по отфильтрованным адресам формулой
Давайте продолжим работать с предыдущим примером и задействуем еще одну из новых функций (ФИЛЬТР / FILTER), чтобы решить следующую задачу: формировать ссылку на отправку писем во все магазины выбранного в выпадающем списке города.
С помощью функции ФИЛЬТР мы можем получить список адресов выбранного в ячейке J2 города.
А дальше необходимо склеить это в одну текстовую строку, добавив между адресами запятые, чтобы потом использовать это в обработчике "mailto: ", формирующем ссылку на отправку писем. Склеить адреса, возвращаемые функцией ФИЛЬТР, можно с помощью функции ОБЪЕДИНИТЬ / TEXTJOIN. Ее первый аргумент — разделитель (в нашем случае запятая), второй — нужно ли пропускать пустые ячейки (в нашем случае пустых ячеек быть не должно, но можно все равно включить эту опцию — аргумент будет равен ИСТИНА / TRUE), третий — значения, которые нужно объединить (у нас это функция ФИЛЬТР, возвращающая список адресов):
=ОБЪЕДИНИТЬ(","; ИСТИНА; ФИЛЬТР(…))
Теперь список будет не в виде массива (в нескольких ячейках), а одним текстовым значением.
Остается добавить обработчик "mailto: " к списку и превратить все в гиперссылку. Второй аргумент функции ГИПЕРССЫЛКА / HYPERLINK — как выглядит ссылка в ячейке:
=ГИПЕРССЫЛКА("mailto: " & ОБЪЕДИНИТЬ(","; ИСТИНА; ФИЛЬТР(…)); "Рассылка")
После перехода по ссылке, сформированной такой формулой, в вашем почтовом клиенте будет сформировано письмо с отобранными функцией ФИЛЬТР адресами в поле «Кому». Можно пойти дальше и добавить тему (?subject= после списка адресатов) и тело письма (&body=).
Функция СМЕЩ / OFFSET
Файл с примерами: СМЕЩ.xlsxФункция СМЕЩ / OFFSET позволяет формировать ссылку на диапазон, описываемый не точным адресом (A1:B10, например), а параметрами — первой ячейкой диапазона, отступом от нее, высотой и шириной.
Вообще говоря, любой диапазон в Excel можно задать тремя параметрами: первой (левой верхней) ячейкой, высотой и шириной. Например, упомянутый A1:B10 — это диапазон с первой ячейкой A1, шириной в 2 столбца и высотой в 10 строк. Зачем еще и отступ в функции СМЕЩ? Чтобы была возможность менять не только ширину и высоту столбца, но и первую ячейку диапазона (и все это — не меняя саму формулу, а только параметры функции СМЕЩ).
Функция СМЕЩ / OFFSET не новая — она есть во всех версиях Excel и в Google Таблицах.
Зачем вообще все это нужно, когда можно просто ссылаться на диапазон в привычной манере? Дело в том, что параметры функции СМЕЩ можно менять, например задавая их в отдельных ячейках или вычисляя с помощью других функций (например, задавая диапазон равным числу прошедших в текущем году месяцев, чтобы не менять формулу каждый месяц).
Аргументы функции СМЕЩ:
=СМЕЩ (ссылка; строка;столбец; высота;ширина)
Рассмотрим несколько примеров, как определенные параметры СМЕЩ соответствуют диапазону на листе:
=СМЕЩ(B1;0;0;9;1)
• Начало — в ячейке B1.
• Отступ от нее:
ноль по строкам;
ноль по столбцам.
• Высота — 9.
• Ширина — 1.
=СМЕЩ(A1;1;1;9;2)
• Начало — в ячейке A1.
• Отступ от нее:
вниз на 1 строку;
вправо на 1 столбец.
• Высота — 9.
• Ширина — 2.
=СМЕЩ(A1;1;1;12;1)
• Начало — в ячейке A1.
• Отступ от нее:
вниз на 1 строку;
вправо на 1 столбец.
• Высота — 12.
• Ширина — 1.
СМЕЩ как аргумент других функций
СМЕЩ будет возвращать массив только в Microsoft 365 / Excel 2021, где появились динамические массивы. В прошлых версиях функция не работает самостоятельно, но это обычно и не нужно: функция формирует диапазон, который потом используется в других функциях как аргумент. Например, для расчета суммы или среднего. То есть мы не выводим диапазон в ячейке листа, а используем для дальнейших вычислений.
