excel+高级教程(高级应用)

目 录 第一节 EXCEL公式及函数的高级应用 1.1 数组公式及其应用 ………………………………………………………….………3 1.1.1 数组公式的输入、编辑及删除 ………………………………………..…………3 一． 数组公式的输入 ………………………………………….….…………………3 二． 编辑数组公式………………………………………………..…..…….…………4 三． 删除数组公式…………………………………………………………..…………5 1.1.2 数组公式的应用……………………………………………………….…….……..5 一． 用数组公式计算两个数据区域的乘积………………………………….……….5 二． 用数组公式计算多个数据区域的和………………………….……...…..……….5 三． 用数组公式同时对多个数据区域进行相同的计算………………...…..……….6 1.2 常用函数及其应用………………………………………………………...….….……6 1.2.1 SUM函数、SUMIF函数和SUMPRODUCT函数 …………………….….……6 一．无条件求和SUM函数…………………………………………………….………6 二．条件求和SUMIF函数……………………………………………………..………6 三．SUMPRODUCT函数………………………………………………….…….…….7 1.2.2 AVERAGE函数……………………………………………….…….……………….7 1.2.3 MIN函数和MAX函数……………………………………….……..………..……7 1.2.4 COUNT函数和COUNTIF函数…………………………….………..………..….7 1.2.5 IF函数………………………………………………………….………..……..……8 1.2.6 AND函数、OR函数和NOT函数…………………………………………..…….8 1.2.7 LOOKUP函数、VLOOKUP函数和HLOOKUP函数……………………...…..9 一．LOOKUP函数……………………………………………………..……………….9 二．VLOOKUP函数…………………………………………………………………….9 三．HLOOKUP函数 …………………………………………..………..…….…..….10 1.2.8 MATCH函数 ……………………………………………………..……….…..…10 1.2.9 INDEX函数……………………………………………………………………...….10 一． 返回数组中指定单元格或单元格数组的数值…………………………..….…..10 二． 返回引用中指定单元格 ………………………………………..…….……...…11 1.2.10 ADDRESS函数…………………………………………………..….…………..11 1.2.11 INDIRECT函数……………………………………………..…….…………….11 1.2.12 矩阵函数——TRANSPOSE函数、MINVERSE函数和MMULT函数.….…..12 一．TRANSPOSE函数……………………………………………….…………..……12 二．MINVERSE函数………………………………………………………….….…..12 三．MMULT函数 ………………………………………………………………….…12 1.2.13 ROUND函数 ………………………………………………….……………..….12 第二节 EXCEL数据处理 2.1 数据排序………………………………………………………………………..…..13 2.1.1 数据排序的规则………………………………………………….……………..….13 2.1.2 数据排序步骤…………………………………………………….……………..….13 2.1.3 自定义排序………………………………………………………………….…..….14 2.2 数据的查找与筛选…………………………………………..…………….…..….14 2.2.1 记录单查找…………………………………………………………………..…….15 一． 查找数据记录 ……………………………………………..……………..…….15 二． 修改或删除记录………………………………………………………………….15 三． 添加新的记录………………………………………………………….…………15 2.2.2 自动筛选与自定义筛选………………………………………………………….15 一． 自动筛选…………………………………………………………….…….………15 二． 自定义筛选方式………………………………………………….….……………16 2.2.3 高级筛选…………………………………………………………….…..…………….16 一．一般情况下的高级筛选………………………………………….…..…………….17 二．计算条件情况下的高级筛选 ………………………………….….………………17 2.3 数据的分类与汇总…………………………………………….…..………………18 2.3.1 进行分类汇总………………………………………….....……………..…….……18 2.3.2 分类汇总的撤消……………………………………..……..…………..…….…….19 2.4 数据透视表………………………………………………………………..………..19 2.4.1 建立数据透视表……………………………………………………………….……19 2.4.2 数据的透视分析……………………………………..……………………..……….20 第3节​ EXCEL图表处理 3.1 图表类型…………………………………………………………………….………….21 3.2 图表的建立………………………………………………….…………….……………21 3.3 图表的编辑、修改及格式化……………………………….………….………………23 一．设置坐标、标、图例等的格式………………………………………………..23 二．改变图表大小………………………………………….…………….…………….23 三．移动或复制图表…………………………………………………………….…….23 四．添加数据标志……………………………………………………...……….….….23 五．改变图表颜色、图案、边框…………………………………………..………….24 3.4 地区销售分布图表的建立……………………………………………..……………..24 3.5 动态图表的建立……………………………………………………….…….………..25 第四节 EXCEL数据分析工具的应用 4.1 模拟运算表………………………………………………………………………….26 4.1.1 单变量模拟运算表…………………………………………………………………26 4.1.2 双变量模拟运算表……………………………………..…………………………..27 4.2 单变量求解…………………………………………….…………………………..27 4.3 规划求解…………………………………………….……………….……………..27 4.3.1 求解优化问题……………………………………….………..…………………….27 4.3.2 求解方程组 ………………………………………..………………………………29 4.4 分析………………………………………….…………………………………30 4.4.1 建立方案 ……………………………………………..…………………….………30 4.4.2 显示方案…………………………………………………………….……………….31 4.4.3 修改、删除或增加方案…………………………………………………………….31 4.4.4 建立方案报告……………………………………………………………………….31 4.5 数据分析工具库…………………………………...………………………………...32 第一节 EXCEL公式及函数的高级应用 公式和函数是Excel最基本、最重要的应用工具，是Excel的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。 1.1 数组公式及其应用 数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。在数组公式中使用两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。数组公式中的每个数组参数必须有相同数量的行和列。 1.1.1 数组公式的输入、编辑及删除 一．数组公式的输入 数组公式的输入步骤如下： (1）选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。 (2）输入数组公式。 (3）同时按“Crtl+Shift+Enter”组合键，则Excel 自动在公式的两边加上大括号{ } 。 特别要注意的是，第（3）步相当重要，只有输入公式后同时按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。否则，如果只按Enter键，则输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示出一个计算结果。 在数组公式中，通常都使用单元格区域引用，但也可以直接键入数值数组，这样键入的数值数组被称为数组常量。当不想在工作表中按单元格逐个输入数值时，可以使用这种方法。如果要生成数组常量，必须按如下操作： （1）直接在公式中输入数值，并用大括号“{ }”括起来。 （2）不同列的数值用逗号“,”分开。 （3）不同行的数值用分号“;”分开。 ★ 输入数组常量的方法： 例如，要在单元格A1:D1中分别输入10，20，30和40这4个数值，则可采用下述的步骤： （1）选取单元格区域A1:D1，如图2-1所示。 图1-1  选取单元格区域A1:D1 （2）在公式编辑栏中输入数组公式“={10,20,30,40}”，如图2-2所示。 图1-2  在编辑栏中输入数组公式 （3）同时按Ctrl+Shift+Enter组合键，即可在单元格A1、B1、C1、D1中分别输入了10、20、30、40，如图2-3所示。 假若要在单元格A1、B1、C1、D1、A2、B2、C2、D2中分别输入10、20、30、40、50、60、70、80，则可以采用下述的方法： 图1-3  同时按Ctrl+Shift+Enter组合键，得到数组常量 （1）选取单元格区域A1:D2，如图2-4所示。 图1-4  选取单元格区域A1:D2 （2）在编辑栏中输入公式“={10,20,30,40;50,60,70,80}”，如图2-5所示。 图1-5  在编辑栏中输入数组公式 （3）按Ctrl+Shift+Enter组合键，就在单元格A1、B1、C1、D1、A2、B2、C2、D2中分别输入了10、20、30、40和50、60、70、80，如图2-6所示。 图1-6  同时按Ctrl+Shift+Enter组合键，得到数组常量 ★ 输入公式数组的方法 例如，在单元格A3:D3中均有相同的计算公式，它们分别为单元格A1:D1与单元格A2:D2中数据的和，即单元格A3中的公式为“=A1+A2”，单元格B3中的公式为“=B1+B2”，…，则可以采用数组公式的方法输入公式，方法如下： （1）选取单元格区域A3:D3，如图2-7所示。 （2）在公式编辑栏中输入数组公式“=A1:D1+A2:D2”，如图2-8所示。 图1-7  选取单元格区域A3:D3 图1-8  在编辑栏中输入数组公式 （3）同时按Ctrl+Shift+Enter组合键，即可在单元格A3:D3中得到数组公式“=A1:D1+A2:D2”，如图2-9所示。 图1-9  同时按Ctrl+Shift+Enter组合键，得到数组公式 二．编辑数组公式 数组公式的特征之一就是不能单独编辑、清除或移动数组公式所涉及的单元格区域中的某一个单元格。若在数组公式输入完毕后发现错误需要修改，则需要按以下步骤进行： （1）在数组区域中单击任一单元格。 （2）单击公式编辑栏，当编辑栏被激活时，大括号“{ }”在数组公式中消失。 （3）编辑数组公式内容。 （4）修改完毕后，按“Crtl+Shift+Enter”组合键。要特别注意不要忘记这一步。 三．删除数组公式 删除数组公式的步骤是：首先选定存放数组公式的所有单元格，然后按Delete键。 1.1.2 数组公式的应用 一．用数组公式计算两个数据区域的乘积 【例2-1】如图2-10所示，已经知道12个月的销售量和产品单价，则可以利用数组公式计算每个月的销售额，步骤如下： 图1-10  用数组公式计算销售额 （1）选取单元格区域B4:M4。 （2）输入公式“=B2:M2*B3:M3”。 （3）按“Crtl+Shift+Enter”组合键。 如果需要计算12个月的月平均销售额，可在单元格B5中输入公式“=AVERAGE(B2:M2*B3:M3)”，然后按“Crtl+Shift+Enter”组合键即可，如图2-10所示。 在数组公式中，也可以将某一常量与数组公式进行加、减、乘、除，也可以对数组公式进行乘幂、开方等运算。例如在图2-10中，每月的单价相同，故我们也可以在单元格B4:M4中输入公式“=B2:M2*28”，然后按“Crtl+Shift+Enter”组合键；在单元格B5中输入公式“=AVERAGE(B2:M2*28)”，然后按“Crtl+Shift+Enter”组合键。 在使用数组公式计算时，最好将不同的单元格区域定义不同的名称，如在图2-10中，将单元格区域B2:M2定义名称为“销售量”，单元格区域B3:M3定义名称为“单价”，则各月的销售额计算公式为“=销售量*单价”，月平均销售额计算公式为“=AVERAGE(销售量*单价)”，这样不容易出错。 二．用数组公式计算多个数据区域的和 如果需要把多个对应的行或列数据进行相加或相减的运算，并得出与之对应的一行或一列数据时，也可以使用数组公式来完成。 【例2-2】某企业2002年销售的3种产品的有关资料如图2-11所示，则可以利用数组公式计算该企业2002年的总销售额，方法如下： 图1-11  某企业的月销售总额计算 （1）选取单元格区域C8:N8。 （2）输入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。 （3）按“Crtl+Shift+Enter”组合键。 三．用数组公式同时对多个数据区域进行相同的计算 【例2-3】某公司对现有三种商品实施降价销售，产品原价如图2-12所示，降价幅度为20%，则可以利用数组公式进行计算，步骤如下： 图1-12  产品降价计算 （1）选取单元格区域G3:I8。 （2）输入公式“=B3:D8*(1-20%)”。 （3）按Crtl+Shift+Enter组合键。 此外，当对结构相同的不同工作表数据进行合并汇总处理时，利用上述方法也将是非常方便的。有关不同工作表单元格的引用可参阅第1章的有关内容，关于数据的合并计算可参阅本章2.3.5节的内容。 1.2 常用函数及其应用 在第1节中介绍了一些有关函数的基本知识，本节对在财务管理中常用的一般函数应用进行说明，其他有关的专门财务函数将在以后的有关章节中分别予以介绍。 1.2.1 SUM函数、SUMIF函数和SUMPRODUCT函数 在财务管理中，应用最多的是求和函数。求和函数有三个：无条件求和SUM函数、条件求和SUMIF函数和多组数据相乘求和SUMPRODUCT函数。 一．无条件求和SUM函数 该函数是求30个以内参数的和。公式为 = SUM（参数1，参数2，…，参数N） 当对某一行或某一列的连续数据进行求和时，还可以使用工具栏中的自动求和按钮 。 例如，在例2-1中，求全年的销售量，则可以单击单元格N2，然后再单击求和按钮 ，按回车键即可，如图1-13所示。 图1-13  自动求和 二．条件求和SUMIF函数 SUMIF函数的功能是根据指定条件对若干单元格求和，公式：=SUMIF(range,criteria,sum_range) 式中 range—用于条件判断的单元格区域； criteria—确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本； sum_range—需要求和的实际单元格。 只有当range中的相应单元格满足条件时，才对 sum_range 中的单元格求和。若省略 sum_range,则直接对 range 中的单元格求和。 利用这个函数进行分类汇总是很有用的。 【例1-4】某商场2月份销售的家电流水记录如图1-14所示，则在单元格I3中输入公式“=SUMIF(C3:C10,211,F3:F10)”，单元格I4中输入公式“=SUMIF(C3:C10,215,F3:F10)”，在单元格I5中输入公式“=SUMIF(C3:C10,212,F3:F10)”，单元格I6中输入公式“=SUMIF(C3:C10,220,F3:F10)”，即可得到分类销售额汇总表。 图1-14  商品销售额分类汇总 SUMIF函数的对话框如图1-15所示。 图1-15  SUMIF函数对话框 当需要分类汇总的数据很大时，利用SUMIF函数是很方便的。 三．SUMPRODUCT函数 SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 公式为 = SUMPRODUCT(array1,array2,array3,…) 式中，array1,array2,array3,...为1至30个数组。 需注意的是，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。对于非数值型的数组元素将作为0处理。 例如，在例1-2中，要计算2002年产品A的销售总额，可在任一单元格（比如O2）中输入公式“=SUMPRODUCT(C2:N2,C3:N3)”即可。 1.2.2 AVERAGE函数 AVERAGE函数的功能是计算给定参数的算术平均值。 公式为 = AVERAGE（参数1，参数2，…，参数N） 函数中的参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。 AVERAGE函数的使用方法与SUM函数相同，此处不再介绍。 1.2.3 MIN函数和MAX函数 MIN函数的功能是给定参数表中的最小值，MAX函数的功能是给定参数表中的最大值。公式为 = MIN（参数1，参数2，…，参数N） = MAX（参数1，参数2，…，参数N） 函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。 例如，MIN（3，5，12，32）=3；MAX（3，5，12，32）=32。 1.2.4  COUNT函数和COUNTIF函数 COUNT函数的功能是计算给定区域内数值型参数的数目。 公式为： = COUNT（参数1，参数2，…，参数N） COUNTIF函数的功能是计算给定区域内满足特定条件的单元格的数目。 公式为： = COUNTIF（range，criteria） 式中　range—需要计算其中满足条件的单元格数目的单元格区域； criteria—确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。 1.2.5  IF函数 IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数IF对数值和公式进行条件检测。 公式为 = IF(logical_test,value_if_true,value_if_false) 式中  logical_test—条件表达式，其结果要么为 TRUE，要么为 FALSE，它可使用任何比较运算符； value_if_true—logical_test 为 TRUE 时返回的值； value_if_false—logical_test 为 FALSE 时返回的值。 IF函数在财务管理中具有非常广泛的应用。 【例2-5】例如，某企业对各个销售部门的销售业绩进行评价，评价及各个销售部门在2002年的销售业绩汇总如图1-16所示，评价计算步骤如下： 图1-16  销售部门业绩评价 （1）选定单元格区域C3:C12。 （2）直接输入以下公式：“=IF(B3:B12<100000,"差",IF(B3:B12<200000,"一般",IF(B3:B12<300000,"好",IF(B3:B12<400000,"较好","很好"))))”。 （3）按“Crtl+Shift+Enter”组合键。 则各个销售部门的销售业绩评价结果就显示在单元格域C3:C12中。 也可以直接在单元格C3中输入公式“=IF(B3<100000,"差",IF(B3<200000,"一般",IF(B3<300000,"好",IF(B3<400000,"较好","很好"))))”后，将其向下填充复制到C4～C12单元格中。 1.2.6 AND函数、OR函数和NOT函数 这3个函数的用法如下： = AND（条件1，条件2，…，条件N） = OR（条件1，条件2，…，条件N） = NOT（条件） AND函数表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），AND函数返回TRUE，否则，只要有一个条件不满足即返回FALSE。 OR函数表示逻辑或，只要有一个条件满足时，OR函数返回TRUE，只有当所有条件都不满足时才返回FALSE。 NOT函数只有一个逻辑参数，它可以计算出TRUE或FALSE的逻辑值或逻辑表达式。如果逻辑值为 FALSE，函数 NOT 返回 TRUE；如果逻辑值为 TRUE，函数 NOT 返回FALSE。 这3个函数一般与IF函数结合使用。 【例2-6】某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，若销售额大于300000元且销售费用占销售额的比例不超过1%，则奖金提取比例为15%，否则为10%，则计算过程如下（如图2-17所示）： （1）在单元格D3中输入公式“=IF(AND(B3>300000,C3/B3<1%),15%,10%)”，将其向下填充复制到D4～C10单元格中。 （2）选取单元格区域E3:E10，输入公式“=B3:B10*D3:D10”，按“Crtl+Shift+Enter”组合键。 则各销售部门的销售奖金提成比例及奖金提取额如图1-17所示。 图1-17  奖金提成比例及提取额的计算 1.2.7 LOOKUP函数、VLOOKUP函数和HLOOKUP函数 一．LOOKUP函数 LOOKUP函数的功能是返回向量（单行区域或单列区域）或数组中的数值。函数 LOOKUP 有两种语法形式：向量和数组。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。 （1）向量形式：公式为 = LOOKUP(lookup_value,lookup_vector,result_vector) 式中　lookup_value—函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用； lookup_vector—只包含一行或一列的区域lookup_vector 的数值可以为文本、数字或逻辑值； result_vector—为只包含一行或一列的区域其大小必须与 lookup_vector 相同。 （2）数组形式：公式为 = LOOKUP(lookup_value,array) 式中　array—包含文本、数字或逻辑值的单元格区域或数组它的值用于与 lookup_value 进行比较。 例如：LOOKUP(5.2,{4.2,5,7,9,10})=5。 注意：lookup_vector的数值必须按升序排列，否则函数LOOKUP不能返回正确的结果。文本不区分大小写。如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。 二．VLOOKUP函数 VLOOKUP函数的功能是在或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为： = VLOOKUP（lookup_value，table_array，col_index_num，range_lookup） 式中　lookup_value—需要在数据表第一列中查找的数值，lookup_value 可以为数值、引用或文字串； table_array—需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，例如数据库或数据清单； 如果range_lookup为TRUE，则table_array的第一列中的数值必须按升序排列，否则函数VLOOKUP不能返回正确的数值，如果range_lookup为FALSE，table_array不必进行排序。table_array的第一列中的数值可以为文本、数字或逻辑值，且不区分文本的大小写； col_index_num—table_array中待返回的匹配值的列序号； col_index_num为1时，返回table_array第一列中的数值；col_index_num为2时，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数VLOOKUP返回错误值#VALUE!；如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!。 range_lookup—逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。 如果其为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。 VLOOKUP函数在财务管理与分析中是一个经常用到的函数，因此熟悉它将会带来很大便利。在以后的有关章节中会经常用到它。 例如，假设单元格A1:A4中的数据分别为1、30、80和90，单元格B1:B4中的数据分别为400、500、600和700，则有：VLOOKUP(5,A1:B4,2)=400，VLOOKUP(30,A1:B4,2)=500，VLOOKUP(79,A1:B4,2)=500，VLOOKUP(92,A1:B4,2)=700。 三．HLOOKUP函数 HLOOKUP函数的功能是从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。公式为： = (lookup_value，table_array，row_index_num，range_lookup) 式中  row_index_num—table_array中待返回的匹配值的行序号。 row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。如果row_index_num小于1，函数HLOOKUP返回错误值 #VALUE!；如果row_index_num大于table_array的行数，函数HLOOKUP返回错误值#REF!。 式中的其他参数含义参阅VLOOKUP函数。 HLOOKUP函数与VLOOKUP函数的区别是：当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP；当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP。VLOOKUP函数在首列进行检索，先得到的是行号，然后根据col_index_num参数指定的列标返回指定的单元格数值；而HLOOKUP函数在首行进行检索，先得到的是列标，然后根据row_index_num参数指定的行号返回指定的单元格数值。 1.2.8  MATCH函数 MATCH函数的功能是返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为： = MATCH(lookup_value,lookup_array,match_type) 式中  lookup_value—需要在数据表中查找的数值，可以是数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用； lookup_array—可能包含所要查找的数值的连续单元格区域，可以是数组或数组引用； match_type—数字-1、0或1，它指明Excel如何在lookup_array中查找lookup_value。 查找方式如下：当match_type为-1时，lookup_array必须按降序排列，函数MATCH查找大于或等于lookup_value的最小数值；当match_type为0时，lookup_array可以按任何顺序排列，函数MATCH 查找等于lookup_value的第一个数值；当match_type为1时，lookup_array必须按升序排列，函数MATCH查找小于或等于lookup_value的最大数值。 例如，MATCH(12,{23,43,12,55},0)=3，MATCH(40,{23,43,12,55})=1。 EXCEL公式及函数的高级应用(5) 1.2.9 INDEX函数 INDEX函数的功能是返回表格或区域中的数值或对数值的引用。INDEX函数有以下两种形式： 1.​  返回数组中指定单元格或单元格数组的数值。 公式为 = INDEX(array,row_num,column_num)   式中  array—单元格区域或数组常数； row_num—数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有 column_num； column_num—数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有 row_num。 需要注意的是：如果同时使用 row_num 和 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格的数值。如果数组只包含一行或一列，则相对应的参数row_num 或column_num为可选。如果数组有多行和多列，但只使用row_num 或column_num，函数INDEX返回数组中的整行或整列，且返回值也为数组。如果将row_num或column_num设置为0，函数INDEX则分别返回整个列或行的数组数值。如果需要使用以数组形式返回的数值时，请在一个水平单元格区域中将函数INDEX作为数组公式输入。此外，row_num和column_num必须指向array中的某一单元格，否则，函数INDEX返回错误值#REF!。 例如：INDEX({1,2;3,4},2,2) = 4。如果作为数组公式输入，则：INDEX({1,2;3,4},0,2) = {2;4} 2.​  返回引用中指定单元格。 公式为： INDEX(reference,row_num,column_num,area_num) 式中  reference—对一个或多个单元格区域的引用； 如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num或column_num分别为可选项。例如，对于单行的引用，可以使用函数INDEX(reference,column_num)。 row_num—引用中某行的行序号，函数从该行返回一个引用； column_num—引用中某列的列序号，函数从该列返回一个引用； area_num—选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，函数INDEX使用区域1。 说明：row_num、column_num和area_num必须指向reference中的单元格,否则，函数INDEX返回错误值#REF!。如果省略row_num和column_num，函数INDEX返回由area_num所指定的区域。 函数INDEX的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数INDEX的返回值可以作为引用或是数值。例如，公式 CELL("width",INDEX(A1:B2,1,2))等价于公式CELL("width",B1)。CELL函数将函数INDEX的返回值作为单元格引用。而在另一方面，公式2*INDEX(A1:B2,1,2)将函数INDEX的返回值解释为B1单元格中的数字。 1.2.10  ADDRESS函数 ADDRESS函数的功能是按照给定的行号和列标，建立文本类型的单元格地址。公式为 = ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 式中  row_num—在单元格引用中使用的行号； column_num—在单元格引用中使用的列标； abs_num—指明返回的引用类型，其中：当为1或省略时为绝对引用，当为2时为绝对行号，相对列标，当为3时为相对行号，绝对列标，当为4时为相对引用； a1—用以指明A1或R1C1引用样式的逻辑值。如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用，如果A1为FALSE，函数ADDRESS返回R1C1样式的引用； sheet_text—一文本，指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。 例如，ADDRESS(2,3)等于“$C$2”；ADDRESS(2,3,2)等于“C$2”。 1.2.11  INDIRECT函数 INDIRECT函数的功能是返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用此函数。 公式为： = INDIRECT(ref_text,a1) 式中  ref_text—对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文字串单元格的引用，如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!； a1——逻辑值，指明包含在单元格ref_text中的引用的类型，如果a1为TRUE或省略，ref_text被解释为A1样式的引用，如果a1为FALSE，ref_text被解释为R1C1样式的引用。 需要注意的是，如果ref_text是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数INDIRECT返回错误值 #REF!。 例如：如果单元格A1包含文本"B2"，且单元格B2包含数值1.333，则：INDIRECT($A$1)=1.333。 上述介绍的几个查找函数LOOKUP、VLOOKUP、HLOOKUP、MATCH、INDEX、ADDRESS、INDIRECT等在财务分析与决策、预测及建立动态图表等中是非常有用的。 1.2.12 矩阵函数——TRANSPOSE函数、MINVERSE函数和MMULT函数 一．TRANSPOSE函数 TRANSPOSE函数的功能是求矩阵的转置矩阵。 公式为 = TRANSPOSE(array) 式中，Array—需要进行转置的数组或工作表中的单元格区域。 函数TRANSPOSE必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。 【例1-7】假设矩阵A中的值如图1-18中单元格区域A2:C5，求其转置矩阵的步骤如下： 图1-18  求转置矩阵 （1）选取存放转置矩阵结果的单元格区域，如E2:H4。 （2）单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数TRANSPOSE，在该函数对话框中输入（可用鼠标拾取）单元格A2:C5，按“Crtl+Shift+Enter”组合键，即得转置矩阵如图2-18所示。 利用TRANSPOSE函数可以把工作表中的某些行（或列）排列的数据转换成列（或行）排列的数据。例如，由于工作需要，要把工作表中的某些行数据改为列数据，若一个一个地改动数据，将是很麻烦也很费时的，而利用TRANSPOSE函数则可以很轻松地进行这项工作。但需要注意的是，利用TRANSPOSE函数对行（列）数据进行转换，则无法单独修改其中转换单元格区域中的某单元格的数据。 二．MINVERSE函数 MINVERSE函数的功能是返回矩阵的逆矩阵。公式为 = MINVERSE(array) 式中，array—具有相等行列数的数值数组或单元格区域。 MINVERSE函数的使用方法与TRANSPOSE函数是一样的。在求解线性方程组时，常常用到MINVERSE函数。 三．MMULT函数 MMULT函数的功能是返回两数组的矩阵乘积。结果矩阵的行数与 array1 的行数相同，列数与 array2 的列数相同。 公式为 = MMULT(array1,array2) 式中  array1, array2—要进行矩阵乘法运算的两个数组。 array1的列数必须与 array2 的行数相同，而且两个数组中都只能包含数值。array1和array2可以是单元格区域、数组常数或引用。如果单元格是空白单元格或含有文字串，或是array1的行数与 array2 的列数不相等时，则函数MMULT返回错误值#VALUE!。 同样地，由于返回值为数组公式，故必须以数组公式的形式输入。 以例1-7的原矩阵和其转置矩阵为例，它们的乘积矩阵求解方法如下： （1）选取存放乘积矩阵结果的单元格区域，如J2:L5。 （2）单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数MMULT，在该函数对话框中的array1栏中输入（可用鼠标拾取）单元格区域A2:C5，在array2栏中输入单元格区域E2:H4，然后按“Crtl+Shift+Enter”组合键，即得矩阵的乘积如图2-18所示。 1.2.13  ROUND函数 ROUND函数的功能是返回某个数字按指定位数舍入后的数字。 公式为 = ROUND(number,num_digits) 式中  number—需要进行舍入的数字； num_digits—指定的位数，按此位数进行舍入。 如果num_digits大于0，则舍入到指定的小数位；如果num_digits等于0，则舍入到最接近的整数；如果num_digits小于0，则在小数点左侧进行舍入。 利用ROUND函数可以防止利用格式工具栏上的【增加小数位数】或【减少小数位数】所带来的看起来“假数据”问题的出现，使得工作表上显示的数据真实可靠。实际上，如果需要调整数据的小数位数，最好使用ROUND函数，而不要使用格式工具栏上的【增加小数位数】或【减少小数位数】按钮。 例如，若单元格A1中的数据为14.3772，若使用格式工具栏上的【减少小数位数】按钮将小数位数设为两位，则单元格A1中的数据显示为14. 38，看起来似乎单元格A1的数据为14.38，但实际上仍为14.3772。若在单元格B1中输入公式“=3*A1”，则单元格B1中的数据显示为43.13，也许“不明真相”的人认为单元格B1的数据算错了（14.38乘以3应该等于43.14），但实际上单元格的数据为43.1316，这种看起来的“假”数据可能会对实际工作带来不便。因此，正确的方法应是：单元格B1中应输入公式“=ROUND(3*ROUND(A1,2),2)”，结果为43.14，即先将单元格A1的数据用函数ROUND四舍五入，然后再对计算后的数据四舍五入。 第2节​ EXCEL数据分析处理 Excel提供了强大的数据分析处理功能，利用它们可以实现对数据的排序、分类汇总、筛选及数据透视等操作。 在进行数据分析处理之前，首先必须注意以下几个问题： （1）避免在数据清单中存在有空行和空列。 （2）避免在单元格的开头和末尾键入空格。 （3）避免在一张工作表中建立多个数据清单，每张工作表应仅使用一个数据清单。 （4）工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。 （5）关键数据应置于数据清单的顶部或底部。 2.1 数据排序 2.1.1 数据排序的规则 Excel允许对字符、数字等数据按大小顺序进行升序或降序排列，要进行排序的数据称之为关键字。不同类型的关键字的排序规则如下： 数值：按数值的大小。 字母：按字母先后顺序。 日期：按日期的先后。 汉字：按汉语拼音的顺序或按笔画顺序。 逻辑值：升序时FALSE排在TRUE前面，降序时相反。 空格：总是排在最后。 2.1.2 数据排序步骤 （1）单击数据区中要进行排序的任意单元格。 （2）单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话框，如图1-35所示。 图1-35  【排序】对话框 （3）在【排序】对话框中用下拉列表框选择要排序的关键字，关键字有“主要关键字”、“次要关键字”和“第三关键字”，根据需要分别选择不同的关键字； （4）单击【确定】按钮，数据就按要求进行了排序。 当只有一个关键字时，可以单击工具栏上的升序按钮 或降序按钮 ，进行自动排序。 2.1.3 自定义排序 在有些情况下，对数据的排序顺序可能非常特殊，既不是按数值大小次序、也不是按汉字的拼音顺序或笔画顺序，而是按照指定的特殊次序，如对总公司的各个分公司按照要求的顺序进行排序，按产品的种类或规格排序等等，这时就需要自定义排序。 利用自定义排序方法进行排序，首先应建立自定义序列，其方法可参阅第1章的有关内容。建立好自定义序列后，即可对数据进行排序，方法是：单击数据区中要进行排序的任意单元格，单击【数据】菜单，选择【排序】项，在弹出的【排序】对话框中单击【选项】按钮，系统弹出【排序选项】对话框，如图1-36所示，在【自定义排序次序】的下拉列表中，选择前面建立的自定义序列，然后单击【确定】按钮，即可对数据进行自定义排序。 图1-36  【排序选项】对话框 2.2 数据的查找与筛选 企业的管理人员经常需要在数据库或数据清单众多的数据中找出需要的数据，Excel提供了功能强大的数据查找与筛选工具。数据查找是指从原始数据中提取满足条件的数据记录，源数据不会改变，也不会被隐藏；数据筛选是指把数据库或数据清单中所有不满足条件的数据记录隐藏起来，只显示满足条件的数据记录。常用的数据查找与筛选方法有：记录单查找、自动筛选和高级筛选。 下面结合实例说明各种查找方法的具体应用。 【例2—11】图2-37为某公司的部分商品销售记录清单。 图2-37  某公司的商品销售明细清单 根据图2-37中的有关资料，可以分别采用记录单查找、自动筛选或高级筛选的方式查找或选择所需要的信息，如下所述： 2.2.1 记录单查找 记录单是查找和编辑数据的最简单的方法，利用记录单，不仅可以查找数据记录，还可以修改和删除记录、添加新的数据记录等。 一．查找数据记录 利用记录单查找数据记录的步骤如下： （1）用鼠标单击数据清单或数据库中的任一非空单元格。 （2）单击【数据】菜单，选择【记录单】项，则系统弹出如图1-38所示的记录单。 图1-38  记录单 （3）单击记录单中的【条件】按钮，则弹出记录单条件对话框，如图1-39所示。 图1-39  记录单条件对话框 （4）输入条件，比如要查找“张三”的销售记录，则在【销售人员】栏中输入“张三”，然后单击【上一条】按钮或【下一条】按钮，系统就逐次显示满足条件的记录行。 还可以使用多个条件联合查找记录，此处不再叙述。 二．修改或删除记录 在图2-38所示的记录单中，即可对某一记录的各字段进行修改。若要删除显示的记录，只需单击记录单上的【删除】按钮即可。 三．添加新的记录 在图2-38所示的记录单中，单击记录单上的【新建】按钮，则出现各字段均为空白的新建记录单，在记录单中输入各字段的值，输入完毕后，单击【新建】按钮，即完成添加新记录。 2.2.2 自动筛选与自定义筛选 一．自动筛选 记录单检索数据每次只能显示一个数据行，当查询的数据较多，或要把查询的结果汇总成表时，就需要使用筛选工具了。自动筛选提供了快速检索数据清单或数据库的方法，通过简单的操作，就能筛选出需要的数据。利用自动筛选查找数据的步骤如下： （1）用鼠标单击数据清单或数据库中的任一非空单元格。 （2）单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中选择【自动筛选】，则系统自动在数据清单的每列数据的标题旁边添加一个下拉列标标志，如图1-40所示。 图1-40  自动筛选的下拉列表标志 （3）单击需要筛选的下拉列表，系统显示出可用的筛选条件，从中选择需要的条件，即可显示出满足条件的所有数据。例如，要查找所有彩电的销售记录，单击“商品”右边的下拉列表，从中选择“彩电”项，则所有的彩电销售记录就显示出来，而其他的数据则被隐藏，如图1-41所示。 图1-41  彩电销售清单的筛选结果 如果有关彩电的销售记录很多，超过了10个，当需要只显示10个记录时，可单击“单价”、“数量”、“金额”等右边的下拉列表中的“前10个”项，系统弹出【自动筛选前10个】对话框，如图2-42所示。这里，在【显示】下拉列表中“最大”表示最大（最好）的前10个记录，“最小”表示最小（最差）的前10个记录。中间的编辑框中的数值表示显示的记录行数，系统默认值为10，但可以修改，根据需要输入数值即可。 图1-42  【自动筛选前10个】对话框 若要恢复所有的记录，则单击“商品”右边的下拉列表中的“全部”项。若要取消【自动筛选】状态，则单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中再次选择【自动筛选】。 二．自定义筛选方式 当在图1-40所示的下拉列表中选择“自定义”项时则会弹出【自定义自动筛选方式】对话框，如图1-43所示，用户可根据具体条件对各栏进行设置。如要查找销售金额大于或等于“150000”且小于或等于“200000”的所有记录，则单击左上角的下拉箭头，选择“大于或等于”，右上角的条件值输入“150000”，单击左下角的下拉箭头，选择“小于或等于”，右下角的条件值输入“200000”，单击【确定】按钮，并选择“与”条件，则满足这些条件的所有记录就显示出来了，如图1-44所示。 图1-43  【自定义自动筛选方式】对话框 图1-44  【自定义自动筛选方式】筛选的结果 2.2.3 高级筛选 高级筛选可以使用较多的条件来对数据清单进行筛选，这些条件既可以是与条件，也可以是或条件，或与条件，与或条件的组合使用，还可以使用计算条件。 一．一般情况下的高级筛选 利用高级筛选对数据清单进行筛选的步骤如下： （1）首先应建立一个条件区域。在条件区域中，同一行中的条件是与条件，也就是这些条件必须同时满足；不同行中的条件是或条件，也就是这些条件只要满足其一即可。如需要查找张三销售彩电的所有记录，则建立条件区域如图1-45所示。 图1-45  建立条件区域 （2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【筛选】子菜单中的【高级筛选】项，则系统弹出如图1-46所示的【高级筛选】对话框。 图1-46  【高级筛选】对话框 （3）一般情况下，系统将自动给出了数据区域，用户只需在【条件区域】栏中输入条件区域（本例中为B19:C20，也可以用鼠标拾取单元格区域，此时在条件区域中将显示“销售明细清单!$B$19:$C$20”。 （4）高级筛选结果可以显示在数据清单的原有区域中，也可以显示在工作表的其他空白单元格区域，系统默认的方式是在数据清单的原有区域中显示结果。若需要在工作表的其他空白单元格区域显示结果，则