Microsoft Excel(エクセル）のINDIRECT関数(インダイレクト)は、セルの参照を文字列で指定することができる関数です。
使い方がイメージしづらい関数ですが、図解と合わせながら具体的な使い方を紹介します。
また、INDIRECT関数は他の関数の組み合わせることで役に立つ関数です。
INDIRECT関数の５つの実例を紹介しますので、実践で取り入れてみてください。
INDIRECT関数(インダイレクト)とは？
まず、INDIRECT関数の”インダイレクト”という英語は、「遠回しな，婉曲的な，率直でない」という意味を持ちます。
INDIRECT関数は『遠回しにセルを参照する関数』とまずは考えていただければと思います。
なぜ遠回しに？と思った方。そう、その通りです。
遠回しに使うのには、それなりに役に立つことがあるんです。
INDIRECT関数の簡単な使い方を図解で紹介します。
下の図ではF4セルにINDIRECT関数で「=INDIRECT(F3)」の数式が入っています。F3セルに入力されている文字列「B3」のセル番地を参照して、表内の「取締役会」がINDIRECT関数の結果として表示されています。
このようにINDIRECT関数では「F3」セルを指定しているのに、「B3」セルの文字列が返されてくるので、遠回りな印象ですよね。
一見利用する価値が無いと感じるかもしれませんが、もちろんメリットはあります。
INDIRECT関数を使用すると、数式を変更しないで参照したいセルを変更することができるようになります。
それでは、INDIRECT関数の詳しい使い方を紹介していきます。
INDIRECT関数の構文と引数
INDIRECT関数の構文(書式)と引数は以下のとおりです。
=INDIRECT(参照文字列,[参照形式])
第１引数：参照文字列
文字列で指定する必要があります。セル番地を直接指定する場合は「"」（ダブルクォーテーション）で囲んでください。(入力例：A3,”B3”）
第２引数：[参照形式]
省略可能な引数です。「参照文字列」の形式を指定します。セル番地を「A3」などのA1形式で表示したい時は、省略もしくは「TRUE」を選択します。セル番地を「R2C2」などのR1C1形式で表示したい時は、「FALSE」を選択します。多くのユーザーは、A1形式を使っているので、この引数は入力不要と覚えておきましょう。
２つ目の引数「参照形式」は省略可能なので、第１引数「参照文字列」の２つの使い方をまずは覚えておきましょう。
上の図のように、F4セルには「＝INDIRECT(F3)」が入力されています。F3セルには、「B5」と入力されているので、B5セルの入力値「材料会議」がINDIRECT関数の結果として表示されます。
F6セルには「＝INDIRECT(”B5”)」が入力されています。「"」（ダブルクォーテーション）が使われているので、B5セルの入力値「材料会議」がINDIRECT関数の結果として表示されます。
INDIRECT関数で、異なる複数シートのセルの値を参照する方法
INDIRECT関数を使うことで、異なる複数のワークシートのセルの値を簡単に表示させることができます。
具体的な事例を１つ紹介します。
下の図のように、「算数」シートと「国語」シートと「英語」シートのC2セルには、それぞれの科目のテストの点数が入力されています。
続いて、各科目の点数をまとめるために、「集計」シートのC3セルに、以下のINDIRECT関数を挿入します。
=INDIRECT(B3&"!C2")
INDIRECT関数の第１引数「参照文字列」には、「B3&”!C2”」が入力されていますが、この構文の意味を解説します。
第１引数「B3&"!C2"」の意味
B3 ⇒ 「算数」という文字が入力されているセルを指定しています。
& ⇒ 「&」前後の文字を結合するための演算子です。
”!C2” ⇒ 「!」はシートを指定する記号です。そのため、算数シートのC2セルを指定しています。
つまり、「=INDIRECT(B3&"!C2")」は、「算数」シートの「C2」セルの値を参照するという数式になるので、点数「70」が表示されます。
残りの「国語」シートと「英語」シートの点数を表示させるには、INDIRECT関数を入力したセルをコピペして利用すれば、簡単に複数のワークシートのセルの値を表示させることができます。
INDIRECT関数の実例１：２つのプルダウンを連動させる
INDIRECT関数は、２つ以上のプルダウンを連動させたい時に活躍します。
INDIRECT関数と一緒に、『名前』機能とプルダウンを組み合わせて使用します。
下の表のように１つ目のプルダウンで選択した「部署」に該当する「担当者」をプルダウンリストに表示して、プルダウンの表示する項目数を絞ることが出来ます。
２段階プルダウンの詳細な設定手順を知りたい方は、こちらの記事を参考にしてください。この記事は「INDIRECT関数」にフォーカスを当てて説明します。
まず、使っていないワークシートや空きスペースに部署別の従業員リストを表にまとめてください。
次に「総務部」列のセル範囲を選択(図①)してから、画面左上の「名前ボックス」に「総務部」と名前を入力して「Enter」キーを押します(図②)。「名前」は１つ目のプルダウンリストの名称と必ず合わせて下さい。
同様の手順で、「調達部」「経理部」の名前も登録します。
次に、２つ目のプルダウンを設定するセルを選択してから、「データ」タブの「データの入力規則」をクリックします。
「データの入力規則」画面で「入力値の種類」は「リスト」を選択します。「元の値」には「=INDIRECT(C3)」と入力し、「OK」で画面を閉じます。
C3セルは、プルダウンで選択する「調達部」「経理部」「総務部」のいずれかの文字列が参照されます。
「調達部」「経理部」「総務部」という文字は「名前」で登録しているので、INDIRECT関数により間接的に従業員リストの各列のリストが２つ目のプルダウンに表示されるという仕組みです。
プルダウンを設定したセルを▼ボタンで開いて確認すると、C列の部署名に合った名前リストだけが表示されることを確認することが出来ます。
このプルダウンに「名前」とINDIRECT関数を利用する方法は、アンケートなどの作成に非常に役に立ちます。
以下の記事で設定手順を１から紹介していますので、プルダウンの基礎から学び直したい方は参考にしてみて下さい。
INDIRECT関数の実例２：VLOOKUP関数と名前を組み合わせる
INDIRECT関数の２つ目の事例を紹介します。
２つ目は、INDIRECT関数とVLOOKUP関数と「名前」機能を組み合わせた方法です。
VLOOKUP関数は、縦方向に検索値を抽出する関数です。
VLOOKUP関数を下の表のD3、D4セルに利用して売上高を抽出したい場合、「関東」と「中部」で参照するセル範囲が異なるため、VLOOKUP関数の検索範囲をD3セルとD4セルをわざわざ変更しなければいけません。
こんな時、VLOOKUP関数の３つ目の引数「検索範囲」にINDIRECT関数を使用することで、検索範囲を切り替える手間を減らすことが出来ます。
まず「関東」エリアの表全体(B8:C11)を選択してから画面左上の「名前ボックス」に「関東」と名前を入力して「Enter」キーを押します。同様の手順で、「中部」エリアの表全体も「中部」の名前を登録します。(名前の登録方法を詳しく知りたい方はこちらをクリック)
名前を登録し終えたら、以下のVLOOKUP関数を挿入します。検索範囲にINDIRECT関数を挿入し、登録した「名前」と同じセルを参照することで、間接的に「関東」エリアもしくは「中部」エリアを検索範囲とすることが出来ます。
=VLOOKUP(C3,INDIRECT(B3),2,FALSE)
D4セルにVLOOKUP関数をコピペします。そうすると、INDIRECT関数と「名前」のおかげでVLOOKUP関数の第２引数の検索範囲を変更することなく検索値を抽出することが出来ます。
VLOOKUP関数の基本的な使い方は以下の記事で紹介していますので、合わせて参考にしてみてください。
INDIRECT関数の実例３：XLOOKUP関数とと名前を組み合わせる
INDIRECT関数の3つ目の事例を紹介します。
３つ目は、INDIRECT関数とXLOOKUP関数と「名前」機能を組み合わせた方法です。
XLOOKUP関数は、Microsoft365ユーザー限定で使用できるVLOOKUP関数の利便性がUPした後継関数です。
一つ前の見出しでVLOOKUP関数とINDIRECT関数を組み合わせた使い方を紹介しましたが、同様の方法でXLOOKUP関数にも利用出来ます。
まず表の「都道府県」列(B8:B13)を選択してから画面左上の「名前ボックス」に「都道府県」と名前を入力して「Enter」キーを押します。同様の手順で、表の各列にも名前を登録します。(名前の登録方法を詳しく知りたい方はこちらをクリック)
名前を登録し終えたら、以下のXLOOKUP関数をC3セルに挿入します。引数「検索範囲」と「戻り範囲」にINDIRECT関数を挿入し、登録した「名前」と同じセルを参照することで、間接的に「都道府県」の売上高を検索することが出来ます。
=XLOOKUP(B3,INDIRECT($B$2),INDIRECT($C$2))
右列のD3セル、D4セルにもXLOOKUP関数を挿入して、担当者の名前を検索します。
表の各列に名前を登録しているので、検索する項目が「売上高」から「担当者」に変更になっても、第3引数のINDIRECT関数の参照セルを修正するだけなので数式の変更が簡単です。
=XLOOKUP(B3,INDIRECT($B$2),INDIRECT($D$2))
XLOOKUP関数の詳しい使い方は以下の記事で紹介していますので、合わせて参考にしてみて下さい。
INDIRECT関数の実例４：MATCH関数と組み合わせる
INDIRECT関数の4つ目の事例を紹介します。
４つ目は、INDIRECT関数とMATCH関数を組み合わせた方法です。
INDIRECT関数は、MATCH関数と相性が良いです。
INDIRECT関数とMATCH関数を組み合わせると、VLOOKUP関数と同じ機能を発揮することが出来ます。
MATCH関数を使用することで、探したい値が表の何行目(何列目）にあるのかを調べることが出来ます。
MATCH関数の構文は、以下のとおり３つの引数で構成されています。
＝MATCH(検査値,検査範囲,[照合の型])
MATCH関数の詳しい使い方を知りたい方は、以下の記事を確認して下さい。
下のG3セルにINDIRECT関数を挿入して、表の中から見つけたい商品番号の値段を検索する方法を紹介します。
値段を抽出したいG3セルに以下のINDIRECT関数を挿入します。
=INDIRECT("D"&MATCH(G2,$B$2:$B$8,0)+1)
INDIRECT関数の中の「MATCH(G2,$B$2:$B$8,0)」で探したい商品番号が表の中の何行目にあるかを指定しています。(上の例では５行目）
INDIRECT関数の最後に「+1」を足しているのは、見出し行(１行目）の行数分を足して検索したいセル番地と一致するように調整しています。
INDIRECT関数の実例５：SUM関数と組み合わせる
INDIRECT関数の5つ目の事例を紹介します。
５つ目は、INDIRECT関数とSUM関数を組み合わせた方法です。
INDIRECT関数とSUM関数を組み合わせて、合計するセル範囲を簡単に調整することが出来ます。
下の表のG3セルに、以下のSUM関数を挿入します。INDIRECT関数で合計するセル番号を指定することで、合計したいセル範囲を簡単に調整することが出来ます。
=SUM(D2:INDIRECT("D"&G2))
G2セルの「5」の数値を入力すると、D2セルからD5セルまでの値が合計されます。
G2セルを「7」に変更すると、D2セルからD7セルまでの値が自動で合計されます。
