Question

EXCEL 函数 index+small+match+if+row组合疑问

研究了一天，不知道错误何在，基础数据在从C4到最下面，在D4输入数组公式引用获得C4往下数据中不重复的数据，=INDEX($B:$B,SMALL(IF(MATCH($B4:$B1000&"",$B4:$B1000&"",)=ROW($B4:$B1000),ROW($B3:$B1000),4^8),ROW($B1)))&""

也曾在网上寻找大量这类公式，公式案例中的基础数据都是从第二行开始，其公式为=INDEX($C:$C,SMALL(IF(MATCH($C$2:$C$999&"",$C$2:$C$999&"",0)=ROW($1:$998),ROW($2:$998),4^8),ROW($C1)))&""，但我把基础数据移到其他位置（也就是往下移动了数行），然后修改公式，总不能获得正确结果，疑惑
打错了，基础数据从B4到最下面，在C4输入数组公式，感觉问题在=ROW($B4:$B1000),ROW($B3:$B1000),4^8)中的 B4，B3 ，但做过很多修改，如改为：=ROW($B1:$B1000),ROW($B2:$B1000),4^8)，不行
改为=ROW($B4:$B1000),ROW($B4:$B1000),4^8) 不行
改为=ROW($B3:$B1000),ROW($B4:$B1000),4^8) 不行

Answer 1

C4单元格输入以下数组公式，按Ctrl+Shift+Enter组合键，然后向下填充公式
=INDEX(B:B,SMALL(IF(MATCH(B$4:B$1000,B$4:B$1000,)=ROW($4:$1000)-3,ROW($4:$1000),4^8),ROW(A1)))&""
公式中首先要保持所有的维度一致，保持引用区域的行绝对引用状态，然后如果首行与第一行相隔多少行，就减去多少行。

Answer 2

=INDEX($B:$B,SMALL(IF(MATCH($B$4:$B$1000&"",$B$4:$B$1000&"",0)=ROW($B$4:$B$1000)-3,ROW($B$4:$B$1000),4^8),ROW($B1)))&""

你的数组公式应用有几处错误，
1、行号列标都要绝对引用
2、后面的也要从第4行开始，ROW($B$4:$B$1000),你从第3行开始的
3、如果行号不是从第一行开始的，MATCH函数是从1开始返回值，要注意后面减去相应的值

追问

谢谢您，我终于理解了，我觉得您的解释很全面，thanks，如果我在match再加个and条件可以吗？比如=INDEX($B:$B,SMALL(IF(AND(MATCH($B$4:$B$1000&"",$B$4:$B$1000&"",)=ROW($4:$1000)-3,$B$4:$B$1000&""=1),ROW($4:$1000),4^8),ROW(B1)))&""
好像不行，那怎么样可以呢

追答

呵呵，那样是不行的。
这种去重复的数组公式，是利用MATCH函数总是返回第一个查找到的值所在顺序的特性，相同的只取第一个行号，其余的都不取（应用的时候，是把不取的行号设为4^8，高级版本也有设为9^9的，就是设为工作簿的行数最大值，当然数据不多时也可以取小点，比如把这里的4^8改为20000，只要超出你数据的最大行就OK），再配合SMALL函数依次返回第一小、第二小……的行号，再用INDEX来返回所在行的值。
理解了公式的意义，才能自己灵活运用，要不然仅靠套用，稍有不慎，就出错了。

Answer 3

公式修改为=INDEX($B:$B,SMALL(IF(MATCH($B$4:$B$1000&"",$B$4:$B$1000&"",)=ROW($1:$997),ROW($4:$1000),4^8),ROW(A1)))&""
按三键CTRL+SHIFT+ENTER结束公式输入
下拉填充

追问

刚才没完全复制你的公式，目前是对的，能告知原因以及我的错误吗

Answer 4

=INDEX($B:$B,SMALL(IF(MATCH($B$4:$B$1000&"",$B$4:$B$1000&"",)=ROW($4:$1000)-3,ROW($4:$1000),4^8),ROW(B1)))&""

追问

依然不全对，三键下拉后，只C4对，从C5开始下面没有数据出现

追答

=INDEX($B:$B,SMALL(IF(MATCH($B$4:$B$1000&"",$B$4:$B$1000&"",0)=ROW($4:$1000)-3,ROW($4:$1000),4^8),ROW(B1)))&""

追问

哦，match满足条件的是其位置，位置，当然肯定是从第1开始，所以行号得减去3，因为是从第4行开始的，是这样理解的吗

追答

对，正确。