Содержание:
Что такое динамические массивы
В сентябре 2018 года Microsoft выпустила обновление, добавляющее в Microsoft Excel совершенно новый инструмент: динамические массивы и 7 новых функций для работы с ними. Эти вещи, без преувеличения, кардинально меняют всю привычную технику работы с формулами и функциями и касаются буквально каждого пользователя.
Рассмотрим простой пример, поясняющий суть.
Предположим, у нас есть простая таблица с данными по городам-месяцам. Что будет, если мы выделим любую пустую ячейку справа листа и введем в нее формулу, связывающую не одну ячейку, а сразу диапазон?
Во всех предыдущих версиях Excel после нажатия Enter мы получили бы содержимое только одной первой ячейки B2. Как еще?
Ну, или можно было бы обернуть этот диапазон в какую-нибудь агрегатирующую функцию типа =SUM(B2:C4) и получить по нему общий итог.
Если бы нам нужны были более сложные операции, чем примитивная сумма, такие как извлечение уникальных значений или Топ-3, то нам пришлось бы вводить нашу формулу как формулу массива с помощью сочетания клавиш. Ctrl+Shift+Enter.
Теперь все иначе.
Теперь после ввода такой формулы мы можем просто нажать на Enter — и получим в результате сразу все значения uXNUMXbuXNUMXb, на которые мы ссылались:
Это не магия, а новые динамические массивы, которые теперь есть в Microsoft Excel. Добро пожаловать в новый мир 🙂
Особенности работы с динамическими массивами
Технически весь наш динамический массив хранится в первой ячейке G4, заполняя своими данными необходимое количество ячеек вправо и вниз. Если выделить любую другую ячейку массива, то ссылка в строке формул станет неактивной, показывая, что мы находимся в одной из «дочерних» ячеек:
Попытка удалить одну или несколько «дочерних» ячеек ни к чему не приведет — Excel тут же их пересчитает и заполнит.
В то же время мы можем смело ссылаться на эти «дочерние» ячейки и в других формулах:
Если скопировать первую ячейку массива (например, с G4 на F8), то весь массив (его ссылки) будет двигаться в том же направлении, что и в обычных формулах:
Если нам нужно переместить массив, то достаточно будет переместить (мышью или комбинацией Ctrl+X, Ctrl+V), опять же только первая основная ячейка G4 – после нее она будет перенесена на новое место и весь наш массив снова расширится.
Если вам необходимо сослаться где-то еще на листе на созданный динамический массив, то вы можете использовать специальный символ # («решетка») после адреса его ведущей ячейки:
Например, теперь вы легко можете сделать в ячейке выпадающий список, который ссылается на созданный динамический массив:
Ошибки динамического массива
Но что произойдет, если для расширения массива недостаточно места или на его пути уже есть ячейки, занятые другими данными? Встречайте принципиально новый тип ошибок в Excel – #ПЕРЕДАЧА! (#ПРОЛИВАТЬ!):
Как всегда, если нажать на иконку с желтым ромбом и восклицательным знаком, мы получим более подробное объяснение источника проблемы и сможем быстро найти мешающие ячейки:
Подобные ошибки возникнут, если массив выйдет за пределы листа или попадет в объединенную ячейку. Если убрать препятствие, то все тут же на лету исправится.
Динамические массивы и умные таблицы
Если динамический массив указывает на «умную» таблицу, созданную с помощью сочетания клавиш Ctrl+T или путем Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу), то он также унаследует свое главное качество – автокалибровку.
При добавлении новых данных внизу или справа интеллектуальная таблица и динамический диапазон также автоматически растягиваются:
Однако есть одно ограничение: мы не можем использовать ссылку на динамический диапазон в форумах внутри смарт-таблицы:
Динамические массивы и другие функции Excel
Хорошо, говоришь ты. Все это интересно и забавно. Не нужно, как раньше, вручную растягивать формулу с ссылкой на первую ячейку исходного диапазона вниз и вправо и все такое. И это все?
Не совсем.
Динамические массивы — это не просто еще один инструмент Excel. Теперь они встроены в самое сердце (или мозг) Microsoft Excel – его вычислительный механизм. Это означает, что другие привычные нам формулы и функции Excel теперь также поддерживают работу с динамическими массивами. Давайте рассмотрим несколько примеров, чтобы дать вам представление о глубине произошедших изменений.
транспонировать
Для транспонирования диапазона (поменяйте местами строки и столбцы) в Microsoft Excel всегда была встроенная функция. ТРАНСПОРТ (ТРАНСПОН). Однако, чтобы ее использовать, необходимо сначала правильно выбрать диапазон для результатов (например, если ввод был диапазон 5х3, то вы должны были выбрать 3х5), затем войти в функцию и нажать кнопку комбинация Ctrl+Shift+Enter, потому что он мог работать только в режиме формулы массива.
Теперь можно просто выделить одну ячейку, ввести в нее ту же формулу и нажать на обычную Enter – динамический массив сделает все сам:
Таблица умножения
Это пример, который я приводил, когда меня просили визуализировать преимущества формул массива в Excel. Теперь, чтобы вычислить всю таблицу Пифагора, достаточно встать в первую ячейку B2, ввести туда формулу умножения двух массивов (вертикальный и горизонтальный набор чисел 1..10) и просто нажать на Enter:
Склеивание и переделка корпуса
Массивы можно не только умножать, но и склеивать стандартным оператором & (амперсанд). Предположим, нам нужно извлечь имя и фамилию из двух столбцов и исправить перескакивающий регистр в исходных данных. Мы делаем это с помощью одной короткой формулы, которая формирует весь массив, а затем применяем к нему функцию ПРОПНАЧ (ПРАВИЛЬНЫЙ)чтобы привести в порядок реестр:
Заключение Топ-3
Предположим, у нас есть набор чисел, из которых мы хотим получить три лучших результата, расположив их в порядке убывания. Теперь это делается по одной формуле и опять же без всяких Ctrl+Shift+Enter как раньше:
Если вы хотите, чтобы результаты располагались не в столбце, а в строке, то достаточно заменить в этой формуле двоеточие (разделитель строк) на точку с запятой (разделитель элементов внутри одной строки). В английской версии Excel этими разделителями являются точки с запятой и запятые соответственно.
VLOOKUP извлечение нескольких столбцов одновременно
функции ВПР (ВПР) теперь вы можете вытягивать значения не из одного, а сразу из нескольких столбцов — просто укажите их номера (в любом желаемом порядке) в виде массива в третьем аргументе функции:
Функция OFFSET, возвращающая динамический массив
Одной из наиболее интересных и полезных (после ВПР) функций анализа данных является функция УДАЛЕНИЕ (КОМПЕНСИРОВАТЬ), которому я посвятил в свое время целую главу в своей книге и статью здесь. Сложность в понимании и освоении этой функции всегда заключалась в том, что она возвращала в результате массив (диапазон) данных, но мы не могли этого увидеть, поскольку Excel еще не умел работать с массивами из коробки.
Теперь эта проблема осталась в прошлом. Посмотрите, как теперь, используя одну формулу и динамический массив, возвращаемый функцией OFFSET, вы можете извлечь все строки для данного продукта из любой отсортированной таблицы:
Давайте посмотрим на ее аргументы:
- A1 – стартовая ячейка (точка отсчета)
- ПОИСКПОЗ(F2;A2:A30;0) – расчет смещения от начальной ячейки вниз – к первой найденной капусте.
- 0 – сдвиг «окна» вправо относительно начальной ячейки
- СЧЁТЕСЛИ(A2:A30;F2) — расчет высоты возвращаемого «окна» — количества строк, где находится капуста.
- 4 — размер «окна» по горизонтали, т.е. вывод 4 столбцов
Новые функции для динамических массивов
Помимо поддержки механизма динамических массивов в старых функциях, в Microsoft Excel добавлено несколько совершенно новых функций, заточенных специально под работу с динамическими массивами. В частности, это:
- GRADE (СОРТИРОВАТЬ) – сортирует входной диапазон и создает динамический массив на выходе
- Сортпо (СОРТИРОВАТЬ ПО) — может сортировать один диапазон по значениям из другого
- ФИЛЬТР (ФИЛЬТР) – извлекает строки из исходного диапазона, соответствующие указанным условиям
- UNIK (УНИКАЛЬНЫЙ) — извлекает уникальные значения из диапазона или удаляет дубликаты
- СЛМАССИВНЫЙ (РАНДАРРЭЙ) – генерирует массив случайных чисел заданного размера
- ПОСЛЕД (ПОСЛЕДОВАТЕЛЬНОСТЬ) — формирует массив из последовательности чисел с заданным шагом
Подробнее о них – чуть позже. Они достойны отдельной статьи (и не одной) для вдумчивого изучения 🙂
Выводы
Если вы прочитали все написанное выше, то думаю, вы уже осознали масштаб произошедших изменений. Очень многие вещи в Excel теперь можно делать проще, проще и логичнее. Признаюсь, я немного шокирован тем, сколько статей придется теперь исправлять здесь, на этом сайте и в моих книгах, но я готов сделать это с легким сердцем.
Подводя итоги, плюсы динамические массивы, вы можете написать следующее:
- Вы можете забыть о сочетании Ctrl+Shift+Enter. Excel теперь не видит разницы между «обычными формулами» и «формулами массивов» и обрабатывает их одинаково.
- О функции SUMPRODUCT (СУММПРОИЗВ), который ранее использовался для ввода формул массива без Ctrl+Shift+Enter ты тоже можешь забыть – теперь это достаточно легко SUM и Enter.
- Умные таблицы и привычные функции (СУММ, ЕСЛИ, ВПР, СУММИФС и т. д.) теперь также полностью или частично поддерживают динамические массивы.
- Есть обратная совместимость: если открыть книгу с динамическими массивами в старой версии Excel, они превратятся в формулы массива (в фигурных скобках) и продолжат работать по «старому стилю».
Нашел какой-то номер минусы:
- Вы не можете удалить отдельные строки, столбцы или ячейки из динамического массива, т.е. он существует как единое целое.
- Вы не можете отсортировать динамический массив обычным способом через Данные — сортировка (Данные — Сортировка). Для этого теперь есть специальная функция. GRADE (СОРТИРОВАТЬ).
- Динамический диапазон нельзя превратить в смарт-таблицу (но можно создать динамический диапазон на основе смарт-таблицы).
Конечно, это не конец, и я уверен, что Microsoft продолжит совершенствовать этот механизм в будущем.
Где скачать?
И наконец, главный вопрос 🙂
Microsoft впервые анонсировала и показала предварительную версию динамических массивов в Excel еще в сентябре 2018 года на конференции. Воспламенять. В последующие несколько месяцев шло тщательное тестирование и обкатка новых функций, сначала на cats сотрудников самой Microsoft, а затем и на тестировщиках-волонтерах из круга Office Insiders. В этом году обновление, добавляющее динамические массивы, стало постепенно распространяться среди постоянных подписчиков Office 365. Например, я получил его только в августе вместе с подпиской на Office 365 Pro Plus (на месяц).
Если в вашем Excel еще нет динамических массивов, но вы очень хотите с ними работать, то есть следующие варианты:
- Если у вас есть подписка на Office 365, вы можете просто подождать, пока это обновление не дойдет до вас. Насколько быстро это произойдет, зависит от того, как часто обновления доставляются в ваш Офис (раз в год, раз в полгода, раз в месяц). Если у вас корпоративный компьютер, вы можете попросить администратора настроить более частую загрузку обновлений.
- Вы можете присоединиться к числу добровольцев-тестировщиков Office Insiders — тогда вы первыми получите все новые возможности и функции (но, конечно, есть вероятность увеличения количества ошибок в Excel).
- Если у вас не подписка, а коробочная автономная версия Excel, то вам придется дождаться выхода следующих версий Office и Excel как минимум в 2022 году. Пользователи таких версий получают только обновления безопасности и исправления ошибок, а все новые «плюшки» теперь достаются только подписчикам Office 365. Печально, но факт 🙂
В любом случае, когда в вашем Excel появятся динамические массивы – после этой статьи вы будете к этому готовы 🙂
- Что такое формулы массива и как их использовать в Excel
- Суммирование окна (диапазона) с помощью функции СМЕЩ
- 3 способа транспонировать таблицу в Excel