灰色預(yù)測模型在Excel中的實現(xiàn)及運用

林建潮

紹興第二醫(yī)院，312000 浙江 紹興

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

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

1.1 建立GM(1,1)模型

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

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

1.3 模型的檢驗

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

C=S2/S1，

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

表1 灰色序列模型精度評定標(biāo)準(zhǔn)

1.4 外推預(yù)測

若擬合結(jié)果模型精度評價為優(yōu)良，則表明模型預(yù)測效果好，可以用于外推預(yù)測；若擬合結(jié)果模型精度評價為不合格，則不可直接用于預(yù)測，須經(jīng)殘差修正后，再進(jìn)行外推預(yù)測[7]。

2 灰色預(yù)測模型在Excel中的實現(xiàn)

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

建一Excel工作簿，在工作表中進(jìn)行如下操作。

(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)評價指標(biāo)與結(jié)果

點擊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),"優(yōu)秀",IF(AND(O16>0.8,O14<0.5),"合格",IF(AND(O16>0.7,O14<0.65),"基本合格","不合格")))”，J列最后3行為預(yù)測結(jié)果。

3 實例驗證

以區(qū)域護(hù)理人力資源預(yù)測在灰色模型中的應(yīng)用為例[8]，數(shù)據(jù)來源于紹興市某區(qū)統(tǒng)計局網(wǎng)站年鑒，計算2004—2015 年該區(qū)每千人口護(hù)士數(shù)，并以此為基礎(chǔ)預(yù)測2016—2018年該區(qū)每千人口護(hù)士數(shù)，計算2016—2018年該區(qū)護(hù)理人力資源需求量。

在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”，模板將自動生成各項統(tǒng)計結(jié)果。其中α=-0.098 3，μ=0.981 0，C=0.207 5，P=1，該預(yù)測模型評價等級為“優(yōu)秀”。詳見圖1。

圖1 灰色模型預(yù)測某區(qū)域護(hù)理人力資源在Excel中的實現(xiàn)

4 討論

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

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