excel高级教程高级应用.docx

Excel高级应用教程目录第一节EXCE1.公式与函数的高级应用11.1 数坦公式与其应用11.1.1 数蛆公式的输入、得辑与除I一 .数也公式的输入1二 .编辑数蛆公式4三 .蛆公式41.1.2 数用公式的应用4一 .用数坦公式计算两个数据区域的乘积4二 .用Iha公式计算多个数据区域的和5三 .用数坦公式同时对多个耐区域进行相同的计算61.2 常用函数与其应用61.2.1 SUM函数、SUMIF函数和SUMPRODUCT函数6一.无M林SUM函数6二M林SUMIF函数7三.SUMPRODUCT*81.2.2 AVERAGE函数81.2.3 MIN函唧MAX函数91.2.4 8UNT函数和COUNTIF函数91.2.5 IF函数91.2.6 AND函数、OR函数和NoT函数10二 .修改或IifiHa录24三 .添加新的记录241.1.2 自动耐与自定义财25一 .自动#A25二 .自定义方式261.1.3 高级*26一 .一般状况下的商级耸地27二 .计算条件状况下的克级篇选282.3 数据的分类与汇总292.3.1 进行分类汇总292.3.2 分类汇总的抵消302.4 302.4.1 302.4.2 数据的透视分析33第三节EXCE1.图表处理333.1 图表类型333.2 图表的建立343.3 图表的编辑、修改与格式化36一 .设置坐标、标题、图例等的格式36二 .变更图表b37三 移动或复制图表37四 .添加数据制37五 .变更图表“色、图案、边根383.4 地区传售分布图表的建立38图1-5在编辑栏中输入数组公式(3)按CtIi+Shift+Enter组合键，就在单元格Al、BKCbDI、A2、B2、C2、D2中分别输入了10、20、30、40和50、60、70、80,如图2-6所示。图1-6同时按Ctrl+Shift+Enter组合键，得到数组常成输入公式数组的方法例如，在单元格A3:D3中均有相同的计算公式，它们分别为单元格Al:DI与单元格A2:D2中数据的和，即单元格A3中的公式为''=A1+A2'',单元格B3中的公式为''=B1+B2”,，则可以采纳数组公式的方法输入公式，方法如F：(1)选取单元格区域A3:D3,如图2-7所示。(2)在公式编辑栏中输入数蛆公式“=A1:D1+A2:D2"，如图2-8所示。(3)同时按Ctrl+Shift+Entcr组合键，即可在单元格A3:D3中得到数组公式“=A1:D1+A2:D2”，如图2-9所示C图1-9同时按Ctrl+Shift+Enter组合键，得到数组公式二.编辑数倒公式数蛆公式的特征之一就是不能单独编辑、清除或移动数组公式所涉与的单元格区域中的某一个单元格。若在数组公式输入完毕后发觉错误须要修改，则须要按以下步骤进行：(1)在数组区域中单击任一单元格。(2)单击公式编辑栏，当编辑栏被激活时，大括号“”在数组公式中消逝。(3)编辑数组公式内容。(4)修改完毕后，按“Crtl+Shift+Enter”组合键。要特殊留意不要遗忘这一步。三.除数疑公式删除数组公式的步骤是：首先选定存放数组公式的全部单元格，然后按DeIete键。数蛆公式的应用一.用数Ia公式计算两个数据区域的乘积【例2-1如图2-10所示，已经知道12个月的销售量和产品单价，则可以利用数组公式计算每个月的销售额，步骤如下：二1Kbmibo¾B”图1-10用数组公式计算销售额(1)选取单元格区域B4:M4o(2)输入公式“=B2:M2*B3:M3”。(3)按“Crtl+Shift+Enter”组合键。假如须要计算12个月的月平均销售额，可在单元格B5中输入公式JAVERAGE(B2:M2AB3:M3)”，然后按“Crtl+Shift+Enter”组合键即可，如图2-10所示。在数组公式中，也可以将某一常境与数组公式进行加、减、乘、除，也可以对数组公式进行乘室、开方等运算。例如在图2-10中，每月的单价相同，故我们也可以在单元格B4:M4中输入公式=B2M2*28w,然后按“Crtl+Shift+Enter”组合健；在单元格B5中输入公式JAVERAGE(B2:M2*28)”，然后按Crtl+Shift+Enterw组合键。在运用数组公式计算时，最好将不同的单元格区域定义不同的名称，如在图2-10中,将单元格区域B2:M2定义名称为“销售量”，单元格区域B3:M3定义名称为“单价”，则各月的销售额计算公式为“=销售量*单价”，月平均销售额计算公式为“=AVERAGE(销传留单价)”，这样不简洁出错，二.用JM公式计算多个好区域的和假如须要把多个对应的行或列数据进行相加或相减的运算，并得出与之对应的一行或一列数据时，也可以运用数组公式来完成。【例2-2某企业2002年俏售的3种产品的有关资料如图2-11所示，则可以利用数组公式计算该企业2002年的总销售额，方法如下：图1-11某企业的月销售总额计算二MK工WWXJ.二WJ.,1.KiWgWJ.WWgwtxoj(1)选取单元格区域C8:N8°(2)输入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7"(>(3)按“Crtl+Shift+Enter”组合健。钮工例如，在例2-1中，求全年的销售量，则可以单击单元格N2,然后再单击求和按钮工，按回车键即可，如图1-13所示。ABCDEFGH"IjK1.M1119份123<TIQH12.4-Jwb1一必mWS知48一”300划O51K-SUM，率k图1-13自动求和二条件求和SUMlF函数SUMIF函数的功能是依据指定条件对若干单元格求和，公式：=SUMIF(range,criteria,sum-range)式中range-用于条件推断的单元格区域；Criteria一确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；SUm_range一须要求和的实际单元格。只有当range中的相应单元格满意条件时，才对sum.range中的单元格求和C若省略SUm_range,则干脆对range中的单元格求和。利用这个函数进行分类汇总是很有用的。【例1-4】某商场2月份销售的家电流水记录如图1-14所示，则在单元格13中输入公式"=SUMIF(C3:ClO,211,F3:FI0)”，单元格14中输入公式JSUMlF(C3C10,215,F3:F10)”，在单元格15中输入公式“=SUMIF(C3:CIO,212,F3:FIOr,单元格16中输入公式JSUMlF(C3C10,220,F3:F10)”，即可得到分类销售额汇总表。XSS三IFC>CO,J1.F3»101IIUQBB0«冏名，A科<ftn创¾门汨2mMlRh211MC集电o<amt<t<211I曲)900OH>OB2<TrKJUIWI陶18811aISMHSQ212VODrZOoosx>aI>M7flM2tvnSbhfl<eOO«000221!1«01e月乂日iMr2J212ZVAt1.2.3MlN函数和MAX函数MIN函数的功能是给定参数表中的母小值，MAX函数的功能是给定参数表中的最大值。公式为=MIN(参数1,参数2,，参数N)=MAX(参数1,参数2,，参数N)函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。例如，MIN(3,5,12,32)=3;MAX(3,5,12,32)=32。COUNT函数和COUNTIF函数CoUNT函数的功能是计算给定区域内数值型参数的数目。公式为：=COUNT(参数1,参数2,.»参数N)CoUNTIF函数的功能是计算给定区域内满意特定条件的单元格的数目。公式为：=CoUNTlF(range,criteria)式中range-须要计算其中满意条件的单元格数目的单元格区域；Criteria-确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。COUNT函数和COUNTIF函数在数据汇总统计分析中是特别有用的函数。IF函数IF函数也称条件函数，它依据参数条件的真假，返PI不同的结果。在实践中，常常运用函数IF对数值和公式进行条件检测.公式为=IF(logical-test,value_if_true,ValueJ1.false)式中IogiCa1.teSt一条件表达式，其结果要么为TRUE,要么为FA1.SE,它可运用任何比较运算符；ValueJ1.trueIogica1.test为TRUE时返回的值；ValueJ1.falseIogica1.test为FA1.SE时返Pl的值“IF函数在财务管理中具有特别广泛的应用。【例2-5例如，某企业对各个销售部门的销售业绩进行评价，评价标准与各个销售部门在2002年的销售业绩汇总如图1-16所示，评价计算步骤如下：ACD1wB-IAti100O(BaT9CJBKW100COXttOXiHRBO7yJQQi>>>XX>S折好豆m*o工9(V4B0±arjtr,w，磐n三ry一ti»01112图1-16销售部门业绩评价(1)选定单元格区域C3:C12。(2)干脆输入以下公式：“=IF(B3:B12<100000,"差”JF(B3:B12v200000,”一般",IF(B3:B12<300000,"好",IF(B3:B12<400000,“较好","很好”)"0(3)按aCrtl+Shift+EnterM组合健。则各个借售部门的销售业绩评价结果就显示在单元格域C3:C12中。也可以干脆在单元格C3中输入公式“=IF(B3vl00000,"差",IF(B3v200000,"一般",IF(B3<300000,"好",IF(B3v400000,“较好很好")”后,将其向下填充复制到C4C12单元格中。1.2.6AND函数、OR函数和NOT函数这3个函数的用法如下：=AND(条件1,条件2,，条件N)=OR(条件1,条件2,，条件N)=NOT(条件)AND函数表示逻辑与，当仝部条件都满意时(即全部参数的逻辑值都为真时)，AND函数返回TRUE,否则，只要有个条件不满意即返回FA1.SE。OR函数表示逻辑或，只要有一个条件满意时，OR函数返IqTRUE,只有当全部条件都不满意时才返回FA1.SEoNOT函数只有一个逻辑参数,它可以计算出TRUE或FA1.SE的逻辑值或逻辑表达式。假如逻辑值为FA1.SE,函数NOT返回TRUE;假如逻辑值为TRUE,函数NOT返PIFA1.SEo这3个函数般与IF函数结合运用。【例2-6某企业依据各销售部门的销售额与销售费用确定奖金提成比例与提取额,若销售额大于300000元且销伊费用占销售额的比例不超过1%,则奖金提取比例为15%,否则为10%,则计算过程如下（如图2-17所示）：（】）在单元格D3中输入公式“=IF（AND（B3>300000,C3B3<l%）,15%,10%）”，将其向下填充复制到D4Clo单元格中。（2）选取单元格区域E3:E10,输入公式M=B3:B10*D3:D10w,按“Crtl+Shift+Enter”组合键。则各销售部门的销售奖金提成比例与奖金提取额如图1-17所示。5UT4W三F0!史南<¾ll>?M»SM2IOMZXM<«<rStMIawsex*1UJD35OC>,>3qn嬴,而2WBBQjJ三¼«三r2>5rr11O,TJXeary9vmIOBCM图1-17奖金提成比例与提取额的计算1.KUP函数、V1.KUP函数和H1.KUP函数.1.OoKuP函数1.OOKUP函数的功能是返回向量（单行区域或单列区域）或数组中的数值，：.函数1.OOKUP有两种语法形式：向量和数组。函数1.OOKUP的向最形式是在单行区域或单列区域（向量）中查找数值，然后返回其次个单行区域或单列区域中相同位置的数值；函数1.OOKUP的数组形式在数组的第一行或第一列杳找指定的数值，然match_type为1时，100kUP_array必需按升序排列，函数MATCH查找小于或等于100kUP_value的最大数值。例如，MATCH(12,123,43,12,55,0)=3,MATCH(40,23,43,12,55)=1。EXCE1.公式与函数的高级应用(5)INDEX函数INDEX函数的功能是返阿表格或区域中的数值或对数值的引用。INDEX函数有以下两种形式：一、返回数蛆中指定单元格或单元格数蛆的数值。公式为=INDEX(array,row_num,column_num)式中array一单元格区域或数组常数；row_num一数蛆中某行的行序号，函数从该行返回数值。假如省略row_num,则必需有column_num；ColUmn_num一数组中某列的列序号，函数从该列返回数值。假如省略column-num,则必需有row_num«须要留意的是：假如同时运用row_num和column_num,函数INDEX返Plrow.num和column_num交叉处的单元格的数值1假如数组只包含一行或一列，则相对应的参数row_num或COIUmn_num为可选。假如数组有多行和多列，但只运用row_nUm或CoIUmn_num,函数INDEX返PI数组中的整行或整列，且返回值也为数组。假如将row_num或column_num设置为0,函数INDEX则分别返回整个列或行的数组数值。假如须要运用以数组形式返回的数值时，请在一个水平单元格区域中将函数INDEX作为数组公式输入。此外,row_num和CoIUmn_nUm必需指向array中的某一单元格，否则，函数INDEX返网错误值#REF!。例如：INDEX(H,2;3,4,2,2)=4°假如作为数组公式输入，则：INDEX(U23,4,0,2)钥二、返回引用中指定单元格。公式为：INDEX(reference,row_num,CohJmn_num,area_num)式中reference一对个或多个单元格区域的引用；假如为引用输入一个不连续的选定区域，必需用括号括起来。假如引用中的每个区域只包含一行或一列，则相应的参数row.num或COlUmn_num分别为可选项。例如，对于单行的引用，可以运用函数INDEX(reference,column_num)。row_num一引用中某行的行序号，函数从该行返回一个引用；CoIUmn_num一引用中某列的列序号，函数从该列返回一个引用；area_num选择引Jn中的一个区域，并返回该区域中rov_num和column_num的交叉区域。选中或输入的第一个区域序号为1,其次个为2,以此类推。假如省略area_num,函数INDEX运用区域1。说明：row_num、column_num和area_num必需指向reference中的单元格,否则，函数INDEX返回错误值#REF!。假如省略row_num和c。IUmn_num,函数INDEX返回由area,num所指定的区域C函数INDEX的结果为一个引用，且在其他公式中也被说明为引用。依据公式的须要，函数INDEX的返回值可以作为引用或是数值。例如，公式CE1.1.rwidth”,INDEX(A,2)等价于公式CE1.1.("width",Bl)0CE1.1.函数将函数INDEX的返PI值作为单元格引用。而在另一方面，公式2*1NDEX(A1.B2,1,2)将函数INDEX的返回值说明为BI单元格中的数字。ADDRESS函数ADDRESS函数的功能是依据给定的行号和列标，建立文本类型的单元格地址。公式为=ADDRESS(row_num,column_num,abs_num,a1,sheet-text)式中rowqum-在单元格引用中运用的行号；上述介绍的几个查找函数1.OOKUP、V1.OOKUP>H1.OoKUP、MATCH、INDEX、ADDRESS、INDlRECT等在财务分析与决策、预料与建立动态图表等中是特别有用的。矩阵函数一transpose函数、Minverse函数和mmult函数.TRANSPOSE函数TRANSPOSE函数的功能是求矩阵的转置矩阵。公式为=TRANSPOSE（array）式中，Array一须要进行转置的数组或工作表中的单元格区域。函数TRANSPOSE必需在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。【例1-7】假设矩阵A中的值如图1-18中单元格区域A2:C5,求其转置矩阵的步骤如下：E2二I：”¥：/.，SB（A2：C5）JIA.就牙D7以Rh】J篇.一41MM刈21M«noW»3aOKC3BgtonWJT«M24 21T5t3S25IeTWXMt5 .wwr36iem$图1-18求转置矩阵（1）选取存放转置矩阵结果的单元格区域，如E2:H4。（2）单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数TRANSPOSE,在该函数对话框中输入（可用鼠标拾取）单元格A2:C5,按“Crtl+Shift+Enter”组合键，即得转置矩阵如图2-18所示。利用TRANSPOSE函数可以把工作表中的某些行（或列）排列的数据转换成列（或行）排列的数据。例如，由于工作须要，要把工作表中的某些行数据改为列数据，若一个一个地改动数据，将是很麻烦也很费时的，而利用TRANSPOSE函数则可以很轻松地进行这项工作。但须要留意的是，利用TRANSPOSE函数对行（列）数据进(2)避开在单元格的开头和末尾键入空格。(3)避开在一张工作表中建立多个数据清单，每张工作表应仅运用一个数据清单。(4)工作表的数据清单应与其他数据之间至少留出一个空列和个空行，以便于检测和选定数据清单.(5)关键数据应置于数据清单的顶部或底部。2.1 数据排序2.1.1 制排序的规则Excel允许对字符、数字等数据按大小依次进行升序或降序排列，要进行排序的数据称之为关键字。不同类型的关犍字的排序规则如下：数值：按数值的大小。字母：按字母先后依次。日期：按日期的先后。汉字：按汉语拼音的依次或按笔画依次。逻辑值：升序时FA1.SE排在TRUE前面，降序时相反C空格：总是排在最终。2.1.2 S排序步It(1)单击数据区中要进行排序的殖意单元格。(2)单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话框，如图1-35所示。图1-35【排序对话框条件的数据记录，源数据不会变更，也不会被除藏；数据筛选是指把数据库或数据清单中全部不满意条件的数据记录隐藏起来，只显示满意条件的数据记录。常用的数据查找与筛选方法有：记录单查找、自动筛选和高级筛选。下面结合实例说明各种杳找方法的具体应用C【例211】图2-37为某公司的部分商拈脩售记求清单。Ji髯建黑签矍缁ssssC*芯器器器lsgsxl7:：:4:：1<三三三三三图2-37某公司的商品销售明细清单依据图2-37中的有关资料，可以分别采纳记录单查找、自动筛选或高级籁选的方式看找或选择所须要的信息，如下所述：2.2.1记录单查找汜录单是查找和编辑数据的最简洁的方法，利用记录单，不仅可以查找数据记录,还可以修改和删除记录、添加新的数据记录等。查找弹记录利用记录单杳找数据记录的步蛛如下：(1)用鼠标单击数据清单或数据库中的任一非空单元格。(2)单击【数据】菜单，选择【记录单】项，则系统弹出如图1-38所示的记录单。高级筛选可以运用较多的条件来对数据清单进行筛选，这些条件既可以是与条件，也可以是或条件，或与条件，与或条件的组合运用，还可以运用计算条件。一.一般状况下的商级篇选利用高级筛选对数据清单进行筛选的步骤如下：(1)首先应建立一个条件区域，在条件区域中，同i行中的条件是与条件，也就是这些条件必需同时满意；不同行中的条件是或条件，也就是这些条件只要满意其一即可。如须要查找张三销售彩电的全部记求，则建立条件区域如图1-45所示。图1-45建立条件区域(2)单击数据清单或数据库中的任非空单元格，然后单击【数据】菜单，选择【筛选】子菜单中的【高级箭选】项，则系统弹出如图1-46所示的【高级筛选】对话框C图1-46【高级筛选】对话框(3)一般状况下，系统将自动给出了数据区域，用户只需在【条件区域】栏中输入条件区域(本例中为B19:C20,也可以用鼠标拾取单元格区域，此时在条件区域中将显示“销售明细清单!$B$19:$C$20”。(4)高级筛选结果可以显示在数据清单的原有区域中，也可以显示在工作表的其他空白单元格区域，系统默认的方式是在数据清单的原有区域中显示结果。若须要在工作表的其他空白单元格区域显示结果，则在【方式】项中选中“将筛选结果复制到$A$2:$G$16;高级筛选的条件区域为$13$19:$C$20,则筛选结果如图2-48所示。2.3数据的分类与汇总在对数据进行分析时，常常须要将相同类型的数据统计出来，这就是数据的分类与汇总。在对数据进行汇总之前，应特殊留意的是：首先必需对要汇总的关键字进行排序。2.3.1进行分类汇总例如，在例2-11中，要按地区进行自动分类汇总，其步骤如下：(1)首先对“地区”进行排序，排序方法见前面所述。(2)单击数据清单或数据库中的任非空单元格，然后单击【数据】菜单，选择【分类汇总】项，系统弹出如图1-49所示的【分类汇总】对话框。图1-49【分类汇总】对话框(3)在【分类汇总】对话框中，【分类字段】选项下选择“地区”，【汇总方式】选项卜.选择“求和”，【选定汇总项】选项F选定“数盘”和“金额”，单击【确定】按钮，则分类汇总的结果如图2-50所示。图1-56各个销售人员销售商品的数据透视表2.4.2好的透视分析在图1-56所建立的数据透视表上，可以很便利地进行多角度的统计与分析。比如要了解李四所销售商品的状况，可在“销售人员”下拉列标中只选中“李四”，然后单击“确定”按钮，则李四的销售状况如图1-57所示。A¾s¾MBCD-P-二12.,q聿*:.g由二5TSjT<yiPK>IYOSa20*oexJtSaJ1.？4-UaKUlU7三0tJlX":7QUM1.iarflW:零_1_IS5a22_id_4唐阳疏通码11三,I碱二,1施11三图1-57李四的销告状况汇总还可以建立透视图，方法是：单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【数据透视图】项，则系统自动显示出数据透视图，从而得到每个销售人员的更为直观的销售状况。第三节EXCE1.图表处理Excel具有完整的图表功能，它不仅可以生成诸如条形图、折线图、饼图等标准图表，还可以生成较为困难的三维立体图表。对各种财务数据进行图表处理，可以更直观地进行财务分析，找出工作表格不简洁发觉的问题，使得财务管理工作更为有效。3.1图表类型EXCeI供应了约14种标准图表类型，如面积图、柱形图、条形图、折线图、饼图、圆环图、气泡图、雷达图、股价图、曲面图、散点图、锥形图、IKI柱图、极锥图等，每种图表类型又都有儿种不同的子类型。此外，EXCel还供应了约20种自定义I?l1-21【图表向导-4步骤之2图表源数据】对话框(2)在【图表向导-4步骤之2图表源数据】对话框中，单击【系列1.在【分类(X)轴标记】栏中填入M=Sheetl!$B$l:$M$r,单击【下一步】按钮,出现【图表向导-4步骤之3-图表选项】对话框，如图1-22所示。图1-22【图表向导-4步辘之3-图表选项】对话框(3)在【图表向导-4步骤之3-图表选项】对话框中，对【标题】的各项进行输入，即在【图表标题】栏中填入“销售额和销售费用的变更图”，在【数值X轴】栏中填入“月份”，在【数值丫轴】栏中填入“金额(千元)“，单击【下一步】按钮，出现【图表向导-4步骤之4一图表位置】如图2-23所示。图1-23【图表向导-4步骤之4-图表位置】对话框(4)在【图表向导-4步骤之4一图表位置】对话框中，若要建立嵌入式图表,所示的数据地图；在数据地图中，数据越多的区域(省份)，颜色就越深。(3)在图2-30中所示的地图中没有标明省份名称，但可以通过下述方法加入省份名称：双击地图，出现地图的菜单，如图2-31所示，单击地图菜单上的【工具】，选择【标记】项，出现【地图标记】对话框，如图2-32所示；(4)在【地图标记】对话框中，【须要设置标记的地图项】中选择“中国”，【创建标记】中选择“地图项名称“，然后单击【确定】按钮。图1-29【对象】对话框图1-30某企业的传售地区分布图1-31地图菜单项r-1(三图132【地图标记】对话框双变量模拟运算表就是考虑两个变量的变更对公式计算结果的影响，在财务管理中应用最多的是长期借款双变量分析模型，有关具体内容可参阅第3章的有关章节；4.2 单变量求解单变显求解就是求解只有一个变最的方程的根，方程可以是线性方程，也可以是非线性方程。单变量求解工具可以解决很多财务管理中涉与到个变量的求解问题。【例2-14】某企业拟向银行以7%的年利率借入期限为5年的K期借款，企业每年的偿还实力为100万元，那么企业最多总共可贷款多少?设计如图2-64所示的计算表格，在单元格B2中输入公式4*=PMT(B1,B3,B4)m,单击【工具】菜单，选择【单变量求解】项，则弹出【单变量求解】对话框，如图1-65所示，在【目标单元格】中输入“B2”，在【目标值】中输入“100”，在【可变单元格】中输入“$B$4”，然后单击【确定】按钮，则系统马上计算出结果，如图1-64所示，即企业最多总共可贷款410.02万元。图1-64贷款总额计算图1-65【单变侬求解】对活框4.3 规划求解规划求解是EXCeI的一个特别有用的工具，不仅可以解决运筹学、线性规划等问题，还可以用来求解线性方程组与非线性方程组。【规划求解】加载宏是Excel的一个可选安装模块，在安装MicrosoftEXCeI时,假如采纳【典型安装】，则【规划求解】工具没有被安装，只花在选择【完全/定制安装】时才可选择安装这个模块。在安装完成进入EXCeI后，单击【工具】菜单，选择【加载宏】项，在【加栽宏】对话框中选定【规划求解】复选框,然后单击【确定】按钮，则系统就安装和加载【规划求解】工具，可以运用它了。求解优化问fl!财务管理中涉与到很多的优化问题，如最大利涧、最小成本、最优投资组合、目标规划、线性回来与非线性回来等等。下面仅举一个简洁的例子来说明利用规划求解工具解决最大利涧的问题，有关规划求解工具的更多实际应用可参阅后面的有关章节"【例1-15】某企业在某月份生产甲、乙两种产品，其有关资料如图2-66所示，则企业应如何支配两种产品的产销组合，使企业获得最大销售利涧？利用规划求解工具求解这个问题的步骤如下：筋二JwciJ-ECSj”石i)BICD1 U户汽一2 货美P免3 户CTAWRI三TC<fJ)JK4 rfinU<>1«m<flwe(tS>2«5 G三i5CT<x>CO1«WflMSlCK,>WU6 CfiUf<1<f>，>7 ttrauH<ta>48 蜜尸与OIUM千JU18159 产R-H，一(»)/3Q10 HUFWrIW11产产：»一町支无格!?知n*<nJi"1.r既,元粗14ira.nKiRt<iir>×t>15产心金门千嵬A16产J«101n图1-66产品有关资料与优化结果(1)首先建立优化模型，(设X和y分别表示甲产品和乙产品的生产量)：目标函数：max销售利润=(140-60)×x+(180-100)×y约束条件：6x+9y3607x+4y24018x+15y850y30X>0,y>0,且为整数(2)单元格Bll和CIl为可变单元格，分别存放甲、乙产品的生产量C(3)单元格B12为目标单元格(销售利涧)，计算公式为a=SUMPRODUCT(B4:C4-B5:C5,B11:C1l)w;(4)在单元格B14中输入产品消耗工时合计计算公式=SUMPRODUCT(B6rC6,BIkCliro在单元格B15中输入产力消耗材料合计计算公式a-SUMPRODUCT(B7:C7,Bl1：C1l)w,在单元格B16中输入产品消耗能源合计计算公式''=SUMPRoDUCT(B8:C8,BI1.Cll。(5)单击【工具】菜单，选择【规划求解】项，则系统弹出【规划求解参数】对话框，如图1-67。JtQGhSJICTbI?“S,*D二雷1图1-67【规划求解参数】对话框(6)在【规划求解参数】对话框中，【设置目标单元格】中输入“$B$12”；【等于】选“最大值”；【可变单元格】中输入“$B逸1:$C$11”；在【约束】中添加以下的约束条件：C$11=整数”、“$B$11:$C、“$B$14V=$E$3"、“$B$15“$E$4”、“$B$16V=$E$5”、这里，添加约束条件的方法是：单击【添加】按钮，系统会弹出【添加约束】对话框，如图1-68所示，输入完毕一个约束条件后，单击【添加】按钮，则又弹出空白的【添加约束】对话框，再输入其次个约束条件。当全部约束条件都输入完毕后，单击【确定】按钮，则系统返回到【规划求解参数】对话框。图1-68【添加约束】对活框假如发觉输入的约束条件有错误，还可以对其进行修改，方法是：选中要修改的约束条件，单击【更改】按钮，则系统弹出【变更约束】对话框，如图1-69所示，再进行修改即可。图1-69【变更约束】对话框输入完毕约束条件后，若还须要添加约束条件，单击【添加】按钮，在弹出的【添加约束】对话框中输入约束条件即可。(7)假如须要，还可以设置有关的项目，即单击【选项】按钮，弹出【规划求解选项】对话框，如图2-70所示，对其中的有关项目进行设置即可;图1-70规划求解选项】对话框(8)在建立好全部的规划求解参数后，单击【求解】，则系统将显示如图2-71所示的【规划求解结果】对话框，选择保存规划求解结果】项，单击【确定】，则求解结果显示在工作表上，如图2-66所示。图1-71【规划求解结果】对话框(9)假如须要，还可以单击【规划求解结果】对话框中的【保存方案1.以便于对运算结果做进一步的分析。求解方程也利用规划求解工具还可以求解线性或非线性方程组，下面举例说明:【例2-16有如下的非线性方程组:3xa+2y3-2z-8=0xa+(x+l)y-3x+z3-5-0xz2+3bc+4yz-!0三0则利用规划求解工具求解方程蛆的解步豚如下：(1)设计工作表格，如图2-72所示；图1-72利用规划求解工具求解方程组(2)单元格E2:E4为变动单元格，存放方程组的解，其初值可设为零(空单元格)；(3)在单元格B2中输入求和公式o=3*E22+2*E32-2*E4-8w;在单元格B3中输入求和公式“=E22+(E2+1)*E3-3*E2+E4人2-5”;在单元格B4中输入求和公式=E2*E42+3*E2+4*E3*E4-IOm;(4)可以随意选取一个方程的求和作为目标函数，而其它两个方程的求和作为约束条件，这里选取方程1的求和作为目标函数，方程2和方程3的求和作为约束条件,故在单元格C2中输入目标函数公式“=B2”;(5)在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$C$2”；【等于】设置为“值为0"；【可变单元格】设置为邰E$2:$E$4"；【约束】中添加“$B$3=0"、"$B$4=0"o如有必要，还可以对“选项”的有关参数进行设置，如“迭代次数”、“精度”等,这里精度设置为IO-Ilo(7)单击【求解】，即可得到方程组的解，如图2-72所示。利用规划求解工具还可以求解一元方程的解，此时仅有一个可变单元格，方法同上。4.4 方案分析在企业的生产经营活动中，由于市场的不断变更，企业的生产销售受到各种因素的影响，企业须要估计这些因素并分析其对企业生产借售的嫩响。Excel供应了称为方案的工具来解决上述问题，利用其供应的方案管理器，可以很便利地对多种方案(即多个假设条件)进行分析。下面结合实例来说明如何运用方案管理器进行方案分析和管理。【例2-17某企业生产产品A、产储B、产品C,在2003年的销售额分别为200万元、400万元和300万元，销售成本分别为120万元、280万元和160万元。依据市场状况推想，2年产品的销售状况有好、般和差三种状况，每种状况下的销售额与销售成本的增K率如图1-73所示。123456729loll121314151617(1.»允名分小*修计rawnwmwttr产UAIKTl产ai$»*产&SU图1-73产品销售资料与预料增长率建立方案依据以上资料，建立分析方案：(1)单击工作表的任单元格，激活工作表，并设计方案计算分析格式，如图2-73所示，并在单元格G7中输入公式”=SUMPROD