Excel揭秘13：在Excel中实现位运算

位运算的规则，如何在Excel中将十进制转换成二进制并进行位算。...





我们知道，计算机使用的是二进制计数法。也就是说，在计算机中的所有信息都是使用二进制来存储和处理的。

下表列出了我们熟悉的十进制数及与其相对应的二进制数：



位运算规则

在位运算中，“按位与”运算（AND运算）分别按位比较两个相应的数字（0或1），当且仅当这两个数字都为1时，结果才为1，如下式所示：



可以看出，实现“按位与”运算，就是将对应位置的数相乘。因此，从右自左的第1个位置是1*1=1，第2个位置是0*1=1，等等，最后得到结果。

除了“按位与”运算外，还有按位或（OR）、异或（XOR）、蕴含（IMP）、相等（EQ）和非（NOT）运算，它们都有不同的运算规则。例如，对于OR运算来说，当两个数字至少有一个为1时，结果为1，如下式所示：



同理，可以看出，实现“按位与”运算，就是将对应位置的数相加，结果为正数就是1，结果为零就是0。因此，从右自左第1个位置是1+1=2，即为1，第2个位置是0+1=1，就是1，等等，最后得到结果。

其他的位运算规则可以参阅相关资料，下面主要以“按位与”和“按位或”为例，讲解如何在Excel中实现位运算。

将十进制数转换成二进制数

有时候，在Excel工作表中实现位运算是非常有用的，然而，Excel并没有直接用于位运算的函数，这就需要我们组合一些函数来实现位运算功能。

下面，以处理0至255之间的十进制数为例。

在将0至255之间的十进制数转换成二进制数时，需要的二进制数的位数是8位。例如，十进制数0的二进制数表示为：

00000000

十进制数255的二进制数表示为：

11111111

可以使用下面的公式来获得十制数相对应的二进制数的每一位（0或1）：

=MOD(INT(十进制数/(2^(二进制数所处的位置值-1))),2)

也就是说，要求二进制数中的其中一位，先求2对该位所在的位置值减1的幂，然后由其十进制数除以所求幂的结果，对求得的结果取整，并作为MOD函数的参数求其对2的余数，即为该位所在的二进制数（0或1）。

下图1演示了在Excel工作表中使用公式将十进制数222转换成二进制数11011110的过程。



图1

在将十进制数转换成二进制数后，我们可以按照位运算的规则对两个或多个二进制数进行位运算。

在Excel中实现位运算

下图2展示了“按位与”运算，并将二进制数结果转换回十进制数。



图2

下图3展示了“按位或”运算，并将二进制数结果转换回十进制数。



图3

可以看出，将二进制数转换成十进制数，只需将对应的位乘以2的该位所在位置减1的结果次方，然后将所有结果相加即可。

使用SUMPRODUCT函数与上面的公式相结合，可以省去上面介结的一些中间步骤，即在一个公式中就将二个十进制数转换成二进制数，将它们“按位与”或者“按位或”，最后将位运算的结果转换成十进制数的结果。

首先，创建一个命名公式。

名称：b

引用位置：={1;2;4;8;16;32;64;128}

即用于转换的数字常量组成的数组。第1个位置值使用2^(1-1)，第2个位置使用2^(2-1)，第3个位置使用2^(3-1)，为8位数的每一个位执行这个操作。

下面是我们的SUMPRODUCT公式，执行两个十进制数（dec1和dec2）的“按位与”运算：

=SUMPRODUCT(b * MOD(INT(dec1/b),2) * MOD(INT(dec2/b),2))

下面的公式执行“按位或”运算：

=SUMPRODUCT(b * SIGN(MOD(INT(dec1/b),2) + MOD(INT(dec2/b),2)))

公式中使用SIGN函数使结果数字值不会大于1。

因此，上文中的图2和图3所示的运算都可以使用一个公式来完成，如下图4所示。



图4

结语

位运算可以用来实现很多令人惊叹的电子表格设计，解决很多具有挑战性的问题，待我进一步学习后与大家分享。

本文为学习Excel Hero Blog的文章《5 And 3 Is 1》时整理的学习笔记，转载请注明出处。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎关注[完美Excel]微信公众号：

方法1—在微信“添加朋友”或者通讯录中搜索“完美Excel”或者“excelperfect”后点击关注。

方法2—扫一扫下面的二维码

    关注 完美Excel