エクセルで別シートにある各々売上データを分類コードごとに対比

エクセルでsheet1とsheet2各々にある売上データを分類コードで集計し、sheet3に反映させたいのですが、上手くいきません。毎月品名に新商品や廃盤商品があり、分類コードが増減されますが、列スタイルは一緒です。どなたか方法を具体的に教えていただけませんか。
当方、エクセルは初心者レベルです。

No.5 ベストアンサー 回答者： tatsu99 回答日時： 2016/10/20 19:49

No2,No3です。

>廃盤の売場に並んでいないゼロと売場に並んでいて販売実績が無いゼロとでは元々意味合いが違いますから、本来はゼロの色を変えるとかしたかったのです。
>tatsu99様の提案通り、廃盤商品の売上金額や販売点数はゼロより空白になった方が、識別し易いですがそんな事可能なのですか？
マクロで行えば、可能です。
以下のマクロを登録して実行してください。（標準モジュールへ登録）
-----------------------------------------------------------
Option Explicit

Public Sub Macro()
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Sh3 As Worksheet
Dim maxRow1 As Long
Dim maxRow2 As Long
Dim maxRow3 As Long
Dim fm1 As String '売上金額伸長のフォーマット
Dim fm2 As String '点数伸長のフォーマット
Dim row As Long
Dim row3 As Long
Dim row3w As Long
Dim code As Variant '分類コード
Set Sh1 = Worksheets("sheet1") '前月売上
Set Sh2 = Worksheets("sheet2") '当月売上
Set Sh3 = Worksheets("sheet3") '売上集計
maxRow1 = Sh1.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める
maxRow2 = Sh2.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める
maxRow3 = Sh3.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める
Dim dicT As Object '連想配列
Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義
fm1 = Sh3.Cells(2, 8).NumberFormat '表示形式を取得
fm2 = Sh3.Cells(2, 10).NumberFormat '表示形式を取得
'sheet1を処理する
row3 = 2
For row = 2 To maxRow1
code = Sh1.Cells(row, 1).Value
Sh3.Cells(row3, 1).Value = Sh1.Cells(row, 1).Value '分類コード
Sh3.Cells(row3, 2).Value = Sh1.Cells(row, 2).Value '商品
Sh3.Cells(row3, 3).Value = Sh1.Cells(row, 3).Value 'sheet1売上金額
Sh3.Cells(row3, 4).Value = Sh1.Cells(row, 4).Value 'sheet1点数
Sh3.Cells(row3, 5).Value = "" 'sheet2売上金額
Sh3.Cells(row3, 6).Value = "" 'sheet2点数
dicT(code) = row3
row3 = row3 + 1
Next
'sheet2を処理する
For row = 2 To maxRow2
code = Sh2.Cells(row, 1).Value
If dicT.exists(code) = True Then
'既存商品
row3w = dicT(code)
Sh3.Cells(row3w, 5).Value = Sh2.Cells(row, 3).Value 'sheet2売上金額
Sh3.Cells(row3w, 6).Value = Sh2.Cells(row, 4).Value 'sheet2点数
Else
'新規商品
Sh3.Cells(row3, 1).Value = Sh2.Cells(row, 1).Value '分類コード
Sh3.Cells(row3, 2).Value = Sh2.Cells(row, 2).Value '商品
Sh3.Cells(row3, 3).Value = "" 'sheet1売上金額
Sh3.Cells(row3, 4).Value = "" 'sheet1点数
Sh3.Cells(row3, 5).Value = Sh2.Cells(row, 3).Value 'sheet2売上金額
Sh3.Cells(row3, 6).Value = Sh2.Cells(row, 4).Value 'sheet2点数
dicT(code) = row3
row3 = row3 + 1
End If
Next
'付加情報を計算する
For row = 2 To row3 - 1
Sh3.Cells(row, 7) = Sh3.Cells(row, 5).Value - Sh3.Cells(row, 3).Value '売上金額増減
'売上金額伸長
If Sh3.Cells(row, 3).Value = "" Or Sh3.Cells(row, 3).Value = 0 Then
Sh3.Cells(row, 8).Value = 0
Else
Sh3.Cells(row, 8).Value = Sh3.Cells(row, 5).Value / Sh3.Cells(row, 3).Value
End If
Sh3.Cells(row, 8).NumberFormat = fm1
Sh3.Cells(row, 9) = Sh3.Cells(row, 6).Value - Sh3.Cells(row, 4).Value '点数増減
'点数伸長
If Sh3.Cells(row, 4).Value = "" Or Sh3.Cells(row, 4).Value = 0 Then
Sh3.Cells(row, 10).Value = 0
Else
Sh3.Cells(row, 10).Value = Sh3.Cells(row, 6).Value / Sh3.Cells(row, 4).Value
End If
Sh3.Cells(row, 10).NumberFormat = fm2
Next
'商品数が減少したとき、余分な行を消す
For row = row3 To maxRow3
Sh3.Range(Cells(row, 1), Cells(row, 10)).Value = ""
Next
MsgBox ("処理完了")
End Sub
----------------------------------------------------------
注意事項です。
sheet1,sheet2,sheet3は必ず存在すること。
sheet3の1行目は、作成しません。2行目以降を作成します。
売上額伸長、点数伸長のセルの表示形式(%表示)は、sheet3のH2、J2の内容を引き継ぎます。
ここの書式はクリアしないでください。

この回答へのお礼

お返事大変遅くなりました。
マクロにコピペしてやってみました。
「何とかデバッグ・・・」とやらが何十回も出てきて諦めかけましたが、さきほど無事出来ました。
新規や廃盤の実績欄は空欄になっていたので、他の実績ゼロ商品と一目瞭然に違いが分かりました。
ありがとうございました。
明日、実際のデータで試してみます。
その際、もしマクロでわからないことが出て来ましたら、ご質問させてください。

ご報告とお礼まで。

お礼日時：2016/10/24 21:40

No.10 回答者： tatsu99 回答日時： 2016/10/25 00:59

No5です。

>マクロにコピペしてやってみました。
>「何とかデバッグ・・・」とやらが何十回も出てきて諦めかけましたが、さきほど無事出来ました。
こちらで、動作確認を行った時は、「何とかデバッグ・・・」は、表示されず、
”処理完了”のメッセージが表示されただけなのですが、「何とかデバッグ・・・」のメッセージが
表示されるのは、マクロのどの行でしょうか？

この回答へのお礼

実際のデータや加工したデータでもtatsu99さんのマクロ式で試しました。問題無く出来ました。「何とかデバック・・・」は多分、シート名の数字が全角とかだったのでなったんだと思います。今は全く大丈夫です。
マクロってスゴイですね、ビックリしました。私も覚えたいです。
今回はエクセル素人の私にも分かり易く回答して頂き、ありがとうございました。
また機会ありましたら宜しくお願い致します。

お礼日時：2016/10/26 16:59

No.9 回答者： ママチャリ 回答日時： 2016/10/22 09:06

Sheet3のＥ2セルは、Sheet２のE列ではなく、C列の値を集計する必要があります。

よって、次の式が正しいと思います。

【Sheet3のＥ2セル】=IF(A2="","",SUMIF(Sheet2!A:A,A2,Sheet2!C:C))

No.8の回答で気づいて頂けると思っていたのですが、残念です。
G列以降の式にも不安を覚えますが、ご検討をお祈りしています。

この回答へのお礼

少し意味がわかりました。
Ｆまでは取りあえず完成しました。

何度もありがとうございました。

お礼日時：2016/10/22 09:56

No.8 回答者： ママチャリ 回答日時： 2016/10/22 07:29

SUMIFの中で、Sheet2!Ｅ:Ｅを参照する記述がありますが、Sheet2はD列までしかデータが無いですよね？

データが存在しない列を集計したら、結果は当然、ゼロです。

この回答へのお礼

おはようございます。
ありがとうございます。
そうしましたら、sheet3のＥ2セルには何を入れたらよいのですか？

お礼日時：2016/10/22 08:49

No.7 回答者： ママチャリ 回答日時： 2016/10/21 22:16

例えば、Sheet3のA列に小分類コードが入力されているとします。

この状態で、Sheet3のC2セルに次の数式を設定すると目的の値が求められるのですが、この式は理解できますか？

【Sheet3のC2セル】=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!C:C))

これと同じような考え方で、C~J列の各値を求めて下さい。ここまでは初心者でもできるはずです。
問題なのは、A:B列への小分類コードと品名の入力です。

一番簡単なのは、Sheet1のA:B列を手作業でコピペ。その下にSheet2のA:B列をコピペ。A:Bセルを選択した状態で、「データ」タグの「重複の削除」で重複を削除し、さらにA列で並び替えを行えば完成です。

もうちょっと勉強すると上記の操作をマクロ記録して、自動実行することも可能になります。
飛び道具のようなマクロを使用するのも悪くはないですが、まずは、ご自分の身の丈に合った方法で、問題を解決されることをお勧めします。

この回答へのお礼

簡潔な方法ご提示ありがとうございます。
やってみましたら、sheet3のＣ列Ｄ列は完成したのですが、Ｅ列Ｆ列は全て「0」になってしまいます。
sheet3E2セルの計算式は=IF(A2="","",SUMIF(Sheet2!A:A,A2,Sheet2!Ｅ:Ｅ))にしたのですが・・・
お手数ですが、Ｅ2セルの計算式アドバイスお願いします。

※A:B列への小分類コードと品名の入力は、「データ」タグの「重複の削除」で簡単にできました。

お礼日時：2016/10/22 07:01

No.6 回答者： tatsu99 回答日時： 2016/10/20 20:02

No5です。

excelに関する質問であれば、このカテゴリ（オフィス系ソフト）でなく
Excel(エクセル)のカテゴリのほうが、回答が付きやすいと思います。
（余談ですが、私はExcel(エクセル)のカテゴリの質問は見ますが、オフィス系ソフトのカテゴリはほとんど見ていません。
たまたま、今日、目にしたので回答した次第です）

No.4 回答者： tatsu99 回答日時： 2016/10/20 18:55

No3です。

追加質問です。
質問４：sheet3の商品I、Jは、廃盤商品かと思います。（sheet2に商品I、Jはないため）
従って、sheet3の商品I、Jのsheet2売上金額とsheet2点数は、
０で表示するのではなく、空白で表示したほうが良いと考えますがいかがでしょうか？
０で表示した場合は、商品はあるが売り上げが０だったということになりませんでしょうか？

この回答へのお礼

質問4その通りＩ、Ｊは廃盤商品です。
廃盤の売場に並んでいないゼロと売場に並んでいて販売実績が無いゼロとでは元々意味合いが違いますから、本来はゼロの色を変えるとかしたかったのです。
tatsu99様の提案通り、廃盤商品の売上金額や販売点数はゼロより空白になった方が、識別し易いですがそんな事可能なのですか？

お礼日時：2016/10/20 19:31

No.3 回答者： tatsu99 回答日時： 2016/10/20 16:20

補足要求です。

質問１：sheet1及びsheet2は約何行ありますか。

質問2：売上金額増減 売上金額伸長 点数増減 点数伸長の算出方法は、以下の式で良いですか。
売上金額増減＝sheet2の売上金額－sheet1の売上金額
売上金額伸長＝sheet2の売上金額／sheet1の売上金額（但し、sheet1の売上金額が０の場合は０を表示）
点数増減＝sheet2の点数－sheet1の点数
点数伸長＝sheet2の点数／sheet1の点数（但し、sheet1の点数が０の場合は０を表示）

質問３：マクロで上記を実現すると、マクロを実行したタイミングで、sheet1,sheet2の内容がsheet3に反映されます。
shee1,sheet2を更新しても、マクロを実行しない限り、sheet3に反映されませんがそれでも良いですか。

この回答へのお礼

ご丁寧にありがとうございます。

質問1どちらも約350行です。将来的には800行ぐらいまで増える予定です。
質問2はその通りです。
質問3マクロ実行のタイミングで反映できるのが一番理想です。（ただ、私は作れませんが・・・）

お礼日時：2016/10/20 19:26

No.2 回答者： angkor_h 回答日時： 2016/10/19 23:08

画像が見えないので何をしたいのかが良くわかりませんが、

コードごとに整理したいのであれば、次の方法が利用できると思います。
Vlookupでコードを検索値とする、
sheet1の下にsheet2を連結して、ピボットテーブルを作成、
但し、範囲は都度指定です。

> 当方、エクセルは初心者レベルです。
これは余分な逃げ口上です…　誰でも知らないことに対しては初心者です。

この回答へのお礼

画像が不鮮明で申し訳ないです。
ピボットテーブルは少しやったことありますので、試してみます。
ありがとうございました。

お礼日時：2016/10/20 19:33

No.1 回答者： くんこば 回答日時： 2016/10/19 23:04

vlookup関数かな。

あとは、Excel先生が答えてくれるのを待ちましょう。

この回答へのお礼

ありがとうございます。vlookup関数調べてみます。

お礼日時：2016/10/20 19:32