Excelで「名前の定義が重複しています」が何度も出る原因と対処法

Excelでシートをコピーしたときに、

「名前の定義が重複しています」

というメッセージが何度も表示され、Enterキーを押し続けないと先に進めないことがあります。

この現象は、コピー元またはコピー先のブックに、同じ名前の定義が多数残っている場合に起こります。特に厄介なのは、Ctrl+F3で開く「名前の管理」に表示されない非表示の名前が残っているケースです。

この記事では、次の順番で対処方法を整理します。

まず手作業で確認する方法
名前の管理に表示されない非表示の名前がある理由
削除してよい名前・削除に注意すべき名前
削除前に一覧化するVBA
#REF! になっている名前だけを削除するVBA
最終手段として、印刷設定・フィルタ設定などを除いて整理するVBA

VBAを使う方法も紹介しますが、実行前には必ずブックのバックアップを取ってください。VBAで削除した操作は、通常の編集操作のようにCtrl+Zで戻せません。

まず試すこと
「名前の定義が重複しています」が表示される原因
まずは名前の管理で確認する
名前の管理に表示されない「非表示の名前」がある
削除してよい名前と、削除に注意すべき名前
VBAを実行する前の注意点
#REF!の名前だけを削除するVBA
それでも解決しない場合の最終手段
まとめ
参考情報

まず試すこと

おすすめの対応順は次のとおりです。

Ctrl+F3で「名前の管理」を開く
エラーを含む名前、不要な名前を手作業で確認する
名前の管理で削除しても解決しない場合、非表示の名前を疑う
参照先が #REF! になっている名前だけ削除する
それでも解決しない場合のみ、一括整理を検討する

いきなり全削除するのではなく、まずは #REF! になっている壊れた名前だけを削除するのが安全です。

「名前の定義が重複しています」が表示される原因

Excelには、セル範囲や数式に名前を付ける「名前の定義」という機能があります。

たとえば、売上データの範囲に 売上表 という名前を付けたり、特定のセルに 消費税率 という名前を付けたりできます。名前を使うと数式が読みやすくなる一方で、ブック内に不要な名前が大量に残ることがあります。

シートをコピーすると、コピー元シートに紐づく名前の定義も一緒にコピーされることがあります。そのとき、コピー先ブックに同じ名前がすでに存在すると、Excelが「この名前はすでにあります」という確認を表示します。

これが少数なら問題ありませんが、壊れた名前や不要な名前が大量にあると、同じような確認メッセージが何十回も表示されることがあります。

まずは名前の管理で確認する

最初に試すべきなのは、Excel標準機能の「名前の管理」です。

手順は次のとおりです。

Excelで対象ブックを開く
Ctrl+F3を押す
「名前の管理」を開く
参照先が #REF! になっている名前がないか確認する
明らかに不要な名前だけ削除する

Microsoft公式情報でも、名前の管理では名前の値・参照先・スコープを確認でき、エラーを含む名前をフィルタできると説明されています。

この画面で不要な名前を削除して解決する場合は、VBAを使う必要はありません。

名前の管理に表示されない「非表示の名前」がある

問題は、名前の管理に表示されない名前が存在することです。

Excelの名前には Visible というプロパティがあり、これがFalseになっている名前は、通常の名前の定義ダイアログに表示されません。VisibleプロパティをFalseにした名前は、名前の定義ダイアログに表示されません。また、名前の管理では、VBAで定義された名前や非表示の名前が表示されない仕様のようです。

つまり、Ctrl+F3の名前の管理で削除したのに、まだ「名前の定義が重複しています」が出続ける場合、非表示の名前が残っている可能性があります。

削除してよい名前と、削除に注意すべき名前

不要な名前の代表例は、参照先が #REF! になっている名前です。

#REF! は、参照していたシートやセル範囲が削除され、参照先が壊れている状態です。こうした名前は、実務上は不要になっていることが多く、削除候補になります。

ただし、すべての名前を削除してよいわけではありません。

たとえば、次のような名前はExcelの機能と関係していることがあります。

Print_Area
Print_Titles
_FilterDatabase
Database
Criteria
Extract
Auto_Open

そのため、この記事では、まず「参照先が #REF! になっている名前」だけを削除対象にします。印刷範囲やフィルタ設定に関係する名前は、原則として残す方針にします。

VBAを実行する前の注意点

VBAを実行する前に、必ず次の点を確認してください。

対象ブックのバックアップを取る
対象ブックをアクティブにしてから実行する
可能であれば、まず一覧出力だけ実行する
共有中のファイルや重要ファイルでは、いきなり本番実行しない
実行後はCtrl+Zで元に戻せない

特に、複数のブックを開いている場合は注意が必要です。以下のコードは ActiveWorkbook、つまり現在アクティブなブックを対象にします。実行前に、必ず整理したいブックを選択してください。

#REF!の名前だけを削除するVBA

このコードは、参照先に #REF! を含む名前のうち、印刷範囲・印刷タイトル・フィルタ設定などに関係しそうな名前を除いて削除します。

VBAの実行手順は次のとおりです。

対象ブックを開く
Alt + F11でVBAエディターを開く
「挿入」→「標準モジュール」を選ぶ
下記コードを貼り付ける
DeleteRefErrorNamesRobust を実行する

Option Explicit

' True にすると Print_Area / Print_Titles / _FilterDatabase なども削除対象にします。
' 安全優先なら False のままにしてください。
Private Const DELETE_EXCEL_STANDARD_NAMES As Boolean = False

Sub DeleteRefErrorNamesRobust()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim delCount As Long
    Dim keepCount As Long
    Dim failCount As Long
    Dim targetCount As Long
    Dim msg As String

    Set wb = ActiveWorkbook

    If wb Is Nothing Then
        MsgBox "対象ブックが見つかりません。", vbExclamation
        Exit Sub
    End If

    msg = "対象ブック: " & wb.Name & vbCrLf & vbCrLf & _
          "参照先に #REF! または #参照! を含む名前の定義を削除します。" & vbCrLf & _
          "非表示の名前も削除対象にします。" & vbCrLf & vbCrLf

    If DELETE_EXCEL_STANDARD_NAMES Then
        msg = msg & "注意: 印刷範囲・フィルタ設定などのExcel標準名も削除対象です。" & vbCrLf & vbCrLf
    Else
        msg = msg & "印刷範囲・フィルタ設定などのExcel標準名は残します。" & vbCrLf & vbCrLf
    End If

    If wb.ProtectStructure Then
        msg = msg & "注意: このブックは構造保護されています。" & vbCrLf & _
                    "削除に失敗する可能性があります。" & vbCrLf & vbCrLf
    End If

    msg = msg & "実行前にバックアップを取ってください。" & vbCrLf & _
                "実行してよろしいですか？"

    If MsgBox(msg, vbYesNo + vbExclamation) = vbNo Then
        Exit Sub
    End If

    ' ブックレベルの名前を処理
    DeleteNamesInCollection wb.Names, _
                            True, _
                            delCount, keepCount, failCount, targetCount

    ' シートレベルの名前を処理
    For Each ws In wb.Worksheets
        DeleteNamesInCollection ws.Names, _
                                False, _
                                delCount, keepCount, failCount, targetCount
    Next ws

    MsgBox "処理が完了しました。" & vbCrLf & vbCrLf & _
           "REFエラーを含む名前: " & targetCount & " 個" & vbCrLf & _
           "削除した名前: " & delCount & " 個" & vbCrLf & _
           "保護して残した名前: " & keepCount & " 個" & vbCrLf & _
           "削除に失敗した名前: " & failCount & " 個", _
           vbInformation

End Sub

Private Sub DeleteNamesInCollection( _
    ByVal namesCol As Names, _
    ByVal skipSheetScopedNames As Boolean, _
    ByRef delCount As Long, _
    ByRef keepCount As Long, _
    ByRef failCount As Long, _
    ByRef targetCount As Long)

    Dim i As Long
    Dim nm As Name
    Dim nameText As String
    Dim refersToText As String
    Dim refersToLocalText As String
    Dim errNo As Long

    For i = namesCol.Count To 1 Step -1

        Set nm = Nothing

        On Error Resume Next
        Set nm = namesCol.Item(i)
        errNo = Err.Number
        Err.Clear
        On Error GoTo 0

        If nm Is Nothing Then
            failCount = failCount + 1
            GoTo ContinueLoop
        End If

        nameText = GetNameText(nm)

        ' wb.Names 側にシートスコープ名が混在する場合の二重処理防止
        If skipSheetScopedNames Then
            If InStr(1, nameText, "!", vbTextCompare) > 0 Then
                GoTo ContinueLoop
            End If
        End If

        refersToText = GetRefersToText(nm)
        refersToLocalText = GetRefersToLocalText(nm)

        If HasRefError(refersToText) Or HasRefError(refersToLocalText) Then

            targetCount = targetCount + 1

            If IsProtectedExcelName(nameText) And Not DELETE_EXCEL_STANDARD_NAMES Then

                keepCount = keepCount + 1

            Else

                Err.Clear
                On Error Resume Next
                nm.Delete
                errNo = Err.Number
                Err.Clear
                On Error GoTo 0

                If errNo = 0 Then
                    delCount = delCount + 1
                Else
                    failCount = failCount + 1
                End If

            End If

        End If

ContinueLoop:
    Next i

End Sub

Private Function HasRefError(ByVal text As String) As Boolean

    If InStr(1, text, "#REF!", vbTextCompare) > 0 Then
        HasRefError = True
    ElseIf InStr(1, text, "#参照!", vbTextCompare) > 0 Then
        HasRefError = True
    Else
        HasRefError = False
    End If

End Function

Private Function GetNameText(ByVal nm As Name) As String

    On Error GoTo ErrHandler
    GetNameText = nm.Name
    Exit Function

ErrHandler:
    GetNameText = "[名前取得エラー] " & Err.Description

End Function

Private Function GetRefersToText(ByVal nm As Name) As String

    On Error GoTo ErrHandler
    GetRefersToText = nm.RefersTo
    Exit Function

ErrHandler:
    GetRefersToText = "[RefersTo取得エラー] " & Err.Description

End Function

Private Function GetRefersToLocalText(ByVal nm As Name) As String

    On Error GoTo ErrHandler
    GetRefersToLocalText = nm.RefersToLocal
    Exit Function

ErrHandler:
    GetRefersToLocalText = "[RefersToLocal取得エラー] " & Err.Description

End Function

Private Function IsProtectedExcelName(ByVal fullName As String) As Boolean

    Dim s As String

    s = LCase$(LocalNameOnly(fullName))

    If Left$(s, 6) = "_xlnm." Then
        s = Mid$(s, 7)
    End If

    Select Case s
        Case "print_area", _
             "print_titles", _
             "_filterdatabase", _
             "database", _
             "criteria", _
             "extract", _
             "auto_open", _
             "auto_close", _
             "consolidate_area"
            IsProtectedExcelName = True
        Case Else
            IsProtectedExcelName = False
    End Select

End Function

Private Function LocalNameOnly(ByVal fullName As String) As String

    Dim p As Long

    p = InStrRev(fullName, "!")

    If p > 0 Then
        LocalNameOnly = Mid$(fullName, p + 1)
    Else
        LocalNameOnly = fullName
    End If

    LocalNameOnly = Replace(LocalNameOnly, "'", "")

End Function

このコードでは、非表示の名前を無理に表示状態へ変更していません。

非表示の名前でも削除できるものは削除し、削除できないものは失敗件数としてカウントします。削除できなかった名前がある場合は、バックアップファイルで検証しながら個別に確認してください。

それでも解決しない場合の最終手段

ここまで実行しても「名前の定義が重複しています」が解消しない場合、#REF! ではない不要な名前が大量に残っている可能性があります。

ただし、次のコードは危険度が高いです。自分で設定した便利な名前、入力規則で使っている名前、数式で使っている名前、外部参照に関係する名前なども削除される可能性があります。

したがって、このコードは次のような場合に限って検討してください。

バックアップを取っている
対象ブックで名前の定義を意図的に使っていない
壊れても復元できる検証環境で試している
印刷範囲やフィルタ設定以外の名前を消してよいと判断できる

Sub DeleteNonProtectedNames()

    Dim wb As Workbook
    Dim nm As Name
    Dim i As Long
    Dim delCount As Long
    Dim keepCount As Long
    Dim failCount As Long

    Set wb = ActiveWorkbook

    If wb Is Nothing Then
        MsgBox "対象ブックが見つかりません。", vbExclamation
        Exit Sub
    End If

    If MsgBox("対象ブック: " & wb.Name & vbCrLf & vbCrLf & _
              "印刷範囲・フィルタ設定などを除き、" & vbCrLf & _
              "名前の定義を一括削除します。" & vbCrLf & vbCrLf & _
              "独自に設定した名前も削除される可能性があります。" & vbCrLf & _
              "必ずバックアップを取ったうえで実行してください。" & vbCrLf & vbCrLf & _
              "本当に実行しますか？", _
              vbYesNo + vbCritical) = vbNo Then
        Exit Sub
    End If

    For i = wb.Names.Count To 1 Step -1

        Set nm = wb.Names(i)

        If IsProtectedExcelName(nm.Name) Then
            keepCount = keepCount + 1
        Else
            On Error Resume Next
            nm.Delete

            If Err.Number = 0 Then
                delCount = delCount + 1
            Else
                failCount = failCount + 1
                Err.Clear
            End If

            On Error GoTo 0
        End If

    Next i

    MsgBox "処理が完了しました。" & vbCrLf & vbCrLf & _
           "削除した名前: " & delCount & " 個" & vbCrLf & _
           "保護して残した名前: " & keepCount & " 個" & vbCrLf & _
           "削除に失敗した名前: " & failCount & " 個", _
           vbInformation

End Sub


Private Function IsProtectedExcelName(ByVal nameText As String) As Boolean

    Dim s As String
    Dim p As Long

    s = LCase$(nameText)

    p = InStrRev(s, "!")
    If p > 0 Then
        s = Mid$(s, p + 1)
    End If

    Select Case s
        Case "_xlnm.print_area", _
             "_xlnm.print_titles", _
             "_xlnm._filterdatabase", _
             "print_area", _
             "print_titles", _
             "_filterdatabase", _
             "criteria", _
             "extract", _
             "database"

            IsProtectedExcelName = True

        Case Else
            IsProtectedExcelName = False

    End Select

End Function

この一括削除コードは、最初に実行するのではなく、まずは名前の管理で確認し、次に一覧化し、そのうえで #REF! の名前だけを削除する方法をおすすめします。

まとめ

Excelでシートコピー時に「名前の定義が重複しています」と何度も表示される場合、不要な名前の定義が大量に残っている可能性があります。

まずは、Ctrl+F3で「名前の管理」を開き、参照先が #REF! になっている名前を確認します。名前の管理で削除しても解決しない場合は、非表示の名前が残っている可能性があります。

おすすめ手順は次のとおりです。

名前の管理で確認する
#REF! の名前だけ削除する
それでも解決しない場合だけ、一括整理を検討する

名前の定義は、印刷範囲、フィルタ、数式、入力規則などに関係していることがあります。不要な名前を整理すれば、シートコピー時のエラーを減らせますが、削除前の確認とバックアップは必須です。

経理・財務・監査・コンサルティングなど、Excelファイルを日常的に扱う実務では、古いブックや引き継いだブックに不要な名前の定義が残っていることがあります。原因不明のエラーが続く場合は、非表示の名前も含めて確認してみてください。

参考情報

カテゴリー: Excel

タグ: VBAコードシートコピー名前の重複削除してはいけない名前の定義名前の定義エラー連発非表示の名前の定義

Excelで「名前の定義が重複しています」が何度も出る原因と対処法｜非表示の名前をVBAで整理する