Trong môi trường làm việc hiện đại, Microsoft Excel vẫn là công cụ mạnh mẽ nhất để quản lý dữ liệu, tạo báo cáo và thực hiện phân tích. Khi bạn làm việc với các workbook lớn, có thể chứa hàng nghìn dòng dữ liệu, rất có thể bạn sẽ gặp phải vấn đề liên quan đến liên kết (links) giữa các workbook, thư mục, hoặc thậm chí là các liên kết tới các trang web. Khi một liên kết không còn tồn tại (broken link) — vì file đã bị di chuyển, đổi tên, hoặc xóa —iversaire, Excel sẽ báo lỗi hoặc hiển thị giá trị trống. Điều này không chỉ làm giảm tính chính xác của dữ liệu mà còn gây khó khăn trong việc bảo trì và chia sẻ file.
Trong bài viết này, chúng ta sẽ đi sâu vào các nguyên nhân gây ra liên kết không tồn tại, cách phát hiện và loại bỏ chúng bằng các phương pháp đơn giản (sử dụng tính năng “Replace Link”), công cụ “Edit Links”, công thức dựng sẵn, và còn tới các giải pháp nâng cao như viết macro VBA để tự động hóa quá trình. Mục tiêu của chúng ta là giúp bạn có thể đảm bảo workbook của mình luôn sạch sẽ, chính xác và đơn giản hơn khi chia sẻ cho đồng nghiệp hoặc khách hàng.
Nếu INDIRECT không thể gọi được, kết quả sẽ là “Broken”.
2.3. Kiểm Tra Thông Qua Macro VBA
Macro dưới đây sẽ liệt kê tất cả cácfeld bên trong workbook và kiểm tra xem chúng có chứa tham chiếu tới file khác hay không:
Sub FindBrokenLinks() Dim ws As Worksheet Dim aCell As Range Dim strFormula As String erfolgreich = 0 For Each ws In ThisWorkbook.Worksheets For Each aCell In ws.UsedRange If aCell.HasFormula Then strFormula = aCell.Formula If InStr(strFormula, "") > 0 Then On Error Resume Next Dim refValue As Variant refValue = Evaluate(strFormula) If Err.Number <> 0 Then Debug.Print ws.Name & "!" & aCell.Address & " : " & strFormula Err.Clear End If On Error GoTo 0 End If End If Next aCell Next ws
End Sub
Macro này sẽ in ra danh sách các ô có liên kết lỗi vào cửa sổ Immediate (Ctrl+G).
3. Cách Loại Bỏ Liên Kết Không Tồn Tại
3.1. Thay Đổi Liên Kết Bằng “Replace Link”
Mở Edit Links.
Chọn liên kết bạn muốn thay thế.
Nhấn Change Source.
Chọn file mới hoặc nhập đường dẫn mới.
Nếu muốn loại bỏ hoàn toàn, chọn Change Source rồi đánh dấu vào ô Remove Links (tùy chọn).
Lưu ý: Khi chọn “Remove Links”, Excel sẽ chuyển tất cả công thức tham chiếu tới file đó thành giá trị hiện tại (tức là “hard-coded”).
Gỡ Bỏ Những Liên Kết Không Tồn Tại Trong Excel
Có thể bạn quan tâm: Gộp Nhiều Sheet Trong 1 File Excel Vào 1 Sheet
3.2. Đổi Công Thức Trực Tiếp
Tìm kiếm toàn bộ workbook bằng Ctrl+F (tìm “”).
Thay thế bằng giá trị cố định hoặc công thức mới.
Ví dụ:
Cũ: =SUM(Sales.xlsxSheet1!B1:B10)
Mới: =SUM(Sheet1!B1:B10) (sử dụng dữ liệu trong workbook hiện tại).
3.3. Dùng Hàm INDIRECT Để Tránh Liên Kết
Nếu bạn muốn giữ công thức nhưng tránh lỗi khi file nguồn bị di chuyển, hãy dùng INDIRECT:
=SUM(INDIRECT("'Sales.xlsxSheet1'!B1:B10"))
Tuy nhiên, INDIRECTkhông hoạt động với file ngoài workbook. Bạn cần mở file nguồn trước khi sử dụng.
3.4. Xóa Tất Cả Liên Kết Bằng Macro
Macro dưới đây sẽ loại bỏ tất cả các liên kết ngoài workbook:
Sub RemoveAllExternalLinks() Dim lnk As WorkbookLink For Each lnk In ThisWorkbook.LinkSources(xlExcelLinks) ThisWorkbook.BreakLink Name:=lnk, Type:=xlLinkTypeExcelLinks Next lnk
End Sub
Cảnh báo: Macro này sẽ chuyển toàn bộ công thức tham chiếu thành giá trị hiện tại.
4. Thực Hành: Kịch Bản Đặc Thù
4.1. Loại Bỏ Liên Kết Trong Power Query
Mở Power Query Editor.
Tìm các bước Source chứa đường dẫn tới file.
Thay đổi đường dẫn hoặc chuyển dữ liệu sang nguồn mới.
Áp dụng thay đổi và tải dữ liệu trở lại workbook.
4.2. Xóa Liên Kết Trong Power Pivot
Vào Power Pivot > Manage.
Chọn Diagram View.
Kiểm tra các میرے Relationships có tham chiếu tới external tables.
Nếu có, hãy xóa hoặc thay đổi nguồn dữ liệu.
4.3. Thay Đổi Liên Kết Trên Các Hàm WEBSERVICE
Nếu WEBSERVICE đang chỉ tới một URL cũ:
=WEBSERVICE("https://oldapi.example.com/data")
Thay đổi thành URL mới hoặc loại bỏ hoàn toàn:
=IFERROR(WEBSERVICE("https://newapi.example.com/data"), "Không thể lấy dữ liệu")
5. Kiểm Tra Định Kỳ: Tạo Báo Cáo Liên Kết
Dưới đây là một macro viết để tạo bảng báo cáo liên kết:
Sub ReportLinks() Dim wsReport As Worksheet Set wsReport = ThisWorkbook.Worksheets.Add wsReport.Name = "LinkReport" wsReport.Cells(1, 1).Value = "File Source" wsReport.Cells(1, 2).Value = "Status" Dim i As Long i = 2 Dim lk As Variant For Each lk In ThisWorkbook.LinkSources(xlExcelLinks) wsReport.Cells(i, 1).Value = lk wsReport.Cells(i, 2).Value = IIf(LookupLinkStatus(lk), "OK", "Broken") i = i + 1 Next lk
End Sub Function LookupLinkStatus(sLink As String) As Boolean On Error Resume Next Dim tempVal As Variant tempVal = Application.WorksheetFunction.GetPivotData(sLink) 'Just test LookupLinkStatus = (Err.Number = 0) Err.Clear
End Function
Macro này tạo một sheet mới “LinkReport” liệt kê tất cả các nguồn link và trạng thái.
6. Phòng Ngừa: Giữ Workbook Không Có Liên Kết Không Tồn Tại
Sử dụng tính năng Enable Editing: Khi mở file được chia sẻ, hãyVariation Enable Editing để tránh Excel tự động cập nhật liên kết.
Đặt đường dẫn tuyệt đối thay vì tương đối: Khi có thể, tránh dùng đường dẫn tương đối vì di chuyển thư mục sẽ làm lỗi.
Sử miniature: Khi chia sẻ workbook, hãy Convert to Power Query hoặc Copy & Paste giá trị thay vì giữ công thức liên kết.
Định kỳ chạy macro kiểm tra: Đặt macro ReportLinks vào sự kiện Workbook_Open để báo cáo ngay khi mở file.
Sử dụng Data Validation: Tránh nhập đường dẫn sai trong các ô dữ liệu nhập.
7. Các Công Cụ Bên Ngoài Excel
Kutools for Excel: Cung cấp tính năng “Remove Links” và “Edit Links” nâng cao.
Power Automate: Tạo luồng làm sạch dữ liệu tự động, bao gồm việc kiểm tra và thay thế liên kết.
Python + openpyxl / pandas: Viết script để quét và sửa các liên kết khi xử lý dữ liệu lớn.
8. Kết Luận
Liên kết không tồn tại trong Excel là một vấn đề thường gặp nhưng có thể được giải quyết nhanh chóng nếu bạn biết cách phát hiện và loại bỏ chúng. Dưới đây là tóm tắt các bước quan trọng:
Phát hiện: Sử dụng Edit Links, công thức INDIRECT hoặc macro VBA.
Loại bỏ: Dùng Replace Link, BreakLink, hoặc chỉnh sửa công thức trực tiếp.
Phòng ngừa: Kiểm tra định kỳ, sử dụng macro báo cáo, và tránh destiné đường dẫn tương đối.
Bằng cách áp dụng những chiến lược trên, bạn sẽ có được một workbook đầy đủ chuẩn xác và đơn giản hơn trong việc chia sẻ. Hãy thử nghiệm các macro và công cụ bạn thấy phù hợp với quy trình làm việc của mình, và đừng quên chia sẻ những kinh nghiệm, mẹo của bạn với cộng đồng.
Chúc bạn thành công trong việc gỡ bỏ các liên kết không tồn tại trong Excel và nâng cao hiệu quả công việc!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.
⚠️
LƯU Ý QUAN TRỌNG:Miễn trừ trách nhiệm: Mặc dù chúng tôi luôn nỗ lực để đảm bảo tính chính xác và cập nhật của thông tin, nhưng các quy định về giáo dục và tuyển sinh có thể thay đổi theo thời gian. BigSchool không chịu trách nhiệm về bất kỳ sai sót nào hoặc kết quả phát sinh từ việc sử dụng thông tin trên trang này. Độc giả nên liên hệ trực tiếp với nhà trường hoặc cơ quan chức năng để có thông tin mới nhất. Ngoài ra, BigSchool tuyệt đối không bán sản phẩm, khóa học hay cung cấp dịch vụ được nhắc đến trong bài.
