Excel(エクセル）のINDIRECT関数(インダイレクト)は、セルの参照を文字列で指定することが出来ます。
使い方がイメージしづらい関数ですが、図解と合わせながら具体的な使い方を紹介します。
また、INDIRECT関数は他の関数の組み合わせて役に立つ関数です。
５つの実例を各見出しで紹介しますので、合わせて参考にしてみて下さい。
INDIRECT関数(インダイレクト)とは？
まず、INDIRECT関数の”インダイレクト”という英語は、「遠回しな，婉曲的な，率直でない」という意味を持ちます。
INDIRECT関数は『遠回しにセルを参照する関数』とまずは考えていただければと思います。
なぜ遠回しに？と思った方。そう、その通りです。
遠回しに使うのには、それなりに役に立つことがあるんです。
一番簡単な使い方を図解で紹介します。
下の図ではF4セルにINDIRECT関数で「=INDIRECT(F3)」の数式が入っています。F3セルに入力されている文字列「B3」のセル番地を参照して、表内の「取締役会」がINDIRECT関数の結果として表示されています。
このようにINDIRECT関数では「F3」セルを指定しているのに、「B3」セルが返されてくるので、遠回りな印象ですよね。
もちろんメリットはあります。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関数を使って、別のワークシートのセルの値を参照することも可能です。
例えば、「算数」シートと「国語」シートと「英語」シートのC2セルの値を別の一つのセルにINDIRECT関数で表示させる方法を紹介します。
下の図のように、集計用の別シートに以下の関数を挿入します。B2セルは「算数」の文字列が入力されています。
=INDIRECT(B2&"!C2")
B2&"!C2"のB2セルには”算数”という文字が入力されています。
B2&"!C2"の&は文字と文字を結合するための演算子です。
B2&"!C2"の"!C2"はシートを参照するための”！”が使われているので、算数シートのC2セルの値を指定しています。
そのため、「=INDIRECT(B2&"!C2")」は、「算数」シートの「C2」セルの値を参照するという数式となるので、点数「70」が表示されます。
残りの「国語」シートと「英語」シートの点数を表示させるには、INDIRECT関数を入力したセルをコピペして利用すれば、簡単に点数を表示させることが出来ます。
実例１：２つのプルダウンを連動させる
INDIRECT関数は『名前』機能とプルダウンを組み合わせて利用すると便利です。
下の表のように１つ目のプルダウンで選択した「部署」に合った「担当者」をプルダウンリストに表示して、プルダウンの表示する項目数を絞ることが出来ます。
２段階プルダウンの詳細な設定手順を知りたい方は、こちらの記事を参考にしてください。この記事は「INDIRECT関数」にフォーカスを当てて説明します。
まず、使っていないワークシートや空きスペースに部署別の従業員リストを表に纏めてください。
次に「総務部」列の項目を選択してから画面左上の「名前ボックス」に「総務部」と名前を入力して「Enter」キーを押します。「名前」は１つ目のプルダウンリストの名称と必ず合わせて下さい。同様の手順で、「調達部」「経理部」の名前も登録します。
２つ目のプルダウンを設定するセルを選択してから、「データ」タブの「データの入力規則」をクリックします。
「データの入力規則」画面で「入力値の種類」は「リスト」を選択します。
「元の値」は「=INDIRECT(C3)」と入力し、「OK」で画面を閉じます。
C3セルでプルダウンで選択する「調達部」「経理部」「総務部」のいずれかの文字列が参照されます。
「調達部」「経理部」「総務部」は「名前」で登録しているので、INDIRECT関数により間接的に従業員リストの各列のリストが２つ目のプルダウンに表示されるという仕組みです。
プルダウンを設定したセルを▼ボタンで開いて確認すると、C列の部署名に合った名前リストだけが表示されることを確認することが出来ます。
このプルダウンに「名前」とINDIRECT関数を利用する方法は、アンケートなどの作成に非常に役に立ちます。
以下の記事で設定手順を１から紹介していますので、合わせて確認してみて下さい。
実例２：VLOOKUP関数と組み合わせる
VLOOKUP関数とINDIRECT関数を組み合わせて使う方法をご紹介します。
VLOOKUP関数は、縦方向に検索値を抽出する関数ですが、下の表のD3、D4セルにVLOOKUP関数を利用する場合、検索範囲が「関東」と「中部」で異なるため、VLOOKUP関数の検索範囲を変更しなければいけません。
こんな時、VLOOKUP関数の３つ目の引数「検索範囲」にINDIRECT関数を使用することで、検索範囲を切り替える手間を減らすことが出来ます。
まず「関東」エリアの表全体(B8:C11)を選択してから画面左上の「名前ボックス」に「関東」と名前を入力して「Enter」キーを押します。同様の手順で、「中部」エリアの表全体も「中部」の名前を登録します。
名前を登録し終えたら、以下のVLOOKUP関数を挿入します。検索範囲にINDIRECT関数を挿入し、登録した「名前」と同じセルを参照することで、間接的に「関東」エリアもしくは「中部」エリアを検索範囲とすることが出来ます。
=VLOOKUP(C3,INDIRECT(B3),2,FALSE)
下のセルにVLOOKUP関数をコピペしても、INDIRECT関数と「名前」のおかげで検索範囲を変更することなく検索値を抽出することが出来ます。
VLOOKUP関数で列番号の登録を不要にする方法を以下の記事で紹介していますので、合わせて参考にしてみてください。
実例３：XLOOKUP関数と組み合わせる
XLOOKUP関数とINDIRECT関数を組み合わせて使う方法をご紹介します。
XLOOKUP関数は、VLOOKUP関数の利便性がUPした検索値を抽出する関数です。
一つ前の見出しでVLOOKUP関数とINDIRECT関数を組み合わせた使い方を紹介しましたが、同様の方法でXLOOKUP関数にも利用出来ます。
まず表の「都道府県」列(B8:B13)を選択してから画面左上の「名前ボックス」に「都道府県」と名前を入力して「Enter」キーを押します。同様の手順で、表の各列にも名前を登録します。
名前を登録し終えたら、以下のXLOOKUP関数を挿入します。引数「検索範囲」と「戻り範囲」にINDIRECT関数を挿入し、登録した「名前」と同じセルを参照することで、間接的に「都道府県」の売上高を検索することが出来ます。
表の各列に名前を登録しているので、検索する項目が「売上高」から「担当者」に変更になっても引数の修正が簡単です。
XLOOKUP関数の詳しい使い方は以下の記事で紹介していますので、合わせて参考にしてみて下さい。
実例４：MATCH関数と組み合わせる
INDIRECT関数は『MATCH関数』と相性が良いです。
INDIRECT関数とMATCH関数を組み合わせるだけで、VLOOKUP関数と同じ機能を発揮することが出来ます。
MATCH関数(マッチ関数）は、指定した検索範囲内(検査範囲内)で、数値や文字列などの検索値(検査値)が、どの位置にあるかを数値(〇〇列目、▲▲行目）で返す関数です。
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」を足しているのは、見出し行(１行目）の行数分を足して検索したいセル番地と一致するように調整しています。
実例５：SUM関数と組み合わせる
INDIRECT関数とSUM関数を組み合わせて、合計するセル範囲を簡単に調整することが出来ます。
下の表のG3セルに、以下のSUM関数を挿入します。INDIRECT関数で合計するセル番号を指定することで、合計したいセル範囲を簡単に調整することが出来ます。
=SUM(D2:INDIRECT("D"&G2))
