<简单易学> <图文并茂>
Excel VBA 制作正态分布曲线
简介
正态分布与Excel
测量数据的正态分布，对相关工作有很重要的判定意义；特别是直观的分布曲线，让人对数据质量一目了然。
/view/45379.htm?wtp=tt
参看不少文档，没有见到Excel有直接绘制正态分布曲线的函数，故考虑使用VBA编程的方法，实现从测量数据自动生成正态分布曲线的功能。
约定和程序
假设有Excel数据表，把测试数据放在第一张表的第一列中：
在VBA编辑器中新建一个模块，名字默认，输入如下代码（代码已经包含注释，请自行参看）：
'*****************************************************
Public Sub myDistrib()
Dim Aver As Double '平均数
Dim Std As Double '标准差
Dim Max As Double '最大值
Dim Min As Double '最小值
Dim Limit As Double '极限值
Aver = Application.WorksheetFunction.Average(Selection)
Std = Application.WorksheetFunction.StDev(Selection)
Max = Application.WorksheetFunction.Max(Selection)
Min = Application.WorksheetFunction.Min(Selection)
'取极值的三倍作为今后绘图的上下限
Limit = Application.WorksheetFunction.Max(Max - Aver, Aver - Min) * 2
'在上下限间创建100个单点值
step = Limit * 2 / 100
Selection.Copy
'创建一个新的表生成需要的数据
'这是绘制分布曲线需要的数据
Worksheets.Add , Worksheets(Worksheets.Count), 1
Worksheets(Worksheets.Count).Name = "【正态分布】" & Trim(Str(Sheets.Count)) Range("A1").Select
ActiveSheet.Paste
[C1] = "平均值"
[D1] = Round(Aver, 2)
[C2] = "标准差"
[D2] = Round(Std, 2)
[C3] = "绘图上限(X2)"
[D3] = Round(Aver + Limit, 2)
[C4] = "绘图下限(X2)"
[D4] = Round(Aver - Limit, 2)
For I = 1 To 100
Cells(I, 6).Value = (I - 1) * step + (Aver - Limit)
Cells(I, 7).Value = Application.WorksheetFunction.NormDist(Cells(I, 6).Value, Aver, Std, 0)
Next I
'这是绘制上下标识和平均值需要的数据
[C6] = "最大值"
[D6] = Max
[E6] = Max
[D7] = 0
[E7] = [G51]
[C9] = "最小值"
[D9] = Min
[E9] = Min
[C10] = "绘图上标值"
[D10] = 0
[E10] = [G51]
[C12] = "平均值"
[D12] = Aver
[E12] = Aver
[C13] = "绘图上标值"
[D13] = 0
[E13] = [G51]
Columns.AutoFit
'绘制图形
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='【正态分布】4'!$F$1:$F$100" ActiveChart.SeriesCollection(1).Values = "='【正态分布】4'!$G$1:$G$100" ActiveChart.SeriesCollection(1).Name = "=""分布曲线"""
ActiveChart.SeriesCollection(2).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "='【正态分布】4'!$D$6:$E$6" ActiveChart.SeriesCollection(2).Values = "='【正态分布】4'!$D$7:$E$7"
ActiveChart.SeriesCollection(2).Name = "=""最大值"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = "='【正态分布】4'!$D$9:$E$9"
ActiveChart.SeriesCollection(3).Values = "='【正态分布】4'!$D$10:$E$10"
ActiveChart.SeriesCollection(3).Name = "=""最小值"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).XValues = "='【正态分布】4'!$D$12:$E$12"
ActiveChart.SeriesCollection(4).Values = "='【正态分布】4'!$D$13:$E$13"
ActiveChart.SeriesCollection(4).Name = "=""平均值"""
'调整图形的位置
ActiveSheet.Shapes(1).IncrementLeft 185
ActiveSheet.Shapes(1).IncrementTop -93.75
End Sub
'*****************************************************
使用方法为：选择Sheet1上，第一列的原始数据；按Alt+F8。
运行“myDistrib”即可。
这个程序，在现有的Excel表后面，插入一张新的数据表，名为“【正态分布】4”。
其上有绘制好的正态分布图，和绘图需要的中间数据。
程序思路
1.首先计算绘制正态分布的两个重要参数：平均值和基本方差
2.根据实测数据，取最大值的2倍作为正态曲线的上下限
3.将上下限间的数据，分成100份，计算其正态分布值
4.绘制曲线
5.绘制测量数据的上下限和平均值位置
总结
VBA自动程序绘制正态分布曲线的效率可观。
本文仅给出一个思路，读者可根据需要，参照本文思路，修改程序得到更为详尽的分布曲线图。
文档历史
2012-5-7 创建文档并在百度文库上发布
联系方式欢迎联系出书
欢迎文档勘误houzer@。