EXCEL数据查找

EXCEL数据查找 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值居多。 B两列范围的第一列A列中，查找F1单元格中的值，执公式的含意是:在A: 行精确查找，找到后返回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),R OW(A1))) I2单元格输入公式 =INDEX($B$1:$B$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),R OW(A1))) J2单元格输入公式 =INDEX($C$1:$C$10,SMALL(IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000),R OW(A1))) 这三个数组公式中，只有INDEX的第一个参数不同，其他都是一样的，公式完成后，把这三个单元格同时选中，用鼠标向下拖动复制到下面的其他格中。 这个公式对初学者来说，就比较难理解了，其实不论多复杂的公式，你可以把它分成多个部份，一部份一部份的来理解，就能知道它的作用了。 以I2格公式为例进行分析: 、先看“IF($B$1:$B$10=$F$1,ROW($B$1:$B$10),1000)” 1 它的意思是在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),RO W(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函数取出这个数据。 在上面介绍了一些常用的数据查找公式，尤其是后面的公式都使用了数组公式。数组公式在输入后一定要通过按三个组合键来确认，否则无效。 数组公式的功能是强大的，但要注意，如果一个工作簿中使用了过多的数组公式，或数据区域非常庞大，会造成运算缓慢，效率变低。