Поиск нулевого значения в одном столбце и соответствующего значения в 5-м столбце предыдущей строки

Question

У меня есть таблица, которая имеет 5 столбцов. Строки после некоторой точки будут иметь значение ноль. Мне нужно получить значение предыдущего ряда столбца 5.
Например; здесь после 4-й строки все значения становятся равными нулю. Затем мне нужно, чтобы значение 89 появилось в некоторой указанной ячейке за пределами этой таблицы.

Year    Amt1    Amt 2   Amt 3    Amt 5
2012         5       6      17      48
2013        15       0      72      83
2014        25      63       0      12
2015         0      56      75      89
   0         0       0       0       0
   0         0       0       0       0
   0         0       0       0       0

Answer 1

Формула, которая будет соответствовать тому, что вы ищете, будет такой:

=OFFSET(E2,MATCH(0,E:E,0)-3,0)

E2 - первое значение в столбце 5 (48)

OFFSET с ячейкой в начале столбца и перемещением вниз на MATCH(0,E:E,0)-3 .

MATCH(0,E:E,0) вернет строку, в которой он может найти первое вхождение 0 (параметры: найдите 0 в столбце E:E и используйте 0 чтобы обозначить точное совпадение).

-3 предназначен для учета заголовка, первой ячейки и перемещения на одну ячейку вверх, потому что MATCH вернет позицию 0 , а не позицию ячейки чуть выше нее.

Последний параметр в OFFSET - это количество столбцов, на которые должен сместиться диапазон, и, поскольку мы уже находимся в столбце E, в этом отношении движения нет.

---

РЕДАКТИРОВАТЬ: так как столбец Year являются идеальными нулями (столбец 5 содержит очень маленькие числа, округленные до 1), вы можете использовать это вместо:

=OFFSET(E2,MATCH(0,A:A,0)-3,0)

---

Понимание офсета

Допустим, вы используете формулу:

=OFFSET(A1, 1, 1)

OFFSET вернет ячейку, которая находится на 1 строку ниже и на 1 столбец после ячейки A1 , что означает ячейку B2 .

Аналогично, =OFFSET(A1, 3, 1) даст ячейку B4 .

На самом деле в OFFSET есть больше опций, а именно высота и ширина результирующего диапазона.

=OFFSET(A1, 1, 1, 2, 3)

OFFSET вернет ячейку, которая находится на 1 строку ниже и на 1 столбец после ячейки A1 , что означает ячейку B2 . Тогда высота результата будет 2, а ширина 3, что означает, что он вернет диапазон B2:D3 (если вы выберете этот диапазон с помощью мыши, вы увидите, что он будет иметь высоту 2 и ширину 3 ячейки). Однако, поскольку это массив, вы получите #N/A с этой формулой. Вы все еще можете выполнять операции с этим массивом, такие как VLOOKUP или INDEX , SUM и т.д.