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

[µo°Ý] ·j´M³Ì«á¤@¦C³£¦³¬Û¦P¼Æ¦rªº²Õ¦X¡C

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2017-1-5 20:04 ½s¿è

¦^´_ 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.     StrRng = "1"                            '  InputBox("½Ð¿é¤JDATA!¦U·j´M¤ñ¹ïªº°_©l´Á¼Æ", "¿é¤J´Á¼Æ")
  6.     Nrange = "280"                          '  InputBox("½Ð¿é¤JDATA!¦U·j´M¤ñ¹ïªº¨´¤î(¶}¼ú)´Á¼Æ", "¿é¤J´Á¼Æ")
  7.     num = "50"                              '  InputBox("½Ð¿é¤J®ÄªGÀÉA¡JH½Æ»s½d³òªº´Á¶Z¼Æ", "¿é¤J¶Z´Á¼Æ")
  8.     RrngA = "253,268,273"                   '  InputBox("½Ð¿é¤J¦U"²Ä¤@­Ó"¤ñ¹ïªº"°ò·Ç´Á¼Æ", "¿é¤J²Ä¤@­Ó´Á¼Æ")
  9.     CrngA = "1-2"                           '  InputBox("½Ð¿é¤J¦U"²Ä¤@­Ó"¤ñ¹ïªº7Äæ¨ú¥ô1~6¤§Äæ¦ì¼Æ", "¿é¤JÄæ¦ì¼Æ(1~6)")
  10.     RrngB = "249-250,263,268"               '  InputBox("½Ð¿é¤J¦U"²Ä¤G­Ó"¤ñ¹ïªº"°ò·Ç´Á¼Æ", "¿é¤J²Ä¤G­Ó´Á¼Æ")
  11.     CrngB = "1-2"                           '  InputBox("½Ð¿é¤J¦U"²Ä¤G­Ó"¤ñ¹ïªº7Äæ¨ú¥ô1~6¤§Äæ¦ì¼Æ", "¿é¤JÄæ¦ì¼Æ(1~6))
  12.     Order = ""                              '  InputBox("½Ð¿é¤J¦A¿z¿ïÅÞ¿è±ø¥óªº°_¨´§Ç¸¹", "¿é¤J§Ç¸¹(1~99)©Î¤£¼W¥[(«öEnter)")
  13.     Number = "04,12,20-22,30,34,38,42,47"   '  InputBox("½Ð¿é¤J¦U«ü©wªº¸¹½X", "¿é¤J¸¹½X(1~49)")
  14.     Ncount = "2-3"                          '  InputBox("½Ð¿é¤JÅçÃÒª©ªº³sÄò¦¸¼Æ", "¿é¤J¦¸¼Æ(2~10)")
  15.    
  16.     tim = Timer
  17.     [L1:L10] = ""
  18.     NUMX = num
  19.     Application.DisplayAlerts = False
  20.     On Error Resume Next                    '  ±N¿ù»~³B²zªº¤è¦¡³]¬°¡uÄ~Äò¤U¤@¦æ¡v¡C
  21.     Application.ScreenUpdating = False      '  ¦b­I´º¤U°õ¦æ
  22.    
  23.     '  ...............
  24.     In1rr = ToSplit(Nrange, ",")
  25.    
  26.     '  ................
  27.     In2rr = ToSplit(NUMX, ",")
  28.     '  ................
  29.       
  30.     '  .......................
  31.     [L1] = Nrange & "=" & Format((Timer - tim) / 24 / 60 / 60, "hh:mm:ss")
  32.     [L2] = "DATA!¦U·j´M¤ñ¹ïªº°_©l´Á¼Æ=" & StrRng
  33.     [L3] = "A¡JH(¶}¼úª©)ªº´Á¶Z¼Æ=" & NUMX
  34.     [L4] = "¤ñ¹ï°ò·Ç´Á¼ÆA=" & RrngA
  35.     [L5] = "Äæ¦ì¼ÆA=" & CrngA
  36.     [L6] = "¤ñ¹ï°ò·Ç´Á¼ÆB=" & RrngB
  37.     [L7] = "Äæ¦ì¼ÆB=" & CrngB
  38.     [L8] = "¼W¥[¦A¿z¿ïÅÞ¿è±ø¥óªº§Ç¸¹=" & Order
  39.     [L9] = "¦U«ü©w¦P¸¹½X=" & Number
  40.     [L10] = "ÅçÃÒª©ªº³sÄò¦¸¼Æ=" & Ncount
  41. End Sub

  42. Function ToSplit(txt As String, sp As String, Optional dash As String = "-") As String()
  43.     Dim FullNameComma As Variant, cts As Integer, nxt As Integer
  44.     Dim lf As Integer, rt As Integer, spl As String


  45.     FullNameComma = Split(txt, sp)
  46.     spl = ""
  47.     For cts = LBound(FullNameComma) To UBound(FullNameComma)
  48.         nxt = InStr(FullNameComma(cts), dash)
  49.         If nxt > 0 Then
  50.             lf = Val(Left(FullNameComma(cts), nxt - 1))
  51.             rt = Val(Mid(FullNameComma(cts), nxt + 1))
  52.             For nxt = lf To rt
  53.                 spl = IIf(spl = "", CStr(nxt), spl & sp & CStr(nxt))
  54.             Next
  55.         Else
  56.             spl = IIf(spl = "", FullNameComma(cts), spl & sp & FullNameComma(cts))
  57.         End If
  58.     Next cts
  59.    
  60.     ToSplit = Split(spl, sp)
  61. End Function
½Æ»s¥N½X

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2017-1-9 11:12 ½s¿è

¦^´_ 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

TOP

¦^´_ 12# ziv976688
3232323.png
2017-1-9 13:54
  1.     Nrange = "28"
  2.     RrngA = "22-26"
  3.     CrngA = "1-2"   

  4.     RrngB = "20-24"
  5.     CrngB = "1-2"   
  6.     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

TOP

¦^´_ 10# ziv976688
3232323.png
2017-1-10 12:33

A.png
2017-1-10 12:33

B.png
2017-1-10 12:33

C.png
2017-1-10 12:34

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

TOP

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

TOP

¦^´_ 18# ziv976688
Á|­Ó¹Ï¨Ò
D.png
2017-1-10 17:54

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD