交流是 Excel 中一個非常有用的功能。 畢竟,用戶經常不得不使用來自其他文件的信息。 但在某些情況下,它們弊大於利。 畢竟,例如,如果您通過郵件發送這些文件,則鏈接不起作用。 今天我們將更詳細地討論如何避免此類問題。
Excel中的關係是什麼
Excel 中的關係經常與函數結合使用,例如 VPR從另一個工作簿獲取信息。 它可以採用特殊鏈接的形式,其中不僅包含單元格的地址,還包含數據所在的書籍的地址。 結果,這樣的鏈接看起來像這樣: =VLOOKUP(A2;'[Sales 2018.xlsx]Report'!$A:$F;4;0). 或者,為了更簡單的表示,用以下形式表示地址: ='[Sales 2018.xlsx]Report'!$A1. 讓我們分析一下這種類型的每個鏈接元素:
- [銷售2018.xlsx]. 此片段包含指向您要從中獲取信息的文件的鏈接。 它也被稱為源。
- 照片. 我們使用了以下名稱,但這不是應有的名稱。 此塊包含您需要在其中查找信息的工作表的名稱。
- $A:$F 和 $A1 – 包含本文檔中包含的數據的單元格或範圍的地址。
實際上,創建指向外部文檔的鏈接的過程稱為鏈接。 在我們註冊了另一個文件中包含的單元格的地址後,“數據”選項卡的內容髮生了變化。 即,“更改連接”按鈕變為活動狀態,用戶可以藉助該按鈕編輯現有連接。
問題的本質
通常,使用鏈接不會出現額外的困難。 即使出現單元格發生變化的情況,所有鏈接也會自動更新。 但是,如果您已經重命名工作簿本身或將其移動到不同的地址,Excel 將變得無能為力。 因此,它會產生以下消息。
在這裡,用戶有兩種可能的選擇來決定如何在這種情況下採取行動。 他可以單擊“繼續”,然後更改將不會被更新,或者他可以單擊“更改關聯”按鈕,他可以手動更新它們。 單擊此按鈕後,將出現一個附加窗口,可以在其中更改鏈接,指示當前正確文件的位置及其名稱。
此外,您可以通過位於“數據”選項卡上的相應按鈕編輯鏈接。 用戶還可以發現連接因#LINK 錯誤而中斷,當 Excel 由於地址本身無效而無法訪問位於特定地址的信息時,就會出現該錯誤。
如何在excel中取消鏈接
如果您無法自己更新鏈接文件的位置,解決上述情況的最簡單方法之一是刪除鏈接本身。 如果文檔只包含一個鏈接,這尤其容易做到。 為此,您必須執行以下步驟序列:
- 打開“數據”菜單。
- 我們找到“連接”部分,並在那裡找到“更改連接”選項。
- 之後,單擊“取消鏈接”。
如果您打算將這本書郵寄給其他人,強烈建議您提前這樣做。 畢竟,刪除鏈接後,另一個文檔中包含的所有值都會自動加載到文件中,在公式中使用,而不是單元格地址,而是將相應單元格中的信息簡單地轉換為值.
如何取消所有書籍的鏈接
但是如果鏈接數量變得太大,手動刪除它們可能需要很長時間。 要一次性解決此問題,您可以使用特殊的宏。 它位於 VBA-Excel 插件中。 您需要激活它並轉到同名選項卡。 將有一個“鏈接”部分,我們需要在其中單擊“斷開所有鏈接”按鈕。
VBA代碼
如果無法激活此插件,您可以自己創建一個宏。 為此,請按 Alt + F11 鍵打開 Visual Basic 編輯器,然後在代碼輸入字段中寫入以下行。
子 UnlinkWorkBooks()
暗淡的 WbLinks
昏暗而長
Select Case MsgBox(“所有對其他書籍的引用都將從該文件中刪除,引用其他書籍的公式將替換為值。” & vbCrLf & “您確定要繼續嗎?”, 36, “取消鏈接?” )
案例 7' 沒有
退出小組
最終選擇
WbLinks = ActiveWorkbook.LinkSources(類型:=xlLinkTypeExcelLinks)
如果不是 IsEmpty(WbLinks) 那麼
對於 i = 1 到 UBound(WbLinks)
ActiveWorkbook.BreakLink 名稱:=WbLinks(i),類型:=xlLinkTypeExcelLinks
下一頁
其他
MsgBox “此文件中沒有其他書籍的鏈接。”, 64, “其他書籍的鏈接”
如果結束
END SUB
如何僅在選定範圍內打破平局
有時鏈接的數量很大,用戶擔心刪除其中一個後,如果有一些是多餘的,將無法返回所有內容。 但這是一個很容易避免的問題。 為此,您需要選擇要刪除鏈接的範圍,然後將其刪除。 為此,您必須執行以下操作序列:
- 選擇需要修改的數據集。
- 安裝 VBA-Excel 插件,然後轉到相應的選項卡。
- 接下來,我們找到“鏈接”菜單,然後單擊“斷開所選範圍內的鏈接”按鈕。
之後,所選單元格集中的所有鏈接都將被刪除。
如果關係沒有斷開怎麼辦
以上所有聽起來都不錯,但在實踐中總是存在一些細微差別。 例如,可能存在關係沒有中斷的情況。 在這種情況下,仍會出現一個對話框,指出無法自動更新鏈接。 在這種情況下該怎麼辦?
- 首先,您需要檢查命名範圍中是否包含任何信息。 為此,請按組合鍵 Ctrl + F3 或打開“公式”選項卡 - “名稱管理器”。 如果文件名已滿,那麼您只需要編輯它或完全刪除它。 在刪除命名範圍之前,您需要將文件複製到其他位置,以便在採取錯誤步驟時可以返回到原始版本。
- 如果您無法通過刪除名稱來解決問題,您可以檢查條件格式。 可以在條件格式規則中引用另一個表中的單元格。 為此,請在“主頁”選項卡上找到相應的項目,然後單擊“文件管理”按鈕。
通常,Excel 不能讓您以條件格式提供其他工作簿的地址,但如果您通過引用另一個文件來引用命名範圍,則可以這樣做。 通常,即使刪除鏈接後,鏈接仍然存在。 刪除這樣的鏈接沒有問題,因為該鏈接實際上不起作用。 因此,如果您刪除它,不會發生任何不好的事情。
您還可以使用“數據檢查”功能來查找是否有任何不必要的鏈接。 如果使用“列表”類型的數據驗證,鏈接通常會保留。 但是如果有很多細胞怎麼辦? 真的有必要依次檢查它們嗎? 當然不是。 畢竟,這將需要很長時間。 因此,您需要使用特殊代碼來顯著保存它。
Option Explicit
'—————————————————————————————
' 作者:The_Prist(Shcherbakov Dmitry)
' 任何復雜的 MS Office 應用程序的專業開發
' 在 MS Excel 上進行培訓
' https://www.excel-vba.ru
' [電子郵件保護]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' 目的:
'—————————————————————————————
子 FindErrLink()
'我們需要查看指向源文件的 Data -Change 鏈接
'並將此處的關鍵字小寫(文件名的一部分)
'星號只是替換任意數量的字符,因此您不必擔心確切的名稱
Const sToFndLink$ = “*2018 年銷售額*”
Dim rr 作為範圍,rc 作為範圍,rres 作為範圍,s$
'用數據驗證定義所有單元格
在錯誤恢復下一頁
設置 rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
如果 rr 什麼都不是,那麼
MsgBox “活動工作表上沒有帶有數據驗證的單元格”, vbInformation, “www.excel-vba.ru”
退出小組
如果結束
在錯誤轉到0
'檢查每個單元格的鏈接
對於每個 rc In rr
'以防萬一,我們跳過錯誤——這也可能發生
'但我們的聯繫必須沒有他們,他們肯定會被發現
s = «»
在錯誤恢復下一頁
s = rc.Validation.Formula1
在錯誤轉到0
'found - 我們在一個單獨的範圍內收集所有內容
如果 LCase(s) 像 sToFndLink 那麼
如果 rres 什麼都不是,那麼
設置 rres = rc
其他
設置 rres = Union(rc, rres)
如果結束
如果結束
下一頁
'如果有連接,則選擇所有具有此類數據檢查的單元格
If Not rres Is Nothing 那麼
rres.選擇
' rres.Interior.Color = vbRed '如果你想用顏色突出顯示
如果結束
END SUB
需要在宏編輯器中創建一個標準模塊,然後在其中插入該文本。 之後,使用組合鍵 Alt + F8 調用宏窗口,然後選擇我們的宏並單擊“運行”按鈕。 使用此代碼時需要注意以下幾點:
- 在搜索不再相關的鏈接之前,您必須首先確定創建它的鏈接的外觀。 為此,請轉到“數據”菜單並在那裡找到“更改鏈接”項。 之後,您需要查看文件名,並在引號中指定它。 例如,像這樣: Const sToFndLink$ = “*2018 年銷售額*”
- 可以不完整地寫入名稱,而只需用星號替換不必要的字符。 在引號中,用小寫字母寫下文件名。 在這種情況下,Excel 將查找所有末尾包含此類字符串的文件。
- 此代碼只能檢查當前處於活動狀態的工作表中的鏈接。
- 使用此宏,您只能選擇它找到的單元格。 您必須手動刪除所有內容。 這是一個優點,因為您可以再次仔細檢查所有內容。
- 您還可以使單元格以特殊顏色突出顯示。 為此,請刪除此行之前的撇號。 rres.Interior.Color = vbRed
通常,在完成上述說明中描述的步驟後,不應再有不必要的連接。 但是,如果文檔中有一些它們並且您由於某種原因無法刪除它們(一個典型的例子是工作表中數據的安全性),那麼您可以使用不同的操作順序。 此說明僅對 2007 及更高版本有效。
- 我們創建文檔的備份副本。
- 使用存檔器打開此文檔。 您可以使用任何支持 ZIP 格式的文件,但 WinRar 以及 Windows 內置的也可以使用。
- 在出現的存檔中,您需要找到 xl 文件夾,然後打開 externalLinks。
- 此文件夾包含所有外部鏈接,每個鏈接對應一個外部鏈接1.xml 格式的文件。 所有這些都只是編號,因此用戶沒有機會了解這是一種什麼樣的連接。 要了解什麼樣的連接,您需要打開 _rels 文件夾,並在那裡查看。
- 之後,我們根據在 externalLinkX.xml.rels 文件中了解的內容刪除所有或特定鏈接。
- 之後,我們使用 Excel 打開我們的文件。 將顯示有關錯誤的信息,例如“書中部分內容錯誤”。 我們同意。 之後,將出現另一個對話框。 我們關閉它。
之後,應刪除所有鏈接。