月末を返すEOMONTH関数で日付範囲の集計が比較的スマートにできた記録【エクセル/スプシ】

エクセルやスプシには関数が多くあるけれど、「こんなことできねえかな」と調べて出会うケースがほとんどだと思います。つまり、長年やってるけど初めて出会う関数はたくさんあるわけです。必要に応じて出会うことばかり。でも、たまには「こんなことができる」を知ってから実務に反映するケースがあってもいいですよね。

そんなケースの一つになるかもしれないので、私の出会いを記録していこうと思います。知ってる人はとっくに知ってるし、知らない人はそれが必要なケースがなかっただけかもしれない。これをきっかけに「あ、これ使えるな」と気づくかも。

関数だけ紹介してもピンとこないので、使用例と合わせて紹介します。今日はEOMONTH関数の紹介です。

Excel関数＋組み合わせ術　［実践ビジネス入門講座］【完全版】第2版作業効率とクオリティがいっきに高まる究極の使いこなしテクニック【Excel 2021/2019/Microsoft 365対応】 [実践ビジネス入門講座]

作者:きたみあきこ
SBクリエイティブ

Amazon

EOMONTH関数
どんなケースで出会ったの
- 式の解説
- ピボットを使いなさいよ
他の活用例

EOMONTH関数

対象セルの月末の日付を取り出す関数です。0で当月末、-NでNヶ月前の月末、NでN月後の月末と指定できます。

eomonth(対象セル,何ヶ月後)
=eomonth(A2,0) 　←A2セルの当月末
=eomonth(A2,-1)　←A2セルの前月末
=eomonth(A2,1)　←A2セルの翌月末

どんなケースで出会ったの

日付＋明細のリストデータがあって、それをcountifsやsumifsで集計するうえで「月単位で集計したいわあ」というケースで出会いました。具体的にはめちゃくちゃ複雑で多くのデータを持つリストなのだけど、記事用に簡略化した例が以下のとおりです。例えを考えるのが下手すぎるだろ。

最初は日付の範囲をF1以上G1未満、G1以上H1未満…と組んでいたのですが、じゃあ3月はどう集計するの？4/1をベタ打ちする？4/1セルを作る？他にスマートな方法はない？と模索した結果、最終的にこんな式になりました。

=sumifs($C:$C,$B:$B,$E:$E,$A:$A,">="&$1:$1,$A:$A,"<"&(eomonth($1:$1,0)+1))

作成にあたり、こちらの記事がとても参考になりました。
Excelやスプレッドシートで一定範囲の日付をカウントしたい時。

日付範囲を指定する方法は本記事では解説しないので、こちらをご参考に…。
SUMIF・AVERAGEIF・COUNTIF関数で日付の範囲を条件にする - Officeのチカラ

式の解説

今回は数を足し込みたかったのでsumifsですが、countifsでも同様です。日付の範囲の取り方がポイント。まず条件1を選手名、条件2､3を日付の範囲としています。

=sumifs($C:$C,$B:$B,$E:$E,
sumifs(集計したい範囲,条件1の範囲,条件1,
足し算するぞ（C列を足すぞ,B列の選手名が,E列と一致するものを

次は日付ですね。F列では2024/1/1～2024/1/31を1月としてカウントしたい。

$A:$A,">="&$1:$1
A列が,1行目の日付以上

まず2024/1/1以降の条件指定。ここまでは簡単。では～1/31の部分をどう取り出すのか。EOMONTH関数で月末を指定するのですが、そのままだと"1/31　0:00:00"となり1/31のデータを正しく抽出できないので、月末の日付+1にして（2/1　0:00:00）を取り出しています。

$A:$A,"<"&(eomonth($1:$1,0)+1)

A列が,1行目の月末+1日の日付未満

これで完成。最近はセル指定じゃなくて行や列指定するのが好きです。もちろんセル指定でもOKです。

=sumifs($C:$C,$B:$B,$E:$E,$A:$A,">="&$1:$1,$A:$A,"<"&(eomonth($1:$1,0)+1))

C列を集計するよ
　条件1/B列がE列に一致して
　条件2/A列が1行目の日付以上で
　条件3/A列が1行目の日付の月末+1未満
　（F1列では選手Aの1/1以上2/1未満の数字を集計）