肖增輝

（廣州杰賽科技股份有限公司，廣東 廣州 510310）

基于Excel/VBA實(shí)現(xiàn)的常用通信業(yè)務(wù)預(yù)測方法研究

肖增輝

（廣州杰賽科技股份有限公司，廣東 廣州 510310）

為提高通信業(yè)務(wù)預(yù)測整體效率，擺脫通信業(yè)務(wù)預(yù)測對于專業(yè)軟件的依賴，從常用的幾種業(yè)務(wù)預(yù)測模型的原理分析入手，通過使用Excel統(tǒng)計(jì)函數(shù)和VBA，提出了一種完全基于Excel的通信業(yè)務(wù)預(yù)測思路和方法。

Excel VBA 瑞利分布 邏輯曲線 回歸預(yù)測

1 引言

通信業(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ù)測模型的思路及方法。

2 常用通信業(yè)務(wù)預(yù)測模型和Excel實(shí)現(xiàn)方法

2.1 瑞利預(yù)測法

社會(huì)經(jīng)濟(jì)現(xiàn)象從總體上看總是遵循著某種清晰的規(guī)律，用統(tǒng)計(jì)學(xué)的術(shù)語來說就是存在著某種分布。而人們的收入及消費(fèi)水平是遵循瑞利分布的，在通信業(yè)務(wù)預(yù)測中經(jīng)常通過瑞利分布函數(shù)來預(yù)測潛在用戶數(shù)。

瑞利分布函數(shù)的表達(dá)式為：

瑞利分布函數(shù)曲線如圖1所示：

圖1 瑞利分布函數(shù)曲線

其中，σ為瑞利分布的均值。由式（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í)，便會(huì)使用電信運(yùn)營商的通信業(yè)務(wù)，成為電信運(yùn)營商的用戶。對于給定的人均GDP和用戶滲透率，如何計(jì)算X0可以通過Excel VBA編程實(shí)現(xiàn)。具體代碼如下：

測算出GDP門限值X0之后，即可通過區(qū)域未來幾年人口增長預(yù)測結(jié)果來計(jì)算出用戶滲透率P。由于計(jì)算用戶滲透率P需要進(jìn)行積分運(yùn)算，但Excel函數(shù)庫中目前沒有直接的積分函數(shù)，因此需要通過VBA編寫代碼實(shí)現(xiàn)。具體代碼如下：

其中，定積分運(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.3 回歸 預(yù)測法

回歸預(yù)測法是在分析市場現(xiàn)象自變量和因變量之間相關(guān)關(guān)系的基礎(chǔ)上，建立變量之間的回歸方程，并將回歸方程作為預(yù)測模型，根據(jù)自變量在預(yù)測期的數(shù)量變化來預(yù)測因變量關(guān)系大多表現(xiàn)為相關(guān)關(guān)系?；貧w預(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。

針對一元線性回歸方程，可用E x c e l內(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é)果。具體思路和方法如下：

◆二次曲線回歸方程：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=aebχ，可通過變換得到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ì)算模板，因此本文不多作討論。

3 結(jié)束語

瑞利分布預(yù)測、邏輯曲線預(yù)測、普及率法以及回歸法預(yù)測是通信業(yè)務(wù)預(yù)測中常用的幾種預(yù)測方法和手段。通過本文中提供的思路和方法，可以快速地編制出一套完全基于Excel/VBA的通信業(yè)務(wù)預(yù)測模板。通過Excel進(jìn)行通信業(yè)務(wù)預(yù)測的編制，可以大大地提高通信業(yè)務(wù)預(yù)測的靈活性和工作效率，同時(shí)也在一定程度上降低對于專業(yè)軟件的依賴。

[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.★

Research on Telecom Traffi c Prediction Method Based on Excel/VBA

XIAO Zeng-hui
(GCI Science & Technology Co., Ltd., Guangzhou 510310, China)

In order to improve overall efficiency of telecom traffic prediction eliminate dependence of telecom traffic prediction on professional software, the principles of several traffic prediction models were analyzed and the prediction thought and method based on Excel was put forward by using Excel functions and VBA.

Excel VBA Rayleigh distribution logistic curve regression prediction

10.3969/j.issn.1006-1010.2015.14.008

TN929.5

A

1006-1010(2015)14-0040-05

肖增輝. 基于Excel/VBA實(shí)現(xiàn)的常用通信業(yè)務(wù)預(yù)測方法研究[J]. 移動(dòng)通信, 2015,39(14): 40-44.

2015-07-06

責(zé)任編輯：袁婷 yuanting@mbcom.cn

肖增輝：助理工程師，學(xué)士畢業(yè)于西安交通大學(xué)信息工程專業(yè)，現(xiàn)任職于廣州杰賽科技股份有限公司，主要研究方向?yàn)橐苿?dòng)通信無線網(wǎng)規(guī)劃優(yōu)化、方案設(shè)計(jì)等。