计算机excel第六章数据管理

1、第六章 数据管理李花： 主要内容：数据列表排序筛选数据的汇总合并计算分列 概述Microsoft Excel 提供了一系列数据管理功能，可以很方便地管理和分析数据列表中的数据。如可以对数据列表进行排序、筛选、分类汇总等操作。1. 数据列表数据列表（数据清单）的基本概念由包含相关数据的一系列数据行组成，是Excel中的数据库表格；第一行为字段名（或称列标题）；行为记录，列为字段；数据列表数据列表一般满足以下条件：每张Sheet中仅使用一个数据列表；使数据列表独立：在数据列表与其它数据之间至少隔开一个空行或空列；避免在表内有空行或空列2. 数据的排序 普通排序原则：“升序”-从小到大数字：从负到正

2、文本：0-9,空格，! 等符号，字母逻辑值：FALSE TRUE错误值：优先级相同空白始终排在最后排序注意有无“标题行”排序结果不同。排序过程中选中了一个区域，则区域之外的数据不排序；隐藏的数据也不参加排序。排序类型按单列排序 鼠标单击选择要排序的列或列中的某一单元格，单击“数据”|“排序和筛选”，选择“升序”或“降序”;多列排序“数据”|“排序和筛选” ，选择“排序”；关键字段可以是多个，主要关键字段不能省略，Excel 2007中排序字段最多有64个;排序类型特殊排序（1）行排序: “排序选项”对话框中选“按行排序”；（2）自定义序列排序：“排序”对话框中单击“次序”下拉列表，选“自定义序

3、列”； (3) 按单元格颜色、字体颜色、单元格图标排序 “排序”对话框中单击“排序依据”下拉列表，选择“单元格颜色”等实例：排序对竞赛结果清单，完成如下排序。先按系别升序排序，当系别相等时再按学号升序排序，注意保留原始顺序，以便恢复原顺序；改变列的顺序，要求顺序为学号、姓名、系别、笔试、上机测试、总分、名次等；按系别排序，要求是电子系、信息系、数学系、生物系、材料系；按照总分求出名次；操作步骤：排序时，主关键字为“系别”，次关键字为“学号”；选择清单最后一条记录的下一行，输入排序之后的列序号3、1、2、4、5、6、7、8，在“排序”对话框的“选项”中选择排序方向为“按行排序”，选择列序号所在行

4、“行11”为主要关键字；操作步骤： 单击Excel按钮，创建自定义序列，序列项为电子系、信息系、数学系、生物系、材料系，主要关键字选择为“系别”，在“排序”对话框的“次序”中 选“自定义序列”，并选择刚创建的序列； 选择“名次”字段的第一个单元格G3，输入公式=RANK(F3,$F$3:$F$10)，向下拖动填充柄，则可以得出总成绩名次；3. 数据筛选数据筛选的概念数据筛选指按指定条件对数据列表中的记录进行选取，只显示满足条件的记录，而隐藏其它记录。筛选方式有两种：自动筛选、高级筛选自动筛选操作步骤：鼠标单击数据列表中的任何一个单元格，单击“数据”|“排序和筛选”，选择 “筛选”，单击与筛选条

5、件有关的字段右侧的下拉箭头。其中可选：按颜色筛选；根据所选字段的数据类型，可以按“文本”、“数字”、“日期”筛选；数值：只显示与指定值匹配的记录取消对某字段的筛选在自动筛选箭头下选 “全选”；恢复显示所有记录“数据”|“排序和筛选”，选择“清除”注：筛选条件是累加的，每一个追加的筛选条件都基于当前的筛选结果，同时对每一列只应用一个筛选条件； 比较复杂的筛选条件，有时利用自动筛选难以完成；实例：自动筛选对学生成绩表完成如下操作:查找未输入数学成绩的学生记录 ； 筛选外语系男生的记录； 筛选数学成绩最好的五个人的记录； 筛选数学成绩低于数学平均分的记录； 系别为外语系或性别为男生的记录； 任何一门

6、课不及格的学生的记录； 高级筛选高级筛选：使用复杂条件来寻找满足条件的记录操作步骤：创建条件区域，条件区域与数据列表之间至少应隔一个空行或空列；“数据”|“排序和筛选”|“高级”；高级筛选的结果可显示在原来的数据区域，也可以复制到其他的区域，复制到其他区域时指定目标区域的左上角即可；条件区域“高级筛选”的条件区域的建立方法与库函数的条件区域建立方法相同。条件区域至少两行，第一行必须是字段名，下面的各行是相应的条件。条件区域的列与列之间：and 与条件区域的行与行之间：or 或实例：高级筛选对学生成绩表用高级筛选完成如下操作：筛选出系别为“外语”或性别为“男”的记录; 筛选出任何一门课不及格的记

7、录；4. 数据的汇总分类汇总建立分级显示(1) 分类汇总目的：在数据列表中快速汇总同类数据，可分类进行求和、计数、求均值、求最值等计算。前提：先要将数据按分类字段进行排序，再进行分类汇总。操作步骤：将数据按分类字段进行排序；单击数据列表中的任一单元格“数据”选项卡|“分级显示”组|“分类汇总” 在“分类汇总”对话框中选择： 分类字段：选排序所依据的字段； 汇总方式：选用于分类汇总的函数方式； 选定汇总项：选要进行汇总计算的字段； 取消分类汇总结果：在“分类汇总”对话框中选择“全部删除”命令按扭。分类汇总的层次显示汇总表的左上角有按钮，分别标明“1”，“2”，“3”等，说明层次。汇总表中的“+”

8、按钮，每个按钮对应一个汇总行，单击“+”按钮，显示出汇总行的明细数据。多极分类汇总在原有分类汇总的基础上，可以进一步做汇总。操作注意：在多级分类汇总时在“分类汇总”对话框中取消选“替换当前分类汇总”。实例：分类汇总对学生成绩表，完成如下操作：求每个系的各科平均成绩；求每个系的学生人数；对上一步骤得出的汇总结果，复制粘贴到其他位置；求每个系、不同性别的人数；第1、2题操作步骤提示：先按系别进性排序，在“分类汇总”对话框中分类字段选为“系别”，汇总方式选“平均值”，汇总项选“数学”、“英语”、“计算机”；嵌套分类汇总，在原有分类汇总的基础上，再次做分类汇总，分类字段为“系别”、汇总方式选“计数”、

9、汇总项选任一字段，一般选分类字段右侧的字段，取消选“替换当前分类汇总”；第4题操作步骤提示：以系别为主关键字，性别为次关键字进行排序；首先对“系别”进行一级分类汇总,汇总方式为“求计数”，选择任何一个没有空白值的字段（最好选排序字段右侧的字段）；接着再按“性别”进行分类汇总，汇总方式和汇总字段同上，在对话框中一定要选清除“替换现有的分类汇总”的默认选项；思考题：如何使用分类汇总的结果？分类汇总的结果不能直接用复制粘贴的方式使用，需要用Ctrl键选择所需要的不连续的内容再复制粘贴或用“定位条件”中选择“可见单元格”，进行复制粘贴。(2) 组及分级显示自动建立分级和人工建立分级操作“数据”| “分

10、级显示”|“组合”,“自动建立分级显示”分类汇总和人工分级的区别:分类汇总的分级显示必须按照某一列的相同的数据进行汇总，而手动建立分级没有这个限制。5. 合并计算当源数据具有相同排列顺序和位置时，可以按位置进行合并计算；当源数据没有按相同的顺序排列，但使用相同标志时，可以按分类合并计算。操作步骤：“数据”|“数据工具”|“合并计算”合并计算注意：选择汇总函数，指定源区域，明确目标区域是否与源区域建立链接关系，以及数据源区域中标题的位置。当合并计算数据时，需要在三维公式中或“合并计算”对话框的“引用位置”下拉列表框中指定数据的源区域。源区域和目标区域可在同一个工作簿的同一个工作表或不同的工作表，

11、也可在不同的工作簿中。合并计算注意：创建目标合并区域时，最好单击目标区域的左上角单元格，避免由于目标区域的形状产生问题。合并计算中“链接”与“不链接”的区别在于数据源的改变能否自动反映到结果区域以及能否显示明细数据。实例1：按位置进行合并要求：每个源区域中的数据以相同的排列顺序和位置时，可以按位置进行合并计算，合并计算时不需要选择标志位置。例1：在合并计算.xlsx实例1！中计算公司年度销售额，一季度、二季度、三季度、四季度数据分别在Sheet1:Sheet4中。注：若选择“创建连至源数据的链接”，合并计算将随着源数据的变化而自动更新且分级显示。 实例2： 按类进行合并计算前提：在源区域数据可

12、以没有按相同顺序排列但包含了行或列标志，则可以按照行或列标志进行合并计算。在“合并计算”对话框“标志位置”选项框中，选定标志的位置在“首行”或“最左列”或两者都选。选择合并区域时，不需要输入行或列标志。要求：对各个班级的收支进行合并计算，求全年级的各项平均。实例3：按类进行合并计算对一公司和二公司的收支进行合并计算，求总公司的收入、支出、金额。6. 分列分列用于将一列数据按照分隔符或指定的宽度分为多列。操作：“数据”|“数据工具”|“分列”实例：分列要求：对姓名字段进行分列，分为姓和名；对出生年月日进行分列，分为年份、月份、日期；对一字符串进行分列，宽度分别为5个字符、6个字符、4个字符、7个

13、字符；操作步骤：第1题：选择数据区域，“数据”|“数据工具”|“分列”，分隔符号选择为“空格”，目标区域选择为B2；第2题：分隔符号选择为“其他”，并输入“-”，目标区域选择为B9；第3题：分隔符号选择为“固定宽度”，在“数据预览”中用鼠标单击第5个字符、第11字符、第18个字符位置等建立分列线；第七章 数据透视表数据透视表的概念数据透视表的修饰数据透视表的操作数据透视图1. 数据透视表的概念作用：用于分析数据列表或数据库中的数据, 快速汇总大量数据，可交互式地进行数据的分析。利用数据透视表可：旋转数据透视表的行列以查看对源数据的不同汇总；筛选数据；显示部分数据的明细数据；数据透视表的建立 操

14、作步骤：单击数据列表中的任一单元格,“插入”| “数据透视表”,在对话框中选择“数据源”和数据透视表的位置；在“数据透视表字段列表”中选择报表筛选字段、列标签字段、行标签字段、数值字段；（选择字段名，直接拖动到各自区域内）数据透视表的结构报表筛选字段（页字段）：用来筛选数据；行标签字段（行字段）：在报表中被指定为行方向的字段，也就是分类字段；列标签字段（列字段）：在报表中被指定为列方向的字段，也就是系列字段；数值字段（数据字段）：汇总数据字段；默认情况下：COUNT函数汇总文本数据； SUM函数来汇总数字数据；实例：数据透视表1根据“家庭开支流水账”，进行汇总。要求：用分类汇总和数据透视表两种

15、方法，求每个支出人的支出总额；用分类汇总和数据透视表两种方法，求出每个支出人在每个项目中的支出总额；用数据透视表求出每个支出人在每一年的总支出；操作步骤：第1题：分类汇总：根据支出人字段排序，再以支出人作为分类字段、汇总方式为求和、汇总项为金额；数据透视表：插入点放在数据列表内，单击“插入”| “数据透视表”，以支出人为行标签，金额为数据字段，汇总方式为求和；操作步骤：第2题：分类汇总：以支出人作为主关键字段、以项目作为次关键字段进行排序，先以支出人作为分类字段分类汇总的基础上再以项目作为分类字段进行分类汇总；数据透视表：以支出人作为行标签、项目作为列标签，金额作为数值字段；操作步骤：第3题：

16、以支出人作为行标签、日期作为列标签，创建数据透视表；单击日期标签，选择“数据透视表工具”| “分组”|“将所选内容分组”，在对话框中选择“步长”为“年”；2. 数据透视表的修饰数据透视表的布局设置数据透视表样式设置字段设置(1) 数据透视表的布局设置操作方法：“数据透视表工具”“设计”选项卡“布局”组，选择相应的命令； 数据透视表的布局设置“总计”：可设置是否显示列总计、行总计等；“报表布局”：可设置以压缩形式、大纲形式或以表格形式显示；“分类汇总”：是否显示分类汇总(2) 数据透视表样式样式的应用“数据透视表工具”|“设计”选项卡|“数据透视表样式”；数据透视表样式选项可选“行标题”、“列标

17、题”、“镶边行”、“镶边列”(3) 字段设置重命名字段改变字段列的排序顺序值字段设置显示明细数据计算字段1. 重命名字段当向数据透视表添加数值字段后，它们都将被重命名，为了不影响表格的美观，需要用户重命名字段。数据透视表数据区域的名称与数据源表头标题行的名称不能相同。重命名字段操作方法：在编辑栏中直接修改字段名称；要保持原有的字段名不变，可以使用替换法，如“求和项：”替换为“空格”；2. 改变字段列的排列顺序操作方法：右键单击字段名，在快捷菜单中选择“排序”或选择数据项，单击鼠标右键，在快捷菜单中选择“移动”； 3. 值字段设置操作方法：选择数值字段名，单击鼠标右键，在快捷菜单中选择“值字段设

18、置”，可以设置汇总方式和值显示方式； 4. 显示明细数据操作步骤：对组合的数据，选择字段名，单击“活动字段”|“展开整个字段”； 对数值单元格，双击该单元格，会新建显示明细数据的工作表；实例：数据透视表2根据“超市业绩表”数据，创建不同地区、不同店面的总销售额数据透视表，并进行修饰。要求：以地区、店面为行标签字段，销售额为汇总字段；数据透视表修饰，达到图片效果；操作步骤提示：“分类汇总”中选“在组的底部显示所有的分类汇总”；“报表布局”中选“以表格形式显示”；“空行”中选“在每个项目后插入空行”；“销售额”字段重命名为“总销售额”，并设置数字格式为“货币格式”；“地区”字段按升序排序；5. 计

19、算字段计算字段是通过对数据透视表中现有的字段进行计算后得到的新字段。操作步骤：单击数据透视表的任意一个单元格，“数据透视表工具”|“选项”|“工具”|“公式”，选择“计算字段”；在对话框内输入字段名称以及公式；实例：数据透视表3以产品明细表为数据源，创建数据透视表，要求在数据透视表中添加计算字段“销售单价”。3. 数据透视表的操作数据透视表的复制数据透视表的移动更新数据复制数据透视表操作方法：“数据透视表工具”|“选项”| “操作”|“选择”，下拉列表中选“整个数据透视表”，进行复制粘贴；作用：数据透视表创建完成后，若需要对同一个数据源再创建数据透视表，只需对原有的数据透视表进行复制即可，这样

20、可以提高工作效率。数据透视表的移动操作方法： “数据透视表工具”|“选项”|“操作”|“移动数据透视表”，选择移动的位置，选择放在独立工作表内还是已有的工作表内；更新数据数据源改变，数据透视表不能自动更新。操作方法：“数据透视表工具”|“选项”| “数据”|“刷新”4. 数据透视图数据透视图是数据透视表的可视化表示，是一种依赖于数据透视表的交互式图表。操作方法： “插入”|“表”|“数据透视表”，选择“数据透视图”，创建数据透视表的同时创建数据透视图或鼠标单击数据透视表的任一单元格，“选项”|“工具”|“数据透视图”；实例：数据透视表4根据销售单明细表，完成数据透视表。求出每个销售员每一天的销

21、售额总额；对数据透视表进行组合，求出每个销售员每一个月的销售总额和业务量；修饰数据透视表，销售员按升序排序，数据项为货币格式，不显示列总计；注意：数据透视表的数据可来源于外部数据；根据同一张工作表，可创建多个数据透视表；数据透视表的数据字段可来自于数据列表的字段，也可以根据已有的字段插入“计算字段”；数据透视表可建立在与原有数据同一个工作表上，也可建立在新工作表上。思考题：数据透视表能否自动更新?数据透视图能否自动更新？数据透视表如何移动或复制？一张数据列表可否建多个数据透视表?数据透视表的字段名能否更改？如何依据外部数据源创建数据列表？数据透视表能否有一个以上的数值字段？第八章 数据图表主要

22、内容：创建数据图表设置图表格式图表的选择、移动、缩放图表的趋势线特殊图表数据图表创建数据图表有助于进行数据分析，看清数据间彼此的关联及差异，有助于预测和决策。1. 建立图表两种图表嵌入图表：图表作为一个图形对象存放在已有的工作表内。独立图表：图表独立存放在新的工作表内，默认名称为Chart1，Chart2等。嵌入图表和独立图表都链接到数据源。创建图表一般操作步骤：选择数据区域，“插入”|“图表”，选择图表类型；单击“设计”选项卡，选择“图表样式”和“图表布局”以及图表位置；单击“布局”选项卡，选择“图表标题”等标签是否显示以及显示的位置；单击“格式”选项卡，选择“形状样式”等快速创建图表的方法

23、：选择数据源，按F11键创建独立图表；选择数据源，按Alt+F1键创建嵌入图表；图表类型Excel提供了11类图表，每一类又分别有多种子类。柱形图适用于显示多组数据在一段时期内的变化；条形图适用于比较在特定时间内的个别数据或多组数据；折线图适用于显示某段时期内数据连续变动情况及趋势，主要用于强调时间性及数据变动率；圆环图与饼图适合于显示要突出比例关系的数据。实例1：数据图表根据销售表，创建内嵌图表，图表类型三维柱形图。要求：添加图表标题、坐标轴标题；应用图表样式、填充颜色；设置数值轴刻度；设置为独立图表；操作步骤：“布局”选项卡|“标签”|“图表标题”，单击“图表上方”；“布局”|“标签”|“

24、坐标轴标题”；数值轴格式：“布局”|“坐标轴”|“主要纵坐标轴”；图表样式、填充颜色：“设计”|“图表样式”，“格式”|“形状样式”；位置：“设计”|“位置”|“移动图表”；2. 设置图表的格式图表工具图表元素(1) 图表工具激活图表，“图表工具”中显示“设计”、“布局”、“格式”选项卡。“设计”选项卡数据组“切换行/列”：切换X轴和Y轴“选择数据”：重新选择数据源图表布局组 各种预制的图表布局，图表布局决定了是否显示以及如何显示图表的标题、标签、网络线、图例、数据表等。“图表样式”组各种预制的图表样式，包括数据系列的样式等。“位置”组 决定以嵌入图表方式或独立图表方式存放。“布局”选项卡“标

25、签”组和“坐标轴”组 设置是否显示图表的标题、坐标轴的标题、图例、数据标签、数据表、网络线、坐标轴等“背景”组 设置是否显示图表背景墙、基底等“格式”选项卡“当前所选内容”组 可以选择相应的图表元素；“形状样式”组和“艺术字样式”组“排列和大小”组(2) 图表元素图表是由一系列图表元素组成的，如图表区、绘图区、标题、系列、坐标轴、数值轴、背景墙、图例、网络线等。图表元素的选取在数据图表内用鼠标选取可见的图表元素或在“布局”或“格式”选项卡中“当前所选内容”组中“图表元素”下拉框中选择元素名称；设置图表元素的格式操作方法：选择图表元素，“布局”选项卡中单击“设置所选内容格式”命令，打开相应的对话

26、框设置格式或选择图表元素，在快捷菜单中选择相应的格式设置命令；实例2：数据图表根据一月份的销售数据，创建饼图。3 . 图表的选定、移动和缩放激活图表嵌入图表：单击图表;独立图表：单击图表工作表的名称;图表的移动和缩放嵌入式图表的移动、缩放、复制和删除：和图形对象的操作相同。独立图表的缩放、复制和删除独立图表的缩放：调整“显示比例”;独立图表的删除：删除工作表，若选择图表，按DEL键，将删除图表区域的部分内容;思考题:若隐藏或删除了数据源中的某些数据，数据图表会有什么变化？若删除了数据图表中的数据系列，数据源会有什么变化？思考题：如何增加和删除数据系列？增加数据系列： 选择要增加的数据区域，复制

27、，单击图表，粘贴到图表区域或单击“选择数据”命令；删除数据系列：在图表内选择要删除的数据系列，单击Delete按钮；4. 图表的趋势线操作步骤：创建二维柱形图，“布局”选项卡|“趋势线”，选择趋势线类型；在“设置趋势线格式”对话框中选择“显示公式”、“显示R平方值”，选择R平方值最高的类型；5. 特殊图表组合图表根据2008数码产品销售表，创建组合图表，其中一图表类型为柱形图，另一图表类型为折线图。2008数码产品销售量U盘MP31月3201502月2901353月3051404月330123操作提示：选择整个数据，创建二维柱形图;选择MP3数据系列，“设计”|“类型”|“更改图表类型”，选择

28、折线图;注意：不能组合二维和三维图表类型。特殊图表双轴图表企业的销售统计中产品的销售量和销售额两类数据是相互关联的。制作图表，对销售量和销售额同时进行分析。操作步骤:选择数据区域，创建二维柱形图;选择“销售量”数据系列，“布局”|“当前所选内容”|“设置所选内容格式”，在“设置数据系列格式”对话框中选择“次坐标轴”，更改图标类型为折线图;特殊图表：动态图表1要求：制作随销售数据变化而变化的图表，当源数据增加或减少时，自动更新图表。补充函数：OFFSET函数格式：OFFSET(reference,rows,cols,height,width)功能：以指定的引用为参照系，通过给定偏移量得到新的引用

29、。如：=OFFSET(C3,2,3,1,1)Reference 作为偏移量参照系的引用区域Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。Height 高度，即所要返回的引用区域的行数。Width 宽度，即所要返回的引用区域的列数。操作步骤:定义名称：时间和销售量名称：时间 在引用位置上输入公式：=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1)名称：销售量在引用位置上输入公式：=OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1)

30、其中COUNTA(value1,value2,) 计算非空单元格的数目操作步骤:选择数据列表，创建图表，单击“设计”|“数据”|“选择数据”，在“选择数据源”对话框中选取 “销售量”系列，在“值”一栏中输入“=Sheet2!销售量”；在“分类轴标签”设置中输入“=Sheet2!时间”;（请不要忽略“销售量”名称前的工作表名称）特殊图表：动态图表2数据表中的销售数据越多，图表上的数据点也越来越拥挤。要求：制作始终反应最新几个数据的动态图表；为了更好地控制数据点，在图表中增加窗体控件控制每次显示的数据点数目。操作步骤：定义“日期”和“销售量”名称定义“日期”名称：引用位置上输入公式：=OFFSET

31、(Sheet4!$A$1,COUNTA(Sheet4!$A:$A)-Sheet4!$D$1,0,Sheet4!$D$1,1)；定义“销售量”名称：引用位置上输入公式： =OFFSET(Sheet4!$B$1,COUNTA(Sheet4!$B:$B)-Sheet4!$D$1,0,Sheet4!$D$1,1);(注意：其中D1单元格中存放了要显示的记录个数）； 在“选择数据源”对话框中选择销售量系列，在“值”栏中输入“=Sheet4!销售量”，选择分类轴标签，输入:=Sheet4!日期，这样图表每次显示出最新的几个数据；在“图表窗口”中添加两个标签和微调控件，标签1标题文字为“每次显示最近数据个数

32、”，选择标签2，在编辑栏中输入“=Sheet4!$D$1”,在微调控件的“单元格链接”属性中设置为“Sheet4!$D$1”；补充函数：PMT函数格式：PMT(利率，期限，PV，FV，类型)功能：基于固定利率和等额分期付款方式，返回投资或贷款的每期付款额其中 利率和期限值的单位一致，或为年利率或为月利率；类型为0或省略，表示为期末；若为1，表示期初；PV为现值，FV为未来值实例：例1：贷款700000元，分20年还清，月利率为0.5%，用PMT函数计算每月月初偿还金额的公式是？到期共支付了多少利息？每月还款额=PMT(0.5%,20*12,700000,0,1)=-4990.07共支付利息：=

33、4990.07*12*20-700000=497616.6实例：例2：若某人为将来得到一笔退休金每月做定期储蓄，他希望在20年以后能拿到40万退休金，预期年利率为6%，则每月的储蓄额为多少? =PMT(6%/12,20*12,0,400000)补充函数：FV函数基于固定利率及等额分期付款方式返回某项投资的未来预期值（复利计算)，通常用来求定期存款以及零存整取到期之后本金和利息之和。FV（rate，nper，PMT，PV，type）FV函数实例： 若某人每年年初存入储蓄的金额是5万，设目前10年期定期存款的年利率为3.25%，以复利计算，求10年后连本带利息能拿到多少？（零存整取，PV一般取0）

34、=FV(3.25%,10,-50000)PV函数PV函数返回投资的现值，现值为一系列未来付款的当前值的累积和 。函数格式：PV(rate，nper，PMT，FV，type)每期给付或所得之金额必须是固定，一笔贷款的未来值为0PV函数实例1：每月底支付一项保险年金500元，设每年的投资收益率为8%，投资年限为20年，则这项年金的现值为多少？=PV(8%/12,20*12,500,0,0)=59777.15实例2：例如，假设要购买一项保险年金，该保险可以在今后二十年内每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。那么该项年金的现值？PV(0.08/12, 12*20,

35、600,0) 计算结果为：￥-71,732.58。第九章 数据分析数据表（模拟运算表）单变量求解规划求解与分析工具库方案分析1. 数据表数据表是一个单元格区域，显示公式中的某些参数值的变化将如何影响公式结果。数据表有两种，单变量数据表和双变量数据表。双变量数据表反映两个变量的改变对一个公式结果的影响。(1) 单变量数据表单变量数据表查看一个变量的不同值对一个或多个公式结果的影响。输入值需排在一行或一列。可变单元格：公式中输入值要替换的单元格。行引用和列引用数据表中，输入值要排在一行或一列。如果输入值排在同一列，则在“输入引用列的单元格”框中，键入可变单元格。 如果输入值排在同一行上，则在“输入

36、引用行的单元格”框中，键入可变单元格。“单变量数据表”中公式位置当输入值排成一列时，在第一个数值的上一行且处于数值列右侧的单元格中键入所需公式。（可以在公式右边的单元格中键入其它公式）当输入值排成一行时，在第一个数值左边一列且数值行下方的单元格内键入所需公式。（可以为一个以上的公式）数据表计算结果数据表的计算结果存放在数组中，不能单独修改其中的某一个值。要清除计算结果，选中数据表中所有结果，按Delete键即可。单变量数据表操作步骤： 根据输入值的方向，选择公式的所在位置，输入公式；“数据”|“数据工具”|“假设分析”|“数据表”，在“数据表”对话框中键入引用的单元格；实例1：单变量数据表例：

37、计算商场出售商品的获利情况。公式为毛利=进货成本*加价百分比*销售数量-销售费用操作步骤：参数写在同一列A10:A14上，因此选择B9单元格输入公式：=B1*B2*B3-B4；选择A9:B14区域，在“数据表”对话框中，选择 “输入引用列的单元格”，单击B2单元格，则产生结果，此结果区域为数组；实例2：单变量数据表“考勤应扣款计算表”中用数据表计算每个部门的基本工资、扣款合计的总额。操作步骤：设A19单元格为可变单元格，输入任何一个部门的值；所有参数值写在C20:C23区域，在D19、E19单元格输入公式：SUMIF(C3:C16,A19,E3:E16)和SUMIF(C3:C16,A19,L3

38、:L16)操作步骤：选择C19:E23区域，在“数据表”对话框的“输入引用列的单元格”中输入“A19”，单击“确定”按钮；(2) 双变量数据表创建双变量数据表的过程与创建单变量数据表类似。双变量中的两组输入值使用同一个公式，一组输入值在公式下方的同一列上，另外一组输入值在公式右侧的同一行上。操作步骤：在某个单元格内输入所需的引用两个输入单元格的公式。在公式下面同一列中键入一组输入值，在公式右边同一行中键入第二组输入数值；选定公式及输入行和列的单元格区域；“数据”|“数据工具”|“假设分析”|“数据表”，输入行引用的单元格以及输入列引用的单元格；实例3：双变量数据表有人要申请等额本息贷款购买房子

39、，其中利率固定，考虑几种可能的贷款年限和贷款金额下求每月还贷额，选择最佳方案。操作步骤：B5单元格先输入公式，然后在B6：B10区域和C5:G5区域输入输入值；选择B5:G10区域，在“数据表”对话框中“引用行的单元格”里选择F2，“引用列的单元格”里选择C3；实例4：双变量数据表在“考勤应扣款计算表”中用数据表计算不同部门、不同职位的人数。操作步骤：在A19:B20区域，创建条件区域；在D20:D23区域输入部门的所有参数值，在E19:H19区域输入职位的所有参数值；在D19单元格输入公式：=DCOUNT(A2:L16, “基本工资,A19:B20);操作步骤：选择D19:H23区域，在“数

40、据表”对话框的“输入引用列的单元格”中输入 “B20”，“输入引用行的单元格”中输入“A20”，单击“确定”按钮；2. 单变量求解单变量求解是在已知结果的情况下推测出形成这个结果的参数。在Excel中根据所提供的目标值，不断改变公式中涉及的引用单元格的输入值，直到达到所需要求的公式的目标值。公式所在的单元格为目标单元格，在求解过程中可修改其值的单元格为可变单元格，“单变量求解”中可变单元格为一个。操作步骤：选择目标单元格，“数据”|“数据工具”|“假设分析”|“单变量求解”，在“单变量求解”对话框中输入目标单元格、目标值以及可变单元格；实例5：单变量求解某人准备向银行贷款，贷款期限为30年，贷

41、款利率为6%，每月能承受的还款额为5000元，那么能贷多少。操作步骤选择B5单元格，输入公式：=PMT(B4/12,B3,B2)；“数据”| “数据工具”|“假设分析”|“单变量求解”，目标单元格中选择B5，目标值-5000，可变单元格选择B2；实例6：单变量求解例：猴子吃桃，有x个桃，每天吃剩下桃的一半又多吃一个，第十天剩了一个桃，求原来一共有多少个桃？操作步骤：设原来的桃个数放在B15单元格里，在B16单元格里输入公式：=B15-B15/2-1,拖动填充柄到B25；“单变量求解”对话框中，选择目标单元格为B25，目标值为1，可变单元格为B15；3. 规划求解 “规划求解”与“分析工具库”是

42、Excel提供的两组数据分析加载宏。加载宏的安装：单击Office按钮，选择“Excel选项”；选择“加载宏”类别，选择“Excel加载宏”，单击“转到”；在“加载宏”对话框中选中“分析工具库”和“规划求解加载项”；规划求解规划求解可求多个变量在一定约束条件下的最佳解。“数据”|“分析”|“规划求解”规划求解的选项：目标单元格，可选最大值、最小值或某一数值；可变单元格，用逗号分隔不相邻单元格的引用；约束条件；规划求解的选项采用线性模型：如果求解模型是线性模型，需选“采用线性模型”复选框，规划求解一定能找到求解模型的最优解。如果一个求解模型是非线性的，并不一定都能得到满意的结果，它受初值以及规划

43、求解选项（如迭代次数、迭代时间等）的影响，在求解过程中按Esc键可中止求解过程。假定非负实例7：规划求解若有如下约束条件，y-x=1; 3x+y=0；y=0，求满足上述四个条件的x、y使得x+y取最大值。操作步骤：在A4单元格里输入公式：=A3-A2，在A5单元格里输入公式：=3*A2+A3；在B7单元格里输入公式：=A2+A3；在 “规划求解参数”对话框中如图设置。实例8：规划求解设某食品公司生产A、B两种产品，需要甲乙丙丁4种原料。每种产品所需的原料和产品的利润值以及每种原料的储备量具体数据如图所示：步骤1：建立数学模型设A、B两种产品分别生产x1、x2吨，则按要求建立条件： 2x1+2x

44、2=12 x1+2x2=8 4x1+0 x2=16 0 x1+4x2=0,x2=0 求2x1+3x2的最大值补充：SUMPRODUCT函数格式：SUMPRODUCT(array1, array2, array3, .)功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。要求：数组参数必须具有相同的维数。步骤2：规划求解设置在B5单元格里输入公式：=SUMPRODUCT(B2:B3,$G$2:$G$3)，并把公式填充到C5:E5单元格；在B6单元格里输入公式：=SUMPRODUCT(F2:F3,G2:G3)在“规划求解参数”对话框设置参数，参考图片，“选项”中选择“采用线性模型”和

45、“假定非负”；4. 方案分析方案就是保存在工作表中并可进行自动替换的一组值，可以比较一组变量的不同的取值对结果的影响，也可以建立报表对这些方案进行对比或汇总。“数据”|“数据工具”|“假设分析”|“方案管理器”创建、显示、编辑、删除方案合并方案：可以把其它工作表的所有方案都复制到当前工作表中。方案分析生成方案总结生成方案摘要方案数据透视表保护方案在方案管理器中，选定方案名称，“编辑”里设为“防止更改”和“隐藏”。当工作表受保护时能添加方案，但不能编辑或删除已有方案。思考题:方案管理器中显示的是当前工作表的方案还是当前工作簿的方案？若对某一个方案设置保护，不允许别人修改，如何操作？第十章 宏及其

46、应用宏的概念录制宏编辑宏运行宏加载宏指定宏VBA1. 宏的概念宏是存储在Visual Basic模块中的一系列命令和函数，是一段可执行的VBA(Visual Basic for Application)程序。创建宏的两种方法：录制宏：录制用户所做的一系列操作，Excel会存储操作时的每个步骤信息；用VB程序设计语言编写宏代码；宏的安全性Excel 2007，默认情况下禁用了宏。要运行宏，应更改安全性设置。 更改安全性设置单击Office按钮，选择“Excel选项”|“信任中心”类别|“信任中心设置”|“宏设置”，启用宏或可以通过“开发工具”|“代码”|“宏安全性”，更改安全性设置；包含宏的文件

47、保存保存宏时，选择文件类型为启用宏的工作簿（*.xlsm），而非默认的工作簿类型（*.xlsx）。录制宏的优点：宏录制简单，不涉及较深的VBA代码；帮助用户快速建立某自定义功能的VBA代码；建立带有快捷键的自定义过程；录制宏的缺点：通过录制宏获取的宏代码具有不可弥补的缺点。代码生硬，欠缺灵活性；代码冗余严重；宏代码无法处理需要循环的事物；录制宏获取的代码效率低下，直接使用这些宏过程无形中会增加VBA应用的开销；2. 录制宏录制宏操作步骤：“开发工具”|“代码”|“录制宏”；（或“视图”|“宏”，选择“录制宏”或单击状态栏的“录制宏”按钮） 在对话框中输入宏名，并为其设置选项； 开始进行操作，录

48、制结束时按“开发工具”|“代码”|“停止录制”（或单击状态栏上的“停止录制”按钮）宏选项宏名和快捷键宏名称的首字必须是字母或汉字，其它字符可以是字母、数字或下滑线字符。宏名称不允许有空格。宏快捷键使任何Excel默认键无效。因此设置快捷键时，输入Shift+字母，若输入Shift+a，则快捷键为Ctrl+Shift+A。（注意：需按Ctrl+Shift+a/A）宏选项保存位置当宏保存的位置为个人宏工作簿时，宏将随Excel的使用而激活；当保存在当前工作簿时，只有打开当前工作簿时宏才能激活。个人宏工作簿，是为宏而设计的一种特殊的具有自动隐藏特性的工作簿。第一次将宏创建到个人宏工作簿时，会创建名为

49、“PERSONAL.XLSB的新文件。 实例：宏建立一个宏，名称为“单元格格式”要求：字体、字号、数字格式等；利用绝对引用和相对引用两种方式创建宏，并比较一下效果以及代码注：在记录或编写宏之前，应先制定计划，确定宏要执行的步骤和命令。因为如果在记录宏时出现失误，对失误的纠正也记录在宏中。 宏的绝对引用和相对引用宏的绝对引用：指录制宏时每一个步骤都用绝对地址记录下来的叫绝对引用，默认情况下为绝对引用。宏的相对引用：指录制宏时每一步都用相对地址来记录下来的叫相对引用。相对引用时，将记录录制宏前最后选定的活动单元格和录制宏时的活动单元格的相对位置。因此宏运行时，执行的单元格将和运行宏时的活动单元格以

50、及录制宏时的相对位置有关。实例：宏的相对引用要求：定义宏，完成删除当前行;在当前单元格前插入空行；总结：绝对引用：录制宏的第一个操作是选定单元格时，运行宏时也从绝对地址开始；若第一个操作不是选定单元格时，运行宏时的第一步是从当前活动单元格开始。相对引用：录制宏的第一个操作是选定单元格时，运行宏的结果与录制宏前的活动单元格与第一个操作选定的单元格的相对位置以及运行宏时的活动单元格有关；若一个操作不是选定单元格时，运行宏的结果与运行宏时的活动单元格有关，即所谓的指哪儿打哪儿。思考题：一个存放于个人宏工作簿的宏，如何删除？在“宏”对话框中选择personal.xlsb中的宏名，删除此宏。3. 编辑宏

51、操作步骤： 在“宏”对话框中选择宏名，单击“编辑”按钮，进入宏代码窗口； 在宏代码窗口中，可以对代码直接进行修改；注意：录制宏产生的代码往往比用户直接编辑的代码复杂。4. 运行宏运行宏，可执行宏当中的命令和函数，也就是重复用户录制新宏时实际所做的操作。 运行宏的方法“宏”对话框中选择宏名，单击“执行”或按快捷键或通过图形、控件按钮或通过快速访问工具栏上的宏按钮；提前中止宏的运行，可按Esc键5. 指定宏为对象、图形或控件指定宏操作步骤：右击要向其指定宏的对象、图形或控件，在快捷菜单中单击“指定宏”；在对话框中选择宏名；指定宏在快速访问工具栏上添加宏按钮并为其指定宏 操作步骤：单击Office按

52、钮，在“Excel选项”对话框中选择“自定义”，“从下列位置选择命令”下拉列表框中选择“宏”，选择“宏名”，单击“添加”；在“自定义快速访问工具栏”中选择“宏名”，单击“修改”按钮，选择按钮；实例：指定宏定义宏，在 “销项查询表”中完成高级筛选，并把此宏指定在命令按钮上。6. 加载宏 加载宏是一类程序，通过加载宏，Excel可以增加新的可选命令或新的功能。这些命令或功能是Excel没有的或者暂时不能使用的。 加载宏的类型Excel内置的加载宏，如规划求解等；来自于其他资源的用于Excel的加载宏；组件对象模型COM加载宏等；思考题：如果要在本地计算机的其他工作簿中使用用户录制的宏，应该怎么办？

53、如果要在其他的计算机中也要使用用户录制的宏应该怎么办？答案1： 宏保存在“个人宏工作簿”答案2：宏保存为“加载宏”如何添加其他来源的加载宏 在“Excel选项”对话框中，选择“加载项”，单击“转到”，在“加载宏”对话框中单击“浏览”，选择加载宏的名称，如*.xlam文件。7. VBAVBA全称为Visual Basic for Application，可以认为是Visual Basic的一个用于特定应用程序Office的子集。VBA开发环境就是VBE。VBE是一个分离出来的、与Excel无缝结合的应用程序，它不能单独运行。(1) VBA开发环境Excel切换到VBE的方法“开发工具”|“代码”|“Visual Basic”或“开发工具”|“控件”|“查看代码”VBE窗口VBE窗口是高度可定制的窗口，一般包括工程资源管理器、代码窗口、监视窗口、属性窗口、立即窗口、本地窗口及对象浏览器。VBA简介 对象：VBA是一种面向对象的语言，如工作表、单元格、图表