时间戳

首页 > 解决方案 > 2 个通过 importrage 链接的 Google 表格工作簿 - 如何在一个工作簿中添加新行而不影响其他工作簿

google-sheets - 2 个通过 importrage 链接的 Google 表格工作簿 - 如何在一个工作簿中添加新行而不影响其他工作簿

问题描述

我有 2 个谷歌工作簿。一个是价目表，另一个是带有定价的供应商列表。

我通过 importrage 命令将带有定价工作簿的供应商列表链接到费率表工作簿。这很有效，并且一切都会更新，直到您在费率表工作簿中添加新行或删除行。此时，供应商工作簿不会更新参考，只是仍然指向原来的同一行，现在显示不正确的数据。

如何动态地让供应商列表工作簿通知费率表工作簿中行的添加或删除并更新 importrange 公式？

这可以做到吗？

任何帮助是极大的赞赏。

编辑：我准备了 2 个示例工作簿。它们是测试表 1 - 速率和测试表 2 通道

我在第一张表中有我们所有的费率。在第二个中，我们将拥有供应商通道。该表由多个选项卡组成，所有选项卡都有一个单独的通道，该通道链接到费率表中的 1 行。

在车道表中，我通过单元格 A8 中的 importrange 将两者链接在一起。现在这可以正常工作并且链接正常，也就是说，直到您在车道链接到的费率表中插入新行，然后它不会更新，但会显示新行信息是什么。

当在费率表中插入新行时，有没有办法让车道表动态更新到下一行？

两张表链接如下：

费率

车道

标签： google-sheets

解决方案

解决方案 1 - 如果您只需要检查一个条件

为了达到预期的结果，您必须为每个费率设置一个 ID。为了这个答案（基于您的示例表），我假设这可能是一个起源城市。在测试表 2 中，我添加了新表 - SheetTest 其中：

第一步

我正在寻找欲望城市驻留在费率表中的行号（例如 - 它的麦克莱伦市）。代码很简单：

MATCH(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1JD45y-0-37Kh2KsHnRAMuB0quPtMVuLKuT1qjnNcm1Q/edit","Rates!B:B"),0).

这给了我-> 10

第二步

我使用此结果动态生成最终范围 - “Rates!A10:X10”。A8 单元格中的最终结果是（为了更清楚，我将工作表的地址更改为 -othersheeturl- ：

=IMPORTRANGE(
  "-othersheeturl-",
  "Rates!A"&
      MATCH(A2,IMPORTRANGE(-othersheeturl-","Rates!B:B"),0)
      &":X"&
      MATCH(A2,IMPORTRANGE("-othersheeturl-","Rates!B:B"),0)
)

工作示例

警告

我看到在您的原始工作表中，您从 Rates 工作表中使用“take” Origin City，因此我在 A2 中删除了此依赖项以使其成为 ID。我希望这能解决你的问题。

解决方案 2 - 如果您需要检查许多条件

方法相同，但工具不同。主要目标是找到符合条件的行号 - “From City”和“To City”。然后这个行号将用于连接期望范围。

第一步 - 一些清洁

为了更好地管理复杂的公式，最好将它们分成更小的部分——就像在“普通”编程中一样。所以，我们有：

从A5城市

New Albany

B5到市区

Calgary

B1中的费率表（外部表）网址：

1JD45y-0-37Kh2KsHnRAMuB0quPtMVuLKuT1qjnNcm1Q

您不必输入整个 URL，只需要这部分

计算我们在B2中寻找的行号的公式

=QUERY(
{IMPORTRANGE(B1,"A1:B"),
ArrayFormula(row(INDIRECT("A1:A"&ROWS(IMPORTRANGE(B1,"A1:B")))))},
"select Col3 where Col1='"&B5&"' and Col2='"&A5&"'
limit 1")

以上代码：

  1. 从费率表（外部）构建一个数组 - 列 A1:B 和表示行号的虚拟索引
  2. 然后，仅显示第 nr 3 列（索引），其中 Col1 匹配我们的“To City”，Col2 匹配“From City”
  3. 最后是一个结果的限制（如果有超过一机）

B3的愿望范围

="Rates!A"&B2&":X"&B2

在A11中呈现来自外部工作表的期望数据的最终公式

=IMPORTRANGE(B1,B3)

工作示例

最后的想法

  1. 您可以隐藏第 1-3 行，也可以将上述所有公式合并为一个。由你决定。我更喜欢第一种方法。
  2. 该解决方案为您提供了尽可能多的条件的自由

推荐阅读