Obecný vzorec
=SUMPRODUCT((range-TIME(0,30,0))*(range>TIME(0,30,0)))
souhrn
Chcete-li sečíst celkový čas za 30 minut, vzhledem k množině časů, které představují trvání, můžete použít funkce SUMPRODUCT a TIME. V zobrazeném příkladu je vzorec v G5:
=SUMPRODUCT((times-TIME(0,30,0))*(times>TIME(0,30,0)))
kde „times“ je pojmenovaný rozsah C5: C14.
Vysvětlení
Tento vzorec používá funkci SUMPRODUCT k sečtení výsledku dvou výrazů, které poskytují pole. Cílem je sečíst pouze čas delší než 30 minut, čas „přebytek“ nebo „navíc“. První výraz odečte 30 minut od každého času v pojmenovaném rozsahu „times“:
times-TIME(0,30,0)
Výsledkem je pole jako toto:
(-0.00347222222222222;0.00694444444444444;0.00347222222222222;-0.00694444444444444;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;-0.0104166666666667)
Druhý výraz je logický test pro všechny časy delší než 30 minut:
times>TIME(0,30,0)
Tím se vytvoří pole TRUE FALSE hodnot:
(FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE)
Uvnitř SUMPRODUCT jsou tato dvě pole vynásobena dohromady, aby se vytvořilo toto pole:
(0;0.00694444444444444;0.00347222222222222;0;0.0138888888888889;0.00694444444444444;0;0.00486111111111111;0.00833333333333333;0)
Všimněte si, že záporné hodnoty v prvním poli jsou nyní nuly. Během násobení jsou hodnoty TRUE FALSE převedeny na 1 a nulu, takže FALSE hodnoty „zruší“ časy, které nejsou větší než 30 min. Nakonec SUMPRODUCT vrátí součet všech hodnot v poli, 1 hodinu a 4 minuty (1:04).
Alternativa k SUMIFS a COUNTIFS
SUMIFS sám o sobě nemůže sečíst delta časových hodnot větších než 30 minut. SUMIFS a COUNTIFS lze použít společně k získání stejného výsledku jako výše uvedený SUMPRODUCT:
=SUMIFS(times,times,">0:30")-(COUNTIFS(times,">0:30")*"0:30")
Časy nad 24 hodin
Pokud celkový čas může překročit 24 hodin, použijte tento vlastní formát času, jako je tento:
(h):mm:ss
Syntaxe hranatých závorek říká, že Excel nemá „převracet“ časy delší než 24 hodin.
S pomocným sloupem
Jak je znázorněno v příkladu, můžete také přidat pomocný sloupec pro výpočet a součet časových delt. Zkopírovaný vzorec v D5 je:
=MAX(C5-"00:30",0)
Zde se MAX používá k odstranění negativních časových delt způsobených časy ve sloupci C, které jsou kratší než 30 minut. Všimněte si, že výsledek v D15 je stejný jako výsledek v G5.
