わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

「INDIRECT関数」完全理解!別シートやセルを参照するメリットと使い方【Excel】

INDIRECT(インダイレクト)は、もともと英語で「間接的、遠回り」という意味です。直接的という意味のダイレクトDirectの反対語です。ExcelのINDIRECT関数も、直接セルを指定すればよいものを、わざわざ回りくどい言い方をしてセルの参照をするので、使い方を理解するのが難しい関数の一つと言えます。もちろん、このようなセルの参照の仕方をするのには理由があり、メリットがあります。そして、デメリットもあります。

別のシートを参照するときに数式が長くなったり、メンテナンスが面倒だったりすることがあります。たくさんのシートがあってデータを集約するのにシートを切り替えるのが面倒だったりします。

そこで、今回は、INDIRECT関数の基本的な使い方について出題します。

目次

1.INDIRECT関数の基本

(1)INDIRECT関数の使い方

問題

セル範囲A3:A7に10~50の整数を入力した。INDIRECT関数を用いてセルA5を参照しなさい。

f:id:waenavi:20191031150427j:plain

 

解説

セル参照は通常、イコールを入力してA5をクリックするか、または直接入力します(参考:【Excel数式】セルの「参照」が分からなければ相対参照は理解できない)。

f:id:waenavi:20191031151154j:plain

 

セルA5の値を変えると、連動してセルA1の値が変わります。このように連鎖的に計算結果を求めていく仕組みが「参照」です。

f:id:waenavi:20191031151239j:plain

 

今度はINDIRECT関数を用いて参照してみましょう。「=INDIRECT("A5")」と入力します。A5の前後にはダブルクォーテーションが必要です。

f:id:waenavi:20191031151535j:plain

 

このときINDIRECT関数挿入の画面を用いてもかまいません。

f:id:waenavi:20191031151438j:plain

 

30となります。これはセルA5の値と同じです。

f:id:waenavi:20191031151357j:plain

 

セルA5の値を変えると、連鎖的にセルA1の値も変わります。これで完成です。セルを参照することによって連動して値が変わることについては、INDIRECT関数、相対参照、絶対参照のどの方法を用いても同じです。

f:id:waenavi:20191031151714j:plain

 

(2)文字列をセルに変換する

問題

「="A"&"5"」と入力したが30と表示されなかった。30と表示するにはどうすればよいか。

f:id:waenavi:20191031151945j:plain

 

解説

セルに「="A"&"5"」と入力します。この&は文字列連結演算子です(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。Aと5を連結することによって、「A5」という文字列を作っていますが、これはセルA5ではありません。A5用紙やA5書類のA5です。「="A5"」であって、「=A5」ではないのです。

f:id:waenavi:20191031152629j:plain

 

セルA5の値を変えても連動しません。

f:id:waenavi:20191031152636j:plain

 

固定の文字列ではなく、セル参照として認識させるにはINDIRECT関数を使います。INDIRECTで囲んで「=INDIRECT("A"&"5")」とします。これで30になります。

f:id:waenavi:20191031152715j:plain

 

セルA5の値を変えると連動します。

f:id:waenavi:20191031152953j:plain

 

(3)文字列をセル範囲に変換する

問題

INDIRECT関数を用いて、セル範囲A3:A7の合計を求めなさい。

f:id:waenavi:20191031154941j:plain

 

解説

通常は合計を求めるにはオートSUM(SUM関数)で良いです(参考:【Excel】合計はオートSUMボタンを押すだけ~!で済めば苦労はしない)。このとき、数式は「=SUM(A3:A7)」となります。

f:id:waenavi:20191031155058j:plain

 

これにダブルクォーテーションをつけて、「=SUM("A3:A7")」とするとエラーになります。文字列を足し算することができないからです。

f:id:waenavi:20191031155136j:plain

 

文字列をセル参照に変換して足し算するにはINDIRECT関数を使います。A3:A7の部分を「INDIRECT("A3:A7")」とします。つまり、「=SUM(INDIRECT("A3:A7"))」となります(この違いについては後述)。

f:id:waenavi:20191031160506j:plain

 

別解

セルB1に「A3:A7」と入力したとします。これは文字列です。

f:id:waenavi:20191031160649j:plain

 

=SUM(INDIRECT(B1))とすると、「A3:A7」がセル範囲に変換され合計を求めることができます。

f:id:waenavi:20191031160756j:plain

 

INDIRECT関数の引数は「文字列」ですが、その文字列をどこかのセルに入力した場合はそれを参照しても良いです。

f:id:waenavi:20191031161537j:plain

 

2.なぜ、INDIRECTを使うのか?

問題

セル範囲A3:A7の合計を求めてから、5行目を削除するといくつになるか。

f:id:waenavi:20191031161641j:plain

 

解説

まずはSUM関数を用いて合計を求めます。「=SUM(A3:A7)」です。

f:id:waenavi:20191031161641j:plain

 

5行目を削除します。

f:id:waenavi:20191031162852j:plain

 

数式が「=SUM(A3:A6)」となっています。このように合計する範囲が削除によって減ると自動的に数式内のセル範囲が狭くなります。

f:id:waenavi:20191031162726j:plain

 

次に、F4キーを用いてセル範囲を絶対参照にします。「=SUM($A$3:$A$7)」です。

f:id:waenavi:20191031162803j:plain

 

5行目を削除します。

f:id:waenavi:20191031162852j:plain

 

数式が「=SUM($A$3:$A$6)」となっています。絶対参照でセル範囲を固定したはずなのにセル範囲が変わります(後述)。

f:id:waenavi:20191031162942j:plain

 

今度は、「=SUM(INDIRECT("A3:A7"))」と入力します。答えは150です。

f:id:waenavi:20191031163300j:plain

 

5行目を削除します。

f:id:waenavi:20191031162852j:plain

 

120になりますが、数式は「=SUM(INDIRECT("A3:A7"))」のままです。合計する範囲が減っても、"A3:A7"は文字列なので変わりません。INDIRECTを使うと対象となるセルの範囲が絶対に変わらないという点で、通常の参照方法よりも強度なセルの固定になります。

f:id:waenavi:20191031163425j:plain

 

3.相対参照や絶対参照との違い

問題

INDIRECT関数を用いてセル参照をすることについて、相対参照や絶対参照との違いを詳しく述べなさい。

f:id:waenavi:20191031151357j:plain

 

解説

(1)絶対的な参照である

相対参照にセルA5を参照します。

f:id:waenavi:20191031163852j:plain

 

相対的に参照すると他のセルにコピーした時にセル参照が変わります。これは参照元と参照先の位置関係(相対位置)を変えないようにするための手法です。下の図で言えば、矢印の長さや向きが変わりません。

f:id:waenavi:20191031163921j:plain

 

これに対して絶対参照は、他のセルにコピーをしてもセル参照は変わりません。参照先を絶対に変えないという意味です。

f:id:waenavi:20191031164014j:plain

 

これはINDIRECT関数も同じです。INDIRECT関数の中身(引数)は文字列であり、コピーをしても文字列が変わることはありません。その意味では絶対参照の一種とも言えます。

f:id:waenavi:20191031164128j:plain

 

(2)絶対参照を超える「超」絶対参照

コピーをしなければ相対参照と絶対参照の効果は同じです。例えば、セルA1に「=A5」と入力して、5行目の上に1行挿入します。

f:id:waenavi:20191031164259j:plain

 

挿入するとセルA5は空白になります。

f:id:waenavi:20191031164232j:plain

 

しかし、セルA1は30のままです。数式が自動的に「=A6」になっています。参照先が移動した場合、自動的にセル番地が変わります。

f:id:waenavi:20191031164334j:plain

 

このことは絶対参照も同じです。「=$A$5」と入力します。

f:id:waenavi:20191031164448j:plain

 

行を挿入すると、「=$A$6」に変わります。

f:id:waenavi:20191031164452j:plain

 

もともと絶対参照とは「コピーをしても絶対に参照先を変えない」という意味なので、絶対参照の$マークで固定されるのは、参照元のセル(A1)をコピーした場合だけです(切り取り等も含む)。セルA1を別のセルに動かさない限り、相対参照と絶対参照の動きはまったく同じなのです。したがって$マークで固定していても、セル番地が変わることはあります。

しかし、INDIRECTは違います。「=INDIRECT("A5")」と入力して、5行目の上に1行挿入します。

f:id:waenavi:20191031164528j:plain

 

0になります。数式は「=INDIRECT("A5")」のままです。それは「A5」が文字列だからです。

f:id:waenavi:20191031164552j:plain

 

5行目を削除すると30に戻ります。参照先をA5で完全に固定するにはINDIRECT関数を使わなければなりません。このようにINDIRECT関数は、参照元、参照先の移動の影響を受けないというメリットがあります。

f:id:waenavi:20191031164844j:plain

 

(3)セル範囲の完全固定

A3:A8の合計は「=SUM(A3:A7)」です。合計は150です。

f:id:waenavi:20191031165350j:plain

 

5行目の上に行を挿入すると「=SUM(A3:A8)」になります。答えは150で変わりません。

f:id:waenavi:20191031165411j:plain

 

5行目を削除すると「=SUM(A3:A7)」に戻ります。セル範囲の始点と終点の間にセルが挿入されたり削除されたりするとそれに伴ってセル範囲が変わります。

f:id:waenavi:20191031165350j:plain

 

このことは絶対参照の場合も同じです。

5行目の上に行を挿入すると「=SUM($A$3:$A$8)」になります。答えは変わりません。参照元のセル(A1)をコピーした場合以外は$マークの効果は無いので、参照先のセル範囲が変わることと$マークは無関係です。したがって、$マークによって固定することはできません。

f:id:waenavi:20191031165453j:plain

 

「=SUM(INDIRECT("A3:A7"))」とすると「A3:A7」で完全に固定され、セルの挿入削除の影響を受けません。その結果、答えが変わります。

f:id:waenavi:20191031165524j:plain

 

(4)#REF!のエラーを回避する

セルA1に「=A5」と入力して、5行目を削除します。

f:id:waenavi:20191031165628j:plain

 

削除すると「#REF!」のエラー値になります。参照先であるA5が消えたことが原因です。

f:id:waenavi:20191031165648j:plain

 

このことは絶対参照も同じです。「=$A$5」と入力します。

f:id:waenavi:20191031165716j:plain

 

行を削除すると「#REF!」となります。

f:id:waenavi:20191031165735j:plain

 

今度は「=INDIRECT("A5")」と入力して、5行目を削除します。

f:id:waenavi:20191031165800j:plain

 

40になります。数式は「=INDIRECT("A5")」のままです。それはINDIRECT関数が、参照元、参照先の移動の影響を受けない「超」絶対参照だからです。

f:id:waenavi:20191031165847j:plain

 

4.デメリットに注意せよ

問題

次の表でももの値段と果物の合計を求めなさい。この場合、INDIRECT関数を用いてもよいか。

f:id:waenavi:20191031174517j:plain

 

解説

Excelで、セル参照が完全に固定することはデメリットのほうが大きいです。例えば、ももの値段を参照するためにセルB6を参照します。300円です。

f:id:waenavi:20191031174555j:plain

 

果物の合計を求めます。「=SUM(B4:B6)」です。730円です。

f:id:waenavi:20191031174621j:plain

 

ここで5行目を削除します。

f:id:waenavi:20191031174741j:plain

 

自動的に数式が「=B5」に変わることによって「300円」のままです。

f:id:waenavi:20191031174646j:plain

 

また、合計の範囲が「B4:B5」と変わることによって550円となります。果物を1つ削除しているのですから、合計が減るのは正しいです。表計算として正しい計算結果と言えます。

f:id:waenavi:20191031174706j:plain

 

これをINDIRECT関数にすると大変なことになります。

  • もも:=INDIRECT("B6")
  • 合計:=SUM(INDIRECT("B4:B6"))

f:id:waenavi:20191031174827j:plain

 

5行目を削除すると、ももが15,000円、果物の合計が15,500円となります。これは間違いです。Excelでセル参照を完全に固定することは多くの弊害があるので、特に意味もなくINDIRECT関数を用いた参照をしてはいけません。

f:id:waenavi:20191031174858j:plain

 

5.別シートの参照

問題

5枚のシートで構成されるExcelブックがあり、シート名は「Sheet1」~「Sheet5」である。

f:id:waenavi:20191031175228j:plain

 

このうちSheet1~4のセルA5にはそれぞれ異なる数値が入力されている。

f:id:waenavi:20191031175352j:plain

f:id:waenavi:20191031175355j:plain

f:id:waenavi:20191031175358j:plain

f:id:waenavi:20191031175401j:plain

 

(1)Sheet5に、Sheet1~4のセルA5の値を表示しなさい。

f:id:waenavi:20191031175521j:plain

 

(2)Sheet1のシート名を「東京」に変えなさい。

f:id:waenavi:20191031175527j:plain

 

解説

(1)INDIRECTを使って集約する

イコールを入力します。

f:id:waenavi:20191031175751j:plain

 

Sheet1のセルA5をクリックします。

f:id:waenavi:20191031175810j:plain

 

Enterキーを押します。これで他のシートのセルを参照することができます(参考:【Excel関数】シート間の参照、複数シートの計算と串刺し、3D集計)。数式は「=Sheet1!A5」となります。

f:id:waenavi:20191031175913j:plain

 

この作業をあと3回繰り返します。これが最も簡単な方法です。

f:id:waenavi:20191031175521j:plain

 

次にINDIRECT関数を使います。「=INDIRECT("Sheet1!A5")」と入力します。ダブルクォーテーションが必要です。INDIRECT("~")で囲むのと囲まないのでは同じです。

f:id:waenavi:20191031180147j:plain

 

別解

シート名がA列に入力されています。INDIRECT関数の引数は文字列なのでこれを利用することができます。「"Sheet1"」と「"!A5"」に分けて考えます。「=INDIRECT(A5&"!A5")」と入力します。

f:id:waenavi:20191031180304j:plain

 

オートフィルをします。A5は相対参照なのでA6~A8になります。"!A5"の部分は文字列なので変わりません。これによってSheet1~4のセルA5の値を集約することができます。

f:id:waenavi:20191031180344j:plain

 

(2)シート名の変更

「=Sheet1!A5」と入力したとします。

f:id:waenavi:20191031180442j:plain

 

Sheet1のシート名を「東京」にします。

f:id:waenavi:20191031175527j:plain

 

数式は自動的に「=東京!A5」に変わります。相対参照や絶対参照のような直接参照の場合、参照先のシート名が変わると自動的に数式が変わります。

f:id:waenavi:20191031180541j:plain

 

「=INDIRECT("Sheet1!A5")」と入力したとします。

f:id:waenavi:20191031180147j:plain

 

Sheet1のシート名を「東京」にします。

f:id:waenavi:20191031175527j:plain

 

「#REF!」のエラーになります。「"Sheet1!A5"」は文字列なので参照先のシート名が変わっても、「"Sheet1"」は変わりません。

f:id:waenavi:20191031180648j:plain

 

「=INDIRECT("東京!A5")」と変える必要があります。

f:id:waenavi:20191031180735j:plain

 

今度は「=INDIRECT(A5&"!A5")」と入力したとします。

f:id:waenavi:20191031180344j:plain

 

Sheet1のシート名を「東京」にします。

f:id:waenavi:20191031175527j:plain

 

「#REF!」のエラーになります。参照しているセルA5が「Sheet1」だからです。

f:id:waenavi:20191031180810j:plain

 

この場合はセルA5を「東京」にすればよいです。このようにINDIRECT関数を用いてシート名を参照する場合は、別のセルにシート名を入力しておいたほうが良いです。

f:id:waenavi:20191031180839j:plain

 

6.シート名&!&セル

問題

5枚のシートで構成されるExcelブックがあり、シート名は東京、大阪、名古屋、福岡、集計である。

f:id:waenavi:20191031184817j:plain

 

東京!A5、大阪!A6、名古屋!B5、福岡!B6、集計!A3にはそれぞれ異なる数値が入力されている。集計シートのD列にシート名、E列にセル番地を入力した。該当するセルの値を求めなさい。

f:id:waenavi:20191031184939j:plain

f:id:waenavi:20191031184941j:plain

f:id:waenavi:20191031184944j:plain

f:id:waenavi:20191031184947j:plain

f:id:waenavi:20191031184950j:plain

 

解説

シート名とセル番地が文字列として入力されているので、INDIRECT関数を用いて参照することができます。シート名とセル番地の間に「!」が必要であることに注意します。

f:id:waenavi:20191031185229j:plain

 

「=INDIRECT(D4&"!"&E4)」と入力します。

f:id:waenavi:20191031185333j:plain

 

これで「=INDIRECT("東京!A5")」となり、東京シートのセルA5を参照することになります。

f:id:waenavi:20191031185336j:plain

 

オートフィルをします。シート名とセル番地が変わるので、すべての値を集約することができます。

f:id:waenavi:20191031185544j:plain

 

なお、同じシートにあるセルを参照するときに、シート名を付けて「集計!A3」としてもかまいません。

f:id:waenavi:20191031185626j:plain

 

シート名を消すとエラーになりますので注意が必要です。

f:id:waenavi:20191031185707j:plain

 

7.シート名はシングルクォーテーションで囲むこと

問題

「東京」というシートがあり、東京のセルA5をINDIRECT関数によって参照した。

(1)シート名を「東京[渋谷]」にすることは可能か。
(2)シート名を「東京(4月)」「東 京」「2020東京」にすることは可能か。
(3)シート名を「'20東京」「東京'20」にすることは可能か。

f:id:waenavi:20191031185815j:plain

 

解説

(1)シート名に使えない文字

シート名を東京[渋谷]としようとすると、エラーとなります。

f:id:waenavi:20191031190038j:plain

 

これはExcelの仕様として、コロン(:)、円記号(¥)、スラッシュ(/)、疑問符(?)、アスタリスク(*)、左角カッコ([)、右角カッコ(])をシート名に使用することができません。これらは半角だけでなく全角文字も不可です(マイクロソフトが禁止しているのだから仕方がない)。もちろんINDIRECTで使用することもできません。

 

(2)シート名にシングルクォートが必要な文字

シート名を「東京(4月)」にします。丸カッコは上記の禁止文字ではないためシート名として使用することは可能です。

f:id:waenavi:20191031190630j:plain

 

INDIRECTはエラー「#REF!」となります。

f:id:waenavi:20191031190710j:plain

 

これはシート名を変えたからです。シート名を「東京(4月)」にします。エラーのままです。

f:id:waenavi:20191031202115j:plain

 

シート名に使用できる記号の中には、セル参照にそのまま使用してはいけない文字があります。カッコのほか、句読点や!#$%&~{}+?<>などキーボードで入力できるほとんどの記号はこれにあたります。これらは半角だけでなく全角文字も不可です。
このような記号をセル参照で用いる場合は、シート名をシングルクォーテーション「'」で囲んで「'シート名'!セル番地」としなければなりません。

f:id:waenavi:20191031202540j:plain

 

INDIRECT関数の場合は、シングルクォーテーションをダブルクォーテーションの中に入れなければなりません。「=INDIRECT("'"&D4&"'!"&E4)」となります。

f:id:waenavi:20191031203016j:plain

f:id:waenavi:20191031203942j:plain

 

また、全角スペース、半角スペースもシート名に使用することができますが、セル参照で使用するときは、シート名をシングルクォーテーション「'」で囲んで「'シート名'!セル番地」としなければなりません。

f:id:waenavi:20191031203836j:plain

f:id:waenavi:20191031204129j:plain

 

このほか、先頭に数字を使用したシート名の場合もシングルクォーテーションが必要です。

f:id:waenavi:20191031203841j:plain

f:id:waenavi:20191031204132j:plain

 

逆に、上記の記号を使っていない場合に、シート名をシングルクォーテーションで囲んでもエラーにはなりませんので、INDIRECT関数のシート名はシングルクォーテーションで囲んだほうが安全です。

f:id:waenavi:20191031204259j:plain

 

(3)シート名にシングルクォートがあったらどうするか

シート名を「'20東京」にしようとするとエラーになります。シート名の最初または最後にシングルクォーテーションを使うことはできません。全角・半角とも不可です(マイクロソフトが禁止しているのだから仕方がない)。シングルクォーテーションはシート名を囲むときに使う記号なので不可ということなのでしょう。

f:id:waenavi:20191031204500j:plain

 

「東京'20」にすることは可能です。シート名の途中にシングルクォーテーションを使うことは可能です。しかし、INDIRECT関数を使用するときには注意が必要です。シート名を「東京'20」にします。

f:id:waenavi:20191031204546j:plain

 

「=INDIRECT(D4&"!"&E4)」はエラーになります。また、シングルクォーテーションで囲んで「=INDIRECT("'"&D4&"'!"&E4)」とするのもエラーです。

f:id:waenavi:20191031204728j:plain

 

シングルクォーテーションをセル参照に用いる場合は、シングルクォーテーションを2つにして、「='東京''20'!A5」としなければなりません。

f:id:waenavi:20191031204958j:plain

 

シート名を「東京''20」として、シングルクォーテーションで囲んで「=INDIRECT("'"&D4&"'!"&E4)」とすれば正しい式になります。

f:id:waenavi:20191031205119j:plain

 

*補足*

このようにシングルクォーテーションをシート名に使うのはややこしいのでやめたほうが良いのですが、どうしても使いたいのであれば、SUBSTITUTEで置換しなければなりません。

  • =INDIRECT("'"&SUBSTITUTE(D4,"'","''")&"'!"&E4)

 

8.INDIRECTの第2引数(R1C1形式)

問題

INDIRECTを用いてセルB5を参照した。A列の左側に2列挿入しても「300」円となるように数式を修正しなさい。

f:id:waenavi:20191031205350j:plain

 

解説

INDIRECT関数でセルB5を参照しています。A列の左に2列挿入します。

f:id:waenavi:20191031205807j:plain

 

セルB5は空白になりますから、INDIRECT関数の答えは0です。

f:id:waenavi:20191031205839j:plain

 

参照元と参照先の位置関係を変えないようにするには、第2引数をFALSE(R1C1形式)にします。R1C1形式とは、参照元から見た参照先の場所を相対位置で指定する方法です。

  • =INDIRECT("R1C1形式",FALSE)

同じ行は「R」、同じ列は「C」、下の行は「R[1]」、次の列は「C[1]」、上の行は「R[-1]」、前の列は「C[-1]」と記述します。[n]で上下左右の位置を表します。

f:id:waenavi:20191031210559j:plain

 

ももの値段は、4行下の同じ列なので「R[4]C」となります。したがって、「=INDIRECT("R[4]C",FALSE)」となります。

f:id:waenavi:20191031210831j:plain

 

A列の左に2列挿入しても、その位置関係は変わらないためエラーにはなりません。

f:id:waenavi:20191031211058j:plain

 

なお、参照元と参照先の間に行を挿入すると、位置関係が変わるため答えが変わります。

f:id:waenavi:20191031211252j:plain

 

9.別ブックの参照

問題

次の図で、INDIRECT関数を用いて、「waenavi.xlsx」のシート「Sheet1」のセルA5を参照しなさい。

f:id:waenavi:20191031213043j:plain

 

解説

ブック「waenavi.xlsx」を開きます。

f:id:waenavi:20191031211958j:plain

f:id:waenavi:20191031212036j:plain

 

INDIRECT関数を用いて「=INDIRECT("'["&B1&"]"&B2&"'!"&B3)」と入力します。ファイル名は[ ]で囲みます。「='[waenavi.xlsx]Sheet1'!A5」となり、開いているブックを参照することができます。

f:id:waenavi:20191031212917j:plain

f:id:waenavi:20191031212858j:plain

 

ただし、対象となるExcelファイルを開いておかなければなりません。開いているブックのなかに「waenavi.xlsx」がなければエラーです。

f:id:waenavi:20191031213010j:plain

 


解説は以上です。INDIRECT応用編へつづく・・・


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]