ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] [第126期]碾转式按档计算最大返利

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2019-3-9 21:53 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 opqazxc 于 2019-3-10 08:05 编辑



附件就图片的excel内容``
再补充一下,lookup 配合得好没问题。。其他的就等总结的验证了。

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2019-3-12 16:12 | 显示全部楼层
opqazxc 发表于 2019-3-9 21:53
附件就图片的excel内容``
再补充一下,lookup 配合得好没问题。。其他的就等总结的验证了。

注:本题所有的讨论都是基于定义1,附件中列举了定义3的结果,主要是比较定义1,定义3和LOOKUP查询结果,三者之间的异同。

本帖子中包含更多资源

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

x

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2019-3-12 17:05 | 显示全部楼层
本帖最后由 anonym 于 2019-3-12 21:57 编辑

公式3,当查询交易额是25000时,出错。需要扩展成
=LOOKUP(A2:A17,MMULT(INT(MOD(ROW(1:8191)/2^COLUMN(A:M)*2,2)),IF(E3:E15,E2:F14,E9:F9)))

公式3需要根据查询值的最大值,调整二进制遍历的上限。

看来还是得改回类似公式1的下拉公式
=LOOKUP(MOD(A2,E$9),MMULT(INT(MOD(ROW($1:$255)/2^COLUMN(A:H)*2,2)),E$2:F$9))+INT(A2/E$9)*F$9

虽然目前看来LOOKUP是一个简便的解答,但我依然不认为它是完美的解答。

TA的精华主题

TA的得分主题

发表于 2019-3-13 09:50 | 显示全部楼层
anonym 发表于 2019-3-12 17:05
公式3,当查询交易额是25000时,出错。需要扩展成
=LOOKUP(A2:A17,MMULT(INT(MOD(ROW(1:8191)/2^COLUMN( ...

52
“如果”最大是定义3,那么最终结果应该是5,不是4,那么就不能按我那么写,可以是按2007版主的再弄个升序什么的。。
但太阳之子的题意,是定义1,所以没人会去考虑定义3,有也是刚好。。
再回现实,我感觉不会有商家这么做,而且现实中有时4的返利并不比2+35)少。。
倒是你说的更清楚的描述,由于我原本数学没读到那么高,也就Σ后面的我就不懂了,也就没资格评论了。。

只能看你最后两句的总结跟我想的没区别。。
lookup扩展的话并不是往三进制或四进制扩展。。
而是二进制。。在MMULT第二参数做手脚。。
53
公式3,原本就有考虑到25000,到是27650+才有可能出错
所以公式325000是不用改的,当然也确实有上限值,但原题写明20000。。
53#:虽然目前看来LOOKUP是一个简便的解答,但我依然不认为它是完美的解答。
这个就因人而异了,对于我自己来说,我认为是个好的解答就可以了,虽然看了别的同学还能再减,额。。
结合52+53楼,要是你想,到是可以写一个你认为完美又符合定义3的。。
对于此题最方便简单的肯定不是函数写在一单元格,而是辅助列,额
或者VBA什么的。。
因为写在一单元格的函数,只能枚举,也就有1048576,这个限制而且还卡。。
然后这是竞赛题
请看第一句话。。
我号是12年注册的,当时就真的是新手了。
然后到现在可以说是第一次这么用,可以说是个新的思路,也把lookup加强了下,对于我自己来说这价值才是最高的。。

本帖子中包含更多资源

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

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2019-3-15 22:28 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 anonym 于 2019-3-17 20:33 编辑

经过讨论,看来可以对LOOKUP下一个结论了。

前提:定义1。

对于“单次累计”,虽然错序,LOOKUP是正确的。

对于“多次累计”,交易额档(n+1)/交易额档(n)>2时,LOOKUP则是错误的。(此处有待商榷)(交易额档(n+1)/交易额档(n)<=2时,“多次累计”其实就是“单次累计”。)

TA的精华主题

TA的得分主题

发表于 2019-3-16 08:55 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
  1. =IFNA(LOOKUP(MOD(A2,E$9),MMULT(INT(MOD(ROW($1:$4095)/2^COLUMN(A:L)*2,2)),N(OFFSET(E$2,MATCH(ROW($1:$12)-1,MMULT(N(ROW($1:$7)>COLUMN(A:F)),INT(E$4:E$9/E$3:E$8-1%))),{0,1})))),)+INT(A2/E$9)*F$9
复制代码

堆一个,根据数据源机动性大点的,除了最后一档,其他项平均2次吧,
枚举的话,除了超过1048576没办法外,其他的我个人认为,lookup错的话也没什么公式了,对于纯一公式。。

TA的精华主题

TA的得分主题

发表于 2019-3-17 23:09 | 显示全部楼层
本帖最后由 anonym 于 2019-3-18 08:47 编辑
opqazxc 发表于 2019-3-16 08:55
堆一个,根据数据源机动性大点的,除了最后一档,其他项平均2次吧,
枚举的话,除了超过1048576没办法外 ...

opqazxc的公式是正确的。

对于定义1,多次累计,假设,最大相邻交易额档的整数倍数N=INT(MAX(交易额档(n+1)/交易额档(n)))=5,请参阅附件。

opqazxc的公式:
{=IFERROR(LOOKUP(MOD(A2,E$9),MMULT(INT(MOD(ROW($1:$16383)/2^COLUMN(A:N)*2,2)),N(OFFSET(E$1,MATCH(ROW($1:$14)-1,MMULT(N(ROW($1:$8)>COLUMN(A:G)),IFERROR(INT(E$3:E$9/E$2:E$8-1%),1))),{0,1})))),)+INT(A2/E$9)*F$9}

常数14的更精确的表示,应该是{=SUM(INT(E$3:E$9/E$2:E$8))-1}。

我的公式:
{=MMULT(--INT(MOD((IF(MOD(A2,E$9)>=E$2,MAX(-(MOD(A2,E$9)<MMULT(--INT(MOD((ROW($1:$78124)/5^COLUMN(A:G)*5),5)),E$2:E$8))+ROW($1:$78124)%%%)/1%%%,)/5^COLUMN(A:G)*5),5)),F$2:F$8)+INT(A2/E$9)*F$9}

请忽略公式的长短。在一些细节上,两个公式还有其他差别,先不谈。(我的EXCEL版本是2010,所以没用IFNA和BASE。BASE要比INT+MOD简单。之前用的ISODD在二进制上比INT+MOD字符少几个,但ISODD只能用在二进制上,不能用在N进制上。)

单次累计的解题思路,就是遍历所有的交易额然后查询,遍历的过程两者相似,即MMULT(二进制遍历系数, 交易额档)。
对于多次累计,我的思路是,修改MMULT的参数1———二进制遍历系数,扩展进制,即将二进制遍历系数扩展成N进制遍历系数。
而opqazxc的思路是,则是修改MMULT的参数2——交易额档,扩展交易额档。而查询用的LOOKUP公式依然成立,证明可参看我上面的帖子和附件的分析。

两个公式的一些区别是,(针对附件的数据)
1. 公式,我的公式遍历用了一次MMULT,查询用了一次MMULT;而opqazxc的公式遍历用了两次MMULT,查询用了LOOKUP。
2. 扩展,我的公式是对所有交易额档都扩展到N=5,而opqazxc的公式只对需要扩展的档扩展到相应的次数。
3. 极值,比如附件中交易额档档数G=8。
我的公式,N进制遍历系数的最大值始终是N^(G-1)-1=5^7-1=78124(2^16-1<78124<2^17-1)。
opqazxc的公式,如果只有一档交易额档扩展到N,二进制遍历系数的最大值的极小值是2^(N+G-1)-1=2^12-1=4095;如果所有交易额档都扩展到N,二进制遍历系数的最大值的极大值是2^(NG-N+1)-1=2^36-1=68719476735。

两个公式各有特点。还没有研究更一般的情况,即交易额档档数扩展到不同档数的概率,以及进制遍历系数的最大值的限制和效率(运算时间)的量化。两者总体感觉还是差不多的。简单看,当需要扩展的交易额档的所有的档数比较小时,opqazxc的公式更好些;而需要扩展的交易额档的所有的档数比较大时,N进制遍历公式更好些。(当然是在EXCEL函数公式允许数组行列数为1048576的前提下。)

opqazxc,看来你擅长“信手拈来”,我擅长“信口开河”!

本帖子中包含更多资源

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

x

TA的精华主题

TA的得分主题

发表于 2019-3-18 09:27 | 显示全部楼层
anonym 发表于 2019-3-17 23:09
opqazxc的公式是正确的。

对于定义1,多次累计,假设,最大相邻交易额档的整数倍数N=INT(MAX(交易额档 ...

“信手拈来”,“信口开河”。。
如果常量14得改,那么column(A:N)得改,ROW($1:$16383)也得改
我写4095是认为运算量还好,
我写出来是想说明能能扩展,仅仅思路上
如果我自己用会选择其他方式。。
算了,过了吧。。

TA的精华主题

TA的得分主题

发表于 2019-3-18 14:33 | 显示全部楼层
opqazxc 发表于 2019-3-18 09:27
“信手拈来”,“信口开河”。。
如果常量14得改,那么column(A:N)得改,ROW($1:$16383)也得改
我写4 ...

opqazxc,你误会了。

“信手拈来”,是指你回贴挺快,言简意赅,而且基本上都是正确的。
“信口开河”,是指我理解力差,喜欢用自己能理解的语言来描述问题。有时夸夸其谈,有时佶屈聱牙(别人能否明白尚未可知,否则我也是如同我前面帖子所说的“自娱自乐”了)。甚至有时不求甚解,而有时又钻牛角尖。而且回帖错误还挺多,上面的一些回帖至少有两三处。在有帖子修改权限的有效时间的范围内,我大都事后在“事故现场”标识了。还有未标识的,比如“当最低档交易额值为0时,最低档返利值不能为非0”,应该是“多次累计,当最低档交易额值为0时,最低档返利值不能为非0”,因为不知道最低档该统计多少次。而单次累计则没有这个问题。应该问题还有很多,乃至笔误,不再赘述。在“事故现场”标识出来,是为了让后来的“过路人”注意,谨慎看帖,绕道前行,或者甚而可以对着“事故现场”“啧啧啧”一番鄙夷一下“肇事人”,活跃一下气氛。

EXCEL函数题可以一题多解。有的时候,做出一个解,比较难于也懒于去思考其他的解,不是技巧上或简单函数上的不同解,而是思路算法上的。所谓窠臼,竞赛版提供了一个头脑风暴,破除窠臼的平台,交流讨论,集思广益,百花齐放,百家争鸣。所以也驻足此地,一论一思,一放一鸣。(EXCEL函数题,其实和初等数学上的两类题挺像。一类是平面几何题,取其一题多解;一类是小学应用题,取其逆思维。)

常量14,我用公式描述,不是说你的公式不对,而是为了呼应后面的最大值的极值的讨论。其实你的和我的公式,我都是就数据写公式,没有很精确,我所提到的忽略细节,也包括这个。是的,COLUMN(A:N)和ROW($1:$16383)是和14相关联的,需要修改。(题外话,用COLUMN获得常数数组,如果数据上限足够大,而又不能用ROW的话,我会用TRANSPOSE(ROW(“1:127”)),甚至常常乐见于TRANSPOSE(ROW(INDIRECT("1:"&N))),N是一个名称,可能是一个比较复杂的数组运算的结果,美其名曰“复用”。而有的时候定义名称,是为了可读性,可可读性究竟是变好还是变差,往往“英雄所见不同”。类似情况不少,这样公式就会比较冗长。所以,我之前的有个附件加了保护,一则也是因为“臭婆娘羞于见公婆”。UI界面还行,解法过程却挺繁复。如同挺干净的写字桌,拉开抽屉,满满的东西,还略显凌乱。二则,如果没人看附件,加了保护又有什么关系呢?当然也有点“故弄玄虚”,所谓营销的手段。)

我只是想把一些想到的问题,想清楚(想清楚了吗?),讲出来(正确描述了吗?),讨论下(过程愉快吗?),不是专门吹毛求疵找错误。网络论坛虽然虚拟,但应该更多是温馨愉快的社交场所,而不是“快意恩仇”,甚至“亲者痛,仇者快”的地方。不以注册时间论英雄,哪怕你是新人,不以用户级别而歧视,哪怕菜鸟一个。看帖读贴,是一种学习和享受,写帖回帖,又何尝不是呢?

TA的精华主题

TA的得分主题

发表于 2019-4-9 18:41 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
jivy1212 发表于 2019-3-2 09:37
我看了别人结果,也觉得LOOKUP二分法奇怪了,我一直卡在MMULT这段,因为觉得E2:F2没用,所以引用的是E$3: ...

跟你一样,刚开始以前是升序,用了lookup发现不对才把所有组合列出来看了一下。我最终采用的还是text处理把小于的置1用vlookup精确查找1(我的数据是用的降序,保证第1个1是从大到小的分法)
然后来看了下别人的答案,才发现lookup也能结果正确。
仔细看了下数据,如果升序二进制从0-63,则2700刚好在第32个上,这个值是lookup二分法的第一个判断点,也是导致之前出错的原因。他们正确的基本都是0-1-1-2-2-...-63-63-0这样的顺序。
如果不考虑100以下的找不到返回错误值,用1-63-0这样的顺序也是可以正确的,这个顺序第一个判断点是2650,保证了2650以上的都在这个之后查找。上面0-1-1-..-0这个顺序同时解决了0值和判断点问题。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-7-13 20:09 , Processed in 0.054925 second(s), 7 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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