EXCEL入门教程3

1、北京师范大学北京师范大学微软办公软件国际认证微软办公软件国际认证Excel数据分析数据分析l自动筛选自动筛选l高级筛选高级筛选l数据透视表数据透视表l数据透视图数据透视图l分析工具库的使用分析工具库的使用 “描述统计描述统计”工具工具 “直方图直方图”工具工具 “抽样抽样”工具工具 “排位与百分比排位排位与百分比排位”工具工具 “回归回归”工具工具l 方案管理方案管理 1. 自动筛选自动筛选l自动筛选和高级筛选的结果，是只显示符合条自动筛选和高级筛选的结果，是只显示符合条件的数据，将不符合条件的数据隐藏起来。件的数据，将不符合条件的数据隐藏起来。l自动筛选的操作方法：自动筛选的操作方法：在数据

2、区域中单击任一单元格，单击在数据区域中单击任一单元格，单击 “数据数据” “排序和筛选排序和筛选” “筛选筛选”，则每一列数，则每一列数据顶端的项目名称右侧会出现一个下拉列表按据顶端的项目名称右侧会出现一个下拉列表按钮。根据需要从中选择及设定条件，则符合条钮。根据需要从中选择及设定条件，则符合条件的数据行就被筛选出来了，其余数据被隐藏件的数据行就被筛选出来了，其余数据被隐藏。1. 自动筛选自动筛选操作操作打开打开“22自定义筛选自定义筛选.xls”文件文件1）筛选出年龄最小的）筛选出年龄最小的3个记录；个记录；2）筛选出姓陈的记录；）筛选出姓陈的记录；3）筛选出工龄在）筛选出工龄在10到到15

3、年的记录；年的记录；4）筛选出女研究生的记录。）筛选出女研究生的记录。1. 自动筛选自动筛选小结小结l自动筛选能解决的问题：针对一个字段的一自动筛选能解决的问题：针对一个字段的一个条件；针对一个字段的两个条件；针对相个条件；针对一个字段的两个条件；针对相“与与”关系的两个字段条件；关系的两个字段条件；l小结自动筛选不能解决的问题：针对一个字小结自动筛选不能解决的问题：针对一个字段有三个及以上条件；针对相段有三个及以上条件；针对相“或或”关系的两关系的两个字段条件。个字段条件。 2. 高级筛选高级筛选l高级筛选的操作方法高级筛选的操作方法先把筛选的条件输入到表格中的一个空白区域先把筛选的条件输入

4、到表格中的一个空白区域中，然后单击中，然后单击 “数据数据” “排序和筛选排序和筛选” “高级高级” ，在弹出的，在弹出的“高级筛选高级筛选”对话对话框进行相应设置即可。框进行相应设置即可。操作操作1：打开：打开“22自定义筛选自定义筛选.xls”文件，在原区文件，在原区域分别筛选出符合以下条件的记录：域分别筛选出符合以下条件的记录：1）姓陈）姓陈的；的；2）工龄在）工龄在10到到15年；年；3）女研究生。）女研究生。 题题1）操作方法：第一步）操作方法：第一步将原数据库的标题行复制将原数据库的标题行复制到空白区域；在到空白区域；在“姓名姓名”字字段下键入条件段下键入条件“陈陈*”；执行；执行

5、高级筛选命令，如右图设高级筛选命令，如右图设置，得到筛选结果如下图置，得到筛选结果如下图操作操作1：打开：打开“22自定义筛选自定义筛选.xls”文件，在原区文件，在原区域分别筛选出符合以下条件的记录：域分别筛选出符合以下条件的记录：1）姓陈）姓陈的；的；2）工龄在）工龄在10到到15年；年；3）女研究生。）女研究生。 题题2）的）的“条件区域条件区域”设置如下：设置如下：题题3）的）的“条件区域条件区域“设置如下：设置如下：小结小结：同行的多个条件之间是相：同行的多个条件之间是相“与与”的关系。的关系。操作操作2：打开：打开“22自定义筛选自定义筛选.xls”文件，在原区文件，在原区域分别筛

6、选出符合以下条件的记录：域分别筛选出符合以下条件的记录：1）年龄）年龄小于小于25或者大于或者大于40且小于且小于50；2）籍贯是江苏）籍贯是江苏的或者性别为男的。的或者性别为男的。 小结小结：不同行的多个条件之间是相：不同行的多个条件之间是相“或或”的关系。的关系。题题1）的）的“条件区域条件区域”： 题题2）的）的“条件区域条件区域”：思考题：写出以下条件区域的条件含义思考题：写出以下条件区域的条件含义操作操作3：打开：打开“22自定义筛选自定义筛选.xls”文件，在原区文件，在原区域分别筛选出符合以下条件的记录：域分别筛选出符合以下条件的记录：1）工龄）工龄大于平均工龄；大于平均工龄；2

7、）年龄最大的）年龄最大的3个。个。3）年龄）年龄大于平均年龄或籍贯是北京的。大于平均年龄或籍贯是北京的。 小结小结：所有自动筛选和高级筛选的条件都可以：所有自动筛选和高级筛选的条件都可以用函数或公式的形式描述用函数或公式的形式描述,再进行高级筛选。再进行高级筛选。题题1）的）的“条件区域条件区域”： 题题2）的）的“条件区域条件区域”：题题3）的）的“条件区域条件区域”： 3. 数据透视表数据透视表对源数据表是对源数据表是“重叠重叠”数据、数据、“流水帐流水帐”数据的情况，怎么办？数据的情况，怎么办？l操作方法操作方法选定数据表中任一单元格，执行选定数据表中任一单元格，执行“插入插入”“表格表

8、格” “数据透视表数据透视表”命令，跟着命令，跟着“向导向导”建立数据透视表。建立数据透视表。建立数据透视表最有效。建立数据透视表最有效。 3. 数据透视表数据透视表 操作操作打开打开“2424数据透视表数据透视表.xls”.xls”文件，建立数据透视表，文件，建立数据透视表，要求将要求将“业务姓名业务姓名”作为行，作为行，“交易年交易年”作为列，作为列，数据项是数据项是“数量数量”计算平均值。计算平均值。小结小结熟练使用熟练使用“数据透视表数据透视表”工具中的工具中的“选项选项”和和“设计设计”选项卡。选项卡。 4. 数据透视图数据透视图以图表的形式显示汇总后的数据项，更清晰。以图表的形式显

9、示汇总后的数据项，更清晰。l操作方法操作方法选定数据表中任一单元格，执行选定数据表中任一单元格，执行“插入插入”“表格表格” “数据透视图数据透视图”命令，跟着命令，跟着“向导向导”建立数据透视图。建立数据透视图。 4. 数据透视图数据透视图操作操作打开打开“2424数据透视表数据透视表.xls”.xls”文件，建立数据透文件，建立数据透视图，要求将视图，要求将“业务姓名业务姓名”作为行（分类轴）作为行（分类轴），“交易年交易年”作为列（系列轴），数据项是作为列（系列轴），数据项是“数量数量”计算平均值。计算平均值。 5. 分析工具库的使用分析工具库的使用安装分析工具库安装分析工具库 点击点击

10、“文件文件”“选项选项” “加载项加载项”；在在“管理管理”框中，选择框中，选择“Excel 加载宏加载宏”，再单击再单击“转到转到”；在在“可用加载宏可用加载宏”框中选中框中选中“分析工具库分析工具库”复选框，然后单击复选框，然后单击“确定确定”。 5. 分析工具库的使用分析工具库的使用安装完毕分析工具库之后，在安装完毕分析工具库之后，在 “数据数据”选项卡中增加选项卡中增加“分析分析”组，其中新增组，其中新增“数据分数据分析析”命令，执行该命令，弹出命令，执行该命令，弹出“数据分析数据分析”对对话框，里面的多种数据分析工具可以满足不同话框，里面的多种数据分析工具可以满足不同的需要。的需要。

11、5-1. “描述统计描述统计”工具工具l“数据分析数据分析”中的描述统计工具，可以统计中的描述统计工具，可以统计选定数据的平均值、标准误差、方差、最大选定数据的平均值、标准误差、方差、最大值、最小值等等。值、最小值等等。操作：打开操作：打开“26分析工分析工具库具库.xls”文件，用文件，用“描描述统计述统计”处理处理“平均平均”字段的所有数据。字段的所有数据。5-2. “直方图直方图”工具工具l “直方图直方图”工具的功能是分析选定区域数据在工具的功能是分析选定区域数据在某些区间的频度及叠加百分比，结果以表格和某些区间的频度及叠加百分比，结果以表格和图表的形式给出，直观、清晰。图表的形式给出

12、，直观、清晰。l 先在空白区输入数据接受序列，就是分段统计先在空白区输入数据接受序列，就是分段统计的数据间隔，该区域包含一组用来定义分段区的数据间隔，该区域包含一组用来定义分段区间的边界值（这些值应当按升序排列）。间的边界值（这些值应当按升序排列）。l 再选择再选择“数据分析数据分析”对话框中对话框中“直方图直方图”，弹，弹出出“直方图直方图”对话框，做相应设置。得到统计对话框，做相应设置。得到统计结果和图表，需要做相应调整。结果和图表，需要做相应调整。操作操作：打开：打开“26分析工具库分析工具库.xls”文件，用文件，用“直方直方图图”分析分析“程序程序”字段数据，区间间隔点为字段数据，区

13、间间隔点为0、60、80、100。5-3. “抽样抽样”工具工具l “抽样抽样”工具实现在输入区域中抽取一定数量的工具实现在输入区域中抽取一定数量的数据。数据。操作操作： 在在“座号座号”字段中字段中抽取抽取10个随机产生的数个随机产生的数据，存在以据，存在以A36为左上角为左上角的区域中。的区域中。5-4. “排位与百分比排位排位与百分比排位”工具工具l 工具作用：产生一个数据表，在其中包含数工具作用：产生一个数据表，在其中包含数据集中各个数值的顺序排位和百分比排位。据集中各个数值的顺序排位和百分比排位。该工具用来分析数据集中各数值间的相对位该工具用来分析数据集中各数值间的相对位置关系置关系

14、 。操作操作对对“平均平均”字段进字段进行行“排位与百分比排位与百分比排位排位”处理。处理。l结果及说明：结果及说明： “点点”是指排序后原数据的序数，即是指排序后原数据的序数，即“座号座号”；“平均平均”即为排序后的数据系列；即为排序后的数据系列； “排位排位”采取重复数据占用同一位置的统计方法；采取重复数据占用同一位置的统计方法； “百分比百分比”是按照降序排列的，为了得到真正的是按照降序排列的，为了得到真正的“百分比排位百分比排位”，作了一下调整：人工第五列，作了一下调整：人工第五列，值为值为1减第四列数据。减第四列数据。5-5. “回归回归”工具工具l 回归分析工具通过对一组观察值使用

15、回归分析工具通过对一组观察值使用“最小最小二乘法二乘法”直线拟合来执行线性回归分析。本直线拟合来执行线性回归分析。本工具可用来分析单个因变量是如何受一个或工具可用来分析单个因变量是如何受一个或几个自变量影响的。几个自变量影响的。操作操作：完成模拟试题：完成模拟试题15第第1小题。小题。 加载宏，然后使用加载宏，然后使用“分析工具库分析工具库”来执行来执行“回归回归”分析，以决定分析，以决定“冰淇淋销售量冰淇淋销售量”（从（从属变量，属变量，Y）与）与“气温气温”（独立变量，（独立变量，X）之间）之间的相互关系。（注意，接受所有的默认设定）的相互关系。（注意，接受所有的默认设定） 6. 方案管理

16、方案管理方案管理也称假设分析，通过假设几套不同方案管理也称假设分析，通过假设几套不同的方案，查看不同的结果，比较方案的优劣。的方案，查看不同的结果，比较方案的优劣。l创建方案创建方案“数据数据” “数据工具数据工具” “模拟分析模拟分析” “方案管理器方案管理器”，弹出，弹出“方案管理器方案管理器”对话对话框；框；单击单击“添加添加”， 在在“方案名方案名”框中，键入方框中，键入方案名称，在案名称，在“可变单元格可变单元格”框中，输入对需要框中，输入对需要更改的单元格的引用，然后单击更改的单元格的引用，然后单击“确定确定”一个一个方案就创建好了；方案就创建好了； 重复以上步骤创建多个方案。重复

17、以上步骤创建多个方案。 6. 方案管理方案管理l 创建方案总结报表创建方案总结报表“数据数据” “数据工具数据工具” “模拟分析模拟分析” “方案管理器方案管理器”，弹出，弹出“方案管理器方案管理器”对对话框；话框；单击单击“摘要摘要” “方案摘要方案摘要”或或“方案数据方案数据透视表透视表”。在。在“结果单元格结果单元格”框中，输入单框中，输入单元格的引用，该单元格为方案中元格的引用，该单元格为方案中 用于更改结用于更改结果的单元格。多个引用需用逗号分隔开。果的单元格。多个引用需用逗号分隔开。 6. 方案管理方案管理注意注意公式的引用单元格是公式的引用单元格是“可变单元格可变单元格”，不同方，不同方案的可变单元格的值不同；公式单元格就是案的可变单元格的值不同；公式单元格就是“摘要摘要”的的“结果单元格结果单元格”。操作操作打开打开“25方案