- 相關推薦
基于Excel/VBA實現(xiàn)的常用通信業(yè)務預測方法研究
【摘要】為提高通信業(yè)務預測整體效率，擺脫通信業(yè)務預測對于專業(yè)軟件的依賴，從常用的幾種業(yè)務預測模型的原理分析入手，通過使用Excel統(tǒng)計函數(shù)和VBA，提出了一種完全基于Excel的通信業(yè)務預測思路和方法。
【關鍵詞】Excel VBA 瑞利分布邏輯 曲線回歸預測
一、引言
通信業(yè)務預測的常用方法主要包括瑞利分布預測、邏輯曲線預測、普及率法以及回歸法預測等。在實際通信業(yè)務預測中，除普及率法之外，其余上述的業(yè)務預測方法多是基于展望系列等專業(yè)軟件實現(xiàn)的。但專業(yè)軟件預測由于其自身的局限性導致操作繁瑣，設置項目多，靈活性不足，并且部分信息處理仍需依賴Excel，整體效率不高。而通過Excel來實現(xiàn)通信業(yè)務預測模型雖然有靈活簡便、高效處理等優(yōu)勢，但業(yè)內目前并沒有基于Excel來實現(xiàn)所有通信業(yè)務預測模型的成熟方法。本文主要針對目前業(yè)務預測現(xiàn)狀，提出了一種使用Excel和VBA(Visual Basic for Applications，Visual Basic宏語言)實現(xiàn)上述幾種業(yè)務預測模型的思路及方法。
二、常用通信業(yè)務預測模型和Excel實現(xiàn)方法
2.1 瑞利預測法
社會經(jīng)濟現(xiàn)象從總體上看總是遵循著某種清晰的規(guī)律，用統(tǒng)計學的術語來說就是存在著某種分布。而人們的收入及消費水平是遵循瑞利分布的，在通信業(yè)務預測中經(jīng)常通過瑞利分布函數(shù)來預測潛在用戶數(shù)。
瑞利分布函數(shù)的表達式為：
(1)
瑞利分布函數(shù)曲線如圖1所示：
其中，σ為瑞利分布的均值。由式(1)可知，瑞利分布函數(shù)的表達式由σ唯一確定。
通過瑞利分布預測潛在用戶數(shù)的原理是：假定通信交流是人們的基本需求，當人群收入和消費能力到達一定水平，有能力支付通信費用時，這些人就成為電信運營商的潛在用戶。在通信業(yè)務預測中，人群的平均收入和消費能力一般用GDP來衡量，而人均GDP數(shù)值可以唯一確定瑞利分布函數(shù)，因此設置一定的GDP門限值，就可以通過對瑞利分布函數(shù)積分來獲得人群中達到該門限值的比例，而該比例即為電信運營商潛在用戶的比例。
對于通信業(yè)務預測來說，現(xiàn)有數(shù)據(jù)一般包括某地區(qū)歷史GDP、人均GDP和用戶滲透率。因此，使用瑞利預測法需要先通過以上數(shù)據(jù)確定一個合理的門限值X0，即當個人GDP達到X0時，便會使用電信運營商的通信業(yè)務，成為電信運營商的用戶。對于給定的人均GDP和用戶滲透率，如何計算X0可以通過Excel VBA編程實現(xiàn)。具體代碼如下：
Private Function Rayleigh(u As Double， x As Double) As Double
Rayleigh = x / u ^ 2 * Exp(-x ^ 2 / (2 * (u ^ 2)))
End Function
Function RayleighX0(u As Double， P As Double) As Double
Dim i As Integer， Precision As Integer
Dim delta As Double， d1 As Double， d2 As Double， d0 As Double， x0 As Double
Precision = 10000
delta = u / Precision
x0 = 0
Pt = 0
i = 1
Do While Pt < (1 - P)
d1 = delta * (i - 1)
d0 = delta * (i - 1 / 2)
d2 = delta * i
i = i + 1
Pt = Pt + delta * (Rayleigh(u， d1) + Rayleigh(u， d2) + Rayleigh(u， d0) * 4) / 6
x0 = x0 + delta
Loop
RayleighX0 = x0
End Function
測算出GDP門限值X0之后，即可通過區(qū)域未來幾年人口增長預測結果來計算出用戶滲透率P。由于計算用戶滲透率P需要進行積分運算，但Excel函數(shù)庫中目前沒有直接的積分函數(shù)，因此需要通過VBA編寫代碼實現(xiàn)。具體代碼如下：
Function RayleighP(u As Double， x0 As Double) As Double
Dim i As Integer， Precision As Long
Dim delta As Double， d1 As Double， d2 As Double， d0 As Double
Precision = 10000
delta = x0 / Precision
RayleighP = 0
For i = 1 To Precision
d1 = delta * (i - 1)
d0 = delta * (i - 1 / 2)
d2 = delta * i
RayleighP = RayleighP + delta * (Rayleigh(u， d1) + Rayleigh(u， d2) + Rayleigh(u， d0) * 4) / 6
Next i
RayleighP = 1 - RayleighP
End Function
其中，定積分運算采用了辛普森(Simpson)公式來近似擬合，在設定的精度要求下完全能夠滿足業(yè)務預測的需求。
在計算出用戶滲透率P之后，與區(qū)域人口預測結果相乘即可得到區(qū)域內通信用戶的預測結果。
2.2 邏輯曲線法
邏輯曲線(Logistic曲線)是一種常見的S形函數(shù)，它是皮埃爾・弗朗索瓦・韋呂勒于1844或1845年在研究它與人口增長的關系時命名的。
邏輯曲線的函數(shù)表達式為：
(2)
邏輯曲線如圖2所示：
廣義邏輯曲線一般用來模擬一些情況下人口增長趨勢：起初階段大致是指數(shù)增長;然后隨著開始變得飽和，增加變慢;最后達到成熟時增加停止。由于這種曲線的特性與通信用戶增長的變化趨勢吻合，因此在通信業(yè)務預測中被廣泛應用。
由式(2)可知，邏輯曲線的表達式由其參數(shù)唯一確定。因此，在正常情況下通過邏輯曲線預測通信用戶發(fā)展情況時，需要先從歷史的用戶滲透率中測算出曲線參數(shù)。曲線參數(shù)測算在Excel中一般是先使用現(xiàn)有數(shù)據(jù)生成散點圖后，再通過趨勢線擬合并顯示趨勢線方程來確定的。但該擬合方式并不支持邏輯曲線方程直接擬合，而且通過趨勢線方程進行曲線擬合的這種方法并不適合做批量、自動化的業(yè)務預測需求，故曲線參數(shù)確定需要考慮通過其它途徑來實現(xiàn)。
對式(2)進行參數(shù)變換得到：
(3)
其中， ，是邏輯曲線函數(shù)無限趨近的最大值，稱之為邏輯曲線預測飽和值。在通信業(yè)務預測中，往往可通過其它測算手段預先得出通信業(yè)務用戶滲透率的飽和值，即可認為該飽和值已知。然后，再對上述表達式進行變換得到：
(4)
(5)
由此可知，自變量t與 成線性關系。由于L已知、P(t)的歷史數(shù)據(jù)已知，因此可利用Excel內置的LINEST()函數(shù)、INTERCEPT()函數(shù)等求解一元線性擬合方程參數(shù)的函數(shù)直接解出參數(shù)a和b的值。或者使用FORECAST()函數(shù)直接計算出 的預測值，再通過公式變換求出P(t)即可。
2.3 回歸預測法
回歸預測法是在分析市場現(xiàn)象自變量和因變量之間相關關系的基礎上，建立變量之間的回歸方程，并將回歸方程作為預測模型，根據(jù)自變量在預測期的數(shù)量變化來預測因變量關系大多表現(xiàn)為相關關系�；貧w預測法有多種類型：依據(jù)相關關系中自變量的個數(shù)不同分類，可分為一元回歸分析預測法和多元回歸分析預測法;依據(jù)自變量和因變量之間的相關關系不同，可分為線性回歸預測和非線性回歸預測。
在通信業(yè)務預測中，常用區(qū)域GDP、人均GDP、人口數(shù)量等作為相關關系中的自變量，以用戶滲透率或預測用戶數(shù)作為相關關系中的因變量。而預測方法主要有一元線性回歸、二元線性回歸、雙曲線方程回歸、二次曲線回歸、三次曲線回歸、冪函數(shù)回歸、指數(shù)函數(shù)回歸、對數(shù)函數(shù)回歸等。下面將分類討論回歸預測法的Excel實現(xiàn)方式。
(1)線性回歸預測
線性回歸預測主要包括一元線性回歸、二元線性回歸以及多元線性回歸等。其中，一元線性回歸方程形如：Y=aX+b;二元線性回歸方程形如：Y=a1X1+a2X2+b。
針對一元線性回歸方程，可用Excel內置的LINEST()函數(shù)、INTERCEPT()函數(shù)通過歷史相關數(shù)據(jù)直接計算出方程參數(shù)，也可以使用FORECAST()函數(shù)直接通過歷史數(shù)據(jù)得出業(yè)務預測結果。
FORECAST()函數(shù)的語法為：FORECAST(x，known_y's，known_x's)。其中，known_x's指自變量的歷史數(shù)據(jù)，known_y's指因變量的歷史數(shù)據(jù)，x指新的自變量，函數(shù)生成結果為新的因變量。
針對二元以及多元線性回歸方程，可用Excel內置的TREND()函數(shù)直接通過歷史數(shù)據(jù)得出業(yè)務預測結果。
TREND()函數(shù)的語法為：TREND(known_y's，known_x's，new_x's，const)。其中，known_x's指自變量的歷史數(shù)據(jù)，known_y's指因變量的歷史數(shù)據(jù)，new_x's指新的自變量，const為邏輯值，是指是否將曲線方程中的常量b設為0，一般默認省略，則常量b按實際值計算。 TREND()函數(shù)與FORECAST()函數(shù)最大的區(qū)別在于TREND()函數(shù)的參數(shù)支持數(shù)組，即支持多元自變量。因此，在通過TREND()函數(shù)作二元以及多元線性回歸預測時，僅需將多個自變量的區(qū)域數(shù)組填入TREND()函數(shù)的對應參數(shù)位置即可。
(2)非線性回歸預測
非線性回歸主要包括雙曲線方程回歸、二次曲線回歸、三次曲線回歸、冪函數(shù)回歸、指數(shù)函數(shù)回歸、對數(shù)函數(shù)回歸等。由于Excel中并沒有對應的非線性回歸預測函數(shù)，因此需要先將非線性回歸方程通過變量轉換成線性回歸方程，之后再使用線性回歸預測函數(shù)FORECAST()或TREND()計算出預測結果，最后再通過反變換得到最終的預測結果。具體思路和方法如下：
雙曲線回歸方程： ，可視為因變量 與 之間存在一元線性相關;
二次曲線回歸方程：Y=aX2+bX+c，可視為因變量Y與自變量X2、X之間存在二元線性相關;
三次曲線回歸方程：Y=aX3+bX2+cX+d，可視為因變量Y與自變量X3、X2、X之間存在三元線性相關;
冪函數(shù)回歸方程：Y=aXb，可通過變換得到ln(Y)=bln(X)+ln(a)，因此可視為因變量ln(Y)與自變量ln(X)之間存在一元線性相關;
指數(shù)函數(shù)回歸方程：Y=aebx，可通過變換得到ln(Y)=bX+ln(a)，因此可視為因變量ln(Y)與自變量X之間存在一元線性相關;
對數(shù)函數(shù)回歸方程：Y=aln(X)+b，可視為因變量Y與自變量ln(X)之間存在一元線性相關。
對于以上回歸方程，通過上述方式進行變換之后即可通過線性回歸預測的方法，使用Excel函數(shù)直接計算出預測結果，其操作方法與邏輯曲線法類似。
2.4 普及率法
由于普及率法計算相對簡單，實際應用中并不依賴于專業(yè)軟件，并且也有成熟的基于Excel實現(xiàn)的計算模板，因此本文不多作討論。
三、結束語
瑞利分布預測、邏輯曲線預測、普及率法以及回歸法預測是通信業(yè)務預測中常用的幾種預測方法和手段。通過本文中提供的思路和方法，可以快速地編制出一套完全基于Excel/VBA的通信業(yè)務預測模板。通過Excel進行通信業(yè)務預測的編制，可以大大地提高通信業(yè)務預測的靈活性和工作效率，同時也在一定程度上降低對于專業(yè)軟件的依賴。
參考文獻：
[1]翟鴻雁. 移動通信網(wǎng)用戶業(yè)務分析與預測方法[J].中國西部科技，2006(22)：32-33.
[2] 涂虬. Excel在回歸分析中的應用[J].武漢工程職業(yè)技術學院學報，2001(2)：25-32.
[3] 王明松.移動用戶預測方法的分析[J].電信工程技術與標準化，2004，17(10)： 19-22.
[4] 張世龍，鄧明清.移動電話用戶預測中Logistic模型和瑞利分布的綜合運用[J].杭州電子科技大學學報，2006，26(3)： 83-87.
[5]殷祚云. Logistic曲線擬合方法研究[J].數(shù)理統(tǒng)計與管理， 2002(1)：41-46.
[6] 張軍亮，劉新平. 廣義S形曲線及其非線性回歸分析[J].紡織高校基礎科學學報， 2003(2)： 130-133.
[7] 蘇正君. 復化辛普生公式求定積分算法解析[J]. 科教導刊(電子版)，2013(27)： 74.
[8] 陳元千，袁自學. 瑞利預測模型的建立與應用[J].中國海上油氣(地質)，1996(2)：101-105.
[9] 徐海霞，任紅松，袁繼勇，等. 用Excel及其“規(guī)劃求解”功能擬合曲線方程[J].農業(yè)網(wǎng)絡信息， 2004(2)：37-39.
[10] Excel Home.Excel 2007 VBA實戰(zhàn)技巧精粹[M].北京： 人民郵電出版社，2013.
【基于Excel/VBA實現(xiàn)的常用通信業(yè)務預測方法研究】相關文章：
基于Monte Carlo方法的通信仿真實現(xiàn)及應用研究03-30
基于CPLD的RS485通信實現(xiàn)方法研究03-07
基于MapObjects控件的鷹眼圖實現(xiàn)方法03-07
基于ＡＳＰ網(wǎng)絡銷售系統(tǒng)的實現(xiàn)與研究03-23
基于linux的網(wǎng)絡對講機的研究與實現(xiàn)03-07
基于網(wǎng)格的聚類方法研究03-13