excel公式技巧：从单元格区域的字符串中提取唯一值( 四 ) _知识经验

我们可以看到，这里对INDEX的行参数和列参数使用了两个构造：
N(IF(1,1+INT((Arry2-1)/MAX(Arry1))))
和
N(IF(1,1+MOD(Arry2-1,MAX(Arry1))))
这里引用了名称Arry2：
ROW(INDIRECT(“1:”& (MAX(Arry1)*ROWS(Data))))
上文中已计算出Arry1的最大值为4 ， Data中的行数为10 ，因此上面的公式转换为：
ROW(INDIRECT(“1:” & 40))
于是， Arry2为由1至40组成的单列数组：
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40}
这样，上述构造中的：
1+INT((Arry2-1)/MAX(Arry1))
成为：
1+INT(({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40}-1)/4)
转换为：
1+INT({0;0.25;0.5;0.75;1;1.25;1.5;1.75;2;2.25;2.5;2.75;3;3.25;3.5;3.75;4;4.25;4.5;4.75;5;5.25;5.5;5.75;6;6.25;6.5;6.75;7;7.25;7.5;7.75;8;8.25;8.5;8.75;9;9.25;9.5;9.75})
转换为：
1+{0;0;0;0;1;1;1;1;2;2;2;2;3;3;3;3;4;4;4;4;5;5;5;5;6;6;6;6;7;7;7;7;8;8;8;8;9;9;9;9}
结果为：
{1;1;1;1;2;2;2;2;3;3;3;3;4;4;4;4;5;5;5;5;6;6;6;6;7;7;7;7;8;8;8;8;9;9;9;9;10;10;10;10}
同样，列参数构造中的：
1+MOD(Arry2-1,MAX(Arry1))
可以转换为：
{1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4}
由于这两个数组都具有相同的向量位移（即它们都是单列数组），我们知道，将它们传递给INDEX函数进行处理时，这些数组中相对应的元素将被“配对” ，因此我们将指示INDEX返回一个值数组，其row_num和col_num参数将依次为：1/1、1/2、1/3、1/4、2/1、2/2、2/3、2/4、3/1 ， … ，依此类推。也就是说，我们将依次从上文生成的10行4列的数组中取值。
现在定义名称Arry3的公式可以转换成：
INDEX({“Amaranth”,”Bronze”,”Silver”,””;”Bronze”,””,””,””;””,””,””,””;”Violet”,”Bronze”,”Amaranth”,””;”Red”,””,””,””;”Puce”,”Bronze”,””,””;”Taupe”,”Ochre”,”Bronze”,”Cerise”;”Silver”,”Red”,”Cerise”,”Orange”;””,””,””,””;”Cerise”,””,””,””},{1;1;1;1;2;2;2;2;3;3;3;3;4;4;4;4;5;5;5;5;6;6;6;6;7;7;7;7;8;8;8;8;9;9;9;9;10;10;10;10},{1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4;1;2;3;4})
转换成最终的结果：
{“Amaranth”;”Bronze”;”Silver”;””;”Bronze”;””;””;””;””;””;””;””;”Violet”;”Bronze”;”Amaranth”;””;”Red”;””;””;””;”Puce”;”Bronze”;””;””;”Taupe”;”Ochre”;”Bronze”;”Cerise”;”Silver”;”Red”;”Cerise”;”Orange”;””;””;””;””;”Cerise”;””;””;””}
至此，成功地将原来的10行4列数组转换成40行1列的数组。这样，就可以将这个数组传递给MATCH函数而不会出错了。
注意，在上述构造中，前面的部分为N(IF(1,是为了强制INDEX返回数组，详细原因参见《Excel公式技巧03：INDEX函数，给公式提供数组》。

excel公式技巧：从单元格区域的字符串中提取唯一值( 四 )

猜你喜欢