王 楠

（西安體育學(xué)院，陜西西安，710068）

基于EXCEL VBA的灰色數(shù)列預(yù)測(cè)程序?qū)崿F(xiàn)

王 楠

（西安體育學(xué)院，陜西西安，710068）

本文利用VBA在EXCEL下編寫(xiě)宏程序，實(shí)現(xiàn)數(shù)列灰色預(yù)測(cè)，并選取02年至09年《中國(guó)體育事業(yè)統(tǒng)計(jì)年鑒》中我國(guó)運(yùn)動(dòng)員在奧運(yùn)會(huì)、亞運(yùn)會(huì)、各類(lèi)世界錦標(biāo)賽中獲得世界冠軍的個(gè)數(shù)作為原始數(shù)據(jù)列，對(duì)2010年至2013年獲運(yùn)動(dòng)員世界冠軍的個(gè)數(shù)進(jìn)行驗(yàn)證性預(yù)測(cè)。

灰色數(shù)列預(yù)測(cè)；VBA；EXCEL

1 灰色系統(tǒng)及灰色數(shù)列預(yù)測(cè)

1.1 灰色系統(tǒng)的基本概念

一個(gè)系統(tǒng)的內(nèi)部特征是完全已知的，即系統(tǒng)的信息是充足完全的，我們稱(chēng)之為白色系統(tǒng)。若一個(gè)系統(tǒng)的內(nèi)部信息是一無(wú)所知，只能從它同外部的聯(lián)系來(lái)觀測(cè)研究，這種系統(tǒng)便是黑色系統(tǒng)。灰色系統(tǒng)介于二者之間，灰色系統(tǒng)的一部分信息是已知的，一部分是未知的。

1.2 灰色數(shù)列預(yù)測(cè)

灰色預(yù)測(cè)是灰色系統(tǒng)理論的重要組成部分，是一種對(duì)含有不確定因素的系統(tǒng)進(jìn)行預(yù)測(cè)的方法。它利用連續(xù)的灰色微分模型，從而預(yù)測(cè)事物未來(lái)發(fā)展趨勢(shì)的狀況。對(duì)系統(tǒng)的發(fā)展變化進(jìn)行全面的觀察分析，并做出長(zhǎng)期預(yù)測(cè)?；疑A(yù)測(cè)的基本方法大致可分為數(shù)列預(yù)測(cè)、災(zāi)變預(yù)測(cè)和系統(tǒng)預(yù)測(cè)，在這些預(yù)測(cè)方法中，灰色數(shù)列預(yù)測(cè)應(yīng)用最為普遍，灰色數(shù)列預(yù)測(cè)是指利用GM(1,1)模型，對(duì)時(shí)間序列進(jìn)行數(shù)量大小的預(yù)測(cè)。

GM(1,1)預(yù)測(cè)模型為：

本文選取殘差模型對(duì)模型進(jìn)行修正處理，提高預(yù)測(cè)的準(zhǔn)確性和精度。

2 EXCEL VBA的灰色數(shù)列預(yù)測(cè)程序?qū)崿F(xiàn)

對(duì)GM(1,1)預(yù)測(cè)模型及其殘差檢驗(yàn)過(guò)程，我們可以運(yùn)用EXCEL2007提供的VB編輯器編寫(xiě)VBA宏程序來(lái)實(shí)現(xiàn)。

2.1 預(yù)測(cè)的VBA程序設(shè)計(jì)

Private Sub hsyc_Click()

Dim x(50), y(50), z(50), q(50), p(50), B(50, 50), c(2, 2), d(2, 2), e(50, 50), f(50, 50)

Dim m(50), h(50), r(50), v(50)

k = Cells(2, 1).Value:n = Cells(3, 1).Value:n = n + k

For i = 0 To k - 1: p(i) = Cells(2 + i, 3).Value: Next i

For i = 0 To k - 1: For j = 0 To i: m(i) = m(i) + p(j): Next j: Next i

For i = 1 To k - 1: B(i, 1) = -1 / 2 * (m(i) + m(i - 1)): B(i, 2) = 1: Next i

For i = 1 To k - 1: c(1, 1) = c(1, 1) + B(i, 1) ^ 2: c(2, 1) = c(2, 1) + B(i, 1): Next i

c(1, 2) = c(2, 1): c(2, 2) = k - 1:t = c(1, 1) * c(2, 2) - c(1, 2) * c(2, 1)

d(1, 1) = c(2, 2) / t: d(2, 2) = c(1, 1) / t: d(1, 2) = -c(1, 2) / t: d(2, 1) = d(1, 2)

For i = 1 To k - 1: B(1, i) = -1 / 2 * (m(i) + m(i- 1)): B(2, i) = 1: Next i

For i = 1 To 2: For j = 1 To k - 1: e(i, j) = d(i, 1) * B(1, j) + d(i, 2) * B(2, j): Next j: Next i

For i = 1 To 2: For j = 1 To k - 1: f(i, 1) = f(i, 1) + e(i, j) * p(j): Next j: Next i :a = f(1, 1): u = f(2, 1)

For i = 0 To n - 1: y(i) = (p(0) - u / a) * Exp(-a * i) + u / a: Next i :x(0) = p(0)

For i = 1 To n - 1: x(i) = y(i) - y(i - 1): Next i

For i = 0 To k - 1: m(i) = 0: c(1, 1) = 0: c(2, 1) = 0: f(i, 1) = 0: Next i

s1 = 0: s2 = 0

For i = 0 To k - 1: s1 = s1 + Abs(x(i) - p(i)): Next i

For i = 0 To n - 1: Cells(2 + i, 4).Value = x(i): Next i

Cells(2 + k, 5).Value = "總殘差s1："

Cells(3 + k, 5).Value = s1

For i = 0 To k - 1: Cells(2 + i, 5).Value = x(i) -p(i): Next i

For i = 0 To k - 1: Cells(2 + i, 6).Value = (x(i) -p(i)) / p(i) * 100: Next i

End Sub

2.2 周期修正的VBA核心程序?qū)崿F(xiàn)

h(1) = (r(0) + r(4)) / 2: h(2) = (r(1) + r(5)) / 2: h(3) = (r(2) + r(6)) / 2: h(4) = (r(3) + r(7)) / 2:

For j = 1 To 4: For i = j - 1 To n - 1 Step 4: v(i) = h(j): Next i: Next j

For i = 0 To k - 1: Cells(2 + i, 7).Value = v(i): Cells(2 + i, 8).Value = v(i) * x(i): Next i

For i = k To n - 1: Cells(2 + i, 7).Value = v(i): Cells(2 + i, 8).Value = v(i) * x(i): Next i

For i = 0 To k - 1: Cells(2 + i, 9).Value = v(i) * x(i) - p(i): s2 = s2 + Abs(v(i) * x(i) - p(i)): Next i

Cells(2 + k, 9).Value = "總殘差s2：" :Cells(3 + k, 9).Value = s2

3 用于世界冠軍的個(gè)數(shù)預(yù)測(cè)的實(shí)證分析

選取02年至09年《中國(guó)體育事業(yè)統(tǒng)計(jì)年鑒》中我國(guó)運(yùn)動(dòng)員在奧運(yùn)會(huì)、亞運(yùn)會(huì)、各類(lèi)世界錦標(biāo)賽中獲得世界冠軍的個(gè)數(shù)作為原始數(shù)據(jù)列，對(duì)2010年至2013年獲運(yùn)動(dòng)員世界冠軍的個(gè)數(shù)進(jìn)行預(yù)測(cè)。EXCEL數(shù)據(jù)處理中：n為實(shí)測(cè)值的個(gè)數(shù)，m為預(yù)測(cè)值的個(gè)數(shù)，圖2為和實(shí)測(cè)值、預(yù)測(cè)值、修正后數(shù)據(jù)關(guān)聯(lián)的曲線(xiàn)圖。點(diǎn)擊“預(yù)測(cè)”按鈕。

此算法根據(jù)GM(1,1)預(yù)測(cè)模型編寫(xiě)，為了提高預(yù)測(cè)的精度，需要采用殘差模型進(jìn)行周期修正。比較原模型和周期修正后的總殘差值S1和S2，可以發(fā)現(xiàn)，原模型的總殘差51.735大于周期修正殘差模型的總殘差49.777，說(shuō)明經(jīng)過(guò)周期修正的模型較原模型預(yù)測(cè)精度更高。

4 結(jié)果分析

通過(guò)對(duì)圖2的實(shí)測(cè)曲線(xiàn)、預(yù)測(cè)曲線(xiàn)、周期修正曲線(xiàn)的觀察可以看出，利用GM(1,1)模型的預(yù)測(cè)曲線(xiàn)自2003年之后呈現(xiàn)逐年遞增趨勢(shì)，在大部分年份中，實(shí)測(cè)曲線(xiàn)在預(yù)測(cè)曲線(xiàn)附近波動(dòng)，但也有個(gè)別年份例外，如2006年和2008年兩年的預(yù)測(cè)值，就與實(shí)測(cè)值存在較大的差異和波動(dòng)，但經(jīng)過(guò)周期修正后的曲線(xiàn)，與真實(shí)值較修正前更接近實(shí)際值。

徐國(guó)祥.統(tǒng)計(jì)預(yù)測(cè)和決策[M].上海：上海財(cái)經(jīng)大學(xué)出版社，1998，199-224.

Procedures for the realization of the grey sequence forecast based on Excel VBA

Wang Nan
(Xi’an Physical Education University,Xi’an,710068，China)

The grey sequence forecast is realized by using VBA in Excel macro program.The paper selects the number of champions between 2002 and 2009 in China Sports Statistics Yearbook as the original data to predict and confirm the number of champions between 2010 and 2013.

Grey Sequence Forecast;VBA;EXCEL