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 查找时是精确匹配,还是近似匹配。

分享给朋友:
您可能感兴趣的文章:
随机阅读: