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

通常使用下面的公式：
=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产生的数组也是这种向量类型。根据其定义，列向量当然是一维的。?可以看出，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))
转换为：
T(INDIRECT(TEXT(MODE.MULT({100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005;100001,100002,100003,100004,100005;200001,200002,200003,200004,200005;300001,300002,300003,300004,300005;400001,400002,400003,400004,400005;500001,500002,500003,500004,500005;600001,600002,600003,600004,600005;700001,700002,700003,700004,700005;800001,800002,800003,800004,800005;900001,900002,900003,900004,900005;1000001,1000002,1000003,1000004,1000005}),”R0C00000″),0))
转换为：
T(INDIRECT(TEXT({100001;100002;100003;100004;100005;200001;200002;200003;200004;200005;300001;300002;300003;300004;300005;400001;400002;400003;400004;400005;500001;500002;500003;500004;500005;600001;600002;600003;600004;600005;700001;700002;700003;700004;700005;800001;800002;800003;800004;800005;900001;900002;900003;900004;900005;1000001;1000002;1000003;1000004;1000005},”R0C00000”),0))
转换为：
T(INDIRECT({“R1C00001″;”R1C00002″;”R1C00003″;”R1C00004″;”R1C00005″;”R2C00001″;”R2C00002″;”R2C00003″;”R2C00004″;”R2C00005″;”R3C00001″;”R3C00002″;”R3C00003″;”R3C00004″;”R3C00005″;”R4C00001″;”R4C00002″;”R4C00003″;”R4C00004″;”R4C00005″;”R5C00001″;”R5C00002″;”R5C00003″;”R5C00004″;”R5C00005″;”R6C00001″;”R6C00002″;”R6C00003″;”R6C00004″;”R6C00005″;”R7C00001″;”R7C00002″;”R7C00003″;”R7C00004″;”R7C00005″;”R8C00001″;”R8C00002″;”R8C00003″;”R8C00004″;”R8C00005″;”R9C00001″;”R9C00002″;”R9C00003″;”R9C00004″;”R9C00005″;”R10C00001″;”R10C00002″;”R10C00003″;”R10C00004″;”R10C00005”},0))

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

猜你喜欢