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

hjj0451

保留两位小数的限制除了限制下嵌套层数外没有什么用，如果理解为任何情况下比如整数都要保留2位小数.00且为数值格式，上面的所有公式都不合格。所以只能理解为以下2种情形：
一、所有情况都是2位小数，包括整数，但是文本格式。
二、多于2位小数时才保留2位小数，数值格式。

Zaezhong

拟评分如下，如有疑问请尽快反馈，斟酌再三只有一个业务员的情况要求考虑在内，其他针对主体公式只要思路正确即可，但是有些情况下会出现错误的公式给1分

Zaezhong

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

本次的题目主要的一个考点是内存数组中各行数据相乘，还有就是生成去除两个最大和两个最小后有效数据的内存数组的构造。方法也相对以前几次的竞赛题目要少，从参与者的情况看主要有采用了直接的方法，也即直接将两个最大和两个最小数据排除后计算，其中的核心公式部分的构造有些不同，但是总体来说可以归类为直接法，还有一个思路参与者中出现的较少，就是先使用全部的销量数据相乘再除以两个最大和两个最小的积，主要是10楼17楼的方法和本人预设的，将其归类为间接法。但是从字符统计上，直接法有较大的优势。求内存数组中每一行的积跟我们平时遇到的求每一行的和不同，Excel没有直接求内存数组各行积的函数，但是配合对数函数和mmult函数可以解决这一疑难，当a>0且a<>1，M>0,N>0时有如下的性质：

根据该性质就可以将两个数相乘的结果转换为相加，这样就可以使用mmult函数对其求和，但是有一点就是最后的结果可能会出现细小的偏差，为此添加了一条因为自身精度引起的误差忽略不计的说明。

直接法：
包括2楼，3楼，5楼，7楼，13楼，16楼，其核心公式又可以分为两种不同的思路（以A2单元格的王顺为例）：
第一种：先转换数据再构建数组
包括2楼，5楼，13楼，先使用LOG(B2:B45)将原始数据进行转换，该步的目的是为了后面使用mmult函数可以直接求和：
2楼：(A2:A45<>TRANSPOSE(A2:A45))+LOG(B2:B45)部分，该段公式的部分内容如下：

由于B2:B45属于[10,99]，那么LOG(B2:B45)的大概的范围就是[1,3)，以第一列数据为例，其中如果是王顺的销售数据，那么<2，上图颜色标记部分，注意这里2是取不到的，再加上COLUMN(J:BA)/1%进行配权，这样就将同一个业务员的数据进行了排序；

5楼：LN(B2:B45)^(A2:A45=TRANSPOSE(A2:A45))的作用相同，不同之处是将王顺的销售数据全部转换为LN(B2:B45)，其他的转换为1；

13楼：该方法的构造与上面两个楼层的构造上又有较大的变化，以上两种构造方式的结果是通过MOD(SMALL(COUNTIF(A2:A45,">"&A2:A45)/1%+LN(B2:B45),COLUMN(A:AR)),100)排序，其中countif是公式的核心，实现将相同业务员的销售数据排在一起（标准使用">"&A2:A45是降序排列），使用(COLUMN(A:AR)>COUNTIF(A2:A45,">"&A2:A45)+2)用于排除最小的两个，使用(COLUMN(A:AR)<COUNTIF(A2:A45,">="&A2:A45)-1)用于排除最大的两个，(COLUMN(A:AR)>COUNTIF(A2:A45,">"&A2:A45)+2)*(COLUMN(A:AR)<COUNTIF(A2:A45,">="&A2:A45)-1)的结果部分有一点需要注意，就是该数组的结果是在COUNTIF(A2:A45,">"&A2:A45)的基础上计数的，如王顺由于在各个业务员的排名中属于最大所以COUNTIF(A2:A45,">王顺")的结果是0，COUNTIF(A2:A45,"王顺")的结果是8，那么1~8应该是王顺占据，排除第一和第二和第7和第8外其余位置要保留，而第二行COUNTIF(A2:A45,">马德明")结果为21，COUNTIF(A2:A45,"马德明")=6，那么22~27由马德明占据，排除前面两个和后面两个，所以在24和25两个位置的数被保留下来，其余的各行也是相同的方法


第二种：先构建数组再转换数据
包括3楼，7楼，16楼，这些楼层的排序方法相同，第一列所有属于王顺的数据保留，不属于显示为1，因为是先排序再使用对数函数转换数据，所以不可避免的要使用指数运算，但是也可以使用text函数，但是text函数会增加嵌套和字符。
以上除了13楼以外都通过small配合ROW(1:44)*44-COLUMN(B:AQ)，部分结果如下图（这是一个44行*42列的区域，第一行是42（44*1-2)到1（44*1-43）的以-1为公差的等差数列，第二行是86（44*2-2）到45（44*2-43）的以-1为公差的等差数列其中红色部分是数组中各行的行次：

排除的是各个业务员最大的两个数。所以MOD(SMALL(B2:B45^(A2:A45=TRANSPOSE(A2:A45))+COLUMN(A:AR)/1%,ROW(1:44)*44-COLUMN(B:AO)),100)是排除了最大两个数据后留下的数据

此时还包括最小的两个数没有排除，而COLUMN(D:AS)<COUNTIF(A:A,A2:A45)的使用是为了将最小的两个排除，该部分使用最小的值是4（原来应该是COUNTIF(A:A,"王顺")>=3，使用COLUMN(D:D)就可以减少一个字符）通过这一步也将各个业务员最小的两个数排除了，这样所谓的有效数据的位次就可以通过下图（即COLUMN(D:AS)<COUNTIF(A:A,A2:A45)部分的结果）轻松得出了

=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)是
=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)),B2:B41^0)^(1/(COUNTIF(A:A,A2:A45)-4)))*(MATCH(A2:A45,A:A,)=ROW(2:45)))的简化形式，其中用到了下面的对数函数性质

如果将Mmult函数的结果记为矩阵A=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),1^B2:B41)
如果将红色部分省去，其余部分记为矩阵B，那么B=A/(COUNTIF(A:A,A2:A45)-4)，一般情况下统计不重复的值使用的MATCH(A2:A45,A:A,)=ROW(2:45)但是还可以使用另外一种思路，假如有5个数，而且每次重复数据出现时对应的数据相同的话也可以分别去取每次出现时的值的1/5，也即1=1/5*5这个基本的分数相乘公式，这就是下方没有MATCH(A2:A45,A:A,)=ROW(2:45)该部分的一个解释，原理基本跟SUM(1/COUNTIF())相同，每一个数只计算其中的1/COUNTIF(A:A,A2:A45)

间接法：
间接法提供的答案不多，主要在字符上有较大的劣势，主要为10楼，17楼以及19楼
三个公式实现全部数据相乘的方法不同，10楼公式全部相乘跟后面排除最大两个和最小两个的方法相同，思路也较直接，17楼的公式与上面的直接法中的有相似之处，17楼可以适当简化=SUM((EXP(MMULT(N(A2:A45=TRANSPOSE(A2:A45)),LN(B2:B45)))/EXP(MMULT(LN(MOD(LARGE((A2:A45=TRANSPOSE(A2:A45))*B2:B45-COLUMN(A:AR)/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))，以上公式使用了ROW(1:44)*44-COUNTIF(A:A,A2:A45)*{1,1,0,0}+{1,2,-1,0}部分是构造一个头尾需要去掉的值的位次，由于非己的数据都是100，200这样的整百，所以需要排除的数是在44的基础下减去的，如第一行王顺要排除的是第37，38，43，44。

至于19楼公式使用的数据库函数的方法就请参考附件，有详细说明。由原作者来讲总比我来讲要透彻。

第一次出题和总结，如有问题请大家多指点，以上说明希望对大家有用~~如果有其他补充的后续会补上~~

hjj0451

wangg913 发表于 2012-10-16 13:13
附件就不上了，数组公式：
※※※※※※※※※※※※※※※※※※※※※※※※※
2012-11-7 18:40  简化 ...

竞赛区只有一个版主活跃还真不行。等我努力某日整个精华申请版主后给你加头版，呵呵。

PS：我又有一题，不过还没有好办法，需要继续想。
还有好多题，我都没做出答案。