用Excel-VBA计算花生密度-产量回归曲线方程
用Excel-VBA计算花生密度-产量回归曲线方程
摘要 介绍用Excel VBA编写密度与花生产量关系方程的程序。该程序通过在Excel表上用鼠标框选密度和产量数据，就可计算出回归系数和相关系数，对回归方程进行方差分析，求出最佳密度和最佳产量，绘制出花生种植密度与产量的关系曲线图。并制作成加载宏，添加到Excel主菜单上，为以后进行类似统计分析提供方便。
关键词 密度；花生产量；Excel VBA；加载宏
众所周知Excel是非常方便快捷的电子表格办公软件，作为微软办公系统被安装到决大多数电脑中，成为自动化办公不可缺少的一个工具。对于Excel来说几乎是家喻户晓，但人们对Excel-VBA了解并不普遍。VBA是Visual Basic for Application的缩写，是集成在Office产品中的开发语言和开发环境。掌握VBA可以使办公效率变得更快。特别在统计分析方面，VBA显得更为重要。虽然市场上有很多统计分析软件，但都缺少专业化和个性化设计。所以掌握VBA，编写自己专业需要的应用程序，提高工作效率，非常实用。
本文用VBA建立花生密度-产量回归曲线方程，并计算最佳密度和最佳产量。
1. 创建程序启动界面
图1 启动Visual Basic 编辑器界面
作者简介：吴占鹏，副研究员，电话：[1**********]，信箱：[email protected]，通信地址：辽宁省阜新市细河区中华路东段55号，邮编：123000。本文发表在《海峡两岸花生学术研讨会论文集》，2009年，418-423页。
打开Excel，选择工具->宏->Visual Basic 编辑器（如图1）。打开VBA窗口（如图2）。单击插入->用户窗体，插入窗体。再给窗体添加一个RefEdit控件、一个标签控
件和两个命令按钮控件。接着更改窗体Caption的属性值为“密度-产量回归方程”，更改第一个命令按钮Caption的属性值为“确定”，第二个命令按钮的Caption属性值为“取消”，更改标签的Caption属性值为“输入数据地址”，更改RefEdit1的TabIndex属性值改为0 （如图3）。
图2 插入窗体界面 图3 生成数据读取对话框
2. 输入程序
然后双击确定按钮，打开脚本输入窗体（如图4），在光标处插入代码：
Dim ss As String
Dim x1() , y1() As Single Dim q, w, t, s, p, r2, a, b, r As Single
Dim ke, ka, sta1, end1, k, i , kuan, gao, ting, zuo, kk, jj, max, stp As Integer ss = RefEdit1.Value : q = 0 : w = 0 : t = 0 : s = 0 : p = 0 : r2 = 0 ka = Range(ss).Column : sta1 = Range(ss).Row k = Range(ss).Rows.Count:end1 = k + sta1 – 1 ReDim x1(sta1 To end1) ReDim y1(sta1 To end1) max = Cells(end1, ka) For i = sta1 To end1
x1(i) = Cells(i, ka) * Cells(i, ka) y1(i) = Cells(i, ka) / Cells(i, ka + 1) Next
For i = sta1 To end1
q = q + x1(i) : w = w + x1(i) * x1(i) : s = s + y1(i)
t = t + y1(i) * y1(i) : p = p + x1(i) * y1(i) Next
b = (p - s * q / k) / (w - q * q / k) : a = s / k - b * q / k
r = (p - s * q / k) / Sqr((w - q * q / k) * (t - s * s / k)) Cells(2 + end1, ka) = "a=" Cells(2 + end1, ka + 1) = a Cells(3 + end1, ka) = "b=" Cells(3 + end1, ka + 1) = b Cells(4 + end1, ka) = " r=" Cells(4 + end1, ka + 1) = r
Range(Chr(ka + 64) & 2 + end1 & ":" & Chr(ka + 64) & 7 + end1).HorizontalAlignment = xlRight
点击 执行程序，用鼠标框选数据（如图4）。
图4 读取数据操作界面
单击确定按钮，计算出回归系数并添加到Excel表格中。接着添加代码增加方差分析表和最佳产量、最佳密度，以及回归曲线图形：
Cells(6 + end1, ka) = " Hx=" Cells(6 + end1, ka + 1) = Sqr(a / b) Cells(7 + end1, ka) = " Hy="
Cells(7 + end1, ka + 1) = 1 / (2 * Sqr(a * b)) : s = 0 : t = 0 : q = 0 : w = 0
Range(Chr(ka + 66) & sta1 & ":" & Chr(ka + 67) & end1).NumberFormatLocal = "0.00_ " For i = sta1 To end1
Cells(i, ka + 2) = Cells(i, ka) / (a + b * Cells(i, ka) * Cells(i, ka)) Cells(i, ka + 3) = Cells(i, ka + 1) - Cells(i, ka + 2)
q = q + Cells(i, ka + 3) : w = w + Cells(i, ka + 3) * Cells(i, 3) : s = s + Cells(i, ka + 1) : t = t + Cells(i, ka + 1) * Cells(i, ka + 1) Next
p = w - q * q / k : r2 = Sqr(1 - p / (t - s * s / k)) Cells(5 + end1, ka) = "R=" : Cells(5 + end1, ka + 1) = r2
Range(Chr(ka + 69) & sta1 & ":" & Chr(ka + 74) & sta1 + 3).HorizontalAlignment = xlCenter Cells(sta1, ka + 5) = "变异原因" Cells(sta1, ka + 6) = "自由度" Cells(sta1, ka + 7) = "平方和" Cells(sta1, ka + 8) = "均方" Cells(sta1, ka + 9) = "F-值" Cells(sta1, ka + 10) = "P-0.05" Cells(sta1 + 1, ka + 5) = "回归" Cells(sta1 + 2, ka + 5) = "误差" Cells(sta1 + 3, ka + 5) = "总的" Cells(sta1 + 1, ka + 6) = 1 Cells(sta1 + 2, ka + 6) = k – 2 Cells(sta1 + 3, ka + 6) = k - 1 Cells(sta1 + 3, ka + 7) = t - s * s / k Cells(sta1 + 2, ka + 7) = p
Cells(sta1 + 1, ka + 7) = Cells(sta1 + 3, ka + 7) – p Cells(sta1 + 1, ka + 8) = Cells(sta1 + 1, ka + 7)
Cells(sta1 + 2, ka + 8) = Cells(sta1 + 2, ka + 7) / Cells(sta1 + 2, ka + 6) Cells(sta1 + 1, ka + 9) = Cells(sta1 + 1, ka + 8) / Cells(sta1 + 2, ka + 8)
Range(Chr(ka + 71) & sta1 + 1 & ":" & Chr(ka + 74) & sta1 + 3).NumberFormatLocal = "0.000_ "
Cells(sta1 + 1, ka + 10)= "=FINV(0.05,1,R[1]C[-4])" stp = (Cells(end1, ka) - Cells(sta1, ka)) / (k - 1) Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(ss), PlotBy:=xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" ActiveChart.HasLegend = False
ActiveChart.Axes(xlValue).MinimumScale = 0 ActiveChart.Axes(xlCategory).MinimumScale = 0 ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "密度" ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "产量" ActiveChart.PlotArea.Interior.ColorIndex = 40
kuan = ActiveChart.PlotArea.InsideWidth gao = ActiveChart.PlotArea.InsideHeight zuo = ActiveChart.PlotArea.InsideLeft ting = ActiveChart.PlotArea.InsideTop
kk = ActiveChart.Axes(xlValue).MaximumScale jj = ActiveChart.Axes(xlCategory).MaximumScale
With ActiveChart.Shapes.BuildFreeform(msoEditingAuto, zuo, gao + ting) For i = stp To max + stp Step stp
AddNodes msoSegmentCurve, msoEditingAuto, zuo + i * kuan / jj, gao + ting - gao / kk * i / (a + b * i * i) Next
.ConvertToShape.Select End With
ActiveChart.Deselect UserForm1.Hide
重新执行程序，得到输出结果（如图5）。
图5 输出结果
3. 制作加载宏
打开VBA编辑器，单击插入->模块，双击模块1，打入代码（如图6）。双击取消按钮，在光标处输入UserForm1.Hide代码（如图7）。
图6 启动菜单代码 图7 隐藏面板代码
回到Excel面板，选择文件->另存为，选择文件扩展名为xla，输入文件名，保存。重新启动Excel，单击工具->加载宏，打开加载宏对话框，单击浏览，选择刚才制作的加载宏文件，单击确定，主菜单上出现“统计分析”菜单，单击统计分析菜单，显示子菜单（如图8）。如果需要再编写其他程序，可在密度产量回归曲线下面继续添加菜单（如图9）。
8 加载宏单项目启动菜单
9 加载宏多项目启动菜单 图 图