Excelで計算結果により参照するセルの位置を動的に変更する方法
参照するセルの位置を数式で指定する方法を紹介します。
メモ
この記事は1つのセルの参照先を指定する方法の紹介です。
複数のセル(セルの範囲)を数式で変更する方法は
こちらの記事を参照してください。
また、数式内でセルを単純に参照する方法については
こちらの記事を参照してください。
概要
計算結果により参照するセルの位置を変更したり、セルに入力された数値をもとに参照するセルを決定したい場合があります。
その際、数式で参照するセルの位置を動的に変更したり、変数で指定したいです。数式中のセルの参照は以下の記述で参照できます。
しかし、以下の記述では、C2のセルを参照できません。
この記事では、OFFSET関数を利用する方法、INDIRECT関数を用いる方法で
値に応じて参照するセルの位置を変更する数式を紹介します。
OFFSET関数を用いる方法
OFFSET関数を使うと指定したセルから、関数のパラメータで指定した値だけ、セルの参照位置を移動できます。書式は以下になります。
書式
OFFSET((開始セル),(範囲開始位置の行オフセット), (範囲開始位置の列オフセット))
または、
OFFSET((開始セル),(範囲開始位置の行オフセット), (範囲開始位置の列オフセット), (範囲行), (範囲列))
記述例
下記の数式はC4セルを参照します。
下記の数式はF6セルを参照します。
C4セルから行(縦方向)で2セル、列(横方向)で3セル移動したセルの参照となります。
例1
具体例で動作を確認します。B列のB2セルから次の値を入力します。
続いて、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列から以下の値を入力します。
また、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セルから次の値を入力します。
また、D3セルに 2 を入力します。
B8セルに次の数式を入力します。
B8セルには、D2セルに入力された行数のB列の値が表示されます。D2セルに 2 が入力されているため、"B" と D2 セルの値が連結され、"B2" セルの値がB8セルに表示されます。
数式を確定すると、B2セルの値 10 がD8セルに表示されることが確認できます。
D2セルの値を 6 に変更します。B6セルの値 4 がB8セルに表示されます。
INDIRECT関数を用いると引数で与えた文字列が示すセルを取得できます。
このページのキーワード
- Excelで計算結果により参照するセルを動的に変更する方法
- Excel セル位置 数式 指定
- Excel セル 数式 指定
- Excel 参照 変更
著者
iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。
掲載日: 2011-06-07
改訂日: 2023-08-19