У меня есть несколько тысяч значений, похожих на те, что показаны на рисунке.
Я пытаюсь суммировать значения времени в столбце K, но только если в столбце M есть значение «флаг», и только последовательные значения.
Например, все ячейки M467-M477 помечены, и при сложении они составят ~0,017 часа.
Затем ячейки M478-M480 игнорируются, затем ячейки M481-M483 суммируются, чтобы получить ~0,0036 часов и т. д. Какая автоматическая формула Excel сделает это?
Пример значений электронной таблицы
решение1
Вариант со вспомогательным столбцом.
Формула в Q2:
=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))
и в R2
=IF(Q3="",Q2,"")
Скопируйте вниз.
решение2
Если вы хотите, чтобы сумма находилась в первой строке каждой группы, вы можете использовать эту формулу:
=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")
Разбираем это:
Они INDEX(K:K,MATCH(1E+99,K:K)+1)находят последнюю ячейку в столбце K, которая имеет номер, и устанавливают ее в качестве предела диапазона ссылок. Таким образом, формула является динамической: по мере добавления новых строк формула будет корректироваться автоматически.
Затем внутри Sum мы начинаем с ячейки, которая находится в той же строке, K2 является относительной, и по мере того, как формула перемещается вниз, также перемещается и ссылка. Конечная ячейка в диапазоне задается другим INDEX/MATCH.
На этот раз мы ищем первую ячейку из строки, в которой находится формула, которая должна быть пустой в столбце M. Мы устанавливаем эту строку -1 как последнюю строку для SUM().
Функция IF() просто преобразует все, что мы хотим, в сумму, а остальное — в пустую строку.
Если вы хотите, чтобы он располагался внизу группы, то используйте следующую формулу массива:
=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")
Будучи формулой массива, ее необходимо подтвердить с помощью Ctrl-Shift-Enter при выходе из режима редактирования. Если все сделано правильно, Excel обведет {}формулу.
На этот раз мы ищем последнее пустое место в M над текущей строкой, чтобы установить первое.
ЕСЛИОШИБКА применяется к строке заголовка, если строка, расположенная чуть ниже, содержит «Флаг».
