41 Excel中快速为全年的生产日报创建目录

最近接到一个生产主管的问题，他把今年（2022）的生产日报表Excel版本发给老板后，老板说一个一个点开查看太麻烦了，要他把这个报表创建一个目录链接起来，这样就方便多了；他说他搞不定，文件太多了；

接收到文件后，我发现这个生产日报和大多数中小工厂的格式都差不多，在录入Excel的时候，就没有注意数据格式的规范性，到想要统计分析的时候就发现很困难。

大概看了一下报表，有以下几个问题：

1. 每天一个工作表，创建了几百个工作页面；

2. 每个页面的格式也不一定相同，关键是无规律；

3. 数字与文本错误特别多（不固定）

4. 无效的空行空列

5. 其它问题

以上的问题，等到录完在整理的时候已经太晚了，所以古哥也很难解决这类没有特定逻辑规律的数据，只能说帮他创建一个目录加上超链接，快速找到对应哪一天的生产日报；

01 创建目录：

创建目录的话，用一些第三方软件是最快的，但是，有些中小工厂可能连外网的权限都没有，更别说安装第三方软件了，加上第三方软件一般都要额外的费用，所以古老师还是用Excel本身能够有的函数来搞定创建目录；

创建目录的第一步就是想办法把这几百张工作表的名提取到一个新建页面作为目录，这一步完成后，再把超链接加上去；

Excel中提取工作表名的方法有单一法和批量法，单一法适合工作表1张的或者较少的，批量法适合比较多的工作表；

02 单一法：

单一法：用函数CELL加上文本提取函数即可,我们录入函数：=TEXTAFTER(CELL("filename",A1),"]")，就可以把这张工作表名提取出来；

函数原理：用CELL函数中取出A1的位置，会返回这个工作簿的存储位置“E:\测试文件\[生产日报.xlsx]1.2”，再用TEXTAFTER函数提取 “]”后的信息，也就是文本名字，老版本没有这个函数可以换成：

“=MID(J1,FIND("]",CELL("filename",A1))+1,100)”

03 批量法：

单一法的缺点也比较明显，就是只能一个工作表一个工作表提取，当工作太多的时候就需要用批量法，批量法相对来复杂一点，但只要记住一个名称就加一个函数就可以了；

第一步：新建一个页面，命名为“目录”,定位在单元格A1的同时按下Ctrl+F3，弹出定义名称，输入“tiqu”,引用位置录入：“=GET.WORKBOOK(1)”

第二步：A1录入标题名称“序号”，A2录入公式：=SEQUENCE(365)，表示自动生成365行的序号，代表365天；低版本可在B2用=ROW（A1）下拉填充替代；

第三步：用函数INDEX来引用定义的名称，在C2录入函数：=INDEX(tiqu,A2#)，自动提取所有工作表名(动态数组)；也可以一步到位的公式：=INDEX(tiqu,SEQUENCE(365))，如没有365张工作页，为了避免错误，再加上=IFERROR(INDEX(tiqu,SEQUENCE(365)),"")就可以把错误值返回空值；

第四步：到了第三步已经只差一点点了，最后用文本函数把工作表名提取出来就可以了。

新版本：=IFERROR(TEXTAFTER(INDEX(tiqu,SEQUENCE(365)),"]"),"")

旧版本：=IFERROR(MID(INDEX(tiqu,ROW(A2)),FIND("]",INDEX(tiqu,ROW(A2)))+1,100),"")

到这里，已经把目录全部自动创建好了，就差超链接了，明日继续分享；

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！23年古哥特训全程班开始预报名招生，23年和古哥一起全方位学习计划运营知识