[53期国庆附加题 ]Long值的移位运算 (VBA 的函数题) -- 已结

ldy

修改了评分标准，增加了得分机会。答过题的请再来看看。9-24
此题原是VBA题，现把它转为函数题，但需要VBA方面的知识。
此题用到的知识简单，但有些难度，恰逢国庆中秋所以分给的较高，最高 5 分。
现在要我重做这题，怕也是没勇气做了，太绕了。呵呵 ，看那位幸运儿能取走这 5 分。
Long值的移位运算的相关讨论参考，
http://club.excelhome.net/thread-440908-1-1.html

11楼有帮助理解的文件下载。http://club.excelhome.net/attachment.php?aid=521819&k=2f04cf8f96baf0a384d5b40c76e01a3e&t=1253630724
如果权限不够请下载这个


这题不能算传统意义上的函数题，算做VBA 的函数体现形式，以附加题的形式出现。

wshcw 版主有好题请继续出题。



题目说明
A列的范围限制在-2147483648 到 2147483647之间的整数，包括 0
B列的范围限制在-7 到 7之间的整数 包括 0
如果不了解移位运算的含义
那么可以这样理解：
根据A列和B列的数字，在C2编写公式后下拉填充，使C的值等于D
E1单元格变为绿色，则说明结果正确，然后优化公式使之符合得分标准。
答题要求：
不使用VBA，不得修改 A、B、D、E列的值
公式不能使用辅助列，也不能定义名称(自定义单元格格式也不算)。

评分标准 1  公式中不使用 MOD 函数。
公式长度<=130字符，且结果正确得 1分
公式长度<=110字符，且结果正确得 2分
公式长度<=100字符，且结果正确得 3分

评分标准 2  公式中使用 MOD 函数。

公式长度<=100字符，且结果正确得 1分
公式长度<=90 字符， 且结果正确得 2分
公式长度<=75字符， 且结果正确得  3分


两种方式兼答兼得，最高 5 分

公式长度计算包含 “=”号
答题者请跟帖占位。
请勿在跟贴中直接发答案(跟帖中如附答案一律不得分).
请将C2 的公式 和 论坛ID 发送到 26258103@163.com，截止日期 2009-10-20
邮件标题为： 53期函数国庆附加题答案
例:
ID: ldy
普通公式 长度=106:   =xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx





修改说明：
由于我的解题思路是从VBA转过来的，MOD 的处理数字的范围在工作表和VBA中是不同的，
x =  A mod  B , A 和  B 都必须是 Long ，否则就会溢出
我做这道函数题时，思路受VBA的影响，没有使用MOD函数，可以移植到VBA代码中。
liuzj2s 会员的答案用MOD函数解题，很棒。

[ 本帖最后由 ldy 于 2009-9-24 16:46 编辑 ]

zhoukick

占位，等坑

ldy又玩猫腻，其中有五组数据错误
我的公式包括=长度为91，还可以优化一些
-100000000 7 -2062581760 84901888 FALSE
-100000000 6 -2105032704 -2105032704 TRUE
-100000000 5 -1052516352 1094967296 FALSE
-100000000 4 -1600000000 -1600000000 TRUE
-100000000 3 -800000000 -800000000 TRUE

邮件已发送

[ 本帖最后由 zhoukick 于 2009-9-23 16:52 编辑 ]

我给出的对照值是正确的，是你的公式中有错误。用1楼的二进制演示文件对照一下吧。——ldy



普通公式 Len=91: =IF(B9=0,A9,MOD(INT(IF(A9<0,2^32+A9,A9)*2^B9),IF(IF(A9<0,2^32+A9,A9)*2^B9>2^31,-1,1)*2^31))

结果有误。

[ 本帖最后由 ldy 于 2009-10-21 20:45 编辑 ]

liuzj2s

占个位置:)

邮件已发送

****************************
对自己公式的进一步测试, 放宽对移位位数的限制，用的是"二进制演示.xls"，将原值改为随机数，由于我的方法主要用的乘法和取余，当左移位数达到16位时，计算可能会受到Excel的15位有效数字的影响，造成结果有偏差；当左移位数达到27时，又可能遇到另外一个Excel中的限制，Mod函数中，两数相除下来的商不能超过2^27(含)，否则会返回#Num!

****************************

收到 —— ldy


普通公式 长度=76:   =MOD(INT(((A2<0)*2^32+A2)*2^B2),(-1)^((B2>=0)*MOD(A2*2^B2,2^32)>=2^31)*2^32)
普通公式 长度=71:   =MOD(INT(MOD(A2,2^32)*2^B2),-(-1^OR(B2<0,MOD(A2*2^B2,2^32)<2^31))*2^32)
普通公式 长度=96:   =INT(A2*2^B2)-IF(B2<0,-(A2<0)*2^B2,INT(A2*2^(B2-32))+(A2*2^(B2-31)-INT(A2*2^(B2-32))*2>=1))*2^32
普通公式 长度=62:   =INT(A2*2^B2)-IF(B2<0,-(A2<0)*2^B2,INT(A2*2^(B2-32)+0.5))*2^32
普通公式 长度=61:   =INT(A2*2^B2)+((B2<0)*(A2<0)*2^B2-INT(A2*2^(B2-32)+0.5))*2^32


无Mod版，普通公式，长度=61
=INT(A2*2^B2)+((B2<0)*(A2<0)*2^B2-INT(A2*2^(B2-32)+0.5))*2^32

Mod版，普通公式，长度=60
=INT(MOD(A2,2^32)*2^B2)-INT(MOD(A2,2^32)*2^(B2-32)+0.5)*2^32

[ 本帖最后由 ldy 于 2009-10-21 20:38 编辑 ]

ldy

原帖由 泓() 于 2009-10-15 09:18 发表
实际应用不太的题目 将会被市场淘汰或被会员抛弃

这道题有一定的难度，但绝非你所说的实际应用不大的题目。

我相信你花了不少时间做这道题，但未能破题，这样的回复有参杂很多负面的情绪在内。

这是你的个人观点，应该尊重，但发错了地方，不仅与题目无关，而且打击其他会员的积极性。

本期3道题都有共通的地方，其他两道题分得的容易，你为什么不说这样话？

如果会员不感兴趣，1楼的附件会有111个下载吗？

考虑再三，决定扣1技术分作为严重警告。

lzzgzby

占位，邮件已发。
重在参与，等待学习！


收到--ldy

数组公式
110=SUM(MOD(RIGHT(INT(2*A2/2^ROW($1:$32))),2)*TEXT(ROW($1:$32)+B2,"[<32]1;[=32]-1;!0")*INT(2^(ROW($1:$32)+B2-1)))
117=SUM(MOD(RIGHT(INT(A2/2^ROW($1:$32)*2)),2)*TEXT(INT(2^(ROW($1:$32)+B2)/2),"[<"&2^31&"]0;"&"[="&2^31&"]"&-2^31&";!0"))
117=SUM(ROUNDDOWN(MOD(RIGHT(INT(A2/2^ROW($1:$32)*2)),2)*TEXT(ROW($1:$32)+B2,"[<32]1;[=32]-1;!0")*2^(ROW($1:$32)+B2)/2,))
123=SUM(INT(MOD(RIGHT(INT(A2/2^ROW($1:$32)*2)),2)*2^TEXT(ROW($1:$32)+B2,"[<32]0;!0")/2))-MOD(INT(A2/2^(31-B2)),2)*(B2>=0)*2^31

答案正确

[ 本帖最后由 ldy 于 2009-10-21 20:38 编辑 ]

chrisfang

答案已发送，请查收。


100 =INT(A2*2^B2+IF(B2<0,2^(B2+32)*(A2<0),-INT(A2*2^(B2-31))*2^31-MOD(RIGHT(INT(A2/2^(31-B2))),2)*2^31))
93  =INT(A2*2^B2+IF(B2<0,2^(B2+32)*(A2<0),-INT(A2*2^(B2-31))*2^31-ISODD(INT(A2/2^(31-B2)))*2^31))
93  =INT(A2*2^B2+IF(B2<0,2^(B2+32)*(A2<0),-INT(A2*2^(B2-31))*2^31-MOD(INT(A2/2^(31-B2)),2)*2^31))
77  =INT(MOD(A2*2^B2,2^((B2<0)*(B2+1)+31))-(B2>=0)*MOD(INT(A2/2^(31-B2)),2)*2^31)
73  =INT(MOD(A2*2^B2,2^((B2<0)*B2+32))-    (B2>=0)*MOD(INT(A2/2^(31-B2)),2)*2^32)
65  =INT((A2+(A2<0)*2^32)*2^B2-ROUND(A2*2^(B2-32)+(A2<0)*2^B2,)*2^32)
64  =INT(A2*2^B2-INT(A2*2^(B2-32)+0.5)*2^32+(A2<0)*(B2<0)*2^(32+B2))
47  =INT(MOD((A2+(A2<0)*2^32)*2^B2+2^31,2^32)-2^31)

[ 本帖最后由 ldy 于 2009-10-21 20:41 编辑 ]

ldy

函数题 Long值的移位运算 (VBA 的函数题)  
做这道题需要了解基础的二进制知识，要了解计算机是如何处理有符号的整数的，参考一楼的二进制演示文件。
有两个名称概念：成员数，序列数，每一个Long值都有两个身份 ，自身值和序列值，这也是此类题目容易让人头晕的地方。
成员数：Long是32位值，有4个字节，成员数= 2^32 或 =256^4 = 4294967296
序列数：自然数中没有0 ，所以跑得最快的人我们叫他第一名，而不会叫他第零名。
        从序列上来说0 是第一个Long值，而-1 是最后一个是 第4294967296个。
        但数学中是有零的，所以在这里 0 就是第零名，-1 就是4294967295名.
0 和正数的序列数就是自身的值，而负数的序列值则是，成员数+自身值
例如 -1 + 4294967296=4294967295， -2147483648+4294967296= 2147483648，-100000+4294967296=4294867296
位移简单来说就是 序列数*2^移位数，然后把结果转换为Long值。
转换公式为 x= mod(int(序列数*2^移位数),成员数)
               结果= if(x<2^31,x,x-2^32)
根据这个思路，我的原始的98字符公式
98字符=INT((A2+2^32*(A2<0))*2^B2)-2^32*INT(((A2+2^32*(A2<0))*2^B2/(2^31)+1)/2)*(INT((A2+2^32*(A2<0))*2^B2)>2^31)
收到  liuzj2s 会员答案 两种方法分别为96 和76字符，他已经可以得满分了。
普通公式 长度=96:   =INT(A2*2^B2)-IF(B2<0,-(A2<0)*2^B2,INT(A2*2^(B2-32))+(A2*2^(B2-31)-INT(A2*2^(B2-32))*2>=1))*2^32
普通公式 长度=76:   =MOD(INT(((A2<0)*2^32+A2)*2^B2),(-1)^((B2>=0)*MOD(A2*2^B2,2^32)>=2^31)*2^32)

在我把98字符发给liuzj2s 会员交流后，他又给出了更简短 61 和60 字符的答案。
无Mod版
61字符 =INT(A2*2^B2)+((B2<0)*(A2<0)*2^B2-INT(A2*2^(B2-32)+0.5))*2^32
Mod版
60字符 =INT(MOD(A2,2^32)*2^B2)-INT(MOD(A2,2^32)*2^(B2-32)+0.5)*2^32

同时我也发现，我的98字符的公式最后30个字符纯粹多余 *(INT((A2+2^32*(A2<0))*2^B2)>2^31) ，去掉这一部分后变为68字符。
68字符  =INT((A2+2^32*(A2<0))*2^B2)-2^32*INT((A2+2^32*(A2<0))*2^(B2-32)+0.5)
再简化到66字符

66字符 =INT((A2+2^32*(A2<0))*2^B2)-2^32*INT(A2*2^(B2-32)+2^B2*(A2<0)+0.5)
所有的 int(xxx+0.5) 可用 round(xxx,)代替，最终我的答案成为65字符
65字符 =INT((A2+2^32*(A2<0))*2^B2)-2^32*ROUND(A2*2^(B2-32)+2^B2*(A2<0),)

liuzj2s 会员的最终答案可缩短至60 和59
60字符 =INT(A2*2^B2)+((B2<0)*(A2<0)*2^B2-ROUND(A2*2^(B2-32),))*2^32
59字符 =INT(MOD(A2,2^32)*2^B2)-ROUND(MOD(A2,2^32)*2^(B2-32),)*2^32

到最后一刻，方版主的答案让我吃惊不小，关键问题想到了就容易了，其中一些技巧太值得学习。
64字符 =INT(A2*2^B2-INT(A2*2^(B2-32)+0.5)*2^32+(A2<0)*(B2<0)*2^(32+B2))
65字符 =INT((A2+(A2<0)*2^32)*2^B2-ROUND(A2*2^(B2-32)+(A2<0)*2^B2,)*2^32)
47字符 =INT(MOD((A2+(A2<0)*2^32)*2^B2+2^31,2^32)-2^31)


虽然字符长短与运行效率没有必然联系，但函数公式的运行效率并不好测试。
如果函数竞赛追求效率为上的话，很多时候两个甚至多个单元格，运行效率比一个单元格要快。
这就很难确定一个标准，作为竞赛来说最直观的就是公式的长短，这也是大多数函数题所采用的评分标准。
在更好的评分标准出现之前，公式的长度限制将是主要的评分标准。

我个人的看法是通过函数竞赛，可以加深对函数的了解，扩充知识面，活跃思维。
这就像邓爷爷打桥牌，不为输赢，为的是要保持一个思路清晰、敏捷的头脑。

目前的最短答案60和47字符未必就是最短的，此题虽然已结题，但如有兴趣，在此基础上更近一步，依然会获得加分鼓励。

chrisfang

哎呀，我的64字符最后部分做个合并同类项就能变成liuzj2s朋友的60字符公式了，可惜眼拙没能再进一步。

=INT(A2*2^B2-INT(A2*2^(B2-32)+0.5)*2^32+(A2<0)*(B2<0)*2^(32+B2))

lzzgzby

基本上不用函数，就是公式！
原来如此……大吃一惊。
我想歪了，佩服佩服。
学习学习，这就是差距。