提取Excel中所有工作表名称

我的人生宝库 2020-04-25

展开全文

主持人：Excel工具是一款很实用的Office办公软件之一，在各个行业领域都能发挥其强大的数据处理作用。然而实际工作中很多人硬生生的把Excel“玩”得让人“哭笑不得”，也同样留给职位交接人一个“烂摊子”。面对不规范的数据源，不得已使用“难以理解”的复杂Excel函数嵌套解决！如图所示

本期素材案例要求：

根据供应商明细表中的到货时间汇总每个月的应付账款余额

本期素材案例：

链接：https://pan.baidu.com/s/1I3vbo55WgXfbazbxmBLFYw

提取码：lmlm

复制这段内容后打开百度网盘手机App，操作更方便哦

讲解员：本期我们邀请牛先生分享用GET.WORKBOOK宏表函数生成工作表名称，通过生成的工作表名称进行跨工作表引用数据汇总

庖丁解牛：同学们，你们准备好了吗？跟上节奏我们要开始了！

Tip1：依次点击“公式”选项卡→“定义名称”→弹出“新建名称”对话框→输入自定义名称“工作表名称”→“引用位置”编辑公式=GET.WORKBOOK(1)→点击“确定”

说明：

GET.WORKBOOK宏表函数是专门用来提取Excel工作簿中的信息数据

GET.WORKBOOK(1)表示返回工作簿中所有工作表名称（返回以水平数组形式表示的所有当前工作表名称）

GET.WORKBOOK(3)表示返回工作簿中当前工作表名称

GET.WORKBOOK(4)表示返回工作簿中工作表个数

GET.WORKBOOK(38)表示返回活动工作表名称

Tip2：在A列右侧插入“辅助列”，尝试着在B2单元格编辑公式=工作表名称，返回结果为[微信公众号第28期.xlsm]汇总，观察发现“]”后的“汇总”就是我们将要提取的工作表名称，因此重新在B2单元格编辑公式=IFERROR(REPLACE(INDEX(工作表名称,ROW(A2)),1,FIND(']',INDEX(工作表名称,ROW(A2))),'')&T(NOW()),'')

公式解析：

①INDEX(工作表名称,ROW(A2))表示提取第二张工作表名称

②FIND(']',INDEX(工作表名称,ROW(A2)))表示查找提取的工作表名称中“]”文本所在字符串的位置

③REPLACE(INDEX(工作表名称,ROW(A2)),1,FIND(']',INDEX(工作表名称,ROW(A2))),'')表示将提取的工作表名称中含“]”所在位置之前的文本全部替换为空

④T(NOW())表示当修改工作表名称、增加或删除工作表时，公式能够自动更新计算

Tip3：在C2单元格编辑公式=IFERROR(ROUND(SUMPRODUCT((C$1=MONTH(INDIRECT($B2&'!$p$2:$p$999'))&'月')*(INDIRECT($B2&'!$p$2:$p$999')<>''),INDIRECT($B2&'!$s$2:$s999')),2),0)