excel工作簿和工作表技巧(关于Excel工作簿的3个神级操作)
今天技巧妹跟大家分享有关于工作簿的3个高效操作,非常实用,赶紧Get!
1、跨工作簿查询
如下图所示,1到6月份的销售数据明细都是单独的一个工作簿,我们如何实现数据的查询引用呢?
这里我们可以利用公式:
=IFERROR(VLOOKUP($B2,INDIRECT("["&C$1&".xlsx]"&C$1&"!B:C"),2,0),""),
往右往下填充。
解析:比如查找的是张夏晗1月份销售业绩,可以输入函数公式=IFERROR(VLOOKUP($B2,'[1月.xlsx]1月'!$B:$C,2,0),""),这里结合INDITECT函数,实现多个工作簿数据区域的动态引用。
注意:利用VLOOKUP函数跨工作簿查询引用,需把所有关联工作簿全部打开。
2、合并多个工作簿
如下图所示,需要将多个工作簿的内容合并在一起。
这里我们可以利用Power Query功能,点击数据——新建查询——从文件——从文件夹,选择要合并的文件内容,点击【转换数据】即可进入编辑器。
(1)
(2)
(3)
接着选中编辑器中前两列数据,右击选择【删除其他列】,删除多余内容。
点击添加列——自定义列,输入公式:=Excel.Workbook([Content],true),确定:
点击展开命令,取消【使用原始列明作为前缀】点击确定即可展开自定义列:
再次删除不需要的内容:
再次展开table:
这时你会发现第一列数据后面存在.xlsx的后缀名,需要去除,可以点击转换——提取——分隔符之前的文本删除后缀名,最后点击关闭并上载即可。
3、禁止修改工作簿名称
如果不想别人在填写数据时随意修改工作簿的名称,可以利用数据验证这个功能。
如下图所示,选择需要填写数据区域——数据——数据验证——允许:自定义——公式中输入:
=MID(CELL("filename"),FIND("[",CELL("filename")) 1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)="报表.xlsx"
解析:先用CELL函数提取当前工作簿的路径及名称,再用FIND函数查找 左中括号 "[" 及 右中括号 "]" 的位置,结合MID函数提取出工作簿名称 。
最后,还可以设置一下出错警告:
,
免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com