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
Tìm và sửa các liên kết bị hỏng trong Excel
Để 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:
- 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.
- 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:
- 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:
- 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:
- 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):
- 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:
Xác định và sửa các liên kết bị hỏng bằng Tìm và Thay thế
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.
- 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.
- bên trong Tìm và thay thế hộp thoại, nhấp vào Tùy chọn cái nút.
- 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ó.
- 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.
- bên trong Nhìn vào hộp, chọn Công thức.
- 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.
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:
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:
- bên trong Tìm và thay thế hộp thoại, chuyển sang Thay thế chuyển hướng.
- 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.
- bên trong Thay bằng , nhập tên tệp hoặc đường dẫn chính xác.
- 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ì.
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.
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ế:
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.
Kiểm tra các liên kết bị hỏng bằng VBA
Đ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.
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.
Tìm các liên kết bị hỏng trong Excel bằng một cú nhấp chuột
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ó!
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.
The post Cách tìm và sửa các liên kết bị hỏng trong Excel appeared first on Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng | Kế Toán MVB.
source https://ketoanmvb.com/cach-tim-va-sua-cac-lien-ket-bi-hong-trong-excel.html
Không có nhận xét nào:
Đăng nhận xét