灰色预测模型在Excel中的实现及运用

林建潮

绍兴第二医院，312000 浙江 绍兴

灰色预测模型是一种以时间序列资料为基础，通过建立有规律的转换生成新数列的回归方程，并应用该回归方程对时间序列相关数据进行预测。灰色预测模型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)主要通过时间数列的研究去探寻研究对象的内在规律。该模型适用于呈单调上升或单调下降的数据序列，并允许对较少的年度数据进行预测分析。该方法计算工作量小，实用性较强，对序列样本含量及概率分布无严格要求，克服了其他数据预测模型要求原始数据较多的缺点。灰色模型适用于较短时间序列内的预测，随着时间推移，不确定因素的加入等，将对系统造成影响，从而对预测时间序列数据精度造成影响。因此，若要对数据进行长期预测时，需充分考虑外部因素(如经济、社会、环境等)，借鉴外部相关因素对模型进行矫正，生成新的灰色预测模型GM(1，1)进行预测。

灰色预测模型在Excel的实现，方法科学简便，计算精确。Excel作为大家熟悉的Office办公系统中的统计处理软件，与其他文档间容易复制粘贴，数据易于转换。本文介绍的灰色预测模型GM(1，1)Excel模板有以下优点：①无需购买特殊软件，在普通办公计算机上都能运行，操作简便。②原始数据与分析结果实现动态实时连接，更改原始数据，立刻显示新的结果。若将本模板文件复制到其他计算机上后，可作为分析软件使用。③虽然本程序最高仅允许运行23个时间序列内的相关数据，但基本满足医疗相关管理部门对预测相关业务范畴的需要。