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

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

「Ⅰ」ではお酒屋さんの2016年の4月、5月の販売実績の集計を行いましたが、下のような当月以降の販売予測データもあったとします。

同じお酒屋さんなので、当然、店舗も商品も実績データと同じです。

ただ、実績データの方は4月実績の下に5月実績が追加されていましたが、この予測データは横方向のデータとなっています。

「Ⅰ-②、④」と同じように集計しやすいようにデータを作っていきます。

1)元の販売予測データのシートをコピーして、シート名を「◆予測データ」としています。

2)1~3行目に行挿入、A~E列に列挿入をします。

3)この予測データは当月からの予測データなので、今回は6月からのデータですが、翌月は
7月からのデータとなります。
このため、実績データがある月分を挿入して、毎回、同じ月が同じ列となるようします。

4)A4~E4に右の項目名を入力。 「地域+区分」 「地域」 「区分」 「上期 数量」 「上期 金額」

5)B5、C5欄には、「Ⅰ-④」と同様にVLOOKUP関数を入力します。

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

6)A5欄にB、C欄をつなぎ合わせたデータを作成する。

A5欄 =IF(B5=0,0,B5&” “&C5)
(A列の「地域+区分」は、実績データに合わせただけです。以降では使用しません。)

7)D5、E5欄に上期を合計するように数式を入力する。

D5欄 =J5+L5+N5+P5+R5+T5
E5欄 =K5+M5+O5+Q5+S5+U5

8)A5~E5欄に入力した計算式を200行目までコピーする。

122件のデータなので商品が追加になってもいいように多めにしています。
(計算式部分だと分かるように、塗りつぶし、フォントの色を変更しておく)

9)D2、E2欄に数量、金額が集計できるように計算式を入力する。

D2欄 =SUBTOTAL(9,D5:D200) 、 E2欄 =SUBTOTAL(9,E5:E200)

10)店舗や商品が追加になっても分かるように、B2、C2欄に下記の計算式を入力します。

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

下のようなデータとなりました。4月、5月(J~M)の列は空白なので非表示にしています。

これで、元の予測データに1~3行目に行挿入、そして実績データがある月の列を挿入して、F列~に貼り付けると毎回同じ形のデータが作成できます。

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

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

シェアする

  • このエントリーをはてなブックマークに追加

フォローする