【INDIRECT関数】徹底解説！「文字を参照に」いつ使う？引数は？

INDIRECT関数ってわかりにくいですよね。

エクセルの関数の中でももっとも使い方が分かりにくい関数のひとつじゃないでしょうか。

引数は「セル参照をあらわす文字列」
戻り値は「セル参照」

という他にない、独特のはたらきをする関数だからです。

なぜ INDIRECT関数を使うのか、どんなときに使えばいいのか。

具体例をあげながら、わかりやすく解説します。

この記事は

INDIRECT関数とはどんな関数？
実用的な使いかたの例
R1C1形式について

などの解説記事です。

INDIRECT関数でしかできない数式を組めるようになります。

INDIRECT関数ってどんな関数？
1. 「文字列」を「セル参照」に変える関数
2. 文字列とセル参照のちがい
実用的な引数の入れかた
INDIRECT関数の応用例
#REF! エラー
R1C1形式とは？
まとめ

INDIRECT関数ってどんな関数？

「文字列」を「セル参照」に変える関数

どんな関数でもそうですが、関数の使いかたを理解するにはコツがあります。

引数・戻り値のデータの型 (タイプ) を理解することです。

たとえばSUM関数は、引数・戻り値とも数値です。

INDIRECT関数の引数は「セル参照をあらわす文字列」です。

そして戻り値は「セル参照」になります。

つまり、INDIRECT関数とは「文字列」を「セル参照」に変える関数です。

この場合の「セル参照をあらわす文字列」とは

「A3」のようなセル番地
「A1:C3」のようなセル範囲
セル範囲に定義された「名前」
「Sheet3!A5」のような他のシートのセル番地

のような文字列を指します。

つまり

=INDIRECT(“A1”)

という数式は

=A1

というセル参照に変換されるということです。

この段階では何に使う関数かまったくわかりませんね。

文字列とセル参照のちがい

そもそもの「セル参照をあらわす文字列」と「セル参照」のちがいを明確にしておきましょう。

C2セルに「=A2」という数式が入っています。

通常のセル参照です。

C4セルに「=”A2″」という数式を入力しました。

これは「A2」という文字列を入力しただけです。

数式内では文字列は ” (ダブルクォーテーション)で囲みます。

逆に言うと、 ” で囲ったものはすべて文字列になります。

セル範囲でも…

“A1:A5” ではセル参照と認識されません。

C1,D1セルに入っている値を参照して & (アンパサンド)で連結しました。

「”A”&2」は「A2」という文字列になります。

& は文字列を連結する演算子です。

逆に言うと、& で連結したものは強制的に文字列になります。

こういった「文字列」を「セル参照」に変換してくれるのが INDIRECT関数です。

実用的な引数の入れかた

INDIRECT関数は

=INDIRECT(“A3”)

のように、固定された文字列を引数にすることはほとんどありません。

=A3

と、はじめからセル参照にすればいいからです。

INDIRECT関数の実用的な使いかたとして、他の関数・セル参照と組みあわせて「セル参照をあらわす文字列」をつくる方法がほとんどです。

状況に応じて、さまざまに変化するセル参照をつくれます。

他の関数を利用して引数を作る

他の関数を利用して、「セル参照をあらわす文字列」をつくる方法です。

「商品コード」「商品名」などが入力された表があります。

この表は随時、データが追加されていくとします。

最新の入力データが分かるように、F3セルに最終の「商品コード」を表示したいです。

=INDIRECT(“A”&COUNTA(A:A))

INDIRECT関数とCOUNTA関数を使って最終データを参照する数式を入力した画像

「A列」は固定です。COUNTA関数を利用してデータの最終行を取得しています。

前項で解説したとおり、「”A”&COUNTA(A:A)」だけでは単なる文字列です。

INDIRECT関数の引数に入れてはじめてセル参照になります。

セル参照を利用

「引数にセル参照を入れて、戻り値のセル参照をつくる」というややこしい使いかたです。

しかし、SUM関数の引数にセル参照を入れるのとまったく同じ考えかたです。

SUM関数では、参照したセルの値を数値として引数に取りこみます。

INDIRECT関数も、参照したセルの値が「セル参照をあらわす文字列」であればいいわけです。

A1:A5 に数値が、C1から C3に「セル番地をあらわす文字列」があります。

D1から D3に、それぞれ C1から C3を参照したINDIRECT関数を入力しました。

このとき、D1セルの INDIRECT関数は

=INDIRECT(C1)　　⇒　　=INDIRECT(“A3”)　　⇒　　=A3

同様にD2、D3セルはそれぞれ、「=A1」「=A5」というセル参照です。

「名前」を使う

セル範囲に定義された「名前」はそのままセル参照としてあつかえます。

つまり「名前」も INDIRECT関数の引数として利用できます。

「商品名」「単価」「在庫数」「金額」の入力された表です。

「単価」「在庫数」「金額」のデータ部分を、それぞれの「名前」で定義してあります。

F3セルに入力された「名前」を参照して、その範囲を合計する SUM関数を G3セルに入力しました。

F3セルに「単価」「在庫数」「金額」のプルダウンリストを設定しました。

リストからの選択によって、G3セルの合計値も変化します。

「名前」についてくわしく

INDIRECT関数の応用例

INDIRECT関数の引数は、他の関数やセル参照を組みあわせることが多いと説明しました。

また、戻り値は「セル参照」になります。

ということは、他の関数の引数に INDIRECT関数を利用することも多いということです。

引数にセル参照を入れる関数は無数にありますからね。

むしろ単独で INDIRECT関数を使うことはほとんどないと言えるでしょう。

INDIRECT関数の応用例を紹介します。

VLOOKUP関数の検索範囲 + 他シートの参照

「商品コード」「商品名」「単価」を入力した表が 2種あります。

「関東版」と「関西版」で単価がちがうので、それぞれの表を「関東」シート、「関西」シートに分けて配置しました。

セル範囲はどちらも「A1:C6」セルとします。

「検索」シートの A2、B2セルに入力した「地域」と「商品コード」をもとに、「商品名」「単価」を検索する VLOOKUP関数を C2、D2セルに入力したいです。

C2セルに入力

=VLOOKUP(B2,INDIRECT(A2&”!A1:C6″),2,FALSE)

INDIRECT関数は A2セルの「関東」を参照しているので

INDIRECT(“関東!A1:C6”)

というセル参照になります。

続いて D2セルには

=VLOOKUP(B2,INDIRECT(A2&”!A1:C6″),3,FALSE)

「地域」「商品コード」を変更すればそれに応じた「商品名」「単価」が表示されます。

入力規則のリスト + 名前定義

2クラス分の名簿があります。

それぞれの氏名の範囲に、「A組」「B組」と名前が定義ずみです。

D2セルに入力されたクラス名をもとに、E2セルに入力規則のリストを設定します。

「データ」タブ → 「データツール」 → 「データの入力規則」ダイアログ

「入力値の種類」に「リスト」。「元の値」に

=INDIRECT(D2)

と入力。

D2セルの入力値によってリストの候補が変化します。

リストの選択値を参照して、さらに別のリストの候補を変化させる、という連動リストもつくれます。

連動リストのつくりかた

その他

テーブルの構造化参照を INDIRECT関数の引数として設定することも可能です。

INDIRECT関数の使用法をまとめると

セル参照や & を駆使して、セル参照をあらわす文字列をつくる
その文字列を引数に設定する
戻り値の「セル参照」を他の関数などに利用する

となります。

#REF! エラー

INDIRECT関数の引数が「セル参照」として不適切な場合、「#REF!」エラーになります。

REFERENCE (参照) エラーという意味です。

「シート名」の問題でエラーになることもあります。

他シートのセル参照を入力したとき、シート名に ‘ (シングルクォーテーション)が勝手についた経験ないですか？

シート名に次の記号が使われているとき、シート名の両側が ‘ で囲われます。

　　　～ (全角) , ~ (半角、チルダ) , – (半角) , ( , ) (全角、半角とも）

これ自体はエラーではありません。

ただ、INDIRECT関数の引数として使う場合には、‘ も込みで文字列にしないとエラーになります。

シート名の参照でエラーになったときは、‘ でシート名を囲うような文字列にしてください。

R1C1形式とは？

今まで触れませんでしたが、INDIRECT関数には省略可能なもう一つの引数があります。

参照形式を「R1C1形式」と「A1形式」から選択できます。

引数を省略したときは「A1形式」です。

R1C1形式での表記方法

「A1形式」とは、列番号と行番号でセル位置をあらわす通常の形式
「R1C1形式」とは、行番号を R (Row=行)、列番号を C (Column=列)で表す形式

たとえば、「R3C5」は「3行の5列」。

「A1形式」でいうと「$E$3(絶対参照)」セルになります。

「A1形式」と行・列の順番が逆になるので注意が必要です。

相対参照はどのように表すのでしょう。

「 R[3]C[-1] 」のようにすると、「3行下、1列左のセル」という相対参照になります。

[ ] で数字を囲って入力します。

同じ行は「R」、同じ列は「C」です。

いつ使う？

R1C1形式は、INDIRECT関数や ADDRESS関数など一部の関数で使用できます。

また VBAでマクロを組むときは、R1C1形式でセル参照したほうが便利なときもあります。

INDIRECT関数は文字列で直接的に参照をあらわすので、基本的に絶対参照です。

R1C1形式を選択できるのは、相対参照を表現するためと思われます。

相対参照の場合、同じ文字列のコピーですむからです。

ブック全体をR1C1形式にするオプション

通常のエクセルの操作画面で「R1C1形式」でセル参照はできません。エラーになります。

オプション画面で「R1C1形式」での操作に変更できます。

「ファイル」タブ → 「オプション」 → 「数式」 → 「数式の処理」 → 「R1C1参照形式を使用する」

にチェック。

列番号はアルファベットではなく、すべて数字になります。

「A1形式」で入力されていた数式も自動的に「R1C1形式」に変わります。

オプションのチェックを外せば、いつでも元に戻せるので、興味のある方はいちど変更してみてください。

まとめ

INDIRECT関数は、セル参照や文字列の連結・他の関数との連携など、やや複雑な入力をしないと「使う意味がない」関数です。

また「名前の定義」や「プルダウンリスト」との連携も定番の使いかたです。

ある程度、エクセルの機能を知っていないと理解できない中級者向けの関数だといえます。

R1C1形式については、知らなければ知らなくてもすむ知識です。

ただ、セル参照の概念そのものを表した表記方法なので、いちど体験することをおすすめします。