巧用Excel数据透视表统计分析学生成绩

巧用Excel数据透视表统计分析学生成绩

科技信息○IT论坛○SCIENCE&TECHNOLOGYINFORMATION2010年第19期

巧用Excel数据透视表统计分析学生成绩

魏零

（桂林航天工业高等专科学校计算机系

广西

桂林

541004）

【摘要】数据透视表工具是最常用、功能最全的Excel数据分析工具之一。合理巧妙运用，能使许多复杂的问题简单化，并可以大大的提高工作效率。针对学生成绩表的统计分析给出解决方案，供教学和教师统计分析成绩时参考。

【关键词】数据透视表；统计；分析；成绩

0引言

我校《大学计算机基础》课程实行教考分离，课程结束后，学生须参加全国普通高校非计算机专业计算机联合考试（一级），通过考试合格的同学既可以获得该门课的学分，又可以取得计算机等级资格证书。每次考试完后由广西区统一阅卷，然后将成绩以Excel电子表格形式下发到各学校（以下简称成绩表）。成绩表的表头各字段名如下：

成绩表说明：理论成绩和操作成绩都是百分制；总评分为空白、合格、优秀，当理论成绩与操作成绩都60分以上，则为合格，当理论成绩与操作成绩都85分以上，则为优秀，其余为空白；当总评为合格或优秀的，证号不为空，证号即为获得的证书号，否则为空白。

课程负责人须对每次考试情况进行汇总统计与分析，了解全校各系、各年级、各专业的总评及人数占比等情况，而Excel的数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，集数据排序、分类汇总、筛选于一身，可以方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。因此，巧用Excel数据透视表可以帮助我们统计分析学生成绩，使许多复杂的问题简单化，并可以大大的提高工作效率。

1

1.1

修改数据源

将“总评”中的空白值换成“不合格”

为了能在统计分析成绩时，总评是不合格的数据不以空白显示，便于知其意，须将“总评”中的空白值换成“不合格”。

操作步骤：选定数据清单中的任意单元格，选择菜单“数据”→“筛选”→“自动筛选”命令，单击“总评”字段右边的下拉按钮，在弹出的下拉列表中选择“（空白）”，在K2单元格中输入“不合格”，双击K2单元格的填充柄，即完成将“总评”中的空白值换成“不合格”。最后，取消自动筛选，即选定数据清单中的任意单元格，选择菜单“数据”→“筛选”→“自动筛选”命令。1.2增加“年级”字段

由于需要对各年级的情况进行统计，而成绩表中没有“年级”字段，但是“学号”的前四位数表示的是年级，因此“年级”字段的值可由学号产生。

操作步骤：先在“学号”字段前增加一列“年级”字段，则“年级”字段在E列，“学号”字段中F列。在第一个学生的“年级”单元格E2中输入“=LEFT(F2,4)”，双击E2单元格的填充柄，即完成将“年级”字段值的填充。

通过对同一数据透视表进行不同的布局，可以得到各种不同角度的成绩分析汇总表。

若需统计各院系、各年级、各总评的理论和操作成绩的平均分及人数占比，则可从字段列表中将“年级”字段拖到页字段区，将“院系名称”拖到行字段区，将“理论成绩”、“操作成绩”和“学号”字段依次拖到数据区，单击“数据”不放，拖到“汇总”单元格。将“总评”字段拖到列字段区。

若要改变数据透视表的布局，只要选中数据透视表里欲删除的字段，将其拖出数据透视表框，然后将新的字段从字段列表中拖到数据透视表相应区域即可。

2.3设置字段的汇总方式和显示方式

Excel对“理论成绩”和“操作成绩”字段默认的汇总方式为“求和”。若要更改汇总方式为“平均值”，可右击“求和项:理论成绩”，选择“字段设置”，或先单击“求和项:理论成绩”，再单击数据透视表工具栏上的“字段设置”按钮，均可调出“数据透视表字段”对话框；在“数据透视表字段”对话框中，单击“汇总方式”列表框中的“平均值”，然后单击“确定”按钮。这就将“理论成绩”的汇总方式改成了“平均值”。再按此方法更改“操作成绩”的汇总方式。

将“学号”字段的显示方式更改为“占同行数据总和的百分比”。操作方法：右击“计数项:学号”，选择“字段设置”，在调出“数据透视表字段”的对话框中，单击“选项”，在“数据显示方式”列表框中选择“占同行数据总和的百分比”，然后单击“确定”按钮。2.4重命名字段

当向数据区域添加字段后，它们都将被重命名，例如“理论成绩”变成了“平均值项:理论成绩”，这样会加大字段所在列的宽度，影响表格的美观。可以使用直接修改法重命名数据透视表的字段名称，这种方法是最简单易行的，即单击数据透视表中需要修改的标题单元格，在编辑栏中输入新标题。我们将“平均值项:理论成绩”改为“理论平均分”，“平均值项:操作成绩”改为“操作平均分”，“计数项:学号”改为“人数占比”。

2.5修饰数据透视表

通常情况下，数据透视表创建完成后，还需要作进一步的修饰美化才能得到更令人满意的效果。可以设置单元格格式（如字体、字号、颜色、小数位数等），还可以利用Excel提供的“自动套用格式”功能来改变数据透视表的格式，迅速获得具有专业外观效果的版式。也可添加/取消行列总计，隐藏行或列等。此处只是将小数位数设置为两位小数。

至此，可得到如图1所示的数据透视表。

2

2.1

创建数据透视表

创建空白的数据透视表选定数据清单中的任意单元格，选择菜单“数据”→“数据透视表和数据透视图”命令，在弹出的“数据透视表和数据透视图向导—3步骤之1”对话框中采用默认状

态，即确定待分析数据的数据类型为“MicorsoftOfficeExcel数据列表或数据库”，单击“完成”按钮。Excel产生一个空白的数据透视表和字段列表，并显示数据透视表工具栏。2.2对数据透视表进行布局

图1数据透视表

3

3.1

查看分析结果

在字段的下拉列表中进行适当的选取，可获得不同的分析结果数据透视表创建好后，当单击行区域或列区域上（下转第383页

）

巧用Excel数据透视表统计分析学生成绩

科技信息○规划与设计○SCIENCE&TECHNOLOGYINFORMATION2010年第19期

表2-3

项目统计年限

靖边县雨量站年最大3、5、7暴雨频率计算成果表

均值

Cv0.50.430.42

Cs/Cv3.53.53.5

5%10%20%83.9596.60

50%99%

3日5日7日

30

63.674.580.9

126.56105.58136.97117.11

54.7028.9466.8134.14

147.16126.09104.3572.7538.09

根据相关资料估算可利用的雨量，多年平均雨水可利用量，结果如表2-4：

W=A（h-b）α

表2-4

集水面积

靖边县西城区多年平均雨水资源可利用量

收集系数（%）

18km2102030405060708090100

集雨量（万m3）10.721.432.042.753.464.174.785.496.1

106.8

辅。具体的做法可包括：增加城市绿地面；增加透水建筑物的面积，如停车场、人流量较少的道路及人行道采用多孔沥青及混凝土地面或草皮砖；超出其渗透能力地面的雨水通过渗透管、渗透池收集利用；建设屋面雨水集蓄与回用系统等[4]。

3.2雨水利用的政策法规支持制定包括绿地种草栽花、植树造林、道路除尘、公园灌溉、清洁车辆、建筑开发利用雨水的法规，同时制定一些优惠政策，鼓励人们利用雨水。进一步讲还需要 …… 此处隐藏：2585字，全部文档内容请下载后查看。喜欢就下载吧 ……