【Excel】難関「INDIRECT関数」を完全理解する最短ステップ★

こんにちは、HARUです！

前回の記事で、OFFSET関数を用いて演算対象の参照範囲を変更する方法をご紹介しました。

↓OFFSET関数の使い方はこちら↓

SUM関数やAVERAGE関数、COUNTA関数と組み合わせた事例を解説したOFFSET関数に続いて、今回はVLOOKUP関数やXLOOKUP関数などの検索関数と相性の良いINDIRECT関数を演習します。

INDIRECT関数がもつ特殊な役割から、そのしくみを理解するのが難しいと言われていますが、一度挙動をおさえてしまえば他の関数や機能と組み合わせることで絶大な効力を発揮してくれます。

INDIRECT関数の役割

indirectは「間接の」「間接的な」という意味です。
発音を調べると、"インダイレクト"、"インディレクト"、いずれのパターンもヒットします。

対義語である「直接的な」を表す英語がdirect（ダイレクト）なので、"インダイレクト"の方が意味を連想しやすいかもしれません。

さて、Excelにおいて何を「間接的に」扱うのか、まずはINDIRECT関数の基本構成を見ていきます。

INDIRECT関数の構成

INDIRECT関数の引数は２つで、第２引数「参照形式」は任意設定です。
実務では第１引数「参照文字列」なる要素だけを指示するケースがほとんどなので、関数の構成自体は非常にシンプルなことがわかります。

以下８パターンの数式を段階的に入力していくと、従来のデータ（セル）参照とINDIRECT関数による参照との違いを理解できます。

サンプルはA1セルに「東京」、C1セルに「A1」と、それぞれ文字列が入力されています。

【1】「=A1」：A1セルを参照することで、A1セルに入力されている「東京」が返る。
【2】「="A1"」：文字列「A1」が返る。
【3】「INDIRECT("A1")」：文字列"A1"がセル番地に変換され、A1セルを参照することで「東京」が返る。
【4】「INDIRECT(A1)」：A1セルに入力された文字列をセル番地に変換し、該当のセル番地を参照する。
今回A1セルにはセル番地になり得ない"東京"が入力されているため、参照元が見つからず「#REF!」エラーが返る。
【5】「=C1」：C1セルを参照することで、C1セルに入力されている「A1」が返る。
【6】「="C1"」：文字列「C1」が返る。
【7】「INDIRECT("C1")」：文字列"C1"がセル番地に変換され、C1セルを参照することで「A1」が返る。
【8】「INDIRECT(C1)」：C1セルに入力された文字列をセル番地に変換し、該当のセル番地を参照する。
今回C1セルにはセル番地になり得る"A1"が入力されているため、A1セルを参照することで「東京」が返る。

通常はパターン【1】【5】のように、特定のセルを直接参照するのが一般的ですが、INDIRECT関数は文字列をセル番地などに変換して参照元を指示します。

INDIRECT関数のこの仕様は、「名前の定義」と組み合わせるとより効果的な働きをしてくれます。

会員クラスに応じた料金体系の反映

名前の定義

特定のセル範囲や関数、テーブルに任意の名前をつけることで数式の構成がシンプルになり、かつその数式がどこを参照しているかが理解しやすくなります。
この機能を「名前の定義」といいます。

下図は、利用金額に応じた割引率を会員クラス別にまとめた表です。
表示形式で"円以上"と付記した値をそれぞれ昇順で並べています。

特定の範囲に名前を定義するには、対象範囲を選択した状態で「数式」タブ→「定義された名前」グループ→「名前の定義」にアクセスします。

「新しい名前」ダイアログボックスが開いたら、名前の欄に対象範囲に付与する名前を入力し、[Enter]で決定します。

また、これと同じことをワークシート左上の名前ボックスでも実行できます。
対象範囲を選択した状態で名前ボックスに名前を入力し、[Enter]で決定します。

すべての範囲に名前を設定します。
定義した名前は名前ボックスのリストに反映されます。

"＝"(イコール)に続けて定義した名前をセルに入力すると、対応する範囲が候補として表示され、決定するとスピルで参照されます。

検索関数と組み合わせる

会員クラス別の割引率に名前を定義したブックの別のシートに、以下のような表があったとします。

C列の利用金額に応じた割引率を取得しお支払い金額を求めていきますが、前述の通り割引率は会員クラスごとに異なります。
そのため、B列に入力されたクラスに応じて参照する割引率表を変化させる必要があります。

①VLOOKUP関数を挿入し、第１引数「検索値」に利用金額を参照する。

②第２引数「範囲」に、会員クラスを参照する。

会員クラスごとに名前を定義した表はいずれも2列目に割引率が入力されており、かつ"～円以上"に該当する値を抽出するため、近似一致検索が求められる。

③第３引数「列番号」は"2"、第４引数「検索方法」は近似一致の"TRUE"または"1"を入力する。

ただしこのやり方ではエラーが返ります。
第２引数「範囲」で単にB3セルそのものを参照しただけではB3セル自体が検索対象範囲と認識され、割引率に該当するデータが見つからないと判断されるためです。

そこで、第２引数「範囲」にINDIRECT関数を挿入し、B3セルそのものではなく、B3セルに入力された文字列を参照元に変換します。

これにより、会員クラスAの方が100,761円（100,000円以上）利用した場合の割引率「6%」が返されます。

数式を下へコピーすると、それぞれの会員クラスと利用金額に応じた割引率が取得できます。

INDIRECT関数はこのように、関数の中に入力した文字列をセル番地に変換したり、参照したセルに入力された文字列をセル番地や定義された名前に変換したりすることで、可変要素に応じて参照元を自在に操れるのです。

ここで、冒頭の基本構成で解説した８つのパターンのうち、INDIRECT関数に関わる以下の２つの違いを改めておさらいしておきましょう。

===
【3】「INDIRECT("A1")」：文字列"A1"がセル番地に変換され、A1セルを参照することで「東京」が返る。
【4】「INDIRECT(A1)」：A1セルに入力された文字列をセル番地に変換し、該当のセル番地を参照する。
===

INDIRECT関数の中に文字列を入力した場合は、それが文字列であることをExcelに伝えるために""(ダブルコーテーションマーク)で囲む必要があり、セル番地や定義された名前に変換し得る文字列が入力されたセルを参照する場合はセル参照が目的なので、""は不要です。

いずれの参照フローも"文字列からの変換"という「間接的な」ステップを挟んでいることが「indirect」たる所以であり、求められる引数が"参照文字列"と表現されているのも納得できます。

集計対象拠点の更新

ここまでの解説で、【＝INDIRECT("A1")】のようにINDIRECT関数の中で文字列として認識させるパターンの存在に触れてきました。

ただし実際は、参照元となる文字列を"A1"とズバリ入力するケースは少なく、条件に応じて柔軟に「作り上げる」ことがほとんどです。

具体的には、次のようなシーンです。

参照元を作り上げる

各店舗の会員リストをシートごとに分けて管理しているブックがあります。
「集計用」シートで店舗名を選択すると、その店舗の会員数と全会員の累計利用金額を取得できるしくみを構築します。

各シートは共通の構造となっており、会員の氏名はB列に、累計利用金額はF列に入力されています。

たとえば「東京」のシートから会員数を求めるとき、COUNTA関数で通常の参照を行うと以下の構成となります。
※B列全体を参照すると見出しのデータも個数としてカウントされるため、最後に"-1"の処置をしています。

東京の店舗だけを固定で調べるならこれで良いですが、選択した店舗名に応じて参照元を変えられるのが理想です。

A2セル（店舗名）を参照しながら、COUNTA関数の引数【東京!B:B】の部分をINDIRECT関数で作り上げます。

A2セルを参照し、"＆"(アンパサンド)に続けて"!B:B"と入力します。

先ほどと同じ結果が返ります。

【東京!B:B】のうち、可変する店舗名の部分はセル参照とし、各シート共通の部分は固定の参照元として"!B:B"と入力したのです。

これにより、リストから選択した店舗名に応じて該当の会員数を取得できます。

累計利用金額の取得も考え方は同じです。
今回はデータの個数ではなく値の合計を求めるため、SUM関数にネストします。

東京シートのF列の値を合算した結果が返されます。

リストから選択した店舗の累計利用金額を表示できます。

一連のテクニックをおさえておけば、数式の参照元を都度書き換える手間が省け、IF関数などで複数パターンに応じた条件分岐を設定しておくよりシンプルに処理できます。

2段階3段階ドロップダウンリスト

表に設定したドロップダウンリストを、INDIRECT関数で2段階・3段階と連動させる方法をご紹介します。

たとえば下図のように、営業エリアのブロック名、販売拠点名、さらに各拠点に在籍している社員の氏名を入力していく表があったとします。

こんなとき、B列で特定のブロック名（営業本部）を選択すると、C列ではその営業本部が管轄する販売拠点が選択でき、さらにD列でその支店の構成メンバーを選択できるととても便利ですよね。

B列で「関西営業本部」を選択すれば、C列では関西営業本部が管轄している支店だけがリストに表示され、ここで「大阪中央支店」を選択すれば、大阪中央支店に所属しているメンバーだけが抽出される、といったイメージです。

販売するエリアが拡大したり、各販売拠点でメンバーの追加がされたりすることも想定した設定をおさえておきましょう。

リスト化するマスター情報の集約

2段階・3段階と連動するドロップダウンリストを構築するには、各営業本部がどの販売拠点を管轄しているかをまとめた表（Sheet2）と、各販売拠点にどのメンバーが在籍しているかをまとめた表（Sheet3）を用意します。

（Sheet2）各営業本部名を見出しに置き、各営業本部が管轄する販売拠点名をそれぞれの列に入力しておきます。

（Sheet3）各販売拠点名を見出しに置き、各々に在籍しているメンバーをそれぞれの列に入力しておきます。

テーブル化

（Sheet2）表にあるいずれかのセルをアクティブにしたら、リボンの「挿入」タブを開き、「テーブル」のアイコンをクリックします。

「テーブルの作成」ダイアログボックスが表示されますので、すべての営業本部名と各販売拠点名がデフォルトで指定されたデータ範囲に収まっていることと、「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認したら"OK"で決定します。

表がテーブル化されます。

（Sheet3）でも同じ要領でテーブルを設定しておきます。

名前の定義

テーブル化した表にあるいずれかのセルをアクティブにすると、「テーブルデザイン」タブが有効になります。
タブを切り替え、「テーブル名」でテーブルに名前をつけます。

（Sheet2）各販売拠点一覧の名前は「拠点」としておきます。

（Sheet3）販売拠点ごとのメンバー一覧の名前は「氏名」としておきます。

ワークシート左上の名前ボックスを開くと、「拠点」と「氏名」が追加されています。
これらをクリックすると、それぞれテーブル範囲が選択されます。

次に、（Sheet3）メンバーを在籍拠点ごとにグループ化し、そのグループ名を在籍している各販売拠点名にします。

すべての販売拠点で1つずつやっていくと相当時間がかかりますので、次の手順で一括設定しましょう。

①対象範囲を選択した状態で、リボンの「数式」タブを開きます。
②「定義された名前」グループにある、「選択範囲から作成」をクリックします。

③今回は行見出しとなっている各販売拠点ごとに名前をつけたいので、「上端行」にチェックしてOKボタンで決定します。

名前ボックスのリストを開くと、すべての販売拠点名が定義されたことが確認できます。

たとえばここで「札幌第二支店」を選択すると、札幌第二支店にカテゴライズされたメンバーが選択されます。

下準備はこれで完了です。

連動するドロップダウンリストの挿入

▶１段階ドロップダウンリスト

（Sheet1）
①ブロック名を入力する範囲をすべて選択します。
②リボンの「データ」タブを開き、「データの入力規則」のアイコンをクリックします。

③「設定」タブの「入力値の種類」から"リスト"を選択します。

④「元の値」の欄に次のように入力します。
【=INDIRECT("拠点[#見出し]")】

今回INDIRECT関数で参照しているのは、Sheet2で「拠点」と名前をつけたテーブル範囲における「見出し」の部分です。
要はブロック名（＝営業本部名）です。

ここまで入力できたら、OKボタンで閉じます。
B列にブロック名が選択できるドロップダウンリストが挿入されました。

「首都圏営業本部」を選択しておいて、次に2段階目のドロップダウンリストを設定します。

▶2段階ドロップダウンリスト

①支店名を入力する範囲をすべて選択します。
②リボンの「データ」タブを開き、「データの入力規則」のアイコンをクリックします。
③「設定」タブの「入力値の種類」から"リスト"を選択します。
④「元の値」の欄に次のように入力します。
【=INDIRECT("拠点["&B2&"]")】

今回INDIRECT関数で参照しているのは、「拠点」と名前のつけた範囲におけるB2セルの情報、要は1段階目で選択した営業本部名が管轄する各拠点のテーブルです。

一見すると呪文のように見えますが、【"拠点["&B2&"]"】も文字列とセル参照を組み合わせて、"拠点[B2]"という参照元を「作り上げて」いるのです。

ちなみに、支店名を入力する範囲をすべて選択した状態でB2セルだけを参照するような数式に見えますが、この入力規則を設定すると参照セルもB2セル、B3セル、B4セルとスライドしていきます。

ここまで入力できたら、OKボタンで閉じます。
これによって、C2セルでは首都圏営業本部が管轄している販売拠点が表示されます。

「東京中央支店」を選択しておいて、次に3段階目のドロップダウンリストを設定します。

▶3段階ドロップダウンリスト

①氏名を入力する範囲をすべて選択します。
②リボンの「データ」タブを開き、「データの入力規則」のアイコンをクリックします。
③「設定」タブの「入力値の種類」から"リスト"を選択します。
④「元の値」の欄に次のように入力します。
【=INDIRECT("氏名["&C2&"]")】

今回INDIRECT関数で参照しているのは、「氏名」と名前のつけた範囲におけるC2セルの情報、要は2段階目で選択した支店に在籍する各メンバーのテーブルです。

ここでも、"氏名[C2]"という参照元を「作り上げて」います。

入力が完了したら、OKボタンで閉じます。
これによって、D2セルでは東京中央支店に在籍しているメンバーが表示されます。

2段階、3段階ドロップダウンリストが完成しました。
選択内容に応じて次段階のリストがどのように変化するか、色々と触れて試してみてくださいね！

選択データの追加・削除

マスターデータにテーブルを設定したことで、販売拠点の拡大やメンバーの追加にも対応できます。

たとえば、ブロック名のリストに表示されている「九州営業本部」の情報を、（Sheet2）のマスターデータから削除します。

これに連動して、ブロック名のリストから九州営業本部がなくなります。
そして、マスターデータに改めて九州営業本部の情報を追加すると、テーブル範囲が拡張され、リストの選択肢にも自動で追加されます。

マスターデータの追加・削除に柔軟に対応できるため、2段階、3段階と連動させる必要がないケースでも、ドロップダウンリストの参照範囲にはテーブルを設定しておくことをおすすめします。

リストの空白を一括変換する

各営業本部が管轄する拠点数や各支店に在籍するメンバーの人数がバラバラのため、今回の手順で名前を定義すると一部のリストに空白が混ざります。
リストを開いたときに、デフォルトでこの空白データが選択されていることもあります。

マスターデータが数種類であればそれぞれのデータ範囲に1つずつ名前をつけても良いですが、サンプルのように情報量の多いリストの場合は一括で設定した方が効率的です。

この副作用で生じるリストの空白が気になるときは、マスターデータの空白セルを"－"（ハイフン）や"＊"（アスタリスク）などのダミーの文字列で埋めておきましょう。

（Sheet2）
①マスターデータの範囲をすべて選択します。
②キーボードの[Ctrl]+[G]→[Alt]+[S]を順に押して「選択オプション」ダイアログボックスを開きます。
③[K]で「空白セル」にチェックし、[Enter]で決定します。

これにより、対象範囲に散在する空白セルをまとめて選択できます。

そして、アクティブセルに"－"（ハイフン）を入力し、[Ctrl]+[Enter]を押します。

選択していたすべての空白セルに、"－"（ハイフン）が一括入力されます。

こうすることで、リストの余白には"－"（ハイフン）が表示されます。

（Sheet3）氏名のマスターデータでも同じ要領で空白セルを埋めておきましょう。

まとめ

今回はINDIRECT関数の役割といくつかのユースケースをご紹介しました。

INDIRECT関数単体で使うことはほとんどありませんが、参照するセル番地や別シートの範囲、定義された名前を「作り上げる」という秀逸な仕様を他の関数と融合することで、データ集計の幅が一気に広がります。

この特長的な性質を完全に理解し実用スキルとして定着させるために、困ったときはこの記事に戻っておさらいしましょう！

↓↓Excel操作をとにかく高速化したい方へ↓↓