|
本帖最后由 wangjguo44 于 2014-1-19 21:12 编辑
chatang 发表于 2014-1-19 19:39
是的老师,就这意思,能把这公式给我讲讲吗?
=IF(ROW(A1)<=COUNT($A:$A),IF(COUNTIF(E:E,B4),INDEX(E:E,MIN(IF(B4=$E$4:$E$271,ROW($E$4:$E$271),4^8))),""),INDEX(E:E,SMALL(IF(COUNTIF($B:$B,$E$4:$E$271)=0,ROW($E$4:$E$271),4^8),ROW(A1)-COUNT($A:$A))))&""
1、根据要求,在B列数据范围里要把B列中含有E列值的对齐列出来,而若B列在E列中不存在,留空白,而在B列数据范围以下,要把E列里那些不曾出现在B列里的依次往下列下去,因此公式用了IF(ROW(A1)<=COUNT($A:$A)——下拉时根据行数是还在B列数值型数据个数(COUNT($A:$A))范围里和还是范围外,把公式分为两部分。
2、在范围里,IF(COUNTIF(E:E,B4),INDEX(E:E,MIN(IF(B4=$E$4:$E$271,ROW($E$4:$E$271),4^8))),"")——
a、如果E列有本行B列数据(COUNTIF(E:E,B4))按INDEX(E:E,MIN(IF(B4=$E$4:$E$271,ROW($E$4:$E$271),4^8)))引用,否则显示空("");
b、IF(B4=$E$4:$E$271,ROW($E$4:$E$271),4^8)))——如果B4=$E$4:$E$271其所在行号,否则赋以4^8(=4的8次方=65536=03版最大行号)
c、MIN(IF(B4=$E$4:$E$271,ROW($E$4:$E$271),4^8))——考虑到名字不重复,用MIN取出其行号;
d、INDEX(E:E,MIN(IF(B4=$E$4:$E$271,ROW($E$4:$E$271),4^8)))——在E列返回对应行号的数据。(其实既然是独一无二,这里可以更简单:IF(OR($E$4:$E$271=B4),B4,""),运算也可以快些;
3、在范围外,执行INDEX(E:E,SMALL(IF(COUNTIF($B:$B,$E$4:$E$271)=0,ROW($E$4:$E$271),4^8),ROW(A1)-COUNT($A:$A)))——
a、IF(COUNTIF($B:$B,$E$4:$E$271)=0,ROW($E$4:$E$271),4^8)——如果E列的数据不在B列,取其行号,否则4^8;
b、SMALL(IF(COUNTIF($B:$B,$E$4:$E$271)=0,ROW($E$4:$E$271),4^8),ROW(A1)-COUNT($A:$A))——下拉依次取第一个、第二个……不在B列的值的行号,由index去引用返回,不用多说,要解释的是ROW(A1)-COUNT($A:$A),因为下拉时行数已经过了B列数据个数,要取第一小,需减去B列数据个数。
最后,再说一遍,公式可以简单些:
- =IF(ROW(A1)<=COUNT($A:$A),IF(OR($E$4:$E$271=B4),B4,""),INDEX(E:E,SMALL(IF(COUNTIF($B:$B,$E$4:$E$271)=0,ROW($E$4:$E$271),4^8),ROW(A1)-COUNT($A:$A))))&""
复制代码
EH(WJG)c.rar
(22.17 KB, 下载次数: 136)
|
|