Excel在随机抽样检测数据统计分析中的运用
摘要:Excel 具有强大的计算功能,利用它可以进行专业的统计运算,对相关变量进行回归分析,对定值数据进行质量控制、实验室比对等多有文章介绍,但对随机抽样检测数据的分析介绍较少,本文利用Excel 中的NORMDIST函数,对某第三方检测机构随机抽样油品中某有害成分(地沟油标志物之一)进行随机抽样检测的结果进行统计和分析。
关键词:Excel,NORMDIST,正态分布。
Excel作为微软OFFICE办公软件的重要组成部分,其函数功能强大,对相关数据分析有一定优势,对相关固定的应用,也可以利用Excel VBA 进行编程开发相应程序,进行自动分析,方便使用者的具体运用,下面以某第三方检测机构随机抽样200批油品对某有害成分进行检测分析的结果为例,(部分数据为虚拟)来对该结果进行分析。
10人次检测结果如下表:
表1
人员 | 甲 | 乙 | 丙 | 丁 | 戊 | 己 | 庚 | 辛 | 壬 | 癸 |
数值 | ||||||||||
1 | 0.06 | 0.14 | 0.32 | 0.24 | 0.15 | 0.19 | 0.43 | 0.56 | 0.98 | 1.56 |
2 | 0.23 | 0.07 | 0.23 | 0.3 | 0.26 | 0.53 | 0.25 | 0.59 | 0.42 | 3.74 |
3 | 0.09 | 0.24 | 0.12 | 0.11 | 0.32 | 0.34 | 1.06 | 0.43 | 2.75 | 205.32 |
4 | 0.13 | 0.12 | 0.15 | 0.06 | 0.11 | 0.37 | 0.62 | 0.31 | 1.08 | 5.43 |
5 | 0.19 | 0.09 | 0.25 | 0.46 | 0.49 | 0.25 | 0.51 | 1.89 | 0.83 | 0.69 |
6 | 0.05 | 0.32 | 0.21 | 0.27 | 0.24 | 0.17 | 0.22 | 0.45 | 1.44 | 0.86 |
7 | 0.27 | 0.23 | 0.11 | 0.16 | 0.28 | 0.19 | 0.13 | 1.05 | 0.66 | 2.34 |
8 | 0.22 | 0.17 | 0.17 | 0.18 | 0.15 | 0.42 | 0.76 | 0.67 | 0.63 | 4.32 |
9 | 0.10 | 0.08 | 0.36 | 0.32 | 0.42 | 0.15 | 0.34 | 0.88 | 1.06 | 7.69 |
10 | 0.14 | 0.37 | 0.27 | 0.23 | 0.35 | 0.24 | 0.65 | 1.34 | 0.24 | 0.57 |
11 | 0.07 | 0.12 | 0.21 | 0.12 | 0.16 | 0.23 | 0.73 | 0.26 | 0.67 | 4.78 |
12 | 0.35 | 0.26 | 0.16 | 0.09 | 0.13 | 0.19 | 0.32 | 0.93 | 2.05 | 0.77 |
13 | 0.24 | 0.15 | 0.12 | 0.25 | 0.27 | 0.29 | 0.54 | 0.42 | 0.43 | 5.23 |
14 | 0.21 | 0.06 | 0.14 | 0.21 | 0.32 | 0.35 | 0.50 | 0.37 | 0.74 | 2.86 |
15 | 0.14 | 0.06 | 0.23 | 0.32 | 0.17 | 0.21 | 0.43 | 0.84 | 0.59 | 12.83 |
16 | 0.08 | 0.16 | 0.38 | 0.12 | 0.20 | 0.12 | 0.26 | 0.56 | 0.56 | 0.29 |
17 | 0.22 | 0.22 | 0.32 | 0.08 | 0.26 | 0.32 | 0.43 | 0.97 | 1.47 | 108.79 |
18 | 0.16 | 0.08 | 0.16 | 0.19 | 0.33 | 0.30 | 1.09 | 1.60 | 0.78 | 3.08 |
19 | 0.12 | 0.19 | 0.19 | 0.41 | 0.12 | 0.22 | 0.82 | 0.73 | 0.98 | 1.09 |
20 | 0.25 | 0.23 | 0.11 | 0.17 | 0.19 | 0.13 | 0.72 | 0.45 | 0.81 | 3.25 |
求和 | 3.32 | 3.36 | 4.21 | 4.29 | 4.92 | 5.21 | 10.81 | 15.3 | 19.17 | 375.49 |
平均值 | 0.166 | 0.168 | 0.2105 | 0.2145 | 0.246 | 0.2605 | 0.5405 | 0.765 | 0.9585 | 18.7745 |
标准差 | 0.08139572 | 0.088472416 | 0.083758739 | 0.109134152 | 0.103993927 | 0.104653564 | 0.266250278 | 0.437426686 | 0.59377716 | 49.90388346 |
对数据进行升序排序见表2:
人员 | 甲 | 乙 | 丙 | 丁 | 戊 | 己 | 庚 | 辛 | 壬 | 癸 |
数值 | ||||||||||
1 | 0.05 | 0.06 | 0.11 | 0.06 | 0.11 | 0.12 | 0.13 | 0.26 | 0.24 | 0.29 |
2 | 0.06 | 0.06 | 0.11 | 0.08 | 0.12 | 0.13 | 0.22 | 0.31 | 0.42 | 0.57 |
3 | 0.07 | 0.07 | 0.12 | 0.09 | 0.13 | 0.15 | 0.25 | 0.37 | 0.43 | 0.69 |
4 | 0.08 | 0.08 | 0.12 | 0.11 | 0.15 | 0.17 | 0.26 | 0.42 | 0.56 | 0.77 |
5 | 0.09 | 0.08 | 0.14 | 0.12 | 0.15 | 0.19 | 0.32 | 0.43 | 0.59 | 0.86 |
6 | 0.10 | 0.09 | 0.15 | 0.12 | 0.16 | 0.19 | 0.34 | 0.45 | 0.63 | 1.09 |
7 | 0.12 | 0.12 | 0.16 | 0.16 | 0.17 | 0.19 | 0.43 | 0.45 | 0.66 | 1.56 |
8 | 0.13 | 0.12 | 0.16 | 0.17 | 0.19 | 0.21 | 0.43 | 0.56 | 0.67 | 2.34 |
9 | 0.14 | 0.14 | 0.17 | 0.18 | 0.20 | 0.22 | 0.43 | 0.56 | 0.74 | 2.86 |
10 | 0.14 | 0.15 | 0.19 | 0.19 | 0.24 | 0.23 | 0.50 | 0.59 | 0.78 | 3.08 |
11 | 0.16 | 0.16 | 0.21 | 0.21 | 0.26 | 0.24 | 0.51 | 0.67 | 0.81 | 3.25 |
12 | 0.19 | 0.17 | 0.21 | 0.23 | 0.26 | 0.25 | 0.54 | 0.73 | 0.83 | 3.74 |
13 | 0.21 | 0.19 | 0.23 | 0.24 | 0.27 | 0.29 | 0.62 | 0.84 | 0.98 | 4.32 |
14 | 0.22 | 0.22 | 0.23 | 0.25 | 0.28 | 0.30 | 0.65 | 0.88 | 0.98 | 4.78 |
15 | 0.22 | 0.23 | 0.25 | 0.27 | 0.32 | 0.32 | 0.72 | 0.93 | 1.06 | 5.23 |
16 | 0.23 | 0.23 | 0.27 | 0.3 | 0.32 | 0.34 | 0.73 | 0.97 | 1.08 | 5.43 |
17 | 0.24 | 0.24 | 0.32 | 0.32 | 0.33 | 0.35 | 0.76 | 1.05 | 1.44 | 7.69 |
18 | 0.25 | 0.26 | 0.32 | 0.32 | 0.35 | 0.37 | 0.82 | 1.34 | 1.47 | 12.83 |
19 | 0.27 | 0.32 | 0.36 | 0.41 | 0.42 | 0.42 | 1.06 | 1.60 | 2.05 | 108.79 |
20 | 0.35 | 0.37 | 0.38 | 0.46 | 0.49 | 0.53 | 1.09 | 1.89 | 2.75 | 205.32 |
平均值 | 0.166 | 0.168 | 0.2105 | 0.2405 | 0.246 | 0.2605 | 0.5405 | 0.765 | 0.9585 | 18.7745 |
-0.116 | -0.108 | -0.1005 | -0.1805 | -0.136 | -0.1405 | -0.4105 | -0.505 | -0.7185 | -18.4845 |
-0.106 | -0.108 | -0.1005 | -0.1605 | -0.126 | -0.1305 | -0.3205 | -0.455 | -0.5385 | -18.2045 |
-0.096 | -0.098 | -0.0905 | -0.1505 | -0.116 | -0.1105 | -0.2905 | -0.395 | -0.5285 | -18.0845 |
-0.086 | -0.088 | -0.0905 | -0.1305 | -0.096 | -0.0905 | -0.2805 | -0.345 | -0.3985 | -18.0045 |
-0.076 | -0.088 | -0.0705 | -0.1205 | -0.096 | -0.0705 | -0.2205 | -0.335 | -0.3685 | -17.9145 |
-0.066 | -0.078 | -0.0605 | -0.1205 | -0.086 | -0.0705 | -0.2005 | -0.315 | -0.3285 | -17.6845 |
-0.046 | -0.048 | -0.0505 | -0.0805 | -0.076 | -0.0705 | -0.1105 | -0.315 | -0.2985 | -17.2145 |
-0.036 | -0.048 | -0.0505 | -0.0705 | -0.056 | -0.0505 | -0.1105 | -0.205 | -0.2885 | -16.4345 |
-0.026 | -0.028 | -0.0405 | -0.0605 | -0.046 | -0.0405 | -0.1105 | -0.205 | -0.2185 | -15.9145 |
-0.026 | -0.018 | -0.0205 | -0.0505 | -0.006 | -0.0305 | -0.0405 | -0.175 | -0.1785 | -15.6945 |
-0.006 | -0.008 | -0.0005 | -0.0305 | 0.014 | -0.0205 | -0.0305 | -0.095 | -0.1485 | -15.5245 |
0.024 | 0.002 | -0.0005 | -0.0105 | 0.014 | -0.0105 | -0.0005 | -0.035 | -0.1285 | -15.0345 |
0.044 | 0.022 | 0.0195 | -0.0005 | 0.024 | 0.0295 | 0.0795 | 0.075 | 0.0215 | -14.4545 |
0.054 | 0.052 | 0.0195 | 0.0095 | 0.034 | 0.0395 | 0.1095 | 0.115 | 0.0215 | -13.9945 |
0.054 | 0.062 | 0.0395 | 0.0295 | 0.074 | 0.0595 | 0.1795 | 0.165 | 0.1015 | -13.5445 |
0.064 | 0.062 | 0.0595 | 0.0595 | 0.074 | 0.0795 | 0.1895 | 0.205 | 0.1215 | -13.3445 |
0.074 | 0.072 | 0.1095 | 0.0795 | 0.084 | 0.0895 | 0.2195 | 0.285 | 0.4815 | -11.0845 |
0.084 | 0.092 | 0.1095 | 0.0795 | 0.104 | 0.1095 | 0.2795 | 0.575 | 0.5115 | -5.9445 |
0.104 | 0.152 | 0.1495 | 0.1695 | 0.174 | 0.1595 | 0.5195 | 0.835 | 1.0915 | 90.0155 |
0.184 | 0.202 | 0.1695 | 0.2195 | 0.244 | 0.2695 | 0.5495 | 1.125 | 1.7915 | 186.5455 |
甲-庚平均值取0.2,标准差按实际 | ||||
辛后平均值,标准差均按实际 | ||||
NORMDIST返回累积分布函数 |
1 | 0.002440755 | 0.009916849 | 0.007900515 | 0.008267169 | 0.020764529 | 0.019777609 | 0.002531494 | 0.013379212 | 0.012453423 | 0.006049815 |
2 | 0.003920897 | 0.009916849 | 0.007900515 | 0.015424807 | 0.028198656 | 0.026812249 | 0.008009152 | 0.018535193 | 0.027990407 | 0.00607512 |
3 | 0.006203365 | 0.014665618 | 0.01200977 | 0.020805019 | 0.037941931 | 0.04795508 | 0.01146238 | 0.026938431 | 0.029199803 | 0.006085939 |
4 | 0.009666071 | 0.021410111 | 0.01200977 | 0.036906155 | 0.066812171 | 0.082714048 | 0.012880814 | 0.036260267 | 0.049317171 | 0.006093143 |
5 | 0.014833828 | 0.021410111 | 0.026596737 | 0.048537912 | 0.066812171 | 0.137583773 | 0.025180521 | 0.038420277 | 0.055280423 | 0.006101238 |
6 | 0.022420081 | 0.030855269 | 0.038747415 | 0.048537912 | 0.087438101 | 0.137583773 | 0.031131128 | 0.043066267 | 0.064112948 | 0.006121885 |
7 | 0.048926829 | 0.085469606 | 0.055654738 | 0.133492397 | 0.113378459 | 0.137583773 | 0.075404592 | 0.043066267 | 0.071438796 | 0.006163887 |
8 | 0.07064363 | 0.085469606 | 0.055654738 | 0.168330173 | 0.185414772 | 0.220698021 | 0.075404592 | 0.077722904 | 0.074020283 | 0.006233009 |
9 | 0.100456864 | 0.158037169 | 0.078814584 | 0.210480583 | 0.233845101 | 0.2757435 | 0.075404592 | 0.077722904 | 0.094150071 | 0.006278668 |
10 | 0.100456864 | 0.210775825 | 0.151478352 | 0.260979704 | 0.539400185 | 0.341407414 | 0.138627683 | 0.09030333 | 0.107351924 | 0.00629788 |
11 | 0.194059465 | 0.277507173 | 0.275089928 | 0.391228082 | 0.775016088 | 0.41889139 | 0.15037432 | 0.131656846 | 0.118102558 | 0.006312682 |
12 | 0.464753478 | 0.360677862 | 0.275089928 | 0.567063674 | 0.775016088 | 0.509320012 | 0.190310202 | 0.170882159 | 0.125683091 | 0.006355128 |
13 | 0.770876003 | 0.586118742 | 0.472040148 | 0.674139346 | 0.916194303 | 1.016614217 | 0.33513847 | 0.26246072 | 0.193292457 | 0.006404941 |
14 | 0.970367996 | 1.102106924 | 0.472040148 | 0.794716362 | 1.073122149 | 1.181271132 | 0.404815887 | 0.302003422 | 0.193292457 | 0.006444107 |
15 | 0.970367996 | 1.325623379 | 0.765355694 | 1.076889155 | 1.841376012 | 1.552097066 | 0.59866765 | 0.355696369 | 0.236914268 | 0.00648212 |
16 | 1.203009535 | 1.325623379 | 1.172540436 | 1.594755111 | 1.841376012 | 1.966669802 | 0.629515006 | 0.401642809 | 0.24857327 | 0.006498917 |
17 | 1.468866548 | 1.574013445 | 2.658135903 | 1.986548414 | 2.059337701 | 2.183880982 | 0.725745289 | 0.499396096 | 0.486184201 | 0.006684306 |
18 | 1.766348028 | 2.134816635 | 2.658135903 | 1.986548414 | 2.505254544 | 2.620634558 | 0.928470534 | 0.830577192 | 0.505683907 | 0.007071656 |
19 | 2.440091722 | 3.906803814 | 3.964124218 | 3.519504536 | 3.717962662 | 3.52707403 | 1.495335691 | 0.901274184 | 0.65511706 | 0.002885452 |
20 | 4.830057488 | 4.532264331 | 4.446338155 | 3.601917789 | 3.507588643 | 3.052000993 | 1.49882682 | 0.650223884 | 0.251532032 | 2.80665E-05 |
因 素 | 偏 离 | 结 果 影 响 | 备 注 |
称样误差 | ±1% | ±1% | |
定容误差 | ±3% | ±3% | |
标样浓度变化 | ±1% | ±1% | 标样化学成分相对稳定 |
反应不完全 | -5%,~-10% | -5%,~-10% | 结果偏小 |
反应容器吸附 | -3%~-5% | -3%~-5% | 结果偏小 |
内标加入体积变化 | ±3% | ±3% | |
滤膜吸附 | -5% | -5% | 结果偏小 |
仪器测定误差 | ±1% | ±1% | |
3.基于Excelvba技术构建审核全肠外营养液处方的算法 王亚奇*,庞成森,马 妮,贺银丽,董卫华(# 西安交通大学第一附属医院药学部,西安 710061)中国药房 2019年第30卷第1期
4. Excel在分析化学实验数据处理中的应用 刘俊桃,陈晓培,吴金松,丁德刚,徐 军
(河南牧业经济学院 理学部,河南 郑州 450000)云南化工2018第45卷第9期
5. Excel在国际实验室间比对数据处理中的应用 黄河清 ,储德韧 ,段路路
上海化工研究院有限公司,上海 200062;2.上海化学品公共安全工程技术研究中心,上海 200062 磷 肥 与 复 肥2019第34卷第1期