比较excel中的两个范围(不进行线性比较)

我有两个excel范围.

我需要做以下事情:

1).计算我有多少相等的值,除了零.在我的例子中,它应该是2(1和8).我找到了这个公式:SUMPRODUCT(--(A2:E2=A3:E3))但它只匹配B1,B2,忽略了8号也出现了两次.

2).另外,我需要在单个单元格中使用这些重复值,用逗号分隔,就像"1,8"一样.

试试这个SUM超过COUNTIFS array¹公式,

=SUM(COUNTIFS(A2:E2, "<>"&0, A2:E2, A3:E3))

¹ _{数组公式需要用Ctrl+ Shift+ 完成Enter↵.一旦正确进入第一个单元格,就可以像任何其他公式一样向下或向右填充或复制它们.尝试并减少对更接近表示实际数据范围的范围的全列引用.数组公式以对数方式计算计算周期,因此将参考范围缩小到最小值是一种好习惯.有关更多信息,请参阅数组公式的指南和示例.}

对于你的问题的后半部分,我将提供这个基本的UDF,它将匹配的值串联在一起.作为编程爱好者,您应该非常乐意修改代码以包含频率计数.

Function stringMatches(rng1 As Range, rng2 As Range, _
                       Optional sDELIM As String = ", ", _
                       Optional bNOZERO As Boolean = True)
    Dim sTMP As String, rng As Range

    stringMatches = vbNullString
    For Each rng In rng1
        If (CBool(Application.CountIf(rng2, rng.Value)) And Not bNOZERO) Or _
           (CBool(Application.CountIfs(rng2, "<>" & 0, rng2, rng.Value)) And bNOZERO) Then
            sTMP = sTMP & rng.Value & sDELIM
        End If
    Next rng

    If CBool(Len(sTMP)) Then _
        stringMatches = Left(sTMP, Len(sTMP) - Len(sDELIM))
End Function