【Excel】名前の定義で「#REF!」が大量に残る!Excelの名前マネージャーを一括整理する手順

Excelで名前の定義機能を使うと、数式が分かりやすくなることがあります。しかし、シートを削除したり、参照範囲を変更したりすると、定義済みの名前が「#REF!」エラーとなることがあります。これらの不要な名前が蓄積すると、名前マネージャーが煩雑になり、管理が困難になります。この記事では、「#REF!」エラーを含む名前の定義を一括で整理する手順を解説します。

Excelで「#REF!」エラーが大量に発生している場合、原因の特定と削除に手間がかかります。手作業での削除は時間がかかり、ミスも発生しやすくなります。本記事を読むことで、効率的に名前マネージャーを整理し、Excelファイルの管理を改善できます。

「#REF!」エラーを含む名前の定義が残る原因

Excelの名前定義機能は、特定のセル範囲や定数、数式に名前を付ける機能です。この機能は、数式を簡潔にし、可読性を高めるのに役立ちます。しかし、定義した名前が参照しているセルや範囲が削除されたり、移動されたりすると、その名前は無効になります。Excelは、無効になった名前を「#REF!」エラーとして表示します。

例えば、あるシートのA1セルに「売上」という名前を定義し、そのシートを削除した場合を考えます。削除されたシートを参照していた「売上」という名前は、もはや有効なセル範囲を指し示せなくなります。このような状況が積み重なると、名前マネージャーには「#REF!」エラーを示す名前が数多く残ることになります。これらはExcelの計算には影響しませんが、名前マネージャーのリストが長くなり、意図しない名前の定義を見つけにくくする原因となります。

「#REF!」エラーを含む名前を一括削除する手順

1. VBAエディターを開く
   Excelファイルを開いた状態で、キーボードの「Alt」キーと「F11」キーを同時に押します。これにより、Visual Basic for Applications(VBA)エディターが開きます。
2. 標準モジュールを挿入する
   VBAエディターのメニューバーから「挿入」をクリックし、「標準モジュール」を選択します。これにより、コードを入力するための新しいモジュールウィンドウが表示されます。
3. VBAコードを貼り付ける
   表示されたモジュールウィンドウに、以下のVBAコードをコピーして貼り付けます。

“`vba
Sub DeleteAllRefNames()
Dim nm As Name
Dim count As Integer
count = 0
Application.ScreenUpdating = False
For Each nm In ThisWorkbook.Names
If nm.RefersTo Like “=#REF!*” Then
nm.Delete
count = count + 1
End If
Next nm
Application.ScreenUpdating = True
MsgBox count & “個の名前定義を削除しました。”
End Sub
“`

1. VBAコードを実行する
   VBAエディターのメニューバーにある「実行」をクリックし、「Sub/ユーザーフォームの実行」を選択します。または、コードウィンドウ内でF5キーを押しても実行できます。
2. 確認メッセージを確認する
   コードの実行が完了すると、「○○個の名前定義を削除しました。」というメッセージボックスが表示されます。ここで表示された数が、削除された「#REF!」エラーの名前の数です。
3. Excelに戻って確認する
   VBAエディターを閉じ、Excelのシートに戻ります。「数式」タブをクリックし、「名前マネージャー」を選択します。定義済みの名前リストから、「#REF!」エラーの項目が削除されていることを確認してください。

VBAコードの解説

このVBAコードは、Excelファイル内のすべての名前定義を調べ、「#REF!」エラーを参照しているものを特定して削除します。具体的には、`ThisWorkbook.Names`でブック内のすべての名前オブジェクトを取得し、`nm.RefersTo Like “=#REF!*”`という条件で「#REF!」エラーを参照している名前のみをフィルタリングします。条件に合致した名前は`nm.Delete`メソッドで削除されます。`Application.ScreenUpdating = False`と`Application.ScreenUpdating = True`は、コード実行中に画面の更新を停止し、処理速度を向上させるためのものです。最後に、削除された名前の数を`MsgBox`で表示し、ユーザーに結果を伝えます。

「#REF!」エラー以外の名前整理方法

VBAを使用する方法は効率的ですが、VBAに慣れていない場合や、より簡単な方法を求める場合は、手動での整理も可能です。

名前マネージャーでの手動削除

名前マネージャーを開き、「#REF!」エラーとなっている名前を一つずつ選択して削除する方法です。

1. 名前マネージャーを開く
   「数式」タブの「名前マネージャー」をクリックします。
2. 「#REF!」エラーの項目を探す
   リストの上部にある「エラー」列を確認します。「#REF!」と表示されている行を探します。
3. 項目を選択して削除する
   削除したい「#REF!」エラーの項目を選択します。複数選択したい場合は、Ctrlキーを押しながらクリックします。選択した項目が反転表示されたら、「削除」ボタンをクリックします。
4. 変更を保存する
   「閉じる」ボタンをクリックして名前マネージャーを閉じます。Excelファイルを保存します。

この方法は、エラーの数が少ない場合には有効ですが、数が多くなると手間がかかります。また、誤って有効な名前を削除しないように注意が必要です。

名前の参照範囲を修正する

「#REF!」エラーとなっている名前が、実際には有効なセル範囲を参照すべきものである場合、参照範囲を修正することでエラーを解消できます。

1. 名前マネージャーを開く
   「数式」タブの「名前マネージャー」をクリックします。
2. エラーとなっている名前を選択する
   「#REF!」エラーとなっている名前を選択します。
3. 「参照先」を編集する
   名前マネージャーの上部にある「参照先」の入力ボックスに、正しいセル範囲を入力するか、シート上で直接範囲を選択し直します。
4. 「OK」をクリックする
   編集が終わったら、「OK」ボタンをクリックして変更を保存します。
5. Excelファイルを保存する
   名前マネージャーを閉じ、Excelファイルを保存します。

この方法で、意図せず削除してしまった名前や、参照先が変更された名前を復旧させることができます。

Excelの名前定義に関する注意点

Excelの名前定義機能は便利ですが、いくつか注意すべき点があります。これらの注意点を理解しておくことで、意図しないエラーの発生を防ぎ、より効果的に機能を活用できます。

名前の命名規則

名前定義には、いくつかの命名規則があります。これらの規則に従わないと、名前の作成や使用に問題が生じることがあります。

• 名前は文字、アンダースコア(_)、またはバックスラッシュ(\)で始める必要があります。
• 数字で始めることはできません。
• 名前にスペースを含めることはできません。スペースの代わりにアンダースコアを使用します。
• Excelの関数名(SUM、AVERAGEなど)や、セル参照(A1、B2など)と同じ名前は使用できません。
• 大文字と小文字は区別されません。例えば、「売上」と「URIAGE」は同じ名前として扱われます。

これらの規則を守ることで、名前の重複や競合を防ぎ、数式での参照をスムーズに行えます。

名前のスコープ

名前には「ブック スコープ」と「ワークシート スコープ」の2種類があります。ブック スコープの名前は、ファイル内のどのシートからでも参照できます。一方、ワークシート スコープの名前は、その名前が定義された特定のワークシート内でのみ有効です。

名前マネージャーで名前を作成または編集する際に、スコープを設定できます。意図しない名前の競合や、特定のシートでのみ使用したい名前がある場合は、ワークシート スコープを適切に設定することが重要です。例えば、複数のシートに同じ名前のデータ範囲がある場合、それぞれにワークシートスコープの名前を定義することで、区別して使用できます。

名前の更新と削除

前述の通り、名前が参照しているセル範囲が変更されたり、シートが削除されたりすると、名前は「#REF!」エラーとなることがあります。定期的に名前マネージャーを確認し、不要になった名前やエラーとなっている名前は削除することが推奨されます。これにより、ファイルサイズを抑え、管理を容易に保つことができます。

また、名前を削除する際には、その名前が数式内で使用されていないかを確認することが重要です。もし使用されている名前を削除してしまうと、その数式も「#NAME?」エラーとなる可能性があります。VBAコードによる一括削除は、この確認を自動で行うため、安全に不要な名前を削除できます。

まとめ

Excelの名前定義機能で「#REF!」エラーが大量に残ると、名前マネージャーの管理が煩雑になります。本記事では、VBAコードを使用して「#REF!」エラーの名前定義を一括で削除する手順を解説しました。また、手動での削除方法や、名前の参照範囲を修正する方法も紹介しました。これらの手順を実践することで、Excelファイルの名前定義を効率的に整理し、管理しやすくなります。今後は、名前定義を使用する際に、命名規則やスコープに注意し、定期的に名前マネージャーを整理することで、Excelファイルの運用効率を高めることができます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】