|
下图所示是《Excel函数学习23：TRANSPOSE函数》的示例3。 在单元格B5中，给出了一个求连续几年薪水之和的最大值的公式。示例中是每连续4年（由单元格A5指定）薪水之和的最大值。 工作表的单元格A10中是2008年至2011年的薪水之和、B10是2009年至2012年的薪水之和，……，依此类推。其中单元格B10中的值就是所有连续4连薪水之和的最大值。 在工作表中，将单元格A5命名为“Number”。我们可以修改单元格A5中的年数，从而求出指定年数的薪水之和的最大值。
公式思路 先要获取连续的数据。如果是4年，那么有7组连续的数据。又因为总共有10个数据，因此形成一个10行×7列的数组，在每列中需要计算的数据与该列列号对应，例如第1列是从第1个开始的4个数据，第2列是从第2个开始的4个数据。然后，将这些数据相加，形成一个有7个值的数组。最后，取出其最大值。
公式解析 单元格B5中的数组公式为： =MAX(MMULT(A8:J8,--(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number 1))-(Number-1)/2)<Number/2))) 其中： COLUMN(A8:J8)的值为{1,2,3,4,5,6,7,8,9,10}。 TRANSPOSE(COLUMN(A8:J8))的值为{1;2;3;4;5;6;7;8;9;10}。 也就是说，把1行10列的数组转置为10行1列的数组。
COLUMNS(A8:J8)的值为10，由于Number是单元格A5中的值，本例中为4，因此COLUMNS(A8:J8)-Number 1的值为10-4 1，即为7。 OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number 1)即为OFFSET(A8:J8,0,0,1,7)，值为A8:G8。
COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number 1))即为COLUMN(A8:G8)，值为{1,2,3,4,5,6,7}。
这样，公式： TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number 1)) 变为： {1;2;3;4;5;6;7;8;9;10}-{1,2,3,4,5,6,7} 其结果为一个10行7列的数组： 1,0,-1,-2,-3,-4,-5; 2,1,0,-1,-2,-3,-4; 3,2,1,0,-1,-2,-3; 4,3,2,1,0,-1,-2; 5,4,3,2,1,0,-1; 6,5,4,3,2,1,0; 7,6,5,4,3,2,1; 8,7,6,5,4,3,2; 9,8,7,6,5,4,3} 此数组再减去(Number-1)/2，本例中即(4-1)/2=1.5，得到数组： {-1.5,-2.5,-3.5,-4.5,-5.5,-6.5,-7.5; -0.5,-1.5,-2.5,-3.5,-4.5,-5.5,-6.5; 0.5,-0.5,-1.5,-2.5,-3.5,-4.5,-5.5; 1.5,0.5,-0.5,-1.5,-2.5,-3.5,-4.5; 2.5,1.5,0.5,-0.5,-1.5,-2.5,-3.5; 3.5,2.5,1.5,0.5,-0.5,-1.5,-2.5; 4.5,3.5,2.5,1.5,0.5,-0.5,-1.5; 5.5,4.5,3.5,2.5,1.5,0.5,-0.5; 6.5,5.5,4.5,3.5,2.5,1.5,0.5; 7.5,6.5,5.5,4.5,3.5,2.5,1.5} 然后，使用ABS函数取上面的数组的绝对值，得到数组： {1.5,2.5,3.5,4.5,5.5,6.5,7.5; 0.5,1.5,2.5,3.5,4.5,5.5,6.5; 0.5,0.5,1.5,2.5,3.5,4.5,5.5; 1.5,0.5,0.5,1.5,2.5,3.5,4.5; 2.5,1.5,0.5,0.5,1.5,2.5,3.5; 3.5,2.5,1.5,0.5,0.5,1.5,2.5; 4.5,3.5,2.5,1.5,0.5,0.5,1.5; 5.5,4.5,3.5,2.5,1.5,0.5,0.5; 6.5,5.5,4.5,3.5,2.5,1.5,0.5; 7.5,6.5,5.5,4.5,3.5,2.5,1.5} 将上面的数组与Number/2即4/2=2比较，得到数组： {True,False, False, False, False, False, False; True,True, False, False, False, False, False; True,True,True, False, False, False, False; True,True,True,True, False, False, False; False,True,True,True,True, False, False; False, False, True,True,True,True, False; False, False, False,True,True,True,True; False, False, False, False, True,True,True; False, False, False, False,False, True,True; False, False, False, False,False,False,True} 前面加上双减号（--），将True值转换为数字1，False值转换为0。即得到数组： {1,0,0,0,0,0,0; 1,1, 0,0,0,0,0; 1,1,1,0,0,0,0; 1,1,1,1,0,0,0; 0,1,1,1,1,0,0; 0,0,1,1,1,1,0; 0,0,0,1,1,1,1; 0,0,0,0,1,1,1; 0,0,0,0,0,1,1; 0,0,0,0,0,0,1}
MMULT(A8:J8,上面的数组)将A8:J8形成的1行10列数组与上面获得的10行7列数组相乘得到一个1行7列的数组： {237348,244540,236394,228744,225739,220894,238728}
最后，使用MAX函数获取数组中的最大值。
下面，来看看工作表第11行中的公式，例如下面是A11中的公式： =--(COLUMNS(A8:$J$8)>=Number) 将当前单元格所在的列至列J的列数与Number值（本例中为4）比较，若大于等于Number，则为True，并使用双减号将其转换为数字1。 拖动A11至J11。
下面，来看看工作表第10行中的公式，例如下面是A10中的公式： =IF(A11,SUM(A8:INDEX(A8:$J$8,1,Number)),0) 如果对应的第11行的单元格（本例为A11）中为1，那么进行求和，INDEX(A8:$J$8,1,Number)获取当前单元格之上对应的第8行中的单元偏移Number之后的单元格。然后与当前单元格之上对应的第8行中的单元格组成要求和的单元格区域，使用SUM函数求和。 拖到A10至J10。
小结 获得这样一个矩阵数组真是不简单！ 建议自已输入公式进行反复调试和体会，多练练手。 下期公式练习预告 Excel公式练习4：将矩形数据区域转换成一行或者一列 例如将下图所示的名为Data的区域： 转换成单独的一列： 或者单独的一行： 有兴趣的朋友可以先试试！ 如果您对本文介绍的内容有什么建议或好的示例，欢迎发送邮件给我：xhdsxfjy@163.com 也可以在本文下面发表留言，留下您的足迹。 本文属原创文章，转载请联系我或者注明出处。
关注《完美Excel》微信公众账号： |
|
|
