Bendroji formulė
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Santrauka
Apibendrinant bendrą 30 minučių trukmės laiką, atsižvelgiant į laiką, nurodantį trukmę, galite naudoti SUMPRODUCT ir TIME funkcijas. Parodytame pavyzdyje formulė G5 yra:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
kur „laikai“ yra įvardytas diapazonas C5: C14.
Paaiškinimas
Ši formulė naudoja funkciją SUMPRODUCT, kad būtų galima susumuoti dviejų išraiškų, gaunančių masyvus, rezultatą. Tikslas yra susumuoti tik ilgesnį nei 30 minučių laiką, „perteklinį“ arba „papildomą“ laiką. Pirmoji išraiška atima 30 minučių nuo kiekvieno nurodyto laiko intervalo:
times-TIME(0,30,0)
Dėl to gaunamas toks masyvas:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Antroji išraiška yra loginis visų kartų, ilgesnių nei 30 minučių, testas:
times>TIME(0,30,0)
Tai sukuria TRUE FALSE reikšmių masyvą:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
SUMPRODUCT viduje šie du masyvai yra padauginti kartu, kad būtų sukurtas šis masyvas:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Pastebėkite, kad neigiamos pirmojo masyvo vertės dabar yra nuliai. Padauginus TRUE FALSE reikšmės yra konvertuojamos į 1 ir nulį, todėl FALSE reikšmės „panaikina“ laiką, kuris yra ne didesnis kaip 30 min. Galiausiai, SUMPRODUCT pateikia visų masyvo reikšmių sumą, 1 valandą ir 4 minutes (1:04).
Alternatyva su SUMIFS ir COUNTIFS
Pats SUMIFS negali susumuoti didesnių nei 30 minučių laiko verčių delta. SUMIFS ir COUNTIFS galima naudoti kartu, kad gautumėte tą patį rezultatą kaip aukščiau pateiktas SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Kartai per 24 valandas
Jei bendras laikas gali viršyti 24 valandas, naudokite šį pasirinktinį laiko formatą:
(h):mm:ss
Kvadratinių skliaustų sintaksėje nurodoma, kad „Excel“ neturėtų „pervažiuoti“ daugiau nei 24 valandas.
Su pagalbine kolona
Kaip parodyta pavyzdyje, taip pat galite pridėti pagalbinį stulpelį, kad apskaičiuotumėte ir susumuotumėte laiko deltas. D5 formulė, nukopijuota žemyn, yra tokia:
=MAX(C5-"00:30",0)
Čia MAX naudojamas atsikratyti neigiamo laiko deltų, kurias sukelia laikas, esantis C stulpelyje, mažesnis nei 30 minučių. Atkreipkite dėmesį, kad rezultatas D15 yra toks pats kaip rezultatas G5.
