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

cleverzhzhf

把公式贴出来，附件不是必须的

ykqrs

^_^，最近的竞赛此起披伏，\(^o^)/~，只不过这一期我有点忐忑不安，顺着思路一直写完，竟然用了130多字就返回结果，稍微改一下119，如果A2：A25可以引用，能省掉6字，如果B2能确定是2013年1月份的某一天，又能省掉4字，是不是我理解错提议了？先把这个119的弄上再说吧！

1. =TEXT(EDATE(41244,RIGHT(MAX(MMULT(SUMIFS(C:C,B:B,"<="&EOMONTH(41244,ROW(1:24)-{1,0})),{-1;1})/1%+ROW(1:24)),2)),"e年m月")

复制代码

yf_home

再试一个：   答题.zip (21.42 KB, 下载次数: 1)

2014-10-7 10:28 上传

点击文件名下载附件

....

jianbing

1. =TEXT(DATE(2013,RIGHT(MAX(MMULT(COLUMN(A:ABC)^0,C2:C732*(COLUMN(A:X)=MONTH(B2:B732)+(YEAR(B2:B732)=2014)*12))/1%+COLUMN(A:X)),2),1),"yyyy年m月")

复制代码

frank55555

=DATE(2013,MOD(MIN(MMULT(SUMIFS(C:C,B:B,{">","<="}&DATE(2013,ROW(1:24)+{1,0},))+ROW(1:24)%,{1;1})),1)/2%,1)
可以少一個S.

=DATE(2013,MOD(MIN(MMULT(SUMIF(B:B,{">","<="}&DATE(2013,ROW(1:24)+{1,0},),C:C)+ROW(1:24)%,{1;1})),1)/2%,1)

sw=andy

据说E15答题的单元格不能自定义日期格式？那外套一个text.
TEXT(INDEX(B:B,--RIGHT(MAX((SUMIF(B2:B731,">"&(B2:B731-DAY(B2:B731)),C2)-SUMIF(B2:B731,">"&(B15:B744-DAY(B15:B744)),C2))/1%%+ROW(2:731)),3)),"e年m月")

hsw212

请指正。

不知道=号算字符吗？
-----
数组公式

1. =MAX(IFERROR(SUBTOTAL(9,OFFSET(C1,ROW(B:B),,FREQUENCY(--TEXT(B2:B731,"ym"),--TEXT(B2:B731,"ym")))),))

复制代码

一嘟噜钥匙

1. =TEXT(RIGHT(MAX(MMULT(-(TEXT(ROW(1:731)+41274,"ym")=TRANSPOSE(TEXT(B2:B732,"ym"))),-C2:C732)*10^5+ROW(1:731)+41274),5),"yyyy年m月")

复制代码

1. =TEXT(EDATE(41244,RIGHT(MAX(MMULT(-(ROW(1:24)=TRANSPOSE(DATEDIF(,B2:B732,"m")-1355)),-C2:C732)/1%+ROW(1:24)),2)),"yyyy年m月")

复制代码

rym9401

希望能学习到更好的方法！


=TEXT(RIGHT(MAX(--(SUMIFS(C2:C731,B2:B731,"<="&EOMONTH(DATE({2013,2014},ROW(1:12),1),0),B2:B731,">="&DATE({2013,2014},ROW(1:12),1))&DATE({2013,2014},ROW(1:12),1))),5),"yyyy年m月")

数组公式

huahua2005

生疏了，支持下翟班~139字符

1. ="201"&TEXT(RIGHT(MAX(MMULT(TRANSPOSE((TEXT(B2:B733,"ym")="1"&COLUMN(AE:OV))*C2:C733),ROW(1:732)^0)*10^3+ROW(31:412)),3),"[<99]0年0月;0年00月")

复制代码