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

8 回答

• 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
  

  注意：虽然这个解决方案有效，但我不会接受我的回答，因为我不想阻止其他人发布更好的解决方案 . 此外，我是网站的新手，很抱歉，如果我通过回答我自己的问题打破任何礼仪代码......我只是想我会分享我使用的确切解决方案，以防其他人发现它有用 .

  回复于 2024-06-02T19:20:00+08:00
• 0

  我最近碰到了这个精确的砖墙，你已经猜到的答案就是你不能用INDIRECT引用动态命名范围 .

  但是，您可以将动态范围公式本身用作INDIRECT 's argument, but this is no use for what you want to do. Somewhat of a PITA since it'这种非常有用的功能 .

  回复于 2024-06-02T19:20:00+08:00
• 0

  如果您的数据包含10,20等 Headers ，那么您不需要使用间接 . 为什么不使用索引/匹配来选择所需的数据？

  例如，将整个表命名为ExampleRanges，并使用以下公式：

  Index(ExampleRanges, match(B2, index(ExampleRanges, , 1), 0), match(C1, index(ExampleRanges, 1,), 0))
  

  回复于 2024-06-02T19:20:00+08:00
• 5

  未经测试，但我认为这会奏效：

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

  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)
  

  回复于 2024-06-02T19:20:00+08:00
• 4

  我知道这已经很老了，但我只是遇到了这个问题并且认为我会添加一个避免任何VBA编码的解决方案，以防它遇到其他任何偶然发现的人：

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

  这假设命名约定为10,20,30等，并且不适用于数百个范围 .

  回复于 2024-06-02T19:20:00+08:00
• 0

  今天我正在修改Excel命名范围，我发现，虽然你无法在 INDIRECT() 调用本身中计算范围的名称，但你仍然可以通过添加一个中间步骤来获得纯粹的"Excel-way"：只需创建一些隐藏的单元格，您可以在其中计算命名范围 .

  例如，假设 A1 包含范围名称的"dynamic part"，则在 A2 中使用公式 = "ExampleRange" & A1 ，现在您拥有全范围名称，可以将其用作 = INDIRECT(A2) .

  回复于 2024-06-02T19:20:00+08:00
• 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函数创建数据透视表：

  =OFFSET(INDIRECT($T$56),C5,$T$57-1)
  

  因此，可以将Address函数嵌入（或包装）到Indirect函数中以创建动态单元地址 .

  回复于 2024-06-02T19:20:00+08:00
• 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
  

  回复于 2024-06-02T19:20:00+08:00