120になりますが、数式は「=SUM(INDIRECT("A3:A7"))」のままです。合計する範囲が減っても、"A3:A7"は文字列なので変わりません。INDIRECTを使うと対象となるセルの範囲が絶対に変わらないという点で、通常の参照方法よりも強度なセルの固定になります。3.相対参照や絶対参照との違い問題INDIRECT関数を用いてセル参照をすることについて、相対参照や絶対参照との違いを詳しく述べなさい。解説(1)絶対的な参照である相対参照にセルA5を参照します。相対的に参照すると他のセルにコピーした時にセル参照が変わります。これは参照元と参照先の位置関係(相対位置)を変えないようにするための手法です。下の図で言えば、矢印の長さや向きが変わりません。これに対して絶対参照は、他のセルにコピーをしてもセル参照は変わりません。参照先を絶対に変えないという意味です。これはINDIRECT関数も同じです。INDIRECT関数の中身(引数)は文字列であり、コピーをしても文字列が変わることはありません。その意味では絶対参照の一種とも言えます。(2)絶対参照を超える「超」絶対参照コピーをしなければ相対参照と絶対参照の効果は同じです。セルA1に「=A5」と入力して、5行目の上に1行挿入します。挿入するとセルA5は空白になります。セルA1は30のままです。数式が自動的に「=A6」になっています。参照先が移動した場合、自動的にセル番地が変わります。このことは絶対参照も同じです。「=$A$5」と入力します。行を挿入すると、「=$A$6」に変わります。絶対参照とは「コピーをしても絶対に参照先を変えない」という意味なので、絶対参照の$マークで固定されるのは、参照元のセル(A1)をコピーした場合だけです(切り取り等も含む)。セルA1を別のセルに動かさない限り、相対参照と絶対参照の動きはまったく同じなのです。したがって$マークで固定していても、セル番地が変わることはあります。INDIRECTは違います。「=INDIRECT("A5")」と入力して、5行目の上に1行挿入します。数式は「=INDIRECT("A5")」のままです。それは「A5」が文字列だからです。5行目を削除すると30に戻ります。参照先をA5で完全に固定するにはINDIRECT関数を使わなければなりません。このようにINDIRECT関数は、参照元、参照先の移動の影響を受けないというメリットがあります。(3)セル範囲の完全固定A3:A8の合計は「=SUM(A3:A7)」です。5行目の上に行を挿入すると「=SUM(A3:A8)」になります。答えは150で変わりません。5行目を削除すると「=SUM(A3:A7)」に戻ります。セル範囲の始点と終点の間にセルが挿入されたり削除されたりするとそれに伴ってセル範囲が変わります。このことは絶対参照の場合も同じです。5行目の上に行を挿入すると「=SUM($A$3:$A$8)」になります。参照元のセル(A1)をコピーした場合以外は$マークの効果は無いので、参照先のセル範囲が変わることと$マークは無関係です。したがって、$マークによって固定することはできません。「=SUM(INDIRECT("A3:A7"))」とすると「A3:A7」で完全に固定され、セルの挿入削除の影響を受けません。その結果、答えが変わります。(4)#REF!のエラーを回避するセルA1に「=A5」と入力して、5行目を削除します。削除すると「#REF!」のエラー値になります。参照先であるA5が消えたことが原因です。このことは絶対参照も同じです。「=$A$5」と入力します。行を削除すると「#REF!」となります。今度は「=INDIRECT("A5")」と入力して、5行目を削除します。数式は「=INDIRECT("A5")」のままです。それはINDIRECT関数が、参照元、参照先の移動の影響を受けない「超」絶対参照だからです。次の表でももの値段と果物の合計を求めなさい。この場合、INDIRECT関数を用いてもよいか。解説Excelで、セル参照が完全に固定することはデメリットのほうが大きいです。ももの値段を参照するためにセルB6を参照します。果物の合計を求めます。「=SUM(B4:B6)」です。ここで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にはそれぞれ異なる数値が入力されている。(1)Sheet5に、Sheet1~4のセルA5の値を表示しなさい。(2)Sheet1のシート名を「東京」に変えなさい。解説(1)INDIRECTを使って集約するイコールを入力します。Sheet1のセルA5をクリックします。Enterキーを押します。これで他のシートのセルを参照することができます(参考:【Excel関数】シート間の参照、複数シートの計算と串刺し、3D集計)。数式は「=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関数を用いてシート名を参照する場合は、別のセルにシート名を入力しておいたほうが良いです。東京!A5、大阪!A6、名古屋!B5、福岡!B6、集計!A3にはそれぞれ異なる数値が入力されている。集計シートのD列にシート名、E列にセル番地を入力した。該当するセルの値を求めなさい。解説シート名とセル番地が文字列として入力されているので、INDIRECT関数を用いて参照することができます。シート名とセル番地の間に「!」が必要であることに注意します。「=INDIRECT(D4&"!"&E4)」と入力します。これで「=INDIRECT("東京!A5")」となり、東京シートのセルA5を参照することになります。オートフィルをします。シート名とセル番地が変わるので、すべての値を集約することができます。同じシートにあるセルを参照するときに、シート名を付けて「集計!A3」としてもかまいません。シート名を消すとエラーになりますので注意が必要です。「東京」というシートがあり、東京のセルA5をINDIRECT関数によって参照した。(1)シート名を「東京[渋谷]」にすることは可能か。京」「2020東京」にすることは可能か。(3)シート名を「'20東京」「東京'20」にすることは可能か。解説(1)シート名に使えない文字シート名を東京[渋谷]としようとすると、エラーとなります。これはExcelの仕様として、コロン(:)、円記号(¥)、スラッシュ(/)、疑問符(?)、アスタリスク(*)、左角カッコ([)、右角カッコ(])をシート名に使用することができません。これらは半角だけでなく全角文字も不可です(マイクロソフトが禁止しているのだから仕方がない)。INDIRECTで使用することもできません。(2)シート名にシングルクォートが必要な文字シート名を「東京(4月)」にします。丸カッコは上記の禁止文字ではないためシート名として使用することは可能です。INDIRECTはエラー「#REF!」となります。これはシート名を変えたからです。シート名を「東京(4月)」にします。シート名に使用できる記号の中には、セル参照にそのまま使用してはいけない文字があります。カッコのほか、句読点や!#$%&~{}+?<>などキーボードで入力できるほとんどの記号はこれにあたります。これらは半角だけでなく全角文字も不可です。このような記号をセル参照で用いる場合は、シート名をシングルクォーテーション「'」で囲んで「'シート名'!セル番地」としなければなりません。INDIRECT関数の場合は、シングルクォーテーションをダブルクォーテーションの中に入れなければなりません。「=INDIRECT("'"&D4&"'!"&E4)」となります。全角スペース、半角スペースもシート名に使用することができますが、セル参照で使用するときは、シート名をシングルクォーテーション「'」で囲んで「'シート名'!セル番地」としなければなりません。このほか、先頭に数字を使用したシート名の場合もシングルクォーテーションが必要です。逆に、上記の記号を使っていない場合に、シート名をシングルクォーテーションで囲んでもエラーにはなりませんので、INDIRECT関数のシート名はシングルクォーテーションで囲んだほうが安全です。(3)シート名にシングルクォートがあったらどうするかシート名を「'20東京」にしようとするとエラーになります。シート名の最初または最後にシングルクォーテーションを使うことはできません。全角・半角とも不可です(マイクロソフトが禁止しているのだから仕方がない)。シングルクォーテーションはシート名を囲むときに使う記号なので不可ということなのでしょう。「東京'20」にすることは可能です。シート名の途中にシングルクォーテーションを使うことは可能です。INDIRECT関数を使用するときには注意が必要です。シート名を「東京'20」にします。「=INDIRECT(D4&"!"&E4)」はエラーになります。シングルクォーテーションで囲んで「=INDIRECT("'"&D4&"'!"&E4)」とするのもエラーです。シングルクォーテーションをセル参照に用いる場合は、シングルクォーテーションを2つにして、「='東京''20'!A5」としなければなりません。シート名を「東京''20」として、シングルクォーテーションで囲んで「=INDIRECT("'"&D4&"'!"&E4)」とすれば正しい式になります。*補足*このようにシングルクォーテーションをシート名に使うのはややこしいのでやめたほうが良いのですが、どうしても使いたいのであれば、SUBSTITUTEで置換しなければなりません。=INDIRECT("'"&SUBSTITUTE(D4,"'","''")&"'!"&E4)8.INDIRECTの第2引数(R1C1形式)問題INDIRECTを用いてセルB5を参照した。A列の左側に2列挿入しても「300」円となるように数式を修正しなさい。解説INDIRECT関数でセルB5を参照しています。A列の左に2列挿入します。セルB5は空白になりますから、INDIRECT関数の答えは0です。参照元と参照先の位置関係を変えないようにするには、第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」がなければエラーです。わえなびチャンネルhttp://www.youtube.com/c/waenavijpチャンネル登録をすると最新の動画をチェックできます。初心者向けの動画も大量にあります。平成三十年八月]スターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。