Excel で INDIRECT でファイル名をセル参照にするとファイル開かないといけない問題を解決する

by .

　長いタイトルとなってしまいましたが、Excel数式における INDIRECT 関数の例の問題を解決する方法をブログ記事にまとめました。例の問題というのは、INDIRECＴ で他のファイル（ブック）を参照した場合に起きる問題です。

INDIRECTの#REF問題の詳細

　INDIRECT 関数を設定したときには正しく値が取得できていたのに、ファイルを開きなおしたら「#REF」になってしまうこと、ありますよね。なぜこの問題が起きるかと言うと、そもそもExcelの関数は参照先のファイルが開いている時に動作するものと、開いていなくても動作するものに分けられます。INDIRECT関数はその中でも、参照先のファイルが開いている状態でないとエラーになってしまうという制約があるからです。

　それでは実際に2つのエクセルファイルを作成してINDIRECTがエラーになることを確認しましょう。適当なExcelファイル、book1.xlsx と book2.xlsx を作成します。

　それぞれのファイルの中身として、INDIRECT関数で参照されるブック名をTarget book name としてD2セルに、INDIRECT関数で参照される文字列を「Read this string→」というセルの横（D6セル）に記入します。

　book1 から book2の値を参照します。具体的な操作としては、book1のD4セルで=を打ち込み数式入力状態にしたうえで、book2のD6セルをクリックします。

　book1に数式が入力され、book2のD6セルのない世杖ある「This is book2」が表示されました。余談ですが、この状態でbook2を閉じると、下記のようにbook2のパス（例では「=’C:\Users\xxx\Desktop\[book2.xlsx]Sheet1′!$D$6」）が数式上で展開されます。これがINDIRECTに指定すべき、正式なパスになります。INDIRECTを使用してないこの段階であれば、book2のD6セルを編集すると、book2が開いているか閉じているかに関わらず、book1のD4セルの値が更新されることが確認できます。

　次に、この値のうち、ファイル名（ブック名）にあたる[]で囲まれている箇所をセルを参照するように書き換えます。いきなりINDIRECTを書くのではなく、まずはINDIRECTのカッコ内に指定する値（例で言うと=”‘C:\Users\xxx\Desktop\[” & D2 & “.xlsx]Sheet1’!$D$6″）を書くと非常にわかりやすいです。

　ではこの数式D4をさらにINDIRECTで囲むなり、他のセルにINDIRECTを書き、カッコの中身としてD4を指定するなどして、INDIRECTによる参照を有効にします。（例ではD5セルに「=INDIRECT(D4)」を入力しています）

　この状態で、D2セルに入力した参照先のブック名をbook1、book2と交互に切り替えると、INDIRECTで参照しているD5セルの結果も「This is book1」「This is book2」と交互に切り替わります。

　book2 を参照した状態で book2を閉じると、D5セルが #REF として参照エラーになってしまうことが分かります。もし、きちんと値が表示されている、という場合はbook1を開きなおしてみてください。D5セルが#REFになると思います。同時にbook2も開くと、きちんと「This is book2」が表示されるため、数式のエラーではないことが分かります。

　それではどうすればいいのかというと、VBAのマクロを使用するというのが方法としてあります。何とかセルへの数式だけで実現したいところですが、セルの数式では実現不可能ですので仕方ありません。

　開発リボンから挿入 > フォームコントロールのボタンを挿入します。

　この時、同時にマクロを作成できますので作成しておきます。名前は何でもよいですが、今回はリロード_Clickとしておきます。

　マクロを利用して、D4セルに記載される数式を
=’C:\Users\xxxx\Desktop[” & Range(“D2”).Value & “.xlsx]Sheet1′!$D$6”
とすることで
=’C:\Users\xxxx\Desktop[book1.xlsx]Sheet1′!$D$6
=’C:\Users\xxxx\Desktop[book2.xlsx]Sheet1′!$D$6
のいずれかに動的に切り替わるようにしています。

　なお、もしリロードボタンを押した際に下記のようにファイルダイアログが開くのであれば、ファイルが存在しない（または、数式が間違っている）可能性がありますので、数式やファイルの存在の確認をしましょう。

　なお、拡張子がxlsxのまま保存すると作成したマクロが削除されてしまいますので、保存する際はマクロつきのエクセルファイル（.xlsm）として保存をお願いします。

　いかがでしたでしょうか？INDIRECTは閉じられたファイルに対して使用すると#REFのエラーが発生してしまい、それを回避するためにはINDIRECTではなくマクロを使用して外部ファイルを参照するような数式を生成する必要がある、という結果となりました。マクロまで作成するのは敷居が高い、場合によっては使えない可能性のある手段ですが、どうしても参照先ファイルをセル参照で柔軟に設定したい、という場合に使える重要なテクニックですので、ぜひお役立てください。

Excel でお困りの方は

　もし Excel でお困りのことがあれば、仙台のWebシステム受託開発のエキスパート集団であるFITSに是非お任せください。最近ですと、建築業界、建設業界のお客様より工事の原価管理、工程管理のExcelの解析、改善依頼など頂いた実績が御座います。前任者不在のExcelなどでお困りの際は、是非一度ご相談ください。

お問い合わせは
https://www.fits-inc.jp/inquiry/
からお気軽にどうぞ。システムに関する無料の相談、お見積りも受け付けております。