第15章Excel应用综合示例.pptVIP

第15章 Excel应用综合示例 在逐项介绍完Excel的统计分析功能后，需要对其有一个全面的把握和回顾，这就需要在实际应用中去综合这些统计分析的方法，在实践中得到提高，本章就是从这个目的出发，通过对一些实际应用例子的分析来回顾和综合Excel统计分析功能，实现对其操作的整体提升 15.1 利用函数快速处理数据 利用函数解决问题的例子很多，例如在老师的日常工作中，对学生成绩进行统计分析管理是一项非常重要也是十分麻烦的工作，Excel强大的数据处理功能可以让老师方便迅速的实现学生成绩的管理。一般来说，录入各科成绩之后，统计及分析流程包括基本计算，例如总分，平均分等；名次排列；简单的统计分析，统计各科分数段人数，及格率，优秀率等；进行简单的图表制作以及输出成绩单等。这些问题都可以通过相应的函数得到解决，本节就分别介绍利用Excel 2007实现上述统计功能的函数及其操作方法。 15.1.1 基本计算及排名 在Excel中录入成绩之后，一般要进行总分、各个同学的均分以及各科均分的计算，并且对分数进行排名。例如，某班考试成绩录入如图15.1所示。 15.1.2 各种指标计算 在进行学生成绩统计时，除了每位同学的均分及总分之外，很多情况下还需要针对各个科目进行一些指标计算来对各科的成绩进行评价。平均分，最高分，最低分，及格率，优生率等指标都是表征成绩的重要参数。在Excel中，这些指标的统计很容易实现，下面以图15.1中的成绩录入表中各指标的计算为例逐一介绍。 平均分：在单元格D29中，输入公式“=AVERAGE (D3:D28)”，拖动填充柄向右填充至I29即可得到所有录入科目的平均分。 最高分：在单元格D30中，输入公式“=MAX(D3:D28)”，拖动填充柄向右填充至I30即可得到所有录入科目的最高分。 最低分：在单元格D31中，输入公式“=MAX(D3:D28)”，拖动填充柄向右填充至I31即可得到所有录入科目的最低分。 及格率：在单元格D32中，输入公式“=COUNTIF(D3:D28,=60)/COUNT (D3:D28)*100”，拖动填充柄向右填充至I32即可得到所有录入科目的及格率。 优生率：在单元格D33中，输入公式“=COUNTIF(D3:D28,=85)/COUNT (D3:D28)*100”，拖动填充柄向右填充至I33即可得到所有录入科目的优生率。结果如图15.8所示。 15.1.3 分数段统计 很多情况下，老师们都希望了解各科的成绩分布，即各科各个分数段的学生人数，以对整体的成绩及学生的水平进行粗略的把握，这就需要用到Excel中具有分段统计功能的函数，包括：COUNTIF函数，DCOUNT函数，FREQUENCY函数，SUM函数等。以图15.1的成绩统计表的分段统计为例，下面分别介绍各个函数的实现过程。 1．用COUNTIF函数统计 2．用DCOUNT函数统计 3．用FREQUENCY函数统计 4．用SUM函数统计 15.1.4 图表分析 通过函数的使用对各科的分数段的学生数据进行统计之后，为了对该结果有一个直观的认识，还可以采用图表来表示。本例通过语文成绩的分数段图表说明图表的制作过程。具体操作方法如下： （1）选中要插入图表的单元格区域C35:D39。 （2）在【插入】菜单中【图表】组中单击【饼图】的下拉菜单，选择【三位饼图】中的第一个，Excel自动做出如图15.14所示的饼图。 （3）单击该饼图，在【设计】选项卡中的【图表布局】组中，单击【快速布局】的下拉菜单，可根据自己的喜好重新进行图表布局，例如，本例中选择有标题的布局形式，如图15.15所示。 除了饼图之外，还可以根据喜好选择柱状图等其他图表类型进行成绩分析。 15.1.5 输出成绩条 一般来说，老师除了要对考试成绩进行分析之外，还要将学生的考试输出并将成绩条发给每个学生，Excel中的输出成绩功能能够非常迅速的实现成绩条的制作。具体操作方法如下： （1）切换至新的工作表“成绩条”中。 （2）选中单元格A1，在编辑栏中输入公式“=IF(MOD(ROW(),3)=0,,IF(MOD(ROW(), 3)=1,学生成绩!A$2,INDEX(学生成绩!$A:$L,(ROW()+4)/3+1,COLUMN())))”，单击【确定】按钮。 （3）拖动填充柄将A1单元格的公式向右复制到单元格区域B1:L1中。 （4）将A1单元格的公式复制到A2:L2单元格区域中。 （5）选中A1:L2单元格区域，利用自动填充功能向下填充直至输出所有学生的成绩。结果如图15.16所示。 15.1.6 保存模板 在做好上述的工作表之后，为了以后的成绩分析不再重复上述繁琐的工作，可以把做好的工作表保存为模板，具体操作方法如下： （1）将“学生成绩”工作表复制一份到