【 Excel 】エクセルで日付の範囲を指定する条件の書くときのまとメモ

エクセルで if 関数や sumif 関数、sumifs 関数などで日付の条件を指定する際のメモです。

登場する例ではだいたい sumifs 関数を使ってます！

例では以下のような状況になっています。

日付ごとに値1、値2、値3があり、それらがテーブルとしてまとめられている。

テーブル名を「テーブル1」としている。

テーブル名の確認・変更は「数式タブ」の「名前の管理」から出来ます。

日付は書式も「日付」になってる。「セルの書式設定」の「表示形式タブ」で設定できます。

セルの書式設定はショートカットが便利です。

セルの書式設定のショートカットキー

Ctrl + 1

そのまま関数の引数に日付を書く場合
指定する日付について別のセルを参照する場合
- セルに年月日がすべて入ってる
- 年月日がそれぞれ別々のセルにある
年と月だけを指定

そのまま関数の引数に日付を書く場合

値1の 2021年2月1日から 2021年2月28日までの合計を求めたいとします。

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">=2021/2/1", テーブル1[日付], "<=2021/2/28")

日付の条件指定の際、以降や以前、より前、より後といった範囲を指定する場合は引数の条件部分は文字列 (ダブルクオート ” ” で囲む) にしてあげないとダメなようです。否定 (<>) の場合も。イコールの時だけ演算子は無しでもOK。

G4セル (74と表示されているところ) に上で書いた sumifs 関数の式が入っています。

他の文字列の形式

条件に指定する日付の形式は日付と認識できればOKっぽいのでいろいろ試してみました！

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">=2021-2-1", テーブル1[日付], "<=2021-2-28")

これはOK！

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">=2021~2~1", テーブル1[日付], "<=2021~2~28")

これはNG。。。年月日の区切りがチルダではダメなようだ。

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">=2021年2月1日", テーブル1[日付], "<=2021年2月28日")

これはOK！

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">=2021ねん2がつ1にち", テーブル1[日付], "<=2021ねん2がつ28にち")

これはえぬじー。ひらがなではだめなようだ。

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">=2021y2m1d", テーブル1[日付], "<=2021y2m28d")

これもNG。。。それっぽく ymd とやってみたけどダメでした。

いろいろと試しましたが、普通にスラッシュやハイフン使えば問題ないです(笑)

指定する日付について別のセルを参照する場合

値1の 2021年2月1日から 2021年2月28日までの合計を求めたいときに、先ほどのように引数に直接書いてもいいのですが、対象の期間を変更したくなったときは少しめんどいす。

ですので、合計したい対象の期間を別のセルで指定します。期間の開始日と終了日をそれぞれ別セルに書いておき、それらを sumifs 関数から参照します。

セルに年月日がすべて入ってる

いまG3 セルに開始日が、G4 セルに終了日が入っています。

集計期間を指定するセルを用意したので、今回は G6 セルに sumifs 関数が入っています。

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">="&G3, テーブル1[日付], "<="&G4)

条件部分は文字列でないとダメなので、このような & 演算子でつないだ形になってます。

+ 演算子は足し算、– 演算子は引き算を表すように、& は文字列の結合を意味します。

1 + 4 = 5
15 – 8 = 7
ITO & room = ITOroom

なにより「文字列」 & 「文字列じゃないやつ」 = 「文字列」と暗黙の型変換的なことをやってくれるので非常にありがたい！

年月日がそれぞれ別々のセルにある

先ほどの例と違って、対象期間を指定する日付の年月日が別々のセルに入っているときは、さらにひと手間いります。

いまは G3, G4 セルに年 (year) が、I3, I4 セルに月 (month) が、K3, K4 セルに日 (day) が入っていまーす。

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">="&DATE(G3, I3, K3), テーブル1[日付], "<="&DATE(G4, I4, K4))

date 関数というものを使って年月日から日付データを作り出しています。日付データを作るというとなんか違和感がありますが、エクセル (というかだいたいのコンピューター) では日付データは数値 (シリアル値、エポック時間など) で表現しています。エクセルの場合、デフォルトでは 1900年1月1日を 1 として数えて下の表のようになっています。試しにエクセルで 1 と入力して、入力したセルの表示形式を「日付」に変更してみてください！

シリアル値	年月日
1	1900年1月1日
2	1900年1月2日
3	1900年1月3日
…	…
44442	2021年9月3日
…	…
77679	2112年9月3日
…	…

つまり、2021年9月3日は 1900年1月1日から数えて 44,442 日目になるということです。

話は逸れましたが、条件部分は date 関数で作った日付データと “<=” や “>=” を & でくっつけて文字列にしています。

条件部分の引数には最終的に文字列として値をセットできればいいので次のようにしても OK です。

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">="&G3&"/"&I3&"/"&K3, テーブル1[日付], "<="&G4&"/"&I4&"/"&K4)

ただ、非常に見づらいのでオススメはできません。。。どうしても date 関数を使うわけにはいかないときはこれでもいいかもしれません(笑)

年と月だけを指定

いままで「何日から何日まで」と期間の開始日と終了日を指定するタイプの範囲について書きましたが、実際に何か集計する場合は「何年の何月」といったように年 (year) だけや月 (month) だけを指定することも多いと思います。

ということで、2021年2月の合計を知りたいとき。

G3 セルに年、G4 セルに月がセッティングされています。

=SUMIFS(テーブル1[値1], テーブル1[日付],  ">="& DATE(G3,G4,1), テーブル1[日付], "<="& DATE(G3,G4+1,0))

sumifs 関数はこんか感じになります。またまた date 関数を使います。さっきの年月日がそれぞれ別々のセルにある場合と似ている！

理屈としては、年と月から開始日と終了日を求めて条件にするということ。

開始日は年と月が分かればその月の 1日とすればいいので簡単です。

ですが、終了日は2021年2月なら28日、3月なら31日、4月なら30日と月の最終日は月によって異なるで工夫がいります。

先ふぉど登場したように、エクセル内部では日付データはシリアル値として管理されています。そして、ある年のある月の最終日とその翌月の0日は同じシリアル値です。

数式っぽく書くと、X 年 Y 月最終日と X 年 Y+1 月 0 日は同じシリアル値。

具体例で書くと、2021年2月28日と2021年3月0日は同じシリアル値です。

なので、 DATE(G3,G4+1,0) となっています。

2021年3月0日を見ていると、「ぼくのなつやすみ」の8月32日を思い出しました。懐かしいです。。。