人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

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



1464656490
●拡大する


●質問者: kanapom
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● なぽりん

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

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

そして2016年の取り扱い品目がふえたら下の方に追加します。A行にb003、B行に白いコップなどのように新たに追加していきます。このシートは数値入力以外はあまりいじらないようにします。書き加えるためだけのシートにしましょうね。

2011年と2010年の差分が急に知りたくなったりしたら、シートを1枚まるごとコピーして、2012年の行に引き算関数をドラッグコピーしたりすればすぐに出すことができます。


2 ● nepia11

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

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


3 ● a-kuma3
ベストアンサー

マクロ (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



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

関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ