excel - 在 Excel 公式中引用动态命名范围

Question

我在 Excel 中有一个表格，其中的列标题对应于工作簿中其他位置的动态命名范围的一部分。例如，我有以下列标题:“10”、“20”等，以及这些动态命名范围:“ExampleRange10”、“ExampleRange2”等。我想输入一个通过连接来引用 ExampleRange10 的 VLookup 公式字符串“ExampleRange”和列标题“10”。这将允许我简单地将公式扩展到表中的所有列，而不是在每列的公式中手动键入“ExampleRange10”、“ExampleRange20”等。

我知道 INDIRECT 函数，并且过去曾成功地将其与命名范围一起使用，但在这种情况下，它似乎不适用于动态命名范围。我猜这是一个细微差别，与 Excel 定义动态命名范围的方式有关(它们不会显示在公式栏左侧的命名范围下拉列表中，并且它们在 VBA 中具有一些有趣的属性， 例如)。有没有办法可以将间接公式与动态命名范围结合使用，或者是否有其他方法可以解决这个问题？

编辑:以下是使用的确切公式。
这是主要公式:=VLOOKUP(B2,INDIRECT("ExampleRange"&C1),2,FALSE)，其中 C1 包含“10”，名为“ExampleRange10”的动态命名范围的公式为: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2)。主公式返回“#REF!”，但当我删除动态命名范围公式并简单地将“ExampleRange10”定义为静态范围时，它可以正常工作。

Answer 1

在进行进一步研究后我可以看出，Excel 的 INDIRECT 函数根本不适用于动态范围。可能有一种聪明的方法可以绕过使用 INDIRECT 并坚持使用非 VBA Excel 世界，但我不知道这种方法。相反，我最终创建了一个与描述的非常相似的用户定义函数 here 。我将主公式更改为 =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

注意:虽然这个解决方案有效，但我不会接受我的答案，因为我不想阻止其他人发布更好的解决方案。另外，我是该网站的新手，如果我回答自己的问题违反了任何礼仪规范，我很抱歉...我只是想分享我使用的确切解决方案，以防其他人发现它有用。