Joel lưu ý rằng khi anh ấy đóng một sổ làm việc có hàng nghìn công thức trong đó, anh ấy gần đây nhận được thông báo này: “Một công thức trong trang tính này chứa một hoặc nhiều tham chiếu không hợp lệ.” Joel tự hỏi làm thế nào anh ta có thể biết trang tính nào trong số bảy trang tính trong sổ làm việc này đang được tham chiếu. Làm thế nào tôi có thể tìm thấy công thức sai? Tôi không quan sát thấy bất kỳ vấn đề nào trong việc hiển thị thông tin trên báo cáo của mình.

Việc theo dõi các tham chiếu không hợp lệ có thể gây khó chịu. Có một số nơi bạn có thể bắt đầu xem xét. Đầu tiên là trong các công thức có trên trang tính. (Có, bạn cần thực hiện các bước này cho mỗi trang tính trong sổ làm việc.) Sử dụng hộp thoại Đi đến Đặc biệt (nhấn F5 và chọn Đặc biệt) để chọn chỉ chuyển đến các ô có lỗi. Sau đó, bạn có thể sử dụng phím Tab để di chuyển giữa bất kỳ ô nào mà Excel chọn.

Bạn cũng có thể sử dụng công cụ Tìm để tìm các lỗi có thể xảy ra. Chỉ cần nhấn Ctrl + F để hiển thị tab Tìm của hộp thoại Tìm và Thay thế, sau đó tìm kiếm ký tự #. Đảm bảo rằng bạn yêu cầu Excel thực hiện tìm kiếm trong Công thức. Kiểm tra bất kỳ thứ gì được tìm thấy để xem liệu đó có phải là lỗi hay không.

Bạn cũng nên xem bất kỳ phạm vi được đặt tên nào được xác định trong sổ làm việc của bạn. Xem từng tên trong hộp thoại Xác định (Chèn | Tên | Xác định), đảm bảo rằng bất kỳ tên nào trong hộp Tham chiếu đến không bao gồm bất kỳ dấu hiệu lỗi nào.

Đây không phải là tất cả những nơi có thể có lỗi; Excel thực sự giỏi trong việc để lỗi tồn tại ở rất nhiều nơi. Nếu bạn cần tìm kiếm lỗi thường xuyên, bạn có thể thử một macro xem qua các công thức của bạn để tìm bất kỳ lỗi tiềm ẩn nào.

Sub CheckReferences()

Dim iSh As Integer   Dim sShName As String   Dim sht As Worksheet   Dim c As Cell   Dim 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

Set wks = Nothing   Set sht = Nothing   Set rng = Nothing

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

Macro này tạo một trang tính được gọi là “Tóm tắt” được sử dụng để liệt kê thông tin về bất kỳ lỗi nào được phát hiện trong các liên kết trang tính.









_Lưu ý: _






Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.






ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.






Mẹo này (8662) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: