☰

在工作簿之间引用动态命名范围

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vlookup named-ranges
在工作簿之间引用动态命名范围

我继承了这个数据结构。我有一堆产品及其供应商信息，布局如下：

Supplier  Order Number  Name  Cost
ACME      00123         A     1
ACME      00321         B     2
ACME      20798         C     3
ACME      11010         D     4
ACME      32333         E     5
ACME      20001         F     6
Babar     20001         G     7
Babar     54687         H     8
Babar     69441         I     9
Babar     65777         J     10

我将从其他工作表中引用这些数据。显然，如果不区分供应商，仅使用订单号的 vlookup 是不可靠的。

我的第一个解决方案在本地有效，但我无法让它在工作簿之间工作。我使用 INDIRECT、MATCH 和 COUNTIF 在上述工作表中设置了一个命名范围，这样范围就会随着新项目的添加而扩大。

在这个例子中，我有Range_ACME定义为：

=INDIRECT("B"&MATCH(Sheet1!$A$2,Sheet1!$A:$A,0)&":D"&(MATCH(Sheet1!$A$2,Sheet1!$A:$A,0)+COUNTIF(Sheet1!$A:$A,Sheet1!$A$2)-1))

（实际上，我使用另一个命名单元格作为 MATCH 查找值，但为了测试目的我对其进行了简化。）这样我就可以像这样找到 ACME 商品 00123 的成本：

=VLOOKUP("00123",Range_ACME,2,false)

当尝试在另一个工作簿中使用这些范围时，我收到一个错误，提示范围无效。如果我使用一个更简单的命名范围，没有所有的 INDIRECT 通配符，那么它就可以正常工作。在这里，Range_ACME_Simple只是

=工作表 1!$B$2:$D$7

在此处输入图片描述

我理解这需要我同时打开工作簿。还有其他方法可以定义动态范围吗，还是我应该将供应商名称与订单号连接起来？

答案1

为了简单地解决这个问题并演示所需的操作，我重新创建了您提供的工作表，并在数据集工作簿中创建了命名范围ACME.xlsx与其数据列标题相关

  • 供应商 =Sheet1!$A:$A
  • 命令 =Sheet1!$B:$B
  • 姓名 =Sheet1!$C:$C
  • 成本 =Sheet1!$D:$D

然后在另一个工作簿中，我为您想要查找的值创建了入口点（供应商和订单号）。您想要退回的商品名称位于下一个入口点

其他工作簿

B3在正在创建的工作簿的单元格中，我输入

=INDEX('C:\ACME.xlsx'!Name,MATCH(B1&B2,'C:\ACME.xlsx'!Supplier&'C:\ACME.xlsx'!Order,0))并按下CTRL++SHIFTENTER

这给了我们想要的结果。现在，如果你将单元格更改B1为 ACME，项目名称将自动更改为 F，并且如果公式中有完整的文件路径，则不需要打开包含数据结构的工作簿。

相关内容