江苏龙网

  1. 首页 > 人文 > >

ExcelVBA编程实现多元线性回归( 四 )

<> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text)End IfIf UserForm1.fxn2.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn2.Text)End IfIf UserForm1.fxn3.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn3.Text)End IfIf UserForm1.fxn4.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn4.Text)End IfEnd IfIf nterm = 2 ThenIf UserForm1.fxn1.Text <> "" ThenIf UserForm1.fxn2.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn2.Text)End IfIf UserForm1.fxn3.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn3.Text)End IfIf UserForm1.fxn4.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn4.Text)End IfEnd IfIf UserForm1.fxn2.Text <> "" And UserForm1.fxn3.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn2.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn3.Text)End IfIf UserForm1.fxn4.Text <> "" And UserForm1.fxn2.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn2.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn4.Text)End IfIf UserForm1.fxn3.Text <> "" And UserForm1.fxn4.Text <> "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn3.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn4.Text)End IfEnd IfIf nterm = 3 ThenIf UserForm1.fxn1.Text = "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn2.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn3.Text & "+ " & beta(4, 1) & "*" & UserForm1.fxn4.Text)End IfIf UserForm1.fxn2.Text = "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn3.Text & "+ " & beta(4, 1) & "*" & UserForm1.fxn4.Text)End IfIf UserForm1.fxn3.Text = "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn2.Text & "+ " & beta(4, 1) & "*" & UserForm1.fxn4.Text)End IfIf UserForm1.fxn4.Text = "" ThenMsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn2.Text & "+ " & beta(4, 1) & "*" & UserForm1.fxn3.Text)End IfEnd IfIf nterm = 4 Then MsgBox ("The Regression Result: Predictive y = " & beta(1, 1) & "+ " & beta(2, 1) & "*" & UserForm1.fxn1.Text & "+ " & beta(3, 1) & "*" & UserForm1.fxn2.Text & "+ " & beta(4, 1) & "*" & UserForm1.fxn3.Text & "+ " & beta(5, 1) & "*" & UserForm1.fxn4.Text)Dim sse As Double, sst As Double, ar2 As Double, yave As Doublesse = 0sst = 0yave = Application.WorksheetFunction.Average(Range(addy))For i = 1 To numxsse = sse + (Range("B" & i) - Range("C" & i)) * (Range("B" & i) - Range("C" & i))sst = sst + (Range("B" & i) - yave) * (Range("B" & i) - yave)Next iar2 = 1 - (sse / (numx - nterm - 1)) / (sst / (numx - 1))MsgBox ("The adjusted R-squared is " & Format(Str(ar2), "0.0000"))Ans = MsgBox("Would you like to plot the data?", vbYesNo)If Ans = 6 ThenCall Plotting(addx, addy, ypre)End IfEnd SubPrivate Sub QuitButton_Click()Unload UserForm1End SubSub Plotting(xdata As String, ydata As String, yp As String)ActiveSheet.Shapes.AddChart2(240, xlXYScatter).SelectActiveChart.SetSourceData Source:=Union(Range(xdata), Range(ydata), Range(yp))ActiveChart.ChartTitle.SelectSelection.DeleteActiveChart.PlotArea.SelectApplication.CutCopyMode = FalseActiveChart.FullSeriesCollection(1).Name = "=""Experimental Data"""ActiveChart.FullSeriesCollection(2).Name = "=""Predictive Y"""ActiveChart.FullSeriesCollection(2).SelectWith Selection.Format.Line.Visible = msoTrue.ForeColor.ObjectThemeColor = msoThemeColorAccent4.ForeColor.TintAndShade = 0.ForeColor.Brightness = 0End WithSelection.MarkerStyle = -4142ActiveChart.SetElement (msoElementLegendRight)ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)ActiveChart.Axes(xlCategory).AxisTitle.SelectSelection.Format.TextFrame2.TextRange.Characters.Text = "X"With Selection.Format.TextFrame2.TextRange.Characters(1, 1).ParagraphFormat.TextDirection = msoTextDirectionLeftToRight.Alignment = msoAlignCenterEnd WithWith Selection.Format.TextFrame2.TextRange.Characters(1, 1).Font.BaselineOffset = 0.Bold = msoFalse.NameComplexScript = "+mn-cs".NameFarEast = "+mn-ea".Fill.Visible = msoTrue.Fill.ForeColor.RGB = RGB(89, 89, 89).Fill.Transparency = 0.Fill.Solid.Size = 10.Italic = msoFalse.Kerning = 12.Name = "+mn-lt".UnderlineStyle = msoNoUnderline.Strike = msoNoStrikeEnd WithWith Selection.Format.TextFrame2.TextRange.Characters(1, 1).ParagraphFormat.TextDirection = msoTextDirectionLeftToRight.Alignment = msoAlignCenterEnd WithWith Selection.Format.TextFrame2.TextRange.Characters(1, 1).Font.BaselineOffset = 0.Bold = msoFalse.NameComplexScript = "+mn-cs".NameFarEast = "+mn-ea".Fill.Visible = msoTrue.Fill.ForeColor.RGB = RGB(89, 89, 89).Fill.Transparency = 0.Fill.Solid.Size = 10.Italic = msoFalse.Kerning = 12.Name = "+mn-lt".UnderlineStyle = msoNoUnderline.Strike = msoNoStrikeEnd WithSelection.Orientation = xlVerticalSelection.Orientation = xlHorizontalApplication.CommandBars("Format Object").Visible = FalseEnd Sub


推荐阅读


上一篇：巧用maven profile动态修改logback日志目录

下一篇：格局改变，三大运营商迎来劲敌，通信行业终将“变天”？