Vispārēja formula
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Kopsavilkums
Apkopojot kopējo ilgumu 30 minūtēs, ņemot vērā laika kopu, kas atspoguļo ilgumu, varat izmantot funkcijas SUMPRODUCT un TIME. Parādītajā piemērā formula G5 ir:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
kur "reizes" ir nosauktais diapazons C5: C14.
Paskaidrojums
Šajā formulā tiek izmantota funkcija SUMPRODUCT, lai summētu divu izteiksmju rezultātu, kas dod masīvus. Mērķis ir summēt tikai laiku, kas pārsniedz 30 minūtes, “pārpalikumu” vai “papildlaiku”. Pirmajā izteiksmē tiek atņemtas 30 minūtes no katras reizes nosauktajā laika diapazonā:
times-TIME(0,30,0)
Rezultātā rodas šāds masīvs:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Otrā izteiksme ir loģisks tests visām reizēm, kas pārsniedz 30 minūtes:
times>TIME(0,30,0)
Tādējādi tiek izveidots TRUE FALSE vērtību masīvs:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
SUMPRODUCT iekšpusē šie divi masīvi tiek reizināti, lai izveidotu šo masīvu:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Ievērojiet, ka negatīvās vērtības pirmajā masīvā tagad ir nulles. Reizināšanas laikā TRUE FALSE vērtības tiek pārvērstas par 1 un nulli, tāpēc FALSE vērtības "atceļ" laikus, kas nav lielāki par 30 minūtēm. Visbeidzot, SUMPRODUCT atgriež visu masīva vērtību summu 1 stunda un 4 minūtes (1:04).
Alternatīva ar SUMIFS un COUNTIFS
Pats par sevi SUMIFS nevar summēt laika vērtību deltu, kas pārsniedz 30 minūtes. SUMIFS un COUNTIFS var izmantot kopā, lai iegūtu tādu pašu rezultātu kā iepriekš sniegtais SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Reizes 24 stundu laikā
Ja kopējais laiks var pārsniegt 24 stundas, izmantojiet šo pielāgoto laika formātu:
(h):mm:ss
Kvadrātiekavju sintakse liek programmai Excel "nepāriet" reizes, kas pārsniedz 24 stundas.
Ar palīgu kolonnu
Kā parādīts piemērā, varat arī pievienot palīga kolonnu, lai aprēķinātu un summētu laika deltu. Lejupielādētā formula D5 ir:
=MAX(C5-"00:30",0)
Šeit MAX tiek izmantots, lai atbrīvotos no negatīvā laika deltām, ko izraisa laiki C slejā, kas ir mazāk nekā 30 minūtes. Ievērojiet, ka rezultāts D15 ir tāds pats kā rezultāts G5.
