《Excel在财务管理与分析中的应用【强烈推荐非常经典】.doc》由会员分享，可在线阅读，更多相关《Excel在财务管理与分析中的应用【强烈推荐非常经典】.doc（133页珍藏版）》请在三一办公上搜索。
1、第一篇 Excel操作一 Excel基本操作1 自定义填充序列；152 运算符及其优先级；163 输入函数规则；184 嵌套函数输入方法（函数中嵌套函数）；205 错误信息含义；216 取消网格线；267 为单元格或区域命名；288 保护数据；309 输入数组常量的办法；4510 输入公式数组的办法；4611 编辑数组公式；47二 函数运用（一）函数参数参数含义Lookup_value 查找值Lookup_vector 查找域Result_vector 结果域known_ys因变量y的观测值集合known_xs自变量x的观测值集合。它可以是一个变量（即一元模型）或多个变量（即多元模型）的集合。
2、如果只用到一个变量，只要 known-ys 和 known-xs 维数相同，它们可以是任何形状的选定区域。如果用到不只一个变量，known_ys 必须是向量（也就是说，必须是一行或一列的区域）。如果省略 known_xs，则假设该数组是 1,2,3.，其大小与 known_ys 相同const逻辑值，指明是否强制使常数b为0（线性模型）或为1（指数模型）。 如果const 为 TRUE或省略，b将被正常计算。如果const为FALSE，b将被设为0（线性模型）或设为1（指数模型）stats逻辑值，指明是否返回附加回归统计值。 如果 stats 为 TRUE，则函数返回附加回归统计值，这时返回的
3、数组为 mn，mn-1，.，m1，b；sen，sen-1，.，se1，seb，r2，sey；F，df；ssreg，ssresid。如果 stats为FALSE或省略，函数只返回系数预测模型的待估计参数m、mn、mn-1、.、m1和b。附加回归统计值返回的顺序见表4-2。表4-2中的各参数说明见表4-3。如果要得到附加回归统计值数组中的值，需用INDEX函数将其取出参数说明se1,se2,.,sen系数 m1,m2, .,mn 的标准误差值Seb常数项 b 的标准误差值（当 const 为 FALSE时，seb = #N/A ）r2相关系数，范围在 0 到 1 之间。如果为 1，则样本有很好的相
4、关性，Y 的估计值与实际值之间没有差别。反之，如果相关系数为 0，则回归方程不能用来预测 Y 值seyY 估计值的标准误差FF 统计值或F 观察值。使用F 统计可以判断因变量和自变量之间是否偶尔发生过观察到的关系Df自由度。用于在统计表上查找 F 临界值。所查得的值和函数 LINEST 返回的F统计值的比值可用来判断模型的置信度ssreg回归平方和ssresid残差平方和（二）函数1 条件求和SUMIF函数语法；502 IF函数语法；533 AND、OR和NOT函数语法；544 LOOKUP函数语法函数 LOOKUP 有两种语法形式：向量和数组。（1）向量形式LOOKUP(lookup_val
5、ue,lookup_vector,result_vector)：在“查找域”中寻找“查找值”，返回与“查找值”相对的“结果域”的值。Lookup_value：查找值；Lookup_vector：查找域；Result_vector：结果域。（2）数组形式LOOKUP(lookup_value,array)：在“数组”中查找“查找值”，返回与“查找值”相关的值。Lookup_value：查找值；Array：数组。5 VLOOKUP函数语法VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)：在“表组”中查找“查找值”，返回“列序号
6、”所标值。Lookup_value：查找值；Table_array：表组；为两列或多列数据，第一列中的值必须以升序排序。Col_index_num：列序号；Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。Range_lookup：范围；如果为 TRUE，则返回精确匹配值或近似匹配值。如果为FALSE，则只返回精确匹配值。6 HLOOKUP函数语法HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
7、：在“表组”中查找“查找值”，返回“行序号”所标值。Lookup_value：查找值；Table_array：表组；为两列或多列数据，第一列中的值必须以升序排序。row_index_num：行序号；row_index_num为1时，返回table_array第一列中的数值；row_index_num为2，返回 table_array 第二列中的数值，以此类推。Range_lookup：范围；如果为 TRUE，则返回精确匹配值或近似匹配值。如果为FALSE，则只返回精确匹配值。7 MATCH函数MATCH(lookup_value,lookup_array,match_type)：在“查找数组”
8、中，根据“匹配类型”查找“查找值”。Lookup_value：查找值；Lookup_array：查找数组；Match_type：匹配类型；为数字 -1、0 或 1。Match-type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。l 如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE。l 如果 match_type 为 0，函数 MATCH 查找等于 lookup_value
9、 的第一个数值。Lookup_array 可以按任何顺序排列。l 如果 match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列：TRUE、FALSE、Z-A、.、2、1、0、-1、-2、.，等等。l 如果省略 match_type，则假设为 1。8 INDEX函数函数 INDEX() 有两种形式：数组和引用。（1）数组形式INDEX(array,row_num,column_num)：在“数组”中查找“行序号”和“列序号”对应值。Array：数组；Row_num：行序号；Column_num：列序号；（2
10、）引用形式INDEX(reference,row_num,column_num,area_num)：在“引用”中，根据“区域号”、“行序号”和“列序号”进行查找。Reference：引用；Row_num：行序号；Column_num：列序号；Area_num：区域号；选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。9 ADDRESS函数ADDRESS(row_num,column_num,abs_num,a1,sheet_text)：根据“引用类型”、“引用位置”、“引用位置”、“行序号”和“列序号”进行引用。Row_num
11、：行序号；Column_num：列序号；Abs_num：引用类型；Abs_num返回的引用类型1 或省略绝对引用2绝对行号，相对列标3相对行号，绝对列标4相对引用a1：引用形式；用以指定 A1 或 R1C1 引用样式的逻辑值。如果a1为 TRUE 或省略，函数 ADDRESS 返回 a1样式的引用；如果a1为 FALSE，函数 ADDRESS 返回 R1C1 样式的引用。Sheet_text：表内容；既引用位置。10 INDIRECT函数INDIRECT函数的功能是返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用此函数。公
12、式为INDIRECT(ref_text,a1)ref_text： a1：引用形式；指明包含在单元格ref_text中的引用的类型，如果a1为TRUE或省略，ref_text被解释为A1样式的引用，如果a1为FALSE，ref_text被解释为R1C1样式的引用。 11 TRANSPOSE函数（矩阵函数）TRANSPOSE函数的功能是求矩阵的转置矩阵。公式为TRANSPOSE(array)Array：数组；需要进行转置的数组或工作表中的单元格区域。函数TRANSPOSE必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。【例2-7】假设矩阵A中的值如图2-1
13、8中单元格区域A2:C5，求其转置矩阵的步骤如下：图2-18 求转置矩阵（1）选取存放转置矩阵结果的单元格区域，如E2:H4。（2）选取函数TRANSPOSE，在该函数对话框中输入（可用鼠标拾取）单元格A2:C5，按“Crtl+Shift+Enter”组合键，即得转置矩阵如图2-18所示。12 MINVERSE函数（矩阵函数）MINVERSE函数的功能是返回矩阵的逆矩阵。公式为MINVERSE(array)array数组；具有相等行列数的数值数组或单元格区域。MINVERSE函数的使用方法与TRANSPOSE函数是一样的。在求解线性方程组时，常常用到MINVERSE函数。13 MMULT函数（
14、矩阵函数）MMULT函数的功能是返回两数组的矩阵乘积。结果矩阵的行数与 array1 的行数相同，列数与 array2 的列数相同。公式为MMULT(array1,array2)array1, array2：要进行矩阵乘法运算的两个数组。14 ROUND函数（四舍五入函数）ROUND函数的功能是返回某个数字按指定位数舍入后的数字。公式为= ROUND(number,num_digits)式中 number需要进行舍入的数字；num_digits指定的位数，按此位数进行舍入。15 LINEST函数（预测函数）LINEST函数的功能是使用最小二乘法计算对已知数据进行最佳线性拟合的直线方程，并返回描
15、述此线性模型的数组。因为此函数返回数值为数组，故必须以数组公式的形式输入。函数公式为= LINEST（known_ys，known_xs，const，stats）下面举例说明LINEST函数的应用。（1）一元线性回归分析LINEST函数可用于一元线性回归分析，也可以用于多元线性回归分析，以及时间数列的自回归分析。当只有一个自变量 x （即一元线性回归分析）时，可直接利用下面的公式得到斜率和 y 轴的截距值以及相关系数： 斜率：INDEX（LINEST（known_ys，known_xs）,1,1）；或INDEX（LINEST（known_ys,known_xs）,1）截距：INDEX（LINE
16、ST（known_ys，known_xs）,1,2）；或INDEX（LINEST（known_ys,known_xs）,2）相关系数：INDEX（LINEST（known_ys,known_xs,true,true）,3,1）【例4-1】某企业19月份的总成本与人工小时及机器工时的数据如图4-1所示。假设总成本与人工小时之间存在着线性关系，则在单元格B13中插入公式“=INDEX(LINEST(B2:B10,D2:D10),2)”，在单元格B14插入公式“=INDEX(LINEST(B2:B10,D2:D10),1)”，在单元格B15插入公式“=INDEX(LINEST(B2:B10,D2:D
17、10,TRUE,TRUE),3,1)”，即得总成本与人工小时的一元线性回归分析方程为：Y=562.72756+4.41444X1，相关系数为R2=0.99801，如图4-1所示。图4-1 一元线性回归分析（2） 多元线性回归分析仍以例4-1的数据为例，首先选取单元格区域A17:D21，再以数组公式方式输入公式“=LINEST(B2:B10,C2:D10,TRUE,TRUE)”，即得该二元线性回归的有关参数如图4-2所示，从而得到：图4-2 二元线性回归分析回归方程：Y = 471.4366+3.6165X1+3.4323X2相关系数：R2 =0.9990标准差：Sey =11.7792。18
18、LOGEST函数（预测函数）LOGEST函数的功能是在回归分析中，计算最符合观测数据组的指数回归拟合曲线，并返回描述该指数模型的数组。由于这个函数返回一个数组，必须以数组公式输入。LOGEST函数的公式为= LOGEST(known_ys,known_xs,const,stats) 【例4-2】某企业12个月某产品的生产量（X）与生产成本（Y）的有关资料如图4-3所示，假设它们之间有如下关系：。选取单元格区域B15:C18，输入公式“=LOGEST(C2:C13,B2:B13,TRUE,TRUE)”（数组公式输入），即得回归参数，如图4-3所示，参数m=0.8887，参数b=1891.7729
19、，生产成本与生产量的回归曲线为：Y=1791.77290.8887X，相关系数R2=0.95885。图4-3 指数回归回归方程的系数及相关系数也可以利用下面的公式直接计算参数m：INDEX(LOGEST(C2:C13,B2:B13),1)=0.8887参数b：INDEX(LOGEST(C2:C13,B2:B13),1,2)=1791.7729相关系数R2：=INDEX(LOGEST(C2:C13,B2:B13,TRUE,TRUE),3,1)= 0.9588519 TREND函数（预测函数）TREND函数的功能是返回一条线性回归拟合线的一组纵坐标值（y 值），即找到适合给定的数组 known_y
20、s 和 known_xs 的直线（用最小二乘法），并返回指定数组 new_xs 值在直线上对应的 y 值。TREND函数的公式为= TREND(known_ys,known_xs,new_xs,const)式中 new_xs 需要函数 TREND 返回对应 y 值的新 x 值。 new_xs 与 known_xs 一样，每个独立变量必须为单独的一行（或一列）。因此，如果 known_ys 是单列的，known_xs 和 new_xs 应该有同样的列数，如果 known_ys 是单行的，known_xs 和 new_xs 应该有同样的行数。如果省略 new_xs，将假设它和 known_xs 一
21、样。【例4-3】某企业过去一年的销售量为下列数据：300，356，374，410，453，487，501，534，572，621，650，670，将它们保存在单元格A1:A12中，则下一年的1、2、3月的销售量预测步骤为：选中单元格区域B1:B3，输入公式“=TREND(A1:A12,13;14;15)”（数组公式输入），即得来年的1、2、3月份的销售量分别为710、743和777。这个公式默认1;2;3;4;5;6;7;8;9;10;11;12作为known_xs的参数，故数组13;14;15就对应其后的3个月份。20 GROWTH函数（预测函数）GROWTH函数的功能是返回给定的数据预测的
22、指数增长值。根据已知的x值和y值，函数GROWTH返回一组新的x值对应的y值。可以使用GROWTH工作表函数来拟合满足给定x值和y值的指数曲线。GROWTH函数的公式为= GROWTH(known_ys,known_xs,new_xs,const)式中，各参数的含义同TREND函数。但需注意的是，如果known_ys中的任何数为零或为负，函数 GROWTH将返回错误值 #NUM!。 【例4-4】以例4-3的资料为例，利用GROWTH函数预测来年的1、2、3月的销售量。预测步骤为：选中单元格区域B1:B3，输入公式“=GROWTH(A1:A12,13;14;15)”（数组公式输入），即得来年的1
23、、2、3月份的销售量分别为756、811和870。这个公式同样默认1;2;3;4;5;6;7;8;9;10;11;12作为known_xs的参数，故数组13;14;15就对应后面的3个月份。21 FORECAST函数（预测函数）FORECAST函数的功能是根据给定的数据计算或预测未来值。此预测值为基于一系列已知的 x 值推导出的 y 值。以数组或数据区域的形式给定 x 值和 y 值后，返回基于 x 的线性回归预测值。FORECAST函数的计算公式为 a+bx式中，；。FORECAST函数的公式为= FORECAST(x,known_ys,known_xs)式中x需要进行预测的数据点。需要说明的
24、是： 如果 x 为非数值型，函数 FORECAST 返回错误值 #VALUE!。如果 known_ys 和 known_xs 为空或含有不同数目的数据点，函数 FORECAST 返回错误值 #N/A。如果 known_xs 的方差为零，函数 FORECAST 返回错误值 #DIV/0!。例如：FORECAST(30,6,7,9,15,21,20,28,31,38,40) = 10.60725。22 SLOPE函数（预测函数）SLOPE函数的功能是返回根据 known_ys 和 known_xs 中的数据点拟合的线性回归直线的斜率。斜率为直线上任意两点的垂直距离与水平距离的比值，也就是回归直线的
25、变化率。SLOPE函数的公式为 = SLOPE(known_ys,known_xs)说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用参数里包含文本、逻辑值或空白单元格，这些值将被忽略。但包含零值的单元格将计算在内。如果 known_ys 和 known_xs 为空或其数据点数目不同，函数 SLOPE 返回错误值 #N/A。例如：SLOPE(2,3,9,1,8,7,5,6,5,11,7,5,4,4) = 0.305556。23 INTERCEPT函数（预测函数）INTERCEPT函数的功能是利用已知的 x 值与 y 值计算直线与 y 轴的截距。截距为穿过 known_xs
26、和 known_ys 数据点的线性回归线与 y 轴的交点。公式为 = INTERCEPT (known_ys,known_xs)例如：INTERCEPT(2, 3, 9, 1, 8, 6, 5, 11, 7, 5) = 0.0483871。三 图表1 地区销售分布图表的建立企业的产品销往全国各地及世界各地，各地的销售量是不同的，我们可以利用Excel的地图分析工具建立销售数据地图，从而可以将企业产品在各地的销售情况更加直观地表示出来。【例2-9】某企业在某些省份的销售数据如图2-28所示，则建立数据地图的步骤如下：图2-28 销售数据 （1）选中数据区域A2:B15。（2）单击【插入】菜单，执
27、行【对象】命令，弹出【对象】对话框，如图2-29所示，选中“Microsoft地图”，则Excel就会根据所选的工作表数据建立如图2-30所示的数据地图；在数据地图中，数据越多的区域（省份），颜色就越深。（3）在图2-30中所示的地图中没有标明省份名称，但可以通过下述方法加入省份名称：双击地图，出现地图的菜单，如图2-31所示，单击地图菜单上的【工具】，选择【标志】项，出现【地图标志】对话框，如图2-32所示；（4）在【地图标志】对话框中，【需要设置标志的地图项】中选择“中国”，【创建标志】中选择“地图项名称”，然后单击【确定】按钮。图2-29 【对象】对话框图2-30 某企业的销售地区分布图
28、2-31 地图菜单项图2-32 【地图标志】对话框（5）在地图上移动鼠标，在鼠标移动过程中，Excel会显示该区域所对应的省份名称，单击左键，该省份名称就会标注在对应的省份区域上（图表太小，此处省略）。（6）如果需要，还可以对各省份添加数量标志，方法是：双击地图，出现【Microsoft 地图控件】对话框，如图2-33所示，根据需要选择数据类型格式，用鼠标把需要的格式拖放在对话框右边区域中的格式上，然后把要设置这种格式的数据列（对话框中的【第B列】）拖放在图中的“列”字框上即可。【Microsoft 地图控件】对话框提供了6种不同的数据类型格式。图2-20就是第1种数据类型格式。图2-33 【
29、Microsoft 地图控件】对话框需要注意的是，图2-28的工作表数据中的各省份名称必须与Excel所规定的相同，否则可能会出现意想不到的错误。Excel所规定的各省份名称如下：黑龙江省、吉林省、辽宁省、内蒙古自治区、新疆维吾尔自治区、北京市、天津市、河北省、山西省、陕西省、青海省、宁夏回族自治区、西藏自治区、山东省、河南省、江苏省、浙江省、上海市、安徽省、湖北省、湖南省、福建省、广东省、广西壮族自治区、江西省、四川省、云南省、贵州省、海南省、香港、台湾省、澳门。2 动态图表的建立在企业的经营活动中，往往需要为每个部门建立大量相似的图表，如果在一张工作表上建立太多的图表，既费时也使得图表显得
30、凌乱不堪。我们可以建立动态图表来解决这个问题，当需要了解某个部门的销售情况时，只需将鼠标移到工作表中该部门的单元格上，即可立即显示出该部门的销售图表。【例2-10】某企业的8个销售部门一年内各月的销售量数据如图2-34所示，建立各部门的动态图表的步骤如下：图2-34 动态销售图表（1）设计动态图表数据区域，如图2-34所示。（2）在单元格A13中输入公式“=INDIRECT(ADDRESS(CELL(row),COLUMN (A3)”，并把该公式向右填充复制到M13中，这里COLUMN的意思是返回参数所在的列标，CELL(row)的意思是返回当前光标所在的行号，ADDRESS（行号，列标）的意
31、思是返回由行号和列标确定的单元格，INDIRECT的意思是返回参数所确定的单元格内容；（3）选中区域A12:M13，插入“折线图”，并进行相应的格式设置，则动态图表就建立起来了。若鼠标单击A3单元格，再按F9键（即对工作表数据重新计算），就会显示部门A的销售图；若鼠标单击A5单元格，再按F9键（即对工作表数据重新计算），就会显示部门C的销售图。这样，就可以很方便地对各个销售部门的销售量进行直观的观察和分析。四 数据处理1 筛选单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中选择【自动筛选】2 分类汇总在对数据进行汇总之前，应特别注意的是：首先必须对要汇总的关键字进行排序。例如，在例2-11
32、中，要按地区进行自动分类汇总，其步骤如下：（1）首先对“地区”进行排序，排序方法见前面所述。（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【分类汇总】项，系统弹出如图2-49所示的【分类汇总】对话框。图2-49 【分类汇总】对话框（3）在【分类汇总】对话框中，【分类字段】选项下选择“地区”，【汇总方式】选项下选择“求和”，【选定汇总项】选项下选定“数量”和“金额”，单击【确定】按钮，则分类汇总的结果如图2-50所示。图2-50 按地区分类汇总结果在图2-50中，左上角有3个按钮，按钮1表示1级汇总，显示全部的销售数量和销售金额汇总；按钮2表示2级汇总，显示各地区的全部
33、销售数量和销售金额汇总；按钮3表示3级汇总，显示各地区的销售数量和销售金额的汇总明细及汇总额（即图2-50所示的汇总结果）。图2-50中，左边的滑动按钮为隐藏明细按钮，单击此按钮，则将隐藏本级的明细数据，同时变为显示明细按钮，再单击按钮，则将显示本级的全部明细数据，同时变为。在上述自动分类汇总的结果上，还可以再进行分类汇总，例如再进行另一种分类汇总，两次分类汇总的关键字可以相同，也可以不同，其分类汇总方法与前面的是一样的，此处不再介绍。如果不再需要分类汇总结果，可在图2-49所示的【分类汇总】对话框中单击【全部删除】，即可撤消分类汇总。五 数据分析工具的应用1 方案分析在企业的生产经营活动中，
34、由于市场的不断变化，企业的生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。Excel提供了称为方案的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种方案（即多个假设条件）进行分析。下面结合实例来说明如何使用方案管理器进行方案分析和管理。【例2-17】某企业生产产品A、产品B、产品C，在2003年的销售额分别为200万元、400万元和300万元，销售成本分别为120万元、280万元和160万元。根据市场情况推测，2004年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如图2-73所示。图2-73 产品销售资料及预计增长率（1
35、） 建立方案根据以上资料，建立分析方案：l 单击工作表的任一单元格，激活工作表，并设计方案计算分析格式，如图2-73所示，并在单元格G7中输入公式“=SUMPRODUCT(B3:B5,1+G4:G6)-SUMPRODUCT(C3:C5,1+H4:H6)”。l 将可变单元格分别进行命名，即单元格G4的名字为“产品A销售额增长率”，单元格H4的名字为“产品A销售成本增长率”，单元格G5的名字为“产品B销售额增长率”，单元格H5的名字为“产品B销售成本增长率”，单元格G6的名字为“产品C销售额增长率”，单元格H6的名字为“产品C销售成本增长率”，单元格G7的名字为“总销售利润”。l 单击【工具】菜单
36、，选择【方案】项，系统弹出【方案管理器】对话框，如图2-74所示，单击【添加】按钮，系统弹出【添加方案】对话框，如图2-75所示。 图2-74 【方案管理器】对话框 图2-75 【添加方案】对话框l 在【添加方案】对话框中，【方案名】编辑框中输入“方案1 销售好”，【可变单元格】编辑框中输入“$G$4:$H$6”，单击【确定】按钮，系统弹出【方案变量值】对话框，如图2-76所示；图2-76 【方案变量值】对话框l 在【方案变量值】对话框中输入每个可变单元格的值（这里要按行输入），完毕后单击【添加】按钮，系统会弹出如图2-75所示的【添加方案】对话框，对第2个方案进行输入；待所有方案输入完毕后，
37、单击【方案变量值】对话框中的【确定】按钮，系统返回到【方案管理器】对话框，如图2-77所示。此时，可单击【关闭】按钮，回到工作表。图2-77 方案建立完毕后的【方案管理器】对话框（2） 显示方案方案制定好后，任何时候都可以执行方案，查看不同的执行结果，方法如下：l 打开原工作表，并激活工作表。l 单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图2-77所示，选择要想查看的方案，单击【显示】按钮，则系统就自动显示出该方案的执行结果，如图2-73所示。（3） 修改、删除或增加方案对做好的方案进行修改，只需在图2-77所示的【方案管理器】对话框中选中需要修改的方案，单击【编辑】按
38、钮，系统弹出如图2-76所示的对话框，进行相应的修改即可。若要删除某一方案，则在图2-77所示的【方案管理器】对话框中选中需要删除的方案，单击【删除】按钮。若要增加方案，则在图2-77所示的【方案管理器】对话框中单击【添加】按钮，然后在图2-75所示的对话框填写相关的项目。（4） 建立方案报告当需要将所有的方案执行结果都显示出来时，可建立方案报告，方法如下：l 在图2-77所示的【方案管理器】对话框中单击【总结】按钮，弹出【方案总结】对话框，如图2-78所示，在【结果类型】中选择“方案总结”项，在【结果单元格】中输入“G7”，然后单击【确定】按钮，则系统在当前工作簿中自动建立一个名为“方案总结
39、”的工作表，如图2-79所示。图2-78 【方案总结】对话框图2-79 方案报告六 宏与VBA的初步应用在有些情况下，我们需要建立自定义函数或子程序来解决某些问题。Excel提供的Visual Basic编辑器可以帮助解决这个问题。1 建立自定义函数【例2-18】建立不允许缺货且陆续均衡供货和消耗情况下的经济订货批量自定义函数。经济订货批量的计算公式为则经济订货批量自定义函数的建立方法和步骤如下：（1）单击【工具】菜单，选择【宏】项，在【宏】项的子菜单中选择【Visual Basic编辑器】，打开Visual Basic编辑器窗口，再单击Visual Basic编辑器窗口的【插入】菜单，选择【
40、模块】项，则显示模块1的窗口，如图2-80所示。图2-80 准备创建模块（2）在模块1窗口中，单击【插入】菜单，选择【过程】项，如图2-81所示，则系统弹出【添加过程】对话框，如图2-82所示。 图2-81 插入过程 图2-82 【添加过程】对话框（3）在【添加过程】对话框中，【名称】栏中输入“经济订货批量”，【类型】选“函数”，单击【确定】按钮，出现编辑过程页面。（4）将Public Function 经济订货批量( )和End Function修改为如下的过程代码（见图2-83）：图2-83 自定义函数过程Public Function 经济订货批量(年消耗量, 订货费用, 单位存货成本,
41、 每日到货量, 每日消耗量)经济订货批量 =(2 * 订货费用 * 年消耗量 / 单位存货成本 * 每日到货量 / (每日到货量 - 每日消耗量)0.5End Function则自定义函数就完成了。然后关闭此窗口，返回工作表。2 使用自定义函数自定义函数的使用方法与Excel的函数是一样的，例如，图2-84为计算经济订货批量的有关参数，则单击单元格B6，单击工具栏上【粘贴函数】按钮，或单击【插入】菜单，选择【函数】项，系统弹出【粘贴函数】对话框，如图2-85所示，在【函数分类】中选择“用户定义”，在【函数名】中选择“经济订货批量”，单击【确定】按钮，系统弹出【经济订货批量】函数对话框，如图2-
42、86所示，在对话框中输入相应的参数或单元格地址，然后单击【确定】按钮，则经济订货批量就计算出来了，如图2-84所示。图2-84 利用自定义函数计算经济订货批量图2-85 【粘贴函数】对话框图2-86 【经济订货批量】函数对话框含有自定义函数的工作簿再次被打开时，系统会弹出如图2-87所示的警告对话框，此时要单击【启用宏】按钮，而不要单击【取消宏】按钮，否则该自定义函数将不能使用。图2-87 警告对话框第二篇 Excel 应用第一章 Excel在资金时间价值计算中的应用第二章 Excel在财务预测中的应用一 财务预测步骤1 销售预测企业的一切财务需求都可以看作是因销售引起的，销售量的增减变化，将
43、会引起库存量、现金流量、应收与应付账款以及公司其他资产和负债的变化。因此销售预测在企业预测系统中处于先导地位。2 估计收入、费用和利润收入和费用与销售量之间也存在一定的函数关系，因此，可以根据销售数据估计收入和费用，并确定净利润。净利润和股利支付率，共同决定了内部留存收益所能提供的资金数额。3 估计需要的资产资产通常是销售收入的函数，根据历史数据可以分析出二者之间的函数关系。根据预计销售收入和资产与销售之间的函数关系，可以预测所需资产的总量。某些流动负债也是销售收入的函数，相应地也可以预测负债的自发增长额，这种增长可以减少企业外部融资的数额。4 估计所需融资根据预计资产总量，减去已有的资金来源
44、、负债的自发增长和内部提供的留存收益，可得出所需的外部融资数额。二 财务预测的分析方法（一） 移动平均法1 移动平均法特点l 移动期数的大小视具体情况而定，移动期数少，能快速地反映变化，但不能反映变化趋势；l 移动期数多，能反映变化趋势，但预测值带有明显的滞后偏差。常用的移动平均法主要有一次移动平均法和二次移动平均法。2 一次移动平均法一次移动平均法是依次计算包含一定项数的时间序列平均数，形成一个平均时间数序列，并据此进行预测。预测模型为式中第t+1期的预测值；、将被平均的n个观测值；n移动平均的项数，即移动期数。在实际预测中，可以多取几个n数，并将得到的预测值与实际值进行比较，选用误差最小的
45、n值。3 二次移动平均法二次移动平均法是对时间序列计算一次移动平均数后，再对一次移动平均数序列进行一次移动平均运算。预测模型为。式中二次移动平均数；第t+1期的预测值，即。二次移动平均法解决了一次移动平均法只能预测下一期的局限性，它可以进行近、短期的预测。但它仍不能解决中长期的预测问题。（二） 指数平滑法指数平滑法实际上也是一种加权平均法，是一种改良的加权平均法，预测模型为式中 平滑系数，01。在指数平滑法中，确定合适的值和初始值是非常重要的。越大，t期的实际值对新预测值的贡献就越大；越小，t期的实际值对新预测值的贡献就越小。一般情况下，可以取几个不同的值进行预测，比较它们的预测误差，选择预测
46、误差最小的值。（三） 回归分析预测法回归分析预测法是通过研究两组或两组以上变量之间的关系，建立相应的回归预测模型，对变量进行预测的一种预测方法。1回归分析预测法的基本程序进行回归分析的步骤如下：（1）收集有关资料。将各种可能的影响因素的有关数据尽可能多地收集起来。（2）判断趋势。根据收集到的数据，判断其变化趋势，从而为建立相应的数学模型做准备。对于变量不多的问题，可以通过绘制散点图来判断变化趋势。（3）建立预测数学模型。根据历史数据的变化趋势，选择相应的描写该问题的数学模型，并采用相关的计算技术来估计数学模型的参数。（4）相关检验。对建立的预测数学模型，必须进行有关的检验，主要是通过计算预测模型的相关系数、方差（或标准差）以及显著性等指标，来判断预测模型的准确性、是否需要修正、采用何种方法修正等。2回归模型建立的方法建立回归模型的一般方法是采用最小二乘法，其原理如下：考虑m个自变量x1、x2、x m和因变量y的关
