「月別シートに情報を入力しているので、自動で集計を取れるようにしたい」
「月のデータをGoogleスプレッドシートで管理したい」
このように感じたことはありませんか？
Googleスプレッドシートを用い、月別シートに情報の入力を行っている、またはこれから年間集計を作る予定のある方はぜひ最後までご覧ください。
年間の情報をひとつのシートにまとめる
結論からお伝えすると、各月別入力シートの情報をひとつのシートにまとめることで、期間を指定して集計を取ったり、担当者や商品別に集計をとったりと、好きな形で集計を取れるようになります。
まず、月の入力シートへの最大入力行を決める必要があります。
今回は、月間最大１００行まで情報を入力できる表（シート）を作りました。
最大入力行の設定は、「おそらく最大５０行あれば足りるな」と感じたら１００行入力できるようにするなど、余裕をもたせておくことをお勧めします。
次に、各月シートからQUERY関数で年間の情報をまとめます。
まとまった年間シートから各集計を取ることで、任意の期間や種別ごとの集計を取ることが可能になります。
次の項から詳しく解説していきます。
月別シートを年間シートにまとめる方法
今回は、果物の売上を月別シートに入力することで、集計を取ることができるGoogleスプレッドシートを用意しました。
月別シート作成・入力ルールを設定し、年間のデータをQUERY関数を用い羅列します。
また、INDIRECT関数を用いることで、シート追加時にも自動で集計が取れるようにしています。
月別シートのルール定義
月別シートのルールを確認し、月別シートを見ていきましょう。
月別シートのルール
- 最大100行まで入力可能とする。
- データ入力時はかならず日付を入力するものとする。
- シート追加は、ひな形からコピーし、シート名を「半角数字＋月」とする。
このような形で最大１００行まで入力可能な月別シートを用意しました。
シート追加時は、必ず「ひな形」シートをコピーして、シート名を「半角数字＋月」とするものとします。
年間データを羅列する
さて、月別シートを「年間データ羅列」シートに表示させる方法をお伝えいたします。
このようにA1セルにシート名「1月」を入力し、B1セルに数式を入力します。
B1セルの数式
=IF(TODAY()=””,0,IFERROR(QUERY(INDIRECT(A1&”!$B$4:$G$103″),”select * where C is not null”),””))
QUERY関数を用い、月別シートの日付項目が空白でないデータを呼び出します。
IF関数とTODAY関数を使っている理由は、シート追加時に自動反映がうまくいかないことがあるためです。
次にA100セルに「2月」と入力し、B100セルには以下数式を入力します。 こちらはB1セルをコピーし、B100セルに貼り付けでOKです。
B100セル数式
=IF(TODAY()=””,0,IFERROR(QUERY(INDIRECT(A100&”!$B$4:$G$103″),”select * where C is not null”),””))
INDIRECT関数を使い、対象シートを指定することで、「シート追加をした際の、データ羅列の自動化」を可能にしています。
上記同様に100行ごとにA列に「半角数字＋月」、B列に数式をコピー＆ペーストしていき、12月まで入力しておきます。
これで年間データをまとめることができました。
各種集計
集計は好きな形で取るのが最適と考えますが、一例として、「月別集計」「期間指定集計」「商品別集計」の作り方をご紹介していきます。
各集計は別の用途で利用しているGoogleスプレッドシートなどでも使える技になりますので、興味のある方はぜひ最後まで読んでみてください。
月ごとの集計をとる月別集計
年間でまとめたデータを、月別に集計していきます。
まずは、３行目にC列から順に１月から１２月まで「月初日付」入力していきます。
入力したらC3～N3セルを範囲選択し、ツールバー「表示」⇒「数字」⇒「カスタム数値形式」を選択し、「m”月”」と入力し適用することで、「1/1」表示を「1月」とすることができます。
次にC4セルに数式を入力していきます。
C4セル数式
=SUMIFS(‘年間データ羅列’!$E:$E,’年間データ羅列’!$C:$C,”>=”&C$3,’年間データ羅列’!$C:$C,”<=”&EOMONTH(C$3,0))
SUMIFS関数を用い、月初日付「C3セル」から月末日付「EOMONTH(C$3,0) 」までの売上個数の合計を 算出します。
同様に、C5セルに数式を入力します。
C5セル数式
=SUMIFS(‘年間データ羅列’!$G:$G,’年間データ羅列’!$C:$C,”>=”&C$3,’年間データ羅列’!$C:$C,”<=”&EOMONTH(C$3,0))
数量と同様の数式で、 月初日付「C3セル」から月末日付「EOMONTH(C$3,0) 」までの 売上金額の合計を算出します。
次にC4セルC5セルを選択した状態で、選択範囲右下にマウスカーソルを合わせ「+」マークになっている状態で、Nまでドラッグ（フィル）します。
これで月間集計の完成です。
月別の集計は、もはやマストと言ってもいいくらい使う機会が多いです。
月間集計のポイント
- 項目名に「月初日付を入力する」
- EOMONTH関数を使い、「月末日付を指定する」
- 項目数の数式を入力したらフィルを使う
期間を指定して集計をとる期間集計
集計対象期間を入力すると対象期間の集計を取れるシートの作り方を説明していきます。
まずは完成イメージを見てみてください。
E3セルに集計対象開始日、G3セルに集計対象終了日を入力するとその期間のデータを呼び出し、集計を表示するといった仕様になります。
B8セルに以下の数式を入力します。
B8セル数式
=QUERY(‘年間データ羅列’!B:G,”select * where C >= date ‘”&TEXT(E2,”YYYY-MM-DD”)&”‘ and C <= date ‘”&TEXT(G2,”YYYY-MM-DD”)&”‘”)
E5セル、F5セル、G5セルにそれぞれ以下の数式を入力します。
E5セル数式
F5セル数式
G5セル数式
これで期間指定集計は完成です。私は仕事でも指定の期間の集計はよく使いますので、皆様の参考にしていただけると思います。
期間指定集計を応用することで、指定期間の指定商品の集計など好みの集計を実装可能です。ぜひチャレンジしてみてください。
商品別の集計をとる商品別集計
年間データ羅列シートからUNIQUE関数を使い、「重複しないすべての商品」を取得し、商品ごとの集計を行います。
まずは完成イメージからみていきましょう。
次に作成方法をご紹介していきます。
重複しないすべての商品名をUNIQUE関数で取得します。
B4セルに以下の数式を入力します。
B4セル数式
=UNIQUE(‘年間データ羅列’!D:D)
個数の集計を取るため、C4セルに以下の数式を入力します。
C4セル数式
本Googleスプレッドシートでは、「商品単価マスタ」シートから商品の単価を参照しておりますので、商品の単価を取得するため、D4セルに以下数式を入力します。
D4セル数式
商品別の売上合計額を算出するため、E4セルに以下の数式を入力します。
E4セル数式
これで商品別集計シートの完成です。
重複しないすべての商品を出すためのUNIQUE関数は、Excelでは2019年ごろから使えるようになった比較的新しい関数です。現在もExcelのバージョンによっては使えない関数ですが、とても便利な関数であり、Googleスプレッドシートなら問題なく使えますので、ぜひ活用してみてください。
まとめ
いかがでしたでしょうか？
今回のポイントは、「年間データ羅列シートに月別データをすべて縦に並べる」という点と、「シートを追加しても数式をいじることなく集計が取れる」点になります。
もっと良い方法があるのかもしれませんが、私は仕事でよくこういった方法を使っております。
ここまで読んでいただき、ありがとうございました。皆様の参考になればなによりです。
コメント