基于LINEST函数构建模型在市场营销预测中的应用研究

阿布力孜·布力布力 邓 楠 穆哈拜提·帕热提

（新疆农业大学 经济与贸易学院，乌鲁木齐 830052）

1　引言

预测是指根据客观事物的发展趋势和变化规律对特定对象的未来发展趋势或状态做出科学的推测与判断。就企业来说，营销预测是其重要的工作内容之一，是指在企业经营战略和营销计划的基础上，全面考虑企业的经营业绩现状，深入分析目前或将来对企业发展可能产生影响的多种因素，科学预测企业未来的业绩和消费者需求等方面的发展变化，从而将充满不确定性的市场营销计划转化为可运作的具体运营目标［1］。企业的财务预算是进行所有经营活动和相关预测的前提，是根据企业销售业绩情况而决定的，这就说明销售量的任何变化自然会引起现金流量、库存量、应收与应付账款以及企业其他资产和负债的变化。由此可知，市场营销预测为企业经营管理起到至关重要的作用，它不仅帮助企业较准确地预测未来的经营利润和成本，还为相关经营计划的安排和长短期决策的制定提供有力依据［2］。一般而言，虽然预测不能保证绝对精确，销售预测也不例外，但是它对于一家企业的营销组合和投资计划的安排具有至关重要的作用。

目前，企业通常通过市场调查得到相关数据资料，并根据相关数据资料的深入分析，预计和测算某一个产品在未来一定时期内的市场表现及变化趋势，进而制定出适合本企业发展和产品业绩现状的营销策略。由于预测是对未来客观事物的推测，是一种复杂的过程。因此，企业需要尽可能获取更多的相关数据资料，运用更严谨的实证分析方法，如用数学模型来进行推理与趋势判断。一般而言，直线趋势法、指数平滑预测法、移动平均预测法、多元回归分析预测法等都是常用的预测方法。其中，多元回归分析法作为目前最主要的预测分析方法，越来越得到更广泛的应用［3］。

统计学将一个因变量与多个自变量之间的关系研究称为多元回归，利用这一方法可将若干多项式方程转化为多元方程来求解，即这一方法在社会学、心理学、医学、金融、市场分析等领域需要数据统计分析时，得到了诸多研究者的广泛应用［4］。由于采用多元回归分析法进行统计是比较复杂的过程，特别是当涉及3个以上自变量时，仅依靠人工计算是更加困难的。因此，自变量较多的多元回归分析通常需要利用计算机编程来完成，这对于非计算机专业的人员来说，是一份难度较大的工作。然而，利用Microsoft Excel软件中的LINEST函数也可进行多元回归分析，根据大量数据进行精确计算，从而完成对预测对象的深入分析［5］，它是一种功能强大、操作简便的统计分析工具。这一工具可以帮助企业营销及管理人员建立回归模型进行市场营销预测，需要相关人员熟练掌握LINEST函数的基本原理和及实际应用。

2　LINEST函数的基本原理

LINEST函数是基于最小二乘法对已知数据进行最佳直线拟合，并返回描绘此直线的数组。所谓最小二乘法（Least Square Method）是近年来应实际需要而产生和发展的一种具有广泛适用性的多元统计分析方法，由S.Wold和C.Albano等于1983年首次提出［6］。目前，它在理论和方法方面得到了迅速发展，并在化工、医学、市场分析、金融等领域得到了广泛的应用。基于最小二乘法的回归分析不仅可作为一种因变量对多种自变量的回归建模方法，而且同样适用于多种因变量对多自变量的回归建模方法，从而可以充分反映模型的整体性［7］。它能有效解决变量间的相关性问题，并适合在样本容量小于变量个数的情况下进行回归建模。

LINEST函数所描绘的直线公式可表示为y=Mx+b或y=m1x1+m2x2+ … +b（如果有多个区域的x值）。式中，因变量y是自变量x的函数值。M值是与每个x值相对应的系数，b为常量。同时，y、x和m还可以是向量。LINEST函数返回的数组为｛mn，mn-1，…，m1，b｝。此外，LINEST 函数还可返回附加回归统计值。

LINEST函数的基本语法为LINEST（known_y’s，known_x’s，const，stats）其中：参数“Known_y’s”是关系表达式y=Mx+b中已知的y值集合，如果数组known_y’s在单独一列中，则known_x’s的每一列被视为一个独立的变量，如果数组knowny’s在单独一行中，则known-x’s的每一行被视为一个独立的变量。参数“Known_x’s”是关系表达式y=mx+b中已知的可选x值集合，数组known_x’s可以包含一组或多组变量。如果只用到一个变量，只要known_y’s和known_x’s维数相同，它们可以是任何形状的区域。如果用到多个变量，则known_y’s必须为向量（即必须为一行或一列），如果省略known_x’s，则假设该数组为｛1，2，3，…｝，其大小与 known_y’s相同［8］。

参数Const为一逻辑值，用于指定是否将常量b强制设为0。如果const为TRUE或省略，b将按正常计算。如果const为FALSE，b将被设为0，并同时调整m值使y=mx。参数“Stats”为一逻辑值，指定是否返回附加回归统计值（如表1所示）。如果stats为TRUE，则LINEST函数返回附加回归统计值，这时返回的数组为

如果stats为FALSE或省略，LINEST函数只返回系数m和常量b。

表1　LINSET函数常用的回归附加值

3　LINEST函数在构建市场营销预测模型中的应用

3.1　利用LINEST函数进行一元线性回归分析

以某电子琴生产商根据上半年销售情况通过直线回归分析预测未来产品销售量为例。收集原始销售收入数据，将原始数据在Excel文档中建立一个数据透视表，如表2所示。

根据表2中的数据建立回归分析数据的取数公式：I7=B5，J7=B6，K7=B7，L7=B8，M7=B9，N7=B10。再利用LINEST函数定义直线回归模型中的参数：D6=INDEX（LINEST（I7：N7，，TRUE，TRUE），1，1），E6=INDEX（LINEST（I7：N7，，TRUE，TRUE），1，2），F6=INDEX（LINEST（I7：N7，，TRUE，TRUE），3，1）。由此可定义未来销售量的计算公式为F10=E9*D6+E6。此时，只需在表2所示的单元格中输入相应的文本数据，即可完成模型构建。

表2　直线回归分析销售预测表

在模型中各单元间建立数据链接，只需将历史销售数据分别输入B5：B10区域的单元格中，直线回归分析区域I7：N7会分别录取到相应的历史销售数据，同时D6：F6区域各单元格则会自动求出参数A，B及相关系数（R2）的值。R2是判断所拟合的直线方程是否合理的重要参数，R2越接近于1，则说明时间与销售量两者之间的线性关系越显著，方程所得的预测结论越可靠。若R2的置信度在合理范围内，则在模型的E9单元格中输入想要预测的未来某个月份，该月的预测销售值就会自动显示在F10单元格中。

3.2　利用LINEST函数进行多元直线回归分析

如上文所述，利用LINEST函数进行多元直线回归分析时，与一元直线回归分析方法相似，而且可以给出各自变量的资料，但需注意的是其数学表达式与一般习惯不同。一般表达式为y=a+b1x1+b2，而LINEST函数的表达式则为y=M1x1+M2x2+ …+b。式中：m表示斜率，b表示截距［9］。现以某房地产公司对一幢小型商业办公楼的租赁价格评估为例，说明其在多元直线回归分析中的使用方法。该楼使用面积为2500m2，有3间办公室，2个入口，已使用25年。为了对这幢楼房的租赁价格进行预测，在待选楼房中随机抽取了11个样本，具体资料如表3所示。

利用LINEST函数定义多元直线回归模型中的计算公式INDEX（LINEST（E2：E12，A2：D12，TRUE，TRUE），可得如下数据（见表4）。其中，A14是斜率m4，B14是斜率m3，D14是斜率m1，E14是截距b。A15：E15区域各单元格数值则分别对应5个标准差se4，se3，se2，se1，seb。而 A16为复判定系数、B16为估计标准差。A17为F值，B17为自由度（n-k-1=11-4-1=6），A18为回归平方和，B18为剩余平方和。

表3　待选评估样本的数据

表4　利用LINEST函数计算后得到的相关数值

根据表4中A14：E14区域的数值可建立多元回归方程：

以此方程对评估目标进行预测即可计算得到该办公楼的租赁价格为158261元。由表4可知复判定系数为0.9967，说明各自变量与租赁价格高度相关。且经检验，当自由度为6，显著性水平为0.05时，F临界值为4.53，而上述公式所求得的F值为459.75，远大于F临界值，说明自变量与租赁价格之间的高度相关不具有偶然性。

但是，众所周知，在进行多元回归分析时，由于各个自变量对因变量的影响程度不同，精确评估与预测某一因变量还取决于合适自变量的选择。因此，进行数据统计时，需要分别考察各自变量对因变量的影响程度，然后进行相互比较，通过选取其中影响较大的进行回归建模。优化选择自变量的方法有多种，较理想的方法是使用逐步回归法、回归参数标准化法等，但这些方法往往计算起来比较复杂，对非统计学专业人员来说难度较大。所以，通常情况下为了方便起见，我们可以直接根据构建的回归方程计算判定系数，并进行各项检验。

4　结论

利用LINEST函数不仅可以对已知数据进行最佳线性拟合，还可以返回描述此曲线的数组。由于此函数返回数值数组，所以必须以数组公式的形式输入。当操作者输入LINEST函数后，如果直接点击电脑键盘的Enter键只能得到一个数值。所以，必须在选定输入公式的单元格及相应数据输出区域，在按键盘F2键的同时还需要按“Ctrl+Shift+Enter”才能得到所需的数组值。这一操作对于具有基本计算机操作技能的人来说简便、快捷，只要公式中各数组区域的数值输入正确，可直接得到相应多元回归方程的参数。利用该函数，无论多项式有多少项或多元回归中有多少个自变量，均可快速、准确地计算出相关的回归参数。

此外，在进行回归分析时，LINEST函数会计算每一点y的估计值和实际值的平方差。这些平方差之和称为残差平方和（ssresid）。回归平方和（ssreg）可通过公式“ssreg=sstotal（总平方和）-ssresid”计算得到。残差平方和与总平方和的比值越小，判定系数R2（R2=ssreg／sstotal且R2取值在0～1）的值越接近1，则说明所拟合的模型越合理。这一判定简单直观，利于使用者迅速做出科学的判断和决策。

综上，利用LINEST函数通过一元直线回归或多元直线回归建立市场营销所需的预测模型，此法对于样本容量无特殊要求，能在样本容量小于变量个数的情况下进行回归建模。此外，还可以同时实现回归建模、数据结构简化以及两组变量间的相关分析，在实际操作时不需要进行复杂的计算编程，是一种简便、快捷的方法。

参考文献：

［1］ 杜跃东.浅析现代企业的市场营销预测［J］.商场现代化，2007（2）：123-124.

［2］ 李华，胡奇英.预测与决策［M］.西安：西安电子科技大学出版社，2005.

［3］ 王顺金，陈立波.财务预测中构建Excel回归分析模型［J］.中国管理信息化，2011，14（6）：11-14.

［4］ 蔡章利，石为人，刁绫.回归分析在连续型数据目标预测中的应用［J］.重庆大学学报，2005，28（10）：91-93.

［5］ 周自玮.利用Excel内部函数LINEST进行多元回归和多项式回归分析［J］.草业与畜牧，2007（8）：48-52.

［6］ 王岱.LINEST函数在最小二乘法求直线拟合中的应用［J］.考试周刊，2010（54）：202-203.

［7］ 徐继初.生物统计及实验设计［M］.北京：农业出版社，1992：112-120.

［8］ 郝敬雷，姜永明，马彦金，等.LINEST函数在离心泵流量-扬程性能曲线拟合中的应用［J］.当代化工，2014（1）：636-638.