|
Sub Click()
Dim A, d, i, k, t
Set d = CreateObject("scripting.dictionary")
A = [B3:D15]
For i = 2 To UBound(A)
t = A(i, 1) & A(i, 2)
d(t) = d(t) & "," & A(i, 3)
Next i
k = d.keys: t = d.items
For i = 0 To UBound(t)
t(i) = IIf(f(t(i)) = "", "无", f(t(i)))
Next i
Range("c17:d65536").ClearContents
Range("c17").Resize(UBound(k) + 1) = Application.Transpose(k)
Range("d17").Resize(UBound(k) + 1) = Application.Transpose(t)
End Sub
'求某个key中的值
Function f(s)
Dim B, C, i, x
'转为数值数组,并排序
B = Split(s, ",")
ReDim C(UBound(B))
For i = 1 To UBound(B)
C(i) = B(i) * 1
Next i
Call SelectionSort(C)
'只保留第一次符合要求的值
x = False
For i = 1 To UBound(C) - 1
If Abs(C(i) - C(i + 1)) > 0.03 Then
If x Then x = False
Else
If x = False Then x = True: f = f & "," & C(i)
End If
Next i
f = Mid(f, 2)
End Function
Sub SelectionSort(arr)
Dim i, j, t, k
For i = LBound(arr) To UBound(arr) - 1
k = i
For j = i + 1 To UBound(arr)
If arr(k) > arr(j) Then k = j '升序
Next
If k <> i Then t = arr(k): arr(k) = arr(i): arr(i) = t
Next
End Sub
自动筛选转置2.rar
(14.19 KB, 下载次数: 8)
|
评分
-
查看全部评分
|