基于Excel/VBA實現的常用通信業務預測方法研究

時間：2024-09-08 10:43:36 通信工程畢業論文我要投稿

相關推薦

　　【摘要】為提高通信業務預測整體效率，擺脫通信業務預測對于專業軟件的依賴，從常用的幾種業務預測模型的原理分析入手，通過使用Excel統計函數和VBA，提出了一種完全基于Excel的通信業務預測思路和方法。

基于Excel/VBA實現的常用通信業務預測方法研究

　　【關鍵詞】Excel VBA 瑞利分布邏輯曲線回歸預測

　　一、引言

　　通信業務預測的常用方法主要包括瑞利分布預測、邏輯曲線預測、普及率法以及回歸法預測等。在實際通信業務預測中，除普及率法之外，其余上述的業務預測方法多是基于展望系列等專業軟件實現的。但專業軟件預測由于其自身的局限性導致操作繁瑣，設置項目多，靈活性不足，并且部分信息處理仍需依賴Excel，整體效率不高。而通過Excel來實現通信業務預測模型雖然有靈活簡便、高效處理等優勢，但業內目前并沒有基于Excel來實現所有通信業務預測模型的成熟方法。本文主要針對目前業務預測現狀，提出了一種使用Excel和VBA(Visual Basic for Applications，Visual Basic宏語言)實現上述幾種業務預測模型的思路及方法。

　　二、常用通信業務預測模型和Excel實現方法

　　2.1 瑞利預測法

　　社會經濟現象從總體上看總是遵循著某種清晰的規律，用統計學的術語來說就是存在著某種分布。而人們的收入及消費水平是遵循瑞利分布的，在通信業務預測中經常通過瑞利分布函數來預測潛在用戶數。

　　瑞利分布函數的表達式為：

　　(1)

　　瑞利分布函數曲線如圖1所示：

　　其中，σ為瑞利分布的均值。由式(1)可知，瑞利分布函數的表達式由σ唯一確定。

　　通過瑞利分布預測潛在用戶數的原理是：假定通信交流是人們的基本需求，當人群收入和消費能力到達一定水平，有能力支付通信費用時，這些人就成為電信運營商的潛在用戶。在通信業務預測中，人群的平均收入和消費能力一般用GDP來衡量，而人均GDP數值可以唯一確定瑞利分布函數，因此設置一定的GDP門限值，就可以通過對瑞利分布函數積分來獲得人群中達到該門限值的比例，而該比例即為電信運營商潛在用戶的比例。

　　對于通信業務預測來說，現有數據一般包括某地區歷史GDP、人均GDP和用戶滲透率。因此，使用瑞利預測法需要先通過以上數據確定一個合理的門限值X0，即當個人GDP達到X0時，便會使用電信運營商的通信業務，成為電信運營商的用戶。對于給定的人均GDP和用戶滲透率，如何計算X0可以通過Excel VBA編程實現。具體代碼如下：

　　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之后，即可通過區域未來幾年人口增長預測結果來計算出用戶滲透率P。由于計算用戶滲透率P需要進行積分運算，但Excel函數庫中目前沒有直接的積分函數，因此需要通過VBA編寫代碼實現。具體代碼如下：

　　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)公式來近似擬合，在設定的精度要求下完全能夠滿足業務預測的需求。

　　在計算出用戶滲透率P之后，與區域人口預測結果相乘即可得到區域內通信用戶的預測結果。

　　2.2 邏輯曲線法

　　邏輯曲線(Logistic曲線)是一種常見的S形函數，它是皮埃爾・弗朗索瓦・韋呂勒于1844或1845年在研究它與人口增長的關系時命名的。

　　邏輯曲線的函數表達式為：

　　(2)

　　邏輯曲線如圖2所示：

　　廣義邏輯曲線一般用來模擬一些情況下人口增長趨勢：起初階段大致是指數增長;然后隨著開始變得飽和，增加變慢;最后達到成熟時增加停止。由于這種曲線的特性與通信用戶增長的變化趨勢吻合，因此在通信業務預測中被廣泛應用。

　　由式(2)可知，邏輯曲線的表達式由其參數唯一確定。因此，在正常情況下通過邏輯曲線預測通信用戶發展情況時，需要先從歷史的用戶滲透率中測算出曲線參數。曲線參數測算在Excel中一般是先使用現有數據生成散點圖后，再通過趨勢線擬合并顯示趨勢線方程來確定的。但該擬合方式并不支持邏輯曲線方程直接擬合，而且通過趨勢線方程進行曲線擬合的這種方法并不適合做批量、自動化的業務預測需求，故曲線參數確定需要考慮通過其它途徑來實現。

　　對式(2)進行參數變換得到：

　　(3)

　　其中，，是邏輯曲線函數無限趨近的最大值，稱之為邏輯曲線預測飽和值。在通信業務預測中，往往可通過其它測算手段預先得出通信業務用戶滲透率的飽和值，即可認為該飽和值已知。然后，再對上述表達式進行變換得到：

　　(4)

　　(5)

　　由此可知，自變量t與成線性關系。由于L已知、P(t)的歷史數據已知，因此可利用Excel內置的LINEST()函數、INTERCEPT()函數等求解一元線性擬合方程參數的函數直接解出參數a和b的值。或者使用FORECAST()函數直接計算出的預測值，再通過公式變換求出P(t)即可。

　　2.3 回歸預測法

　　回歸預測法是在分析市場現象自變量和因變量之間相關關系的基礎上，建立變量之間的回歸方程，并將回歸方程作為預測模型，根據自變量在預測期的數量變化來預測因變量關系大多表現為相關關系。回歸預測法有多種類型：依據相關關系中自變量的個數不同分類，可分為一元回歸分析預測法和多元回歸分析預測法;依據自變量和因變量之間的相關關系不同，可分為線性回歸預測和非線性回歸預測。

　　在通信業務預測中，常用區域GDP、人均GDP、人口數量等作為相關關系中的自變量，以用戶滲透率或預測用戶數作為相關關系中的因變量。而預測方法主要有一元線性回歸、二元線性回歸、雙曲線方程回歸、二次曲線回歸、三次曲線回歸、冪函數回歸、指數函數回歸、對數函數回歸等。下面將分類討論回歸預測法的Excel實現方式。

　　(1)線性回歸預測

　　線性回歸預測主要包括一元線性回歸、二元線性回歸以及多元線性回歸等。其中，一元線性回歸方程形如：Y=aX+b;二元線性回歸方程形如：Y=a1X1+a2X2+b。

　　針對一元線性回歸方程，可用Excel內置的LINEST()函數、INTERCEPT()函數通過歷史相關數據直接計算出方程參數，也可以使用FORECAST()函數直接通過歷史數據得出業務預測結果。

　　FORECAST()函數的語法為：FORECAST(x，known_y's，known_x's)。其中，known_x's指自變量的歷史數據，known_y's指因變量的歷史數據，x指新的自變量，函數生成結果為新的因變量。

　　針對二元以及多元線性回歸方程，可用Excel內置的TREND()函數直接通過歷史數據得出業務預測結果。

　　TREND()函數的語法為：TREND(known_y's，known_x's，new_x's，const)。其中，known_x's指自變量的歷史數據，known_y's指因變量的歷史數據，new_x's指新的自變量，const為邏輯值，是指是否將曲線方程中的常量b設為0，一般默認省略，則常量b按實際值計算。　　TREND()函數與FORECAST()函數最大的區別在于TREND()函數的參數支持數組，即支持多元自變量。因此，在通過TREND()函數作二元以及多元線性回歸預測時，僅需將多個自變量的區域數組填入TREND()函數的對應參數位置即可。

　　(2)非線性回歸預測

　　非線性回歸主要包括雙曲線方程回歸、二次曲線回歸、三次曲線回歸、冪函數回歸、指數函數回歸、對數函數回歸等。由于Excel中并沒有對應的非線性回歸預測函數，因此需要先將非線性回歸方程通過變量轉換成線性回歸方程，之后再使用線性回歸預測函數FORECAST()或TREND()計算出預測結果，最后再通過反變換得到最終的預測結果。具體思路和方法如下：

　　雙曲線回歸方程：，可視為因變量與之間存在一元線性相關;

　　二次曲線回歸方程：Y=aX2+bX+c，可視為因變量Y與自變量X2、X之間存在二元線性相關;

　　三次曲線回歸方程：Y=aX3+bX2+cX+d，可視為因變量Y與自變量X3、X2、X之間存在三元線性相關;

　　冪函數回歸方程：Y=aXb，可通過變換得到ln(Y)=bln(X)+ln(a)，因此可視為因變量ln(Y)與自變量ln(X)之間存在一元線性相關;

　　指數函數回歸方程：Y=aebx，可通過變換得到ln(Y)=bX+ln(a)，因此可視為因變量ln(Y)與自變量X之間存在一元線性相關;

　　對數函數回歸方程：Y=aln(X)+b，可視為因變量Y與自變量ln(X)之間存在一元線性相關。

　　對于以上回歸方程，通過上述方式進行變換之后即可通過線性回歸預測的方法，使用Excel函數直接計算出預測結果，其操作方法與邏輯曲線法類似。

　　2.4 普及率法

　　由于普及率法計算相對簡單，實際應用中并不依賴于專業軟件，并且也有成熟的基于Excel實現的計算模板，因此本文不多作討論。

　　三、結束語

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

　　參考文獻：

　　[1]翟鴻雁. 移動通信網用戶業務分析與預測方法[J].中國西部科技，2006(22)：32-33.

　　[2] 涂虬. Excel在回歸分析中的應用[J].武漢工程職業技術學院學報，2001(2)：25-32.

　　[3] 王明松.移動用戶預測方法的分析[J].電信工程技術與標準化，2004，17(10)： 19-22.

　　[4] 張世龍，鄧明清.移動電話用戶預測中Logistic模型和瑞利分布的綜合運用[J].杭州電子科技大學學報，2006，26(3)： 83-87.

　　[5]殷祚云. Logistic曲線擬合方法研究[J].數理統計與管理， 2002(1)：41-46.

　　[6] 張軍亮，劉新平. 廣義S形曲線及其非線性回歸分析[J].紡織高校基礎科學學報， 2003(2)： 130-133.

　　[7] 蘇正君. 復化辛普生公式求定積分算法解析[J]. 科教導刊(電子版)，2013(27)： 74.

　　[8] 陳元千，袁自學. 瑞利預測模型的建立與應用[J].中國海上油氣(地質)，1996(2)：101-105.

　　[9] 徐海霞，任紅松，袁繼勇，等. 用Excel及其“規劃求解”功能擬合曲線方程[J].農業網絡信息， 2004(2)：37-39.

　　[10] Excel Home.Excel 2007 VBA實戰技巧精粹[M].北京：人民郵電出版社，2013.

【基于Excel/VBA實現的常用通信業務預測方法研究】相關文章：