文 / 小鱼儿 编辑 / 小鱼儿 雷哥收到一份来自老板的任务,内容如下,你敢不敢挑战下? 案例:给公司几百名员工分别建档:以每个员工的名字建一个工作表,内含各自的年终奖情况,而且还要方便、快速地找到每个员工的工作表。 雷哥手里有一份EXCEL文件,如上图所示。但是这个EXCEL文件只有一个汇总工作表,含员工姓名及年终奖汇总情况,难道要一个个新建工作表(sheet)并按照各员工姓名重命名吗?sheet! sheet! sheet!......是不是想想就要累晕在起跑上?别急,遇事先别慌,方法很重要!雷哥有办法! 借助透视表,批量新建指定名称的上千个工作表不是难题!Step1:点击数据区域中的任意单元格,点击【插入】-【数据透视表】,点击【确定】; Step2:在透视表设置选项里,将【姓名】及【年终奖】均拖到【筛选】项里,另外将【年终奖】拖到【求值】项里; Step3:点击工作表里的筛选信息处,比如【姓名】处,来激活【筛选透视表工具】; Step4:选择【筛选透视表工具】-【分析】-【选项】下拉按钮,选择【显示报名筛选页】,选中其中需要指定命名的【姓名】,也就是按照“姓名”给每个工作表(sheet)进行命名; Step5:点击【确定】即可,这样,就产生了若干个按照汇总表中的各员工姓名来命名的工作表。 详见动图。 同时,每个工作表的年终奖数值也是该员工的年终奖数值奥(已经按照姓名筛选完各自的年终奖)~ 拓展:每月考核,每日考勤录入等工作表的新建,都可以用这种方法来快速批量处理~例如,想要新建12个月的信息表(1月,2月,3月……),就可以借助透视表,快速新建12个工作表并批量命名! 想看哪个工作表,轻轻一点,自动跳转链接so easy!我们已经新建了以各员工姓名命名的若干多个工作表了,但是这么多表格,找到其中某个员工的工作表谈何容易?!别着急,雷哥告诉你如何一秒快速查找某个工作表! Step1:上图为例,C2处输入公式=HYPERLINK('#'&A2&'!A1',A2),回车,点击该C2单元格,即可链接到“Robin”工作表的A1单元格,详见动图。详细分析见后文。 Step2:再返回总表继续处理,将鼠标放在总表C2单元格右下角处,当单元格右下角变成“+”形时,双击即可批量自动填充,所有超链接完成,详见动图。 拓展分析(Step1的函数):该公式理解起来可能有点费力,可以直接套用,记住'A2'是变动的即可,这里的A2也就是需要自动跳转的内容所在的单元格,比如本例中,先要跳转“Robin”,而“Robin”就是“A2”单元格的内容,公式简单翻译过来就是,跳转到本工作簿中“Robin”工作表的A1单元格,跳转链接命名为“Robin”(函数的参数2)。 有兴趣的朋友可以学习下本函数含义:HYPERLINK(link_location,[friendly_name]),有超链接的功能,第1个参数是要超链接到的位置(文档的路径、文件名或指向文档中的某个更为具体的位置),参数2是超链接命名,有提示说明功能,当然也可忽略。 其中,“#”表示在本工作簿,“&”是连接符,连接符的前后文本符号需要用英文双引号“”括起来;第一个“A2”表示要跳转的工作表,也就是以A2单元格内容命名的工作表,此处是“Robin”工作表,“!A1”表示前面工作表下的A1单元格,也就是定位到“Robin”工作表的A1单元格。 进一步人性化,从每个分工作表快速返回总表!在上面操作时,我们还会进一步发现一点小问题,那就是总表中点击链接进入某个员工工作表之后,如果想再查看其它工作表,需要返回总表进行查看,那如何快速从分表返回到总表呢? 其实,继续利用HYPERLINK函数就能解决这个问题! Step1: 点击第一个员工的工作表“Robin”,然后按住“SHIFT”键,找到最后一个员工的工作表,这样就能选中全部员工工作表; Step2: 在当前工作表合适的单元格处输入公式=HYPERLINK('#'&'雷哥office员工总表'&'!A1','点击链接:返回雷哥office员工总表'),此时再按住【CTRL+回车键】,进行批量输入公式,这样每个员工的分表中,都在同一位置加入了超链接,均能返回总表,详见动图。
这样,一个非常人性化的员工数据库就建成啦,是不是很简单呢?学会这招,再也不用一个个命名工作表,又一个个复制粘贴啦,效率大大提高,点击总表进入分表,点击返回总表链接又能快速返回,老板不夸你才怪呢~~~赶紧操练起来吧! 拓展思考:=HYPERLINK('#'&'雷哥office员工总表'&'!A1','点击链接:返回雷哥office员工总表'),这个函数是什么意思,聪明的你,是否知道呢? |
|
来自: 阿白mvo3hep7cv > 《Excel攻略》