基于EXCEL VBA的灰色數列預測程序實現

王 楠

（西安體育學院，陜西西安，710068）

基于EXCEL VBA的灰色數列預測程序實現

王 楠

（西安體育學院，陜西西安，710068）

本文利用VBA在EXCEL下編寫宏程序，實現數列灰色預測，并選取02年至09年《中國體育事業統計年鑒》中我國運動員在奧運會、亞運會、各類世界錦標賽中獲得世界冠軍的個數作為原始數據列，對2010年至2013年獲運動員世界冠軍的個數進行驗證性預測。

灰色數列預測；VBA；EXCEL

1 灰色系統及灰色數列預測

1.1 灰色系統的基本概念

一個系統的內部特征是完全已知的，即系統的信息是充足完全的，我們稱之為白色系統。若一個系統的內部信息是一無所知，只能從它同外部的聯系來觀測研究，這種系統便是黑色系統。灰色系統介于二者之間，灰色系統的一部分信息是已知的，一部分是未知的。

1.2 灰色數列預測

灰色預測是灰色系統理論的重要組成部分，是一種對含有不確定因素的系統進行預測的方法。它利用連續的灰色微分模型，從而預測事物未來發展趨勢的狀況。對系統的發展變化進行全面的觀察分析，并做出長期預測。灰色預測的基本方法大致可分為數列預測、災變預測和系統預測，在這些預測方法中，灰色數列預測應用最為普遍，灰色數列預測是指利用GM(1,1)模型，對時間序列進行數量大小的預測。

GM(1,1)預測模型為：

本文選取殘差模型對模型進行修正處理，提高預測的準確性和精度。

2 EXCEL VBA的灰色數列預測程序實現

對GM(1,1)預測模型及其殘差檢驗過程，我們可以運用EXCEL2007提供的VB編輯器編寫VBA宏程序來實現。

2.1 預測的VBA程序設計

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核心程序實現

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 用于世界冠軍的個數預測的實證分析

選取02年至09年《中國體育事業統計年鑒》中我國運動員在奧運會、亞運會、各類世界錦標賽中獲得世界冠軍的個數作為原始數據列，對2010年至2013年獲運動員世界冠軍的個數進行預測。EXCEL數據處理中：n為實測值的個數，m為預測值的個數，圖2為和實測值、預測值、修正后數據關聯的曲線圖。點擊“預測”按鈕。

此算法根據GM(1,1)預測模型編寫，為了提高預測的精度，需要采用殘差模型進行周期修正。比較原模型和周期修正后的總殘差值S1和S2，可以發現，原模型的總殘差51.735大于周期修正殘差模型的總殘差49.777，說明經過周期修正的模型較原模型預測精度更高。

4 結果分析

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

徐國祥.統計預測和決策[M].上海：上海財經大學出版社，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