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

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

下の左側が元の販売実績データで右側のような集計表を作成したいのですが、元の販売実績データには、「地域」と「お酒の種類」のデータが含まれていません。

  

このため、下のシート「集計区分」の情報を基にデータを追加します。

販売実績データのシートを作成する。

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

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

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

4)B5、C5欄にシート「集計区分」のデータを取り出す関数を入力します。

ここでは VLOOKUP関数(検索したデータに該当した行の指定列からデータを取り出す関数)を使用します。

 =VLOOKUP( 検索値 , 範囲 , 列番号 , FALSE ) となるので、B5欄は

 検索値:◆実績データのG5、範囲:集計区分のE:F、列番号:2 です。

B5欄に下記の計算式を入力します。

B5欄 =VLOOKUP(G5,集計区分!E:F,2,FALSE)

C5欄も同様に下記の計算式となります。

C5欄 =VLOOKUP(I5,集計区分!B:C,2,FALSE)

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

A5欄 =B5&” “&C5 (間に半角の空白を挟んでいます)

6)D5、E5欄にJ5、L5欄の数値を参照する数式を入力する。

7)A5~E5欄に入力した計算式をデータの一番下の行までコピーする。
(計算式部分だと分かるように、塗りつぶし、フォントの色を変更しておく)

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

D2欄 =SUBTOTAL(9,D5:D300)、 E2欄 =SUBTOTAL(9,E5:E300)

 ここでは、100件強/月のデータなので、300行目までとしています。

D:D、E:Eとすると、計算式のセルも含まれるので循環参照になってしまいます。


項目を追加した下のようなデータが出来たので、これを基に集計します。

まず、「Ⅰ-①」と同様にSUMIF関数を使って集計します。

この表のままでは検索条件が不足しているので、◆実績データのA欄に作成した
「地域+区分」に相当するものを追加します。

J4欄に「=”関東エリア”&” “&L4」の計算式を入力して下にコピー、関西、九州エリアは計算式を関西エリア、九州エリアに置き換えます。

M4、N4欄に下記の計算式を入力し、5行目以下の小計、合計以外の箇所にコピーします。

M4欄 =SUMIF(◆実績データ!A:A,J4,◆実績データ!D:D)

N4欄 =SUMIF(◆実績データ!A:A,J4,◆実績データ!E:E)

小計欄は各エリアの合計、合計欄は小計を足したものとしています。

J欄は、このまま見せるのは不細工なので非表示にしておきます。

今度は、SUMIFS関数(複数の条件を満たすデータを合計)を使って集計します。

ここでは計算式のコピーを容易にするために、J列に地域を記載しています。

※J列に地域を記載せずに、K列の空白部分に地域を記載(ちょっと不細工ですが、フォントを白色にするという手もあります)するか、地域毎にK25、K30、K35を参照する計算式にしてもいいです。

 =SUMIFS( 合計対象範囲 , 条件範囲1 , 条件1 , 条件範囲2 , 条件2・・・) となるので、M25欄(先ほどの表の下に作成したので25行目になっています)は、

合計対象範囲:◆実績データのD:D , 条件範囲1:◆実績データのB:B , 条件1:J25 ,
条件範囲2:◆実績データのC:C , 条件2:L25 です。

M25、N25欄に下記の計算式を入力し、先ほどと同様に下方へコピーします。

M25欄 =SUMIFS(◆実績データ!D:D,◆実績データ!B:B,J25,◆実績データ!C:C,L25)

N25欄 =SUMIFS(◆実績データ!E:E,◆実績データ!B:B,J25,◆実績データ!C:C,L25)

これで、SUMIF関数と同様の集計表ができました。

SUMIFS関数を使うのであれば、◆実績データの「地域+区分」は不要です。

この「SUMIFS関数」も大変便利です。Excel2007の新関数だそうです。

シェアする

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

フォローする