こんにちは。さて今回は集計作業の中でも頻繁に発生する作業で
「日別のデータを月別に集計」を関数を用いて行っていきますが、ついでに項目別という条件も合わせて
集計していきたいと思います。
今回の例は、下の表に日別項目別の売上データがあり
上には月別項目別の表が用意されており、売上を関数で集計していきます。
※月別の表にはあらかじめ、○月1日という風に1日の日付を用意しておきます。
SUMIFS関数で一発解決!複数の条件を指定して加算集計出来ます。
=SUMIFS(合計対象範囲,条件範囲1,条件1,条件範囲2,条件2,…)
=SUMIFS($E$14:$E$1048576,$D$14:$D$1048576,D3,$C$14:$C$1048576,
">="&C3,$C$14:$C$1048576,"<="&EOMONTH(C3,0))
SUMIFS関数はまず、集計したいデータの列($E$14:$E$1048576)を指定します。
気をつけたいポイントとして
①範囲を常に動かしたくない際は$をつけておくことでコピペしてもズレることがなくなります。
②日別データが下に伸びる可能性があるので、エクセルの最下行(1048576)まで念のため指定しておきます。
$D$14:$D$1048576,D3
次に一つ目の条件として項目の範囲と条件の項目を指定します。
こちらでも条件範囲には$を付けましたが、条件の項目(D3)には$をつけません。
下にコピペする際D4,D5,D6…と参照をずらしたいからです。
$C$14:$C$1048576,”>=”&C3
条件2では日別データの範囲と、条件には○月1日以上となるように指定しています。
“>=”&C3
とすると、C3以上という条件になります。
$C$14:$C$1048576,”<=”&EOMONTH(C3,0)
条件3では日別データの範囲と、条件には○月1日の月末となるように指定しています。
EOMONTH(C3,0)
⇒EOMONTH(2021/5/1,0)
⇒2021/5/31 (指定した月の月末になる)
となります。
あとは月別表の下までコピペすれば完成です。
条件指定が多すぎて関数が複雑…事前準備で条件指定を少なくしてみよう
SUMIFS関数では一度に複数の条件を入れることが出来ますが、その一方でわかりにくいといったデメリットが出てきます。その際には一度に無理な条件指定を行わずに、段階的に作業を行うことで関数をシンプルに使用することも出来ます。
まずは日別データの隣に日別を月(その日の月初1日)に変えたものを用意します。
=EOMONTH(C14,-1)+1
先ほどはEOMONTHの2つ目の引数を0にしていましたが、-1にすることで
前月末になります。
前月末に+1しているので、当月初(1日)になります。
あとは下まで関数をコピペすれば、すべて行に月初の日付が入りました。
次に、さらに隣に月と項目を繋げた文字列を作成します。
=B14&D14
とするだけで繋げることができます。
こちらも下までコピペしましょう。
上の月別表の隣にも月と項目を繋げたものを用意します。
あとはこれを用いてSUMIFS関数で集計します。
=SUMIFS($E$14:$E$1048576,$A$14:$A$1048576,B3)
先ほどよりだいぶシンプルになりました。
用意した月項目の文字列を条件指定にすれば条件が1パターンのみになりました。
エクセルには複数の条件を使用した集計や検索を行うことが多いので、その際は
このように事前に条件を繋げてしまう方法がありますので、ぜひご活用ください。
この内容はyoutubeでも公開していますので気になる人はご確認ください。
応援してくれる方はチャンネル登録お願いします。
コメントを残す