- 公開日:
- 更新日:
エクセルで行を増やすと数式(関数)が反映されない場合
エクセルで行を挿入すると、表の中にある数式や関数が正しく反映されなくなる場合があります。
この問題は、行の挿入により参照している範囲がずれてしまったときに発生します。参照範囲がずれてしまうと正しい計算式が表示されず、また、エラーが発生することもあります。
この記事では、2つの関数を使用してこれらの現象を防ぐ方法をご紹介します。
OFFSET関数を使う

以下では、行の挿入で数式が正しく反映されない現象と、OFFSET関数を使用した対処法をご説明します。
数式が反映されない
表の中で行の挿入を行うと、数式の参照範囲がずれてしまい、正しい計算式が表示されなくなってしまう場合があります。
ここでは実際に、以下のような表を使用して数式が反映されない現象をご説明します。

この表では4月1日から5日までの入出金を管理しています。
ただしこの表は4月4日のデータが抜けているため、5行目に新しく行を挿入し、4月4日のデータを入力します。

5行目に行を挿入し、4月4日のデータを入力しました。
すると、4月5日の残高が先ほどの金額から変わっていないことが確認できます。
表の中で行の挿入を行うと、このように数式が正しく反映されないことがあります。
以下セクションでは、OFFSET関数を使った対処法をご説明します。
OFFSET関数を使った対処法
行の挿入をすると数式が反映されないという場合は、OFFSET関数を使用すると良いでしょう。
OFFSET関数は、基準のセル範囲から指定した行数/列数を移動した位置にあるセル範囲を返す関数です。OFFSET関数について詳しく知りたい方は、以下の記事をご参照ください。
ExcelでOFFSET関数の使い方|指定した位置のセル範囲を参照する

行を挿入したい残高の列のセル(例:D5)を選択します。

数式バーに、=OFFSET(D5,-1,0)+B5-C5と入力します。
OFFSET関数の書式は「=OFFSET(参照,行数,列数,[高さ], [幅])」です。
今回の例では「参照,行数,列数」を指定して、常に1つ上の残高のセルを参照するように値を入力しています。

Enterを押します。5行目に行を挿入するため、行番号(例:5)を選択します。

Ctrl + Shift + +(プラス)を押して、行を挿入します。

①挿入した行のセル(例:A5,B5)にデータを入力し、②残高の列のセル(例:D5)を選択し、③数式バーに=D4+B5-C5と入力します。

Enterを押します。
4月5日の残高のセルを見ると、数式が反映されて正しい答えが表示されていることを確認できます。
INDIRECT関数を使う

以下では、行の挿入によって参照したい範囲がずれる現象と、INDIRECT関数を使った対処法をご説明します。
参照範囲がずれる
常に特定のセルを参照したい場合でも、行を挿入すると参照範囲がずれてしまうことがあります。
ここでは実際に、以下のような表を使用して参照範囲がずれる現象をご説明します。

この表では、1月から5月までの残高を管理しています。
最新のデータは常にB3セルに入力するようにし、最新の残高を表示するB1セルには、あらかじめ=B3と入力してあります。
この表は5月のデータが抜けているため、4月のデータの上に行を挿入して5月のデータを入力します。

行の挿入をし、B3セルに最新のデータを入力しました。
すると、行の挿入をした影響でB1セルの参照範囲が1つずれてしまいました。
このように、常に特定のセルを参照したい場合に行の挿入をすると、参照範囲がずれてしまうことがあります。
常に同じセルを参照する場合には、以下セクションでご紹介しているINDIRECT関数が有効です。
INDIRECT関数を使った対処法
INDIRECT関数を使って、常に同じセルを参照する方法をご説明します。
INDIRECT関数とは、セルの参照を文字列で指定する関数です。INDIRECT関数について詳しく知りたい方は、以下の記事をご参照ください。
ExcelのINDIRECT関数の使い方|セルや他のシートを参照する

①INDIRECT関数を反映させたいセル(例:B1)を選択し、②数式バーに=INDIRECT("B3")と入力します。
INDIRECT関数の書式は「=INDIRECT(参照文字列,[参照形式])」のように記述します。
今回は例として、参照文字列に"B3"と入力します。

Enterを押します。3行目に行を挿入するため、行番号(例:3)を選択します。

Ctrl + Shift + +(プラス)を押して、行を挿入します。

①挿入した行のセル(例:A3,B3)にデータを入力し、Enterを押します。
②INDIRECT関数を入力したセル(例:B1)を選択し、数式バーを確認すると、行の挿入をしても常にB3セルを参照していることが確認できます。