I-Iメソッド（参照範囲外出メソッド）
先の「ビッグテーブルに挑む3つの基本技」で、INDEX関数の活用がオススメと書きました。
今回それと組み合わせて使うと便利な関数を紹介します。
やや込み入ってきますが、「上級エクセル使い」への道ですのでついてきてください。
INDIRECT関数
おさらいになりますが、INDEX関数の基本構造は、
=INDEX( ①所得したいデータ列, MATCH( 検索語, ②検索語を含む列, 0))
でした。参照範囲が①②と2つあります。
INDIRECT関数は文字列をアドレスと認識する関数です。これによって、参照範囲をINDEX関数の外に文字列として出すことができます。
前回の名字ランキング表を使い試してみます。(この下にランキング表ファイルを再掲していますが、見えない時があるみたいで、「ビッグテーブルに挑む３つの基本技」でとってきてください。)
上図のようにE2セルに②の範囲、F2セル、G2セルに①の範囲を記しておきます。
F4セルにINDEX関数を図のように設定。
INDIRECTをつけることで、それぞれ参照範囲として認識されます。
ついでに、コピーした時動いて困る座標には$マークをつけておきます。
あとは数式をドラッグコピーすれば完成。
INDIRECT関数を使うことの利点
この関数を使うことの利点はなんでしょうか。
1)別なシート、別なファイルも参照できる
2)範囲の文字列をコピーして列名を変えるだけでよい
3)参照した範囲が後に残る
INDEX関数を使わなくても、別なシート、別なファイルを参照できなくはないが、数式がかなり長くなる。また範囲枠を移動させて参照範囲を変える技は使えません。
この参照範囲外出し方式だと、参照範囲を隣りのセルにコピーして列名を変えるだけですむ。（下記の参照範囲の作成方法を参考）
また最初の参照式だと、シートを別ファイルに移動した時に、数式内に古い参照が残り好ましくない。
それを防ぐには、数式を値貼り付けで数値にする。
そうすると今度はどこを参照したのかトレースできない。INDIRECTだと数値に変えても、3)の参照範囲が残ります。
結果的に数式が複雑に見えますが、まずければエラーになるし、慣れればさほどでもないです。
参照範囲は簡単に作れる
上の図の、E2、F2、G2にある参照範囲ですが、これも例のショートカットキーを使えば直観的に簡単に作れます。
1)範囲をおきたいセルにまず、= と入れる。
2)指定したい先頭セルから、例のCtrl＋Shift＋矢印キーで範囲指定する。（下図参照）
3)そのままエンターするとエラーになるが、先頭の=を削れば、範囲の文字列が残る。
4)後はそれを隣にコピーして、列名だけを変える。
INDEX関数は参照範囲の行数がそろっていないといけないのですが、こうして作成した方が間違いなく作れます。
INDIRECT関数は、これに限らず使えるので是非覚えた方がいいでしょう。
特にEXCELでは、他のテーブルを参照して、一致した時のデータを持ってくる、という操作がほとんどなので、INDEXとINDIRECTの組み合わせに慣れると抜群の威力を実感できます。特にビッグテーブルにおいては。
私のEXCEL作業の7割はこれで、I-I（アイアイ）メソッドと呼んでいます。
この記事が気に入ったらサポートをしてみませんか？