|
楼主 |
发表于 2024-6-11 15:44
|
显示全部楼层
Public p, LastSJH, LastSJL, LastTJH, jgh, t, sc As Integer
Public sj, tj As Variant
Sub dd()
On Error GoTo Err
Application.StatusBar = "正在处理,请稍后……"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableCancelKey = xlDisabled
Sheets("结果").Cells.Clear
LastSJH = Sheets("数据").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
LastSJL = Sheets("数据").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column - 2
LastTJH = Sheets("条件").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
sc = Sheets("条件").Cells(2, 5).Value
Set sj = Range(Worksheets("数据").Cells(2, 3), Worksheets("数据").Cells(LastSJH + 1, LastSJL + 2))
tj = Range(Worksheets("条件").Cells(2, 1), Worksheets("条件").Cells(LastTJH + 1, 4))
Sheets("条件").Select
For z = 1 To LastTJH
For Each c In sj
If c.Value = tj(z, 1) Then js = js + 1
Next
Cells(z + 1, 2).Value = js
js = 0
Next
Cells(2, 2).Select
Range(Cells(1, 1), Cells(LastTJH + 1, 4)).Sort Key1:=Cells(2, 2), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
t = Timer()
Do
Application.ScreenUpdating = False
Sheets("数据").Select
Cells(2, 1).Select
Range(Cells(1, 1), Cells(LastSJH + 1, LastSJL + 2)).Sort Key1:=Cells(2, 1), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
Application.ScreenUpdating = True
For xh3 = 1 To LastSJH
For xh4 = 1 To LastSJL
sj = Range(Worksheets("数据").Cells(2, 3), Worksheets("数据").Cells(LastSJH + 1, LastSJL + 2))
tj = Range(Worksheets("条件").Cells(2, 1), Worksheets("条件").Cells(LastTJH + 1, 4))
For h = 1 To LastSJH
For l = 1 To LastSJL
For z = 1 To LastTJH
If sj(h, l) = tj(z, 1) Then
hc = sj(h, l)
sj(h, l) = sj(h, tj(z, 4))
sj(h, tj(z, 4)) = hc
Exit For
End If
Next
Next
Next
For h = 1 To LastSJH
If xh3 + h - 1 <= LastSJH Then jgh = xh3 + h - 1 Else jgh = xh3 + h - 1 - LastSJH
For l = xh4 To LastSJL
js = 0
For z = 1 To LastTJH
If sj(jgh, l) = tj(z, 1) Then
For xh = 1 To LastSJH
If sj(xh, l) = tj(z, 1) Then js = js + 1
Next
tjgs = tj(z, 3)
If js > tjgs Then
For l1 = 1 To LastSJL
If Rnd() > 0.8 Then DoEvents
If sj(jgh, l) <> sj(jgh, l1) Then
js1 = 0
js2 = 0
For z1 = 1 To LastTJH
If sj(jgh, l1) = tj(z1, 1) Then
tjgs1 = tj(z1, 3)
Exit For
End If
Next
For xh = 1 To LastSJH
If sj(xh, l) = sj(jgh, l1) Then js1 = js1 + 1
Next
For xh = 1 To LastSJH
If sj(xh, l1) = sj(jgh, l) Then js2 = js2 + 1
Next
If js1 < tjgs1 And js2 < tjgs Then
hc = sj(jgh, l1)
sj(jgh, l1) = sj(jgh, l)
sj(jgh, l) = hc
Call pd
If p = 0 Then GoTo z:
Exit For
End If
End If
Next
End If
Exit For
End If
Next
Next
Next
Next
Next
Loop Until Timer() - t > sc * 60
z:
Application.ScreenUpdating = False
If p = 0 Then MsgBox ("True") Else MsgBox ("False")
Range(Worksheets("数据").Cells(1, 2), Worksheets("数据").Cells(LastSJH + 1, LastSJL + 2)).Copy Sheets("结果").Cells(1, 1).Resize(LastSJH + 1, LastSJL + 2)
Sheets("结果").Cells(2, 2).Resize(LastSJH, LastSJL) = sj
Sheets("数据").Select
Cells(2, 2).Select
Range(Cells(1, 1), Cells(LastSJH + 1, LastSJL + 2)).Sort Key1:=Cells(2, 2), Order1:=1, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
Sheets("结果").Select
Cells(2, 1).Select
Range(Cells(1, 1), Cells(LastSJH + 1, LastSJL + 2)).Sort Key1:=Cells(2, 1), Order1:=1, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
Err:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableCancelKey = xlErrorHandler
Application.StatusBar = False
End Sub
Function pd()
p = 0
For z = 1 To LastTJH
For l = 1 To LastSJL
js = 0
For h = 1 To LastSJH
If sj(h, l) = tj(z, 1) Then js = js + 1
If js > tj(z, 3) Then
p = 1
Exit For
End If
Next
Next
Next
End Function
这是当时论坛里一位代写的,自费200元的代码,结果是一次性的,只对当时的数据有用,但变换数据就用不了,大神你觉得值多少钱? |
|