[练习]数组随机数竟赛题

HYY514

<p>刚学完数组出个练习题:</p><p>用VBA在A1:A65536 单元格产生<font color="#ee1111">不重复</font><font color="#000000">随机数</font>,随机数范围1至65536,包括1和65536,</p><p><font color="#2222dd">运行时间少于1秒钟的,金子奖励.</font></p><p><font color="#2222dd">以回复可见方式进行回复,</font></p><p><font color="#2222dd">参考答案在结贴时会发布.</font></p><p><font color="#2222dd"></font>&nbsp;-------------------------------------------------------</p><p><font color="#d52b6f">呵呵,履行诺言结帖,代码运行在一秒内的到这里领BB(本来想把此帖直接编辑成赠送帖却不行)</font></p><p><font color="#d52b6f">依代码运行效率论赏,不报到者,即为自动弃权,哈哈[em07][em01]</font></p><p><font color="#d52b6f"></font>&nbsp;</p><p><a href="http://excelpx.com/dispbbs.asp?boardid=99&amp;id=58968">http://excelpx.com/dispbbs.asp?boardid=99&amp;id=58968</a></p><p><font color="#2222dd"></font>---------------------------------------------------------&nbsp;</p><p><font color="#2222dd"></font>&nbsp;练习题原本是发给VBA入门班的,既然转到这里来了,</p><p>也把我原来的代码发上来,欢迎分享更好的思路:</p><p>Sub fx1()<br/>Dim a(1 To 65536, 0) As Long<br/>Dim tmp As Long<br/>Dim n As Long<br/>Dim i As Long<br/>Dim t As Double<br/>t = Timer</p><p>For i = 1 To 65536<br/>&nbsp;&nbsp;&nbsp; a(i, 0) = i<br/>Next<br/>For i = 1 To 65536<br/>&nbsp;&nbsp;&nbsp; n = Int(Rnd * 65536 + 1)<br/>&nbsp;&nbsp;&nbsp; tmp = a(i, 0)<br/>&nbsp;&nbsp;&nbsp; a(i, 0) = a(n, 0)<br/>&nbsp;&nbsp;&nbsp; a(n, 0) = tmp<br/>Next<br/>Range("a1:a65536") = a<br/>MsgBox Timer - t<br/>End Sub</p><p>Sub fx2()<br/>Dim a(1 To 65536, 0) As Long<br/>Dim tmp As Long<br/>Dim n As Long<br/>Dim i As Long<br/>Dim t As Double<br/>t = Timer<br/>For i = 1 To 65536<br/>&nbsp;&nbsp;&nbsp; n = Int(Rnd * 65536 + 1)<br/>&nbsp;&nbsp;&nbsp; tmp = IIf(a(i, 0) = 0, i, a(i, 0))<br/>&nbsp;&nbsp;&nbsp; a(i, 0) = IIf(a(n, 0) = 0, n, a(n, 0))<br/>&nbsp;&nbsp;&nbsp; a(n, 0) = tmp<br/>Next<br/>[a1:a65536] = a<br/>MsgBox Timer - t<br/>End Sub</p><p><br/>'另:<br/>Sub 检验数据是否有重复()<br/>Dim d As Object<br/>Dim i As Long<br/>Dim bol As Boolean<br/>Dim ar, s As String<br/>ar = [a1:a65536]<br/>Set d = CreateObject("scripting.dictionary")<br/>For i = 1 To 65536<br/>&nbsp;&nbsp;&nbsp; s = ar(i, 1)<br/>&nbsp;&nbsp;&nbsp; If s &lt;&gt; "" Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If d(s) = "" Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; d(s) = 1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Else<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; d(s) = d(s) + 1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox s &amp; "重复" &amp; vbCrLf &amp; d(s) &amp; "个"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bol = True<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If s &gt; 65536 Or s &lt;= 0 Then MsgBox s &amp; vbCrLf &amp; "随机数范围错误!"<br/>&nbsp;&nbsp;&nbsp; End If<br/>Next<br/>&nbsp;If Not (bol) Then MsgBox "没有重复"<br/>End Sub<br/></p>

[此贴子已经被作者于2008-6-27 21:47:33编辑过]

杰

<p>前两天兰版刚出过一个题和这个几乎一样......</p><p>&nbsp;</p><p>----------------------------------------------------</p><p><font color="#4822dd">VBA应用数组的目的是什么----让你的程式更有效率.</font></p><p><font color="#4822dd">此帖要考验的就是你如何应用数组让你的程序更有效率,所以强调一个运行时间,</font></p><p><font color="#4822dd">在整个A列的大范围产生随机数.兰版该帖子不要求你使用数组不要求效率,只要求用简单的代码完成目的,在这一点上是有很大区别的.</font></p><p><font color="#2222dd">&nbsp;如果你把</font><font color="#4822dd">兰版该帖的简单代码扩展到整个A列,运行所花的时间是惊人的.</font></p>

[此贴子已经被HYY514于2008-6-25 23:22:30编辑过]

杰

[em04][em04][em04]

ycb20010823

数组知识还没有消化呢，我想做不出来这样的题！

rqan

<p>不好意思,要1分多钟</p><p>找到时间长的原因了,只要把红色赋值部分移到循环外,时间也可控制在1秒之内~</p><p>

游客，如果您要查看本帖隐藏内容请回复

[此贴子已经被作者于2008-6-26 15:23:13编辑过]

cjch1969

<p><font color="#ff0000" size="5">生成数据用时&nbsp;&lt; 1秒</font></p><p>

游客，如果您要查看本帖隐藏内容请回复

</p>

cjch1969

<p>附件上传</p><p>&nbsp;

游客，如果您要查看本帖隐藏内容请回复

mousewy

学习一下

Qinjabil

<p>学习</p>

qixizhuzhi

学习