[第91期]计算所有业务员的几何平均值之和（已总结）

lsj128568

先上个397的，再想想
=SUM(--TEXT(10^(MMULT(LOG10(IF((TRANSPOSE(A2:A45)=A2:A45)*(MATCH(A2:A45,A2:A45,)=ROW(1:44)),TRANSPOSE(B2:B45),1)),ROW(1:44)^0)-MMULT(LOG10(MOD(SMALL(IF((TRANSPOSE(A2:A45)=A2:A45)*(MATCH(A2:A45,A2:A45,)=ROW(1:44)),TRANSPOSE(B2:B45),101)+ROW(1:44)/1%%,IF({1,1,0,0},ROW(1:44)*44-{43,42},ROW(1:44)*44+COUNTIF(A2:A45,A2:A45)-{0,0,45,44})),100)),{1;1;1;1}))^(1/(COUNTIF(A2:A45,A2:A45)-4)),"[>1].00;!0"))

sharprain

1. =SUM(ROUND(EXP(MMULT(COLUMN(A:AR)^0,LN(B2:B45^((MMULT((A2:A45=TRANSPOSE(A2:A45))*(B2:B45+ROW(1:44)%<TRANSPOSE(B2:B45)+COLUMN(A:AR)%),ROW(1:44)^0)>1)*(MMULT((A2:A45=TRANSPOSE(A2:A45))*(B2:B45+ROW(1:44)%>TRANSPOSE(B2:B45)+COLUMN(A:AR)%),ROW(1:44)^0)>1))^(A2:A45=TRANSPOSE(A2:A45)))))^(1/(COUNTIF(A2:A45,TRANSPOSE(A2:A45))-4)),2)*TRANSPOSE(MATCH(A2:A45,A2:A45,)=ROW(1:44)))

复制代码

370字节了，先写一个慢慢简化吧没有约定使用03版函数，如果用07版函数countifs，可以比较容易做到300字以下

1. =SUM(ROUND(EXP(MMULT(COLUMN(A:AR)^0,LN(B2:B45^(MMULT(N(COUNTIFS(A2:A45,A2:A45,B2:B45,{"<",">"}&B2:B45)+(MATCH(A2:A45&B2:B45,A2:A45&B2:B45,)<>ROW(1:44))<2),{1;1})=0)^(A2:A45=TRANSPOSE(A2:A45)))))^(1/(COUNTIF(A2:A45,TRANSPOSE(A2:A45))-4)),2)*TRANSPOSE(MATCH(A2:A45,A2:A45,)=ROW(1:44)))

复制代码

有283字节

第三种方法：

1. 
   
2. =SUM(ROUND(EXP(MMULT(LN(TRANSPOSE(B2:B45)^(A2:A45=TRANSPOSE(A2:A45))),ROW(1:44)^0)-MMULT(LN(MOD(SMALL(ROW(1:44)/1%+TRANSPOSE(B2:B45)^(A2:A45=TRANSPOSE(A2:A45)),ROW(1:44)*44-COUNTIF(A2:A45,A2:A45)*{1,1,0,0}+{1,2,-1,0}),100)),{1;1;1;1}))^(1/(COUNTIF(A2:A45,A2:A45)-4)),2)*(MATCH(A2:A45,A2:A45,)=ROW(1:44)))

复制代码

304字节，未简化

wddn

要求的几何平均值，最后求和：
=SUM((MATCH(A2:A45,A2:A45,)=ROW(1:44))*10^(MMULT((TRANSPOSE(ROW(1:44))>COUNTIF(A2:A45,">"&A2:A45)+2)*(TRANSPOSE(ROW(1:44))<COUNTIF(A2:A45,">="&A2:A45)-1)*TRANSPOSE(LOG(MOD(SMALL(COUNTIF(A2:A45,">"&A2:A45)/1%+B2:B45,ROW(1:44)),100))),1^B2:B45)/(COUNTIF(A2:A45,A2:A45)-4)))

要求的几何平均值，最后求和(247字符)：
=SUM((MATCH(A2:A45,A2:A45,)=ROW(1:44))*10^(MMULT((COLUMN(A:AR)>COUNTIF(A2:A45,">"&A2:A45)+2)*(COLUMN(A:AR)<COUNTIF(A2:A45,">="&A2:A45)-1)*LOG(MOD(SMALL(COUNTIF(A2:A45,">"&A2:A45)/1%+B2:B45,COLUMN(A:AR)),100)),1^B2:B45)/(COUNTIF(A2:A45,A2:A45)-4)))

要求的几何平均值，最后求和(236字符)：
=SUM(10^(MMULT((COLUMN(A:AR)>COUNTIF(A2:A45,">"&A2:A45)+2)*(COLUMN(A:AR)<COUNTIF(A2:A45,">="&A2:A45)-1)*LOG(MOD(SMALL(COUNTIF(A2:A45,">"&A2:A45)/1%+B2:B45,COLUMN(A:AR)),100)),1^B2:B45)/(COUNTIF(A2:A45,A2:A45)-4))/COUNTIF(A2:A45,A2:A45))



要求的几何平均值，最后求和并保留两位小数(268)：
=ROUND(SUM(EXP(MMULT((TRANSPOSE(ROW(2:45))>COUNTIF(A2:A45,">"&A2:A45)+3)*(TRANSPOSE(ROW(2:45))<COUNTIF(A2:A45,">="&A2:A45))*MOD(SMALL(COUNTIF(A2:A45,">"&A2:A45)/1%+LN(B2:B45),TRANSPOSE(ROW(2:45)-1)),100),1^B2:B45)/(COUNTIF(A2:A45,A2:A45)-4))/COUNTIF(A2:A45,A2:A45)),2)

要求的几何平均值，最后求和并保留两位小数(244)：  

=ROUND(SUM(EXP(MMULT((COLUMN(A:AR)>COUNTIF(A2:A45,">"&A2:A45)+2)*(COLUMN(A:AR)<COUNTIF(A2:A45,">="&A2:A45)-1)*MOD(SMALL(COUNTIF(A2:A45,">"&A2:A45)/1%+LN(B2:B45),COLUMN(A:AR)),100),1^B2:B45)/(COUNTIF(A2:A45,A2:A45)-4))/COUNTIF(A2:A45,A2:A45)),2)

simon.chen

苦思冥想，还是要辅助单元格，功力不够，还是看各位大神出来跳吧。

swyrlbq

有几点不明白，望题主明示：1、业务员，如刘二是增加、还是改换，也就是说行数是否随意增加，如果可随意增加，行数有没有限制；2、保留两位小数，是必须用公式，还是可以通过设置单元格格式处理。3、公式是否必须2003版通过。
先帖上一条公式，不知符合要求：
=SUM((MATCH(A2:A45,A2:A45,)=ROW(1:44))*(10^MMULT(LOG(RIGHT(LARGE(IF(A2:A45=TRANSPOSE(A2:A45),B2:B45+(45-COLUMN(A:AR))*100,1),IF(COUNTIF(A:A,A2:A45)-4>=COLUMN(A:AR),COLUMN(C:AT)+MMULT((ROW(1:44)>=COLUMN(A:AR))/1,COUNTIF(A2:A45,A1:A44)),999)),2)),1^ROW(1:44)))^(1/(COUNTIF(A:A,A2:A45)-4)))


突破两位小数使用公式，281字符

=ROUND(SUM(10^MMULT(LOG(MOD(LARGE(IF(A2:A45=TRANSPOSE(A2:A45),COLUMN(A:AR)/1%+B2:B45,1),(COUNTIF(A:A,A2:A45)-4<COLUMN(A:AR))*999+COLUMN(C:AT)+MMULT(1*(ROW(1:44)<COLUMN(A:AR)),COUNTIF(A:A,A2:A45))),100)),ROW(1:44)^0)^(1/(COUNTIF(A:A,A2:A45)-4))*(MATCH(A2:A45,A2:A45,)=ROW(1:44))),2)

5blessyou

1. =ROUND(SUM(10^MMULT(LOG(MOD(SMALL(B2:B45^(A2:A45=TRANSPOSE(A2:A45))+COLUMN(A:AR)/1%,ROW(1:44)*44-COLUMN(B:AQ)),100))*(COLUMN(D:AS)<COUNTIF(A:A,A2:A45))/(COUNTIF(A:A,A2:A45)-4),ROW(1:42)^0)/COUNTIF(A:A,A2:A45)),2)

复制代码

一嘟噜钥匙

288字符：
=ROUND(SUM((EXP(MMULT(N(A2:A45=TRANSPOSE(A2:A45)),LN(B2:B45)))/EXP(MMULT(LN(MOD(LARGE((A2:A45=TRANSPOSE(A2:A45))*TRANSPOSE(B2:B45)+(45-ROW(1:44))/1%,COUNTIF(A2:A45,A2:A45)*{0,0,1,1}+{1,2,-1,0}+(ROW(1:44)-1)*44),100)),ROW(1:4)^0)))^(1/(COUNTIF(A2:A45,A2:A45)-4))/COUNTIF(A2:A45,A2:A45)),2)

delete_007

既然已经开贴，就放弃思考了。

Zaezhong

我好像没开帖权限的，本来想弄个提醒的，不知道怎么就可以看到，结束也罢

Zaezhong

1. =RMB(SUM((MATCH(A2:A45,A:A,)=ROW(2:45))*(DPRODUCT(OFFSET(A1:B45,ROW(1:44)-1,),2,OFFSET(A1,ROW(1:44)-1,,2))/10^MMULT(LOG(MOD(SMALL(COLUMN(B:AS)/1%+(A2:A45=TRANSPOSE(A2:A45))*B2:B45,ROW(1:44)*44-COUNTIF(A:A,A2:A45)*{1,1,0,0}+{1,2,-1,0}),100)),1^B2:B5))^(1/(COUNTIF(A:A,A2:A45)-4))))+0

复制代码

贴下我开始的答案，字符上没有优势，感谢各位参与，今天评分已经没有了，满足条件的评分后续会补上