Найти предыдущую строку с конкретным значением в столбце A

Question

Учитывая приведенный выше пример листа, который имеет семантические строки «заголовок», «подзаголовок» и «промежуточный итог», я пытаюсь определить формулу для поиска предыдущей строки подзаголовка относительно текущей ячейки. Например, если формула была введена в F5 , она найдет строку 2 и, если она будет введена в F17 , найдет строку 13 .

Строки условно форматируются как заголовок, подзаголовок или промежуточный итог, при наличии значений H , S или T в столбце $A:$A , то есть строка n подзаголовка - это строка, где $An = "S" . Теперь я хотел бы распространить эту концепцию на мои формулы.

За строкой заголовка всегда будет следовать подзаголовок (поэтому мне не нужно беспокоиться о неправильных заголовках и подзаголовках).

Я пробовал следующее:

1. =MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)

   Это всегда возвращает строку 2 , потому что MATCH возвращает первое совпадение в наборе, и я не могу ограничить высоту OFFSET (т.е. рекурсивно, потому что предыдущее местоположение подзаголовка неизвестно);

2. {=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}

   Это также возвращает 2 , потому что даже в контексте массива (то есть с помощью Ctrl+Alt+Enter), MATCH все еще просто возвращает первый результат;

3. =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 как число;

4. {=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).

Могу ли я попробовать что-нибудь еще?

Answer 1

Самый простой способ сделать это - обмануть и использовать смешанную абсолютную / относительную формулу. Это формула массива (введите с помощью CTRL+SHIFT+ENTER), введенная в ячейку B4 но она может находиться в любом месте строки 4. Он вернет номер строки с пометкой S

=MAX(IF($A$1:A4="S",ROW($A$1:A4)))

При копировании вторая часть ссылок B4 and A4 увеличится. Это гарантирует, что вы получите строку с наибольшим соответствием, которое находится над текущей строкой. Вы можете вводить эти формулы быстрее, используя F4 после ввода / выбора соответствующего диапазона. Это будет циклически повторять знаки доллара во всех вариантах.

Изображение диапазонов

Используется для замены ваших формул

Немного прочитав вопрос (и основываясь на правке @SteveTaylor), кажется, вы используете это для обновления своих формул. Вы можете использовать строку, возвращаемую сверху вместе с INDEX чтобы получить диапазоны данных для суммирования. Я вижу 2 формулы, которые можно заменить:

• Общий расчет для каждой помеченной строки данных. В этом случае на строку промежуточного итога выше можно ссылаться динамически.
• Итоговый расчет по итоговой строке. В этом случае значения для суммирования сверху могут ссылаться динамически.

Для данных одной строки вы можете использовать формулу, начиная с F3 в качестве формулы массива. Обратите внимание, что я переключился на использование SUMPRODUCT что значительно упрощает переход к более чем двум столбцам.

=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)

Для общей формулы строки вы можете использовать, начиная с F11 , снова формулу массива:

=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))

Если вы хотите, чтобы одна формула управляла ими всеми! затем вы можете объединить их во вложенный IF на основе значения в столбце A Здесь указана формула массива, начиная с F2 которую можно скопировать вниз.

=IF(
  A2="S", 
  SUM(D2:E2), 
    IF(A2="T", 
      SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))), 
      C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))

Эта формула не делает различий между пустой строкой и строкой "данных". В настоящее время он возвращает 0 для строки распорки, что нормально.

Изображение результатов и формул для двух блоков ваших данных.