VBA マスター機種と一致 別シート 抽出

質問

エクセルVBAで悩んでいます。
1枚目のシートに2000列くらいのデータがあり、
2枚目のシートに自分の抽出したいマスター機種一覧があります。
そして3枚目のシートに1枚目データと2枚目マスターとが一致したものだけを
全て抽出したいと考えております。
　　　　A　　　B　　　　C　　　　D　　　　　E　　　　　F　　　　G
1　　品目　　日付　　在庫数　　輸送手段　　注文数　　賞味期限　注文納期　
2　　りんご　　1/17　　5　　　　トラック　　　　50　　　　　2/5　　　 1/20
3　　りんご　　1/20　　8　　　　船　　　　　20　　　　　2/17　　　1/25
4　　ぶどう　　1/15　　8　　　　トラック　　　　100　　　　1/30　　　　1/20
5　　パイン　　1/16　　500　　　飛行機　　　200　　　　2/20　　　　2/1
6　　バナナ　　1/13　　500　　　飛行機　　　1000　　　2/25　　　　2/10
7　　バナナ　　1/15　　10　　　　トラック　　　　400　　　3/1　　　　　2/24

仮に上記のようなデータがあったとして、マスターにりんごとバナナの品目と注文数と注文納期だけを別シートに抽出するにはどうすればよいでしょうか？
変数とif　で一致したら、、というところまでできたのですが、りんごの列が２つあると
下の列のりんごしか抽出できないのです。2列あれば2列とも抽出したいのですが、
できません。
分かりずらいかと思いますが、分かる方がいらっしゃったら、ご教授願います。

回答No.2

real beatin（@realbeatin） · ベストアンサー

こんにちは。

> そして3枚目のシートに1枚目データと2枚目マスターとが一致したものだけを
> 全て抽出したいと考えております。

フィルターを使います。

> 2枚目のシートに自分の抽出したいマスター機種一覧があります。

「2枚目のシート」の「どこに」「どのように」
ということが要件になりますが、
こちらで仮の設定として、
　　　　A
1　　品目
2　　りんご
3　　バナナ
のように、
１行めが項目タイトル、
抽出したい「品目」はＡ２から下方向に連続して配置されている場合
を例にお応えしますので、あとはそちらで応用なさってください。

> 1枚目のシートに2000列くらいのデータがあり、
> 2列あれば2列とも抽出したいのですが、

2000行、2行という意味ですよね？
ともあれ、基データには、空の行が含まれないことが条件になりますので、
ご注意を。
因みに、ご提示のサンプルで言えば、
　　ぶどう　　1/15　　8　　　　トラック　　　　100　　　　1/30　　　　1/20
のように横並びになったデータ（レコード）をExcelでは行と呼び、
　　品目
　　りんご
　　りんご
　　ぶどう
　　パイン
　　バナナ
　　バナナ
のように縦並びになった項目（フィールド）をExcelでは列と呼びます。

> ...マスターにりんごとバナナの品目と注文数と注文納期だけを別シートに抽出するにはどうすればよいでしょうか？

　　「2枚目のシート」の「マスター機種一覧」に
　　「品目」として "りんご" "バナナ" が指定してあるようなケースで
　　"りんご" "バナナ" に該当するすべてのデータを抽出した上で、
　　「品目」「注文数」「注文納期」の３項目を
　　「3枚目のシート」に転記したい。
ということで宜しいでしょうか？
「3枚目のシート」の「どこに」「どのように」転記したいのか
書かれていませんので、
１行めが項目タイトル、
A列「品目」、B列「注文数」、C列「注文納期」
という風に、これも仮の設定をこちらでしていますので、
、あとはそちらで応用なさってください。

> 変数とif　で一致したら、、というところまでできたのですが、りんごの列が２つあると
> 下の列のりんごしか抽出できないのです。2列あれば2列とも抽出したいのですが、
> できません。

ご自分で書いた記述を提示されたなら、その記述への修正を加える事は可能です。
ただ、抽出などを扱う場合の多くで、Excelの一般機能を活用する方が、
簡易に短時間で処理できることが多いです。
今回の例でも、Excelの一般機能のフィルターを使うのがお奨めです。

尚、
「1枚目のシート」という表現に合わせて、
Sheets(2)のようにシートの並び順による指定方法でお応えしていますが、
シートの並びは容易に変更されてしまうものですから、
ＶＢＡではあまり使われない指定方法です。
なるべく、Sheets("Sheet1")　Sheets("Sheet2")　Sheets("Sheet3")
等のように、シートに設定されている実際の名前で指定するようにしてください。

不足・不明・応用の相談、などあれば"具体的に"補足してみてください。

' ' ///

Sub ReW9114698()
Dim aryCrit　'　「品目」を配列として格納しておく変数

　　' ' 「2枚目のシート」
　　With Sheets(2)
　　　　' ' マスター機種一覧から「品目」を配列として取得。
　　　　' ' 「品目」が、A2から下方向に連続して配置されている場合の例
　　　　aryCrit = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Value
　　End With

　　' ' 取得済の「品目」配列を一次元配列に置換。
　　aryCrit = Application.Transpose(aryCrit)

　　' ' 「1枚目のシート」
　　With Sheets(1)
　　　　If .FilterMode Then .ShowAllData　'　フィルター抽出状態を解除（全表示）

　　　　' ' 「品目」配列をキーにフィルターで抽出。
　　　　.Cells.CurrentRegion.AutoFilter Field:=1, Criteria1:=aryCrit, Operator:=xlFilterValues

　　　　Application.ScreenUpdating = False　'　Excelの描画一時停止

　　　　' ' 抽出されたデータの内、「品目」「注文数」「注文納期」にあたる「A列,E列,G列」をコピー
　　　　.Range("A:A,E:E,G:G").Copy

　　　　' ' 「3枚目のシート」→ Sheets(3)　の　「セルA1」→.Range("A1")　を先頭に貼付け
　　　　Sheets(3).Range("A1").PasteSpecial

　　　　If .FilterMode Then .ShowAllData　'　フィルター抽出状態を解除（全表示）
　　End With

　　Application.CutCopyMode = 0　'　コピーモード解除
　　Application.ScreenUpdating = True　'　Excelの描画再開

End Sub

' ' ///

回答No.1

chie65536（@chie65535）

・下準備をします。

１．シート１の「使ってない列の２行目のセル」に、以下の式を入力して下さい（添付画像ではH列）

=NOT(ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)))

２．式を入力したセルを、必要なだけ下方向にコピーします。

３．シート２には、A列の１行目から「抽出したい物のリスト」を書きます（添付画像参照）

・抽出作業

１．シート１のH列を選んで「データ」→「フィルタ」→「オートフィルタ」を設定して下さい（H列の１行目に「▼」マークが付きます。添付画像参照）

２．次に「▼」マークをクリックして「TRUE」を選びます。

すると、添付画像のように「シート２に書いた抽出したい物」だけが抽出されます。

３．シート１全体を選択してコピー、シート３に貼り付けします。

マクロにしたい場合は「ツール」→「マクロ」→「新しいマクロの記録」を行なって、上記の「・抽出作業」の「１．」～「３．」を行なってから、マクロの記録を終了させて下さい。

この「マクロの記録」を行なうと、上記で行なった一連の作業がマクロとして「Macro1」などの名前が付いて保存されますから、後で再利用できます。