[第126期]碾转式按档计算最大返利

opqazxc

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

anonym

opqazxc 发表于 2019-3-9 21:53
附件就图片的excel内容``
再补充一下，lookup 配合得好没问题。。其他的就等总结的验证了。

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

anonym

opqazxc 发表于 2019-1-6 17:50
下拉``

区域``

公式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是一个简便的解答，但我依然不认为它是完美的解答。

opqazxc

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

回52楼

“如果”最大是定义3,那么最终结果应该是5,不是4,那么就不能按我那么写，可以是按2楼007版主的再弄个升序什么的。。

但太阳之子的题意，是定义1，所以没人会去考虑定义3，有也是刚好。。

再回现实，我感觉不会有商家这么做，而且现实中有时4的返利并不比2+3（5）少。。

倒是你说的更清楚的描述，由于我原本数学没读到那么高，也就Σ后面的我就不懂了，也就没资格评论了。。

只能看你最后两句的总结跟我想的没区别。。

但lookup扩展的话并不是往三进制或四进制扩展。。

而是二进制。。在MMULT第二参数做手脚。。

回53楼

公式3，原本就有考虑到25000，到是27650+才有可能出错

所以公式3在25000是不用改的，当然也确实有上限值，但原题写明20000。。

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

这个就因人而异了，对于我自己来说，我认为是个好的解答就可以了，虽然看了别的同学还能再减，额。。

结合52+53楼，要是你想，到是可以写一个你认为完美又符合定义3的。。

对于此题最方便简单的肯定不是函数写在一单元格，而是辅助列，额

或者VBA什么的。。

因为写在一单元格的函数，只能枚举，也就有1048576，这个限制而且还卡。。

然后这是竞赛题

请看第一句话。。

我号是12年注册的，当时就真的是新手了。

然后到现在可以说是第一次这么用，可以说是个新的思路，也把lookup加强了下，对于我自己来说这价值才是最高的。。

anonym

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

前提：定义1。

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

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

opqazxc

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错的话也没什么公式了，对于纯一公式。。

anonym

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，看来你擅长“信手拈来”，我擅长“信口开河”！

opqazxc

anonym 发表于 2019-3-17 23:09
opqazxc的公式是正确的。

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

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

anonym

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界面还行，解法过程却挺繁复。如同挺干净的写字桌，拉开抽屉，满满的东西，还略显凌乱。二则，如果没人看附件，加了保护又有什么关系呢？当然也有点“故弄玄虚”，所谓营销的手段。）

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

xzk235

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值和判断点问题。