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

Question

Учитывая приведенный выше пример листа, который имеет семантические строки 'header', 'subheader' и 'subtotal', я пытаюсь определить формулу для поиска предыдущей строки подзаголовка относительно текущей ячейки. Например, если формула была введена в 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) все равно возвращается только первый результат;AltEnterMATCH

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)as array -{5}, который не является допустимым числовым heightпараметром OFFSET(я хотел, IF(OFFSET(...)="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что значительно упрощает переход к более чем 2 столбцам.

=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 для строки-разделителя, что нормально.

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