Genel formül
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
Özet
Süreyi temsil eden bir dizi zaman verildiğinde 30 dakikadan fazla toplam süreyi toplamak için SUMPRODUCT ve TIME işlevlerini kullanabilirsiniz. Gösterilen örnekte, G5'teki formül şöyledir:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
burada "zamanlar" adlandırılmış aralık C5: C14'tür.
Açıklama
Bu formül, dizileri veren iki ifadenin sonucunu toplamak için SUMPRODUCT işlevini kullanır. Amaç, sadece 30 dakikadan fazla olan zamanı, "fazla" veya "fazladan" zamanı toplamaktır. İlk ifade, "zamanlar" olarak adlandırılan aralıktaki her defadan 30 dakika çıkarır:
times-TIME(0,30,0)
Bu, şuna benzer bir dizi ile sonuçlanır:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
İkinci ifade, 30 dakikadan uzun süreler için mantıksal bir testtir:
times>TIME(0,30,0)
Bu, bir TRUE FALSE değerleri dizisi oluşturur:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
SUMPRODUCT içinde, bu diziyi oluşturmak için bu iki dizi birlikte çarpılır:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
İlk dizideki negatif değerlerin artık sıfır olduğuna dikkat edin. Çarpma sırasında, DOĞRU YANLIŞ değerleri 1'e ve sıfıra dönüştürülür, bu nedenle YANLIŞ değerler 30 dakikadan fazla olmayan süreleri "iptal eder". Son olarak, SUMPRODUCT, dizideki tüm değerlerin toplamını 1 saat 4 dakika (1:04) döndürür.
SUMIFS ve COUNTIFS ile alternatif
ÇOKETOPLA, 30 dakikadan daha büyük zaman değerlerinin deltasını toplayamaz. SUMIFS ve COUNTIFS, yukarıdaki SUMPRODUCT ile aynı sonucu elde etmek için birlikte kullanılabilir:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
24 saatin üzerindeki zamanlar
Toplam süre 24 saati aşabiliyorsa, bunu aşağıdaki gibi özel bir saat biçimi kullanın:
(h):mm:ss
Köşeli parantez sözdizimi, Excel'e 24 saatten uzun süreler "dönmemesini" söyler.
Yardımcı bir sütun ile
Örnekte gösterildiği gibi, zaman deltalarını hesaplamak ve toplamak için bir yardımcı sütun da ekleyebilirsiniz. D5'teki formül aşağıya kopyalanır:
=MAX(C5-"00:30",0)
Burada MAX, C sütunundaki 30 dakikadan az sürelerin neden olduğu negatif zaman deltalarından kurtulmak için kullanılır. D15'teki sonucun G5'teki sonuçla aynı olduğuna dikkat edin.
