按ALT+F11 打開代碼窗口,
在左邊的格(工程資源管理器)內,右鍵,
插入,模塊,
將下面的代碼復制到右邊窗口內(模塊1的代碼窗口),
-----------
Public Function iJH(iRng1 As Range, iRng2 As Range) As String
Application.Volatile
If iRng1.Cells.Count < 1 Or iRng2.Cells.Count < 1 Then iJH = "#參數有誤": Exit Function
Dim i As Long, iStr1 As String, tmp As String, c As Range
For Each c In iRng1
iStr1 = iStr1 & "<" & c.Value & ">"
Next
For Each c In iRng2
If c.Value <> "" Then
If InStr(iStr1, "<" & c.Value & ">") Then tmp = tmp & c.Value & ","
End If
Next
iJH = Left(tmp, Len(tmp) - 1)
End Function
----------------
然後關閉代碼窗口,返回sheet表
現在就可以將這個iJH()函數當做普通函數使用了,他帶有兩個參數,即數據集所在的兩個區域
如果第壹集合在 A1:B2 內:
1 3
2 4
第二集合在 E3:F4 內:
1 4
6 7
在其他任意單元格內輸入公式
=iJH(A1:B2,E3:F4)
顯示結果:
1,4