Если вы хотите разрешить любое количество сбросов в начале данных и не использовать вспомогательные столбцы, формула немного сложна:
Введите следующую формулу в C1 и ctrl-enter/copy-paste/fill-down/auto-fill в оставшуюся часть столбца таблицы:
=
INDEX(
(B:B),
IFERROR(
1/(1/SUMPRODUCT(LARGE(ROW(A$1:A1)*(A$1:A1<>"res"),1))),
ROW()-1+MATCH("tmp",A1:INDEX(A:A,ROWS(A:A)),0)
)
)
Объяснение:
Функция LARGE(ROW(A$1:A1)*(A$1:A1<>"res"),1) используется для возврата наибольшего номера строки (т. Е. Ближайшей) res строки от текущей строки вверх. (SUMPRODUCT() инкапсулирующий его, предназначен для того, чтобы гарантировать, что внутренние диапазоны будут оцениваться как массивы, не требуя ввода формулы в массив.) Если нет такого не- res строк, SUMPRODUCT(LARGE(…)) значение 0
Для ненулевого значения, IFERROR(1/(1/value),expression) вычисляет значение (то есть ближе всего вверх не- res номер строка). При нулевом значении он оценивается как результат выражения (так как 1/(1/0)) приводит к #DIV/0! ошибка).
Таким образом, для каждого последовательного значения сброса, начиная с первой строки, оценивается ROW()-1+MATCH("tmp",A1:INDEX(A:A,ROWS(A:A)),0) . Это просто возвращает номер строки первого ряда, начиная с текущего ряда и далее, что является допустимым показанием температуры. ( A1:INDEX(A:A,ROWS(A:A)) - это просто диапазон от ячейки в столбце A соответствующей текущей строке, то есть A1 для формулы, введенной в C1 , до последней ячейки столбца A , INDEX(A:A,ROWS(A:A)) .)
Наконец, мы заканчиваем INDEX((B:B),row_number) который извлекает соответствующие показания температуры.
Пройдя по формуле в C10 следует прояснить вышесказанное:
ROW(A$1:A10)*(A$1:A10<>"res")
→ {1;2;3;4;5;6;7;8;9;10}*({"res";"res";"tmp";"tmp";"res";"tmp";"tmp";"tmp";"res";"res"}<>"res")
→ {1;2;3;4;5;6;7;8;9;10}*{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE}
→ {1;2;3;4;5;6;7;8;9;10}*{0;0;1;1;0;1;1;1;0;0}
→ {0;0;3;4;0;6;7;8;0;0}1/(1/SUMPRODUCT(LARGE({0;0;3;4;0;6;7;8;0;0},1)))
→ 1/(1/SUMPRODUCT(8)) (поскольку LARGE() возвращает n-е наибольшее значение, где n - второй аргумент) → 1/(1/8)
→ 1/0.125
→ 8=INDEX((B:B),IFERROR(8,…))
→ =INDEX(B:B,8)
→ =20,2
Заметки:
- Многострочная, предварительно проверенная формула фактически работает, введенная как есть.
- Скобки вокруг
(B:B) требуются только для того, чтобы заставить B:B оставаться на своей линии.
Если вам нужна более простая формула, вам нужно использовать вспомогательный столбец (D):
Введите следующую формулу в C1 и ctrl-enter/copy-paste/fill-down/auto-fill в оставшуюся часть столбца таблицы:
=IF(A1="res",IF(ROW()=1,D1,INDEX(C:C,ROW()-1)),B1)
Введите следующую формулу в D1 и ctrl-enter/copy-paste/fill-down/auto-fill в оставшуюся часть столбца таблицы:
=IF(A1="res",D2,B1)
Объяснение:
Вспомогательная колонка:
- Если текущая строка содержит значение сброса, формула в столбце
D копирует значение из ячейки непосредственно ниже.
- В противном случае он получает значение из той же строки столбца
B
- Работая от последней строки данных в столбце вверх, это приводит к оценке
D1 до первого действительного значения в столбце B
Основная колонка:
- Для строки сброса формула в столбце
C проверяет, находится ли она в первой строке, и в этом случае она использует значение из ячейки столбца D соответствующее той же строке, то есть D1 . Если формула находится в любой другой строке столбца C , она получает значение из ячейки, расположенной непосредственно выше.
- Если строка является допустимой строкой температуры, формула получает показания температуры из ячейки в столбце
B соответствующем той же строке.
