Excelの数式で参照するセルの範囲を変数で指定する (計算結果により参照するセルの範囲を変更する)

Master Mole

質問: セルの値に応じて参照するセルの範囲を変更したい

Excelで入力された値によってシートの参照範囲を変更したいです。値が4の場合は、A2からA4セルまでを参照し、値が10の場合はA2からA10セルまでを参照範囲にしたいです。 どのように設定するのが良いでしょうか？

計算結果により参照するセルの範囲を変更する場合の手順を紹介します。

はじめに

計算結果により参照するセルの範囲を変更する場合の手順を紹介します。

計算結果により参照するセルの範囲を変更したり、セルに入力された数値をもとにしてセルの範囲決定したいことがあります。数式中のセルの範囲の参照は 以下の記述の形式で記述すると参照できます。

しかし、以下の記述をしてもF10からF18のセルを参照できません。

対応方法:OFFSET を用いる方法

OFFSET関数を利用すると、セルの範囲を取得できます。

書式

書式は以下になります。

OFFSET((開始セル),(範囲開始位置の行オフセット), (範囲開始位置の列オフセット), (範囲行), (範囲列))

書式例

B6セルから高さ4, 幅1でセルの範囲を取得する場合は次のように記述します。

OFFSET(B6,0,0,4,1)

例

下図の表を作成します。E3のセルに以下の数式を記述します。

=SUM(OFFSET(B3,0,0,D3,1))

E3のセルには、B3からD3のセルに記述された行数分のセルの範囲の合計値が表示される動作になります。
下図ではD3のセルに"3"が入力されているためB3セルから3行分、すなわち 4,5,6の合計値である15がE3のセルに表示されています。

D3のセルの値を"8"に変えます。B3セルから8行分の合計値がE3セルに表示されるため、4,5,6,8,1,2,3,5の合計、つまり34がE3のセルに表示されます。

対応方法:INDIRECT を用いる方法

INDIRECT関数を用いてもセルの範囲を取得できます。

書式

書式は以下になります。

INDIRECT("(セルの位置を示す文字列)")

INDIRECTを用いて範囲を記述する書式

範囲を指定する場合はINDIRECT関数を用いて以下のように記述します。

INDIRECT("(範囲の開始位置を示す文字列)"):INDIRECT("(範囲の終了位置を示す文字列)")

範囲の先頭が決まっている場合の記述書式は次のとおりです。

(範囲の開始セル):INDIRECT("(範囲の終了位置を示す文字列)")

範囲の終了が決まっている場合の記述書式は次のとおりです。

INDIRECT("(範囲の開始位置を示す文字列)"):(範囲の終了セル)

書式例

B6セルから高さ4, 幅1でセルの範囲を取得する場合は次の数式を記述します。

B6:INDIRECT("B"&6+4)

例

下図の表を作成します。E3のセルに以下の数式を入力します。

=SUM(B3:INDIRECT("B"&D3))

E3のセルには、B3からD3のセルに記述された行数分のセルの範囲の合計値が表示される動作になります。
下図ではD3のセルに"4"が入力されているためB3セルからB4セルまで、すなわち 4,5の合計値である9がE3のセルに表示されています。


D3のセルの値を"8"に変えます。B3セルからB8セルまでの合計値がE3セルに表示されるため、4,5,6,8,1,2の合計、つまり26がE3のセルに表示されます。

著者

neg

iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。