Excel。締め日が20日なので、その20日で集計したいけど、どうしたらいい？
＜MONTH&EDATE関数・SUMIF関数＞
20日締めで集計をしたいそうなのですが、DAY関数を使って云々といっておりまして、
何かもっと簡単な方法はないですかね？
ということでしたので、
EDATE関数を使ってみるアイディアはいかがでしょうか？
ということで、次の表があります。
A列が購入日で、B列が支払金額（購入金額）という表ですね。
末締めだったらば、月ごとに集計すればいいわけなので、
これは小計を使うなど簡単に算出することが出来るのですが、
今回は、【20日締め】。
すなわち、20日より大きければ翌月が支払月となるわけです。
この区分けをするのに、効率的な方法はないですか？というのが今回のテーマ。
まずは考えてみることにしょう。
問題となるのは、21日を翌月にしたいわけですね。
例えば6月21日ならば、7月で集計できるようにする。
21日～末日までを翌月にするために加算する方法を取るとなると、
判断が複雑になってしまい、【簡単】とはいきません。
そこで、まずは、日付からマイナス20日します。
これで、6月21日は6月1日になりますが、
これでは、7月で集計することが出来ませんので、＋1か月。
要するに、ひと月後にすれば、
7月1日になるので、7月で集計することができます。
また、7月20日の場合は、マイナス20日すると、
6月末日の6月30日になり、＋1か月すれば、7月30日になるので、
7月で集計することができます。
このような動きをするように
数式を作成していけばいいということになります。
マイナス20はともかく、＋1か月をするには、
なかなか使う機会がないEDATE関数がここで大活躍します。
EDATE関数を使うと簡単に表現できます。
数式を一度に作るのもいいですが、
ステップを確認していきますので、マイナス20という列を作りました。
C3の数式は、
=A3-20
算出された数値がシリアル値で算出されてしまいますので、
表示形式を日付に変えましょう。
続いて、「ひと月後」を求めますので、D3をクリックして、
EDATE関数ダイアログボックスを表示しましょう。
開始日には、C3
月には、1
月の1は、「ひと月後」という意味になります。
OKボタンをクリックして、オートフィルで数式をコピーしましょう。
算出した結果がシリアル値になっていますので、表示形式を日付に変えます。
E列には集計するための「月抽出」を設けて算出させます。
日付から月を抽出するには、MONTH関数を使いますので、
E3をクリックして、MONTH関数ダイアログボックスを表示しましょう。
これで、月が算出できました。オートフィルで数式をこぴーしておきます。
あとは、E列で集計してあげればいいわけです。
ちなみに、数式をまとめると、次のようになります。
=MONTH(EDATE(A3-20,1))
MONTH+EDATE関数のネストで算出することもできます。
あとはSUMIF関数を使って算出していくわけですが、
G5:G8には、表示形式のユーザー定義を使って、0”月”としてあります。
こうすることによって、SUMIF関数の条件で使うことが可能になります。
では、H5をクリックして、SUMIF関数ダイアログボックスを表示しましょう。
範囲には、$E$3:$E$17
オートフィルで数式をコピーしますので、
絶対参照を忘れないようにしましょう。
検索条件には、G5
合計範囲には、$B$3:$B$17
こちらも絶対参照を忘れずに設定しましょう。
あとは、OKボタンをクリックして、
オートフィルで数式をコピーして完成ですね。
このように、何か共通点を探してあげるコトによって、
関数で対応できることもありますので、
日ごろ使っている数式も再度確認してみると、
意外な発見やスキルアップが出来るかもしれませんね。
