エクセル雑感
日時データから日付ごとの集計（UNIQUE,SUMIFS,GROUPBY）

「エクセルのお題」
日時データより日別合計を算出する。
この問題って既出?ですかね。普通にありがちな問題です。
D列への日付のユニー化と日付ごとの合計。
最近は関数も増えているので、いろいろな方法がありますよね。
添付のように一発スピルにも挑戦ですかね。

これは、いろいろとできるようにしておいた方が良いと思います。
エクセルの技術の問題というより、考え方の問題かなと。
こういう基本問題は、言ってみれば公式を覚えるようなものだと思います。
まずは基本を知らないと、応用も何もないですから…
スピルしない2019以前のエクセルならどうするか?
日付のユニークデータ作成方法を考えましょう。
もちろん作業列使ってください。
日時の基本は、整数部分は日で小数以下が時分です。
つまり整数部分だけ取り出せば日になることが分かります。
もちろん、YEAR,MONTH,DAY関数を使うという手もありです。
整数部分を取り出すなら、良く使われる関数がINT関数です。
=INT(A2)
これが日付です。もちろん以下でも良いです。
=DATE(YEAR(A2),MONTH(A2),DAY(A2))
この数式を16行までドラッグコピーします。
これで日付の一覧が完成です。
では、この日付の一覧をユニークな日付の一覧にします。
これは方法が複数あり、かつ、説明が大変なので、既存ページの説明で勘弁してください😅
「重複削除しユニークデータ作成」
https://excel-ubara.com/excel2/EXCEL317.html
これで、D列にはユニークな日付の一覧が作成されました。
ここまでできれば、E列にSUMIF(S)関数で集計できます。
先程の日時を日付に変換した列（仮にC列）を条件範囲にして、B列を合計すればよいですね。
=SUMIFS(B2:B16,C2:C16,D2)

ではExcel2021以降のスピル対応ではどうでしょうか。
本当にスピルは便利ですよね。使えば使うほど便利さがわかってくると思います。
そんな中で今回はUNIQUE関数です。
名前の通り、ユニークデータを作成してくれます。
先程のINT関数と組み合わせれば、一発でユニークな日付の一覧が作成できます。

次はE列にSUMIFS関数を入れれば…
あれ、今回は日時を日付にした作業列が無いぞ、どうしたら良いのだろう…
そうですね、先の2019以前のやり方では、C列で日付変換したのでそれを条件にできましたが、それがありません。
もちろん基本に戻ってC列に作業列を追加するというのも正しい対処方法です。
せっかくUNIQUE関数で一発でユニークな日付を作成できたのに、結局日付の作業列を追加するのでは、UNIQUE関数が不憫だ…
ここで、1日の範囲を考えましょう。
ある日の1日の範囲というのは、
指定日の0時以上、かつ、指定日の翌日の0時未満
指定日の翌日の0時 > 対象日 >=指定日の0時
ということです。
これをSUMIFS関数に組み込みます。
SUMIF(S)やCOUNTIF(S)での条件指定は難しいですよね。
完成した数式をみると簡単そうなのですが、いざ作ろうとすると、ちょっと手が止まってしまうかもしれません。
以下も参考にしてください。「SUMIF関数」
https://excel-ubara.com/excel1/EXCEL530.html

SUMIF(S)でどうしてこれができないのかと思う仕様があります。
「合計範囲」「条件範囲」
これらはセル範囲でなければならないのです。
配列ではダメなんですね。
INT(A2:A16)
これはセル範囲ではなく配列です。
なのでエラーになってしまうのです。
本当にこの仕様だけはどうにかしてもらいたい…

ここまで来たら残るは最新の365です。
超便利な超優秀な関数が追加されています。
GROUPBY関数です。
UNIQUE関数+SUMIFS関数=GROUPBY関数
こんな感じで考えてもらえれば良いです。
https://excel-ubara.com/excel1/EXCEL694.html
さらにPIVOTBY関数も追加されています。
「PIVOTBY関数」
https://excel-ubara.com/excel1/EXCEL695.html
=GROUPBY(INT(A2:A16),B2:B16,SUM,,0)
たったこれだけでできてしまうのです。どうです、便利でしょ。
引数が8個もあるので、細かい使い方をみていくと迷子になってしまうかもしれないので注意してね。
単純に今回のような使い方なら、そんなに難しくはないと思います。

=GROUPBY(INT(A2:A16),B2:B16,SUM,,0)
この数式のなかの第3引数、
,SUM,
この部分秀逸ですよね。
この引数は function(関数)を指定します。
これについては以下を参照してください。
「イータ縮小ラムダ（eta reduced lambda）」
https://excel-ubara.com/excel1/EXCEL696.html
以上になります。
✅2019までの非スピルバージョン
✅2021以降のスピル対応
✅365の最新関数
この3通りでのやり方を説明しました。
もちろん、方法や数式はこの他にも多数ありますので、良い方法を思いついたら実際にやってみてください。

同じテーマの記事
新着記事
アクセスランク