Cách tìm và sửa các liên kết bị hỏng trong Excel

Các liên kết không hoạt động có gây ra sự tàn phá cho trang tính của bạn không? Đừng lo! Hướng dẫn này sẽ dạy bạn 3 cách dễ dàng để tìm và sửa các liên kết Excel bị hỏng, cộng với giải pháp một cú nhấp chuột của riêng chúng tôi như một phần thưởng bổ sung 🙂

Các ô Excel thường có thể liên kết với các sổ làm việc khác để lấy thông tin liên quan từ đó. Khi sổ làm việc nguồn bị xóa, di dời hoặc bị hỏng, các tham chiếu bên ngoài đến tệp đó sẽ bị hỏng và công thức của bạn bắt đầu trả về lỗi. Rõ ràng, để sửa các công thức, bạn cần phải tìm các liên kết bị hỏng. Câu hỏi là làm thế nào? Câu trả lời theo bên dưới 🙂

Để phát hiện các liên kết không hoạt động đến các sổ làm việc khác, hãy thực hiện các bước sau:

  1. Trên Dữ liệu tab, trong Truy vấn & Kết nối nhóm, nhấp vào Chỉnh sửa liên kết cái nút.Nếu nút này chuyển sang màu xám, điều đó có nghĩa là không có tham chiếu bên ngoài nào trong sổ làm việc của bạn.

    Nhấp vào nút Chỉnh sửa liên kết.

  2. bên trong Chỉnh sửa liên kết hộp thoại, bạn có thể xem danh sách tất cả các nguồn bên ngoài mà sổ làm việc hiện tại của bạn đang tham chiếu. Khi cửa sổ này được mở lần đầu tiên, tất cả các liên kết đều có trạng thái không xác định. Để làm rõ trạng thái, hãy nhấp vào Kiểm tra trạng thái nút ở phía bên phải:Nhấp vào nút Kiểm tra Trạng thái để chẩn đoán các liên kết.
  3. Khi thông tin được cập nhật, hãy xem lại trạng thái của từng liên kết. Để thuận tiện, bạn có thể nhấp vào tiêu đề của Trạng thái cột để sắp xếp các bản ghi cho phù hợp.Rõ ràng, các liên kết được chẩn đoán là Lỗi: Không tìm thấy nguồn bị hỏng. Trong sổ làm việc của tôi, có hai liên kết như vậy:

    Tìm các liên kết bị hỏng trong Excel

  4. Hãy tiếp tục và sửa chữa các liên kết bị hỏng. Đối với điều này, hãy chọn nguồn báo cáo lỗi và nhấp vào Thay đổi nguồn nút bên phải:Sửa các liên kết bị hỏng trong Excel
  5. bên trong Thay đổi nguồn cửa sổ mở ra, điều hướng qua các thư mục trên máy tính của bạn cho đến khi bạn tìm thấy sổ làm việc chính xác, chọn nó và bấm OK (hoặc bấm đúp vào sổ làm việc):Chọn sổ làm việc nguồn chính xác.
  6. Lặp lại bước trên cho từng nguồn có vấn đề riêng lẻ. Khi hoàn tất, hãy nhấp vào Đóng cái nút.

Sau khi sửa chữa tất cả các nguồn sai, bạn có thể nhận thấy rằng danh sách các liên kết của bạn đã thực sự trở nên ngắn hơn. Lý do là bạn có thể đã có nhiều lần xuất hiện của cùng một sổ làm việc và sau khi thay đổi nguồn, những cái không chính xác đã biến mất khỏi danh sách.

Ví dụ: chúng tôi có các cặp sau tham chiếu đến cùng một tệp: Colrado report.xlsx (sai chính tả) và Colorado report.xlsx (chính xác); Florida_report.xlsx (không tồn tại) và Florida report.xlsx (chính xác). Sau khi sửa các liên kết, các nguồn không chính xác đã biến mất và bây giờ chúng tôi có danh sách này:

Các liên kết bị hỏng được sửa chữa.

Các Chỉnh sửa liên kết tính năng được thảo luận ở trên có thể giúp bạn nhanh chóng nhận được danh sách tất cả các nguồn bên ngoài trong sổ làm việc, nhưng nó không hiển thị ô nào chứa các tham chiếu bên ngoài đó. Để xác định các ô như vậy, bạn có thể sử dụng Tìm và thay thế.

Tìm các liên kết bị hỏng đến tất cả hoặc sổ làm việc cụ thể

Các liên kết bên ngoài luôn trỏ đến một tệp Excel khác có “.xl” như một phần của phần mở rộng tên tệp, chẳng hạn như .xls, .xlsx, .xlsm, v.v. Bạn có thể tận dụng thực tế này khi tìm kiếm tham chiếu đến bất kỳ sổ làm việc bên ngoài nào. Hoặc bạn có thể tìm kiếm văn bản cụ thể (chuỗi con) trong một tên sổ làm việc cụ thể. Các bước chi tiết theo sau.

  1. nhấn Ctrl + F để mở Tìm và thay thế hộp thoại. Hoặc bấm vào Tìm & Chọn > Tìm thấy… trên Trang Chủ tab trong Chỉnh sửa nhóm.
  2. bên trong Tìm và thay thế hộp thoại, nhấp vào Tùy chọn cái nút.
  3. Tùy thuộc vào việc bạn muốn tìm tất cả các liên kết bên ngoài trong sổ làm việc hay chỉ tham chiếu đến một tệp cụ thể, hãy nhập một trong các liên kết sau vào Tìm cái gì cái hộp:
    • Để tìm kiếm tất cả các liên kết, hãy nhập .xl.
    • Để tìm kiếm các liên kết đến một sổ làm việc cụ thể, hãy nhập tên sổ làm việc đó hoặc phần duy nhất của nó.
  4. bên trong Trong vòng hộp, chọn Sách bài tập để tìm kiếm trên tất cả các tab hoặc Tấm để xem trang tính hiện tại.
  5. bên trong Nhìn vào hộp, chọn Công thức.
  6. Nhấn vào Tìm tất cả cái nút.

Và bây giờ là phần quan trọng – phân tích kết quả.

Nếu bạn đã tìm kiếm tất cả các tài liệu tham khảo bên ngoài, sau đó nhấp vào tiêu đề của Giá trị để sắp xếp kết quả theo giá trị và cuộn xuống danh sách. Các liên kết bị hỏng đến các sổ làm việc khác sẽ có lỗi #REF! lỗi trong Giá trị cột.

Tìm kiếm ô chứa liên kết bị hỏng

Nếu bạn đã tìm kiếm các tham chiếu đến một sổ làm việc cụ thể, chỉ cần xem lại kết quả.

Ví dụ: chúng tôi biết rằng một số ô trong báo cáo tóm tắt trỏ đến tệp không tồn tại (Colrado report.xlsx) và chúng tôi muốn biết chính xác những ô nào. Vì vậy, chúng tôi gõ từ sai chính tả colrado bên trong Tìm cái gì hộp và đánh Tìm tất cả. Kết quả là Excel sẽ hiển thị 3 tham chiếu như vậy trong 2 trang tính khác nhau. #REF! lỗi trong Giá trị xác nhận rằng cả ba liên kết đều bị hỏng:

Tìm các liên kết bị hỏng đến một tệp cụ thể.

Ghi chú. Phương pháp chẩn đoán các liên kết bị hỏng này không đáng tin cậy 100%. Ví dụ: lỗi #REF! lỗi có thể được trả về bởi một công thức trong ô được liên kết trong sổ làm việc nguồn và nó cũng sẽ được hiển thị trong Tìm tất cả các kết quả. Tuy nhiên, điều đó không có nghĩa là tham chiếu đến sổ làm việc khác đó bị hỏng. Vì vậy, khi sử dụng cách tiếp cận này, có lý do để nhấp vào từng lỗi và kiểm tra lại các tham chiếu theo cách thủ công.

Sửa các liên kết bị hỏng đến một sổ làm việc cụ thể

Trong danh sách của Tìm tất cả kết quả, bạn có thể nhấp vào bất kỳ mục nào để điều hướng đến ô chứa liên kết và chỉnh sửa từng mục riêng lẻ. Hoặc bạn có thể sử dụng Thay thế Tất cả tính năng sửa tất cả các lần xuất hiện của một liên kết không hợp lệ cùng một lúc. Đây là cách thực hiện:

  1. bên trong Tìm và thay thế hộp thoại, chuyển sang Thay thế chuyển hướng.
  2. bên trong Tìm cái gì , nhập tên tệp hoặc đường dẫn không chính xác.
  3. bên trong Thay bằng , nhập tên tệp hoặc đường dẫn chính xác.
  4. Nhấp chuột Thay thế tất cả.

Ghi chú. Sau khi nhấp vào Thay thế tất cả nút, Cập nhật giá trị cửa sổ có thể mở ra nhắc bạn chọn sổ làm việc nguồn. Đừng làm điều đó và chỉ cần nhấp vào Huỷ bỏ mà không cần chọn bất cứ thứ gì.

Hủy cập nhật giá trị.

Ví dụ, hãy thay thế một tên sổ làm việc sai Colrado report.xlsx với một trong những bên phải Colorado report.xlsx. Trong trường hợp cụ thể này, chỉ thay thế một từ duy nhất (colrado) cũng sẽ hoạt động. Tuy nhiên, hãy nhớ rằng một văn bản được chỉ định sẽ được thay thế ở bất kỳ đâu trong chuỗi đường dẫn (đường dẫn đầy đủ đến tệp được hiển thị nếu sổ làm việc nguồn hiện đang đóng). Vì vậy, bạn nhập đoạn văn bản càng nhỏ thì khả năng mắc lỗi càng lớn.

Thay thế các liên kết bị hỏng trong Excel

Sau khi thay thế xong, lỗi #REF! lỗi biến mất khỏi kết quả và các giá trị bình thường được hiển thị thay thế:

Tất cả các liên kết bị hỏng được thay thế bằng những liên kết chính xác.

Theo cách tương tự, bạn có thể thay thế đường dẫn đến một tệp nguồn. Ví dụ: nếu sổ làm việc nguồn ban đầu nằm trong Các tài liệu và sau đó bạn chuyển nó vào Báo cáo thư mục con trong cùng một thư mục, bạn có thể thay thế Các tài liệu với Tài liệu Báo cáo .

Ai đó có thể nói rằng thật nực cười khi sử dụng Tìm và thay thế để giải quyết vấn đề liên kết bị hỏng, nhưng theo tôi biết, đây là tính năng có sẵn duy nhất có thể giúp bạn tìm các ô chứa liên kết bị hỏng.

Đoạn mã dưới đây lặp lại mọi tham chiếu bên ngoài trong sổ làm việc và cố gắng tìm hiểu xem nó có bị hỏng hay không. Để tìm các tệp bên ngoài, chúng tôi sử dụng LinkSources phương pháp. Để xác định các liên kết bị hỏng, LinkInfo phương pháp được sử dụng.

Sub FindBrokenLinks()
    linksDataArray = ActiveWorkbook.LinkSources(xlExcelLinks)
    Dim reportHeaders() As String
    Dim rangeCur As Range
    Dim sheetCur As Worksheet
    Dim rowNo As Integer
    Dim linkFilePath, linkFilePath2, linkFileName As String
    Dim linksStatusDescr As String  'https://docs.microsoft.com/en-us/office/vba/api/excel.xllinkstatus
    Dim sheetReportName As String

    sheetReportName = "Broken Links report"
    linksStatusDescr = "File missing"
    reportHeaders = Split("Worksheet, Cell, Formula, Workbook, Link Status", ", ")
    rowNo = 1 'Header row

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    If Evaluate("ISREF('" & sheetReportName & "'!A1)") Then
        ActiveWorkbook.Worksheets(sheetReportName).Cells.Clear
    Else
        Sheets.Add.Name = sheetReportName
    End If
    Set sheetReport = ActiveWorkbook.Worksheets(sheetReportName)

    For indI = 0 To UBound(reportHeaders)
        sheetReport.Cells(rowNo, indI + 1) = reportHeaders(indI)
    Next

    For Each sheetCur In ActiveWorkbook.Worksheets
        If sheetCur.Name <> sheetReport.Name Then
            For Each rangeCur In sheetCur.UsedRange
                If rangeCur.HasFormula Then
                    For indI = LBound(linksDataArray) To UBound(linksDataArray)
                        linkFilePath = linksDataArray(indI)   'LinkSrouces returns the full file path with the file name
                        linkFileName = Right(linkFilePath, Len(linkFilePath) - InStrRev(linkFilePath, ""))   'extract only the file name
                        linkFilePath2 = Left(linksDataArray(indI), InStrRev(linksDataArray(indI), "")) & "[" & linkFileName & "]"  'the file path with the workbook name in square brackets
                        linksStatusCode = ActiveWorkbook.LinkInfo( CStr(linkFilePath), xlLinkInfoStatus)

                        If xlLinkStatusMissingFile = linksStatusCode And (InStr(rangeCur.Formula, linkFilePath) Or InStr(rangeCur.Formula, linkFilePath2)) Then
                            rowNo = rowNo + 1
                            With sheetReport
                                .Cells(rowNo, 1) = sheetCur.Name
                                .Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
                                .Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
                                .Cells(rowNo, 3) = "'" & rangeCur.Formula
                                .Cells(rowNo, 4) = linkFilePath
                                .Cells(rowNo, 5) = linksStatusDescr
                            End With
                            Exit For
                        End If
                    Next indI

                    For Each namedrangeCur In Names
                       If InStr(rangeCur.Formula, namedrangeCur.Name) Then
                            linkFilePath = ""
                            linksStatusCode = -1

                            If 0 < InStr(namedrangeCur.RefersTo, "[") Then
                                linkFilePath = Replace( Split( Right(namedrangeCur.RefersTo, Len(namedrangeCur.RefersTo) - 2), "]")(0), "[", "")
                                linksStatusCode = ActiveWorkbook.LinkInfo( CStr(linkFilePath), xlLinkInfoStatus)
                            End If
                            If xlLinkStatusMissingFile = linksStatusCode Then
                                rowNo = rowNo + 1
                               With sheetReport
                                   .Cells(rowNo, 1) = sheetCur.Name
                                   .Cells(rowNo, 2) = Replace(rangeCur.Address, "$", "")
                                   .Hyperlinks.Add Anchor:=.Cells(rowNo, 2), Address:="", SubAddress:="'" & sheetCur.Name & "'!" & rangeCur.Address
                                   .Cells(rowNo, 3) = "'" & rangeCur.Formula
                                   .Cells(rowNo, 4) = linkFilePath
                                   If 0 < Len(linkFilePath) Then
                                    .Cells(rowNo, 5) = linksStatusDescr
                                   End If
                               End With
                            End If
                            Exit For
                        End If
                    Next namedrangeCur
                End If
            Next rangeCur
        End If
    Next
    Columns("A:E").EntireColumn.AutoFit

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Danh sách các liên kết không hợp lệ được xuất ra trong một trang tính mới có tên Báo cáo liên kết bị hỏng. Cột B có một siêu liên kết đến ô chứa liên kết.

Tìm các liên kết bị hỏng với VBA

Bạn có thể chèn mã trong sổ làm việc của riêng bạn hoặc tải xuống tệp mẫu của chúng tôi với macro cũng như hướng dẫn từng bước về cách sử dụng nó.

Ghi chú. Mã này chỉ tìm thấy các liên kết đến các sổ làm việc không hợp lệ (không tồn tại, đã di chuyển hoặc đã xóa), nhưng không tìm thấy các trang tính thiếu. Lý do là LinkInfo chỉ kiểm tra tên tệp. Cố gắng kiểm tra tên trang tính dẫn đến Lỗi 2015.

Trong khi đọc qua phần đầu tiên của hướng dẫn này, bạn có thể cảm thấy hơi nản vì không có cách đơn giản nào để tìm tất cả các liên kết bị hỏng trong một tệp, chẳng hạn bằng cách nhấp vào một nút. Mặc dù một giải pháp như vậy không tồn tại trong Excel, nhưng không có gì ngăn cản chúng tôi tự phát triển nó 🙂

Đối với người dùng của chúng tôi Suite Ultimate, chúng tôi cung cấp công cụ một cú nhấp chuột để tìm tất cả các tham chiếu bên ngoài trong sổ làm việc hoặc chỉ các liên kết bị hỏng. Chỉ cần nhấp vào Tìm liên kết? nút trên Công cụ Ablebits và ngay lập tức bạn sẽ thấy danh sách tất cả các liên kết trong sổ làm việc hiện tại, trong đó những liên kết không hợp lệ được đánh dấu bằng màu đỏ nhạt. Để giới hạn danh sách các liên kết không hoạt động, hãy chọn Chỉ liên kết bị hỏng hộp kiểm.

Nhấp vào địa chỉ ô trên ngăn của phần bổ trợ sẽ đưa bạn đến ô chứa một liên kết cụ thể. Thats tất cả để có nó!

Tìm các liên kết bị hỏng trong Excel bằng một cú nhấp chuột

Không giống như mã VBA ở trên, phần bổ trợ tìm thấy tất cả các loại liên kết bị hỏng kể cả những trường hợp thiếu hoặc nhập sai trang tính.

Source link

Trả lời