【エクセル】マスターから条件一致の全データ抽出

質問

質問させていただきます。
バージョンは2003です。

一つのＢＯＯＫ内に、マスターデータsheet（以下、マスター）が一つあります。２sheet目以降は同じフォーマットで個人毎の区分でsheet分け（以下、個人シート）しており、マスターから個人名にてデータを抽出し、個人シートに一覧表を作成したいと考えております。またレギュレーションとしては、ＢＯＯＫを開いた時には自動で計算されていることです。
いろいろな関数で試しても構築できずに困っております。
またＶＢＡについては完全に素人ですが、領域的にはＶＢＡであろうと覚悟はしております。

関数でもＶＢＡでも構いません。何卒ご教授くださいませ。
よろしくお願いいたします。

■マスターデータ
　Ａ　　　Ｂ　　　Ｃ　　　
１鈴木　商品１　外注１
２山田　商品２　外注２
３田中　商品３　外注３
４鈴木　商品４　外注１
５鈴木　商品５　外注３

例えば“鈴木用”個人シートであれば、１，４，５行にある３データを個人シート上に一覧表として表示させたいです。

参考までに情報量としては、マスターデータでは29列×3000行。個人シートは100シート程度あります。

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

回答No.1

keithin · ベストアンサー

マスタシート：
　　Ａ　　　　　Ｂ　　Ｃ　　　Ｄ列
１　検索キー　 名前　商品　　項目
２　鈴木-0001　鈴木　商品１　外注１
３　山田-0001　山田　商品２　外注２
４　田中-0001　田中　商品３　外注３
５　鈴木-0002　鈴木　商品４　外注４
６　鈴木-0003　鈴木　商品５　外注５
　　　：

A2:
=IF(B2="","",B2&TEXT(COUNTIF($B$2:B2,B2),"-0000")
以下コピー

鈴木シート：
　　Ａ　　Ｂ　　Ｃ　　　Ｄ
１　鈴木　３件
２　番号　名前　商品　　項目
３　　１　鈴木　商品１　外注１
４　　２　鈴木　商品４　外注４
５　　３　鈴木　商品５　外注５

B1:
=COUNTIF(マスタ!B:B,A1)

A3:
=IF(ROW(A1)>$B$1,"",ROW(A1))
以下コピー

B3:
=IF($A3="","",VLOOKUP($A$1&TEXT($A3,"-0000"),マスタ!$A:$AZ,COLUMN(),FALSE))
右にコピー，下にコピー

＃マスタシートにどうしても絶対検索キー列を用意したくないときは，右端の邪魔にならない列なり別シートなりに作成しても構いませんが，その時はVLOOKUP関数は使えないのでINDEX(MATCH())関数などで代用します。
＃メンバーシートのA1セルやB1セルは，このシートが「鈴木」のシートだと指示するためにと計算の高速化のために用意していますが，必要に応じて何なりと工夫して作成して構いません。

＃想定データ数が多いので，良く寄せられるご相談ですが回答に出てくる配列数式では重すぎて，実用には適しません。

お礼

keithinさま

早速のお返事、どうもありがとうございました。
お陰様で解決いたしました。
本日朝から格闘していたものですから、ちょっと涙が出そうです。
本当にありがとうございました。

今回初めてＯＫＷａｖｅに登録＆質問をさせて頂いたのですが、このように知識・技術の共有を実感させて頂けたことも新たな発見でした。

keithinさま、重くて実用には適さないとのことですが、諸々工夫してみたいと思います。
ありがとうございました。

回答No.2

mu2011

簡単なマクロ（ＶＢＡ）を体感してみて下さい。
先ずは個人シートの作成だけですが以下の手順になります。

マスタには見出し行ありとしています（見出し行が無い場合、先頭に挿入して下さい）

（１）仮に個人シートのA1にマスタの担当者列同様の文字列（仮に担当者）、A2に担当者名を入力
（２）ツール→マクロ→マクロの記録開始→ショートカットキー登録（後々の操作が楽になる）
（３）データ→フィルタ→フィルタオプションの設定→指定した範囲を選択、リスト範囲欄にマスタシートの抽出列sheet1!A:C（とびとびは駄目です）、検索条件範囲欄に$A$1:$A$2,抽出範囲欄にA3→ＯＫ
（４）ツール→マクロ→マクロの記録終了

後は自動ではありませんが、各個人シートで（１）の操作とショートカットキー押下で個人別情報が抽出できます。

因みにalt＋F11キー押下→VBE画面の欄にある標準モジュール下のModule1をダブルクリックで以下の様なコードが表示されます。
AdvancedFilterで検索すれば解説サイトがあります。
Sub Macro1()
Sheets("Sheet1").Columns("A:C").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3"), Unique:=False
End Sub

お礼

mu2011

遅くにご回答頂いておりまして恐縮です。
ありがとうございました。

教えて頂いたことやってみました！
完璧に抽出できますね！
ありがとうございます！

ただこれだと、マスタの内容が変化した時に自動更新できないので、本件にては適用できないのですが、これはデータ量の多い情報処理などで非常に使いやすいテクニックだと思いますので、めちゃくちゃ助かります！今後是非使っていきたいと思います。

僕も今回の事を機にＶＢＡの領域に踏み込もうと思っております。

また何か伺うことがあるかもしれませんが、
その際はよろしくお願いいたします。

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