比较两个范围：经典范围与单个单元格中逗号分隔值的范围

Suppose I've got a range of values: 6 2 4 7 8 7 1 5 1 9 4 6 8 8 1 7 4 2

假设我有一系列值:6 2 4 7 8 7 1 5 1 9 4 6 8 8 1 7 4 2

And separately in a single cell: "1, 4, 9"

并且单独在一个单元格中:“1,4,9”

I need to compare these values in a single cell with the given range. In case if there is a match - type "Yes", if not - "No".

我需要在给定范围的单个单元格中比较这些值。如果匹配,则键入“是”,否则键入“否”。

"1, 4, 9" - result: "Yes"

“1,4,9” - 结果:“是”

"3, 10, 11" - result: "No"

“3,10,11” - 结果:“不”

Here I've got a UDF that simply compares two ordinary ranges and enters number of times values were matched as a result. Should work as a kind of a template I suppose.

在这里,我有一个简单地比较两个普通范围的UDF,并输入值作为结果匹配的次数。我认为应该作为一种模板工作。

 Public Function Compare(r1 As Range, r2 As Range) As Long
   Dim r As Range, v As Variant, v2 As Variant
   Dim rr As Range
   For Each r In r1
      v = r.Value
      If v <> 0 And v <> "" Then
         For Each rr In r2
            v2 = rr.Value
            If v = v2 Then Compare = Compare + 1
         Next rr
      End If
   Next r
End Function

1 个解决方案

#1

I modified the code to split the first cell range:

我修改了代码以拆分第一个单元格范围:

 Public Function Compare(r1 As Range, r2 As Range) As Long
   Dim r As Integer, v As Variant, v2 As Variant
   Dim rr1() As String
   Dim rr As Range
   rr1 = Split(r1, ",")
   For r = LBound(rr1) To UBound(rr1)
      v = --Trim(rr1(r))
      If v <> 0 And v <> "" Then
         For Each rr In r2
            v2 = rr.Value
            If v = v2 Then Compare = Compare + 1
         Next rr
      End If
   Next r
End Function

To get the "Yes/No" you would call it like this:

要获得“是/否”,您可以这样称呼它:

=IF(Compare(A1,$D$1:$D$18)>0,"Yes","No")

#1