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