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

准提部林

数组：１１２字．２００３版

1. 以預設201301~201412為條件計算，不具靈活及廣用性，刪～

复制代码

数组．１０５：２００３版

1. 數列與轉置數列運算，資料多，卡檔，刪～

复制代码

不加入賽局，有參即與，退場囉～～

复制代码

flykiss80

1. =TEXT(DATE(2013,0+RIGHT(MAX(MMULT(COLUMN(A:OI)^0,C2:C400*(DATEDIF("2013-1-1",B2:B400,"m")=COLUMN(A:X)-1))/1%%+COLUMN(A:X)),2),1),"e年m月")

复制代码

136字符，缺点是数据增加后需要改变公式

1. =TEXT(DATE(2013,MOD(MAX(SUMIFS(C:C,B:B,">"&DATE(2013,ROW(1:24),),B:B,"<"&DATE(2013,ROW(2:25),1))/1%%+ROW(2:25)),100),),"e年m月")

复制代码

126字符，可以输入任意多行数据
计算单月销售金额最高的月份（题目）-flykiss80.rar (17.66 KB, 下载次数: 3)

2014-10-2 22:53 上传

点击文件名下载附件

cmxxccxx

1. =TEXT(MOD(MAX(MMULT(SUMIF(B:B,">"&DATE(2013,ROW(1:24)+{0,1},),C1),{1;-1})*10^6+DATE(2013,ROW(2:25),)),10^6),"e年m月")

复制代码

xianxing110a

1. =TEXT(RIGHT(MAX(MMULT(SUMIF(B:B,">"&EOMONTH(IF({1},B2:B731),{-1,0}),C1),{1;-1})*10^5+B2:B731),5),"e年m月")

复制代码

aoe1981

　　纠结了半天，终于有了答案：
　　公式如下（143字）：

1. =TEXT(DATE(2013,RIGHT(MAX(SUMIFS(C2:C400,B2:B400,"<"&DATE(2013,ROW(2:25),1),B2:B400,">="&DATE(2013,ROW(1:24),1))+ROW(1:24)/100),2),1),"yyy年m月")

复制代码

　　附件如下：
　　 计算单月销售金额最高的月份（aoe1981）.rar (16.4 KB, 下载次数: 1)

2014-9-30 20:23 上传

点击文件名下载附件

　　这个131字，思路同上：
　　

1. =TEXT(DATE(2013,RIGHT(MAX(SUMIFS(C:C,B:B,"<"&DATE(2013,ROW(2:25),1),B:B,">="&DATE(2013,ROW(1:24),1))+ROW(1:24)/100),2),1),"yyy年m月")

复制代码

　　下面03版可用，140字：

1. =TEXT(DATE(2013,RIGHT(MAX(SUMIF(B:B,"<"&DATE(2013,ROW(2:25),1),C:C)-SUMIF(B:B,"<"&DATE(2013,ROW(1:24),1),C:C)+ROW(1:24)/100),2),1),"yyy年m月")

复制代码

　　再进一步128字，03版可类似修改（略）：

1. =TEXT(DATE(2013,RIGHT(MAX(SUMIFS(C:C,B:B,"<="&DATE(2013,ROW(2:25),),B:B,">"&DATE(2013,ROW(1:24),))+ROW(2:25)/100),2),),"yyy年m月")

复制代码

zhaojiaoyang200

1. =TEXT(MOD(MAX(SUMIFS(C:C,B:B,"<="&DATE(2013+{0,1},ROW(2:13),),B:B,">="&2013+{0,1}&"/"&ROW(1:12))+(2013+{0,1}&"/"&ROW(1:12))/10^5),1)*10^5,"e年m月")

复制代码

alzbz

1. =TEXT(MATCH(1,FREQUENCY(-9E+307,-MMULT(TRANSPOSE(ROW(2:731)^0),C2:C731*(TEXT(B2:B731,"em")=TEXT(41&COLUMN(I:AF)*31,"em")))),)*30+41260,"e年m月")

复制代码

142字符。
07版134字符：

1. =TEXT(MATCH(1,FREQUENCY(-9E+307,-MMULT(COLUMN(B:ABC)^0,C2:C731*(TEXT(B2:B731,"em")=TEXT(41&COLUMN(I:AF)*31,"em")))),)*30+41260,"e年m月")

复制代码

cl63519

INDEX(TEXT(B2:B400,"yyyy年m月"),MOD(MAX(MMULT(N(TEXT(B2:B400,"yy/m")=TEXT(N(OFFSET(B2,-1+COLUMN(A:OI),)),"yy/m")),C2:C400)*10^3+ROW(1:399)),10^3))

补充内容 (2014-10-5 16:13):
=TEXT(EOMONTH("2013/1/1",MOD(MAX(MMULT(N(DATEDIF("2013/1/1",TRANSPOSE(B2:B400),"m")=ROW(1:23)-1),C2:C400)*10^3+ROW(1:23)-1),10^3)),"yyyy年m月"),
140字符的算法.

准提部林

不好意思，多佔楼层？
刪了～～

wlc5201130

=TEXT(DATE(2013,MOD(MAX(MMULT(TRANSPOSE((TEXT(B2:B731,"em")=TEXT(DATE(2013,COLUMN(B:Y),),"em"))*C2:C731),ROW(1:730)^0)/1%+ROW(2:25)),100),),"e年m月")