Excel函数——查找和引用函数

1、第5章查找和引用函数在Excel中，查找和引用函数的主要功能是查询各种信息。在数据量很大的工作表中,Excel的查找和引用函数能起到很大的作用。在实际应用中，查找和引用函数会和其他类 型的函数一起综合应用，完成复杂的查找或者定位。在本章中，将根据函数的性质，分为 查找和引用函数两个小节详细讲解。5.1查找函数查找函数的主要功能是快速的确定和定位所需要的信息。这类函数的主要功能是检 索：根据实际需要，在工作表或者在多个工作薄中获取需要的信息或者数据。在本小节中,将详细讲解各个函数的功能。5.1.1 ADDRESS函数：返回引用地址【功能说明】该函数根据给定的行号和列号，返回某一个具体的单元格的地

2、址。【语法表达式】 ADDRESS(row-num ， column-num ， abs-num, al， sheet-text)【参数说明】zRow-num :代表行号，表示单元格在哪一行的数字。例如，单元格D2，就表示其在第2行。z Column-num :代表列标号，表示单元格在哪一列的数字。例如，单元格D2，就表示其在D列。z Abs-num :代表引用类型。使用函数时，其值可以是1、2、3、4中的任意一个值。 该参数也可以省略。如果省略该参数，系统将其默认为1。数字和其代表的引用类型的关系，如表 5.1所示。表5.1数字和代表类型数字引用类型1代表绝对引用2代表绝对行号，相对列标3代

3、表相对行号，绝对列标4代表相对引用z A1 ：代表引用样式的逻辑值。如果参数a1为TRUE或者省略，那么函数将返回a1样式的引用；如果 a1为FALSE，那么函数将返回 R1C1样式的引用。zSheet-text :代表一文本，指定作为外部引用时的工作表名称。如果省略该参数，那么表示不使用任何工作表名。【使用说明】如果参数a1是指定的四个数字之外的任意数字，函数都将返回错误值#value!。【实际应用】某公司组织员工进行某种游戏，现在有员工编号和游戏序号。根据最后得奖序号来判 断员工编号的单元格。在单元格E2中输入公式“ =ADDRESS(3,1,1) ”，得到得奖员工编号所在单元格，如 图5

4、.1所示。E2ABcDE1灵工占号眈甲元帖111In5A531112nU3ttt114115V11&VI117vn邑=ADDR£SS（3,1 1）査比印电罔因？:皿加j Microsoft EXC(ICbt胡7fl3 iS人，文！)布，好娠诵烏砸抨发工JE戴現冲_ -阿(3 凹卫®图5.1获取得奖单元格【应用说明】在 Excel中，引用类型有绝对引用、相对引用、混合引用三种类型。引用样式有al和R1C1两种。5.1.2 AREAS函数：返回区域个数【功能说明】给定一个引用，利用AREAS函数计算该引用中包含的区域的个数。该引用区域可以是连续的单元格区域，也可以是某个

5、具体的单元格。【语法表达式】 AREAS(refere nee)【参数说明】Refere nee：代表对单元格或单元格区域的引用。使用函数是，其值可是 是单个单元格，也可以是单元格区域。如果需要引用多个不连续的单元格区域，那么需要 用括号把各引用区域引起来。【使用说明】在引用多个不连续的单元格区域时，一定要用大括号把引用区域括起来。【实际应用】某总公司将各个不同部门的销量统计在不同的单元格范围内，现在需要统计公司所有 的区域总数。在单元格 B2 中输入“ =AREAS(A4:B11,D4:E11,A15:B21,D14:E21,G14:H21)”，计算的区域总数结果如图 5.2所示。d 哼 ”

6、 z二aCfltyiWIWtiiii. - t/tvatA E)«l- M x开因畝卯悴馬炊砌丰阖贬开发工皿座脅” D *E1”J-S (A4 rBl L D4; El 1, AI5; B21 n DU; EZ1, G14;HZ1)»ACDEFGHI-1_5_1员a号TAfifiI!S 号1113M2H30011245(2L21136702U65Q114B7U214A301152153 ID1164TO卽临450117530217510西郃中雨郃肛齢梢蛍员工齢梢童貝工编号J11Ml340511312咄号m47D5124帥11341369D513710J146B5砒A9Q51

7、491031535415卿51537011G4-Q541651651011755417S50517570H 4 4 h AUDKESS.速喜；CfcKiQSE 疚吐Uffi辭叭 如脚删_ uw少H业iimuZpo做.勺？ 、也，盘图5.2返回区域个数【应用说明】单元格也是小区域，因此AREAS函数也可以统计单元格的个数。该函数还可以应用到经济销售的其他领域，用来统计销售项目个数或者销售区域总数。5.1.3 CHOOSE函数：从列表中选择数值【功能说明】该函数利用给定的参数值，返回数值参数列表中的一个值。列表中的数 值个数介于1-29这29个数值之间。【语法表达式】CHOOSE(index-nu

8、m,value1,value2 )【参数说明】zIndex-num :用来指定所选定的数值参数，其中Index-num的参数必须是介于 1-29之间的数字表达式或者单元格字段。这里包括了三种情况：(1 )当Index-num的值为1时，CHOOSE函数会返回 Value1的值；为2时，会返 回Value2的值；依次类推。(2) 当Index-num的值是一个小数，那么按四舍五入取整后，再返回(1)。(3) 当Index-num的值小于1或者大于列表中最后一个值的序列号，函数将返回 一个错误值#VALUE !。z Value1, value2为1到29个数值参数。函数根据Index-num提供的

9、参数值，从中选择一个数值或者一项要执行的操作。这些数值参数可以是数值表达式、列 表、公式、单元格引用、定义名称、函数或者文本。【使用说明】zIndex-num中的参数可以是数值表达式，也可以是字段，其运算结果是一个介于1和29之间数值。如果Index-num是一个数组，则在计算时，每一个值都会参与计算。z数值参数间需要用逗号隔开。【实际应用】某小公司中包含了多个员工的薪酬信息，根据不同的工资等级，公司需要为这些员工 选取不同的奖金比例等级。在单元格C2中输入函数表达式 “ =CHOOSE(INT(B2/3000),"4%","8%","12%&

10、quot;)，计算单元格B2中工资额的奖金比例，然后利用自动填充功能，计算其他工资额的奖金比例，结 果如图5.3所示。图5.3选取奖金比例【应用说明】CHOOSE函数的数值参数还可以为区域被引用。5.1.4 COLUM N函数：返回列号【功能说明】该函数用来返回指定引用的列号。列号的范围是1256之间的任意整数。【语法表达式】 COLUMN(refere nee)【参数说明】表示单元格，或单元格区域。也就是，一个单元格，或单元格区域。【使用说明】z 如果省略参数referenee，那么返回函数COLUMN所在单元格的列号。z 如果参数referenee是单元格区域，且函数 COLUMN 以水平

11、数组的方式输入， 那么函数将返回位于单元格区域首列的单元格的列号。【实际应用】某公司统计了三个部门的员工编号，下面需要通过员工编号判断所在部门。在单元格E3中输入“ =COLUMN(C6) ”，判断员工编号314所在的部门，如图 5.4示。諒0 * e ?w”；迂刁豆紳調川內 WckmMe斫时E3开瑙Jlh 鈕：佃 S3垢丘可*£壬开注I远劎一"XA =COLJ11M（C6）AEC i DF1fil»号2部门1部门二部门三所在部门s1112113113q112212312511?2133136114214314?115215315811$31币g117217317

12、10iia2183 IS11iPLLWTJ 阿 1图5.4确定员工所在的部门【应用说明】参数referenee只能引用一个单元格区域，不能同时引用多个单元格区域。5.1.5 COLUMNS函数：返回引用的列数1256之间的数值。【功能说明】计算引用的单元格区域包含的列数，返回的结果是【语法表达式】 COLUMNS (array)【参数说明】array :代表要计算其列数的数组或数组公式，可以是引用的单元格区域。【使用说明】该函数不能省略参数。【实际应用】某公司统计了各部门的员工编号，现在需要统计部门总数。在单元格 E3中输入“ =COLUMNS(A2:C1O) ”，结果如图5.5示。AcE1员

13、工堀号2部门一部门二和门三总椰门Sf3m211311341122123125113233SIJ&114214314711521531531咔21$316S11721731710118218318E3i t t n CMOQEE . COLIIWn ELI Ml応M=COLJ11WS （.AZiCl 0开曲贡面：仙MS丘习|啦壬开脱|远声一“ X图5.5返回引用区域的数目【应用说明】如果参数是单元格、单元格区域、数组、数组公式以外的任何值，函数 将返回错误值#Value !。5.1.6 HLOOKUP函数：实现水平查找【功能说明】在数据表或数值数组的首行查找指定的数值，并在数据表中指定行

14、的同 一列中返回一个数值。【语法表达式】 HLOOKUP(lookup-value ， table-array ， row-index-num ，range-lookup) 【参数说明】z Lookup-value ：表示需要在数据表第一行中查找的数值。该参数可以是数值、引 用或文本字符串。z Table-array:表示需要在其中查找数据的数据表。该参数可以引用单元格区域或区域名称。第一行的数值可以为文本、数字或逻辑值，文本不区分大小写。z Row-index-num 表示行号，也就是在 table-array 中待返回的表格中相匹配的值的 行序号。z Range-lookup :表示逻辑值

15、，用来指明函数 HLOOKUP查找时是精确匹配，还是 近似匹配。当range-lookup值为TRUE，或省略该参数时，函数将返回近似匹配 值。也就是，如果函数找不到精确匹配值，那么，函数将返回小于lookup-value的最大数值；当其值为 FALSE 时，那么函数会查找精确匹配值，否则，函数将 返回错误值 #N/A ！。【使用说明】z HLOOKUP 函数中 H 表示“行”。z 如果range-lookup值为TRUE，那么table-array的第一行的数值必须按升序排列， 如-2、-1、0、1、2A-Z、FALSE、TRUE ;否则，该函数将找不到能匹 配的数值；如果其值为 FALSE

16、，那么，table-array可以不用排序。z 使用函数时，如果参数 row-index-num 值小于 1，那么函数将返回错误值 #VALUE ！。 z 如果参数 row-index-num 的值大于 table-array 的行数时，函数将返回错误值 #REF！。z 在函数计算时，如果 hlookup找不到lookup-value，且range-lookup的值 为 TRUE ，那么函数返回小于 lookup-value 的最大值；如果 HLOOKUP 小于 table-array 第一行中的最小数值，函数将返回错误值 #N/A ！。【实际应用】某厂商销售 A、B 和 C 三种商品，在销售

17、单上，用户需要获取商品的单价。在单元格 C5 中输入函数“ =HLOOKUP(A5,$A$1:$C$2,2,FALSE) ”，选取商品 A 单 价，然后利用自动填充功能，得到其他项目的单价，如图 5.6 所示。J；町亍厂； BBSS弓 1用翩8工1锂-Excel' d sc汙站殛入雄辐屬磴式II3E甫阖画刑虹Jt 1I0OK翩-b囂C5A =BLDQEUF(A5,:N 2t FALSE)ADcDEF : C1AElc2S45.905121703=顼甘制量A533. B06B545.907C450512X703A.鞋MEDg I b545-9010C4005123,7011*冋4 H c

18、otw COLCims HLWKUPMYTEKUlli第叩：S4.0b.3a| 曲 L.二1 丄亠書 TX'.+；£图5.6选取单价信息【应用说明】当比较值位于数据表的首行，并且要查找下面给定行中的数据时，可以 使用此函数。当比较值位于要查找的数据左边的一列时，则要使用VLOOKUP函数。5.1.7 HY PERLINK!数：创建跳转【功能说明】禾U用该函数可以创建一个快捷方式，也就是页面跳转。【语法表达式】HYPERLINK (link-location , friendly-name)【参数说明】z Link-location :表示文档的路径和文件名，可以把这个文档当作

19、文本打开。该参 数还可以为文档中某一更具体的位置指定路径，路径可以是存储在硬盘驱动器上 的文件，也可以是服务器上的“通用命名规范”路径，还可以是“统一资源定位 符”。z Friendly-name :表示单元格中显示出来的跳转文本值或数字值，单元格的内容呈 蓝色并带有下划线；如果省略该参数，Link-location在单元格里将作为跳转文本显示。【使用说明】z Lin k-locati on可以是被双引号括起来的文本字符串，也可以是包含了文本字符串 链接的单元格。z Friendly-name可以是数值、文本字符串、名称或包含跳转文本或数值的单元格。 如果该参数返回错误值#VALUE !,那么

20、单元格就是显示错误值以代替跳转文本。z如果需要选定一个包含超链接的单元格并且不跳往超链接的目标文件，可以单击单元格区域，同时按住鼠标按钮，直到光标变成一个十字，然后释放鼠标按钮即 可。【实际应用】某公司统计了员工的信息，但是公司将其年龄设置为超连接。在单元格C2中输入公式“ =HYPERLINK("30岁","年龄")”，设置第一名员工的年龄5.7所示。图5.7查看超连接结果超连接。然后依次输入其他员工的超链接公式，结果如图【应用说明】通过创建的快捷方式可以打开存储在网络服务器和In ternet中的文件。单击HYPERLINK函数所在的单元格时，表格能

21、打开存储在link-location中的文件。5.1.8 LOOKUP函数：查找数据【功能说明】从单行或者是单列的单元格区域中，或者从一个数组中，返回需要查找 的值。它有两种形式：向量形式和数组形式，都只包含一行或一列区域。【语法表达式】向量LOOKUP (lookup-value ， lookup-vector， result-vector)数组 LOOKUP (lookup- value ， array)【参数说明】在向量LOOKUP中：z Lookup-value :表示函数在第一个向量中搜索的值。该参数可以是数字、文本、 逻辑值、名称，也可以引用值。z Lookup-vector :只

22、包含一行或一列的区域。该参数可以是文本、数字或者逻辑值。 设置参数的值时，必须以升序顺序放置，否则函数可能无法提供正确值。zResult-vector :也只包含一行或一列区域，而且，大小必须与lookup-vector 致。在数组LOOKUP中：z lookup- value :表示函数在数组中搜获的值。使用函数时，其值可以是数字、文 本、逻辑值、名称，也可以是引用值。z array:表示数组名，参数必须以升序顺序放置，否则，函数无法提供正确的值。【使用说明】在向量LOOKUP中：z如果要查询的值列表较大或者值可能会随时间而改变时，而且是指定行或列的位置，应使用向量形式的LOOKUP。向量形

23、式的LOOKUP可以在单行或单列区域中查找值，返回第二个单行或单列区域中相同位置的值。z 如 果函数 找不到lookup-vector ，那么，它与lookup-vector中小于或等于 lookup-vector的最大值匹配。如果lookup-value 小于lookup-vector中的最小值，函数会返回错误值#N/A ！。在数组LOOKUP中：z如果函数找不到lookup-value，它将使用数组中小于或等于lookup-value的最大值；如果小于lookup-value第一行或列中的最小值，这个最小值取决于数组的维 度，函数将返回错误值 #N/A !。z如果要查询的值列表较小或者在一

24、段时间内保持不变时，而且要匹配的值在数组的第一行或列中时，应使用数组形式的LOOKUP。数组形式的LOOKUP可以在数组的第一行或列中查找指定值，返回数组最后一行或列中相同位置的值。z 数组包含要与lookup-value进行比较的文本、数字、逻辑值的单元格区域，而且 是根据数组的维度进行搜索。z如果数组包含的列数多于行数，函数将在第一行中搜索lookup-value ;如果数组是正方形的或者行数多于列数，那么，函数将在第一列中进行搜索；函数始终会 选择行或者列中的最后一个值。【实际应用】某销售公司统计了各部门员工的销量数据，现在需要确定最大销量的员工部门。在单元格 B13 中输入“ =LOO

25、KUP(MAX(C2:C11),$C$2:$C$11,A2:A11)”，确定最大销量员工所在的部门，如图 5.8所示。上tcjll, A2:A11)hBPEfG1 mi抽凰2 1 A1H.1E245PJcl時67D5 1 A1H忠加6 E3 JOTC47DS 1A1U7卿riiT BnsSOD10 1G99SD11B120S501213 邪门1 c 1L4歪那卑冋墾哉H畔-MicTtHDft ST由1用,h < » h 血就<1肝 HYFERLT冊；IIUF坯 FtA 匸 13祐掃 憧比 KS昭SGWF W - * KS图5.8确定员工的部门【应用说明】LOOKUP函数中

26、大小写文本是一样的。5.1.9 MATC H函数：在数组中查找【功能说明】返回指定方式下与指定数值匹配的数组中元素的相对应的位置。【语法表达式】MATCH(lookup-value , lookup-array， match-type)【参数说明】z Lookup-value :表示需要在数据表中查找的数值。也就是是需要在参数 Lookup-array中查找的数值。使用函数时，该参数可以是数值、数字、文本、逻 辑值的单元格引用。zLookup-array :表示数组或者数组引用。其中，数组可以是包含要查找的数值的连续单元格区域。z Match-type :一个数字。其值可以是 -1、0、或者1

27、。该参数指明查找值的方法， 如表5.2所示。表5.2 match-type 参数说明Match-type 值函数查找范围 Lookup-array排序方式1或省略查找小于等于look-value的最大数值必须是升序排列0查找等于look-value的第一个数值可以是任意次序排列-1查找大于等于look-value的最小数值必须是降序排列【使用说明】z 如果省略参数Match-type，系统将默认其值为 1。z MATCH函数返回的是lookup-array中目标值的位置，并非数值本身。z 查找文本时，函数不区分大小写。z 函数如果查找不到目标值的位置，就返回错误值#N/A !。【实际应用】已知

28、某产品单价表，查询某产品品牌所在的数据列。在单元格B8中输入表达式“ =MATCH(A8,A2:A6) ”，查询商品D品牌在“商品”数据 列的数据列，在本例中返回的结果是4，结果如图5.9所示。图5.9查询产品品牌的数据列【应用说明】如果参数Match-type的值为0,且Lookup-value是文本，Lookup-array可以包含通配符、星号和问号。星号可以匹配任何字符序列，问号只能匹配单个字符。5.1.10 ROW函数:返回引用的行号【功能说明】返回指定单元格的行号，或者是指定单元格区域中首行的单元格的行号。返回的值是165536之间的任意数。【语法表达式】ROW(refere nee

29、)【参数说明】Refere nee：表示指定的单元格或单元格区域。【使用说明】z如果参数reference是单元格区域，那么函数返回区域中首行的单元格的行号。z 参数Referenee不能引用多个单元格区域。z如果省略Referenee参数，那么返回函数 ROW所在的单元格的行号。【实际应用】某公司统计了 112月的销量，现在需要通过销量来确定该销量对应的月份。在单元格B15中输入公式“ =ROW(B9)-1 ”，判断销量对应的月份，如图 5.10所示。图5.10查看销量所在的月份【应用说明】如果 Referenee是单元格区域，并且函数ROW作为垂直数组输入，那么函数ROW会返回一个垂直数组

30、形式的Referenee行号。5.1.11 ROWS函数:返回引用的行数【功能说明】该函数可以返回所引用的单元格区域的行数，也可以返回数组的行数。【语法表达式】ROWS(array)【参数说明】Array :表示需要计算其行数的数组、数组公式或引用单元格区域。【使用说明】此函数可以根据用户提供的参数，返回特定的单元格所在行数，这个用 法与Row的数组形式使用有点类似。【实际应用】某公司将每月的销量统计在一列中，现在需要计算已经统计的月份。在单元格B12中输入公式“ =ROWS(A2:A10) ”，判断统计的月份，如图5.11所示。日呼 e .缈腮 I用aistxhx - wrcr - w x

31、2开巻砂眾it紅戸醉丽叫鯉；雅|吐遛一 fl x就紺 二1_迢凹 mo叫p.图5.11统计销量的月份【应用说明】如果查询的是数组的行数，如果公式不是以数组形式输入的，和ROW函数返回的结果是相同的。5.1.12 RTD函数：获取实时数据【功能说明】该函数可以RTD服务器中返回实时数据。【语法表达式】RTD(ProglD ,server, topic1, topic2, )【参数说明】z ProgID :表示已安装在本地计算机上，经过注册的COM自动化加载宏的ProgID名称，这个名称要求用括号括起来。zServer：表示运行加载宏的服务器名称。如果没有服务器，那么，程序是在本地计算机上运行，那

32、么该参数为空白，不然，要用双引号将服务器的名称用引号引 起来。如果在 VBA中使用RTD，就必须用双重引号将服务器名称引起来，或者 对赋予VBA NUllString属性，即使服务器只在本地计算机上运行。z Topic1 , topic2,:表示28个参数，从序列1到28，这些参数放在一起时，只代表一个唯一的实时数据。【使用说明】必须在本地计算机上创建并且要注册RTD COM自动化加载宏。如果没有安装实时数据服务器，那么，在要使用RTD函数时，在单元格中会返回错误值。z 如果服务器要继续更新结果，那么与其它的函数会有不同点，RTD函数的公式会在工作簿表格中处于自动计算模式下更改。【实际应用】现

33、在已知巴黎时间，转换为北京时间。在单元格B12中输入公式“ =RTD("smart","",B1) ”，转换时间，如图 5.12所示。图5.12转换时间【应用说明】RTD服务器是增加用户设置的命令或专用功能，扩大了Excel应用功能的辅助程序，方便COM加载项的运用。其中，COM加载项是指用户通过添加自定义的标 签或指令来扩充Excel功能的补充程序。5.1.13 VLOOKUP函数：实行竖直查找【功能说明】在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。 函数VLOOKUP中的“ V”表示垂直查找。【语法表达式】 VLOOKUP(look

34、up_value ，table_array，colndex_num， range_lookup) 【参数说明】z lookup_value :表示需要在表格第一列中查找的数值。Lookup_value可以直接输入数值，也可以指定单元格。如果lookup_value小于table_array的第一列中的最小值，函数将返回一个错误值“#N/A !”。zTable_array :表示两列或者多列数据，应当使用对单元格区域的引用，或者引用单元格区域名称。Table_array第一列中的值是由lookup_value搜索的值，这些值 可以是文本、数字或逻辑值，且不区分大小写。zCol_index_num

35、 :表示在Table-array中需要返回的匹配值的序列号。如果col_index_num 的参数值为1，函数将返回table_array第一列的数值；如果col_index_num的参数值为2,函数将返回table_array第二列的数值，以此类推。zrange_lookup :表示一个逻辑值，规定函数VLOOKUP查找精确的匹配值，还是近似匹配值。【使用说明】z 在table_array第一列中搜索文本值时，table_array第一列中的数据不应该出现前导空格、尾随空格、不一致的直引号（或”）、弯引号（或“）或非打印字符。如果出现了这些特殊符号，那么，VLOOKUP函数可能返回错误值或意

36、外值。z 当colndex_num的参数值小于1时，VLOOKUP函数会返回错误值 #VALUE !。 z 当colndex_num的参数值大于table_array的列数，那么 VLOOKUP函数会返回 错误值#N/A。z 当参数range_lookup值为TRUE或省略时，函数返回精确匹配值或近似匹配值，如果找不到精确匹配值，那么函数会返回小于lookup_value的最大数值。z table_array第一列中的值必须以升序排序，否则函数无法返回正确的值。可以选 择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排列。z 当参数range_lookup值为FALSE时，tab

37、le_array第一列可以任意顺序排列，而且函数会寻找精确的匹配值。如果在table-array第一列中有两个或两个以上的值与lookup_value相匹配，那么函数会返回第一个找到的值；如果找不到精确匹配值，函数会返回错误值#N/A。z在搜索数字或日期的值时，table_array第一列中的数据不要保存为文本形式，否贝打VLOOKUP函数继续返回错误值或意外值。【实际应用】某公司统计了各部门员工的销量，现在需要根据员工编号来查看其对应的销量。在单元格 B14中输入“ =VLOOKUP（B13,B2:C11,2,FALSE） ”，查看员工编号是117的销量，结果如图5.13所示。er肯風印弓l

38、 用函Ktjdsx - Mlcrascft Excel- x粉砂、匚3）右电忙式E35丽闻 rSEIP觑.3 - r xB14A . =VLOOKUF (El3j B2:Cllk 2t FALSE)¥'ABcDEF'11员工堀兮销量2A111J2D3日112450cu?6705A11436B115以匚116A117gB11B|10C119n§12Q12:13 !吕l:编号11753D1Hi酰RO 爵 ETD VLOOKUPINDEK Ifl二if二口碍咼 1 ltxxrt -.<>.+；图5.13查询销量【应用说明】如果range_lookup值

39、为FALSE，且lookup_value是文本值，那么在lookup_value中可以使用通配符、问号和星号。问号匹配任意单个字符，星号精辟培任一 字符序列。如果查找的是问号和星号本身，应当在该字符前键入波形符，也就是“ ”。5.2 引用函数在 Excel 中，所有的函数操作都是基于单元格地址的。因此，在多种处理情况下，用 户需要首先获取或者分析单元格的地址。在本小节中，将详细讲解各种引用函数。521 INDEX函数：返回指定内容【功能说明】该函数分为数组形式 INDEX 函数和引用形式 INDEX 函数两种。数组形 式的 INDEX 函数，根据给定的行号和列号，返回指定行列交叉处的单元格的值

40、。引用形 式的 INDEX 函数，函数返回指定行列交叉处的单元格的引用。【语法表达式】数组形式： INDEX(array ， row_num ， column_num) 引用形式： INDEX (reference，row_num，column_num，area_num) 【参数说明】数组形式z Array ：表示单元格区域或数组常量。 如果数组中只包含一列或一行， 那么可以不 使用 row_num 或者 column_num 参数；如果数组中包含多行和多列，且使用了 row_num 或 column_num 中的一个参数，那么 INDEX 函数将以数组形式返回整 行或整列。z Row_num

41、 ：表示数组中某一指定行的行号， 用来确定函数最后返回的数值所在的 行。使用函数时，当 row_num 的值为 1 时，可是省略该参数，但不能省略 column_num 。z Column_num ：表示数组中某一列的列号，用来确定函数最后返回的数值所在的 列。使用函数时，当 column_num 的值为 1 时，可是省略该参数，但不能省略 row_num 。引用形式：z Referenee：表示引用单个单元格， 或引用多个单元格。如果引用不连续的单元格， 需要用括号把各单元格括起来；如果引用的单元格区域中只包含一行或一列，那 么参数 row_num 或 eolumn_num 就可选可不选了。

42、z Row_num :表示引用中的某一行的行号。z Column_num ：表示引用中的某一列的列号。z Area_num :表示从引用区域中，返回 row_num 和eolumn_num 的交叉的区域。 选择或输入第一个区域的编号是 1，第二个区域的编号是 2，依此类推。如果省 略该参数，那么 INDEX 函数使用区域 1。【使用说明】在数组形式的 INDEX 函数中：z 如果 INDEX 的第一个参数是数组常量，应该选择数组形式。z 如果同时使用了 row_num 和 eolumn_num 参数， INDEX 函数会返回 row_num 和column_num交叉处单元格中的值。如果将ro

43、w_num或者column_num设置为"0”或者"零”，INDEX函数会分别 返回整行或者整列的值。要将返回的值用作数组，那么在行的水平单元格区域要 以数组公式的形式输入INDEX函数；如果要输入数组公式，那么，需要同时按【Ctrl】、【shift】和【enter】键。zRow_num和column_num参数必须指向数组中某个单元格，否则INDEX将返回一个错误值#REF ！。在引用形式的INDEX函数中：z 在 Referenee 禾口 area_num 选择了特定区域后，row_num 禾口 column_num 将选择一个特定的单元格，row_num1是该区域中的

44、第一行，column_numl是该区域的第一列，依此类推。函数返回的引用是row_num和column_num的交叉点。z 如果将row_num或者column_num设置为0或者"零”，INDEX将分别返回整列 或整行的引用。zRow_num或者column_num和area_num必须指向引用的某个单元格；否则INDEX将返回错误值#N/A ！。如果省略参数 row_num和column_num，那么函数将返回 area_num指定的引用区域。【实际应用】已知各产品的单价表，查询某产品的单价。在单元格B2中输入函数表达式“ =INDEX($A$2:$B$6,3,2) ”，选择单价

45、表区域中的第 三行第二列的单价数据，也就是鼠标的单价，结果如图5.14所示。图5.14查询产品单价【应用说明】函数的结果是一个引用， 根据使用的公式，函数返回值可以做引用或值。 因此该函数可以与其他函数嵌套使用。522 INDIRECT函数：返回指定的引用【功能说明】当需要更改公式中引用的某个单元格，而不更改公式本身，此时就可以 用INDIRECT函数。函数返回指定单元格的值。【语法表达式】INDIRECT(ref-text ，a1)【参数说明】zRef-text :表示单元格的引用。单元格的引用，可以是A1-样式的引用，也可以是R1C1-样式的引用，也可以是对定义为引用的名称或者对文本字符串

46、单元格的引 用。如果这个参数引用了不合法的单元格，那么函数将返回一个错误值#REF!。z A1 ：代表一个逻辑值。用来指明包含在单元格ref-text中的引用类型。当参数al值为TRUE或省略时，ref-text代表是A1样式的引用；当参数 a1为FALSE时， ref-text代表是R1C1样式的引用。【使用说明】z如果参数ref-text引用的是另一个工作簿，也就是外部引用，那么外部源工作簿必须处于打开状态，否则函数INDIRECT会返回错误值#REF !。z如果使用“剪切”命令，或是插入或者删除行或列，使单元格发生了移动，那么单元格引用将被更新。【实际应用】已知各产品的单价表，根据其中单元格区域来查询单价。在单元格 B8中输入函数表达式“ =INDIRECT(A