4.4多班级学生单科成绩分数段统计（三）
案例背景
某中学初三年级有9个教学班，期末考试后，需要统计全年级9个班级学生的期末考试各个分数段人数分布情况，以便进行综合分析。
本案例通过尝试使用数据透视表技术以数学学科为例来统计全年级9个班级学生的期末考试各个分数段人数分布情况。
要实现本案例中的功能，学员应该掌握以下EXCEL技术点
●数据透视表
●数据透视图
●综合应用 逻辑判断公式设计与操作，用逻辑判断替代 IF函数和AND函数的嵌套。
最终效果展示
4.3.1创建原始数据表
Step 1创建工作簿、重名工作表
创建工作簿“年级单科成绩分数段统计表.xls”，然后将工作表重命名为“成绩表”，删除多余工作表。
Step2输入原始成绩
①在单元格A1中输入“班级”，单元格B1中输入“姓名”，单元格C1中输入
“性别”，单元格D1中输入“数学”。
②输入初三年级1班、2班……9班全部251名学生的的班级、姓名、性别和数学成绩。
Step 3输入分数段分段标准
在单元格E1，F1，G1，H1，I1分别输入“60分以下”，“60-70”, “70-80”, “80-90”, “90-100”。
Step 4设计“计数“公式
①在单元格E2输入公式:” =IF(AND($D2>=0,$D2<60),1,0)”。
②在单元格F2输入公式:” =IF(AND($D2>=60,$D2<70),1,0)”
③在单元格G2，H2，I2中陆续输入公式:
” =IF(AND($D2>=70,$D2<80),1,0)”
” =IF(AND($D2>=80,$D2<90),1,0)”
” =IF(AND($D2>=90,$D2<100),1,0)”
Step 5改进公式
①在单元格E3输入公式:” = ($D3>=0)*($D3<60)“
②在单元格F3输入公式:” = ($D3>=60)*($D3<70)“
③在单元格G3，H3，I3中陆续输入公式:
” = ($D3>=70)*($D3<80)“
” = ($D3>=80)*($D3<90)“
” = ($D3>=90)*($D3<100)“
Step6复制填充公式
选中单元格区域E3:I3，双击I3右下角的填充柄即可完成第4行至最后一行的公式填充。
4.3.2创建数据透视表统计各分数段人数
上面已经建立了原始数据表单“成绩表”，下面要在此基础上创建数据透视表。数据透视表是从数据库中产生的一个动态汇总表格，它的透视和筛选能力使其具有极强的数据分析能力，通过转换行或列可以查看源数据不同的汇总结果，并且可以显示不同的页面来筛选数据，还可以根据需要显示区域中的明细数据。
Step1选择数据类型和报表类型
①在“成绩表”中选择任意一个非空单元格如A1 单元格，然后单击菜单“数据”→“数据透视表和数据透视图”，打开“数据透视表和数据透视图的向导—3步骤之1”对话框。
②在“请指定待分析的数据源类型”组合框中选择默认的的“Microsoft Office Excel数据列表或数据库”、在“所需创建的报表类型”中选择默认的“数据透视表”
③单击“下一步”按钮打开“数据透视表和数据透视图的向导—3步骤之2”对话框。
Step2选择数据源区域
①在该对话框中，“选择区域”文本框中默认的工作表的数据区域为$A$1：$I$252。
②单击“下一步”按钮打开“数据透视表和数据透视图的向导—3步骤之3”对话框。
Step3选择数据透视表显示位置
选中“新建工作表”单选按钮。然后单击“布局”按钮，弹出“数据透视表和数据透视图的向导——布局”对话框。
Step4设计数据透视表布局
①将“性别”字段拖到“页”字段框中。
②将“班级”字段拖到“行”字段框中。
③将字段“60分以下”，“60-70”, “70-80”, “80-90”, “90-100”拖到“数据”区域框中。
④单击确定按钮，返回“数据透视表和数据透视图的向导—3步骤之3”对话框，单击完成按钮，即可创建Excel默认的数据透视表版式图，同时会打开“数据透视表“工具栏和”数据透视表字段列表“任务窗格。
Step5设置数据项字段布局
光标指向灰色的“数据”标志框，这时鼠标指针变成双向箭头黑色十字形状时，向右拖动鼠标到“汇总”处，松开鼠标即可得到新的布局。
Step6重命名数据透视表列字段
在单元格B4中输入新的字段名“60以下”。
②分别在单元格C4，D4，E4，F4中输入新的字段名“60--70”, “70--80”, “80--90”, “90--100”。
Step7修饰美化数据透视表
①将单元格区域A2:F2“合并及居中”，输入表头“初三年级期末考试成绩各分数段人数统计表”，字体字号字形为“宋体16加粗”
②选中B列至F列，将列宽设置为10。
③选中单元格区域A4:F14，为表格添加边框，并将文字设置为居中对齐。
Step8分别统计男生和女生各分数段人数
①光标单击B2单元格中页字段“性别”的下箭头按钮，选择“男”。
②单击确定按钮，即可得到男生各分数段人数统计表。
③光标单击B2单元格中页字段“性别”的下箭头按钮，选择“女”。再单击确定按钮，即可得到女生各分数段人数统计表。
4.3.3创建“数据透视图”图表
通过创建数据透视图可以直观的了解各班各分数段的人数分布。
Step1规范班级名称
为避免班级名称“1”，“2”，……“9”与数字1，2…..9混淆，将“成绩表”中班级名称“1”，“2”，……“9”分别改为“1班”，“2班”，……“9班”。
Step2重新调整数据透视表布局
光标分别指向灰色的“班级”和“数据”标志框，这时鼠标指针变成双向箭头黑色十字，分别向左、向右拖动鼠标及其互换位置，松开鼠标，然后“数据透视表”工具栏的“刷新数据”按钮“！“，即可得到新的布局。
Step3创建“数据透视图”
单击 “数据透视表”工具栏的“图表向导”按钮，得到工作表名为“chart1”的数据透视图。
Step2重新选择图表类型
①右键单击数据透视图中任意数据系列，在弹出的下拉列表中选择“”图表类型。
②弹出“图表向导-4步骤之1-图表类型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选择”柱形图“，在”子图表类型 “选项框中选择”蔟状柱形图“。
③单击确定按钮即可得到新的“蔟状柱形图”图表。
关键知识点讲解：
数据透视表
数据透视表是一种交互式报表，可以快速分类汇总的大量数据，并且可以随时选择其中页、行和列中不同元素，已达到快速查看源数据的不同统计结果的目的。同时还可以随意显示和打印出所需区域的明细数据，从而使得分析、组织复杂的数据更加快捷和有效。使用数据透视表，我们不必输入复杂的公式和函数，仅仅通过向导就可以创建一个交互式表格。
1.什么是数据透视表
数据透视表是一种在Excel中对大量数据快速汇总和建立交叉列表的交互式表格，是一种多维式表格。数据透视表能帮助用户分析、组织数据，利用它可以快速地从不同角度对数据进行分类、汇总，尤其适用于对数据记录数量众多、以流水账形式记录和结构复杂的工作表或者数据库数据，它可以将其中的一些内在的规律显示出来。
2.数据透视表基本结构
⑴字段
字段是指在数据透视表或数据透视图报表中，来源于源数据中字段的一类数据，数据透视表有行字段、列字段和数据字段。数据透视图具有系列字段、分类字段也字段和数据字段。
数据透视表的字段名称包括源数据中数据区域第一行割裂的字段名称，也可能包括用户自己定义的计算字段。不论是原有字段还是自定义字段，他们都出现在数据透视表的字段列表中。
⑵项
项是指在数据透视表字段的子分类或成员。在本节案例中“1班”、“2班”等就是字段“班级”中的项，“男”、“女”就是字段“性别”的项。
需要注意的是，不论是行字段的项还是列字段的项，它们不仅包含源数据现有的全部项，而且也可能包含用户自己定义的计算项。
⑶行字段
行字段是指来自源数据且在数据透视表中被指定为行方向的字段。在本节案例中““班级”就是行字段。
包含多个行字段的数据透视表具有一个内部行字段，它离数据区最近。任何其他行字段都是外部行字段。内部行字段和外部行字段具有不同的属性。外部行字段中的项只显示一次，而其它字段中的项则可按需要显示多次。
行字段列表中每一项即为行字段的项。通过单击行字段右侧的下三角按钮，我们可以选择该字段的某个项或者某些项。
⑷列字段
列字段是指数据透视表中被指定为列方向的字段。
列字段列表中每一项即为列字段的项，通过单击列字段右侧的下三角按钮，我们可以选择该字段的某个项或者某些项。
⑸页字段
页字段是指被分配到或筛选方向上字段。页字段允许筛选整个数据透视表，显示单个项或所有项的数据。在本案例中“性别”就是页字段，他可用来按性别筛选报表，通过使用“性别”字段，用户可以只显示男生、女生或全体学生的汇总数据，如果单击页字段的其他项，整个数据透视表报表都会发生变化，以便只显示与该项关联的汇总数据。
页字段列表中每一项即为页字段项。通过单击页字段右侧的下三角按钮，我们可以选择该页字段的某个项。与行字段和列字段不同的是，页字段每次只能选择一个项。而行字段和列字段可以每次选择一个项或者多个项。
⑹数据字段
数据字段是指汇总数据清单所指定的数值型字段。数据字段提供要汇总的数据值。通常，数据字段包含数字，可用SUM汇总函数合并这些数据。但数据字段也可以包含文本，此时数据透视表使用COUNTA汇总函数。
如果报表有多个数据字段，则报表中会出项名称为“数据”的字段按钮，用来访问所有数据字段。例如本案例的数据透视表中就出现了名称为“数据”的字段按钮，单击此按钮右侧的下三角按钮，即可看到到“全部”、“60分以下”、“60--70”、“70--80”、“80--90”、“90--100”即分数段的每一个分段标准。
⑺数据区
数据区是指数据透视表报表中包含汇总数据的部分。数据区中的单元格显示了行和列字段中各项的汇总数据。数据区的每个值都代表了源记录或行中的一项数据的汇总。例如本案例中B5单元格就包含了“1班”期末考试成绩在 “60分以下”的学生人数。
⑻拖放区域
拖放区域是完成“数据透视表报表和数据透视图向导“后所看到的蓝色分级显示区域，如下图所示，若要调整数据透视表报表布局，除了从向导的布局对话框中进行布局外，还可以从字段列表窗口拖动字段并将其放到所需区域。
⑼字段列表
字段列表列出了数据源中可供在数据透视表报表中使用的所有字段，如下图所示，如果按明细数据的级别组织，则单击”+”或”-”显示或隐藏较低级别。若要显示数据透视表报表中的某个字段的数据，则将该字段从字段列标中拖到某个拖放区域即可。
字段列表包含了数据透视表报表的所有字段，不论是源数据中已有字段，还是后来用户自定义的计算字段。
如果在数据透视表报表工作表界面没有出现字段列表面板，可以在数据透视表报表中单击鼠标右键，在弹出的快捷菜单中选择“显示字段列表“命令，如下图３３所示。或者单击”透视表报表“工具栏上的“显示字段列表“按钮。
如果想隐藏已经显示的字段列表，请在数据透视表报表单击右键，在弹出的快捷菜单中选择“隐藏字段列表“命令，或者单击”“数据透视表”工具栏上的“隐藏字段列表“按钮。
⑽“数据透视表”工具栏
“数据透视表”工具栏是非常实用的，利用这个工具栏，我们可以方便地对数据透视表报表进行设置。“数据透视表”工具栏的结构如下图所示。
如果在数据透视表工作表界面没有出现“数据透视表”工具栏，可以在数据透视表报表中单击鼠标右键，在弹出的快捷菜单中选择“显示数据透视表工具栏“命令，或者单击”视图“→“工具”→“数据透视表”命令。
|
