Excel求连续数据之和的最大值
发布时间:2023-04-09 08:58: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)<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列的数组:
{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。
小结
获得这样一个矩阵数组真是不简单!
建议自已输入公式进行反复调试和体会,多练练手。
猜你喜欢
- 今天在office文档中打开超链接时报出如上图的错误,之前也出现过这样的问题,但是由于太忙,当时是直接右键超链接文本复制超链接,然后打开浏览
- 电脑蓝屏出现代码0x0000013a怎么办?当我们的电脑出现蓝屏,并提示错误代码0x0000013a该怎么办呢,怎么解决电脑蓝屏出现错误代码
- 在输入文档时,我们经常会用五角星来说明某事物的重要性,那你知道如何快速插入五角星吗?这里给大家介绍一个快速输入的技巧那就是利用搜狗输入法,直
- 我们在对别人的word文档进行修订时,可启动Word 的修订功能,进入到修订状态中,这样用户对文档进行的修改操作,都会通过修订标记显示出来,
- 更新win10系统后,蓝屏启动,win10critical_process_died错误代码提示。这一原因小编认为应该是系统中的部件有些损坏
- 许多用户在使用Excel的时候,经常需要使用到绘图功能,基本大部分用户都会,而导出图片就不会了,一起来了解一下吧许多用户在使用Excel的时
- 现在的年轻人都喜欢经常更换图像啥的,像有的人还喜欢经常更换win10的壁纸,一旦壁纸数量多起了也不好管理。这就需要删掉些壁纸啦,可是自己又不
- 1、找到页面设置对话框,不同的版本,在不同位置,WORD2003里,从文件——页面设置;WORD2010_2013里,从页面布局——页面设置
- 对于电脑系统来说,一般都有非常多的重要文件,而microsoft visual就是其中之一,许多小伙伴在不知情的情况下不小心删除了,这种情况
- 用word时候。在修改、审阅word文档时,有时需要添加批注,写下笔记等,那么word批注怎么做?下面小编就为大家介绍word编辑批注方法,
- Office 十大新功能:1、Office 中简洁的Modern风格的用户界面2、Office 中的新的开始屏幕显示了一系列的文档选项3、O
- 问题:本人非常重要的Word资料,本来想保留A部分,删除B部分,结果把A部分给删除了。并且存盘,然后关掉Word2003关机了。今早才发现把
- Win10电脑没有声音?那么可能是你的声卡驱动出现了问题,我们需要点开设备管理器查看声卡设备的驱动是否出问题,当出现:声卡由于其配置信息(注
- 今天教大家一个小技巧,非常的实用,那就是在word文档里快速对齐姓名的方法。学会这个小技巧,你就可以省下很大的时间和精力,如果你还不会这项操
- Word如何插图不超出边框?有不少用户都是用过Word这款软件,而为了丰富我们的Word内容,有的时候需要在文中插入一些图片,不过当我们插图
- “开发工具”功能区提供了很多的指令,人们用起来也非常的方便和快捷,但在默认情况下,开发工具功能区是不显示的,那么如何让它显示呢?下面简单介绍
- excel中F4键有什么用?excel中有很多功能,有很多不是很常用,所以不知道该怎么使用,但是f4对于部分人来说还是很常用的,今天我们就来
- 写word过程中,在写好word后,就需要进行生成目录,在文档修改过程中,要更新word文档目录,那么word文档目录如何生成呢?如何生成w
- 用Word我们可以制作出各种贺卡和好看的卡片,在制作贺卡的过程中我们都会将Word的背景换上好看的颜色,或者是给Word背景弄个漂亮的图片,
- 关注职场办公,分享实用干货,洞察科技资讯,这里是「职场科技范」。当领导发了100份PDF文档给你,并且要求在下班前整理成Word给他,你是不