【Excel】表の編集中に突然「#REF」と表示された！エクセルで数式が参照しているデータを削除した際に出るエラーの対処法

「#REF!」エラーが出る時とは

　例として「感謝セール割引表」を使って考えてみます。この表では、B列の「定価」（①）とC列の「割引率」（②）をもとに、D列の「割引額」（③）を計算しています。

　D列の「割引額」は「定価×割引率」という式で求めています。試しにセルD3をクリックしてみると、「=B3*C3」（④）と入力されています。

　さらに、「定価」と「割引額」をもとに、E列の「割引後額」（⑤）を計算しています。計算式は「定価－割引額」です。試しにセルE3をクリックしてみると、「=B3-D3」（⑥）と入力されていますね。

　ここで、「割引後額」を算出できたので、「割引額」の列は必要がなくなったとして、削除することにします。D列を選択して（⑦）、［ホーム］タブ（⑧）→［削除］（⑨）をクリックします。

　すると、先ほどまでデータが入力されていた「割引後額」列のデータが「#REF!」（⑩）に変わってしまいました。

　「割引後額」列に入力されている数式（定価－割引額）が参照する「割引額」列のデータが削除されたため、このようなエラーが発生してしまったのです。

対処法1：「割引額」列を非表示にする

　このような場面で、「#REF!」エラーを表示させないためには、どうしたらよいでしょうか。まず1つは、不要になった「割引額」列は、削除せずに非表示にしようという方法があります。では、やってみましょう。

　D列を選択して（①）、［ホーム］タブ（②）→［書式］（③）→［非表示/再表示］（④）→［列を表示しない］（⑤）をクリックします。

　すると、「割引額」列が表示されなくなりました（⑥）が、「割引後額」列のデータは保たれています（⑦）。「割引額」列は非表示にしているだけで、削除されたわけではないからです。

　この方法のよいところは、割引率に変更があった場合でも、正しく計算されることです。
例として、「ロゴ入りショート丈ソックス」の「割引率」（⑧）を「5％」から「10％」に変更してみます。変更前の「割引後額」は「¥608」（⑨）です。

　セルC4の「割引率」を「10％」（⑩）に変更すると、「割引後額」も自動的に「¥576」（⑪）に変更されました。

　非表示機能を使うことのデメリットは、そこにデータがあることを忘れがちだということです。そのため、非表示になっていることを知らない他人が、意図しない変更を行ってしまう可能性があります。

対処法2：「割引後額」列のデータを「値」としてコピペする

　先ほどの「感謝セール割引表」の例では、「割引後額」列には数式が入力されていて、その数式が参照しているデータが削除されたためにエラーが発生したのでしたよね。2つ目の方法は、この「割引後額」列のデータをコピーして、別の列に「値」として貼り付けようというやり方です。ではやってみましょう。

　E列を選択して（①）、［Ctrl］＋［C］キーを押してコピーします。

　隣のF列を選択して［Ctrl］＋［V］キーを押し、コピーしたデータを貼り付けます。すると、［貼り付けオプション］ボタン（②）が表示されるのでクリックし、［貼り付けオプション］メニューから［値と元の書式］（③）を選択します。

　すると、F列には元の書式を保った状態で、E列のデータが「値」として貼り付けられます（④）。試しにセルF3をクリックしてみると、数式バーには数式ではなく「927」（⑤）という値が表示されますね。

　必要がなくなったD列とE列を削除します。D列とE列を選択して（⑥）、［ホーム］タブ（⑦）→［削除］（⑧）をクリックします。

　「割引後額」列に「#REF!」というエラーは発生しませんね（⑨）。

　この方法のよいところは、非表示機能の欠点（非表示にした列は忘れがちだという点）を補えることです。ただし、「割引率」のデータを変更する場合には、注意する必要があります。試しに、セルC4（⑩）の値を「5％」から「10％」に変えてみましょう。「割引後額」は「¥608」（⑪）です。

　セルC4の値が「10％」（⑫）に変わっても、セルD4の「割引後額」は、「¥608」（⑬）のままで、自動的に計算はされませんね。

　このままスルーしてしまうと、誤った「割引後額」が適用されてしまう可能性があります。

「#REF!」が表示されても慌てない

　今回は、「#REF!」というエラーが表示された時の対処法を2つ紹介しました。どちらの方法も一長一短ですが、作成した資料が未完成で、内容を変更する可能性のある場合は、数式に関係するセル範囲は削除しない方がよさそうですね。

　ぜひ参考にしてみてくださいね！