标准书号52-53%2F308-05212%2F第5章+电子表格EXCEL.ppt

1、计算机应用基础,作者：曹淑艳,责任编辑：黄娟琴 吴昌雷出版日期：2008年9月 IDPN：308-2008-019 课件章数：07,第五章 电子表格EXCEL,主要内容,5.1 基础知识与基本操作 5.2 公式、常用函数与地址引用 5.3 工作簿与工作表 5.4 图表与打印输出 5.5 数据处理与管理 5.6 函数与应用 5.7 数据分析,5.1 基础知识与基本操作,一、 简介 功能简介 启动退出关闭 窗口工作簿工作表 电子表格、工作簿(book1.xls)、工作表标签、 工作表与单元格（ AIV，256列；165536行） 文件的建立与打开 文件的保存与加密保存,Excel窗口中的各种元素,

2、菜单栏,控制菜单图标,工作表格区,状态栏,工作表标签,水平滚动条,垂直滚动条,常用 工具栏,格式 工具栏,程序控制按钮,工作簿 控制按钮,标题栏,求助框,编辑栏,PDF工具栏,5.1 基础知识与基本操作,二、 数据类型与数据输入 数值型数据与输入 日期格式与输入 分数形式3/8；3-8；当前日期CTRL+； 文本型、逻辑型数据与输入 输入大批数据的方法 在单元格内输入显示多行文本 (ALT+回车),5.1 基础知识与基本操作,三、选定/修改/删除 选定行列单元格 修改、删除数据 删除与清除的区别？,5.1 基础知识与基本操作,四、输入序列与自定义序列 自动填充序列 用填充柄填充数据序列 等差数

3、列：开始的两个单元格数据，然后拖曳 用菜单命令填充等差或等比序列 编辑菜单填充序列 自动填充文字序列 自定义序列 工具菜单选项自定义序列,5.1 基础知识与基本操作,五、移动插入/复制插入/交换数据 移动插入 Shift左键 右键拖曳 复制插入 Ctrl+Shift+左键 右键拖曳 交换数据 相邻的两行或两列做移动插入，即可实现数据交换,5.1 基础知识与基本操作,六、插入行/列/单元格 插入行/列 选定行、列或单元格（选定的数量与将要插入的数量是等同的） 插入菜单行（列），或者右键插入 插入单元格 选定单元格区域（选定的数量与将要插入的数量是等同的） 插入菜单单元格，或者右键插入,5.1 基

4、础知识与基本操作,七、命名行/列/单元格区域 命名的好处 通过名字引用单元格区域，要比用地址引用更加直观； 通过名字可以快速选定单元格区域 命名的方法 单元格区域命名：插入菜单名称定义 为选定区域的行/列命名：插入菜单名称指定首行（最左列）,5.1 基础知识与基本操作,八、批注 批注是为单元格加注释 一个单元格加批注后，右上角会出现一个三角标识 当鼠标指向这个标识的时候，显示批注信息 插入菜单批注，或者右键插入批注 工具选项视图批注,5.2 公式、常用函数与地址引用,一、公式： 以等号开始，可以包含函数、地址引用、运算符、常量。,5.2 公式、常用函数与地址引用,简单公式举例 地址引用 三种形

5、式：相对引用、绝对引用、混合引用 在公式输入时，按F4键可在几种引用之间转换。 在将公式复制到其他单元时起作用（移动时不变化）。 举例：复制移动插入删除单元格对公式的影响,5.2 公式、常用函数与地址引用,数组公式： 数组的概念：对一组数据进行运算，结果也是一组数据 注意： 使用 “Ctrl+Shift+Enter”组合键代替“Enter” 才能正确显示结果 运算结果不能单独改变 举例,二、函数 函数可以完成特殊功能或操作，使用函数可以简化和缩短公式，甚至可以完成公式不能完成的功能或操作。 （一）函数格式、输入与嵌套 函数格式： 函数名（自变量1，自变量2， ） *一个函数的自变量可以有030

6、个,5.2 公式、常用函数与地址引用,函数输入（调用）： 插入/函数 按钮 或shift+f3 函数嵌套： 将某一函数作为另一函数的参数调用。最多可以嵌套七层。 例：=if(average(a2:a6)50,sum(b2:b6),0),5.2 公式、常用函数与地址引用,函数嵌套：将某一函数，作为另一函数的参数调用。最多可以嵌套七层。 例：=if(average(a2:a6)50,sum(b2:b6),0),5.2 公式、常用函数与地址引用,（二）常用函数 1求和函数SUM(参数1,参数2,) 。 2算术平均值函数AVERAGE(参数1,参数2,)。 3最大值函数MAX(参数1,参数2,)。 最

7、小值函数MIN(参数1,参数2,) 4统计个数函数COUNT、COUNTA、COUNTBLANK,5.2 公式、常用函数与地址引用,5.2 公式、常用函数与地址引用,COUNT(参数1, 参数2, ) 功能：返回包含数字以及包含参数列表中的数字的单元格的个数。 COUNTA(参数1, 参数2, ) 功能：求非空单元格的个数。 COUNTBLANK(参数1, 参数2, ) 功能：求“空”单元格的个数。,5.2 公式、常用函数与地址引用,5四舍五入函数ROUND（数值型参数，N）。 功能：按指定的位数对数值进行四舍五入。 ROUND(number, num_digits) Number 需要进行四

8、舍五入的数字。 Num_digits 指定的位数，按此位数进行四舍五入。 说明 如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。,5.2 公式、常用函数与地址引用,6条件函数IF（逻辑表达式，表达式1，表达式2） 功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。 IF(logical_test,value_if_true,value_if_false) 7条件求和函数SUMIF（条件数据区，“条件”，求和数据区） 功能：根据指定条件对若

9、干单元格求和。 SUMIF(range,criteria,sum_range) Range 为用于条件判断的单元格区域。 Criteria 为确定哪些单元格将被相加求和的条件。 Sum_range 是需要求和的实际单元格。,5.2 公式、常用函数与地址引用,8条件计数COUNTIF（条件数据区，“条件”）。 功能：计算区域中满足给定条件的单元格的个数。 COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域。 Criteria 为确定哪些单元格将被计算在内的条件。,错误值,5.3 工作簿与工作表,一、选定/移动/复制工作表 选定工作表 在工作

10、簿内“移动/复制”工作表 不同的工作簿之间“移动复制”工作表 插入/删除/重新命名工作表,二、保护工作表 目的： 不允许对工作表中的某些或全部单元格进行修改操作； 根据需要选择是否允许插入行/列、删除行/列等操作。 即可以对整个工作表进行保护，也可以只保护指定的单元格区域。,5.3 工作簿与工作表,5.3 工作簿与工作表,1、保护工作表的条件 条件： 被保护的单元格区域处在“锁定”状态； 执行工具菜单保护保护工作表。 默认情况下，工作表中的每个单元格都处于“锁定”状态；,5.3 工作簿与工作表,2、保护工作表的步骤 选定允许修改的单元格区域，右键设置单元格格式保护取消“锁定”； 选定不允许修改

11、的单元格区域，右键设置单元格格式保护选中“锁定” （单元格缺省是锁定的）； 工具菜单保护保护工作表。,5.3 工作簿与工作表,3、撤销工作表的保护 将要撤销的工作表变成当前工作表； 单击工具菜单保护撤销工作表保护； 如果保护工作表时设置了密码，必须输入密码后才可以撤销对工作表的保护。,5.3 工作簿与工作表,4、隐藏工作表 隐藏工作表 格式菜单工作表隐藏。 取消隐藏 格式菜单工作表取消隐藏，在“取消隐藏”列表中选择要显示的工作表。,5.3 工作簿与工作表,5、隐藏公式 隐藏公式后，在编辑栏和单元格均看不到公式，只能在单元格看到公式的计算结果。 隐藏公式： 选定要隐藏的单元格区域设置单元格格式保

12、护隐藏； 工具菜单保护保护工作表。 恢复显示公式： 工具菜单保护撤销工作表保护； 选定隐藏单元格区域设置单元格格式保护取消“隐藏”；,5.3 工作簿与工作表,三、保护工作簿 目的： 禁止删除、移动、重命名或插入工作表； 也可以禁止执行移动、缩放、隐藏和关闭工作簿窗口等操作。 如果保护工作簿时设置了密码，只有知道密码才能取消保护。,5.3 工作簿与工作表,四、隐藏工作簿 隐藏工作簿 将要隐藏的工作簿成为当前工作簿，窗口菜单隐藏。 取消隐藏 窗口菜单取消隐藏； 在“取消隐藏工作簿”列表中，选择要显示的工作簿。,5.3 工作簿与工作表,五、同时显示一个工作簿的多个工作表 在默认情况下，一个工作簿内的

13、所有工作表在一个窗口中打开，通过单击工作表标签显示不同工作表中的内容。 若希望同时看到一个工作簿内的多个工作表： 窗口菜单新建窗口； 窗口菜单重排窗口； 在“重排窗口”对话框（如图）的“排列方式”中选择一项，如果只显示当前工作簿中的工作表，应选中“当前活动工作簿的窗口”复选框。,5.3 工作簿与工作表,六、同时显示多个工作簿的工作表 打开多个工作簿后，同时显示多个工作簿的工作表的操作与上述操作一样，只是放弃选择“当前活动工作簿的窗口”复选框。,5.3 工作簿与工作表,七、拆分窗口 一个工作表窗口可以拆分为“2个窗格”或“4个窗格”，分隔条将窗格分开。 窗口拆分后，能方便地浏览一个工作表的不同部

14、分。 拆分的方法： 鼠标拖曳滚动条上的“拆分条”； 或者，窗口菜单拆分； 取消拆分的方法： 双击滚动条上的“拆分条”； 或者，窗口菜单取消拆分；,5.3 工作簿与工作表,八、冻结窗格 如果工作表过大，在向下或向右滚动显示时会看不到表头，这时可以采用“冻结”行或列的方法，冻结始终要显示的前几行或前几列。 冻结窗格的方法： 选择一个单元格； 窗口菜单冻结窗格。 撤销冻结的方法： 窗口菜单取消冻结窗格,5.4 图表与打印输出,一、 图表的类型与组成 Excel提供了十几种图表类型，每个图表类型又细分为多个子类型。 柱形图 条形图 折线图 饼图 面积图 XY散点图 圆环图 股价图 ,5.4 图表与打印

15、输出,图表的组成 一个图表由以下部分组成： 图表标题 坐标轴与坐标轴标题 图例 绘图区 二、创建图表 内嵌图表与独立图表 内嵌图表与独立图表创建的方法基本相同，主要的区别是它们存放的位置不同。 创建常用图表59 柱形图、折线图、XY散点图、饼图、圆环图 *创建修饰组合图表61,5.4 图表与打印输出,三、 图表编辑与格式修饰 图表编辑 图表的格式修饰 *调整柱形图数据标志间距与应用举例 *在图表中显示隐藏数据表,5.4 图表与打印输出,四、视图与打印设置 打印页面的基本设置 添加“页眉/页脚” 打印区域与重复标题的设置,5.4 图表与打印输出,*五、 打印预览与打印 打印预览 打印设置 打印输

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

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

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

19、以将每一个数据区创建为一个列表。 一个工作表中可以创建多个列表，一个列表相当于一个独立的数据集，可以对列表进行筛选、添加行和创建数据透视表等操作。,5.6 函数与应用,Excel函数一共11类，分别是数据库工作函数、日期与时间函数、工程函数、信息函数、财务函数、逻辑函数、统计函数、查找和引用函数、文本函数、数学和三角函数和用户自定义函数。 函数的构成： 函数名：不区分大小写。 参数表：用逗号分隔的参数1，参数2，参数N（N30）构成；参数可以是常数、单元格地址、单元格区域、单元格区域的名称或函数。 返回值：根据参数表给出的参数计算得到的结果。,5.6 函数与应用,函数嵌套 是指一个函数可以作为

20、另一个函数的参数使用。 例如：ROUND(AVERAGE(A2:C2),0) AVERAGE的返回值作为ROUND的参数； Excel函数嵌套最多可嵌套七级。 函数的输入 单击x 插入菜单函数 快捷键：Shift+F3,5.6 函数与应用,数学与三角函数 1、绝对值函数ABS ABS(number) 返回数字的绝对值（Absolute Value）number 需要计算其绝对值的实数 例子： ABS(5.8)=5.8 ABS(-4.3)=4.3 2、取整函数INT INT(number) 将数字向下舍入到最接近的整数（Integer） number 需要进行向下舍入取整的实数 例子： INT(

21、8.9) 将 8.9 向下舍入到最接近的整数 (8) INT(-8.9) 将 -8.9 向下舍入到最接近的整数 (-9) A2-INT(A2) 返回单元格 A2 中正实数的小数部分,5.6 函数与应用,3、截取函数TRUNC TRUNC(number, num_digits) 如果省略num_digits，截取（Truncate）number的整数部分，否则保留指定位数的小数 number 需要截尾取整的数字 num_digits 小数位数，默认值为 0 例子： TRUNC(3.6)=3 TRUNC(-3.6)=-3 TRUNC(5.627, 1)=5.6 TRUNC(5.627, 2)=5.

22、62,5.6 函数与应用,4、求余函数MOD MOD(number,divisor) （Modul） 返回两数相除的余数，结果的正负号与除数相同，符号不同取互补数。 number 被除数 divisor 除数 例子： MOD(7, 4)=3 MOD(-7, -4)=3 MOD(7, -4) = -1 MOD(-7, 4) = 1 如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!,5.6 函数与应用,5、正弦SIN、余弦COS SIN(x)：返回x的正弦（Sine）值，其中x用弧度表示。 COS(x)：返回x的余弦(Cosine)值，其中x用弧度表示。 例子： 用图形描述正弦

23、和余弦的曲线,5.6 函数与应用,6、平方根函数SQRT SQRT(number) 返回number的平方根（Square root） 例子： SQRT(4)=2 说明：如果参数 Number 为负值，函数 SQRT 返回错误值 #NUM!,5.6 函数与应用,7、随机函数RAND RAND() 返回大于等于 0 及小于 1 的均匀分布随机（Random）数，每次计算工作表时都将返回一个新的数值。 若要生成 a 与 b 之间的随机实数，请使用： RAND()*(b-a)+a 例子： 产生1-20之间的整数：INT(RAND()*20)+1,5.6 函数与应用,8、对数函数 自然对数函数LN(b

24、)：返回一个数的自然对数（Logarithm），自然对数以常数项 e (2.71828182845904) 为底 对数函数LOG(b ,a)：返回以a为底，b的对数，即logab 常用对数函数LOG10(b)：返回以10为底，b的对数，即log10b,5.6 函数与应用,9、指数函数EXP EXP(n)： Exponent 返回 e 的 n 次幂,5.6 函数与应用,10、求幂函数 POWER(a, b)：返回a的b次幂（Power），等价于ab 例子： POWER(2, 3)=8 POWER(2, 10) = 1024 POWER(2, 20) = 1,048,576 POWER(2, 30

25、) = 1,073,741,824,5.6 函数与应用,统计函数 1、中位数函数MEDIAN MEDIAN(number1,number2,.) 返回给定数值集合的中值（Median），如果参加统计的数据的个数为偶数（Even），返回位于中间的两个数的平均值。 例子： MEDIAN(1, 5, 2) = 2 MEDIAN(2, 1, 4, 3) = 2.5 （2和3的平均数） 说明： 参数应为数字，或者是包含数字的名称、数组或引用； 如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。,5.6 函数与应用,2、众数函数MODE MODE(number

26、1,number2,.)：一组中出现最频繁的数或数列。 返回在某一数组或数据区域中出现频率最多的数值 如果数据集合中不含有重复的数据，则 MODE 数返回错误值 #N/A 例子： MODE(2, 5, 6, 5, 8, 6, 11, 6, 8) = 6 MODE(2, 6, 5, 6, 2, 6, 5, 5) = 6,5.6 函数与应用,众数、中位数和平均数 在一组数值中，众数（Mode）是出现频率最高的数值，而中位数（Median）是位于中间的值，平均数（Average）是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。 假设数据分布在三个区域中，其中一半分布在一个较小数值区中，另

27、外一半分布在两个较大数值区中。函数 AVERAGE 和函数 MEDIAN 可能会返回位于数据点稀疏处的中间值；而函数 MODE 则会返回位于数据点密集处的较小值。,5.6 函数与应用,3、频数函数FREQUENCY FREQUENCY(data_array,bins_array) 以一列垂直数组返回某个区域中数据的频率（Frequency）分布 data_array 为一数组或对一组数值的引用，用来计算频率。 bins_array 为间隔的数组或对间隔的引用，该间隔用于对 data_array 中的数值进行分组。 bins_array要求从小到大存放，每个数据表示一个分段点，统计的结果包含该数

28、据以及小于该数据的个数 计算结果是一个数组，用“Ctrl+Shift+Enter”输入 例子：统计“职工情况表”不同年龄段的人数。,5.6 函数与应用,4、标准差函数STDEV 标准差（Standard Deviation）：反映一组数据于数据的平均值的离散程度。 标准差函数STDEV（参数1,参数2,） 功能：返回自变量表列中数据的标准偏差。 自变量为数值型。 -标准差反映数据相对于均值的离散程度。,5.6 函数与应用,方差： （加平方是为了 消除+-号的影响） 标准差： （便于与平均值对比） 如果标准差比较小，说明数据于平均值的离散程度就比较小，平均值能够反映数据的均值，具有统计意义。 如

29、果标准差比较大，说明数据与平均值的离散程度就比较大（数据中含有非常大或非常小的数），平均值在一定程度上失去数据“均值”的意义。,5.6 函数与应用,5、线型趋势值函数FORECAST FORECAST(x,known_ys,known_xs) 已知的数值为已有的 x 值和 y 值，再利用线性回归对新值进行预测（Forecast） x 为需要进行预测的数据点 known_ys 为因变量数组或数据区域 known_xs 为自变量数组或数据区域,逻辑函数 1、逻辑与函数AND AND(logical1, logical2, .) 所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返

30、回 FALSE。 说明： 参数必须是逻辑值 TRUE 或 FALSE, 或者包含逻辑值的数组或引用。 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 如果指定的单元格区域内包括非逻辑值，则 AND 将返回错误值 #VALUE!。,5.6 函数与应用,2、逻辑或函数OR OR(logical1, logical2, .) 任何一个参数逻辑值为 TRUE，即返回 TRUE；所有的参数均为FALSE，才返回FALSE。 3、逻辑非函数NOT NOT(logical) 函数值取参数的反值。参数值为TRUE则函数值为FALSE；反之，参数值为FALSE则函数值为TRUE。 例子：工资补助表

31、,5.6 函数与应用,数据库函数 数据库函数的格式： DatabaseFunction(database, field, criteria) 其中： database是数据清单区 field用于用于指明要统计的列，field可以是： 字段（Field）名所在的单元格地址； 带英文双引号的“字段名”； 字段在数据清单中的位置，“1”表示第1列，等等。 Criteria：条件区，约定与高级筛选一样。,5.6 函数与应用,条件区 条件区的第一行是字段名或者是数据清单中字段名所在单元格地址的引用，或为空白（公式作为筛选条件）； 从第二行开始设置筛选条件：筛选条件出现在不同的行表示“或”关系，筛选条件出

32、现在相同行表示“与”关系； 在条件中允许出现“*”代表任意一个字符串，“?”代表任意一个字符；,5.6 函数与应用,1、求和函数DSUM DSUM(database, field, criteria) 功能：对database区的field字段求满足条件区条件的数据的累加和 说明： DSUM和SUM不同，SUM求任意数据的累加和，DSUM求数据清单中一列数据中满足条件的数据累加和； DSUM包含了SUMIF的功能。SUMIF只能对给定的一个条件求数据的累加和，而DSUM可以对给定的多个条件求指定的列数据的累加和。,5.6 函数与应用,2、其他数据库函数 平均值函数DAVERAGE 最大值函数D

33、MAX 最小值函数DMIN 统计数值型数据的个数DCOUNT 省略field字段，对数据清单区求满足条件的个数 统计个数DCOUNTA 省略field字段，对数据清单区求满足条件的个数,5.6 函数与应用,五、财务函数及应用 1偿还函数PMT(rate,nper,pv,fv,type) 。 功能：返回在固定利率下，投资或贷款的等额分期偿还额。 PMT函数自变量的解释：比率：每期的利率日期：年金付款期间的总次数现值：未来各期年金现在价值的总和预期值：最后一次付款完成后，所能获得的现金总额，默认为0类型值：表示何时付款到期。0（默认）或1； 0-期末；1-期初*采用按月等额均还贷款的方式。,5.6

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

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

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

37、PLACE 替换文本中的字符 8.REPT 按照给定的次数重复显示文本 9.SEARCH 在一个文本值中查找另一个文本值（不区分大小写） 10.TRIM 从文本删除空格 11.VALUE 将文本参数转换为数字,5.7 数据分析,一、 用假设方法求解 目的：研究当变量在一定范围内变化时，与之相关的的公式的结果。 1、使用“单变量求解”命令求特定解108 通过调整一个单元格中的值，从而求得指定单元格中的特定值的方法 *目标单元格要包含公式。,5.7 数据分析,2、运用模拟运算表作模拟分析 单变量输入“模拟运算表”: 对一个变量输入不同的值，模拟它对一个或多个公式影响的结果 双变量输入“模拟运算表”

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

39、（每月1期，共20年），年利率6.3%.,5.7 数据分析,二、线性回归分析 在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式（称回归方程式）。 当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。,5.7 数据分析,例1 对销售额进行多元回归分析预测，数据见图。,5.7 数据分析,解：本题可用二元线性回归分析来求解： 设定变量：Y=销售额，X1=电视广告费用，X2=报纸广告费用 方程为：Y=a1X1+a2X2+b 通过线性回归分析确定a1,a2,b的值，从而确定方程。,5.7 数

40、据分析,1操作方法与步骤 (1) 建立数据模型 (2)工具加载宏/“分析工具库”复选框 (3)工具数据分析/“回归” (4）设置“回归”对话框中参数: “Y值输入区”，“X值输入区”，“输出区域” (5)根据结果得出方程,5.7 数据分析,三、 规划求解 “规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。 规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值。（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式

41、中求得期望的结果）,5.7 数据分析,意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果。如获得最大的利润、最小的成本等。 注意事项： 在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。 添加规划求解命令:工具/加载宏 /规划求解,5.7 数据分析,1、求解线性规划问题118 例： 工厂生产3种玩具：狗、娃娃、大熊猫； 3种玩具的净利润（元）：90、75、50 3种玩具各用机工（小时）：3、4、5 3种玩具各用手工（小时）：4、3、2,5.7 数据分析,工厂极限生产能力：机工：= 3

42、2件 求满足以上条件时，如何安排各玩具的生产数量以获得最大利润。,5.7 数据分析,解： 1 建立数据表如图: 2 进行规划求解 在规划求解参数设置中输入： 目标单元格地址 可变单元格地址 约束条件（如有多个逐一添加） 3单击求解 结果：如生产20只玩具狗、45个芭比娃娃、32只大熊猫，可以得到最大利润；,5.7 数据分析,求解的结果可形成： 运算结果报告 敏感性报告 极限值报告,5.7 数据分析,*关干规划求解方法的若干选项 在调用规划求解工具时，单击“选项”，可以打开“规划求解选项”对话框： 1)最长运算时间 在此设定求解过程的时间。可输入的最大值为32767(秒)，默认值100 (秒)可

43、以满足大多数小型规划的求解要求。,5.7 数据分析,2)迭代次数 在此设定求解过程中迭代运算的次数，限制求解过程的时间。可输入的最大值为32767 默认值100次可满足大多数小型规划的求解要求。 3)精度 在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值; 上下限。精度值必须表示为小数 (0到1之间)，输入数字的小数位越多，精度越高。 4)允许误差 在此输入满足整数约束条件并可被接受的目标单元格求解结果与真实的最佳结果间的百分偏差。这个选项只应用于具有整数约束条件的问题。设置的允许误差值越大，求解过程就越快。,5.7 数据分析,5)收敛度 在此输入收敛度数值，当最近五

44、次迭代后目标单元格中数值的变化小于收敛度框叫置的数值时，椒划求解停止运行。收敛度只应用于非线性规划求解问题，并且必须表示夕小数(0到1之间)。设置的数值越小，收敛度就越高。例如，00叨1表示比001更小的个对差别。收敛度越小，规划求解得到结果所需的时间就越长。 6)采用线性模型 当模型中的所有关系都是线性的，并且希望解决线性优化问题时，选中此复选框可加速求解进程。 7)显示迭代结果 如果选中此复选框，每进行一次迭代后都将中断规划求解，并显示当前的迭代结果。 8)自动按比例缩放 如果选中此复选框，当输入和输出值量级差别很大时，可自动按比例缩放数值。例如，基于百万美元的投资将利润百分比最大化。,5

45、.7 数据分析,9)假定非负 如果选中此复选框，则对于在“添加约束”对话框的“约束值”框中没有设置下限的所有可变单元格，假定其下限为零。 10)估计 指定在每个一维搜索中用来得到基本变量初始估计值的逼近方案。 正切函数:使用正切向量线性外推。 二次方程:用二次方程外推法，提高非线性规划问题的计算精度。,5.7 数据分析,11)导数 指定用于估计目标函数和约束函数偏导数的差分方案。 向前差分:用于大多数约束条件数值变化相对缓慢的问题。 中心差分:用于约束条件变化迅速，特别是接近限定值的问题。虽然此选项要求更多的 计算，但在规划求解不能返回有效解时也许会有帮助。,5.7 数据分析,12)搜索 指定

46、每次的迭代算法，以确定搜索方向。 牛顿法:用牛顿法迭代需要的内存比共钝法多，但所需的迭代次数少。 共轭法:比牛顿法需要的内存少，但要达到指定精度需要较多次的迭代运算。当间题较大和内存有限，或步进迭代进程缓慢时，可用此选项。 13)装入模型 显示装入模型对话框，输入对所要加载的模型的引用。 14)保存模型 显示保存模型对话框，在其中可指定保存模型的位置。只有需要在工作表上保存多个漠型时，才使用此命令。第一个模型会自动保存。,5.7 数据分析,2 、 求解方程组的解,5.7 数据分析,四、 移动平均 移动平均法是根据时间序列资料，逐项推移，依次计算移动平均，来反映现象的长期趋势。特别是现象的变量值

47、受周期变动和不规则变动的影响，起伏较大，不能明显地反映现象的变动趋势时，运用移动平均法，消除这些因素的影响，进行动态数据的修匀，以利于进行长期趋势的分析和预测。,5.7 数据分析,简单移动平均的计算公式： 设xi为时间序列中的某时间点的观测值，其样本数为N；每次移动地求算术平均值所采用的观测值的个数为n（n的取值范围：2nt-1），则在第t时间点的移动平均值Mi,式中：Mi第t时间点的移动平均值， 也可当作第t+1时间点的预测值， 即：yi+1=Mi或yi=Mi-1 移动平均分析工具及其公式可以基于 特定的过去某段时期中变量的均值，对未来值进行预测,5.7 数据分析,简单移动平均的计算公式：

48、设xi为时间序列中的某时间点的观测值，其样本数为N；每次移动地求算术平均值所采用的观测值的个数为n（n的取值范围：2nt-1），则在第t时间点的移动平均值Mi,式中：Mi第t时间点的移动平均值，也可当作第t+1时间点的预测值， 即：yi+1=Mi或yi=Mi-1 移动平均分析工具及其公式可以基于特定的过去某段时期中变量的均值，对未来值进行预测。,5.7 数据分析,例1 某公司1994至2005年销售额数据如图8.30所示。进行三年移动平均，并预测2006年销售额。 操作步骤： 建立模型,工具数据分析/“移动平均” 设置“移动平均”对话框，确定。,5.7 数据分析,结果如图。单元格区域D5:D1

49、2即为标准误差。根据公式yi+1=Mi得2006年的销售额预测应为M2005，所以为640。,5.7 数据分析,五、 指数平滑 指数平滑是在移动平均的基础上的进一步扩展。指数平滑法是用过去时间数列值的加权平均数作为趋势值，越靠近当前时间的指标越具有参考价值，因此给予更大的权重，按照这种随时间指数衰减的规律对原始数据进行加权修匀。所以它是加权移动平均法的一种特殊情形。其基本形式是根据本期的实际值Yt和本期的趋势值，分别给以不同权数和1，计算加权平均数作为下期的趋势值。,5.7 数据分析,例1利用上题例题的数据，用Excel进行单指数平滑进行分析。 操作步骤： 建立数据模型； 工具数据分析/指数平

50、滑”； 设置“指数平滑”对话框,5.7 数据分析,5.7 数据分析,结果：,5.7 数据分析,六、 相关分析 相关关系是指变量之间存在的不完全确定性的关系。在实际问题中，许多变量之间的关系并不是完全确定性的，例如居民家庭消费与居民家庭收入这两个变量的关系就不是完全确定的。收入水平相同的家庭，它们的消费额往往不同；消费额相同的家庭，它们的收入也可能不同。对现象之间相关关系密切程度的研究，称为相关分析。 对两个变量之间线性相关程度的度量称为简单相关系数-r。,5.7 数据分析,简单相关系数又称皮尔逊相关系数，它描述了两个定距变量间联系的紧密程度。样本的简单相关系数一般用r表示。 R的性质：r0为正相关，r=0.8，高度相关；0.5=r0.8，视为中度相关；0.3=r0.5，视为低度相关；|r|0.3时，视为不相关。,5.7 数据分析,例根据下图的数据，对家庭月消费支出与家庭月收入的数据进行相关分析。,5.7 数据分析,操作步骤： 建立数据模型； 工具数据分析/相关系数 设置“相关系数”对话