excel 高级教程(高级应用).doc

Excel高级应用教程
目 录
第一节 EXCEL公式及函数得高级应用 1
1、1 数组公式及其应用 1
1、1、1 数组公式得输入、编辑及删除 1
一.数组公式得输入 1
二.编辑数组公式 2
三.删除数组公式 3
1、1、2 数组公式得应用 3
一.用数组公式计算两个数据区域得乘积 3
二.用数组公式计算多个数据区域得与 3
三.用数组公式同时对多个数据区域进行相同得计算 3
1、2 常用函数及其应用 4
1、2、1 SUM函数、SUMIF函数与SUMPRODUCT函数 4
一.无条件求与SUM函数 4
二.条件求与SUMIF函数 4
三.SUMPRODUCT函数 5
1、2、2 AVERAGE函数 5
1、2、3 MIN函数与MAX函数 5
1、2、4  COUNT函数与COUNTIF函数 5
1、2、5  IF函数 6
1、2、6 AND函数、OR函数与NOT函数 6
1、2、7 LOOKUP函数、VLOOKUP函数与HLOOKUP函数 7
一.LOOKUP函数 7
二.VLOOKUP函数 7
三.HLOOKUP函数 8
1、2、8  MATCH函数 8
1、2、9 INDEX函数 8
一、返回数组中指定单元格或单元格数组得数值。 8
二、返回引用中指定单元格。 9
1、2、10  ADDRESS函数 9
1、2、11  INDIRECT函数 9
1、2、12 矩阵函数——TRANSPOSE函数、MINVERSE函数与MMULT函数 10
一.TRANSPOSE函数 10
二.MINVERSE函数 10
三.MMULT函数 10
1、2、13  ROUND函数 10
第二节 EXCEL数据分析处理 11
2、1 数据排序 11
2、1、1 数据排序得规则 11
2、1、2 数据排序步骤 11
2、1、3 自定义排序 12
2、2 数据得查找与筛选 12
2、2、1 记录单查找 13
一.查找数据记录 13
二.修改或删除记录 13
三.添加新得记录 14
2、2、2 自动筛选与自定义筛选 14
一.自动筛选 14
二.自定义筛选方式 14
2、2、3 高级筛选 15
一.一般情况下得高级筛选 15
二.计算条件情况下得高级筛选 16
2、3 数据得分类与汇总 16
2、3、1 进行分类汇总 16
2、3、2 分类汇总得撤消 17
2、4  数据透视表 17
2、4、1 建立数据透视表 17
2、4、2  数据得透视分析 19
第三节 EXCEL图表处理 19
3、1 图表类型 19
3、2  图表得建立 19
3、3 图表得编辑、修改及格式化 21
一.设置坐标、标题、图例等得格式 21
二.改变图表大小 21
三.移动或复制图表 21
四.添加数据标志 21
五.改变图表颜色、图案、边框 22
3、4  地区销售分布图表得建立 22
3、5  动态图表得建立 24
第四节 EXCEL数据分析工具得应用 24
4、1 模拟运算表 24
4、1、1 单变量模拟运算表 24
4、1、2 双变量模拟运算表 25
4、2 单变量求解 25
4、3  规划求解 25
4、3、1  求解优化问题 26
4、3、2  求解方程组 27
4、4 方案分析 28
4、4、1  建立方案 28
4、4、2  显示方案 29
4、4、3  修改、删除或增加方案 29
4、4、4  建立方案报告 30
4、5  数据分析工具库 30
第一节 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函数有以下两种形式:
一、返回数组中指定单元格或单元格数组得数值。
公式为 = 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}
二、返回引用中指定单元格。
公式为: 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,也许“不明真相