ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
123
返回列表 发新帖
楼主: Zaezhong

[Excel 函数与公式] [第91期]计算所有业务员的几何平均值之和(已总结)

[复制链接]

TA的精华主题

TA的得分主题

发表于 2012-11-11 11:54 | 显示全部楼层
本帖最后由 hjj0451 于 2012-11-11 11:01 编辑

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

点评

本题的陷阱在哪里?我都不知道坑在哪里,更不知道自己是否已经掉坑里了。  发表于 2012-11-11 12:26
答案有问题的不仅仅是保留小数的问题,也正如你说,保留出来剥削一层嵌套和方便查看结果外没有其他用途,但是本题的陷阱并不在于此,只要实际的值大小不影响,均不设扣分处理  发表于 2012-11-11 12:12

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-11 14:13 | 显示全部楼层
本帖最后由 Zaezhong 于 2012-11-11 20:05 编辑

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


本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

点评

建议:因计算过程复杂,模拟结果不易,由此产生的数字精度有些差异,上述三种情况都不在考核范围。 主要计算方法合意即可,?  发表于 2012-11-11 15:25
哈哈,看来本题的陷阱,包含:1、可能存在小数销量;2、计算过程中的小数保留问题;3、极端情况,仅有一个销售员。  发表于 2012-11-11 15:19

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-11-11 18:23 | 显示全部楼层
本帖最后由 Zaezhong 于 2012-11-11 20:34 编辑

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

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

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







本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

6

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-11-13 21:53 | 显示全部楼层
本帖最后由 hjj0451 于 2012-11-13 21:13 编辑
wangg913 发表于 2012-10-16 13:13
附件就不上了,数组公式:
※※※※※※※※※※※※※※※※※※※※※※※※※
2012-11-7 18:40  简化 ...


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

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



点评

大头哥,别隐啊,顺便把吴姐大侠挖出来.  发表于 2012-11-27 09:45
直接申请就可以,你技术分这么多,没有精华应该也行。去试试吧。竞赛区正缺人手。我也要隐退了。  发表于 2012-11-14 12:23
支持大师申请版主,支持出题 ^_^  发表于 2012-11-14 07:46
这么多资源,分给我两个题目吧,哈哈……  发表于 2012-11-13 22:36
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 02:45 , Processed in 0.040143 second(s), 7 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表