データを貼り付けるだけで集計できるようにする

エクセルで販売実績データの集計用のファイルを作成して、月毎にデータが追加になっていってもデータを貼り付けするだけで集計できるようにします。
ここでは、月毎にデータが追加になっても販売実績データを貼り付けるだけで集計できるように集計用データを修正します。

今回は、6月、7月と販売データが追加になっていっても簡単に集計できるようにします。

「Ⅰ-②」で作成した「◆実績データ」を修正します。

A5~E5欄に上の赤文字の計算式を入れて「◆実績データ」を作成しましたが、この計算式を販売データの無い239行目にコピーするとB、C欄が「#N/A」となってしまいます。

また、G237を「三宮店」を「三宮2号店」、I238を「横浜 本格麦」を「横浜 本格麦麦」に変更すると、同様に「#N/A」となります。

このため、店舗や商品が追加になった時だけ、「#N/A」となるように計算式を変更します。

1) B5、C5の計算式にIF関数(条件によって返す値を変える関数)を追加して、G(or I)列が空白なら“0”、そうでないなら元の計算式となるように変更します。

=IF( 論理式 , 真の場合 , 偽の場合 ) となるので、下記のように修正します。

B5欄 =IF(G5=””,0,VLOOKUP(G5,集計区分!E:F,2,FALSE))
C5欄 =IF(I5=””,0,VLOOKUP(I5,集計区分!B:C,2,FALSE))

2) A欄(SUMIFS関数を使えば不要ですが、残すことにして)も同様に修正します。

A5欄 =IF(B5=0,0,B5&” “&C5)

3) A5~C5の計算式を修正したら、A5~E5の計算式を半年とか一年間のデータをカバーできるくらいの行までコピーします。ここでは、2,000行目までとします。

4) D2、E2欄にSUBTOTALの計算式が入っていますが、これも2,000行目までとします。

5) COUNTIF関数(条件に一致するデータの個数)を使って、「#N/A」となっているデータの個数を算出する計算式を追加します。

=COUNTIF( 範囲 , 検索条件 ) となるので、B2、C2欄に下記の計算式を入力します。

B2欄 =COUNTIF(B5:B2000,#N/A) 、C2欄 =COUNTIF(C5:C2000,#N/A)

 

下のように2,000行目まで計算式が入りました。

これで、6月、7月と販売データが追加になっても、元のデータ(1~3行目に挿入してから)をF~L欄に貼り付けるだけで、集計表が出来上がります。

B2欄のフォントが変わっていますが、これは「条件付き書式」で ”0” に等しくないときは、赤文字、太字になるように設定しています。エラーを放置すると集計を間違えてしまうので目立つようにした方がいいと思います。

「三宮2号店」、「横浜 本格麦麦」を元に戻すと、エラーも修正されます。

もし、B2、C2欄が “0” 以外の時は、店舗や商品が追加となっているか、データに誤りがあるのでエラー箇所を確認して対応します。

店舗や商品が追加となっている場合は、シート「集計区分」のテーブルに追加します。

 

D2、E2の合計欄は、集計したい項目をフィルターで抽出すると計算できます。

また、フィルターをクリアした場合は総合計なので、例えば集計表の「上期 計」と数値が合っているか、検算用の計算式(集計表の下にでも)を入れておくといいと思います。

 

 

ここで説明したように、1年分のデータを集計できるように事前に準備をしておくと、毎月の集計の手間が省けますし、ミスも少なくなります。

毎回、同じ形式のデータを集計されているなら、ぜひ試してみて下さい。

月毎に地域別・種類別の集計表を作成する

エクセルで販売実績データの集計用のファイルを作成して、月毎にデータが追加になっていってもデータを貼り付けするだけで集計できるようにします。 ここでは、月毎に予実管理が出来るように販売実績の集計用データを基に集計表を作成します。

集計したい項目をデータに追加する

エクセルで販売実績データの集計用のファイルを作成して、月毎にデータが追加になっていってもデータを貼り付けするだけで集計できるようにします。 ここでは、集計したい項目を追加して集計用のデータを作成し、その追加した項目で集計します。

まず、店舗別、商品別に集計してみる

エクセルで販売実績データの集計用のファイルを作成して、月毎にデータが追加になっていってもデータを貼り付けするだけで集計できるようにします。 ここでは、単純に店舗毎、商品別といった項目毎に販売実績データを集計してみます。

予実管理表を作成する

エクセルで販売計画データの集計用データを作成し、販売実績・予測集計表と同じ形式の集計表を作成します。そして、販売金額の予実管理表を作成します。 ここでは、今までに作成した販売実績・予測集計表、販売計画集計表を基に販売金額の予実管理表を作成します。

販売計画データを集計する

エクセルで販売計画データの集計用データを作成し、販売実績・予測集計表と同じ形式の集計表を作成します。そして、販売金額の予実管理表を作成します。 ここでは、販売計画の集計表を作成します。

実績・予測集計表を作成する

エクセルで販売予測データの集計用のシートを追加して、販売予測データを貼り付けるだけで集計表が更新できるようにします。また、販売実績と販売予測から成る販売見込みの集計表を作成します。 ここでは、販売予測集計表を作成し、販売実績も足しこんだ販売実績・予測集計表を作成します。

販売予測データに集計する項目を追加する

エクセルで販売予測データの集計用のシートを追加して、販売予測データを貼り付けるだけで集計表が更新できるようにします。また、販売実績と販売予測から成る販売見込みの集計表を作成します。 ここでは、月毎の販売予測データを貼り付けるだけで集計できるように集計用データを作成します。