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)<COLUMN()-4,"",INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$D2,ROW($A$1:$A$7),""),COLUMN(A$1))))}
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)<COLUMN()-4,""
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),RO
W($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"))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。