sumifsで同月のデータのみ合計する

たまにExcelで困るのは、

こんな感じのデータが有った場合、

日付	金額
2018/1/2	100
2018/1/3	200

１月だけのデータを集めたいとする。

今までは金額の隣に月を表示する列を追加して、

sumifsを書いていた。

=month(A1)みたいな

日付	金額	月
2018/1/2	100	1
2018/1/3	200	1

これだと更新頻度が高い場合、

毎回列を追加するのが面倒なのでどうにかsumifsないで完結したかった。

あとmonthは年の概念がないので、

2017年12月と2018年12月はどちらも12月になりその点も不便。

結果、EOMONTHを使うことで解決する。

EOMONTHを使って、月末と月初を自動で入力するとsumifsで完結する。

例えば１月のデータを合計したかったら、

1/1から1/31の期間指定でsumifsをすればいい。

sumifs(金額,日付,">="&月初,日付,"<="&月末)

んで、月初と月末を毎回入力するのはだるいので、

ここでEOMONTHを使う。

EOMONTHは対象のセルの月末を表示するもの。

例えばA1に2018/01/02が入っているとして、

EOMONTH(A1,0)なら1/31が表示される。

月末はこれでOK！

これを使って月初を表示する場合は、

EOMONTH(A1,-1)+1とする。

EOMONTH(A1,-1)は前月の月末を表示する。

A1に1月の日付が入ってた場合12月31日

前月の月末＋1なので当月の１日が表示される。

これを組み合わせて、

sumifsを使うと余計な手間をかけず集計できる。

冒頭の例だと

sumifs(金額,日付,">="&EOMONTH(指定のセル,-1)+1,日付,"<="&EOMONTH(指定のセル,-1))

指定のセルの日付が2/15とかなら2月のデータだけ集まる。

これで指定のセルを変えれば何月のデータでも集計できるので便利。

データ分析できない社員はいらない できない社員はいらないシリーズ