エクセルのデータ抽出について・・・

質問

はじめまして。
みなさまのお力が借りたく、質問いたします。
わかりずらい質問だとは思いますが、宜しくお願い致します。
例として、エクセルのファイルが2個あるとします。
A.xls
B.xls
とします。
AにはIDを入力するシートが1枚
BにはそのIDの人の名前・住所・生年月日などを規則性無く入れてあるシートが7枚程あるとします。

AのID列に「10」といれると
Bの全7枚のシートの中から該当する人のデータを抜き出し、
Aの所定のセルに表示する。といった事は可能でしょうか？

VLOOKUP関数を使ってみたのですが、検索するシートを1枚じゃないとダメみたいで・・・
シートを複数指定するとエラーになってしまいます。
問題は、抜き出したい「ID 10」が7枚のシートの中の何枚目にあるかがわからないのです。
ちなみに、該当する「ID 10」があるシートには必ず名前、住所、生年月日も同じ列に付随して存在します！

このようなことは不可能なのでしょうか？

もし可能ならばどうしたらいいか教えてください！
宜しくお願い致します。

回答No.4

tommy-pie83 · ベストアンサー

自分の勉強にと思い マクロを書いてみました。
ただしこれを使うには

Cドライブの"Test"フォルダに "Test_A.xls" "Test_B.xls"という2つのファイルが入っていることが必要です。

Test_B.xls は お使いのBのエクセルファイルを複製して名前を変えて保存してください。
2行目から データが入っており、A列にID、その右B,C,Dまで書き込みされているという設定です。

Test_A.xlsは 新しく作ってください。 ただし IDを入力するセルは ”E15” 結果を 表示するのは
”A25からD25”までになります。

最初に開くのは Test_A です。このファイルにマクロを書き込みます。

ツール（T)→マクロ→Visual Basic Editorをクリック。最大表示にしたほうがわかりやすいと思います。
プロジェクトと左上にあると思います。
Sheet１ をダブルクリックすると右に白い面が現れると思います。
何も書かれていない状態だと思いますのでここに点線の間のものをコピペしてください。
---------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim IDNum As String
Range("A25:D25").ClearContents
IDNum = Range("E15")
If IDNum <> "" Then

Call ID検索

End If

End Sub
---------------
シート１のE15に IDを入力した後 どこでも良いのでセルをダブルクリックすると
”ID検索” という プログラムに行きます。

続く

お礼

ご回答ありがとうございました。
皆様のお力を借りて、なんとかできました！！
tommy-pie83さんには、前回の質問でもご回答頂きまして、本当に感謝しております。
また、複数回に分けてご回答頂きまして大変恐縮です。
これが、マクロというものなんですね・・・
ちょっと僕には理解しかねますが、少し勉強してみようと思います^^
今回は勉強になりました。
ありがとうございました。

回答No.8

tommy-pie83

すみませんしつこくて…
前回のは 私の勘違いでした。
書き換えた Bのファイルの保存場所が違っていたためでした。

前のファイルはシートが3枚で 連続した4桁のIDを入れていましたが、再度1桁から4桁までいろいろ試しましたが大丈夫でした。数字だけでなく文字でも試してみました。

それと 最初に Testフォルダの中に Test_A を入れておく必要があると書きましたが、こちらは どこにおいてもOKでした。私自身は マイドキュメントに入れていましたので。

お騒がせしました、本当におっちょこちょいですね。

回答No.7

tommy-pie83

さっきの3回の回答はなかったことにしてもらったほうがいいみたいです。

Bのシートのデータを入れ替えて試してみたら
エラーにはならないのですけど あるはずのデータを読みに行きませんでした。
ごめんなさい。
どうしてだか 私には解らないので無理みたいです。
でも おかげで勉強になりました。

回答No.6

tommy-pie83

これで最後

次は簡単です。ファイルを閉じるときに Test_A.xlsのシート1の
A25からD25に表示されているデータを消すというだけ。
これはプロジェクトの ThisWorkbookを ダブルクリックしてそこに貼り付けてください。
---------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Range("A25:D25").ClearContents

End Sub
---------------
Test_A のシート1に IDを入力後 ダブルクリックすると Test_B を開くようになっていますので
最初から開いておく必要はありません。
また 検索が終わると閉じるようにしています。

私が試した Bのファイルは シートが3枚、データがA2から D列まで ランダムにはいったものを使いましたが
このマクロは Aのシートをダブルクリックして Bを開いたときに シートの枚数を数えて処理しますので 現在7枚だと
書いてありますが 増えても大丈夫なはずです。

もしよろしかったら試してみてください。その際 Bの各々のシートに書き込まれている 住所、氏名などの セルの位置などによって
書き換える必要がありますので、もし不明の点がありましたら また書き込んでください。

回答No.5

tommy-pie83

次は一番上の ツールバー(？だったかなこんな基本が解りません、笑い) の挿入（I）をクリックして
その中から 標準モジュールを選んでください。
さきほどの プロジェクトのなかに Module１が増えたと思います。
これをダブルクリックして また次のものをコピペ して下さい。
”ID検索”です。
---------------
Sub ID検索()

Dim Ws As Integer
Dim IDNum As String
Dim I As Integer
Dim rg As Range
Dim pop As String
Dim Result As String
Dim RW As String

IDNum = Range("E15")
Application.ScreenUpdating = False

Workbooks.Open "C:\Test\Test_B.xls"
Ws = Worksheets.Count
For I = 1 To Wｓ

With Worksheets(I)
pop = .Range("A2").End(xlDown).Address
For Each rg In .Range("A2:" & pop)
If rg.Value = IDNum Then

Result = rg.Row
RW = I
Sheets("Sheet" & RW).Activate
Range("A" & Result, "D" & Result).Select
Selection.Copy

Workbooks("Test_A.xls").Worksheets("sheet1").Activate

Range("A25").Select
ActiveSheet.Paste
Range("E15").ClearContents
Range("A1").Select
Workbooks("Test_B.xls").Close

Exit Sub
End If
Next
End With
Next I

Workbooks("Test_A.xls").Worksheets("sheet1").Activate
Range("E15").ClearContents
Range("A1").Select

Workbooks("Test_B.xls").Close

End Sub
---------------
う～ん、中身はあちこちの寄せ集めなので 私には詳しく説明できません。
なんとなくしか解りません。

再び続く

回答No.3

pipipi523

ちょっと力技的解決策
"IF"と"ISERROR"を積み重ねて・・・

=IF(ISERROR(VLOOKUP(A3,Sheet2!A2:B4,2,0)),
IF(ISERROR(VLOOKUP(A3,Sheet3!A2:B4,2,0)),
IF(ISERROR(VLOOKUP(A3,Sheet4!A2:B4,2,0)),
"",VLOOKUP(A3,Sheet4!A2:B4,2,0)),
VLOOKUP(A3,Sheet3!A2:B4,2,0)),
VLOOKUP(A3,Sheet2!A2:B4,2,0))

・・・お勧めしません。

お礼

ご回答ありがとうございました。
皆様のお力を借りて、なんとかできました！！
pipipi523さんのご回答も参考にさせて頂きました。
僕のスキルでは、pipipi523さんの提案して頂いてる方法が理解できませんでした。申し訳ありません(TДT)
しかし、今回は勉強になりました。
ありがとうございました。

回答No.2

ysko614

別シートの内容をシート毎貼り付けて下さい。

貼り付けたシートを元のシートから参照するには、名前の定義で、参照したい範囲に対して、名前の定義を行います。

方法はメニューの挿入から、名前で定義を選択し、参照するべき範囲を選択します。

実際に元のシートから別シートの範囲を見る際には、F３キーで名前の貼り付けを行います。これで別シートのデータ範囲を参照する事が可能です。

お礼

ご回答ありがとうございました。
皆様のお力を借りて、なんとかできました！！
ysko614さんのご回答も参考にさせて頂きました。
僕のスキルでは、ysko614さんの提案して頂いてる方法が理解できませんでした。申し訳ありません(TДT)
しかし、今回は勉強になりました。
ありがとうございました。

回答No.1

kakkysan

うまくお答えできるかどうか分かりませんが，自分の勉強のつもりでやってみました．

(1)bookＢの各シートの内容をbooＡの１枚のシートにまとめます．
の A1 に
=[bookB.xls]!sheet1!A1 の様にリンクを張り適当な範囲までフィルハンドルでコピー

同様に （引き続きbooＡのsheet2 に） sheet2～sheet7 の内容を順にリンクさせる
（bookBの内容が更新され，行が増えるようなら各シートをコピーするとき行数を多めに余裕を持って コピー）

(2) Vlookupを使用しますが，booＡ の sheet２のＡ列（ＩＤが入った列）は，昇順に並び替えてからlookup．
これでどうでしょうか．

蛇足ですが，もし大量のデータを扱うのでしたら，Ａｃｃｅｓｓを使うことをお勧めします．

お礼

ご回答ありがとうございました。
皆様のお力を借りて、なんとかできました！！
kakkysanさんのご回答も参考にさせて頂きました。
今後はAccessも視野にいれたいと思います。
ありがとうございました。