¦p¦ó§PÂ_¬°¦P¤@²Õ(¶i¶¥°ÝÃD¡^
- ©«¤l
- 1447
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1471
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2025-3-24
|
¦^´_ 8# ã´£³¡ªL
ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½úªº¤è®×,¤è®×¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦b«ü¾É
°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub NumberCode()
Dim Arr, i&, xD, T$, TC$
'¡ô«Å§iÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxDÅܼƬO¦r¨å
Arr = Range([D2], [A65536].End(3))
'¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥HA~DÄæ²Ä2¦C¥H¤UÀx¦s®æȱa¤J°}¦C¤¤
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!
T = Arr(i, 1) & Arr(i, 2): TC = xD(T)
'¡ô¥OTÅܼƬO ²Ä1Äæ»P²Ä2Äæ°}¦CÈ©Ò²Õ¦¨ªº·s¦r¦ê,
'¥OTCÅܼƬO ¥HTÅܼƬdxD¦r¨å¦^¶ÇitemÈ
If TC = "" Then xD(T) = "|" & Arr(i, 4): GoTo 101
'¡ô¦pªGTCÅܼƬOªÅªº!´N¥OTÅܼƷíkey,item¬O"|"¦r¤¸³s±µ²Ä4Äæ°}¦CÈ,
'©Ò²Õ¦¨ªº·s¦r¦ê,µM«á´N¸õ¨ì101¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
If TC <> "|" & Arr(i, 4) Then xD(T) = "²§±`"
'¡ô¦pªGTCÅܼÆȤ£¦P©ó "|"³s±µ²Ä4ÄæArr°}¦CÈ?
'´N¥OTÅܼƦbxD¦r¨å¸ÌªºitemÈ´«¬° "²§±`"¦r¦ê
101: Next i
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!
T = Arr(i, 1) & Arr(i, 2): Arr(i, 1) = ""
'¡ô¥OTÅܼƬO ²Ä1Äæ»P²Ä2Äæ°}¦CÈ©Ò²Õ¦¨ªº·s¦r¦ê,
'²M°£±¼Arr°}¦C²Ä1Ä檺È
If xD(T) = "²§±`" Then Arr(i, 1) = "x"
'¡ô¦pªG¥HTÅܼƬdxD¦r¨å¦^¶ÇitemȬO "²§±`",
'´N¥O·í¦CArr°}¦C²Ä1Ä檺ȬO "x"
Next i
[G2].Resize(UBound(Arr)) = Arr
'¡ô¥OArr°}¦Cȱq[G2]Àx¦s®æ¶}©l¼g¤J,¶W¹L¦¹½d³òªº°}¦CÈ©¿²¤
End Sub |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|
- ©«¤l
- 1447
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1471
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2025-3-24
|
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»y¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
Option Explicit
Sub TEST()
Dim Brr, Y, i&, T$, T4$
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([D2], Cells(Rows.Count, 1).End(xlUp))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~DÄæ²Ä2¦C¥H¤UÀx¦s®æȱa¤J°}¦C¤¤
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
T = Brr(i, 1) & Brr(i, 2): T4 = Brr(i, 4) & "|"
'¡ô¥OTÅܼƬO ²Ä1Äæ»P²Ä2Äæ°}¦CÈ©Ò²Õ¦¨ªº·s¦r¦ê,
'¥OT4ÅܼƬO ²Ä4Äæ°}¦Cȳs±µ "|"²Å¸¹©Ò²Õ¦¨ªº·s¦r¦ê
If Y(T) <> T4 And Y(T) <> "" Then Y(T & "|") = "X" Else Y(T) = T4
'¡ô¦pªGTÅܼƬdY¦r¨å±oitemÈ»P T4ÅܼƤ£¦P,¦Ó¥B
'¥BTÅܼƬdY¦r¨å±oitemȤ£¬Oªì©lÈ!´N¥OTÅܼƳs±µ"|"²Õ¦¨ªº·s¦r¦ê·íkey,
'item¬O"X",¯Ç¤JY¦r¨å¤¤,
'§_«h´N¥OTÅܼƷíkey,item¬OT4ÅܼƯǤJY¦r¨å¤¤
Next
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
Brr(i, 1) = Y(Brr(i, 1) & Brr(i, 2) & "|")
'¡ô¥OBrr°}¦C²Ä1Äæ¼g¤J ²Ä1Äæ»P²Ä2Äæ°}¦Cȳs±µ"|"²Õ¦¨ªº·s¦r¦ê,
'¬dY¦r¨å¦^¶ÇitemÈ
Next
[G2].Resize(UBound(Brr)) = Brr
'¡ô¥O±q[G2]Àx¦s®æ¶}©l¼g¤JBrr°}¦CÈ,¶W¹L¦¹½d³òªº°}¦CÈ©¿²¤
Set Y = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|