Динамический массив excel как включить

Обновлено: 06.01.2025

Это перевод главы книги 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

Вы используете тот же трюк, который применили для возврата 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);ЛОЖЬ)

Формулы массива — это мощные формулы, позволяющие выполнять сложные вычисления, которые часто не могут выполняться со стандартными функциями. Их также называют формулами CTRL+SHIFT-ВВОД или CSE, так как для их ввода необходимо нажать CTRL+SHIFT+ВВОД. Формулы массива можно использовать для практически невозможного, например

Подсчет количества символов в диапазоне ячеек.

Суммирует числа, которые отвечают определенным условиям, например наименьшие значения в диапазоне или числа, которые выпадают между верхней и нижней границами.

Суммирование всех n-х значений в диапазоне значений.

Excel массива содержит два типа формул массива: формулы массива, которые выполняют несколько вычислений для получения одного результата, и формулы массива, которые вычисляют несколько результатов. Некоторые функции возвращают массивы значений или требуют массив значений в качестве аргумента. Дополнительные сведения см. в руководстве и примерах формул массива.

Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Создание формулы массива для вычисления одного результата

Этот тип формулы позволяет упростить модель листа благодаря замене нескольких отдельных формул.

Щелкните ячейку, в которую нужно ввести формулу массива.

Введите необходимую формулу.

В формулах массива используется синтаксис обычных формул. Все они начинаются со знака равно (=), и вы можете использовать любую из встроенных Excel формул массива.

Например, эта формула вычисляет итоговую стоимость массива цен на акции и помещает результат в ячейку рядом с элементом "Итоговая стоимость".

Сначала формула умножает общие итоги (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем добавляет эти результаты, чтобы получить общий итог 35 525. Это пример формулы массива с одной ячейкой, так как она находится только в одной ячейке.

При нажатии CTRL+SHIFT+ВВОДExcel автоматически вставляет формулу между (пара открываемой и закрываемой скобок).

Создание формулы массива для вычисления нескольких результатов

Чтобы вычислить несколько результатов с помощью формулы массива, введите массив в диапазон ячеек с таким же количеством строк и столбцов, что и в аргументах массива.

Выделите диапазон ячеек, в который нужно ввести формулу массива.

Введите необходимую формулу.

В следующем примере формула множается по цене в каждом столбце, а формула живет в выбранных ячейках строки 5.

При нажатии CTRL+SHIFT+ВВОДExcel автоматически вставляет формулу между (пара открываемой и закрываемой скобок).

Если вам нужно включить новые данные в формулу массива, см. статью Расширение формулы массива. Вы также можете попробовать:

Незаметно для меня 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. Формула указывает на диапазон ячеек

Рис. 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-минутные интервалы, отсчитанные от текущего времени

Генерация последовательности букв алфавита

Рис. 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. Вычисление всех ВПР и последующее их суммирование

Объединение имени и фамилии

Рис. 84. Объединение имени и фамилии и понижение регистра

Одна формула вместо таблицы данных «что если»

А в этом примере свойство трансляции (см. главу 9) используется для расчета ежемесячных платежей по автокредиту. Диапазон В4:F7 – динамический массив с единственной формулой в В4: =ПЛТ(C9/12;A4:A7;-B3:F3). Первый аргумент – процентная ставка за период. Обратите внимание, что используется простая относительная ссылка, так как не нужно протягивать формулу. Второй аргумент – общее число периодов выплаты по займу – вместо скаляра получает массив А4:А7. Третий аргумент – сумма кредита с обратным знаком – также вместо скаляра получает массив В3:F3. Благодаря трансляции Excel генерит массив из 4 строк и 5 столбцов и передает эти 20 значений в ПЛТ. Обратите внимание, условное форматирование отлично работает с динамическим массивом.

Рис. 85. Одна формула динамического массива в ячейке В4 заменяет таблицу данных

Условное форматирование на основе функции ЗНАК()

Рис. 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-летний прогноз

Данные в строке (столбце) разместить в диапазоне

Рис. 95. Данные из строки в массив из трех столбцов

Использование динамических массивов для зависимой проверки

Функция проверки данных позволяет выбирать из выпадающего списка. Это прекрасно работает, пока кто-то не захочет иметь два списка. Причем так, чтобы элементы во втором списке зависели от того, что выбрано в первом. Это называется зависимой проверкой.

Читайте также: