Загальна формула
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Резюме
Щоб підсумувати загальну кількість часу за 30 хвилин, задавши набір періодів часу, що представляють тривалість, ви можете використовувати функції SUMPRODUCT і TIME. У наведеному прикладі формула в G5 має вигляд:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
де "раз" - названий діапазон C5: C14.
Пояснення
Ця формула використовує функцію SUMPRODUCT для підсумовування результатів двох виразів, що дають масиви. Мета - підсумувати лише час, що перевищує 30 хвилин, "надлишок" або "зайвий" час. Перший вираз віднімає 30 хвилин від кожного разу в зазначеному діапазоні "раз":
times-TIME(0,30,0)
У результаті виникає такий масив:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Другий вираз є логічним тестом для всіх часів, що перевищують 30 хвилин:
times>TIME(0,30,0)
Це створює масив TRUE FALSE значень:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Усередині SUMPRODUCT ці два масиви множать разом, щоб створити цей масив:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Зверніть увагу, що негативні значення в першому масиві тепер є нулями. Під час множення значення TRUE FALSE перетворюються на 1 і нуль, тому значення FALSE "скасовують" часи, що не перевищують 30 хв. Нарешті, SUMPRODUCT повертає суму всіх значень у масиві, 1 годину та 4 хвилини (1:04).
Альтернатива з SUMIFS і COUNTIFS
Сам по собі SUMIFS не може підсумовувати дельту значень часу, що перевищує 30 хвилин. SUMIFS і COUNTIFS можна використовувати разом, щоб отримати той самий результат, що і SUMPRODUCT вище:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Час більше 24 годин
Якщо загальний час може перевищувати 24 години, використовуйте такий спеціальний формат часу:
(h):mm:ss
Синтаксис квадратних дужок повідомляє Excel не «перевертатися» в рази більше 24 годин.
З допоміжною колоною
Як показано в прикладі, ви також можете додати допоміжний стовпець для обчислення та підсумовування дельт часу. Формула в D5, скопійована вниз, така:
=MAX(C5-"00:30",0)
Тут MAX використовується для позбавлення від негативних дельт часу, спричинених часом у стовпці C, що становить менше 30 хвилин. Зверніть увагу, результат у D15 такий же, як результат у G5.
