Office Hack

エクセルで行を増やすと数式(関数)が反映されない場合

  • Release

エクセルで行を挿入すると、表の中にある数式や関数が正しく反映されなくなる場合があります。

この問題は、行の挿入により参照している範囲がずれてしまったときに発生します。参照範囲がずれてしまうと正しい計算式が表示されず、また、エラーが発生することもあります。

この記事では、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関数を入力する

数式バーに、=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関数を入力する

INDIRECT関数を反映させたいセル(例:B1)を選択し、②数式バーに=INDIRECT("B3")と入力します。

INDIRECT関数の書式は「=INDIRECT(参照文字列,[参照形式])」のように記述します。

今回は例として、参照文字列に"B3"と入力します。

行番号を選択する

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

行を挿入する

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

参照範囲がずれなくなった

挿入した行のセル(例:A3,B3)にデータを入力し、Enterを押します。

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