首先感谢硫酸下,花间一壶酒,夜尘无星等大神(大神太多,名字有疏漏的请见谅),是他们直接或间接的无私帮助,让我这个新手小白对excel有着浓厚的兴趣。
规则:A列有编好号的号码1-100号,其中缺了几个号码,B列是完整的1-100编号,现在我们要找出缺号的号码,把它放在C2:C101,不缺号的号码显示为空,如图:
![](http://imgsrc.baidu.com/forum/w%3D580/sign=1f859a8539fa828bd1239debcd1e41cd/7a065c6034a85edf2e0ae0a343540923dc547567.jpg)
先表明一点,我的公式不一定是最优的,只是个人的一些理解,说的不对的地方请各位大神批评指正。我的版本为excel 2007,2003版本的童鞋请升级到高版本。看到这么长的公式不要懵(其实也不是很长),我们分步走。
这个思路很简单,就是在A列里面查找B列,找不到的就是缺号号码。最简单的函数VLOOKUP,我想大家首先会想到这个。
C2输入公式=VLOOKUP(B2,A:A,1,0)后回车,然后下拉填充到C101单元格。现在缺号的号码会显示错误值,我们先去掉错误值用空值代替,改写公式:
=IFERROR(VLOOKUP(B2,A:A,1,0),””)
这里缺号的号码为空值,不缺号的正常显示。这个不是我们需要的,我们需要缺号的号码显示值,正常号码显示为空。继续改写公式:
=IF(IFERROR(VLOOKUP(B2,A:A,1,0),””)=””,B2,””)
这里就达到了我们第一步要求,但是缺号的号码不是集中在一起,中间有很多空值,我们可以通过筛选-文本筛选—不包含—空值(不填写数据代表空值)来去掉空值,也可以高级筛选—选择不重复记录去掉空值。
童鞋们可能要问,不是一条公式达到这个效果,你这里还用了那么多操作,差评!大家莫着急,我们慢慢来,继续改写公式为数组公式:
首先选择C2:C101这100个单元格,然后输入
= IF(IFERROR(VLOOKUP(B2:B101,A2:A101,1,0),””)=””,B2:B101,””)
如果直接在刚才的基础上修改,记得选择C2:C101的同时,再全选公式,然后CTRL+SHIFT+ENTER同时按,这样就直接得到100个单元格的值;如果是输入公式,直接CTRL+SHIFT+ENTER同时按就可以。
这一步完成,再来下一步,如何用公式提取缺号号码值?
这里大家可以先百度,关键字为:INDEX+SMALL,选择第一条记录:Excel一对多查询(index+small+if)。百度解释的比较清楚,我就不再赘述。
参看了百度,我们现在可以写出公式啦。这里就是一对多的查找,查找C2:C101里面不等于空值的数值,并把他们集中放在一起,例如放在F2:F101单元格。
数组公式,F2:F101输入
=IFERROR(INDEX(C2:C101,SMALL(IF(C2:C101<>"",ROW(1:100),4^8),ROW(1:100))),""),然后CTRL+SHIFT+ENTER,这里仍然用IFERROR去掉错误值,用空值代替。
好啦,到此基本完成,两个公式整合在一起,把C2:C101替换为IF(IFERROR(VLOOKUP(B2:B101,A2:A101,1,0),””)=””,B2:B101,””)就可以。眼尖的童鞋又要问,怎么不是图片的公式呢?这里说到另外一个思路,在A列里面统计B列出现的次数,显然次数为1的代表正常值,为0的代表缺号。
普通公式:=IF(COUNTIF(A:A,B2),””,B2)
数组公式:选择E2:E101单元格,输入=IF(COUNTIF(A2:A101,B2:B101),””,B2:B101), ,然后CTRL+SHIFT+ENTER
最后一条公式得到结果,选择G2:G101,输入数组公式
=IFERROR(INDEX(IF(COUNTIF(A2:A101,B2:B101),"",B2:B101),SMALL(IF(IF(COUNTIF(A2:A101,B2:B101),"",B2:B101)<>"",ROW(1:100),4^8),ROW(1:100))),"")
然后CTRL+SHIFT+ENTER,就可以得到我们想要的结果。
规则:A列有编好号的号码1-100号,其中缺了几个号码,B列是完整的1-100编号,现在我们要找出缺号的号码,把它放在C2:C101,不缺号的号码显示为空,如图:
![](http://imgsrc.baidu.com/forum/w%3D580/sign=1f859a8539fa828bd1239debcd1e41cd/7a065c6034a85edf2e0ae0a343540923dc547567.jpg)
先表明一点,我的公式不一定是最优的,只是个人的一些理解,说的不对的地方请各位大神批评指正。我的版本为excel 2007,2003版本的童鞋请升级到高版本。看到这么长的公式不要懵(其实也不是很长),我们分步走。
这个思路很简单,就是在A列里面查找B列,找不到的就是缺号号码。最简单的函数VLOOKUP,我想大家首先会想到这个。
C2输入公式=VLOOKUP(B2,A:A,1,0)后回车,然后下拉填充到C101单元格。现在缺号的号码会显示错误值,我们先去掉错误值用空值代替,改写公式:
=IFERROR(VLOOKUP(B2,A:A,1,0),””)
这里缺号的号码为空值,不缺号的正常显示。这个不是我们需要的,我们需要缺号的号码显示值,正常号码显示为空。继续改写公式:
=IF(IFERROR(VLOOKUP(B2,A:A,1,0),””)=””,B2,””)
这里就达到了我们第一步要求,但是缺号的号码不是集中在一起,中间有很多空值,我们可以通过筛选-文本筛选—不包含—空值(不填写数据代表空值)来去掉空值,也可以高级筛选—选择不重复记录去掉空值。
童鞋们可能要问,不是一条公式达到这个效果,你这里还用了那么多操作,差评!大家莫着急,我们慢慢来,继续改写公式为数组公式:
首先选择C2:C101这100个单元格,然后输入
= IF(IFERROR(VLOOKUP(B2:B101,A2:A101,1,0),””)=””,B2:B101,””)
如果直接在刚才的基础上修改,记得选择C2:C101的同时,再全选公式,然后CTRL+SHIFT+ENTER同时按,这样就直接得到100个单元格的值;如果是输入公式,直接CTRL+SHIFT+ENTER同时按就可以。
这一步完成,再来下一步,如何用公式提取缺号号码值?
这里大家可以先百度,关键字为:INDEX+SMALL,选择第一条记录:Excel一对多查询(index+small+if)。百度解释的比较清楚,我就不再赘述。
参看了百度,我们现在可以写出公式啦。这里就是一对多的查找,查找C2:C101里面不等于空值的数值,并把他们集中放在一起,例如放在F2:F101单元格。
数组公式,F2:F101输入
=IFERROR(INDEX(C2:C101,SMALL(IF(C2:C101<>"",ROW(1:100),4^8),ROW(1:100))),""),然后CTRL+SHIFT+ENTER,这里仍然用IFERROR去掉错误值,用空值代替。
好啦,到此基本完成,两个公式整合在一起,把C2:C101替换为IF(IFERROR(VLOOKUP(B2:B101,A2:A101,1,0),””)=””,B2:B101,””)就可以。眼尖的童鞋又要问,怎么不是图片的公式呢?这里说到另外一个思路,在A列里面统计B列出现的次数,显然次数为1的代表正常值,为0的代表缺号。
普通公式:=IF(COUNTIF(A:A,B2),””,B2)
数组公式:选择E2:E101单元格,输入=IF(COUNTIF(A2:A101,B2:B101),””,B2:B101), ,然后CTRL+SHIFT+ENTER
最后一条公式得到结果,选择G2:G101,输入数组公式
=IFERROR(INDEX(IF(COUNTIF(A2:A101,B2:B101),"",B2:B101),SMALL(IF(IF(COUNTIF(A2:A101,B2:B101),"",B2:B101)<>"",ROW(1:100),4^8),ROW(1:100))),"")
然后CTRL+SHIFT+ENTER,就可以得到我们想要的结果。