Формула, которая будет соответствовать тому, что вы ищете, будет такой:
=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
и т.д.