参照するセルの位置を数式で指定する、計算結果により参照するセルの位置を変更する - Excel

参照するセルの位置を数式で指定する方法を紹介します。

概要

計算結果により参照するセルの位置を変更したり、セルに入力された数値をもとに参照するセルを決定したい場合があります。
その際、数式で参照するセルの位置を動的に変更したり、変数で指定したいです。数式中のセルの参照は以下の記述で参照できます。


しかし、以下の記述では、C2のセルを参照できません。

この記事では、OFFSET関数を利用する方法、INDIRECT関数を用いる方法で 値に応じて参照するセルの位置を変更する数式を紹介します。

OFFSET関数を用いる方法

OFFSET関数を使うと指定したセルから、関数のパラメータで指定した値だけ、セルの参照位置を移動できます。書式は以下になります。

書式

または、

記述例

下記の数式はC4セルを参照します。

下記の数式はF6セルを参照します。
C4セルから行(縦方向)で2セル、列(横方向)で3セル移動したセルの参照となります。

例1

具体例で動作を確認します。B列のB2セルから次の値を入力します。

10

12

20

22

4

続いて、D2セルに 0 を入力します。


B8セルに以下の数式を入力します。


数式を入力すると、B8セルには 10 が表示されます。B2セルの値を参照していることがわかります。


D2セルの値を 2 に変更します。OFFSET関数の第一引数で指定した、B2セルから、D2セルの数だけ下方向に移動します。D2セルの値を 2 に変更しましたので、 B2セルから2つ下のB4セルの値が、D2セルに表示されます。下図の結果でも、D2セルの値 20 がB8セルに表示されています。


D2セルの値を 4 に変更すると B2セルから4つ下の、B5セルの値がB8セルに表示されます。

例2

先の例では行方向に参照位置を移動しましたが、列方向に移動する場合の例です。
2行目B列から以下の値を入力します。

12

8

2

3

14

20

また、B4セルに 0 を入力します。


B6セルに次の数式を入力します。横方向(列方向)へのオフセットを指定する場合は、OFFSET関数の第三引数に数値を与えます。


数式を確定します。B4セルの値は 0 なので、B2セルの値がB6セルに表示されます。下図でも、B2セルの値 12 が表示されています。


B4セルの値を2に変更します。基準のB2セルから右に2つシフトしたセルである、D2セルの値が表示されます。


B4セルの値を4に変更した場合は、B2セルから右に4つシフトしたF2セルの値が表示されます。

INDIRECT関数を用いる方法

INDIRECT関数を使うと、関数に与えた文字列のセルを参照できます。

書式

記述例

下記の数式はB4セルを参照します。

下記の数式はE5セルを参照します。

下記の数式はF2セルに記載されているアドレスのセルを参照します。F2セルに"H5"が入力されていれば、H5セルを参照します。

例

具体例で動作を確認します。B列のB2セルから次の値を入力します。

10

12

20

22

4

また、D3セルに 2 を入力します。


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


B8セルには、D2セルに入力された行数のB列の値が表示されます。D2セルに 2 が入力されているため、"B" と D2 セルの値が連結され、"B2" セルの値がB8セルに表示されます。 数式を確定すると、B2セルの値 10 がD8セルに表示されることが確認できます。


D2セルの値を 6 に変更します。B6セルの値 4 がB8セルに表示されます。


INDIRECT関数を用いると引数で与えた文字列が示すセルを取得できます。