doodle-on-web

自分で調べたことや、仕事の中で質問されたことなどをまとめています。

Excelで指定時間以上の時間のみ合計したい

スポンサーリンク

例:30分以上の合計時間を計算したい

作業に30分以上かかっている人の、その超過分だけ計算したいのですが。。。

f:id:doodle-on-web:20190829163812p:plain

関数例

= SUMPRODUCT((範囲- TIME(0 、30 、0 ))* (範囲> TIME(0 、30 、0 )))

説明

期間を表す一連の時間を指定して、30分間の合計時間を合計するには、SUMPRODUCT関数とTIME関数を使用できます。示されている例では、G5の式は次のとおりです。

= SUMPRODUCT((times- TIME(0 、30 、0 ))* (times> TIME(0 、30 、0 )))

ここで、「times」は名前付き範囲 C5:C14です。

この式の仕組み

この式は、SUMPRODUCT関数を使用して、配列を生成する2つの式の結果を合計します。目標は、30分を超える時間、つまり「余剰」または「余分な」時間のみを合計することです。最初の式は、名前付き範囲「times」内のすべての時間から30分を減算します。

time - TIME(0 、30 、0 )

これにより、次のような配列が生成されます。

{ - 0.00347222222222222。0.00694444444444444 ; 0.00347222222222222 ; - 0.00694444444444444 ; 0.0138888888888889 ; 0.00694444444444444 ; 0 ; 0.00486111111111111 ; 0.00833333333333333 ; - 0.0104166666666667 }

2番目の式は、30分を超えるすべての時間の論理テストです。

回> TIME(0 、30 、0 )

これにより、TRUE FALSE値の配列が作成されます。

{ FALSE ; TRUE ; TRUE ; FALSE ; TRUE ; TRUE ; FALSE ; TRUE ; TRUE ; 偽}

SUMPRODUCT内では、これらの2つの配列を乗算してこの配列を作成します。

{ 0 ; 0.00694444444444444 ; 0.00347222222222222 ; 0 ; 0.0138888888888889 ; 0.00694444444444444 ; 0 ; 0.00486111111111111 ; 0.00833333333333333 ; 0 }

最初の配列の負の値がゼロになっていることに注意してください。乗算中、TRUE FALSE値は1と0に変換されるため、FALSE値は30分を超えない「キャンセル」時間です。最後に、SUMPRODUCTは、配列内のすべての値の合計である1時間4分(1:04)を返します。

SUMIFSおよびCOUNTIFSの代替

それ自体では、SUMIFSは30分を超える時間値のデルタを合計できません。SUMIFSとCOUNTIFSを一緒に使用して、上記のSUMPRODUCTと同じ結果を得ることができます。

= SUMIFS( times 、 times 、"> 0:30" )- (COUNTIFS( times 、"> 0:30" )* "0:30" )

24時間以上の時間

合計時間が24時間を超える場合は、次のようなカスタムの時間形式を使用します。

[ h ]:mm:ss

角括弧構文は、Excelに24時間を超えて「ロールオーバー」しないように指示します。

ヘルパー列付き

例に示すように、ヘルパー列を追加して、時間の差分を計算および合計することもできます。コピーされたD5の式は次のとおりです。

= MAX( C5 - "00:30" 、0 )

ここでは、列Cの時間が30分未満であることに起因する負の時間差を取り除くためにMAXが使用されます。D15の結果はG5の結果と同じであることに注意してください。