新人マネージャー奮闘記

新人マネージャーがあくせくしています。

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

たまにExcelで困るのは、

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

 

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

 

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

 

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

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から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なので当月の1日が表示される。

 

これを組み合わせて、

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

 

冒頭の例だと

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

 

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

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