Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.
ПРОСМОТРX – первая новая функция после появления динамических массивов
Функции ПРОСМОТРX и ПОИСКПОЗX дебютировали для Office 365 в августе 2019 года. Они были разработаны для объединения возможностей ВПР, ИНДЕКС, ПОИСКПОЗ и динамических массивов. Ранее я писал о двенадцати удивительных преимуществах ПРОСМОТРX.[1] Здесь расскажу лишь о двух из них. Но сначала введение для тех, кто еще не знаком с ПРОСМОТРX.
В ячейке D4 вы хотите найти категорию (Category) для элемента W25-6. До появления ПРОСМОТРX, вы могли это сделать с помощью =ВПР(A4;F6:I17;4;ЛОЖЬ) или комбинации функций =ИНДЕКС(I7:I17;ПОИСКПОЗ(A4;F7:F17;0)). С ПРОСМОТРX сделать это еще проще. Вы ищете значение ячейки A4 в диапазоне F$7:F$17, и хотите вернуть соответствующее значение из I$7:I$17. Нет необходимости в аргументе ЛОЖЬ функции ВПР или 0 функции ИНДЕКС, потому что ПРОСМОТРX по умолчанию ищет точное совпадение:
Рис. 68. Простой пример ПРОСМОТРX
Скачать заметку в формате Word или pdf, примеры в формате Excel
Возврат 12 столбцов с помощью ВПР
Если вам нужно вернуть значения для каждого из 12 месяцев, вы аккуратно используете смешанные ссылки, а также функцию СТОЛБЕЦ(), чтобы задать правильное значение третьего аргумента функции ВПР: =ВПР($A3;$O$3:$AA$226;СТОЛБЕЦ(B:B);ЛОЖЬ)
Рис. 69. Хитрости использования ВПР для извлечения значений в несколько столбцов
Возврат 12 столбцов с помощью ИНДЕКС и ПОИСКПОЗ
До появления динамических массивов, возможно, лучшим решением было использование вспомогательного столбца для ПОИСПОЗ и двенадцати столбцов для ИНДЕКС. Добавьте столбец B. Используйте заголовок Где? В ячейку В6 введите формулу =ПОИСКПОЗ(A6;$Q$6:$Q$229;0). Формула вернет номер той строки в таблице поиска, где находится A308. В ячейку С6 введите =ИНДЕКС(R$6:R$229;$B6). Обратите внимание на относительные ссылки в первом и втором аргументах. При копировании вправо результат за январь автоматически изменится на февраль и т.д.
Рис. 70. Вспомогательный столбец для ПОИСКПОЗ
С динамическими массивами одна формула ПРОСМОТРX вернет все значения
В ячейке В6 введите формулу =ПРОСМОТРX(A6;$O$6:$O$229;$P$6:$AA$229). Она вернет 12 значений в диапазон В6:М6. Протяните формулу вниз вдоль столбца В.
Рис. 71. Функция ПРОСМОТРX с использованием динамических массивов
Ошибка копирования формулы динамических массивов
Обычно, если вы введете формулу в B6, выберите B6 и дважды щелкните маркер заполнения, Excel скопирует формулу во все строки столбца B. Из-за ошибки это не работает, если B6 содержит динамический массив. (Ошибка все еще актуальна по состоянию на 25 сентября 2020 года.)
Обходной путь:
- Из ячейки B6 нажмите клавишу со стрелкой влево, чтобы перейти к ячейке A6.
- Из A6 нажмите Ctrl + стрелка вниз, чтобы перейти к последней используемой ячейке в столбце A.
- Нажмите клавишу со стрелкой вправо, чтобы вернуться в столбец B.
- Нажмите Ctrl + Shift + стрелка вверх, чтобы выбрать все ячейки столбца В вплоть до ячейки B6.
- Нажмите Ctrl + D, чтобы скопировать формулу в верхней части выделения во все ячейки выделения.
Двухкоординатный поиск с ИНДЕКС и ПОИСКПОЗ
Выполнение двухкоординатного поиска – еще один сценарий, в котором ИНДЕКС + ПОИСПОЗ выигрывают у ВПР.[2] Рассмотрим следующий пример. Пользователь может выбрать счет в B3 и месяц в B4. Нужна формула, чтобы найти значение на пересечении счета А621 и месяца май.
На первом шаге ищем A621 в диапазоне номеров счетов. =ПОИСКПОЗ(B3;A9:A24;0) возвращает 12, т.е., A621 находится в 12-м ряду номеров счетов. Далее, =ПОИСКПОЗ(B4;B8:G8;0) возвращает 5, т.е., май – пятый месяц. Теперь, вы знаете, что вам нужна 12-я строка и 5-й столбец массива B9:G21: =ИНДЕКС(B9:G24;ПОИСКПОЗ(B3;$A$9:$A$24;0);ПОИСКПОЗ(B4;$B$8:$G$8;0))
Рис. 72. ИНДЕКС + ПОИСПОЗ для двухкоординатного поиска
Двухкоординатный поиск с ПРОСМОТРX
Формула на основе функции ПРОСМОТРX будет короче, но… сложнее для понимания. Итак, вы хотите найти A621 в списке учетных записей, поэтому формула будет начинаться с =ПРОСМОТРX(B3;A12:A27;…). Но теперь самое сложное. Как вам получить аргумент просматриваемый_массив?
Вы используете тот же трюк, который применили для возврата 12 месяцев, но транспонированный. В следующей формуле ПРОСМОТРX выполняет горизонтальный поиск: =ПРОСМОТРX(B4;B11:G11;B12:G27). Обычно вы используете одну строку в качестве аргумента просматриваемый_массив. И ПРОСМОТРX возвращает одно значение. Если же в качестве аргумента просматриваемый_массив ввести прямоугольный диапазон, ПРОСМОТРX вернет один столбец из 16 строк.
Этот промежуточный результат можно увидеть диапазоне I12:I27. Если в ячейке B4 изменить май на февраль, то формула вернет числа из столбца C12:C27. Трюк состоит в том, чтобы использовать внутренний ПРОСМОТРX для получения значений, подставляемых в аргумент просматриваемый_массив внешнего ПРОСМОТРX.
Рис. 73. Формула на основе ПРОСМОТРX короче, но сложнее
[1] Рассказ от первого лица – автора книги Билла Джелена. – Прим. Багузина
[2] Не знаю, насколько выигрывает… Вот формула с ВПР, которая решает эту задачу: =ВПР(B3;A8:G24;ПОИСКПОЗ(B4;A8:G8;0);ЛОЖЬ)