点击上方蓝字关注 Excel函数与公式
关注后发送函数名称,即可获取对应教程
![混杂数据分列及动态统计](http://p2.ttnews.xyz/loading.gif)
微信公众号 | Excel函数与公式(ID:ExcelLiRui)
个人微信号 | (ID:ExcelLiRui520)
混杂数据分列及动态统计
工作中有时候需要经常从数据源中按要求统计数据结果,在源数据结构不能被修改且数据源频繁更新的情况下,每次都要及时准确的统计出所需报表并不轻松。
今天就来介绍一下混杂数据分列及动态统计的技术。
更多系统课程,点击文末“阅读原文”获取。
问题描述及效果展示
比如现在原始数据位于A列,在一个单元格中同时包含了部门、姓名和销售数据。
A列数据结构不允许更改,且数据源频繁更新,要求按部门统计人数之和、销售额之和。
做好的效果,如下图所示,不但在A列数据更改时,右侧报表可以刷新结果,而且当A列中追加新的行记录时,结果也可以同步更新。
这里用的不是函数公式,也没有使用VBA编程。
这里用的是Power Query,演示版本是Excel 2016最新版,自带Power Query,没有新版的同学可以回到本公众号发送“2016”获取安装包。
你一定想知道具体操作步骤,别急,下面有介绍。
数据源展示:
先看一下数据源,很简单,就是A列单列。
观察数据源的特点,找到规律后再确定方法。
可以发现部门和姓名之间使用冒号:间隔,在姓名和销售额之间使用空格 间隔。
这时候我们就可以构建问题的处理思路,借助这些规律来处理数据。
下面是具体操作方法。
操作步骤详解:
要处理数据,需要先把数据源导入到Power Query编辑器。
单击数据-自表格,如下图所示。
弹出创建表的对话框后,确认表格范围,单击确定。
进入Power Query编辑器后,如下图所示。
![混杂数据分列及动态统计](http://p2.ttnews.xyz/loading.gif)
借助前面说过的规律,拆分提取数据。
先按冒号拆分部门和姓名。
Excel会很聪明的自动识别出按冒号分隔。
确定分隔符无误后,我们单击确定就可以了。
拆分部门和姓名之后,如下图所示。
这时再按空格拆分姓名和销售额。
再次确定空格为分隔符,单击确定。
这时部门、姓名、销售额数据分别各自为列了,我们进行分组。
在分组设置时,单击添加聚合,命名新列名为求和,操作为求和,如下图所示。
单击确定后,即可在编辑器界面看到我们想要的结果。
这时候单击关闭并上载,即可把结果返回到工作表。
返回到Excel工作表后,效果如下图所示。
注意,这个数据表并不是孤立存在的,而是和数据源保持关联的,当数据源更新后我们可以快速更新结果报表,这是超给力的一个功能!
然后,你可以把结果表复制到自己想放的位置,比如数据源所在工作表的G1单元格。
这时大功告成,你会发现无论数据源怎么变,你想要的结果都可以瞬间得到。
好啦,以后再遇到用常规方法很难搞定的问题时,可以借助本文的技术大幅提升工作效率。
如果你觉得有用,就点右上角分享给朋友们看看吧~
除了今天介绍的技术,还有很多10倍提升工作效率的方法,我专门整理在Excel特训营一期里有视频详解,长按下图识别二维码,永久拥有超值系列视频课!
(长按图片识别二维码)
更多精品课程
更多精彩的Excel实战技术还有很多,我已经整理到Excel特训营中以超清视频演示并同步讲解。
不但有具体场景,还讲解思路和方法,更有配套的课件下载和社群互动,想系统学习的同学点击底部“
阅读原文”。还想看更多精彩?长按识别下图二维码,里面有各种方向不同的Excel特训营,每一期特训营内容不同,都可以帮你系统提升自己!
长按下图 识别二维码点关注,获取更多资料
按上图↑识别二维码,查看详情
如果你觉得有用,就分享给朋友们看看吧~
閱讀更多 Excel函數與公式 的文章