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函数只有一