利用Excel进行统计分析-Chapter15-Multiple Regression Model

Chap15-1StatisticsforManagersUsingMicrosoft®Excel5thEditionChapter15MultipleRegressionModelBuildingChap15-2LearningObjectivesInthischapter,youlearn:TousequadratictermsinaregressionmodelTousetransformedvariablesinaregressionmodelTomeasurethecorrelationamongindependentvariablesTobuildaregressionmodel,usingeitherthestepwiseorbest-subsetsapproachToavoidthepitfallsinvolvedindevelopingamultipleregressionmodelChap15-3NonlinearRelationshipsTherelationshipbetweenthedependentvariableandanindependentvariablemaynotbelinearCanreviewthescatterplottocheckfornon-linearrelationshipsExample:QuadraticmodelThesecondindependentvariableisthesquareofthefirstvariablei21i21i10iεXβXββYChap15-4QuadraticRegressionModelwhere:β0=Yinterceptβ1=regressioncoefficientforlineareffectofXonYβ2=regressioncoefficientforquadraticeffectonYεi=randomerrorinYforobservationii21i21i10iεXβXββYModelform:Chap15-5QuadraticRegressionModelLinearfitdoesnotgiverandomresidualsNonlinearfitgivesrandomresidualsXresidualsXYXresidualsYXChap15-6QuadraticRegressionModelQuadraticmodelsmaybeconsideredwhenthescatterdiagramtakesononeofthefollowingshapes:X1YX1X1YYYβ10β10β10β10β1=thecoefficientofthelineartermβ2=thecoefficientofthesquaredtermX1i21i21i10iεXβXββYβ20β20β20β20Chap15-7QuadraticRegressionEquationTestforOverallRelationshipH0:β1=β2=0(nooverallrelationshipbetweenXandY)H1:β1and/orβ2≠0(thereisarelationshipbetweenXandY)F-teststatistic=21i21i10iXbXbbYˆMSEMSRCollectdataanduseExceltocalculatetheregressionequation:Chap15-8TestingforSignificanceQuadraticEffectTestingtheQuadraticEffectComparequadraticregressionequationwiththelinearregressionequation21i21i10^XbXbbiY1i10^iXbbYChap15-9TestingforSignificanceQuadraticEffectTestingtheQuadraticEffectConsiderthequadraticregressionequationHypotheses(Thequadratictermdoesnotimprovethemodel)(Thequadratictermimprovesthemodel)21i21i10^iXbXbbYH0:β2=0H1:β20Chap15-10TestingforSignificanceQuadraticEffectTestingtheQuadraticEffectHypotheses(Thequadratictermdoesnotimprovethemodel)(Thequadratictermimprovesthemodel)TheteststatisticisH0:β2=0H1:β202b22Sβbt3nd.f.where:b2=estimatedslopeβ2=hypothesizedslope(zero)Sb2=standarderroroftheslopeChap15-11TestingforSignificanceQuadraticEffectTestingtheQuadraticEffectIfthettestforthequadraticeffectissignificant,keepthequadraticterminthemodel.Chap15-12QuadraticRegressionExamplePurityincreasesasfiltertimeincreases:PurityFilterTime31728315522733840105412671370147815851587169917Purityvs.Time02040608010005101520TimePurityChap15-13QuadraticRegressionExampleRegressionStatisticsRSquare0.96888AdjustedRSquare0.96628StandardError6.15997Simple(linear)regressionresults:Y=-11.283+5.985TimeCoefficientsStandardErrortStatP-valueIntercept-11.282673.46805-3.253320.00691Time5.985200.3096619.328192.078E-10FSignificanceF373.579042.0778E-10^TimeResidualPlot-10-5051005101520TimeResidualststatistic,Fstatistic,andadjustedr2areallhigh,buttheresidualsarenotrandom:Chap15-14QuadraticRegressionExampleCoefficientsStandardErrortStatP-valueIntercept1.538702.244650.685500.50722Time1.564960.601792.600520.02467Time-squared0.245160.032587.524061.165E-05RegressionStatisticsRSquare0.99494AdjustedRSquare0.99402StandardError2.59513FSignificanceF1080.73302.368E-13Quadraticregressionresults:Y=1.539+1.565Time+0.245(Time)2^TimeResidualPlot-5051005101520TimeResidualsTime-squaredResidualPlot-505100100200300400Time-squaredResidualsThequadratictermissignificantandimprovesthemodel:adjustedr2ishigherandSYXislower,residualsarenowrandom.Chap15-15UsingTransformationsinRegressionAnalysisIdea:Non-linearmodelscanoftenbetransformedtoalinearformCanbeestimatedbyleastsquaresiftransformedTransformXorYorbothtogetabetterfitortodealwithviolationsofregressionassumptionsCanbebasedontheory,logicorscatterplotsChap15-16TheSquareRootTransformationThesquare-roottransformationUsedtoovercomeviolationsoftheequalvarianceassumptionfitanon-linearrelationshipi1i10iεXββYChap15-17TheSquareRootTransformationShapeoforiginalrelationshipRelationshipwhentransformedi1i10iεXββYi1i10iεXββYXb10b10XYYYYXXChap15-18TheLogTransformationOriginalmultiplicativemodelTransformedmultiplicativemodeliβ1i0iεXβY1i1i10iεlogXlogββlogYlogTheMultiplicativeModel:OriginalmultiplicativemodelTransformedexponentialmodeli2i21i10iεlnXβXββYlnTheExponentialModel:iXβXββiεeY2i21i10Chap15-19InterpretationofCoefficientsForthemultiplicativemodel:Whenbothdependentandindependentvariablesaretransformed:ThecoefficientoftheindependentvariableXkcanbeinterpretedasfollows:a1percentchangeinXkleadstoanestimatedbkpercentagechangeinthemeanvalueofY.i1i10iεlogXlogββlogYlogChap15-20CollinearityCollinearity:HighcorrelationexistsamongtwoormoreindependentvariablesThecorrelatedvariablescontributeredundantinformationtothemultipleregressionmodelChap15-21CollinearityIncludingtwohighlycorrelatedindependentvariablescanadverselyaffecttheregressionresultsNonewinformationprovidedCanleadtounstablecoefficients(largestandarderrorandlowt-values)Coefficientsignsmaynotmatchpri