1464656490 エクセル初心者です。

昨年と去年の売り上げ比較データを作りたいのですが、2つの比較する値が一致しません。
現状、3つの手順をしていますが、特にA列とC列をそろえるのが手間でしょうがありません。

①A列とC列それぞれ並べかえる
②A列とC列をそろえる
③引き算(B列引くD列=E列)

AとCの値が一致していればいいのですが、現状そうもいかないようです。
2015年と2016年を比較して、前年度比較を簡単に出せる方法はないでしょうか。
どなたか詳しい方、お教えください。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2016/05/31 10:01:30
  • 終了:2016/06/05 23:00:56

ベストアンサー

id:a-kuma3 No.3

a-kuma3回答回数4325ベストアンサー獲得回数17732016/06/01 00:06:53

マクロ (VBA) を使う方法と、ピボットテーブルを使う方法があります。

  • マクロ (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列に数式をセットするところまでを自動で行います。

・データを貼り付けたところ
f:id:a-kuma3:20160531184421p:image

・マクロを実行した後
f:id:a-kuma3:20160531184419p:image
# 商品名が重複してた場合も試してます

質問の文面では、「③引き算(B列引くD列=E列)」とありましたが、添付の画像では E列は「D列-B列」になっているので、数式もそのようにしてあります。


比較する商品の数はいくつくらいあるんでしょうか。
延々と動き続けるとパソコンが固まっちゃうかもしれないので、10000行で打ち切るようにしてあります(びびり)。
それ以上を比較したいのであれば、ソースの先頭にある 10000 を適当に大きくしてください。



もうひとつの、ピボットテーブルを使う方法です。

2015年と2016年のデータを横に並べてではなく、縦に並べて貼り付けます。
それぞれの列には見出しを入れます(この方が、ピボットテーブルを使いやすい)。
それぞれのデータには、年を入れておきます(ここでは、先頭の列に入れました)。
品名で並べ替える必要はありません。
f:id:a-kuma3:20160531184417p:image

リボンの「挿入タブ」から、ピボットテーブルを挿入します。
f:id:a-kuma3:20160531184415p:image
表の中のどれかのセルが選択されていれば、範囲は自動で良いように選んでくれます。

ピボットテーブルのテーブルリストで、以下のように設定します(上のエリアからドラッグ&ドロップです)。

  • 列ラベルに「年」
  • 行ラベルに「品名」
  • 値に「数量」(集計方法は「合計」を選びます:デフォルトです)

f:id:a-kuma3:20160531184412p:image

ピボットテーブルの必要な分だけを別のシートにコピーします。
2016年と2015年の差を式で入力します。
f:id:a-kuma3:20160531184411p:image
# ピボットテーブルのシートでもできますが、式の見た目がちょっと変わります。

式を下にコピーします。
f:id:a-kuma3:20160531184408p:image



どちらでも、お好きな方をどうぞ。

その他の回答(2件)

id:NAPORIN No.1

なぽりん回答回数4491ベストアンサー獲得回数8312016/05/31 11:18:47

A~Fの商品名のかわりに、商品ナンバーなどをわりふってください。
そして、2015年の商品名と2016年の商品名の列を別々にたてるのをよして、永遠に2行目だけが商品名になるようにしておきます。

A行:商品ナンバー(「a001」など)
B行:実際の商品名(「赤い歯ブラシ」など)
C行:2015年の売り上げ
D行:2016年の売り上げ

そして2016年の取り扱い品目がふえたら下の方に追加します。A行にb003、B行に白いコップなどのように新たに追加していきます。このシートは数値入力以外はあまりいじらないようにします。書き加えるためだけのシートにしましょうね。
 
2011年と2010年の差分が急に知りたくなったりしたら、シートを1枚まるごとコピーして、2012年の行に引き算関数をドラッグコピーしたりすればすぐに出すことができます。

id:nepia11 No.2

nepia11回答回数232ベストアンサー獲得回数272016/05/31 14:19:52

http://web-tan.forum.impressrd.jp/e/2013/09/04/15918
http://xn--xckxabsx8dzfo045a1cub4h7emcm.com/infomation/itemid.html

なぽりんさんに補足します。
上記リンクは、商品コードの付け方についてのまとめです。
取扱をやめた商品、新たに取扱をはじめた商品、リニューアルされた商品・・・
いろいろあるでしょうし、商品ごとの比較だけでなく、カテゴリ毎の比較もするでしょうから、
商品コードの決め方が鍵になります。

id:a-kuma3 No.3

a-kuma3回答回数4325ベストアンサー獲得回数17732016/06/01 00:06:53ここでベストアンサー

マクロ (VBA) を使う方法と、ピボットテーブルを使う方法があります。

  • マクロ (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列に数式をセットするところまでを自動で行います。

・データを貼り付けたところ
f:id:a-kuma3:20160531184421p:image

・マクロを実行した後
f:id:a-kuma3:20160531184419p:image
# 商品名が重複してた場合も試してます

質問の文面では、「③引き算(B列引くD列=E列)」とありましたが、添付の画像では E列は「D列-B列」になっているので、数式もそのようにしてあります。


比較する商品の数はいくつくらいあるんでしょうか。
延々と動き続けるとパソコンが固まっちゃうかもしれないので、10000行で打ち切るようにしてあります(びびり)。
それ以上を比較したいのであれば、ソースの先頭にある 10000 を適当に大きくしてください。



もうひとつの、ピボットテーブルを使う方法です。

2015年と2016年のデータを横に並べてではなく、縦に並べて貼り付けます。
それぞれの列には見出しを入れます(この方が、ピボットテーブルを使いやすい)。
それぞれのデータには、年を入れておきます(ここでは、先頭の列に入れました)。
品名で並べ替える必要はありません。
f:id:a-kuma3:20160531184417p:image

リボンの「挿入タブ」から、ピボットテーブルを挿入します。
f:id:a-kuma3:20160531184415p:image
表の中のどれかのセルが選択されていれば、範囲は自動で良いように選んでくれます。

ピボットテーブルのテーブルリストで、以下のように設定します(上のエリアからドラッグ&ドロップです)。

  • 列ラベルに「年」
  • 行ラベルに「品名」
  • 値に「数量」(集計方法は「合計」を選びます:デフォルトです)

f:id:a-kuma3:20160531184412p:image

ピボットテーブルの必要な分だけを別のシートにコピーします。
2016年と2015年の差を式で入力します。
f:id:a-kuma3:20160531184411p:image
# ピボットテーブルのシートでもできますが、式の見た目がちょっと変わります。

式を下にコピーします。
f:id:a-kuma3:20160531184408p:image



どちらでも、お好きな方をどうぞ。

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません