★ FEATURED ARTICLE Excel玩到一定程度总会遇到一个特别憋屈的需求我想快速拿到当前工作簿里所有工作表的名称做成一个目录或者下拉选项。常规做法是什么右键单击工作表标签一个一个重命名、复制、粘贴。如果工作簿里有二十个表咬咬牙也能干完但要是碰上那种一个工作簿拆了七八十个Sheet的项目表手动复制能让你怀疑人生。我之前在做一个多表汇总模板时就被这事卡过。后来在旧资料里翻到一个词宏表函数GET.WORKBOOK。试完之后拍大腿这东西真是藏在Excel角落里的宝贝。今天就把这个函数从原理到操作、再到各种应用场景和坑一次性讲透。1. 为什么翻遍函数列表就是找不到“取工作表名”这个功能1.1 宏表函数是什么来头GET.WORKBOOK不是普通的工作表函数它属于XLM宏表函数。XLM是Excel 4.0时代的宏语言比VBA还早。后来微软用VBA取代了XLM但这些宏表函数没有完全消失而是以“兼容模式”留在了Excel内部可以通过一种特殊途径继续调用。这就解释了为什么你在公式编辑栏里输入GET.WORKBOOKExcel会提示“函数无效”——因为它在常规函数列表里根本没有名字。它就像一个退居幕后但不肯退休的老员工职责还在只是不再出现在通讯录上。调用途径就是名称管理器。你在“公式”选项卡里点开“名称管理器”新建一个名称在引用位置里输入GET.WORKBOOK(1)Excel就会接受这个公式。之后在单元格里调用刚才定义的名称就能拿到完整的工作表名数组。1.2 GET.WORKBOOK函数的参数GET.WORKBOOK函数可以传两个参数格式是GET.WORKBOOK(type_num, name_text)第一个参数type_num是必填的它决定你要提取什么信息常用的几个值如下type_num返回内容1工作簿中所有工作表的名称水平数组4当前工作簿的文件路径5当前工作簿的名称6当前工作簿的保存状态13当前工作簿的摘要信息38当前工作簿中所有工作表的名称垂直数组用type_num1或者38返回的都是工作表名称数组。区别在于一个是横向排列一个是纵向排列。因为我们在单元格里填公式时数组需要配合INDEX或者新版的动态数组函数才能逐项显示所以横向还是纵向影响不大后面统一按type_num1来讲。第二个参数name_text是可选的。默认情况下省略表示返回当前工作簿的信息你也可以在引号里指定另一个已打开工作簿的名称这样就能跨工作簿获取对方的工作表名。1.3 为什么传统思路都绕远路了没有GET.WORKBOOK之前大家获取工作表名的常用方案不外乎三类用VBA写一个循环遍历Worksheets集合这是最正统的做法但对不熟悉代码的人来说门槛太高而且每次都要开宏编辑器写完还得考虑宏安全性设置。用CELL函数配合文件路径慢慢解析这种方案需要先把文件保存成带路径的格式然后用MID和FIND从路径字符串里一步步截取而且CELL函数只能拿到当前表格的信息换了工作表就变维护起来很麻烦。纯手动复制最笨但最可靠谁用谁知道累。宏表函数方案的最大优势是公式驱动、自动更新、不需要写代码。你定义好名称后它就是一组普通公式工作簿里新增或删除工作表刷新一下目录就跟着变。这种体验在搭建模板时非常爽。2. 第一次实操用GET.WORKBOOK做出会刷新的工作表目录2.1 定义一个名称拿到全部工作表名假设我现在手上有个工作簿里面有“销售汇总”、“华东”、“华南”、“华北”四个工作表。我想在“销售汇总”这个表里生成一个目录列出其他所有工作表的名称。第一步打开公式选项卡点击“名称管理器”在弹窗里点“新建”。名称我习惯取一个简单好记的英文名比如wbNames或者中文“所有工作表”也行。关键是“引用位置”这一栏必须手动输入GET.WORKBOOK(1)注意这里不要先去单元格里选区域也不要加任何$符号。直接输入这个公式点确定。第二步回到任意单元格输入INDEX(wbNames, ROW(A1))然后向下拖动填充。你会看到A1单元格显示“[工作簿名]销售汇总”A2显示“[工作簿名]华东”以此类推。所有工作表名称一次性倒出来了。为什么会显示成“[工作簿名]工作表名”这种格式因为GET.WORKBOOK返回的数组里每项都带工作簿名前缀用中括号括起来。这是宏表函数的原始输出格式后面第三节会专门讲怎么清洗。2.2 为什么必须配合INDEX使用这里有个很关键的原理问题GET.WORKBOOK(1)返回的是一个数组它不是单一的值。如果你在单元格里直接输入wbNames在旧版Excel里会报错因为单元格默认只能显示一个值放不下整个数组。在新版Excel 365里如果开启了动态数组输入wbNames会直接溢出到旁边的单元格里把全部工作表名都显示出来但显示的其实是一整片连续区域不是单格一个值。而INDEX函数的作用就是从数组里按位置取出第N个元素。ROW(A1)在向下填充时依次变成1、2、3……于是INDEX依次取出第一个、第二个、第三个工作表名。这种写法在旧版Excel和新版Excel里都通用兼容性最好。2.3 想要横向排列怎么办有人问过我不想竖着排能不能横着排可以把ROW(A1)换成COLUMN(A1)然后向右填充INDEX(wbNames, COLUMN(A1))COLUMN(A1)向右填充时依次是1、2、3……同样能按顺序取出。想要纵向用ROW想要横向用COLUMN这个技巧同时适用于其他类似场景比如INDEX取某一列的第N个值。2.4 名称管理器里的引用位置能不能写完整判断有人喜欢写得很复杂比如用IFERROR嵌套、用COUNTA限制行数等等。我的建议是名称里就保留最原始的GET.WORKBOOK把清洗和处理逻辑全部放到单元格公式里做。原因是名称管理器的公式很难调试一旦写复杂了出了问题排查起来非常痛苦。而在单元格里写公式有公式求值工具有逐级查看出错一眼就能看到是哪一步的问题。3. 把“[工作簿名]工作表名”变成干净的工作表名3.1 新版Excel一步搞定TEXTAFTER函数拿到“[工作簿名]销售汇总”这种格式后第一步就是去掉中括号前缀。如果你用的是Excel 365、Excel 2021或者WPS最新版可以直接用TEXTAFTER函数TEXTAFTER(INDEX(wbNames, ROW(A1)), ])TEXTAFTER的含义是提取指定分隔符之后的文本。这里指定“]”作为分隔符提取它后面的所有内容。比如“[项目进度.xlsx]销售汇总”用TEXTAFTER提取后就是“销售汇总”干净利落。如果你想把前后空格也清掉可以再套个TRIMTRIM(TEXTAFTER(INDEX(wbNames, ROW(A1)), ]))实际测试下来工作表名一般不会有额外空格但套上TRIM总归没坏处。3.2 旧版Excel的替代方案MIDFIND如果你的环境还是Excel 2016、2019没有TEXTAFTER也不用慌用最经典的三件套MID(INDEX(wbNames, ROW(A1)), FIND(], INDEX(wbNames, ROW(A1))) 1, 99)拆解一下FIND(], 原文)找到中括号右半边出现的位置加1就是工作表名的起始位置。MID从那个位置开始截取99个字符。工作表名最长是31个字符99完全够用。有一点容易踩FIND这里的“]”必须是英文半角符号不能写成中文全角“】”。GET.WORKBOOK返回的中括号是英文的写错了就找不到位置公式直接报错。3.3 特殊工作表名惹的祸如果你的工作表名称里带了空格比如“华东 数据”这种GET.WORKBOOK返回的数组项可能是[工作簿.xlsx]华东 数据注意工作表名会被一对单引号包起来。这种设计是为了后续能把它直接当引用使用防止带空格的名称在公式里被拆开。但你提取清单时这对单引号就变成多余的内容了。处理方法在TEXTAFTER之后把单引号替换掉。新版可以用SUBSTITUTES旧版就用SUBSTITUTE嵌套SUBSTITUTE(TEXTAFTER(INDEX(wbNames, ROW(A1)), ]), , )如果是旧版MID方案就再多套一层SUBSTITUTESUBSTITUTE(MID(INDEX(wbNames, ROW(A1)), FIND(], INDEX(wbNames, ROW(A1))) 1, 99), , )另外提一句有些繁体版本或者特定区域设置的Excel返回格式可能有差异。建议拿到结果后先在几个不同命名的表上试一下确认返回的实际格式再写清洗公式。3.4 顺手做个动态序号工作表目录通常需要序号列。序号可以用最普通的ROW(A1)也可以更智能一点让序号跟着工作表总数变。比如先定义一个名称COUNTA(wbNames)然后序号写IF(ROW(A1)工作表总数, ROW(A1), )这样多了个判断让序号在超出工作表数量后自动隐藏目录不会显得空荡荡。不过说实话对于大多数场景我更喜欢直接填充到足够行数然后用IFERROR把多余部分变成空。简单直接不绕弯。4. 用GET.WORKBOOK的时候这几件事一定要提前知道4.1 文件必须保存成xlsm真相没那么吓人很多教程提到宏表函数就警告说“必须保存为启用宏的工作簿”这个说法并不准确。实际测试如下文件格式能否使用GET.WORKBOOK备注.xlsx可以使用但另存时Excel提示“工作簿包含宏表函数”关闭再打开后公式可能丢失.xlsm可以使用稳定保留推荐格式.xls旧版可以使用兼容性尚可但建议迁移.xlsb可以使用二进制格式打开速度快实操中最稳妥的方案是一旦用上了GET.WORKBOOK直接把文件另存为.xlsm格式。这样下次打开不会弹警告公式也能完整保留。别嫌格式带个“宏”字这里并没有实际写VBA代码只是Excel识别到宏表函数后给出的格式建议。4.2 目录不会自动刷新这个坑每天都能踩到我最早做模板时遇到过一个诡异现象我在工作簿里新增了一个工作表结果目录里的名单死活不动还是缺了一个。原因在于GET.WORKBOOK属于宏表函数它的计算结果不会像普通公式那样在工作表结构变化后立刻触发自动重算。它其实能感知变化但需要你先触发一次计算。解决办法有两个一是手动刷新按CtrlAltF9这是强制重算整个工作簿目录瞬间就会更新。如果只想重算当前表按F9也行。二是在工作表结构变更后随便编辑任意单元格再回车Excel会自动触发一次计算链宏表函数的结果也会跟着刷新。我做模板时干脆在“工作表目录”这个页上放一个说明文字“新增或删除工作表后请按F9刷新目录”。虽然有点土但真的能少很多迷惑时刻。4.3 公式被禁用谈谈宏安全设置有些公司电脑的安全策略较严打开文件时会禁用宏或者禁用宏表函数。具体表现是单元格里显示#NAME?或者#VALUE!名称管理器里也能看到定义但结果就是算不出来。遇到这种情况先检查Excel左下角的“安全警告”条。如果提示“宏已被禁用”点“启用内容”。但因为没写VBA只是宏表函数很多环境并不会弹这个警告那问题可能出在加载项或兼容设置上需要在“信任中心”里把“启用所有宏”勾上前提是你自己控制文件来源别乱开别人的文件。这里有个个人建议这种公式风格比较“老派”如果是给客户或者不懂Excel的人发模板最好在交付时说明一下来源并附上刷新说明。否则对方打开一看目录是错的第一时间就会觉得模板有问题。4.4 跨工作簿引用的注意事项前面提过GET.WORKBOOK可以传入第二个参数指定工作簿名称比如GET.WORKBOOK(1, 另一个工作簿.xlsx)这个功能听起来很香但实际限制不少。要求目标工作簿必须处于打开状态而且名称要和窗口标题完全一致。如果对方文件在磁盘上没打开公式只会返回#REF!。我的经验是跨工作簿获取工作表名这种需求老老实实用VBA或者最新版的VSTACKHSTACK方案更省心宏表函数第二次参数适合在一个工作簿内部引用另一个已加载的插件表时使用通用性一般。5. 把这些工作表名用起来三个高频实战场景5.1 场景一把“工作表目录”变成超链接导航拿到了干净的工作表名直接在目录页做超链接跳转体验就像给工作簿加了个首页。A列放序号B列放工作表名C列放超链接HYPERLINK(#B2!A1, 跳转)注意如果工作表名没有空格可以不加单引号但为了统一处理特殊字符最好在引用时用单引号包一圈。这个公式拼出来的链接格式是#华东 数据!A1在工作簿内部跳转时井号开头代表当前文件。只要你B列的工作表名正确点击“跳转”就能直接切到对应的表非常顺手。5.2 场景二配合INDIRECT动态汇总每个表的数据工作表目录最常见的延伸用法是汇总各表数据。比如每个门店一张表每张表的B2单元格放当月销售额现在要汇总所有门店的销售额。假设目录页的A列已经是清洗后的工作表名汇总公式可以写成SUM(INDIRECT(A2!B2))INDIRECT把文本“华东!B2”转成真正的引用于是它就会去华东表里取B2的值。加点思路还能把求和范围扩展成一整列SUM(INDIRECT(A2!B:B))这种写法的妙处在于目录里新增一行工作表名汇总表就自动多一个门店不用每次新开门店后改公式。要注意的是INDIRECT是易失函数工作簿一旦动过单元格就会重算。门店特别多、数据特别大时文件会有一点卡顿。如果卡得明显可以评估改用VBA数组一次读入内存求和但绝大多数场景下INDIRECT够用了。5.3 场景三生成一份“动态工作表清单”下拉选项Pivot表、图表、数据验证之类的地方经常需要“从多个工作表里选一个”这种操作。假设你已经用GET.WORKBOOK把工作表名清洗到了某个区域比如目录页的A2:A10那么可以在数据验证里设置来源为OFFSET(目录!$A$2,0,0,COUNTA(目录!$A$2:$A$100),1)这样下拉菜单会根据A列已有内容的行数自动伸缩。新增一个工作表并在目录里刷新后下拉菜单也会跟着多一个选项完全不用手动改数据验证的范围。还有一个更花哨的做法如果把清洗后的工作表名定义成名称“表名列表”然后数据验证来源直接填“表名列表”逻辑上一样但看起来更简洁。名称引用建议改成OFFSET(目录!$A$2,0,0,COUNTA(目录!$A$2:$A$100),1)这样名称引用的就是动态区域而不是固定区域下拉选项数量永远正确。5.4 顺带提一嘴GET.WORKBOOK(4)的隐藏用法既然聊到宏表函数有一个被忽视的兄弟函数也值得知道GET.WORKBOOK(4)返回当前工作簿的完整保存路径。比如你在A1输入通过名称管理器定义的名称为wbPath公式GET.WORKBOOK(4)wbPath它能直接显示“D:某文件夹某项目.xlsx”。这个在需要输出文件存放位置的报表表头里特别实用配合CELL函数可以组合出更多路径信息。这里需要提个醒GET.WORKBOOK(4)返回的是最后一次保存时的路径如果你用“另存为”改了名字或位置没保存前它显示的仍是旧路径。同样需要触发重算后才会更新。写在最后的个人体会GET.WORKBOOK算是我遇到的Excel“知道的人不多但知道以后离不开”的典型函数。它最大的价值不在于单次获取列表这个动作而在于提供了一个活的、可刷新、可嵌套的工作表清单源。只要把它定义成名称放在一边后面无论是做超链接目录、动态汇总、下拉菜单还是配合其他函数做更复杂的自动化模板都不用再手动维护工作表名列表了。我在实际项目里最常用的是场景一和场景二组合一个工作簿左边是带超链接的首页目录右边是汇总区域中间用INDIRECT串起来。新同事接手后不用问我每个表在哪里、公式怎么改因为目录自动会刷新公式自动会算。这种“一次搭好、长期省事”的感觉就是Excel模板真正的价值所在。最后再分享一个小技巧当你调试GET.WORKBOOK相关公式时先用一个空工作簿建两个工作表分别命名成“测试A”和“测试B”把公式跑通了再移植到正式模板里。这样能避开很多因为工作表名特殊字符导致的意外问题也方便你观察GET.WORKBOOK到底返回了什么格式。等你见过几次返回结果的样子后面无论遇到带引号、带空格还是带括号的工作表名都能一眼判断该怎么清洗了。
阅读完成 · 觉得有帮助？
