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

¦p¦ó±N横¦C¸ê®ÆÂରª½¦C¨Ã½s¸¹

¦p¦ó±N横¦C¸ê®ÆÂରª½¦C¨Ã½s¸¹


½Ð°Ý¦p¦ó±N¤W¤è横¦C±q¥ª¨ì¥kªº­q³æ+½s¸¹¡A¥H¤½¦¡Åܦ¨¥kÃ䪽¦C±q¤W¨ì¤Uªº¤è¦¡§e²{
1. ¦p¾î¦C­q³æªº½s¸¹¦³2µ§¥H¤W¡A«hª½¦C­q³æ¦Û°Ê¥[-1¡B-2¡K¡K
¨Ò¦p¡GÀx¦s®æA5­q³æIT97802521¡A¦@¦³B5¡BC5¨â­Ó½s¸¹¡A¬GÂà¨ìNÄæ­q³æ±Æ¦C®É¡A·|¦Û°Ê¥Í¦¨IT97802521¡BIT97802521-1¨â±i­q³æ¡AOÄæ«h±a¥X¸Ó­q³æ¨â­Ó½s¸¹¡A¥H¦¹Ãþ±À¡K

2. ³æ¤@­q³æªº½s¸¹¦pªG¦³­«½Æ¡A«h¤£­«½Æ§ì¨ú¡A¶È§ì¨ú°ß¤@­È§Y¥i¡C
¨Ò¦p¡GÀx¦s®æA7­q³æIT96801568¡AÁöµM¦³¤T­Ó½s¸¹¡A¦ýC7¸¹F7½s¸¹­«½Æ¡A¬G¥u»Ý§ì¨ú¨ä¤¤1µ§½s¸¹§Y¥i¡C¬GÂà¨ìNÄæ­q³æ±Æ¦C®É¡A­q³æIT96801568¥u·|¦³IT96801568¡BIT96801568-1¡A¤£·|¦³IT96801568-2¡C
Àx¦s®æA15­q³æIT2D800727¥H¦¹Ãþ±À

3. ªÅ¥ÕÀx¦s®æ¤ÎYµL»Ý§ì¨ú¡C

¯S§O´£¿ô¡A¤W¤è¾î¦C¸ê®Æ¬O¥Ñ¨t²Î§ì¨ú¡Aµo²{¦n¹³ªÅ®æ¨Ã«DªÅ¥Õ®æ¡AÀ³¸Ó¬O¦³µLªk¦C¦L¦r¤¸©ÎÁôÂær¤¸§Ú²q¡A¦]¬°¿ï¨úB2:J19®É¨Ï¥Î´M§ä>¯S®í¥Ø¼Ð>ªÅ®æ¡Aµo²{¨Ã«D¥þ¬°ªÅ®æ¡C
¦ý§Ú¤£ª¾¹DÂæ·|¤£·|¼vÅT¡K¡K¡K


´ú¸ÕÀÉ.rar (10.51 KB)
*¦v¤k¤@ªTµL»~*

C2»PB16½s¸¹¬Û¦P
¦P¦C½s¸¹¬Û¦P¥u¨ú1­Ó¡A¦ý¤£¦P¦C½s¸¹¬Û¦P­n¨ú¶Ü?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089

M¤j~~~¤£¦P¦C¤¬¤£¼vÅT¡A¥H¦P¦C¬°Àˬd¬°¥D¡AÁÂÁ§A~~~~~
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 3# msmplay
³oºØ°ÝÃD¤£«Øij¨Ï¥Î¨ç¼Æ¨Ó¸Ñ
À³¸Ó±Ä¥ÎVBA»²§U¸û¬°¬Ù¨Æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh

H¤j~~~~¨º½Ð°ÝVBA¦³¸Ñ£v¡H¦]¬°§Ú¨S¦³ÀYºü¨ä¹ê
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 5# msmplay

¨âºØ¤è¦¡
1.°õ¦æexµ{§Ç¼g¤JP¡AQÄæ
2.¼g¦¨¦Û©w¸q¨ç¼Æ¡A°}¦C¤½¦¡¦bR¡ASÄæ
   Âà¸mªí®æ.zip (20.82 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh


   H¤j~~~~§A¹ê¦b¬O¤Ó¼F®`¤F!!!ÁÙ¯uªº§¹¥þ¸Ñ¨M¤F§Úªº¤@¤j°ÝÃD­C!!!!¶W·PÁ§Aªº~~~~~~~~~~~~
*¦v¤k¤@ªTµL»~*

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2018-3-9 13:06 ½s¿è

K2:K26{=INDIRECT(TEXT(SMALL(IF((COUNTIF(OFFSET(B$1,ROW($1:$18),,,COLUMN(A:I)),B$2:J$19)=1)*(LEN(B$2:J$19)>1),ROW(B$2:J$19)/1%+1),ROW(A1)),"!R0C00"),)&IF(T(INDIRECT(TEXT(SMALL(IF((COUNTIF(OFFSET(B$1,ROW($1:$18),,,COLUMN(A:I)),B$2:J$19)=1)*(LEN(B$2:J$19)>1),ROW(B$2:J$19)/1%+1),ROW(A1)),"!R0C00"),))=LEFT(K1,10),-COUNTIF(K$1:K1,LEFT(K1,10)&"*"),"")


¤G¬q¦³¦â¦rÅé¬Û¦P

L2:L26{=IFERROR(INDIRECT(TEXT(SMALL(IF((COUNTIF(OFFSET(B$1,ROW($1:$18),,,COLUMN(A:I)),B$2:J$19)=1)*(LEN(B$2:J$19)>1),ROW(B$2:J$19)/1%+COLUMN(B:J)),ROW(A6)),"!R0C00"),),"") 55-4156.rar (14.56 KB)
1

µû¤À¤H¼Æ

    • ML089: ¤Ó§Q®`¤Fª÷¿ú + 5
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 8# hcm19522


   h¤j~~~~§A¤]¦n¼F®`³á!!!
*¦v¤k¤@ªTµL»~*

TOP

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


°õ¦æµ²ªG:



Sub TEST()
Dim ¸ê®Æ°}¦C, ªÅ°}¦C, ¦r¨å, i&, j%, ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ&, ¦P¦C¤£­«½Æ½s¸¹¼Æ%
Set ¦r¨å = CreateObject("Scripting.Dictionary")
¸ê®Æ°}¦C = Range([J2], [A65536].End(xlUp))
ReDim ªÅ°}¦C(1 To 10000, 1 To 2)
For i = 1 To UBound(¸ê®Æ°}¦C)
   ­q³æ = Trim(¸ê®Æ°}¦C(i, 1))
   ¦P¦C¤£­«½Æ½s¸¹¼Æ = 0
   For j = 2 To UBound(¸ê®Æ°}¦C, 2)
      If Trim(¸ê®Æ°}¦C(i, j)) = "Y" Or Trim(¸ê®Æ°}¦C(i, j)) = "" Then GoTo j01
      If ¦r¨å(Trim(¸ê®Æ°}¦C(i, j)) & i) <> "" Then
         GoTo j01
         Else
         ¦r¨å(Trim(¸ê®Æ°}¦C(i, j)) & i) = "X"
      End If
      ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ = ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ + 1
      ¦P¦C¤£­«½Æ½s¸¹¼Æ = ¦P¦C¤£­«½Æ½s¸¹¼Æ + 1
      If ¦P¦C¤£­«½Æ½s¸¹¼Æ = 1 Then
         ªÅ°}¦C(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 1) = ­q³æ
         Else
         ªÅ°}¦C(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 1) = ­q³æ & "-" & ¦P¦C¤£­«½Æ½s¸¹¼Æ - 1
      End If
      ªÅ°}¦C(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 2) = Trim(¸ê®Æ°}¦C(i, j))
j01: Next
Next
[P2].Resize(ªÅ°}¦C¤w¨Ï¥Î¦C¼Æ, 2) = ªÅ°}¦C
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD