Excel生成概率分布表

1、 利用Excel提供的統(tǒng)計(jì)函數(shù)“NORMSDIST”，可以生成標(biāo)準(zhǔn)正態(tài)分布累積概率分布表,即生成標(biāo)準(zhǔn)正態(tài)分布累積概率分布表的具體操作步驟如下。 第1步：將工的值（可根據(jù)需要確定）輸入到工作表的A列.將1取值的尾數(shù)輸入到第1行.形成標(biāo)準(zhǔn)正態(tài)分布表的表頭，如下表所示。 A A B c D E F FI G G H I I T KI 1 1 X X 0.000.00 0.010.01 0.020.02 0.030.03 0.040.04 0.050.05 0.060.06 0.070.07 0.080.08 0.090.09 2 0.0 3 11 4 0.2 5 0.30.3 6 0.40.4 7

2、7 0.5 8 0.6 9 t.7 10 0.80.8 11 0.90.9 12 id 第2步：在B2單元格輸入公式“=NORMSDIST$A2+B$1）”,然后將其向下、向右復(fù)制即可得到標(biāo)準(zhǔn)正態(tài)分布概率表.部分結(jié)果如下表所示（讀者可根據(jù)需要牛.成不同工的標(biāo)準(zhǔn)正態(tài)分布概率表九 A B C D E F G H I 一J K 1 X 0.000.00 0.010.01 0.020.02 0.030.03 0.040.04 0.050.05 o.oeo.oe 0.070.07 0.080.08 0.090.09 2 0.00.0 05000 05040 0.5080 0.5120 0.5160 0.

3、5199 05239 05279 0.5319 0.5359 3 M 0.5398 0.5438 0.5478 0.5517 0.5557 0.5596 0.5636 0.5675 0.5714 0.5753 4 U 0.5793 0.5832 0.5871 0.5910 0.5948 0.5987 0.6026 0.6064 0.6103 0.6141 5 0.30.3 0.6179 06217 0.6255 0.6293 0.6331 0.6368 0.6406 0.6443 0.6480 0.6517 6 0.40.4 06554 06591 0.6628 0.6664 0.6700 0

4、.6736 0.6772 06808 06844 0.6879 7 0.50.5 0.6915 0.6950 0.6985 0.7019 0.7054 0.7088 0.7123 0.7157 0.7190 0.7224 8 0.60.6 0.7257 0.7291 0.7324 0.7357 0.7389 0.7422 0.7454 0.7486 0.7517 0.7549 9 0.70.7 0.7580 0.7611 0.7642 0.7673 0.7704 0.7734 0.7764 0.7794 0.7823 0.7852 10 0.80.8 07881 07910 0.7939 07

5、967 07995 08023 08051 0.8078 0.8106 08133 11 0.90.9 0.8159 0.8186 0.8212 0.8238 0.8264 0.8289 0.8315 0.8340 0.8365 0.8389 12 1.01.0 0.8413 0.8438 0.8461 0.8485 0.8508 0.8531 0.8554 0.8577 0.8599 0.8621 13 1.11.1 0.8643 0.8665 0.8686 0.8708 0.8729 0.8749 0.8770 0.8790 0.8810 0.8830 14 1.21.2 08849, 0

6、.8869 0.8888 0.8907 0.8925 0.8944 08962 0.8980 0.8997 0.9015 15 1.31.3 0.9032 0.9049 0.9066 0.9082 0.9099 0.9115 0.9131 0.9147 0.9162 0.9177 16 1.41.4 0.9192 09207 0.9222 0.9236 0.9251 0.9265 0.9279 0.9292 0.9306 0.9319 17 1.51.5 0.9332 0.9345 0.9357 0.9370 0.9382 0.9394 0.9406 0.9418 0.9429 0.9441

7、18 1.61.6 0.9452 0.9463 0.9474 0.9484 0.9495 0.9505 0.9515 0.9525 0.9535 0.9545 19 1.7 0.9554 09564 0.9573 0.9582 0.9591 0.9599 0.9608 0.9616 0.9625 0.9633 20 1.81.8 0.9641 09649 0.9656 0.9664 0.9671 0.9678 09686 0.9693 0.9699 0.9706 21 1.91.9 0.9713 0.9719 0.9726 0.9732 0.9738 0.9744 0.9750 0.9756

8、0.9761 0.9767 22 2.02.0 0.9772 09778 0.9783 0.9788 0.9793 0.9798 09803 0.9808 0.9812 0.9817 23 2.1 0.9821 09826 0.9830 0.9834 0.9838 0.9842 0.9846 0.9850 0.9854 0.9857 24 2.2 0.9861 0.9864 0.9868 0.9871 0.9875 0.9878 0.9881 0.9884 0.9887 0.9890 25 2.3 0.9893 09896 0.9898 0.9901 0.9904 0.9906 0.9909

9、0.9911 0.9913 0.9916 26 2.42.4 0.9918 0.9920 0.9922 0.9925 0.9927 0.9929 0.9931 0.9932 0.9934 0.9936 27 2.52.5 0.9938 09940 0.9941 0.9943 0.9945 0.9946 0.9948 0.9949 0.9951 0.9952 28 2.62.6 0.9953 0.9955 0.9956 0.9957 0.9959 0.9960 0.9961 0.9962 0.9963 0.9964 29 2.72.7 0.9965 0.9966 0.9967 0.9968 0.

10、9969 0.9970 0.9971 0.9972 0.9973 0.9974 30 2.82.8 0.9974 0.9975 0.9976 0.9977 0.9977 0.9978 0.9979 0.9979 0.9980 0.9981 31 2.92.9 0.9981 0.9982 0.9982 0.9983 0.9984 0.9984 0.9985 0.9985 0.9986 0.9986 32 3.03.0 0.9987 0.9987 0.9987 0.9988 0.9988 0.9989 0.9989 0.9989 0.9990 0.9990 33 3.13.1 0.9990 0.9

11、991 0.9991 0.9991 0.9992 0.9992 0.9992 0.9992 0.9993 0.9993 34 3.2 0.9993 09993 0.9994 0.9994 0.9994 0.9994 0.9994 0.9995 0.9995 0.9995 35 3.33.3 0.9995 0.9995 0.9995 0.9996 0.9996 0.9996 0.9996 0.9996 0.9996 0.9997 36 3.43.4 0.9997 09997 0.9997 0.9997 0.9997 0.9997 0.9997 0.9997 0.9997 0.9998 37 3.

12、53.5 0.9998 0.9998 0.9998 0.9998 0.9998 0.9998 0.9998 0.9998 0.9998 0.9998 38 3.63.6 nq8 AAAAA nqqqq n AAAAA n nAAAA HAAAA flAAAA nqqqq 39 3.73.7 0.9999 09999 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 40 3.83.8 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 0.9999 41 3.

13、93.9 1.0000 10000 1.0000 1.0000 1.0000 1OOOO 1OOOO 1.0000 1.0000 1.0000 42 4040 1OOOO 10000 1.0000 1.0000 -10000 1.0000 1OOOO 1.0000 1.0000 1.0000 利用Excel提供的統(tǒng)計(jì)函數(shù)“NORMSINV”,可以生成標(biāo)準(zhǔn)正態(tài)分布分位數(shù)表.該表是根據(jù)標(biāo)準(zhǔn)正態(tài)分布隨機(jī)變量分布的累積概率的值計(jì)算的相應(yīng)的臨界值，如果有P(Z4r)=p.則對(duì)于任意給定的戶(0力(1).可以求出相應(yīng)的也用Excel生成林準(zhǔn)正態(tài)分布分位數(shù)表的具體操作步驟如下。 第1步：將標(biāo)準(zhǔn)正態(tài)變量累積概

14、率的值輸入到工作表的A列，其尾數(shù)輸人到第1行.形成標(biāo)準(zhǔn)正態(tài)分布分位數(shù)表的表頭，如下表所示。 A B C D E F G H I T K 1 P 0.000 0.001 O.tO2 0.003 0.004 0.005 0.006 0.007 0.008 0.009 2 0.50 3 0.51 4 0.52 5 0.53 6 0.54 7 0.55 8 0.56 9 0.57 10 0.58 11 0.59 12 0.60 13 0.1 14 02 15 0.S3 16 044 17 045 18 0.66 19 07 20 0.68 21 0.69 22 0.70 23 0.71 第2步：在B2

15、單元格輸入公式“=NORMSINV($A2+B$D”，然后將其向下、向右復(fù)制即可得到標(biāo)準(zhǔn)正態(tài)分布分位數(shù)表部分結(jié)果如下表所示(讀者可根據(jù)需要生成不同夕值的標(biāo)準(zhǔn)正態(tài)分布分位數(shù)表)。1 p 0.0000.000 0.0010.001 0.0020.002 0.0030.003 0.0040.004 0.0050.005 0.0060.006 0.007 0.0080.008 0.0090.009 2 050050 0.0000 0.0025 0.0050 0.0075 0.0100 0.0125 0.0150 0.0175 0.0201 0.0226 3 0.510.51 0.0251 0.0276

16、 0.0301 0.0326 0.0351 0.0376 0.0401 0.0426 0.0451 0.04761 4 0.520.52 00502 0.0527 0.0552 0.0577 0.0602 0.0627 0.0652 0.0677 00702 0.07281 5 0.530.53 00753 0.0778 0.0803 0.0828 0.0853j 0.0878 0.0904 0.0929 00954 0.09791 6 0.540.54 0.1004 0.1030 0.1055 0.1080 0.1105 0.1130 0.1156 0.1181 0.1206 0.12311

17、 7 0.550.55 01257 0.1282 0.1307 0.1332 0.1358 0.1383 0.1408 0.1434 0.1459 0.1484 8 0.560.56 0.1510 0.1535 0.1560 0.1586 0.1611! 0.1637 0.1662 0.1687 0.1713 0.1738 9 0.570.57 01764 0.1789 0.1815 0.1840 0,1866 0.1891 0.1917 0.1942 01968 0.1993 10 0.580.58 0.2019 0.2045 0.2070 0.2096 0.2121j 0.2147 0.2

18、173 0.2198 0.2224 0.22501 11 0.590.59 02275 0.2301 0.2327 0.2353 02378 0.2404 0.2430 0.2456 0.2482 0.2508 12 o.eoo.eo 02533 02559 0.2585 02611 02637 02663 0.2689 0.2715 02741 0.2767 13 0.C10.C1 02793 0.2819 0.2845 0.2871 0.2898 0.2924 0.2950 0.2976 03002 0.30291 R0.620.62 03055 0.3081 0.3107 0.3134

19、0.3160 0.3186 0.3213 0.3239 03266 0.32921 15 0.630.63 03319 0.3345 0.3372 0.3398 03425J 0.3451 0.3478 0.3505 03531 0.35581 16 0.640.64 03585 0.3611 0.3638 0.3665 0.3692 0.3719 0.3745 0.3772 0.3799 0.3826 17 0.650.65 03853 0.3880 0.3907 0.3934 0,3961 0.3989 0.4016 0.4043 04070 0.4097 18 0.660.66 0412

20、5 0.4152 0.4179 0.4207 04234 04261 0.4289 0.4316 04344 0,4372 19 0.670.67 04399 0.4427 0.4454 0.4482 0.4510J 0.4538 0.4565 0.4593 04621 0.4649 20 0.680.68 04677 0.4705 0.4733 0.4761 0.4789j 0.4817 0.4845 0.4874 04902 0.4930 21 0.690.69 04959 0.4987 0.5015 0.5044 0,5072 0.5101 0.5129 0.5158 05187 0.5

21、215 22 0.700.70 05244 0.5273 0.5302 0.5330 0.5359 0.5388 0.5417 0.5446 05476 0.5505 23 0.710.71 05534 0.5563 0.5592 0.5622 0.5651 0.5681 0.5710 0.5740 05769 O.5799 24 0.720.72 0.5828 0.5858 0.5888 0.5918 0.5948 0.5978 0.6008 0.6038 0.6068 0.6098 25 0.730.73 0.6128 0.6158 0.6189 0.6219 0.6250 0.6280

22、0.6311 0.6341 0.6372 0.6403 26 0.740.74 06433 0.6464 0.6495 0.6526 0.6557 0.6588 0.6620 0.6651 0.6682 0.6713 27 0.750.75 06745 0.6776 0.6808 0.6840 0.6871 0.6903 0.6935 0.6967 0.6999 0.7031 28 0.760.76 07063 0.7095 07128 0.7160 0.7192 0.7225 0.7257 0.7290 0.7323 0.7356 29 0.770.77 07388 0.7421 0.745

23、4 0.7488 0.7521 0.7554 0.7588 0.7621 0.7655 0.7688 30 0.70.7 07722 07756 07790 07824 0.7858 0.7892 07926 07961 0.7995 0.8030 31 0.790.79 08064 0.8099 0.8134 0.8169 0.8204 0.8239 0.8274 0.8310 0.8345 0.8381 32 0.800.80 08416 0.8452 0.8488 0.8524 0.8560 0.8596 0.8633 0.8669 0.8705 0.8742 33 0.810.81 0

24、8779 0.8816 0.8853 0.8890 0.8927 0.8965 0.9002 09040 0.9078 0.9116 34 0.820.82 09154 09192 09230 0.9269 0.9307 0.9346 0.9385 09424 0.9463 09502 35 0.830.83 09542 0.9581 0.9621 0.9661 0.9701 0.9741 0.9782 09822 09863 0.9904 36 0.840.84 09945 0.9986 10027 1.0069 1.0110 1.0152 1.0194 10237 10279 10322

25、37 0.850.85 10364 1.0407 1.0450 1.0494 1.0537 1.0581 1.0625 1.0669 1.0714 1.0758 38 0.860.86 10803 1.0848 1.0893 1.0939 1.0985 1.1031 1.1077 1.1123 1.1170 1.1217 39 0.870.87 11264 1.1311 1.1359 1.1407 1.1455 1.1503 1.1552 1.1601 1.1650 1.1700 40 0.880.88 1.1750 1.1800 1.1850 1.1901 1.1952 1.2004 1.2

26、055 1.2107 1.2160 1.2212 41 0.890.89 12265 1.2319 1.2372 1.2426 1.2481 1.2536 1.2591 1.2646 1.2702 1.2759 10.900.90 12816 1.2873 1.2930 1.2988 1.3047 1.3106 1.3165 1.3225 1.3285 1.3346 43 0.91.0.91. 13408 1.3469 1.3532 1.3595 1.3658 1.3722 1.3787 13852 13917 13984 44 0.920.92 14051 1.4118 1.4187 1.4

27、255 1.4325 1.4395 1.4466 1.4538 1.4611 1.4684 45 0.930.93 14758 1.4833 1.4909 1.4985 1.5063 1.5141 1.5220 1.5301 1.5382 1.5464 46 0.940.94 15548 1.5632 1.5718 1.5805 1.5893 1.5982 1.6072 1.6164 1.6258 1.6352 47 0.950.95 16449 1.6546 1.6646 1.6747 1.6849 1.6954 1.7060 1.7169 1.7279 1.7392 48 0.960.96

28、 1.7507 1.7624 1.7744 1.7866 1.79911 1.8119 1.8250 1.8384 1.8522 1.8663 49 0.970.97 18808 1.8957 1.9110 1.9268 1.9431 1.9600 1.9774 1.9954 2.0141 2.0335 50 0.980.98 20537 2.0749 2.0969 2.1201 2.1444 2.1701 2.1973 22262 2.2571 22904 利用Excel提供的統(tǒng)計(jì)函數(shù)“TINV、可以構(gòu)建/分布臨界值表該表是根據(jù)/分布的右尾概率a計(jì)算的相應(yīng)的臨界值。如果P(2z)=a,則對(duì)于

29、任意給定的概率p(Oa1 14.4578 15.6555 17.5387 19.2806 21.4336 41.4217 44.9853 48.2319 52.1914 55.0027 33 3232 15,1340 163622 18.2908 20,0719 222706 42.5847 46.1943 49,4804 534858 56,3281 34 3333 15.8153 17.0735 190467 20,8665 23.1102 43.7452 47.3999 50.7251 547755 57,6484 35 3434 165013 177891 198063 21.6643

30、 239523 44.9032 48,6024 51.9660 560609 58.9639 36 3535 17,1918 185089 20.5694 22.4650 247967 46.0588 49.8018 53.2033 57.3421 60.2748 37 3636 17.8867 19.2327 21.3359 23.2686 25.6433 47.2122 50.9985 54.4373 58.6192 61.5812 38 3737 18.5858 19.9602 22.1056 24,0749 264921 48.3634 52.1923 55.6680 598925 6

31、2.8833 39 3S 19.2889 20.6914 22.8785 24.8839 27.3430 49.5126 53.3835 56.8955 61.1621 64.1814 40 3939 19.9959 21.4262 23.6543 25.6954 28.1958 50.6598 54.5722 58.1201 624281 65.4756 41 4040 20.7065 22.1643 24.4330 26.5093 29.0505 51.8051 55.7585 59.3417 63.6907 66.7660 42 4141 21.4208 22.9056 25.2145

32、27.3256 299071 52.9485 56.9424 60.5606 649501 68.0527 43 4242 22.1385 23.6501 25.9987 28.1440 30.7654 54.0902 58.1240 61.7768 66,2062 69.3360 44 4 22.8595 243976 26.7854 28.9647 31.6255 55.2302 59.3035 62.9904 674593 70.6159 45 4444 23.5837 25.1480 27.5746 29.7875 32.4871 56.3685 60.4809 64.2015 68.

33、7095 71.8926 46 4S 24.3110 25,9013 28.3662 30.6123 333504 57.5053 61.6562 65.4102 699568 73.1661 4646 25.0413 266572 29.1601 31,4390 342152 58.6405 62.8296 66.6165 71.2014 74,4365 利用Excel提供的統(tǒng)計(jì)函數(shù)“FINV”，可以構(gòu)建戶分布臨界值表， 該表是根據(jù)F分布的右尾概率a計(jì)算的相應(yīng)的臨界值。如果P(F?i)=a,則對(duì)于任意給定的概率可以求出相應(yīng)的工。生成F分布臨界值表的具體操作步驟如下。 第1步：在B1單元格輸入

34、F分布右尾概率a的取值(如a=0.05),在第2行輸入分子自由度d/1的值.在第1列輸入分母自由度d#的值,如下表所示。 A B C D E F G H I J K 1 a- o.os 2 2 Clf?tf1 1 2 3 4 5 6 7 8 9 11 3 1 4 2 5 3 6 4 7 7 6 8 6 9 7 10 8 19 1u 111 112 113 114 1 1J 117 21S 2 215 22t 第2步：在B3單元格輸入公式“=F1NV($B$LB$2,$A3)W.然后將其向下、向右復(fù)制即可得到F分布臨界值表,a=0.05時(shí)F分布臨界值表的部分結(jié)果如下表所示(讀者可根據(jù)需要生成不同

35、a和不同自由度的P分布臨界值表)。 A B c D E F G H I J K 1 a= 0.05 2 df2clf1 1 2 3 4 5 6 7 8 9 io21 3 1 161.448 199.500 215.707 224583 230.162 233.986 236.768 238.883 240.543 241.882 4 2 18,513 19000 19164 19247 19296 19330 19353 19371 19385 19.396 5 3 10.128 9.552 9277 9.117 9.013 8.941 8.887 8.845 8.812 8.786 6 4

36、7.709 6.944 6.591 6388 6.256 6.163 6.094 6.041 5.999 5.964 7 5 5 6.608 5.786 5409 5.192 5.050 4.950 4.876 4.818 4.772 4.735 8 6 5.987 5.143 4.757 4.534 4.387 4.284 4.207 4.147 4.099 4.060 9 7 5.591 4.737 4.347 4.120 3.972 3.866 3.787 3.726 3.677 3.637 10 8 5.318 4459 4066 3838 3687 3.581 3.500 3438

37、3.388 3347 11 9 5.117 4.256 3.863 3.633 3.482 3.374 3.293 3.230 3.179 3.137 12 TO 4.965 4.103 3708 3478 3.326 3.217 3.135 3.072 3.020 2.978 13 11 4.844 3.982 3.587 3.357 3.204 3.095 3.012 2.948 2.896 2.854 14 12 4.747 3.885 3.490 3.259 3.106 2.996 2.913 2.849 2.796 2.753 15 1313 4.667 3.806 3411 3.1

38、79 3.025 2.915 2.832 2.767 2.714 2.671 16 14 4.600 3.739 3.344 3.112 2.958 2.848 2.764 2.699 2.646 2.602 17 15 4.543 3.682 3287 3056 2901 2.790 2707 2641 2588 2544 18 1C 4.494 3.634 3239 3.007 2.852 2.741 2.657 2.591 2.538 2.494 19 17 4.451 3.592 3.197 2965 2.810 2.699 2.614 2.548 2.494 2.450 20 18

39、4.414 3.555 3.160 2.928 2.773 2.661 2.577 2.510 2.456 2.412 21 4.381 3.522 3.127 2895 2.740 2.628 2.544 2.477 2.423 2.378 22 20 4.351 3.493 3.098 2.866 2.711 2.599 2.514 2.447 2.393 2.348 23 21 4325 3467 3072 2840 2685 2.573 2.488 2.420 2366 2321 24 22 4.303.443.049 2.817 2.661 2.549 2.464 2.392.342

40、.297 25 23 4279 3.422 3.028 2796 2.640 2.528 2.442 2.375 2.320 2275 26 2424 4.263.403009 2776 2.621 2.508 2.423 2.352.302.255 27 25 4.243.382991 2759 2.603 2.490 2.405 2.332.282.236 28 26 4.225 3.369 2975 2743 2.587 2.474 2.388 2.321 2.265 2.220 29 2727 4.210 3.354 2.960 2.728 2.572 2.459 2.373 2.30

41、5 2.250 2.204 30 28 4.193340 2947 2714 2558 2.445 2359 2291 2236 2190 31 29 4.183 3.328 2.934 2.701 2.545 2.432 2.346 2.278 2.223 2.177 32 30 4.171 3.316 2.922 2.690 2.534 2.421 2.334 2.266 2211 2.165 33 31 4160 3.305 2911 2679 2523 2.409 2323 2255 2.199 2.153 34 32 4.143.292.901 2668 2.512 2.399 2.

42、313 2244 2.182.142 35 3333 4.139 3285 2892 2659 2.503 2.389 2.303 2235 2.179 2.133 36 34 4.133.272.883 2650 2.494 2.380 2.294 2225 2.172.123 37 35 4.121 3.267 2.874 2641 2.485 2.372 2285 2.217 2.161 2.114 38 3636 4.113 325g 2866 2634 2.477 2.364 2277 2209 2.153 2.106 39 37 4.103.252.859 2626 2.470 2.356 2.270 2201 2.142.098 40 38 4.093.242.852 2619 2.463 2.349 2.262 2.192.132.091 41 39 4.091 3.238 2845 2612 2.456 2.342 2.255 2.187 2.131J 2.084 42 40 41 4.083232 2.839 2.606 2.449 2.336 2249 2.182.122.077 43 4.079 3.226 2833 2600 2443 2.330 2.243 2.174 2.118 2.071 44 42 4