おしえて

【Excel】VLOOKUP関数で複数のシートから検索する方法

締切済

質問者：とも吉
質問日時：2019/06/22 21:54
回答数：6件

ブックA（ファイルの中に月曜～土曜までの各シートがある）の中から
ブックBの条件と一致するデータを抽出したい場合、VLOOKUP関数で
範囲指定ができません。
ブックAのシート全てを範囲指定したい場合の
関数を教えていただけないでしょうか。
当方、マクロは全く分かりません・・・
よろしくお願いします。

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (6件)

最新から表示
回答順に表示

No.6

回答者： d-q-t-p
回答日時：2019/06/24 11:15

Excelのバージョンが不明ですが PowerQueryを使えば複数の

テーブル情報を結合することができます。また komugi3333
さんも書いている通りデータベースクエリでも手間をかけれ
ばできます。

> 当方、マクロは全く分かりません・・・
こういっては何ですがそれならそもそも「○○別」でシートを
分けてはいけませんね。今からでも「曜日」列を追加して全て
のシートを１つにまとめることをお勧めします。

何でも数式でやりたいなら Excelはあきらめた方がいいです。

そして今の形で何とかしたいなら「～は分かりません」といっ
てたら何ともならないので勉強してください。そのためのアド
バイスならしますが「数式で何とか」といってたらかえって
処理が重くて難解なものになることは間違いないです。

- 3
- 件

通報する

No.5

回答者： komugi3333
回答日時：2019/06/23 17:13

直接の回答でなくてすみません。

関数ではないので大変申し訳ございませんが、関数やVBAとは別の方法として、「SQL」というものがあります。

「SQL」は「Microsoft Query」という機能や、VBAの「QueryTableオブジェクト、ADO、DAO」などで使えます。

例えば「Microsoft Query」という機能からSQLを使う場合、たとえば「ブックB」の好きなシートにて「Microsoft Query」を使って、以下のように書くと、「ブックA」の 7つのシートを単純に縦に結合しながら・同時に・吸い込むことができます。

SELECT '月曜日' AS [曜日], 日付, 氏名 FROM `D:\1\A.xlsm`.`Sheet1$`
UNION ALL
SELECT '火曜日' AS [曜日], 日付, 氏名 FROM `D:\1\A.xlsm`.`Sheet2$`
UNION ALL
SELECT '水曜日' AS [曜日], 日付, 氏名 FROM `D:\1\A.xlsm`.`Sheet3$`
UNION ALL
SELECT '木曜日' AS [曜日], 日付, 氏名 FROM `D:\1\A.xlsm`.`Sheet4$`
UNION ALL
SELECT '金曜日' AS [曜日], 日付, 氏名 FROM `D:\1\A.xlsm`.`Sheet5$`
UNION ALL
SELECT '土曜日' AS [曜日], 日付, 氏名 FROM `D:\1\A.xlsm`.`Sheet6$`
UNION ALL
SELECT '日曜日' AS [曜日], 日付, 氏名 FROM `D:\1\A.xlsm`.`Sheet7$`

（上記の例では、ブックAが「D:\1」というフォルダに入っていて、かつ、すべてのシートに、「日付」という列と「氏名」という列が存在することが前提です。その他にも吸い込みたい列があれば、SELECT からFROMのあいだに、半角のカンマと列名を書き足せばOKです。ただ、セル側の列名の最後尾にスペースなどが隠れているとエラーになるかもしれませんのでそういった注意が必要ですが・・・。※UNION ALLではなく、UNIONを使うと重複分が勝手に消されてしまうので、必ず「UNION ALL」を使います。）

あとは、「ブックB」側でオートフィルタでもかければOKかと思います。
（※SQLに詳しくなれば、Microsoft Queryの画面の中だけで絞り込んで一発で完結もできます。）

「Microsoft Query」自体が、慣れないと扱いづらいといえば扱いづらいので、この方法を推奨するものではありませんが、今回のご質問のような内容が多発するようでしたら、方法のひとつとしてご検討されてはいかがでしょうか？

なお、シートを結合しなくてもいいならピボットテーブルにて、外部データの吸い込み見たいな感じで、ピボットのフィルタ機能を利用してやる方法も無いではないと思います。

- 2
- 件

通報する

この回答へのお礼

すみません・・・何を言っているのか・・・
さっぱりわかりません＞＜
書いていただいて申し訳ないです。。。

通報する

お礼日時：2019/06/23 18:02

No.4

回答者： fujillin
回答日時：2019/06/23 15:29

こんにちは

＞VLOOKUP関数で範囲指定ができません。
複数シートのデータを対象にできる方法があれば、解決と解釈しました。
No1様がご指摘のように直接複数のシートを指定することはできませんが、いくつか方法は考えられると思います。
ピックアップするデータ数がひとつ以下なのか、複数存在するのかによっても多少の違いはありますが、以下のような考えを応用することで実現可能ではないかと思います。

例えば、元データのブックに「まとめシート」を作成して、一旦全てのデータを集めておいて、検索する際はこちらのシートを対象にする方法が考えられます。
纏めるといっても単純な参照で済みますので、参照の関数をあらかじめ設定しておくだけです。
エクセルの行数は100万余ありますので、全データ数がこれを越えるようであれば、既にエクセルの範囲というよりも、データベースを利用すべき規模と言えるでしょう。
シート数が7とのことなので、1シート当たりのデータが14万以下であれば、範囲固定で参照式を作れるので更に簡単になります。

あるいは、作業行を7行設けておいて、一旦、各シートの検索結果をそこに表示しておくほうが、仕組みの作成手間はもっと少なくて済みそうです。
最終結果は、作業行を参照して該当するものをピックアップするようにしておけば良いことになります。

作業行を設けたくなければ、No1様の回答にもありますが、検索式を7つ連結した（やや長い）関数式を作成することでも、同様のことが可能になるでしょう。
シート数が7つに限定できて、さほど多くはないので、ヒット数をひとつ以下に限定できる場合は、この方法もありと思います。

- 0
- 件

通報する

この回答へのお礼

ありがとうございます。
検索式を作る時間と、データをひとつに纏める時間を考えたら
一つにまとめる方が時短になるのかなって思いました。

通報する

お礼日時：2019/06/23 18:26

No.3

回答者：銀鱗
回答日時：2019/06/23 15:11

んーと。

同じシート上にある場合はデータを抽出できるということでしょうか？

もしそうであるなら、他のシートやブックを参照する方法が分からないということですよね。
同じシート上のセルを参照する場合は
　＝A1
　＝A1:B10
のようにセル番地を指定します。
異なるシート上にあるセルを参照する場合は
　＝Sheet2!A1
　＝Sheet2!A1:B10
のようにシート名とセル番地を「!」で繋いで指定します。
異なるブックのセルを参照する場合は
　=[C:\data\example.xlsx]Sheet2!A1
　=[C:\data\example.xlsx]Sheet2!A1:B10
のようにブックのパスとファイル名を「 [　] 」で囲みシート名とセル番地を指定します。

以下の例は範囲指定なので、これだけではエラーになります。例としてSUM関数の範囲として割り当ています。

列全体を範囲とする場合は、列記号のみを指定します。行番号は指定しません。
　＝SUM(A:A)
　＝SUM(A:F)
　＝SUM(Sheet2!A:A)
　＝SUM(Sheet2!A:F)
　＝SUM([C:\data\example.xlsx]Sheet2!A:A)
　＝SUM([C:\data\example.xlsx]Sheet2!A:F)

シート全体を指定する場合は、セル番地や列ではなく行番号を指定するのが一般的です。
　＝SUM(1:1048576)
　＝SUM(Sheet2!1:1048576)
　＝SUM([C:\data\example.xlsx]Sheet2!1:1048576)
Excelのバージョンによってはこの行番号は異なるので、
　何も入力されていない列のセルを選択し、Ctrl+↓で最終行のセルを選択させるか、
　適当な列全体を選択した直後に、Shift+Enterで最終行をアクティブにするなど、
使用しているExcelの最大行を確認すると良いでしょう。

・・・
と、まあ、質問者さんがつまずいている点を予想して回答してみましたが、具体的に何が分からないのでしょうか。
単純にVLOOKUP関数の使い方が分からないということでしょうか？