- 公開日：
- 更新日：
エクセルで行の削除で関数の参照がずれる場合
行を削除すると、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(D3,-1,0)+B3-C3を入力します。
Ctrl + Enterを押します。
D3の右下■をドラッグし、セルD9までOFFSET関数を入力した数式をオートフィルします。
残高の数値に変更はありませんが、D3~D9セルまでOFFSET関数の計算式が入力されました。
ここで試しに任意の行を削除してみましょう。行番号3を選択します。
Ctrl + -（マイナス）を押して3月29日の行を削除しても、残高の列にエラーが表示されなくなりました。
INDIRECT関数を使う
行の削除で参照が無くなりエラーメッセージが起きた時、INDIRECT関数を使うことによって対処することができます。
エラー表示が起きる現象
ここでは、行削除時にエラーメッセージが表示される現象について説明します。
複数の日付から、最新の日の売上を参照できるかどうかを確認していきましょう。
①数式が入力されているB1セルを選択し、②数式バーで数式（例：=B3）を確認します。
ここで任意の行を削除してみましょう。
行番号3を選択します。
Ctrl + -（マイナス）を押します。B1セルに#REF!のエラーが表示されました。
このエラーが表示されないようにするには、次の対処法を試してください。
INDIRECT関数を使った対処法
INDIRECT関数を使って、上記のエラー表示をした場合の対処方法を説明します。
INDIRECT関数の詳細については、以下の記事を参照してください。
ExcelのINDIRECT関数の使い方｜セルや他のシートを参照する
①INDIRECT関数を反映させたいセル（例：B1）を選択します。
INDIRECT関数の書式は、=INDIRECT(参照文字列,[参照形式])です。今回は、参照文字列に"B3"を入力します。
②数式バーに=INDIRECT("B3")を入力します。
Enterを押します。任意の行番号（例：3）を選択します。
Ctrl + -（マイナス）を押し、4月5日の行を削除してみましょう。
B1セルがエラー表示されず、4月4日の売上データが反映されました。