Nội dung
Giao tiếp là một tính năng rất hữu ích trong Excel. Rốt cuộc, người dùng rất thường xuyên phải sử dụng thông tin từ các tệp khác. Nhưng trong một số tình huống, chúng có thể gây hại nhiều hơn lợi. Rốt cuộc, ví dụ, nếu bạn gửi các tệp này qua thư, các liên kết không hoạt động. Hôm nay chúng tôi sẽ nói chi tiết hơn về những gì cần làm để tránh một vấn đề như vậy.
Mối quan hệ trong Excel là gì
Các mối quan hệ trong Excel rất thường được sử dụng cùng với các hàm như VPRđể lấy thông tin từ sổ làm việc khác. Nó có thể ở dạng một liên kết đặc biệt chứa địa chỉ của không chỉ ô mà còn của cuốn sách chứa dữ liệu. Kết quả là, một liên kết như vậy trông giống như sau: = VLOOKUP (A2; '[Doanh số 2018.xlsx] Báo cáo'! $ A: $ F; 4; 0). Hoặc, để trình bày đơn giản hơn, hãy biểu diễn địa chỉ trong biểu mẫu sau: = '[Doanh số 2018.xlsx] Báo cáo'! $ A1. Hãy phân tích từng phần tử liên kết của loại này:
- [Bán hàng 2018.xlsx]. Đoạn này chứa một liên kết đến tệp mà bạn muốn lấy thông tin. Nó còn được gọi là nguồn.
- Hình ảnh. Chúng tôi đã sử dụng tên sau đây, nhưng đây không phải là tên nên có. Khối này chứa tên của trang tính mà bạn cần tìm thông tin.
- $ A: $ F и $ A1 - địa chỉ của ô hoặc dải ô chứa dữ liệu được chứa trong tài liệu này.
Trên thực tế, quá trình tạo liên kết đến một tài liệu bên ngoài được gọi là liên kết. Sau khi chúng tôi đã đăng ký địa chỉ của ô trong một tệp khác, nội dung của tab "Dữ liệu" sẽ thay đổi. Cụ thể, nút “Thay đổi kết nối” sẽ hoạt động, với sự trợ giúp của người dùng có thể chỉnh sửa các kết nối hiện có.
Thực chất của vấn đề
Theo quy định, không có thêm khó khăn nào phát sinh để sử dụng các liên kết. Ngay cả khi một tình huống phát sinh trong đó các ô thay đổi, thì tất cả các liên kết sẽ tự động được cập nhật. Nhưng nếu bạn đã đổi tên chính sổ làm việc hoặc di chuyển nó đến một địa chỉ khác, Excel sẽ trở nên bất lực. Do đó, nó tạo ra thông báo sau.
Ở đây, người dùng có hai tùy chọn khả thi về cách hành động trong tình huống này. Anh ta có thể nhấp vào “Tiếp tục” và sau đó các thay đổi sẽ không được cập nhật hoặc anh ta có thể nhấp vào nút “Thay đổi liên kết”, anh ta có thể cập nhật chúng theo cách thủ công. Sau khi chúng tôi nhấp vào nút này, một cửa sổ bổ sung sẽ xuất hiện trong đó có thể thay đổi các liên kết, cho biết tệp chính xác hiện đang nằm ở đâu và nó được gọi là gì.
Ngoài ra, bạn có thể chỉnh sửa các liên kết thông qua nút tương ứng trên tab "Dữ liệu". Người dùng cũng có thể phát hiện ra rằng kết nối bị hỏng do lỗi #LINK, lỗi này xuất hiện khi Excel không thể truy cập thông tin nằm tại một địa chỉ cụ thể do bản thân địa chỉ đó không hợp lệ.
Cách hủy liên kết trong excel
Một trong những phương pháp đơn giản nhất để giải quyết tình huống được mô tả ở trên trong trường hợp bạn không thể tự cập nhật vị trí của tệp được liên kết là xóa chính liên kết đó. Điều này đặc biệt dễ thực hiện nếu tài liệu chỉ chứa một liên kết. Để thực hiện việc này, bạn phải thực hiện theo trình tự các bước sau:
- Mở menu "Dữ liệu".
- Chúng tôi tìm thấy phần “Kết nối” và ở đó - tùy chọn “Thay đổi kết nối”.
- Sau đó, nhấp vào “Hủy liên kết”.
Nếu bạn có ý định gửi cuốn sách này cho người khác qua thư, bạn nên gửi trước. Rốt cuộc, sau khi xóa các liên kết, tất cả các giá trị có trong một tài liệu khác sẽ được tự động tải vào tệp, được sử dụng trong các công thức và thay vì địa chỉ ô, thông tin trong các ô tương ứng sẽ đơn giản được chuyển thành giá trị. .
Cách hủy liên kết tất cả các sách
Nhưng nếu số lượng liên kết trở nên quá lớn, việc xóa chúng theo cách thủ công có thể mất nhiều thời gian. Để giải quyết vấn đề này ngay lập tức, bạn có thể sử dụng một macro đặc biệt. Nó nằm trong addon VBA-Excel. Bạn cần kích hoạt nó và chuyển đến tab cùng tên. Sẽ có một phần "Liên kết", trong đó chúng ta cần nhấp vào nút "Phá vỡ tất cả các liên kết".
Mã VBA
Nếu không thể kích hoạt tiện ích bổ sung này, bạn có thể tự tạo macro. Để thực hiện việc này, hãy mở trình soạn thảo Visual Basic bằng cách nhấn các phím Alt + F11 và viết các dòng sau vào trường nhập mã.
Sub UnlinkWorkBooks ()
Liên kết Wb mờ
Dim và As Long
Chọn Case MsgBox (“Tất cả các tham chiếu đến các sách khác sẽ bị xóa khỏi tệp này và các công thức liên quan đến các sách khác sẽ được thay thế bằng các giá trị.” & VbCrLf & “Bạn có chắc chắn muốn tiếp tục không?”, 36, “Hủy liên kết?” )
Trường hợp 7 ′ Không
Thoát Sub
Kết thúc Chọn
WbLinks = ActiveWorkbook.LinkSources (Loại: = xlLinkTypeExcelLinks)
Nếu không phải là IsEmpty (WbLinks) thì
Đối với i = 1 Tới UBound (WbLinks)
ActiveWorkbook.BreakLink Name: = WbLinks (i), Type: = xlLinkTypeExcelLinks
Sau
khác
MsgBox “Không có liên kết đến các sách khác trong tệp này.”, 64, “Liên kết đến các sách khác”
Cuối Nếu
End Sub
Cách phá vỡ mối quan hệ chỉ trong phạm vi đã chọn
Theo thời gian, số lượng liên kết rất lớn và người dùng sợ rằng sau khi xóa một trong số chúng, sẽ không thể trả lại mọi thứ nếu một số liên kết bị thừa. Nhưng đây là vấn đề khó tránh khỏi. Để thực hiện việc này, bạn cần chọn phạm vi xóa các liên kết, sau đó xóa chúng. Để làm điều này, bạn phải thực hiện chuỗi hành động sau:
- Chọn tập dữ liệu cần được sửa đổi.
- Cài đặt tiện ích bổ sung VBA-Excel, sau đó chuyển đến tab thích hợp.
- Tiếp theo, chúng tôi tìm menu "Liên kết" và nhấp vào nút "Ngắt liên kết trong các phạm vi đã chọn".
Sau đó, tất cả các liên kết trong tập hợp ô đã chọn sẽ bị xóa.
Phải làm gì nếu các mối quan hệ không bị phá vỡ
Tất cả những điều trên nghe có vẻ tốt, nhưng trong thực tế luôn có một số sắc thái. Ví dụ, có thể có một tình huống mà các mối quan hệ không bị phá vỡ. Trong trường hợp này, một hộp thoại vẫn xuất hiện cho biết rằng không thể tự động cập nhật các liên kết. Làm gì trong tình huống này?
- Đầu tiên, bạn cần kiểm tra xem có bất kỳ thông tin nào được chứa trong các dải ô đã đặt tên hay không. Để thực hiện việc này, hãy nhấn tổ hợp phím Ctrl + F3 hoặc mở tab “Công thức” - “Trình quản lý tên”. Nếu tên tệp đã đầy, bạn chỉ cần chỉnh sửa hoặc xóa hoàn toàn. Trước khi xóa các dải ô đã đặt tên, bạn cần sao chép tệp sang một số vị trí khác để có thể quay lại phiên bản gốc nếu thực hiện sai các bước.
- Nếu bạn không thể giải quyết vấn đề bằng cách xóa tên, bạn có thể kiểm tra định dạng có điều kiện. Các ô trong bảng khác có thể được tham chiếu trong các quy tắc định dạng có điều kiện. Để thực hiện việc này, hãy tìm mục tương ứng trên tab “Trang chủ”, sau đó nhấp vào nút “Quản lý tệp”.
Thông thường, Excel không cung cấp cho bạn khả năng cung cấp địa chỉ của các sổ làm việc khác ở định dạng có điều kiện, nhưng bạn có thể cung cấp nếu bạn tham chiếu đến một phạm vi đã đặt tên với một tham chiếu đến tệp khác. Thông thường, ngay cả sau khi liên kết bị xóa, liên kết vẫn còn. Không có vấn đề gì trong việc loại bỏ một liên kết như vậy, vì thực tế liên kết đó không hoạt động. Do đó, sẽ không có gì xấu xảy ra nếu bạn loại bỏ nó.
Bạn cũng có thể sử dụng chức năng “Kiểm tra dữ liệu” để tìm xem có bất kỳ liên kết không cần thiết nào không. Các liên kết thường vẫn còn nếu loại xác thực dữ liệu "Danh sách" được sử dụng. Nhưng phải làm gì nếu có rất nhiều ô? Có thực sự cần thiết phải kiểm tra tuần tự từng cái không? Dĩ nhiên là không. Rốt cuộc, nó sẽ mất một thời gian rất dài. Do đó, bạn cần sử dụng một mã đặc biệt để tiết kiệm đáng kể.
Tùy chọn rõ ràng
'—————————————————————————————
'Tác giả: The_Prist (Shcherbakov Dmitry)
'Phát triển chuyên nghiệp các ứng dụng cho MS Office ở bất kỳ mức độ phức tạp nào
'Thực hiện đào tạo trên MS Excel
'https://www.excel-vba.ru
'[email được bảo vệ]
'WebMoney — R298726502453; Yandex.Money - 41001332272872
' Mục đích:
'—————————————————————————————
Sub FindErrLink ()
'chúng ta cần tìm trong liên kết Data -Change liên kết đến tệp nguồn
'và đặt các từ khóa ở đây bằng chữ thường (một phần của tên tệp)
'dấu hoa thị chỉ thay thế bất kỳ số ký tự nào nên bạn không phải lo lắng về tên chính xác
Const sToFndLink $ = “* doanh số 2018 *”
Dim rr As Range, rc As Range, rres As Range, s $
'xác định tất cả các ô với xác thực dữ liệu
On Error Resume Next
Đặt rr = ActiveSheet.UsedRange.SpecialCells (xlCellTypeAllValidation)
Nếu rr là không có gì thì
MsgBox “Không có ô nào có xác thực dữ liệu trên trang tính hiện hoạt”, vbInformation, “www.excel-vba.ru”
Thoát Sub
Cuối Nếu
Về lỗi GoTo 0
'kiểm tra từng ô để tìm liên kết
Đối với mỗi rc Trong rr
'để đề phòng, chúng tôi bỏ qua lỗi - điều này cũng có thể xảy ra
nhưng các kết nối của chúng ta phải không có chúng và chúng chắc chắn sẽ được tìm thấy
s = «»
On Error Resume Next
s = RC.Validation.Formula1
Về lỗi GoTo 0
'found - chúng tôi thu thập mọi thứ trong một phạm vi riêng biệt
Nếu (các) LCase Thích sToFndLink thì
Nếu rres không là gì thì
Đặt rres = rc
khác
Đặt rres = Union (rc, rres)
Cuối Nếu
Cuối Nếu
Sau
'nếu có kết nối, hãy chọn tất cả các ô có kiểm tra dữ liệu như vậy
Nếu không rres thì không có gì
rres.Chọn
'rres.Interior.Color = vbRed' nếu bạn muốn làm nổi bật bằng màu sắc
Cuối Nếu
End Sub
Cần phải tạo một mô-đun chuẩn trong trình chỉnh sửa macro, và sau đó chèn văn bản này vào đó. Sau đó, gọi cửa sổ macro bằng tổ hợp phím Alt + F8, sau đó chọn macro của chúng tôi và nhấp vào nút “Chạy”. Có một số điều cần lưu ý khi sử dụng mã này:
- Trước khi bạn tìm kiếm một liên kết không còn phù hợp nữa, trước tiên bạn phải xác định xem liên kết mà nó được tạo ra trông như thế nào. Để thực hiện việc này, hãy chuyển đến menu "Dữ liệu" và tìm mục "Thay đổi liên kết" ở đó. Sau đó, bạn cần xem tên tệp và chỉ định nó trong dấu ngoặc kép. Ví dụ, như thế này: Const sToFndLink $ = “* doanh số 2018 *”
- Có thể viết tên không đầy đủ mà chỉ cần thay thế các ký tự không cần thiết bằng dấu hoa thị. Và trong dấu ngoặc kép, hãy viết tên tệp bằng chữ nhỏ. Trong trường hợp này, Excel sẽ tìm tất cả các tệp có chứa một chuỗi như vậy ở cuối.
- Mã này chỉ có thể kiểm tra các liên kết trong trang tính hiện đang hoạt động.
- Với macro này, bạn chỉ có thể chọn các ô mà nó đã tìm thấy. Bạn phải xóa mọi thứ theo cách thủ công. Đây là một điểm cộng, vì bạn có thể kiểm tra lại mọi thứ một lần nữa.
- Bạn cũng có thể làm cho các ô được đánh dấu bằng một màu đặc biệt. Để thực hiện việc này, hãy xóa dấu nháy đơn trước dòng này. rres.Interior.Color = vbRed
Thông thường, sau khi bạn hoàn thành các bước được mô tả trong hướng dẫn ở trên, sẽ không còn kết nối không cần thiết nữa. Nhưng nếu có một số trong số chúng trong tài liệu và bạn không thể xóa chúng vì lý do này hay lý do khác (ví dụ điển hình là bảo mật dữ liệu trong trang tính), thì bạn có thể sử dụng một chuỗi hành động khác. Hướng dẫn này chỉ có hiệu lực cho các phiên bản 2007 trở lên.
- Chúng tôi tạo một bản sao lưu của tài liệu.
- Mở tài liệu này bằng trình lưu trữ. Bạn có thể sử dụng bất kỳ tệp nào hỗ trợ định dạng ZIP, nhưng WinRar cũng sẽ hoạt động, cũng như WinRar được tích hợp sẵn trong Windows.
- Trong kho lưu trữ xuất hiện, bạn cần tìm thư mục xl, sau đó mở liên kết ngoài.
- Thư mục này chứa tất cả các liên kết bên ngoài, mỗi liên kết tương ứng với một tệp có dạng externalLink1.xml. Tất cả chúng đều chỉ được đánh số, và do đó người dùng không có cơ hội hiểu loại kết nối này là gì. Để hiểu loại kết nối nào, bạn cần mở thư mục _rels và xem nó ở đó.
- Sau đó, chúng tôi xóa tất cả hoặc các liên kết cụ thể, dựa trên những gì chúng tôi tìm hiểu được trong tệp externalLinkX.xml.rels.
- Sau đó, chúng tôi mở tệp của mình bằng Excel. Sẽ có thông tin về lỗi như “Lỗi ở một phần nội dung trong Sách”. Chúng tôi đồng ý. Sau đó, một hộp thoại khác sẽ xuất hiện. Chúng tôi đóng nó.
Sau đó, tất cả các liên kết nên được gỡ bỏ.