行の挿入や列の挿入をすると関数の参照も追従して移動参照してきます。
例えば下図のようなイメージです。
参照は「Ｃ5」を参照している状態です。
この状態で４行目と５行目の間に行の挿入をしてみましょう。
参照も同じように行方向に1行ずれて、「Ｃ6」になってます。
一見便利な機能ですよね。
しかし、ある場面ではこの行・列方向への参照のズレが迷惑になってきます。
たとえば下図のように体温記録を入力しているとします。
最新データは行挿入してＣ6に常に書き込んでいくとしましょう。
この際に新しくデータを追加すると…
参照がズレています。
これでは最新データとは言えませんね。
VBAでデータを収集してモニターで最新データを表示する際にも
このような悩みに直面すると思います。
前置きが長くなりましたが、今回はこのような行・列の挿入に影響されることなく、
参照できる方法について説明したいと思います。
■関数の仕組み
=INDIRECT(参照するセル番地の文字列,参照方法)
参照するセル番地の文字入力：参照したいセル番地の文字を入力
参照方法：TRUE…A1方式 or FALSE…R1C1方式
■文字入力でセルを参照する
では実践です。
関数の仕組みは下記の式ですね。
=INDIRECT(参照するセル番地の文字入力,参照方法)
では先ほどの体温管理表を参照して見ていきたいと思います。
最新データは常に「C6」に格納されることとします。
つまり参照するセル番地は「Ｃ6」です。
では関数に当てはめていきましょう。
関数式はこのようになります。
=INDIRECT(“C6”,TRUE)
ちなみに参照方法がTRUEとなっていますが
入力しなくてもTRUEと同じA1方式となりますので
=INDIRECT(“C6”)
でもかまいません
FALSEのR1C1方式とはR+行数+Ｃ+列数で指示する方法です。
上記の式をR1C1方式で打ち込むとすると…
=INDIRECT(“R6C3”,FALSE)
このようになります。
ただ通常使用する際はあまり使用しないので、こちらは覚えなくても大丈夫です。
では実際に打ち込んでみましょう。
うまく反映できてますね。
問題はここからです。行を挿入して新しい情報を入れても「Ｃ6」を参照すれば成功です。
結果はこちら！
うまく参照できています。
これで列や行を追加してもズレることがない参照となりました。
■セルに入力されている文字列でINDIRECT関数を使用する
ちなみにINDIRECT関数は直接、文字入力をする方法以外に
セルに入力されてある文字を使用しても参照できます。
例えば下記のように最新データの番地が入力されてあるセルがあったとします。
「Ｃ4」に入力されている文字ですね。こちらのセルを参照してみましょう。
再度、関数の仕組みを見てみましょう
=INDIRECT(参照するセル番地の文字列,参照方法)
こちらの参照するセル番地の文字を「Ｃ4」に入力してある文字を参照してみましょう。
関数に当てはめるとこのようになります。
=INDIRECT(C4)
では実際に打ち込んでみましょう。
うまく機能してしますね。
さらにこんな入力方法もあります。
C4に「Ｃ」
Ｃ5に「7」がある状態で
Ｃ4とＣ5を「＆」でつなぐことで「Ｃ7」を参照することができます。
=INDIRECT(Ｃ4＆Ｃ5)
こちらでも同じ結果を参照できます。
■MATCH関数と組み合わせる
さらにMATCH関数と組み合わせると検索機能のような役割を果たすことができます。
下図のように日付を入力する項目を追加しました。
F3にはE3で入力した値が何行目にあるかMATCH関数で求めれるようになっています。
参照したい日付は12/31にしているので７行目で間違いないですね。
では結果の「7」を使用して関数に組み込みましょう。
=INDIRECT(“C”&F3)
温度を表示したいので「Ｃ」は変わりません。
そして検索結果のF3と「＆」でつなげます。
結果は…
うまく機能していますね。
ちなみに日付を変えてもその時の体温に変わってくれます。
■別シートでも参照する
さらにINDIRECT関数は別シートでも対応可能です。
例えば下記のように「田中君の体温」というシートで管理しているとしましょう。
表示させるのは別のシートです。
この場合の関数の流れはこのようになります。
=INDIRECT(“シート名!参照セル番地“)
では田中君に合わせてみましょう。
=INDIRECT(“田中君の体温!C3“)
これで別シートを参照することができます。
では実際に打ち込んでみましょう。
無事、別シートも参照してくれました。
■まとめ
いかがだったでしょうか。
表示する仕組みがあるファイルでは必要不可欠なINDIRECT関数についてまとめました。
ぜひ参考にしてみてください