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