Билл Джелен. Динамические массивы в Excel

Незаметно для меня Microsoft совершил прорыв, представив в сентябре 2018 новые возможности – динамические массивы. Ниже – перевод книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. Книги серии Straight to the Point предназначены для глубокого освещения одного аспекта Excel.

Билл Джелен – основатель сайта MrExcel.com и автор множества книг о Excel. Сайт бесплатно отвечает на более чем 30 000 вопросов в год. В моем блоге представлены три книги Джелена:

• Сводные таблицы в Microsoft Excel 2013,
• Всё о ВПР: от первого применения до экспертного уровня,
• Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Скачать заметку в формате Word или pdf, примеры в формате Excel

Содержание

1. Начало работы
2. Функция СОРТ
3. Функция СОРТПО
4. Функция ФИЛЬТР
5. Функция УНИК
6. Комбинирование функций
7. Функция ПОСЛЕД
8. Функция СЛМАССИВ
9. Почему формулы массива (Ctrl+Shift+Enter) такие жесткие: неявное пересечение
10. Функция ПРОСМОТРX и динамические массивы
11. Обычные функции, использующие динамические массивы
12. Динамические массивы и функции кубов

Введение

Ранее многие люди пытались изучать формулы массива и потерпели неудачу. Майк Гирвин написал отличную книгу, чтобы объяснить, как работают формулы массива. Целые главы той книги здесь будут сведены к коротким предложениям, благодаря новым функциям.

О динамических массивах было объявлено 24 сентября 2018 года, но даже в MS Excel 2019 они пока не представлены. Динамические массивы доступны только в Office 365. Я думаю, что парадигма покупки бессрочной лицензии на Office каждые три или шесть лет устарела, и рекомендую переходить на подписку.

Как организована эта книга. В главе 1 вы узнаете о концепции формулы, распространяющейся на соседние ячейки. Вы увидите, как непустая ячейка может блокировать разлив массива, и как это исправить. Вы услышите о неявном пересечении и о том, как нотация @ может решить эту проблему. Главы 2–8 посвящены каждой из шести новых функций и их комбинациям. Глава 9 основана на превосходном видео Джо Макдэйда. Из этой главы вы узнаете термины, которые использует команда Excel Calc: Подъем, Трансляция, Попарный подъем, Усечение массива и Неявное пересечение. В главе 10 рассматриваются два способа, с помощью которых динамические массивы делают новую функцию ПРОСМОТРX более мощной. В 11-й – приведены 24 примера объединения динамических массивов с рядом обычных функций Excel. В 12-й главе показано, как динамические массивы могут работать с  функциями кубов.

Оригинальные файлы с примерами можно загрузить с сайта автора. К каждой главе я приложу файл, адаптированный к настоящему переводу.

Глава 1. Начало работы

Формулы теперь могу разливаться

В этой главе рассматривается новая формула =A2:A20, ошибка #ПЕРЕНОС! и новый неявный оператор пересечения @.

Начнем с базовой формулы массива. Перейдите в ячейку Е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 справится с занятой ячейкой?

Excel вернет ошибку #ПЕРЕНОС! Excel сообщает, что не может вернуть массив целиком. Поэтому не вернет ни одного результата. Если выбрать контекстное меню слева от ячейки с ошибкой, можно  выделить мешающие ячейки. Возможно, их получится перенести в другое место листа.

Рис. 5. Ошибка #ПЕРЕНОС! и ее контекстное меню

Как только вы очистите ячейки, мешающие размещению массива, он автоматически разольется.

Существует несколько типов ошибки #ПЕРЕНОС! Та, что выше, называется Диапазон для переноса данных не пустой. Другие ошибки:

• Неопределенный размер. Вы не можете использовать волатильные функции, например, СЛУЧМЕЖДУ(), в качестве аргумента функции ПОСЛЕДОВ().
• Выходит за пределы листа. Вы не можете ввести функцию =СОРТ(C:C) в ячейке E2.
• Табличная формула. Вы не можете использовать функции динамического массива внутри Таблицы.
• Не хватает памяти. Вам следует ссылаться на диапазон меньшего размера.
• Разлив в объединенные ячейки. Динамический массив не может разливаться в объединенную ячейку.
• Неопознанная ошибка. Excel не может распознать ошибку.

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

Рис. 6. Динамический массив «отслеживает» Таблицу

Сравните с рис. 2. Вы преобразовали диапазон А1:С19 в Таблицу (Ctrl+T). Формула в ячейке Е3 изменилась на =Таблица1. Теперь, если вы добавите еще одну строку в Таблицу (А11:С11), формула в ячейке Е3 не изменится, а динамический массив автоматически расширится еще одной строкой (Е12:G12).

Хотя формулы динамического массива могут указывать на Таблицу, в самой Таблице использовать формулы динамического массива нельзя.

Использование нового типа ссылок на массив: E3#

Как вы захотите сослаться на массив E3:G12, но не знаете, какого он размера, добавьте оператор разлитого диапазона (#) после ячейки, содержащей формулу массива.

Например, =E3 вернет East, =E3# вернет весь массив, формула которого хранится в E3. Неофициально это называется ссылочной нотацией массива. Такая нотация поддерживается только при ссылке внутри одной книги.

Что такое неявное пересечение

Если вы введете =@C2:C11 в любой ячейке в строках со 2 по 10, формула вернет значение из столбца С той строки, в которой вы ввели формулу. Знак @ известен как неявный оператор пересечения.

Рис. 7. Используйте нотацию @, когда вам нужно неявное пересечение.