友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
Excel word ppt office使用技巧大全(DOC格式)-第85部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！
仅靠“记录单”进行检索就不能满足你的需要。此时可以使用 Excel 的自动筛选功能，具体操
作方法是：点击―数据→筛选→ 自动筛选‖菜单命令，数据清单的列标记（字段）右侧会显示一个
下拉按钮。如果你需要将总分大于 500 分的记录全部筛选出来，可以点击―总分‖下拉按钮，选
择― 自定义‖选项打开― 自定义自动筛选方式‖对话框。点击―总分‖下面第一行右侧的下拉按钮，
选择―大于‖选项，然后在其右边的框内输入―500‖，选中两行中间的―与‖后确定，工作表就会
显示所有符合筛选条件的记录。
当然，筛选出来的记录还可以再次筛选。假如你要将总分大于 500 分中―性别‖为―女‖ 的记
录筛选出来。可以按相同方法打开―性别‖下拉列表选择―女‖，则―张晓菲‖ 的记录就会显示在工
作表中。
自动筛选出来的数据可供进一步分析，也可以打印或复制到其他工作表。如果你要清除筛
501
…………………………………………………………Page 502……………………………………………………………
选结果，点击“数据→筛选→自动筛选”菜单命令即可。另外，执行数据自动筛选时，如果“自
定义自动筛选方式”对话框中的两个条件需要同时满足，则应选中“与”选项，否则应当选中
“或”选项。
4。高级筛选
虽然“自动筛选”操作简单，但是可供使用的筛选条件有限。为此，Excel 提供了―高级筛
选‖功能。它能够使用各种条件对数据清单进行筛选，其功能强大和使用灵活远非― 自动筛选‖
可比。
（1）条件区域：
使用高级筛选必须在工作表中构造区域，它由条件标记和条件值构成。条件标记和数据清
单的列标记相同，可以从数据清单中直接复制过来；条件值则须根据筛选需要在条件标记下方
构造，是执行高级筛选的关键部分。
构造高级筛选的条件区域需要注意：如果条件区域放在数据清单的下方，那么两者之间应
至少有一个空白行。如果条件区域放在数据清单的上方，则数据清单和条件区域之间也应剩余
一个或几个空白行（一般不要这样设计，这样影响其他功能使用数据清单） 。
（2）单列多条件：
如果某一个条件标记下面输入了两个或多个筛选条件，我们将其称为单列多条件，你只要
在条件标记下自上而下依次输入筛选条件即可。例如你需要列出图 1 中年龄大于 17 和年龄小
于 17 的学生名单，只需在图 1 条件标记―年龄‖ 的下方D10 和 D11 单元格输入―》17‖和―=85‖即可。
（4）多行单条件：
构造高级筛选条件有这样的要求：如果多个筛选条件需要同时满足，它们必须分布于条件
区域的同一行，这就是所谓的“与”条件，否则筛选条件必须分布于条件区域的不同行，也就
是“或”条件。假如你要在图 1 中找出―性别‖是―男‖，或―语文‖成绩―》=80‖，或―化学‖成绩―》=90‖
的所有记录。可以在图 1 条件区域的 C10 单元格内输入―男‖，然后在G11 单元格内输入―》=80‖，
最后在 J12 单元格内输入―》=90‖即可。
（5）两列两组条件：
如果你需要在图 1 中寻找物理成绩大于等于90 的男生或者物理成绩大于等于 80 的女生，
可以按照如下方法构造条件区域。C10、C11 单元格内分别输入―男‖和―女‖，接着在H10 、H11
单元格内分别输入―》=90‖和―》=80‖即可。
（6）执行筛选操作：
数据清单和条件区域建立完毕，你就可以执行数据筛选任务了，具体操作方法是：点击“数
据→筛选→高级筛选”菜单命令，在打开的对话框中首先选择筛选结果的显示方式，若选择“在
原有区域显示筛选结果”，则像自动筛选那样在数据清单中显示结果；否则就要在“复制到”
框内指定筛选结果的显示位置。接着点击“数据区域”框，选中数据清单所在的区域，使该区
域的绝对引用进入框内；再用相同方法完成对“条件区域”的绝对引用。点击“确定”按钮，
就会在你选择的位置显示筛选结果。
如果你选择“在原有区域显示筛选结果”，则筛选完成后只能看到结果而看不到原来的数
据清单。此时点击“数据→筛选→全部显示”菜单命令，即可恢复原来的数据清单。
二、数据的分析与求解
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 自动进入― 目标单元格‖ 。接着在对�
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！