=ПОСЛЕД(конец периода-начало периода + 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, где появились динамические массивы. В прошлых версиях функция не работает самостоятельно, но это обычно и не нужно: функция формирует диапазон, который потом используется в других функциях как аргумент. Например, для расчета суммы или среднего. То есть мы не выводим диапазон в ячейке листа, а используем для дальнейших вычислений.
В этом примере мы вычисляем среднее значение чисел из диапазона, заданного не обычной ссылкой, а функцией СМЕЩ (цветом выделены ячейки, соответствующие заданным параметрам функции: высота 12, ширина 1, начало в B2 — оно, начало, получено путем отступа на строку и на столбец от A1). Функция СМЕЩ тут выступает аргументом другой функции — СРЗНАЧ / AVERAGE, предоставляя ей диапазон для вычисления.
СМЕЩ с константами в качестве аргументов не имеет большого смысла — все примеры выше были приведены для демонстрации синтаксиса функции. На практике имеет смысл брать параметры из ячеек или вычислять с помощью других формул, чтобы была возможность формировать динамически изменяемый диапазон, не трогая саму формулу.
Например, в следующей функции СМЕЩ высота диапазона задается числом в ячейке F1.
И мы можем полученный диапазон использовать как аргумент другой функции, например СРЗНАЧ / AVERAGE.
Теперь достаточно поменять число в ячейке F1, чтобы тут же получить среднее за другой период, не трогая саму формулу.
Бывают ситуации, когда параметр для СМЕЩ задается не в ячейке и не меняется пользователем, а определяется расчетно.
Например, если нам нужно в течение года вычислять сумму нарастающего итога, чтобы сравнивать накопленный результат за прошедшие месяцы с аналогичным результатом прошлого года, можно отталкиваться от текущей даты и ее месяца, чтобы динамически задавать ширину диапазона.
Количество полных прошедших месяцев можно определить, например, так:
=МЕСЯЦ(СЕГОДНЯ())-1
(вычитаем из номера текущего месяца единицу).
А затем использовать полученное число в качестве параметра СМЕЩ, чтобы суммировать не все данные прошлого года, а столько месяцев, сколько прошло в текущем:
=СМЕЩ(B2;0;0;1;МЕСЯЦ(СЕГОДНЯ())-1)
И просуммировать полученный диапазон:
=СУММ(СМЕЩ(B2;0;0;1;МЕСЯЦ(СЕГОДНЯ())-1))
В такой ситуации стоит предусмотреть ситуацию, когда текущий месяц — январь. Это первый месяц, и часть нашей функции — МЕСЯЦ(СЕГОДНЯ())-1 — будет возвращать ноль, а вся конструкция — ошибку. Можно перехватить эту ситуацию с помощью функции ЕСЛИ и в случае января возвращать сумму за весь год:
=ЕСЛИ(МЕСЯЦ(СЕГОДНЯ())=1;
СУММ(B2:M2);
СУММ(СМЕЩ(B2;0;0;1;МЕСЯЦ(СЕГОДНЯ())-1)))
Функция LET
Файлы с примерами:
Функция LET.xlsx
Функция LET + ВПР.xlsx
В ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз, пригодится новая функция LET (Excel 2021 или Microsoft 365).
Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее.
Давайте посмотрим на совсем простой пример: зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET.
В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a.
Конечно, на практике для таких простых выражений функция LET не нужна. Но если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз, или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет.