Általános képlet
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Összegzés
Összefoglalva az összes időtartamot 30 perc alatt, adott időtartamot figyelembe véve, használhatja a SUMPRODUCT és TIME funkciókat. A bemutatott példában a G5 képlete a következő:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
ahol az "idők" a C5: C14 megnevezett tartomány.
Magyarázat
Ez a képlet a SUMPRODUCT függvény segítségével összegzi két tömböt adó kifejezés eredményét. A cél csak a 30 percnél hosszabb idő, a "többlet" vagy az "extra" idő összegzése. Az első kifejezés 30 percet von le minden időből a megnevezett tartományban:
times-TIME(0,30,0)
Ez egy ilyen tömböt eredményez:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
A második kifejezés egy logikai teszt, amely minden alkalommal meghaladja a 30 percet:
times>TIME(0,30,0)
Ez létrehoz egy TRUE FALSE értékeket tartalmazó tömböt:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
A SUMPRODUCT belsejében ezt a két tömböt megszorozzuk, hogy létrehozzuk ezt a tömböt:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Az első tömb negatív értékei most nullák. A szorzás során az IGAZ HAMIS értékeket 1-re és nullára konvertálják, így a HAMIS értékek "törlik" a 30 percnél nem hosszabb időket. Végül a SUMPRODUCT visszaadja a tömb összes értékének összegét, 1 óra és 4 perc (1:04).
A SUMIFS és a COUNTIFS alternatívája
Önmagában a SUMIFS nem tudja összegezni a 30 percnél nagyobb időértékek delta értékét. A SUMIFS és a COUNTIFS együtt használható, hogy ugyanazt az eredményt kapja, mint a fenti SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
24 órán át tartó idők
Ha az összes idő meghaladhatja a 24 órát, használja ezt a következő egyéni formátumot:
(h):mm:ss
A szögletes zárójeles szintaxis azt mondja az Excel-nek, hogy ne "tekerje át" a 24 óránál hosszabb időt.
Segítő oszloppal
Amint a példában látható, felvehet egy segítő oszlopot is az idő deltáinak kiszámításához és összegzéséhez. A D5 képlete, lemásolva:
=MAX(C5-"00:30",0)
Itt a MAX-ot arra használjuk, hogy megszabaduljunk a negatív idő deltáitól, amelyeket a C oszlop 30 percnél rövidebb idő okoz. Vegye figyelembe, hogy a D15 eredménye megegyezik a G5 eredményével.
