灰色預測模型在Excel中的實現及運用

林建潮

紹興第二醫院，312000 浙江 紹興

灰色預測模型是一種以時間序列資料為基礎，通過建立有規律的轉換生成新數列的回歸方程，并應用該回歸方程對時間序列相關數據進行預測?；疑A測模型GM(1，1)表示1階、1個變量的微分方程模型，是一階N個變量的微分方程模型GM(1，N)中最基本的一種[1]。由于該模型不容易受樣本量大小和概率分布的影響，適應性較強，已被廣泛用于艾滋病、淋病、結核病、流感等多種疾病的預測[2-4]。本文結合護理人力資源需求預測實例，利用Excel編制灰色預測模型GM(1，1)，為廣大醫務工作者預測時間序列相關數據提供方法和便利。

1 灰色預測模型GM(1，1)的建模步驟[5]

1.1 建立GM(1,1)模型

Zt=(Yt+Yt-1)/2(t=1,2,3,…n)。

式中，X0為初始時刻(t=0)的原始數據；μ，α為待定系數，根據最小二乘法估計參數向量，其表達式為：

1.3 模型的檢驗

灰色序列預測模型須經精度檢驗后才可以進行外推預測，其精度檢驗通常采用后驗差比值C和小誤差概率P的方法。

C=S2/S1，

根據C和P的計算結果，按表1的方法可綜合評定該模型的精度[6]。

表1 灰色序列模型精度評定標準

1.4 外推預測

若擬合結果模型精度評價為優良，則表明模型預測效果好，可以用于外推預測；若擬合結果模型精度評價為不合格，則不可直接用于預測，須經殘差修正后，再進行外推預測[7]。

2 灰色預測模型在Excel中的實現

創建灰色模型GM(1，1)在Excel中實現的“模板”文件:

建一Excel工作簿，在工作表中進行如下操作。

(2)計算t值(0，1，…，n，n+1，n+2，n+3)，點擊B3單元格→“0”；點擊B4→“=IF(OR(C4<>"",C3<>"",C2<>"",C1<>""),B3+1,IF(OR(C4<>"",C3<>"",C2<>""),COUNT(C:C)+2,IF(OR(C4<>"",C3<>""),COUNT(C:C)+1,IF(OR(C4<>""),COUNT(C:C),""))))”，單擊單元格右下角下拉(以下簡稱“下拉”)至B25。

(3)計算Xt-0值，點擊D3單元格→“=IF(AND(B3>0,C3<>""),C3,"")”，下拉至D25。

(4)計算Yt值，點擊E3單元格→“=IF(C3<>"",ROUND(C3,3),"")”，點擊E4單元格→“=IF(C4<>"",ROUND((E3+C4),3),"")”，下拉至E25。

(5)計算Zt值，點擊F3單元格→“=IF(D3<>"",(E2+E3)/2,"")”，下拉至F25。

(7)計算XtZt值，點擊H3單元格→“=IF(D3<>"",C3*F3,"")”，下拉至H25。

(10)計算δt值，點擊K3單元格→“=IF(AND(B3>0,C3<>""),ROUND((C3-J3),4),"")”，下拉至K25。

(13)評價指標與結果

點擊O3單元格→“=SUM(D:D)”，O4單元格→“=SUM(F:F)”，O5單元格→“=SUM(G:G)”，O6單元格→“=SUM(H:H)”，O7單元格→“=ROUND((COUNT(D:D)*O5-POWER(O4,2)),4)”，O8單元格→“=ROUND((O3*O4-COUNT(D:D)*O6)/O7,4)”，O9單元格→“=ROUND((O5*O3-O4*O6)/O7,4)”，O10單元格→“=ROUND(AVERAGE(K:K),4)”，O11單元格→“=ROUND(STDEVP(D:D),4)”，O12單元格→“=ROUND(STDEVP(K:K),4)”，O13單元格→“=ROUND(0.674 5*O11,4)”，O14單元格→“=ROUND((O12/O11),6)”，O15單元格→“=SUM(M:M)”，O16單元格→“=ROUND((O15/COUNT(D:D)),4)”，O17單元格→“=IF(AND(O16>0.95,O14<0.35),"優秀",IF(AND(O16>0.8,O14<0.5),"合格",IF(AND(O16>0.7,O14<0.65),"基本合格","不合格")))”，J列最后3行為預測結果。

3 實例驗證

以區域護理人力資源預測在灰色模型中的應用為例[8]，數據來源于紹興市某區統計局網站年鑒，計算2004—2015 年該區每千人口護士數，并以此為基礎預測2016—2018年該區每千人口護士數，計算2016—2018年該區護理人力資源需求量。

在C3-C11單元格依次輸入“0.998，1.311，1.325，1.395，1.524，1.631，1.823，1.890，2.031，2.649，2.939，3.028”，模板將自動生成各項統計結果。其中α=-0.098 3，μ=0.981 0，C=0.207 5，P=1，該預測模型評價等級為“優秀”。詳見圖1。

圖1 灰色模型預測某區域護理人力資源在Excel中的實現

4 討論

灰色預測模型GM(1，1)主要通過時間數列的研究去探尋研究對象的內在規律。該模型適用于呈單調上升或單調下降的數據序列，并允許對較少的年度數據進行預測分析。該方法計算工作量小，實用性較強，對序列樣本含量及概率分布無嚴格要求，克服了其他數據預測模型要求原始數據較多的缺點?；疑Ｐ瓦m用于較短時間序列內的預測，隨著時間推移，不確定因素的加入等，將對系統造成影響，從而對預測時間序列數據精度造成影響。因此，若要對數據進行長期預測時，需充分考慮外部因素(如經濟、社會、環境等)，借鑒外部相關因素對模型進行矯正，生成新的灰色預測模型GM(1，1)進行預測。

灰色預測模型在Excel的實現，方法科學簡便，計算精確。Excel作為大家熟悉的Office辦公系統中的統計處理軟件，與其他文檔間容易復制粘貼，數據易于轉換。本文介紹的灰色預測模型GM(1，1)Excel模板有以下優點：①無需購買特殊軟件，在普通辦公計算機上都能運行，操作簡便。②原始數據與分析結果實現動態實時連接，更改原始數據，立刻顯示新的結果。若將本模板文件復制到其他計算機上后，可作為分析軟件使用。③雖然本程序最高僅允許運行23個時間序列內的相關數據，但基本滿足醫療相關管理部門對預測相關業務范疇的需要。