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

[µo°Ý] (¦r¨åª«¥ó)¬d¸ß¨Ã¹ï»ô¸ê®Æ¦C°ÝÃD

[µo°Ý] (¦r¨åª«¥ó)¬d¸ß¨Ã¹ï»ô¸ê®Æ¦C°ÝÃD

¦U¦ì«e½ú¦n,
¤p§Ì¥Ø«e½m²ß¦r¨åª«¥ó®É¹J¨ì¤@­Ó°ÝÃD,
ªþ¤WÀÉ®×¥H¨Ñ«e½ú°Ñ¦Ò:
20161111-¬d¸ß¨Ã¹ï»ô¾î¦C°ÝÃD.zip (7.94 KB)

°ÝÃD¥H¤å¦r²³æ»¡©ú¦p¤U:

AÄæ»PBÄ欰¸ê®ÆÁ`ªí,¨Ì§Ç±Æ¦C,
AÄæÀx¦s®æ¸ê®Æ»PBÄæÀx¦s®æ¸ê®Æ¨ã¦³¬Û¹ïÃö«Y.


CÄæ»PDÄ檺¸ê®Æ¬O¨ú¦ÛAÄæBÄæ,¦ý¬O¶¶§Ç¸g¹L¥´¶Ã.
EÄæ»PFÄæ¥ç¦P.

²{¦b¬O§Æ±æ¥i¥H±N¸ê®Æ±Æ§Ç«á,±N¸ê®Æªº¾î¦C¦ì¸m¹ï»ôAÄæ»PBÄ檺¦ì¸m.

¥Î¤å¦r»¡©ú¥i¯à¤£©ö²z¸Ñ,
ÁٽЫe½ú°Ñ¦ÒªþÀɪº"´Á±æµ²ªG¥Ü·N"­¶ÅÒ.


³o­Ó½m²ß°ÝÃD¬O§ï½s¦Û¥H¤Uºô§}ªº¹ê¨Ò7:
http://club.excelhome.net/thread-868892-1-1.htmlz

¸Ó½×¾Â¦³ªO¤Íªþ¤W°Ñ¦Ò¸Ñµª¦p¤U,
§Ú¥Î¹q¸£ª©¬Ý¤£¨ìºô¯¸ªº¹Ï¤ù,
±À´ú¥i¯à¬O±q²Ä¤T¾î¦C¶}©l,»P¤p§Ì¦¹½gªºªþÀɦ³¨Ç®t²§,¶È¨Ñ°Ñ¦Ò:
  1. Private Sub CommandButton1_Click()  ¡¥by:oobird
  2. Dim d As Object, rng, i%, j%, arr
  3. Set d = CreateObject("Scripting.Dictionary")
  4. rng = Range("a3:f" & [a65536].End(xlUp).Row)
  5. ReDim arr(1 To UBound(rng), 1 To 4)
  6. For i = 1 To UBound(rng)   
  7. d(CStr(rng(i, 1))) = i
  8. Next i
  9. For j = 3 To 5 Step 2
  10. For i = 1 To Cells(65536, j).End(xlUp).Row - 2
  11. If d(CStr(rng(i, j))) <> "" Then
  12. arr(d(CStr(rng(i, j))), j - 2) = rng(i, j)     
  13. arr(d(CStr(rng(i, j))), j - 1) = rng(i, j + 1)
  14. End If
  15. Next i
  16. Next j
  17. [c3].Resize(UBound(rng), 4) = arr
  18. End Sub   
½Æ»s¥N½X
¦]¬°ª¾¹D½×¾Â¤Wªº«e½úÀ³¸Ó¦³¨ä¥L¤è¦¡ªº¸Ñªk,
©Ò¥H¤p§Ì¤æÁx¤W¨Óµo°Ý,¥i¥Hªº¸ÜÁÙ±æ«e½ú¤£§[¸Ñµª,·PÁÂ.

¯uªº«Ü©êºp,
"­ì©l¸ê®Æ"¤À­¶¸ò
"´Á±æµ²ªG¥Ü·N"¤À­¶ªºAÄæ¸òBÄæÀ³¸Ó¬O§¹¥þ¤@¼Ëªº,
"´Á±æµ²ªG¥Ü·N"¤À­¶ªº²Ä19¾î¦C¬O¦h¥´ªº,¦Ó²Ä¤@ªÅ¥Õ¾î¦CÀ³¤©§R°£¦Ó¥¼§R°£,
³y¦¨§xÂZ¤£¦n·N«ä.

TOP

¦^´_ 2# greetingsfromtw
  1. Sub Ex2()
  2.     Dim arr As Variant, cts As Integer
  3.     Dim ln As Integer, cn As Integer
  4.    
  5.     With Sheets("­ì©l¸ê®Æ")
  6.         cts = Application.CountA(.Range("A:A"))
  7.         ReDim arr(1 To cts - 1, 1 To 4)
  8.         
  9.         For ln = 2 To cts
  10.             For cn = 3 To 5 Step 2
  11.                 If .Cells(ln, cn) <> "" Then
  12.                     arr(Asc(.Cells(ln, cn).Value) - 64, cn - 2) = .Cells(ln, cn)
  13.                     arr(Asc(.Cells(ln, cn).Value) - 64, cn - 1) = .Cells(ln, cn + 1)
  14.                 End If
  15.             Next cn
  16.         Next ln

  17.         .[G2].Resize(cts - 1, 4) = arr
  18.     End With
  19. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# c_c_lai


    ¤Q¤À·PÁÂc_c_lai«e½ú´£¨Ñ¸Ñµª,¤p§Ì¦A¬ã¨s¤@¤U

TOP

  1. Sub TEST()
  2. Dim Arr, D, T$, R&, C&
  3. Arr = Range("A2:F" & [A65536].End(3).Row)
  4. Set D = CreateObject("Scripting.Dictionary")
  5. For C = 1 To UBound(Arr, 2) Step 2
  6.     For R = 1 To UBound(Arr)
  7.         T = Arr(R, C): If T = "" Then GoTo 101
  8.         If C = 1 Then D(T) = R: GoTo 101
  9.         Arr(R, C) = "": Arr(R, C + 1) = ""
  10.         Arr(D(T), C) = T
  11.         Arr(D(T), C + 1) = Arr(D(T), 2)
  12. 101: Next
  13. Next
  14. [´Á±æµ²ªG¥Ü·N!A3:F3].Resize(UBound(Arr)) = Arr
  15. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# ­ã´£³¡ªL

«D±`·PÁ²a´£³¡ªL«e½úÄ@·N¶O¤ß«üÂI,¤@ª½¥H¨Ó©Ó»X«e½úµL¨p«ü¾É,¯uªº«D±`·P¿E.
¦A¦¸·PÁÂ.

TOP

¦^´_ 5# ­ã´£³¡ªL

²a´£³¡ªL«e½ú±z¦n,¤£¦n·N«ä«_¬N°Ý¤@¤U,

«e½ú©Ò´£¨Ñµ{¦¡½Xªº¨ä¥L³¡¥÷¤p§Ì«j±j¯à»â·|¤@¤G,

¥u¬OÃö©ó²Ä¤T¦æ:
Arr = Range("A2:F" & [A65536].End(3).Row)

¨ä¤¤¼Æ¦r3³o­Ó³¡¥÷,¤p§Ì¤£©ú¨ä·N,
ÁöµMª¾¹D¥i¨ú¥NxlUP,¦ý¬O¥Î1,2,4¥h­×§ïµ{¦¡½X¶i¦æ¸ÕÅç,«oµLªk¹F¨ì¨ú¥NxlToRightµ¥¨ä¥L¤è¦Vªº®ÄªG,
¦Ó¬OÅܦ¨¿ï¨ú¾ãÄæ©Î¬O¾ã¦C,
¤p§Ì¤æÁx,Àµ½Ð«e½ú¤£§[«üÂI,·PÁÂ.

TOP

¦^´_ 7# greetingsfromtw


¤£²ßºD¥Î¼Æ¦r, ´N¥Î­ì¨Ó¼ô¥Îªº´N¦n, ¤£¶··Ð³o­Ó!
¥Ñ¤U¦Ó¤W, °_ÂI´N­n¨ú¤U¤èªºÀx¦s®æ©¹¤W§ä,
¥Ñ¤W¦Ó¤U, °_ÂI´N¬Û¤Ï,
¥ª/¥k¤è¦V¥ç¦P, ¦h°Ñ´X¦¸, °µ¨ìÀ´¬°¤î!

TOP

¦^´_ 8# ­ã´£³¡ªL


  ¤F¸Ñ,¤Q¤À·PÁ«e½ú«üÂI,¤p§Ì·|¦A§V¤O½m²ß.

TOP

¦^´_ 5# ­ã´£³¡ªL


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾ÇÂǦ¹©«¾Ç²ß«e½úªº¤è®×,¤è®×¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

¸ê®Æªí:


µ²ªGªí:



Sub TEST()
Dim Arr, D, T$, R&, C&
'¡ô«Å§iÅܼÆ
Arr = Range("A2:F" & [A65536].End(3).Row)
'¡ô¥OArrÅܼƬO¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J°}¦C¸Ì
Set D = CreateObject("Scripting.Dictionary")
'¡ô¥ODÅܼƬO¦r¨å
For C = 1 To UBound(Arr, 2) Step 2
'¡ô³]¶¶°j°é¶]Äæ!¨C¶¦^¤@¦¸ +2 (1,3,5,.....)
    For R = 1 To UBound(Arr)
    '¡ô³]¶¶°j°é¶]¦C!
        T = Arr(R, C): If T = "" Then GoTo 101
        '¡ô¥OTÅܼƬO°j°éªºArr°}¦C­È
        '¦pªGTÅܼƬOªÅ¦r¤¸!´N¸õ¨ì101¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ(ªÅ¥Õ¤£³B²z)

        If C = 1 Then D(T) = R: GoTo 101
        '¡ô¦pªGCÅܼƬO1!´N¥O¥HTÅܼƷíkey,item¬ORÅܼÆ(¯Á¤Þ¦C¸¹),
        '¸õ¨ì101¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ

        Arr(R, C) = "": Arr(R, C + 1) = ""
        '¡ô¥O³B²z¹Lªº°}¦C¦ì¸m²M°£
        '(¦]¬°¥Î¦P¤@°}¦C©ñ­ì¸ê®Æ½Õ¾ã¬°·s¸ê®Æ)

        Arr(D(T), C) = T
        '¡ô¥OTÅܼƬdD¦r¨å±oitem­È¦C²ÄCÅܼÆÄæArr°}¦C­È¬O TÅܼÆ
        Arr(D(T), C + 1) = Arr(D(T), 2)
        '¡ô¥O¥k°¼ÄæArr°}¦C­È¬OArr°}¦C²Ä2Äæ­È
101: Next
Next
[´Á±æµ²ªG¥Ü·N!A3:F3].Resize(UBound(Arr)) = Arr
'¡ô¥OArr°}¦C­È¼g¤J¥t¤@­Ó¤u§@ªí
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD