problems29

Exercise 1Page 1Most Elementary Random Listrun VBA randomlist: press ctrl+qRuns 200Bins0 0Output 0.05 7 -p .Seed -3 Exer2Output VBA codeExer2Runs 16 0.014213 Sub Exercise2()0.36309 0.141064 N = Range(Exer2Runs).Value0.189497 ReDim Exer2Random(0 To N) As Variant0.377561 Range(Range(Exer2Output).Cells(1, 1), Range(Exer2Output).Cells(15000, 1).ClearContents0.288651 0.09331 For Index = 1 To N0.102064 If Index = 1 Then0.401299 X1 = Range(Seed) + Application.Pi()0.811799 X2 = Exp(5 + Log(X1)0.735271 Exer2Random(1) = X2 - Int(X2)0.377573 Else0.290455 X1 = Exer2Random(Index - 1) + Application.Pi()0.361072 X2 = Exp(5 + Log(X1)0.841576 Exer2Random(Index) = X2 - Int(X2)0.154677 End IfNext IndexFor Index = 1 To NRange(Exer2Output).Cells(Index, 1) = Exer2Random(Index)Next IndexEnd SubExercise 2Page 12N = Range(Exer2Runs).ValueReDim Exer2Random(0 To N) As VariantRange(Range(Exer2Output).Cells(1, 1), Range(Exer2Output).Cells(15000, 1).ClearContentsX1 = Range(Seed) + Application.Pi()Exer2Random(1) = X2 - Int(X2)X1 = Exer2Random(Index - 1) + Application.Pi()Exer2Random(Index) = X2 - Int(X2)Range(Exer2Output).Cells(Index, 1) = Exer2Random(Index)Exercise 2 (中文)Page 13运行宏, 使用 Ctrl + r. 该输出结果是基于种子的,对不同的输出, 你必须用不同的种子.确信种子 -p .Seed -3 Exer2Output VBA codeExer2Runs 16 0.014213 Sub Exercise2()0.36309 0.141064 N = Range(Exer2Runs).Value0.189497 ReDim Exer2Random(0 To N) As Variant0.377561 Range(Range(Exer2Output).Cells(1, 1), Range(Exer2Output).Cells(15000, 1).ClearContents0.288651 0.09331 For Index = 1 To N0.102064 If Index = 1 Then0.401299 X1 = Range(Seed) + Application.Pi()0.811799 X2 = Exp(5 + Log(X1)0.735271 Exer2Random(1) = X2 - Int(X2)0.377573 Else0.290455 X1 = Exer2Random(Index - 1) + Application.Pi()0.361072 X2 = Exp(5 + Log(X1)0.841576 Exer2Random(Index) = X2 - Int(X2)0.154677 End IfNext IndexFor Index = 1 To NRange(Exer2Output).Cells(Index, 1) = Exer2Random(Index)Next IndexEnd SubExercise 2 (中文)Page 14N = Range(Exer2Runs).ValueReDim Exer2Random(0 To N) As VariantRange(Range(Exer2Output).Cells(1, 1), Range(Exer2Output).Cells(15000, 1).ClearContentsX1 = Range(Seed) + Application.Pi()Exer2Random(1) = X2 - Int(X2)X1 = Exer2Random(Index - 1) + Application.Pi()Exer2Random(Index) = X2 - Int(X2)Range(Exer2Output).Cells(Index, 1) = Exer2Random(Index)Exercise 3Page 15To run the macro, use Ctrl + tExer3Runs 26 Exer3Output7E-084.9E-07 Notice that the random numbers take a 3.43E-06 while to get going! If youre using this method,0.000024 you generally start choosing random numbers0.000168 starting from the 100th number . .0.0011760.0082350.057648 Sub Exercise3()0.403536 0.824752 N = Range(Exer3Runs).Value0.773267 ReDim Exer3X(0 To N) As Double0.412872 Exer3X(0) = 10.890104 ReDim Exer3Random(0 To N) As Double0.230728 Range(Range(Exer3Output).Cells(1, 1), Range(Exer3Output).Cells(15000, 1).ClearContents0.615099 0.305696 For Index = 1 To N0.139872 Exer3X(Index) = (7 * Exer3X(Index - 1) Mod (10 8)0.979104 Exer3Random(Index) = Exer3X(Index) / 10 80.853731 Next Index0.97612 0.83284 For Index = 1 To N0.82988 Range(Exer3Output).Cells(Index, 1) = Exer3Random(Index)0.809163 Next Index0.6641440.649008 End Sub0.543056Exercise 3Page 16Brute-force spreadsheet implementationof this methodX U214 0.00000098 0.000001686 0.0000074802 0.00004833614 0.000336235298 0.0023531647086 0.01647111529602 0.11529680707214 0.80707264950498 0.64950554653486 0.54653582574402 0.82574478020814 0.78020846145698 0.46145723019886 0.23019961139202 0.61139227974414 0.27974495820898 0.95820970746286 0.70746395224002 0.95224066568014 0.665680while to get going! If youre using this method,you generally start choosing random numbersRange(Range(Exer3Output).Cells(1, 1), Range(Exer3Output).Cells(15000, 1).ClearContentsExer3X(Index) = (7 * Exer3X(Index - 1) Mod (10 8)Exer3Random(Index) = Exer3X(Index) / 10 8Range(Exer3Output).Cells(Index, 1) = Exer3Random(Index)Exercise 3Page 17Brute-force spreadsheet implementationExercise 3 (中文)Page 18运行宏, 使用 Ctrl + tExer3Runs 26 Exer3Output7E-084.9E-07 Notice that the random numbers take a 3.43E-06 while to get going! If youre using this method,0.000024 you generally start choosing random numbers0.000168 starting from the 100th number . .0.0011760.0082350.057648 Sub Exercise3()0.403536 0.824752 N = Range(Exer3Runs).Value0.773267 ReDim Exer3X(0 To N) As Double0.412872 Exer3X(0) = 10.890104 ReDim Exer3Random(0 To N) As Double0.230728 Range(Range(Exer3Output).Cells(1, 1), Range(Exer3Output).Cells(15000, 1).ClearContents0.615099 0.305696 For Index = 1 To N0.139872 Exer3X(Index) = (7 * Exer3X(Index - 1) Mod (10 8)0.979104 Exer3Random(Index) = Exer3X(Index) / 10 80.853731 Next Index0.97612 0.83284 For Index = 1 To N0.82988 Range(Exer3Output).Cells(Index, 1) = Exer3Random(Index)0.809163 Next Index0.6641440.649008 End Sub0.543056Exercise 3 (中文)Page 19Brute-force spreadsheet implementationof this methodX U214 0.00000098 0.000001686 0.0000074802 0.00004833614 0.000336235298 0.0023531647086 0.01647111529602 0.11529680707214 0.80707264950498 0.64950554653486 0.54653582574402 0.82574478020814 0.78020846145698 0.46145723019886 0.23019961139202 0.61139227974414 0.27974495820898 0.95820970746286 0.70746395224002 0.95224066568014 0.665680while to get going! If youre using this method,you generally start choosing random numbersRange(Range(Exer3Output).Cells(1, 1), Range(Exer3Output).Cells(15000, 1).ClearContentsExer3X(Index) = (7 * Exer3X(Index - 1) Mod (10 8)Exer3Random(Index) = Exer3X(Index) / 10 8Range(Exer3Output).Cells(Index, 1) = Exer3Random(Index)Exercise 3 (中文)Page 20Brute-force spreadsheet implementationExercise 4Page 21Run the macro by pressing Ctrl + wAn Excel function that produces an integer between 000 and 999 is =Int(Rand()*1000) .your daily number pick 130 1 Then GoTo start-84 0.0000 S2 = Sqr(-2 * Log(S1) / S1)-83 0.0000 X1 = rand1 * S2-82 0.0000 X2 = rand2 * S2-81 0.0000-80 0.0000 If X1 M * Q Then-77 0.0000 distribution(M) = distribution(M) + 1-76 0.0000 Else: distribution(Int(X1 / Q) = distribution(Int(X1 / Q) + 1-75 0.0000 End If-74 0.0000 -73 0.0000 If X2 M * Q Then-70 0.0000 distribution(M) = distribution(M) + 1-69 0.0001 Else: distribution(Int(X2 / Q) = distribution(Int(X2 / Q) + 1-68 0.0001 End If-67 0.0001 -66 0.0001 Next Index-65 0.0001-64 0.0001 For Index = -M To M-63 0.0002 Range(bin).Cells(Index + M, 1) = Index-62 0.0002 Range(output).Cells(Index + M, 1) = distribution(Index) / (2 * N)-61 0.0002 Next Index-60 0.0003-59 0.0003 Range(stoptime) = Time-58 0.0003-57 0.0003 End Sub-56 0.0004-55 0.0005-54 0.0006-53 0.0006-52 0.0007-51 0.0008-50 0.0009-49 0.0010-48 0.0012-47 0.0013-46 0.0015-45 0.0016-44 0.0019-43 0.0021-42 0.0023-41 0.0026-40 0.0029-39 0.0031-38 0.0033-37 0.0038-36 0.0041-35 0.0045-34 0.0049-33 0.0054-32 0.0059-31 0.0064-30 0.0066-29 0.0073-28 0.0078-27 0.0083-26 0.0089-25 0.0094-24 0.0100-23 0.0104-22 0.0112-21 0.0117-20 0.0124-19 0.0130-18 0.0137-17 0.0144-16 0.0148-15 0.0152-14 0.0159-13 0.0164-12 0.0171-11 0.0175-10 0.0178-9 0.0183-8 0.0186-7 0.0187-6 0.0192-5 0.0197-4 0.0197-3 0.0199-2 0.0198-1 0.02000 0.01991 0.01992 0.01973 0.01964 0.01955 0.01926 0.01887 0.01868 0.01809 0.017710 0.017411 0.016912 0.016513 0.015914 0.015415 0.014816 0.014217 0.013618 0.012819 0.012320 0.011821 0.011122 0.010623 0.009924 0.009525 0.008826 0.008427 0.007728 0.007229 0.006830 0.006331 0.005932 0.005233 0.005034 0.004535 0.004236 0.003837 0.003438 0.003139 0.002940 0.002541 0.002442 0.001943 0.001944 0.001745 0.001646 0.001447 0.001148 0.001049 0.000950 0.000851 0.000752 0.000653 0.000654 0.000555 0.000456 0.000457 0.000358 0.000359 0.000260 0.000261 0.000262 0.000263 0.000164 0.000165 0.000166 0.000167 0.000168 0.000169 0.000070 0.000071 0.000072 0.000073 0.000074 0.000075 0.000076 0.000077 0.000078 0.000079 0.000080 0.000081 0.000082 0.000083 0.000084 0.000085 0.000086 0.000087 0.000088 0.000089 0.000090 0.000091 0.000092 0.000093 0.000094 0.000095 0.000096 0.000097 0.000098 0.000099 0.0000100 0.0000Frequency Distribution150100500-50-100-1500.00000.00500.01000.01500.02000.0250binTo run m acro, press this buttonUN-18F利用模拟正态分布的VBA注意这里的 Application.screenupdating 有很大的不同!THE VBA ProgramSub Exercise5()Runs 600,000 starttime 9:40:17 Range(starttime) = TimeNo. bins 100 stoptime 9:40:20Bin size 0.05 elapsed 0:00:03 Application.screenupdating = Falsebin output N = Range(runs).Value-100 0.0000 M = Range(No._bins).Value-99 0.0000 Q = Range(bin_size).Value-98 0.0000-97 0.0000 Range(Range(bin).Cells(1, 1), Range(bin).Cells(15000, 1).ClearContents-96 0.0000 Range(Range(output).Cells(1, 1), Range(output).Cells(15000, 1).ClearContents-95 0.0000-94 0.0000 ReDim sample(2 * N) As Double-93 0.0000 ReDim distribution(-M To M) As Integer-92 0.0000-91 0.0000 For Index = 1 To N-90 0.0000 start:-89 0.0000 Static rand1, rand2, S1, S2, X1, X2-88 0.0000 rand1 = 2 * Rnd - 1-87 0.0000 rand2 = 2 * Rnd - 1-86 0.0000 S1 = rand1 2 + rand2 2-85 0.0000 If S1 1 Then GoTo start-84 0.0000 S2 = Sqr(-2 * Log(S1) / S1)-83 0.0000 X1 = rand1 * S2-82 0.0000 X2 = rand2 * S2-81 0.0000-80 0.0000 If X1 M * Q Then-77 0.0000 distribution(M) = distribution(M) + 1-76 0.0000 Else: distribution(Int(X1 / Q) = distribution(Int(X1 / Q) + 1-75 0.0000 End If-74 0.0000 -73 0.0000 If X2 M * Q Then-70 0.0000 distribution(M) = distribution(M) + 1-69 0.0001 Else: distribution(Int(X2 / Q) = distribution(Int(X2 / Q) + 1-68 0.0001 End If-67 0.0001 -66 0.0001 Next Index-65 0.0001-64 0.0001 For Index = -M To M-63 0.0002 Range(bin).Cells(Index + M, 1) = Index-62 0.0002 Range(output).Cells(Index + M, 1) = distribution(Index) / (2 * N)-61 0.0002 Next Index-60 0.0003-59 0.0003 Range(stoptime) = Time-58 0.0003-57 0.0003 End Sub-56 0.0004-55 0.0005-54 0.0006-53 0.0006-52 0.0007-51 0.0008-50 0.0009-49 0.0010-48 0.0012-47 0.0013-46 0.0015-45 0.0016-44 0.0019-43 0.0021-42 0.0023-41 0.0026-40 0.0029-39 0.0031-38 0.0033-37 0.0038-36 0.0041-35 0.0045-34 0.0049-33 0.0054-32 0.0059-31 0.0064-30 0.0066-29 0.0073-28 0.0078-27 0.0083-26 0.0089-25 0.0094-24 0.0100-23 0.0104-22 0.0112-21 0.0117-20 0.0124-19 0.0130-18 0.0137-17 0.0144-16 0.0148-15 0.0152-14 0.0159-13 0.0164-12 0.0171-11 0.0175-10 0.0178-9 0.0183-8 0.0186-7 0.0187-6 0.0192-5 0.0197-4 0.0197-3 0.0199-2 0.0198-1 0.02000 0.01991 0.01992 0.01973 0.01964 0.01955 0.01926 0.01887 0.01868 0.01809 0.017710 0.017411 0.016912 0.016513 0.015914 0.015415 0.014816 0.014217 0.013618 0.012819 0.012320 0.011821 0.011122 0.010623 0.009924 0.009525 0.008826 0.008427 0.007728 0.007229 0.006830 0.006331 0.005932 0.005233 0.005034 0.004535 0.004236 0.003837 0.003438 0.003139 0.002940 0.002541 0.002442 0.001943 0.001944 0.001745 0.001646 0.001447 0.001148 0.001049 0.000950 0.000851 0.000752 0.000653 0.000654 0.000555 0.000456 0.000457 0.000358 0.000359 0.000260 0.000261 0.000262 0.000263 0.000164 0.000165 0.000166 0.000167 0.000168 0.000169 0.000070 0.000071 0.000072 0.000073 0.000074 0.000075 0.000076 0.000077 0.000078 0.000079 0.000080 0.000081 0.000082 0.000083 0.000084 0.000085 0.000086 0.000087 0.000088 0.000089 0.000090 0.000091 0.000092 0.000093 0.000094 0.000095 0.000096 0.000097 0.000098 0.000099 0.0000100 0.0000Frequency Distribution150100500-50-100-1500.00000.00500.01000.01500.02000.0250binTo run m acro, press this buttonUN-18FSimulating the Normal Distribution using VBAnote that here Application.screenupdating makes a big difference!Runs 200,000 starttime 9:40:12 VBA codeMean 2 stoptime 9:40:14 Sub Exercise6()Sigma 3 elapsed 0:00:02 Range(starttime) = TimeApplication.screenupdating = FalseBin Output ReDim sample(2 * N) As Double-1 0.0017 Dim distribution(-30 To 30) As Integer-0.9 0.0007-0.8 0.0009 N = Range(runs).Value-0.7 0.0012 mu = Range(Mean).Value-0.6 0.0015 sigma = Range(Sigma).Value-0.5 0.0020 -0.4 0.0025 For Index = 1 To N-0.3 0.0031 start:-0.2 0.0040 Static rand1, rand2, S1, S2, X1, X2-0.1 0.0049 rand1 = 2 * Rnd - 10 0.0057 rand2 = 2 * Rnd - 10.1 0.0073 S1 = rand1 2 + rand2 20.2 0.0089 If S1 1 Then GoTo start0.3 0.0101 S2 = Sqr(-2 * Log(S1) / S1)0.4 0.0120 X1 = rand1 * S2 * sigma + mean0.5 0.0139 X2 = rand2 * S2 * sigma + mean0.6 0.0160 0.7 0.0184 If (X1 - mean) / sigma 3 Then1 0.0253 distribution(30) = distribution(30) + 11.1 0.0279 Else: distribution(Int(X1 - mean) / sigma / 0.1) = distribution(Int(X1 - mean) / sigma / 0.1) + 11.2 0.0300 End If1.3 0.0324 1.4 0.0343 If (X2 - mean) / sigma 3 Then1.7 0.0385 distribution(30) = distribution(30) + 11.8 0.0395 Else: distribution(Int(X2 - mean) / sigma / 0.1) = distribution(Int(X2 - mean) / sigma / 0.1) + 11.9 0.0398 End If2 0.0396 2.1 0.0392 Next Index2.2 0.03842.3 0.0374 For Index = -30 To 302.4 0.0355 Range(bin).Cells(Index + 31, 1) = Index * 0.1 + mu2.5 0.0342 Range(output).Cells(Index + 31, 1) = distribution(Index) / (2 * N)2.6 0.0323 Next Index2.7 0.03012.8 0.02822.9 0.02563 0.02313.1 0.02043.2 0.01853.3 0.01633.4 0.01393.5 0.01213.6 0.01053.7 0.00843.8 0.00713.9 0.00594 0.00484.1 0.00404.2 0.00334.3 0.00264.4 0.00214.5 0.00164.6 0.00114.7 0.00094.8 0.00074.9 0.00055 0.00146420- 20 .0 0 0 00 .0 0 5 00 .0 1 0 00 .0 1 5 00 .0 2 0 00 .0 2 5 00 .0 3 0 00 .0 3 5 00 .0 4 0 00 .0 4 5 0T o ru n E x e rc is e 6 , p re s s th is b u tto nUN-18F利用模拟正态分布的VBA注意这里的 Application.screenupdating 有很大的不同!Runs 200,000 starttime 9:40:12 VBA codeMean 2 stoptime 9:40:14 Sub Exercise6()Sigma 3 elapsed 0:00:02 Range(starttime) = TimeApplication.screenupdating = FalseBin Output ReDim sample(2 * N) As Double-1 0.0017 Dim distribution(-30 To 30) As Integer-0.9 0.0007-0.8 0.0009 N = Range(runs).Value-0.7 0.0012 mu = Range(Mean).Value-0.6 0.0015 sigma = Range(Sigma).Value-0.5 0.0020 -0.4 0.0025 For Index = 1 To N-0.3 0.0031 start:-0.2 0.0040 Static rand1, rand2, S1, S2, X1, X2-0.1 0.0049 rand1 = 2 * Rnd - 10 0.0057 rand2 = 2 * Rnd - 10.1 0.0073 S1 = rand1 2 + rand2 20.2 0.0089 If S1 1 Then GoTo start0.3 0.0101 S2 = Sqr(-2 * Log(S1) / S1)0.4 0.0120 X1 = rand1 * S2 * sigma + mean0.5 0.0139 X2 = rand2 * S2 * sigma + mean0.6 0.0160 0.7 0.0184 If (X1 - mean) / sigma 3 Then1 0.0253 distribution(30) = distribution(30) + 11.1 0.0279