Aさん たまにセルの値を数式の一部にできたら便利なのになーと思いますが、そんな便利なことできないですよね？
森田 その場合は、関数の「INDIRECT」を活用することで実現できますよ！
では、INDIRECTの使い方について解説していきますね。
はじめに
この記事は関数の概要を把握していることが前提です。
関数の概要の詳細は以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
ワークシート上のセルの値を数式の一部として利用したい場合は「INDIRECT」が有効
実務では、ワークシート上のセルの値を数式の一部として利用したい場合があります。
たとえば、次のようなケースです。
- ドロップダウンリストを2階層以上にする際、セルの値（テーブル名等）を基準に参照する表を切り替えたい（ドロップダウンリストの選択肢を可変にしたい）
- テーブルの一部を参照する際、セルの値（列名等）を基準に参照する列を切り替えたい
- 同じ構成の表が複数シートある場合、セルの値（シート名+セル番地等）を基準に参照するシート+セルを切り替えたい
解説する上で少々ややこしいので、セルの値にある「セル番地」を使って参照するセルを可変にしたいといったシンプルな例だと次のイメージとなります。
こんな場合、関数の「INDIRECT」を使いましょう。
INDIRECTは「インダイレクト」と読む。
INDIRECTを使うことで、指定したセルの値を数式の一部にできます。
INDIRECTの構文
INDIRECTの構文は以下の通りです。
=INDIRECT(参照文字列,[参照形式])
指定される文字列への参照を返します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 参照文字列 | ◯ | 文字列 | 参照したいセル/範囲を示す数式を文字列で指定します。 ※上記文字列は定数とセル参照を組み合わせ可能。 |
| 参照形式 | － | 論理 | 「参照文字列」をA1形式とR1C1形式のいずれで参照するか指定します。 ※TRUE：A1形式、FALSE：R1C1形式 |
引数「参照形式」を省略した場合、「TRUE」と同じA1形式で参照される。
※実務ではA1形式が主流のため、基本的に省略で問題なし。
引数「参照文字列」に指定した文字列が適切なセル参照になっていない場合、エラー値「#REF!」が表示。
※セル参照のルールと違う表記やワークシートの上限を超えた行列の参照等。
引数「参照文字列」で別ブックを参照している場合（外部参照）、そのブックを開いておく必要がある。
※参照先のブックを開いていない場合、エラー値「#REF!」が表示。
※Web版のExcelは外部参照のサポートなし。
【参考】INDIRECTは「検索/行列関数」
あくまで参考情報となりますが、INDIRECTはリボン「数式」タブの関数ライブラリの「検索/行列」に分類されています。
実際にINDIRECTを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
INDIRECTの使用結果イメージ
INDIRECTを使い、ワークシート上のセルの値を数式の一部にするイメージは以下の通りです。
今回はI2セルの値を数式の一部にしました。
I2セルの値である「D8」を数式の一部とみなし、「=D8」と同じ意味の数式にできました。
INDIRECTの戻り値自体は、D8セルの値の「キウイフルーツ」が返ります。
INDIRECTの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はJ2セル - 「=in」等と入力
- サジェストから「INDIRECT」を選択し、「Tab」キーで確定
- 参照したいセル/範囲を示す数式を文字列で指定
※今回はI2セルの参照のみ - 「Enter」キーで確定
手順②の際にIMEを半角英数モードにすること。
【補足】複雑な数式になる参照の場合、後でINDIRECTの数式に書き換えると記述しやすい
構造化参照（テーブルを参照）や別シートのセル参照等、複雑な数式になってしまう場合は、いきなりINDIRECTの数式を記述するのはハードルが高いです。
よって、先にシンプルに参照した数式をセットしましょう。
たとえば、注文テーブルの「商品名」列の同じ行を参照すると、「=注文テーブル [@商品コード]」という数式になります。
これを後からINDIRECTの数式へ書き換えるイメージです。
参照の数式として固定部分は定数としてダブルクォーテーション（”）で囲み、可変にしたいところのみ、セル参照にしましょう。
そして、定数とセル参照の間はアンパサンド（&）で連結すればOKです。
パッと見はややこしい数式ですが、元の数式から書き換えると、一気にハードルは下がります。
可変にする部分まで定数として残してしまうと、エラー値「#REF!」が表示されるため要注意。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_INDIRECT.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はJ2セル - 「=in」等と入力
- サジェストから「INDIRECT」を選択し、「Tab」キーで確定
- 参照したいセル/範囲を示す数式を文字列で指定
※今回はI2セルの参照のみ - 「Enter」キーで確定
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
INDIRECTはワークシート上のセルの値を数式の一部にしたい際に役立つ関数です。
特に、テーブルをベースに「データの入力規則」やXLOOKUPを併用する場合に役立つ機会があるため、覚えておくと良いですね。
なお、INDIRECT以外にもExcel関数の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 INDIRECTは理解が難しい関数ですが、覚えることで2階層以上のドロップダウンリストの設定や、XLOOKUPでの複数列の転記等に役立ちます。
意外と応用できる場面も増えるため、必要に応じて覚えると良いですね。
