求連續(xù)N個(gè)數(shù)據(jù)中所有連續(xù)M個(gè)數(shù)據(jù)之和的最大值。

下圖所示。

在單元格B5中，給出了一個(gè)求連續(xù)幾年薪水之和的最大值的公式。示例中是每連續(xù)4年（由單元格A5指定）薪水之和的最大值。

工作表的單元格A10中是2008年至2011年的薪水之和、B10是2009年至2012年的薪水之和，……，依此類推。其中單元格B10中的值就是所有連續(xù)4連薪水之和的最大值。

在工作表中，將單元格A5命名為“Number”。我們可以修改單元格A5中的年數(shù)，從而求出指定年數(shù)的薪水之和的最大值。

公式思路

先要獲取連續(xù)的數(shù)據(jù)。如果是4年，那么有7組連續(xù)的數(shù)據(jù)。又因?yàn)榭偣灿?0個(gè)數(shù)據(jù)，因此形成一個(gè)10行×7列的數(shù)組，在每列中需要計(jì)算的數(shù)據(jù)與該列列號(hào)對(duì)應(yīng)，例如第1列是從第1個(gè)開始的4個(gè)數(shù)據(jù)，第2列是從第2個(gè)開始的4個(gè)數(shù)據(jù)。然后，將這些數(shù)據(jù)相加，形成一個(gè)有7個(gè)值的數(shù)組。最后，取出其最大值。

公式解析

單元格B5中的數(shù)組公式為：

=MAX(MMULT(A8:J8,–(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/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列的數(shù)組轉(zhuǎn)置為10行1列的數(shù)組。

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))

變?yōu)椋?/p>

{1;2;3;4;5;6;7;8;9;10}-{1,2,3,4,5,6,7}

其結(jié)果為一個(gè)10行7列的數(shù)組：

{0,-1,-2,-3,-4,-5,-6;

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}

此數(shù)組再減去(Number-1)/2，本例中即(4-1)/2=1.5，得到數(shù)組：

{-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函數(shù)取上面的數(shù)組的絕對(duì)值，得到數(shù)組：

{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}

將上面的數(shù)組與Number/2即4/2=2比較，得到數(shù)組：

{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}

前面加上雙減號(hào)（–），將True值轉(zhuǎn)換為數(shù)字1，F(xiàn)alse值轉(zhuǎn)換為0。即得到數(shù)組：

{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,上面的數(shù)組)將A8:J8形成的1行10列數(shù)組與上面獲得的10行7列數(shù)組相乘得到一個(gè)1行7列的數(shù)組：

{237348,244540,236394,228744,225739,220894,238728}

最后，使用MAX函數(shù)獲取數(shù)組中的最大值。

下面，來看看工作表第11行中的公式，例如下面是A11中的公式：

=–(COLUMNS(A8:$J$8)>=Number)

將當(dāng)前單元格所在的列至列J的列數(shù)與Number值（本例中為4）比較，若大于等于Number，則為True，并使用雙減號(hào)將其轉(zhuǎn)換為數(shù)字1。

拖動(dòng)A11至J11。

下面，來看看工作表第10行中的公式，例如下面是A10中的公式：

=IF(A11,SUM(A8:INDEX(A8:$J$8,1,Number)),0)

如果對(duì)應(yīng)的第11行的單元格（本例為A11）中為1，那么進(jìn)行求和，INDEX(A8:$J$8,1,Number)獲取當(dāng)前單元格之上對(duì)應(yīng)的第8行中的單元偏移Number之后的單元格。然后與當(dāng)前單元格之上對(duì)應(yīng)的第8行中的單元格組成要求和的單元格區(qū)域，使用SUM函數(shù)求和。

拖到A10至J10。

小結(jié)

獲得這樣一個(gè)矩陣數(shù)組真是不簡單！

建議自已輸入公式進(jìn)行反復(fù)調(diào)試和體會(huì)，多練練手。