[开_148]计算单月销售金额最高的月份（已评分总结）

cleverzhzhf

根据以下内容答题：


备注说明：
理论上，如果出现销售额相同情况，取任意一个月份均算正确。

奖励：
1、150个字符以内技术分1分
2、其他答案，根据情况奖励财富若干
计算单月销售金额最高的月份（题目）.zip (18.34 KB, 下载次数: 585)

2014-9-28 08:25 上传

点击文件名下载附件

竞赛日期：2014-9-28至2014-10-20

——————————————————————————————————————————

70楼评分明细做了更新
84楼对此题目做了简短的讲解

在可接受的速度范围内，一剑老师的公式可以简化到93字符：

1. =TEXT(RIGHT(MIN(-(SUMIFS(C:C,B:B,"<="&B2:B732,B:B,TEXT(B2:B732,">=e-m"))&B2:B732)),5),"e年m月")

复制代码

delete_007

131字符：

1. =TEXT(TEXT(RIGHT(MAX(MMULT(-(TEXT(TRANSPOSE(B2:B731),"yymm")-ROW(1301:1412)=0),-C2:C731)/1%%+ROW(1301:1412)),4),"0!/00!/1"),"e年m月")

复制代码

一剑惊心

大致做一个

1. =TEXT(RIGHT(MAX(SUMIFS(C:C,B:B,"<"&DATE({2013,2014},ROW(2:13),1),B:B,">="&{2013,2014}&-ROW(1:12))/1%%%+({2013,2014}&-ROW(1:12))),5),"e年m月")

复制代码

缩减一下字符

1. =TEXT(RIGHT(MAX(SUMIFS(C:C,B:B,"<="&B2:B732,B:B,TEXT(B2:B732,">=e-m"))/1%%%+B2:B732),5),"e年m月")

复制代码

不怕卡出翔的话，继续缩减

1. =TEXT(RIGHT(MAX(IFERROR(SUMIFS(C:C,B:B,"<="&B:B,B:B,TEXT(B:B,">=e-m"))/1%%%+B:B,)),5),"e年m月")

复制代码

再来一个2003版可用的

1. =TEXT(RIGHT(MAX((SUMIF(B:B,TEXT(B2:B732,">=e-m"),C1)-SUMIF(B:B,">0"&B2:B732,C1))/1%%%+B2:B732),5),"e年m月")

复制代码

cleverzhzhf

我的答案：
136字符：

1. =TEXT(EDATE(41244,MOD(MIN(MMULT(-(TEXT(TRANSPOSE(B2:B731),"em")=TEXT(EDATE(41244,ROW(1:24)),"em")),C2:C731+0)+ROW(1:24)%),1)/1%),"e年m月")

复制代码

不取巧，把41244换成2012年12月，144字符：

1. =TEXT(EDATE("2012-12",MOD(MIN(MMULT(-(TEXT(TRANSPOSE(B2:B731),"em")=TEXT(EDATE("2012-12",ROW(1:24)),"em")),C2:C731+0)+ROW(1:24)%),1)/1%),"e年m月")

复制代码

opqazxc

1. =TEXT(RIGHT(MIN(-(MMULT(-(TEXT(B2:B731,"em")=TRANSPOSE(TEXT(B2:B731,"em"))),-C2:C731)&B2:B731)),5),"e年m月")

复制代码

``

ouyangyunzi

=TEXT(EDATE(41274,RIGHT(MAX((SUMIF(B:B,">"&EDATE(41274,ROW(1:24)-1),C:C)-SUMIF(B:B,">"&EDATE(41274,ROW(1:24)),C:C))/1%+ROW(1:24)),2)),"yyyy年m月")

补充内容 (2014-10-8 21:23):
=TEXT(EDATE(41274,MOD(MAX((SUMIF(B:B,">"&EDATE(41274,ROW(1:24)-1),C:C)-SUMIF(B:B,">"&EDATE(41274,ROW(1:24)),C:C))/1%+ROW(1:24)),100)),"e年m月")

补充内容 (2014-10-8 21:26):
=TEXT(EDATE(41274,MOD(MAX((SUMIF(B:B,">"&DATE(2013,ROW(1:24),),C:C)-SUMIF(B:B,">"&DATE(2013,ROW(2:25),),C:C))/1%+ROW(1:24)),100)),"e年m月")

ys19840718

149字符

1. =TEXT(RIGHT(TEXT(MAX(SUMIFS(C:C,B:B,">="&EDATE(41244,ROW(1:24)),B:B,"<="&EDATE(41274,ROW(1:24)))+EDATE(41244,ROW(1:24))%%%),"0.000000"),6),"yyyy年m月")

复制代码

xgg2001

1. =TEXT(DATE(2013,RIGHT(MAX(MMULT(COLUMN(A:ABB)^0,(TEXT(B2:B731,"e-m")-DATE(2013,COLUMN(A:X),)=1)*C2:C731/1%)+COLUMN(B:Y)),2),),"e年m月")

复制代码

题目有些细节还待说明，例如重复最大值取前取后，计算结果是否可以通过自定义结果显示等

衣袂飘飘

1. =TEXT(MOD(MAX(SUMIFS(C:C,B:B,">"&DATE(2013,ROW(1:24),0),B:B,"<"&DATE(2013,ROW(2:25),1))*10^6+DATE(2013,ROW(1:24),1)),10^6),"e年m月")

复制代码

F15输入，数组公式。

天地有雪

实力不足！尽力了！

1. {=TEXT(EDATE("2015-1-31",-RIGHT(MAX(MMULT(N(A2:A25=TRANSPOSE(DATEDIF(B2:B731,"2015-1-31","m"))),IF(C2:C731="",0,C2:C731))+A2:A25%),2)),"yyy年m月")}

复制代码