楼主
liuguansky |
Q:如何在三个单元格中查找三个单元格的内容，要一个单元格匹配一个单元格，但是不论顺序，可以匹配的返回"组",不能匹配的返回空？
A：用如下自定义函数代码可以实现：
- Function ZZu(rng1 As Range, rng2 As Range) As String
- Application.Volatile
- Dim arr1, arr2, i%, j%, arr0
- ZZu = ""
- arr0 = Array("1,2,3", "1,3,2", "2,1,3", "2,3,1", "3,1,2", "3,2,1")
- If rng1.Count = rng2.Count And rng1.Count = 3 Then
- arr1 = rng1.Value
- arr2 = rng2.Value
- For i = 0 To 5
- arr = Split(arr0(i), ",")
- s = 0
- For j = 1 To 3
- If arr2(1, CInt(arr(j - 1))) Like "*" & arr1(1, j) & "*" Then s = s + 1
- Next j
- If s = 3 Then ZZu = "组": Exit For
- Next i
- End If
- End Function
或者：
- Function Zu(rng1 As Range, rng2 As Range) As String
- Application.Volatile
- Dim arr1, arr2, arrt() As Integer, i%, j%, arrm() As Integer, arrn() As Integer
- Zu = ""
- If rng1.Count = rng2.Count And rng1.Count = 3 Then
- arr1 = rng1.Value: arr2 = rng2.Value
- ReDim arrt(1 To 3, 1 To 3)
- For i = 1 To 3
- For j = 1 To 3
- If arr2(1, j) Like "*" & arr1(1, i) & "*" Then
- arrt(i, j) = 1
- Else: arrt(i, j) = 0
- End If
- Next j, i
- ReDim arrm(1 To 3): ReDim arrn(1 To 3)
- For i = 1 To 3
- arrm(i) = Application.Sum(Application.Index(arrt, 0, i))
- Next i
- For i = 1 To 3
- arrn(i) = Application.Sum(Application.Index(arrt, i, 0))
- Next i
- If Application.Min(Application.Min(arrm), Application.Min(arrn)) > 0 Then Zu = "组"
- End If
- End Function
|