用Excel实现灰色预测法

1、用Excel进行灰色预测法i生成数灰理论中常用的生成方程有：累加生成，记为 AGO(Accumulated Generating Operation);累减生成或逆累加生成，记为 IAGO (Inverse Accumulated Generating Operation)。若记工为原始数列，为件厂次累加生成后( id为尸AG。)的生成数列 即二(WQ)产(2 )%)产-e(1),尹，%)则有AGO算式；=)(加)=(，”*F 1；(2)+%k_1)+# %k)巾=1对于其41iag。算式为：於）（/）（初=/）出（户（蔺）=/“（J "（公）一/）（/）（-1）a0x "

2、（简）=（：）（'"）-）（*"）（。- 1）/»-.）=，（/）（磅）一 d'D（#（I）（7=1,2 ）上述关系还可转化为：（l）（k）=K°）（/）（初-淤）（*） （- 1）=/）（僧一/）（。一 1）=£«1）（加）-£，1）（。=£/"）（附 +，（4） - £/0（，） m-/K-14f协户（户（初=（户（简）一户（g（/一 1）,=/_"（后 _/_D（- 1）=9一©（）-£/"（'） m-m=q）+.L（m -

3、£丁2）（/） w=lw=l=W 2M/）（J"（6）=Jf（蔺 心）（d" （初=收（身 上述关系表明：对.丹作，,次AGO得.0,将.0作尸次IAGO得产，可记为v（0） AGO（r A. AA（o）IAGO Jr）例 77 行原始数列为 d°3.278, 3.337, 3.39, 3.6 jl(5 )=£/*(/) kf")(4)十d°(5)=13.684 +3.85=17.534,l-AGO生成数列X为(1尸户(1),山)(Z), An)(3),？0(5)=3.278,6,615,10.005,13.684.17.5

4、34)9, 3.85),试作一 次累加生成。解：产(加£产(闻W=161,才=£，。)(力=,。()=3.278 m-g2,户二r/x-1=r(0)(l)-Kr(0)(2 )=3.27 8 +3.337=6.615,公3. x(1) (3)=工,r(0) (/)f (2)+$3 )=6.615+3.39=10.005,444,公)(4)=£*(力JW-1=AX，)(3)-k?D)(4)=10.005+3.679-13.684,我们也可在Excel中实现上述计算。打开Excel,将原始数列依照图71 所不键入到单兀格中，在单兀格B4犍入公式“B3”，在单兀格C4中健

5、入公 A UB4IC3V,回乍后选中C4,拖动复制于衲至单元格F4,就可得到1-AG0 生成数列，。TABC 1 D 次累加生成蒙EF 12序号123453J。，3 27S3 337no3 6793 854扪3.27S6. 61510.00513. 6M17.534图7-1例7-2 试对上述l-AGOx 做累减生成。解：户(J)(助K(周(.V0)=(打一不(11) =山)(曲一幺”(万一1)=大(左一1)十一；I(才1)=/。)出，41, (l)(x(1)(l)=r(,)(l)-.r(1)(0) (取 户(0)=0) =?n(l)=o)(l)=3.278,42, (J)(2)= Q(2)-工

6、(1) =6 (515-3.278 =3.337,43, (，”(3)=/)-央(2) =10.005 -6.615=3.39I, (央(4)=户(4)一工(3) =13.68410.005 =3 679Q5, K”(J：)(5)"D(5)"D(4) = 17.534-13 684=3.85从此，有/：5十)，逐*匕),)，K"(Q(4), /kJ,)-/1：(l)f/1，(2)-?1)(l),xI1)(3)-A<1)(2)r>xl)(4)-/1)(3)flr1)(5)-xcl4)=/)Q), QQ),产(4),6= 3.27B P 3.357 , 3

7、凸9, 3.679, i h5 同样，我们也可在Excel中实现上述计算，打开由CCm 将原始数列依照 图7U所示键入到单元格B3至F3中，在单元格B4键入公式科B3Z在单元 格C4中键入公式“B4 C3 回十后选中C4,拖动豆制手柄至单元格F4, 就可得到I AGO生成数列。叫-暮3CDEF1一次累减生成数23序号1O345护e. eis10. 00513 6S417. 5%i嘉3.2TSE;编M3。3.6793. £5图F2关联度如果说参考数列为的，比莪数列(因素数列)为屈3=12门).且xD-fr0(l),芯(2)殉()工/Q)为(丹)2厅)则称in ill 111 in .1

8、 (Z) .i; (jfr) | + pniHKniax 艮(#)一*4)(#) = ' ' 1 *J住g) -七+ p max max |踊一再(初为曲线不与可花第/点的矢械系数，上式中：(1)河(为一子(幻| = A. (#)称为第#点死与耳的绝对差.(2】mini可小(外一七(*)|称为两级最小差，其中min4(流) ,£|是第一级最小差，这表示在第苍曲线上，找各点与 演的最小差.inin(nii叫(外一I；(力)|)是第二级最小差,表示在各曲线中找出的最小差法础 Jh.再按，=1 我所有曲线即中的最小差。(3 ) n】axinax%(4)-一工(4)是两级最大

9、差,其意义与最小差相似。(4) p称为分辨系数，是。与1间的数，一般取p=0.5,综合各点的关联系数,得整个与曲线与参考曲线即的关联度5 由下式计律，(5)对于单位不同或初值不同的数列做关联度分析时,一般要做处理. 使之无量纲化，归一化过程C例7-3有原始的参考数列为及比较数列方、心分别为#=5(1)，丸(2),加，JO(4). ,Fo(5), >o(6)= 20, 22, 40, 45, 60, 80),JtnQ)，乃(2)，(3)户(4),八(5)，j，(6)= 30, 35, 55, 60, 70, 90,月=5(1)，/2(2)，月，)2(4)，力(5),力(6)=40, 45,

10、 43, 55, 65, 70)试求.门及法与M的关茯度。解.第一步“初值化”(归一化过程上电 1)册(2) *3) .% .% v0(6)R'跖抑册M。) M(l)=(1, 1.1, 2, 2.25, 3, 4,%(1)%(2)出(3)网(4) #(5)M QDi Q)' /Q)' M (1)'即 1)_ 30 35 55 60 70 90 1- 3O,3O,3O,SO'io'SOJ=(1, 1.166, 1.8333, 2, 2.333, 3,赤二!乃乃Q)乃G)乃G)乃(5)兄伯)正'为为(1)'为(1)'为必=1

11、, 1.125, 1.075 , 1.375, 1.625, 1.75)第二步求关联度系数中两级差。设 劣(4)为 Aj (4) = % (才)-田(#) |尸4 (1), 4 (2),. (3),. (4), 4 (5),4(6)A1Cl) = (.roa)-.r1O)| = |l-l| = OAj(2) = |x0(-)-1 (2)|=|1.1-1 166卜 0.0667A1(3)=|io(3)-r1(3)| = |2- 334| = 0 1667A： (4)=0.25A1 (5)=0.6667Ai (6)=1由于 A=0,0.0667,0.1667,0.25,0.6667,1又 A2()

12、 = |.r0()-x2(r)|所以幺=幺 A(2),A2(3)A(4)A(5)H(6)=(0.0.0250925 0.875.1.375,2 25min xo (4)- x (左)| = i呼i 4 (同二 n】in 孙A 4 ,A(4)A(5)A(6)0 =min0,0.066,0.666.0,25,0 686,1=0m ill min xQ(/c) - xt (A) = 0max W)-.Vj (X0| = niax (/)=inax0,0.066,0 666,0.25,0.686,1)=1niax|xjj(Ar) - x2 (A)| = maxA2 (A)=in ax0,0.025,0

13、.925,0.875,1.3 75,2.25=2.25max n%x" (#) - xs (4)|=max max |.v0 (Hr) - x ()|,max|.rD (Jr)盯(")=max 1,2.25= 2.25第三步求美联系数0m in nun(4) - xt (4)| + p in ax ni ax J” ()-.三(十)|51|%(4)-.(力)| + p maxmax|jo(A) 一.(4)|由于一七(A)| = A1，取p=Q5,将两级车代入，即两级最小差min min氏(#) - xt (A)| = 00.5x2.251.125&i（）=max 1

14、11 ax ,。(N)-三()|= 2.25Aj(?) +0.5x2.25 A1 (2)+1.1251.125如二1.125,1(1)+1.125 -Q + L125 -却=1.1251.125= 0.9441A(2)"125 0.066+1.1254(3) =1.125°25=0.8714(3)+ L125 0.166+1.125_ = 0.818"5)=(5)+1.125口25=02790.66 + 1.125左6)=1.125A(6) + L1251 + 1.125I 105 = 0.52941.125Aj(4) + 1.125 0.25 + 1.125自=

15、刍刍刍自自&=1,。9445,08714,0.818,0.6279.0.5294相似地有&式1)=1.125式2)=1.1250.025 + 1.125= 0.978| 175& (3)=：= 0.54870925+1.1251.1250.875 + 1.125= 0.5625加(5)=1.125= 0.451.375 + 1.125虞二1.1252.25 4 1.125= 0.333&=&刍(2)刍刍(4)刍刍(6)=1,0.978,0.5487。562so 450333第四步求关联度。1 n1 66K=7 &4&Q开刍(3)+刍(4)

16、+。+刍(6) 6=*(1+0.9445+0.871+0.818十0.6279,0.，294)二079841 n，；二-£,(左)=0.6454791 >-i -这说明冷与xo的关联程度小于力与近的关联程度。在Excel中实现上述计算过程。首先，将原始数据键入在单兀格区域C3： H5,也将分辨系数p=0键入单元格J2中：其次，初始化数据，在单元格 C6中键入公式“C3/$C3”，回车后再选中该单元格并拖动复制手柄至单元格 H8,就可得到初始化后的三组数据；再次计笄两级差，在单元格C9中谗入 公式“ABS(C$6-C7)", |口|乍后，再选中该单兀格并拖动复制手柄至单

17、兀格 H10.就可得到力和灯与题的绝对差A；(乃(片1, 2： 41, 2, 3, 4, 5, 6)。 在单元格19中键入公式“ MIN(C9:II9) ”，在单元格J9键入公式 “MAX(C9:H9)L选中这两个单元格并拖动复制于柄至单元格110与J10, 得到两数数列的一级最小与最大差，再在单元格Ill中犍入公式“MIN (19： 110)。在单元格JH中键入公式“MAX (J9：分别得到二级最小差与最大差。最后计算关联系数与关联度：在单元格C13中键入公式 “($I$ll$J$2*$J$ll)/(C9+$J$2*$J$n)"，回车后再选中将公式复制至单元格 区域C13H14,就

18、可得到两组数据的关联系数；在单元格113中说人公式“AVERAGE（C13：H13）Z回车后再选中该单元格,并将其公式复制到单元格 114,就可得到阳、叼各与刖的关联度分别为0.7984207和0.6454791,比较 可知对与女的关联度大于巧与比的关联度（见图7-4）。A LCD1FGHIL J JI1关联度il算表2 1序号123456分辨系效P=0.5320224045602。4力3?3555607025V24045436565?0初 比为11.122 25347均11.16671.8333322.333338化电L1.1251.0751.3751.6251.75一家最小箜一废最大差91

19、00 06670.166670 25Q66C11011。200. 0250. 9250. 8751. 3752. 2502.26111213二瞪最小（大）差02.25关关联宸r判断10S4410.870970.8182C.62790.52940 7984207*1与太0的关 族爻筑于"与 x0的关限度1410. 97830. 548780. 56250.450. 33330 6454791图7-43 GM (1, 1)模型例 7-4 有数列为4*)=2374, 3278, 3.337 T 3M 3679,试建立如(1, 1)解：第 步 作AGQ生成产=£，卅(，在EkwI中

20、，将基础数据 键入到单人格区域ALH3和A4:B22中，在单兀格CS中键入公式"C3二 任 单元格D5中犍入公式“C5+D3”，选中D5,利用复制手柄籽其父制至9元格G5从而.得生成数列：J叱商2.874,6.15L 9.489, 12.879. 16.558)第二步确定数据矩阵日外，利用-眇户(1) + (2)L(户(2)+，气功1一 L(户+,“4) ，一一泊+一户)在单元格D6中键入公式"-1/2+(05+D5)Z单元格D7中64入1.左单 M描DZ中键入公式“D3”，选中单无格区域D6: D8,利用复制手柄将其复 制至单元格G8,就可得到X和心 (如图7-5所示)。

21、即得ABCD J_EFGH1GM (K 1)模型孩费计翼表2 ,方号K12345mac锻”)2.57432783.3373.2»36794AG3生成 数初始化W'K1 1.01799S781.0341671751 122330595Q&)2 k46. 1523.48912. 87916. 5586析好工ERTrT4.51378205-1471857初谀用应R. VKT1 11185 jNyj =3.2783.3373.3936为91011计算逆矩 阵btb=423.243<75-38.236-38.236 T 1 研)I=0.0173173480.1655365

22、。16”加61.8323637p / -132.953744&-0.037204uXo=-82. 3925312半必少然D %一13,6843 0653 13般1A23741314M测HI型85.26513424色00WR低卜-82.39253会蛀石为。导致搐型幺°)色+1) =317以加07卅:，*0.515尹因2. £746.1069389 9. 469588712.54229316,55597216还原劲提2. S743. 2323389 3. 35154983. 18170443.613678017IX退差0,0453611 -0,M755-o. oerod

23、0.0653211相对早差1.41214 -0.5259M-2.7051%1.7755%19严旭3. 2925347 3.41733873. 54687333. 681318202122美联专标初始化攵6)1 1.05730511. D772471l.l：808C2除因0 0.01390630. M3CT99C.00425C40.0430799S也1 0. 51707J0. JJJJJJJJ8Jbl9Z60.6720583图7-5-4.5131-7.821B -11.184 1-14.7185 1X/%二廿。阳(4)，。)=3.278,3.337 3393679第三步计算(*口尸选中单元格区域

24、C9, D10,按下等号按钮.等待进入公式.在编川栏中 犍 入公式 “ =MMULT(D6：G7.TRANSPOSE(D6：G7) M ,然后 同时按 下 “Chi十Shift十Enter”键，就可得到Wk再选中单元格区域F9: G10,按下 等号按钮,在编辑栏中键入公式“MINVERSE(C9:D10)”,同时按下组合健 “ Ctrl+ShifH-Entcr "，就可得到r-4.513-4.513-7.82 -11.184 一 14.7185-7.82111-11.184-14.7185423.2434876-38.236-38.2360.017317348 0.165536520

25、.1655365261.83236376从得出的结果口J以看出8%的特点：(1) £是对称矩阵。(2)方3是2 X2方阵，其右下角对角元素代表B的行数。第四步求参数列。利用公式)=(方为一|/、，首先选中单元格区域CILCIZ,按下等号按钮, 在编辑栏中健入公式“MMULT(D6c7.TRANSPOSE(D8:G8)“，同时按下组 合健“Ctrl+Shift+Entcr"，就可得到的力。再选中单元格区域E1LE12,按下 等号按钮.在编辑栏中犍入公式“MMULT(Fg：G10,Cn：C12)”,同时按下组 合键“Ctrl十Shift+Enter”,就可得到3.2780.01

26、7317348 0.165536 -4.513 -7.82 -11.184 -14.71850.165536521.8329611113.3373393.679J -0.037204|3.065363313第五步确定模型。由上一步计算可得预测模型为0 037204v(1) =3 065363313d/从而可得递推模型为父)伏+1)=(«°)(1)_2州-必 + 上a aV 叫 1)=2 8F= -82.39253423.065363313Z - -0.037204即有+ 1) = 85.26653424/由，"一 82.3925342如图7-5中的第13行所示。第

27、六步精度检验之残差检验.并先利用所得预测模型计算数据。我们在单元格C15中键入公式 “C13+F13 ”，在单元格 D15 中键入公式 “SC13*EXP(-$E11*C2)+$F13”， 选中该单元格，拖动复制手柄将其公式复制至单元格G15,得到数列31)(4); 在单兀格C16中世入公式"Cl5",在单元格D16中键入公式“D15 C15”, 选中该单元格，拖动复制手柄将其公£复制至单元格G16,得到数列¥°)(流)： 即有：生成模型计算数据.审(左)还原数据¥。)(4).i(1)(2)=6.1060389.i (0)(2)=3.

28、232038914。)(3)=94605887.V：0)(3)=3.3545 497633(4)=12.942293土(4 )=3.481704402% (5)=16.5 55971933：°)(5 )=3.613678854在单元格D17中健入公式“D3 D16”，在单元格D18中键入公式 “D17/D3”,先选中单元格D18设置其格式为百分数，再选中单元格区域D17:D18,施动曳制手柄至单元格G18,就可得到绝对误差数列0(向和相对 误差数列e(身，即有;相对误差 e(2)-L4021% 43)=-0.5259% e(4)=-2.7051%) c(5)=L7755%>绝对

29、误差 67(2)-0.0459611 (3)=-0.01755 9(4)=-0.091704 (7(5)=0.065321146 第七步 精度检验之二关联度检验。以f的导数作为参考数列与土作关联度分析。由预测模型3 (0=85.206534241 03 7304,-82.3 925342对上式求导数，有土(/)=0.037204乂8,26653424又一.m2°"=3.1722 88707/"7"4'离散化后，得i<0) ( + 1) = 3 172289eC 037：D4/在单元格D19中健入公式“$C14*EXP(-$E11*C2)&q

30、uot;，选中该单元格后 拖动复制手柄，将其公式复制至单兀格G19,即有=1, *(2)= 3.29233474 = 2, x(o)(3)= 3.4173387 / = 3,，(4) = 3.5468733 。=4, .泮(5) = 3.681318016为了确定两组数据小)(后和2。)(处的关联度,分别将它们初始化,即在 单兀格D4中键入公式“D3/$D$3”，回午后再选中该单元格，利用复制手柄 将其公式复制至单元格G4：在单元格D20中键入公式“D19/$DS19”，回车 后再选中该单元格,利用复制手柄将其公式复制至单元格G20,这样得到了 初始化后的两组数据.下面计算绝对差()，在单元格D21中键入公式“ ABS(D20-D4