病毒安全知识,电脑网络技术,手工杀毒方法,答疑解决笔记

导航

« GameAcc VPN Connection清理风扇积灰解决夏天死机 »

Excel查找重复数据的公式

  如果需要从一张excel表中搜索复制数据到另一个excel表,如下图所示,要从前一张表(假设为1.xls中的sheet1工作表)中搜索客户公司(A列),将搜索到的公司后面一列(B列)的钱款数填充到后一张表格(假设为2.xls中的sheet1工作表)的相同公司名(B列)后面的C列。原本这样的查找工作用vlookup函数做个公式就可以了,不过vlookup有个限制,如果要查找的对象在搜索区域中有多个符合要求的值,即发现有重复的查找对象,就只能返回第一个找到的值,如图示例,要在1.xls中A列查找A公司,但1.xls的A列中有两个A公司,那么vlookup只能返回第一个找到的A公司后面的钱款数12500,而在2.xls中同时也有两个A公司需要数据填充,如果使用vlookup函数的公式,则它们得到的数据都是12500,而不会是一个12500,另一个得到2500。 

Excel查找重复数据的公式

所以在excel中查找重复数据时使用vlookup就很不方便,这时就需要使用数组公式(虽然有人自定义出一个wlookup的函数,增加重复值序号参考数,不过我还是觉得觉得用数组会容易一些,尽管不好理解)。

  符合在excel电子表中查找重复数据要求的数组公式有两种形式:一种用于横向填充查找到的重复数据得到的值,即将找到重复对象对应的值在后面几列依次排列,如上图的话,就是在同一行上的C、D列分别填充找到的A公司钱款数:12500、2500。公式如下:

{=IF(COUNTIF([1.xls]Sheet1!$A$2:$A$1000,$B2)>COLUMN(A:A)-1,INDEX([1.xls]Sheet1!$B$2:$B$1000,SMALL(IF([1.xls]Sheet1!$A$2:$A$1000=$B2,ROW([1.xls]Sheet1!$A$2:$A$1000)),COLUMN(A1))),"")}

  另一种公式比较符合上图表格的格式,可以向下填充查找到的重复数据得到的值,这样2.xls中的B列上两个A公司后面都能得到不同的钱款数:

{=IF(COUNTIF([1.xls]Sheet1!$A$2:$A$1000,B2)>COLUMN(A:A)-1,INDEX([1.xls]Sheet1!$B:$B,SMALL(IF([1.xls]Sheet1!$A$2:$A$1000=B2,ROW([1.xls]Sheet1!$A$2:$A$1000)),COUNTIF($B$2:B2,B2))),"")}

说明几点:
0、因以上示例数据从第2行开始,所以上面公式中都是A2、B2起始;2.xls的B列中是待查找的对象,如B2即为查找对象,1.xls的A列是被搜索列,1.xls的B列是要返回的值,即要填充到2.xls的C列,公式就是放在 2.xls的C列;公式里的$是绝对引用,避免拖拉复制中发生变化。
注:两个文件的工作表都是sheet1,以上示例是两个文件的工作表间数据传递,如果是同一个文件中不同工作表或同一工作表间的数据传递自然就更简单了。
1、这两个都是数组公式,第一次复制到单元格时,不要复制最外面的大括号,粘贴后按ctrl+shift+enter,就自动加上大括号变成数组,然后就可以向下拖拉复制(如果包括大括号一起复制会让excel认为是文本复制,不会认作公式执行)。
2、公式最开始的“COUNTIF([1.xls]Sheet1!$A$2:$A$1000,B2)>COLUMN(A:A)-1”主要是用于限定条件,没有查找到数据的可以显示为空(""),避免查不到时显示出错状态;如果不想显示空,而要显示为0,就把最后的引号""改成"0",这样在搜索对象中没有找到对应数据的就自动显示为0值。
3、针对公式2,如果2.xls有两个相同公司名,而1.xls中只有一个,两个中只有一个能获得数据,另一个为计算出错状态。
4、针对公式2,如果2.xls有一个公司名,而1.xls中有两个,只能获得1.xls工作表中第一个被找到的数据。

  描述这个excel查找重复数据可真够难的,希望能看得明白。以上公式部分参考了http://club.excelhome.net/。


>> 除非说明均为原创,如转载请注明来源于http://www.stormcn.cn/post/1082.html

  • 1楼.美雅时尚
  • 每个人的心灵都是不同的人,有的人饱满,有的人空洞,有的人充实,有的人空虚。

    博主的文章写得不做!要支持!每日一顶辛卯年(兔)七月廿十 2011-8-19

  • 2011-8-19 15:50:05  [引用
  • 5楼.返利网
  • 我们也鼓励你们这样做,人们渴望这样的心灵鸡汤。它只需要一点点时间,却具有深远的影响力。 故事是爱的赠礼
    支持博主!!加油!收藏啦……
  • 2011-8-19 19:45:56  [引用
  • 7楼.广州seo
  • excel的公式也比较难弄,只会一些简单的。欢迎回访,走走轻松一下。
  • 2011-8-21 11:18:59  [引用

发表评论(欢迎交流,无须注册 | 如申请友链与本站要求不符,恕不回复,见谅):

验证码

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

  • 收藏文章:
  • 新浪微博:
  • 订阅博客:
  • 腾讯微博:

最新发表

最新评论及回复

本站出现的所有广告均不代表本人及本站观点立场 | 关于我 | 网站地图 | 联系邮箱 | 返回顶部
Copyright 2008-2016 www.stormcn.cn. All Rights Reserved. Powered By Z-Blog. 闽ICP备09000343号

闽公网安备 35010202000133号