表を作成したときに、数式で１つ上のセルや１つ横のセルを参照している場合、行や列を削除した場合に参照先がなくなり、「#REF!」に置き換わってしまいエラーとなることがあります。
「#REF!」を発生させないため、対象のセルから必ず１つ上のセルや１つ横ののセルを参照させる方法を紹介します。
行や列を削除したとき数式が「#REF!」になる原因
行や列を削除したとき、数式が「#REF!」になる原因は参照していたセルが存在しなくなったためです。
例えば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!」にならない数式となりました。
セル参照の方が手軽なので用途に合わせて使い分けましょう。
