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