Office Hack

エクセルで行の削除で関数の参照がずれる場合

  • Release

行を削除すると、Excelの計算が正しく参照されなくなり、エラーが表示されることがあります。

この記事では、OFFSET関数、INDIRECT関数をそれぞれ使って、エラーを回避する方法を解説します。

行の削除はExcelで最も頻繁に行う操作の1つなので、しっかりと覚えてエラー表示にならないように対策しておきましょう。

OFFSET関数を使う

エラー表示が起こる

ここでは、受取・支払・残高の計算で、ある行を削除するとエラー表示になってしまう現象と対処法について解説します。

エラー表示が起きる現象

受取、支払、残高計算のデータで、ある行を削除するとデータの合計金額にエラーが表示されます。

まず、そのエラーメッセージが出る現象について見てみましょう。

数式を確認する

受取、支払、残高計算のデータの中で、まずは残高の計算式を見てみましょう。

①残高の任意のセル(例:D3)を選択します。②数式バーにD2+B3-C3が入力されているのを確認します。

D4~D8セルにも、前日の残高+当日の受取-当日の支払の計算式が入力されています。

行番号を選択する

Enterを押します。行番号3を選択します。

行を削除する

Ctrl + -(マイナス)を押すと、D3からD8の残高セルがすべてエラー表示になってしまいました。

このエラーメッセージを表示させないようにするには、OFFSET関数を使用します。詳しくは、以下のセクションを参照してください。

OFFSET関数を使った対処法

行の削除でエラーメッセージが発生する場合は、OFFSET関数で対処することができます。

OFFSET関数は、指定したセルまたはセル範囲から指定した行数および列数だけ離れた位置にあるセル範囲を返す関数です。詳しくは、以下の記事を参照してください。

ExcelでOFFSET関数の使い方|指定した位置のセル範囲を参照する

セルを選択する

残高の列の任意のセル(例:D3)を選択します。

OFFSET関数を入力する

ここで、数式バーにOFFSET関数を入力します。OFFSET関数の書式は、=OFFSET(参照,行数,列数,[高さ], [幅])となっており、今回は「参照,行数,列数」だけで前日の残高のセルを指定することができます。

ここでは、数式バーに=OFFSET(D3,-1,0)+B3-C3を入力します。

OFFSET関数をオートフィルする

Ctrl + Enterを押します。

D3の右下■をドラッグし、セルD9までOFFSET関数を入力した数式をオートフィルします。

行番号を選択する

残高の数値に変更はありませんが、D3~D9セルまでOFFSET関数の計算式が入力されました。

ここで試しに任意の行を削除してみましょう。行番号3を選択します。

エラーにならない

Ctrl + -(マイナス)を押して3月29日の行を削除しても、残高の列にエラーが表示されなくなりました。

INDIRECT関数を使う

エラー表示が起きる現象

行の削除で参照が無くなりエラーメッセージが起きた時、INDIRECT関数を使うことによって対処することができます。

エラー表示が起きる現象

ここでは、行削除時にエラーメッセージが表示される現象について説明します。

=B3が入力されている状態

複数の日付から、最新の日の売上を参照できるかどうかを確認していきましょう。

数式が入力されているB1セルを選択し、②数式バーで数式(例:=B3)を確認します。

行を選択する

ここで任意の行を削除してみましょう。

行番号3を選択します。

エラーが表示される

Ctrl + -(マイナス)を押します。B1セルに#REF!のエラーが表示されました。

このエラーが表示されないようにするには、次の対処法を試してください。

INDIRECT関数を使った対処法

INDIRECT関数を使って、上記のエラー表示をした場合の対処方法を説明します。

INDIRECT関数の詳細については、以下の記事を参照してください。

ExcelのINDIRECT関数の使い方|セルや他のシートを参照する

INDIRECT関数の入力

INDIRECT関数を反映させたいセル(例:B1)を選択します。

INDIRECT関数の書式は、=INDIRECT(参照文字列,[参照形式])です。今回は、参照文字列に"B3"を入力します。

②数式バーに=INDIRECT("B3")を入力します。

行の選択

Enterを押します。任意の行番号(例:3)を選択します。

エラーが表示されなかった

Ctrl + -(マイナス)を押し、4月5日の行を削除してみましょう。

B1セルがエラー表示されず、4月4日の売上データが反映されました。