Обща формула
=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.
