主讲：刘主讲：刘 会会 齐齐liuhq-8888163.com 第2讲 Excel财务应用基础(二)第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院2第 页第第2讲讲 Excel财务应用基础财务应用基础(二二)一、excel公式二、excel函数三、数据分析工具第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院3第 页一、一、excel公式公式1、公式概念2、运算符3、公式编辑4、输入数组公式5、使用名称建立公式6、公式的显示与隐藏7、公式审核及出错检查第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院4第 页1、公式概念、公式概念qExcel的公式由运算符、数值、字符串、变量和函数组成。q公式必须以等号公式必须以等号“=”=”开头开头，在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。q输入：单元格输入公式或者编辑栏输入公式输入：单元格输入公式或者编辑栏输入公式 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院5第 页2、运算符、运算符在公式中可以使用运算符Excel中的运算符如下表续类别运算符运算符运算功能运算功能优先先级引用引用:区域运算符，用于引用区域运算符，用于引用单元格区域。例如：元格区域。例如：A1:B2，引用了，引用了A1、A2、B1、B2四个四个单元格；元格；B:B 引用整个引用整个B列；列；3:3引用整个第引用整个第3行。行。1引用引用,联合运算符，用于将多个引用合并。例如：合运算符，用于将多个引用合并。例如： 公式公式“=SUM(A1:B2, B2:C3)”的功能是将的功能是将单元格元格A1、A2、B1、B2以及以及B2、B3、C2、C3中的数据加中的数据加总，其中，其中B2单元格被加元格被加总两次。两次。2引用引用空格空格交叉运算符，用于引用两个交叉运算符，用于引用两个单元格区域的重叠部分。元格区域的重叠部分。例如，例如，A1:B2 B2:C3的的结果是果是B2。3算算术( )括号括号4算算术-负号号5算算术%百分号百分号6算算术乘方乘方7算算术*和和/乘法和除法乘法和除法8算算术+和和加法和减法加法和减法9文本文本&文本文本连接接10逻辑=、=、等于、小于、大于、等于、小于、大于、小于等于、大于等于、不等于小于等于、大于等于、不等于11第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院7第 页3、公式编辑、公式编辑修改公式复制或移动公式单击公式编辑栏 选择单元格，按【F2】功能键 利用工具栏上的复制、剪切、粘贴按钮利用【Ctrl+C】、【Ctrl+X】、【Ctrl+V】组合键操作利用鼠标拖动填充柄复制复制公式的过程中需要注意公式中对单元单元格具有不同的引用方式格具有不同的引用方式 注意选择性粘贴选择性粘贴第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院8第 页4、输入数组公式、输入数组公式组合键编辑数组公式删除数组公式数组公式的应用选取输入公式单元格或单元格区域输入计算公式按 【Ctrl+Shift+Enter】组合键参见演示文件适用于有规律性批量数据输入第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院9第 页5、使用名称建立公式、使用名称建立公式利用名称框定义名称利用菜单定义名称插入/名称/定义第一个字符必须是字母、下划线、或汉字不能与单元格引用相同不区分大小写直接输入公式利用粘贴名称方法q命名的方法q单元格名称的命名规定q使用名称建立公式q为已有的公式套用名称参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院10第 页6、公式的显示与隐藏、公式的显示与隐藏使用菜单命令 工具/选项/视图 利用快捷键切换 按【Ctrl+】组合键 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院11第 页7、公式审核及出错检查、公式审核及出错检查（1）追踪引用单元格q工具/公式审核/追踪引用单元格 q工具/公式审核/取消所有追踪箭头 （2）追踪从属单元格q工具/公式审核/追踪从属单元格q工具/公式审核/取消所有追踪箭头 （3）公式审核工具栏q工具/公式审核/显示“公式审核”工具栏 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院12第 页二、函数二、函数1.函数的简介2.常用工作表函数3.逻辑函数4.数学和三角函数5.日期函数6.查找函数7.财务函数第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院13第 页1、函数简介、函数简介函数的概念函数是能够完成特定功能的程序。在Excel中，它是系统预定义的一些公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算,然后把计算的结果存放在某个单元格中。 在大多数情况下，函数的计算结果是数值。当然，它也可以返回文本、引用、逻辑值、数组或工作表的信息 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院14第 页Excel函数分类：分 类功 能 简 介数据库函数对数据清单中的数据进行分析、查找、计算等日期与时间对日期和时间进行计算、设置及格式化处理工程函数用于工程数据分析与处理信息函数对单元格或公式中数据类型进行判定财务函数进行财务分析及财务数据的计算逻辑函数进行逻辑判定、条件检查统计函数对工作表数据进行统计、分析查找函数查找特定的数据或引用公式中的特定信息文本函数对公式、单格中的字符、文本进行格式化或运算数学函数进行数学计算等外部函数进行外部函数调用及数据库的链接查询等功能自定义函数用户用vba编写，用于完成特定功能的函数第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院15第 页函数的语法：函数名(参数1, 参数2, 参数3,) 注意： q函数名与其后的括号“（”之间不能有空格。q当有多个参数时，参数之间要用逗号“，”分隔。q参数部分总长度不能超过1024个字符。q参数可以是数值、文本、逻辑值、单元格地址或单元格区域地址，也可以是各种表达式或函数。q函数中的“，”、“”等都是半角字符，而非全角的中文字符。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院16第 页函数调用 单一函数的调用方法q直接输入函数q利用菜单命令或工具按钮调用函数q函数调用向导嵌套函数的输入方法Excel的帮助系统=IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套嵌套函数函数第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院17第 页2、常用函数、常用函数（1）条件函数IF （2）求和函数 （3）平均值函数（4）计数函数 （5）求最大值、最小值函数第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院18第 页（1）条件函数）条件函数IFq格式：IF(条件, 表达式1, 表达式2)。 q功能：当条件成立时，计算出表达式1的值；当条件不成立时，计算出表达式2的值qIF函数的嵌套调用参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院19第 页（2）求和函数）求和函数q无条件求和SUM函数 q条件求和SUMIF函数 qSUMPRODUCT函数 续第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院20第 页1 1）求和函数）求和函数 自动求和按钮图示工作表中的所有汇总数据可用自动求和按钮计算出来！第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院21第 页2）SUM函数函数q格式：SUM(x1, x2,x30)， x1，x2，x30是需要求和的参数 ，可以是数据或单元格区域q功能：无条件求和SUM函数，计算所选取的单元格区域中所有数值的和。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院22第 页3）条件求和函数）条件求和函数SUMIFq格式：SUMIF (range, criteria, sum_range) lrange是用于条件判断的单元格区域，lcriteria条件，其形式可以为数字、表达式或文本lsum_range是需求和的实际单元格。只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。若省略sum_range，则直接对range中的单元格求和。 q功能 ：对range单元格区域中的数据进行Criteria条件检查，然后对满足条件行的sum_range同行进行求和。续 某商场的销售记录如图所示。如果统计各类商品的总销售额。彩电的销售总额：=SUMIF(C$3:C$13,彩电,F$3:F$13)第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院24第 页4）SUMPRODUCT函数函数q格式：SUMPRODUCT（ARRAY1，ARRAY2，.） 其中：array为1-30个数组。 q功能：在给定的几组数组中将数组间对应的元素相乘，并返回乘积之和。q注意：参数数组必须有相同的维数，否则出错。求和函数使用演示第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院25第 页（3）平均值函数）平均值函数q格式：AVERAGE(n1，n2，n30) AVERAGEA(n1，n2，n30) 其中，n1，n2，n30是要计算平均值的参数，该函数最多允许有30个参数。参数可以是数字，或者是涉及数字的名称、数组或引用 q功能：两函数都是求参数的平均值。AverAge不对文本和逻辑值类数据进行计算；AverageA要对文本和逻辑函数进行平均值计算：文本被视为0，逻辑值true被视为1，false被视为0，空文本（）也作为0计算 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院26第 页AVERAGE函数与AVERAGEA函数的区别 ：第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院27第 页（4）计数函数）计数函数q格式：lCOUNT(v1, v2, )lCOUNTA(v1, v2, )lCOUNTBLANK (range)lCOUNTIF (range, criteria)q功能：lCount 统计数字的个数lCountA统计数字和文本的个数lCountBlank统计空白单元格的个数lCountif统计满足条件Criteria的单元格个数第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院28第 页(5)求最大值、最小值函数求最大值、最小值函数q格式：lMAX(number1,number2,.)lMIN(number1,number2,.) 其中：number1, number2, .是要从中找出最大值或最小值的 1 到 30 个数字参数，也可以是单元格或单元格区域的引用。q功能：MAX函数返回一组值中的最大值，MIN函数返回一组值中的最小值 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院29第 页3、 逻辑函数逻辑函数1）比较运算q比较运算就是人们常说的比较式，又称关系运算。比较运算只有两种不同的结果，要么“正确”，要么“错误”，不可能有第三种结果。2）逻辑运算qAND(l1, l2, )qOR(l1, l2, )qNOT(logical)qTRUE( )qFALSE( )q其中， l1, l2是关系式或逻辑值。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院30第 页 某单位有3000名职工，要按其工资缴纳个人收入所得税，税率与工资的关系如表所示。该单位的职工表如下所示，用IF函数求出各职工的税率！工工 资税税 率率3000以上以上税率税率=30%25003000税率税率=25%20002500税率税率=20%15002000税率税率=15%12001500税率税率=10%10001200税率税率=8%8001000税率税率=5%800以下以下税率税率=0在E4中输入编辑栏中的公式！第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院32第 页4、 数学和三角函数数学和三角函数Excel提供了许多数学和三角函数，它们能够完成大多数数学和三角运算，这些函数可以在公式中直接引用，然后将公式的计算结果返回到输入公式的单元格中。常见的数学函数：MOD 、TRUNC 、ABS 、SQRT 、SIN 、ASIN(n1)，ACOS(n1)，ATAN(n2)、EXP(n)、POWER(x, n)、LN(n)、FACT(n)、LOG(n, base)、MINVERSE(array)、MMULT(array1, array2) 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院33第 页5、日期及时间函数、日期及时间函数 Excel处理日期的方式:Excel将日期存储为一系列连续的序列数，将时间存储为小数。qNOW( ) ：返回当前系统日期和时间qTODAY( )：返回当前系统日期qYEAR(serial_number)：返回日期的年份值qMONTH(serial_number)：返回日期的月份值qDAY(serial_number)：返回月份中第几天的值qWEEKDAY(serial_number, return_type)：返回一周中第几天的值，结果1-7之间的数字，周日是第1天，周一是第2天，。qHOUR（serial_number）：返回小时数值qINUTE（serial_number）：返回分钟数值qSECOND(serial_number)：返回秒数值qDATE(year, month, day)：返回特定日期序列qDATEDIF(start_date, end_date, unit) :返回天数qNETWORKDAYS(start_date, end_date, holidays):返回完整的工作日数据 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院34第 页6、查找函数、查找函数qLOOKUP(value, r1, r2)qHLOOKUP(value, table, n, range_lookup)qVLOOKUP(lookup_value, table_array,col_index_num, range_lookup)qMATCH(lookup_value,lookup_array,match_type)参见演示文件MATCH( lookup_value, lookup_array, match_type)Lookup_value:为需要在数据表中查找的数值，可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。Lookup_array:可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。Match_type：为数字 -1、0 或 1。Match-type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE。如果 match_type 为 0，函数 MATCH 查找等于 lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。如果 match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列：TRUE、FALSE、Z-A、.、2、1、0、-1、-2、.，等等。如果省略 match_type，则假设为 1。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院36第 页7、引用函数、引用函数（1）Index（array, row_num, column_num）返回数组中指定单元格的数值（2） ADDRESS(row_num,col_num,abs_num,a1, sheet_text)按照给定的行列建立文本类型的单元格地址（3）INDIRECT（ref_text, a1） INDIRECT函数返回由文字串指定的引用。 INDIRECT ：返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，请使用函数 INDIRECT。语法：INDIRECT(ref_text,a1)Ref_text：为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值 #REF!。如果 ref_text 是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数 INDIRECT 返回错误值 #REF!。A1：为一逻辑值，指明包含在单元格 ref_text 中的引用的类型。如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院38第 页8、矩阵函数、矩阵函数（1）TRANSPOSE函数：求矩阵的转置矩阵。（2）MINVERSE函数：返回矩阵的逆矩阵。（3）MMULT函数：返回两数组的矩阵乘积。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院39第 页9、财务函数、财务函数 Excel提供了许多有关财务、投资、偿还、利息及折旧方面的函数，在工作表中运用这些函数可以较松地完成相关的财务运算，或者对其他财务管理软件的运算输出数据进行验证。l折旧函数 l投资函数 l计算偿还率的函数 （1）折旧函数 Excel折旧函数有DB、DDB、SLN、 SYD 及VDB。运用这5个折旧函数可以确定指定时期内资产的折旧值。（2）投资函数 Excel投资分析方面的函数，这些函数使用的参数大致相同，意义相近 ，如下表所示。qPMT函数返回的支付款项包括本金和利息，qPV函数可以计算投资的现值。qFV函数计算投资在将来某个日期的价值，它可以计算出投资的一次性偿还金额，也可以计算出一系列数额相等的分期偿还金额。 qXNPV函数计算一组现金流的净现值，这些现金流不一定定期发生 qNPV(rate, value1, value2, .)qIPMT(rate, per, nper, pv, fv, type)qPPMT(rate, per, nper, pv, fv, type) qNPER(rate, PMT, pv, fv, type)（3）计算偿还率的函数 qRATE函数用于计算投资的各期利率。可以计算连续分期等额投资的偿还率，也可以计算一次性偿还的投资利率 qIRR函数计算由数值代表的一组现金流的内部收益率。内部收益率是指投资偿还的固有率，它是引起投资的净现值等于零的比率 第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院41第 页10、数据库函数、数据库函数 数据库函数用于对存储在数据库或数据清单中的数据进行统计分析。 常见DAVERAGE、DSUM、DCOUNT、DMAX、DMIN、DGET等函数第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院42第 页三、数据分析工具三、数据分析工具（一）模拟运算（二）单变量求解（三）规划求解（四）方案管理（五）数据分析工具库第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院43第 页（一）模拟运算（一）模拟运算q模拟运算是指通过模拟运算测试由一组替换值代替公式中的变量时对公式运算结果的影响。qExcel主要可以构建两种模拟运算：单变量模拟运算和双变量模拟运算。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院44第 页1、单变量模拟运算、单变量模拟运算（1）单变量模拟运算含义（2）单变量模拟运算步骤第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院45第 页（1）单变量模拟运算含义）单变量模拟运算含义 用于测试一个输入变量的不同变化值对公式运算结果的影响。即使用同一公式对单元格区域依次进行求解，再将这些结果依次输入到相应的单元格中。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院46第 页（2）单变量模拟运算步骤）单变量模拟运算步骤续1.设计模拟运算表结构；2.在第一个运算单元格中输入运算公式；3.选取包括公式和需要进行模拟运算的单元格区域在内的单元格区域；4.使用excel模拟运算功能（数据/模拟运算表），设置弹出的对话框即可。计算不同利率下的月还款额：参见演示文件Pmt函数，基于固定利率及等额分期付款方式，返回贷款的每期付款额。格式：PMT(rate,nper,pv,fv,type)Rate：贷款利率；Nper：该项贷款的付款总数；Pv：现值，或一系列未来付款的当前值的累积和，也称为本金；Fv：为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零；Type：用以指定各期的付款时间是在期初还是期末，Type值0 或省略表示期末，1表示期初 说明：应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。 如果要计算贷款期间的支付总额，请用 PMT 返回值乘以 nper。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院49第 页2、双变量模拟运算、双变量模拟运算（1）双变量模拟运算含义（2）双变量模拟运算步骤第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院50第 页（1）双变量模拟运算含义）双变量模拟运算含义 用于分析两个变量的几组不同数值变化对公式计算结果的影响。即在公式中使用了两个变量，这两个变量在公式中可使用两个空白单元格格表示，分别被引用到行和列单元格。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院51第 页（2）双变量模拟运算步骤）双变量模拟运算步骤1.设计模拟运算表结构2.在行列标题交叉的单元格中输入运算公式3.选取包括公式和需要进行模拟运算的单元格区域在内的单元格区域4.使用excel模拟运算功能（数据/模拟运算表），设置弹出的对话框即可参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院52第 页（二）单变量求解（二）单变量求解1、单变量求解含义2、单变量求解步骤第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院53第 页1、单变量求解含义单变量求解含义单变量求解是指求解只有一个变量的方程的根。所求解的方程可以是线性方程，也可以是非线性方程。模拟运算功能是在已知变量的情况下，对目标单元格进行求值。如果知道要从公式获取结果的输入值，需要计算可变单元格的取值，就需要使用单变量求解功能。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院54第 页2、单变量求解步骤、单变量求解步骤1.设计模拟运算表结构（样式、字体、标题等）2.在目标结果单元格中输入运算公式3.使用excel单变量求解功能（工具/单变量求解），设置弹出的对话框即可。思考：思考：某人在银行存入50000元，存款期为5年，为了在5年末从银行提取出65000元，存款年利率是多少？定义表格结构目标单元格定义公式使用单变量求解功能，设置目标值和可变单元格。求解结果。参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院56第 页思考题： 某人先期存入银行10000元，现希望10年末能够获得200000元，每月应该存入等额资金多少元？10期年利率为5.5%。 提示：函数FV的使用。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院57第 页（三）规划求解（三）规划求解1、规划求解含义2、规划求解工具的装载3、规划求解最优化问题4、规划求解方程组问题第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院58第 页1、规划求解含义、规划求解含义q规划求解是excel提供的一个非常有用的工具，不仅可以解决运筹优化问题，还可以解决线性方程组和非线性方程组的问题。q当企业面临决策类的问题需要解决时，可以使用规划求解功能，从而设置规划求解，得到最佳结果。q使用规划求解功能可以计算某个单元格公式的最佳值。规划求解将与目标单元格公式相关联的一组单元格中的数值进行调整，最终在目标单元格中计算出期望的结果。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院59第 页2、规划求解工具的装载、规划求解工具的装载规划求解工具是excel的一个可选安装模块，通常使用“完全/定制安装”后才可以加载到系统中。使用工具/加载宏命令，在弹出的对话框中选定规划求解，单击确定即可加载该工具。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院60第 页3、规划求解最优化问题、规划求解最优化问题 以下面例题讲解规划求解工具的使用。 某企业生产A、B两种产品。每生产一盒A产品，需要机器运转1小时，需要耗费原料1.6克，并可以获得50元毛利。每生产一盒B产品，需要机器运转1.5小时，需要耗费原料1.8克，并可以获得65元毛利。现在企业领导决定，每月使用800克原料，并分配580小时机器运转时间来生产这两种产品，那末企业如何分配A、B两种产品的生产才能获得最高的利润？第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院61第 页步骤：（1）建立优化求解模型（2）设计工作表结构（3）设置公式（4）规划求解设A产品生产x盒，B产品生产y盒，则有：目标函数：max销售利润=50x + 65y约束条件：1x + 1.5y 580 1.6x + 1.8y 800x0,y0.且为整数安排条件区和结果区表的格式设置启用规划求解功能，工具/规划求解在弹出对话框中设置目标函数和约束条件设置。修改约束条件：q进行规划求解后，如果需要重新设置约束条件，即修改规划求解的限制条件，形成其他方案。例如，上例中企业领导修改了原来的方案，将每月原料配额由800克增加到1000克，机器运转时间由580小时增加到600小时。此时又怎样安排生产？q如果企业领导决定每月生产150盒A产品，利用剩余的原料和时间生产B产品，此时又怎样安排生产？分析报告的建立：q规划求解除了显示结果外，还可以产生分析报告。可以产生运算结果报告、敏感性报告和极限值报告。3种报告系统会自动添加一张新的工作表。参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院64第 页4、规划求解方程组问题、规划求解方程组问题 有些财务管理问题可以利用线性或非线性方程组建立定量分析模型，所以涉及对这些方程进行求解，利用规划求解可以方便求解方程组。 例如求解如下方程组的解。参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院65第 页（四）方案分析（四）方案分析当企业需要比较分析若干个方案时，可以利用方案管理功能，添加不同的方案内容，生成相应的报表，进而比较分析合适的方案，进行选择。企业利用规划求解计算出结果后，可以将这些结果保存为方案，作为决策的依据。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院66第 页方案管理的内容：方案管理的内容：菜单：工具/方案1、建立方案2、显示方案3、修改、删除和增加方案4、合并方案5、建立方案摘要报告 打开方案管理对话框只是显示当前工作表中的方案，其他工作表的方案则不显示。实际工作中，为了便于方案的管理与使用，可以通过“合并方案”功能直接复制其他工作表中的方案。也可以将其他工作簿中的方案合并。！方案可以来源不同的工作簿合并后的结果。 如果当前工作表中建立了多个方案，为了更好地对多个方案进行比较分析，可以利用建立“方案摘要”的方法将所有方案集中在一个工作表中显示出来。参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院69第 页（五）数据分析工具库（五）数据分析工具库1.数据分析工具库的含义2.数据分析工具库的装载3.数据分析工具库的使用第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院70第 页1、数据分析工具库的含义、数据分析工具库的含义 Excel还提供了一组“数据分析工具库”的数据分析工具，利用该工具库可以更好对数据进行分析与处理。数据分析工具库提供了不同的分析工具类型，在进行数据分析操作时，可以选择合适的工具进行，从而达到更好地说明效果。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院71第 页2、数据分析工具库的装载、数据分析工具库的装载数据分析工具库是excel的一个可选安装模块，通常使用“完全/定制安装”后才可以加载到系统中。使用工具/加载宏命令，在弹出的对话框中选定分析工具库，单击确定即可加载该工具。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院72第 页3、数据分析工具库的使用、数据分析工具库的使用（1）方差分析工具（2）相关系数（3）回归分析（4）抽样分析工具（5）直方图分析工具（6）移动平均法（7）指数平滑法第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院73第 页（1）方差分析工具）方差分析工具 方差分析工具提供了几种方差分析工具。具体使用哪一种工具则根据因素的个数以及待检验样本总体中所含样本的个数而定。 单因素方差分析工具可对两个或更多样本的数据执行简单的方差分析。此分析可提供一种假设检验，该假设的内容是：每个样本都取自相同基础概率分布，而不是对所有样本来说基础概率分布都不相同。 双因素方差分析工具可用于当数据按照二维进行分类时的情况。例如，在测量植物高度的实验中，植物可能使用不同品牌的化肥（例如 A、B 和 C），并且也可能放在不同温度的环境中（例如高和低）。对于这 6 对可能的组合 化肥，温度，我们有相同数量的植物高度观察值。使用此方差分析工具，我们可检验.第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院74第 页 统计学:单因素和双因素方差分析中可以利用F统计量或者P值进行检验。方法如下：如果F的值大于F的临界值，则拒绝原假设。如果P值小于显著性水平，则拒绝原假设。 例题1：为检验3家工厂生产的机器混合一批原料所需要的平均时间是否相同，某公司得到了关于混合原料所需时间的数据，利用这些数据检验3家工厂混合生产一批原料所需要的平均时间是否相同。显著性水平=0.05工厂123202820262619243123222722步骤：1、表格结构设置2、选择单因素方差分析，工具/数据分析单因素方差分析。设置对话框问题：原假设？第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院76第 页SS：离差平方和，Df：自由度，MS：均方差，F:F统计量，P-value：p值，F crit：临界值。F=10.636，大于临界值4.2565，拒绝原假设，即3家工厂混合原料所需平均时间因该不相同。参见演示文件 例题2：某试验田采用了2种不同的施肥方式和4种不同的水温。试验方式和产量如表所示，在0.05的显著性水平下,分析施肥方式和水温对产量的影响各自各自是否显著。步骤：1、表格结构设置2、选择无重复双因素分析，工具/数据分析无重复双因素分析,设置对话框水温施肥方式一次施肥二次施肥三次施肥42019219161514169101120876参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院78第 页（2）相关系数）相关系数参见演示文件相关系数是描述两个测量值变量之间的离散程度的指标 ，相关系数的取值在 -1 和 +1 之间。在excel中，利用相关系数分析工具或者统计函数correl、pearson可以进行相关系数的计算。从而实现对两个测量值变量的变化是否相关的判断。使用步骤具体举例说明。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院79第 页（3）回归分析）回归分析使用回归分析工具能够对数据组建立回归方程，拟合回归曲线，对参数进行检验和统计，并且对预测值进行精度检验和置信区间的估计等操作。利用回归分析可以建立两个变量间相互关系的方程，即回归方程。通常y表示因变量，x表示自变量。一个自变量称为一元线性回归模型，两个自变量以上称为多元现性回归模型。回归模型的建立有如下两种方法： 方法1：使用函数 方法2：使用数据分析/回归分析工具市场满意度调查产品价格满意度1,500 31,400 41,800 21,630 52,300 42,000 31,560 33,000 52,500 41,900 32,200 5例题3：产品销售价格和满意度进行分析(线性回归分析)方法1：使用函数建立线性回归模型：y=a + bxa=INTERCEPT(known_y , known_x), b=SLOPE(known_y , known_x)使用函数=RSQ(known_y , known_x)求解判定系数。 判定系数=0.203200887，与系数1相差太远，说明拟合程度比较低。参见演示文件方法2：使用回归分析工具建立线性回归模型：y=a + bx1、使用回归分析工具工具/数据分析下的回归2、设置相关参数xy值不能错要标志出xy轴名称，注意：必须选中列标题。指定输出的位置和输出项目3、美化表格4、分析输出结果续参见演示文件 例题4：在经过连续10个月的销售低谷后，某公司发现它在市场上推出的新产品销售量呈指数增长。在此后的6个月中，销售量逐月增加，依次为33100、47300、69000、102000、150000和220000单位。试建立回归曲线。 请根据前面6个月的销售量，预测第17、第18、第19三个月的销售量。使用函数建立非线性回归模型，步骤如下：1、设置表格结构，条件区，结果区2、使用函数求解指数函数的常数和底 LOGEST (known_y , known_x，const，stats) GROWTH(known_y , known_x ，new_x，const)3、写出回归曲线方程。参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院85第 页（4）抽样分析工具）抽样分析工具参见演示文件步骤如下：1、使用抽样分析工具，工具/数据分析下的抽样2、设置抽取数据的相关参数，形成抽样数据 输入：输入区域中指定为进行数据抽样的数据源。 抽样方法：选择抽样方法 输出：指定输出区域。3、对抽样的数据进行设置（设置表格，标题等内容）4、抽样数据的使用5、进一步分析第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院86第 页（5）直方图分析工具）直方图分析工具参见演示文件步骤如下：1、计算全距，=最大值-最小值；2、分组：结合实际统计的标准界限进行分组；3、设置频数表，并计算频数； 利用frequency函数求解频数FREQUENCY(array1,array2)4、计算累计频数，设置公式计算；5、直方图分析。工具/数据分析下的直方图，进行对话框设置，形成一张新表。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院87第 页（6）移动平均法）移动平均法参见演示文件移动平均法是使用时间数列中最近几个时期数据值得平均数作为下一个时期的预测值。移动平均数=最近n期数据之和/n，n为移动项数。进行预测时，衡量预测结果好坏的一个重要因素是预测精度。常用均方误差（mse）来反映预测精度的测度。均方误差是误差平方和的平均数。但是均方误差在实际应用中不够直观，因此实际中往往采用百分比误差，计算公式为：第i期预测百分比误差=（预测值-实际值）实际值*100%第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院88第 页（7）指数平滑法）指数平滑法是用过去时间数列值的加权平均数作为预测值，它是由移动平均法演变而来的。其中表示t期时间数列的预测值，表示t期时间数列的实际值，表示平滑系数。确定的合理值得准则是：选择均方误差达到最小的值。实际中可以多选几个值进行试算。参见演示文件第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院89第 页小结：小结：函数与公式数据分析工具第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院90第 页作业：作业：1、作业：参见学生课后作业。2、布置小组作业。第四章第四章 账务处理子系统的分析和设计账务处理子系统的分析和设计北京交通大学经管学院91第 页本 讲 还 有 问 题？