- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¦^´_ 25# duck_simon
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
¦A¦¸ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾Ç½Æ²ß¬Q¤Ñªº²ßÃD¤ß±oµù¸Ñ¦p¤U,½Ð°Ñ¦Ò
¸Õ«ö¤F´X¦Ê¦¸³Ì¦h4Ó¤ñ¹ï¤W,¨S°¾°]¹B
Option Explicit
Sub TEST_2()
Call ¶Ã¼Æ«¸m
'¡ô°õ¦æ(¶Ã¼Æ«¸m)°Æµ{¦¡
Dim Brr, B, V, Y, xR As Range, i&, j&
'¡ô«Å§iÅܼÆ:(Brr,B,V,Y)¬O³q¥Î«¬ÅܼÆ,xR¬OÀx¦s®æÅܼÆ,(i,j)¬Oªø¾ã¼ÆÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Brr = [A1:J12]: [A1:J12].Interior.ColorIndex = xlNone: [K20] = ""
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C!¥H[A1:J12]Àx¦s®æÈˤJ,
'¥O[A1:J12]Àx¦s®æ©³¦â¬O µL¦â :[K20]Àx¦s®æȬOªÅ¦r¤¸
For Each xR In [B20:F20]
'¡ô³]³v¶µ°j°é!¥OxR³oÀx¦s®æÅܼƬO [B20:F20]Àx¦s®æªº¤@®æ
Y(xR & "/") = xR.Interior.ColorIndex: V = V & "/" & xR
'¡ô¥OxRÅܼƳs±µ "/"ªº·s¦r¦ê¬°Key,Item¬O xRÅܼƪº©³¦â ¯Ç¤JY¦r¨å,
'¥OV³o³q¥Î«¬ÅܼƬO ¦Û¨³s±µ "/"¦A³s±µ xRÅܼƪº·s¦r¦ê
Next
For i = 4 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q4 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
i01:
For j = 2 To UBound(Brr, 2)
'¡ô³]¶¶°j°é!j±q2 ¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹
If B = 1 Then
'¡ô¦pªGB³o³q¥Î«¬ÅܼƬO 1??
Cells(i, j).Interior.ColorIndex = Y(Cells(i, j) & "/")
'¡ô¥Oi°j°é¦Cj°j°éÄ檺Àx¦s®æ©³¦â¬O:
'¥Hi°j°é¦Cj°j°éÄ檺Àx¦s®æȳs±µ"/"ªº·s¦r¦ê¬°Key¬dY¦r¨åªº¦^¶ÇÈ
ElseIf InStr(V & "/", "/" & Brr(i, j) & "/") Then
'¡ô§_«h¦pªGVÅܼƳs±µ "/"ªº·s¦r¦ê¸Ì¥]§t¤F,
'¥]§t¤F(i°j°é¦Cj°j°éÄæBrr°}¦CȦb«e«á¦U³s±µ"/"ªº·s¦r¦ê)
If Y("|") < 2 Then
'¡ô¦pªG¥H "|"¬dY¦r¨å¦^¶ÇItemÈ < 2 ?
Y("|") = Y("|") + 1
'¡ô¥OY¦r¨å¸Ì"|"¬°keyªºitemȲ֥[ 1
Else
B = 1: [K20] = "X": GoTo i01
'¡ô¥OBÅܼƬO 1:¥O[K20]Àx¦s®æȬO "X",
'³Ì«á¸õ¨ì i01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
End If
End If
Next
Y("|") = 0: B = 0
'¡ô¥OY¦r¨å¸Ì"|"¬°keyªºitemȬO 0:¥OBÅܼƬO 0
Next
Set Y = Nothing: Set Brr = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
========================================
Sub ¶Ã¼Æ«¸m()
With [B4:J12]
'¡ô¥H¤U¬OÃö©ó[B4:J12]Àx¦s®æªºµ{§Ç
.Value = "=INT(MOD(RAND()*1000,49))+1"
'¡ô¥O®æȬO¤½¦¡:
'0¨ì1¤§¶¡ªº¶Ã¼Æ1000¿°£¥H49ªº¾l¼Æ¥h°£¤p¼Æ«á +1
.Value = .Value
'¡ô¥O®æ¸Ìªº¤½¦¡Âà¤Æ¬°È
End With
End Sub
========================================
Option Explicit
Sub TEST_2_¦r¨åÂo«½Æ()
Call ¶Ã¼Æ«¸m
'¡ô¥O°õ¦æ(¶Ã¼Æ«¸m)°Æµ{¦¡
Dim Brr, B, V, Y, xR As Range, i&, j&
'¡ô«Å§iÅܼÆ:(Brr,B,V,Y)¬O³q¥Î«¬ÅܼÆ,xR¬OÀx¦s¦s®æÅܼÆ,(i,j)¬Oªø¾ã¼ÆÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Brr = [A1:J12]: [A1:J12].Interior.ColorIndex = xlNone: [K20] = ""
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C!¥H[A1:J12]Àx¦s®æȱa¤J,
'¥O[A1:J12]Àx¦s®æ©³¦â¬OµL¦â,¥O[K20]Àx¦s®æȬO ªÅ¦r¤¸
For Each xR In [B20:F20]
'¡ô¥O³]³v¶µ°j°é!¥OxR³oÀx¦s®æÅܼƬO [B20:F20]Àx¦s®æ¸Ìªº¤@®æ
Y(xR & "/") = xR.Interior.ColorIndex: V = V & "/" & xR
'¡ô¥OxRÅܼƳs±µ"/"ªº·s¦r¦ê·íkey,item¬OxRÅܼƪº©³¦â¯Ç¤JY¦r¨å,
'¥OV³o³q¥Î«¬ÅܼƬO ¦Û¨³s±µ"/"¦A³s±µxRÅܼƪº·s¦r¦ê
Next
For i = 4 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q4 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
i01:
For j = 2 To UBound(Brr, 2)
'¡ô³]¶¶°j°é!j±q2 ¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
If B = 1 Then
'¡ô¦pªGB³o³q¥Î«¬ÅܼƬO 1?
Cells(i, j).Interior.ColorIndex = Y(Cells(i, j) & "/")
'¡ô¥Oi°j°é¦Cj°j°éÄ檺Àx¦s®æ©³¦â¬O:
'¥Hi°j°é¦Cj°j°éÄ檺Àx¦s®æȳs±µ"/"ªº·s¦r¦ê¬°Key¬dY¦r¨åªº¦^¶ÇÈ
ElseIf InStr(V & "/", "/" & Brr(i, j) & "/") And Y(Brr(i, j) & "/" & i) = "" Then
'¡ô§_«h¦pªGVÅܼƳs±µ "/"ªº·s¦r¦ê¸Ì¥]§t¤F,
'¥]§t¤F(i°j°é¦Cj°j°éÄæBrr°}¦CȦb«e«á¦U³s±µ"/"ªº·s¦r¦ê)
'¦Ó¥B i°j°é¦Cj°j°éÄæBrr°}¦Cȳs±µ"/"¦A³s±µiÅܼƪº·s¦r¦ê¬dY¦r¨å¦^¶ÇȬOªÅ¦r¤¸?
If Y("|") < 2 Then
'¡ô¦pªG¥H "|"¬dY¦r¨å¦^¶ÇItemÈ < 2 ?
Y("|") = Y("|") + 1
'¡ô¥OY¦r¨å¸Ì"|"¬°keyªºitemȲ֥[ 1
Else
B = 1: [K20] = "X": GoTo i01
'¡ô¥OBÅܼƬO 1:¥O[K20]Àx¦s®æȬO "X",
'³Ì«á¸õ¨ì i01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
End If
Y(Brr(i, j) & "/" & i) = 1
'¡ô¥Oi°j°é¦Cj°j°éÄæBrr°}¦Cȳs±µ"/"¦A³s±µiÅܼƪº·s¦r¦ê,
'³o·s¦r¦ê¬°Y¦r¨åkeyªºitemȬO 1
End If
Next
Y("|") = 0: B = 0
'¡ô¥OY¦r¨å¸Ì"|"¬°keyªºitemȬO 0:¥OBÅܼƬO 0
Next
Set Y = Nothing: Set Brr = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
========================================
Option Explicit
Sub TEST_2_InstrÂo«½Æ()
Call ¶Ã¼Æ«¸m
'¡ô¥O°õ¦æ(¶Ã¼Æ«¸m)°Æµ{¦¡
Dim Brr, B, V, xR As Range, i&, j&, A(49)
'¡ô«Å§iÅܼÆ:(Brr,B,V,)¬O³q¥Î«¬ÅܼÆ,xR¬OÀx¦s¦s®æÅܼÆ,(i,j)¬Oªø¾ã¼ÆÅܼÆ,A¬O¤@ºû°}¦C(0~49)
Brr = [A1:J12]: [A1:J12].Interior.ColorIndex = xlNone: [K20] = ""
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C!¥H[A1:J12]Àx¦s®æȱa¤J,
'¥O[A1:J12]Àx¦s®æ©³¦â¬OµL¦â,¥O[K20]Àx¦s®æȬO ªÅ¦r¤¸
For Each xR In [B20:F20]
'¡ô¥O³]³v¶µ°j°é!¥OxR³oÀx¦s®æÅܼƬO [B20:F20]Àx¦s®æ¸Ìªº¤@®æ
A(Val(xR)) = xR.Interior.ColorIndex: V = V & "/" & xR
'¡ô¥OxRÅܼÆÂà¤Æ¬°¼Æ¦r¬°¯Á¤Þ¸¹ªºA°}¦CȬOxRÅܼƪº©³¦â¸¹
'¥OV³o³q¥Î«¬ÅܼƬO ¦Û¨³s±µ"/"¦A³s±µxRÅܼƪº·s¦r¦ê
Next
For i = 4 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q4 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
i01: A(0) = "||"
For j = 2 To UBound(Brr, 2)
'¡ô³]¶¶°j°é!j±q2 ¨ìBrr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
If B = 1 Then
'¡ô¦pªGB³o³q¥Î«¬ÅܼƬO 1?
Cells(i, j).Interior.ColorIndex = A(Cells(i, j))
'¡ô¥Oi°j°é¦Cj°j°éÄ檺Àx¦s®æ©³¦â¬O:
'¥Hi°j°é¦Cj°j°éÄ檺Àx¦s®æȬ°¯Á¤Þ¸¹ªº A°}¦CÈ
ElseIf InStr(V & "/", "/" & Brr(i, j) & "/") And InStr(A(0), "/" & Brr(i, j) & "/") = 0 Then
'¡ô§_«h¦pªGVÅܼƳs±µ "/"ªº·s¦r¦ê¸Ì¥]§t¤F,
'¥]§t¤F(i°j°é¦Cj°j°éÄæBrr°}¦CȦb«e«á¦U³s±µ"/"ªº·s¦r¦ê)
'¦Ó¥B i°j°é¦Cj°j°éÄæBrr°}¦CÈ«e«á¦U³s±µ"/"ªº·s¦r¦ê¦b 0¯Á¤Þ¸¹A°}¦C¸Ì¦³³Q¥]§t?
If Val(A(0)) < 2 Then
'¡ô¦pªG0¯Á¤Þ¸¹A°}¦CÈÂà¤Æ¬°¼ÆÈ«á < 2?
A(0) = Val(A(0)) + 1 & "|" & Mid(A(0), 3)
'¡ô¥O0¯Á¤Þ¸¹A°}¦CȬO ¦Û¨Âà¤Æ¬°¼ÆÈ+1 ³s±µ"|" ¦A³s±µ¦Û¨±q²Ä3¦r¶}©l¤§«á¥þ³¡¦r¦ê,
'²Õ¦X¦¨ªº·s¦r¦ê
Else
B = 1: [K20] = "X": GoTo i01
'¡ô¥OBÅܼƬO 1:¥O[K20]Àx¦s®æȬO "X",
'³Ì«á¸õ¨ì i01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
End If
A(0) = A(0) & "/" & Brr(i, j) & "/"
'¡ô¥O0¯Á¤Þ¸¹A°}¦CȬO ¦Û¨³s±µ"/" ¦A³s±µi°j°é¦Cj°j°éÄæBrr°}¦CÈ,
'³Ì«á³s±µ"/"ªº·s¦r¦ê
End If
Next
B = 0
'¡ô¥O¥OBÅܼƬO 0
Next
Set Brr = Nothing: Erase A
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|