Excelに年月日のデータと金額とがあり、2018年6月分の集計をしたい場合、どのような関数を組めば良いでしょうか。
上記の例では6月は6/1…600円と6/28…110円がありTOTAL710円なのですが、そのための式です。なんとなくSUMIFだなーとは想像はつくでしょう。ただ、SUMIFって特定の値と完全一致したモノしか集計しないですよね。
まぁ実は不等号が使えるわけなんです。
たとえば上記の例で7/1以降のデータを集計したい場合。
7/10…400円、8/22…50円、8/30…430円 TOTAL … 880円ですよね。
=SUMIF(A:A,">=2018/7/1",B:B)こういう風に検索条件の引数に不等号が可能です。
また検索条件に別セルを参照したい場合
その場合は”（ダブルクォーテーション）で括った不等号と、＆（アンド）でセル参照をくっつければ可能です。
=SUMIF(A:A,">="&D2,B:B)SUMIFSで複数条件
SUMIFは単一条件しか出来ないので、○月○日以降～、という条件しか出来ません。2018/6/1以降【AND】2018/6/30以前、という条件ができれば”2018年6月度”というデータの集計が出来ますよね。
=SUMIFS(B:B,A:A,">=2018/6/1",A:A,"<=2018/6/30")SUMIFSの構文は
SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2….）なので上記のようになります。
ただ、条件の日付でベタ打ちしてしまうと別の月の集計するときに、いちいち入力修正しないといけないので面倒です。
通常は別セルに月度の表示をさせ、セル参照で対応します。
D列に月度のセルを作って、そのセルを参照した状態です。
>=2018/6/1 をD2セルで代用して、<=2018/6/30をD3未満とすることで代用しております。
まだまだ足りない。EOMONTH使おう
ただ、この状態だと最後の12月の集計が出来ないという問題が出ます。2019年1月があれば良いのですが、見栄え悪いですね。
そこでEOMONTH関数を使います。
EOMONTH関数は「開始日から起算して、指定した月だけ前あるいは後の月の最終日に対応するシリアル値を計算します。」とのこと。簡単に言うとひと月後の月末、前の月の月末などが出せる関数ですね。
=EOMONTH("2018/6/11",1)↑であれば結果は2018/7/31になります。
=EOMONTH("2018/6/20",-1)↑ であれば結果は2018/5/31になります。－1は前月となります。
この関数を活用して6/1～6/30のデータをSUMIFSで表しますと
=SUMIFS(B:B,A:A,">="&EOMONTH(D2,-1)+1,A:A,"<="&EOMONTH(D2,0))
条件1の ”>=” & EOMONTH(D2,-1)+1 というのは、D2セルは6/1ですよね。
それに1ヶ月マイナスの月末をEOMONTHで求めています。結果5/31ですね。それに +1 していますので結果としては6/1になります。
D2そのままでも良いのですが仮にD2に6/5とか6/11とかでもちゃんと6/1にしたい為です。
最終的に日付を表示させないので（下図参照）、日付にもし1日以外が入っていた場合、ちゃんと集計してくれません。
条件2の ”<=”&EOMONTH(D2,0) は分かりやすいですね。6月の末日を出すのでD2,0を指定して結果は2018/6/30。そして不等号は=を付けてやりましょう。6/30も集計に含めるため。
あとはD列が日付まで表示されているのでセルの書式設定でYYYY年M月にしてやればキレイになりますね。
