终极难题！用INDEX、MATCH对多列数据合并之后去重复，显示是错误值，再求高手赐教！

greenbeer

=IF(ROW()-1>COUNTA($A$2:$B$8),"", INDIRECT(TEXT(--RIGHT(SMALL(IF($A$2:$B$8<>"",COLUMN(A:B)*100000001+ROW($2:$8)*1000),ROW(A1)),5),"!R0!C000"),))
注意:"R0C000"这裡错,应改"!R0!C000"

斯文小弟

greenbeer 发表于 2012-4-27 22:36
=IF(ROW()-1>COUNTA($A$2:$B$8),"", INDIRECT(TEXT(--RIGHT(SMALL(IF($A$2:$B$8"",COLUMN(A:B)*100000001+R ...

难题已经解决，请参考8楼。不过，还是想请教一下，为什么要加两个!?

greenbeer

1.我是繁2003,这我曾遇过,当要在text()中,使用"RC"格式,需加!强制符号于R于C前,具体!的用法,请楼主去搜寻一下.
2.这题我测试了一下,发现indirect()与offset()在此情形,match()的结果无法运用.
3.但是index()与lookup()是可以的!
(1)F2为一般格式,可以如下:
E2=INDEX(($A:$B,$C:$D),IF(ROW()>COUNTA($A$2:$A$7)+1,ROW()-COUNTA($A$2:$A$7),ROW()),IF(ROW()>COUNTA($A$2:$A$7)+1,2,1),IF(COLUMN()=5,1,2))&"" 右拉下拉

(2)F2为日期格式,可以如下:
E2=IF(ROW()>COUNTA($A$2:$B$7)+1,"",LOOKUP(IF(ROW()<=COUNTA($A$2:$A$7)+1,ROW(),ROW()-COUNTA($A$2:$A$7)),ROW(A$2:A$7),IF(COLUMN()=5,IF(ROW()<=COUNTA($A$2:$A$7)+1,$A$2:$A$7,$B$2:$B$7),IF(ROW()<=COUNTA($A$2:$A$7)+1,$C$2:$C$7,$D$2:$D$7))))
或
E2=IF(ROW()>COUNTA($A$2:$B$7)+1,"",INDEX(($A:$B,$C:$D),IF(ROW()>COUNTA($A$2:$A$7)+1,ROW()-COUNTA($A$2:$A$7),ROW()),IF(ROW()>COUNTA($A$2:$A$7)+1,2,1),IF(COLUMN()=5,1,2)))  右拉下拉

G2=INDEX(E:E,SMALL(IF(MATCH($E$2:$E$11&$F$2:$F$11,$E$2:$E$11&$F$2:$F$11,)=ROW($1:$10),ROW($2:$11),99),ROW($A1)))&""  维持楼主的公式