感谢另一位Excel催化剂铁粉【我是庞庞】的笔记分享,本篇的分享内容也是笔者非常喜爱的也是在Excel插件圈子首创的极具自由度的报表输出功能。有此功能,几乎不需要依赖各种系统,也能生产出各种定制格式文件,整个过程零代码参与。 在数据整理阶段,我们需要将数据努力转换为规范的标准数据格式,以一维表数据库表结构最好。有了标准的数据,其他绝大多数的数据结果样式,应该是可以将近自动化生成的,这是一个比较理想的结果。 除了常规地统计分析中的透视表汇总分析,也有一些数据拆分的操作,即本篇的一转多,从一个数据源转换为多个文件每个文件仅有少量指定条件的数据,并样式可自由灵活定制的。
以下是正文 部分
开始之前,先讲讲个人工作上经常用到拆分数据经历,数据涉及到整个省级,Excel工作簿动则60M大小,常年尿性保持30M大小以上,因此用过几款Excel插件进行拆分数据。
如:方方格子拆分会无限卡机,试过等了半个小时仍然卡机无反应,放弃。易用宝拆分会丢失最后一行,为此我被老板批的差点就溶了,呵呵,放弃;Kutools拆分不兼容中国式报表拆分,放弃;Excel精灵拆分,字段一多在前后添加“【字段名】更多”手指头敲的痛痛滴,放弃;其他插件没有用过,不发表言论。
最后无意中度娘到了网友转发的Excel催化剂插件,下载试用,看了配套视频,加了QQ群请教群友,期间得到了作者、网友“做自己”大量帮助指导,感谢诸位帮助,顺利搞定了工作的刚需。
Excel催化剂先是一微信公众号的名称,后来顺其名称,正式推出了Excel插件,插件将持续性地更新,更新的周期视李伟坚老师时间而定,争取一周能够上线一个大功能模块。Excel催化剂插件承诺个人用户永久性免费使用!
相信很多人一看名字就有点懵了,一开始我也懵,后面网友告诉我,这个就是自由拆分报表。李老师是数据分析大师,他取的名字比较专业的术语。
按照个人的叫法,就是WPS里面的“拆分表格”。这个功能位于Excel催化剂界面《数据分析》这个模块。这个是个利器啊,最近在零星学习中,笔记散落记着,回头归纳一下再分享给大家。
自由报表里面有两种方式:
今天主要介绍一对多,也就是拆分表格。请先看看图认识一下,免得等下,我说什么,你却一脸懵逼,到时就怪我咯。
首先隆重请上我们示例某个地区的工作簿,工作簿内部已特殊处理过,文件大小如下图:
【操作第一步】:
准备数据,这份表格,我们可以叫它为:数据源。我们打开这份表格,得到以下的大图:
再次申明:数据由于涉密,里面的农户基本信息经过加工处理呈现。注意看表格的第一行,直接就是列名字段,不会产生那些什么首行、首列是空白的隐患。
【操作第二步】:
建立一个中国式报表模板,设置好里面的格式、效验、字体等等,如下图:
最好报表模板设置和需要拆分的数据在同一个工作簿里面,Sheet1名,按习惯取:如右图:
【操作第三步】:
在数据源点击“自由报表-一对多主表明细表结构报表:第1步:映射关系配置”,如下图:
点击“第1步:映射关系配置”后,会出现如下图:
现在我们来配置上面这个表格,到数据源表,把列名复制下来后,点上图“源表列名称”下的列1那个单元格,右键选择转置后,得到右图:→
【操作第四步】
在需要拆分字段名旁边对应的单元格,输入=符号后,移动鼠标去点击箭头指示模板对应的单元格,如下图所示:
注意事项:“需要拆分字段名”就是相当于是其它插件里面的“拆分依据或拆分字段”名字不同,大概意思一样。
Excel催化剂拆分必须要具有报表格式的主单元格。类似上面所说的字段名,否则拆分出来的是和明细数据一样的。
【操作第五步】
在经过配置后,我们得到以下图:
在引用报表的相应列如:“序号=中国式报表!A5:A11”后,我们可以Ctrl+D快速填充,在旁边的列输入A下拉单元格得到A-U字母,然后用函数=SUBSTITUTE(B7,"A",D7)快速得到报表的对应列,右键复制到红框之处,如下图:
强大之处:数据源的列名和报表的列名不一致,字段不一样多,也同样可以拆分,这也是我深深喜欢Excel催化剂的强大之处。在此感谢李老师开发这么好用的插件。如下图所示:
【操作第六步】
经过自由配置后,点击第2步:命名报表文件,如下图1:
【操作第七步】
点击第2步:命名报表文件后,会生成下图2:
【操作第八步】
在上面目标单元格输入函数PathCombine,这个函数是自由拼接、保存文件路径的意思,如下图1:
打开需要保存到的文件夹,右键复制路径,粘贴到Path1如下图2:
强大之处:如上图在Path2处,用链接符“&”后面的任意符号、数字、文字,需加上双引号来引用。这样自由度的任意命名真香。
【操作第九步】
命名完成后,点击第3步:生成报表,如下图:
【操作第十步】
泡杯茶,或者跑去和女同事侃侃,回来后就发现下图:
【操作第十一步】
打开检查一下,布置分配给下级工作,效果图如下图1:
效果图2:
PS:别认为拆分出来超过15位数字会变成E+,这是故意不设置格式让它成这样的。
最近看到很多人都觉得安装这款软件复杂,操作难,更是各种伸手党直接求答案心里。其实嘛,再好的软件,也需要一个学习的过程,再牛逼的学者,也是从小学读起。摒弃浮躁,付出点空闲时间学习下,技多不压身。临阵磨刀,有用?
Excel催化剂不能说完美,也有BUG的时候,但人尚无完人,更何况是软件呢。希望李老师不忘初心,砥砺前行。祝Excel催化剂越来越好。
以上步骤看着繁琐,操作几次后,这些步骤都是瞬间完成的。
关于拆分自由报表心得就到此结束,目前还在持续学习着Excel催化剂的各种功能,也衷心感觉到其强大之处,也陆续在做着笔记,有机会就成册发给大家看看,技术不到家,望大家海涵,多多指点,在此先感谢大家了。
催化剂解说:上述的图文步骤,的确看起来较为复杂,再好的文字能力也很难表达清晰传递到位。正因如此,Excel催化剂非常推荐使用视频的方式来学习鼠标键盘操作类工具。会得到事半功倍的效果。 本功能也很早就录制成视频教程,读者们不必深究其中的一些文字步骤细节,只需知晓下整个业务场景,可以启发到自己日常工作的再改造即可。