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

fugb-2010

F15=TEXT(MOD(MAX(MMULT((COLUMN(A:OI)<=ROW(1:399))*(TRANSPOSE(B2:B400)>=--TEXT(B2:B400,"e-m")),C2:C400)*10^5+B2:B400),10^5),"e年m月")

wangyuhu

还是一直用2003版的，常规思路
=TEXT(INDEX(B:B,RIGHT(MAX(MMULT((TEXT(B2:B731,"em")=TRANSPOSE(TEXT(B2:B731,"em")))*TRANSPOSE(C2:C731),ROW(2:731)^0)/1%%+ROW(2:731)),3)),"e年m月")

kuangben8

含=号136字符。

1. =TEXT(MOD(MAX(SUMIFS(C:C,B:B,">"&DATE(2013,COLUMN(A:X),0),B:B,"<"&DATE(2013,COLUMN(B:Y),1))*10^5+EDATE(41244,COLUMN(A:X))),10^5),"e年m月")

复制代码

允许设置单元格格式，含=号123字符。

1. =MOD(MAX(SUMIFS(C:C,B:B,">"&DATE(2013,COLUMN(A:X),0),B:B,"<"&DATE(2013,COLUMN(B:Y),1))*10^5+EDATE(41244,COLUMN(A:X))),10^5)

复制代码

---

---

---

---

---

---

---

---

---

---

在减一点，含=号118字符。

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

复制代码

再想想吧。。。

Bodhidharma

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

复制代码

apirl2008

1. =TEXT(LOOKUP(,0/(MAX(MMULT(TRANSPOSE(ROW(A2:A731))^0,(DATE(YEAR(B2:B731),MONTH(B2:B731),1)=DATE(2013,COLUMN(A1:X1),1))*C2:C731))=MMULT(TRANSPOSE(ROW(A2:A731))^0,(DATE(YEAR(B2:B731),MONTH(B2:B731),1)=DATE(2013,COLUMN(A1:X1),1))*C2:C731)),DATE(2013,COLUMN(A1:X1),1)),"yyyy年M月")

复制代码

居然有人说这是送分题，我怎么解的这么的费劲呢？275个字符，好长啊。。。。

1. =TEXT(DATE(2013,MATCH(MAX(SUMIFS(C2:C731,B2:B731,">="&DATE(2013,COLUMN(A1:X1),1),B2:B731,"<"&DATE(2013,COLUMN(A1:X1)+1,1))),SUMIFS(C2:C731,B2:B731,">="&DATE(2013,COLUMN(A1:X1),1),B2:B731,"<"&DATE(2013,COLUMN(A1:X1)+1,1)),),1),"yyyy年M月")

复制代码

省了一点点，236个字符，继续省

1. =TEXT(MOD(MAX(SUMIFS(C2:C731,B2:B731,">="&DATE(2013,COLUMN(A1:X1),1),B2:B731,"<"&DATE(2013,COLUMN(A1:X1)+1,1))/1%%%+DATE(2013,COLUMN(A1:X1),1)),10^6),"yyyy年M月")

复制代码

一下子又省掉了几十个字符，比吃肉还开心呢，哈，继续省

1. =MOD(MAX(SUMIFS(C2:C731,B2:B731,">="&DATE(2013,COLUMN(A1:X1),1),B2:B731,"<"&DATE(2013,COLUMN(A1:X1)+1,1))/1%%%+DATE(2013,COLUMN(A1:X1),1)),10^6)

复制代码

去掉外层的text，144个字符，能过关么？日期格式直接用设置单元格来解决吧。。。。。没办法了、、、、、

1. =TEXT(MOD(MAX(MMULT(TRANSPOSE(ROW(A2:A731))^0,(IFERROR(DATEDIF("2013/1/1",$B$2:$B$731,"M"),4^8)+1=COLUMN(A1:X1))*C2:C731)/1%%%+DATE(2013,COLUMN(A1:X1),1)),10^6),"yyyy年M月")

复制代码

额。。。。。没省下来，又涨了一些，171字符

1. =TEXT(RIGHT(MAX(MMULT(TRANSPOSE(ROW(A2:A731))^0,(IFERROR(DATEDIF("2013/1/1",$B$2:$B$731,"M"),4^8)+1=COLUMN(A1:X1))*C2:C731)/1%%%+DATE(2013,COLUMN(A1:X1),1)),5),"yyyy年m月")

复制代码

决定再也不想了，坐等优秀答案（再想就剁手！）

5blessyou

来一个玩玩，没有细想

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(2:25)),2),),"e年m月")

复制代码

1. =TEXT(DATE(2013,RIGHT(MAX(MMULT(TRANSPOSE(C2:C731)+0,N(TEXT(B2:B731,"em")=TEXT(DATE(2013,COLUMN(B:Y),),"em")))/1%+COLUMN(B:Y)),2),),"e年m月")

复制代码

pxiceplay

F15=TEXT(DATE(2013,100*MOD(MAX(SUMIFS(C2:C731,B2:B731,">"&DATE(2013,ROW(1:24),),B2:B731,"<"&DATE(2013,ROW(2:25),1))+ROW(1:24)%),1),1),"e年m月")
数组
如果光要长度不要速度的话，取整列
=TEXT(DATE(2013,100*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),"e年m月")

CheryBTL

凑个热闹：

1. =INDEX((2012+INT(ROW(12:35)/12)&"年"&MOD(ROW(12:35),12)+1)&"月",RIGHT(MAX(MMULT(-(TEXT(TRANSPOSE(B2:B731),"yy-m")=12+INT(ROW(12:35)/12)&"-"&MOD(ROW(12:35),12)+1),-C2:C731)/1%+ROW(1:24)),2))

复制代码

素锦流年つ

=TEXT(OFFSET(B1,MATCH(MAX(MMULT(--(TRIM(LEFT(SUBSTITUTE(TEXT(INDIRECT("B2:B"&MATCH(LOOKUP(4^8,B:B),B:B,)),"yyyy-m-d"),"-",REPT(" ",5),2),7))=TRANSPOSE(TRIM(LEFT(SUBSTITUTE(TEXT(INDIRECT("B2:B"&MATCH(LOOKUP(4^8,B:B),B:B,)),"yyyy-m-d"),"-",REPT(" ",5),2),7)))),INDIRECT("C2:C"&MATCH(LOOKUP(4^8,B:B),B:B,)))),MMULT(--(TRIM(LEFT(SUBSTITUTE(TEXT(INDIRECT("B2:B"&MATCH(LOOKUP(4^8,B:B),B:B,)),"yyyy-m-d"),"-",REPT(" ",5),2),7))=TRANSPOSE(TRIM(LEFT(SUBSTITUTE(TEXT(INDIRECT("B2:B"&MATCH(LOOKUP(4^8,B:B),B:B,)),"yyyy-m-d"),"-",REPT(" ",5),2),7)))),INDIRECT("C2:C"&MATCH(LOOKUP(4^8,B:B),B:B,))),),),"yyyy年m月")

cleverzhzhf

评分规则见附件，由于最近时间较紧，可能测试过程中难免有些疏漏。错误与疑问之处可直接与我联系。
如没有问题过些时日加分，并进行题目分析和答案思路总结。

简单说几点评分内容：
1、3楼一剑惊心老师的答案突破100字符，额外奖励1技术分
2、多占楼层扣财富
3、超过字符数，答案正确奖励30财富
4、对于修改单元格格式的，并答案接近的，奖励20财富，但并不能得到与截图一致的答案，因为：
    1）文本型数据，默认单元格左对齐
    2）数值型数据，默认单元格右对齐
计算单月销售金额最高的月份（答案汇总测试v2）.rar (49.28 KB, 下载次数: 128)

2014-10-30 00:23 上传

点击文件名下载附件