便利な機能

行や列を削除しても数式を「#REF!」にしないための対策

表を作成したときに、数式で1つ上のセルや1つ横のセルを参照している場合、行や列を削除した場合に参照先がなくなり、「#REF!」に置き換わってしまいエラーとなることがあります。

「#REF!」を発生させないため、対象のセルから必ず1つ上のセルや1つ横ののセルを参照させる方法を紹介します。

行や列を削除したとき数式が「#REF!」になる原因

行や列を削除したとき、数式が「#REF!」になる原因は参照していたセルが存在しなくなったためです。

例えば1つ上のセルを参照している数式があった場合、対象の列を削除してしまったときに発生します。

サンプルデータ1(セル参照)

サンプルデータ1のNo.3の行(5行目)を削除した場合、No.4以降が「#REF!」となります。

実際には、C6の数式で参照していたC5が削除されたため、C6セルの「C5」が「#REF!」に置き換わっています。以降のセル参照は補正されるため1つ上のセルを参照したままとなります。

ただ、C5が「#REF!」となっているため、エラーを含む数式として[#REF!」と表示されてしまいます。

上記例では、C5の「#REF!」を「C4」に更新すれば全体のエラーは解決しますが、削除する度に修正するのは手間になるので対策していきましょう。

決まった位置を参照する場合はINDIRECT関数を使う

今回の解説では1つ上のセルや1つ左のセルなど、対象のセルから決まった位置(座標)にあるセルを参照したいときに使用することができます。

対策としては、INDIRECT関数のR1C1スタイルを使います。

Rが「Row(行)」、Cが「Colum(列)」となるので、R[0]C[0]を自セルとして座標を指定します。

1つ上のセルを参照する場合:

=INDIRECT("R[-1]C", FALSE)

1つ右のセルを参照する場合:

=INDIRECT("RC[1]", FALSE)

※[0]は省略可能

INDIRECT関数を使った場合の削除を確認します。

サンプルデータ2(INDIRECT関数)

先程と同様に、No.3の行(5行目)を削除します。

INDIRECT関数で参照した場合は、D5セルが「#REF!」とならず、No.2の値に”-4″が追加されています。

以下は数式表示した場合

INDIRECT関数で指定した場合は「#REF!」に置き換わらずに参照しています。

上記例ではB列の参照はセル参照にしていますが、INDIRECT関数に置き換えることももちろん可能です。

置き換えた場合の数式:

=INDIRECT("R[-1]C", FALSE) & "-" & INDIRECT("RC[-2]", FALSE)

となります。行列どちらを削除しても「#REF!」にならない数式となりました。

セル参照の方が手軽なので用途に合わせて使い分けましょう。

モバイルバージョンを終了