Незаметно для меня 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
Содержание
- Начало работы
- Функция СОРТ
- Функция СОРТПО
- Функция ФИЛЬТР
- Функция УНИК
- Комбинирование функций
- Функция ПОСЛЕД
- Функция СЛМАССИВ
- Почему формулы массива (Ctrl+Shift+Enter) такие жесткие: неявное пересечение
- Функция ПРОСМОТРX и динамические массивы
- Обычные функции, использующие динамические массивы
- Динамические массивы и функции кубов
Введение
Ранее многие люди пытались изучать формулы массива и потерпели неудачу. Майк Гирвин написал отличную книгу, чтобы объяснить, как работают формулы массива. Целые главы той книги здесь будут сведены к коротким предложениям, благодаря новым функциям.
О динамических массивах было объявлено 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. Используйте нотацию @, когда вам нужно неявное пересечение.
Добрый день. Некорректная гиперссылка в содержании на пункт 6.на Комбинирование функций. Тдет отсылка к п. 5.
Извините, оказывается по ссылке в статье сразу 2 пункта: 5+6)
Огромное спасибо за Вашу работу и ценные для изучения материалы!