複数のエクセルのファイルにおいて、共通する項目のみを抽出する方法を教え

質問

複数のエクセルのファイルにおいて、共通する項目のみを抽出する方法を教えてください。

エクセル初心者です。
たとえば下記のようなデータ１とデータ２のファイルがあった場合、
データ１と共通する項目のみを、データ２から抽出するにはどうすればよいのでしょうか。

●データ１（今月の購入分）

品名コード　品名　　　価格　　購入店名

１　　　　　りんご　　100円　　C店　　
５　　　　　みかん　　300円　　B店
１　　　　　りんご　　120円　　D店
４　　　　　すいか　　900円　　E店

●データ２（今年の購入分）

品名コード　品名　　　　購入店名

１　　　　　りんご　　　C店
３　　　　　かぼちゃ　　B店　
５　　　　　みかん　　　B店
６　　　　　にんじん　　A店
１　　　　　りんご　　　D店
４　　　　　すいか　　　E店

上記の表のように、すべての項目がデータ１と２で共通しているわけではありません。
それぞれ別の人が入力したデータのため、入力形式や順序などが揃っていないのです。
確実に双方に入力されているデータは品名と購入店名のみなので、
その二つの情報を元に、共通する項目を抜き出すしか方法がなさそうなのです。

このデータ１とデータ２の表を利用して、
データ２（今年度の購入分）からデータ１（今月の購入分）のみを抽出することは可能でしょうか？

最終的にはデータ２を下記のような形にしたいのです。

品名コード　品名　　　購入店名

１　　　　　りんご　　　C店
５　　　　　みかん　　　B店
１　　　　　りんご　　　D店
４　　　　　すいか　　　E店

実際はひとつひとつ抽出するには途方もない量のデータなので、
知恵を貸していただけるととても助かります。

回答No.4

tom04 · ベストアンサー

NO.1です！
補足を読ませてもらいました。

hakkairoさんが仰っている、三つの問題点の最初の二つについてはまったく問題ないデータ量だと思います。
行数が万単位でもちゃんと表示されるはずです。

一つ気になるのが三点目です。
「品名コードと店名がまったく同じデータが複数存在する」ということなので前回のSheet2の作業列D2セルの式では
最初のデータのみしか抽出しませんので、結果に同じものが複数表示されても良いのであれば
D2セルの数式を
=IF(COUNTBLANK(A2:C2),"",IF(COUNTIF(Sheet1!E:E,A2&B2&C2),ROW(),""))

に訂正してみてください。

尚、最後にある
＞ちなみに、作業列２には数字が「0～4」までしか表示されていませんでした。

に関しては「0」という数値が出ること自体がおかしいですねぇ～！

数式の　ROW()　となっていますのでその行番号が表示されるはずです。
この件に関してはちょっと判りかねます。

この程度で解決できないかも知れませんが・・・
ごめんなさいね。m(__)m

お礼

遅くなりましたがありがとうございます！

作業列にはやはり０～４までの数字しか表示されませんでしたが、
抽出するものには数字が入るという判断材料とすることができたので、
フィルタをかけて数字が入っている項目のみを抽出しました。

本当に助かりました！

回答No.3

KURUMITO

分かりやすいようにデータ１をシート１に、データ２をシート２に貼り付けることにします。その上でお求めの表をシート３に作るとしてシート３のＡ１セルには次の式を入力してＣ１セルまでオートフィルドラッグします。

=IF(COLUMN(A1)=1,Sheet1!$A1,IF(COLUMN(A1)=2,Sheet1!$B1,IF(COLUMN(A1)=3,Sheet1!$D1,"")))

Ａ２セルには次の式をＣ２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(Sheet1!$B1="","",IF(COLUMN(A2)=1,INDEX(Sheet2!$A:$A,SUMPRODUCT((Sheet2!$B$1:$B$5000=$B2)*(Sheet2!$C$1:$C$5000=$C2)*ROW($A$1:$A$5000))),IF(COLUMN(A2)=2,Sheet1!$B2,IF(COLUMN(A2)=3,Sheet1!$D2,""))))

お礼

遅くなりましたが回答ありがとうございます。

質問文に書いた表は仮のもので、実際の表とはだいぶ異なる部分があり、
回答者様に書いていただいた式の内容を完全に理解する能力が私になかったため、
式をそのまま入力するという形では対応することができませんでした。

今後、勉強して知識がつきましたら参考にさせていただきます。

回答No.2

noname#204879

[データ]→[フィルタ]→[フィルタオプションの設定]を勉強しませう。

お礼

遅くなりましたが回答ありがとうございます。

今回は急いでいたのでこのような形で質問させていただきましたが、
今後のために自分でも勉強を重ねようと思います。

回答No.1

tom04

こんばんは！
一例です。
質問は別ファイル（別ブック）になっていますが、↓の画像は別Sheetでやっています。
別ファイルでも方法はまったく同様です。
質問を読ませてもらうと商品はコードによって決まっているみたいなので、コードと購入店だけで判断できると思います。

両方のSheetに作業用の列を設けています。
Sheet1（データ1）のE2セルを
=A2&D2
として、フィルハンドルの（+）マークでダブルクリック、またはオートフィルで下へずぃ～～～！っとコピーします。

Sheet2（データ2）のD2セルに
=IF(COUNTIF(Sheet1!E:E,A2&C2)=1,ROW(),"")
という数式をいれ、同様にオートフィルで下へコピー！

最後にSheet2のF2セルに
=IF(COUNT($D:$D)<ROW(A1),"",INDEX(A:A,SMALL($D:$D,ROW(A1))))
という数式をいれ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

こんなんで良かったのですかね？m(__)m

補足

詳しい説明ありがとうございます。
教えていただいた通りにやってみたところ、こちらの表では確かにできました。

が、実際のデータでは上手くできませんでした…何故でしょうか。
抽出できないデータがどうしても残ってしまうのです。

原因として考えられるのは下記の三点かと思うのですが…

・データ１のデータが多すぎる（100行以上）
・データ２のデータが多すぎる（1000行以上）
・品名コードと店名が全く同じデータが複数存在する

ちなみに、作業列２には数字が「0～4」までしか表示されていませんでした。