Динамический массив excel как включить
Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
ПРОСМОТРX – первая новая функция после появления динамических массивов
Функции ПРОСМОТРX и ПОИСКПОЗX дебютировали для Office 365 в августе 2019 года. Они были разработаны для объединения возможностей ВПР, ИНДЕКС, ПОИСКПОЗ и динамических массивов. Ранее я писал о двенадцати удивительных преимуществах ПРОСМОТРX.[1] Здесь расскажу лишь о двух из них. Но сначала введение для тех, кто еще не знаком с ПРОСМОТРX.
В ячейке D4 вы хотите найти категорию (Category) для элемента W25-6. До появления ПРОСМОТРX, вы могли это сделать с помощью =ВПР(A4;F6:I17;4;ЛОЖЬ) или комбинации функций =ИНДЕКС(I7:I17;ПОИСКПОЗ(A4;F7:F17;0)). С ПРОСМОТРX сделать это еще проще. Вы ищете значение ячейки A4 в диапазоне F$7:F$17, и хотите вернуть соответствующее значение из I$7:I$17. Нет необходимости в аргументе ЛОЖЬ функции ВПР или 0 функции ИНДЕКС, потому что ПРОСМОТРX по умолчанию ищет точное совпадение:
Рис. 68. Простой пример ПРОСМОТРX
Возврат 12 столбцов с помощью ВПР
Если вам нужно вернуть значения для каждого из 12 месяцев, вы аккуратно используете смешанные ссылки, а также функцию СТОЛБЕЦ(), чтобы задать правильное значение третьего аргумента функции ВПР: =ВПР($A3;$O$3:$AA$226;СТОЛБЕЦ(B:B);ЛОЖЬ)
Рис. 69. Хитрости использования ВПР для извлечения значений в несколько столбцов
Возврат 12 столбцов с помощью ИНДЕКС и ПОИСКПОЗ
До появления динамических массивов, возможно, лучшим решением было использование вспомогательного столбца для ПОИСПОЗ и двенадцати столбцов для ИНДЕКС. Добавьте столбец B. Используйте заголовок Где? В ячейку В6 введите формулу =ПОИСКПОЗ(A6;$Q$6:$Q$229;0). Формула вернет номер той строки в таблице поиска, где находится A308. В ячейку С6 введите =ИНДЕКС(R$6:R$229;$B6). Обратите внимание на относительные ссылки в первом и втором аргументах. При копировании вправо результат за январь автоматически изменится на февраль и т.д.
Рис. 70. Вспомогательный столбец для ПОИСКПОЗ
С динамическими массивами одна формула ПРОСМОТРX вернет все значения
В ячейке В6 введите формулу =ПРОСМОТРX(A6;$O$6:$O$229;$P$6:$AA$229). Она вернет 12 значений в диапазон В6:М6. Протяните формулу вниз вдоль столбца В.
Рис. 71. Функция ПРОСМОТРX с использованием динамических массивов
Ошибка копирования формулы динамических массивов
Обычно, если вы введете формулу в B6, выберите B6 и дважды щелкните маркер заполнения, Excel скопирует формулу во все строки столбца B. Из-за ошибки это не работает, если B6 содержит динамический массив. (Ошибка все еще актуальна по состоянию на 25 сентября 2020 года.)
- Из ячейки B6 нажмите клавишу со стрелкой влево, чтобы перейти к ячейке A6.
- Из A6 нажмите Ctrl + стрелка вниз, чтобы перейти к последней используемой ячейке в столбце A.
- Нажмите клавишу со стрелкой вправо, чтобы вернуться в столбец B.
- Нажмите Ctrl + Shift + стрелка вверх, чтобы выбрать все ячейки столбца В вплоть до ячейки B6.
- Нажмите Ctrl + D, чтобы скопировать формулу в верхней части выделения во все ячейки выделения.
Двухкоординатный поиск с ИНДЕКС и ПОИСКПОЗ
Выполнение двухкоординатного поиска – еще один сценарий, в котором ИНДЕКС + ПОИСПОЗ выигрывают у ВПР.[2] Рассмотрим следующий пример. Пользователь может выбрать счет в B3 и месяц в B4. Нужна формула, чтобы найти значение на пересечении счета А621 и месяца май.
На первом шаге ищем A621 в диапазоне номеров счетов. =ПОИСКПОЗ(B3;A9:A24;0) возвращает 12, т.е., A621 находится в 12-м ряду номеров счетов. Далее, =ПОИСКПОЗ(B4;B8:G8;0) возвращает 5, т.е., май – пятый месяц. Теперь, вы знаете, что вам нужна 12-я строка и 5-й столбец массива B9:G21: =ИНДЕКС(B9:G24;ПОИСКПОЗ(B3;$A$9:$A$24;0);ПОИСКПОЗ(B4;$B$8:$G$8;0))
Рис. 72. ИНДЕКС + ПОИСПОЗ для двухкоординатного поиска
Двухкоординатный поиск с ПРОСМОТРX
Формула на основе функции ПРОСМОТРX будет короче, но… сложнее для понимания. Итак, вы хотите найти A621 в списке учетных записей, поэтому формула будет начинаться с =ПРОСМОТРX(B3;A12:A27;…). Но теперь самое сложное. Как вам получить аргумент просматриваемый_массив?
Вы используете тот же трюк, который применили для возврата 12 месяцев, но транспонированный. В следующей формуле ПРОСМОТРX выполняет горизонтальный поиск: =ПРОСМОТРX(B4;B11:G11;B12:G27). Обычно вы используете одну строку в качестве аргумента просматриваемый_массив. И ПРОСМОТРX возвращает одно значение. Если же в качестве аргумента просматриваемый_массив ввести прямоугольный диапазон, ПРОСМОТРX вернет один столбец из 16 строк.
Этот промежуточный результат можно увидеть диапазоне I12:I27. Если в ячейке B4 изменить май на февраль, то формула вернет числа из столбца C12:C27. Трюк состоит в том, чтобы использовать внутренний ПРОСМОТРX для получения значений, подставляемых в аргумент просматриваемый_массив внешнего ПРОСМОТРX.
Рис. 73. Формула на основе ПРОСМОТРX короче, но сложнее
[1] Рассказ от первого лица – автора книги Билла Джелена. – Прим. Багузина
[2] Не знаю, насколько выигрывает… Вот формула с ВПР, которая решает эту задачу: =ВПР(B3;A8:G24;ПОИСКПОЗ(B4;A8:G8;0);ЛОЖЬ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше
Excel формулы, возвращаемые набором значений, также называемыми массивом, возвращают эти значения в соседние ячейки. Это действие называется переносом.
Формулы, которые могут возвращать массивы переменного размера, называются динамическими формулами массивов . Формулы, которые в настоящее время возвращают массивы, которые успешно переносятся, могут называться формулами разлитого массива.
Ниже приведены некоторые примечания, которые помогут вам понять и использовать формулы этого типа.
Что означает сброс?
Примечание: Более старые формулы массива, известные как устаревшие формулы массива, всегда возвращают результат фиксированного размера — они всегда переносятся в одно и то же количество ячеек. Поведение сброса, описанное в этом разделе, не применяется к устаревшим формулам массива.
Перенос означает, что формула имеет несколько значений, и эти значения были помещены в соседние ячейки. Например, =SORT(D2:D11,1,-1), который сортирует массив в порядке убывания, вернет соответствующий массив высотой 10 строк. Но вам нужно ввести формулу только в верхней левой ячейке, или F2 в этом случае, и она будет автоматически вылита в ячейку F11.
Ключевые точки
При нажатии клавиши ВВОД для подтверждения формулы Excel динамически размер диапазона выходных данных и поместить результаты в каждую ячейку в этом диапазоне.
Если вы создаете формулу динамического массива для работы со списком данных, может быть полезно поместить ее в таблицу Excel, а затем использовать структурированные ссылки для ссылки на данные. Это связано с тем, что структурированные ссылки автоматически настраиваются при добавлении или удалении строк из таблицы.
Формулы разлитого массива не поддерживаются в Excel таблицах, поэтому их следует поместить в сетку за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.
После ввода формулы разлитого массива при выборе любой ячейки в области сброса Excel вокруг диапазона будет выделена граница. Граница исчезнет при выделении ячейки за пределами области.
Редактируема только первая ячейка в области сброса. Если вы выберете другую ячейку в области прокрутки, формула будет видна в строке формул, но текст будет "фантомным" и не может быть изменен. Если необходимо обновить формулу, выделите левую верхнюю ячейку в диапазоне массива, измените ее по мере необходимости, а затем Excel при нажатии клавиши ВВОД автоматически обновите оставшуюся часть области сброса.
Устаревшие формулы массива, введенные с помощью клавиш CTRL+SHIFT+ВВОД (CSE), по-прежнему поддерживаются в целях обратной совместимости, но больше не должны использоваться. При необходимости можно преобразовать устаревшие формулы массива в формулы динамического массива, найдите первую ячейку в диапазоне массива, скопируйте текст формулы, удалите весь диапазон устаревшего массива, а затем повторно введите формулу в левую верхнюю ячейку. Перед обновлением устаревших формул массива до динамических формул массивов следует учитывать некоторые различия между ними в вычислениях.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше
Excel формулы, возвращаемые набором значений, также называемыми массивом, возвращают эти значения в соседние ячейки. Это действие называется переносом.
Формулы, которые могут возвращать массивы переменного размера, называются динамическими формулами массивов . Формулы, которые в настоящее время возвращают массивы, которые успешно переносятся, могут называться формулами разлитого массива.
Ниже приведены некоторые примечания, которые помогут вам понять и использовать формулы этого типа.
Что означает сброс?
Примечание: Более старые формулы массива, известные как устаревшие формулы массива, всегда возвращают результат фиксированного размера — они всегда переносятся в одно и то же количество ячеек. Поведение сброса, описанное в этом разделе, не применяется к устаревшим формулам массива.
Перенос означает, что формула имеет несколько значений, и эти значения были помещены в соседние ячейки. Например, =SORT(D2:D11,1,-1), который сортирует массив в порядке убывания, вернет соответствующий массив высотой 10 строк. Но вам нужно ввести формулу только в верхней левой ячейке, или F2 в этом случае, и она будет автоматически вылита в ячейку F11.
Ключевые точки
При нажатии клавиши ВВОД для подтверждения формулы Excel динамически размер диапазона выходных данных и поместить результаты в каждую ячейку в этом диапазоне.
Если вы создаете формулу динамического массива для работы со списком данных, может быть полезно поместить ее в таблицу Excel, а затем использовать структурированные ссылки для ссылки на данные. Это связано с тем, что структурированные ссылки автоматически настраиваются при добавлении или удалении строк из таблицы.
Формулы разлитого массива не поддерживаются в Excel таблицах, поэтому их следует поместить в сетку за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.
После ввода формулы разлитого массива при выборе любой ячейки в области сброса Excel вокруг диапазона будет выделена граница. Граница исчезнет при выделении ячейки за пределами области.
Редактируема только первая ячейка в области сброса. Если вы выберете другую ячейку в области прокрутки, формула будет видна в строке формул, но текст будет "фантомным" и не может быть изменен. Если необходимо обновить формулу, выделите левую верхнюю ячейку в диапазоне массива, измените ее по мере необходимости, а затем Excel при нажатии клавиши ВВОД автоматически обновите оставшуюся часть области сброса.
Устаревшие формулы массива, введенные с помощью клавиш CTRL+SHIFT+ВВОД (CSE), по-прежнему поддерживаются в целях обратной совместимости, но больше не должны использоваться. При необходимости можно преобразовать устаревшие формулы массива в формулы динамического массива, найдите первую ячейку в диапазоне массива, скопируйте текст формулы, удалите весь диапазон устаревшего массива, а затем повторно введите формулу в левую верхнюю ячейку. Перед обновлением устаревших формул массива до динамических формул массивов следует учитывать некоторые различия между ними в вычислениях.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Незаметно для меня Microsoft совершил прорыв, представив в сентябре 2018 новые возможности – динамические массивы. Ниже – перевод книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. Книги серии Straight to the Point предназначены для глубокого освещения одного аспекта Excel.
Содержание
Введение
Ранее многие люди пытались изучать формулы массива и потерпели неудачу. Майк Гирвин написал отличную книгу, чтобы объяснить, как работают формулы массива. Целые главы той книги здесь будут сведены к коротким предложениям, благодаря новым функциям.
О динамических массивах было объявлено 24 сентября 2018 года, но даже в MS Excel 2019 они пока не представлены. Динамические массивы доступны только в Office 365. Я думаю, что парадигма покупки бессрочной лицензии на Office каждые три или шесть лет устарела, и рекомендую переходить на подписку.
Оригинальные файлы с примерами можно загрузить с сайта автора. К каждой главе я приложу файл, адаптированный к настоящему переводу.
Глава 1. Начало работы
Формулы теперь могу разливаться
Начнем с базовой формулы массива. Перейдите в ячейку Е3. Наберите =A2:C10. В более ранних версиях Excel вам пришлось бы включить этот диапазон в качестве аргумента какой-нибудь функции, или использовать формулу массива, одновременно нажав Ctrl+Shift+Enter.
Рис. 1. Формула указывает на диапазон ячеек
Теперь же достаточно нажать Enter. Excel возвращает значения в 27 ячеек, которые выбираются автоматически вправо и вниз. Посмотрите на формулу в строке формул… здесь нет фигурных скобок, а это значит, что никто не нажимал Ctrl+Shift+Enter.
Рис. 2. Одна формула вернула множество значений
Ячейка E4 содержит текст Central, и, хотя строка формул показывает формулу для этой ячейки, она отображается серым цветом. Давайте проверим с помощью VBA, что содержится в ячейках Е3 и Е4?
Рис. 3. VBA подтверждает, что в ячейке Е4 не формула
VBA показывает, что в ячейке Е3 – формула, а в Е4 – нет. Также в Excel можно ввести формулу =ЕФОРМУЛА(E4). Она вернет ЛОЖЬ. И еще одна проверка. Выберете диапазон D1:H20, и пройдите по меню Главная –> Найти и заменить –> Формулы. Будет выделена только ячейка E3.
Один из первых вопросов на YouTube в ответ на мои первые видео с динамическими массивами был: можно ли вы копировать и вставлять значения? Да! Выберите диапазон E3:G11, нажмите Ctrl+C, кликните правой кнопкой мыши на выбранную новую ячейку и выберите Специальная вставка –> Значения.
Что происходит, если формула не может пролиться?
Что произойдет, если ячейка, куда должен разлиться диапазон, будет занята?
Рис. 4. Как Excel справится с занятой ячейкой?
Как только вы очистите ячейки, мешающие размещению массива, он автоматически разольется.
- Неопределенный размер. Вы не можете использовать волатильные функции, например, СЛУЧМЕЖДУ(), в качестве аргумента функции ПОСЛЕДОВ().
- Выходит за пределы листа. Вы не можете ввести функцию =СОРТ(C:C) в ячейке E2.
- Табличная формула. Вы не можете использовать функции динамического массива внутри Таблицы.
- Не хватает памяти. Вам следует ссылаться на диапазон меньшего размера.
- Разлив в объединенные ячейки. Динамический массив не может разливаться в объединенную ячейку.
- Неопознанная ошибка. Excel не может распознать ошибку.
Если ваша формула указывает на Таблицу, динамический массив будет расширяться при добавлении новых строк в Таблицу
Рис. 6. Динамический массив «отслеживает» Таблицу
Сравните с рис. 2. Вы преобразовали диапазон А1:С19 в Таблицу (Ctrl+T). Формула в ячейке Е3 изменилась на =Таблица1. Теперь, если вы добавите еще одну строку в Таблицу (А11:С11), формула в ячейке Е3 не изменится, а динамический массив автоматически расширится еще одной строкой (Е12:G12).
Хотя формулы динамического массива могут указывать на Таблицу, в самой Таблице использовать формулы динамического массива нельзя.
Что такое неявное пересечение
Если вы введете =@C2:C11 в любой ячейке в строках со 2 по 10, формула вернет значение из столбца С той строки, в которой вы ввели формулу. Знак @ известен как неявный оператор пересечения.
Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
Примеры этой главы предназначены для того, чтобы дать вам отправную точку. Но они, конечно же, не исчерпывают всех возможностей использования обычных функций Excel в сочетании с новыми свойствами динамических массивов. Всякий раз, когда функция ожидает скаляр, передача ей массива приведет к подъему. И в итоге функция вернет диапазон значений.
Использование СЕГОДНЯ() и ПОСЛЕД() для календаря
Допустим, вы используете СЕГОДНЯ() в качестве третьего аргумента функции ПОСЛЕД(). Тогда формула =ПОСЛЕД(6;7;СЕГОДНЯ()) в ячейке А4 вернет массив из 6 строк и 7 столбцов календаря, начинающегося сегодня:
Рис. 74. Использование СЕГОДНЯ() и ПОСЛЕД() для создания динамического календаря на ближайшие шесть недель
Добавьте формулу =ТЕКСТ(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 Роджер Говьер указали, что три формулы, вполне способны заменить сводную таблицу. При этом не будет потребности в обновлении.
Чтобы построить отчет:
Рис. 79. Замена сводной таблицы тремя формулами; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Отображение чисел в двоичном, восьмеричном или шестнадцатеричном виде
Если нужно представить десятичные числа в двоичном, восьмеричном или шестнадцатеричном виде, используйте функции ОСНОВАНИЕ() и ПОСЛЕД(). Формула в А4: =ОСНОВАНИЕ(ПОСЛЕД(B1);;). У функции ОСНОВАНИЕ() три аргумента. Первый число здесь представлен функцией ПОСЛЕД(B1), которая задает массив целых чисел от 1 до 16. Второй – основание, здесь это массив констант . Третий – мин_длина, здесь также массив констант . Работа этой формулы дает пример попарного подъема.
Рис. 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. Генератор анаграмм с использованием динамических массивов
Использование ссылки на массив как части ссылки
Создание элементов календаря
Рис. 90. Генерация элементов календаря
Прогнозирование
Рис. 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. Данные из строки в массив из трех столбцов
Использование динамических массивов для зависимой проверки
Функция проверки данных позволяет выбирать из выпадающего списка. Это прекрасно работает, пока кто-то не захочет иметь два списка. Причем так, чтобы элементы во втором списке зависели от того, что выбрано в первом. Это называется зависимой проверкой.
Рис. 96. Зависимая проверка
Как VBA работает с динамическими массивами?
Существуют миллионы строк кода VBA, которые используют синтаксис Range.Formula. Если и в вашем коде есть такие конструкции, Excel применит неявное пересечение, и вы не получите динамический массив, который мог бы разлиться. Код VBA будет работать также, как и до введения динамических массивов, и поэтому код останется рабочим.
Если вы пишете код для версии Excel, поддерживающей динамические массивы, используйте новый синтаксис Range.Formula2. Такой код создаст формулу, которая потенциально может разлиться. На рисунке ниже первая строка кода пыталась сгенерировать =ПОСЛЕД(10), но Excel автоматически добавил оператор @ неявного пересечения, так что код вернет в ячейку А1 только одно значение – единицу. Напротив, формула в L1 разольется и вернет динамический массив:
Рис. 97. Для вывода динамического диапазона используется новый синтаксис Range.Formula2
Аналогично код Range.FormulaR1C1 вернет одно значение, а Range.Formula2R1C1 – динамический диапазон.
Читайте также: