第5章excel(浙大版)

1、o5.1 基础知识与基本操作o5.2 公式、常用函数与地址引用o5.3 工作簿与工作表o5.4 图表与打印输出o5.5 数据处理与管理o5.6 函数与应用 o5.7 数据分析 o一、 简介n功能简介n启动退出关闭n窗口工作簿工作表窗口工作簿工作表o电子表格、工作簿(book1.xls)、工作表标签、o工作表与单元格（ AIV，256列；165536行）n文件的建立与打开n文件的保存与加密保存菜单栏控制菜单图标工作表格区状态栏工作表标签水平滚动条垂直滚动条常用工具栏格式工具栏程序控制按钮工作簿控制按钮标题栏求助框编辑栏PDF工具栏o二、二、 数据类型与数据输入数据类型与数据输入o 数值型数据与输

2、入n日期格式与输入日期格式与输入o分数形式分数形式3/8；3-8；当前日期；当前日期CTRL+；n 文本型、逻辑型数据与输入文本型、逻辑型数据与输入n 输入大批数据的方法n在单元格内输入显示多行文本o(ALT+回车)o三、选定三、选定/修改修改/删除删除n选定行列单元格n修改、删除数据n删除与清除的区别？o四、输入序列与自定义序列n自动填充序列自动填充序列o用填充柄填充数据序列o等差数列：开始的两个单元格数据，然后拖曳o用菜单命令填充等差或等比序列n编辑菜单填充序列o自动填充文字序列n自定义序列o工具菜单选项自定义序列o五、五、移动插入/复制插入/交换数据o移动插入nShift左键n右键拖曳o

3、复制插入nCtrl+Shift+左键n右键拖曳o交换数据n相邻的两行或两列做移动插入，即可实现数据交换o六、插入行/列/单元格o插入行/列n选定行、列或单元格（选定的数量与将要插入的数量是等同的）n插入菜单行（列），或者右键插入o插入单元格n选定单元格区域（选定的数量与将要插入的数量是等同的）n插入菜单单元格，或者右键插入o七、命名行/列/单元格区域o命名的好处n通过名字引用单元格区域，要比用地址引用更加直观；n通过名字可以快速选定单元格区域o命名的方法n单元格区域命名：插入菜单名称定义n为选定区域的行/列命名：插入菜单名称指定首行（最左列）o八、批注o批注是为单元格加注释o一个单元格加批注后

4、，右上角会出现一个三角标识o当鼠标指向这个标识的时候，显示批注信息o插入菜单批注，或者右键插入批注o工具选项视图批注一、公式：以等号开始，可以包含函数、地址引用、运算符、常量。运算符含义优先度-负号或单目减高%百分号 乘方*、/乘、除+、-加、减&文本连接运算举例 = = 关系运算举例n简单公式举例n地址引用 三种形式：相对引用、绝对引用、混合引用o在公式输入时，按F4键可在几种引用之间转换。o在将公式复制到其他单元时起作用（移动时不变化）。举例：复制移动插入删除单元格对复制移动插入删除单元格对公式的影响公式的影响o数组公式：n数组的概念：对一组数据进行运算，结果也是一组数据o注意：n

5、使用 “Ctrl+Shift+Enter”组合键代替“Enter” 才能正确显示结果n运算结果不能单独改变 举例二、函数二、函数 函数可以完成特殊功能或操作，使用函数可以简化和缩短公式，甚至可以完成公式不能完成的功能或操作。（一）函数格式、输入与嵌套o函数格式：函数名（自变量1，自变量2， ）o*一个函数的自变量可以有030个o函数输入（调用）：n插入/函数n 按钮n或shift+f3o函数嵌套：将某一函数作为另一函数的参数调用。最多可以嵌套七层。o例：=if(average(a2:a6)50,sum(b2:b6),0)n函数嵌套：将某一函数，作为另一函数的参数调用。最多可以嵌套七层。o例：=

6、if(average(a2:a6)50,sum(b2:b6),0)（二）常用函数1求和函数SUM(参数1,参数2,) 。2算术平均值函数AVERAGE(参数1,参数2,)。3最大值函数MAX(参数1,参数2,)。 最小值函数MIN(参数1,参数2,)4统计个数函数COUNT、COUNTA、COUNTBLANK oCOUNT(参数1, 参数2, )n功能：返回包含数字以及包含参数列表中的数字的单元格的个数。o COUNTA(参数1, 参数2, )n功能：求非空单元格的个数。oCOUNTBLANK(参数1, 参数2, )n功能：求“空”单元格的个数。5四舍五入函数ROUND（数值型参数，N）。o功

7、能：按指定的位数对数值进行四舍五入。oROUND(number, num_digits)nNumber 需要进行四舍五入的数字。nNum_digits 指定的位数，按此位数进行四舍五入。o说明说明n如果 num_digits 大于 0，则四舍五入到指定的小数位。 n如果 num_digits 等于 0，则四舍五入到最接近的整数。 n如果 num_digits 小于 0，则在小数点左侧进行四舍五入。 6条件函数IF（逻辑表达式，表达式1，表达式2）o功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。oIF(logical_test,value_if_true,value_if_false

8、)7条件求和函数SUMIF（条件数据区，“条件”，求和数据区）o功能：根据指定条件对若干单元格求和。 oSUMIF(range,criteria,sum_range)nRange 为用于条件判断的单元格区域。nCriteria 为确定哪些单元格将被相加求和的条件。nSum_range 是需要求和的实际单元格。8条件计数COUNTIF（条件数据区，“条件”）。o功能：计算区域中满足给定条件的单元格的个数。 oCOUNTIF(range,criteria)nRange 为需要计算其中满足条件的单元格数目的单元格区域。nCriteria 为确定哪些单元格将被计算在内的条件。错误值错误值错误值出现的原

9、因错误值出现的原因举例说明举例说明#DIV/0!被除数为0=3/0N/A引用了无法使用的数值#NAME?不能识别的名字=SUN(A1:A4)#NULL!交集为空=SUM(A1:A3 B1:B3)#NUM!数据类型不正确=SQRT(-4)#REF!引用无效单元格引用单元格被删除#VALUE!不正确的参数或运算符=1+”a”#宽度不够，加宽即可o一、选定/移动/复制工作表n选定工作表n在工作簿内“移动/复制”工作表n 不同的工作簿之间“移动复制”工作表n 插入/删除/重新命名工作表o二、保护工作表o目的：n不允许对工作表中的某些或全部单元格进行修改操作；n根据需要选择是否允许插入行/列、删除行/列

10、等操作。o即可以对整个工作表进行保护，也可以只保护指定的单元格区域。o1、保护工作表的条件、保护工作表的条件o条件：n被保护的单元格区域处在“锁定”状态；n执行工具菜单保护保护工作表。n默认情况下，工作表中的每个单元格都处于“锁定”状态；o2、保护工作表的步骤、保护工作表的步骤p选定允许允许修改的单元格区域，右键设置单元格格式保护取消“锁定”；p选定不允许不允许修改的单元格区域，右键设置单元格格式保护选中“锁定” （单元格缺省是锁定的）；工具菜单保护保护工作表。o3、撤销工作表的保护、撤销工作表的保护o将要撤销的工作表变成当前工作表；o单击工具菜单保护撤销工作表保护；o如果保护工作表时设置了密

11、码，必须输入密码后才可以撤销对工作表的保护。o4、隐藏工作表o隐藏工作表n格式菜单工作表隐藏。o取消隐藏n格式菜单工作表取消隐藏，在“取消隐藏”列表中选择要显示的工作表。o5、隐藏公式、隐藏公式o隐藏公式后，在编辑栏和单元格均看不到公式，只能在单元格看到公式的计算结果。o隐藏公式：n选定要隐藏的单元格区域设置单元格格式保护隐藏；n工具菜单保护保护工作表。o恢复显示公式：n工具菜单保护撤销工作表保护；选定隐藏单元格区域设置单元格格式保护取消“隐藏”；o三、保护工作簿o目的：n禁止删除、移动、重命名或插入工作表；n也可以禁止执行移动、缩放、隐藏和关闭工作簿窗口等操作。o如果保护工作簿时设置了密码，

12、只有知道密码才能取消保护。o四、隐藏工作簿n隐藏工作簿o将要隐藏的工作簿成为当前工作簿，窗口菜单隐藏。n取消隐藏o窗口菜单取消隐藏；n在“取消隐藏工作簿”列表中，选择要显示的工作簿。o五、同时显示一个工作簿的多个工作表n在默认情况下，一个工作簿内的所有工作表在一个窗口中打开，通过单击工作表标签显示不同工作表中的内容。n若希望同时看到一个工作簿内的多个工作表：n窗口菜单新建窗口；n窗口菜单重排窗口；在“重排窗口”对话框（如图）的“排列方式”中选择一项，如果只显示当前工作簿中的工作表，应选中“当前活动工作簿的窗口”复选框。o六、同时显示多个工作簿的工作表n打开多个工作簿后，同时显示多个工作簿的工作

13、表的操作与上述操作一样，只是放弃选择“当前活动工作簿的窗口”复选框。o七、拆分窗口o一个工作表窗口可以拆分为“2个窗格”或“4个窗格”，分隔条将窗格分开。o窗口拆分后，能方便地浏览一个工作表的不同部分。o拆分的方法：n鼠标拖曳滚动条上的“拆分条”；n或者，窗口菜单拆分；o取消拆分的方法：n双击滚动条上的“拆分条”；n或者，窗口菜单取消拆分；o八、冻结窗格o如果工作表过大，在向下或向右滚动显示时会看不到表头，这时可以采用“冻结”行或列的方法，冻结始终要显示的前几行或前几列。o冻结窗格的方法：n选择一个单元格；n窗口菜单冻结窗格。o撤销冻结的方法：窗口菜单取消冻结窗格o一、 图表的类型与组成Exc

14、el提供了十几种图表类型，每个图表类型又细分为多个子类型。n柱形图n条形图n折线图n饼图n面积图nXY散点图n圆环图n股价图no 图表的组成o一个图表由以下部分组成：n图表标题n坐标轴与坐标轴标题n图例n绘图区o二、创建图表n 内嵌图表与独立图表o内嵌图表与独立图表创建的方法基本相同，主要的区别是它们存放的位置不同。n创建常用图表59n柱形图、折线图、XY散点图、饼图、圆环图n*创建修饰组合图表61o三、 图表编辑与格式修饰n 图表编辑n 图表的格式修饰n*调整柱形图数据标志间距与应用举例n *在图表中显示隐藏数据表o四、视图与打印设置n 打印页面的基本设置n 添加“页眉/页脚”n 打印区域与

15、重复标题的设置o*五、 打印预览与打印n 打印预览n 打印设置n 打印输出o一、 数据清单o二、“记录单”的使用n 用“记录单”查看、编辑数据清单n 用“记录单”查找数据清单中符合条件的记录三、筛选与高级筛选n 自动筛选与应用举例n高级筛选与应用举例o条件设置n与（同行）、或（错行）o公式作为筛选条件n公式的结果作为条件来筛选记录；n条件区的第一行为空，但是仍然是条件区域的一部分。（由于系统通过公式中的地址引用来确定条件所在的列，因此不需要表头。）n公式中用作“条件”的引用必须用相对地址引用，公式中其他所有的引用都必须用绝对引用。o四、 排序o1、排序原则：n数值：按数值大小n字母与符号：09

16、空格!”#$%&()*,./:;?_|+AZn汉字o按拼音字母排序：“大”“小”o按笔划排序：按笔划多少o按“自定义序列”排序n逻辑值排序原则：FALSETRUEo注意：n无论升序还是降序排列，空白单元格总是排在最后面所有错误值的优先级相同；排序时，隐藏的行/列也会参与排序，最好取消隐藏o2、简单排序o3、自定义序列排序o五、分类汇总o分类汇总是指在数据清单中按某一列数据的值对数据清单进行分类后，按不同的类对数据进行统计。o分类汇总时要：n确定数据清单中的一列为分类字段；n分类字段中的同类别的数据在相邻的单元格（预排序）；n确定分类汇总的方式（总和、个数、平均值）；n确定要统计哪些数据

17、列。o六、数据透视表o数据透视表是对原有的数据清单重组并建立一个统计报表。o数据透视表是一种交互式的、交叉制表的Excel报表，用户对数据进行汇总和分析。o七、分级显示o分级显示可以隐藏数据表中的若干行/列，只显示指定的行/列数据。o分级显示通常用于隐藏数据表的明细数据行/列，而只显示汇总行/列。o一般情况下，汇总行在明细行的下面，汇总列在明细列的右侧。o八、合并计算o用三维公式实现合并计算o按位置合并计算o按分类合并计算o九、列表o为了更好地对工作表中的数据区进行独立的管理，可以将每一个数据区创建为一个列表。o一个工作表中可以创建多个列表，一个列表相当于一个独立的数据集，可以对列表进行筛选、

18、添加行和创建数据透视表等操作。oExcel函数一共11类，分别是数据库工作函数、日期与时间函数、工程函数、信息函数、财务函数、逻辑函数、统计函数、查找和引用函数、文本函数、数学和三角函数和用户自定义函数。o函数的构成：n函数名：不区分大小写。n参数表：用逗号分隔的参数1，参数2，参数N（N30）构成；参数可以是常数、单元格地址、单元格区域、单元格区域的名称或函数。n返回值：根据参数表给出的参数计算得到的结果。o函数嵌套n是指一个函数可以作为另一个函数的参数使用。n例如：ROUND(AVERAGE(A2:C2),0)nAVERAGE的返回值作为ROUND的参数；nExcel函数嵌套最多可嵌套七级

19、。函数的输入o单击xo插入菜单函数o快捷键：Shift+F3o数学与三角函数数学与三角函数o1、绝对值函数ABSoABS(number)n返回数字的绝对值（Absolute Value）number 需要计算其绝对值的实数n例子：ABS(5.8)=5.8 ABS(-4.3)=4.3o2、取整函数INToINT(number)n将数字向下舍入到最接近的整数（Integer）n number 需要进行向下舍入取整的实数n例子：INT(8.9) 将 8.9 向下舍入到最接近的整数 (8) INT(-8.9) 将 -8.9 向下舍入到最接近的整数 (-9) A2-INT(A2) 返回单元格 A2 中正

20、实数的小数部分 o3、截取函数TRUNC TRUNC(number, num_digits) n如果省略num_digits，截取（Truncate）number的整数部分，否则保留指定位数的小数nnumber 需要截尾取整的数字nnum_digits 小数位数，默认值为 0 n例子：TRUNC(3.6)=3TRUNC(-3.6)=-3TRUNC(5.627, 1)=5.6TRUNC(5.627, 2)=5.62o4、求余函数MODo MOD(number,divisor) （Modul）o返回两数相除的余数，结果的正负号与除数相同，符号不同取互补数。 onumber 被除数odivisor

21、除数o例子：MOD(7, 4)=3 MOD(-7, -4)=3MOD(7, -4) = -1MOD(-7, 4) = 1 o如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!o5、正弦SIN、余弦COS oSIN(x)：返回x的正弦（Sine）值，其中x用弧度表示。oCOS(x)：返回x的余弦(Cosine)值，其中x用弧度表示。o例子：用图形描述正弦和余弦的曲线o6、平方根函数SQRT oSQRT(number)o返回number的平方根（Square root）o例子：SQRT(4)=2 o说明：如果参数 Number 为负值，函数 SQRT 返回错误值 #NUM!o7、

22、随机函数RANDoRAND()o返回大于等于 0 及小于 1 的均匀分布随机（Random）数，每次计算工作表时都将返回一个新的数值。o若要生成 a 与 b 之间的随机实数，请使用： RAND()*(b-a)+ao例子：产生1-20之间的整数：INT(RAND()*20)+1o8、对数函数o自然对数函数LN(b)：返回一个数的自然对数（Logarithm），自然对数以常数项 e (2.71828182845904) 为底o对数函数LOG(b ,a)：返回以a为底，b的对数，即logabo常用对数函数LOG10(b)：返回以10为底，b的对数，即log10bo9、指数函数EXPo EXP(n)：

23、 Exponento返回 e 的 n 次幂o10、求幂函数oPOWER(a, b)：返回a的b次幂（Power），等价于abo例子：POWER(2, 3)=8POWER(2, 10) = 1024POWER(2, 20) = 1,048,576POWER(2, 30) = 1,073,741,824 统计函数统计函数o1、中位数函数MEDIANoMEDIAN(number1,number2,.) o返回给定数值集合的中值（Median），如果参加统计的数据的个数为偶数（Even），返回位于中间的两个数的平均值。o例子：MEDIAN(1, 5, 2) = 2MEDIAN(2, 1, 4, 3)

24、= 2.5 （2和3的平均数）o说明：n参数应为数字，或者是包含数字的名称、数组或引用；n如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。 o2、众数函数MODEoMODE(number1,number2,.)：一组中出现最频繁的数或数列。o返回在某一数组或数据区域中出现频率最多的数值 o如果数据集合中不含有重复的数据，则 MODE 数返回错误值 #N/A o例子：MODE(2, 5, 6, 5, 8, 6, 11, 6, 8) = 6MODE(2, 6, 5, 6, 2, 6, 5, 5) = 6o众数、中位数和平均数o在一组数值中，众数（Mo

25、de）是出现频率最高的数值，而中位数（Median）是位于中间的值，平均数（Average）是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。o假设数据分布在三个区域中，其中一半分布在一个较小数值区中，另外一半分布在两个较大数值区中。函数 AVERAGE 和函数 MEDIAN 可能会返回位于数据点稀疏处的中间值；而函数 MODE 则会返回位于数据点密集处的较小值。o3、频数函数FREQUENCY FREQUENCY(data_array,bins_array)o以一列垂直数组返回某个区域中数据的频率（Frequency）分布odata_array 为一数组或对一组数值的引用，用来计算

26、频率。obins_array 为间隔的数组或对间隔的引用，该间隔用于对 data_array 中的数值进行分组。o bins_array要求从小到大存放，每个数据表示一个分段点，统计的结果包含该数据以及小于该数据的个数o计算结果是一个数组，用“Ctrl+Shift+Enter”输入o例子：统计“职工情况表”不同年龄段的人数。o4、标准差函数STDEVo标准差（Standard Deviation）：反映一组数据于数据的平均值的离散程度。标准差函数STDEV（参数1,参数2,）功能：返回自变量表列中数据的标准偏差。自变量为数值型。-标准差反映数据相对于均值的离散程度。方差：（加平方是为了消除+-

27、号的影响）标准差：（便于与平均值对比）o如果标准差比较小，说明数据于平均值的离散程度就比较小，平均值能够反映数据的均值，具有统计意义。o如果标准差比较大，说明数据与平均值的离散程度就比较大（数据中含有非常大或非常小的数），平均值在一定程度上失去数据“均值”的意义。ninixxinssxxins122122)(1)(1o5、线型趋势值函数FORECAST FORECAST(x,known_ys,known_xs)o已知的数值为已有的 x 值和 y 值，再利用线性回归对新值进行预测（Forecast）ox 为需要进行预测的数据点oknown_ys 为因变量数组或数据区域oknown_xs 为自变量

28、数组或数据区域o逻辑函数逻辑函数o1、逻辑与函数ANDoAND(logical1, logical2, .) o所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。o说明：n参数必须是逻辑值 TRUE 或 FALSE, 或者包含逻辑值的数组或引用。 n如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 n如果指定的单元格区域内包括非逻辑值，则 AND 将返回错误值 #VALUE!。o2、逻辑或函数ORo OR(logical1, logical2, .)o任何一个参数逻辑值为 TRUE，即返回 TRUE；所有的参数均为FALSE，才返回FALSE。

29、o3、逻辑非函数NOToNOT(logical)o函数值取参数的反值。参数值为TRUE则函数值为FALSE；反之，参数值为FALSE则函数值为TRUE。o例子：工资补助表o数据库函数o数据库函数的格式：DatabaseFunction(database, field, criteria)其中：odatabase是数据清单区ofield用于用于指明要统计的列，field可以是：n字段（Field）名所在的单元格地址；n带英文双引号的“字段名”；n字段在数据清单中的位置，“1”表示第1列，等等。oCriteria：条件区，约定与高级筛选一样。o条件区o条件区的第一行是字段名或者是数据清单中字段名所

30、在单元格地址的引用，或为空白（公式作为筛选条件）；o从第二行开始设置筛选条件：筛选条件出现在不同的行表示“或”关系，筛选条件出现在相同行表示“与”关系；o在条件中允许出现“*”代表任意一个字符串，“?”代表任意一个字符；o1、求和函数DSUMo DSUM(database, field, criteria)o功能：对database区的field字段求满足条件区条件的数据的累加和o说明：nDSUM和SUM不同，SUM求任意数据的累加和，DSUM求数据清单中一列数据中满足条件的数据累加和；nDSUM包含了SUMIF的功能。SUMIF只能对给定的一个条件求数据的累加和，而DSUM可以对给定的多个条

31、件求指定的列数据的累加和。o2、其他数据库函数o平均值函数DAVERAGEo最大值函数DMAXo最小值函数DMINo统计数值型数据的个数DCOUNTn省略field字段，对数据清单区求满足条件的个数o统计个数DCOUNTAn省略field字段，对数据清单区求满足条件的个数五、财务函数及应用1偿还函数PMT(rate,nper,pv,fv,type) 。o功能：返回在固定利率下，投资或贷款的等额分期偿还额。oPMT函数自变量的解释：比率：每期的利率日期：年金付款期间的总次数现值：未来各期年金现在价值的总和预期值：最后一次付款完成后，所能获得的现金总额，默认为0类型值：表示何时付款到期。0（默认）

32、或1； 0-期末；1-期初*采用按月等额均还贷款的方式。o应用举例：o1、贷款偿还：假设想买一台分期付款的汽车，总价值200，000（现值）元，年利率为9%（9/12）%=0.75%月息），3年付清，一个月付一次，每次应付多少金额？解：=PMT（0.75%,36,200000）o结果：-6359.946532o例2、 要15年存款1500000 (预期值) ，年利率为5%，问：每月需存多少钱？o解：=PMT(0.05/12, 15*12, 0, 1500000)o或： =PMT(0.05/12, 15*12, , 1500000)o结果为：-$5,611.90o2可贷款函数PV(rate, n

33、per, pmt,fv,type)。3未来值函数FV(rate,nper,pmt,pv,type) 。4.NPER 返回投资期间的数量5.RATE 返回年金的各期利率6.EFFECT 返回实际年利率o六、日期函数及其应用1.DAY 2.MONTH 3.NOW 4.TIME 5.TODAY 6.WEEKDAY 7.YEAR 七、查找和引用函数及应用七、查找和引用函数及应用1按列查找函数VLOOKUP（查找值，数据区，列标，匹配类型）。o功能： VLOOKUP函数在第二个自变量给定的数据区域的最左列中查找第一个自变量给定的值，然后将第三个自变量指定列值返回到单元中。o匹配的类型：o若省略=true

34、=1,即返回近似值（小于查找值的最大值,数据区域的第一列必须排序)o若为0=false,要求精确匹配值。2按行查找函数HLOOKUP（查找值，数据区，行标，匹配类型）。八、 文本函数及其应用1.EXACT 检查两个文本值是否相同2.FIND 在一个文本值中查找另一个文本值（区分大小写）3.FIXED 将数字格式设置为具有固定小数位数4.取子串函数MID、LEFTRIGHT返回文本值中最左边右边的字符5.LEN 返回文本字符串中的字符个数6.LOWERUPPER将文本转换为小写大写形式7.REPLACE 替换文本中的字符8.REPT 按照给定的次数重复显示文本9.SEARCH 在一个文本值中查找

35、另一个文本值（不区分大小写）10.TRIM 从文本删除空格11.VALUE 将文本参数转换为数字o一、 用假设方法求解o目的：研究当变量在一定范围内变化时，与之相关的的公式的结果。o1、使用“单变量求解”命令求特定解108通过调整一个单元格中的值，从而求得指定单元格中的特定值的方法o*目标单元格要包含公式。o2、运用模拟运算表作模拟分析、运用模拟运算表作模拟分析n单变量输入单变量输入“模拟运算表模拟运算表”:o对一个变量输入不同的值，模拟它对一个变量输入不同的值，模拟它对对一个或多个公式一个或多个公式影响的结果影响的结果n双变量输入双变量输入“模拟运算表模拟运算表”o对两个变量输入不同的值，模

36、拟它对两个变量输入不同的值，模拟它对对一个公式一个公式的影响结果的影响结果3、方案管理器 假设多种条件，依据一定的计算得到不同的方案，以供决策。n创建方案n显示方案n编辑方案n创建方案总结报告n保护方案:可以隐藏或防止修改o 需要保护工作表n删除方案n总结方案o例:设有三种备选方案，使用方案管理器生成方案及方案摘要，从中选出最优惠的方案。ol 工商银行：贷款额300000元，付款期数120期（每月1期，共10年），年利率5.75%.ol 建设银行：贷款额300000元，付款期数180期（每月1期，共15年），年利率6.05%.ol 中国银行：贷款额300000元，付款期数240期（每月1期，共

37、20年），年利率6.3%.o二、线性回归分析o在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式（称回归方程式）。o当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。o 例1 对销售额进行多元回归分析预测，数据见图。解：本题可用二元线性回归分析来求解：设定变量：Y=销售额，X1=电视广告费用，X2=报纸广告费用方程为：Y=a1X1+a2X2+b通过线性回归分析确定a1,a2,b的值，从而确定方程。1操作方法与步骤(1) 建立数据模型(2)工具加载宏/“分析工具库”复选框(3)工具数

38、据分析/“回归”(4）设置“回归”对话框中参数:“Y值输入区”，“X值输入区”，“输出区域”(5)根据结果得出方程o三、 规划求解n“规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。n规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值。（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果）o意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果。如获得最大

39、的利润、最小的成本等。o注意事项：n在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。n添加规划求解命令:工具/加载宏 /规划求解o 1、求解线性规划问题 118o例：o工厂生产3种玩具：狗、娃娃、大熊猫；o3种玩具的净利润（元）：90、75、50o3种玩具各用机工（小时）：3、4、5o3种玩具各用手工（小时）：4、3、2o工厂极限生产能力：机工：=400 h 手工：= 280 ho 要求：l生产玩具狗的数量 = 32件 o求满足以上条件时，如何安排各玩具的生产数量以获得最大利润。a. 解：1 建立数据表如图:2 进行规划求

40、解在规划求解参数设置中输入：目标单元格地址可变单元格地址约束条件（如有多个逐一添加） 3 单击求解结果：如生产20只玩具狗、45个芭比娃娃、32只大熊猫，可以得到最大利润；o 求解的结果可形成： 运算结果报告 敏感性报告 极限值报告o*关干规划求解方法的若干选项n在调用规划求解工具时，单击“选项”，可以打开“规划求解选项”对话框：n1)最长运算时间o 在此设定求解过程的时间。可输入的最大值为32767(秒)，默认值100 (秒)可以满足大多数小型规划的求解要求。n 2)迭代次数o 在此设定求解过程中迭代运算的次数，限制求解过程的时间。可输入的最大值为32767o默认值100次可满足大多数小型规

41、划的求解要求。n3)精度o 在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值;o上下限。精度值必须表示为小数 (0到1之间)，输入数字的小数位越多，精度越高。n4)允许误差o 在此输入满足整数约束条件并可被接受的目标单元格求解结果与真实的最佳结果间的百分偏差。这个选项只应用于具有整数约束条件的问题。设置的允许误差值越大，求解过程就越快。n 5)收敛度o 在此输入收敛度数值，当最近五次迭代后目标单元格中数值的变化小于收敛度框叫置的数值时，椒划求解停止运行。收敛度只应用于非线性规划求解问题，并且必须表示夕小数(0到1之间)。设置的数值越小，收敛度就越高。例如，00叨1表示

42、比001更小的个对差别。收敛度越小，规划求解得到结果所需的时间就越长。n6)采用线性模型o 当模型中的所有关系都是线性的，并且希望解决线性优化问题时，选中此复选框可加速求解进程。n7)显示迭代结果o 如果选中此复选框，每进行一次迭代后都将中断规划求解，并显示当前的迭代结果。n8)自动按比例缩放o 如果选中此复选框，当输入和输出值量级差别很大时，可自动按比例缩放数值。例如，基于百万美元的投资将利润百分比最大化。n9)假定非负o 如果选中此复选框，则对于在“添加约束”对话框的“约束值”框中没有设置下限的所有可变单元格，假定其下限为零。n 10)估计o 指定在每个一维搜索中用来得到基本变量初始估计值

43、的逼近方案。o 正切函数:使用正切向量线性外推。o 二次方程:用二次方程外推法，提高非线性规划问题的计算精度。n n11)导数o 指定用于估计目标函数和约束函数偏导数的差分方案。o 向前差分:用于大多数约束条件数值变化相对缓慢的问题。o 中心差分:用于约束条件变化迅速，特别是接近限定值的问题。虽然此选项要求更多的o计算，但在规划求解不能返回有效解时也许会有帮助。n 12)搜索o 指定每次的迭代算法，以确定搜索方向。o 牛顿法:用牛顿法迭代需要的内存比共钝法多，但所需的迭代次数少。o 共轭法:比牛顿法需要的内存少，但要达到指定精度需要较多次的迭代运算。当间题较大和内存有限，或步进迭代进程缓慢时，

44、可用此选项。n 13)装入模型o 显示装入模型对话框，输入对所要加载的模型的引用。n 14)保存模型o 显示保存模型对话框，在其中可指定保存模型的位置。只有需要在工作表上保存多个漠型时，才使用此命令。第一个模型会自动保存。o o2 、 求解方程组的解o四、 移动平均o移动平均法是根据时间序列资料，逐项推移，依次计算移动平均，来反映现象的长期趋势。特别是现象的变量值受周期变动和不规则变动的影响，起伏较大，不能明显地反映现象的变动趋势时，运用移动平均法，消除这些因素的影响，进行动态数据的修匀，以利于进行长期趋势的分析和预测。 o简单移动平均的计算公式：o设xi为时间序列中的某时间点的观测值，其样本

45、数为N；每次移动地求算术平均值所采用的观测值的个数为n（n的取值范围：2nt-1），则在第t时间点的移动平均值Mi式中：式中：M Mi i第第t t时间点的移动平均值，时间点的移动平均值，也可当作第也可当作第t+1t+1时间点的预测值，时间点的预测值，即：即：y yi+1i+1=M=Mi i或或y yi i=M=Mi-1i-1移动平均分析工具及其公式可以基于移动平均分析工具及其公式可以基于特定的过去某段时期中变量的均值，对未来值进行预测特定的过去某段时期中变量的均值，对未来值进行预测tntiiniiiiixnxxxxnM11211)(1o简单移动平均的计算公式：简单移动平均的计算公式：o设xi

46、为时间序列中的某时间点的观测值，其样本数为N；每次移动地求算术平均值所采用的观测值的个数为n（n的取值范围：2n0为正相关，r=0.8，高度相关；0.5=r0.8，视为中度相关；0.3=r0.5，视为低度相关；|r|0.3时，视为不相关。o例例根据下图的数据，对家庭月消费支出与根据下图的数据，对家庭月消费支出与家庭月收入的数据进行相关分析。家庭月收入的数据进行相关分析。o操作步骤：操作步骤：o建立数据模型；建立数据模型；o工具工具数据分析数据分析/相关系数相关系数o设置设置“相关系数相关系数”对话框内容对话框内容o“确定”，输出结果如图所示。分析结果表明：相关系数r=0.979747601，表

47、示家庭月消费支出与家庭月收入之间存在高度正相关关系。n分析结果表明：相关系数分析结果表明：相关系数r=0.979747601，表示家庭月消费支出与家庭月收入之间存在高表示家庭月消费支出与家庭月收入之间存在高度正相关关系。度正相关关系。n操作步骤操作步骤:n（1）建立数据模型：将数据输入到工作表区域）建立数据模型：将数据输入到工作表区域A1:E16中，如图中，如图8.38所示；所示；o七 、 方差分析n方差分析方差分析(Analysis of Variance, 缩写为缩写为ANOVA)是数理统计学中常用的数据处理方法之一，是经济和科是数理统计学中常用的数据处理方法之一，是经济和科学研究中分析试

48、验数据的一种有效的工具。运用数理的学研究中分析试验数据的一种有效的工具。运用数理的方法对数据进行分析，以鉴别各种因素对研究对象某些方法对数据进行分析，以鉴别各种因素对研究对象某些特征值的影响大小和影响方式，这种方法就叫做方差分特征值的影响大小和影响方式，这种方法就叫做方差分析。析。n所关注的对象的特征称为指标，影响指标的各种原因叫所关注的对象的特征称为指标，影响指标的各种原因叫做因素，在实验中因素的各种不同状态称为因素的水平。做因素，在实验中因素的各种不同状态称为因素的水平。 n 单因素方差分析n单因素方差分析的作用是通过对某一因素的不同水平进行多次观测，然后通过统计分析判断该因素的不同水平对考察指标的影响是否相同。从理论上讲，实际上是在检验几个等方差正态总体的等均值假设。单因素方差分析的基本假设是各组的均值相等。n例1为了考察不同的销售渠道对总销