Динамические массивы в Excel

Что такое динамические массивы

В сентябре 2018 года Microsoft выпустила обновление, которое добавляет в Microsoft Excel совершенно новый инструменты: динамические массивы (Dynamic Arrays) и 7 новых функций для работы с ними. Эти вещи, без преувеличения, совершенно кардинальным образом меняет всю привычную технику работы с формулами и функциями и касаются, буквально, каждого пользователя.

Рассмотрим простой пример, чтобы объяснить суть.

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

Ссылка в формуле на диапазон

Во всех прошлых версиях Excel после нажатия на Enter мы бы получили содержимое только одной первой ячейки B2. А как иначе?

Ну, или можно было бы завернуть этот диапазон в какую-нибудь аггрегирующую функцию типа =СУММ(B2:C4) и получить по нему общий итог.

Если бы нам потребовались операции посложнее примитивной суммы, например, извлечение уникальных значений или Топ-3, то пришлось бы вводить нашу формулу как формулу массива, используя сочетание клавиш Ctrl+Shift+Enter.

Теперь всё по-другому.

Теперь после ввода такой формулы мы можем просто нажать на Enter - и получить в результате сразу все значения, на которые мы ссылались:

Динамический массив

Это не магия, а новые динамические массивы, которые теперь есть в Microsoft Excel. Добро пожаловать в новый мир :)

Особенности работы с динамическими массивами

Технически, весь наш динамический массив хранится в первой ячейке G4, заполняя своими данными необходимое количество ячеек вправо и вниз. Если выделить любую другую ячейку массива, то в строке формул ссылка будет неактивной, показывая, что мы находимся в одной из "дочерних" ячеек:

Одна из ячеек массива

Попытка удалить одну или несколько "дочерних" ячеек ни к чему не приведёт - Excel тут же заново их вычислит и заполнит.

При этом ссылаться на эти "дочерние" ячейки в других формулах мы можем совершенно спокойно:

Ссылка на отдельные ячейки массива

Если копировать первую ячейку массива (например из G4 в F8), то и и весь массив (его ссылки) сдвинется в том же направлении, как и в обычных формулах:

Копирование динамического массива

Если нам нужно переместить массив, то достаточно будет перенести (мышью или сочетанием Ctrl+X, Ctrl+V), опять же, только первую главную ячейку G4 - вслед за ней перенесется на новое место и заново развернётся весь наш массив.

Если вам нужно сослаться где-нибудь еще на листе на созданный динамический массив, то можно использовать спецсимвол # ("решётка") после адреса его ведущей ячейки:

Ссылка на динамический массив

Например, теперь можно легко сделать выпадающий список в ячейке, который ссылается на созданный динамический массив:

Выпадающий список на динамическом массиве

Ошибки динамических массивов

Но что будет, если для развёртывания массива не будет достаточно пространства или на его пути окажутся ячейки уже занятые другими данными? Знакомьтесь с принципиально новым типом ошибок в Excel - #ПЕРЕНОС! (#SPILL!):

Ошибка распространения динамического массива

Как всегда, если щелкнуть мышью по значку с желтым ромбом и восклицательным знаком, то мы получим более подробное пояснение по источнику проблемы и сможем быстро найти мешающие ячейки:

Ошибка ПЕРЕНОС

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

Динамические массивы и "умные" таблицы

Если динамический массив указывает на "умную" таблицу, созданную сочетанием клавиш Ctrl+T или с помощью Главная - Форматировать как таблицу (Home - Format as Table), то он также унаследует её главное качество - автоподстройку размеров.

Динамический массив и умная таблица

При добавлении новых данных внизу или справа "умная" таблица и динамический диапазон тоже будут автоматически растягиваться:

Растягивание динамического массива

При этом, однако, есть одно ограничение: мы не можем использовать ссылку на динамический диапазон в форумулах внутри "умной" таблицы:

Ошибка дин.массива внутри умной таблицы

Динамические массивы и другие функции Excel

Хорошо, скажете вы. Всё это интересно и забавно. Не нужно, как раньше, вручную протягивать формулу со ссылкой на первую ячейку исходного диапазона вниз и вправо и всё такое. И всё?

Не совсем.

Динамические массивы - это не просто еще один инструмент в Excel. Теперь они внедрены в самое сердце (или мозг) Microsoft Excel - его вычислительный движок. А это значит что и другие, привычные нам формулы и функции Excel теперь тоже поддерживают работу с динамическими массивами. Давайте разберём несколько примеров, чтобы вы осознали всю глубину произошедших изменений.

Транспонирование

Чтобы транспонировать диапазон (обменять местами строки и столбцы) в Microsoft Excel всегда имелась встроенная функция ТРАНСП (TRANSPOSE). Однако, чтобы её использовать, вы должны были сначала правильно выделить диапазон для результатов (например, если на входе был диапазон 5х3, то вы должны были обязательно выделить 3x5), потом ввести функцию и нажать сочетание Ctrl+Shift+Enter, т.к. она умела работать только в режиме формул массива.

Теперь можно просто выделить одну ячейку, ввести в нее эту же формулу и нажать на обычный Enter - динамический массив сделает всё сам:

Транспонирование и динамический массив

Таблица умножения

Этот пример я обычно приводил, когда меня просили наглядно показать преимущества формул массива в Excel. Теперь чтобы посчитать всю таблицу Пифагора достаточно встать в первую ячейку B2, ввести туда формулу перемножающую два массива (вертикальный и горизонтальный набор чисел 1..10) и просто нажать на Enter:

Таблица умножения динамическим массивом

Склейка и преобразование регистра

Массивы можно не только перемножать, но склеивать стандартным оператором & (амперсанд). Предположим, нам нужно сцпеить имя и фамилию из двух столбцов и поправить скачущий регистр в исходных данных. Делаем это одной короткой формулой, которая формирует весь массив, а потом применяем к нему функцию ПРОПНАЧ (PROPER), чтобы привести в порядок регистр:

Склейка массивов

Вывод Топ-3

Предположим, что у нас есть куча чисел, из которых нужно вывести три лучших результата, расположив их в порядке убывания. Теперь это делается одной формулой и, опять же, без всяких Ctrl+Shift+Enter как раньше:

Топ-3

Если захочется, чтобы результаты располагались не в столбец, а в строку, то достаточно заменить в этой формуле двоеточия (разделитель строк) на точку с запятой (разделитель элементов внутри одной строки). В англоязычной версии Excel роль этих разделителей играют точка с запятой и запятая, соответственно.

ВПР извлекающая сразу несколько столбцов

Функцей ВПР (VLOOKUP) теперь можно вытаскивать значения не из одного, а сразу из нескольких столбцов - достаточно указать их номера (в любом желаемом порядке) в виде массива в третьем аргументе функции:

ВПР с выводом сразу нескольких столбцов

Функция СМЕЩ (OFFSET) возвращающая динамический массив

Одной из самых интересных и полезных (после ВПР) функций для анализа данных является функция СМЕЩ (OFFSET), которой я посвятил в своё время целую главу в своей книжке и статью здесь. Сложность в понимании и освоении этой функции всегда была в том, что она возвращала в качестве результата массив (диапазон) данных, но увидеть его мы не могли, т.к. Excel до сих пор не умел работать с массивами "из коробки".

Сейчас эта проблема в прошлом. Посмотрите, как теперь с помощью одной формулы и динамического массива, возвращаемого СМЕЩ, можно извлечь все строки по заданному товару из любой отсортированной таблицы:

Функция СМЕЩ и динамический массив

Давайте разберём её аргументы:

  • А1 - стартовая ячейка (точка отсчёта)
  • ПОИСКПОЗ(F2;A2:A30;0) - вычисление сдвига от стартовой ячейки вниз - до первой найденной капусты.
  • 0 - сдвиг "окна" вправо относительно стартовой ячейки
  • СЧЁТЕСЛИ(A2:A30;F2) - вычисление высоты возвращаемого "окна" - количества строк, где есть капуста.
  • 4 - размер "окна" по горизонтали, т.е. выводим 4 столбца

Новые функции для динамических массивов

Помимо поддержки механизма динамических массивов в старых функциях, в Microsoft Excel были добавлены несколько совершенно новых функций, заточенных именно под работу с динамическими массивами. В частности, это:

  • СОРТ (SORT) - сортирует входной диапазон и выдает динамический массив на выходе
  • СОРТПО (SORTBY) - умеет сортировать один диапазон по значениям из другого
  • ФИЛЬТР (FILTER) - извлекает из исходного диапазона строки, удовлетворяющие заданным условиям
  • УНИК (UNIQUE) - извлекает из диапазона уникальные значения или убирает повторы
  • СЛМАССИВ (RANDARRAY) - генерит массив случайных чисел заданного размера
  • ПОСЛЕД (SEQUENCE) - формирует массив из последовательности чисел с заданным шагом

Подробнее про них - чуть позже. Они стоят отдельной статьи (и не одной) для вдумчивого изучения :)

Выводы

Если вы прочитали всё написанное выше, то, думаю, уже осознаёте масштаб изменений, которые произошли. Очень многие вещи в Excel теперь можно будет делать проще, легче и логичнее. Я, признаться, немного в шоке от того, сколько статей теперь придется корректировать здесь, на этом сайте и в моих книгах, но готов это сделать с легким сердцем.

Подбивая итоги, в плюсы динамических массивов можно записать следующее:

  • Можно забыть про сочетание Ctrl+Shift+Enter. Теперь Excel не видит различий между "обычными формулами" и "формулами массива" и обрабатывает их одинаково.
  • Про функцию СУММПРОИЗВ (SUMPRODUCT), которую раньше использовали для ввода формул массива без Ctrl+Shift+Enter тоже можно забыть - теперь достаточно просто СУММ и Enter.
  • Умные таблицы и привычные функции (СУММ, ЕСЛИ, ВПР, СУММЕСЛИМН и т.д.) теперь тоже полностью или частично поддерживают динамические массивы.
  • Есть обратная совместимость: если открыть книгу с динамическими массивами в старой версии Excel, то они превратятся в формулы массива (в фигурных скобках) и продолжат работать в "старом стиле".

Нашлось и некоторое количество минусов:

  • Нельзя удалить отдельные строки, столбцы или ячейки из динамического массива, т.е. он живёт как единый объект.
  • Нельзя сортировать динамический массив привычным образом через Данные - Сортировка (Data - Sort). Для этого есть теперь специальная функция СОРТ (SORT).
  • Динамический диапазон нельзя превратить в умную таблицу (но можно сделать динамический диапазона на основе умной таблицы).

Само-собой, это еще не конец и, я уверен, Microsoft продолжит совершенствовать этот механизм в будущем.

Где скачать?

И, наконец, главный вопрос :)

Microsoft впервые анонсировало и показало превью динамических массивов в Excel еще в сентябре 2018 года на конференции Ignite. В последующие несколько месяцев происходило тщательное тестирование и обкатка новых возможностей сначала на кошках сотрудниках самой Microsoft, а потом на добровольцах-тестировщиках из круга Office Insiders. В этом году обновление, добавляющее динамические массивы стали постепенно раскатывать уже по обычным подписчикам Office 365. Я, например, получил его только в августе с моей подпиской Office 365 Pro Plus (Monthly Targeted).

Если в вашем Excel ещё нет динамических массивов, а поработать с ними очень хочется, то есть следующие варианты:

  • Если у вас подписка Office 365, то можно просто продождать, пока до вас дойдет это обновление. Как быстро это случится - зависит от настройки частоты доставки обновлений для вашего Office (раз в год, раз в полгода, раз в месяц). Если у вас корпоративный ПК, то можно попросить вашего администратора настроить загрузку обновлений почаще.
  • Можно записаться в ряды тех самых добровольцев-тестировщиков Office Insiders - тогда вы будете первым получать все новые возможности и функции (но есть шанс повышенной глючности в работе Excel, само-собой).
  • Если у вас не подписка, а коробочная standalone-версия Excel, то придется ждать до выхода следующей версии Office и Excel в 2022 году, как минимум. Пользователи таких версий получают только обновления безопасности и исправления ошибок, а все новые "плюшки" теперь достаются только подписчикам Office 365. Sad but true :)

В любом случае, когда динамические массивы появятся в вашем Excel - после этой статьи вы будете к этому уже готовы :)

Ссылки по теме



11.09.2019 01:10:29
Здравствуйте!
Интересно, это сколько новых идей и практического применения вызовет использование динамических массивов?
Николай, на счёт переписать 2-ю книжку Вы, конечно, пошутили... Или нет?
11.09.2019 08:14:52
Ну, переписывать с нуля, конечно не придется, но пару глав добавить точно потребуется :)
12.09.2019 11:40:52
Николай, задравствуйте!
Вначале говорю Вам Спасибо, за такую замечательную новость!
И хочу спросить - какая у Вас версия Excel сейчас? Сегодня купил Office 365 (правда пока проверил на MacOS) - нет динамических массивов  :(
Моя версия Excel 16.29 (19090802)
12.09.2019 15:19:29
Добавьте в ленту команду "Программа предварительной оценки Office" (это в русскоязычной версии офиса по подписке так команда называется), нажимайте на кнопку и присоединяйтесь к программе предварительной оценки. В инсайдерской сборке раннего доступа 16.0.12105.20000 новые формулы динамических массивов есть.
Кстати, для Мака их может еще и не быть.
27.09.2019 11:25:29
У меня Excel 2016, но динамические массивы почему то не работают правильно. Открываю пример, все работает, пытаюсь самостоятельно создать массив из диапазона, выдает ошибку  В чем может быть причина?
03.10.2019 14:59:33
В том, что Excel 2016 не умеет работать с динамическими массивами. Для них, на сегодняшний день, нужно иметь подписку на Office 365 (да и там не всё так гладко, не все подписчики ещё это обновление получили, а Microsoft все жалобы игнорирует…). Если хотите динамических массивов без подписки, нужно ждать пока следующий офисный пакет выйдет, годика через 2. :-( И его ещё потом купить придётся.
02.10.2019 14:46:46
Николай, добрый день.
Напишите пожалуйста какая у вас версия офиса и сборка.
PS: Я правильно понимаю, что эта ваша версия в рамках подписки и вы не состоите в Office Insider?
04.10.2019 11:15:06
У меня Office 365 Pro Plus, версия 1909, сборка 12026.20264 Monthly Channel (Targeted)
Не Office Insider
04.10.2019 14:32:47
Николай Спасибо!!!
Достучался через IT до Microsoft, сказали что для нас добавление новых функций запланировано на конец Q1'2020.

Теперь в томительном ожидании.
06.10.2019 08:35:10
Ого. Прилично ждать. Жаль слышать :(
Кирилл, добрый день! Подскажите, пожалуйста, "для нас", это для кого?
04.10.2019 18:10:05
Странно,
у меня Microsoft Office 365 ProPlus
Версия 1909 (сборка 12026.20264) Monyhly Channel
Если смотреть "о программе Excel" то Microsoft Excel для Office 365 MSO (16.0.12026.20100) 32-разрядная
новых плюшек до сих порт нет.
05.10.2019 16:17:28
Обновления приходят (чем левее, тем раньше) : Monthly (Targeted) > Monthly > Semi-Annual (Targeted) > Semi-Annual. Судя по ответу Николая выше, ваша версия и не должна иметь ничего нового.

ЗЫ. Можете попробовать пнуть админов, чтобы переключили вас на Monthly (Targeted) канал обновлений. Если это у вас возможно и приемлемо. Всётаки, Targeted-каналы - это всё ещё тестовые версии, которые не обязаны быть стабильными.
05.10.2019 16:12:49
Ну вот, собственно, и ответ. Targeted каналы - это почти то же самое, что и Insider. Только Insider - это совсем-совсем тестовые сборки, возможности из которых не факт, что вообще появятся и будут развиваться дальше, а targeted - уже готовые версии, но выдаются, на-пробу, раньше, чем всем остальным.
06.10.2019 10:26:34
Простите, если скажу банальность. А чем плоха для таблицы умножения классическая формула?: =$A2*B$1
08.10.2019 13:59:24
ничем) в данном случае)
23.10.2019 17:01:20
Посмотрел видео, рассказал жене, решили купить офис 365 E-1 за 10$ в месяц, Вот уже неделю стоит а "магии" динамических массивов нет, постоянно обновляю, но не работает.
Офис купил только после просмотра Вашего видео, до этого у меня стоял ломанный Excel 2016.
Чем я обидел майкрасофт??? Почему мне не дают эту "магию". И почему Вы работаете на благо и приводите клиентов, а Майкрасофт Вас подводит???
:D
06.11.2019 15:29:51
Добрый день! У меня такой вопрос возник по работе с динамическими массивами. В силу специфики своей работы приходится очень часто работать с таблицами, где собрано огромное количество данных по наименованием. Каждое наименование имеет годовое значение и месяца. Проблема заключается в том, что все данные по месяцам должны быть округлены, но не отличаться от годового значения. При использовании формул округления в итоге общая сумма по месяцам в большинстве случает расходится с заданной суммой по году на 0,1 0,2 и тд.
Есть ли Excel способ задать какие либо ограничения по округлений данных по месяцам, чтобы их сумма не превышала или не была ниже заданного округлённого  годового значения?
Спасибо
Наверх