絶望感がハンパないExcelの「#REF!」エラーは数式の置換で一気に解決！

参照先が見つからないときに表示される「#REF!」エラー

　Excelの数式に誤りがあるとエラーが表示されます。エラーの内容によって「#VALUE!」「#N/A!」「#DIV/0!」「#REF!」などと表示されますが、悩ましいのが「#REF!」です。数式から参照するセルを含む行や列、シートを削除してしまい、参照先のセルがなくなった場合に表示されます。ちなみに「#REF!」は「reference」の略。参照エラーという意味です。

　何が問題かというと、数式に含まれるセル参照の部分が「#REF!」に置き換わり、もとのセル参照がわからなくなることです。以下は「#REF!」エラーの単純な例です。セルA1とB1の掛け算をセルC1に入力してあります。ここで列Bを削除すると、参照先を見失って「#REF!」エラーとなります。

セルC1には、セルA1とB1の掛け算が入力されている

B列を削除すると「#REF!」エラーになる

　行や列の削除の直後であれば［Ctrl］＋［Z］キーで戻せますが、シートの削除は復元不可。操作直後にやってしまった！　と頭を抱えた経験がある人もいるのではないでしょうか。今回は、悩ましい「#REF!」エラーの対処法を紹介したいと思います。

［数式の表示］で状況を確認する

　以下は見積の商品名と単価をセル参照で取得しています。商品を管理している［商品］シートを別ファイルとして保存し、元のファイルから［商品］シートを削除したら「#REF!」エラーが表示されてしまいました。参照できない「商品名」「単価」に加え、金額計算のセルも「#REF!」エラーとなっています。

数式から参照する［商品］シートを削除してしまったため、「#REF!」エラーが表示された。金額計算のセルにも「#REF!」エラーが表示されている

　こんな時は焦らずに［数式の表示］で状況を把握しましょう。［数式］タブにある［数式の表示］ボタンをクリックして、入力されている数式を表示します。表示を元に戻すときは、再度［数式の表示］ボタンをクリックします。

［数式］タブの［数式の表示］をクリックする。［Ctrl］＋［Shift］＋［@］キーを押してもいい

入力されている数式が表示される。列幅を調整しておく

　数式の一部が「#REF!」に置換されているのは「商品名」と「単価」です。通常の表示で「#REF!」と表示されていた金額計算のセルの数式は問題ありませんね。つまり「#REF!」エラーが発生しているセルを参照する数式にも「#REF!」エラーと表示されることがわかります。慌てて正しい数式を削除しないようにしてください。

文字列の「置換」で解決

　「#REF!」エラーを手動で修正するのはおすすめしません。修正ミスにより「#VALUE!」エラーなどが表示される可能性もあります。上記の例でも12カ所あります。「置換」の機能を使うのがスマートです。置換は通常の表示でも可能ですが、数式を表示した状態で操作したほうが結果を確認しやすいのでおすすめです。

　ここでは、別ファイルに保存していた［商品］シートを移植し、「#REF!」の文字列を「商品!」に置換していきます。［検索と置換］ダイアログボックスは［Ctrl］＋［H］キーで呼び出せます。

　［すべて置換］は意図しない箇所を修正してしまう可能性があるので、ひとつずつ［置換］を推奨します。［次を検索］をクリックして、検索範囲を一通り確認してから置換してもいいと思います。

［商品］シートを移植し、元に戻しておく。［Ctrl］＋［H］キーを押して［検索と置換］ダイアログボックスを呼び出す。［検索する文字列］に「#REF!」、［置換後の文字列］に「商品!」と入力する。［置換］をクリックする

「#REF!」が「商品!」に置換された。次に検索にヒットしたセルが選択される

複数回の置換するときのコツ

　ひとつのシートから複数のシートのセル範囲を参照していることもあるでしょう。そういった場合には、先ほどの例ならば「#REF!$A$2」と「#REF!$B$2」といったように、セル範囲を指定した文字列を含めて置換してください。

　また、「#REF!」エラーのあるファイルを保存して開き直した場合、以下のように「=XLOOKUP(C2,#REF!,#REF!)」などと表示されることがあります。

「=XLOOKUP(C2,#REF!,#REF!)」のような「#REF!」エラーが表示されることもある

　XLOOKUP関数の引数がすべて「#REF!」に置き換わってしまっています。先ほどと同じ手法は使えませんが、あきらめる必要はありません。置換したい文字列は「#REF!,#REF!)」なので、最初の「#REF!,」と「#REF!)」で分けて考えて、左の「#REF!,」を「商品!$A$2:$A$10,」、右の「#REF!)」を「商品!$B$2:$B$10)」と置換すればいいだけです。

　なお、「仕入先」は別のシートを参照しているので、間違って修正しないように「製品名」のセル範囲を選択してから［検索と置換］ダイアログボックスを操作します。

置換したい範囲を選択して［Ctrl］＋［H］キーを押す。［検索する文字列］に「#REF!,」、［置換後の文字列］に「商品!$A$2:$A$10,」と入力して置換する

すべて置換した結果。「#REF!」の表示が変更されない場合は［検索と置換］ダイアログボックスを一度閉じて確認しよう

続けて［検索する文字列］に「#REF!)」、［置換後の文字列］に「商品!$B$2:$B$10)」と入力して置換する

すべて置換できた

「仕入先」の「#REF!」エラーも同様に修正できる

　一般的な文字列置換の操作と同じなので難しくはないですよね。ほかのファイルを参照するセルに含まれるファイルパスを削除したり、参照するセル範囲をまとめて書き換えたりするときにも便利です。