ExcelのMATCH関数の検査範囲の指定について

質問

Excelで、指定した値の場所（行番号）を返すというのをMATCH関数を用いてやっているのですが、検査範囲の指定が上手く処理することができないので教えて下さい。

条件----------------------------------------------------------------------------------------------------------------
2つのExcelファイル、DATA.xlsx　と　TEST.xlsx　を用いる。
DATA.xlsx内に「B19」というシートが存在し、このシート内のB列にある"7777"という値が存在する行番号をTEST.xlsx内のセルに表示させる。
-----------------------------------------------------------------------------------------------------------------------

まず、
=MATCH(7777,[DATA.xlsx]B19!$B:$B,0)

で正しい値が返ってくることは理解しています。
この式の中の、B19というシート名について、
この式が存在するシートと同じシートの適当なセル（ここではA33を使うことにします）にB19
という値が存在するとします。このA33のセルにある値を見て値を返す、何がしたいかといいますと、将来参照するシートがB19ではなく例えばB15であったり、B6とした場合、A33のセルの値を変更すればよいというものにしたいのです。

それで、
=MATCH(7777,"[TEST.xlsx]"&$A33&"!$B:$B",0)

としてみたのですが、#VALUE!が返ってきます。
何処が間違っているのか？教えてください。
よろしくお願いします。

回答No.3

Chiquilin · ベストアンサー

いくつかあります。

１つ目は「"」で囲ったものは文字列にしかならないということです。
A1に「10」と入力して B1に「=SUM("A1")」としても参照してくれな
いのと同じです。文字列をセルの参照に変換する処理が抜けています。

次にシート名について。数字を含むシート名などによっては参照する
際「'」で囲む必要があります。「='B19'!A1」のように。

なので今の情報だけで考えるなら

=MATCH(7777,INDIRECT("’[TEST.xlsx]"&$A33&"’!$B:$B"),0)

こうなります。

ただし INDIRECT関数は参照元のファイルを開いておかないと使え
ない関数です。使い方を考えた場合に本当にこれで上手く利用でき
るのかどうかについては疑問を感じます。

また INDIRECTは揮発性関数です。重い処理の関数と組み合わせる
のは避けた方がいいと思います。表全体の運用に影響します。

INDIRECTを使わず それぞれのシートに対して全て検索したものを
どこかに表示させて その中から該当するシートのデータを参照した
方がまだましくらいです。

お礼

回答有難うございます。お礼が遅くなり申し訳ございませんでした。何度も御礼コメントを送ろうとしたのですが、エラーで送れませんでした。
詳しい解説に加えアドバイスも下さりありがとうございます。勉強になりました。
ありがとうございました。

回答No.2

kagakusuki

INDIRECT関数でシート名を指定する際において、例えば

Sheet1 (2)

の様なコピーシートや

2015年8月

等の様な「数字から始まっているシート名」を持つシート、及び

B19

等の様な「数字で終わっているいるシート名」を持つシートを指定するためには、

INDIRECT("'[DATA.xlsx]Sheet1 (2)'!$B:$B")

や

INDIRECT("'[DATA.xlsx]B19'!$B:$B")

の様に「'」で囲んだ形で指定しなければなりません。
　一方、例えば

Sheet1

の様な「コピーシートでも、『数字から始まっているシート名』を持つシートでも、『数字で終わっているいるシート名』でも無いシート」の場合は、

INDIRECT("[DATA.xlsx]Sheet1!$B:$B")

の様に「'」で囲んでいない形でも、

INDIRECT("'[DATA.xlsx]Sheet1'!$B:$B")

の様に「'」で囲んだ形でも、どちらの形式で指定しても構いません。
　ですから、A33セルに、コピーシートのシート名や「数字から始まっているシート名」及び「数字で終わっているいるシート名」が入力されている場合にも対応するためには、次の様にする必要があります。

=MATCH(7777,INDIRECT("'[DATA.xlsx]"&A33&"'!B:B"),0)

　但し、これだけでは「A33セルに誤ったシート名が入力されている場合」や「指定されたシートのB列に7777という値が入力されているセルが存在しなかった場合」にはエラーとなってしまいますので、次の様にされた方が良いと思います。

=IF(A33="","",IFERROR(MATCH(7777,INDIRECT("'[DATA.xlsx]"&A33&"'!B:B"),0),IF(ISREF(INDIRECT("'[DATA.xlsx]"&A33&"'!B:B")),"(該当データ無し)","(該当シート無し)")))

お礼

回答有難うございます。お礼が遅くなり申し訳ございませんでした。何度も御礼コメントを送ろうとしたのですが、エラーで送れませんでした。
詳しい解説ありがとうございます。今回のことだけではなく、今後にも役立ちます。
ありがとうございました。

回答No.1

bunjii

INDIRECT関数を使って範囲を指定しないとエラーになります。
=MATCH(7777,INDIRECT("[TEST.xlsx]"&$A33&"!$B:$B"),0)
検証していませんので、駄目なときはエラーの種類を補足してください。

お礼

回答有難うございます。お礼が遅くなり申し訳ございませんでした。何度も御礼コメントを送ろうとしたのですが、エラーで送れませんでした。
教えて頂いたとおりにしましたら、上手くいきました。
ありがとうございました。