c_c_lai

¦^´_ 2# ziv976688 h0 dl3d04d04
°Ñ¦Ò¬Ý¬Ý¡I
¦]§Ú¤£À´³o¹CÀ¸³W«h¡A¥u±N§Aªºµ{¦¡½X²¤¤©¸Õ¼g¡A¤£ª¾¥i§_¡H

1. Sub Ex()
   
2.     Dim startrang%, endrang%, tim!, In1rr As Variant, In2rr As Variant, StrRng$, Ncount$, Number
   
3.     Dim RrngA$, Nrange$, CrngA$, RrngB$, CrngB$, i%, j%, y%, num$, Order$, NUMX$
   
4.     Dim m1%, sta%
   
5. 
   
6.     StrRng = "1"                            '  InputBox("½Ð¿é¤JDATA!¦U·j´M¤ñ¹ïªº°_©l´Á¼Æ", "¿é¤J´Á¼Æ")
   
7.     Nrange = "280"                          '  InputBox("½Ð¿é¤JDATA!¦U·j´M¤ñ¹ïªº¨´¤î(¶}¼ú)´Á¼Æ", "¿é¤J´Á¼Æ")
   
8.     num = "50"                              '  InputBox("½Ð¿é¤J®ÄªGÀÉA¡JH½Æ»s½d³òªº´Á¶Z¼Æ", "¿é¤J¶Z´Á¼Æ")
   
9.     RrngA = "253,268,273"                   '  InputBox("½Ð¿é¤J¦U"²Ä¤@­Ó"¤ñ¹ïªº"°ò·Ç´Á¼Æ", "¿é¤J²Ä¤@­Ó´Á¼Æ")
   
10.     CrngA = "1-2"                           '  InputBox("½Ð¿é¤J¦U"²Ä¤@­Ó"¤ñ¹ïªº7Äæ¨ú¥ô1~6¤§Äæ¦ì¼Æ", "¿é¤JÄæ¦ì¼Æ(1~6)")
    
11.     RrngB = "249-250,263,268"               '  InputBox("½Ð¿é¤J¦U"²Ä¤G­Ó"¤ñ¹ïªº"°ò·Ç´Á¼Æ", "¿é¤J²Ä¤G­Ó´Á¼Æ")
    
12.     CrngB = "1-2"                           '  InputBox("½Ð¿é¤J¦U"²Ä¤G­Ó"¤ñ¹ïªº7Äæ¨ú¥ô1~6¤§Äæ¦ì¼Æ", "¿é¤JÄæ¦ì¼Æ(1~6))
    
13.     Order = ""                              '  InputBox("½Ð¿é¤J¦A¿z¿ïÅÞ¿è±ø¥óªº°_¨´§Ç¸¹", "¿é¤J§Ç¸¹(1~99)©Î¤£¼W¥[(«öEnter)")
    
14.     Number = "04,12,20-22,30,34,38,42,47"   '  InputBox("½Ð¿é¤J¦U«ü©wªº¸¹½X", "¿é¤J¸¹½X(1~49)")
    
15.     Ncount = "2-3"                          '  InputBox("½Ð¿é¤JÅçÃÒª©ªº³sÄò¦¸¼Æ", "¿é¤J¦¸¼Æ(2~10)")
    
16.    
    
17.     tim = Timer
    
18.     [L1:L10] = ""
    
19.     NUMX = num
    
20.     Application.DisplayAlerts = False
    
21.     On Error Resume Next                    '  ±N¿ù»~³B²zªº¤è¦¡³]¬°¡uÄ~Äò¤U¤@¦æ¡v¡C
    
22.     Application.ScreenUpdating = False      '  ¦b­I´º¤U°õ¦æ
    
23.    
    
24.     '  ...............
    
25.     In1rr = ToSplit(Nrange, ",")
    
26.    
    
27.     '  ................
    
28.     In2rr = ToSplit(NUMX, ",")
    
29.     '  ................
    
30.       
    
31.     '  .......................
    
32.     [L1] = Nrange & "=" & Format((Timer - tim) / 24 / 60 / 60, "hh:mm:ss")
    
33.     [L2] = "DATA!¦U·j´M¤ñ¹ïªº°_©l´Á¼Æ=" & StrRng
    
34.     [L3] = "A¡JH(¶}¼úª©)ªº´Á¶Z¼Æ=" & NUMX
    
35.     [L4] = "¤ñ¹ï°ò·Ç´Á¼ÆA=" & RrngA
    
36.     [L5] = "Äæ¦ì¼ÆA=" & CrngA
    
37.     [L6] = "¤ñ¹ï°ò·Ç´Á¼ÆB=" & RrngB
    
38.     [L7] = "Äæ¦ì¼ÆB=" & CrngB
    
39.     [L8] = "¼W¥[¦A¿z¿ïÅÞ¿è±ø¥óªº§Ç¸¹=" & Order
    
40.     [L9] = "¦U«ü©w¦P¸¹½X=" & Number
    
41.     [L10] = "ÅçÃÒª©ªº³sÄò¦¸¼Æ=" & Ncount
    
42. End Sub
    
43. 
    
44. Function ToSplit(txt As String, sp As String, Optional dash As String = "-") As String()
    
45.     Dim FullNameComma As Variant, cts As Integer, nxt As Integer
    
46.     Dim lf As Integer, rt As Integer, spl As String
    
47. 
    
48. 
    
49.     FullNameComma = Split(txt, sp)
    
50.     spl = ""
    
51.     For cts = LBound(FullNameComma) To UBound(FullNameComma)
    
52.         nxt = InStr(FullNameComma(cts), dash)
    
53.         If nxt > 0 Then
    
54.             lf = Val(Left(FullNameComma(cts), nxt - 1))
    
55.             rt = Val(Mid(FullNameComma(cts), nxt + 1))
    
56.             For nxt = lf To rt
    
57.                 spl = IIf(spl = "", CStr(nxt), spl & sp & CStr(nxt))
    
58.             Next
    
59.         Else
    
60.             spl = IIf(spl = "", FullNameComma(cts), spl & sp & FullNameComma(cts))
    
61.         End If
    
62.     Next cts
    
63.    
    
64.     ToSplit = Split(spl, sp)
    
65. End Function

½Æ»s¥N½X

c_c_lai

¦^´_ 1# ziv976688
¼g¤F¤@­Ó´ú¸Õµ{¦¡¡A´£¨Ñ§A¤F¸Ñ ToSplit() ªºÀ³¥Î¡G

1. Sub Test()
   
2.     Dim txt As String, sp As Variant
   
3.    
   
4.     txt = "249-255,263,268"
   
5.     sp = ToSplit(txt, ",")
   
6.     MsgBox "249-255,263,268" & vbCrLf & Join(sp, ",")
   
7.    
   
8.     txt = "04,12,20-22,30,34,38,42,47"
   
9.     sp = ToSplit(txt, ",")
   
10.     MsgBox "04,12,20-22,30,34,38,42,47" & vbCrLf & Join(sp, ",")
    
11. End Sub

½Æ»s¥N½X

c_c_lai

¦^´_ 6# ziv976688
    '  22 ~ 26  ¡G  21 (4)¡B11 (3)¡B2 (2)¡B26 (2)¡B 38 (2)¡B 43 (2)¡B 46 (2)
    '  20 ~ 24  ¡G  21 (4)¡B2 (3)¡B12 (2)¡B14 (2)¡B 30 (2)¡B 46 (2)¡B 17 (1)
    '  45 ~ 51  ¡G  11 (3)¡B34 (2)¡B6 (2)¡B41 (2)¡B 49 (2)¡B 22 (2)¡B 30 (2)
³o¤TºØ²Õ¦X¡A¸ò§A­nªº¼Æ¾Ú¦³©Ò®t¶Z¶Ü¡H

c_c_lai

¦^´_ 8# ziv976688
¬O«ü22´Á¨ì26´Á¤§¶¡¡A21¥X²{4¦¸¡F11¥X²{3¦¸¡F02¥X²{2¦¸¡C

¦Ü©ó
(¤@) «ü©w¸¹½Xªº½d³ò
     ¬O«ü "A°ò·Ç´Á¼Æ21´Á¨ì24´Á" ªº ²Ä 24 ´Á¦æ¤¤¡A¦³¥X²{ "«ü©w¸¹½X"¡H

(¤G) A°ò·Ç´Á¼Æ*B°ò·Ç´Á¼Æ¤ñ¹ïªº´Á¼Æ
     ¦³21*18,21*19,21*20¡F
     22*18,22*19,22*20,22*21¡F23*18,23*19,23*20,23*21,23*22¡F
     24*18,24*19,24*20,24*21,24*22,24*23
     ¤£¤Ó©ú¥Õ¡A21*18 ¡A . . . . .  µ¥µ¥¬O¦ó¸q¡H

c_c_lai

¦^´_ 12# ziv976688

¤U¸ü (50.34 KB)

2017-1-9 13:54

1.     Nrange = "28"
   
2.     RrngA = "22-26"
   
3.     CrngA = "1-2"   
   
4. 
   
5.     RrngB = "20-24"
   
6.     CrngB = "1-2"   
   
7.     Number = "3,11,21-26,43,49"

½Æ»s¥N½X

³o¼Ëªºµ{¦¡½X»P¹Ï¥Üªº»¡©ú¹ïºÙ¶Ü¡H
¯à§_´N¥H¦¹¹Ï¥Ü¡A ¦A±N¥¦¸àÄÀ¤@¤U (A²Õ¦X¡BB²Õ¦X¡B°ò·Ç¦Cªº­q©w)¡H

c_c_lai

¦^´_ 10# ziv976688

¤U¸ü (66.56 KB)

2017-1-10 12:33

¤U¸ü (79.54 KB)

2017-1-10 12:33

¤U¸ü (113.55 KB)

2017-1-10 12:33

¤U¸ü (115.31 KB)

2017-1-10 12:34

¥H¤W 1 ~ 8 ªº
        StrRng =
        Nrange =
        num    =
        RrngA  =
        CrngA  =
        RrngB  =
        CrngB  =
        RrngC  =
        CrngC  =
        Order  =
        Number =
        Ncount =
¯à§_¤@¤@§iª¾¡H

c_c_lai

¦^´_ 16# ziv976688
ÁÂÁ§Aªº»¡©ú¡A¦ý¬O 5¡A6¡A7 ¤T­Ó¹Ï¥Ü¤¤
³£§t¦³ ²Ä 2 ²Õ¡B¥H¤Î ²Ä 3 ²Õ ©O¡I

c_c_lai

¦^´_ 18# ziv976688
Á|­Ó¹Ï¨Ò

¤U¸ü (74.64 KB)

2017-1-10 17:54

c_c_lai

¦^´_ 20# ziv976688
½Ð°Ý¡G
A.  RrngA = "21-24" «üªº¬O ²Ä21´Á¨ì²Ä24´Á´Á¼Æ¡A¦¹¶µ¤wµL±e¸mºÃ¡C
    CrngA = "1-2"
       (½Ð¿é¤J¦U"²Ä¤@­Ó"¤ñ¹ïªº7Äæ¨ú¥ô1~6¤§Äæ¦ì¼Æ", "¿é¤JÄæ¦ì¼Æ(1~6)")
    ¥Ø«e¤§¿é¤J­È¬° "1-2"¡A ¦Ó§Aªº
    ²Ä21´Á = RrngA ¡F01,02 = CrngA=2 (Äæ)
    ¦b¦¹¤§ 01,02 = CrngA=2 (Äæ) «üªº¬O 01 (BÄæ¦ì­È),02 (CÄæ¦ì­È)
    Á`¦@¦³¨â­ÓÄæ¦ì¡H ¬°¦ó­n¨ú 01,02¡H ¬O¦]¬° CrngA «ü©w "1-2" ("B"¡B"C"Äæ)¡H
    ¦P²z
    RrngB = "18-21" «üªº¬O ²Ä18´Á¨ì²Ä21´Á¤§´Á¼Æ¡A¦¹¶µ¥ç¤wµL±e¸mºÃ¡C
    CrngB = "1-2"¡A ¦ý¬O§Aªº»¡©ú
    ²Ä18´Á = RrngB ¡F30 = CrngB=1 (Äæ)
    18      12   15   21   23   30   48   26
    ¬°¦ó¬O¨ú²Ä¤­¦ì (FÄæ) ªº 30¡A ¥B«ü©w¤§ CrngB=1 ¡H ¦Ó§Aªº»¡©ú¬O
    ©Ò¥H~ A°ò·Ç²Õ¦X*B°ò·Ç²Õ¦X = ·í´Á (21´Á) ²Ä¤@¦ì (BÄæ) ¥X 01, ²Ä¤G¦ì (CÄæ)¥X 02¡F
    ¨ä¤W3´Á (18´Á) ²Ä¤­¦ì (FÄæ) ¥X 30 ¡H  
    30 ¬O®Ú¾Ú¬Æ»ò¼Æ¾Ú¥X½uªº¡A¦b§A«ü©wªº Number = "3,11,21-26,43,49" ¤¤¨Ã¤£¦s¦b 30 ³o¼Æ¦r¡H
B.  RrngA¨ìNrange=21´Á¨ì28´Á=Nrange-RrngA=7 ³o­Ó»P NUM=15 ~~ :
    ±NDATA!ªº(Nrange-NUM)¨ìNrangeªºA¡JH¶}¼úª©´Á¼Æ½Æ»s¶K¤WSheet1!A7 ªºªí¹F¦³¦ó¤£¦P¡H
C.  ±µ¤U¨Ó¡A ·í´Á (17´Á) ²Ä¤@¦ì (BÄæ) ¥X01, ²Ä¤G¦ì (CÄæ) ¥X02¡F¨ä¤W3´Á (14´Á) ²Ä¤­¦ì (FÄæ)
    ¥X30=A*B°ò·Ç²Õ¦X²Ä1­ÓÅçÃÒª©=17-12+14-5
    ¬°¦ó¤S«ü¦V 17´Á (RrngA = "21-24"¡ARrngB = "18-21")¡H "¨ä¤W3´Á" ¨ä¤¤ªº 3 ¬O "©T©w­È" ¶Ü¡H
    ¦p¬O¡A¨º¦b¦ó³B©w¸q¡H ¤S¡B¦¹³B 17-12+14-5 ªº§t·N¬O¡H ©¹¤U¤§ (14´Á)¡B(5´Á) ¥ç¦P¦¹°Ý¡C

ÁÂÁ§Aªº­@¤ß¦^ÂСI

c_c_lai

¦^´_ 22# ziv976688
¤£¦n·N«ä¡A¼Ó¤WªººÃ°Ý¡A©|¥¼©ú½T¸Ñµª¡C
A.  ²Ä18´Á = 18      12   15   21   23   30   48   26
    ¬°¦ó¬O¨ú²Ä¤­¦ì (FÄæ) ªº 30¡A ¦Ó«D¨ä¥L¼Æ¾Ú¡H
    ¦P¼Ëªº¡A
    ²Ä21´Á = 21      01   02   11   26   38   43   04
    ¬°¦ó­n¨ú 01,02¡A ¦Ó«D¨ä¥L¼Æ¾Ú¡H

    P.S.  «D¥H¦×²´¨Ó§PÂ_¡AÀ³¥H¤@¦X²zµ{¦¡ÅÞ¿è¨Ó°µ§PÂ_¡C
   
B.  RrngA¨ìNrange=21´Á¨ì28´Á=Nrange-RrngA=7  (28 - 21 = 7)
    7 ¬O«ü °ò·Ç¦CA ªº´Á¼Æ¨ì Nrange ´Á¼Æªº´Á¶Z¡C
    ©Ò¥H¨äÅçÃÒª©¦P¼Ë¬O ©¹¤U7´Á ¨Ó¹ïÀ³¡C

    NUM=XX~~¬O«ü©w´Á¶Z´Á¼Æ¡C
    ·íNUM=15¡F§Y¬O®ÄªGÀÉ®×Sheet1!A7:H22=DATA!A&Nrange-NUM+1:H&Nrange+1
   
    B1.  ¨ä¤¤¡A A7:H22 ¬O®Ú¾Ú¬Æ»ò¦Ó¨Ó¡H
    B2.  A&Nrange (28) - NUM (15) + 1 : H&Nrange (28) + 1¡A §Y A14:H29 ¡H

C.  "¨ä¤W3´Á" ¨ä¤¤ªº 3 ¬O "©T©w­È" ¶Ü¡H
    ¦p¬O¡A¨º¦b¦ó³B©w¸q¡H

    ±µ¤U¨Ó¡A ·í´Á (17´Á) ²Ä¤@¦ì (BÄæ) ¥X01, ²Ä¤G¦ì (CÄæ) ¥X02¡F¨ä¤W3´Á (14´Á) ²Ä¤­¦ì (FÄæ)
    ¥X30=A*B°ò·Ç²Õ¦X²Ä1­ÓÅçÃÒª©=17-12+14-5

    ®Ú¾Ú¬Æ»ò©w¸q¡A¬°¦ó¤S«ü¦V 17´Á (RrngA = "21-24"¡ARrngB = "18-21")¡H
    ©¹¤U¤§ (14´Á)¡B(5´Á) ¥ç¦P¦¹°Ý¡C
    ¤S¡B¦¹³B 17-12+14-5 ªº§t·N¬O¡H