Excel公式技巧中的降维技术( 三 ) _知识经验

如何得到这个50行1列的数组？
（或者，一个1行50列的数组：
{“A1″,”B1″,”C1″,”D1″,”E1″,”A2″,”B2″,”C2″,”D2″,”E2″,”A3″,”B3″,”C3″,”D3″,”E3″,”A4″,”B4″,”C4″,”D4″,”E4″,”A5″,”B5″,”C5″,”D5″,”E5″,”A6″,”B6″,”C6″,”D6″,”E6″,”A7″,”B7″,”C7″,”D7″,”E7″,”A8″,”B8″,”C8″,”D8″,”E8″,”A9″,”B9″,”C9″,”D9″,”E9″,”A10″,”B10″,”C10″,”D10″,”E10″}）
通常使用下面的公式：
=INDEX(A1:E10,N(IF(1,1+(INT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1)/COLUMNS(A1:E10))))),N(IF(1,1+(MOD((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:E10)*COLUMNS(A1:E10)))-1),COLUMNS(A1:E10))))))
其思路是将这个二维数组中的每个元素精确地索引一次，上面的公式转换为：
=INDEX(A1:E10,N(IF(1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4;5;5;5;5;5;6;6;6;6;6;7;7;7;7;7;8;8;8;8;8;9;9;9;9;9;10;10;10;10;10})),N(IF(1,{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})))
这里的关键是，传递给INDEX函数的数组（用于参数row_num和参数column_num的数组）都是相同的向量类型（均为列向量），从而确保了由INDEX产生的数组也是这种向量类型。根据其定义，列向量当然是一维的。这里使用的技术请参阅《Excel公式技巧03：INDEX函数，给公式提供数组》、《Excel公式练习44：从多列中返回唯一且按字母顺序排列的列表》。
可以看出， INDEX结构具有不可否认的优势，不仅可以将其用于重新定义工作表区域的维度，还可以重新定义公式中某些其他子函数产生的数组的维度。
然而，还可以使用更短的公式：
=T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),”R0C00000″),0))
注意，上述公式结构使用了函数T ，因此要求单元格区域A1:E10内的值是非数字的。对于由数值组成的单元格区域，可以使用N函数。对于包含混合数据类型的区域，建议使用INDEX方法。
关键是要利用MODE.MULT函数的特性来返回返回一维数组，无论传递给该函数的数组本身是一维数组还是二维数组，这都同样适用。然而， MODE.MULT函数自身也存在缺点：传递的数组中的任何元素都要至少出现一次，否则将出错，这意味着我们要强制解决该问题。因此，这里故意使用了扩展的单元格区域A1:E20：
1+MOD(ROW(A1:E20)-1,10)
转换为：
1+MOD({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1,10)
转换为：
1+MOD({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},10)
转换为：
1+{0;1;2;3;4;5;6;7;8;9;0;1;2;3;4;5;6;7;8;9}
得到：
{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}
此时，公式中的：
T(INDIRECT(TEXT(MODE.MULT(10^5*(1+MOD(ROW(A1:E20)-1,10))+COLUMN(A1:E10)),”R0C00000″),0))
转换为：
T(INDIRECT(TEXT(MODE.MULT(10^5*{1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10}+COLUMN(A1:E10)),”R0C00000″),0))
转换为：
T(INDIRECT(TEXT(MODE.MULT({100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000;100000;200000;300000;400000;500000;600000;700000;800000;900000;1000000}+{1,2,3,4,5}),”R0C00000″),0))

Excel公式技巧中的降维技术( 三 )

猜你喜欢