Formula generică
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
rezumat
Pentru a însuma cantitatea totală de timp de peste 30 de minute, având în vedere un set de ori care reprezintă durata, puteți utiliza funcțiile SUMPRODUCT și TIME. În exemplul prezentat, formula din G5 este:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
unde „ori” este intervalul numit C5: C14.
Explicaţie
Această formulă utilizează funcția SUMPRODUCT pentru a însuma rezultatul a două expresii care dau matrice. Scopul este de a însuma doar timpul mai mare de 30 de minute, timpul „excedentar” sau „extra”. Prima expresie scade 30 de minute din fiecare dată din intervalul numit „ori”:
times-TIME(0,30,0)
Acest lucru are ca rezultat o matrice ca aceasta:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
A doua expresie este un test logic pentru toate timpurile mai mari de 30 de minute:
times>TIME(0,30,0)
Aceasta creează o serie de valori ADEVĂRATE FALSE:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
În interiorul SUMPRODUCT, aceste două matrice sunt multiplicate împreună pentru a crea această matrice:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Observați că valorile negative din prima matrice sunt acum zerouri. În timpul multiplicării, valorile ADEVĂRATE FALSE sunt convertite la 1 și zero, astfel încât valorile FALSE „anulează” ori care nu sunt mai mari de 30 de minute. În cele din urmă, SUMPRODUCT returnează suma tuturor valorilor din matrice, 1 oră și 4 minute (1:04).
Alternativă cu SUMIFS și COUNTIFS
În sine, SUMIFS nu poate însuma delta valorilor de timp mai mari de 30 de minute. SUMIFS și COUNTIFS pot fi utilizate împreună pentru a obține același rezultat ca SUMPRODUCT de mai sus:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Timpi peste 24 de ore
Dacă timpul total poate depăși 24 de ore, utilizați acest format de oră personalizat, ca acesta:
(h):mm:ss
Sintaxa parantezei pătrate îi spune lui Excel să nu „răstoarne” ori mai mare de 24 de ore.
Cu o coloană de ajutor
Așa cum se arată în exemplu, puteți adăuga, de asemenea, o coloană de ajutor pentru a calcula și a suma deltelor de timp. Formula din D5, copiată, este:
=MAX(C5-"00:30",0)
Aici, MAX este folosit pentru a scăpa de deltele de timp negative, cauzate de timpii din coloana C care sunt mai mici de 30 de minute. Observați că rezultatul din D15 este același cu rezultatul din G5.
