ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¼Ð¥Ü­«½Æ­È

¼Ð¥Ü­«½Æ­È

¦p¦ó¦bCÄæ¤ÎDÄæ³]©w¤½¦¡¡A¨âºØ¤£¦P­«½Æ­ÈÅã¥Ü

­«½Æ­È(¨âºØ¼Ð¥Ü).zip (6.54 KB)

joyce

¤£¦n·N«ä¡A¸É¥R»¡©ú¡G
¢ÐÄ椺¦P¤@¸¹½X¥BAÄ椺¤£¦P¥N½X¡A«h¢Ñ¢±¡×¢°¡A¢Ñ¢²¡×¢±¡A¢Ñ¢²¡×¢²¡F¢Ò¢±©M¢Ò¢²©M¢Ò¢³¡×¢²
¢ÐÄ椺¦P¤@¸¹½X¥BAÄæ¦P¥N½X¡A«h¢Ñ¢¶©M¢Ñ¢·¡×¢°¡F¢Ò¢¶©M¢Ò¢·¡×¢°
joyce

TOP

C2:
=COUNTIF(B$1:B2,B2)

D2:
=COUNTIF(B:B,B2)

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-6 14:09 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
'=COUNTIF(B$1:B2,B2):¦^¶Ç ¤W¤èªºÀx¦s®æ½d³ò¤¤¦P·í¦CÀx¦s®æ­Èªº­Ó¼Æ
'=COUNTIF(B:B,B2):¦^¶Ç ©T©wªºÀx¦s®æ½d³ò¤¤¦P·í¦CÀx¦s®æ­Èªº­Ó¼Æ


Sub TEST() '2­Ó°}¦C¤è¦¡
Dim Brr, Crr, Y, i&, T$, R&, N&
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO¦r¨å
Brr = Range([B2], Cells(Rows.Count, "B").End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HBÄæÀx¦s®æ­È±a¤J
R = UBound(Brr): ReDim Crr(1 To R, 1 To 2)
'¡ô¥ORÅܼƬO Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹,
'¥OCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr°}¦C,¾î¦V1~2

For i = 1 To R: T = Brr(i, 1): N = Y(T) + 1: Crr(i, 1) = N: Y(T) = N: Next
'¡ô³]¶¶°j°é!¥OTÅܼƬOBrr°}¦C­È,¥ONÅܼƬO¥HTÅܼƬdY¦r¨åitem­È²Ö¥[1,
'¥OCrr°}¦C²Ä1Äæ¼g¤JNÅܼƭÈ,¥O¦bY¦r¨å¸ÌTÅܼÆkey¨äitem­È¬ONÅܼƭÈ

For i = 1 To R: Crr(i, 2) = Y(Brr(i, 1)): Next
'¡ô³]¶¶°j°é!¥OCrr°}¦C²Ä2Äæ¼g¤J(¥HBrr°}¦C­È¬dY¦r¨å±o¨ìªºitem­È)
[C2].Resize(R, 2) = Crr
'¡ô¥O±q[C2]¶}©lªº½d³òÀx¦s®æ¼g¤JCrr°}¦C­È
End Sub

Sub TEST_1() '1­Ó°}¦C¤è¦¡
Dim Brr, Y, i&, T$, R&, N&
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO¦r¨å
Brr = Range([B2], Cells(Rows.Count, "A").End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~BÄæÀx¦s®æ­È±a¤J
R = UBound(Brr)
'¡ô¥ORÅܼƬO Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
For i = 1 To R: T = Brr(i, 2): N = Y(T) + 1: Brr(i, 1) = N: Y(T) = N: Next
'¡ô³]¶¶°j°é!¥OTÅܼƬO²Ä2ÄæBrr°}¦C­È,¥ONÅܼƬO¥HTÅܼƬdY¦r¨åitem­È²Ö¥[1,
'¥OBrr°}¦C²Ä1Äæ¼g¤JNÅܼƭÈ(¨ú¥N±¼­ì°}¦C­È),
'¥O¦bY¦r¨å¸ÌTÅܼÆkey¨äitem­È¬ONÅܼƭÈ

For i = 1 To R: Brr(i, 2) = Y(Brr(i, 2)): Next
'¡ô³]¶¶°j°é!¥OBrr°}¦C²Ä2Äæ¼g¤J(¥H²Ä2ÄæBrr°}¦C­È¬dY¦r¨å±o¨ìªºitem­È)
[C2].Resize(UBound(Brr), 2) = Brr
'¡ô¥O±q[C2]¶}©lªº½d³òÀx¦s®æ¼g¤JBrr°}¦C­È
End Sub

A~BÄæ¥HAÄ欰°ò·Ç±Æ§Ç«á­«·s°õ¦æµ²ªG:
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : «Î¼e¤£¦p¤ß¼e¡C
ªð¦^¦Cªí ¤W¤@¥DÃD