求连续时段之和的最大值

楼主 gouweicao78	【题目】已知某机器连续10天运行的监测数据表（每个小时一个数据）。 1、求这10天内连续24小时数据之和最大值是多少？ 2、求出最大值发生的起始时间，答案形式为：第3天06:00（示例，不是最终结果）【说明】第1天的0:00~23:00的24个数据之和第1天的1:00~第2天的0:00的24个数据之和第1天的2:00~第2天的1:00的24个数据之和 …… 【要求】 1、使用函数与公式解决，不使用VBA、定义名称，但可以使用辅助列 2、至少简要描述解题思路 3、本题2009年11月20日截稿；公布答案之后如有更好解法者可继续跟帖。【得分】 1、计算结果正确+2技能分（算出最大值的1分，算出起始时间得1分） 2、使用辅助列方法不再加分，未使用辅助列者可再加3~5技能分 3、一人可以多个无辅助列解法并多次得分，总分控制在12技能分求连续时段之和的最大值（题目）.rar
2楼棉花糖	D30:D31=TEXT(MID(MAX(10^11+(SUBTOTAL(9,OFFSET(A17,ROW(1:10),COLUMN(A:X),,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A17,ROW(2:11),,,COLUMN(A:X))))10^7+ROW(1:10)100+COLUMN(A:X)-1),{3;8},5),"[>1100]0!.00;第0天00!:!0!0") 利用subtotal的支持多维引用，把上下列的数累加起来，假设最大数据大于11，有取巧成分。严格的话，分开两个公式。分开的公式 D28=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,25- COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) D29=TEXT(RIGHT(MAX((SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A15,ROW(2:11),,,COLUMN(A:X))))10^7+ROW(1:10)100+COLUMN(A:X)-1),5),"第0天00!:!0!0") 91个字符=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,24),OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) 附件求连续时段之和的最大值（题目）.rar
3楼 biaotiger1	最大值 =MAX(SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)))-N(OFFSET($B$15,ROW(1:10)+1,COLUMN(A:X)-1,,))) 1、+号前面部分SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))取得列数依次减小的单行区域的和 2、+-号中间部分SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)))取得第二行列数依次增加的单行区域的和二者相加，得到任意25个小时的数据之和 3、-号后面部分-N(OFFSET($B$15,ROW(1:10)+1,COLUMN(A:X)-1,,))减掉上下两行重叠区域的下面一个单元格的数据得到要求的和考虑到07有iferror函数，于是有了下面的解法 =MAX(SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0)) 大意与上面公式相同，不同处仅iferror函数，将offset函数偏移0列的结果处理成0 发生时间 =TEXT(MAX(IF(MMULT(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0)),INT(ROW(1:24)/25+1)),ROW(B16:B25),""))-15,"第0天")&TEXT(MAX(IF(MMULT(TRANSPOSE(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0))),INT(ROW(1:10)/11+1)),ROW(1:24)-1,"")),"00")&":00" 分解来看 1、TEXT(MAX(IF(MMULT(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0)),INT(ROW(1:24)/25+1)),ROW(B16:B25),""))-15,"第0天") 此部分返回的是和的最大值起始时间所在天数（D28为最大值的数值所在单元格，我试验了，如果将D28的公式代入后，公式可用） --()部分返回一个10行24列的数组，加上mmult(--(),INT(ROW(1:24)/25+1))结果返回一个10行一列的数组{0;0;0;0;1;0;0;0;0;0} max(if(...))部分返回最大值（实际就是1）所在位置 -15得到对应的天数 text()函数返回规定的格式 2、TEXT(MAX(IF(MMULT(TRANSPOSE(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0))),INT(ROW(1:10)/11+1)),ROW(1:24)-1,"")),"00")&":00" 此部分与上面1结构相似，不同处在于用transpose函数将10行24列的数组转置为24行10列的数组后，同样运用mmult矩阵运算 max(if(...))部分返回最大值（实际就是1）所在位置 text()函数返回规定格式 3、text(...)&text(...)得到要求的结果公式太长，所以先贴出来啦～～～等待高手高招高明的解法
4楼 biaotiger1	最大值 =MAX(SUBTOTAL(9,OFFSET(B15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET(B15,ROW(1:10)+1,,,COLUMN(A:X)-1)),)) 最大值发生的起始时间 =TEXT(MAX(IF(D28=SUBTOTAL(9,OFFSET(B15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET(B15,ROW(1:10)+1,,,COLUMN(A:X)-1)),),ROW(1:10)*100+COLUMN(A:X)-1)),"第0天00")&":00" 第一个小板凳实在太长了，似乎第二个稍好些最大值的思路同上面二楼，版主不用费心了最大值发生的起始时间跟二楼相比，缩短了一些
5楼轻舟上逆	掀一门帘子。求连续时段之和的最大值（题目）.rar
6楼 rongjun	第一Q： =MAX(MMULT(ROUND(MOD(SMALL(ROW(1:10)/1%%+COLUMN(A:X)100+B16:Y25,ROW(1:217)-1+COLUMN(A:X)),100),2),ROW(1:24)^0)) 第二Q： =TEXT(RIGHT(MAX(MMULT(ROUND(MOD(SMALL(ROW(1:10)/1%%+COLUMN(A:X)100+B16:Y25,ROW(1:217)-1+COLUMN(A:X)),100),2),ROW(1:24)^0)10^6+SMALL(ROW(1:10)100+COLUMN(A:X)-1,ROW(1:217))),4),"第0天00:!0!0") 求连续时段之和的最大值_rongjun.rar
7楼 knifefox	=MAX(MMULT(N(INDIRECT(TEXT(TEXT((ROW(1:216)-1)/24+B15:Y15,"dhh")+1602,"r0c00"),)),ROW(1:24)^0)) =TEXT((MATCH(D28,MMULT(N(INDIRECT(TEXT(TEXT((ROW(1:216)-1)/24+B15:Y15,"dhh")+1602,"r0c00"),)),ROW(1:24)^0),)+23)/24,"第d天h:mm")
8楼轻舟上逆	再加一种方法求连续时段之和的最大值（题目）.rar
9楼 gouweicao78	先公布我的答案吧：第1问 90字： =MAX(SUBTOTAL(9,OFFSET(A16,ROW(1:9),,,COLUMN(A:Y)),OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26))) 132字 =MAX(MMULT(N(INDIRECT(TEXT((INT((ROW(1:216)+COLUMN(A:X)-2)/24)+16)100+MOD(ROW(1:216)+COLUMN(A:X)-2,24)+2,"r0c00"),0)),ROW(1:24)^0)) 第2问，151字（未利用第一步成果） =TEXT(MOD(MAX(SUBTOTAL(9,OFFSET(A16,ROW(1:9),,,COLUMN(A:Y)),OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26))10^6+ROW(1:9)/1%+COLUMN(A:Y)-1),10^5),"第0天00")&":00" 164字（直接利用第一步成果） =TEXT((MATCH(D28,MMULT(N(INDIRECT(TEXT((INT((ROW(1:216)+COLUMN(A:X)-2)/24)+16)*100+MOD(ROW(1:216)+COLUMN(A:X)-2,24)+2,"r0c00"),0)),ROW(1:24)^0),0)+23)/24,"第d天h:mm")
10楼 gouweicao78	【感悟】不必排斥辅助列：本题本来有送分（辅助列法，完全可以参照多维引用在连续时段降雨量统计中的应用【题意】本题来源帖中的描述，是“有转角的连续数据”，意思是从第一行最后一个跳到第二行第一个称为“转角”，而另一个关键词是“连续”，构建连续的东西用ROW、COLUMN来做产生连续的数组，是一个常规的解法。【点评】从得到的解法来看，主要有几个关键技术，比如：SUBTOTAL函数统计引用函数产生多维引用数据技术、函数法多个关键字排序技术、MMULT与INDIRECT、TEXT精妙引用结合等，当然，最短公式反而最朴实无华，下面我会进行点评：第一问解A： 1、SUBTOTAL+函数多维引用公式1=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) 公式2=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,24),OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) 公式3=MAX(SUBTOTAL(9,OFFSET(A16,ROW(1:9),,,COLUMN(A:Y)),OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26))) 公式3为目前最短公式，90个字符，注意A16:A25、Z16:Z25都被利用了，这两个区域不能有数值（可以有文本或0），否则会影响计算结果。【回顾函数语法】SUBTOTAL(function_num, ref1, ref2, ...) 其中，公式1是最早原型，SUBTOTAL+SUBTOTAL的解法，忘记了SUBTOTAL函数可以使用n个ref参数。 3个公式的解法，实际上都是利用OFFSET将连续时段分成上下两行的单元格，再利用SUBTOTAL函数对OFFSET产生的多维引用进行求和。公式2共91字，其中的第1个OFFSET始终用24个连续单元格，也就是不断地将引用“推后”，如图：第2个OFFSET则不断在下一行“推进”。公式3与此类似，不过是将第1个OFFSET的24个整体“推后”改为固定尾部在Z列，这样实际使用单元格也就只有Z列部分会影响计算结果。如图，OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26)效果就是红色部分、OFFSET(A16,ROW(1:9),,,COLUMN(A:Y))则是蓝色部分。 biaotiger的解法已自己解释了，这里就不再点评。 2、函数法多关键字排序技术 =MAX(MMULT(ROUND(MOD(SMALL(ROW(1:10)/1%%+COLUMN(A:X)100+B16:Y25,ROW(1:217)-1+COLUMN(A:X)),100),2),ROW(1:24)^0)) =MAX(MMULT(MOD(SMALL(ROW(1:10)10^7+COLUMN(B:Y)10^4+B16:Y25,COLUMN(A:X)+ROW(1:217)-1),10^4),ROW(1:24)^0)) rongjun和轻舟上逆兄的解法都使用了这项经典技术，利用row10^7+column10^4或者/1%%（相当于10^4），先把行、列号放大，再加上B16:Y25的数据，并使用SMALL函数进行排序得到第1~24个、第2~25个、第3~26个……数据，再使用MOD进行还原（去除放大的行列号），最后用MMULT求和。事实上，这两个公式都可以用普通公式而不需按CTRL+SHIFT+ENTER结束形成数组公式。有关问题的理解，可参考： [原创]【公式解析系列】之多关键字排序解法 3、INDIRECT+TEXT精妙引用 =MAX(MMULT(N(INDIRECT(TEXT(TEXT((ROW(1:216)-1)/24+B15:Y15,"dhh")+1602,"r0c00"),)),ROW(1:24)^0)) 本题共有数据10行24列计240个，每连续的24个为一组，因此最后一组从第217开始。 knifefox兄利用时间和日期数据特性，将(ROW(1:216)-1)/24得到以小时计算的序列，即第0小时、1小时……215小时，再加上B15:Y15的0~23小时，获得连续的24个数据，当超过24小时是，比如第25小时则TEXT("25:00","dhh")得到101，可以看为1行01列再加上1602（初始位置，16行02列，即B16单元格），第二次使用TEXT将它变为R1C1引用样式的文本，再利用INDIRECT得到多维引用并用N函数降低维度，最后再使用MMULT函数进行求和。 =MAX(MMULT(N(INDIRECT(TEXT((INT((ROW(1:216)+COLUMN(A:X)-2)/24)+16)100+MOD(ROW(1:216)+COLUMN(A:X)-2,24)+2,"r0c00"),0)),ROW(1:24)^0)) 这个解法中，利用ROW(1:216)+COLUMN(A:X)-2构建一个从0~23、1~24、2~25……等连续数组，并结合/24与INT、MOD(……，24）分别得到行、列号，相比knifefox的时间方法而言，就逊色许多了。
11楼 gouweicao78	关于第2问，基本上核心还是在第1问中，如果不使用第1问的计算成果，那么一般都采用多关键字排序技术+MOD还原法得到具体值，再使用TEXT函数得到题目要求的格式。 TEXT格式的使用上，取巧一下可以先+23小时，再使用"第d天h:mm"，也有采用"第0天00!:!0!0"、"第0天00"最后公式再&":00"。如果使用第1问的结果，一般则用MATCH(D28，第1问的核心公式,0)来计算第几个数据。
12楼 Olivia	我看看

楼主 gouweicao78	【题目】已知某机器连续10天运行的监测数据表（每个小时一个数据）。 1、求这10天内连续24小时数据之和最大值是多少？ 2、求出最大值发生的起始时间，答案形式为：第3天06:00（示例，不是最终结果）【说明】第1天的0:00~23:00的24个数据之和第1天的1:00~第2天的0:00的24个数据之和第1天的2:00~第2天的1:00的24个数据之和 …… 【要求】 1、使用函数与公式解决，不使用VBA、定义名称，但可以使用辅助列 2、至少简要描述解题思路 3、本题2009年11月20日截稿；公布答案之后如有更好解法者可继续跟帖。【得分】 1、计算结果正确+2技能分（算出最大值的1分，算出起始时间得1分） 2、使用辅助列方法不再加分，未使用辅助列者可再加3~5技能分 3、一人可以多个无辅助列解法并多次得分，总分控制在12技能分求连续时段之和的最大值（题目）.rar
2楼棉花糖	D30:D31=TEXT(MID(MAX(10^11+(SUBTOTAL(9,OFFSET(A17,ROW(1:10),COLUMN(A:X),,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A17,ROW(2:11),,,COLUMN(A:X))))10^7+ROW(1:10)100+COLUMN(A:X)-1),{3;8},5),"[>1100]0!.00;第0天00!:!0!0") 利用subtotal的支持多维引用，把上下列的数累加起来，假设最大数据大于11，有取巧成分。严格的话，分开两个公式。分开的公式 D28=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,25- COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) D29=TEXT(RIGHT(MAX((SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A15,ROW(2:11),,,COLUMN(A:X))))10^7+ROW(1:10)100+COLUMN(A:X)-1),5),"第0天00!:!0!0") 91个字符=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,24),OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) 附件求连续时段之和的最大值（题目）.rar
3楼 biaotiger1	最大值 =MAX(SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)))-N(OFFSET($B$15,ROW(1:10)+1,COLUMN(A:X)-1,,))) 1、+号前面部分SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))取得列数依次减小的单行区域的和 2、+-号中间部分SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)))取得第二行列数依次增加的单行区域的和二者相加，得到任意25个小时的数据之和 3、-号后面部分-N(OFFSET($B$15,ROW(1:10)+1,COLUMN(A:X)-1,,))减掉上下两行重叠区域的下面一个单元格的数据得到要求的和考虑到07有iferror函数，于是有了下面的解法 =MAX(SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0)) 大意与上面公式相同，不同处仅iferror函数，将offset函数偏移0列的结果处理成0 发生时间 =TEXT(MAX(IF(MMULT(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0)),INT(ROW(1:24)/25+1)),ROW(B16:B25),""))-15,"第0天")&TEXT(MAX(IF(MMULT(TRANSPOSE(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0))),INT(ROW(1:10)/11+1)),ROW(1:24)-1,"")),"00")&":00" 分解来看 1、TEXT(MAX(IF(MMULT(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0)),INT(ROW(1:24)/25+1)),ROW(B16:B25),""))-15,"第0天") 此部分返回的是和的最大值起始时间所在天数（D28为最大值的数值所在单元格，我试验了，如果将D28的公式代入后，公式可用） --()部分返回一个10行24列的数组，加上mmult(--(),INT(ROW(1:24)/25+1))结果返回一个10行一列的数组{0;0;0;0;1;0;0;0;0;0} max(if(...))部分返回最大值（实际就是1）所在位置 -15得到对应的天数 text()函数返回规定的格式 2、TEXT(MAX(IF(MMULT(TRANSPOSE(--(D28=SUBTOTAL(9,OFFSET($B$15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET($B$15,ROW(1:10)+1,,,COLUMN(A:X)-1)),0))),INT(ROW(1:10)/11+1)),ROW(1:24)-1,"")),"00")&":00" 此部分与上面1结构相似，不同处在于用transpose函数将10行24列的数组转置为24行10列的数组后，同样运用mmult矩阵运算 max(if(...))部分返回最大值（实际就是1）所在位置 text()函数返回规定格式 3、text(...)&text(...)得到要求的结果公式太长，所以先贴出来啦～～～等待高手高招高明的解法
4楼 biaotiger1	最大值 =MAX(SUBTOTAL(9,OFFSET(B15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET(B15,ROW(1:10)+1,,,COLUMN(A:X)-1)),)) 最大值发生的起始时间 =TEXT(MAX(IF(D28=SUBTOTAL(9,OFFSET(B15,ROW(1:10),COLUMN(A:X)-1,,25-COLUMN(A:X)))+IFERROR(SUBTOTAL(9,OFFSET(B15,ROW(1:10)+1,,,COLUMN(A:X)-1)),),ROW(1:10)*100+COLUMN(A:X)-1)),"第0天00")&":00" 第一个小板凳实在太长了，似乎第二个稍好些最大值的思路同上面二楼，版主不用费心了最大值发生的起始时间跟二楼相比，缩短了一些
5楼轻舟上逆	掀一门帘子。求连续时段之和的最大值（题目）.rar
6楼 rongjun	第一Q： =MAX(MMULT(ROUND(MOD(SMALL(ROW(1:10)/1%%+COLUMN(A:X)100+B16:Y25,ROW(1:217)-1+COLUMN(A:X)),100),2),ROW(1:24)^0)) 第二Q： =TEXT(RIGHT(MAX(MMULT(ROUND(MOD(SMALL(ROW(1:10)/1%%+COLUMN(A:X)100+B16:Y25,ROW(1:217)-1+COLUMN(A:X)),100),2),ROW(1:24)^0)10^6+SMALL(ROW(1:10)100+COLUMN(A:X)-1,ROW(1:217))),4),"第0天00:!0!0") 求连续时段之和的最大值_rongjun.rar
7楼 knifefox	=MAX(MMULT(N(INDIRECT(TEXT(TEXT((ROW(1:216)-1)/24+B15:Y15,"dhh")+1602,"r0c00"),)),ROW(1:24)^0)) =TEXT((MATCH(D28,MMULT(N(INDIRECT(TEXT(TEXT((ROW(1:216)-1)/24+B15:Y15,"dhh")+1602,"r0c00"),)),ROW(1:24)^0),)+23)/24,"第d天h:mm")
8楼轻舟上逆	再加一种方法求连续时段之和的最大值（题目）.rar
9楼 gouweicao78	先公布我的答案吧：第1问 90字： =MAX(SUBTOTAL(9,OFFSET(A16,ROW(1:9),,,COLUMN(A:Y)),OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26))) 132字 =MAX(MMULT(N(INDIRECT(TEXT((INT((ROW(1:216)+COLUMN(A:X)-2)/24)+16)100+MOD(ROW(1:216)+COLUMN(A:X)-2,24)+2,"r0c00"),0)),ROW(1:24)^0)) 第2问，151字（未利用第一步成果） =TEXT(MOD(MAX(SUBTOTAL(9,OFFSET(A16,ROW(1:9),,,COLUMN(A:Y)),OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26))10^6+ROW(1:9)/1%+COLUMN(A:Y)-1),10^5),"第0天00")&":00" 164字（直接利用第一步成果） =TEXT((MATCH(D28,MMULT(N(INDIRECT(TEXT((INT((ROW(1:216)+COLUMN(A:X)-2)/24)+16)*100+MOD(ROW(1:216)+COLUMN(A:X)-2,24)+2,"r0c00"),0)),ROW(1:24)^0),0)+23)/24,"第d天h:mm")
10楼 gouweicao78	【感悟】不必排斥辅助列：本题本来有送分（辅助列法，完全可以参照多维引用在连续时段降雨量统计中的应用【题意】本题来源帖中的描述，是“有转角的连续数据”，意思是从第一行最后一个跳到第二行第一个称为“转角”，而另一个关键词是“连续”，构建连续的东西用ROW、COLUMN来做产生连续的数组，是一个常规的解法。【点评】从得到的解法来看，主要有几个关键技术，比如：SUBTOTAL函数统计引用函数产生多维引用数据技术、函数法多个关键字排序技术、MMULT与INDIRECT、TEXT精妙引用结合等，当然，最短公式反而最朴实无华，下面我会进行点评：第一问解A： 1、SUBTOTAL+函数多维引用公式1=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,25-COLUMN(A:X)))+SUBTOTAL(9,OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) 公式2=MAX(SUBTOTAL(9,OFFSET(A15,ROW(1:10),COLUMN(A:X),,24),OFFSET(A15,ROW(2:11),,,COLUMN(A:X)))) 公式3=MAX(SUBTOTAL(9,OFFSET(A16,ROW(1:9),,,COLUMN(A:Y)),OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26))) 公式3为目前最短公式，90个字符，注意A16:A25、Z16:Z25都被利用了，这两个区域不能有数值（可以有文本或0），否则会影响计算结果。【回顾函数语法】SUBTOTAL(function_num, ref1, ref2, ...) 其中，公式1是最早原型，SUBTOTAL+SUBTOTAL的解法，忘记了SUBTOTAL函数可以使用n个ref参数。 3个公式的解法，实际上都是利用OFFSET将连续时段分成上下两行的单元格，再利用SUBTOTAL函数对OFFSET产生的多维引用进行求和。公式2共91字，其中的第1个OFFSET始终用24个连续单元格，也就是不断地将引用“推后”，如图：第2个OFFSET则不断在下一行“推进”。公式3与此类似，不过是将第1个OFFSET的24个整体“推后”改为固定尾部在Z列，这样实际使用单元格也就只有Z列部分会影响计算结果。如图，OFFSET(Z15,ROW(1:9),,,COLUMN(A:Y)-26)效果就是红色部分、OFFSET(A16,ROW(1:9),,,COLUMN(A:Y))则是蓝色部分。 biaotiger的解法已自己解释了，这里就不再点评。 2、函数法多关键字排序技术 =MAX(MMULT(ROUND(MOD(SMALL(ROW(1:10)/1%%+COLUMN(A:X)100+B16:Y25,ROW(1:217)-1+COLUMN(A:X)),100),2),ROW(1:24)^0)) =MAX(MMULT(MOD(SMALL(ROW(1:10)10^7+COLUMN(B:Y)10^4+B16:Y25,COLUMN(A:X)+ROW(1:217)-1),10^4),ROW(1:24)^0)) rongjun和轻舟上逆兄的解法都使用了这项经典技术，利用row10^7+column10^4或者/1%%（相当于10^4），先把行、列号放大，再加上B16:Y25的数据，并使用SMALL函数进行排序得到第1~24个、第2~25个、第3~26个……数据，再使用MOD进行还原（去除放大的行列号），最后用MMULT求和。事实上，这两个公式都可以用普通公式而不需按CTRL+SHIFT+ENTER结束形成数组公式。有关问题的理解，可参考： [原创]【公式解析系列】之多关键字排序解法 3、INDIRECT+TEXT精妙引用 =MAX(MMULT(N(INDIRECT(TEXT(TEXT((ROW(1:216)-1)/24+B15:Y15,"dhh")+1602,"r0c00"),)),ROW(1:24)^0)) 本题共有数据10行24列计240个，每连续的24个为一组，因此最后一组从第217开始。 knifefox兄利用时间和日期数据特性，将(ROW(1:216)-1)/24得到以小时计算的序列，即第0小时、1小时……215小时，再加上B15:Y15的0~23小时，获得连续的24个数据，当超过24小时是，比如第25小时则TEXT("25:00","dhh")得到101，可以看为1行01列再加上1602（初始位置，16行02列，即B16单元格），第二次使用TEXT将它变为R1C1引用样式的文本，再利用INDIRECT得到多维引用并用N函数降低维度，最后再使用MMULT函数进行求和。 =MAX(MMULT(N(INDIRECT(TEXT((INT((ROW(1:216)+COLUMN(A:X)-2)/24)+16)100+MOD(ROW(1:216)+COLUMN(A:X)-2,24)+2,"r0c00"),0)),ROW(1:24)^0)) 这个解法中，利用ROW(1:216)+COLUMN(A:X)-2构建一个从0~23、1~24、2~25……等连续数组，并结合/24与INT、MOD(……，24）分别得到行、列号，相比knifefox的时间方法而言，就逊色许多了。
11楼 gouweicao78	关于第2问，基本上核心还是在第1问中，如果不使用第1问的计算成果，那么一般都采用多关键字排序技术+MOD还原法得到具体值，再使用TEXT函数得到题目要求的格式。 TEXT格式的使用上，取巧一下可以先+23小时，再使用"第d天h:mm"，也有采用"第0天00!:!0!0"、"第0天00"最后公式再&":00"。如果使用第1问的结果，一般则用MATCH(D28，第1问的核心公式,0)来计算第几个数据。
12楼 Olivia	我看看