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

【Excel】表を編集していたらエラーが！エクセルで「#REF!」エラーが出てしまった時の対策

トップスタジオ

2019年4月24日 06:55

表を編集していたら突然「#REF!」エラーが表示された！

　Excelで数式を作成していると、さまざまなエラーに遭遇することがありますよね。『何気ない操作をしたら突然「#REF!」エラーが現れて焦った！』という経験のある読者は意外と多いのではないでしょうか。

　「#REF!」エラーは、数式で参照しているセルをうっかり削除してしまった時などに発生するエラーです。そこで今回は、このような「#REF!」エラーが表示される理由と対処法を解説します。

どんな操作をすると「#REF!」エラーが表示される？

　では、具体的にどのような操作をしたらこの「#REF!」エラーが表示されるのか、実際に試してみましょう。次の「〇△物産顧客名簿」の例を使ってやってみます。この表では「姓」と「名」が別々のセルに入力されていますが、これをまとめてD列の「氏名」欄（①）に表示したいとします。

　このような例で、「姓」と「名」を結合して表示する方法はいろいろありますが、今回はセルD3に「=B3&C3」（②）という数式を入力します。

　［Enter］キーを押して数式を確定すると、「萩原」という姓と「幸雄」という名が結合されて「萩原幸雄」（③）と表示されました。

　オートフィルで、この数式をD列の他のセルにコピーします（④）。D列のすべてのセルで、「姓」と「名」を結合して「氏名」を表示できましたね。

　D列に「氏名」を表示できたので、「姓」と「名」に分かれたデータは一見必要なくなったように思えます。そこで、B列とC列のデータを削除することにしましょう。B列とC列の列見出しをドラッグしてこれらの列全体を選択（⑤）し、［ホーム］タブ（⑥）→［削除］（⑦）→［シートの列を削除］（⑧）をクリックします。

　すると「姓」列と「名」列のデータが削除されると同時に、先ほどまでデータが入力されていた「氏名」列のデータがすべて「#REF!」（⑨）に変わってしまいました！　一体なぜでしょうか。

　先ほど「氏名」列に入力した数式を思い出してください。「氏名」列の数式では、「姓」列と「名」列のデータを参照していたのに不要だとして削除してしまったため、このようなエラーが発生してしまったのです。

対処法1：不要なデータは削除せずに隠す

　ではこの「#REF!」エラーを回避するにはどうしたらいいでしょうか。1つ目の方法は、見た目上は不要でも数式で参照しているデータは削除しないで隠す（非表示にする）というものです。この方法を使えば、不要なデータは見えなくなりますが、削除されたわけではないので数式では正常に参照できます。

　ではやってみましょう。「姓」列と「名」列を削除する前の「〇△物産顧客名簿」に戻します。隠したい列全体（B列とC列）を選択（①）して、［ホーム］タブ（②）→［書式］（③）→［非表示/再表示］（④）→［列を表示しない］（⑤）をクリックします。

　するとB列の「姓」とC列の「名」は表示されなくなりますが、実際のデータは存在しているので「氏名」列に「#REF!」エラーが表示されることはありません（⑥）。

対処法2：「氏名」列のデータを値としてコピペする

　前述の例で「氏名」列にエラーが表示されたのは、ここに入力されている数式で参照しているセルが削除されたからでしたよね（①）。したがって、「氏名」列に数式ではなく値を入力しておけば、エラーは発生しなくなるといえます。そこで2つ目の方法として、「姓」と「名」を結合する数式が入力されている「氏名」列のデータをコピペして値に変換する方法を解説します。

　D列の列見出しをクリックして列全体を選択（②）し、［Ctrl］＋［C］キーを押して（③）列全体をコピーします。

　少し変な感じがするかもしれませんが、コピーしたデータを同じ場所に貼り付けます。再びD列の列見出しをクリックして（④）、［Ctrl］＋［V］キーを押します（⑤）。すると、セルD3の右横に［貼り付けオプション］ボタン（⑥）が表示されるのでクリックし、表示されるメニューから［値］（⑦）を選択します。

　すると、D列のデータが値として貼り付けられました。試しにセルD3をクリックしてみましょう。先ほどは「=B3&C3」という数式が入力されていましたが、「萩原幸雄」（⑧）という文字列（値）に変わっていますね。これなら、「姓」列と「名」列はどこからも参照されていないので、削除しても大丈夫そうです。

　実際に「姓」列と「名」列を削除してみても、エラーは出現しませんね（⑨）。