logo标签列表

在Excel公式中引用动态命名区域

exceldynamicexcel-formulanamed-ranges
10
我在Excel中有一个表格，其中的列标题对应于工作簿中其他地方的命名范围的一部分。例如，我有这些列标题：“10”，“20”等，和这些动态命名范围：“ExampleRange10”，“ExampleRange2”等。我想输入一个VLookup公式，通过连接字符串“ExampleRange”和列标题“10”来引用ExampleRange10。这将允许我将公式简单地扩展到表格中的所有列，而不是在每个列的公式中手动输入“ExampleRange10”，“ExampleRange20”等。
我知道INDIRECT函数，并且已经在以前成功地使用过具名范围，但它似乎不能与此情况下的动态命名范围一起使用。我猜想这是Excel如何定义动态命名范围的细微差别（它们不会出现在公式栏左侧的命名范围下拉列表中，在VBA中它们具有一些有趣的属性）。是否有办法在动态命名范围上使用INDIRECT公式，或者有另一种方法来解决这个问题？
编辑：这里是使用的确切公式。 这是主要公式：= VLOOKUP（B2，INDIRECT（“ ExampleRange”＆amp; C1），2，FALSE），其中C1包含“10”，而名为“ExampleRange10”的动态命名范围的公式是：= OFFSET（Sheet1！$ F $ 2,0,0，COUNTA（Sheet1！$ F $ 2：$ F $ 25），2）。主要公式返回“＃REF！”但当我删除动态命名范围公式并将“ExampleRange10”仅定义为静态范围时，它可以正确工作。
你能提供确切的不起作用的公式吗？ - Peter L.
你可以根据你的布局情况，使用offset()和column()的组合来实现。 - Tim Williams
@KyleWurtz 在 Excel 2007 中 =VLOOKUP(B5,INDIRECT("range"&C1),1,0) 对我来说运行良好。你确定公式的其余部分没有错误，例如第三个参数 - 要返回的范围中的列号？ - Peter L.
1
@PeterL. 呃。你的“range10”是动态的吗？我已经仔细检查了我的公式和范围，但是我找不到任何错误。当我进入名称管理器并单击“引用”框时，动态范围和静态范围都会突出显示相同的区域，因此我认为问题不在于动态范围的OFFSET公式...正如我之前提到的，当我用静态范围文本的开头替换“ExampleRange”时，完全相同的VLOOKUP公式也可以工作，因此VLOOKUP公式似乎也是有效的... - Kyle Wurtz
@KyleWurtz 你说得对 - 双重动态确实不起作用...也许可以使用中间单元格，在其中获取 "ExampleRange"&C1 以进行进一步的间接引用？ - Peter L.
@PeterL。我尝试了，但没有成功。看起来根本问题在于INDIRECT与动态命名范围不兼容...我目前正在研究编写一个VBA自定义函数作为解决方法。请参见链接。 - Kyle Wurtz
8个回答
7
据我进一步研究，Excel的INDIRECT函数似乎无法使用动态范围。可能有巧妙的方法来避免使用INDIRECT并保持在非VBA Excel世界中，但我不知道这样的方法。因此，我最终创建了一个用户定义的函数，该函数与此处所描述的函数非常相似。我将主要公式改为=VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE)，其中DINDIRECT是我创建的VBA函数的名称。
唯一的缺点（或者根据您的看法，这可能不是缺点）是必须将工作簿另存为启用宏的工作簿，并且自定义函数的使用不太容易理解，需要向其他用户进行解释。总的来说，对我来说这是一个可接受的解决方案。
以下是代码：
Public Function DINDIRECT(sName As String) As Range
     Dim nName As Name

     On Error Resume Next
          Set nName = ActiveWorkbook.Names(sName)
          Set nName = ActiveSheet.Names(sName)
     On Error GoTo 0

     If Not nName Is Nothing Then
          Set DINDIRECT = nName.RefersToRange
     Else
          DINDIRECT = CVErr(xlErrName)
End Function

注意：虽然这个解决方案可行，但我不打算接受我的答案，因为我不想阻止其他人发布更好的解决方案。同时，我是该网站的新手，如果我回答了自己的问题而犯了任何礼仪规则的错误，请见谅...我只是想分享我使用的确切解决方案，以便其他人能够从中受益。
5

最近我遇到了同样的问题，答案就如你已猜到的，实际上你无法使用INDIRECT引用动态命名范围。

但是，你可以将动态范围公式本身作为INDIRECT的参数使用，但这对于你想要做的事情没有什么用。这有点令人恼火，因为这是非常有用的功能。

感谢确认INDIRECT与动态命名范围不兼容。我完全同意这个功能会很有用，尤其是考虑到解决方法的代码非常简单（所以他们应该很容易将其纳入）以及问题出现的频率（至少对我来说如此），这让人特别沮丧。 - Kyle Wurtz
没错。当你不得不想出一个不使用UDFs（或任何VBA）的解决方案时，这会让人倍感沮丧，而我经常被要求这样做。 - blackworx
2
如果您的数据已经有了像10、20等这样的标题，那么您不需要使用间接函数。为什么不直接使用Index/Match来选择您需要的数据呢？
例如，将整个表格命名为ExampleRanges，使用以下公式：
Index(ExampleRanges, match(B2, index(ExampleRanges, , 1), 0), match(C1, index(ExampleRanges, 1,), 0))
解决了！使用索引函数解决了我在命名公式中使用间接函数的问题 :) - cyberponk
1

我知道这篇文章已经很老了，但是我最近才看到它，并且想提供一种解决方案，避免使用任何VBA编码，以防对其他遇到类似问题的人有所帮助：

=VLOOKUP(B2,CHOOSE(C1/10,example10,example20,example30,example40),2,0)

这是基于10、20、30等命名约定的假设，对于数百个范围来说并不理想。
0
通过使用地址和间接函数，可以为命名范围添加新的变化。我有一个案例，需要为一系列表格设置命名范围，并使用以下内容：
Named Range: WWDH-FF-PI which points to Linear!$A$19 (first cell in table)

获取地址的代码如下： $T$56: =ADDRESS(MATCH(S56,Linear!A:A,0),1,1,1,"Linear")
之后可以使用偏移函数多次复制，创建一个数据透视表：
=OFFSET(INDIRECT($T$56),C5,$T$57-1)

因此，地址函数可以嵌入（或包装）到间接函数中，以创建动态单元格地址。

那个方法可行，但不幸的是，它无法处理动态命名范围，即指向可变数量单元格的命名范围，通常取决于它们的内容。 - blackworx
0

未经测试，但我认为这应该可以工作：

用户定义函数返回您动态命名范围的地址：

Function Named_Range_Address(Range_Name As Range, _ 
    Optional SheetName As Boolean) As String 

    Dim strName As String 
    Application.Volatile 

    If SheetName = True Then 
        strName = "'" & Range_Name.Parent.Name & "'!" & Range_Name.Address 
    Else 
        strName = Range_Name.Address 
    End If 

    Named_Range_Address = strName 
End Function

那么您应该可以使用您的VLOOKUP公式：

=VLOOKUP(B2,INDIRECT(named_range_address("ExampleRange"&C1,TRUE)),2,FALSE)
谢谢您的建议。我在谷歌搜索时看到过类似这样的函数，但是这个特定的函数要求第一个参数为Range类型，但由于我正在连接字符串“ExampleRange”和C1中的文本，所以我得到了一个String类型的参数。然而，您使用用户定义函数的建议是正确的。我找到了另一个函数，它需要RangeName As String作为参数在这里，对我来说很好用。 - Kyle Wurtz
0
今天我在玩Excel命名范围时发现，虽然在INDIRECT()函数中不能计算范围的名称，但你仍然可以通过添加一个中间步骤以纯“Excel方式”获取它：只需创建一些隐藏单元格，在其中计算命名范围即可。
例如，假设A1包含范围名称的“动态部分”，那么在A2中使用公式= "ExampleRange" & A1，现在你就有了完整的范围名称，可以将其用作= INDIRECT(A2)。
问题不在于名称本身是动态的，而在于它所引用的范围是动态的。 - blackworx
0

我知道这是一个非常老的帖子，但我遇到了同样的问题，也许我的解决方案可以帮助未来的人们。

基本上，我创建了一个宏，在保存时会删除和重新定义范围，并给它命名。因此，INDIRECT函数将按名称工作，因为范围不是动态的。您只需要在添加任何值到已命名范围后保存工作簿即可。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim a, b, c, d, e, f As Integer
Dim data As Worksheet

Set data = ThisWorkbook.Worksheets("Data")

a = data.Range("A" & Rows.count).End(xlUp).row
b = data.Range("B" & Rows.count).End(xlUp).row
c = data.Range("C" & Rows.count).End(xlUp).row
d = data.Range("D" & Rows.count).End(xlUp).row
e = data.Range("E" & Rows.count).End(xlUp).row
f = data.Range("F" & Rows.count).End(xlUp).row



ActiveWorkbook.Names("KP").Delete
ActiveWorkbook.Names("KPT").Delete
ActiveWorkbook.Names("AP").Delete
ActiveWorkbook.Names("APT").Delete
ActiveWorkbook.Names("DISC").Delete
ActiveWorkbook.Names("SEATS").Delete

ActiveWorkbook.Names.Add Name:="KP", RefersTo:="=Data!$A$2:$A$" & a
ActiveWorkbook.Names.Add Name:="KPT", RefersTo:="=Data!$B$2:$B$" & b
ActiveWorkbook.Names.Add Name:="AP", RefersTo:="=Data!$C$2:$C$" & c
ActiveWorkbook.Names.Add Name:="APT", RefersTo:="=Data!$D$2:$D$" & d
ActiveWorkbook.Names.Add Name:="DISC", RefersTo:="=Data!$E$2:$E$" & e
ActiveWorkbook.Names.Add Name:="SEATS", RefersTo:="=Data!$F$2:$F$" & f

End Sub
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接