菜鸟记633-怎么统计这个难倒99.99%表哥表妹的不规则表格?

万一您身边的朋友用得着呢?

各位朋友早上好,小菜继续和您分享经验之谈,截止今日小菜已分享600+篇经验之谈,可以文章编号或关键词进行搜索。

微信推送规则发生改变,如果您想看到小菜每个工作日的经验之谈,请多多点开浏览、分享到朋友圈、加星标或点个赞

每日一图完毕,以下才是今天的正式内容……

温馨提示:本文长达近2400字,阅读大概需要15分钟,但是非常实用,强烈建议您阅读完毕并推荐给需要的朋友。

摘要:本文介绍如何利用PowerQuery将二维表格转换为一维表格的案例。

关键词:EXCEL2019;PowerQuery;逆透视;Mid函数;Find函数;Right函数;Len函数;操作难度*****

俗话说的好“有趣的灵魂万里挑一、好看的皮囊千篇一律”;

小菜却说“让人头大的表格千千万,不知道哪天就遇上!

比如像设计成这样的一张课表:

图1:源数据

一个学期累计下来,您知道怎么统计各班都上了什么课程?上了什么内容?都哪些老师上的吗?一共上了多少课时?

多希望咱们手里有一张一维表,以上统计简直就是分分钟搞定!

遇到问题找办法解决问题,没有问题创造问题去解决就是了!

仔细分析规则:第二行里有部门、教师和教学周信息,每个单元格里的数据排列顺序都是一样的,标注同上字符的单元格信息和上一个单元格是一样的,C授课类型表示有两位老师同时上课;

再梳理一下需要解决的技术问题:

1-第二行数据需要信息的提取;

2-同上单元格信息的提取;

3-课程单元格需要信息的提取;

4-统计。

如果咱们可以将表格先转换为一维表,然后再提取单元格班级、课程、授课内容、课程类型和共同指导教师信息,再做统计是不是就迎刃而解了?

步骤1:添加字段转换数据表

根据需要的信息,咱们在原工作表前添加出部门、教师、教学周和课时列:

图2:添加字段后工作表示意图

现在的表格您看着是不是就好用多了?如果有多个教师的课表,咱们粘贴到一起:

图3:汇总的表格

小菜提示:建议此时查一下是否存在没有数据的空单元格,并进行数据清洗(如删除空格、批量替换不规则数据等等),否则会影响下一步的操作结果!

接下来咱们重点解决同上单元格信息的问题,在选中相应数据区域的基础上,替换掉“同上”:

图4:替换同上字符

然后马上Ctrl+G,定位空值,按等号,再按向上箭头后按Ctrl+Enter,咱们就实现了将原来同上单元格填充上上一个单元格的内容:

图5:替换同上并填充上一单元格信息效果

数据表到了这里,咱们是不是有了更多可操作的空间了?

小菜提示:实践中删除前两行,形成规范的数据库结构,操作起来更容易避免莫名问题的出现。

请看会动的图

步骤2:逆透视数据表

如果您有印象的话,小菜已经多次介绍过PowerQuery这个好用的工具,尤其在二维表转一维表的操作中,非常实用!

把第二行数据所需要的信息和同上的问题解决好以后,咱们选中有效的数据区域,依次点击数据—来自表格/区域,创建表:

图 6:启动PQ

确定后启动PQ编辑器:

图7:编辑器

选中部门至节次列后,点击转换中的逆透视列,记得选“逆透视其他列”:

图8:逆透视列

小菜提示:咱们到底选中那些列再逆透视呢?实质是哪些列不需要变动(已经是一维表结构)就先选中,需要逆透视的就是哪些每列数据性质一样的二维表结构列。

瞬间得到逆透视结果,您看是不是咱们需要的表格了?

图9:逆透视结果

点击编辑器最左上角的关闭并上载按钮后,回到Excel中得到需要的表格:

图10:关闭并上载结果

小菜提示:在这里需要利用筛选把没有上课的单元格(即打叉单元格),课时修改为0,否则最后统计会出错。当然您也可以前期不填数据,逆透视后再把课时数据补充上。

请看会动的图

步骤3:提取单元格信息

万里长征已经走完了一半了,下面进入到攻关阶段,提取班级、课程、授课内容、课程类型和共同指导教师信息;

根据每个人工换行符后的信息字段规则,咱们请出Mid+Find函数组合,如提取班级信息这样写函数:

=MID(G2,FIND("级:",G2)+2,FIND("课程",G2)-4)

小菜分析:这个函数的意思是告诉Excel,去G2单元格找文字,从“级:”后第3个(+2的结果)开始找,到“课程”前4位(班级:占3个字符,课程的课又占1个字符,所以要-4)就可以了。具体加减要根据您让软件Find的特定字符而定,比如提取班级信息也可以写成:

=MID(G2,FIND("班级:",G2)+3,FIND("课程",G2)-4)

小菜再告诉您一个小秘密,加减位数您多试试几个数字,直到找到规律为止。

同理,其他的提取函数咱们这样写:

提取教师:

=MID(G2,FIND("程:",G2)+2,FIND("授课内容",G2)-FIND("程:",G2)-2)

提取授课内容:

=MID(G2,FIND("授课内容:",G2)+5,FIND("课程类型",G2)-FIND("授课内容:",G2)-5)

提取课程类型:

=MID(G2,FIND("课程类型:",G2)+5,FIND("共同授课",G2)-FIND("课程类型:",G2)-5)

提取共同授课教师:

=RIGHT(G2,LEN(G2)-FIND("教师:",G2)-2)

小菜提示:提取共同授课教师咱们用Right函数更简洁一些,各函数原理都是按照单元格中各信息排列顺序,找到从哪里提取的位数和共提取多少位数(用两个Find相减得到)实现咱们的需求。

最后咱们得到这样的表格:

图11:提取结果示意图

其中函数出错的地方就是没有上课,您可以复查一下课时数对不对再进行下一步的统计工作;

步骤4:透视数据表

有了规范的一维表格,咱们插入数据透视表,想获得什么信息只需要拖动一下表格就可以了:

比如咱们以人为单位来统计:

图12:统计教师学时

以课程为单位统计教学进度:

图13:统计课程进度

以班级为单位统计教学进度:

图14:统计班级教学信息

总而言之就一句话:想怎么统计就怎么统计!

请看会动的图

(0)

相关推荐