Excel公式：从多列中返回唯一且按字母顺序排列的列表( 二 ) _知识经验

ROW(INDIRECT(“1:”&5*4))
得到：
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}
再看Arry2：
=1+INT((Arry1-1)/COLUMNS(Range1))
转换为：
1+INT(({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1)/5)
转换为：
1+INT({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}/5)
转换为：
1+INT({0;0.2;0.4;0.6;0.8;1;1.2;1.4;1.6;1.8;2;2.2;2.4;2.6;2.8;3;3.2;3.4;3.6;3.8})
转换为：
1+{0;0;0;0;0;1;1;1;1;1;2;2;2;2;2;3;3;3;3;3}
得到：
{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4}
接着看Arry3：
=1+MOD(Arry1-1,COLUMNS(Range1))
转换为：
1+MOD({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},5)
转换为：
1+{0;1;2;3;4;0;1;2;3;4;0;1;2;3;4;0;1;2;3;4}
得到：
{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5}
再回到Arry4 。可以转换为：
INDEX(Range1,N(IF(1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4})),N(IF(1,{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})))
这里使用了强制INDEX返回数组的技术，详情可参阅《Excel公式技巧03：INDEX函数，给公式提供数组》。上述公式可转换为：
INDEX(Range1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4},{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})
现在应该可以看清楚为INDEX函数的每个参数传递数组的原因了，因为上述公式等价于执行下列每个公式：
INDEX(Range1,1,1)
INDEX(Range1,1,2)
INDEX(Range1,1,3)
INDEX(Range1,1,4)
INDEX(Range1,1,5)
INDEX(Range1,2,1)
INDEX(Range1,2,2)
…
INDEX(Range1,4,5)
因此，Arry4的结果为：
{“Due”;””;”Otto”;””;”Otto”;””;””;””;””;”Tre”;”Sei”;”Cinque”;””;”Quattro”;”Otto”;”Due”;””;””;”Quattro”;”Otto”}
而Excel将Range1解析为：
{“Due”,””,”Otto”,””,”Otto”;””,””,””,””,”Tre”;”Sei”,”Cinque”,””,”Quattro”,”Otto”;”Due”,””,””,”Quattro”,”Otto”}
我们可以看到这两个数组中的值没有任何区别。唯一不同的是，Range1包含一个4行5列的二维数组，而Arry4是通过简单地将Range1中的每个元素进行索引而得出的，实际上是20行1列的一维区域。
好了，现在就可以使用我们掌握的常用的适用于一维区域的技术来操作该数组了！
4. 再看看主公式中的：
INDEX(Arry4,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>””,MATCH(Range1,Arry4,0)),Arry1),COUNTIF(Range1,”<“&Arry4)),ROWS($1:1)),IF(Arry4<>””,COUNTIF(Range1,”<“&Arry4)),0))
先看看这部分：
IF(Range1<>””,MATCH(Range1,Arry4,0))
转换为：
IF({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE},MATCH(Range1,Arry4,0))
使用Range1和Arry4替换，得到：
IF({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE},MATCH({“Due”,””,”Otto”,””,”Otto”;””,””,””,””,”Tre”;”Sei”,”Cinque”,””,”Quattro”,”Otto”;”Due”,””,””,”Quattro”,”Otto”},{“Due”;””;”Otto”;””;”Otto”;””;””;””;””;”Tre”;”Sei”;”Cinque”;””;”Quattro”;”Otto”;”Due”;””;””;”Quattro”;”Otto”},0))

Excel公式：从多列中返回唯一且按字母顺序排列的列表( 二 )

猜你喜欢