INDIRECT（インダイレクト）は、もともと英語で「間接的、遠回り」という意味です。直接的という意味のダイレクトDirectの反対語です。ExcelのINDIRECT関数も、直接セルを指定すればよいものを、わざわざ回りくどい言い方をしてセルの参照をするので、使い方を理解するのが難しい関数の一つと言えます。もちろん、このようなセルの参照の仕方をするのには理由があり、メリットがあります。そして、デメリットもあります。
別のシートを参照するときに数式が長くなったり、メンテナンスが面倒だったりすることがあります。たくさんのシートがあってデータを集約するのにシートを切り替えるのが面倒だったりします。
そこで、今回は、INDIRECT関数の基本的な使い方について出題します。
目次
- １．INDIRECT関数の基本
- ２．なぜ、INDIRECTを使うのか？
- ３．相対参照や絶対参照との違い
- ４．デメリットに注意せよ
- ５．別シートの参照
- ６．シート名＆！＆セル
- ７．シート名はシングルクォーテーションで囲むこと
- ８．INDIRECTの第2引数（R1C1形式）
- ９．別ブックの参照
１．INDIRECT関数の基本
（１）INDIRECT関数の使い方
問題
セル範囲A3:A7に10～50の整数を入力した。INDIRECT関数を用いてセルA5を参照しなさい。
解説
セル参照は通常、イコールを入力してA5をクリックするか、または直接入力します（参考：【Excel数式】セルの「参照」が分からなければ相対参照は理解できない）。
セルA5の値を変えると、連動してセルA1の値が変わります。このように連鎖的に計算結果を求めていく仕組みが「参照」です。
今度はINDIRECT関数を用いて参照してみましょう。「=INDIRECT("A5")」と入力します。A5の前後にはダブルクォーテーションが必要です。
このときINDIRECT関数挿入の画面を用いてもかまいません。
30となります。これはセルA5の値と同じです。
セルA5の値を変えると、連鎖的にセルA1の値も変わります。これで完成です。セルを参照することによって連動して値が変わることについては、INDIRECT関数、相対参照、絶対参照のどの方法を用いても同じです。
（２）文字列をセルに変換する
問題
「="A"&"5"」と入力したが30と表示されなかった。30と表示するにはどうすればよいか。
解説
セルに「="A"&"5"」と入力します。この＆は文字列連結演算子です（参考：【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結）。Aと5を連結することによって、「A5」という文字列を作っていますが、これはセルA5ではありません。A5用紙やA5書類のA5です。「="A5"」であって、「=A5」ではないのです。
セルA5の値を変えても連動しません。
固定の文字列ではなく、セル参照として認識させるにはINDIRECT関数を使います。INDIRECTで囲んで「=INDIRECT("A"&"5")」とします。これで30になります。
セルA5の値を変えると連動します。
（３）文字列をセル範囲に変換する
問題
INDIRECT関数を用いて、セル範囲A3:A7の合計を求めなさい。
解説
通常は合計を求めるにはオートSUM（SUM関数）で良いです（参考：【Excel】合計はオートSUMボタンを押すだけ～！で済めば苦労はしない）。このとき、数式は「=SUM(A3:A7)」となります。
これにダブルクォーテーションをつけて、「=SUM("A3:A7")」とするとエラーになります。文字列を足し算することができないからです。
文字列をセル参照に変換して足し算するにはINDIRECT関数を使います。A3:A7の部分を「INDIRECT("A3:A7")」とします。つまり、「=SUM(INDIRECT("A3:A7"))」となります（この違いについては後述）。
別解
セルB1に「A3:A7」と入力したとします。これは文字列です。
=SUM(INDIRECT(B1))とすると、「A3:A7」がセル範囲に変換され合計を求めることができます。
INDIRECT関数の引数は「文字列」ですが、その文字列をどこかのセルに入力した場合はそれを参照しても良いです。
２．なぜ、INDIRECTを使うのか？
問題
セル範囲A3:A7の合計を求めてから、5行目を削除するといくつになるか。
解説
まずはSUM関数を用いて合計を求めます。「=SUM(A3:A7)」です。
5行目を削除します。
数式が「=SUM(A3:A6)」となっています。このように合計する範囲が削除によって減ると自動的に数式内のセル範囲が狭くなります。
次に、F4キーを用いてセル範囲を絶対参照にします。「=SUM($A$3:$A$7)」です。
5行目を削除します。
数式が「=SUM($A$3:$A$6)」となっています。絶対参照でセル範囲を固定したはずなのにセル範囲が変わります（後述）。
今度は、「=SUM(INDIRECT("A3:A7"))」と入力します。答えは150です。
5行目を削除します。
120になりますが、数式は「=SUM(INDIRECT("A3:A7"))」のままです。合計する範囲が減っても、"A3:A7"は文字列なので変わりません。INDIRECTを使うと対象となるセルの範囲が絶対に変わらないという点で、通常の参照方法よりも強度なセルの固定になります。
３．相対参照や絶対参照との違い
問題
INDIRECT関数を用いてセル参照をすることについて、相対参照や絶対参照との違いを詳しく述べなさい。
解説
（１）絶対的な参照である
相対参照にセルA5を参照します。
相対的に参照すると他のセルにコピーした時にセル参照が変わります。これは参照元と参照先の位置関係（相対位置）を変えないようにするための手法です。下の図で言えば、矢印の長さや向きが変わりません。
これに対して絶対参照は、他のセルにコピーをしてもセル参照は変わりません。参照先を絶対に変えないという意味です。
これはINDIRECT関数も同じです。INDIRECT関数の中身（引数）は文字列であり、コピーをしても文字列が変わることはありません。その意味では絶対参照の一種とも言えます。
（２）絶対参照を超える「超」絶対参照
コピーをしなければ相対参照と絶対参照の効果は同じです。例えば、セルA1に「=A5」と入力して、5行目の上に1行挿入します。
挿入するとセルA5は空白になります。
しかし、セルA1は30のままです。数式が自動的に「=A6」になっています。参照先が移動した場合、自動的にセル番地が変わります。
このことは絶対参照も同じです。「=$A$5」と入力します。
行を挿入すると、「=$A$6」に変わります。
もともと絶対参照とは「コピーをしても絶対に参照先を変えない」という意味なので、絶対参照の＄マークで固定されるのは、参照元のセル（A1）をコピーした場合だけです（切り取り等も含む）。セルA1を別のセルに動かさない限り、相対参照と絶対参照の動きはまったく同じなのです。したがって＄マークで固定していても、セル番地が変わることはあります。
しかし、INDIRECTは違います。「=INDIRECT("A5")」と入力して、5行目の上に1行挿入します。
0になります。数式は「=INDIRECT("A5")」のままです。それは「A5」が文字列だからです。
5行目を削除すると30に戻ります。参照先をA5で完全に固定するにはINDIRECT関数を使わなければなりません。このようにINDIRECT関数は、参照元、参照先の移動の影響を受けないというメリットがあります。
（３）セル範囲の完全固定
A3:A8の合計は「=SUM(A3:A7)」です。合計は150です。
5行目の上に行を挿入すると「=SUM(A3:A8)」になります。答えは150で変わりません。
5行目を削除すると「=SUM(A3:A7)」に戻ります。セル範囲の始点と終点の間にセルが挿入されたり削除されたりするとそれに伴ってセル範囲が変わります。
このことは絶対参照の場合も同じです。
5行目の上に行を挿入すると「=SUM($A$3:$A$8)」になります。答えは変わりません。参照元のセル（A1）をコピーした場合以外は＄マークの効果は無いので、参照先のセル範囲が変わることと＄マークは無関係です。したがって、＄マークによって固定することはできません。
「=SUM(INDIRECT("A3:A7"))」とすると「A3:A7」で完全に固定され、セルの挿入削除の影響を受けません。その結果、答えが変わります。
（４）#REF!のエラーを回避する
セルA1に「=A5」と入力して、5行目を削除します。
削除すると「#REF!」のエラー値になります。参照先であるA5が消えたことが原因です。
このことは絶対参照も同じです。「=$A$5」と入力します。
行を削除すると「#REF!」となります。
今度は「=INDIRECT("A5")」と入力して、5行目を削除します。
40になります。数式は「=INDIRECT("A5")」のままです。それはINDIRECT関数が、参照元、参照先の移動の影響を受けない「超」絶対参照だからです。
４．デメリットに注意せよ
問題
次の表でももの値段と果物の合計を求めなさい。この場合、INDIRECT関数を用いてもよいか。
解説
Excelで、セル参照が完全に固定することはデメリットのほうが大きいです。例えば、ももの値段を参照するためにセルB6を参照します。300円です。
果物の合計を求めます。「=SUM(B4:B6)」です。730円です。
ここで5行目を削除します。
自動的に数式が「=B5」に変わることによって「300円」のままです。
また、合計の範囲が「B4:B5」と変わることによって550円となります。果物を1つ削除しているのですから、合計が減るのは正しいです。表計算として正しい計算結果と言えます。
これをINDIRECT関数にすると大変なことになります。
- もも：=INDIRECT("B6")
- 合計：=SUM(INDIRECT("B4:B6"))
5行目を削除すると、ももが15,000円、果物の合計が15,500円となります。これは間違いです。Excelでセル参照を完全に固定することは多くの弊害があるので、特に意味もなくINDIRECT関数を用いた参照をしてはいけません。
５．別シートの参照
問題
5枚のシートで構成されるExcelブックがあり、シート名は「Sheet1」～「Sheet5」である。
このうちSheet1～4のセルA5にはそれぞれ異なる数値が入力されている。
（１）Sheet5に、Sheet1～4のセルA5の値を表示しなさい。
（２）Sheet1のシート名を「東京」に変えなさい。
解説
（１）INDIRECTを使って集約する
イコールを入力します。
Sheet1のセルA5をクリックします。
Enterキーを押します。これで他のシートのセルを参照することができます（参考：【Excel関数】シート間の参照、複数シートの計算と串刺し、３Ｄ集計）。数式は「=Sheet1!A5」となります。
この作業をあと3回繰り返します。これが最も簡単な方法です。
次にINDIRECT関数を使います。「=INDIRECT("Sheet1!A5")」と入力します。ダブルクォーテーションが必要です。INDIRECT("～")で囲むのと囲まないのでは同じです。
別解
シート名がA列に入力されています。INDIRECT関数の引数は文字列なのでこれを利用することができます。「"Sheet1"」と「"!A5"」に分けて考えます。「=INDIRECT(A5&"!A5")」と入力します。
オートフィルをします。A5は相対参照なのでA6～A8になります。"!A5"の部分は文字列なので変わりません。これによってSheet1～4のセルA5の値を集約することができます。
（２）シート名の変更
「=Sheet1!A5」と入力したとします。
Sheet1のシート名を「東京」にします。
数式は自動的に「=東京!A5」に変わります。相対参照や絶対参照のような直接参照の場合、参照先のシート名が変わると自動的に数式が変わります。
「=INDIRECT("Sheet1!A5")」と入力したとします。
Sheet1のシート名を「東京」にします。
「#REF!」のエラーになります。「"Sheet1!A5"」は文字列なので参照先のシート名が変わっても、「"Sheet1"」は変わりません。
「=INDIRECT("東京!A5")」と変える必要があります。
今度は「=INDIRECT(A5&"!A5")」と入力したとします。
Sheet1のシート名を「東京」にします。
「#REF!」のエラーになります。参照しているセルA5が「Sheet1」だからです。
この場合はセルA5を「東京」にすればよいです。このようにINDIRECT関数を用いてシート名を参照する場合は、別のセルにシート名を入力しておいたほうが良いです。
６．シート名＆！＆セル
問題
5枚のシートで構成されるExcelブックがあり、シート名は東京、大阪、名古屋、福岡、集計である。
東京!A5、大阪!A6、名古屋!B5、福岡!B6、集計!A3にはそれぞれ異なる数値が入力されている。集計シートのD列にシート名、E列にセル番地を入力した。該当するセルの値を求めなさい。
解説
シート名とセル番地が文字列として入力されているので、INDIRECT関数を用いて参照することができます。シート名とセル番地の間に「！」が必要であることに注意します。
「=INDIRECT(D4&"!"&E4)」と入力します。
これで「=INDIRECT("東京!A5")」となり、東京シートのセルA5を参照することになります。
オートフィルをします。シート名とセル番地が変わるので、すべての値を集約することができます。
なお、同じシートにあるセルを参照するときに、シート名を付けて「集計!A3」としてもかまいません。
シート名を消すとエラーになりますので注意が必要です。
７．シート名はシングルクォーテーションで囲むこと
問題
「東京」というシートがあり、東京のセルA5をINDIRECT関数によって参照した。
（１）シート名を「東京[渋谷]」にすることは可能か。
（２）シート名を「東京（4月）」「東 京」「2020東京」にすることは可能か。
（３）シート名を「'20東京」「東京'20」にすることは可能か。
解説
（１）シート名に使えない文字
シート名を東京[渋谷]としようとすると、エラーとなります。
これはExcelの仕様として、コロン（:）、円記号（￥）、スラッシュ（/）、疑問符（?）、アスタリスク（*）、左角カッコ（[）、右角カッコ（]）をシート名に使用することができません。これらは半角だけでなく全角文字も不可です（マイクロソフトが禁止しているのだから仕方がない）。もちろんINDIRECTで使用することもできません。
（２）シート名にシングルクォートが必要な文字
シート名を「東京（4月）」にします。丸カッコは上記の禁止文字ではないためシート名として使用することは可能です。
INDIRECTはエラー「#REF!」となります。
これはシート名を変えたからです。シート名を「東京（4月）」にします。エラーのままです。
シート名に使用できる記号の中には、セル参照にそのまま使用してはいけない文字があります。カッコのほか、句読点や！＃＄％＆～｛｝＋？＜＞などキーボードで入力できるほとんどの記号はこれにあたります。これらは半角だけでなく全角文字も不可です。
このような記号をセル参照で用いる場合は、シート名をシングルクォーテーション「'」で囲んで「'シート名'!セル番地」としなければなりません。
INDIRECT関数の場合は、シングルクォーテーションをダブルクォーテーションの中に入れなければなりません。「=INDIRECT("'"&D4&"'!"&E4)」となります。
また、全角スペース、半角スペースもシート名に使用することができますが、セル参照で使用するときは、シート名をシングルクォーテーション「'」で囲んで「'シート名'!セル番地」としなければなりません。
このほか、先頭に数字を使用したシート名の場合もシングルクォーテーションが必要です。
逆に、上記の記号を使っていない場合に、シート名をシングルクォーテーションで囲んでもエラーにはなりませんので、INDIRECT関数のシート名はシングルクォーテーションで囲んだほうが安全です。
（３）シート名にシングルクォートがあったらどうするか
シート名を「'20東京」にしようとするとエラーになります。シート名の最初または最後にシングルクォーテーションを使うことはできません。全角・半角とも不可です（マイクロソフトが禁止しているのだから仕方がない）。シングルクォーテーションはシート名を囲むときに使う記号なので不可ということなのでしょう。
「東京'20」にすることは可能です。シート名の途中にシングルクォーテーションを使うことは可能です。しかし、INDIRECT関数を使用するときには注意が必要です。シート名を「東京'20」にします。
「=INDIRECT(D4&"!"&E4)」はエラーになります。また、シングルクォーテーションで囲んで「=INDIRECT("'"&D4&"'!"&E4)」とするのもエラーです。
シングルクォーテーションをセル参照に用いる場合は、シングルクォーテーションを２つにして、「='東京''20'!A5」としなければなりません。
シート名を「東京''20」として、シングルクォーテーションで囲んで「=INDIRECT("'"&D4&"'!"&E4)」とすれば正しい式になります。
＊補足＊
このようにシングルクォーテーションをシート名に使うのはややこしいのでやめたほうが良いのですが、どうしても使いたいのであれば、SUBSTITUTEで置換しなければなりません。
- =INDIRECT("'"&SUBSTITUTE(D4,"'","''")&"'!"&E4)
８．INDIRECTの第2引数（R1C1形式）
問題
INDIRECTを用いてセルB5を参照した。A列の左側に2列挿入しても「300」円となるように数式を修正しなさい。
解説
INDIRECT関数でセルB5を参照しています。A列の左に2列挿入します。
セルB5は空白になりますから、INDIRECT関数の答えは０です。
参照元と参照先の位置関係を変えないようにするには、第2引数をFALSE（R1C1形式）にします。R1C1形式とは、参照元から見た参照先の場所を相対位置で指定する方法です。
- =INDIRECT("R1C1形式",FALSE)
同じ行は「R」、同じ列は「C」、下の行は「R[1]」、次の列は「C[1]」、上の行は「R[-1]」、前の列は「C[-1]」と記述します。[n]で上下左右の位置を表します。
ももの値段は、4行下の同じ列なので「R[4]C」となります。したがって、「=INDIRECT("R[4]C",FALSE)」となります。
A列の左に2列挿入しても、その位置関係は変わらないためエラーにはなりません。
なお、参照元と参照先の間に行を挿入すると、位置関係が変わるため答えが変わります。
９．別ブックの参照
問題
次の図で、INDIRECT関数を用いて、「waenavi.xlsx」のシート「Sheet1」のセルA5を参照しなさい。
解説
ブック「waenavi.xlsx」を開きます。
INDIRECT関数を用いて「=INDIRECT("'["&B1&"]"&B2&"'!"&B3)」と入力します。ファイル名は[ ]で囲みます。「='[waenavi.xlsx]Sheet1'!A5」となり、開いているブックを参照することができます。
ただし、対象となるExcelファイルを開いておかなければなりません。開いているブックのなかに「waenavi.xlsx」がなければエラーです。
解説は以上です。INDIRECT応用編へつづく・・・
