返回目录:excel表格制作
数据模型
今天我们来把几个表罗列一下:
数据表关系图
什么事都从最简单的开始入手,一点一点的积累成就感。
第一步:整理教师列表
这个应该很简单不需要写公式只要点几下鼠标就成,Excel中直接删除重复项就行,或者用Power Query删除重复项加载到模型中。这个表将来用作切片器。
第二步:整理班级全称表
这里涉及一个快慢班的分组问题,假设是通过奇数偶数来区分快慢班。我们最后希望得到这样的结果:这是对快慢班分组之后的年级各班的数量。
同时把这个奇数班和偶数班,与校区、年级、班级合并组成每个班唯一的全称,这个过程可以在Power Pivot中完成,也可以在Power Query中完成,公式也不复杂,就是在Excel中也很容易实现。以上这些数据处理的数据源是成绩表,通过对成绩表的处理得到我们想要的表格。
提取一个班级全称的唯一值的一个表,添加到数据模型中:
第三步:成绩表处理
成绩表中我们要完成如下度量值的公式编写:
- 人数:总人数、各科及格人数、各科优秀人数
- 率:各科及格率、各科优秀率
- 分数:各科平均分、总平均分
- 排名:各科学生个人年组排名
这里就是各科的及格人数和优秀人数的公式,还有排名的公式,稍微有些复杂,其他的公式都很简单。
及格人数与优秀人数公式的编写,要把各科的及格分数线,优秀分数线的条件写清楚就可以了,可以用AND、OR等逻辑函数,或者是"||"、"&&"逻辑运算符号。
排名的公式给出一个例子:
语文年组排名:=IF(HASONEVALUE('成绩'[年级分组]), RANKX( ALLSELECTED('成绩'[学号]), [以下项目的总和:语文],,DESC,Skip), BLANK())
这个RANKX是经典的组内排名公式,也是我们这个模型中用的最多的一个公式形式。
第四步:使用DAX查询创建中间表
- 得分:平均成绩得分、及格率得分、优秀率得分;
- 年组:平均分、及格率、优秀率、排名;
这个过程使用DAX Studio来编写查询公式会比较方便
这是其中一个表的例子,很多个查询表都是这种结构
EVALUATE SUMMARIZE('班级平均成绩明细', '班级平均成绩明细'[年级分组], '班级平均成绩明细'[全分类], "语文排名", [语文排名], "语文得分", [语文得分], "数学排名", [数学排名], "数学得分", [数学得分], "英语排名", [英语排名], "英语得分", [英语得分], "总分排名", [总分排名], "总分得分", [总分得分])
度量值写在Pivot表里面,写查询就会简单些,当然也可以把度量值直接写在查询里。
第五步:计算排名与得分
用Power Query将班级平均分、及格率、优秀率的表格处理后加载到模型中,编写排名与得分的度量值。
语文及格率排名:=IF(HASONEVALUE('班级及格率'[年级分组]), RANKX(ALLSELECTED('班级及格率'[全称]), [以下项目的总和:语文及格率],,DESC,Skip), BLANK())
排名还是用这个组内排名公式 ,其他各科的及格率、优秀率、平均分都是这样来写。
最后一步:就是见证奇迹的时刻----展现数据
这一步要根据最终的报表格式来制作,可以使用透视表+切片器,也可以使用查询表,
我们下次列举几个表的例子来说明一下。