ExcelのINDIRECT関数は文字列をセル範囲に変換する関数ですが、文字列をセル範囲に変換することだけ理解しても実際にINDIRECT関数を使うことはできません（参考：「INDIRECT関数」完全理解！別シートやセルを参照するメリットと使い方）。
INDIRECT関数の第1引数である文字列はセル番地だけでなく、セルやセル範囲に定義した名前も使えます。これによって条件付き書式や入力規則のドロップダウンリストと連携させることができます。また、別のシートの表を参照することもできるので、VLOOKUP、INDEX、MATCHなどの関数と組み合わせてデータを取得することもできます。
そこで、今回は、名前を付けたセルやセル範囲への参照、プルダウンリストの連動、連番の配列を利用する問題など、INDIRECT関数を使った応用事例を出題します。
目次
- ０．INDIRECT関数の基本（復習）
- １．名前を定義したセルを参照する
- ２．INDEX関数の第1引数として使う
- ３．範囲演算子（半角スペース）
- ４．入力規則とINDIRECT関数
- ５．ドロップダウンリストを連動させる
- ６．MATCH、VLOOKUP関数との組み合わせ
- ７．INDIRECT関数のオートフィル
- ８．連番の配列
０．INDIRECT関数の基本（復習）
INDIRECT関数の基本的な使い方についてはこちらの記事をご覧ください。
なお、この記事の内容を理解するにはINDIRECTのほか、ROW、COLUMN、VLOOKUP、INDEX、MATCH、SUMPRODUCT、条件付き書式、入力規則の知識を必要とします。
１．名前を定義したセルを参照する
（１）名前の定義、変更、削除（復習）
問題
- セルA5に「もも」という名前を定義しなさい。
- 「もも」をセルA4に変更しなさい。
- 名前「もも」を削除しなさい。
解説
セルA5を選択します。名前ボックスに「もも」と入力します。
Enterキーで確定となり、A5というセル番地の代わりに「もも」が使えるようになります。
数式タブの名前の管理をクリックします。
定義された名前の一覧を見ることができ、編集することも可能です。編集ボタンを押します。
参照範囲をA4にします。
これで「もも」はセルA4となります。名前の定義を間違えたときは、名前の管理で修正すればよいです。
定義した名前を削除することができます。数式タブの名前の管理をクリックします。
「もも」を選択して、削除ボタンで消すことができます。
（２）セルの参照
問題
セルA5に「もも」という名前を定義して、INDIRECT関数で参照しなさい。
解説
もう一度、セルA5に対して「もも」を定義します。「=A5」と参照しても良いですが、その代わりに「=もも」と参照しても良いです。
30となります。
INDIRECT関数を用いる場合はダブルクォーテーションが必要です。「=INDIRECT("もも")」です。
（３）セル範囲の参照
問題
セル範囲A3:A7に「数量」という名前を定義して、INDIRECT関数で合計を求めなさい。また、「数量」という文字を入力した場合にそれを利用して合計を求めなさい。
解説
セル範囲A3:A7を選択します。
「数量」という名前を定義します。
合計は「=SUM(A3:A7)」と求めても良いですが、「=SUM(数量)」としてもよいです。
INDIRECT関数を用いる場合はダブルクォーテーションが必要です。「=SUM(INDIRECT("数量"))」です。
セルB1に「数量」と入力したとします。セルまたはセル範囲に定義した名前を、別のセルに入力した時はその文字列をINDIRECT関数で使用することができます。
「=SUM(INDIRECT(B1))」となります。
２．INDEX関数の第1引数として使う
（１）別のシートからデータを取得する
問題
2つのシート「Sheet1」「Sheet2」がある。Sheet1のセル範囲B4:C8に「東日本」、セル範囲B10:C13に「西日本」という名前をそれぞれ定義し、「東日本」、「西日本」の支店名を上から順に取得しなさい。
解説
Sheet1のセル範囲B4:C8を選択して「東日本」という名前を付けます。
セル範囲B10:C13に「西日本」という名前を付けます。
Sheet2の表には「東日本」「西日本」と入力されています。これらはセル範囲に定義した名前です。INDIRECT関数を使います。また、支店名を取得するのはINDEX関数です。INDEX関数で検索する範囲はINDIRECT("東日本")、行番号はA列にある数字です。複合参照です。
- =INDEX(INDIRECT(B$3),$A4,1)
これで東日本、西日本で定義した範囲の1列目を取得することができます。
なお、空白のときにエラーになるので、IFまたはIFERROR関数で空白にします。
- =IFERROR(INDEX(INDIRECT(B$3),$A4,1),"")
セルに名前を入力し、それをINDIRECTでセル範囲に変換することによって、シート名を入力したり、シングルクォーテーションなどを気にしたりすることなく参照することができます。
（２）列の合計を求める
問題
「東日本」、「西日本」の売上金額の合計を求めなさい。
解説
名前を定義しているセル範囲を合計すればよいです。
- =SUM(INDIRECT(B3))
「東日本」「西日本」のうち1列目の支店名は文字列なので合計には関係なく、2列目だけの合計になります。
別解
2列目だけを指定するにはINDEX関数を使い、行番号を０、列番号を2にします。
- =SUM(INDEX(INDIRECT(B3),0,2))
３．範囲演算子（半角スペース）
（１）セル範囲の共通範囲
問題
セル範囲F3:F7とセル範囲C7:F7の共通範囲であるセルを参照しなさい。
解説
「=F3:F7」と入力します。
半角スペースを入れて、C7:F7を選択します。これで共通範囲であるF7の値を求めることができます。
この半角スペースのことを「範囲演算子」といいます。2つの範囲の共通部分を求める演算子で、その共通範囲が1つのセルであればその値が返ります。
これをINDIRECT関数で囲むとエラーです。
範囲演算子は範囲同士の演算をする（文字列ではない）のでINDIRECT関数の外に出さなければなりません。「INDIRECT() INDIRECT()」という感じでINDIRECTを２つにします。
- =INDIRECT("F3:F7") INDIRECT("C7:F7")
（２）名前と名前の共通範囲
問題
セル範囲F3:F7に「名古屋」、セル範囲C7:F7に「合計」という名前を定義して、共通範囲であるセルを参照しなさい。
解説
セル範囲F3:F7に「名古屋」という名前を付けます。
また、セル範囲C7:F7に「合計」という名前をつけます。
名前も範囲演算子が使えます。「=名古屋 合計」で名古屋の合計になります。
この場合もINDIRECTは２つになります。
- =INDIRECT("名古屋") INDIRECT("合計")
「名古屋」「合計」と入力したとします。
INDIRECT関数であれば、これらのセルを参照して求めることができます。
- =INDIRECT(A1) INDIRECT(B1)
東京にするとエラーになります。「東京」という名前は無いからです。
セル範囲D3:D7を「東京」という名前にします。これで東京の合計が求められます。
４．入力規則とINDIRECT関数
（１）ドロップダウンリストで名前を切り替える
問題
2つのシート「Sheet1」「Sheet2」がある。Sheet1のセル範囲B4:C8に「東日本」、セル範囲B10:C13に「西日本」という名前をそれぞれ定義した。
Sheet2のセルA4に入力規則（リスト）を設定して、ドロップダウンリストで東日本と西日本が選べるようにしなさい。また、「東日本」、「西日本」の売上金額の合計を求めなさい。
解説
セルA4を選択します。
データタブ、データの入力規則をクリックします。
入力値の種類を「リスト」にします。ここで、「ドロップダウンリストから選択する」にチェックが入っていることを確認します。
元の値を「東日本,西日本」とします。なお、東日本と西日本の間に半角のカンマが必要です。
これで東日本と西日本を選択できるようになります。
「東日本」「西日本」はセル範囲の名前であり、SUMで合計を求められます。
- =SUM(INDIRECT(A4))
- =SUM(INDEX(INDIRECT(A4),0,2))
（２）リストに並べる項目をINDEX関数で取得する
問題
Sheet2のセルA6に入力規則（リスト）を設定して、ドロップダウンリストで西日本の支店名を選べるようにしなさい。
解説
西日本の支店名は「西日本」の1列目であり、INDEX関数で「INDEX(西日本,0,1)」となります。
セルA6を選択します。
データタブ、データの入力規則をクリックします。
入力値の種類を「リスト」にします。元の値を「=INDEX(西日本,0,1)」とします。
これで西日本の支店を選択することができます。
（３）条件付き書式とINDIRECT関数
問題
セルA6の入力規則を変更して、セルA4で選んだエリアの支店名を選べるようにしなさい。
また、条件付き書式を設定して、セルA4で選んだエリアでない支店名の場合に赤色の文字にしなさい。
解説
セルA4は東日本と西日本を選択できるように入力設定したセルです。これを用いる場合はINDIRECT関数を使います。
セルA6を選択します。
データタブ、データの入力規則をクリックします。
元の値を「=INDEX(西日本,0,1)」となっているのを変更して、「=INDEX(INDIRECT(A4),0,1)」とします。
セルA4が東日本であれば東日本の支店を選択することができます。
セルA4を西日本に変えると西日本の支店を選択することができます。
ただし、ここで大阪支店を選んだあとで、セルA4を東日本に切り替えても、大阪支店は残ったままなので注意が必要です。
この場合はエラーを表示するなどの工夫が必要です。例えば、セルA6に条件付き書式を設定する方法が考えられます。セルA6の入力規則のリストは「=INDEX(INDIRECT(A4),0,1)」でしたが、そのリストに存在しない支店であればCOUNTIFが０になるはずです。
- =COUNTIF(INDEX(INDIRECT(A4),0,1),A6)=0
これで赤色になります。
５．ドロップダウンリストを連動させる
問題
2つのシート「Sheet1」「Sheet2」がある。
Sheet2のセル範囲A4:A13に入力規則（リスト）を設定して、ドロップダウンリストで光熱費、通信費、生活雑貨を選べるようにしなさい。
また、Sheet2のセル範囲B4:B13に入力規則（リスト）を設定して、セル範囲A4:A13（左隣）の分類に属する費用の項目を選べるようにしなさい。
解説
入力規則でリストにするものが分かっている場合は名前を付けたほうが良いです。Sheet1のセル範囲B3:D3に「分類」という名前を付けたとします。
Sheet2のセル範囲A4:A13を選択します。
データタブ、データの入力規則をクリックします。
入力値の種類を「リスト」にします。元の値を「=分類」とします。
これで分類を選択することができます。
B4:B6に「光熱費」、C4:C9に「通信費」、D4:D9に「生活雑貨」という名前を付けます。つまり、3行目の分類名をそのままセル範囲の名前にします。
Sheet2のセル範囲B4:B13を選択します。
データタブ、データの入力規則をクリックします。
入力値の種類を「リスト」にします。元の値を「=INDIRECT(A4)」（相対参照であることに注意！）とします。
これで、A列の分類を変えると対応する項目を選択することができます。
別解
1列ずつ名前を付けるのが面倒な場合はB4:D9に一括で名前を付けます。B4:D9に「費用」という名前を付けたとします。
この場合、B列の入力規則は「=OFFSET(費用,0,MATCH(A4,分類,0)-1,COUNTA(INDEX(費用,0,MATCH(A4,分類,0))),1)」と長い数式になります。INDIRECTは不要です。
６．MATCH、VLOOKUP関数との組み合わせ
問題
シート「東日本」と「西日本」にそれぞれ表を入力した。
（１）各シートのB列の合計を求めなさい。
（２）3行目の項目のなかで「電話番号」が左から何列目にあるかを求めなさい。
（３）東日本仙台支店と西日本福岡支店の売上金額を求めなさい。
解説
シート名がA列に入力されていることから、INDIRECT関数が使えます。セルにシート名を入力し、それをINDIRECTでセル範囲に変換することによって、異なるシートのデータを参照することができます。なお、シート名は念のためシングルクォーテーションで囲んで、「"'"&A2&"'"」とします（参考：「INDIRECT関数」完全理解！別シートやセルを参照するメリットと使い方）。
SUMのなかにINDIRECTを入れて「=SUM(INDIRECT("'"&A2&"'!B:B"))」とします。B列の参照はB:Bです。
2つのシートは表の形が異なり、東日本の電話番号は左から5列目、西日本の電話番号は左から3列目です。この場所を数字で返す関数はMATCH関数です。
検索値はC$1、検索する範囲はINDIRECTで、3行目の参照は3:3です。複合参照です。
- =MATCH(C$1,INDIRECT("'"&$A2&"'!3:3"),0)
左から何列目かが分かるということはINDEX関数やVLOOKUP関数を使えば、その列のデータを取得することも可能であるということです。
シート名と支店名があります。これを用いて売上金額を求めるにはVLOOKUP関数を使います。検索値はB6、検索する範囲はINDIRECTで、参照はA:Bです。A$4:B$10等でもよいです。
- =VLOOKUP(B6,INDIRECT("'"&A6&"'!A:B"),2,0)
７．INDIRECT関数のオートフィル
問題
シート「東日本」と「西日本」にそれぞれ表を入力した。
セルB1に入力したシートのセルA4～B8のデータを取得しなさい。
解説
セルA1にシート名が入力されているのでINDIRECT関数が使えます。とりあえずセルA4を参照します。
- =INDIRECT("'"&$B$1&"'!A4")
これをオートフィルします。すべてA4になります。INDIRECTは参照先を絶対に変えない参照方法なので当然です。
オートフィルをして文字列を変えるためにはADDRESS(ROW,COLUMN)を使います。ADDRESSはセル参照を文字列に変える関数です。
- =INDIRECT("'"&$B$1&"'!"&ADDRESS(ROW(A4),COLUMN(A4)))
これで相対参照ができます。ただし、セルA4を参照しているため4行目を削除するとエラーになることに注意しなければなりません。
別解
OFFSET関数を使う方法もあります。4行目を削除してもエラーにはなりません。
- =OFFSET(INDIRECT("'"&$B$1&"'!A4"),ROW()-ROW($A$3),COLUMN()-COLUMN($A$3))
８．連番の配列
（１）整数の総和
問題
SUMPRODUCT関数とROW関数を用いて、1～100の整数の総和を求めなさい。また、1～100の整数のうち3でも5でも割り切れない数の総和を求めなさい。
解説
ROW関数の引数がセル範囲で行番号が複数ある場合、縦方向の配列になります。例えば、ROW(1:100)やROW(A1:A100)などとすると、{1;2;3;4;・・・;100}という縦方向の配列（縦ベクトル）となります。
SUMPRODUCT関数は引数が1つの配列しかない場合、配列の中身の総和になります。したがって、SUMPRODUCTのなかに、ROW(1:100)を入れると1から100までの総和となります。5050です。
- =SUMPRODUCT(ROW(1:100))
ところが、オートフィルをすると答えが変わってしまいます。相対参照で数式が変わるからです。
また、3行目を削除すると、4950になります。
数式が「=SUMPRODUCT(1:99)」になってしまいます。相対参照や絶対参照のような参照方法の場合、参照先に変動があった場合、その影響を受けます。
そこでINDIRECT関数を使います。オートフィルや行削除をしても変わりません。
- =SUMPRODUCT(ROW(INDIRECT("1:100")))
3で割り切れない数はMOD関数で「mod(X,3)>0」です。また、5で割り切れない数は「mod(X,5)>0」です。掛け算をすればAND条件（論理積）となり、2632となります。
- =SUMPRODUCT(ROW(1:100)*(mod(ROW(1:100),3)>0)*(mod(ROW(1:100),5)>0))
INDIRECT関数を使うと次のようになります。
- =SUMPRODUCT(ROW(INDIRECT("1:100"))*(mod(ROW(INDIRECT("1:100")),3)>0)*(mod(ROW(INDIRECT("1:100")),5)>0))
（２）１～Nの連番を用いた応用問題
問題
セルA1とA2にそれぞれ文字列を入力した。セルA1の文字列の中に、セルA2の文字列が何個含まれているかを求めなさい。ただし、LEN(A1)>LEN(A2)>0とする。
たとえば、「BCBCBCB」のなかに「BCB」が3個含まれているものとする。
解説
セルA1の文字列の先頭から順に、セルA2の文字列と比較します。MID関数を使います。取得する文字数はLEN（A2）です。
- =MID(A1,開始番号,LEN(A2))
MIDで取り出したものと、A2が一致すれば１、不一致ならば０とします。
- =(MID(A1,開始番号,LEN(A2))=A2)*1
- =EXACT(MID(A1,開始番号,LEN(A2)),A2)*1
開始番号は1からLEN(A1)-LEN(A2)+1までです。ROW関数とINDIRECT関数で連番の配列を作ります。
- ROW(INDIRECT("1:"&LEN(A1)-LEN(A2)+1))
SUMPRODUCTでこの合計を求めたら一致する回数、つまりA2が含まれる回数が求められます。したがって次のような式になります。
- =SUMPRODUCT((MID(A1,ROW(INDIRECT("1:"&LEN(A1)-LEN(A2)+1)),LEN(A2))=A2)*1)
解説は以上です。
