文字列として入力したセル番地やセルの範囲名を、関数で参照できる形式に変換する。なかなかメリットが伝わり難い関数だが、「VLOOKUP」関数と組み合わせた、仕事に役立つ実例などを紹介する。

文字列で記入されているセルの値を返すINDIRECT(インダイレクト)

 「INDIRECT」関数を使うと、参照したいセル番地やセルの範囲を文字列として指定できる。別のシートにあるデータを1つに集約したり、「VLOOKUP」関数と組み合わせて複数の表から特定の値を取り出せる。

書式 INDIRECT(参照文字列,参照形式)
参照文字列(必須) セル参照を表す文字列を指定する
参照形式(省略可) [参照文字列]の形式を指定する。「TRUEまたは省略」した場合は参照形式がA1形式になり、「FALSE」と記載した場合は参照形式がR1C1形式になる。

※通常のExcelではセルを「A1」「B2」などアルファベットで列を、数字で行を指定する。これがA1形式。一方のR1C1形式ではセルをR(行:Row)とC(列:Column)で指定する形式。「R1C1」は1行1列という意味で、A1形式では「A1」となる。

基本的な使い方

 なかなか使いこなしが難しいINDIRECT関数。機能を単純に説明すると、「参照文字列」に「A1」など、セル番地を「文字列」で指定すると、そのセルの値を表示する、となる。

 例えば下図でB1セルにE3セルの値(\1200)を表示したい場合は、「=INDIRECT("E3")」と記述すれば、B1セルに「\1200」と表示される。文字列を「参照文字列」に直接指定する場合は、「"」(ダブルクォーテーション)で囲んで指定する。「参照形式」は通常、A1形式を使うため、省略してもよい場合がほとんどだ。

上の表で、指定した「E3」に入力されている値を返す
上の表で、指定した「E3」に入力されている値を返す

 ここまで説明しても、INDIRECT関数がなぜ便利なのか分からない人も多いはず。そもそもE3の値を表示したいならB1セルに「=E3」と記載すれば済む。

 では、なぜINDIRECT関数を使うのか? 代表的な活用例としては、「別シートのデータを1つに集約する」「複数の表から特定の値を取り出す」の2つが多い。「仕事での使い方」でこの2つを詳しく紹介しながら、INDIRECT関数のメリットを実感してほしい。

【仕事での使い方1】別シートのデータを1つに集約する

各支店別に入場者数をまとめた3枚のシートがある。この中にある入場者数の合計を、「集計」シートにまとめたい。INDIRECT関数なら簡単にできる
各支店別に入場者数をまとめた3枚のシートがある。この中にある入場者数の合計を、「集計」シートにまとめたい。INDIRECT関数なら簡単にできる

 別々のシートの同じセル番地にある合計の値を、1枚のシートにまとめたい。通常、別シートの値を参照するには「=シート名!セル番地」と指定するが、シート名を手入力するのは面倒だ。

 少しでもExcelに詳しい人なら、「&」を使って文字列を作ればいいのでは? と気付くかもしれない。そこで「集計」シートのB2セルからD2セルに入力した「千代田」「港」「品川」と「&」を使って、「=千代田!B6」という文字列を作り出そうと考えてしまうのである。

 実は、この考え方には落とし穴がある。例えばB3セルに「千代田」の合計を表示するために「=B2&"!B6"」と入力してみる。B2セルには「千代田」と入っているし、これで「=千代田!B6」と同じになるはず……。ところが結果は、「2,434」とは表示されずに「千代田!B6」という文字列になってしまった。Excelは「『千代田』のシートのB6セルの値を表示する」と理解せず、単純に文字列を表示してしまったのだ。

別シートのセルを参照するには、「=千代田!B6」のように、シート名、半角の「!」、セル番地の順に書く。そこで見出しのシート名を使い、文字を結合する「&」で「=B2&"!B6"」と入力してみたが、単なる文字として認識されてしまった
別シートのセルを参照するには、「=千代田!B6」のように、シート名、半角の「!」、セル番地の順に書く。そこで見出しのシート名を使い、文字を結合する「&」で「=B2&"!B6"」と入力してみたが、単なる文字として認識されてしまった

 ようやくINDIRECT関数の出番。INDIRECT関数は、「参照文字列」にセル番号を「文字列」として指定しても、その値を参照できる関数だった。ここまでくればピンと来た方も多いはず。INDIRECT関数は、通常ではExcelが文字列として理解してしまうテキストなどを、セル番地などに変換してくれるのだ。

 だから、B3セルに「INDIRECT(B2&"!B6")」と入力すれば、ちゃんと「2,434」と表示される。「港」「品川」の合計もドラッグでコピーするだけで表示可能だ。

「=千代田!B6」をセル参照として認識させるには、INDIRECT関数でセル参照に変換すればよい。あとはこの式をコピーすれば、他の合計も表示できる
「=千代田!B6」をセル参照として認識させるには、INDIRECT関数でセル参照に変換すればよい。あとはこの式をコピーすれば、他の合計も表示できる

【仕事での使い方2】複数の別表を切り替えて転記する

一般会員と特別会員の2つの料金表がある。B3セルに入力した「一般」「特別」の値によって、転記元の表を切り替えたい。INDIRECT関数で実現してみよう
一般会員と特別会員の2つの料金表がある。B3セルに入力した「一般」「特別」の値によって、転記元の表を切り替えたい。INDIRECT関数で実現してみよう

 会員種別と施設名から、対応する料金を転記したい──。VLOOKUP関数を使えばいいが、厄介なのは会員種別が「一般」なら一般会員価格の表、「特別」なら特別会員価格の表と、転記元の表を切り替える必要のあることだ。

 この場合、2つの価格表にそれぞれ「一般」「特別」と名前を付ける。次にVLOOKUP関数の引数「範囲」を、INDIRECT関数を使ってB2セルの文字を範囲名に変換してから指定する。

 まずは、それぞれの価格表の範囲に「一般」「特別」の名前を付けよう。価格表の範囲をドラッグで指定し、シートの左上にある「名前ボックス」に名前を入力するだけだ。

名前を付けたいセル範囲を選択し(1)、「名前ボックス」に名前を入力して(2)「Enter」キーを押す(3)
名前を付けたいセル範囲を選択し(1)、「名前ボックス」に名前を入力して(2)「Enter」キーを押す(3)

 続いて、B4セルに入力する関数を見ていこう。VLOOKUP関数の「検索値」は施設の名称となるので、「B3」と記載する。「範囲」は、先ほど付けた名前の範囲を指定する。だが、単純に「B2」と記載してもExcelは文字列と認識し、範囲を参照してくれない。そこで「INDIRECT(B2)」と記載する。すると、名前を付けた範囲を参照するようになる。

 あとは、VLOOKUP関数のお作法通りに記載していく。価格は合致する施設が入ったセルの右側にあるので「列番号」には「2」と入力、「検索の型」は完全一致なので「FALSE」を指定する。これで完成だ。

VLOOKUP関数の式を立てるのだが、「範囲」には、INDIRECT関数でB2セルの文字を範囲名(「一般」「特別」)に変換したものを指定する
VLOOKUP関数の式を立てるのだが、「範囲」には、INDIRECT関数でB2セルの文字を範囲名(「一般」「特別」)に変換したものを指定する
VLOOKUP関数の引数「範囲」を単純に「B2」とすると、文字列を指定したと見なされ、エラー(#N/A)になる。INDIRECT関数を使うと範囲名になるので、エラーにならない。ちなみに「N/A」は「Not Applicable(該当なし)」や「Not Available(利用できない、入手できない)」の意味
VLOOKUP関数の引数「範囲」を単純に「B2」とすると、文字列を指定したと見なされ、エラー(#N/A)になる。INDIRECT関数を使うと範囲名になるので、エラーにならない。ちなみに「N/A」は「Not Applicable(該当なし)」や「Not Available(利用できない、入手できない)」の意味