交流是 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 不能让您以条件格式提供其他工作簿的地址,但如果您通过引用另一个文件来引用命名范围,则可以这样做。 通常,即使删除链接后,链接仍然存在。 删除这样的链接没有问题,因为该链接实际上不起作用。 因此,如果您删除它,不会发生任何不好的事情。
您还可以使用“数据检查”功能来查找是否有任何不必要的链接。 如果使用“列表”类型的数据验证,链接通常会保留。 但是如果有很多细胞怎么办? 真的有必要依次检查它们吗? 当然不是。 毕竟,这将需要很长时间。 因此,您需要使用特殊代码来显着保存它。
选项显式
'——————————————————————————————
' 作者: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 打开我们的文件。 将显示有关错误的信息,例如“书中部分内容错误”。 我们同意。 之后,将出现另一个对话框。 我们关闭它。
之后,应删除所有链接。