OFFSET関数で別ファイルを参照する｜#VALUE!回避
ここでは、「OFFSET関数で別ファイルを#VALUE!エラーなく参照する方法」を解説します。
OFFSET関数は、そのまま使うと別ファイルを閉じた時に#VALUE!エラーになってしまいます。
その回避方法です。
１．まずはやりたいことを確認
まずはやりたいことを、例を含めて確認します。
１－１．OFFSET関数で別ファイルをエラーなく参照したい
やりたいことは、「OFFSET関数で別ファイルをエラーなく参照する」です。
そもそもOFFSET関数とは、「基準のセルから指定した行列の数だけシフトしたセルの値を返す」関数です。
動きはINDEX関数と似ていますが、範囲を指定しなくていいのでINDEX関数よりも動きが軽くなっています。
しかし一方で、「別ファイルを参照するとエラーになる」デメリットもあります。そのため、とても便利な関数なのにも関わらず、使用用途が制限されてしまいます。
１－２．例で確認
一つ例を見てみましょう。
まず、Book1に上のような表があるとします。
そしてBook2にOFFSET関数を使って、Book1のセルA1を基準として下に1、右に1移動したセルB2の値「aaa」を取得したいとします。
するとOFFSET関数は、#VALUE!エラーになります。これは、OFFSET関数が「開いていないファイルの中身を参照できない」からです。
厳密には、数式を作るときは参照先のファイルを開いているのでエラーにならず、後でエラーに気づきます。
しかしこのようなエラーは、ひと手間で解消することができます。
２．エラー回避にはコピーシートを作成する
OFFSET関数で、別ファイル参照時の#VALUE!エラーを回避するには、「コピーシートを作成する」のが簡単です。
２－１．コピーシートとは
コピーシートとは、「他のファイルにあるシートのコピーを作る」ことを言います。
具体的には、OFFSET関数を使うファイルに新しいシートを作成し、そのシートに「＝」を使って参照したい別ファイルの値をリンクさせます。
後は、コピーシートに対してOFFSET関数を使います。
このようにすると、参照先のファイルが閉じられていても最新の値を取得することができ、結果OFFSET関数もエラーになりません。
２－２．手順を見てみる
例を見ながら手順を確認します。
（１）新しいシートを作成する
まずは、OFFSET関数を使うファイルに新しいシートを作成します。
画面下方の「+」を押すと、新しいシートが作成できます。
（２）参照先のシートと「＝」でリンクさせる
次に、「＝」で参照先のシートの値をリンクさせます。
（３）OFFSET関数を使った数式を作る
最後に、新しく作ったシートに対してOFFSET関数を参照させます。
これで、参照先のファイルが閉じてしまっても#VALUE!エラーにはならなくなります。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】