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

[µo°Ý] (¤w¸Ñ¨M)¦p¦óÂ^¨ú¤£¦PÃþ§O¥N¸¹ªº³Ì«á¤@µ§¸ê®Æ

[µo°Ý] (¤w¸Ñ¨M)¦p¦óÂ^¨ú¤£¦PÃþ§O¥N¸¹ªº³Ì«á¤@µ§¸ê®Æ

¥»©«³Ì«á¥Ñ freeffly ©ó 2012-2-22 17:21 ½s¿è

³Ì·s¥N¸¹Â^¨ú.rar (14.47 KB)

§Ú·Q­nÂ^¨ú¤£¦PÃþ§O¥N¸¹ªº³Ì«á¤@µ§¸ê®Æ¡C
¥Ø«e¨Ï¥Îªº¤è¦¡¦pªþ¥[Àɤº©Ò¥Ü¡A¦³¨S¦³¤èªk¥i¥H¤£¥Î¸g¹L¨BÆJ¤@¤Î¨BÆJ¤Gª½±µ±o¨ìµ²ªG¸ê®Æ?
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

I2=OFFSET($A$1,MAX(IF(LEFT($A$2:$A$975,2)=F2,ROW($1:$974),0)),)
°}¦C¤½¦¡

TOP

¦^´_ 2# ANGELA

³o¼ËÁÙ¬O­n¥ý¨ú¥X¥N¸¹²M³æ
¦pªG­n¤@Äæ·d©w¡A§Æ±æEXCEL¯à­t²ü±o¤F
I2°}¦C¤½¦¡
=IF(ROW(A1)>SUM(1/COUNTIF($A$2:$A$975,"="&LEFT($A$2:$A$975,2)&"*")),"",INDIRECT("A"&SMALL(IF(LEFT($A$2:$A$975,2)<>LEFT($A$3:$A$976,2),ROW($A$2:$A$975),""),ROW(A1))))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# ANGELA


    ÁöµM¤@¼Ë­n´£¨ú¦C¸¹²M³æ¤]¬Ý¨ì¤£¤@¼Ëªº¤èªk,ÁÂÁª©¥D¦^´_
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 3# Hsieh


    ³o­Ó¤èªkªº½T¬O¥i¥H¤@Äæ·d©w
   ¹ïexcelªº­t²ü¤]®¼¤j
   ÁÂÁ¤j¤j¦^´_¡A¤]¬Ý¨ì¤£¦Pªº«ä¦Ò¤è¦¡
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 3# Hsieh


    ²Ê¤ß¤j·N,¤@ª½¬O§Úªº­P©R¶Ë,±q¤p¨ì¤j³£§ï¤£¤F,ÁÂÁ«ü¾É,¦¹ÃDÀ³§ï¬°
    =INDEX(A:A,SMALL(IF(RIGHT($A$3:$A$976,3)<=RIGHT($A$2:$A$975,3),ROW($A$2:$A$975),1000),ROW(A1)))&""
   ¤@¯ë§Ú¤ñ¸ûºD¥ÎINDEX

TOP

ǢǢ!
§Ú¦³¬Ý¨ì,¤£¬O°½¯º,¬O¦]¬°§Ú¤]¬O¤@¼Ë

¹ïexcel¤Ó¦³§â´¤,±`§â¤½¦¡¤@©Ô´N¥æ¤F
°ÝÃD¬O¤¤¶¡ªº¼Æ¦r¦³¥i¯à¬O¤å¦r«¬µLªk¥[Á`
¦³¥i¯à¬O¦ì¸mÂI¿ù,¦pÀ³ÂIa1/a20,¥i¬O¤â»~¦¨a2/a2,Åý¼Æ¦r²§±`
Åý¥DºÞµLªk«H¿à,
°£¤F³t«×¥~ÁÙ­n¥¿½T¤~¬O¥¿¹D
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

ǢǢ,
³Q¦Ñ®vkÀY¤F,­ì¨Ó¥u­n¬O¤H³£Ãø§K¥Ç¿ù.

TOP

¦^´_ 6# ANGELA


    ³o¦U¤è¦¡ªº½T¬O¥i¥H¤@Äæ·d©w
   ¦n¹³¤]¨S³y¦¨excel«Ü¤j­t²ü
   ¤£¹LÁÙ­nªáÂI®É¶¡®ø¤Æ¤@¤U¤½¦¡
   ÁÂÁ³á!
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

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

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, V, Y, R&, i&, T$, M%, K%
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([A1], Cells(Rows.Count, "A").End(xlUp))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HAÄæÀx¦s®æ­È±a¤J°}¦C¤¤
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
   T = Left(Brr(i, 1), 2)
   '¡ô¥OTÅܼƬO¥ªÃä2¦rªº¦r¦ê
   K = Right(Brr(i, 1), 3)
   '¡ô¥OKÅܼƬO¥kÃä3¦rÂର¼Æ­È(¦]¬°K«Å§i¬O¼Æ­È)
   M = Y(T)
   '¡ô¥OMÅܼƬOTÅܼƬdY¦r¨å±o¨ìitem,
   '¤@¶}©lY¦r¨å¸Ì¨S¦³¦¹key®É,item¬Oªì©l­ÈÂà¾ã¼Æ=0,
   '¦]¬°M«Å§i¬O¾ã¼Æ­È

   If M < K Then M = K
   '¡ô³o¬O­n¤ñ¸û¥X³Ì¤j­È
   Y(T) = M
   '¡ô¥O¥HTÅܼƷíkey,item¬O·í¤Uªº³Ì¤j­È©ñ¦^¦r¨å
Next
For Each V In Y.KEYS
'¡ô³]³v¶µ°j°é!¥OVÅܼƬO Y¦r¨åªºkey
   R = R + 1
   '¡ô¥ORÅܼƲ֥[ 1
   Brr(R, 1) = V & Format(Y(V), "000")
   '¡ô¥O¥H²Õ¦X¦r¦ê(µ²ªG¸ê®Æ)Âл\±¼­ìBrr°}¦C­È,
   '²¦³ºBrrªº­ì¸ê®Æ¤w¸g¥Î¤£¨ì¤F,
   '¦p¦¹´N¤£¥²¦A³]¤@­Ó°}¦C¸Ëµ²ªG¸ê®Æ
   '¦ý¬O»Ý­n·Ç½Tªºª¾¹Dµ²ªG¸ê®Æ¨ì°}¦Cªº­þ¤@¦C,¦pRÅܼƩÎY.Count

Next
[J2].Resize(Y.Count, 1) = Brr
'¡ô¥OBrr§½³¡¸ê®Æ(µ²ªG¸ê®Æ)¼g¤JÀx¦s®æ¤¤,¶W¹L¦¹½d³òªºBrr°}¦C­È©¿²¤
Set Y = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD