基于Excel/VBA实现的常用通信业务预测方法研究
【摘要】为提高通信业务预测整体效率，摆脱通信业务预测对于专业软件的依赖，从常用的几种业务预测模型的原理分析入手，通过使用Excel统计函数和VBA，提出了一种完全基于Excel的通信业务预测思路和方法。
【关键词】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实现的常用通信业务预测方法研究】相关文章：