Formuła ogólna
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Podsumowanie
Aby zsumować całkowity czas w ciągu 30 minut, biorąc pod uwagę zestaw czasów, które reprezentują czas trwania, można użyć funkcji SUMPRODUCT i TIME. W pokazanym przykładzie formuła w G5 to:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
gdzie „czasy” to nazwany zakres C5: C14.
Wyjaśnienie
Ta formuła używa funkcji SUMPRODUCT w celu zsumowania wyników dwóch wyrażeń, które dają tablice. Celem jest zsumowanie tylko czasu dłuższego niż 30 minut, czasu „nadwyżki” lub „dogrywki”. Pierwsze wyrażenie odejmuje 30 minut od każdego czasu w nazwanym zakresie „times”:
times-TIME(0,30,0)
Daje to tablicę taką jak ta:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Drugie wyrażenie to test logiczny dla wszystkich czasów dłuższych niż 30 minut:
times>TIME(0,30,0)
Tworzy to tablicę wartości TRUE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Wewnątrz SUMPRODUCT te dwie tablice są mnożone razem, aby utworzyć tę tablicę:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Zauważ, że wartości ujemne w pierwszej tablicy są teraz zerami. Podczas mnożenia wartości PRAWDA FAŁSZ są konwertowane na 1 i zero, więc wartości FAŁSZ „znoszą się” razy, które nie są większe niż 30 minut. Na koniec funkcja SUMPRODUCT zwraca sumę wszystkich wartości w tablicy, 1 godzinę i 4 minuty (1:04).
Alternatywa z SUMIFS i COUNTIFS
Sama funkcja SUMA.WARUNKÓW nie może sumować delty wartości czasu większych niż 30 minut. SUMIFS i COUNTIFS mogą być używane razem, aby uzyskać ten sam wynik, co SUMPRODUCT powyżej:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Razy powyżej 24 godzin
Jeśli łączny czas może przekroczyć 24 godziny, użyj tego niestandardowego formatu czasu, takiego jak:
(h):mm:ss
Składnia nawiasów kwadratowych informuje program Excel, aby nie „przewijał” czasów dłuższych niż 24 godziny.
Z kolumną pomocniczą
Jak pokazano w przykładzie, możesz również dodać kolumnę pomocniczą do obliczania i sumowania delt czasu. Wzór w D5, skopiowany poniżej, to:
=MAX(C5-"00:30",0)
Tutaj MAX jest używany do usuwania ujemnych delt czasu, spowodowanych czasami w kolumnie C, które są krótsze niż 30 minut. Zwróć uwagę, że wynik w D15 jest taki sam, jak wynik w G5.
