求无需VBA且自动刷新的Excel工作表动态列表解决方案

方案一：动态数组函数（适用于Excel 365/2021及后续版本） #

这种方法完全依赖原生函数，无需宏，添加新工作表时会自动更新列表。

操作步骤：

1. 切换到Overview工作表，在A1单元格输入以下公式：

   =TEXTAFTER(CELL("filename",INDIRECT("'"&SEQUENCE(SHEETS())&"'!A1")),"[",-1)
   

2. 按回车后，公式会自动溢出填充到A1:A4区域；后续添加新工作表时，列表会自动扩展并更新。

原理拆解：

• SHEETS()：返回当前工作簿的总工作表数量
• SEQUENCE(SHEETS())：生成从1到总表数的序列，用来遍历每个工作表
• INDIRECT("'"&SEQUENCE(SHEETS())&"'!A1")：引用每个工作表的A1单元格（仅用于获取文件信息，无需单元格有实际内容）
• CELL("filename",...)：返回包含工作表名的完整路径字符串，TEXTAFTER(..., "[", -1)提取最后一个[之后的工作表名称部分

注：即使工作表名称包含空格、特殊符号，公式也能正常工作，因为INDIRECT中的单引号已处理这类场景。

方案二：Power Query（适用于Excel 2016及以后版本） #

Power Query可以提取工作表名称，通过设置刷新规则实现自动更新，兼容性更广。

操作步骤：

1. 切换到数据选项卡，点击获取数据>从文件>从Excel工作簿，选择当前工作簿文件。
2. 在导航器界面，不要选择任何工作表，直接点击转换数据进入Power Query编辑器。
3. 在编辑器中点击主页>高级编辑器，替换原有代码为：

   let
       Source = Excel.CurrentWorkbook(),
       ExtractSheetNames = Table.SelectColumns(Source, {"Name"})
   in
       ExtractSheetNames
   

4. 点击关闭并上载，选择将数据加载到Overview工作表的A1单元格。
5. 设置自动刷新：
   • 右键加载后的表格，选择刷新>连接属性
   • 勾选打开文件时刷新数据，若需要定时自动刷新，可额外勾选每隔X分钟刷新

原理说明：

• Excel.CurrentWorkbook()：获取当前工作簿的所有工作表元数据
• Table.SelectColumns：仅保留工作表名称列，最终加载到Excel中就是纯净的工作表名称列表

两种方案对比：

• 动态数组方案：操作极简，添加新表即时自动更新，但仅支持Excel 365/2021及以后版本
• Power Query方案：兼容性更强，支持更早版本Excel，可自定义刷新时机，操作稍繁琐但可控性高

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