Excel目录自动生成怎么弄,Excel VBA 自动生成目录

题图来自Unsplash，基于CC0协议

导读

在Excel中生成动态更新的目录，常见的方法有公式法和VBA编程法：

公式法（适用于单一表或连续编号目录）

1. 使用SUBSTITUTE函数逐级替换标题文本，配合IF或CHOOSE函数实现多级标题目录。

   =MID( SUBSTITUTE(SUBSTITUTE(A2,"标题1>","1."),1),FIND("标题2.","1.")+2) 

   需要根据标题层级嵌套多个SUBSTITUTE函数，再用TEXTJOIN/PHONETIC辅助提取有效目录。

2. 利用GET.WORKBOOK&MID组合获取工作簿所有工作表名称：

   =TEXTJOIN("\";",TRUE,IF(GET.WORKBOOK(1)>=65536, : , "Sheet "&ROW(1:1)))

VBA法（更强大的目录生成功能）

Function GenerateTOC(Separator As String) As String
    Dim shp As Shape, i As Long
    On Error Resume Next
    For Each shp In ActiveDocument.Shapes
        If Left(shp.Name,6)="标题 "Then GenerateTOC = GenerateTOC & shp.TextFrame.TextRange & Separator
    Next shp
End Function

调用方法：=GENERATE_TOC("-")

这两种方法均可实现自动目录更新效果，前者适用于单工作表多层级标题采集，后者支持文档级别目录处理；建议普通用户优先选择公式法，VBA法需要先启用宏并注意文件加密保护。