Všeobecný vzorec
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Zhrnutie
Ak chcete zhrnúť celkový čas za 30 minút, vzhľadom na množinu časov predstavujúcich trvanie, môžete použiť funkcie SUMPRODUCT a TIME. V zobrazenom príklade je vzorec v G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
kde „times“ je pomenovaný rozsah C5: C14.
Vysvetlenie
Tento vzorec používa funkciu SUMPRODUCT na sčítanie výsledku dvoch výrazov, ktoré poskytujú polia. Cieľom je spočítať iba čas nad 30 minút, a to nadbytočný alebo nadbytočný čas. Prvý výraz odčíta od každého času v pomenovanom rozsahu „časy“ 30 minút:
times-TIME(0,30,0)
Výsledkom bude pole ako je toto:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Druhý výraz je logický test pre všetky časy dlhšie ako 30 minút:
times>TIME(0,30,0)
Takto sa vytvorí pole TRUE FALSE hodnôt:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Vo vnútri SUMPRODUCT sú tieto dve polia znásobené tak, aby vytvorili toto pole:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Všimnite si, že záporné hodnoty v prvom poli sú teraz nuly. Počas násobenia sa hodnoty TRUE FALSE prevedú na 1 a nulu, takže FALSE hodnoty „zrušia“ časy, ktoré nie sú väčšie ako 30 min. Nakoniec program SUMPRODUCT vráti súčet všetkých hodnôt v poli, 1 hodinu a 4 minúty (1:04).
Alternatívne s SUMIFS a COUNTIFS
SUMIFS sám osebe nemôže súčet delta časových hodnôt presahujúcich 30 minút. SUMIFS a COUNTIFS možno použiť spoločne na dosiahnutie rovnakého výsledku ako vyššie uvedený SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Časy nad 24 hodín
Ak celkový čas môže presiahnuť 24 hodín, použite tento vlastný formát času, ako je tento:
(h):mm:ss
Syntax hranatej zátvorky hovorí, že program Excel nemá „pretáčať“ časy dlhšie ako 24 hodín.
So stĺpikom pomocníka
Ako je uvedené v príklade, môžete tiež pridať stĺpec pomocníka na výpočet a súčet časových delt. Vzorec v D5, skopírovaný nadol, je:
=MAX(C5-"00:30",0)
Tu sa MAX používa na odstránenie negatívnych časových delt spôsobených časmi v stĺpci C, ktoré sú kratšie ako 30 minút. Všimnite si, že výsledok v D15 je rovnaký ako výsledok v G5.
