エクセルで、行削除や列削除、シート削除といった操作をしていると、そこを参照していた数式が、#REF!エラーになって壊れたなんてことありませんか？
今回は、INDIRECT関数を使うことで#REF!エラーになりにくい式を作ろう！というお話です。
そもそも#REF!とは？
#REF!に強い式を作ろう！・・・の前に、そもそもどういうエラーなのか、どういう時に発生するのかを確認しておきましょう。
エクセルヘルプで調べてみるとこのような結果が出てきました。
記事冒頭でも書いたとおり、やはり削除時に発生するものだそうですね。
念のため実験しておきましょう。
シンプルに、A1セルに=D1を入力し、D列を削除してみます。
このように、表示上だけ#REF!になるのではなく、数式内のD1が#REF!に置き換わり、そのうえで表示も#REF!になるという事がわかりました。
ちなみにですが、参照の仕方を=$D$1（絶対参照）にしてもダメです。
もう一つ言うと、=D1&E1の場合はD1が#REF!に置き換わり、E1は左にスライドしてD1に置き換わるので、=#REF!&D1という式になり、表示は#REF!となります。
なお、ここでいう削除というのは、DELETEキーなどによるセル内容の削除ではなく、右クリックなどによる列削除、行削除、セル削除のことです。
INDIRECT関数を使うと#REF!に強くなる
さて、行列セル削除をすると#REF!になる場合があるという事がわかりました。
しかし、参照先をINDIRECT関数を使って組むことにより、#REF!になりにくくする事ができます。
実際に、SUM関数の集計範囲にINDIRECT関数を使って試してみましょう。
SUM関数で実験
結果を比較するために、=SUM(E:E)と、=SUM(INDIRECT("E:E"))の2つの式を用意してみます。
式の内容はシンプルで、E列の数値を合計しています。
ここで、E列を削除するとどうなるでしょうか。
このように、普通のSUM関数の式は#REF!となってしまいましたが、INDIRECT関数を使っている方は崩れないで済みました。
メリットとデメリット
ここまでで紹介したように、INDIRECT関数を使うことで、#REF!になりにくいという利点があります。
反面、参照先が固定されてしまっているので、行列セル削除をしても、自動で範囲を変更してくれないというデメリットがあります。
どういう事か実験してみましょう。
下図のように、月間エサ代（H列）を集計していたとします。
ここで、性別の列要らないなって事に気づき、G列を削除するとどうなるでしょう。
普通のSUM関数の式は、集計範囲が自動で左にスライドしてG列を合計してくれました。
しかし、INDIRECT関数を使っている式は、集計範囲がH列で固定されたままになってしまいました。
このように、INDIRECT関数を使う事でたしかに#REF!になりにくい式は作れましたが、こういったデメリットがあるという事を理解したうえで活用しましょう。
参照先のシートを入れ替える事がある場合に便利
ここまでは列削除の場合でお話してきましたが、個人的に一番効果を感じるのは、別シートを参照している時です。
たとえばVLOOKUP関数で、検索先の表が別シートになっている場合。
この時、太郎リストは定期的に更新するものとします。
そして太郎リストは毎回大幅な更新があるとします。
こういう場合、太郎リストの内容を直接変更するのではなく、そもそも新しい太郎リストと、シートまるごと交換した方が作業的には楽ですよね。
ですが、普通のVLOOKUP関数の式では、太郎リストのシートを交換するために、一度シート削除すると、その時点で#REF!エラーとなってしまいます。
それではINDIRECT関数を使った式に変更してみましょう。
この式の状態で、太郎リストシートを一度削除してみます。
すると、表示上は#REF!になってしまいますが、数式は崩れずに残ったままになります。
つまり、新しい太郎リストのシートを持ってきてあげれば、無事に太郎リスト更新完了となるわけです。
このように、参照先のシートを差し替える事がある場合、INDIRECT関数を使って#REF!を回避するのは非常に便利なんです。
実例
実際にこの手法を使ってるエクセルファイルがあります。
当ブログで公開している、住所分割エクセルですね。
実在する市区町村かチェックするために、総務省が公開しているデータを使っているのですが、たまに更新が入るんですよね。
その時、部分的に手で直すと、変更が漏れてしまう事があるので、シートまるごと交換できる仕組みにしておいた方が都合が良かったのです。
つまりINDIRECT関数すごい
参照先が固定されてしまうというデメリットこそありますが、理解したうえで使うとめちゃくちゃ便利です。
このINDIRECT関数はぜひ広まってほしいので、今回の使い方以外にもINDIRECT関数が大活躍してる記事を紹介して終わります。
INDIRECT関数そのものの解説はコチラ。
コメント