２つのシートから合致するデータの抽出方法

質問

Excelについて質問させて頂きます。
二つのシートにある表からA列B列が合致する行だけを表示させたいと思います。

例;A列→商品名、B列→商品コード、C列以降→在庫数など。

シート1には、現在も販売されているもの。
シート2には、過去の分も販売されていたもの。
があります。

どのような解決策がありますか？

データベースには10000件以上のデータがあるので一つずつ照合していけません。

どなたかExcelの関数に詳しいかたがいらっしゃいましたらご教授頂けましたら幸いです。

宜しくお願い致します。

回答No.4

ushi2015

こんにちは、商品名、商品コードをキーにするなら、
シート1のセルA1を選択して、リボンのデータタブのフィルタで
詳細設定をクリックし、リスト範囲が表全体になっているはずなので、
検索条件範囲にシート2のA1からB列の最終行までを指定して、OKすればいいはずです。
もし別のシートに抽出する場合は別シートの１行目に項目名をコピーしておいてから
上記作業で抽出先をその項目部分にします。
マクロなら、
Sub test1()
　　Dim sh1 As Worksheet
　　Dim sh2 As Worksheet
　　Dim sh3 As Worksheet

　　Set sh1 = Worksheets("Sheet1")
　　Set sh2 = Worksheets("Sheet2")
　　Set sh3 = Worksheets("Sheet3")

　　sh3.UsedRange.ClearContents

　　sh1.Range("A1:C1").Copy sh3.Range("A1")
　　sh1.Columns("A:C").AdvancedFilter _
　　　　Action:=xlFilterCopy, _
　　　　CriteriaRange:=Intersect(sh2.Range("A:B"), sh2.Range("A1").CurrentRegion), _
　　　　CopyToRange:=sh3.Range("A1").CurrentRegion, _
　　　　Unique:=False
End Sub
こんな感じです。

お礼

ありがとうございました。
私自身も質問の、仕方とか、説明不足で、どの方法を行っても抽出出来なかったので今回はなくなく1個づつ絞りこみました。

これからも同じような作業が発生するので、私自身も調べてみます。

また再度、質問させて頂くかもしれませんが、その際はもっとより具体的に分かりやすく質問したいと思います。マクロの記述ありがとうございました。
マクロの世界は奥深そうですね。皆様どこで勉強されたのか知りたいです。尊敬します。

回答No.3

imogasi

エクセル初心者の場合や、仕事でいそいでやるような時はＣＯＵＮＴＩＦ関数利用がおすすめ。既出回答のような関数式は、考えて、理解する暇がない人向け。
本当はＶＢＡなどでプログラムを組んだ方がよいとは思う。
エクセルの操作機能では、ずばりの物はないし、フリーソフト見つかるかどうか疑問。
ーー
Sheet1、Ｓｈｅｅｔ２があって
Ｓｈｅｅｔ１のＡ，Ｂ，Ｃ列に
商品 商品コード カウント数
a1　 1 1
a2　33 0
a3　 3 0
a4　 4 1
a5　 2 1
a6　 3 1
a7　 56 0
a8 61 0
a9　 20 1
a10　 71 0
Ｃ列は関数　=COUNTIF(Sheet2!D2:D100,A2&"-"&B2)　で出したもの
行数は１万程度らしいが説明例なので１００までとした。
カウント数が１以上なら存在するし、０なら見当たらないということなので、ＩＦ関数を被せて任意の文字列でコメント的に出せる。
どちらを主にするか書いてないのは、質問として片手落ち。逆に考える式はわかるだろう。
こういう文字列を結合しての比較は、要注意であるが、取り急ぎ挙げてみる。質問者の場合で、うまく行くかチェックして。
ーー
Ｓｈｅｅｔ２のＡ，Ｂ、Ｄ列に（Ｄ列は作業列の役割）
商品 商品コード 結合
a1　 1 a1-1
a2　 23 a2-23
a31　 3 a31-3
a4　 4 a4-4
a5　 2 a5-2
a6　 3 a6-3
a7　 55 a7-55
a8　 60 a8-60
a9　 20 a9-20
a10　 70 a10-70
a11　 71 a11-71
a12　 80 a12-80
a13 　95 a13-95
A31　 3 A31-3
Ｄ列Ｄ２には=A2&"-" &B2と入れて出したもの。

補足

ありがとうございます。
マクロを組みたいのですが、やり方が判りません。
どこかで勉強できるサイトなどがあればうれしいです。
今は月曜までにとある支店のデータだけを抽出して提出しなければならなかったため、皆様の回答を参考にデータを抽出してみようと思いますが、
あと300以上の支店および、今後も同様な作業を行うことを考えるとマクロ作業が学べれば幸いです。
再度、質問させていいただくこともあるかもしれませんがその節はよろしくお願いいたします。

回答No.2

kagakusuki

今仮に、Sheet4のA列を作業列として使用して、"Sheet1の行にあるデータ"の中で、Sheet2の表にも「同じ商品名と商品コードの組み合わせ」があるものだけを抽出して、Sheet3に表示させるものとします。

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

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",IF(COUNTIFS(Sheet2!$A:$A,INDEX(Sheet1!$A:$A,ROW()),Sheet2!$B:$B,INDEX(Sheet1!$B:$B,ROW())),COUNTIF(Sheet1!$A:$A,"<"&INDEX(Sheet1!$A:$A,ROW()))+COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()-1),INDEX(Sheet1!$A:$A,ROW())),""))

　次に、Sheet4のA2セルをコピーして、同シートのA3以下に貼り付けて下さい。

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

=IF(ROWS($2:2)>COUNT(Sheet4!$A:$A),"",INDEX(Sheet1!$A:A,MATCH(SMALL(Sheet4!$A:$A,ROWS($2:2)),Sheet4!$A:$A,0),COLUMNS($A:A)))

　次に、Sheet3のA2セルをコピーして、同じ行のA2セルよりも右側にあるセルに、必要な列数分だけ貼り付けて下さい。
　次に、Sheet3の2行目をコピーして、同シートの4行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。
どういった仕組みになっているのか今現在は理解できていませんが、取り急ぎ月曜までにデータの抽出を行いたいので頑張って作業してみます。
また、再度質問させていただくかもしれませんがよろしくお願いいたします。

回答No.1

ushi2015

こんにちは
関数でないとダメなのですか？
A列B列が合致するという事ですが、商品コードだけでも判定出来るのですよね？
どちらのシートの表で合致する行だけを表示するのですか？
シート1なら、
セルA1から表が有るなら、そこを選択して、リボンのデータタブのフィルタで
詳細設定をクリックし、リスト範囲が表全体になっているはずなので、
検索条件範囲にシート2のB1からB列の最終行までを指定して、OKすればいいです。

お礼

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

補足

ありがとうございます。
商品名にも商品コードも同一のものがあるため、二つでワンセットで検索したいのでフィルタだけで絞りこめなかったもので。