Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
Примеры этой главы предназначены для того, чтобы дать вам отправную точку. Но они, конечно же, не исчерпывают всех возможностей использования обычных функций Excel в сочетании с новыми свойствами динамических массивов. Всякий раз, когда функция ожидает скаляр, передача ей массива приведет к подъему. И в итоге функция вернет диапазон значений.
Использование СЕГОДНЯ() и ПОСЛЕД() для календаря
Допустим, вы используете СЕГОДНЯ() в качестве третьего аргумента функции ПОСЛЕД(). Тогда формула =ПОСЛЕД(6;7;СЕГОДНЯ()) в ячейке А4 вернет массив из 6 строк и 7 столбцов календаря, начинающегося сегодня:
Рис. 74. Использование СЕГОДНЯ() и ПОСЛЕД() для создания динамического календаря на ближайшие шесть недель
Скачать заметку в формате Word или pdf, примеры в формате zip (политикой провайдера запрещено загружать файлы Excel, содержащие код VBA)
Добавьте формулу =ТЕКСТ(A4:G4;"ДДД") в ячейку А3 для заголовков дней недели. Обратите внимание, что эта формула разливается на 7 ячеек по горизонтали.
ТДАТА() и ПОСЛЕД() для вывода интервалов времени
Допустим вам нужно получить массив времени с интервалом 5 минут. Одна минута = 1/1440 суток. Поэтому, чтобы формула массива возвращала 5-минутные интервалы, используйте 1/288 в качестве аргумента шаг: =ПОСЛЕД(5;4;ТДАТА();1/288)
Рис. 75. 5-минутные интервалы, отсчитанные от текущего времени
Генерация последовательности букв алфавита
Функция СИМВОЛ() возвращает букву, основанную на коде ASCII. Например, СИМВОЛ(65) вернет A (английскую), а СИМВОЛ(90) – Z. Можно использовать ПОСЛЕД() внутри СИМВОЛ() для генерации последовательности букв, а затем передать этот массив в функцию ОБЪЕДИНИТЬ(), чтобы показать последовательность букв, разделенных дефисом (или любым другим символом). Формула в С3: =ОБЪЕДИНИТЬ("—";ИСТИНА;СИМВОЛ(ПОСЛЕД(B6;1;КОДСИМВ(A6))))
Рис. 76. Генерация последовательных букв с разделителем
Заметим, что ASCII коды принимают значения от 0 до 255, поэтому если ввести строчную я, и указать число букв более 1, функция вернет ошибку, т.к., после я символов ASCII нет.
Несколько наибольших значений диапазона
Если вам нужно вернуть N наибольших значений диапазона, введите требуемое число в G1. Формула в G2 вернет N наибольших чисел, автоматически расширив область результата:
Рис. 77. Возвращает N наибольших значений диапазона
Несколько наименьших значений диапазона с изменением направления вывода
Усложним задачу. Нужно вывести N наименьших значений, но расположить их не по вертикали, а по горизонтали. Первое, что напрашивается, поместить функцию НАИМЕНЬШИЙ() внутрь функции ТРАНСП(): =ТРАНСП((НАИМЕНЬШИЙ(A1:E8;ПОСЛЕД(G1)))).
Рис. 78. Вернуть N наименьших значений в горизонтальном направлении
Посмотрите еще раз на рис. 78. Формула в G2 использует ПОСЛЕД(4) для возврата вертикального массива, который затем превращается в горизонтальный с помощью ТРАНСП(). Так почему же сразу не вернуть горизонтальный массив!? Для этого используйте внутри функцию ПОСЛЕД(1;G1). Эта формула используется в ячейке G3, и она намного короче формулы в G2.
Обратите также внимание, как в ячейках G6, G7 и G11 выводится текст формул. Это делается с помощью функции Ф.ТЕКСТ(). На рис. 78 формула в G6 сама является функцией динамического массива! Функция Ф.ТЕКСТ() ожидала на вход скаляр, а получила массив G2:G3, и разлилась на две ячейки G6 и G7.
Имитация сводной таблицы диапазоном с тремя формулами
Как автор книги Сводные таблицы в Microsoft Excel, я люблю хорошую сводную таблицу. Но менеджер Microsoft проекта динамических диапазонов Джо Макдэйд и MVP Excel Роджер Говьер указали, что три формулы, вполне способны заменить сводную таблицу. При этом не будет потребности в обновлении.
Чтобы построить отчет:
- Формула в L6: =СОРТ(УНИК(E2:E564)) возвращает уникальный вертикальный список клиентов;
- M5: =ТРАНСП(СОРТ(УНИК(B2:B564))) предоставляет горизонтальный список продуктов;
- М6: =СУММЕСЛИМН(G2:G564;E2:E564;L6#;B2:B564;M5#) возвращает сумму продаж по клиенту и продукту.
Когда вы указываете L6# и M5# в аргументах СУММЕСЛИМН, Excel применяет трансляцию, и превращает скаляр в массив нужной размерности для поиска в диапазонах E2:E564 и B2:B564.
Рис. 79. Замена сводной таблицы тремя формулами; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Отображение чисел в двоичном, восьмеричном или шестнадцатеричном виде
Если нужно представить десятичные числа в двоичном, восьмеричном или шестнадцатеричном виде, используйте функции ОСНОВАНИЕ() и ПОСЛЕД(). Формула в А4: =ОСНОВАНИЕ(ПОСЛЕД(B1);{10;2;8;16};{2;8;4;2}). У функции ОСНОВАНИЕ() три аргумента. Первый число здесь представлен функцией ПОСЛЕД(B1), которая задает массив целых чисел от 1 до 16. Второй – основание, здесь это массив констант {10;2;8;16}. Третий – мин_длина, здесь также массив констант {2;8;4;2}. Работа этой формулы дает пример попарного подъема.
Рис. 80. Генерация двоичных, восьмеричных и шестнадцатеричных чисел
Суммирование длин текстовых строк большого числа ячеек
Используйте формулу =СУММ(ДЛСТР(A2:A12)). Теперь для ее ввода не требуется Ctrl+Shift+Enter.
Рис. 81. Проверка длины ваших твитов
Текст по столбцам
Эта сложная формула разработана Риком Ротстайном. Благодаря использованию функции ПОСЛЕД(), вы можете разбить текстовую строку на слова с использованием лишь одной формулы в В1.
Рис. 82. Функция ПОСЛЕД() облегчает синтаксический анализ
Суммирование всех ВПР
В старом Excel это можно было сделать только с помощью старинной функции ПРОСМОТР(). На самом деле, это была одна из двух причин, по которой пользователи Excel добирались до функции ПРОСМОТР(). С динамическими массивами вы можете использовать ВПР. Вы всё еще будете использовать старую функцию ПРОСМОТР, когда у вас вектор поиска и вектор результатов ориентированы в противоположных направлениях. Единственная формула в Е9 выполняет сначала расчет массива ВПР, а потом суммирует отдельные значения: =СУММ(ВПР(C2:C26;E1:F6;2)). Обратите внимание, что поиск ВПР ведет приблизительно – четвертый аргумент опущен.
Рис. 83. Вычисление всех ВПР и последующее их суммирование
Объединение имени и фамилии
В этом примере используется попарный подъем, который описан в главе 9. Функция получает диапазон имен, один скаляр (пробел) и диапазон фамилий. Первый и последний вектор имеют одинаковый размер. Формула =ПРОПНАЧ(A2:A10&" "&B2:B10) возвращает массив 9×1.
Рис. 84. Объединение имени и фамилии и понижение регистра
Одна формула вместо таблицы данных «что если»
А в этом примере свойство трансляции (см. главу 9) используется для расчета ежемесячных платежей по автокредиту. Диапазон В4:F7 – динамический массив с единственной формулой в В4: =ПЛТ(C9/12;A4:A7;-B3:F3). Первый аргумент – процентная ставка за период. Обратите внимание, что используется простая относительная ссылка, так как не нужно протягивать формулу. Второй аргумент – общее число периодов выплаты по займу – вместо скаляра получает массив А4:А7. Третий аргумент – сумма кредита с обратным знаком – также вместо скаляра получает массив В3:F3. Благодаря трансляции Excel генерит массив из 4 строк и 5 столбцов и передает эти 20 значений в ПЛТ. Обратите внимание, условное форматирование отлично работает с динамическим массивом.
Рис. 85. Одна формула динамического массива в ячейке В4 заменяет таблицу данных
Условное форматирование на основе функции ЗНАК()
Вот еще один пример условного форматирования. Всякий раз, когда я добавляю набор значков «три треугольника» (который явно состоит из двух треугольников и прямоугольника… но я отвлекся), я использую функцию ЗНАК(), чтобы разделить все возможные значения на три группы: больше, равно и меньше. В примере ниже показаны цены закрытия акций компании DJI (ведущего китайского производителя дронов). Используется попарное поднятие для нахождения разницы двух массивов, смещенных один относительно другого на один день. Формула в C3: =ЗНАК(B3:B17-B4:B18) позволяет показать в какую сторону изменилась цена акций.
Рис. 86. Отражение динамики цен закрытия акций
Попарный подъем основан на двух массивах с одинаковым числом строк. В этом примере оба массива находятся в столбце B, но один начинается с B3 (для значения сегодняшнего дня), а другой – с B4 (для значения вчерашнего дня). На рисунке ниже показана формула в C3 в режиме редактирования, так что вы можете видеть два массива.
Рис. 87. Сравнение цен закрытия текущего и предыдущего дней
Использование оператора # для указания на весь массив
Это пример генератора анаграмм. Анаграмма – литературный прием, состоящий в перестановке букв слова или фразы, которая в результате дает другое слово или словосочетание. В оригинале автор использует английскую фразу, буквы которой переставляет случайным образом. Случай редко приводит к осмысленному результату, но позволяет получить что-то любопытное. При переводе я использовал слово из статьи Анаграммы.
Рис. 88. Генератор анаграмм с использованием динамических массивов
Этот пример построен на основе нескольких шагов, что не очень эффективно, зато наглядно. В ячейке D2 использован оператор ссылки на массив #. В ячейке D2 массив из A2 сортируется по массиву B2. Поскольку число символов исходного текста в ячейке А1 не известно, размер массивов A2 и B2 также неизвестен. Поэтому ссылка на массив целиком с использованием нотации # очень удобна. В предыдущих версиях Excel пришлось бы использовать функцию СМЕЩ или ИНДЕКС, и в них задавать какое-нибудь большое число, чтобы идти от обратного и определять размер массива.
Чуть подробнее о шагах на этом рисунке. Введите фразу в ячейку А1. В ячейке B1 вычисляется число символов. Это вспомогательная ячейка, которая используется в других формулах. Ячейка A2 применяет подъем с помощью функции ПСТР() для возврата массива отдельных символов фразы. Здесь ПСТР() в качестве второго аргумента – начальная позиция – получает вместо скаляра динамический массив, последовательность целых чисел от 1 до длины строки А1. Третий аргумент – количество знаков =1, так что ПСТР() последовательно возвращает по одному знаку. Ячейки В2 генерирует случайные целые неповторяющиеся числа. Размер массива в В2 такой же, как и в А2. Ячейка D2 сортирует символы массива A2# по числам в B2#. И, наконец, ячейка E2 сцепляет все перетасованные символы из D2 в слово или фразу. Понажимайте F9 для генерации нового набора случайных чисел. Может быть, в ячейке Е2 получится что-то осмысленное.
Использование ссылки на массив как части ссылки
Excel MVP Wyn Hopkins обнаружил, что вы можете использовать ссылку A3# в качестве первой части ссылки на ячейку. Предположим, что у вас есть динамический массив, начинающийся с A3. Формулы других динамических массивов располагаются в B3 и C3. Эти массивы будут расти и сжиматься вместе с A3#, но они не являются частью A3#. Предположим, что вы хотите сослаться на A3:Сn, где n – количество строк в массиве A3#, вы можете использовать ссылку вида A3#:C3, и такой синтаксис будет работать.
Рис. 89. Синтаксис A2#:C2 допустим
Создание элементов календаря
Для любой даты динамические массивы могут вернуть название месяца или последнюю дату месяца. На рисунке ниже в ячейке A2 создан массив дат, соответствующих началу месяцев 2019 г., с использованием формулы =ДАТА(2019;ПОСЛЕД(12);1). Чтобы сгенерировать полные названия месяцев введите в ячейку В2 формулу =ТЕКСТ(A2#;"ММММ"), либо в ячейку D2 =ТЕКСТ(ДАТА(2019;ПОСЛЕД(12);1);"МММ"). Чтобы найти даты конца месяцев, используйте в С2 формулу: =КОНМЕСЯЦА(ДАТА(2019;ПОСЛЕД(12);1);0).
Рис. 90. Генерация элементов календаря
Как ни странно, формула =КОНМЕСЯЦА(A2#;0) не работает, и возвращает ошибку #ЗНАЧ! Это общая проблема всех функций, которые изначально были в надстройке Пакет анализа. Формулы, содержащие эти функции, плохо работают с массивами. Разработчики Excel могли бы исправить это, но тогда не будет работать обратная совместимость.
Прогнозирование
Прогнозирование в Excel использует функции ПРЕДСКАЗ() и ПРЕДСКАЗ.ETS(). На рис. 91 в ячейке А146 введены следующие 12 месяцев: =ПОСЛЕД(12;1;145;1). А в ячейке С146 – формула для прогноза продаж: =ПРЕДСКАЗ.ETS(A146#;$B$2:$B$145;$A$2#;1;1). Фактические продажи за прошлые периоды находятся в диапазоне B2:B145. Массив известных периодов введен в ячейке А2: =ПОСЛЕД(144).
Рис. 91. Прогноз на следующие 12 месяцев
Более сложное прогнозирование
В предыдущем примере мы вычислили массив из 12 ячеек, каждая из которых содержала месячный прогноз. В примере ниже этот массив помещается в функцию СУММ, чтобы вернуть прогноз на год. Формула усложняется, и для ее понимания, аргументы в нотации разнесены на отдельные строки:
Рис. 92. Годовой прогноз продаж
Функция ПРЕДСКАЗ.ETS ожидает скаляр, но вместо этого вы передаете ей массив из 12 значений, используя ПОСЛЕД() внутри КОНМЕСЯЦА(). Прогноз основан на фактических данных за 2007–2018 гг., расположенных в ячейках В2:B145. Формулу из E2 можно скопировать в E3:E6.
Возможно, вы спросите: почему нужно пять отдельных формул? Нельзя ли заменить ссылку на год – D2 ссылкой на массив ПОСЛЕД(5;1;2019)? Ответ – нельзя. С двумя вертикальными массивами в одной функции Excel попытается сделать попарный подъем. Но массивы имеют разный размер, поэтому вернется ошибка.
Транспонирование одного массива для предотвращения попарного подъема
Одна из стратегий предотвращения попарного подъема состоит в том, чтобы расположить годы по горизонтали. Одна формула в E3 возвращает массив 12*5:
Рис. 93. Если разместить годы по горизонтали, формула работает
Прогнозирование всех пяти лет в одной формуле
Если вы передадите ПРЕДСКАЗ.ETS в функцию СУММ, то получите суммарный прогноз продаж на 60 месяцев:
Рис. 94. Одна формула возвращает 5-летний прогноз
Данные в строке (столбце) разместить в диапазоне
Предположим, что у вас есть вектор чисел (D1:O1), и вы хотите разместить его в диапазон шириной N столбцов. Введите количество столбцов в D3. Тогда формула =ЕСЛИОШИБКА(ИНДЕКС(E1:AH1;ПОСЛЕД(15;D3));»») вернет диапазон шириной три столбца. Поскольку вы не знаете сколько понадобится строк, и чтобы избежать появления ошибок в нижней части диапазона, вы поместили вашу формулу внутрь функции ЕСЛИОШИБКА().
Рис. 95. Данные из строки в массив из трех столбцов
Использование динамических массивов для зависимой проверки
Функция проверки данных позволяет выбирать из выпадающего списка. Это прекрасно работает, пока кто-то не захочет иметь два списка. Причем так, чтобы элементы во втором списке зависели от того, что выбрано в первом. Это называется зависимой проверкой.
На рисунке ниже элементы первого выпадающего списка отображаются в D4#, благодаря формуле =СОРТ(УНИК(B4:B23)). Проверка в H3 указывает на D4#. Список для второй проверки появляется в E4# благодаря формуле =ФИЛЬТР(A4:A23;B4:B23=H3;"Выберите подкласс"). Проверка в H5 ссылается на E4# (чтобы создать список, выберите ячейку и пройдите по меню Данные –> Работа с данными –> Проверка данных).
Рис. 96. Зависимая проверка
Как VBA работает с динамическими массивами?
Существуют миллионы строк кода VBA, которые используют синтаксис Range.Formula. Если и в вашем коде есть такие конструкции, Excel применит неявное пересечение, и вы не получите динамический массив, который мог бы разлиться. Код VBA будет работать также, как и до введения динамических массивов, и поэтому код останется рабочим.
Если вы пишете код для версии Excel, поддерживающей динамические массивы, используйте новый синтаксис Range.Formula2. Такой код создаст формулу, которая потенциально может разлиться. На рисунке ниже первая строка кода пыталась сгенерировать =ПОСЛЕД(10), но Excel автоматически добавил оператор @ неявного пересечения, так что код вернет в ячейку А1 только одно значение – единицу. Напротив, формула в L1 разольется и вернет динамический массив:
Рис. 97. Для вывода динамического диапазона используется новый синтаксис Range.Formula2
Аналогично код Range.FormulaR1C1 вернет одно значение, а Range.Formula2R1C1 – динамический диапазон.