Учитывая приведенный выше пример листа, который имеет семантические строки «заголовок», «подзаголовок» и «промежуточный итог», я пытаюсь определить формулу для поиска предыдущей строки подзаголовка относительно текущей ячейки. Например, если формула была введена в F5
, она найдет строку 2
и, если она будет введена в F17
, найдет строку 13
.
Строки условно форматируются как заголовок, подзаголовок или промежуточный итог, при наличии значений H
, S
или T
в столбце $A:$A
, то есть строка n
подзаголовка - это строка, где $An = "S"
. Теперь я хотел бы распространить эту концепцию на мои формулы.
За строкой заголовка всегда будет следовать подзаголовок (поэтому мне не нужно беспокоиться о неправильных заголовках и подзаголовках).
Я пробовал следующее:
=MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)
Это всегда возвращает строку
2
, потому чтоMATCH
возвращает первое совпадение в наборе, и я не могу ограничить высотуOFFSET
(т.е. рекурсивно, потому что предыдущее местоположение подзаголовка неизвестно);{=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}
Это также возвращает
2
, потому что даже в контексте массива (то есть с помощью Ctrl+Alt+Enter),MATCH
все еще просто возвращает первый результат;=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)
Это возвращает
0
, потому чтоIF
не ожидает массива здесь, то расширяетOFFSET($A5, 0, 0, -ROW($A5), 1)
до одного значения0
, которое не соответствует"S"
, и обрабатываетLARGE
FALSE
как число;{=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)}
Это возвращает
#VALUE
, потому что расширение массива происходит слишком рано, что оставляет-ROW($A5)
виде массива-{5}
, что не является допустимым числовым параметромheight
дляOFFSET
(я хотел, чтобыIF(OFFSET(...)="S",...)
Бит "S", ...) должен быть массивом, а не-ROW($A5)
, но Excel не может различить).
В настоящее время я нацеливаюсь на Excel 2010. Более ранние версии не применимы (хотя прямая совместимость является бонусом). Я пытаюсь избежать VBA, поскольку мне труднее распространять файлы * .xlsm, чем * .xlsx (кроме того, я уже знаю, как это сделать с VBA).
Могу ли я попробовать что-нибудь еще?