在Excel中，A、B列的数据，两列数据中含有一个或者多个相同数字时，如何先对比再提取相同的数字出来？

Question

例如下图

Answer 1

摸索到了一个公式，但提取的数字只能按升序排列，如第9行，C9得到的数字是578，而不是785，可以吗？
C1公式：
=TEXT(SUM(LARGE(IF(MMULT(1-ISERR(FIND(ROW($1:$10)-1,A1:B1)),{1;1})>1,ROW($1:$10)-1,),ROW($1:$10))*10^(ROW($1:$10)-1)),"0;0;""""")
同时按Ctrl+Shift+Enter三键输入数组公式，下拉。追问

你好！你这个数组只能用到纯数字的对比，如果对比的数字经过筛选出来的话就自动显示错误的。还有一个问题就是这个数组公式不能显示交集数字0，自动省略掉了。我求两列数据交集中的其中两列数据是经过其它函数公式经过计算以及筛选得到的数值，而不是纯手工输入的数值。

追答

你的追问一半是对的，不能把相同的0选出来。但另一半经验证，A、B均是公式得到的数字一样可以取出来的。
要取出0，公式要复杂很多了：
=TEXT(SUM(LARGE(IF(MMULT(1-ISERR(FIND(ROW($1:$10)-1,A1:B1)),{1;1})>1,ROW($1:$10)-1,),ROW($1:$10))*10^(ROW($1:$10)-1)),REPT(0,COUNT(0/(MMULT(1-ISERR(FIND(ROW($1:$10)-1,A1:B1)),{1;1})>1))))
数组公式

追问

请问你所说的A、B两列数据是必须紧挨的吧，而我对比的两列数据并不是这样的。我对比的是C列和X列，所以才显示是错误的。想问下怎么修改这个数组，能求得正确的交集（包括0在内）？

追答

放弃！最怕提问与实际问题根本不是一回事，因为Excel的公式要引用原数据单元格，自己提问时把问题改动，别人费一个晚上来为你解答，结果实际问题却并非如此。但还上补一个贴图，说明上面提供的公式是没问题的，A、B分别是用公式引用F、G列，一点问题也没有

追问

谢谢老师的细心解答，我这样做只是想把问题简化的，所以才问出这么多问题了。不过最后还是谢谢老师的耐心以及帮助！

追答

自己把公式修改成类似于以下方式试试，公式中的
{1;1}
根据两列数据中间空了多少列就用英文分号分开加多少个0（下面的公式中隔了B列，加一个0）：
=TEXT(SUM(LARGE(IF(MMULT(1-ISERR(FIND(ROW($1:$10)-1,A1:C1)),{1;0;1})>1,ROW($1:$10)-1,),ROW($1:$10))*10^(ROW($1:$10)-1)),REPT(0,COUNT(0/(MMULT(1-ISERR(FIND(ROW($1:$10)-1,A1:C1)),{1;0;1})>1))))

Answer 2

你可以在C列输入公式：｛=IF(COUNT(FIND(0,A1:B1))>1,0,)&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$1000),A1)*FIND(ROW($1:$1000),B1))*ROW($1:$1000)*10^(9-ROW($1:$1000))),0,)｝
这是数组公式。你懂得。
这个是三键盘确定的，即是：CTRL+SHIFT+ENTER追问

如果两列有5万个数据—（A列有50000个单元格，B列也一样的）来对比，请问要改哪里？

追答

哪就修改数值，将公式中的1000改成你想要的
如果你的excel版本是2007以上的，将1000改成十万都可以
=IF(COUNT(FIND(0,A1:B1))>1,0,)&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$50000),A1)*FIND(ROW($1:$50000),B1))*ROW($1:$50000)*10^(9-ROW($1:$50000))),0,)｝

追问

你好，这个数组下拉之后，C列很多单元格中没有交集就自动出现0，而是是无规则出现的，这是怎么回事？

追答

你的是什么版本？

追问

2010版本的

追答

稍微等下，我在接电话

追问

嗯，好的。我这里的A、B两列的数据是经过其它函数筛选过来的，而不是纯数字的，我用纯数字来用这个数组就没出现0值了

追答

哦，也许因为数据庞大所造成
=IF(COUNT(FIND(0,A1:B1))=2,0,"")&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($1:$50000),A1))+ISNUMBER(FIND(ROW($1:$50000),B1))=2,ROW($1:$50000)*10^(10-ROW($1:$50000)))),0,)
这个公式是可以的，不过计算起来很耗时间，当公式下拉后，需要等待很长时间。并且，需要再次下拉。

Answer 3

这个要写一个自定义函数，求字符串的交集追问

这个我知道，但是我不会写函数，所以才问各位大神、老师，哪位大神、老师能帮我解答下

追答

在vba编辑器输入一下代码

Public Function Str_Intersect(Str1 As String, Str2 As String) As String
    
    '如果参数1，2有长度为0 的，返回零长度字符串
    If Len(Str1) = 0 Or Len(Str2) = 0 Then
        
        Str_Intersect = ""
        Exit Function
        
    End If
    
    
    Dim PickedStr As String
    
    Dim i As Integer, j As Integer
    
    Dim ReStr1 As String
    
    
    Str_Intersect = ""      '初始化返回值
    
    
    '去除str1中的重复项
    ReStr1 = Str1
    
    For i = 1 To Len(Str1) - 1
        
        PickedStr = Mid(Str1, i, 1)
        
        ReStr1 = Left(ReStr1, i) & Replace(ReStr1, PickedStr, "", i + 1)
        
    Next
    
    
    '找出str1和str2共有的字符
    
    For i = 1 To Len(ReStr1)
        
        PickedStr = Mid(ReStr1, i, 1)
        
        For j = 1 To Len(Str2)
            
            If PickedStr Like Mid(Str2, j, 1) Then
                
                Str_Intersect = Str_Intersect & PickedStr
            
            End If

        Next
        
    Next
    
End Function

Answer 4

=IF(COUNTIF($A$1:$A$65536,B1)>=1,B1,"")追问

你好，你给的解答结果是空集，任何交集的数字都没有

追答

明白你的意思了，超级大相师的数组公式是正确的。