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

[µo°Ý] (¤w¸Ñ¨M)±ø¥ó¤U¦Û°Ê½s¸¹

[µo°Ý] (¤w¸Ñ¨M)±ø¥ó¤U¦Û°Ê½s¸¹

¥»©«³Ì«á¥Ñ freeffly ©ó 2012-5-9 15:49 ½s¿è

ªþÀÉ
§Ú·Q­n§ä¥X¦P¤@¤Ñ½æµ¹¦P¨Ì«È¤á¤£¦P³æ»ùªÌ½s¸¹

§PÂ_±ø¥ó
A¡BC¡BD¡BFÄæ
A¸òCÄæ¦P¤@­Ó«È¤á¥B«~¸¹¬Û¦P
DÄæ-¸ò-¤§¶¡ªº§Ç¸¹¤@¼ËªÌ¡A¦]¬°³o¦U¬O¤é´Áªº§Ç¸¹
FÄæ¦pªG³æ»ù¤£¤@¼Ëªº¸Ü(­ì¥»§ÚÁÙ·Q¤£¨ì«ç»ò§PÂ_¡A¥Ø«e¥u·Q¨ì¥­§¡³æ»ù»P³æ»ù¤£¤@¼Ë´N¥i¥H§ä¥X¨Ó)

²Å¦X¥H¤W´N¦bGÄæ¦Û°ÊÅܸ¹


Book1.rar (3.76 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

¦^´_ 1# freeffly
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each a In Range([A2], [A65536].End(xlUp))
  4.    d(a & a.Offset(, 2)) = ""
  5.    a.Offset(, 6) = "A" & d.Count
  6. Next
  7. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

ª©¥D§Aªº¤èªk¦b¦pªG¤£²Å¦X±ø¥ó¤U¤]·|½s¸¹
§Ú·Q­n±N¤£²Å¦X±ø¥óªº³¡¥÷ªÅ¥Õ
¦pªþÀɶÀ¦â°Ï¶ô¦]¬°³æ»ù¬Û¦PÀ³¸Ó¤£²Å¦X±ø¥ó
¥t¥~¦pªG¸ê®Æ¥u¦³¤@µ§¦pºñ¦â°Ï¶ôÀ³¸Ó¤]­nªÅ¥Õ

¸ê®Æªº¥Î³~¬O·Q§ä¥X
­þ¤@­Ó²£«~­þ¤@¤Ñ½æµ¹¦P¤@­Ó«È¤á¦³¤£¦P³æ»ù±¡§Î

Book2.rar (7.01 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 3# freeffly
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each a In Range([A2], [A65536].End(xlUp))
  4.    m = a & a.Offset(, 2) & Split(a.Offset(, 3), "-")(1)
  5.    If IsEmpty(d(m)) Then
  6.       d(m) = Array(1, a.Offset(, 5))
  7.    Else
  8.       ar = d(m)
  9.       If ar(1) <> a.Offset(, 5) Then
  10.       ar(0) = ar(0) + 1
  11.       d(m) = ar
  12.       End If
  13.       Erase ar
  14.    End If
  15. Next
  16. For Each ky In d.keys
  17.   If d(ky)(0) = 1 Then d.Remove ky
  18. Next

  19. For Each a In Range([A2], [A65536].End(xlUp))
  20.    m = a & a.Offset(, 2) & Split(a.Offset(, 3), "-")(1)
  21.    i = Application.Match(m, d.keys, 0)
  22.    If IsNumeric(i) Then
  23.    a.Offset(, 6) = "A" & i
  24.    Else
  25.    a.Offset(, 6) = ""
  26.    End If
  27. Next

  28. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh


    ¬Ý¤£¼ôªRªº¼gªk´N¦n¹³¦b¬Ý¤ñ¸û²`ªº­^¤å
   ¤j·§¥i¥Hª¾¹D·N«ä
   ½Ð°Ýª©¥D¬O¤£¬O¥u¯à§PÂ_¨â¦U?
   
   ªþÀɧڬO¤F¨Ì¤U¦P¤@¤Ñ¦P¤@­Ó¦U¤á¦³3µ§¥æ©öªº±¡§Î¤Uªºª¬ªp
  ÁöµM¥Ø«e¤½¥q¥u¦³2µ§(A«~¡BB«~)¡A¦]¬°¤£ª¾¹D·|¤£·|¦³²Ä¤Tµ§(¦P®É½æABC«~)©Ò¥H·Qª¾¹D
  ¦pªG¬O¶W¹L¨âµ§ªº±¡§Î¥i¥H³B²z¶Ü



Book2.rar (8.4 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 5# freeffly
¦]¬°¬O¥HAÄæ§@«È¤á¸ê®Æ
©Ò¥H¥²¶·±NAÄæ«È¤á¥N¸¹¶ñº¡
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh


   ¬Ý¨ì¤F
  ¨Sª`·N¨ì«È¤á¥N¸¹¨º¤@Ä檺¸ê®Æ¦³ÅÜ
  ¦r¨å¸ò°}¦C¹ï§Ú¨Ó»¡ÁÙ¬Oºâ«Ü­¯¥Í
  ½Ð°Ýª©¥D(1)³o¦U¬O¥²­nªº¶Ü?
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 7# freeffly
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each a In Range([A2], [A65536].End(xlUp))
  4.    m = a & a.Offset(, 2) & Split(a.Offset(, 3), "-")(1) '¦ê±µA¡BCÄæ»PDÄ檺¤é´Á³¡¤À¦r¦ê
  5.    If IsEmpty(d(m)) Then '·í¦r¨å¥Hm¬°¯Á¤Þªº¤º®eªì©l¤Æ®É°õ¦æ
  6.       d(m) = Array(1, a.Offset(, 5)) '±NAÄæ»PFÄ欰°}¦C½áµ¹m¬°¯Á¤Þªº¤º®e
  7.       
  8.    Else '·í¥X²{²Ä¤G­Ó¥H¤Wªºm
  9.       ar = d(m) '¨ú¥X¦r¨å¤º®e
  10.       If ar(1) <> a.Offset(, 5) Then '·í°}¦C²Ä¤G­Ó¤¸¯À­È»PFÄ椣¦P
  11.       ar(0) = ar(0) + 1 '°}¦C²Ä¤@­Ó­È¥[¤@¡A·í¦¨¦P¯Á¤Þ¥B³æ»ù¤£¦Pªº­p¼Æ
  12.       d(m) = ar '±N°}¦C¦^¦s¨ì¦r¨å
  13.       End If
  14.       Erase ar '²M°£°}¦C
  15.    End If
  16. Next
  17. For Each ky In d.keys
  18.   If d(ky)(0) = 1 Then d.Remove ky '¦pªG¦r¨å¤º®e²Ä¤@­Ó¶µ¥Ø¬O1¡A¤]´N¬O³æ¤@­p¼Æ´N²¾°£¸Ó¯Á¤Þ¤º®e
  19. Next

  20. For Each a In Range([A2], [A65536].End(xlUp))
  21.    m = a & a.Offset(, 2) & Split(a.Offset(, 3), "-")(1)
  22.    i = Application.Match(m, d.keys, 0) '¨ú±o¸Ó¦r¦ê¬O¦r¨å²Ä´X­Ó¶µ¥Ø
  23.    If IsNumeric(i) Then
  24.    a.Offset(, 6) = "A" & i '¼g¤J½s¸¹
  25.    Else
  26.    a.Offset(, 6) = "" '¤£¦r¨å¤º´N²MªÅGÄæ
  27.    End If
  28. Next

  29. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 8# Hsieh


    ÁÂÁª©¥Dªá®É¶¡¸ÑÄÀ
   ´X¦¸¬Ý¨ìª©¥D¨Ï¥Î¦r¨å¤è¦¡³B²z
   ²`²`ı±o¦Û¤vÀ³¸Ó­nªá¦hÂI®É¶¡¾Ç²ß¦r¨å
   ³v¦æ°õ¦æ®É¥i¥H¬d¸ßµ²ªG
   ³o¦U³¡¥÷Åý§Ú¦³ÂIª¾¹Dµ{¦¡½X§@¥Î
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¤µ¤Ñ¤~¸Õ¥XUnion()ªºµ²ªG¤]¬O°}¦C,¾Ç²ß¤è®×¦p¤U,
½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, V, Y, N&, i&, T1$, T3$, T4$, T6$, TT1$, xR As Range
Set Y = CreateObject("Scripting.Dictionary")
Set xR = Range([G1], Cells(Rows.Count, "A").End(xlUp)): Brr = xR
For i = 2 To UBound(Brr)
   T1 = Brr(i, 1): T3 = Brr(i, 3): T4 = Brr(i, 4): T6 = Brr(i, 6)
   TT1 = T1 & "|" & T3 & "|" & Split(T4 & "-", "-")(1)
   If Y.Exists(TT1) = Empty Then
      Set Y(TT1) = Cells(i, 7): Y(TT1 & "³æ»ù") = Brr(i, 6)
      ElseIf Brr(i, 6) <> Y(TT1 & "³æ»ù") Then
         Set Y(TT1) = Union(Y(TT1), Cells(i, 7)): Y(TT1 & "³æ»ù") = "|"
   End If
Next
xR.Offset(1, 6).ClearContents
For Each V In Y.items
   If IsArray(V) Then N = N + 1: V.Value = "A" & N
Next
Set Y = Nothing: Set xR = Nothing: Erase Brr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤p¨Æ¤£°µ¡B¤j¨ÆÃø¦¨¡C
ªð¦^¦Cªí ¤W¤@¥DÃD