用Excel做线性回归分析

#2用Excel进行一元线性回归分析查找联合利华产品销量增加的广告投入原因数据包括实体广告（公车，地铁站电视，海报）和网络广告（网络平台的投入）根据查询信息输入数据ABcDE1序号投入广告费〔实体）投入广告费〔网络）促进消费额2110.8711.2200.93218.6623230.8433325.3251.25436.2134.4256.86540.1445.4288.87644.5756326.48757.5699.4398.99866.6129.1440.710972145.2)469.8111087.4156.3476.7121188.9172.4509.2131298.5177.3523.314求和654.4110754373.51选择工具-数据分析-回归2开始输入数据

回归结果图回归统计表，方差分析表ABCDEFGH[0.9954418240.990904425回归统计Muitipi回归结果图回归统计表，方差分析表ABCDEFGH[0.9954418240.990904425回归统计Muitipi已RRSqu；areAdjustedRSquai0.98888318&丄标淮误差8观测值科方差潘12.4381190212df归差计

回残总

2342911151688.80791392.361242153民1.169275844.40396154.7068047FSignificmceF490.2461~~6.52Y33E-101516|CoefficientstStatP^alueLo^er95%Upper95%T-^i95.0%95.0%TFIoterc^pF~1FT551肛・11.6236785416.135268665.98E-08161.2565884213.845764161.256588213.8457637\Wiable10.77363260'r^Q.6367593111.2149529590.255295-0.6668170282.21408225-0.6668172.214082246M9XVariable21.5Q382GQ2$2853331285.2T04220930.QQQ5140.8583576422.14929440.85835花42.1492943^123rEstkjm^3UWFPROBABILITYOUTPUT2425直预测Y标淮残笫百分比排位Y261212.8034139-11.90341392-1.0580152214.166666667200.9272236.575159-5.77515899-0.51331543612.5230.8283251.12785050.0721495480.00641289320.83333333251.2294267.2960279-10.49602791-0.93292204729.16666667256.8305286.87849031.92150971105288.8316306.246238620.153761441.79133368745.83333333326.4327381.561775417.338224561.54107935854.16666667398.9338433.2190477.4809529750.66493210862.5440.7349461.6082628.191737960.72810905370.83333333469.83510490.214673-13.51467305-1.20122931879.16666667476.73611515.5867209-6.386720887-0.56767310287.5509.23712530.3823414-7.082341435-0.62950218295.83333333523.31r可以看出函数的系数a=187.551176；b=0.773632609；b=1.5038260212所以y=0.773632609x+1.50382602x+187.55117612最后，读取回归结果如下：R=0.995441824R2R=0.995441824R2=0.990904425F=490.2461S=12.43811902n工xy一工x工iiii=1i=1i=1・:n工y2一(工yiiiir=—i=1i=ii=1(—1<r<1)“y)2iii“i=1i=1ii=1i=15建立回归模型，并对结果进行检验模型为：y=0.773632609x+1.50382602x+187.55117612至于检验，R、R2和F值可以直接从回归结果中读出。当0.7<r<1，两变量正线性相关的关系较强。R=0.995441824R检验：相关系数临界值：