二つの表を比べてデータを拠出したい。

質問

こんな感じのファイルが２種類あります。

(1)のファイル

Ａ列　　　　　　　　　Ｂ列　　　　　Ｃ列
商品コード　　　　　　金額　　　　商品名
２３４５６７　　　　３００円　　　りんご
３３３８８９　　　　５００円　　　みかん
５５６８９０　　　１０９０円　　　バナナ
００９８７０　　　２２３９円　　　メロン

(2)のファイル

Ａ列　　　　　　　　　Ｂ列　　　　　Ｃ列
商品コード　　　　　　金額　　　　商品名
４４８００８　　　３３３０円　　　ぶどう
２３４５６７　　　　３３０円　　　りんご
００９８７０　　　２２５０円　　　メロン
３３３８８９　　　　５００円　　　みかん

基本的に、
商品コードが同じであれば商品名は必ず同じである。
同じ商品でも金額は同額、(1)の方が高い、(2)の方が高い、いずれの場合もあり得る。
(1)の表にはあるが(2)には載っていない商品が一部ある。
同様に(2)の表にはあるが(1)には載っていない商品がある。
商品数はどちらも２５０００件程度。
商品コードは必ず６桁である。
並び順は順不動である。
どちらの表にどのコードの商品が欠けているかは不明です。

二つのファイルを加工して使用する事は可能です。
（貼り付けて一つのファイルにする等）

やりたい事は、(1)(2)それぞれに掲載されている同じ商品の中から、
(2)の表に掲載されている金額の方が高い場合、その商品のセルに色を付ける事です。
色でなくてもなんでも構いません。
後に拠出する為に、分かり易くなれば良いのです。

どうすれば良いのかアドバイスをお願いします。

回答No.7

tom04 · ベストアンサー

No.3です。

なかなかご希望通りにならないようですね。

＞後に拠出する為に、分かり易くなれば良いのです

とありますので、VBAになってしまいますが、
この際、一気にSheet3に抽出するようにしてみました。

Alt+F11キー　→　メニュー　→　挿入　→　「標準モジュール」を選択しVBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub 表示() 'この行から
Dim i As Long
Dim endR As Long
Dim wS1 As Worksheet
Dim wS2 As Worksheet
Dim wS3 As Worksheet
Set wS1 = Worksheets("Sheet1") '←「Sheet1」は実際のSheet名に！
Set wS2 = Worksheets("Sheet2") '←「Sheet2」も・・・
Set wS3 = Worksheets("Sheet3")

Application.ScreenUpdating = False
wS3.Cells.ClearContents
wS1.Rows(1).Copy wS3.Cells(1, 1)

wS1.Columns(2).Interior.ColorIndex = xlNone
i = wS1.Cells(Rows.Count, 1).End(xlUp).Row
Range(wS1.Cells(2, 4), wS1.Cells(i, 4)).Formula = _
"=IF(COUNTIF(Sheet2!A:A,A2),IF(B2>VLOOKUP(A2,Sheet2!A:B,2,FALSE),1,0),"""")"

wS1.Cells(1, 1).CurrentRegion.AutoFilter field:=4, Criteria1:="1"
endR = wS1.Cells(Rows.Count, 1).End(xlUp).Row
If endR > 1 Then
Range(wS1.Cells(2, 2), wS1.Cells(endR, 2)).Interior.ColorIndex = 6 '←黄色に色づけ
Range(wS1.Cells(2, 1), wS1.Cells(endR, 3)).Copy
wS3.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlValues
End If
wS1.Columns(4).Delete
wS1.AutoFilterMode = False

wS2.Columns(2).Interior.ColorIndex = xlNone
i = wS2.Cells(Rows.Count, 1).End(xlUp).Row
Range(wS2.Cells(2, 4), wS2.Cells(i, 4)).Formula = _
"=IF(COUNTIF(Sheet1!A:A,A2),IF(B2>VLOOKUP(A2,Sheet1!A:B,2,FALSE),1,0),"""")"

wS2.Cells(1, 1).CurrentRegion.AutoFilter field:=4, Criteria1:="1"
endR = wS2.Cells(Rows.Count, 1).End(xlUp).Row
If endR > 1 Then
Range(wS2.Cells(2, 2), wS2.Cells(endR, 2)).Interior.ColorIndex = 6
Range(wS2.Cells(2, 1), wS2.Cells(endR, 3)).Copy
wS3.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlValues
End If
wS2.Columns(4).Delete
wS2.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub 'この行まで

※　コード内の「Sheet1」「Sheet2」の部分はSheet名がついている場合、実際のSheet名にしてください。
※　両SheetのD列を作業用の列として使っていますので、データはC列までしかない！という前提です。
※　Sheet3に抽出したデータのB列を両Sheetとも黄色に色づけするようにしていますが、
条件付き書式が設定してあるとB列に色が付きません。
※　関数でないので、データ変更があるたびにマクロを実行する必要があります。

何とかご希望通りの動きになればよいのですが・・・m(_ _)m

お礼

何度も有難うございました。

試してみましたが、実行されません。
（応答なし）状態になります。
もしかしてデーター量が多くいのに、当方のＰＣのスペックが
ついていけてないのかもしれません。

何度も本当に有難うございました。

なんとか自力で試してみます。
いろいろと感謝致します。

回答No.6

noname#204879

[No.5お礼]へのコメント、
「そういう事になる」のなら、何れかのブックのシートを、他シートが存在するブックへ移動(またはコピー)して、１つのブック内の２枚のシートを比較させるのが得策です。
そうすれば、お互いに参照する範囲に[名前]を付けることによって、[条件付き書式]で各シートの「セルに色を付ける事」(「色でなくてもなんでも」)が簡単に可能になるからです。

回答No.5

noname#204879

[No.1お礼]へのコメント、
それから もう一つ。

》 こんな感じのファイルが２種類あります
の「ファイル」は「シート」の間違いではないですよね？
つまり、同じ“ブック”内の「こんな感じの」異なるシートでなく、「こんな感じの」シートが異なる“ブック”にあると？

お礼

何度も御指摘頂き有難うございます。
言葉足らずですみません。

元々名前の違う「ファイル」が二つ存在していました。
もちろん一つのファイルの他のシートにコピーすれば同じファイル内と
いう事になりますし、最初からそのようにした方がアドバイスを頂く上で
早かったのかもしれないですね。

＞つまり、同じ“ブック”内の「こんな感じの」異なるシートでなく、
＞「こんな感じの」シートが異なる“ブック”にあると？

そういう事になるかと思います。

今後は気を付けるように致します。

回答No.4

KURUMITO

ファイル１がシート１にファイル２がシート２にあるとしてデータはA,B,C列の２行目から下方にあるとします。
二つのシートでシート２にある商品コードはシート１に含まれる場合もあるがそうでない場合もある、含まれる場合にはその商品コードは一つしかないものとします。その場合にはシート２のA,B,C列の２行目から下方の行を選択します。その場合にはA２セルがアクティブな状態になっていますね。そこでホームタブの「条件付き書式」で「新しいルール」から「数式を使用して書式設定するセルを決定」を選択し、下の窓には次の式を入力します。

=AND(COUNTIF(Sheet1!$A:$A,$A2)>0,$B2>INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$A:$A,0)))

同じ画面の「書式」をクリックして「塗りつぶし」のタブから好みの色を指定してOKします。

お礼

有難うございます。

試してみたのですが、
条件、条件付き書式に他のワークシートまたはブックへの参照は使用できません。
とエラーが出ます。

＞ファイル１がシート１にファイル２がシート２にあるとして
＞データはA,B,C列の２行目から下方にあるとします。
＞二つのシートでシート２にある商品コードはシート１に含まれる
＞場合もあるがそうでない場合もある、含まれる場合には
＞その商品コードは一つしかないものとします。

上記の通りに試しました。
何がいけないのでしょうか？

回答No.3

tom04

こんにちは！

両Sheetを一つのブック（別Sheetで構いません）にまとめて、
条件付き書式でやってみてはどうでしょうか？

（1）の表がSheet1・（2）の表がSheet2とします。

Sheet1のA列すべてを範囲指定　→　名前ボックスに仮に「コード1」と入力しEnter
これでSheet1のA列が「コード1」と名前定義されます。

次にSheet1のA～B列を範囲指定　→　同様に名前ボックスに「範囲1」と入力しEnter
Sheet1のA・B列が「範囲1」と名前定義されました。

Sheet2のB列すべてを範囲指定し、条件付き書式を設定します。

Excel2003の場合
Sheet2のB列すべてを範囲指定　→　条件付き書式　→　数式が・・・
※　Excel2007以降の場合は
条件付き書式　→　新しいルール　→　数式を使用して・・

数式欄に
=AND(COUNTIF(コード1,A1),B1>VLOOKUP(A1,範囲1,2,0))
と入力　→　書式　→　好みの色を選択しOK

これでSheet2のB列金額がSheet1の金額を超える場合に色が付きます。

Sheet1にも同様の表示をしたい場合は
Sheet2の同じ範囲を別名で名前定義しておき、
Sheet1のB列に同じ方法で条件付き書式の設定をします。

※　当然数式の名前定義部分が変わりますので、そこは名前定義したものに変更します。

こんな感じではどうでしょうか？m(_ _)m

お礼

早々に有難うございます。

使用しているソフトが抜けていました。
エクセル２００７です。
大変失礼致しました。

教えて頂いた方法で一度試してみます。

補足

報告が遅くなりすみませんでした。
試してみたのですが、上手くいきません。

全然関係のないセルが何個かだけ色が変わりました。
全く条件には合っていないものも、合っているものもあります。
合わせて数個のみが変化しました。

なぜなんでしょうか？

回答No.2

tsubuyuki

やりたいことは何となく理解しました。

で、どんなソフトを使う予定ですか？

オススメはアクセスですが。

お礼

早々に有難うございます。

肝心な事が抜けておりました。
使用しているのはエクセル２００７です。

申し訳ございませんでした。

回答No.1

noname#204879

お申し越しの場合、「拠出」(キョシュツ)という言葉使いは間違っています。
「抽出」(チュウシュツ)のことですかね？

お礼

御指摘有難うございます。
抽出の間違いでした。
訂正してお詫び致します。