Excel求连续数据之和的最大值

求连续n个数据中所有连续m个数据之和的最大值。

如下所示。

在单元格B5中，给出了计算连续几年工资总和最大值的公式。该示例是连续4年工资总和的最大值(由单元格A5指定)。

在工作表的A10单元格中，它是2008年至2011年的工资总和，B10是2009年至2012年的工资总和，以此类推。其中单元格B10中的值是所有4个连续薪资总和的最大值。

在工作表中，将单元格A5命名为“数字”。我们可以修改单元格A5中的年数，以找到指定年数的最大薪资总和。

公式思维

先获取连续数据。如果是4年，有7组连续数据。因为总共有10个数据，形成了一个10行 7列的数组，每一列中要计算的数据对应该列的列号。例如，第一列是来自第一列的4个数据，第二列是来自第二列的4个数据。然后，将这些数据相加，形成一个有7个值的数组。最后，取出它的最大值。

公式分析

单元格B5中的数组公式为：

=MAX(MMULT(A8:J8)，(ABS(TRANSPOSE(COLUMN(a 8: j8))-COLUMN(OFFSET(a 8: j8，0，0，1，COLUMNS(a 8: j8)-Number(1))-(Number-1)/Number(2)))

其中：

COLUMN(A8:J8)的值为{1，2，3，4，5，6，7，8，9，10}。

转置(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)-number1)为OFFSET(A8:J8，0，0，1，7)，其值为A8:G8。

Column (offset (a8:j8，0，0，1，columns (a8:j8)-number1))是COLUMN(A8:G8)，值为{1，2，3，4，5，6，7}。

这样，公式：

转置(COLUMN(a 8: j8))-COLUMN(OFFSET(a 8: j8，0，0，1，COLUMN(a 8: j8)-Number 1))

变成：

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

结果是10行7列的数组：

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

从这个数组中减去(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进行比较，得到数组：

{真、假、假、假、假、假、假、假；

真，真，假，假，假，假，假；

真，真，真，假，假，假，假；

真，真，真，真，假，假，假；

假，真，真，真，真，假，假；

假，假，真，真，真，真，假；

假，假，假，真，真，真，真；

假，假，假，假，真，真，真；

假，假，假，假，假，真，真；

假，假，假，假，假，假，真}

在前面加上双减号()，将真值转换为数字1，将假值转换为数字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，上面的数组)将a83360j8形成的1行10列的数组乘以上面得到的10行7列的数组，得到1行7列的数组：

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

最后，使用MAX函数获取数组中的最大值。

接下来，让我们看看工作表第11行的公式。例如，以下是A11中的公式：

=(COLUMNS(a 8: $ J $ 8)=Number)

将当前单元格所在的列j与列之间的列数与数字值(在本例中为4)进行比较，如果大于或等于数字，则为真，并使用双减号将其转换为数字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之后的单元格。然后，对应于当前单元格的第八行中的单元格形成需要求和的单元格区域，并使用求和。

拖到A10到J10。

总结

得到这样的矩阵阵列不容易！

建议你输入公式反复调试和体验，多练手。