Power Query 打印格式学分表格整理
左侧的是每个学生一张表学分表,要整理成右侧的总学分统计表:
根据规律,直接获取总学分后面的值的公式,QQ群里给出了各种各样的公式解法,我们要讨论的是将这个表格降维处理,整理出标准的一维表格,方便进一步的数据统计。
先来分析这个表格的构成:
学生成绩单这一行对于数据表来说是没用的;
每个学生的信息在第三行;
具体的科目与成绩,又分成了两栏,并列排列;
后面的总成绩与统计时间基本没用;
每个学生选修的科目不同,所以每个学生的成绩表的行数各不相同;
以上就是这个成绩表的基本特征,我们如果用Power Query来处理,就需要考虑这些特征,然后利用其中的规律,整理数据。
仅导入就好了,然后修改一下名称:数据源,之后的操作引用这个数据源:
第四列做行筛选,保留不为null的行:
提升标题,删除空白列,统一数据格式:
这个学生信息表就整理好了,放着备用。
这一步的工作比较多,首先还是做行筛选:
第三列两个条件筛选,不为null并且不等于校名。
然后删除空白列,添加个索引列,为了确定每一行的行号:
其实下面的工作,如果在Excel中就是一个counif函数就能解决的,我们要给每个学生一个序号,用COUNTIF函数固定住第一行,统计序号的个数,这样每个学生的成绩就对应一个相同的编号。这个工作放到Power Query中就显得有些复杂:
Table.RowCount(Table.SelectRows(Table.Range(a,0,[id]),(x)=>x[列2]="序号"))
Table.Range(a,0,[id])就相当于Excel中的可变区域选取,类似于A$2:A2,当然这样选择之后得到的是一个表格,从第一行开始选取对应的索引号的行数的表格;
然后我们用Table.SelectRows筛选出“序号“行,Table.RowCount进行计数;
接下来就是引用我们第二步获取的学生信息表中的学号,我们只要学号列数据就可以了,其他的信息可以最后通过学号来合并查询。
这个时候先不要做数据整理,因为第一行中有重复的名称,做出的标题行会乱,我们删除id列后开始拆分表格。
因为是两栏式数据表格排列,我们就要给他分开,每6列加一列学号组成一个表格,一共两个表格,分别提升标题行,学号列命名:
查询2(2)、查询2(3)就是拆分好的两个成绩表
第五步:合并、整理数据
查询2(2)、查询2(3)追加合并为新查询:
其实到这一步,我们要做的工作就已经做好了,我们已经成功的把复杂表头的表格整理成了一维表格,至于统计总学分,就是一个分组就能搞定的事:
然后我们通过学号用合并查询把学生的详细信息查询出来:
我们如果使用追加查询后的一维数据表作为数据源,加载到Povit,就可以做更多的数据分析:
例如各学期的学分情况:
各科目的平均分:
各科的学分获得:
当然如果动手写度量值,就会有更详细的分析,这里就不多说了。
页面更新:2024-03-12
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2020-2024 All Rights Reserved. Powered By 71396.com 闽ICP备11008920号-4
闽公网安备35020302034903号