Excel高级操作2

五、数据处理
操作实例13
1、排序
6、数据透视表
产销量及利润汇总统计： 现有某家电企业2007-2009年在北京、天津 及上海的彩电、空调和洗衣机的产销量及利润的 统计数据。现需要实现如下功能：
（1）求出各地区每年的总利润及三年内的总利润 （2）求出各产品在三年的总产量、总销量及总利 润，并做图。
31
④ 列出住房贷款“贷款金额”和“总付款期数”各种可能的数 据，并在行与列交叉的单元格中输入目标函数PMT（） ⑤ 选择目标单元区域（A13:F19）， “数据”—”模拟运算表”
21
பைடு நூலகம்
五、数据处理
5、模拟运算表
结果如图
1、排序
5、模拟运算表
22
五、数据处理
5、模拟运算表
操作实例11
1、排序
5、模拟运算表
2
五、数据处理
五、数据处理
数据处理就是对表中的数据进行查询、排序、 筛选、分类汇总、模拟运算表、数据透视表、单变 量求解、规划求解等操作。 说明：进行数据处理时，工作表中的数据必须以数据 清单的形式存在的。（如：学生成绩表.xls）
数据清单：即第一行是列标题（字段名），第一行之外
的每一行（称为一个记录）是描述一个人或事物的相 关信息；每一列具有相同的数据项（字段）
5、模拟运算表
操作实例12
1、排序
5、模拟运算表
产品销售价格 月销售数量

现有某种产品，造价 为218元，其最终产品销 售价格与每月销售数量对 应关系如表所示： 问题是：该产品以哪种 价格销售获取利润最高？
300
320 340 350 360 370 390
40
32 30 26 18 14 10
25
五、数据处理
操作实例13
1、排序
6、数据透视表
结果：
33
7、规划求解
五、数据处理 7、单变量求解
1、排序
我们在进行数据处理时，往往知道我们所需 要的一个计算固定模式（如：本息和的计算）， 同时又希望该计算的结果为一个预想值，这就要 确定一下最初的约束条件（如：每年存多少钱？ 某个年后达到一个预期的数）。那么这种运算方 式可以通过“单变量求解”的方法来完成。
析结果。
17
五、数据处理
5、模拟运算表
5、模拟运算表
1、排序
【例13】以算式z=3x+4y+1为例：要求当x等于
从1到4间的所有整数，而y为1到8间所有整数时 所有z的值，用模拟运算表做。
步骤： （1）选中C2：G10区域 （2）“数据”--“模拟运算表” （3）引用行单元格A2 引用列单元格B2
18
输入公式
五、数据处理
5、模拟运算表
5、模拟运算表
1、排序
【例14】单变量模拟运算表——计算年利息
现有本金 ￥200，000元，假若年利率是 4.50% ，则年利息为 9000元。试分析如果年利率 分别为：4.20%， 4.40% ，4.60%， 4.80%， 5.00% ，5.20% 时，年利息分别为多少？
34
7、规划求解
五、数据处理 7、单变量求解
注意：该工作表中必 须有一个公式、一个 空的可变单元格。
1、排序
【例17】 单变量求解举例
35
7、规划求解
五、数据处理 7、单变量求解
1、排序
【例18】 本息和的计算
现在我们相存入一笔钱，希望通过利滚利的方 式，十年后达到10万元。那么，当初应存入多少钱？ （假设年利率为5%）
自动筛选示范实例
【例11】----筛选举例
学生成绩表.xls
8
五、数据处理 3、筛 选
高级筛选可依据多个字段进行复杂的筛 选，筛选的条件（条件区域）放在数据区域 之外，条件区域与数据区域至少要留一空行 （列）。
9
3、筛选
高级筛选：
五、数据处理 3、筛 选 高级筛选的步骤如下：
3、筛选
1）建立条件区域，并设置筛选条件
五、数据处理 6、数据透视表
1、排序
在进行比较复杂的综合数据分析时，常常借助 数据透视表（一种交互的、交叉制表的 Excel 报表， 用于对数据进行汇总和分析） ，它集排序、筛选和 汇总功能于一身，用户可以旋转数据清单的行或列， 对数据源的不同汇总情况进行查看，在需要汇总大 量数据并对每种数据进行多层次比较时，数据透视 表会给用户带来极大方便。 27
方法： （1）单击关键字段——工具栏中的“排序”按钮 （2） “数据”——“排序”
5
五、数据处理 2、排序
2、排序
（3）自定义排序：
按特定顺序排序（非字母顺序、笔画顺序） 步骤：
① 将特定内容按要求顺序建立自定义序列 ② “数据”---“排序”--- “选项”按钮，在“自定 义排序次序”中选规定的序列即可。
20
五、数据处理
5、模拟运算表
具体步骤如下：
1、排序
5、模拟运算表
① 建立一工作簿，将住房贷款有关的基本数据输入 ② 计算总付款期数。总付款期数=借款年限×每年付款期数 ③ 计算每期偿还金额。输入公式：
=PMT(年利率/年还款期数,总还款期数,贷款金额) =PMT($D$5/$D$7,$D$8,$D$4)
五、数据处理
5、模拟运算表 1、排序
5、模拟运算表
步骤：① 建立工作表； ② 选中A6:H13单元个区域，“数据”---”模拟运算 表”---在“输入引用行单元格”中输入B4，在“输入引用 列单元格”中输入B3； ③ 隐藏无用数据，从有用数据中可很清楚地看出最高利润。
销售数量 销 售 价 格
26
6、数据透视表
6、数据透视表
五、数据处理
6、数据透视表
1、排序
【例16】对如下数据清单按产品名称和销售员ID创建 数据透视表
28
6、数据透视表
五、数据处理
6、数据透视表
1、排序
步骤如下：
1、 “数据”——“数据透视表和数据透视图” 2、选中数据源类型下的“Microsoft Excel数据清单或数据 库”，单击“所需创建的报表类型”下的“数据透视表” 3、“选定区域” 4、单击“新建工作表”选项，然后单击"版式"按钮,显示如 下:版式对话框主要由"页"、"行"、"列"和"数据"四个部分 组成，分别对应数据透视表中的"页字段"、"行字段"、"列 字段"和"数据字段"。
11
五、数据处理 3、筛 操作实例10 选 筛选应用---进货及销售情况统计.xls 要求：
3、筛选
（1）计算出销售、进货价格比和销售利润（保留2位小数） （2）查询进货人为“甲”的所有记录 （3）查询进货人为“甲”且货物产地均为“台湾”的记 录 （4）查询销售利润前8名的记录 （5）查询销售数量在100和200之间的记录 （6）查询进货价格低于500，销售、进货价格比大于1.2 的记录 （7）查询产地为“台湾”或“广东”的所有记录
29
6、数据透视表
五、数据处理 6、数据透视表
1、排序
步骤（接上页）： 5、拖动“地区”到 “页”上,拖动“日期” 到“列”上,分别拖动 “产品名称”和“销售 员ID”到“行”上,然 后再分别拖动“销售量” 和“金额”到“数据” 上 ”，然后，单击 “完成”按钮 即可。 30 结果如图：
6、数据透视表
12
五、数据处理 进货及销售情况统计.xls
3、筛选
操作结果
(2)
(1)
13
五、数据处理 进货及销售情况统计.xls
3、筛选
操作结果
(4)
(3)
(5) (7)
(6)
14
4、分类汇总
五、数据处理 4、分类汇总
4、分类汇 总
分类汇总：以某个字段值为依据，把该字 段值相同的记录的其他字段的值汇总起来。
36
五、数据处理
7、规划求解
1、排序
7、单变量求解
操作实例14
磁盘销售预测：
每张磁盘3.8元，为获得50000元的销售额， 问需要卖出多少张磁盘，才能达到目标？
37
7、规划求解
五、数据处理 8、规划求解
1、排序
利用Excel提供的规划求解可以对未知问题进行 预测或寻求现有问题最佳解决方案（求得某个单元格 （目标单元格）中公式的最优值）。 规划求解将对直接或间接与目标单元格中公式相 关联的一组单元格（通常称为可变单元格）中的数值 进行调整，最终在目标单元格公式中求得期望的结果， 并显示该结果对应的可变单元格内的数值。
五、数据处理
5、模拟运算表
1、排序
5、模拟运算表
模拟运算表具有数据分析能力，它可以显示公式中某
些值的变化对计算结果的影响，为同时求解某一运算中所
有可能的变化值组合提供了捷径。并且，模拟运算表还可 以将所有不同的计算结果同时显示在工作表中，便于查看 和比较。 通过在模拟运算表中设置特定的参数值，就可得到分
6、数据透视表
五、数据处理
操作实例13
1、排序
6、数据透视表
操作步骤：
（1）“数据”---“数据透视表和数据透视图向导” （2）指定行字段（产品名称）、列字段（地区）和数 据（利润或销量） （3）双击某地区销售某产品对应的单元格，即可得到 相应的工作表 （4）注意数据透视表工具栏的应用
32
6、数据透视表
信息管理系
柳淑花
Email: liushuhua08@
1
目

录




一、Excel 基本操作 ------------（1） 二、工作表的编辑------------------（2） 三、工作表的格式设置--------（2） 四、公式和函数* ------------------（3） 五、数据处理* -----------------------（5） 六、图表的建立与编辑* ----（1） 七、打印工作表----------------------（1） 八、实例操作---------------------------（1）