ExcelのINDIRECT関数で範囲を参照する8つの簡単な方法

INDIRECT関数(検索/行列関数)は、文字列で指定された参照を返す関数です。この関数を使うと、任意のセルや範囲の参照から値を取得できます。本記事では、ExcelのINDIRECT関数を使った範囲指定の活用方法を、具体的な手順とともに詳しく解説します。

説明をわかりやすくするために、4四半期分の売上情報を表すサンプルデータを使用します。データセットは5つの列で構成されており、営業担当者、第1四半期、第2四半期、第3四半期、第4四半期となっています。

練習用ファイルのダウンロード

ExcelのINDIRECT関数で範囲を扱う8つの方法

1. セル参照を使って値を取得する

INDIRECT関数を使えば、セルやセル範囲の参照を通じて値を取得できます。

ここでは、第1四半期のセル範囲C4:C8の値を取得してみましょう。

手順は以下の通りです。

⏩ セルH4に次の数式を入力します。

=INDIRECT("C4:C8")

ここでは、INDIRECT関数の第一引数(参照文字列)に、セル範囲C4:C8を文字列として("C4:C8")指定しています。

Enterキーを押すと、指定したセル範囲のすべての値が表示されます。

2. セルに入力した参照を使って値を取得する

セル範囲を直接数式に書かず、別のセルに格納してから値を取得することも可能です。これにより、参照先を変更したいときに数式を編集する必要がなくなります。

ここでは、第2四半期のセル範囲D4:D8の値を取得します。下のデータセットでは、取得元のセル範囲をあらかじめセルに入力しておきます。

⏩ セルI4に次の数式を入力します。

=INDIRECT(H4)

ここでは、INDIRECT関数の参照文字列としてセルH4を指定しています。セルH4にはセル範囲D4:D8が入力されているため、その範囲の値が取得されます。

Enterキーを押すと、指定した範囲のすべての値が表示されます。

3. セル内の値を使って参照を作成する

INDIRECT関数を使うと、セル内の値を組み合わせて新しいセル参照を作成できます。行番号だけを変数にしたい場合などに非常に便利です。

参照を作成する手順を見てみましょう。

⏩ セルI4に次の数式を入力します。

=INDIRECT("E"&H4)

ここでは、INDIRECT関数の参照文字列として"E"&H4を指定しています。Eは列名で、セルH4には値5が入力されています。列名EとセルH4の値を連結することでE5という参照が作られ、最終的にINDIRECT関数がセルE5の値を取得します。

Enterキーを押すと、作成した参照先のセルの値が表示されます。

4. 名前付き範囲を使って合計(SUM)を求める

INDIRECT関数には名前付き範囲も使用できます。他の関数と組み合わせれば、名前付き範囲の値をさまざまに計算できます。

まず、範囲に名前を付ける手順を紹介します。

最初に、対象のセル範囲を選択します。

➤ ここではセル範囲C4:C8を選択しました。

次に、名前ボックス(アドレスバー)をクリックし、好きな名前を入力して範囲に名前を付けます。

➤ ここでは選択した範囲にQuarter_1という名前を付けました。

次に、この名前付き範囲を使ってQuarter_1の売上合計を計算します。INDIRECT関数とSUM関数を組み合わせましょう。

⏩ セルI4に次の数式を入力します。

=SUM(INDIRECT(I3))

ここでは、SUM関数の引数としてINDIRECT(I3)を指定しています。

INDIRECT関数の参照文字列にはセルI3を選択しています。セルI3には範囲C4:C8の名前が入力されているため、INDIRECT関数がその範囲の値を取得し、SUM関数がそれらの合計を返します。

最後にEnterキーを押すと、Quarter_1の売上合計が表示されます。

5. 名前付き範囲で平均(AVERAGE)を求める

AVERAGE関数とINDIRECT関数を組み合わせれば、名前付き範囲の平均値も計算できます。

⏩ セルI4に次の数式を入力します。

=AVERAGE(INDIRECT(I3))

ここでは、AVERAGE関数の引数としてINDIRECT(I3)を指定しています。

INDIRECT関数の参照文字列にはセルI3を選択しており、そこには範囲C4:C8の名前が入力されています。

INDIRECT関数がその範囲の値を取得し、AVERAGE関数がそれらの平均を返します。

最後にEnterキーを押すと、Quarter_1の売上平均が表示されます。

6. 範囲内の最大値(MAX)を求める

名前付き範囲の中から最大値を求めたい場合は、MAX関数とINDIRECT関数を組み合わせます。

⏩ セルI4に次の数式を入力します。

=MAX(INDIRECT(I3))

ここでは、MAX関数の引数としてINDIRECT(I3)を指定しています。

INDIRECT関数の参照文字列にはセルI3を選択しており、そこには範囲C4:C8の名前が入力されています。

INDIRECT関数がその範囲の値を取得し、MAX関数がそれらの中から最大値を返します。

最後にEnterキーを押すと、Quarter_1の売上の最大値が表示されます。

7. 範囲内の最小値(MIN)を求める

同様に、MIN関数とINDIRECT関数を組み合わせれば、名前付き範囲の最小値も求められます。

⏩ セルI4に次の数式を入力します。

=MIN(INDIRECT(I3))

ここでは、MIN関数の引数としてINDIRECT(I3)を指定しています。

INDIRECT関数の参照文字列にはセルI3を選択しており、そこには範囲C4:C8の名前が入力されています。

INDIRECT関数がその範囲の値を取得し、MIN関数がそれらの中から最小値を返します。

Enterキーを押すと、Quarter_1の売上の最小値が表示されます。

8. 小さい値3つの平均を求める

AVERAGE関数、SMALL関数、ROW関数、INDIRECT関数を組み合わせると、セル範囲内の小さい方から3つの数値の平均を求めることもできます。

⏩ セルI4に次の数式を入力します。

=AVERAGE(SMALL(F4:F8,ROW(INDIRECT("1:3"))))

ここでは、AVERAGE関数の引数としてSMALL(F4:F8,ROW(INDIRECT("1:3")))を指定しています。

SMALL関数には、配列としてF4:F8、順位(k)としてROW(INDIRECT("1:3"))を指定しています。

さらにROW関数には、参照としてINDIRECT("1:3")を指定しています。

INDIRECT関数の参照文字列には1:3を指定しており、これは3つの値を意味します。ここでINDIRECT関数をROW関数内で使うことで、行を挿入・削除しても数式が正しく機能するようになります。

これにより、SMALL関数が小さい方から3つの数値を返し、AVERAGE関数がそれらの平均を計算します。

Enterキーを押すと、第4四半期の売上のうち小さい方から3つの平均が表示されます。

注意点

🔺 INDIRECT関数は、参照文字列が有効なセル参照でない場合や、範囲の上限を超えた場合に#REF!エラーを表示します。

🔺 関数名のスペルを間違えると、#NAME?エラーが表示されます。

練習セクション

まとめ

本記事では、ExcelのINDIRECT関数で範囲を扱う8つの方法を解説しました。あわせて、INDIRECT関数がエラーを表示しやすいケースとその理由についても触れました。ご意見やアイデア、フィードバックがあれば、ぜひコメント欄でお聞かせください。

関連記事

• Excelで間接アドレスを使う方法(12の例)
• Excelでシート名を指定したINDIRECT関数の使い方(4つの条件)
• INDIRECT関数で別シートから値を取得する方法
• INDIRECT関数を使ったドロップダウンリストの作成方法