先放一弹小伙伴的公众号后台留言:
在之前的更文中,表姐强调了一下,我们做表格的重要原则:
其中关于数据源部分,我们强调说,要尽可能的全面,不要对数据源进行多工作表的拆分。
可事实证明,在工作中,你一定会遇到把工作表不拆分,无快感的情况。
比如:12个月的工资表,拆分为12个sheet:
这样你在年底做全年薪资成本统计的时候,一定开动了你复制粘贴的双手,把12个月的数据,统统贴到一个sheet当中,然后再用数据透视表分析吧~
又或者,你的数据源,不可避免的需要由多个业务部门/项目部进行提供,每到月底时,你再贴到一起,做全公司的统计:
再比方说,公司的物资进销存系统,其中库存核定、出库流水、入库流水,都存储在不同的工作表(sheet)当中:
我们需要根据期初、入库、出库的情况,去做一张库存报表。
凡此以上种种,都涉及到一个关键词...
如何不用复制粘贴的方法,快速完工作表的合并?
目前行当里的方法,无非有2:
①SQL+ADO的方法
②VBA合并工作表
标题党不是说:一句话的事儿吗?
好的,我们来看个简单的例子,我有一份水果的期初库存、出入库工作簿,其中包含3个工作表:
①期初
②入库
③出库
提问:如何查看目前实时库存(用数据透视表的方法)
答:用SQL语句,虚拟出一份数据源表,然后再做数据透视
①插入一张数据透视表,但点选数据来源为【使用外部数据源--选择链接】,然后找到对应的Excel文件。
②找到数据源工作簿打开后,然后选择任一个工作表:
是的,随便你选啥,因为后面我们要把你选的表,给改掉。这里就是个占位的意思~
确定后,可在【连接名称:】处,看见该表名称
③用SQL语言的方法,修改数据透视表的数据来源。
点击【数据透视表工具】--【分析】选项卡--【更改数据源】--【连接属性】
在打开的【连接属性】对话框中--命令类型选择【SQL】--命令文本的编辑栏中,粘贴一句话(SQL代码):
select 类别,期初, 0 as 入库,0 as 出库 from [期初$] union all select 类别,0 as 期初,入库, 0 as 出库 from [入库$] union all select 类别,0 as 期初,0 as 入库, 出库 from [出库$]
更改后,【数据透视表字段】区域中的字段,即包含了这几张表内的所有字段。然后我们再进行数据透视表的布局调整,即可:
④用【计算字段】的方式,新建一个字段【库存】。
最终通过上述方法,连接期初、入库、出库三张工作表,完成库存的实时数据统计。
【一句话】语法解析:
select 类别,期初, 0 as 入库,0 as 出库 from [期初$] union all select 类别,0 as 期初,入库, 0 as 出库 from [入库$] union all select 类别,0 as 期初,0 as 入库, 出库 from [出库$]
上诉案例中用到的SQL语句,是SQL的标准语法:
select...from...
eg:select A from TAB1
从TAB1表中,选择字段名为A的那一列
as 将字段名进行重命名。
union all 将几个查询的结果,进行连接
思考题:
如何利用这一句SQL,将所有的数据源合并到一起呢?
答案在附件的练习文件当中。
欢迎小伙伴们互动留言~~
百度云盘链接地址:http://pan.baidu.com/s/1sl96D7R
我是表姐凌祯
带您一起提高桌面生产力
如您有office相关的问题,欢迎与我交流
希望我微薄的积累能够帮助到您
联系客服