Question to say "I can!"

EXCEL数据查找

2014-03-28

EXCEL有强大的数据处理功能,可以用它对数据进行组织和管理,当在工作中采集了大量数据后,出于某种需要,我们希望将符合某种条件的数据提取出来,有时是一个汇总结果,有时是所有符合条件的数据本身。很多刚接触EXCEL的朋友不知道从哪个方面入手,下面我们通过几个实例,快速掌握这些方法。
一、通过一定条件查找,返回汇总结果
在这个销量表中,如果我们想统计出姓名为“张三”的全部销量,可以用一个函数来做公式:
=SUMIF(销量表!B:B,"张三",销量表!C:C)
这个SUMIF函数是一个条件汇总函数,它有三个参数,第一个参数是要查找的区域,在本例中是B列,第二个参数是要查找的条件,在本例中是“张三”这个姓名,第三个参数是需要汇总的区域。
这个公式的含义是:在“销量表”的B列查找“张三”这个内容,在B列找到后,把找到的同一行中的C列值相加。
我们还可以用下面这个公式达到同样的效果:
=SUM(IF(销量表!B1:B100="张三",销量表!C1:C100))
这是一个数组公式,在输入完成后要按CTRL+SHIFT+ENTER组合键确认,此时会自动在公式的两端加上一对花括号(手工输入花括号无效)。
在这个公式中用了两个函数SUM与IF,先由IF对它右边的条件“销量表!B1:B100="张三"”做判断,这个条件同样是在销量表的B列查找“张三”,当条件成立时,就执行“销量表!C1:C100”这个部份,即返回C列同一行中的数值,最后由SUM函数将返回的所有相符的数值相加,得到正确结果。
第二个公式看似比第一个公式复杂,但它有一个“特殊”的作用,就是可以“多条件”查找。
再看一个例子,还是在“销量表”中,把六七月份的“张三”的销量统计出来(不要五月份的)
=SUM(IF((MONTH(销量表!A2:A100)>5)*(销量表!B2:B100="张三"),销量表!C2:C100))
这个数组公式用了两个条件,一个是A列的月份要大于5(即只要6、7月份的),另一个条件是B列等于“张三”,只有这两条件都成立,才执行同一行中的C列汇总求和。
在这个公式中,由于要对“月份”进行计算,所以用一了个MONTH函数,它的作用是提取日期中的“月份”。
要注意一点,在SUMIF函数中可以用“整列”(即:销量表!B:B),而在SUM与IF组合的数组公式中,不能用“整列”这个方式,只能用一个具体的区域(如:销量表!A2:A100)。
提示1:在上面几个公式中,为了方便使用,可以把“条件”放在一个固定的单元格中,然后在公式中只引用这个单元格。比如把要查找的“张三”输入到K1格中,然后把公式改成: =SUMIF(销量表!B:B,k1,销量表!C:C)
提示2:如果公式不在“销量表”中,那么公式中必须加上表格名字用以指定数据位置(如:“销量表!B1:B100”前面的“销量表!”),如果公式就在“销量表”工作表中,则公式中不必加工作表的名字。
提示3:想达到上面的结果,还有很多其他方法,大家可根据自己的喜好选择。
二、通过条件查找,返回符合条件的内容。
在上图的表格中,每个姓名是唯一的,希望通过一个姓名查找到所需的电话号码
比如查找李四的电话号码
在F1单元格中输入一个要查找的姓名“李四”,然后在F2单元格中用公式 =VLOOKUP(F1,A:B,2,0)
只要按需要改变F1格中的姓名,就可在F2单元格中得到对应的号码。
这个公式使用了VLOOKUP函数,它有四个参数,第一个参数是要查找的值,第二个参数是要查找的范围,第三个参数表示返回范围中的第几列内容,第四个参数如果为0,就表示“精确查找”,如果为1就表示“模糊查找”,一般用0值居多。
公式的含意是:在A:B两列范围的第一列A列中,查找F1单元格中的值,执行精确查找,找到后返回A:B两列中第二列B列的内容。
还可以用另一个公式来达到这个效果(这个更灵活):
=INDEX(B:B,MATCH(F1,A:A,0))
这个公式用了两个函数,MATCH函数返回F1单元格的值在A列的位置,第三个参数0表示只返回查找到的第一个值;INDEX函数通过前一个函数返回的位置,在B列中取出对应的内容。
这两个公式各有特点,当查找区域是连续时,用VLOOKUP比较省事;当区域不在同一个位置时,就只能用第二个公式了。
三、通过指定条件,把所有符合的内容都显示出来
还是以最上面的“销量表”为例,通过一个公式把所有姓名等于“张三”的内容都显示出来。
先在F1格输入一个姓名“张三”,然后在H、I、J列使用公式:
H2单元格输入公式 =INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))
I2单元格输入公式 =INDEX($B$1:$B$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))
J2单元格输入公式 =INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))
这三个数组公式中,只有INDEX的第一个参数不同,其他都是一样的,公式完成后,把这三个单元格同时选中,用鼠标向下拖动复制到下面的其他格中。
这个公式对初学者来说,就比较难理解了,其实不论多复杂的公式,你可以把它分成多个部份,一部份一部份的来分析理解,就能知道它的作用了。
以I2格公式为例进行分析:
1、先看“IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000)”
它的意思是在B1:B10区域内查找F1单元格的值(一个要查找的姓名),如果区域内有这个值,就返回它的行号(ROW是返回行号的函数),如果没有就返回一个比较大的数值(任意,只要大于数据的个数就行)。如果有多个符合的内容,就返回多个行号。
用本例数据查找“张三”来说明,通过这部份运算,会得到一串数值{1000;2;1000;4;5;1000;1000;1000;9;1000}
它表示在区域的第2、4、5、9行中找到了与“张三”相符的内容。为了方便说明,我给这串数值取个名字叫“行”
2、再看“SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1))”这部份,它等价于“SMALL(行,ROW(A1))”
这部份用SMALL函数返回一个“第几小值”,由于I2单元格最后用的是ROW(A1),它是A1格的行号,即1,表示返回第一个最小的值,所以从上面“行”数值串中返回“2”。为了说明方便,我给它取名为“位置”。
提示:之所以在上面部份用ROW(A1)来代表1,是为了向下拖动复制公式时,它会自动递增,每复制一行就自动增加1,例如把I2公式复制到I3格时,在I3格会变成ROW(A2)。这时在SMALL函数的第二个参数变成2,表示返回第二小值,即“行”字串中的“4”,依次类推。
3、最后看“INDEX($B$1:$B$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),ROW(A1)))”,它等价于“INDEX($B$1:$B$10,位置)”
这就好理解了,通过“位置”的数值,从B1:B10中取出相应位置的一个内容。
每个公式都可用这种“分解”的方法去理解和分析。

四、对多个重复数据,只显示最后一个
还是以“销量表”为例,这个表中的数据会不断向下追加,如果我们希望在某一个单元格中只反映最新追加的数据,就需要用下面的公式了:
先在G1单元格中输入一个姓名“张三”,然后在G2单元格中输入数组公式 =INDEX(C1:C100,MAX(IF(B1:B100=G1,ROW(B1:B100),0)))
这个公式的含义与上面的差不多,只是把SMALL函数换成了MAX函数,MAX是求“最大值”的函数。
由于数据不断追加,最新输入的数据的行号肯定是最大的,通过MAX就能确定它的位置,然后用INDEX函数取出这个数据。
在上面介绍了一些常用的数据查找公式,尤其是后面的公式都使用了数组公式。数组公式在输入后一定要通过按三个组合键来确认,否则无效。
数组公式的功能是强大的,但要注意,如果一个工作簿中使用了过多的数组公式,或数据区域非常庞大,会造成运算缓慢,效率变低。



作者:admin | Categories:技术人生 | Tags:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

无觅相关文章插件,快速提升流量