EXCEL数据透视表，分析成绩快又好 Office 2003 2010 Excel精选教程

开学了，考试的成绩得好好分析一下，教务处还需要分析报告呢！这难不倒小荷，因为她是Excel高手，而且她知道，只要请出数据透视表，不必输入复杂的函数与公式，仅仅通过向导就可以创建一个交互式表格，用鼠标拖拖拉拉就能完成对成绩的快速统计分析。

*准备工作：
输入必要的数据，工作表命名为“总表”，以D列的政治分数（满分100）为例，还要对源数据进行补充设计。在单元格E1～I1分别输入60以下(差)、60分及以上（及格）、60-79（中等）、80-89（良好）、90分以上（优秀）。然后在单元格E2～I2分别输入下面的公式：E2：=IF(D2<60,1,0)，F2：=IF(D2>=60,1,0)，G2：=IF(AND(D2>=60,D2<=79),1,0)，H2：=IF(AND(D2>=80,D2<=89),1,0)，I2：=IF(D2>=90,1,0)。最后选择单元格区域E2～I2，将其向下填充复制到区域E264～I264。

*基础分析

图1

图2
如何计算8个班的政治实考人数、最高分、最低分、平均分呢？
首先单击数据区域的任意单元格，选择菜单“数据→数据透视表和数据透视图”，三个步骤都保持默认，将看到一张空的数据透视表，把此工作表命名为“基础分析”。
接下来单击空表区域内的任意位置，将会出现放置不同字段类型的提示，并且显示“数据透视表字段列表”对话框，将“班级”字段拖入“将行字段拖到此处”中，将“政治”字段拖入“请将数据字段拖到此处”中。共需拖4次，因为要对政治实考人数、最高分、最低分、平均分4个项目进行统计分析（见图1）。
在B4单元格“求和项：政治”上右击，选择“字段设置”菜单项，在弹出的“数据透视表字段”对话框中，选择汇总方式为“计数”，确定。依次把“求和项:政治2”的汇总方式选择为“最大值”；把“求和项:政治3”的汇总方式选择为“最小值”；把“求和项:政治4”的汇总方式选择为“平均值”，单击“ ”按钮，弹出“单元格格式”对话框，根据需要设置单元格格式即可（这里保留2为小数）。
最后，拖动字段改变数据透视表布局，把“数据”字段拖到“汇总”字段上即可（见图2）。可以根据需要对第四行的字段进行必要的修改。

*深入分析
1.每个班的优、良、中、差、及格人数
刚才的分析还不够，小荷还要深入分析其他的指标。选中数据透视表所在的A1:E13单元格区域，单击菜单“编辑→复制”，切换到另一工作表内，在任意单元格右击，选择“粘贴”菜单项，即可得到一个复制的透视表，把“数据”字段拖到数据透视表区域外，并把此工作表命名为“深入分析”。把“60以下(差)”字段拖入“请将数据字段拖到此处”中，依次把字段“60分及以上（及格）”、“60-79（中等）”、“80-89（良好）”、“90分以上（优秀）”字段都拖入到“请将数据字段拖到此处”中。最后利用替换法把“求和项:”替换成一个空格，这样每一个班的“优、良、中、差、及格人数”就出来了。

2.每个班的差生率、及格率、中等率、良好率、优秀率

图3

图4
单击工作表“深入分析”内的数据透视表中的任意一个单元格，然后选择“数据透视表→公式→计算字段”，打开“插入计算字段”对话框，在插入计算字段”对话框的“名称”框内输入“差生率”，将光标定位到“公式”框中，清除原有的数据“=0”，在字段列表框中双击“60以下(差)”字段，输入“/”，再输入“(+)”，把鼠标定位到 “+”的前面，双击字段列表框中的“60以下(差)”字段，然后把鼠标定位到 “+”的后面，双击字段列表框中的“60分及以上（及格）”字段。此时“公式”框中显示“='60以下（差）'/('60以下（差）'+'60分及以上（及格）')”（见图3，此公式的含义是不及格的人数/班级人数为“差生率”）。接下来依次创建及格率、中等率、良好率、优秀率等计算字段。根据需要设置“差生率”、“及格率”、“中等率”、“良好率”、“优秀率”的单元格格式（这里保留2为小数，百分比，见图4）。

3.以每隔5分统计各分数段

图5
从30分～100分每隔5分统计各班各分数段人数，类似高考的一分一段统计。
小荷根据上面的常规操作再创建一个空的数据透视表，并把此工作表命名为“分数段”。将“班级”字段拖入“将列字段拖到此处”中；将“政治”字段拖入“将行字段拖到此处”中；再将“政治”字段拖入“请将数据字段拖到此处”中。
然后她在B4单元格“求和项：政治”上单击鼠标右键，选择“字段设置”菜单项，在弹出的“数据透视表字段”对话框中，选择汇总方式为“计数”，单击“确定”按钮。
接下来选中在“政治”字段的任意一个分数，右击，选择“组及显示明细数据→组合”菜单项，在“起始于”编辑框中输入“30”，在“终止于”编辑框中输入“100”，然后设置“步长”为“5”，单击“确定”按钮关闭对话框（见图5）。

为了检验教学质量，校长决定增加月考次数，每次都要对成绩进行统计分析。小荷笑着说：“没问题！我用透视表快速汇总大量数据，并随时选择页、行、列中的不同的字段，快速查看数据的不同统计结果，和函数结合使用简直太省力了！其他哪位老师需要我帮忙统计吗？”