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

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

下のようなお酒屋さんの2016年4月、5月の販売データがあったとします。

店舗は黄色で塗りつぶしている浅草、吉祥寺、梅田、堺、三宮、博多、鹿児島の7店舗、扱っている商品は日本酒、焼酎、ビール系飲料です。

今回だけ集計したいのであれば、J1とK1欄に赤字(J2、K2のところ)で記載している計算式を入れておけば、集計したい販売年月、店名、商品名をフィルターで絞り込むと計算できます。

それを集計表に転記してもいいのですが、毎月のこととなるとちょっと面倒ですよね。

そこで、下表のような集計表を別シートに作成してみます。

まず、左側の集計表は店舗別の集計(4月、5月の合計値)です。

ここでは SUMIF関数(条件に合うデータを合計)を使用します。

 =SUMIF( 範囲 , 検索条件 , 合計範囲 ) となるので、C4欄は下記となります。

範囲:販売実績データのB列、検索条件:集計-1のB4、合計範囲:販売実績データのE列

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

C4欄(数量) =SUMIF(販売実績データ_元!B:B,B4,販売実績データ_元!E:E)

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

D4欄(金額) =SUMIF(販売実績データ_元!B:B,B4,販売実績データ_元!G:G)

C4欄、D4欄に計算式を入力したら、10行目まで計算式をコピーします。

そして、合計欄のC11、D11 SUM関数を入力すると完成です。

※範囲、合計範囲で「B:B」「E:E」としていますが、これはB列、E列全てが対象となり、データ件数が増加しても集計できます。ただし、データの下部に別のデータがあると、その部分も集計される可能性があります。その場合は、「B4:B300」「E4:E300」のように行も指定する必要があります。

次に、真ん中の集計表は商品別の集計です。もう簡単ですよね。

G4欄、H4欄に下記の計算式を入力して、20行目まで計算式をコピーします。

G4欄(数量) =SUMIF(販売実績データ_元!D:D,F4,販売実績データ_元!E:E)

H4欄(金額) =SUMIF(販売実績データ_元!D:D,F4,販売実績データ_元!G:G)

そして、合計欄のG21、H21に SUM関数を入力すると完成です。

さてここで、ここに記載されている商品はこれで本当に全てなの? という問題があります。

店舗別集計(冒頭に7店舗と記載)の合計とも合っているので、今回は大丈夫のようですが、そもそもこの集計表を作成するためには、事前に商品名をリストアップする必要があります。

まず、下図のように販売実績データ_元のD列をL列(どこでもいいです)にコピーします。

L列にカーソルを持ってきて、上部メニューの「データ」、そして「重複データの削除」を選択して重複データの削除を行います。するとN列のようなデータとなります。

並びは違っていますけど、上記の商品別集計と合っていますよね。

この「重複データの削除」は、結構、重宝します。Excel2007から追加されたようです。

最後に右側の地域別・種類別の集計ですが、これは、店名はあっても地域のデータが無い、商品名はあってもお酒の種類のデータが無い、ということで、すんなりといきません。

ということで次は、「集計したい項目をデータに追加する」という内容にします。

シェアする

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

フォローする