いまさら聞けないExcelの使い方講座

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

「#REF!」エラーが出た!

 Excelで作業をしていたら、急に「#REF!」と表示されてびっくりした……という経験はありませんか。

 「#REF!」というエラーは、数式で参照するデータが存在しない時に発生するエラーです。セルに入力された数式の参照しているデータが、意図せず削除されてしまった時などに発生します。

 今回は、この「#REF!」というエラーが表示された時の対処法を2つ紹介します。

「#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つ紹介しました。どちらの方法も一長一短ですが、作成した資料が未完成で、内容を変更する可能性のある場合は、数式に関係するセル範囲は削除しない方がよさそうですね。

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

今月のExcelTips