Как OFFSET должен работать с отфильтрованными данными EXCEL?

Question

Рассматривать:

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 2 | Paris         |
+---+---------------+
| 3 | London        |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+
| 7 | Amsterdam     |
+---+---------------+
| 8 | Copenhagen    |
+---+---------------+
| 9 | Amsterdam     |
+---+---------------+

Предположим, я отфильтрую столбец A, чтобы найти города Италии. Следующие результаты возвращаются.

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+

Теперь рассмотрим следующее выражение:

=IFERROR(VLOOKUP($AR5,Copy!$I$1:$K$3,2,0),OFFSET($A$1,2,0))

Относится ли OFFSET к ВСЕМ исходных данных - в этом случае моим значением по умолчанию в случае ошибки будет London - или только набор результатов - в этом случае моим значением по умолчанию в случае ошибки будет Firenze?

Я вижу противоречивые результаты в своем рабочем листе, и что нужно сузить варианты отладки, не понимая, что должно произойти в этом случае.

Answer 1

Сначала касаемо формул отладки в целом:

Лучший способ сделать это - использовать функциональность Evaluate formula (на вкладке Formula ). Это дает вам очень хороший способ увидеть, что происходит на каждом шаге / параметре. Кроме того, если вы выделите часть в своей формуле (например, полная формула OFFSET), нажмите F9. Это позволит оценить выделенный оператор в строке формул.

OFFSET вернет London , так как OFFSET также рассмотрит скрытые ячейки.

Если вы хотите вернуть вторую видимую ячейку, вам нужен небольшой трюк с дополнительным столбцом: поместите формулу =SUBTOTAL(3,A1)+C1 в C2 (при условии, что ваша таблица начинается в строке 1 и столбец C доступен. Затем скопируйте формулу вниз. Теперь он покажет вам счетчик для всех видимых ячеек, то есть числа будут меняться в зависимости от вашего автофильтра.

Теперь формула =INDEX(A:A,MATCH(2,C:C,0)) вернет вам второй видимый элемент.

Answer 2

Я не верю, что OFFSET() исключает скрытые / отфильтрованные данные. Из приведенного ниже примера видно, что даже когда часть данных скрыта / отфильтрована, формула в C1 указывает на одну и ту же ячейку.

Чтобы получить 3-й видимый элемент из вашего списка, вы можете использовать эту формулу массива ¹:

=INDEX(xmen,
       SMALL(IF(SUBTOTAL(3,OFFSET(xmen,ROW(xmen)-ROW(A2),0,1)),ROW(xmen)),3)-1,1)

Куда:

xmen → относится к вашему диапазону данных, исключая любые заголовки (A1:A9 в вашем примере, A2:A9 в приведенном ниже примере)
A2 → указывает на первый элемент в вашем диапазоне (без заголовка)

Например:

-
¹ Должен быть зафиксирован / введен с помощью Ctrl + Shift + Enter после его интеграции с формулой VLOOKUP

Answer 3

Будьте проще с этим:

=VLOOKUP(SUBTOTAL(5,A:A),A:B,2,FALSE)

Он находит наименьшее видимое число (промежуточный итог 5 является минимальным) и vlookup значение (которое также принимает первое значение, найденное с этим индексным номером).