پیگیری مراجع نامعتبر در اکسل

Question

جوئل خاطرنشان کرد که وقتی کتاب کاری را می بندد که هزاران فرمول در آن وجود دارد، اخیراً این پیام را دریافت می کند: "یک فرمول در این کاربرگ حاوی یک یا چند مرجع نامعتبر است." جوئل تعجب می کند که چگونه می تواند بداند به کدام یک از هفت کاربرگ موجود در این کتاب کار اشاره شده است. چگونه می توانم فرمول اشتباه را پیدا کنم؟ من هیچ مشکلی در نمایش اطلاعات گزارش های خود مشاهده نمی کنم.

ردیابی مراجع نامعتبر می تواند خسته کننده باشد. چندین مکان وجود دارد که می توانید شروع به جستجو کنید. اولین مورد در فرمول هایی است که در کاربرگ ها وجود دارد. (بله، باید این مراحل را برای هر کاربرگ در کتاب کار انجام دهید). سپس می توانید از کلید Tab برای حرکت در میان سلول هایی که اکسل انتخاب می کند استفاده کنید.

همچنین می توانید از ابزار Find برای جستجوی خطاهای احتمالی استفاده کنید. فقط Ctrl+F را فشار دهید تا تب Find در کادر محاوره ای Find and Replace نمایش داده شود، سپس کاراکتر # را جستجو کنید. مطمئن شوید که به اکسل می‌گویید که جستجوی خود را در فرمول‌ها انجام دهد. هر چیزی را که پیدا شد بررسی کنید تا ببینید آیا خطا است یا خیر.

همچنین باید به هر محدوده نامگذاری شده در کتاب کار خود نگاهی بیندازید. به هر نام در کادر محاوره‌ای Name Manager نگاه کنید (برگه فرمول‌ها، روی ابزار Name Manager کلیک کنید)، مطمئن شوید که هر چیزی که در ستون Refers To است شامل هیچ نشانه‌ای از خطا نباشد.

اینها همه جاهایی نیستند که ممکن است خطا وجود داشته باشد. اکسل واقعاً خوب است که اجازه می دهد خطاها در بسیاری از مکان ها وجود داشته باشند. اگر نیاز دارید که اغلب خطاها را جستجو کنید، ممکن است یک ماکرو را امتحان کنید که از طریق فرمول های شما برای هر گونه خطای احتمالی نگاه می کند.

Sub CheckReferences()
 Check for possible missing or erroneous links in
 formulas and list possible errors in a summary sheet

  Dim iSh As Integer
  Dim sShName As String
  Dim sht As Worksheet
  Dim c, sChar As String
  Dim rng As Range
  Dim i As Integer, j As Integer
  Dim wks As Worksheet
  Dim sChr As String, addr As String
  Dim sFormula As String, scVal As String
  Dim lNewRow As Long
  Dim vHeaders

  vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")
  check if Summary worksheet is in workbook
  and if so, delete it
  With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
  End With

  For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Summary" Then
      Worksheets(i).Delete
    End If
  Next i

  iSh = Worksheets.Count

  create a new summary sheet
    Sheets.Add After:=Sheets(iSh)
    Sheets(Sheets.Count).Name = "Summary"
  With Sheets("Summary")
    Range("A1:D1") = vHeaders
  End With
  lNewRow = 2

   this will not work if the sheet is protected,
   assume that sheet should not be changed; so ignore it
  On Error Resume Next

  For i = 1 To iSh
    sShName = Worksheets(i).Name
    Application.Goto Sheets(sShName).Cells(1, 1)
    Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)

    For Each c In rng
      addr = c.Address
      sFormula = c.Formula
      scVal = c.Text

      For j = 1 To Len(c.Formula)
        sChr = Mid(c.Formula, j, 1)

        If sChr = "[" Or sChr = "!" Or _
          IsError(c) Then
          write values to summary sheet
          With Sheets("Summary")
            .Cells(lNewRow, 1) = sShName
            .Cells(lNewRow, 2) = addr
            .Cells(lNewRow, 3) = scVal
            .Cells(lNewRow, 4) = "" & sFormula
          End With
          lNewRow = lNewRow + 1
          Exit For
        End If
      Next j
    Next c
  Next i

 housekeeping
  With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
  End With

 tidy up
  Sheets("Summary").Select
  Columns("A:D").EntireColumn.AutoFit
  Range("A1:D1").Font.Bold = True
  Range("A2").Select
End Sub

این ماکرو یک کاربرگ به نام "Summary" ایجاد می کند که برای فهرست کردن اطلاعات مربوط به هر گونه خطای شناسایی شده در پیوندهای کاربرگ استفاده می شود.

همچنین می توانید از برنامه Excel MVP Bill Manvilles FindLink استفاده کنید که کار شگفت انگیزی در مکان یابی اطلاعات در پیوندها انجام می دهد. می توانید از افزونه برای جستجوی کاراکتر # در همه پیوندهای خود استفاده کنید، که به شما کمک می کند تا خطاها را پیدا کنید. مشخص نیست که آیا این افزونه با اکسل 2013 کار می کند، زیرا آخرین به روز رسانی فایل (تا لحظه نگارش این مقاله) در سال 2011 بود. اطلاعات بیشتر در مورد FindLink را می توانید در اینجا بیابید:

http://www.manville.org.uk/software/findlink.htm