Excel常用的查找引用函数及方法

Excel常用的查找引用函数及方法

在excel函数的运用中，查找引用函数是经常用到的。像VLOOKUP的运用，还有有条件的引用等等。在这里，我想讲讲比较常用的几个函数，还有一些网上常见到的经典问题。

一、 VLOOKUP

VLOOKUP是初学者问得比较多的一个函数，许多人学引用就是从它开始的。它是功能是：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。说白一点就是：根据首列进行查找。

它的格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

用中文来表示：VLOOKUP（查找条件，查找范围，要引用的数据在范围内的第几列，查找方式<精确与否>）。

这里有点得说明一下，它的第一个参数lookup_value（查找条件）必须是查找范围的最左列，也就是首列。第四个参数，查找方式，有两种状态，一种为False(或0)，函数以精确匹方式查找，并支持无序查找;一种为True(或1)，函数以模糊匹配方式查找，定位在小于它的最大值。

图一

如图，在B12里的公式：=VLOOKUP(A12,$A$1:$E$10,2,0)

这公式根据A12的数据。在A1：A10里查找与A12精确匹配的数据，找到后，取相对应的第二列的数据。如果找不到，就会返回#N/A。

这里，A12的“A003”就是查找条件，$A$1:$A$10是查找范围，2是取值列，最后的0是精确匹配。

如果用模糊查找：=VLOOKUP(A12,$A$1:$E$10,2,1)，并把A12的值改为：A013，大家请看图二

图二

函数就会查找小于A013的最大值A009，并取出第2列里的对应值，得到：陈平。

如果我们要根据姓名来查询学号，VLOOKUP能否完成呢？因为姓名在学号的右边，是不合乎“查找范围的最左列”的条件的。办法总是有的，以下公式就是了：

=VLOOKUP(A13,IF({1,0},$B$1:$B$10,$A$1:$A$10),2,0)

如图三：

图三

此公式利用IF函数来产生一个新的内存数组来提供给VLOOKUP作第二个参数（查找范围）进行查询，最终达到目的。

除了用这个方法外，还可以用以下公式来实现：

=INDEX($A$1:$A$10,MATCH(A14,$B$1:$B$10,0))

如图四：

图四

这里用到了两个有关查找与引用的函数：index和match

二、 index与match

2.1 函数基础

我们先看看index在帮助里是怎么说的：

返回表或区域中的值或值的引用。函数 INDEX() 有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。

INDEX(array,row_num,column_num) 返回数组中指定单元格或单元格数组的数值。

INDEX(reference,row_num,column_num,area_num) 返回引用中指定单元格区域的引用。

这里用到的是第一种：index(引用区域，引用区域的第几行，引用区域的第几列)

当引用区域只是一行或只是一列时，第二或第三个参数就得省略。举个简单的例子：

=INDEX(A1:E10,3,2)

这公式就是引用A1：D10区域的第三行第二列的数据。如图五：

图五

面公式：=INDEX(B1:B10,3)也一样得到“贝瑶”。这里的引用区域只是在D列，所以把列的参数省了。

Match的功能是：返回在指定方式下与指定数值匹配的数组中元素的相应位置

格式：MATCH(lookup_value,lookup_array,match_type)

MAT(要查找的数据，查找区域，查找方式)

这里的第三个查找方式与VLOOKUP的一样。

=MATCH(A16,B1:B10,0) 返回A16的数据（贝瑶）在B1：B10的位置，得到3。

如图六：

图六

现在回头来看公式：=INDEX($A$1:$A$10,MATCH(A14,$B$1:$B$10,0))

这回明白了吧：用Match函数取得A14（王春燕）在第几行（4），然后用index函数在A1:A10里取第四行就得到了A003。

2.2 两个经典

2.2.1同名横排

在此讲一个许多网友问过的问题。

做一个简单的例子，如下图

A列里的姓名有重复，要求把相对应的B列的值横排在唯一姓名的右边。

E2的公式：（数组公式）

{=IF(COUNTIF($A$1:$A$7,$D2)

D列的值可以用高级筛选得到，也可以用公式（此公式一会就说）

公式中红色的部分是公式的核心。蓝色的部分作用是去错误值。

红色部分公式还是index的引用，第一个参数是$B$1:$B$7，只在一列引用，所以后面的还要一个参数就够。这个参数就是：SMALL(IF($A$1:$A$7=$D2,ROW($A$1:$A$7),""),COLUMN(A$1))，先看IF的部分：IF($A$1:$A$7=$D2,ROW($A$1:$A$7),"")，意思是：$A$1:$A$7里的数据与D2的相等的，就取得它的行号，否则为空（""）,得到的结果是：{"";2;3;"";"";6;""}。

外面套的是SMALL函数，这是了个取第N个最小值的函数。

格式：SMALL(array,k) 《SMALL(取值区域或数组，取第几个)》

前面的公式，把结果放进去就得到：SMALL({"";2;3;"";"";6;""}，COLUMN(A$1))

COLUMN(A$1)的结果是1，也就是列号。这里运用行绝对列相对的引用，当公式向右拖拉复制时，COLUMN(A$1)公变成COLUMN(B$1)、COLUMN(C$1)、COLUMN(D$1)、、、、、、，这样依次会得到1，2，3，4、、、、、、这样的值。用这方法，SMALL就会在E1、F1、G1里依次得到{"";2;3;"";"";6;""}的第一、第二、第三、、、、、第N个最小值。这个就是所有“张三”所在行的行号了。

最后用index函数分别在B列里依次取得相对应的值。

再说说前面那蓝色部分吧，刚才已经说了，是去除错误值的。

IF(COUNTIF($A$1:$A$7,$D2)

Countif函数是计数的，这里是计算A1：A7里有几个D2的数据，COLUMN()-4,是列号减去4，因为E列并不是第一列，所以得用-4来调整，在E1里，COLUMN()就等于5，COLUMN()-4=1。也就是这是公式开始的第一列。当列数大于COUNTIF($A$1:$A$7,$D2)时，就清空。这样，就把#NUM!这个错误值去了，这样就美观多了。

2.2.2一列中取唯一值

D列的数据又如何自动生成呢？

D列的值可以用高级筛选得到，也可以用公式，这里说一个公式。取一列中唯一值的公式有许多，我们也可以运用上面的原理来到得，当然，这并不是很好的方法，但在此为了了解与熟悉这个原理，我们就拿它来开刀吧。

因为D1的标题也是唯一的，我们就从D1开始放入公式：=IF(ROW()>SUM(1/COUNTIF($A$1:$A$7,$A$1:$A$7)),"",INDEX($A$1:$A$7,SMALL(IF(MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7),ROW($A$1:$A$7),""),ROW(A1))))

这公式可以分为两大部分，红色部分是主体，是index的引用，外层是IF，是用判断去错误值的。

在这个Index引用里，主体是IF(MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7),ROW($A$1:$A$7),"")，下面看看这公式的计算机理：

MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7)，这一段公式，用MATCH函数分别得到$A$1:$A$7里的每一个元素在$A$1:$A$7里的位置，因为这里用了精确匹配的查找方式，得到的位置是第一次出现的位置，这样，就会得到这么一组数据：{1;2;2;4;4;2;7}，也就是说，第一个数据“姓名”在$A$1:$A$7

里的位置是1，同理，第二个数据“张三”的位置是2，第三个数据“张三”的位置还是2，明白了吧？那MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7)又得到什么呢？ROW($A$1:$A$7)，得到$A$1:$A$7的行号：{1;2;3;4;5;6;7}，MATCH($A$1:$A$7,$A$1:$A$7,0)=ROW($A$1:$A$7)就是一个判断，把两个结果放入公式就可以看到，{1;2;2;4;4;2;7}={1;2;3;4;5;6;7}，两组数据进行对比，相对应位置上的数据相等的话，就会返回TRUE（真），否则返回FALSE(假)，这样就会得到这样的结果：{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}，在外层加上IF再一次选择：IF({TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE},ROW($A$1:$A$7),"")，是TRUE值的，返回行号，否则返回空值。（这里，也可以把后面的,""省略，这样，公式会在条件为FALSE时返回值为FALSE，这样的结果所以得到的效果是一样的。）得到：{1;2; "";4; "";"";7}（省略,""：{1;2;FALSE;4;FALSE;FALSE;7}）

最后，用SMALL函数结合ROW函数把这几个数值依次取出来，做为INDEX的第二个参数，完成取唯一值的大任。

三、 INDIRECT

3.1．1基础

返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，请使用函数 INDIRECT。

上面是帮助里对INDIRECT函数功能的讲述。

这是格式：

INDIRECT(ref_text,a1)

ref_text：为对单元格的引用，是一个定义为引用的名称或对文本字符串。

•

A1是引用式样：

•

如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。

•

如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。

•

帮助里对INDIRECT函数的运用讲的并不多，但INDIRECT在引用方面的功能是很强大的。不过用法有点玄。

比如：

=INDIRECT($A$1),得到的结果不是A1里的数据B1，而是A1里的数据的指向—B1单元格里的数据：100。

=INDIRECT("A1"),是对A1里的数据直接的引用了，得到：B1。

=INDIRECT("sheet1!b1"),是对sheet1!b1里的数据直接的引用。

=INDIRECT(A1&"!b1")，是对A1里的数据连接文本“B1“后的指向的引用。如图：

A1里的数据是“sheet1”， A1&"!b1"其实就是sheet1!b1, =INDIRECT(A1&"!b1")得到的结果是sheet1!b1里的数据：100。

3.1.2 经典例子

这里举个INDIRECT与SUMIF相结合的三维引用的例子。

下面的例子来自excelhome网站

这是个多表汇总的例子，公式只有一个，用宏表函数得到表名。

=SUM(SUMIF(INDIRECT(IF(sht_name=csht_name,INDEX(sht_name,1),sht_name)&"!A1:a10"),A2,INDIRECT(IF(sht_name=csht_name,INDEX(sht_name,1),sht_name)&"!b1:b10")))-SUMIF(INDIRECT(INDEX(sht_name,1)&"!A1:a10"),A2,INDIRECT(INDEX(sht_name,1)&"!b1:b10"))

这里定义了两个名称

取得当前表名

csht_name=MID(NT(1),FIND("]",NT(1))+1,31)

取得全部表名：

sht_name=MID(OK(1),FIND("]",OK(1))+1,31)

IF(sht_name=csht_name,INDEX(sht_name,1),sht_name)

看这个，很明了的，这是典型的if的用法了：当表名是当前表时，就取用第一个表名来取代，否则直接取用就是。

因为sht_name取得的是全部表的表名，汇总表也在内了，汇总表是不参于计算的，所以就用第一个表来代替。

这样，第一个表就用多了一次，所以在后面就得减一次：-SUMIF(INDIRECT(INDEX(sht_name,1)&"!A1:a10"),A2,INDIRECT(INDEX(sht_name,1)&"!b1:b10"))

套在外一层的，就是INDIRECT的引用了：INDIRECT(IF(sht_name=csht_name,INDEX(sht_name,1),sht_name)&"!A1:a10")

其实你可以把它简化为这样：INDIRECT("各表的表名"&"!A1:A10")

写到这里，觉得有点江郎才尽的感觉了。

最后，说说choose这个函数。

这样吧，用帮助里的一个例子来说：=CHOOSE(2,A2,A3,A4,A5)

这里，有5个参数：2,A2,A3,A4,A5

第一个参数，是叫CHOOSE函数：取后面的参数的第2个（也就是A3，当然，是取得里面的数据，而不是像INDIRECT取它的指向）。后面的参数就像是水果，给CHOOSE拿的。

这函数好像没什么大作用呀，给出一大堆“水果”，然后叫他取第几个才取第几个，谁不会呀？

其实每一个函数单独使用功能再强也强不到哪，只有几个函数齐心合力一起来才显示出公式的威力。

我们看看正面的例子：工资条问题。

工资条的问题许多人都问过，当然方法也不止一个：

=CHOOSE(MOD(ROW(),3)+1,"",Sheet1!A$1,OFFSET(Sheet1!A$1,ROW()/3+1,))这是罗刚君的

=IF(MOD(ROW(),3),OFFSET(工资表!$A$1,(MOD(ROW()-1,3)>0)*ROUND(ROW()/3,),COLUMN(A1)-1),"")这个公式是excelhome的版主写的，放在《excel实战技巧精粹》这本书里。

我们看罗同志的公式就是用CHOOSE来完成这个功能的。

这里，最外层的就是CHOOSE，它有四个参数：

MOD(ROW(),3)+1

""

Sheet1!A$1

OFFSET(Sheet1!A$1,ROW()/3+1,)

第一个参数，是叫CHOOSE函数取后面第几个的指示参数：例如在A1里，MOD(ROW(),3)+1，ROW()就得到1，那么MOD(ROW(),3)就得到1。（这个不用太多解释吧）再+1就是2了。也就是叫CHOOSE取第二个参数：Sheet1!A$1

公式到了A2，MOD(ROW(),3)+1就会得到3，就取：OFFSET(Sheet1!A$1,ROW()/3+1,)

OFFSET是个偏移函数，在这里，以SHEET1!A$1为基点，向下偏移ROW()/3+1完事。