岭回归excel计算过程

,,,,,,岭回归做法,,,,,,,,,

,,,,,,,,,,,,,,,,

原理：,岭回归主要解决的由于多重共线性导致的|X'X|≈0的问题。通过加正则项减小(X'X)逆对角线过大的误差。,,,,,,,,,,,,,,

一、,数据处理,,,,,,,(X'X)逆,,,,,,,

,原始数据,,,,,转置,,,,,,,,,

,C,X1,X2,Y,,X',,,,,,,,,

,1,1.1,1.1,16.3,,1,1,1,1,1,1,1,1,1,1

,1,1.4,1.5,16.8,,1.1,1.4,1.7,1.7,1.8,1.8,1.9,2,2.3,2.4

,1,1.7,1.8,19.2,,1.1,1.5,1.8,1.7,1.9,1.8,1.8,2.1,2.4,2.5

,1,1.7,1.7,18,,,,,,,,,,,

,1,1.8,1.9,19.5,,公式,β_hat=,(X'X)逆*X'*Y,,,,,,,

,1,1.8,1.8,20.9,,,,,,,,,,,

,1,1.9,1.8,21.1,,,,,,,,,,,

,1,2,2.1,20.9,,,,,,,,,,,

,1,2.3,2.4,20.3,,,,,,,,,,,

,1,2.4,2.5,22,,,,,,,,,,,

,,,,,,,,,,,,,,,,

二、,计算步骤,,,,,,,,,,,,,,

,X'X,0,,,X'X+0.05In,0.05,,,X'X+0.1In,0.1,,,X'X+0.15In,0.15,

,10,18.1,18.6,,10.05,18.1,18.6,,10.1,18.1,18.6,,10.15,18.1,18.6

,18.1,34.09,35.06,,18.1,34.14,35.06,,18.1,34.19,35.06,,18.1,34.24,35.06

,18.6,35.06,36.1,,18.6,35.06,36.15,,18.6,35.06,36.2,,18.6,35.06,36.25

,|X'X|,0.5558,,,|X'X|,2.24517,,,|X'X|,4.33624,,,|X'X|,6.82976,

,,,,,,,,,,,,,,,,

,(X'X)逆,,,,(X'X+0.05In)逆,,,,(X'X+0.1In)逆,,,,(X'X+0.15In)逆,,

,2.600575747,-2.328175603,0.921194674,,2.208028791,-0.979435856,-0.186177439,,1.954319872,-0.715827537,-0.310868402,,1.756489247,-0.58698988,-0.333540271

,-2.328175603,27.06009356,-25.08096438,,-0.979435856,7.726586405,-6.989671161,,-0.715827537,4.533881888,-4.023301293,,-0.58698988,3.21790224,-2.811079745

,0.921194674,-25.08096438,23.91147895,,-0.186177439,-6.989671161,6.902372649,,-0.310868402,-4.023301293,4.083952918,,-0.333540271,-2.811079745,2.917525652

,,,,,,,,,,,,,,,,

,β-hat,,,,β-hat,,,,β-hat,,,,β-hat,,

,C,11.29244332,,,C,10.53812852,,,C,9.690063281,,,C,8.983037471,

,β1,11.30730479,,,β1,5.188638277,,,β1,4.33742136,,,β1,4.064677968,

,β2,-6.590680101,,,β2,-0.25927658,,,β2,1.0012384,,,β2,1.626424647,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,X'X+0.2In,0.2,,,X'X+0.3In,0.3,,,X'X+0.4In,0.4,,,X'X+0.5In,,

,10.2,18.1,18.6,,10.3,18.1,18.6,,10.4,18.1,18.6,,10.5,18.1,18.6

,18.1,34.29,35.06,,18.1,34.39,35.06,,18.1,34.49,35.06,,18.1,34.59,35.06

,18.6,35.06,36.3,,18.6,35.06,36.4,,18.6,35.06,36.5,,18.6,35.06,36.6

,|X'X|,9.72648,,,|X'X|,16.73252,,,|X'X|,25.36036,,,|X'X|,35.616,

,,,,,,,,,,,,,,,,

,(X'X+0.2In)逆,,,,(X'X+0.3In)逆,,,,(X'X+0.4In)逆,,,,(X'X+0.5In)逆,,

,1.595993617,-0.505218743,-0.329821271,,1.350209054,-0.401852202,-0.302883248,,1.170385594,-0.336509419,-0.273182242,,1.032973944,-0.290431267,-0.246743037

,-0.505218743,2.498334444,-2.154119476,,-0.401852202,1.730761415,-1.461704513,,-0.336509419,1.326479593,-1.102665735,,-0.290431267,1.07648248,-0.883591644

,-0.329821271,-2.154119476,2.277082768,,-0.302883248,-1.461704513,1.59013705,,-0.273182242,-1.102665735,1.225771243,,-0.246743037,-0.883591644,0.999129605

,,,,,,,,,,,,,,,,

,β-hat,,,,β-hat,,,,β-hat,,,,β-hat,,

,C,8.395692995,,,C,7.482593178,,,C,6.807661248,,,C,6.288938679,

,β1,3.959027315,,,β1,3.907510644,,,β1,3.920819736,,,β1,3.949351415,

,β2,2.027179411,,,β2,2.537814388,,,β2,2.862015366,,,β2,3.090477594,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,X'X+0.6In,0.6,,,X'X+In,1,,,X'X+1.5In,1.5,,,,,

,10.6,18.1,18.6,,11,18.1,18.6,,11.5,18.1,18.6,,,,

,18.1,34.69,35.06,,18.1,35.09,35.06,,18.1,35.59,35.06,,,,

,18.6,35.06,36.7,,18.6,35.06,37.1,,18.6,35.06,37.6,,,,

,|X'X|,47.50544,,,|X'X|,111.5212,,,|X'X|,229.0214,,,,,

,,,,,,,,,,,,,,,,

,(X'X+0.6In)逆,,,,(X'X+In)逆,,,,(X'X+1.5In)逆,,,,,,

,0.924513066,-0.255844383,-0.224142751,,0.651314728,-0.173904155,-0.162193377,,0.475852475,-0.124198001,-0.119587078,,,,

,-0.255844383,0.906422507,-0.736252522,,-0.173904155,0.557203473,-0.439378342,,-0.124198001,0.377431978,-0.290496871,,,,

,-0.224142751,-0.736252522,0.844198054,,-0.162193377,-0.439378342,0.523487911,,-0.119587078,-0.290496871,0.356626062,,,,

,,,,,,,,,,,,,,,,

,β-hat4,,,,β-hat4,,,,β-hat4,,,,,,

,C,5.877830834,,,C,4.834931296,,,C,4.156414379,,,,,

,β1,3.97980947,,,β1,4.074835995,,,β1,4.137587579,,,,,

,β2,3.261314073,,,β2,3.659205604,,,β2,3.887682767,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

三、,归纳结果,,,,,,,,,,,,,,

,k,0.00,0.05,0.10,0.15,0.20,0.30,0.40,0.50,0.60,1.00,1.50,,,

,C,11.29,7.48,6.81,6.29,8.40,7.48,6.81,6.29,5.88,4.83,4.16,,,

,β1,11.31,5.19,4.34,4.06,3.96,3.91,3.92,3.95,3.98,4.07,4.14,,,

,β2,-6.59,-0.26,1.00,1.63,2.03,2.54,2.86,3.09,3.26,3.66,3.89,,,

,,,,,,,,,,,,,,,,

四、,制作折线图,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,

五、,计算误差,,,,,,,,,,,,,,

,,k=0.2,,K=0,,,,,,,,,,,

,Y,Y-hat,MSE,Y-hat,MSE,,,,,,,,,,

,16.3,14.98052039,1.741026431,16.48073048,0.032663506,,,,,,,,,,

,16.8,16.97910035,0.032076936,17.23664987,0.190663113,,,,,,,,,,

,19.2,18.77496237,0.180656986,18.65163728,0.300701673,,,,,,,,,,

,18,18.57224443,0.327463687,19.31070529,1.717948356,,,,,,,,,,

,19.5,19.37358304,0.015981247,19.12329975,0.14190308,,,,,,,,,,

,20.9,19.1708651,2.989907495,19.78236776,1.249101828,,,,,,,,,,

,21.1,19.56676783,2.350800876,20.91309824,0.034932269,,,,,,,,,,

,20.9,20.57082439,0.108356583,20.06662469,0.694514415,,,,,,,,,,