第24讲 · 系统级技巧：自动生成工作表目录

发布于 2026-09-29 · 更新于 2026-09-28T16:47:55.790Z

📌 阶段六 · 批量输出与自动化 · 第24讲宏表函数 · 约 35 分钟

用宏表函数 GET.WORKBOOK 给进销存工作簿做一张可点击的导航目录

学习目标

理解宏表函数的使用前提：不能直接写在单元格里，要放进定义名称中
会用 GET.CELL 提取单元格的填充色和公式文本
会用 GET.WORKBOOK(1) 取出工作簿全部表名，并用 INDEX 逐个显示
会用 HYPERLINK 把表名变成可点击的跳转链接
为进销存工作簿生成目录导航，并对照「成品系统」完成期末大作业自查

知识点讲解

什么是宏表函数：老前辈的新用法

宏表函数是 Excel 4.0 时代的一批老函数，比如 GET.CELL、GET.WORKBOOK、EVALUATE。它们比 VLOOKUP 这类现代函数年纪大得多，脾气也特别：不能直接在单元格里单独运行，必须先写进「定义名称」里，再在单元格中调用这个名称。

用法套路永远是两步：①右击目标单元格（或公式-定义名称）新建名称，在引用位置写宏表函数；②在单元格输入 =名称来取结果。

💡 提示:

宏表函数属于宏功能，文件建议另存为启用宏的工作簿（.xlsm）格式，避免重开文件后名称失效

如果宏表函数在普通 .xlsx 里新建时被拦截，把文件转为 .xlsm 再操作

GET.CELL：提取单元格的信息

GET.CELL(type_num,reference) 返回引用单元格的信息，第一个参数是信息类型编号，范围 1～66，每个数字代表一种信息；第二个参数是要查询的单元格。

两个常用编号：63 返回单元格的填充（背景）颜色值；6 返回单元格里的公式文本。

实操一（提取颜色）：先定义名称「计算颜色」，引用位置写 =get.cell(63,A2)；再到 B2 的编辑栏输入 =计算颜色，B2 就显示 A2 的背景色编号。按颜色筛选、按颜色统计都靠它开路。

实操二（提取公式）：定义名称「提取公式」，引用写 =get.cell(6,D2)，然后在 E2 输入 =提取公式，D2 里的公式就原样显示出来了。

=get.cell(63,A2)

放进定义名称「计算颜色」的引用位置：返回 A2 的填充色编号，单元格再用 =计算颜色调用

=get.cell(6,D2)

放进定义名称「提取公式」：返回 D2 单元格的公式文本

=FORMULATEXT(D2)

Excel 2013 及以上版本可以直接用这个新函数提取公式文本，效果同 get.cell(6,…)，无需定义名称

GET.WORKBOOK：把所有表名抓出来

GET.WORKBOOK(type_num, name_text) 用来提取工作簿本身的信息：type_num 指明要哪类信息；name_text 可选，是工作簿名，省略时默认当前工作簿。

本讲主角是 =get.workbook(1)：返回工作簿中所有表的名字。

实操：任意单元格右击-定义名称，名称写「工作表名」，引用位置输入 =get.workbook(1)。然后在单元格里输入 =工作表名——你会发现只显示了第一个表名。别急，点一下编辑栏按 F9 刷新就能看到，它其实是个数组，只是单元格只挑第一个值显示。

正确取法是用 INDEX 逐个拿：=INDEX(工作表名,1) 取第一个，或直接 =INDEX(工作表名,ROW())，下拉时 ROW() 递增，表名一个接一个全出来。（新版 Excel 里输入 =工作表名会自动把整串表名「溢出」显示到下方，原理相同。）

=get.workbook(1)

放进定义名称「工作表名」的引用位置：返回本工作簿全部工作表名组成的数组

=INDEX(工作表名,ROW())

ROW() 返回当前行号，下拉时依次取数组第 1、2、3……个元素，表名清单自动展开

💡 提示:

单元格里输入 =工作表名只显示第一个表名是正常现象——它本来就是数组，要用 INDEX 取

HYPERLINK：让目录可以点击直达

表名列出来了，还想点一下就跳过去，用 HYPERLINK 函数：HYPERLINK(link_location, friendly_name)，第一个参数是要打开的路径或地址（本地文件、UNC 路径、URL 都行），第二个参数是单元格里显示的跳转文字，省略时直接显示第一个参数。

单独用法：=HYPERLINK("http://www.163.com","网易")，单元格显示「网易」，点击打开网页。

放进目录：=HYPERLINK(INDEX(工作表名,ROW())&"!A1")——把 INDEX 取出的表名拼上 "!A1"，点击就跳到那张表的 A1。目录导航从此不是装饰，是真入口。

=HYPERLINK("http://www.163.com","网易")

最简用法：显示「网易」，点击打开网址

=HYPERLINK(INDEX(工作表名,ROW())&"!A1")

目录写法：表名来自 GET.WORKBOOK 数组，拼上 !A1 作为跳转目标，点击直达对应工作表

EVALUATE 与 SUBSTITUTE：算文本里的算式

EVALUATE(formula_text) 能把一段文字表达式真的算出结果：单元格 A3 写着 "3*5+2"，定义名称「运算」引用 =evaluate(A3)，再在 B3 输入 =运算，回车得到 17。

遇到带逗号的算式（如 "90,88,95" 想求和）就先请第 16 讲的 SUBSTITUTE 替换文本：SUBSTITUTE(text,old_text,new_text,[instance_num])，第四个参数指替换第几处，省略则全换。定义名称引用 =EVALUATE(SUBSTITUTE(A9,",","+"))，把逗号换成加号再运算。

另一种思路：在 C9 拼 ="{"&A9&"}" 得到 {90,88,95}，再定义名称 =EVALUATE("{"&A9&"}")，单元格用 =SUM(计算) 求和——直接 SUM 不行，是因为那个拼接结果被 Excel 当成了文本而不是数组，用 EVALUATE 转一道就成真数组了。

=evaluate(A3)

放进定义名称「运算」：把 A3 里的文本算式当公式计算，返回数值结果

=EVALUATE(SUBSTITUTE(A9,",","+"))

先把 A9 里的逗号替换成加号，再交给 EVALUATE 计算

=SUBSTITUTE(text,old_text,new_text,[instance_num])

文本替换函数：把 text 里的 old_text 换成 new_text；第四参数指定只换第几处，省略则全部替换

实战演练

最后一讲，给整套进销存系统装上「总入口」：一张自动生成的目录页，列出工作簿里全部工作表，点表名直达。同时这也是全部课程的收官——做完这张目录，就去看课程提供的「成品系统」，对照它完成期末大作业的最后一轮自查。

建目录页 — 打开你的进销存工作簿（或本讲练习数据所在工作簿），新建一张工作表，拖到所有表的最前面，A1 合并几个单元格写大标题「进销存系统目录」，A2 写副标题「点击表名直达对应工作表」。
定义名称抓表名 — 公式选项卡-定义名称：名称输入「工作表名」，引用位置输入 =GET.WORKBOOK(1)，确定。如果弹出宏相关提示，按提示启用；文件建议另存为 .xlsm 格式以保留宏表函数。
展开表名清单 — A4 输入 =INDEX(工作表名,ROW()) 并下拉若干行。如果发现显示的全是第一个表名，记住那是「只显示第一个值」的假象，INDEX 加 ROW() 就是逐个取值的正解；若目录从第 4 行才开始，可把公式调成 =INDEX(工作表名,ROW()-3) 对齐序号。此时每个表名前会带着「[工作簿名]」前缀，下一步清洗。
清洗表名前缀 — B4 输入 =MID(A4,FIND("]",A4)+1,50)，用第 16 讲的 MID+FIND 把「]」之后的纯表名截出来，下拉。这列就是干净的工作表名清单：使用说明、商品档案、供应商档案、客户档案、入库单、出库单、库存台账、应收账款、销售分析、采购计划、仪表盘、目录……
做成可点击导航 — C4 输入 =HYPERLINK(INDEX(工作表名,ROW())&"!A1","点击进入")，下拉到底。也可以把 B4 直接换成跳转版：=HYPERLINK(INDEX(工作表名,ROW())&"!A1",B4)——显示表名、点击跳转。做完点几个链接试试，应该一张表都不用找，直达目的地。
美化定稿 — 给目录表套上第 2 讲学的格式：标题行底纹、清单区加边框、冻结前几行；再把字体调大些，做成真正的系统首页。保存工作簿（.xlsm），以后加新表只需把目录公式多下拉一行，目录自动收录。
收官：对照成品系统做期末自查 — 24 讲的零件到这里全部讲完。现在前往课程网站的「成品系统」页，用 Univer 全屏打开完整进销存工作簿作为期末大作业参考：10 张表联动、SUMIF 自动台账、条件格式预警、采购甘特图、销售占比图、KPI 仪表盘一应俱全。逐张对照检查你的工作簿：缺哪张表补哪张，公式对不上的回到对应讲次复习——把它完整复刻并改进，就是这门课的期末答卷。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

配套练习文件:lecture-24.xls、进销存成品系统.xlsx(见本页底部「附件下载」)。

验收清单

逐项自查,全部通过即完成本讲:

☐ 能说出宏表函数的使用前提：必须写进定义名称，再在单元格用 =名称调用
☐ 目录页能列出工作簿里全部工作表名（=GET.WORKBOOK(1) + INDEX 逐个取）
☐ 每个表名可以点击直达该工作表（HYPERLINK 已生效）
☐ 理解 =工作表名只显示第一个表名是因为它是数组，会用 INDEX(工作表名,ROW()) 展开
☐ 工作簿已另存为启用宏格式，目录表美化完成并放在首位
☐ 已前往「成品系统」页查看完整进销存系统，完成期末大作业的最后对照自查

🔄 第24讲 · 系统级技巧：自动生成工作表目录