如何在Excel 2007中执行两个二进制数的XOR计算 [英] How do I perform a XOR calculation of two binary numbers in excel 2007

问题描述

例如，我想对两个二进制数进行XOR计算:在工作表1上

I wanted to perform a XOR calculation of two Binary numbers for example: on Sheet 1

Range A1 = 10101010
Range A2 = 11100010

现在我需要对A1进行XOR，A2中的A2结果.我尝试了不同的两个公式进行XOR计算，例如:A1^A2, (BITXOR (A1, A2))，但不幸的是，它不起作用，因为我使用的是excel 2007，但不支持"XOR".

Now I need to perform XOR of A1, A2 result in A3. I tried different formula's two perform XOR calculations like: A1^A2, (BITXOR (A1, A2)) but unfortunately it didn't worked I think because I am using excel 2007 "XOR" doesn't support.

我希望得到1001000的结果.

推荐答案

首先，您应该注意Excel 2013之前的Excel没有内置的按位运算符或函数(即使操作数是数字，Excel的OR()函数也是逻辑的). Excel 2013终于添加了这个明显缺失的功能.

First, you should note that Excel pre-Excel2013 has no bitwise operators or functions built-in (Excel's OR() function is logical even if the operands are numeric). Excel 2013 finally adds this glaringly missing functionality.

最简单的方法是创建一个执行此操作的用户定义函数.如果您准备使用十进制输出，辅助列或非常重复的串联公式，但是公式可以使用，但是VBA可以克服这些限制-如果您能够在工作簿中使用代码，则我建议这样做.

The simplest way is to create a User Defined Function that does it. Formulae can work if you are prepared for either a decimal output, or helper columns, or a very repetitive Concatenate formula but VBA gets around these limitations - I recommend it if you are able to have code in the workbook.

十进制输入，十进制输出

以下示例仅公开了内置的按位运算符，以用作Excel公式中的函数.我假设是整数类型，尽管您可以将其更改为接受小数等.

The below examples just expose the built-in bitwise operators to use as functions in Excel formulae. I assume an integral type, although you could change it to accept decimals etc.

您可以使用Excel内置的BIN2DEC()函数将字符串二进制数字(例如"1010")转换为小数(对于前面的示例为10)，尽管这只能处理9位+符号位，或者可以使用数组公式为您转换(请参见下面有关使用公式"的部分).

You can convert your string binary numbers (e.g. "1010") to decimals (10, for the previous example) using the BIN2DEC() function built-in to Excel, although this only handles 9 bits + sign bit, alternatively you can use an array formula to convert it for you (see my section on "Using Formulas" below).

Public Function BITWISE_OR(operand1, operand2)
   BITWISE_OR = CLng(operand1) Or CLng(operand2)
End Function
Public Function BITWISE_AND(operand1, operand2)
   BITWISE_AND = CLng(operand1) And CLng(operand2)
End Function
Public Function BITWISE_XOR(operand1, operand2)
   BITWISE_XOR = CLng(operand1) Xor CLng(operand2)
End Function

使用公式将数字结果转换回二进制字符串是很烦人的-如果您需要的范围超出了Excel内置的DEC2BIN()(微不足道的-512到+511)函数所涵盖的范围，那么我建议您使用VBA (请参见下文)，或使用列或行逐位构建二进制字符串(请参见下面的使用公式"部分).

Converting the numeric results back to binary strings is pretty annoying with formulas - if you need more than the range covered by DEC2BIN() (a paltry -512 to +511) function built in to Excel then I would suggest either using VBA (see below), or building up your binary string bit by bit using columns or rows (see my Using Formulas section below).

二进制字符串输入，二进制字符串输出

以下内容实质上是根据输入字符串中的相应位依次遍历字符串，依次设置每个位.它使用Mid$语句就地对字符串执行位更改.位串可以是任意长度.

The below essentially iterates through a string setting each bit in turn based on the corresponding bits in the input strings. It performs the bit changes on the string in-place using Mid$ statement. Bit strings can be arbitrary length.

以下内容看起来很复杂，但实际上，对于And，Or和XOr，它们都是重复3次的相同基本内容.

The below looks complicated but really it is the same basic stuff repeated 3 times for each of And, Or and XOr.

'str1, str2: the two bit strings. They can be different lengths.
'significantDigitsAreLeft: optional parameter to dictate how different length strings should be padded. Default = True.
Public Function Bitstr_AND(str1 As String, str2 As String, Optional significantDigitsAreLeft As Boolean = True)
    Dim maxLen As Long, resStr As String, i As Long
    If Len(str1) > Len(str2) Then maxLen = Len(str1) Else maxLen = Len(str2) 'get max length of the two strings
    str1 = getPaddedString(str1, maxLen, significantDigitsAreLeft) 'pad left or right to the desired length
    str2 = getPaddedString(str2, maxLen, significantDigitsAreLeft) 'pad left or right to the desired length
    resStr = String$(maxLen, "0") 'prepare the result string into memory (Mid$ can operate without creating a new string, for performance)
    For i = 1 To maxLen
        If Mid$(str1, i, 1) = "1" And Mid$(str2, i, 1) = "1" Then
            Mid$(resStr, i, 1) = "1" 'in-place overwrite of the existing "0" with "1"
        End If
    Next i
    Bitstr_AND = resStr
End Function
'For explanatory comments, see Bitstr_AND
Public Function Bitstr_OR(str1 As String, str2 As String, Optional significantDigitsAreLeft As Boolean = True)
    Dim maxLen As Long
    Dim resStr As String
    Dim i As Long
    If Len(str1) > Len(str2) Then maxLen = Len(str1) Else maxLen = Len(str2)
    str1 = getPaddedString(str1, maxLen, significantDigitsAreLeft)
    str2 = getPaddedString(str2, maxLen, significantDigitsAreLeft)
    resStr = String$(maxLen, "0")
    For i = 1 To maxLen
        If Mid$(str1, i, 1) = "1" Or Mid$(str2, i, 1) = "1" Then
            Mid$(resStr, i, 1) = "1"
        End If
    Next i
    Bitstr_OR = resStr
End Function
'For explanatory comments, see Bitstr_AND
Public Function Bitstr_XOR(str1 As String, str2 As String, Optional significantDigitsAreLeft As Boolean = True)
    Dim maxLen As Long
    Dim resStr As String
    Dim i As Long
    If Len(str1) > Len(str2) Then maxLen = Len(str1) Else maxLen = Len(str2)
    str1 = getPaddedString(str1, maxLen, significantDigitsAreLeft)
    str2 = getPaddedString(str2, maxLen, significantDigitsAreLeft)
    resStr = String$(maxLen, "0")
    For i = 1 To maxLen
        If Mid$(str1, i, 1) = "1" Then
            If Not Mid$(str2, i, 1) = "1" Then
                Mid$(resStr, i, 1) = "1"
            End If
        ElseIf Mid$(str2, i, 1) = "1" Then 'Save an If check by assuming input string contains only "0" or "1"
            Mid$(resStr, i, 1) = "1"
        End If
    Next i
    Bitstr_XOR = resStr
End Function
'Helper to pad string
Private Function getPaddedString(str As String, length As Long, padLeft As Boolean) As String
    If Len(str) < length Then
        If padLeft Then
            getPaddedString = String$(length - Len(str), "0") & str
        Else
            getPaddedString = str & String$(length - Len(str), "0")
        End If
    Else
        getPaddedString = str
    End If
End Function

使用公式

您可以使用文本函数或Sumproduct进行XOR操作.如果您不想使用VBA，但公式难以确保它们涵盖所有情况，例如负数或不同长度的二进制字符串，这可能会更合适.我向您推荐了出色的博客文章 http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html (使用Sumproduct和

Using Formulas

You can do an XOR operation using Text functions or Sumproduct. This may be more appropriate if you do not want to use VBA but formulas are painful to ensure they covers all situations, like negatives or different length binary strings. I refer you to the superb blog post http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html for examples using Sumproduct, and http://chandoo.org/wp/2011/07/29/bitwise-operations-in-excel/ for examples using Text functions.

我自己编写了可以处理某些情况的公式，并在下面解释它们以指导您.

I cooked up my own formulae that handles certain cases and I explain them below to guide you.

二进制字符串输入，十进制输出

在下面，A2和B2最多以32位字符串形式指代两个二进制数.字符串可以是可变长度的，因为公式会将0填充到所需的长度.如何将其增加到更多位应该是显而易见的. 必须使用Ctrl + Shift + Enter 输入.

In the below, A2 and B2 refer to the two binary numbers in up to 32-bits string form. The strings can be variable length, as the formula will pad with 0's to the necessary length. It should be obvious how to increase it to more bits. They must be entered using Ctrl+Shift+Enter.

最高有效位在左侧.要使它的最低有效位在左侧，可以除去2部分的幂的小减法，然后使其移到右侧.

The most significant bit is on the left. To make it least significant bit on the left, you can remove the little subtraction in the powers of 2 part, and make it pad to the right.

按位与:

=SUM((((MID(REPT("0",32-LEN($A$2))&$A$2,ROW($1:$32),1)="1")+(MID(REPT("0",32-LEN($B$2))&$B$2,ROW($1:$32),1)="1"))=2)*(2^(32-ROW($1:$32))))

按位或:

=SUM((((MID(REPT("0",32-LEN($A$2))&$A$2,ROW($1:$32),1)="1")+(MID(REPT("0",32-LEN($B$2))&$B$2,ROW($1:$32),1)="1"))>0)*(2^(32-ROW($1:$32))))

按位异或:

=SUM((((MID(REPT("0",32-LEN($A$2))&$A$2,ROW($1:$32),1)="1")+(MID(REPT("0",32-LEN($B$2))&$B$2,ROW($1:$32),1)="1"))=1)*(2^(32-ROW($1:$32))))

二进制字符串输入，二进制字符串输出

单个单元格的解决方案将很艰巨，因为Excel中没有数组连接公式.您可以使用CONCATENATE函数将每个位粘合在一起，每个位都是If的结果，如果比较每个二进制字符串，则返回1或0(如果合适).就像我说的那样，尽管很容易(就像= IF(Mid(A1,1,1)="1"，...那样建立起来)，但是这很无聊，所以我个人不会为您这样做；)

A single cell solution would be arduous because there is no array concatenation formula in Excel. You could do it using the CONCATENATE function glueing together each bits, with each bit being the result of an If comparing each binary string returning 1 or 0 as appropriate. As I said, though easy (just build it up like =IF(Mid(A1,1,1) = "1",...), this would be boring so I personally won't do it for you ;)

或者，您可以更简单地使用列或行来构建字符串，例如: 如果A1和B1有您的二进制字符串，则在C1中放置(对于AND或OR，将= 2的末尾更改为> 0，对于XOR则将其更改为= 1):

Alternatively, you could do it more simply using columns or rows to build up the string, like: If A1 and B1 have your binary strings, then in C1 put (for AND, or for OR change the =2 at the end to >0 and for XOR change it to =1):

=IF((MID($A1,1,1)="1")+(MID($B1,1,1)="1"))=2，"1"，"0")

然后在D1中放:

=C1 & IF((MID($A1,COLUMN()-COLUMN($C1),1)="1")+(MID($B1,COLUMN()-COLUMN($C1),1)="1"))=2,"1","0")

然后将其拖动到尽可能多的列上

Then drag this across as many columns as bits

这篇关于如何在Excel 2007中执行两个二进制数的XOR计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！