完整版教程excel课件

对外经济贸易大学信息学院,应用软件Excel,考核说明,学分课时：2学分，36课时其中理论课18课时，实验课18学时考核方式：课堂表现、上机作业、上机考试期末考试，闭卷考试，笔试。总评成绩：平时成绩占20%，上机测试占20%，期末考试占60%,1主教材：Excel实用教程杨尚群 乔红 蒋亚君 编著 人民邮电出版社2参考书：Excel应用教程 谢柏青 贺卫军 编著 高等教育出版社经营分析方法与IT工具 姜旭平 著 清华大学出版社3.参考资料： 帮助菜单网上资源,主要内容,第1章 基础知识与基本操作第2章 公式、常用函数与地址引用第3章 工作簿与工作表第4章 格式化工作表第5章 图表与打印输出第6章 数据处理与管理第7章 函数与应用 第8章 数据分析 第9章 其他,二课时 本章重点： 单元格与单元格区域的表示方法 数据类型（数值型、文本型、逻辑型）及其数据的输入方法 数据序列填充（等差序列、等比序列和日期序列）、自定义序列,第1章 基础知识与基本操作,第1章 基础知识与基本操作,一、 简介功能简介启动退出关闭窗口工作簿工作表电子表格、工作簿(book1.xls)、工作表标签、工作表与单元格（ AIV，256列；165536行）文件的建立与打开文件的保存加密保存工具保护保护工作簿,Excel窗口中的各种元素,菜单栏,控制菜单图标,工作表格区,状态栏,工作表标签,水平滚动条,垂直滚动条,常用工具栏,格式工具栏,程序控制按钮,工作簿控制按钮,标题栏,求助框,编辑栏,PDF工具栏,第1章 基础知识与基本操作,二、数据类型与数据输入 数值型数据与输入日期格式与输入分数形式3/8；3-8；当前日期CTRL+； 文本型、逻辑型数据与输入 输入大批数据的方法在单元格内输入显示多行文本自动换行格式单元格 对齐自动换行强制换行 (ALT+回车),第1章 基础知识与基本操作,三、选定/修改/删除选定行列单元格修改、删除数据删除与清除的区别？,第1章 基础知识与基本操作,四、输入序列与自定义序列自动填充序列用填充柄填充数据序列等差数列：开始的两个单元格数据，然后拖曳用菜单命令填充等差或等比序列编辑菜单填充序列自动填充文字序列自定义序列工具菜单选项自定义序列,第1章 基础知识与基本操作,五、移动插入/复制插入/交换数据移动插入Shift左键右键拖曳复制插入Ctrl+Shift+左键右键拖曳交换数据相邻的两行或两列做移动插入，即可实现数据交换,第1章 基础知识与基本操作,六、插入行/列/单元格插入行/列选定行、列或单元格（选定的数量与将要插入的数量是等同的）插入菜单行（列），或者右键插入插入单元格选定单元格区域（选定的数量与将要插入的数量是等同的）插入菜单单元格，或者右键插入,第1章 基础知识与基本操作,七、命名行/列/单元格区域命名的好处通过名字引用单元格区域，要比用地址引用更加直观；通过名字可以快速选定单元格区域命名的方法单元格区域命名：插入菜单名称定义为选定区域的行/列命名：插入菜单名称指定首行（最左列）,第1章 基础知识与基本操作,八、批注批注是为单元格加注释一个单元格加批注后，右上角会出现一个三角标识当鼠标指向这个标识的时候，显示批注信息插入菜单批注，或者右键插入批注工具选项视图批注,本章小结,Excel简介数据类型与数据输入选定/修改/删除输入序列与自定义序列复制移动/插入删除命名行/列/单元格区域批注作业：P20/习题P146/实验1 基础操作,第2章 公式、常用函数与地址引用,二课时本章重点：运算符、公式与常用函数相对地址、绝对地址和混合地址公式的复制对公式中地址引用的影响难点：相对地址、绝对地址和混合地址的正确使用,公式的使用,公式：所在单元格的值由计算得到单元格引用：A1引用样式和R1C1引用样式公式的组成“=”打头运算符：算术运算符：+，-，*，/，%，；文本运算符：&；比较运算符：=，=，；引用运算符：(冒号)，(逗号)，(空格)。,第2章 公式、常用函数与地址引用,一、公式：以等号开始，可以包含函数、地址引用、运算符、常量。,引用的样式,A1样式先列后行,列用字母,行用数字通常采用的样式R1C1样式先行后列,行前加字母R,列前加字母C方便编程,宏和模块中采用本课程中一律采用A1样式,第2章 公式、常用函数与地址引用,简单公式举例地址引用 三种形式：相对引用、绝对引用、混合引用在公式输入时，按F4键可在几种引用之间转换。在将公式复制到其他单元时起作用（移动时不变化）。举例：复制移动插入删除单元格对公式的影响,第2章 公式、常用函数与地址引用,数组公式：数组的概念：对一组数据进行运算，结果也是一组数据注意：使用 “Ctrl+Shift+Enter”组合键代替“Enter” 才能正确显示结果运算结果不能单独改变 举例,二、函数 函数可以完成特殊功能或操作，使用函数可以简化和缩短公式，甚至可以完成公式不能完成的功能或操作。（一）函数格式、输入与嵌套25函数格式：函数名（自变量1，自变量2， ）*一个函数的自变量可以有030个,第2章 公式、常用函数与地址引用,函数输入（调用）：插入/函数 按钮或shift+f3函数嵌套：将某一函数作为另一函数的参数调用。最多可以嵌套七层。例：=if(average(a2:a6)50,sum(b2:b6),0),第2章 公式、常用函数与地址引用,函数嵌套：将某一函数，作为另一函数的参数调用。最多可以嵌套七层。例：=if(average(a2:a6)50,sum(b2:b6),0),第2章 公式、常用函数与地址引用,（二）常用函数1求和函数SUM(参数1,参数2,) 。2算术平均值函数AVERAGE(参数1,参数2,)。3最大值函数MAX(参数1,参数2,)。 最小值函数MIN(参数1,参数2,)4统计个数函数COUNT、COUNTA、COUNTBLANK,第2章 公式、常用函数与地址引用,第2章 公式、常用函数与地址引用,COUNT(参数1, 参数2, )功能：返回包含数字以及包含参数列表中的数字的单元格的个数。 COUNTA(参数1, 参数2, )功能：求非空单元格的个数。COUNTBLANK(参数1, 参数2, )功能：求“空”单元格的个数。,第2章 公式、常用函数与地址引用,5四舍五入函数ROUND（数值型参数，N）。功能：按指定的位数对数值进行四舍五入。ROUND(number, num_digits)Number 需要进行四舍五入的数字。Num_digits 指定的位数，按此位数进行四舍五入。说明如果 num_digits 大于 0，则四舍五入到指定的小数位。 如果 num_digits 等于 0，则四舍五入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行四舍五入。,第2章 公式、常用函数与地址引用,6条件函数IF（逻辑表达式，表达式1，表达式2）功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果。IF(logical_test,value_if_true,value_if_false)7条件求和函数SUMIF（条件数据区，“条件”，求和数据区）功能：根据指定条件对若干单元格求和。 SUMIF(range,criteria,sum_range)Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件。Sum_range 是需要求和的实际单元格。,第2章 公式、常用函数与地址引用,8条件计数COUNTIF（条件数据区，“条件”）。功能：计算区域中满足给定条件的单元格的个数。 COUNTIF(range,criteria)Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件。,错误值,第2章 公式、常用函数与地址引用,小结预习第3章作业：教材P41/习题2教材P173/实验11.2,第3章 工作簿与工作表,二课时本章重点：保护/隐藏-工作表保护-工作簿,第3章 工作簿与工作表,一、选定/移动/复制工作表选定工作表在工作簿内“移动/复制”工作表 不同的工作簿之间“移动复制”工作表 插入/删除/重新命名工作表,多张工作表的操作,引用另一张工作表上的数据一般格式：!例：sheet3!b5引用其他工作簿上的数据一般格式：!例：Book1.xlssheet3!b5工作表的插入、复制、移动和删除 可以制作单独的统计表,二、保护工作表目的：不允许对工作表中的某些或全部单元格进行修改操作；根据需要选择是否允许插入行/列、删除行/列等操作。即可以对整个工作表进行保护，也可以只保护指定的单元格区域。,第3章 工作簿与工作表,第3章 工作簿与工作表,1、保护工作表的条件条件：被保护的单元格区域处在“锁定”状态；执行工具菜单保护保护工作表。默认情况下，工作表中的每个单元格都处于“锁定”状态；,第3章 工作簿与工作表,2、保护工作表的步骤选定允许修改的单元格区域，右键设置单元格格式保护取消“锁定”；选定不允许修改的单元格区域，右键设置单元格格式保护选中“锁定” （单元格缺省是锁定的）；工具菜单保护保护工作表。,第3章 工作簿与工作表,3、撤销工作表的保护将要撤销的工作表变成当前工作表；单击工具菜单保护撤销工作表保护；如果保护工作表时设置了密码，必须输入密码后才可以撤销对工作表的保护。,第3章 工作簿与工作表,4、隐藏工作表隐藏工作表格式菜单工作表隐藏。取消隐藏格式菜单工作表取消隐藏，在“取消隐藏”列表中选择要显示的工作表。,第3章 工作簿与工作表,5、隐藏公式隐藏公式后，在编辑栏和单元格均看不到公式，只能在单元格看到公式的计算结果。隐藏公式：选定要隐藏的单元格区域设置单元格格式保护隐藏；工具菜单保护保护工作表。恢复显示公式：工具菜单保护撤销工作表保护；选定隐藏单元格区域设置单元格格式保护取消“隐藏”；,第3章 工作簿与工作表,三、保护工作簿目的：禁止删除、移动、重命名或插入工作表；也可以禁止执行移动、缩放、隐藏和关闭工作簿窗口等操作。如果保护工作簿时设置了密码，只有知道密码才能取消保护。,第3章 工作簿与工作表,四、隐藏工作簿隐藏工作簿将要隐藏的工作簿成为当前工作簿，窗口菜单隐藏。取消隐藏窗口菜单取消隐藏；在“取消隐藏工作簿”列表中，选择要显示的工作簿。,第3章 工作簿与工作表,五、同时显示一个工作簿的多个工作表在默认情况下，一个工作簿内的所有工作表在一个窗口中打开，通过单击工作表标签显示不同工作表中的内容。若希望同时看到一个工作簿内的多个工作表：窗口菜单新建窗口；窗口菜单重排窗口；在“重排窗口”对话框（如图）的“排列方式”中选择一项，如果只显示当前工作簿中的工作表，应选中“当前活动工作簿的窗口”复选框。,第3章 工作簿与工作表,六、同时显示多个工作簿的工作表打开多个工作簿后，同时显示多个工作簿的工作表的操作与上述操作一样，只是放弃选择“当前活动工作簿的窗口”复选框。,第3章 工作簿与工作表,七、拆分窗口一个工作表窗口可以拆分为“2个窗格”或“4个窗格”，分隔条将窗格分开。窗口拆分后，能方便地浏览一个工作表的不同部分。拆分的方法：鼠标拖曳滚动条上的“拆分条”；或者，窗口菜单拆分；取消拆分的方法：双击滚动条上的“拆分条”；或者，窗口菜单取消拆分；,第3章 工作簿与工作表,八、冻结窗格如果工作表过大，在向下或向右滚动显示时会看不到表头，这时可以采用“冻结”行或列的方法，冻结始终要显示的前几行或前几列。冻结窗格的方法：选择一个单元格；窗口菜单冻结窗格。撤销冻结的方法：窗口菜单取消冻结窗格,本章小结,选定工作表；移动/复制工作表；插入/删除/重命名工作表；保护工作表；隐藏工作表；隐藏公式；保护工作簿；隐藏工作簿；同时显示多个工作表；拆分窗口；冻结窗格。,本章作业,预习：第四章 格式化工作表第五章 图表与打印输出作业：教材P51 习题教材P160 实验10.3,第4章 格式化工作表,一课时本章重点： 自定义数据的显示格式,第4章 格式化工作表,格式工具栏改变数据显示格式的按钮：,第4章 格式化工作表,一、改变数据的显示格式选定要改变显示格式的单元格区域；格式菜单单元格数字，或者右键设置单元格格式数字；在“分类”列表中选择数据格式的类别。,第4章 格式化工作表,二、自定义数据的显示格式48格式/单元格/数字/分类框-自定义0与#的区别；设置千、百万格式,第4章 格式化工作表,三、数据的格式修饰数据的格式修饰包括改变数据的字体、字型、字号、颜色等。方法：格式工具栏的按钮单元格格式字体,第4章 格式化工作表,四、条件格式条件格式用于为满足条件的单元格数据设置特定的文字格式、边框和底纹。选定单元格区域格式菜单条件格式,第4章 格式化工作表,五、 表格的格式修饰51调整行高列宽51隐藏行/列标号51对齐方式与合并单元格52大标题的合并与居中*边框底纹52*手动绘制边框53*六、 插入图片、艺术字53*七、定位、查找与替换54,七、定位、查找与替换,第4章 格式化工作表,小结预习第5章作业：教材P63/习题教材P175/实验11.4,第5章 图表与打印输出,一课时 本章重点： 内嵌图表与独立图表 常用图表,第5章 图表与打印输出,一、 图表的类型与组成65Excel提供了十几种图表类型，每个图表类型又细分为多个子类型。柱形图条形图折线图饼图面积图XY散点图圆环图股价图,第5章 图表与打印输出,图表的组成一个图表由以下部分组成：图表标题坐标轴与坐标轴标题图例绘图区二、创建图表 内嵌图表与独立图表内嵌图表与独立图表创建的方法基本相同，主要的区别是它们存放的位置不同。创建常用图表59柱形图、折线图、XY散点图、饼图、圆环图*创建修饰组合图表61,第5章 图表与打印输出,三、 图表编辑与格式修饰 图表编辑 图表的格式修饰*调整柱形图数据标志间距与应用举例 *在图表中显示隐藏数据表,第5章 图表与打印输出,四、视图与打印设置 打印页面的基本设置 添加“页眉/页脚” 打印区域与重复标题的设置,第5章 图表与打印输出,*五、 打印预览与打印 打印预览 打印设置 打印输出,第5章 图表与打印输出,小结预习第6章作业：教材P 63 /习题教材P175/实验11.4,第6章 数据处理与管理,四课时 本章重点： 筛选与高级筛选 分类汇总 数据透视表,第6章 数据处理与管理,一、 数据清单82二、“记录单”的使用 用“记录单”查看、编辑数据清单 用“记录单”查找数据清单中符合条件的记录,第6章 数据处理与管理,三、筛选与高级筛选 自动筛选与应用举例高级筛选与应用举例条件设置与（同行）、或（错行）公式作为筛选条件公式的结果作为条件来筛选记录；条件区的第一行为空，但是仍然是条件区域的一部分。（由于系统通过公式中的地址引用来确定条件所在的列，因此不需要表头。）公式中用作“条件”的引用必须用相对地址引用，公式中其他所有的引用都必须用绝对引用。跨表筛选,第6章 数据处理与管理,四、 排序1、排序原则：数值：按数值大小字母与符号：09空格!”#$%?_|+AZ汉字按拼音字母排序：“大”“小”按笔划排序：按笔划多少按“自定义序列”排序逻辑值排序原则：FALSE分类汇总分类汇总命令的参数分类依据的字段（一般先按此项排序）汇总方式（每次命令选一种）汇总项（可以多项）3个复选框分类汇总命令的结果插入的行中保存结果明细数据的显示和隐藏,第6章 数据处理与管理,六、数据透视表数据透视表是对原有的数据清单重组并建立一个统计报表。数据透视表是一种交互式的、交叉制表的Excel报表，用户对数据进行汇总和分析。,第6章 数据处理与管理,七、分级显示分级显示可以隐藏数据表中的若干行/列，只显示指定的行/列数据。分级显示通常用于隐藏数据表的明细数据行/列，而只显示汇总行/列。一般情况下，汇总行在明细行的下面，汇总列在明细列的右侧。,第6章 数据处理与管理,八、合并计算用三维公式实现合并计算按位置合并计算按分类合并计算,第6章 数据处理与管理,九、列表为了更好地对工作表中的数据区进行独立的管理，可以将每一个数据区创建为一个列表。一个工作表中可以创建多个列表，一个列表相当于一个独立的数据集，可以对列表进行筛选、添加行和创建数据透视表等操作。,本章小结,数据清单记录单自动筛选&高级筛选排序分类汇总数据透视表分级显示合并计算列表,本章作业,预习第七章 函数与应用作业教材P102 习题教材P163 实验10.5 排序和筛选教材P166 实验10.6 分类汇总和数据透视表,第7章 函数与应用,六课时 本章重点： 统计函数、财务函数、数据库函数函数：ABS、INT、MOD、MODE、FREQUENCY、STDEV、AND、OR、NOT、DSUM、DAVERAGE、DMAX、 DMIN、DCOUNT、DCOUNTA、PMT、VLOOKUP,第7章 函数与应用,Excel函数分别是数据库函数日期与时间函数信息函数财务函数逻辑函数统计函数查找和引用函数文本函数数学和三角函数,函数的构成,函数名：不区分大小写。参数表：用逗号分隔的参数1，参数2，参数N（N30）构成；参数可以是常数、单元格地址、单元格区域、单元格区域的名称或函数。返回值：根据参数表给出的参数计算得到的结果。,第7章 函数与应用,函数嵌套函数嵌套是指一个函数可以作为另一个函数的参数使用。例如：ROUND(AVERAGE(A2:C2),0)AVERAGE的返回值作为ROUND的参数；Excel函数嵌套最多可嵌套七级。函数的输入单击x插入菜单函数快捷键：Shift+F3,数学与三角函数,绝对值函数ABSABS(number)返回数字的绝对值（Absolute Value）number 需要计算其绝对值的实数例子：ABS(5.8)=5.8 ABS(-4.3)=4.3取整函数INTINT(number)将数字向下舍入到最接近的整数（Integer） number 需要进行向下舍入取整的实数例子：INT(8.9) 将 8.9 向下舍入到最接近的整数 (8) INT(-8.9) 将 -8.9 向下舍入到最接近的整数 (-9) A2-INT(A2) 返回单元格 A2 中正实数的小数部分,数学与三角函数,截取函数TRUNC TRUNC(number, num_digits) 如果省略num_digits，截取（Truncate）number的整数部分，否则保留指定位数的小数number 需要截尾取整的数字num_digits 小数位数，默认值为 0 例子：TRUNC(3.6)=3TRUNC(-3.6)=-3TRUNC(5.627, 1)=5.6TRUNC(5.627, 2)=5.62,数学与三角函数,求余函数MOD MOD(number,divisor) （Modul）返回两数相除的余数，结果的正负号与除数相同，符号不同取互补数。 number 被除数divisor 除数例子：MOD(7, 4)=3 MOD(-7, -4)=3MOD(7, -4) = -1MOD(-7, 4) = 1 如果 divisor 为零，函数 MOD 返回错误值 #DIV/0!,数学与三角函数,正弦SIN、余弦COS SIN(x)：返回x的正弦（Sine）值，其中x用弧度表示。COS(x)：返回x的余弦(Cosine)值，其中x用弧度表示。例子：用图形描述正弦和余弦的曲线,数学与三角函数,平方根函数 SQRT(number)返回number的平方根（Square root）例子：SQRT(4)=2 说明：如果参数 Number 为负值，函数 SQRT 返回错误值 #NUM!,数学与三角函数,随机函数RAND()返回大于等于 0 及小于 1 的均匀分布随机（Random）数，每次计算工作表时都将返回一个新的数值。若要生成 a 与 b 之间的随机实数，请使用： RAND()*(b-a)+a例子：产生1-20之间的整数：INT(RAND()*20)+1,数学与三角函数,自然对数函数LN(b)：返回一个数的自然对数（Logarithm），自然对数以常数项 e (2.71828182845904) 为底对数函数LOG(b ,a)：返回以a为底，b的对数，即logab常用对数函数LOG10(b)：返回以10为底，b的对数，即log10b,数学与三角函数,指数函数EXP(n)： Exponent返回 e 的 n 次幂,数学与三角函数,求幂函数POWER(a, b)：返回a的b次幂（Power），等价于ab例子：POWER(2, 3)=8POWER(2, 10) = 1024POWER(2, 20) = 1,048,576POWER(2, 30) = 1,073,741,824,统计函数,统计函数中位数函数MEDIANMEDIAN(number1,number2,.) 返回给定数值集合的中值（Median），如果参加统计的数据的个数为偶数（Even），返回位于中间的两个数的平均值。例子：MEDIAN(1, 5, 2) = 2MEDIAN(2, 1, 4, 3) = 2.5 （2和3的平均数）说明：参数应为数字，或者是包含数字的名称、数组或引用；如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。,统计函数,众数函数MODE众数（Mode）：一组中出现最频繁的数或数列。MODE(number1,number2,.) 返回在某一数组或数据区域中出现频率最多的数值 如果数据集合中不含有重复的数据，则 MODE 数返回错误值 #N/A 例子：MODE(2, 5, 6, 5, 8, 6, 11, 6, 8) = 6MODE(2, 6, 5, 6, 2, 6, 5, 5) = 6,统计函数,众数、中位数和平均数:在一组数值中，众数（Mode）是出现频率最高的数值，而中位数（Median）是位于中间的值，平均数（Average）是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。假设数据分布在三个区域中，其中一半分布在一个较小数值区中，另外一半分布在两个较大数值区中。函数 AVERAGE 和函数 MEDIAN 可能会返回位于数据点稀疏处的中间值；而函数 MODE 则会返回位于数据点密集处的较小值。,统计函数,FREQUENCY(data_array,bins_array)频数函数FREQUENCY以一列垂直数组返回某个区域中数据的频率（Frequency）分布data_array 为一数组或对一组数值的引用，用来计算频率。bins_array 为间隔的数组或对间隔的引用，该间隔用于对 data_array 中的数值进行分组。 bins_array要求从小到大存放，每个数据表示一个分段点，统计的结果包含该数据以及小于该数据的个数计算结果是一个数组，用“Ctrl+Shift+Enter”输入例子：统计“职工情况表”不同年龄段的人数。,统计函数,标准差函数STDEV标准差（Standard Deviation）：反映一组数据于数据的平均值的离散程度。标准差函数STDEV（参数1,参数2,）功能：返回自变量表列中数据的标准偏差。自变量为数值型。-标准差反映数据相对于均值的离散程度。,统计函数,方差：（加平方是为了消除+-号的影响）标准差：（便于与平均值对比）如果标准差比较小，说明数据于平均值的离散程度就比较小，平均值能够反映数据的均值，具有统计意义。如果标准差比较大，说明数据与平均值的离散程度就比较大（数据中含有非常大或非常小的数），平均值在一定程度上失去数据“均值”的意义。,统计函数,FORECAST(x,known_ys,known_xs)已知线型趋势值函数FORECAST的数值为已有的 x 值和 y 值，再利用线性回归对新值进行预测（Forecast）x 为需要进行预测的数据点known_ys 为因变量数组或数据区域known_xs 为自变量数组或数据区域,逻辑函数,逻辑函数逻辑与函数ANDAND(logical1, logical2, .) 所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。说明：参数必须是逻辑值 TRUE 或 FALSE, 或者包含逻辑值的数组或引用。 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 如果指定的单元格区域内包括非逻辑值，则 AND 将返回错误值 #VALUE!。,逻辑函数,逻辑或函数OR (logical1, logical2, .)任何一个参数逻辑值为 TRUE，即返回 TRUE；所有的参数均为FALSE，才返回FALSE。 逻辑非函数NOTNOT(logical)函数值取参数的反值。参数值为TRUE则函数值为FALSE；反之，参数值为FALSE则函数值为TRUE。例子：工资补助表,数据库函数,数据库函数数据库函数的格式：DatabaseFunction(database, field, criteria)其中：database是数据清单区field用于用于指明要统计的列，field可以是：字段（Field）名所在的单元格地址；带英文双引号的“字段名”；字段在数据清单中的位置，“1”表示第1列，等等。Criteria：条件区，约定与高级筛选一样。,数据库函数,条件区:条件区的第一行是字段名或者是数据清单中字段名所在单元格地址的引用，或为空白（公式作为筛选条件）；从第二行开始设置筛选条件：筛选条件出现在不同的行表示“或”关系，筛选条件出现在相同行表示“与”关系；在条件中允许出现“*”代表任意一个字符串，“?”代表任意一个字符；,数据库函数,求和函数DSUM(database, field, criteria)功能：对database区的field字段求满足条件区条件的数据的累加和说明：DSUM和SUM不同，SUM求任意数据的累加和，DSUM求数据清单中一列数据中满足条件的数据累加和；DSUM包含了SUMIF的功能。SUMIF只能对给定的一个条件求数据的累加和，而DSUM可以对给定的多个条件求指定的列数据的累加和。,数据库函数,其他数据库函数平均值函数DAVERAGE最大值函数DMAX最小值函数DMIN统计数值型数据的个数DCOUNT省略field字段，对数据清单区求满足条件的个数统计个数DCOUNTA省略field字段，对数据清单区求满足条件的个数,财务函数,五、财务函数及应用1偿还函数PMT(rate,nper,pv,fv,type) 。,财务函数,1偿还函数PMT(rate,nper,pv,fv,type) 。功能：返回在固定利率下，投资或贷款的等额分期偿还额。PMT函数自变量的解释：rate：每期的利率nper：年金付款期间的总次数pv：现值,未来各期年金现在价值的总和fv：预期值,最后一次付款完成后，所能获得的现金总额，默认为0type：类型值.表示何时付款到期。0（默认）或1； 0-期末；1-期初*采用按月等额均还贷款的方式。,财务函数,1、贷款偿还：假设想买一台分期付款的汽车，总价值200，000（现值）元，年利率为9%（9/12）%=0.75%月息），3年付清，一个月付一次，每次应付多少金额？解：=PMT（0.75%,36,200000）结果：-6359.946532,财务函数,例2、 要15年存款1500000 (预期值) ，年利率为5%，问：每月需存多少钱？解：=PMT(0.05/12, 15*12, 0, 1500000)或： =PMT(0.05/12, 15*12, , 1500000)结果为：-$5,611.90,财务函数,2可贷款函数PV(rate, nper, pmt,fv,type)。3未来值函数FV(rate,nper,pmt,pv,type) 。4.NPER 返回投资期间的数量5.RATE 返回年金的各期利率6.EFFECT 返回实际年利率六、日期函数及其应用100,日期函数,六、日期函数及其应用1001.DAY 2.MONTH 3.NOW 4.TIME 5.TODAY 6.WEEKDAY 7.YEAR,查找和引用函数,七、查找和引用函数及应用1按列查找函数VLOOKUP（查找值，数据区，列标，匹配类型）。功能： VLOOKUP函数在第二个自变量给定的数据区域的最左列中查找第一个自变量给定的值，然后将第三个自变量指定列值返回到单元中。匹配的类型：若省略=true=1,即返回近似值（小于查找值的最大值,数据区域的第一列必须排序)若为0=false,要求精确匹配值。,查找和引用函数,2按行查找函数HLOOKUP（查找值，数据区，行标，匹配类型）。,文本函数,八、 文本函数及其应用1.EXACT 检查两个文本值是否相同2.FIND 在一个文本值中查找另一个文本值（区分大小写）3.FIXED 将数字格式设置为具有固定小数位数4.取子串函数MID、LEFTRIGHT返回文本值中最左边右边的字符5.LEN 返回文本字符串中的字符个数,文本函数,6.LOWERUPPER将文本转换为小写大写形式7.REPLACE 替换文本中的字符8.REPT 按照给定的次数重复显示文本9.SEARCH 在一个文本值中查找另一个文本值（不区分大小写）10.TRIM 从文本删除空格11.VALUE 将文本参数转换为数字,第7章 函数与应用,小结预习第8章作业：教材P 106/习题教材P160-166/实验1-实验5,第8章 数据分析,六课时 本章重点： 用假设方法求解 线性回归分析 规划求解,第8章 数据分析,一、 用假设方法求解目的：研究当变量在一定范围内变化时，与之相关的的公式的结果。1、使用“单变量求解”命令求特定解108通过调整一个单元格中的值，从而求得指定单元格中的特定值的方法*目标单元格要包含公式。,第8章 数据分析,2、运用模拟运算表作模拟分析单变量输入“模拟运算表”:对一个变量输入不同的值，模拟它对一个或多个公式影响的结果双变量输入“模拟运算表”对两个变量输入不同的值，模拟它对一个公式的影响结果,第8章 数据分析,3、方案管理器 假设多种条件，依据一定的计算得到不同的方案，以供决策。创建方案显示方案编辑方案创建方案总结报告保护方案:可以隐藏或防止修改 需要保护工作表删除方案总结方案,第8章 数据分析,例:设有三种备选方案，使用方案管理器生成方案及方案摘要，从中选出最优惠的方案。l 工商银行：贷款额300000元，付款期数120期（每月1期，共10年），年利率5.75%.l 建设银行：贷款额300000元，付款期数180期（每月1期，共15年），年利率6.05%.l 中国银行：贷款额300000元，付款期数240期（每月1期，共20年），年利率6.3%.,第8章 数据分析,二、线性回归分析114在大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间的回归关系函数表达式（称回归方程式）。当研究的因果关系只涉及因变量和一个自变量时，叫做一元回归分析；当研究的因果关系涉及因变量和两个或两个以上自变量时，叫做多元回归分析。,第8章 数据分析,例1 对销售额进行多元回归分析预测，数据见图。,第8章 数据分析,解：本题可用二元线性回归分析来求解：设定变量：Y=销售额，X1=电视广告费用，X2=报纸广告费用方程为：Y=a1X1+a2X2+b通过线性回归分析确定a1,a2,b的值，从而确定方程。,第8章 数据分析,1操作方法与步骤(1) 建立数据模型(2)工具加载宏/“分析工具库”复选框(3)工具数据分析/“回归”(4）设置“回归”对话框中参数:“Y值输入区”，“X值输入区”，“输出区域”(5)根据结果得出方程,第8章 数据分析,三、 规划求解“规划”是数学概念，它是指运用微积分和线性代数的方法，在满足一组约束条件的情况下，求出一个多变量函数极值的模型。数学规划是运筹学的一个分支，主要包括线性规划、非线性规划、动态规划和整数规划等。规划求解可求得工作表上某个单元格（目标单元格）中公式的最优值。（对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果）,第8章 数据分析,意义：规划求解用于在生产或工作中的一些问题或项目在受多个因素的制约的前提下如何获得最佳的结果。如获得最大的利润、最小的成本等。注意事项：在创建模型过程中，对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。添加规划求解命令:工具/加载宏 /规划求解,第8章 数据分析,1、求解线性规划问题118例：工厂生产3种玩具：狗、娃娃、大熊猫；3种玩具的净利润（元）：90、75、503种玩具各用机工（小时）：3、4、53种玩具各用手工（小时）：4、3、2,第8章 数据分析,工厂极限生产能力：机工：= 32件求满足以上条件时，如何安排各玩具的生产数量以获得最大利润。,第8章 数据分析,解：1 建立数据表如图:2 进行规划求解在规划求解参数设置中输入：目标单元格地址可变单元格地址约束条件（如有多个逐一添加）3单击求解结果：如生产20只玩具狗、45个芭比娃娃、32只大熊猫，可以得到最大利润；,第8章 数据分析,求解的结果可形成： 运算结果报告 敏感性报告 极限值报告,第8章 数据分析,*关干规划求解方法的若干选项在调用规划求解工具时，单击“选项”，可以打开“规划求解选项”对话框：1)最长运算时间 在此设定求解过程的时间。可输入的最大值为32767(秒)，默认值100 (秒)可以满足大多数小型规划的求解要求。,第8章 数据分析,2)迭代次数 在此设定求解过程中迭代运算的次数，限制求解过程的时间。可输入的最大值为32767默认值100次可满足大多数小型规划的求解要求。3)精度 在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值;上下限。精度值必须表示为小数 (0到1之间)，输入数字的小数位越多，精度越高。4)允许误差 在此输入满足整数约束条件并可被接受的目标单元格求解结果与真实的最佳结果间的百分偏差。这个选项只应用于具有整数约束条件的问题。设置的允许误差值越大，求解过程就越快。,第8章 数据分析,5)收敛度 在此输入收敛度数值，当最近五次迭代后目标单元格中数值的变化小于收敛度框叫置的数值时，椒划求解停止运行。收敛度只应用于非线性规划求解问题，并且必须表示夕小数(0到1之间)。设置的数值越小，收敛度就越高。例如，00叨1表示比001更小的个对差别。收敛度越小，规划求解得到结果所需的时间就越长。6)采用线性模型 当模型中的所有关系都是线性的，并且希望解决线性优化问题时，选中此复选框可加速求解进程。7)显示迭代结果 如果选中此复选框，每进行一次迭代后都将中断规划求解，并显示当前的迭代结果。8)自动按比例缩放 如果选中此复选框，当输入和输出值量级差别很大时，可自动按比例缩放数值。例如，基于百万美元的投资将利润百分比最大化。,第8章 数据分析,9)假定非负 如果选中此复选框，则对于在“添加约束”对话框的“约束值”框中没有设置下限的所有可变单元格，假定其下限为零。 10)估计 指定在每个一维搜索中用来得到基本变量初始估计值的逼近方案。 正切函数:使用正切向量线性外推。 二次方程:用二次方程外推法，提高非线性规划问题的计算精度。,第8章 数据分析,11)导数 指定用于估计目标函数和约束函数偏导数的差分方案。 向前差分:用于大多数约束条件数值变化相对缓慢的问题。 中心差分:用于约束条件变化迅速，特别是接近限定值的问题。虽然此选项要求更多的计算，但在规划求解不能返回有效解时也许会有帮助。,第8章 数据分析,12)搜索 指定每次的迭代算法，以确定搜索方向。 牛顿法:用牛顿法迭代需要的内存比共钝法多，但所需的迭代次数少。 共轭法:比牛顿法需要的内存少，但要达到指定精度需要较多次的迭代运算。当间题较大和内存有限，或步进迭代进程缓慢时，可用此选项。 13)装入模型 显示装入模型对话框，输入对所要加载的模型的引用。 14)保存模型 显示保存模型对话框，在其中可指定保存模型的位置。只有需要在工作表上保存多个漠型时，才使用此命令。第一个模型会自动保存。,第8章 数据分析,2 、 求解方程组的解,第8章 数据分析,8.4 移动平均移动平均法是根据时间序列资料，逐项推移，依次计算移动平均，来反映现象的长期趋势。特别是现象的变量值受周期变动和不规则变动的影响，起伏较大，不能明显地反映现象的变动趋势时，运用移动平均法，消除这些因素的影响，进行动态数据的修匀，以利于进行长期趋势的分析和预测。,第8章 数据分析,简单移动平均的计算公式：设xi为时间序列中的某时间点的观测值，其样本数为N；每次移动地求算术平均值所采用的观测值的个数为n（n的取值范围：2n0为正相关，r=0.8，高度相关；0.5=r0.8，视为中度相关；0.3=r0.5，视为低度相关；|r|0.3时，视为不相关。,第8章 数据分析,例根据下图的数据，对家庭月消费支出与家庭月收入的数据进行相关分析。,第8章 数据分析,操作步骤：建立数据模型；工具数据分析/相关系数设置“相关系数”对话框内容,第8章 数据分析,“确定”，输出结果如图所示。,分析结果表明：相关系数r=0.979747601，表示家庭月消费支出与家庭月收入之间存在高度正相关关系。,第8章 数据分析,第8章 数据分析,分析结果表明：相关系数r=0.979747601，表示家庭月消费支出与家庭月收入之间存在高度正相关关系。操作步骤:（1）建立数据模型：将数据输入到工作表区域A1:E16中，如图8.38所示；,第8章 数据分析,8.7 方差分析方差分析(Analysis of Variance, 缩写为ANOVA)是数理统计学中常用的数据处理方法之一，是经济和科学研究中分析试验数据的一种有效的工具。运用数理的方法对数据