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

Zaezhong

要求计算每一个业务员的几何平均，最后将所有的结果加总，具体说明见附件


答案正确者，奖励：
1、公式长度含等号小于等于300字，奖励技术分2分；
2、公式长度含等号小于等于400字，奖励技术分1分。
视完成情况，奖励财富若干。

---

总结楼层：http://club.excelhome.net/forum.php?mod=redirect&goto=findpost&ptid=930654&pid=6462656

hjj0451

好题目，先占个楼：
法一，MMULT法取第[3,N-2]个值，327：

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

复制代码

法二，SMALL法取第[3,N-2]个值，279：

1. =ROUND(SUM(10^MMULT(TRANSPOSE(LOG(B2:B45/COUNTIF(A:A,A2:A45))*MMULT(-(B2:B45+ROW(1:44)%-MID(SMALL(COLUMN(J:BA)/1%+(B2:B45+ROW(1:44)%)*(A2:A45=TRANSPOSE(A2:A45)),ROW(1:44)*44-(COUNTIF(A:A,A2:A45)-3)^{0,1}),3,5)<0),{-1;1})*(A2:A45=TRANSPOSE(A2:A45))),1/(COUNTIF(A:A,A2:A45)-4))),2)

复制代码

　　　继续想办法简化，210:

1. =FIXED(SUM(10^MMULT(MOD(SMALL((A2:A45<>TRANSPOSE(A2:A45))+LOG(B2:B45)+COLUMN(J:BA)/1%,ROW(1:44)*44+COLUMN(A:AP)-42),10)*(COLUMN(D:AS)<COUNTIF(A:A,A2:A45))/(COUNTIF(A:A,A2:A45)-4),1^B2:B43)/COUNTIF(A:A,A2:A45)))

复制代码

wangg913

附件就不上了，数组公式：

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

复制代码

※※※※※※※※※※※※※※※※※※※※※※※※※
2012-11-7 18:40  简化一下：

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

复制代码

帮助文件中 RMB()函数 的参数decimals[省略后]默认为2，可2010版默认为0，即--RMB(X)=ROUND(X,0)，出乎意料。
因此，如果是2010版，上式中的--RMB(SUM())应换成ROUND(SUM(),2)
※※※※※※※※※※※※※※※※※※※※※※※※※
2012-11-11 10:40

apirl2008

2、为了计算方便B列的数据统一为[10,99],具体需要排除的数据已经使用颜色标出
这一点是不是说，有颜色的将不在考虑进去计算？
4、如王顺最大两个为99和98,最小两个为37和42,那么王顺的销量几何平均为(50*84*68*90)^(1/4),其余类似,最后全部相加
全部相加是什么意思？用得到的平均值再加上两个最大值与两个最小值？

delete_007

1. =ROUND(SUM(10^((MMULT(LOG(TRANSPOSE(B2:B45)^(A2:A45=TRANSPOSE(A2:A45))),ROW(1:44)^0)-MMULT(4+LOG(MOD(SMALL(B2:B45%^(A2:A45=TRANSPOSE(A2:A45))+COLUMN(A:AR),ROW(1:44)*44-{43,42}),1)*MOD(LARGE(IF(A2:A45=TRANSPOSE(A2:A45),B2:B45)%+45-COLUMN(A:AR),ROW(1:44)*44-{42,43}),1)),{1;1}))/(COUNTIF(A:A,A2:A45)-4))*(MATCH(A2:A45,A:A,)=ROW(2:45))),2)

复制代码

最原始的思路，336字符，继续努力突破300.

1. =ROUND(SUM(10^(MMULT(LOG(MOD(SMALL(IF(A2:A45=TRANSPOSE(A2:A45),B2:B45*101,5501)+COLUMN(A:AR)/1%%,ROW(1:44)*44-COLUMN(B:AO)),100)),ROW(1:40)^0)/(COUNTIF(A:A,A2:A45)-4))/COUNTIF(A:A,A2:A45)),2)

复制代码

取巧进行到底，191字符。该公式成立的条件是：需要去掉的两个最小值<=55<=需要去掉的两个最大值。

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)),ROW(1:42)^0)/(COUNTIF(A:A,A2:A45)-4))*(MATCH(A2:A45,A:A,)=ROW(2:45))),2)

复制代码

不取巧的通用公式，225字符。
把去重改成计数的方式，公式减少到214字符。

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)),ROW(1:42)^0)/(COUNTIF(A:A,A2:A45)-4))/COUNTIF(A:A,A2:A45)),2)

复制代码

LOG换成LN再减少一个字符，ROW(1:42)^0换成1^B2:B43，210字符。

1. =ROUND(SUM(EXP(MMULT(MOD(SMALL(LN(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)),1^B2:B43)/(COUNTIF(A:A,A2:A45)-4))/COUNTIF(A:A,A2:A45)),2)

复制代码

悟空师弟

长长的公式吓吓的人，245字符，不过，计算结果与楼主给出314.94不一样，下面公式的结果是321.6：

1. =SUM(MMULT(MOD(-SMALL(ROW(1:44)/1%-TEXT(MOD(SMALL(B2:B45*(TRANSPOSE(A2:A45)=A2:A45)+COLUMN(A:AR)/1%,ROW(1:44)*44-44+COLUMN(A:AP)),100),"0;;100"),ROW(1:44)*42-42+COLUMN(A:AN)),100),ROW(1:40)^0)/(COUNTIF(A2:A45,A2:A45)^2-COUNTIF(A2:A45,A2:A45)*4))

复制代码

简化一下，218字符：

1. =SUM(MOD(-SMALL(ROW(1:44)/1%-TEXT(MOD(SMALL(B2:B45*(TRANSPOSE(A2:A45)=A2:A45)+COLUMN(A:AR)/1%,ROW(1:44)*44-44+COLUMN(A:AP)),100),"0;;100"),ROW(1:44)*42-42+COLUMN(A:AN)),100)/(COUNTIF(A:A,A2:A45)-4)/COUNTIF(A:A,A2:A45))

复制代码

CheryBTL

先来一个，再减化，221字符：

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

复制代码

将LOG换用LOG10再省一个，220字符：

1. =ROUND(SUM(10^(MMULT(LOG10(MOD(IF(COLUMN(E:AV)>COUNTIF(A:A,A2:A45),1,SMALL((A2:A45=TRANSPOSE(A2:A45))*B2:B45+COLUMN(A:AR)/1%,ROW(1:44)*44-COLUMN(B:AS))),100)),ROW(1:44)^0)/(COUNTIF(A:A,A2:A45)-4))/COUNTIF(A:A,A2:A45)),2)

复制代码

换INT再减一字符,219:

1. =INT(SUM(10^(2+MMULT(LOG10(MOD(IF(COLUMN(E:AV)>COUNTIF(A:A,A2:A45),1,SMALL((A2:A45=TRANSPOSE(A2:A45))*B2:B45+COLUMN(A:AR)/1%,ROW(1:44)*44-COLUMN(B:AS))),100)),ROW(1:44)^0)/(COUNTIF(A:A,A2:A45)-4))/COUNTIF(A:A,A2:A45)))%

复制代码

去掉IF再省2字符，215：

1. =INT(SUM(10^(2+MMULT(LOG10(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))),ROW(1:42)^0)/(COUNTIF(A:A,A2:A45)-4))/COUNTIF(A:A,A2:A45)))%

复制代码

再省一个字符：

1. =ROUND(SUM(10^MMULT(LOG10(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)

复制代码

————————————————————————————————————————
换回INT再省2字符（212）：

1. =INT(SUM(10^MMULT(LOG10(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)%))%

复制代码

————————————————————————————
这个题应该也可以要求人名出现次数小于或等于5次，
只是需要假定当等于5次时，几何平均值中间值，小于5次时取0，
下面公式对人名出现次数没有限制，用TEXT防错，251字符：

1. =INT(SUM(10^(2+MMULT(LOG10(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))),ROW(1:42)^0)/TEXT(COUNTIF(A:A,A2:A45)-4,"0;1;1"))/COUNTIF(A:A,A2:A45)*(COUNTIF(A:A,A2:A45)>4)))%

复制代码

如果上式中的ROW(1:42)^0可以改用C1:C42+1可再省3字符。

xgg2001

222，不知道是否符合题意{:soso_e110:}

1. =SUM(10^(MMULT(LOG(IF(COUNTIF(A:A,A2:A45)>46-COLUMN(A:AP),MOD(SMALL(ROW(1:44)+(A2:A45=TRANSPOSE(A2:A45))*TRANSPOSE(B2:B45)%,ROW(1:44)*44+COLUMN(A:AP)-44),1)/1%,1)),ROW(1:42)^0)/(COUNTIF(A:A,A2:A45)-4))/COUNTIF(A:A,A2:A45))

复制代码

ff8feng

{:soso_e141:}
先来个含等号265的，有空的话再考虑精简，最近比较忙。。。
=ROUND(SUM(10^MMULT(LOG10(RIGHT(IF((COLUMN(A:AR)<43)*(COLUMN(A:AR)>46-COUNTIF(A2:A45,A2:A45)),SMALL((A2:A45=TRANSPOSE(A2:A45))*TRANSPOSE(B2:B45)+ROW(1:44)/1%,COLUMN(A:AR)+ROW(1:44)*44-44),1),2)),ROW(1:44)^0)^(1/(COUNTIF(A2:A45,A2:A45)-4))/COUNTIF(A2:A45,A2:A45)),2)

感觉此题的关键就是如何建立二维数组来获取每个人的有效数据，一旦取数成功此题也就豁然开朗了，为了方便评阅，传个附件模拟一下取数核心。。。

绿玉人

1. =ROUND(SUM((MATCH(A2:A45,A:A,)=ROW(2:45))*10^(MMULT(TRANSPOSE(LOG(B2:B45^(A2:A45=TRANSPOSE(A2:A45)))),B2:B45^0)-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)),B2:B5^0))^(1/(COUNTIF(A:A,A2:A45)-4))),2)
   

复制代码