В Excel есть инструмент, который кардинальным образом изменяет привычные подходы к обработке данных с помощью формул - динамические массивы (dynamic array).
Они были анонсированы еще осенью 2018-ого года, но стали доступны пользователям только через год и только в версии офис по подписке - Microsoft Office 365.
Именно по этой причине я долгое время практически не упоминал динамические массивы в своих заметках и видео, так как офисом 365 пользуются относительно немногие, а пользователям Excel 2019 и более ранних версий, насколько мне известно, динамические массивы недоступны. Но, тем не менее, динамические массивы заслуживают пристального внимания и, возможно, их отсутствие в вашей версии программы подтолкнет вас к ее обновлению.
Фактически работа с массивами для Эксель не нова и я уже рассказывал на страницах этого блога о работе с так называемыми формулами массива. Их неоспоримым преимуществом является доступность в любой версии Excel, но они сложны для понимания, что делает формулы массива своеобразным изгоем, который реально применяют в своей работе единицы.
Динамические массивы - это не просто новый уровень формул массива, это скорее абсолютно новый подход к данным и их обработке.
Продемонстрирую сказанное на примере.
Не для кого, пожалуй, не секрет, что в любой ячейке листа мы можем сослаться на другую ячейку, чтобы получить ее значение.
Фактически на этом принципе и строилась вся работа в электронных таблицах ранее. Но с внедрением динамических массивов мы можем ссылаться не на одну ячейку, а на диапазон, что ранее бы привело к ошибке.
Сославшись на диапазон мы получаем значения этого диапазона. На то что это динамический массив указывает синяя рамка, обрамляющая значения.
Само это действие в Excel называют переносом (spill), а сам итоговый диапазон - диапазоном переноса (spill range).
Связь между диапазонами такая же как и раньше с обычными ссылками на ячейки - при изменении значения в основной таблице меняется и значение в динамическом диапазоне. При этом обратите внимание на то, что во всех ячейках динамического диапазона фигурирует одна и та же ссылка (A5:B8), но только в его первой ячейке (верхней левой), то есть в ячейке, в которую мы вводили формулу, ссылка активна и мы ее можем редактировать.
В других ячейках ссылки отображаются серым цветом, что указывает на то, что они наследуются из первой ячейки диапазона и их нельзя изменить или удалить. Если нужно скопировать или перенести диапазон переноса, то эти действия необходимо делать с основной ячейкой, а именно с левой верхней.
Ну и поскольку при задании массива использовались относительные ссылки, то и при работе с ним будут сохраняться те же правила, что и при работе с ячейками, в которых находятся обычные ссылки на другие ячейки листа. Так при копировании динамического диапазона в соседнюю ячейку, относительные ссылки изменятся и в результате мы получим диапазон, который будет смещен на то же расстояние, что и главная ячейка диапазона переноса.
Со значениями в отдельных ячейках динамического массива мы можем работать также, как и с любыми другими ячейками на листе, то есть можем задействовать их в формулах или ссылаться на них из других ячеек. Но вся мощь динамических массивов раскрывается тогда, когда мы задействуем их в формулах целиком. И давайте рассмотрим несколько примеров.
Таблица умножения
Начну я с классической задачи - создания таблицы умножения. На ее примере обычно демонстрируют принцип работы абсолютных ссылок и формул массива. Теперь с динамическими массивами эта задача решается элементарно - устанавливаем табличный курсор в первую ячейку будущей таблицы умножения, вводим знак равенства, выбираем диапазон с заголовками столбца или строки и умножаем его на аналогичный диапазон. Все!
После нажатия клавиши Enter будет создан динамический массив с рассчитанными значениями.
Применение в формулах
В заметке, посвященной функции СУММЕСЛИ я рассматривал вот такой пример.
В нем нужно было подсчитать общую сумму заказов указанного покупателя. Мы можем дополнить формулу динамическим массивом, чтобы рассчитать значения сразу для всех покупателей. Для этого вручную создам список покупателей в ячейках ниже и задействую функцию СУММЕСЛИМН.
Укажем диапазон суммирования (столбец "Стоимость заказа"), затем условие - в столбце "Покупатель" будем искать значения из вспомогательного диапазона с наименованиями покупателей. Так как мы не будем формулу протягивать на другие ячейки диапазона, а получим в результате динамический массив, то можем не фиксировать ссылки на диапазоны в формуле, то есть необязательно в данном случае превращать относительные ссылки в абсолютные. В итоге одной формулой решим сразу три задачи.
В этой формуле мы задействовали ограниченный диапазон и на выходе получили динамический массив, но максимально «раскрыться» динамическим массивам позволяют функции, которые также появились с их внедрением в Excel. Эти функции позволяют очень просто решать задачи, для которых ранее приходилось создавать сложнейшие формулы с несколькими вложенными функциями или использовать формулы массива. Наиболее часто используемые функции динамических массивов я уже рассматривал ранее при создании выпадающего списка с поиском. И сейчас давайте рассмотрим использование одной из таких функций.
Функция УНИК
В рассмотренном примере я вручную создал диапазон уникальных значений с наименованиями покупателей. Их всего три и сделать это было просто. Если же наименований будет с десяток или больше, то задача значительно усложнится, а решить ее можно очень просто с помощью функции динамических массивов УНИК.
У функции единственный обязательный аргумент - это диапазон с данными. Выбираем значения столбца покупатель и получаем результат буквально за несколько секунд.
Особенности динамических массивов
И тут самое время рассказать о некоторых нюансах, которые обязательно следует знать при работе с динамическими массивами.
Во-первых, динамический массив будет создан только в том случае, если ему «хватает» свободных ячеек. То есть если на месте будущего динамического массива есть непустая ячейка, то появится ошибка #ПЕРЕНОС! (SPILL).
Для примера создам формулу с функцией УНИК на месте ранее созданного вручную списка, чтобы заменить ранее вручную введенные значения на динамический массив. Так как две ячейки ниже уже заполнены, то в ячейке с формулой возникла ошибка.
Если очистить пространство для динамического массива, то есть удалить ранее введенные значения в ячейки ниже, то ошибка исчезнет и динамический массив будет создан.
Та же ошибка появится в том случае, если на пути динамического массива возникнут объединенные ячейки. Это еще один довод не использовать объединение ячеек на листах, на которых происходит обработка данных.
И еще один нюанс, упрощающий работу с динамическими массивами. Сейчас в формуле фигурирует классическая ссылка на диапазон ячеек с наименованиями покупателей.
Мы знаем, что этот диапазон теперь является динамическим, а значит можем сослаться на динамический диапазон следующим образом - указываем ссылку на главную ячейку динамического диапазона ( это та ячейка, в которую мы вводили формулу), а затем ставим знак решетки #.
Такое обозначение даст понять Excel что нужно задействовать весь динамический диапазон. Более того, сам Excel использует такое обозначение при указании динамических массивов. Если в любой ячейке листа сослаться на динамический массив, выбрав все его ячейки, то вместо привычных ссылок на диапазон Excel подставит обозначение динамического массива - ссылку на главную ячейку и знак решетки.
Ну и еще один важный момент касается умных таблиц. Динамические массивы и их функции нельзя использовать внутри умных таблиц. Это приведет к появлению ошибки ПЕРЕНОС, что вполне логично, ведь принцип работы умных таблиц построен на автоматическом дублировании формулы во все последующие ячейки. Это означает, что попытка создать внутри умной таблицы динамический массив приведет к тому, что формула или ссылка будет продублирована ниже, а динамический массив не может быть перенесен на не пустые ячейки, что и вызовет ошибку ПЕРЕНОС.
Однако создавать динамические массивы на основе данных умных таблиц можно и, зачастую, это даже предпочтительнее.
Также может возникнуть вполне резонный вопрос - что будет с таблицами, в которых используются динамические массивы, если их открыть в более ранних версиях Excel, где динамических массивов нет. В этом случае динамические массивы будут преобразованы в обычные формулы массива. Но нужно понимать, что совместимость эта ограниченная, то есть если вы задействуете обычные функции, вроде СУММЕСЛИ, то преобразование в формулу массива сработает. Если же в рабочей книге используется новые функции динамических массивов вроде функции УНИК, то совместимости между версиями не будет.
Ну и в последующих заметках и видео я расскажу о различных вариантах использования динамических массивов и их функций.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
