☰

如何在 Excel 中创建源工作表和目标工作表？

tag-icon • tag-icon microsoft-excel worksheet-function vba automatic-update
如何在 Excel 中创建源工作表和目标工作表？

我正在努力使用 Excel 功能来创建来源和目的地工作簿。

我的源数据来自网络。所以我下载了它，创建了一些新列和函数，并用它在 Power BI 上制作了一个仪表板。当我的源数据发生变化时，这个仪表板需要更新。

由于我必须对来源在 Power BI 上制作仪表板之前，我无法直接链接数据进行自动更新。

这里的想法是创建一个目的地包含所有必要列的 Excel 工作表，以便在 Power BI 上进行连接。然后下载源文件并创建链接。这样，我应该能够下载新的来源每周归档一次，将更新我的目的地文件并最终更新我的仪表板！！

我该如何实现这一点？我不是 Excel 专家，确实需要帮助！！

PS 我使用的是 Office 365，Excel 版本 2102

答案1

所以据我理解，这个问题实际上是三个问题：（a）如何链接到另一个 Excel 文件，（b）如何链接到网络上的 Excel 文件（无需手动下载），以及（c）如果我链接的 Excel 工作簿一直在变化，如何更新引用？

（a）链接到我电脑上的另一个工作簿

在计算机上打开两个工作簿后，转到目标单元格并输入 =，然后直接切换到源工作簿并单击/选择源单元格，然后按 Enter。Excel 将更新目标单元格中​​的公式，如下所示：

='[test.xlsm]Sheet1'!$A$1

或这个

='C:\Users\JohnDoe\Documents\[test.xlsm]Sheet1'!$A$1

如果要从源表复制整列数据，请编辑公式并将其更改为相对引用：

='C:\Users\JohnDoe\Documents\[test.xlsm]Sheet1'!A1

现在复制并粘贴/拖动公式向下到逐个单元格地复制。

笔记：

  1. Excel 实际上会在目标文件中存储数据的物理副本，因此即使源不可用，目标电子表格仍可正常工作。如果您重新打开文件（尤其是在另一台计算机上），Excel 会询问您是否要更新源数据。
  2. 您可以轻松更改文件的源位置或名称，只需单击数据功能区 > 编辑链接，然后单击“更改源”即可

（b）链接到存储在 Web 服务器上的另一个工作簿

您可以手动输入源文件的 URL，就像输入本地文件的文件路径一样，例如（请注意，这是我从网上看似可靠的来源找到的随机工作测试文件，使用风险自负）：

='https://docs.collectiveaccess.org/images/6/68/[Sample_data.xlsx]Sheet1'!A1

首先，我建议您先使用本地文件设置目标工作簿，然后使用“更改源”选项链接到网络副本（只需将 URL 粘贴到弹出的打开文件对话框中即可）。如果文件名发生变化，请每周执行相同的过程。

（c）对源工作簿的动态引用

为了进一步复杂化文件链接，假设您的源数据每周都会发生微小变化。在某处插入了一个额外的列，或者工作表名称发生了变化等。我们可以通过几种方式来处理此问题，要么使用简单（但手动）的“搜索和替换”电子表格中的公式文本，要么使用源文件中的命名范围，要么使用目标文件中的相对命名范围。

假设您链接到源电子表格中 Sheet1、C 列的 SALES 数据：

='[test.xlsm]Sheet1'!C1

下周，源电子表格略有变化，相同的数据列现在是 Sheet2 的 D 列。

方法 1：点击主页功能区 > 查找和选择 > 替换（或快捷键 CTRL + H）。在找什么文本框中[test.xlsm]Sheet1'!C放入用。。。来代替文本框[test.xlsm]Sheet2'!D，然后点击全部替换。

方法 2：在源文件中定义一个命名范围。打开源文件并选择 C ​​列。现在单击“公式”功能区 >“定义名称”，然后输入姓名 SALES。 在下面参考=Sheet1!$C:$C由于选择了，Excel 应该已经填写了。现在在目标工作簿中，比如在 B1 中，输入='[test.xlsm]Sheet1'!SALES并向下复制。请注意，这是相对的，例如=SALESB100 将返回中的值'[test.xlsm]Sheet1'!C100。现在，如果 SALES 数据的位置发生变化，只需在公式 > 名称管理器下更新命名范围即可。

方法 3：在目标工作簿中定义一个相对命名范围（在您无法访问源文件的情况下）。选择目标表中的第一个单元格，例如 B1，其中必须存放 SALES 数据。现在单击公式功能区 > 定义名称，然后给出姓名 SALES及以下参考输入='[test.xlsm]Sheet1'!$C1。现在在 B1 中输入=SALES并向下复制 B 列。注意 $ 符号的位置。此命名范围与定义名称时光标所在的行号相关。如果您=SALES在单元格 B100 中使用，它将返回'[test.xlsm]Sheet1'!C100。如果您=SALES在单元格 G101 中使用，它将返回'[test.xlsm]Sheet1'!C101。如果您想同时引用整个多列范围（例如 SALES 和 TOTALS 列始终彼此相邻），则可以省略 $ 符号参考条目。然后=SALES在单元格 B100 中将返回'[test.xlsm]Sheet1'!C100，但=SALES在单元格 C100 中将返回'[test.xlsm]Sheet1'!D100。同样，您可以在需要时将数据功能区 > 名称管理器中的命名范围更新到其在 Sheet 2 Column D 中的新位置。请注意：如果插入行或列，相对名称范围将无法正常工作。SALES插入后将保持相对于硬编码引用 B1 的位置。使用上述 $ 至少可以防止列插入。

相关内容