エクセルで、二つのデータの比較、抽出について

質問

こんばんは。エクセルで困っていますので、皆さんのお力をお借りしたいと思います。

シート1

コード　商品名　　金額
15　　　りんご　　500円
33　　　みかん　　200円
22　　　かりん　　350円

シート2

コード　商品名　　金額
15　　　りんご　　500円
45　　　メロン　　1200円
90　　　すいか　　3000円

と二つのシートにそれぞれデータがあります。
そのシートを比較して、
シート3に

コード　商品名　　金額
33　　　みかん　　200円
22　　　かりん　　350円
45　　　メロン　　1200円
90　　　すいか　　3000円

と、一致しないデータを表示させたいと思います。
この場合、シート３にどのような関数を使用するのがいいのでしょうか？
VBAでやったほうが良いのでしょうか？
色々と考えているのですが、一反頭を真っ白にして皆さんの意見を聞きたいと思います。

よろしくお願いします。

回答No.2

imogasi · ベストアンサー

（１）私ならVBAでやりますね。
（２）関数式による解法に未練がありそうなので、載せてみます。長い関数式ですが、それでもこれを使いたいですか。
（例データ）
Sheet1のA1:C7（D列は関数式の結果先取りして載せているので無いものとして見てください）
番号 氏名 住所 非共通連番
12 山田 千代田区 1
14 上野 中野区
15 石井 大田区
16 上岡 渋谷区 2
17 中野 豊島区 3
21 小村 北区 4
Sheet2のA1:C6（D列は関数式の結果先取りして載せているので無いものとして見てください）
番号 氏名 住所 非共通連番
13 今野 江東区 5
14 上野 板橋区
15 今泉 品川区 6
16 石井 目黒区
18 岡島 杉並区 7
（関数式）
（１）Sheet1のD2に=IF(ISERROR(VLOOKUP(B2,Sheet2!$B$2:$B$6,1,FALSE)),MAX($D$1:D1)+1,"")といれ
D７まで複写。
（２）Sheet2のD2に=IF(ISERROR(VLOOKUP(B2,Sheet1!$B$2:$B$7,1,FALSE)),MAX($D$1:D2)+1,"")
といれ、D6まで複写する。
（３）Sheet2のD2だけ関数式を=IF(ISERROR(VLOOKUP(B2,Sheet1!$B$2:$B$6,1,FALSE)),MAX(Sheet1!$D$1:$D$10)+MAX($D$1:D1)+1,"")に修正する。
(集約表）Sheet3
（関数式）
A1に=IF(ROW()>MAX(Sheet1!$D$1:$D$10),OFFSET(Sheet2!$A$1,MATCH(ROW(),Sheet2!$D$1:$D$10,0)-1,0),OFFSET(Sheet1!$A$1,MATCH(ROW(),Sheet1!$D$1:$D$10,0)-1,0))といれA7まで複写する。
B1はA1を複写し、=IF(ROW()>MAX(Sheet1!$D$1:$D$10),OFFSET(Sheet2!$A$1,MATCH(ROW(),Sheet2!$D$1:$D$10,0)-1,1),OFFSET(Sheet1!$A$1,MATCH(ROW(),Sheet1!$D$1:$D$10,0)-1,1))に変える。
B7まで複写する。
C1はB1を複写し、=IF(ROW()>MAX(Sheet1!$D$1:$D$10),OFFSET(Sheet2!$A$1,MATCH(ROW(),Sheet2!$D$1:$D$10,0)-1,2),OFFSET(Sheet1!$A$1,MATCH(ROW(),Sheet1!$D$1:$D$10,0)-1,2))に変え、C7まで複写する。７はSheet2のD列のMAX数です。
$d$10などは最下見込み行ですので、実際に合わせて変えて
下さい。
（結果）Sheet3のA1:C7
12 山田 千代田区
16 上岡 渋谷区
17 中野 豊島区
21 小村 北区
13 今野 江東区
15 今泉 品川区
18 岡島 杉並区
（その他）
このほかに、（１）＃N/Aを出さない、（Sheet3)
（２）無駄な数字を出さない（Sheet1、Sheet2)
関数式の作り方の手当てが必要だが、長くなるので略。

お礼

VBAで作成の方が妥当ということですね。
勉強不足の身なので集中して勉強しましたが、なかなか頭が働かず。。。また別の質問として皆様のお力をお借りしようと思います。

関数式に関しては、理解できました。
確かに長くて無駄な感じがしてしまいますが、ちゃんと出来たので、とても感謝です。

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

回答No.3

miwaki

シート3の1行目は他のシートと同じとし、シート3の2行目以降に次の関数を入れます。
セルA2：=IF(ISNUMBER(MATCH(Sheet1!$A2,Sheet2!$A:$A,0)),"",Sheet1!A2)
これをコピーして、A2からCｘ（ｘは、シート1のデータ長）に貼り付けます。
セルAｙ(ｙは入りつけたデータの次の行）：=IF(ISNUMBER(MATCH(Sheet2!$A2,Sheet1!$A:$A,0)),"",Sheet2!A2)
これを同様にシート2のデータ長分A列からC列に貼り付けます。
次にシート3の全てのデータをコピーして同じ位置に値で貼り付けます。
最後にA列でソートすればお望みのデータが出来上がります。

お礼

ご回答ありがとうございました。

回答No.1

run_away

Excelではプログラムを組まないと対応するのは難しいです。Accessを利用してはいかがでしょうか。

「どうしてもExcelで！」というのであれば、
★ステップ(1)　「シート１でシート２になあるデータを探す。」
lookup関数等を利用して、シート１の金額の右側等のセルにシート２に見つかったものは「○」、見つからなかったものは「×」など表示します。
★ステップ(2)　「シート２でシート１になあるデータを探す。」
ステップ１と同様のことをシート２に対して実施する。
★ステップ(3)　「さまる！」
シート１、シート２から「×」にマークされているものだけをシート３にコピル（手作業）

こんな感じでどうでしょう？

お礼

ご回答ありがとうございます。
私も本当はアクセスを使いたいところなのですが、派遣先にはソフトがなく、面倒ながらにしてエクセルで。。。
関数でだとクッション置きながらになっちゃいますよね。
ありがとうございました。