有各学校学生成绩表scores.xlsx,部分数据如下图所示:
在第2个sheet“设置”中有各科成绩分级设置,如下图所示:
现在需要用这些数据做一个学校成绩汇总报表,统计各学校各科平均分及各分数等级占比,目标报表如下图所示:
这个汇总的要求是要根据设置中指定的科目和顺序显示,设置的科目变了,汇总表会自动跟随改变。
1、 运行集算器
可去润乾官网下载职场版,同时下载一个免费授权就够了,首次运行时会提示加载。
2、 编写脚本:
把代码列出来看得清楚点:
A | |
1 | =file("E:/学校成绩汇总/scores.xlsx") |
2 | =A1.xlsimport@t(;"设置").sort(总分) |
3 | =A2.fname().new(~:科目,A2.field(~):分段).delete(1) |
4 | =A1.xlsimport@t(;"总成绩") |
5 | =A4.pivot@r(学校,姓名;科目,分数) |
6 | =A5.join(科目,A3:科目,分段) |
7 | =A6.new(学校,科目,分数,分段.pseg(分数):段位) |
8 | return A7,A3 |
A1 定义数据文件scores.xlsx
A2 读出sheet“设置”中的分数等级,并按总分列数据从小到大排列
A3 用A2中的数据生成科目、分段2列的序表,分段列的值为对应科目的分级分数序列,并删除第1行。得到的数据结构如下图所示
A4 从sheet“总成绩”中读出学生成绩数据,选项@t表示第1行是列标题
A5 以学校、姓名为分组,对A4的数据进行转置,选项@r表示列转行,转换后的新列名命名为科目、分数。转换后的数据如下图所示:
A6 把A3中的数据与A5按科目做连接,把A3中的分段列添加到A5。
A7 从A6中选取学校、科目、分数3列,并增加一个新列段位,其值为分数在分段序列中的区段序号。A7中的数据如下图所示:
A8 返回A7、A3两个数据集供报表使用
3、 运行报表设计器
可去润乾官网下载职场版,同时下载一个免费授权就够了,首次运行时会提示加载。
4、 新建报表:
新建报表后,按F11弹出新建数据集窗口,如下图:
按增加按钮,选择脚本数据集,再按确定按钮,弹出下图窗口,把在集算器中调试好的脚本复制粘贴到窗口中
脚本代码写好以后,按确定按钮回到新建数据集窗口,由于脚本返回了两个数据集,所以要将新建的数据集名称改成两个,如下图所示,改为ds1,ds2
按上图中的确定按钮后,回到新建报表界面,设计一个如下图所示的报表(报表文件可下载附件得到):
B2 横向扩展,表达式为:=ds2.select(科目),按设置的科目及顺序列出科目
D3 横向扩展,表达式为:=list(3,2,1,0),列出分数分级区段序号,显示值表达式为:map(list(3,2,1,0),list("特优率","优秀率","及格率","过差率"))
A4 纵向扩展,表达式为:=ds1.group(学校;学校:1)
B4 表达式为:=ds1.avg(分数,科目==B2)
C4 表达式为:=ds1.count(科目==B2)
D4 表达式为:=ds1.count(科目==B2 && 段位==D3)/C4*100,设置显示值表达式为:if(value()>0,value(),""),当值为0时显示为空
C列 不要勾选可视属性,隐藏此列
5、 按F9预览报表,然后选择菜单:文件--导出--Office2007--Excel文件,将报表导出为excel文件。
【附件】 score.zip