貼り付け用シートを消したら集計表が#REF!だらけに｜消す前の30秒点検と直し方

貼り付け用シートを1枚消しただけで、集計が全部止まった

筆者は以前、データを貼り付けるためだけに用意していたシートを削除したことがあります。中身を入れ替える感覚で、シートごと消してしまったのです。集計表のほうには、そのシートを参照する数式がずらりと並んでいました。

結果は、集計表のあちこちに#REF!が出る状態でした。厄介だったのは、どこまで影響が広がっているのかがすぐにはわからなかったことです。#REF!のセルを足し算している合計欄もエラーになり、そのまた先の表もエラーになる。結局、原因に気づくまで4時間ほどかかりました。

このときは、手元に元のマスタブックが残っていました。マスタと見比べて「消したのは貼り付け用シートだ」と確かめ、壊れたブックを直すのはあきらめて、マスタをコピーして使い直しました。それ以来、貼り付け用のシートにも「このシートは集計表から参照されています。消さずに中身だけ入れ替えてください」という説明文を1行入れています。

この記事では、#REF!が出る仕組みと、出てしまったときに早く片づける手順、そして消す前に確かめる方法を順番に見ていきます。

#REF!は「参照していた場所がなくなった」という印

#REF!は、数式が参照していたセルやシートが消えてしまい、どこを見ればいいのかわからなくなったときに出るエラーです。数式の書き方が間違っているわけではなく、数式の「行き先」がなくなった状態だと考えてください。

出方は、何を消したかで変わります。

シートを消したとき

ほかのシートから参照されているシートを削除すると、そのシートを見ていた数式がすべて#REF!になります。たとえば =貼り付け!B2 という数式は、シートを消すと =#REF!B2 に変わります。集計表が1枚のシートに頼っている作りだと、被害がいちばん大きくなるのがこのパターンです。

行や列を消したとき

=B5*C5 のように特定のセルを指している数式は、5行目を削除すると =#REF!*#REF! になります。シートを消したときと違い、元の「B5」という住所そのものが消えてしまうのが特徴です。

範囲の一部を消したとき

=SUM(B2:B10) の途中の行を削除しても、#REF!にはなりません。範囲が自動で B2:B9 に縮むからです。エラーが出ないぶん安心しがちですが、合計の値は変わっています。範囲の行をすべて消したときだけ#REF!になります。

つまり、#REF!が出るのは「行き先が丸ごと消えたとき」です。そして、エラーが出ないまま数字だけ変わるケースもある、という点は覚えておいてください。

同じ名前のシートを作り直しても元に戻らない

シートを消してしまったとき、多くの人が最初に試すのが「同じ名前のシートを作って、データを貼り直す」ことです。筆者もそう考えました。ところが、これでは#REF!は消えません。

数式の中身は、シートを消した時点で =#REF!B2 に書き換わっています。数式はもう「貼り付け」という名前を覚えていないので、同じ名前のシートができても参照し直してはくれません。見た目は元通りでも、数式とシートのつながりは切れたままです。

ここを知らないと、作り直したシートにデータを貼っては首をかしげる、という作業を何度も繰り返すことになります。筆者が4時間かかった理由の1つもここでした。

列を消したらVLOOKUPだけが#REF!になる理由

シートや行を消していないのに、VLOOKUPの列だけが#REF!になることがあります。これは参照先が消えたのではなく、「列番号が範囲からはみ出した」ことが原因です。

作例で考えてみます。=VLOOKUP(A2,貼り付け!A:E,5,FALSE) は、A列からE列の5列の中で、左から5番目の列（E列）の値を返す式です。ここで貼り付け用シートのC列を削除すると、範囲は自動で A:D の4列に縮みます。ところが列番号の「5」は数字で書いてあるので、そのまま残ります。4列しかない範囲の5番目を探しに行くので、#REF!になるわけです。

もっと怖いのは、列番号が範囲の中に収まってしまう場合です。同じ式で列番号が「3」だったら、C列を消したあとはD列だった値が3番目に繰り上がり、エラーを出さずに別の列の値を返します。数字は出ているのに中身が違う、という一番気づきにくい状態です。

貼り付け用シートの列を消したり足したりする可能性があるなら、列番号を数字で書かずに済むXLOOKUPやINDEXとMATCHの組み合わせにしておくと、列の増減に強くなります。

まず、保存せずに戻せるかを確かめる

#REF!に気づいたら、手を動かす前に「戻せる道が残っているか」を確かめます。

• 行や列を消した直後なら Ctrl+Z：行・列の削除は元に戻すで取り消せます。
• シートの削除は Ctrl+Z で戻らない：削除の確認メッセージに「完全に削除されます」と出るとおり、シートの削除は元に戻せません。
• まだ上書き保存していないなら、保存せずに閉じる：閉じて開き直せば、最後に保存した状態から始められます。
• OneDriveやSharePointに保存しているブックなら、バージョン履歴：［ファイル］→［情報］→［バージョン履歴］から、消す前の版を開けます。
• 元のブックやマスタが残っているなら、それを使う：筆者のように、壊れたブックを直すよりマスタをコピーしたほうが早いことも多いです。

どれも使えないときに、次の「探して直す」に進みます。

#REF!のセルを全部見つける

直す前に、どこが壊れているかを洗い出します。目で探すと必ず見落とすので、Excelに探させましょう。

1. 集計表のシートで Ctrl+G を押し、［セル選択］をクリックする
2. ［数式］を選び、その下のチェックは［エラー値］だけ残す
3. ［OK］を押すと、エラーになっているセルがまとめて選ばれる

ブック全体で探したいときは、Ctrl+F の［オプション］で［検索場所］を「ブック」、［検索対象］を「数式」にして「#REF!」を検索し、［すべて検索］を押します。一覧に出たセルの数が、直す量の目安になります。

ここで1つ注意があります。選ばれたセルの中には、#REF!そのものが入っているセルと、#REF!のセルを足し算しているせいでエラーになっているだけのセルが混ざっています。数式の中に「#REF!」という文字が入っているセルが本当の原因で、そこを直せば後ろのセルは自然に戻ります。Ctrl+F の検索で数式に「#REF!」を含むセルを探すと、原因のセルだけを拾えます。

シートを消した#REF!は、置換でまとめて直せる

シートを削除した場合は、数式の中に #REF!B2 のようにセルの住所が残っています。このおかげで、置換を使えば一気に直せます。

1. 消したシートと同じ名前・同じ並びのシートを作り、データを貼る
2. 集計表のシートで Ctrl+H を押す
3. ［検索する文字列］に「#REF!」、［置換後の文字列］に「貼り付け!」（作り直したシート名＋!）を入れる
4. ［オプション］で［検索対象］が「数式」になっていることを確かめて［すべて置換］

=#REF!B2 が =貼り付け!B2 に戻り、数式がシートを見つけ直します。手順1を飛ばして置換すると、存在しないシートを指すことになり、ファイルを選ぶ画面が開いてしまうので順番は守ってください。行や列の削除で出た#REF!が同じシートに混ざっていると、そちらも置き換わって別の誤った式になります。先に Ctrl+F の［すべて検索］で一覧を見て、シート削除の#REF!だけを選んでから置換する範囲を絞ってください。シート名に空白が入っている場合は 'データ 貼り付け'! のように、名前を半角の ‘ で囲みます。

置換したら、合計欄など何か所かをマスタや前回の結果と見比べて、数字が合っているかを確かめておくと安心です。

行や列を消した#REF!は、式を書き直すしかない

行や列を消した場合は、=#REF!*#REF! のように住所そのものが消えています。どのセルを指していたのかを数式から読み取れないので、置換では直せません。

書き直すときは、次の順で手がかりを集めます。

• 同じ列の、壊れていない行の数式を見る：1行上や1行下が正常なら、それをコピーして貼るだけで直ることが多いです。
• 前回の版やマスタの同じセルを見る：数式バーの中身を写せば確実です。
• 作った人に聞く：数式の意図がわからないまま推測で書き直すと、別の誤りを埋め込むことになります。

なお、#REF!をIFERRORで空白や0に置き換えるのはやめておきましょう。エラーは消えますが、壊れた参照はそのまま残り、合計だけが静かに小さくなります。エラーを隠したせいで集計がずれる話は、IFERRORを付ける前に確かめたいことで詳しく書いています。

消す前の30秒点検

一番いいのは、そもそも#REF!を出さないことです。シートや行を消す前に、そこを参照している数式がないかを30秒で確かめられます。

シートを消す前：シート名でブック内を検索

Ctrl+F の［オプション］で［検索場所］を「ブック」、［検索対象］を「数式」にし、消したいシートの名前を検索します。たとえば「貼り付け!」で1件でも見つかれば、そのシートはどこかから参照されています。0件なら消しても#REF!は出ません。

行や列を消す前：参照先のトレース

消したい行のセルを選び、［数式］タブの［参照先のトレース］を押します。矢印が出れば、そのセルを使っている数式があります。点線の矢印と小さな表のアイコンが出たときは、別のシートから参照されている合図です。矢印をダブルクリックすると、参照しているセルの一覧が出ます。

名前を付けた範囲も見ておく

セル範囲に「売上データ」のような名前を付けて数式で使っている場合、その範囲があるシートを消すと、名前の中身も =#REF!$A$1:$E$500 のように壊れます。数式は「=SUM(売上データ)」のままなので、数式バーを見ても原因がわかりません。Ctrl+F3 で［名前の管理］を開くと、参照範囲の欄に#REF!が出ている名前が見つかります。シートを消す前に、ここも一度のぞいておくと安心です。

この3つを習慣にするだけで、「消してから気づく」はかなり減ります。

そもそも消さずに済む作りにする

点検は忘れることがあります。うっかり消しても壊れない、あるいは消せない作りにしておくと、もう一段安心です。

• 貼り付け用シートは「シートごと」ではなく「中身だけ」消す：Ctrl+A で全体を選んで Delete を押せば、シートとのつながりを残したまま中身を空にできます。貼り付け用と集計用を分けて運用する方法は、貼り付けシートと集計シートに分けた実例で、週3時間の作業が1時間になるまでを紹介しています。
• 貼り付け用シートの1行目に説明を書く：筆者がいまやっている方法です。「集計表から参照中。シートは消さない」と書いておくだけで、自分も、引き継いだ人も手が止まります。
• シートの構成を保護する：［校閲］→［ブックの保護］で「シート構成」にチェックを入れると、シートの削除や名前の変更ができなくなります。シートの追加もできなくなるので、構成が固まったブックに向いています。
• 参照される側の行を消さない運用にする：不要になった行は削除せず、フラグ列に「除外」と入れて集計条件で外すほうが安全な表もあります。

ブックを人に渡す前提なら、こうした「壊れにくさ」はなおさら効いてきます。役割ごとにシートを分ける考え方は、人に引き継げる表の作り方にまとめています。

4時間かかったあの日の自分に伝えたい順番

最後に、#REF!が出たときの動き方を、筆者が遠回りした経験から並べ直しておきます。

1. 上書き保存をしない。行・列の削除なら Ctrl+Z で戻す
2. 保存せずに閉じる、バージョン履歴、マスタのどれかで戻せないかを確かめる
3. Ctrl+F で数式に「#REF!」を含むセルを探し、原因のセルだけを洗い出す
4. シートを消した場合は、同じ名前のシートを作ってから置換で直す
5. 行や列を消した場合は、隣の行や前の版を見て書き直す
6. 直したら、合計を前回の結果と見比べる

あの日の筆者は、3の前に「同じ名前のシートを作り直す」を何度も試して時間を溶かしました。順番を知っていれば、マスタに切り替える判断も、もっと早くできたはずです。

複数の人が触るブックで、どこを点検すれば壊れにくくなるかをもう一歩踏み込んで確かめたい方は、人に渡せる形にする改修手順と点検シートも参考にしてください。