Изучите этот рисунок:
Предположим, вы хотите создать отчет на основе этого, как если бы вы отфильтровали регион. То есть, если вы отфильтруете по северу, вы увидите:
Но что, если вам нужна версия того же самого на основе формул?
Вот результат, который вы ищете в столбцах I: K:
Понятно, что это тот же отчет, но здесь нет отфильтрованных элементов. Если вам нужен новый отчет по Востоку, было бы неплохо просто изменить значение в G1 на Восток:
Вот как это делается. Во-первых, это не делается с помощью ВПР. Так что я соврал насчет названия этой техники!
Столбец F ранее не отображался, и его можно скрыть (или переместить в другое место, чтобы он не мешал отчету).
В столбце F показаны номера строк, в которых G1 находится в столбце A; то есть какие строки содержат значение «Север»? Этот метод включает в себя использование ячейки выше, поэтому она должна начинаться по крайней мере в строке 2. Она совпадает со значением «Север» против колонка А, но вместо того , чтобы весь столбец, используйте функцию OFFSET: OFFSET($A$1,F1,0,1000,1).
Поскольку F1 равен 0, это OFFSET(A1,0,0,1000,1)A1: A1000. (1000 произвольно, но достаточно велико для выполнения работы - вы можете указать любое другое число).
Значение 2 в F2 соответствует первому «северу». Вы также хотите добавить обратно значение F1 в конце, но пока оно равно нулю.
«Магия» оживает в ячейке F3. Вы уже знаете, что первый север находится в строке 2. Итак, вы хотите начать поиск в двух строках ниже A1. Вы можете сделать это, указав 2 в качестве второго аргумента функции СМЕЩЕНИЕ.
Формула в F3 будет автоматически указывать на 2 , который был вычислен в ячейке F2: При копировании формулы вниз, вы увидите , =OFFSET($A$1,F2,0,1000,1)что OFFSET($A$1,2,0,1000,1)что A3: A1000. Итак, вы сопоставляете Север с этим новым диапазоном, и он находит Север в третьей ячейке этого нового диапазона, поэтому ПОИСКПОЗ дает 3.
Добавив обратно значение из ячейки выше, F2, вы увидите 3 плюс 2 или 5, что является строкой, содержащей второй север.
Эта формула заполнена достаточно глубоко, чтобы получить все значения.
Это даст вам номера строк, в которых находятся все записи Севера.
Как преобразовать эти номера строк в результаты в столбцах с I по K? Все это делается с помощью одной формулы. Введите эту формулу в I2: =IFERROR(INDEX(A:A,$F2),””). Скопируйте вправо, а затем скопируйте вниз.
Зачем нужна ЕСЛИОШИБКА? Где ошибка? Обратите внимание на ячейку F6 - она содержит # N / A (вот почему вы хотели бы скрыть столбец F), потому что после строки 15 больше нет севера. Поэтому, если столбец F является ошибкой, верните пробел. В противном случае выберите значение из столбца A (и, если заполнено вправо, B и C).
$ F2 является абсолютной ссылкой на столбец F, поэтому правая заливка по-прежнему относится к столбцу F.
Это гостевая статья от Боба Умласа, MVP по Excel. Это одна из его любимых техник из его книги Excel Outside the Box.
Нестандартный Excel »
