こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル 2枚のシートの違いを抽出したい

目を通していただきありがとうございます。
エクセルは全く詳しくないため、質問させていただきます。

sheet1とsheet2に各2000行ほどの表があります。

sheet1
A列(商品) B列(産地) C列(定価) D列(特価) E列(数量)
 みかん    愛媛     100      80       500
 みかん    静岡     120      85       400
 オレンジ  アメリカ     150      90       100
 
sheet2
A列(商品) B列(産地) C列(定価) D列(特価) E列(数量)
 みかん    愛媛     100      80       300
 オレンジ  アメリカ     150     100       100
 いちご     福岡     300     150       100
 みかん    静岡     120      70       400

それぞれ「1行目のみかん」はA~D列まで同じ値で、E列のみの違いですので、
抽出の必要はありません。
sheet2の3行目のいちごはsheet1にはありませんので、これも抽出の必要はありません。


sheet1の2行目とsheet2の4行目のみかん、
sheet1の3行目とsheet2の2行目のオレンジ、はD列(特価)の違いがあります。
この<D列のみ違いがある>ものを行単位でsheet3に抽出したいのですが、
何か良い方法(関数等)がありましたら、教えていただけないでしょうか?

なお、表題に「エクセル」と書きましたが、実際はKingsoftのSpreadsheets 2012を使用しています。
基本的な関数でしたらそのまま使えるようですので、エクセルとして書かせていただきました。

よろしくお願いいたします。





 
  

投稿日時 - 2014-02-24 20:44:35

QNo.8488947

困ってます

質問者が選んだベストアンサー

 今仮に、Sheet4のA列とB列を作業列として使用するものとします。

 まず、Sheet4のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(COUNTIFS(Sheet2!$A:$A,INDEX(Sheet1!$A:$A,ROW()),Sheet2!$B:$B,INDEX(Sheet1!$B:$B,ROW()),Sheet2!$C:$C,INDEX(Sheet1!$C:$C,ROW()),Sheet2!$D:$D,"<>"&INDEX(Sheet1!$D:$D,ROW()),Sheet2!$E:$E,INDEX(Sheet1!$E:$E,ROW())),10000000+ROW(),""))

 次に、Sheet4のB2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet2!$A:$A,ROW())="","",IF(COUNTIFS(Sheet1!$A:$A,INDEX(Sheet2!$A:$A,ROW()),Sheet1!$B:$B,INDEX(Sheet2!$B:$B,ROW()),Sheet1!$C:$C,INDEX(Sheet2!$C:$C,ROW()),Sheet1!$D:$D,"<>"&INDEX(Sheet2!$D:$D,ROW()),Sheet1!$E:$E,INDEX(Sheet2!$E:$E,ROW())),20000000+ROW(),""))

 次に、Sheet4のA2~B2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

 次に、Sheet3のA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet4!$A:$B),"",INDEX(CHOOSE(INT(SMALL(Sheet4!$A:$B,ROWS($2:2))/10000000),Sheet1!$A:$E,Sheet2!$A:$E),MOD(SMALL(Sheet4!$A:$B,ROWS($2:2)),10000000),COLUMNS($A:A)))

 次に、Sheet3のA2セルをコピーして、Sheet3のB2~E2の範囲に貼り付けて下さい。
 次に、Sheet3のA2~E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。


 これで、<D列のみ違いがある>データのみが、Sheet3に抽出されます。

投稿日時 - 2014-02-25 04:17:24

お礼

丁寧な回答ありがとうございました。
sheet1~4までも回答に載せていただき、非常に助かりました。
仕事時間の短縮にもつながりそうです。
本当にお世話になりました。

投稿日時 - 2014-02-26 19:34:28

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

Sheet2のデータをSheet1にマッチングして、
マッチングした結果を別シートに表示することですね。
アドインを使って、Excelでデータのマッチングができます。
データ1、データ2を選択して、マッチング条件でA,B、C、D列を選択すれば
一括でできます。

投稿日時 - 2014-02-25 14:18:23

お礼

回答ありがとうございました。
色々な方法があるのですね。勉強になりました。

投稿日時 - 2014-02-26 19:12:32

ANo.2

No1の回答の補足です。

No1の回答はすべての列が一致するデータも抽出してしまいますので、D列だけ一致しないデータを抽出したいなら以下のような関数が簡単です(Excel2007以降のバージョン)。

Sheet1のF列(F2セル)に以下の式を入力してオートフィルコピーしてください。
この列で「1」が返る行が該当データですので、オートフィルタでこの行データを絞り込んで、新規シートにコピー貼り付けしてください。

=COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2,Sheet2!C:C,C2,Sheet2!D:D,"<>"&D2,Sheet2!E:E,E2)

投稿日時 - 2014-02-24 23:54:46

お礼

No.1~2と丁寧な回答、ありがとうございました。
補足も、とてもわかりやすかったです。
お世話になりました。

投稿日時 - 2014-02-26 19:44:44

ANo.1

Excelなら以下のような一般機能を利用した操作でご希望のデータを抽出することができます。

リストのD列とE列を入れ替え(列範囲を選択してShiftキーを押しながらドラッグ)、A,B,C,E,Dれ鵜の順に並べ替えます。
次に項目を含めたSheet1のA~D列のデータ範囲を選択して、データタブの「詳細設定」をクリックして、この範囲がリスト範囲に登録されていることを確認して、検索条件範囲にSheet2のA~D列のデータ範囲を選択し「OK」すれば、必要なデータだけが抽出されていますので、その範囲を選択して別シートにコピーします。
最後にD列とE列を入れ替えれば完成です。

#Excelなら関数(配列数式)を使えば、ご希望のデータを表示することができますが、表示データ数が多くなるとシートの動きが重くなるのであまりお勧めできません。

投稿日時 - 2014-02-24 22:49:17

お礼

No.1~2と丁寧な回答、ありがとうございました。
補足も、とてもわかりやすかったです。
お世話になりました。

投稿日時 - 2014-02-26 19:43:49

あなたにオススメの質問