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

[µo°Ý] ¾î¦¡¸ê®ÆÂà´«¬°ª½¦¡¸ê®Æ

¦^´_ 9# aer


    ´¿À°ªB¤Í¾ã²z¹LÃþ¦üªº¸ê®Æ¡A´£¨Ñ´X­Ó«Øij°Ñ¦Ò
   1.À³¦Ò¼{¸ê®Æ°ß¤@©Ê¡A©m¦W¬O¦³¥i¯à­«½Æªº¡AÀ³¥[¤J¨­¥÷ÃÒ¸¹©Î³ø¦W¥N¸¹µ¥µ¥¤@¨Ö³B²z¡A¤£µM«áÄò±`¦³°ÝÃD
   2.ª`·N±Æ§Ç»Ý¨D¡A¨Ò¦p¥Î¤é´Á©Î²Äxx±èªºxx±Æ§Ç(¥Îxx±Æ§Ç¡A¥i¯à±o¦Û«Ø²M³æ)
   3.­ãª©¤wµ¹¤F§¹¾ãªº¤½¦¡©Mvba¡A«áÄò¬Ý³æ¦ìªø©x©Î»Ý¨D³æ¦ì±`»Ý­n´£¨Ñ¦UºØ¤£¦P«¬¦¡¾ã²z«áªº¸ê®Æ¡A¥i¹B¥Î¼Ï¯Ã¤ÀªR§@ªì¨B¾ã²z¦A¶i¦æ²Ó¶µ½Õ¾ã¡C

TOP

¦^´_ 10# ­ã´£³¡ªL
ÁÂÁ­㴣³¡ªLª©¥D¡A¦p¦³°ÝÃD¦A½Ð±Ð±z¡A¦A¦¸·PÁ±z¡I

TOP

¦^´_ 11# KCC
KCC ÁÂÁ±zªº«ü±Ð¡A¦¹¸ê®ÆÂà´««á§Y¬O¥Î¼Ï¯Ã¤ÀªRªí°µ²Î­p¤ÀªR ¡C

TOP

¥»©«³Ì«á¥Ñ handmuch ©ó 2016-7-25 22:32 ½s¿è

Ãþ¦üªº°ÝÃD«Ü¦h¤H³£°Ý¹L ¥u¬OÃD¥Øªø±o¤£¤Ó¤@¼Ë     ³oÃþ³£¬O¤GºûÂà¤@ºû

¥i¥H¥Î"¼Ï¯Ã¤ÀªRªí©M¼Ï¯Ã¤ÀªR¹ÏºëÆF"¨Ó§¹¦¨!!

¤GºûÂà¤@ºû±Ð¾Ç¼v¤ù
http://expresmile.teachable.com

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-2 10:04 ½s¿è

¦^´_ 1# aer
¦^´_ 10# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É
ÁÂÁ aer«e½úµoªí¦¹¥DÃD»P½d¨Ò
¥H¤U¤ß±oµù¸Ñ½Ð¦A«ü¾É!ÁÂÁÂ

Option Explicit
Sub TEST()
Dim Arr, Brr, T, i&, N&, j%
'¡ô«Å§iÅܼÆ(Arr,Brr,T)¬O³q¥Î«¬ÅܼÆ,(i,N)¬Oªø¾ã¼Æ,(j)¬Oµu¾ã¼Æ,
½Ð±Ð ­ã´£³¡ªL«e½ú:
1.³o¸ÌªºjÅܼƬ°¤°»ò­n«Å§i¬°µu¾ã¼Æ?
2.¤°»ò®É­Ô¸Ó¥Îªø¾ã¼Æ? µu¾ã¼Æ?

With Sheets("Sheet1").UsedRange
'¡ô¥H¤U¬OÃö©ó ¦W¬°: "Sheet1" ¤u§@ªíªº¨Ï¥ÎÀx¦s®æªºµ{§Ç
'¨Ï¥ÎÀx¦s®æ¬O«ü¦³³Q½s¿è¹LªºÀx¦s®æ
'¤u§@ªíªº¨Ï¥ÎÀx¦s®æ¬O«ü:¯à²[»\©Ò¦³ ¨Ï¥ÎÀx¦s®æ ªº³Ì¤p¤è¥¿°Ï°ì
'¨Ò¦p:
'1.¶}­Ó¥þ·sªº¤u§@ªí>[D3]¿é¤J "¥ª¤W¨¤",[F10]¿é¤J "¥k¤U¨¤" ,.Address=$D$3:&F$10]
'2.¦A¦b[K2]¿é¤J "¥k¤W¨¤" .Address=$D$2:$K$10
'3.¦A¦b[G12]¿é¤J "¤UÃäÃä" .Address=$D$2:$K$12
'4.¦A¦b[H1]¿é¤J "¤WÃäÃä" .Address=$D$1:$K$12]
'5.¦A¦b[S20]¿é¤J "·s¥k¤U¨¤" .Address=$D$1:$S$20]
'ÁÂÁ ­ã´£³¡ªL«e½ú±`¥Î¤£¦Pªº³¯­z¤è¦¡Åý«á½ú¾Ç²ß!ÁÂÁÂ

     Arr = .Value
     '¡ô¥OArr¬O¤Gºû°}¦C!°}¦C­È´N¬O ¤u§@ªíªº¨Ï¥ÎÀx¦s®æªº­È
     ReDim Brr(1 To .Count, 1 To 3)
     '¡ô«Å§i¤GºûBrr°}¦Cªº½d³ò! Áa¦V±q1 ¨ì¤u§@ªíªº¨Ï¥ÎÀx¦s®æ®æ¼Æ ¦C,
     '¾î¦V±q1 ¨ì3Äæ

End With
For i = 2 To UBound(Arr)
'¡ô³]¥~¶¶°j°éiÅܼƱq2 ¨ìArr°}¦CªºÁa¦V³Ì¤j¦C¸¹¼Æ
   For j = 2 To UBound(Arr, 2)
   '¡ô³]¤º¶¶°j°éjÅܼƱq2 ¨ìArr°}¦Cªº¾î¦V¦V³Ì¤jÄ渹¼Æ
      If Arr(i, j) <> "" Then
      '¡ô¦pªG°j°éArr°}¦C­È ¤£¬OªÅ¦r¤¸??
         N = N + 1
         '¡ôN²Ö¥[ 1
         'NÅܼƦb«e­±³£¨S¥X²{¹L!
         '¥u¦³«Å§i¬O ªø¾ã¼Æ,©Ò¥HNªºªì©l­È¬O0 ,¨C¦¸if±ø¥ó¦¨¥ß´N·|¥[ 1

         T = Split(Trim(Arr(i, j)), "±è")
         '¡ô¥OTÅܼƬO °j°éArr°}¦C­È¸g¹L¥hÀY§ÀªÅ¥Õ¦r¤¸«á,
         '¥H"±è"¦r¤¸¤À³Îªº¤@ºû°}¦C
         'TÅܼƦb³Ì«e­±¬O«Å§i¬° ³q¥Î«¬ÅܼÆ!¦¹¤@°Ê§@·|ÅýTÅܦ¨¤@ºû°}¦C
         'TÅܼÆÅܤÆÁ|¨Ò:T=5 (T¬O¼Æ¦r),T="5" (T¬O¦r¦ê),T=5 & 5 (T¬O¦r¦ê)

         Brr(N, 1) = Arr(i, 1)
         '¡ô±NArr°}¦C°j°é¦C²Ä¤@Ä檺­È­Ë¤J Brr°}¦CN¦C²Ä¤@Äæ¸Ì
         Brr(N, 2) = T(0) & "±è"
         '¡ô±NT³o¤@ºû°}¦Cªº²Ä¤@­Ó­È³s±µ"±è"¦¨¬°¦r¦ê«á,©ñ¶iBrr°}¦CN¦C²Ä¤GÄæ¸Ì
         '¦]¬°³QSplit()©î¸Ñªº°}¦C³£±q(0)¶}©l½s¸¹!
         Brr(N, 3) = T(1)
         '¡ô±NT³o¤@ºû°}¦Cªº²Ä¤G­Ó­È,©ñ¶iBrr°}¦CN¦C²Ä¤TÄæ¸Ì
      End If
   Next j
Next i
With Sheets("Sheet2")
'¡ô¥H¤U¬OÃö©ó ¦W¬°: "Sheet2" ¤u§@ªíªºµ{§Ç
     .UsedRange.Clear
     '¡ô¤u§@ªíªº¨Ï¥ÎÀx¦s®æ²M°£±¼!
     '³o­Ó°Ê§@·|Åý.UsedRange.Address=$A$1
     If N = 0 Then Exit Sub
     '¡ô¦pªGNÅܼƬO0,´Nµ²§ôµ{¦¡°õ¦æ!
     'NÅܼƦpªG¬O0,¬O¦]¬°«e­±ªºµ{§ÇIF±ø¥ó³£¤£¦¨¥ß!N³£¨S¾÷·|²Ö¥[

     .[A1:C1] = Array("©m¦W", "±è¦¸", "¤é´Á")
     '¡ô[A1],[B1],[C1]Àx¦s®æ¥Î¤@ºû°}¦C­Ó§O­Ë¶i¥h,[A1]="©m¦W",[B1]="±è¦¸",[C1]="¤é´Á"
     .[A2:C2].Resize(N) = Brr
     '¡ô±q[A2:C2]¦V¤UÂX®iNÅÜ¼Æ ¦C¼Æªº°Ï°ìÀx¦s®æ,¥HBrr°}¦C­È­Ë¶i¥h
     Application.Goto .[A1]
     '¡ôÀx¦s®æ´å¼Ð¸õ¨ìSheets("Sheet2").[A1]
End With
End Sub

TOP

'¨Ï¥ÎÀx¦s®æ¬O«ü¦³³Q½s¿è¹LªºÀx¦s®æ
'¤u§@ªíªº¨Ï¥ÎÀx¦s®æ¬O«ü:¯à²[»\©Ò¦³ ¨Ï¥ÎÀx¦s®æ ªº³Ì¤p¤è¥¿°Ï°ì
'¨Ò¦p:
'1.¶}­Ó¥þ·sªº¤u§@ªí>[D3]¿é¤J "¥ª¤W¨¤",[F10]¿é¤J "¥k¤U¨¤" ,.Address=$D$3:&F$10]
'2.¦A¦b[K2]¿é¤J "¥k¤W¨¤" .Address=$D$2:$K$10
'3.¦A¦b[G12]¿é¤J "¤UÃäÃä" .Address=$D$2:$K$12
'4.¦A¦b[H1]¿é¤J "¤WÃäÃä" .Address=$D$1:$K$12
'5.¦A¦b[S20]¿é¤J "·s¥k¤U¨¤" .Address=$D$1:$S$20

TOP

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