検索・参照中級
INDIRECT関数を見て理解する
文字列をセルや範囲への参照として解釈し、指定した位置の値を取得する関数です。
3つのポイント
- 文字列をセル参照に変換する
- A1形式・R1C1形式を切り替える
- 参照先の値を別シートからも取得する
文字列から別シートの範囲を参照する
サンプル数式
- 参照先を表す文字列
- A1形式かどうか
いまの結果：
先頭行：11 ｜ 101
入力・条件：参照先を表す文字列 「DATA!A2:B3」 ／ A1形式かどうか TRUE
- 参照先を表す文字列A2：「DATA!A2:B3」
- A1形式かどうかB2：TRUE
DATA!A2:B3から2行×2列の参照を解決し、D2を起点にスピル表示します。
例を試す
サンプルデータ
※ Excel と Google スプレッドシートで結果や挙動が異なる場合、Excel の仕様を基準にしています。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 参照文字列 | 参照形式 | 結果 | ||
| 2 | 11 | 101 | |||
| 3 | 22 | 202 |
A2、B2に値を入力すると、結果と処理順が変わります。
サンプルデータ（シート名：DATA）
| A | B | |
|---|---|---|
| 1 | 商品 | 数量 |
| 2 | 11 | 101 |
| 3 | 22 | 202 |
| 4 | 33 | 303 |
2つの引数
色付きの丸数字は、サンプル数式と引数ガイドで同じ引数を示しています。表にも同じ番号があれば、その引数で指定しているセルを示します。
A2表のセル: sample A2この文字列からセル番地・範囲・シート名・名前付き範囲を解決します。
B2表のセル: sample B2TRUEはA1形式、FALSEはR1C1形式です。省略するとTRUEになります。
この数式を日本語にすると
初期状態はA1形式でDATA!A2:B3を参照し、D2:E3に2行×2列で表示します。「例を試す」でR1C1形式にも切り替えられます。A2・B2への直接入力もできます。
この説明は、最初に表示されているサンプルデータをもとにしています。入力値を変更しても説明文は変わりません。
処理の流れ
手順1〜3を順番に確認しましょう。表内の小さな角丸数字は、対応する手順を示します。
参照文字列を確認する
参照文字列の指定はDATA!A2:B3です。
参照形式を確認する
参照形式の指定はtrueです。A1形式で解釈します。
参照先と結果を確認する
D2DATA!A2:B3から2行×2列の参照を解決し、D2を起点にスピル表示します。
対応アプリ
- Excel
- Google スプレッドシート
関数自体が対応するアプリです。教材例の数式や再計算が、両アプリで同じように動くことを保証するものではありません。
この例の注意点
- INDIRECTは文字列をセル・範囲への参照として解釈します。ADDRESSは参照文字列を組み立てる関数で、役割が異なります。
- 第2引数a1を省略するとTRUEとなり、A1形式で解釈します。FALSEならR1C1形式です。
- R1C1形式のR2C1:R3C2は、2行1列目から3行2列目（A2:B3）を表します。第2引数をFALSEにすると、この形式で参照できます。
- 第2引数を省略する式は=INDIRECT(A2)です。この教材のB2はセル参照なので、B2を空白にする操作は引数の省略とは異なり、Excel 2021ではFALSEとしてR1C1形式になります。
- 存在しないシート名・名前付き範囲や無効な番地（例：DATA!A0）は#REF!になります。第2引数にabcなど論理値へ変換できない値を入れると#VALUE!になり、引数セルの実エラーは伝播します。
- Excel 2021を優先しています。Sheetsとはエラー・空白値・スピルの表示に差があり、Managerの747件の実測証跡に記録されています。
- この例題では、別シートのセル・範囲参照を扱います。外部ブック、複雑な循環参照、他関数との入れ子を含む汎用式評価は対象外です。
一般構文
対応：Excel / Google スプレッドシート ・ 構文：引数名に差あり
- Excel 2021では空白参照が0表示になる一方、Sheetsでは空欄表示になる実測例があります。
- 一部の空白・参照文字列や上流のエラー値で、Excel 2021とSheetsのエラー結果が異なります。
- スピル妨害時はExcel 2021で#スピル!、Sheetsでは#REF!が観測されました。
Excel
=INDIRECT(ref_text, [a1])[] は省略できる引数です。
省略時
a1→ TRUE省略時はA1形式です。明示的に空の引数位置を置く場合とは異なります。
Google スプレッドシート
=INDIRECT(cell_reference_as_string, [is_A1_notation])[] は省略できる引数です。
省略時
is_A1_notation→ TRUE省略時はA1形式です。明示的に空の引数位置を置く場合とは異なります。
公式資料・確認範囲
公式資料
- Microsoft Excel 公式構文・省略時の扱い
- Google スプレッドシート 公式対応状況・構文・省略時の扱い
実機確認
- Excel 2021 / Google スプレッドシート省略時の扱い・計算結果・入力の扱い・エラー・配列展開を確認済み
この教材で扱わない範囲
- この教材の操作は2つの固定シート（入力側とDATA）のセル・範囲参照およびA1/R1C1の切り替えを対象とします。名前付き範囲の評価器は内部にありますが、定義の表示を伴う教材プリセットは公開していません。
- 外部ブックの参照、複雑な循環参照、他関数との入れ子を含む汎用式評価、未観測の型変換の完全再現は対象外です。
次に学ぶ関数
ROWS
INDIRECTで参照先の範囲を切り替えたら、ROWSで範囲の行数を数える方法を学びます。
次に学ぶ