|
- Function Paixu(xstr) '把任意数据型字符按各位数从小到大排序
- Dim w(9)
- For i = 1 To Len(xstr)
- a = Val(Mid(xstr, i, 1))
- w(a) = a
- Next
- Paixu = "'" & Join(w, "")
- End Function
- Sub tt()
- r = Cells(Rows.Count, 1).End(xlUp).Row
- c = Cells(2, Columns.Count).End(xlToLeft).Column
- arr = [a1].Resize(r, c)
- Set d = CreateObject("scripting.dictionary")
- For i = 3 To c '各列
- x = Paixu(arr(2, i)) '各数内排序
- d(x) = d(x) & "," & i '得出列号,入字典
- arr(2, i) = "'" & arr(2, i)
- Next
- For i = 4 To r '各行
- n = 0
- x = Paixu(arr(i, 1)) '首数内排序
- arr(i, 1) = "'" & arr(i, 1)
- If Not d.exists(x) Then '如果列中没有
- For j = 3 To c: arr(i, j) = j - 1: Next '从1开始到末列顺序填
- Else '如果第2行某列数与首数相同(排序过后)
- xrr = Split(d(x), ",") '找出各列
- For j = 1 To UBound(xrr)
- xc = Val(xrr(j))
- arr(i, xc) = "P" '找出各列并加以标记
- Next
- For j = 3 To c '对于各列
- n = n + 1 '顺序+1
- If arr(i, j) = "P" Then n = 0 '如果已作标记,则令为0
- arr(i, j) = n
- Next
- End If
- Next
- [a1].Resize(r, c) = arr '显示结果
- End Sub
复制代码 |
|