Excel查找和引用函数应用手记

• Excel查找
• 引用函数
• 应用手记

Excel还是很强大的，很多功能不是Excel没有，而是我们没学会。学点基础编程还是有好处的，这不今天又学到一招。

遇到这么个情况：

■sheet1表格中A列为订单号，序号为1~300，B列为订单数量

■sheet2表格中A列为订单号，B列为对应出库数据，但是sheet2中可能只有200行数据，且这200个订单号均包含在sheet1表格中A列300个数据中，是这300个订单数据的子集，但是这200个订单号是随机的，没有连续性，也没有规律性。

■同样Sheet3表格中A列为订单号，B列为对应入库数据，Sheet3中有可能只有180个数据，和Sheet2表格一样，这180个订单号也是Sheet1中300个订单的子集，订单号随机，没有连续性和规律性。

■现在报表上要求，把这3个工作表的数据汇总到1个表格当中去，做一张新的工作表，A列为订单号，B列为订单数量，C列为出库数量，D列为入库数量，如果没有出库数据和入库数据的订单则默认为0。

这个问题说麻烦很麻烦，一般人的默认做法无非是排序后，再想办法复制粘贴，这也是我很早以前用过的旧办法，费时费力，遇到跳号的数据还容易出错。我一直想着，把这些数据想办法导入到同一个数据库中，然后再把数据库列出来，应该是最有效的办法，不过我没学过数据库，还不太会用SQL语句，所以也只能一愁莫展了。

不过，这两天在网上搜索又琢磨出一个办法，用Excel的2个函数就可以实现。

做事情一步步来，先计算出库数量。

1.首先，判断Sheet1表格中A列的数据在Sheet2中是否存在

2.如果存在，则引用Sheet2中B列的数据;

3.如果不存在，则默认填充为0.

在Sheet1表格中C2单元格输入公式“=IF(COUNTIF(sheet2!$A:$A,A2)>0,VLOOKUP(A2,SHEET2!$A$1:$B:$201,2,TRUE),0)” 即可实现引用Sheet2中对应订单号的第2列出库数据。

同样，计算入库数量的时候，只要把上面公式中工作表名称和数据域改下即可： sheet1工作表中D2单元格输入公式”=IF(COUNTIF(sheet3!$A:$A,A2)>0,VLOOKUP(A2,SHEET3!$A$1:$B:$181,2,TRUE),0)“即可。

这个公式其实利用了1个if判断语句，countif函数，以及vlookup函数。不过后来想想，用countif函数其实不是最合适的，判断某已知值是否存在的话，或许用Match函数更合适。

如果要不是学了点可怜的编程基础，还真不一定能看懂这些函数和语句的用法。5月份有将近1800条数据，要是没这两个函数，我真的是不知要多花费多少时间去搞这张报表了。

++++++++++++++Excel有关查找函数和引用函数介绍++++++++++++++++

如果需要确定某已知值在某个数据表（一行或一列）中是否存在，可以使用MATCH函数进行查找。

方法1：使用MATCH函数 =IF(ISNA(MATCH($B$3,$D:$D,0)),”不存在”,”存在”)

MATCH函数是EXCEL主要的查询函数之一，该函数通常用于以下几个方面：

1.确定列表中某个值的位置；

2.对某个输入值进行检验，确定这个值是否存在于某个列表中；

3.判断某一列表中是否存在重复数据；

4.定位某一列表中最后一个非空单元格位置。

MATCH函数的语法如下： MATCH(lookup_value,lookup_array,match_type) 以上公式利用MATCH函数的查找功能，当查询条件存在时，MATCH函数结果为具体位置（数值），否则显示为#N/A错误。

方法2：使用COUNTIF函数 COUNTIF函数 用来计算区域中满足给定条件的单元格的个数。

例如:公式 =IF(COUNTIF($D:$D,$B$3)>0,”存在”,”不存在”) 则表示在D列中查找B3单元格的值，如果存在则显示存在，不存在输出不存在

语法 COUNTIF(range,criteria)

Range 为需要计算其中满足条件的单元格数目的单元格区域。

Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、”32″、”>32″ 或 “apples”。

HLOOKUP与VLOOKUP函数

HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。

当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。

语法形式为：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。

Table_array查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。

Row_index_num 为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。

Col_index_num为相对列号。最左列为1，其右边一列为2，依此类推.

Range_lookup为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。