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

[µo°Ý] excel vba 20­Ó¼Æ¦r¤À²Õ¦¨4²Õ(ÀH¾÷)

[µo°Ý] excel vba 20­Ó¼Æ¦r¤À²Õ¦¨4²Õ(ÀH¾÷)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-12-4 15:38 ½s¿è

³Â·Ð¦U¦ì¤j¤jÀ°§Ú¸ÑÃD(§ÚÁÙ¬Oªì¾ÇªÌ T^T)
´N¬O¦³1~20­Ó¼Æ¦r¡A·QÀH¾÷¤À²Õ¦¨4²Õ
¥i½Ð¤j¤j­Ì´£¨Ñ±zªº¼¶¼g¤è¦¡¶Ü?(¨Ãµy¥[ª`¸Ñ)

·PÁ¦U¦ìªºÀ°¦£

³Â·Ð¦U¦ì¤j¤jÀ°§Ú¸ÑÃD(§ÚÁÙ¬Oªì¾ÇªÌ T^T)
´N¬O¦³1~20­Ó¼Æ¦r¡A·QÀH¾÷¤À²Õ¦¨4²Õ
¥i½Ð¤j¤j­Ì´£¨Ñ±zªº¼¶¼g¤è¦¡ ...
tray1203 µoªí©ó 2014-12-4 12:30
  1. Sub nn()
  2.   Dim iI%, iJ%, iMax%, iGet% ' ©w¸q¾ã¼Æ
  3.   Dim sStr$ ' ¦r¦ê
  4.   Dim vA(), vT() ' °}¦C
  5.   
  6.   Randomize (Rnd) ' ªì©l¤Æ¶Ã¼Æ
  7.   vA = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20) ' «Å§i°}¦C¥]§t¨C­Ó¼Æ¦r (1~20)
  8.   ReDim vT(4, 0) ' ³]©w¼Ðªº°}¦Cªºªì©l¤j¤p
  9.   iMax = 20 ' 20 ­Ó¼Æ¦r
  10.   Do While iMax > 0 ' Do °j°é¶}©l,·í­p¼Æ­È¤j©ó0«hÄ~Äò°õ¦æ°j°é
  11.     For iI = 0 To 4 ' For °j°é¶}©l,¨C²Õ5­Ó¼Æ¦r(0 ~ 4, ¦@4²Õ)
  12.       iGet = Int((iMax - iI - 1 + 1) * Rnd + 1) ' §ì¨ú¤U­Ó¼Æ¦r
  13.       vT(iI, UBound(vT, 2)) = vA(iGet - 1) ' ¦s¨ì¼Ðªº°}¦C
  14.       For iJ = iGet To iMax - iI - 1
  15.         vA(iJ - 1) = vA(iJ) '  ±N¸Ó¼Æ¦r¤§«áªº¤¸¯À¨Ì§Ç¶ñ¸É¤W¨Ó
  16.       Next
  17.       If UBound(vA) > 0 Then f ' If ±ø¥ó¦¡¶}©l(­Y©|¥¼¨ì³Ì«á¤@­Ó¼Æ¦r)
  18.         ReDim Preserve vA(UBound(vA) - 1) ' «h°}¦C¤¸¯À¼Æ¶q´î 1
  19.       Else ' §_«h
  20.         Erase vA ' ²M°£¸Ó°}¦C
  21.       End If ' If ±ø¥ó¦¡µ²§ô
  22.     Next  ' For °j°éµ²§ô
  23.     If UBound(vT, 2) < 3 Then ReDim Preserve vT(4, UBound(vT, 2) + 1) ' ­Y©|¥¼³B²z§¹¦¨, ¼Ðªº°}¦C·s¼W¦s©ñ¤U1²Õ5 (0 ~ 4)­Ó¼Æ¦rªºªÅ¶¡
  24.     iMax = iMax - 5 ' ­p¼Æ­È´î¥h1²Õªº¼Æ¶q (5)
  25.   Loop ' Do °j°éµ²§ô
  26.   
  27.   sStr = "" ' ©³¤U±N¦U¤¸¯À¨Ì§Ç©ñ¤J sStr ÅܼƤº, ¥H«K«áÄòÅã¥ÜÀH¾÷¤À²Õµ²ªG¥Î
  28.   For iMax = 0 To 3 ' 4²Õ³]¦r
  29.     For iI = 0 To 4 ' ¨C²Õ5­Ó¼Æ¦r
  30.       If sStr <> "" Then
  31.         sStr = sStr & ", " & vT(iI, iMax) ' «D­º¦¸²K¥[¼Æ¦r, «e­±¥[¤W , °µ¬°¼Æ¦rªº°Ï¹j
  32.       Else
  33.         sStr = vT(iI, iMax) ' ­º¦¸²K¥[¼Æ¦r, «e­±¤£¥[ ,
  34.       End If
  35.     Next
  36.     sStr = sStr & Chr(10) ' 1²Õ¼Æ¦r²K¥[§¹¦¨, ¥[¤W´«¦æ¦r¤¸
  37.   Next
  38.   MsgBox sStr
  39. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# tray1203
  1. Sub test()
  2.     Dim Ary(1, 19), Rng As Range
  3.     Cells.Clear
  4.     [a1] = "¸ê®Æ"
  5.     [b1] = "¶Ã¼Æ±Æ¦C"
  6.     For i = 0 To 19
  7.         Ary(0, i) = i + 1
  8.         Ary(1, i) = Int(Rnd() * 100) '¨ú¶Ã¼Æ­È
  9.     Next
  10.    
  11.     With ActiveSheet
  12.         Set Rng = .[a2:b2].Resize(UBound(Ary, 2) + 1) '³]©wÀx¦s®æ¼Æ
  13.         Rng.Value = Application.Transpose(Ary) '±NAryªº°}¦C¸ê®Æ¦s¤JÀx¦s®æ¤¤
  14.         [a2:b2].Resize(UBound(Ary, 2) + 1).Select '¿ï¨úÀx¦s®æªº¸ê®Æ
  15.         Selection.Sort Key1:=Rng(2), Order1:=xlAscending, Header:=xlNo '¨Ì¶Ã¼Æ¥Ñ¤p¦Ü¤j±Æ§Ç
  16.     End With

  17.     For i = 1 To 4
  18.         Cells((i - 1) * 5 + 2, 1).Resize(5).Copy
  19.         Cells(i + 1, 4) = "²Ä " & i & " ²Õ"
  20.         Cells(i + 1, 5).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  21.         False, Transpose:=True 'Àx¦s®æÂà¸m
  22.     Next
  23. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¹D¼w¬O´£ª@¦Û§Úªº©ú¿O¡A¤£¸Ó¬O¨þ¥¸§O¤HªºÃ@¤l¡C
ªð¦^¦Cªí ¤W¤@¥DÃD