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

丢丢表格

1. =TEXT(DATE(2013,MOD(MAX(SUMIFS(C:C,B:B,">"&DATE(2013,ROW(1:24),),B:B,"<"&DATE(2013,ROW(2:25),1))+ROW(1:24)%),1)/1%,1),"e年m月")
   
2. 
   
3. =TEXT(DATE(2013,MOD(MAX(MMULT(SUMIF(B:B,">"&DATE(2013,ROW(1:24)+{0,1},),C:C),{1;-1})+ROW(1:24)%),1)/1%,1),"e年m月")
   
4. 
   
5. =TEXT(DATE(2013,MOD(MAX(MMULT(SUMIF(B:B,">"&DATE(2013,ROW(1:24)+{0,1},),C:C),{1;-1})+ROW(2:25)%),1)/1%,),"e年m月")
   
6. 
   
7. =TEXT(DATE(2013,MOD(MAX(MMULT(SUMIF(B:B,">"&DATE(2013,A2:A25+{0,1},),C:C),{1;-1})+A3:A26%),1)/1%,),"e年m月")
   
8. 
   
9. =TEXT(MOD(MMULT(SUMIF(B:B,">"&DATE(2013,A2:A25+{0,1},),C:C),{1;-1})*10^6+DATE(2013,A3:A26,),10^6),"e年m月")
   

复制代码

看见星光

1. 如果多个最大值可以任意提取一个的话，我的理解，13~14年等的设定就没什么意义了啊？年份可以任意？135字：=TEXT(RIGHT(MAX(MMULT(COLUMN(A:ADS)^0,(TEXT(B2:B800,"em")=TRANSPOSE(TEXT(B2:B800,"em")))*(C2:C800))/1%%%+TRANSPOSE(B2:B800)),5),"e年m月")

复制代码

允许自定义格式的话：123:

1. =MOD(MAX(MMULT(COLUMN(A:ADS)^0,(TEXT(B2:B800,"em")=TRANSPOSE(TEXT(B2:B800,"em")))*(C2:C800))/1%%%+TRANSPOSE(B2:B800)),10^6)

复制代码

13~14年~换个思路128：

1. =TEXT(EDATE(41244,RIGHT(MAX(SUMIFS(C:C,B:B,">="&EDATE(41244,ROW(1:24)),B:B,"<"&EDATE(41244,ROW(2:25)))/1%+ROW(1:24)),2)),"e年m月")

复制代码

再换个116：

1. =TEXT(DATE(2013,MOD(MAX(SUMIFS(C:C,B:B,">"&DATE(2013,A2:A25,),B:B,"<="&DATE(2013,A3:A26,))/1%+A3:A26),100),),"e年m月")

复制代码

再继续108。

1. =TEXT(DATE(2013,RIGHT(MAX(MMULT(SUMIF(B:B,"<="&DATE(2013,A3:A26-{0,1},),C:C),{1;-1})/1%+A3:A26),2),),"e年m月")

复制代码

zxh361

第一种：一个不多一个不少，刚好150

1. =TEXT(EDATE("2012-12",MOD(MAX(MMULT(TRANSPOSE(ROW(2:800)^0),IFERROR(DATEDIF("2012-12",B2:B800,"m")=COLUMN(A:X),)*C2:C800)+COLUMN(A:X)%),1)/1%),"e年m月")

复制代码

简化：改一下日期函数 138字符

1. =TEXT(DATE(2013,MOD(MAX(MMULT(--(0&TRANSPOSE(C2:C800)),IFERROR(N(DATEDIF(41244,B2:B800,"m")=COLUMN(A:X)),))+COLUMN(A:X)%),1)/1%,1),"e年m月")

复制代码

第二种：
用SUMIFS函数147字符

1. =TEXT(DATE(2013,MOD(MAX(SUMIFS(C2:C800,B2:B800,">="&DATE(2013,COLUMN(A:X),1),B2:B800,"<="&DATE(2013,COLUMN(A:X)+1,))+COLUMN(A:X)%),1)/1%,1),"e年m月")

复制代码

简化：135字符

1. =TEXT(DATE(2013,MOD(MAX(SUMIFS(C:C,B:B,">="&DATE(2013,COLUMN(A:X),1),B:B,"<="&DATE(2013,COLUMN(A:X)+1,))+COLUMN(A:X)%),1)/1%,1),"e年m月")

复制代码

满坛皆为吾师

感谢翟老师给新人加分机会，赞。
计算单月销售金额最高的月份（题目）.rar (16.84 KB, 下载次数: 3)

2014-9-29 23:56 上传

点击文件名下载附件

三键客

1. =TEXT(RIGHT(MAX(--(MMULT(SUMIF(B:B,"<"&EDATE(412&{44,14},ROW(2:25)),C:C),{1;-1})&EDATE(41244,ROW(1:24)))),5),"e年m月")

复制代码

gdw831001

1. =TEXT(RIGHT(MAX(10^8*MMULT(TRANSPOSE((TRANSPOSE(TEXT(EDATE(41274,ROW(1:24)),"emm"))=TEXT(B2:B731,"emm"))*C2:C731),ROW(2:731)-ROW(1:730))+EDATE(41274,ROW(1:24))),5),"yyyy年m月")

复制代码

上不了台面啊

sharprain

1. =TEXT(EOMONTH("2013-1",MOD(MAX(MMULT(SUMIF(B:B,"<="&EOMONTH("2013-1",ROW(1:24)-{0,1}),C:C),{1;-1})/1%+ROW(1:24)),100)),"e年m月")

复制代码

先写一个凑热闹EDATE 也可以的

1. =TEXT(EDATE("2012-12",MOD(MAX(MMULT(SUMIF(B:B,">="&EDATE("2012-12",ROW(1:24)+{0,1}),C:C),{1;-1})/1%+ROW(1:24)),100)),"e年m月")

复制代码

124字节了

chunlin1wang

1. =TEXT(DATE(2013,MOD(MAX(MMULT(SUMIF(B:B,">="&DATE(2013,ROW(1:24)+{0,1},1),C:C),{1;-1})/1%+ROW(1:24)),100),1),"yyyy年m月")

复制代码

AVEL

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

复制代码

一剑惊心

再来一个公式，缩减了40多个字符，压缩到百字符以内

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

复制代码

不怕卡出翔的话，再减2个字符

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月")

复制代码

lnzcqlx

1. =TEXT(DATE(2013,MAX((MAX(MMULT((TEXT(DATE(2013,ROW(1:24),1),"yyyymm")=TRANSPOSE(TEXT(B2:B731,"yyyymm")))*(ROW(1:24)^0*TRANSPOSE(C2:C731)),ROW(1:730)^0))=MMULT((TEXT(DATE(2013,ROW(1:24),1),"yyyymm")=TRANSPOSE(TEXT(B2:B731,"yyyymm")))*(ROW(1:24)^0*TRANSPOSE(C2:C731)),ROW(1:730)^0))*ROW(1:24)),1),"yyyy年m月")

复制代码

补充内容 (2014-10-6 08:53):
=TEXT(DATE(2013,RIGHT(MAX(MMULT((TEXT(DATE(2013,ROW(1:24),1),"yyyymm")=TRANSPOSE(TEXT(B2:B731,"yyyymm")))*(ROW(1:24)^0*TRANSPOSE(C2:C731)),ROW(1:730)^0)+ROW(1:24)%%),2),1),"yyyy年m月")