昨年と去年の売り上げ比較データを作りたいのですが、2つの比較する値が一致しません。
現状、3つの手順をしていますが、特にA列とC列をそろえるのが手間でしょうがありません。
①A列とC列それぞれ並べかえる
②A列とC列をそろえる
③引き算(B列引くD列=E列)
AとCの値が一致していればいいのですが、現状そうもいかないようです。
2015年と2016年を比較して、前年度比較を簡単に出せる方法はないでしょうか。
どなたか詳しい方、お教えください。
マクロ (VBA) を使う方法と、ピボットテーブルを使う方法があります。
まず、マクロ (VBA) を使う方です。
VBA を使ったことがなければ、まずは、ここらあたりをサラッと読んでください。
http://www.atmarkit.co.jp/ait/articles/1402/03/news143.html
リボンに「開発」タブを表示して、「Visual Basic」を開きます。
「標準モジュール」を追加します。
以下のページの真ん中からくらいに操作イメージがあります。
http://brain.cc.kogakuin.ac.jp/~kanamaru/lecture/vba2013/01-intro01.html
標準モジュールのウィンドウが開いたら、以下のコードを貼り付けます。
Const MAX_DATA = 10000 Sub 売上比較() Set s = ActiveSheet.Sort Set sf = ActiveSheet.Sort.SortFields ' A-B 列を並べ替え sf.Clear sf.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal s.SetRange Range("A:B") s.Header = xlYes s.Orientation = xlTopToBottom s.Apply ' C-D 列を並べ替え sf.Clear sf.Add Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal s.SetRange Range("C:D") s.Apply ' 商品をそろえる last_row_1 = Cells(Rows.Count, 1).End(xlUp).Row ' A last_row_2 = Cells(Rows.Count, 3).End(xlUp).Row ' C r = 2 Do While r <= last_row_1 And r <= last_row_2 If r > MAX_DATA Then Exit Do End If Set c1 = Cells(r, 1) Set c2 = Cells(r, 3) If c1.Value < c2.Value Then Range(c2, Cells(r, 4)).Insert Shift:=xlDown last_row_2 = last_row_2 + 1 ElseIf c1.Value > c2.Value Then Range(c1, Cells(r, 2)).Insert Shift:=xlDown last_row_1 = last_row_1 + 1 End If r = r + 1 DoEvents Loop ' 計算式を E 列にセット last_row = last_row_1 If last_row < last_row_2 Then last_row = last_row_2 End If Range("E:E").Clear Cells(2, 5).Formula = "=D2-B2" Cells(2, 5).Copy Range(Cells(2, 5), Cells(last_row, 5)) End Sub
Excel のシートに戻って、A-B列に 2015年のデータ、C-D列に 2016年のデータを貼り付けてください。
リボンの「マクロ」をクリックすると、開いたダイアログに「売上比較」が表示されているので、それをクリックして選択したら、右上の「実行」のボタンをクリックします。
後は、比較するデータの数によりますが、黙ってみてれば、並べ替えて、同じ商品を一行に配置して、E列に数式をセットするところまでを自動で行います。
・データを貼り付けたところ
・マクロを実行した後
# 商品名が重複してた場合も試してます
質問の文面では、「③引き算(B列引くD列=E列)」とありましたが、添付の画像では E列は「D列-B列」になっているので、数式もそのようにしてあります。
比較する商品の数はいくつくらいあるんでしょうか。
延々と動き続けるとパソコンが固まっちゃうかもしれないので、10000行で打ち切るようにしてあります(びびり)。
それ以上を比較したいのであれば、ソースの先頭にある 10000 を適当に大きくしてください。
もうひとつの、ピボットテーブルを使う方法です。
2015年と2016年のデータを横に並べてではなく、縦に並べて貼り付けます。
それぞれの列には見出しを入れます(この方が、ピボットテーブルを使いやすい)。
それぞれのデータには、年を入れておきます(ここでは、先頭の列に入れました)。
品名で並べ替える必要はありません。
リボンの「挿入タブ」から、ピボットテーブルを挿入します。
表の中のどれかのセルが選択されていれば、範囲は自動で良いように選んでくれます。
ピボットテーブルのテーブルリストで、以下のように設定します(上のエリアからドラッグ&ドロップです)。
ピボットテーブルの必要な分だけを別のシートにコピーします。
2016年と2015年の差を式で入力します。
# ピボットテーブルのシートでもできますが、式の見た目がちょっと変わります。
式を下にコピーします。
どちらでも、お好きな方をどうぞ。
A~Fの商品名のかわりに、商品ナンバーなどをわりふってください。
そして、2015年の商品名と2016年の商品名の列を別々にたてるのをよして、永遠に2行目だけが商品名になるようにしておきます。
A行:商品ナンバー(「a001」など)
B行:実際の商品名(「赤い歯ブラシ」など)
C行:2015年の売り上げ
D行:2016年の売り上げ
そして2016年の取り扱い品目がふえたら下の方に追加します。A行にb003、B行に白いコップなどのように新たに追加していきます。このシートは数値入力以外はあまりいじらないようにします。書き加えるためだけのシートにしましょうね。
2011年と2010年の差分が急に知りたくなったりしたら、シートを1枚まるごとコピーして、2012年の行に引き算関数をドラッグコピーしたりすればすぐに出すことができます。
http://web-tan.forum.impressrd.jp/e/2013/09/04/15918
http://xn--xckxabsx8dzfo045a1cub4h7emcm.com/infomation/itemid.html
なぽりんさんに補足します。
上記リンクは、商品コードの付け方についてのまとめです。
取扱をやめた商品、新たに取扱をはじめた商品、リニューアルされた商品・・・
いろいろあるでしょうし、商品ごとの比較だけでなく、カテゴリ毎の比較もするでしょうから、
商品コードの決め方が鍵になります。
マクロ (VBA) を使う方法と、ピボットテーブルを使う方法があります。
まず、マクロ (VBA) を使う方です。
VBA を使ったことがなければ、まずは、ここらあたりをサラッと読んでください。
http://www.atmarkit.co.jp/ait/articles/1402/03/news143.html
リボンに「開発」タブを表示して、「Visual Basic」を開きます。
「標準モジュール」を追加します。
以下のページの真ん中からくらいに操作イメージがあります。
http://brain.cc.kogakuin.ac.jp/~kanamaru/lecture/vba2013/01-intro01.html
標準モジュールのウィンドウが開いたら、以下のコードを貼り付けます。
Const MAX_DATA = 10000 Sub 売上比較() Set s = ActiveSheet.Sort Set sf = ActiveSheet.Sort.SortFields ' A-B 列を並べ替え sf.Clear sf.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal s.SetRange Range("A:B") s.Header = xlYes s.Orientation = xlTopToBottom s.Apply ' C-D 列を並べ替え sf.Clear sf.Add Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal s.SetRange Range("C:D") s.Apply ' 商品をそろえる last_row_1 = Cells(Rows.Count, 1).End(xlUp).Row ' A last_row_2 = Cells(Rows.Count, 3).End(xlUp).Row ' C r = 2 Do While r <= last_row_1 And r <= last_row_2 If r > MAX_DATA Then Exit Do End If Set c1 = Cells(r, 1) Set c2 = Cells(r, 3) If c1.Value < c2.Value Then Range(c2, Cells(r, 4)).Insert Shift:=xlDown last_row_2 = last_row_2 + 1 ElseIf c1.Value > c2.Value Then Range(c1, Cells(r, 2)).Insert Shift:=xlDown last_row_1 = last_row_1 + 1 End If r = r + 1 DoEvents Loop ' 計算式を E 列にセット last_row = last_row_1 If last_row < last_row_2 Then last_row = last_row_2 End If Range("E:E").Clear Cells(2, 5).Formula = "=D2-B2" Cells(2, 5).Copy Range(Cells(2, 5), Cells(last_row, 5)) End Sub
Excel のシートに戻って、A-B列に 2015年のデータ、C-D列に 2016年のデータを貼り付けてください。
リボンの「マクロ」をクリックすると、開いたダイアログに「売上比較」が表示されているので、それをクリックして選択したら、右上の「実行」のボタンをクリックします。
後は、比較するデータの数によりますが、黙ってみてれば、並べ替えて、同じ商品を一行に配置して、E列に数式をセットするところまでを自動で行います。
・データを貼り付けたところ
・マクロを実行した後
# 商品名が重複してた場合も試してます
質問の文面では、「③引き算(B列引くD列=E列)」とありましたが、添付の画像では E列は「D列-B列」になっているので、数式もそのようにしてあります。
比較する商品の数はいくつくらいあるんでしょうか。
延々と動き続けるとパソコンが固まっちゃうかもしれないので、10000行で打ち切るようにしてあります(びびり)。
それ以上を比較したいのであれば、ソースの先頭にある 10000 を適当に大きくしてください。
もうひとつの、ピボットテーブルを使う方法です。
2015年と2016年のデータを横に並べてではなく、縦に並べて貼り付けます。
それぞれの列には見出しを入れます(この方が、ピボットテーブルを使いやすい)。
それぞれのデータには、年を入れておきます(ここでは、先頭の列に入れました)。
品名で並べ替える必要はありません。
リボンの「挿入タブ」から、ピボットテーブルを挿入します。
表の中のどれかのセルが選択されていれば、範囲は自動で良いように選んでくれます。
ピボットテーブルのテーブルリストで、以下のように設定します(上のエリアからドラッグ&ドロップです)。
ピボットテーブルの必要な分だけを別のシートにコピーします。
2016年と2015年の差を式で入力します。
# ピボットテーブルのシートでもできますが、式の見た目がちょっと変わります。
式を下にコピーします。
どちらでも、お好きな方をどうぞ。
コメント(0件)