要转换成下面的6行6列数组:
{3,15,0,0,3,0;0,6,0,0,0,0;0,0,5,0,0,0;3,0,0,0,0,0;0,1,0,0,0,0;0,0,0,0,0,0}
这将通过将一个数组传递给INDEX函数的参数row_num来实现,这个作为参数值的数组为:
{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}
那么,如何生成这个数组呢?
有许多方法,下面是其中的一种:
COLUMNS(Range1)*Arry2-TRANSPOSE(COLUMNS(Range1)-Arry1)
其中,名称Arry1:
=ROW(INDIRECT(“1:”&COLUMNS(Range1)))
转换为:
{1;2;3;4;5;6}
名称:Arry2:
=ROW(INDIRECT(“1:”&ROWS(Range1)))
转换为:
{1;2;3;4;5;6}
将其代入上面的公式中:
COLUMNS(Range1)*{1;2;3;4;5;6}-TRANSPOSE(COLUMNS(Range1)-{1;2;3;4;5;6})
由于示例中Range1的列数为6,故公式转换为:
6*{1;2;3;4;5;6}-TRANSPOSE(6-{1;2;3;4;5;6})
转换为:
6*{1;2;3;4;5;6}-TRANSPOSE({5;4;3;2;1;0})
转换为:
6*{1;2;3;4;5;6}-{5,4,3,2,1,0}
转换为:
{6;12;18;24;30;36}-{5,4,3,2,1,0}
正交的两个数组相减,得到:
{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}
这正是我们需要的 。
现在,如上所述,我们将此数组作为参数row_num的值传递给INDEX函数 。这里,确保我采用了必要的技术来强制INDEX对一组值进行操作(更多信息,请参见《Excel公式技巧03:INDEX函数,给公式提供数组》),因此:
INDEX(FREQUENCY(0+(Range1&0),0+(Range1&0)),N(IF(1,COLUMNS(Range1)*Arry2-TRANSPOSE(COLUMNS(Range1)-Arry1))))
转换为:
INDEX({3;15;0;0;3;0;0;6;0;0;0;0;0;0;5;0;0;0;3;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0},{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})
得到:
{3,15,0,0,3,0;0,6,0,0,0,0;0,0,5,0,0,0;3,0,0,0,0,0;0,1,0,0,0,0;0,0,0,0,0,0}
再回到公式的主要构造:
MIN(IF(IF(Range1<>””,COUNTIF(Range1,Range1)+1/(Range1*10^6))=LARGE(IF(INDEX(FREQUENCY(0+(Range1&0),0+(Range1&0)),N(IF(1,COLUMNS(Range1)*Arry2-TRANSPOSE(COLUMNS(Range1)-Arry1)))),IF(Range1<>””,COUNTIF(Range1,Range1)+1/(Range1*10^6))),ROWS($1:1)),Range1))
将上面生成的中间结果代入:
MIN(IF({3.000001,FALSE,3.000001,FALSE,3.00000016666667,3.00000016666667;3.000001,6.0000002,FALSE,FALSE,FALSE,3.00000016666667;FALSE,FALSE,5.0000005,5.0000005,5.0000005,FALSE;3.00000025,3.00000025,FALSE,FALSE,FALSE,5.0000005;FALSE,1.00000033333333,FALSE,3.00000025,FALSE,FALSE;6.0000002,6.0000002,6.0000002,6.0000002,6.0000002,5.0000005}=LARGE(IF({3,15,0,0,3,0;0,6,0,0,0,0;0,0,5,0,0,0;3,0,0,0,0,0;0,1,0,0,0,0;0,0,0,0,0,0},{3.000001,FALSE,3.000001,FALSE,3.00000016666667,3.00000016666667;3.000001,6.0000002,FALSE,FALSE,FALSE,3.00000016666667;FALSE,FALSE,5.0000005,5.0000005,5.0000005,FALSE;3.00000025,3.00000025,FALSE,FALSE,FALSE,5.0000005;FALSE,1.00000033333333,FALSE,3.00000025,FALSE,FALSE;6.0000002,6.0000002,6.0000002,6.0000002,6.0000002,5.0000005}),ROWS($1:1)),Range1))
转换为:
MIN(IF({3.000001,FALSE,3.000001,FALSE,3.00000016666667,3.00000016666667;3.000001,6.0000002,FALSE,FALSE,FALSE,3.00000016666667;FALSE,FALSE,5.0000005,5.0000005,5.0000005,FALSE;3.00000025,3.00000025,FALSE,FALSE,FALSE,5.0000005;FALSE,1.00000033333333,FALSE,3.00000025,FALSE,FALSE;6.0000002,6.0000002,6.0000002,6.0000002,6.0000002,5.0000005}=LARGE({3.000001,FALSE,FALSE,FALSE,3.00000016666667,FALSE;FALSE,6.0000002,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,5.0000005,FALSE,FALSE,FALSE;3.00000025,FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,1.00000033333333,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE,FALSE},ROWS($1:1)),Range1))
这里ROWS($1:1)=1,转换为:
MIN(IF({3.000001,FALSE,3.000001,FALSE,3.00000016666667,3.00000016666667;3.000001,6.0000002,FALSE,FALSE,FALSE,3.00000016666667;FALSE,FALSE,5.0000005,5.0000005,5.0000005,FALSE;3.00000025,3.00000025,FALSE,FALSE,FALSE,5.0000005;FALSE,1.00000033333333,FALSE,3.00000025,FALSE,FALSE;6.0000002,6.0000002,6.0000002,6.0000002,6.0000002,5.0000005}=6.0000002,Range1))
推荐阅读
- excel技巧:强制返回数组的公式技术
- 当同事偷偷修改了你的Excel文件名.....
- 多个工资表汇总 的最简单公式
- 复合饼图的做法,一分钟学会
- 《Excel平安经》
- 这种高难度的Excel多层柱形图,99%的人都不会
- 比Excel透视表好用10倍都不止,“超级”透视表来了!
- 这10个最经典的Excel小技巧,关键时能救急!
- excel技巧:获取最大年增长率对应的值
- excel技巧:在主工作表中汇总多个工作表中满足条件的值