Магия таблиц. 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, где появились динамические массивы. В прошлых версиях функция не работает самостоятельно, но это обычно и не нужно: функция формирует диапазон, который потом используется в других функциях как аргумент. Например, для расчета суммы или среднего. То есть мы не выводим диапазон в ячейке листа, а используем для дальнейших вычислений.