Excel替换INDIRECT函数：引用单元格内命名范围的非易失性公式方案

针对你遇到的INDIRECT函数导致的性能问题，以下是无需UDF的非易失性解决方案，适配511个命名范围的场景：

方法1：INDEX+辅助表+动态命名范围（兼容全Excel版本） #

1. 创建辅助表：新建工作表（比如命名为RangeList），在A列依次输入所有511个命名范围的名称（A1=MyRange1，A2=MyRange2，……，A511=MyRange511）。可通过单元格公式="MyRange"&ROW(A1)下拉批量生成名称，避免手动录入。
2. 定义动态命名范围：
   • 定义AllRangeNames，公式为：

     =RangeList!$A$1:INDEX(RangeList!$A:$A,COUNTA(RangeList!$A:$A))
     

   • 定义RangeReferences，公式为：

     =CHOOSE(ROW(INDIRECT("1:"&COUNTA(AllRangeNames))),MyRange1,MyRange2,...,MyRange511)
     

     这里的参数可通过辅助表批量生成：在辅助表B列用=","&A1下拉，复制B1:B511的内容，去掉开头的逗号后粘贴到CHOOSE的括号内，快速完成511个引用的录入。
3. 报表公式替换：将原VLOOKUP(<值>,INDIRECT(B2),FALSE)替换为：

   =VLOOKUP(<查找值>,INDEX(RangeReferences,MATCH(B2,AllRangeNames,0)),FALSE)
   

方法2：LET+XLOOKUP（适用于Excel 365/2021） #

利用Excel 365的动态数组特性，直接在单元格内完成逻辑封装：

=LET(
    rangeNames, {"MyRange1","MyRange2",..."MyRange511"},
    rangeRefs, {MyRange1,MyRange2,...,MyRange511},
    targetRef, INDEX(rangeRefs,XLOOKUP(B2,rangeNames,,0)),
    VLOOKUP(<查找值>,targetRef,FALSE)
)

其中rangeNames和rangeRefs的数组内容同样可通过辅助表批量生成后复制粘贴，无需手动逐个输入。

方法3：封装逻辑到命名范围（简化工作表公式） #

将匹配逻辑整合到一个命名范围中，让工作表公式更简洁：

1. 定义命名范围GetTargetRange，公式为：

   =INDEX(CHOOSE(ROW(INDIRECT("1:511")),MyRange1,MyRange2,...,MyRange511),MATCH(Sheet1!$B$2,{"MyRange1","MyRange2",..."MyRange511"},0))
   

2. 报表中直接使用：

   =VLOOKUP(<查找值>,GetTargetRange,FALSE)
   

性能优化提示 #

• 替换前先测试少量公式，确认响应速度提升后再批量替换（可通过查找替换功能，批量替换INDIRECT(B2)为对应的非易失性公式）。
• 确保所有命名范围均为有效引用，避免因错误引用导致公式报错或额外性能损耗。

内容的提问来源于stack exchange，提问作者Rodp