Üldine valem
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Kokkuvõte
Kui arvestada 30 minuti pikkune ajakogus, võite kasutada funktsioone SUMPRODUCT ja TIME. Näidatud näites on valem G5-s järgmine:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
kus "korda" on nimetatud vahemik C5: C14.
Selgitus
Selles valemis kasutatakse funktsiooni SUMPRODUCT kahe massiivi andva avaldise tulemuse summeerimiseks. Eesmärk on summeerida ainult aeg, mis on pikem kui 30 minutit, "ülejääk" või "lisaaeg". Esimene avaldis lahutab 30 minutit igast ajavahemikust nimetatud vahemikus "korda":
times-TIME(0,30,0)
Selle tulemuseks on selline massiiv:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Teine avaldis on loogiline test kõigi aegade jaoks, mis on pikemad kui 30 minutit:
times>TIME(0,30,0)
See loob massiivi TRUE FALSE väärtusi:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
SUMPRODUCTi sisemuses korrutatakse need kaks massiivi selle massiivi loomiseks:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Pange tähele, et esimese massiivi negatiivsed väärtused on nüüd nullid. Korrutamisel teisendatakse TRUE FALSE väärtused 1-ks ja nulliks, nii et FALSE-väärtused "tühistavad" ajad, mis pole suuremad kui 30 min. Lõpuks tagastab SUMPRODUCT massiivi kõigi väärtuste summa 1 tund ja 4 minutit (1:04).
Alternatiiv funktsioonidega SUMIFS ja COUNTIFS
Iseenesest ei saa SUMIFS kokku võtta ajaväärtuste delta, mis on suurem kui 30 minutit. SUMIFS-i ja COUNTIFS-i saab koos kasutada, et saada sama tulemus kui ülalolevas SUMPRODUCTis:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Kord üle 24 tunni
Kui kogu aeg võib ületada 24 tundi, kasutage järgmist kohandatud ajavormingut:
(h):mm:ss
Ruudusulgude süntaks ütleb Excelile, et see ei peaks "üle sõitma" kordi, mis ületavad 24 tundi.
Abikolonniga
Nagu näites näidatud, saate aja deltade arvutamiseks ja summeerimiseks lisada ka abistaja veeru. D5-s kopeeritud valem on järgmine:
=MAX(C5-"00:30",0)
Siin kasutatakse MAX-i negatiivse aja deltade vabanemiseks, mis on põhjustatud veeru C aegadest, mis on vähem kui 30 minutit. Pange tähele, et D15 tulemus on sama kui G5 tulemus.
