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

Типовой лист с формулами и без них

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

  • Объединение SUMIF () и SUBTOTAL () для динамического итога
  • Удалите повторяющиеся строки в excel, если не все столбцы одинаковы
  • Как определить, какие значения в заданном наборе ячеек будут содержать числовой диапазон
  • Excel можно заполнить две ячейки одной if-формулой
  • Формула, которая смотрит на значение в ячейке и в зависимости от того, что значение
  • Как можно выделить две отдельные ячейки с разделенными запятыми данными и соединить их?
  • Строки условно отформатированы как заголовок, подзаголовок или промежуточный итог, по наличию значений 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",...) бит – это массив, а не -ROW($A5) , но Excel не может отличить).

    В настоящее время я нацелен на Excel 2010. Более ранние версии не применимы (хотя форвардная совместимость – это бонус). Я пытаюсь избежать VBA, так как мне сложнее распространять файлы * .xlsm, чем * .xlsx (к тому же я уже знаю, как это сделать с VBA).

    Есть ли другие вещи, которые я могу попробовать?

  • Excel для подсчета количества одновременных сеансов в зависимости от времени начала и окончания
  • Как я могу выполнить эту функцию с помощью VBA в excel?
  • Вычисление среднего числа блоков с переменным размером в столбце?
  • Диагностика медленных таблиц Excel?
  • Где находится «Просмотр в том же окне» в Windows 7
  • Конвертировать несколько валют в usd без использования vlookup
  • One Solution collect form web for “Найдите предыдущую строку со значением в столбце A”

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

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

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

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

    Изображение данных и результат

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

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

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

    Для данных с одной строкой вы можете использовать формулу, начиная с 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 для разделительной строки, которая является прекрасной.

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

    Введите описание изображения здесь

    Введите описание изображения здесь

    Давайте будем гением компьютера.