خلاص شدن از شر نام های محدوده استفاده نشده در اکسل

Question

تری یک کتاب کار دارد که محدوده‌های نام‌گذاری زیادی دارد. او نمی‌پرسد آیا راهی برای تعیین اینکه کدام محدوده‌های نام‌گذاری شده واقعاً در فرمول‌ها استفاده می‌شوند وجود دارد تا بتواند نام‌هایی را که استفاده نمی‌شوند حذف کند.

چند راه مختلف وجود دارد که می توان این کار را انجام داد. یکی از راه‌های آسان این است که Name Manager را فعال کنید ( Ctrl+F3 را فشار دهید ) و نام تمام نام‌های محدوده‌ای را که می‌خواهید بررسی کنید یادداشت کنید. Name Manager را ببندید و مراحل زیر را دنبال کنید:

1. Ctrl+F را فشار دهید . اکسل تب Find از کادر محاوره ای Find and Replace را نمایش می دهد.
2. در کادر Find What، نام اولین نام محدوده ای را که می خواهید بررسی کنید وارد کنید.
3. از لیست کشویی Within استفاده کنید تا مشخص کنید می خواهید در Workbook جستجو کنید.
4. از لیست کشویی Look In استفاده کنید تا مشخص کنید می خواهید در فرمول ها جستجو کنید.
5. روی Find All کلیک کنید.

در این مرحله، اکسل تمام سلول هایی را که در آنها نام محدوده ای را که در مرحله 2 مشخص کرده اید پیدا کرده است، نمایش می دهد. اگر چیزی نمایش داده نشد، نام در هیچ فرمولی استفاده نمی شود. سپس می‌توانید مراحل را برای هر یک از نام‌های محدوده دیگری که می‌خواهید بررسی کنید، تکرار کنید.

می‌توانید با قرار دادن فرآیند یافتن در یک ماکرو، فرآیند را خودکارتر کنید. نمونه زیر است:

Sub CleanseNames()
    Dim ns As Names
    Dim n As Name

    Set ns = ThisWorkbook.Names
    MsgBox "There are " & ns.Count & " names in the workbook", vbInformation
    For Each n In ns
        On Error GoTo err
        fnd = False
        fnd = Cells.Find(What:=n.Name, LookIn:=xlFormulas, _
          LookAt:=xlPart).Activate
err:
        If fnd = False Then n.Delete
    Next n
End Sub

ماکرو از هر نام محدوده در کتاب کار عبور می کند و سپس آن نام را در هر سلولی جستجو می کند. اگر پیدا نشد، نام حذف می شود.

البته ممکن است حذف خودکار نام ها به نفع شما نباشد. پس از همه، آنها می توانند در مکان های بیشتری از فرمول ها باشند. شاید بهتر باشد به سادگی نام هایی را که ظاهراً مورد استفاده نیستند فهرست کنید. در اینجا یک مثال از یک ماکرو است که کار را انجام می دهد:

Sub ListUnusedNames()
    Dim w As Worksheet
    Dim n As Name
    Dim sUnusedNames As String
    Dim arrUsedNames() As String
    Dim K As Integer

    On Error Resume Next
    K = -1
    For Each n In Names    First Loop
        For Each w In ActiveWorkbook.Worksheets
            If Not w.Cells.Find(What:=n.Name, _
                After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, _
                SearchFormat:=False) Is Nothing Then

                K = K + 1
                ReDim Preserve arrUsedNames(K)
                arrUsedNames(K) = n.Name
                 Jump out early; found an occurrence
                GoTo LabNextName
            End If
        Next w
LabNextName:
    Next n

    sUnusedNames = ""
    For Each n In Names    Second Loop
        If Not IsPartOfArray(n.Name, arrUsedNames) Then
            sUnusedNames = sUnusedNames & n.Name & vbCr
        End If
    Next n

    If Len(sUnusedNames) = 0 Then
        MsgBox "There are no names in the active workbook that are unused."
    Else
        MsgBox "Unused Names:" & vbCr & vbCr & sUnusedNames
    End If
End Sub

Function IsPartOfArray(stringToBeFound As String, arr As Variant) As Boolean
    Returns TRUE If argument stringToBeFound is part of array arr.

    IsPartOfArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

اگر ترجیح می‌دهید از یک راه‌حل شخص ثالث برای مدیریت نام‌ها در کتاب کار خود استفاده کنید - از جمله اینکه بدانید کدام نام‌ها استفاده نشده‌اند - یک انتخاب عالی افزونه Name Manager است که توسط Jan Karel Pieterse نوشته شده است. اطلاعات بیشتر در مورد این افزونه رایگان را می توانید در اینجا بیابید:

https://jkp-ads.com/excel-name-manager.asp