На следующем рисунке показана ситуация, в которой вам необходимо выполнить 12 функций ВПР для каждого номера счета.
ВПР - это мощный инструмент, но для выполнения расчетов требуется много времени. Кроме того, формулу нужно редактировать в каждой ячейке при копировании. Третий аргумент должен измениться с 2 на 3 для февраля, затем на 4 для марта и так далее.
Один из способов решения проблемы - добавить строку с номерами столбцов. Затем третий аргумент VLOOKUP может указывать на эту строку, как показано ниже. По крайней мере, вы можете скопировать ту же формулу из B4 и вставить в C4: M4, прежде чем копировать 12 формул вниз.
Но есть гораздо более быстрый подход: добавить новый столбец B с помощью Where? как заголовок. Столбец B содержит функцию ПОИСКПОЗ. Эта функция очень похожа на ВПР: вы ищете значение в A4 в столбце P4: P227. 0 в конце похож на False в конце VLOOKUP. Он указывает, что вам нужно точное совпадение. Вот большая разница: MATCH возвращает то место, где найдено значение. Ответ 208 говорит, что A308 - это 208-я ячейка в диапазоне P4: P227. С точки зрения пересчета времени, MATCH и VLOOKUP примерно равны.
Я слышу, о чем вы думаете: «Какая польза знать, где что-то находится? Я никогда не слышал, чтобы менеджер звонил и спрашивал: «По какой строке это дебиторская задолженность?» »
Хотя люди редко спрашивают, в какой строке что-то находится, для функции ИНДЕКС может быть удобно знать эту позицию. Формула на следующем рисунке сообщает Excel, что нужно вернуть 208-й элемент из четвертого квартала: Q227.
По мере копирования этой формулы массив значений перемещается по таблице подстановки, как показано ниже. Для каждой строки вы выполняете одну функцию ПОИСКПОЗ и 12 ИНДЕКС. Функция ИНДЕКС невероятно быстрая по сравнению с ВПР. Полный набор формул вычислит на 85% быстрее, чем 12 столбцов ВПР.
Заметка
В конце 2018 года в Office 365 была представлена новая логика для ВПР, которая обеспечивает такую же высокую скорость вычислений, как показанный здесь ИНДЕКС / ПОИСКПОЗ.
