Как получить значение ячеек из предыдущей видимой строки в формулах Excel

Question

Можно ли написать формулу в Excel, чтобы рассчитать значение на основе предыдущей видимой строки?

Применяя Filter в столбцах предыдущая видимая строка изменяется, но обычные формалы не учитывают видимость предыдущей строки, поэтому результат не изменяется при применении фильтров. Например:

Давайте исходные значения ячеек электронной таблицы:

   A | B | C | D 
1: 5   3   1
2: 9   1   1
3: 2   3   0  
4: 7   8   1   =A3-B4 equals 2-8=-6

Теперь предположим, что мы делаем фильтр на C столбец, чтобы скрыть третий ряд, поэтому мы имеем

   A | B | C | D 
1: 5   3   1
2: 9   1   1
4: 7   8   1   =A3-B4 is still equals -6 but I want to get: 9-8=1

Можно ли получить такие формулы? Большое спасибо.

Answer 1

Попробуйте эту формулу

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(A$1,ROW(A$1:A3)-ROW(A$1),0)),A$1:A3)-B4

SUBTOTAL/OFFSET part возвращает 1 или ноль для каждого значения в столбце A в зависимости от того, является ли оно видимым или нет, LOOKUP находит последний 1 (эквивалент последнего видимого значения) и дает это значение.