第15章 Excel应用综合示例

第15章Excel应用综合示例•在逐项介绍完Excel的统计分析功能后，需要对其有一个全面的把握和回顾，这就需要在实际应用中去综合这些统计分析的方法，在实践中得到提高，本章就是从这个目的出发，通过对一些实际应用例子的分析来回顾和综合Excel统计分析功能，实现对其操作的整体提升15.1利用函数快速处理数据•利用函数解决问题的例子很多，例如在老师的日常工作中，对学生成绩进行统计分析管理是一项非常重要也是十分麻烦的工作，Excel强大的数据处理功能可以让老师方便迅速的实现学生成绩的管理。一般来说，录入各科成绩之后，统计及分析流程包括基本计算，例如总分，平均分等；名次排列；简单的统计分析，统计各科分数段人数，及格率，优秀率等；进行简单的图表制作以及输出成绩单等。这些问题都可以通过相应的函数得到解决，本节就分别介绍利用Excel2007实现上述统计功能的函数及其操作方法。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）将“学生成绩”工作表复制一份到另一工作簿中。•（2）删除所有学生的单科成绩，即表中D3:J28部分。•（3）单击Office图标，选择【另存为】中的【其他格式】，弹出【另存为】对话框，在【保存类型】下拉列表框中选【模板（*.xlt）】，单击【确定】按钮，模板保存工作完成。•对“成绩条”工作表也可以进行相同的操作，这样，以后只要将学生考试的原始成绩输入工作表中Excel即可进行模板中的所有操作，方便快捷的满足用户的需求。15.2处理问题方式的多样化•很多问题在Excel中可以选择不同的工具实现求解的目的，能够准确选择工具的基础是对每个分析工具的数量掌握，本节以按照一定的条件对某些特定值进行求和为例，介绍不同的条件求和的方法，有小见大，说明Excel解决问题途径的多样性。•在某单位中，在工资记录表中记录了40个职工的姓名、工作年限、学历、职称和工资等数据，其部分内容的格式如图15.17所示，要在这些数据中计算男性助理经济师的平均工资，在Excel中有很多工具可供选择，本节就介绍其中的几种。15.2.1利用函数求解•函数是Excel解决问题必不可少的工具，可以进行条件求和的函数有两种，一是AVERAGEIFS函数，二是SUM、IF和COUNTIFS函数的嵌套等，本节详细介绍这两种方法。•1．使用AVERAGEIFS函数进行求解•2．利用SUM、IF和COUNTIFS函数的嵌套进行求解15.2.2利用条件求和工具•上一小节中利用SUM函数和IF函数的结合求解满足条件的总工资数，可以看到其公式比较繁琐，容易出现错误，并且在设置更多条件时，会因IF函数嵌套过多而造成更大的麻烦，因此，在Excel中还可以使用条件求和工具对设定条件的数据进行求和，其具体操作方法如下：15.2.3利用分类汇总工具•利用前面两种工具虽然可以比较快速地求解出所要求的平均值，但是要同时求出男性工人中各个职称的平均工资，利用前两种工具就太过繁琐和复杂，在这种情况下，就可以利用分类汇总工具进行求解，其具体操作方法如下：15.2.4反复筛选法•分类汇总的方法虽然可以表征各个分类的特征，但是其分类有一定的层次性，操作是必须要按从主要到次要的分层原则进行分类，而使用反复筛选法就可以避免此问题，利用反复筛选法求解本节中满足条件的平均工资的具体方法如下：15.3在Excel中拓宽思路•在很多比较简单的问题上，通过不断的思考，可以改变解决问题的思路，并同样能够实现解决问题的目标，本节就以解决鸡兔同笼问题为例，介绍不同的求解方法，并通过此例将各个求解方法进行延伸拓展。•“鸡兔问题”是一道古典数学问题，源自我国古代四、五世纪的数学著作《孙子算经》，算经卷下第三十一题为：“今有雉、兔同笼，上有三十五头，下有九十四足。问雉、兔各几何？”其基本思想是给定鸡和兔的总数以及它们的脚的数量，来求解鸡和兔分别的数量。本节中假设兔子和鸡共有25只，且总共有68条腿，要求解此题，在Excel中可以尝试多种方法计算。15.3.1利用IF函数试探•通过IF函数试探的方法实际上就是一个不断尝试的求解过程，其具体方法如下：15.3.2利用模拟运算表进行求解•此方法的思想同样是逐个运算，但是此方法可以同时确定所有计算的结果，较上一方法更加简便，其具体实现方法如下：15.3.3利用矩阵方法求解•此问题的实质就是一个二元一次方程组，具体形式如下：15.3.4利用规划求解方法计算•利用规划求解方法同求解方程的原理一样，其约束条件就改成了两个固定的方程，利用规划求解的方法求解本解问题的具体方法如下：15.4精益求精地选择求解方法•本书中介绍了多种预测数据系列的方法，针对同一问题，如何进行预测模型的选择，需要进一步思考，本节以预测某地区经济规模为例，会运用多种模型对其求解，并最终选择合适的模型。•某地区由于经济发展，其经济规模在不断扩大，具体数据如图15.65所示，其散点图如图15.66所示，为了更准确地预测该地区的发展趋势，并估计2008年此地区的经济规模，故选用了多种模型进行比较。15.4.1一元线性回归模型•根据其散点图可以看出，数据点有良好的线性特征，因此可以选择较简单的一样线性回归模型进行预测，其具体方法如下：15.4.2一元指数回归模型•该地区的数值处于持续稳定增长时期，如果此时期是将来更快的发展速度的准备时期，此例中就可以指数模型，其具体方法如下：15.4.3一元对数回归模型•如果此地区的发展阶段处于一个发展过程的中后期，无论是线性增长还是指数增长，模型的预测水平都会有较大偏差，为了使其发展速度最后接近于某一水平，可以采用一元对数回归模型进行预测，其具体方法如下：15.4.4逻辑斯蒂回归模型•在不能确定本地区的发展阶段时，可以利用逻辑斯蒂回归分析直接结合其整个发展过程，其具体方法如下：15.4.5趋势移动平均法•由于此数据为时间数据，因此可以采用移动平均的方法进行预测，加上此数据线性明显，故可以采用趋势移动平均法进行求解，其具体方法如下：15.4.6各种模型的比较•本节介绍了在预测经济规模中的几种常用模型，可以看出线性模型和指数模型在短期内的预测是较为理想的，但对于不可能无限制发展的经济规模，两者在长期预测方面还有欠缺，这就需要使用对数模型和逻辑斯蒂模型进行预测，而通过时间序列分析同样可以得到理想的效果。15.5用随机数模拟世界•随机数的使用目的就是为了取代大量的随机实验，如何真正的将其运用到实际领域中，需要对随机数有深入的了解，本节先会介绍比较简单的天气预报的模拟，随后以蒙特卡洛模拟的基本运算思想为例，介绍随机事件的模拟方法。15.5.1简单事件的模拟•以简单的天气预报为例，在今后的三天中，如果每一天下雨的概率均为40%，要求这三天中恰有两天下雨的概率大概是多少，其具体方法如下：15.5.2复杂事件的模拟原理•很多情况下，需要模拟的事件不仅仅是受一个因素，而是受多个因素影响，所以需要更复杂的方法来实现对复杂时间的模拟。例如，每个指标是需要三个子指标相乘得到的，并且三个子指标都服从一定的统计分布，而并非确定的值，这样，所要求的指标也不会是一个确定的值，而是在一个范围内波动的变量，要模拟此指标的变化规律，具体方法如下：15.6本章小结•本章介绍了或简单或复杂的综合应用实例，分别概括各种函数的综合应用，各种模型的综合选择，各种求解方法的探索以及解决问题的不同特点，从基本的工作表函数，到回归分析，规划求解再到随机数发生器，这些内容的综合应用无疑是对Excel统计分析能力的一种发掘，是对本书的总结，也是对之前内容的综合和提升，有助于进一步发掘Excel的统计分析功能。