=INDIRECT(E2)は、E2に文字列として書かれたアドレスのセルを読みます。E2にC4と書いてあれば、数式はC4の値を返します。アドレスは部品から組み立てることもできます。=INDIRECT("C"&E3)は、C列のE3の行番号のセルを読みます。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
F2はC4、つまりCarrotの価格$0.80を読みます。E2をC3やB5に変えると、F2もそれに従います。F3は「C」とE3の6をつなげてアドレスC6を作り、$1.10を返します。E3を2にするとAppleの価格になります。
INDIRECT関数の構文
=INDIRECT(ref_text, [a1])
ref_text(参照文字列): 参照を表す文字列。"C4"、"B2:B6"、"Prices!A2"、"'Price list'!A2:B9"など。a1(参照形式): A1形式のアドレスならTRUEか省略。FALSEにするとR1C1形式で読み、"R4C3"は4行3列目を意味します。行と列がどちらも数値のときに向いています。
文字列が正しいアドレスでないと、結果は#REF!になります。INDIRECTは本物の参照を返すので、SUM、COUNTIF、VLOOKUPなど、範囲を受け取るすべての関数の中で使えます。
数値から範囲を作る
アドレスは範囲全体でもかまいません。そこに数値をつなげると、大きさをセルで決める範囲になります。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
E2が3だと文字列はB2:B4になり、F2はJanからMarまでを足して12,900になります。E2を6にすると半年分の27,900です。データが2行目から始まるので1+E2としています。同じ合計はINDIRECTを使わずに=SUM(B2:INDEX(B2:B7,E2))とも書け、こちらは揮発性ではありません。方法の比較はOFFSETのページにあります。
セルに書かれた名前のシートを参照する
シート名もセルから取れます。すると1つの集計用の数式が、シートをまたぐ検索になります。各行がA列に書かれた名前のシートを読みます。名前を単一引用符で囲んでおけば、スペースを含む名前でも動きます。
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
B2は文字列'Jan'!B2:B4を組み立てて合計し、12,500になります。B3とB4は同じ数式を下にコピーしたものなので、Feb(12,200)とMar(13,700)を読みます。Febタブを開いて数値を変えると、集計も変わります。A2のJanをFebに書き換えると、B2はFebを合計します。引用符の中のB2:B4は文字列なので、数式を下にコピーしても変わりません。変わるのはA2の参照だけです。
連動するプルダウン(ドロップダウンリスト)
1つ目のプルダウンに応じて項目が変わる2つ目のプルダウンは、INDIRECTの定番の使い方です。Excelでのいつもの手順は次のとおりです：
- カテゴリーごとの項目を列にまとめ、各範囲にカテゴリー名を付けます。見出しを含めて列を選択し、数式 > 選択範囲から作成 > 上端行を使います。これで名前
Fruit、Vegetable、Dairyができます。 - A2にカテゴリーのリストを設定します。データ > データの入力規則 > 入力値の種類: リスト、元の値は
Fruit,Vegetable,Dairyです。 - B2に、元の値が
=INDIRECT(A2)のリストを設定します。A2がFruitなら、リストはFruitという名前の範囲を読みます。
下のシートでは、名前付き範囲の代わりにカテゴリーごとのシートを使って同じものを作っています。D2はINDIRECTを使ってA2に書かれた名前のシートの項目をスピルさせ、B2のリストはD2:D4を読みます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
A2でDairyを選ぶと、D2:D4がMilk、Butter、Cheeseに変わり、B2の選択肢も変わります。B2は新しく選ぶまで前の値のままです。Excelも同じ動きをするので、フォームでは項目の横に=COUNTIF(D2:D4,B2)>0のようなチェックを加えることがよくあります。Excel 365では名前付き範囲を使わずに、2つ目のリストをスピルする数式に向けることもできます。たとえば作業用のセルに=INDIRECT("'"&A2&"'!A2:A4")を入れ、元の値を=D2#にします。残りの設定はプルダウン(ドロップダウンリスト)のページにあります。
INDIRECTは揮発性で、挿入した行を無視する
INDIRECTは参照ではなく文字列を読むので、副作用が2つあります：
- 変更のたびに再計算される。 文字列がどのセルを指すかExcelにはわからないので、ブックのどこかを編集するたびにすべてのINDIRECTを再計算します。数十個なら問題ありませんが、何万もあるとキーを押すたびに遅くなります。行番号を使ったINDEX(
=INDEX(C:C,E3))は=INDIRECT("C"&E3)と同じ結果になり、入力が変わったときだけ再計算されます。 - アドレスが動かない。 4行目より上に行を挿入すると
=C4は=C5になりますが、=INDIRECT("C4")はC4を読み続け、そこは別の行になっています。シートに何があっても決まったセルにとどまる参照が欲しいなら、それが狙いどおりのこともあります。たいていは、誰かが行を挿入するのを待っているバグです。
別のブックへのINDIRECTは、そのブックが開いている間だけ動きます。閉じていると#REF!を返します。
練習: 行番号から価格を出す
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
やってみよう: F2で、INDIRECTを使ってE2に書かれた行番号のC列の価格を返してください。
よくある質問
ExcelのINDIRECTは何をしますか？
文字列を参照に変えます。=INDIRECT("C4")はC4の値を返し、=INDIRECT(E2)はE2に書かれたセルアドレスの値を返します。アドレスは&で組み立てられるので、=INDIRECT("C"&E2)はC列のE2の行番号のセルを読みます。
セルに書かれた名前の別シートを参照するには？
シート名を単一引用符で囲んでアドレスを組み立てます：=INDIRECT("'"&A2&"'!B2")。引用符があれば、スペースを含む名前でも動きます。=SUM(INDIRECT("'"&A2&"'!B2:B4"))はそのシートの範囲を合計します。
INDIRECTが#REF!を返すのはなぜですか？
文字列が正しいアドレスでないか、存在しないシートを指しているか、閉じている別のブックを指しているからです。INDIRECTを外して同じ式だけをセルに入れ、数式が組み立てる文字列を確かめてください。
INDIRECTは揮発性関数ですか？
そうです。文字列がどのセルを指すか前もってわからないので、Excelはブックのどこかが変わるたびにすべてのINDIRECTを再計算します。少しなら問題ありませんが、何千もあるとブックが遅くなります。多くの場合、INDEXなら揮発性にならずに同じことができます。
