首页 > Excel专区 > Excel教程 >

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

Excel教程 2021-12-16 17:46:06

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

下图所示。

在单元格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)

其中:

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列的数组:

{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比较,得到数组:

{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公式excel技巧excel教程excel表格

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.