1AbCDEFGHijKLMNOP12-回报率月扌票A股票B股票C股票D股票E股票F相关系数矩阵「股票A股票B1——1J/1口不套"口十31994年0.351-0.115-0.425-0.211-0.0760.233股10.29441995年0.7080.2470.3720.2230.0210.357股票B0.294151996年0.7330.3670.2550.5820.1280.078股票C0.8060.42861997年-0.203-0.427-0.049-0.0940.071-0.272股票D0.1630.54971998年0.166-0.045-0.0570.2750.137-0.135股票E0.5040.34781999年-0.2660.0160.090.0790.0220.225股票F0.0650.72992000年0.0120.4750.335-0.1890.20.366股票A股票B102001年-0.026-0.204-0.028-0.7430.0910.048协方差矩阵0.4540.281112002年1.064-0.1490.697-0.2620.2240.046股:票A0.4540.2060.038122003年0.1940.3680.3111.8680.2070.264股票B0.2810.0380.07913期望回报率0.2030.0530.150.1530.1030.121股票C0.2940.1080.03514方差0.2060.0790.0870.4440.0080.039股票D0.6660.0490.10315标准差0.4540.2810.2940.6660.0910.198股票E0.0910.0210.00916标准差0.4540.2810.2940.6660.0910.198股票F0.1980.0060.04117\协方差矩阵股扌票A股票B股票C股票D股票E股票F18股票A0.2060.0380.1080.0490.0210.006不正确的数值19股票B0.0380.0790.0350.1030.0090.041方差0.2290.08820股票C0.1080.0350.0870.0440.0190.015标准差0.4780.29621股票D0.0490.1030.0440.4440.0190.027标准差0.4780.29622股票E0.0210.0090.0190.0190.008-0.00123股票F0.0060.0410.0150.027-0.0010.03924r协方差矩阵股票a股票B股票C股票D股票E股票F25股票A0.2060.0380.1080.0490.0210.00626股票B0.0380.0790.0350.1030.0090.04127股票C0.1080.0350.0870.0440.0190.01528股票D0.0490.1030.0440.4440.0190.02729股票E0.0210.0090.0190.0190.008-0.00130股票F0.0060.0410.0150.027-0.0010.03931QRST12股票c股票D股票E股票F30.8060.1630.5040.06540.4280.5490.3470.729510.2260.7240.25460.22610.3170.20770.7240.3171-0.08280.2540.207-0.08219股票c股票D股票E股票F100.2940.6660.0910.1981110.1080.0490.0210.006120.0350.1030.0090.041130.0870.0440.0190.015140.0440.4440.0190.027150.0190.0190.008-0.001160.0150.027-0.0010.03911718190.0960.4930.0090.044200.310.7020.0960.209210.310.7020.0960.20922232425262728293031UVWXYZinD13:=AVERAGE(D3:D12)inO3:inD14:=VARP(D3:D12)inP4:inD15:=SQRT(D14)inQ5:inD16:=STDEVP(D3:D12)inR6:inS7:inD18:=COVAR($D$3:$D$12,D3:D12)inT8:inE19:=COVAR($E$3:$E$12,E3:E12)inF20:=COVAR($F$3:$F$12,F3:F12)inO4:O8:inG21:=COVAR($G$3:$G$12,G3:G12)inP5:P8:inH22:=COVAR($H$3:$H$12,H3:H12)inQ6:Q8:inI23:=COVAR(I3:I12,I3:I12)inR7:R8:inD19:D23:{=TRANSPOSE(E18:I18)}inN11:N16:inE20:E23:{=TRANSPOSE(F19:I19)}inO10:T10:inF21:F23:{=TRANSPOSE(G20:I20)}inG22:G23:{=TRANSPOSE(H21:I21)}inO11:inH23:=I22)13:I13),(D3:I1inD25:I30:{=MMULT(TRANSPOSE(D3:I12-DinO19:=VAR(D3:D12)inO20:=SQRT(O19)inO21:=STDEV(D3:D12)2-D13:l13))/10}AAAB2=CORREL($D$3:$D$12,D3:D12)3=CORREL($E$3:$E$12,E3:E12)4=CORREL($F$3:$F$12,F3:F12)5=CORREL($G$3:$G$12,G3:G12)6=CORREL($H$3:$H$12,H3:H12)7=CORREL(I3:I12,I3:I12)89=TRANSPOSE(P3:T3)10=TRANSPOSE(Q4:T4)11=TRANSPOSE(R5:T5)12=TRANSPOSE(S6:T6)1314{=TRANSPOSE(D15:I15)}15{=D15:I15}1617=O3*$N11*O$1018192-D13:l13),(D2012-D13:l13))/1O}~2T22。