Excel には、グループごとにデータを合計する SUMIF や SUMIFS といった関数がすでに用意されています。 しかし、日付と時刻の値が組み合わされている場合、それらは動作しません。 この記事では、SUMPRODUCT関数とINT関数を使用して、Excelで時間なしで日付で合計する方法を紹介します。
構文
=SUMPRODUCT( 値の範囲 * ( INT( 日付時刻の範囲 ) = 日付のセル ) )
手順
- =SUMPRODUCT()関数で開始
- 値の範囲 $C$3.XXX を含む範囲を選択するタイプ
- =Int=INT( 日付時刻の範囲) )
Step=7054$C$16
- アスタリスクで条件範囲と製品範囲値、括弧を開ける *(
- 続けてINT($B$3:B$16) で時間部分を削除
- 等号と条件範囲 =E3
- Type )) で関数を閉じて数式を終了
How
Excelは日付と時間の値を数値として保持します。 エクセルでは、履歴は1900年1月1日から始まっていると仮定し、この日付を1として受け取ります。整数が日数を表すのに対して、小数は時間を表します。 例えば；2018/1/1は43101に等しく、12:00は0.5に等しい
時間値や数値の小数部分を取り除くためにINT関数を使用することができます。 INT関数は単に数値から整数部分を返します。 この場合、
INT($B$3:$B$16) は {43292;43292;43292;43293;43293;43293;43293;43294;43294}
しかしながら、配列に対して INT 関数を使用するには、SUMPRODUCT 関数を使用しなければならないのです。 SUMPRODUCT関数は、配列式を使用せずに配列を扱うことができるため、SUMIF関数やSUMIFS関数の制限を超える利点があります。 SUMPRODUCT は、範囲 – 値の条件から配列を評価し、返すことができます。 この機能を使用して、条件範囲 – 条件ペアを解決します。
(INT($B$3:$B$16)=E3) は {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
また、引数が1つだけならSUMPRODUCT関数はその引数配列内の値の和を返します。 しかし、条件式はブール値を返します。 そこで、SUMPRODUCT関数にとって意味のある数値に変換する必要があります。 幸い、積演算子 (*) がこれを行います。
値の配列と条件の配列を掛け合わせることで、私たちの必要性に応じて生成された配列の値を得ることができます。
$C$3:$C$16*(INT($B$3:$B$16)=E3) は {1;2;3;4;0;0;0;0;0;0;0}
最後に、SUMPRODUCT は Excel で日付ごとの合計として結果を返し、これが我々が求めているものである。
=SUMPRODUCT($C$3:$C$16*(INT($B$3:$B$16)=E3)) は 2018/11/07 の日付で 10 を返します。
代替オプション
別の方法は、ピボットテーブルを使用して、値の合計以外のオプションも提供することができます。 詳細な情報は、ピボットテーブルで時間のない日をグループ化する方法
関連記事 SUMPRODUCTで加重平均を計算する方法、SUMPRODUCTで2d範囲をSUMする方法、複数の基準でSUMPRODUCTを使ってOR演算子を使用して値を合計する方法
もご覧ください。