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

[µo°Ý] ½Ð±Ð­n¦p¦ó¨Ï¥Î VBA §P§O¤£³W«hÀx¦s®æªº¿ìªk2

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-12-7 08:42 ½s¿è

¦^´_ 20# c_c_lai
20# ªº Ex() ¤º¤§ C¡BD Äæ¤@¦æ¤@¦æ¦a¥h»¼¼W¬O¬°¤F­n
Æ[¹î¸ê®Æ³B²z¤Î¹Lµ{¡C¥H¤U¬°§ï¥H "¦r¨å" ³B¸Ì¡G
  1. Sub ex2()
  2.     Dim dic As Object, dic2 As Object
  3.     Dim A As Range, Rng As Range
  4.    
  5.     Set dic = CreateObject("Scripting.Dictionary")
  6.     Set dic2 = CreateObject("Scripting.Dictionary")
  7.     dic("³sÄò¼Æ¶q") = "¼Æ¶q"
  8.     dic2("³sÄò¦ì§}") = "²Õ¦X¼Æ¶q"
  9.    
  10.     With ¤u§@ªí1
  11.         Set Rng = .Range("A1").CurrentRegion
  12.         Rng.Replace 0, Empty, xlWhole
  13.         
  14.         Set A = Rng.Find(Empty)
  15.         
  16.         Do Until A Is Nothing
  17.             A = 0: s = 1
  18.             Cnt A
  19.             dic(s) = dic(s) + 1
  20.             dic2(sRng) = Range(sRng).Count
  21.             
  22.             Set A = Rng.Find(Empty)
  23.         Loop
  24.     End With
  25.    
  26.     With ¤u§@ªí2
  27.         .[A1].Resize(dic.Count, 1) = Application.Transpose(dic.keys)
  28.         .[B1].Resize(dic.Count, 1) = Application.Transpose(dic.items)
  29.         .[A1].Resize(dic.Count, 2).Sort key1:=.[A1], Header:=xlYes
  30.         .[C1].Resize(dic2.Count, 1) = Application.Transpose(dic2.keys)
  31.         .[D1].Resize(dic2.Count, 1) = Application.Transpose(dic2.items)
  32.    End With
  33.    
  34.     '  MsgBox dic.Count - 1
  35.     Set dic = Nothing
  36.     Set dic2 = Nothing
  37. End Sub
½Æ»s¥N½X
·PÁ Hsieh ª©¤jªº«ü¾É¡I

TOP

¦^´_ 19# c_c_lai
¦^´_ 13# Hsieh
¦^´_ 4# stillfish00

§Ú°µ3­Ó¼Ð·Ç¹Ï«¬¤Î²Î­p¼Æ¶q¥H¨Ñ¤j®a´ú¸Õ¥Î
§Úªºµ{¦¡¤]±Ä¥Î»¼°j©I¥s¤è¦¡³B²z¡A­ì¹Ï§Î°õ¦æ®É¶¡¤j·§2~3¬í´N¯à§¹¦¨¡C
³oµ{¦¡¼g±o«ÜºC«Ü¤[¡A¨C­Ó«ü¥O³£¬O±qºô¸ô¤W©Î°Ñ¦Ò¤j®aªºµ{¦¡ºCºC°ï¥X¨Óªº¡A¤@¦@ªá¤F7~8¤p®É¤~§¹¦¨¡A¤]º¡¦³¦¨´N·P¡A½Ð¦³»yªk«Ý§ï¶i¤§³B½Ð¤j®a¦h¦h«ü±Ð¡C
¤U¤è¬°´ú¸ÕÀÉ®×
­±¿n¤j¤p¤À§G²Î­p_ML089.rar (139.12 KB)
   
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 22# ML089
Timer¨Ï¥Îªº©Ç©Çªº¡A¦³®ÉÀþ¶¡°õ¦æ§¹¦¨«oÅã¥Ü9.X¬í¡ATimer¤ÎTimeE­pºâ¥X¨Ó¤]®t«Ü¦h(1.5¬í¤Î1.0¬í)¡A´X¥G®t1.5­¿¡A¤j®a¥i¥HÀ°§Ú¬Ý¬Ý¶Ü?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 22# ML089

³o¬ODEBUD¹ï¸Ü®Ø¿é¤J1®É¡A¥i¥H²£¥Í°}¦C³B²z«áªº¸ê®Æ¥H¨Ñ¬d®Ö

   
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 23# ML089
½Ð±Ð¡G
  1.     '  ¡õ ­pºâ¨C²Õ­Ó¼Æ¡C ª`·N! Frequency ¦^¶Ç i + 1 ²Õ¡A©Ò¥ý±N i - 1
  2.     Bins_array = Application.Evaluate("Row(1:" & i - 1 & ")")
  3.     ArrN = Application.Frequency(Arr, Bins_array)
  4.     MaxN = Application.Max(ArrN)

  5.     '  ¡õ ²Î­p¨C²Õ­Ó¼Æ¡C ª`·N! Frequency ¦^¶Ç i + 1 ²Õ¡A©Ò¥ý±N MaxN - 1
  6.     Bins_array = Application.Evaluate("Row(1:" & MaxN - 1 & ")")
  7.     ArrF = Application.Frequency(ArrN, Bins_array)
½Æ»s¥N½X
Bins_array¡BArrN ¡BMaxN ¡BArrF  ªº¤º²[¤Î§@¥Î¡A¯à§_¥[¥H¸ÑªR¡A
§Ú¹ï¤@¨Ç¥Î»y¤£«Ü±E±x¡A¥B¤ÏÀ³¸û¿ð¶w¡AÁÂÁÂÅo¡I

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-12-7 16:05 ½s¿è

¦^´_ 22# ML089
Nice Job¡A´£¨ú¨ìArray¥[³t«Ü¦h¡A
§Ú¤]µo²{§Úªº¤èªkÅÞ¿è¤W¦³°ÝÃD¡A
¥t¥~¡ATime­ì¥»´N¥u¦³¨ì¬í¦Ó¤w¡A®É¶¡¶Ã¸õ¬O¦]¬°§A§â inputbox ªº®É¶¡¤]ºâ¤F¡C

TOP

¦^´_ 26# stillfish00
«¢«¢¡A¤º¦æ¤H¤@¬Ý´N§ä¥X°ÝÃD¡A§Ú³ºµM©Çtimer¨ç¼Æ©Ç©Çªº¡A·PÁÂ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-12-7 17:22 ½s¿è

¦^´_ 26# stillfish00
¦^´_ 27# ML089
ªº½T¡I
´£¨ú¨ìArray½T¹ê¥[³t«Ü¦h¡A¦P®É¤]¸Ñ¨M¤F¿ð½wªº§xÂZ¡C
ÁÂÁ§Aµ¥ªº«ü±Ð¡I

TOP

¦^´_ 25# c_c_lai

Frequency ¥i¥H«öF1¬Ý¬Ý»¡©ú¡C(¥H«e§Ú¤@¶}©l¬Ý¤]¤£À´¡A³o¬O¥¿±`ªº¡A¦h°µÃD¥Ø¤~¯àºCºC¤F¸Ñ)

¥ÎÁ|¨Ò»¡©ú
¨Ò¤@:¦³¤@¸s¼Æ¦r {1,2,3,4,5,6,7,8,9}¡A­n¨D3­Ó½d³ò¦U¦³¦h¤Ö¼Æ¦r 1~2¡B3~7¡B8~9¡A¤½¦¡¦p¤U
=FREQUENCY({1,2,3,4,5,6,7,8,9},{2,7,9}) = {2;4;2;0}¡A³Ì«á0¬O«ü¤j©ó9¤§¼Æ


¥ÎÁ|¨Ò»¡©ú
¨Ò¤G:¨Ò¦p¥H#22µ{¦¡¤¤¤U­±³¡¤Àµ{¦¡¨Ó»¡©ú
   '  ¡õ ­pºâ¨C²Õ­Ó¼Æ¡C ª`·N! Frequency ¦^¶Ç i + 1 ²Õ¡A©Ò¥ý±N i - 1
    Bins_array = Application.Evaluate("Row(1:" & i - 1 & ")")
    ArrN = Application.Frequency(Arr, Bins_array)
    MaxN = Application.Max(ArrN)

    '  ¡õ ²Î­p¨C²Õ­Ó¼Æ¡C ª`·N! Frequency ¦^¶Ç i + 1 ²Õ¡A©Ò¥ý±N MaxN - 1
    Bins_array = Application.Evaluate("Row(1:" & MaxN - 1 & ")")
    ArrF = Application.Frequency(ArrN, Bins_array)

»¡©ú
   '  ¡õ ­pºâ¨C²Õ­Ó¼Æ¡C
1. ±N¨C²Õ³sÄò0¶i¦æ§Ç¸¹½s½X©ñ¦b°}¦C¤¤¡A°}¦C¸ê®Æ¨Ò¦p¤U
   Arr={1,1,.."X" ,2,.."X" ,3,3,.."X" ,4,4,.."X" ,5,5,5,.."X" ,6,.."X" ,7,.."X" ,8,8,.."X" ,9,9,9,.."X"}
2. §Ú·Q¨D 1¦³´X­Ó¡A2¦³´X­Ó....9¦³´X­Ó¡A¥i¥H¥Î¤U¦C¤½¦¡
   ArrN = FREQUENCY(Arr,{1,2,3,4,5,6,7,8}) = {2;1;2;2;3;1;1;2;3}
   ¦^¶Ç­Èªí¥Ü¬° 1¦³3­Ó¡B2¦³1­Ó¡B3¦³2­Ó¡B4¦³2­Ó¡B5¦³3­Ó¡B6¦³1­Ó¡B7¦³1­Ó¡B8¦³2­Ó¡B9¦³3­Ó
3. MaxN = Max({2;1;2;2;3;1;1;2;3}) = 3¡A©Ò¦³²Õ¼Æ¤¤³Ì¤j­Ó¼Æ¬° 3 («á­±­pºâ­n¥Î)
       

    '  ¡õ ²Î­p¨C²Õ­Ó¼Æ¡C
4. ¤W­±¤w¸g­pºâ¥X¨C²Õªº­Ó¼Æ°}¦C ArrN ¤Î³Ì¤j­Ó¼Æ MaxN¡A­n²Î­p¦U­Ó¼Æ¦³´X²Õ¡A¤½¦¡¦p¤U
   ArrF = FREQUENCY(ArrN,{1,2,...,MaxN-1}) = {{2;1;2;2;3;1;1;2;3},{1,2}) = {3;4;2}       
   ¦^¶Ç­Èªí¥Ü¬° 1­Ó¦³3²Õ¡B2­Ó¦³4²Õ¡B3­Ó¦³2²Õ

5. ¤½¦¡¤¤ Bins_array = Application.Evaluate("Row(1:" & i - 1 & ")") ´N¬O­n²£¥Í {1,2,...i-1} §Ç¸¹


6. ¨ä¥L»¡©ú´N­n¾a GOOGLE ¨Ó¬d¸ß¡A¦h¬Ý´X­Óºô­¶»¡©ú´N¯à©ú¥Õ¡A§Ú¤]¬O¤@Ãä¬d¤@ÃäTRY¤~¦¨¥\ªº¡C

­è±µÄ²VBA¥\¤O©|²L¡A»¡©ú¤£²M¤§³B©|¥B¨£½Ì¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 28# c_c_lai

­×¥¿­p®É¤è¦¡¿ù»~¡A±N INPUTBOX() ²¾¦Ü³Ì«e­±¡A«z! 1¬í¦h´N¯à§¹¦¨¡C
­±¿n¤j¤p¤À§G²Î­p_ML089.rar (141.68 KB)

­±¿n¤j¤p¤À§G²Î­p_ML089.rar (141.68 KB)

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD