表太多了，我自己分不清了，怎么办？
下面把一些常用的做目录方法全部写出了，由浅入深的来细数做目录那些事
1. 超链接公式做
现在我有四个表（简单举个例子），爱知趣四名员工的工资情况。用HYPERLINK函数来写，因为没办法确认工作表名字变化，我们用一个变通的办法，把工作表名字改为1，2，3，4
然后，我们序号就是工作表名字了，写公式
=HYPERLINK("#'"&B7&"'!a1",INDIRECT("'"&B7&"'!c2"))
简单介绍下这个公式：HYPERLINK(超链接路径，单元格里显示名字)
"#'"&B7&"'!a1"：第一参数结果其实是 #’1’!A1 ,注意的是，为了这种工作表的写法，最好都要在表名字前面加单引号，这是一个好习惯，防止名字里有特殊字符报错
INDIRECT("'"&B7&"'!c2")：第二参数就是INDIRECT(’1’!c2) 得到第一个表里c2的内容
但是我们发现，没有的工作表会错误，前面加IFERROR来容错，修改为：
同样的，总工资哪里做公式：
=IFERROR(HYPERLINK("#'"&B4&"'!a1",INDIRECT("'"&B4&"'!f2")),"")
搞定了，以后自己增加工作表，名字自己按照顺序编号，自动变化。
2. 搭配宏表公式
上面小伙伴说，那样不对啊，我不想要编号，我已经好多表了，我就要原来的名字怎么办？
要用宏表函数get.workbook
EXCEL里不能直接使用，要自定义名称
起名字叫sh
sh就得到了全部的工作表名称数组
利用index的数组用法，可以拿出单个的名称
这结果，不太满意，我们想要的是 ] 符号后面的数据，所以公式改下
=MID(INDEX(sh,ROW(目录!A2)),FIND("]",INDEX(sh,ROW(目录!A2))) 1,99)
就是找到 ] 符号位置，然后提取后面内容出来（这里我们要从第二个工作表开始，所以，改为A2注意下）
我们鼠标点中c4单元格，把这个函数写入自定义名称，叫name
剩下的和第一个就一样了
=IFERROR(HYPERLINK("#'"&name&"'!a1",name),"")
=IFERROR(HYPERLINK("#'"&name&"'!a1",INDIRECT("'"&name&"'!f2")),"")
最后，一定不要忘记，另存为格式为XLSM，因为get.workbook是宏表公式
3. 利用错误生成目录
这个方法就特别秀了，据说来自法立明老师的方法，秀上天了。我们来实际操作
选中第一个你要生成目录的表，按住shift，选最后一个表
在没有数据的地方，我这里选A1单元格输入公式=A65537
另存为工作表为xls格式的表格，这个是2003格式的表格，最大行只有65536
这个时候会弹出一个检查窗体
点击复制到新表
生成一个检验报告，框红线的就是发生错误部分（只有65536行数据，a1我们写的公式超过这个范围了）
删除不要的整理下格式
通过找符号 ‘ 就能得到工作表名字，名字知道了。
4. 利用VBA来解决
这个方法是最灵活的，有兴趣的小伙伴，可以自己研究下。
直接上代码
| Sub test()
Dim sh As Worksheet, n& Range("b4:e65536").ClearContents '清空原来的数据 For Each sh In Sheets '遍历工作表 If sh.Name <> "目录" Then '工作表名字不是目录 n = n 1 '计数器 Cells(n 3, "b").Value = n '序号 Cells(n 3, "C").Value = sh.Name '姓名 Cells(n 3, "d").Value = sh.Cells(2, "F").Value '工资 '插入超链接 ActiveSheet.Hyperlinks.Add Anchor:=Cells(n 3, "C"), Address:="", SubAddress:= _ "'" & sh.Name & "'!A1", TextToDisplay:=sh.Name End If Next End Sub |
如果不会使用，可以忽略不看了
5. 用power query来生成目录
这个方法要求的就比较多了，必须使用office2010以上版本，其中2010和2013要另外安装插件
选中数据，自文件，从工作簿，选择做目录的文件，就是自己
点击导入
随便选一个工作表，点击转换数据
这两个数据删除了
筛选不要的数据
删除其他列
点击关闭并上载
你就得到一个工作表名字的表
总结
我们用了5个方法来做目录
1，单纯公式做的，好处wps，office都能用，兼容性好，缺点数据多了会卡，工作表名字不能随便起
2，配合宏表函数用，要用自定义名称，最后要另存为xlsm格式，缺点数据多了会卡
3，利用错误报告来生成目录，优点简单操作，自动生成，缺点，一次操作，不能自动更新
4，vba代码，优点灵活方便，diy随便弄，只有想不到的，没有做不到的，缺点学习成本高，给别人使用还得教学。。。。
5，pq生成表名字，这个方法没有优点，能做还不如第二个方法，介绍一个知识点吧
评论 (0)