按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
就会在你选择的位置显示筛选结果。
如果你选择“在原有区域显示筛选结果”，则筛选完成后只能看到结果而看不到原来的数
据清单。此时点击“数据→筛选→全部显示”菜单命令，即可恢复原来的数据清单。
二、数据的分析与求解
Excel 提供了多种数据分析手段，从函数、分析工具库、加载宏等，一直到数据透视表和
503
…………………………………………………………Page 504……………………………………………………………
数据透视图。下面介绍以函数和加载宏为主的若干分析工具。
1。成绩分布频率分析
学生成绩分析的一项重要任务，就是统计各分数段中的人数，为研究成绩分布提供基础数
据。下面以图 1 为例，说明如何计算 550～500、500～450 、450～400 、400～350，以及小于
350 分数段内的人数。
首先在图 1 中的M2 ：M6 区域依次输入 550、500、450 、400 、350，表示统计上述分数段
内的成绩个数。当然，你可以根据数据分析的具体要求，选择其他方式划分数据分段方法。接
着在 M2：M6 区域右侧预留相同大小的单元格区域（N2 ：N6） ，作为存放数据统计结果的位置。
上述工作完成后，选中存放统计结果的区域 （N2 ：N6） ，在编辑栏内输入公式
―=FREQUENCY（L2：L7 ，M2：M6） ，最后让光标停留在公式的末尾。按下 Shift＋Ctrl 键敲回车，
编辑栏内将显示―｛=FREQUENCY（L2：L6 ，M2：M6）｝‖（大括号表示这是一个数组公式） ，N2 ：N6
区域就会显示各分数段中的成绩个数。
注意：数组公式―｛=FREQUENCY（B2：B6，C2：C6）｝‖可以对一组或多组数值进行多重计算，
并得出一个或多个计算结果。数组公式中的大括号―｛
｝‖不能用手工输入，只能按组合键 Shift＋Ctrl＋Enter 自动生成。
2。对象相关性分析
教育教学研究的任务之一是了解各学科之间的相互关系，例如数学的学习是否对物理成绩
有影响。而商品销售领域，也需要分析两组数据（单位可以不同）之间是否相关。例如空调和冷
饮的销售量与气温之间的关系，或者商场的客流人数与销售额是否相关等等。
这里仍然以图 1 所示的―学籍管理表‖为例，我们的任务是分析这些学生的物理成绩是否与
数学成绩相关。分析的操作方法是：选中数据清单中的一个空白单元格，在编辑栏内输入公式
―=CORREL（F2：F7，I2：I7）‖，回车即可得到数学和物理成绩的相关系数。这个计算结果有以下
504
…………………………………………………………Page 505……………………………………………………………
三种情况：如果物理成绩中的高（低）分与数学成绩中的高（低）分对应，说明这两个数据集合是
正相关。计算结果应该是一个小于 1 的实数，它越接近于 1，说明两者相关性越强；如果物理
成绩中的低（高）分与数学成绩中的高（低）分对应，说明这两个数据集合是负相关。计算结果应
该是接近…1 的实数，它越接近于…1，说明两者负相关越强；如果上述计算结果接近零，说明这
两个集合中的数据互不相关。另外，为了保证分析结果的准确性。用于分析的两个数据集合中
的数据个数不能太少，一般应在 30 个以上。
如果你将图 1 中的数学成绩换成一段时间内的商场客流量，同时将物理成绩换成同一时间
内的商场销售额。运用相同的方法进行计算，就可以得知客流量与销售额是否相关，从而为制
定营业策略提供依据。
3。方差分析
在质量检验等领域，经常需要检验两个对象的平均值是否存在差异。例如两个班级的数学
课分别采用了不同的教学方法，我们需要通过期中考试成绩检验两种方法是否存在实质性差
异，以便对教学改革的成果做出判断。再如，一种手机经过改进，我们要了解两种手机的通话
距离是否存在实质性差异。就可以随机抽取数目相同的两种手机，在不同条件下测试它们的通
话距离，从而了解手机的改进是否有效。
在上述两个例子中，影响实验结果的因素只有一个。我们将其称为单因素实验，对应的方
差分析就是单因素方差分析。下面我们以不同教法的两个班级的数学课为例，说明单因素方差
分析的操作方法：
假设这两个班级的期中考试数学成绩分别存放在 A2：A46 和 B2：B46 区域，请你将光标放
在该区域以外的任意一个单元格。点击―工具→数据分析‖菜单命令，在打开的对话框中选中―方
差分析：单因素方差分析‖，―确定‖之后打开同名对话框（如无此选项，请检查你的 Office
XP 安装方式） 。
505
…………………………………………………………Page 506……………………………………………………………
其中“输入区域”让你输入数据区域的单元格引用，它由两个或两个以上按“列”或“行”
排列的相邻数据区域组成，我们这个实例应当输入“S｜AS｜2：S｜BS｜46‖（可以用鼠标选中区域的方
法输入） ；―分组方式‖用来确定输入区域中的数据如何排列。由于两个班级的成绩分别存放在
A 、B 列，所以应当选中―分组方式‖ 中的―列‖选项；又由于 A1 、B1 单元格带有班级代码，故
应将―标志位于第一行‖选中；它下面的―0。05‖称为显著性水平，一般取默认值 0。05 即可；―输
出区域‖必须输入一个空白单元格引用，用来确定计算结果存放区域左上角的位置；如果你选
中了―新工作表组‖，就需要在右侧的框中输入该工作表的名称；若选中了―新工作簿‖，则可以
创建一个新的工作簿，并在其中粘贴计算结果。
以上设置完成后点击“确定”按钮，就可以在选定区域内输出分析结果。其中的“组间”
就是影响成绩的因素（不同教学方法） ，―组内‖就是误差，―总计‖就是总和，―差异源‖则是方差
来源，―SS‖就是平方和，―df‖称为自由度（上例为 1），―MS‖就是均方，―F‖称为 F 比，―P…value‖
则是原假设（结论）成立的概率（这个数值越接近 0，说明原假设（实验班和对照班的数学成绩没
有显著差异）成立的可能性越小，反之原假设成立的可能性越大） ，―Fcrit‖为拒绝域的临界值。
假如上面两个班级的计算结果是“P…value‖等于 0。1，因此在显著性水平 0。05 的条件下原假
设不成立，实验班和对照班的数学成绩有显著的差异，说明教学方法对成绩有显著影响。
4。实现利润分析
上面介绍的内容属于统计分析的范畴，目的是大量数据中寻找统计规律。而在企业管理等
领域，管理人员则要了解不同因素或方案对经营目标的影响。例如确定四季度的销售利润总额
以后，如何确定其他开支的数额，才能保证完成销售利润目标等。下面介绍一个分析实例：
假设某企业四季度的销售利润指标定为 1650 万元，如果其他条件保持不变，销售收入需
要增加多少。由于销售利润与销售收入不是简单的线性关系（例如销售收入增加 2 万元，销售
利润同比增加 1。5 万元） ，而要受到多种因素的制约。例如增加销售收入就要加大销售成本和
506
…………………………………………………………Page 507……………………………………………………………
费用，还要缴纳更多的营业税。使用手工解决这类问题非常麻烦，需要根据销售收入与成本的
关系逐步计算。
如果利用 Excel 的单变量求解命令，就可以快速计算出结果，甚至可以针对不同情况反复
计算。下面介绍这个问题的求解方法：设 Excel 工作表 A2 、B2 、C2、D2 和 E2 单元格分别存
放―销售收入‖、―销售成本‖、―销售费用‖、―营业税‖和―销售利润‖ 的数值。根据经验，―销售成
本‖约占―销售收入‖ 的 8％，―销售费用‖约占―销售收入‖ 的 25％ ，―营业税‖约占―销售收入‖ 的
10％。据此可以建立―销售收入‖与其他因素的数学关系，这是执行单变量求解的关键。
根 据 上 面 的 分 析 ， 可 以 选 中 E2 单 元 格 ， 在 编 辑 栏 输 入 公 式
―=A2…（A2*0。08＋A2*0。25＋A2*0。1）‖ 。点击―工具→单变量求解‖
菜单命令，在打开的对话框中可见 E2 自动进入― 目标单元格‖ 。接着在对话框的― 目标值‖
内输入―1650‖，在―可变单元格‖框内输入―S｜AS｜2‖（也可以将光标放入框中，然后点击 A2 单元
格） 。上述操作完成后点击―确定‖按钮，就会弹出―单变量求解状态‖对话框，说明已经求得一
个解，而且目标值和―当前解‖相同。与此同时，你可以在 A2 单元格中看到求出的―销售收入‖，
上面这个例子的计算结果是 2894。737 万元，即要想完成 1650 万元的销售利润指标，销售收入
必须达到 2894。737 万元。
5。成绩评价分析
上面的实例讲解的是目标设定以后，实现这个目标必须满足的条件。但是实际中往往存在
这样的问题，就是实现目标的某个或多个条件发生了变化，它会对结果产生哪些影响。
下面以图 1 所示的―学籍管理表为例‖，说明某个学生单科成绩发生的变化，会对全体学生
的总平均分有什么影响。
假如我们要分析学生“赵明君”的“数学”成绩提高到了 70、80 或 90
分，全班学生的总平均分的变化情况如何。由于这种分析只涉及一种数据（―赵明君‖ 的―数