阿布力孜·布力布力 鄧 楠 穆哈拜提·帕熱提

（新疆農(nóng)業(yè)大學(xué) 經(jīng)濟(jì)與貿(mào)易學(xué)院，烏魯木齊 830052）

1　引言

預(yù)測(cè)是指根據(jù)客觀事物的發(fā)展趨勢(shì)和變化規(guī)律對(duì)特定對(duì)象的未來(lái)發(fā)展趨勢(shì)或狀態(tài)做出科學(xué)的推測(cè)與判斷。就企業(yè)來(lái)說(shuō)，營(yíng)銷(xiāo)預(yù)測(cè)是其重要的工作內(nèi)容之一，是指在企業(yè)經(jīng)營(yíng)戰(zhàn)略和營(yíng)銷(xiāo)計(jì)劃的基礎(chǔ)上，全面考慮企業(yè)的經(jīng)營(yíng)業(yè)績(jī)現(xiàn)狀，深入分析目前或?qū)?lái)對(duì)企業(yè)發(fā)展可能產(chǎn)生影響的多種因素，科學(xué)預(yù)測(cè)企業(yè)未來(lái)的業(yè)績(jī)和消費(fèi)者需求等方面的發(fā)展變化，從而將充滿不確定性的市場(chǎng)營(yíng)銷(xiāo)計(jì)劃轉(zhuǎn)化為可運(yùn)作的具體運(yùn)營(yíng)目標(biāo)［1］。企業(yè)的財(cái)務(wù)預(yù)算是進(jìn)行所有經(jīng)營(yíng)活動(dòng)和相關(guān)預(yù)測(cè)的前提，是根據(jù)企業(yè)銷(xiāo)售業(yè)績(jī)情況而決定的，這就說(shuō)明銷(xiāo)售量的任何變化自然會(huì)引起現(xiàn)金流量、庫(kù)存量、應(yīng)收與應(yīng)付賬款以及企業(yè)其他資產(chǎn)和負(fù)債的變化。由此可知，市場(chǎng)營(yíng)銷(xiāo)預(yù)測(cè)為企業(yè)經(jīng)營(yíng)管理起到至關(guān)重要的作用，它不僅幫助企業(yè)較準(zhǔn)確地預(yù)測(cè)未來(lái)的經(jīng)營(yíng)利潤(rùn)和成本，還為相關(guān)經(jīng)營(yíng)計(jì)劃的安排和長(zhǎng)短期決策的制定提供有力依據(jù)［2］。一般而言，雖然預(yù)測(cè)不能保證絕對(duì)精確，銷(xiāo)售預(yù)測(cè)也不例外，但是它對(duì)于一家企業(yè)的營(yíng)銷(xiāo)組合和投資計(jì)劃的安排具有至關(guān)重要的作用。

目前，企業(yè)通常通過(guò)市場(chǎng)調(diào)查得到相關(guān)數(shù)據(jù)資料，并根據(jù)相關(guān)數(shù)據(jù)資料的深入分析，預(yù)計(jì)和測(cè)算某一個(gè)產(chǎn)品在未來(lái)一定時(shí)期內(nèi)的市場(chǎng)表現(xiàn)及變化趨勢(shì)，進(jìn)而制定出適合本企業(yè)發(fā)展和產(chǎn)品業(yè)績(jī)現(xiàn)狀的營(yíng)銷(xiāo)策略。由于預(yù)測(cè)是對(duì)未來(lái)客觀事物的推測(cè)，是一種復(fù)雜的過(guò)程。因此，企業(yè)需要盡可能獲取更多的相關(guān)數(shù)據(jù)資料，運(yùn)用更嚴(yán)謹(jǐn)?shù)膶?shí)證分析方法，如用數(shù)學(xué)模型來(lái)進(jìn)行推理與趨勢(shì)判斷。一般而言，直線趨勢(shì)法、指數(shù)平滑預(yù)測(cè)法、移動(dòng)平均預(yù)測(cè)法、多元回歸分析預(yù)測(cè)法等都是常用的預(yù)測(cè)方法。其中，多元回歸分析法作為目前最主要的預(yù)測(cè)分析方法，越來(lái)越得到更廣泛的應(yīng)用［3］。

統(tǒng)計(jì)學(xué)將一個(gè)因變量與多個(gè)自變量之間的關(guān)系研究稱為多元回歸，利用這一方法可將若干多項(xiàng)式方程轉(zhuǎn)化為多元方程來(lái)求解，即這一方法在社會(huì)學(xué)、心理學(xué)、醫(yī)學(xué)、金融、市場(chǎng)分析等領(lǐng)域需要數(shù)據(jù)統(tǒng)計(jì)分析時(shí)，得到了諸多研究者的廣泛應(yīng)用［4］。由于采用多元回歸分析法進(jìn)行統(tǒng)計(jì)是比較復(fù)雜的過(guò)程，特別是當(dāng)涉及3個(gè)以上自變量時(shí)，僅依靠人工計(jì)算是更加困難的。因此，自變量較多的多元回歸分析通常需要利用計(jì)算機(jī)編程來(lái)完成，這對(duì)于非計(jì)算機(jī)專業(yè)的人員來(lái)說(shuō)，是一份難度較大的工作。然而，利用Microsoft Excel軟件中的LINEST函數(shù)也可進(jìn)行多元回歸分析，根據(jù)大量數(shù)據(jù)進(jìn)行精確計(jì)算，從而完成對(duì)預(yù)測(cè)對(duì)象的深入分析［5］，它是一種功能強(qiáng)大、操作簡(jiǎn)便的統(tǒng)計(jì)分析工具。這一工具可以幫助企業(yè)營(yíng)銷(xiāo)及管理人員建立回歸模型進(jìn)行市場(chǎng)營(yíng)銷(xiāo)預(yù)測(cè)，需要相關(guān)人員熟練掌握LINEST函數(shù)的基本原理和及實(shí)際應(yīng)用。

2　LINEST函數(shù)的基本原理

LINEST函數(shù)是基于最小二乘法對(duì)已知數(shù)據(jù)進(jìn)行最佳直線擬合，并返回描繪此直線的數(shù)組。所謂最小二乘法（Least Square Method）是近年來(lái)應(yīng)實(shí)際需要而產(chǎn)生和發(fā)展的一種具有廣泛適用性的多元統(tǒng)計(jì)分析方法，由S.Wold和C.Albano等于1983年首次提出［6］。目前，它在理論和方法方面得到了迅速發(fā)展，并在化工、醫(yī)學(xué)、市場(chǎng)分析、金融等領(lǐng)域得到了廣泛的應(yīng)用?；谧钚《朔ǖ幕貧w分析不僅可作為一種因變量對(duì)多種自變量的回歸建模方法，而且同樣適用于多種因變量對(duì)多自變量的回歸建模方法，從而可以充分反映模型的整體性［7］。它能有效解決變量間的相關(guān)性問(wèn)題，并適合在樣本容量小于變量個(gè)數(shù)的情況下進(jìn)行回歸建模。

LINEST函數(shù)所描繪的直線公式可表示為y=Mx+b或y=m1x1+m2x2+ … +b（如果有多個(gè)區(qū)域的x值）。式中，因變量y是自變量x的函數(shù)值。M值是與每個(gè)x值相對(duì)應(yīng)的系數(shù)，b為常量。同時(shí)，y、x和m還可以是向量。LINEST函數(shù)返回的數(shù)組為｛mn，mn-1，…，m1，b｝。此外，LINEST 函數(shù)還可返回附加回歸統(tǒng)計(jì)值。

LINEST函數(shù)的基本語(yǔ)法為L(zhǎng)INEST（known_y’s，known_x’s，const，stats）其中：參數(shù)“Known_y’s”是關(guān)系表達(dá)式y(tǒng)=Mx+b中已知的y值集合，如果數(shù)組known_y’s在單獨(dú)一列中，則known_x’s的每一列被視為一個(gè)獨(dú)立的變量，如果數(shù)組knowny’s在單獨(dú)一行中，則known-x’s的每一行被視為一個(gè)獨(dú)立的變量。參數(shù)“Known_x’s”是關(guān)系表達(dá)式y(tǒng)=mx+b中已知的可選x值集合，數(shù)組known_x’s可以包含一組或多組變量。如果只用到一個(gè)變量，只要known_y’s和known_x’s維數(shù)相同，它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，則known_y’s必須為向量（即必須為一行或一列），如果省略known_x’s，則假設(shè)該數(shù)組為｛1，2，3，…｝，其大小與 known_y’s相同［8］。

參數(shù)Const為一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為0。如果const為T(mén)RUE或省略，b將按正常計(jì)算。如果const為FALSE，b將被設(shè)為0，并同時(shí)調(diào)整m值使y=mx。參數(shù)“Stats”為一邏輯值，指定是否返回附加回歸統(tǒng)計(jì)值（如表1所示）。如果stats為T(mén)RUE，則LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值，這時(shí)返回的數(shù)組為

如果stats為FALSE或省略，LINEST函數(shù)只返回系數(shù)m和常量b。

表1　LINSET函數(shù)常用的回歸附加值

3　LINEST函數(shù)在構(gòu)建市場(chǎng)營(yíng)銷(xiāo)預(yù)測(cè)模型中的應(yīng)用

3.1　利用LINEST函數(shù)進(jìn)行一元線性回歸分析

以某電子琴生產(chǎn)商根據(jù)上半年銷(xiāo)售情況通過(guò)直線回歸分析預(yù)測(cè)未來(lái)產(chǎn)品銷(xiāo)售量為例。收集原始銷(xiāo)售收入數(shù)據(jù)，將原始數(shù)據(jù)在Excel文檔中建立一個(gè)數(shù)據(jù)透視表，如表2所示。

根據(jù)表2中的數(shù)據(jù)建立回歸分析數(shù)據(jù)的取數(shù)公式：I7=B5，J7=B6，K7=B7，L7=B8，M7=B9，N7=B10。再利用LINEST函數(shù)定義直線回歸模型中的參數(shù)：D6=INDEX（LINEST（I7：N7，，TRUE，TRUE），1，1），E6=INDEX（LINEST（I7：N7，，TRUE，TRUE），1，2），F(xiàn)6=INDEX（LINEST（I7：N7，，TRUE，TRUE），3，1）。由此可定義未來(lái)銷(xiāo)售量的計(jì)算公式為F10=E9*D6+E6。此時(shí)，只需在表2所示的單元格中輸入相應(yīng)的文本數(shù)據(jù)，即可完成模型構(gòu)建。

表2　直線回歸分析銷(xiāo)售預(yù)測(cè)表

在模型中各單元間建立數(shù)據(jù)鏈接，只需將歷史銷(xiāo)售數(shù)據(jù)分別輸入B5：B10區(qū)域的單元格中，直線回歸分析區(qū)域I7：N7會(huì)分別錄取到相應(yīng)的歷史銷(xiāo)售數(shù)據(jù)，同時(shí)D6：F6區(qū)域各單元格則會(huì)自動(dòng)求出參數(shù)A，B及相關(guān)系數(shù)（R2）的值。R2是判斷所擬合的直線方程是否合理的重要參數(shù)，R2越接近于1，則說(shuō)明時(shí)間與銷(xiāo)售量?jī)烧咧g的線性關(guān)系越顯著，方程所得的預(yù)測(cè)結(jié)論越可靠。若R2的置信度在合理范圍內(nèi)，則在模型的E9單元格中輸入想要預(yù)測(cè)的未來(lái)某個(gè)月份，該月的預(yù)測(cè)銷(xiāo)售值就會(huì)自動(dòng)顯示在F10單元格中。

3.2　利用LINEST函數(shù)進(jìn)行多元直線回歸分析

如上文所述，利用LINEST函數(shù)進(jìn)行多元直線回歸分析時(shí)，與一元直線回歸分析方法相似，而且可以給出各自變量的資料，但需注意的是其數(shù)學(xué)表達(dá)式與一般習(xí)慣不同。一般表達(dá)式為y=a+b1x1+b2，而LINEST函數(shù)的表達(dá)式則為y=M1x1+M2x2+ …+b。式中：m表示斜率，b表示截距［9］?，F(xiàn)以某房地產(chǎn)公司對(duì)一幢小型商業(yè)辦公樓的租賃價(jià)格評(píng)估為例，說(shuō)明其在多元直線回歸分析中的使用方法。該樓使用面積為2500m2，有3間辦公室，2個(gè)入口，已使用25年。為了對(duì)這幢樓房的租賃價(jià)格進(jìn)行預(yù)測(cè)，在待選樓房中隨機(jī)抽取了11個(gè)樣本，具體資料如表3所示。

利用LINEST函數(shù)定義多元直線回歸模型中的計(jì)算公式INDEX（LINEST（E2：E12，A2：D12，TRUE，TRUE），可得如下數(shù)據(jù)（見(jiàn)表4）。其中，A14是斜率m4，B14是斜率m3，D14是斜率m1，E14是截距b。A15：E15區(qū)域各單元格數(shù)值則分別對(duì)應(yīng)5個(gè)標(biāo)準(zhǔn)差se4，se3，se2，se1，seb。而 A16為復(fù)判定系數(shù)、B16為估計(jì)標(biāo)準(zhǔn)差。A17為F值，B17為自由度（n-k-1=11-4-1=6），A18為回歸平方和，B18為剩余平方和。

表3　待選評(píng)估樣本的數(shù)據(jù)

表4　利用LINEST函數(shù)計(jì)算后得到的相關(guān)數(shù)值

根據(jù)表4中A14：E14區(qū)域的數(shù)值可建立多元回歸方程：

以此方程對(duì)評(píng)估目標(biāo)進(jìn)行預(yù)測(cè)即可計(jì)算得到該辦公樓的租賃價(jià)格為158261元。由表4可知復(fù)判定系數(shù)為0.9967，說(shuō)明各自變量與租賃價(jià)格高度相關(guān)。且經(jīng)檢驗(yàn)，當(dāng)自由度為6，顯著性水平為0.05時(shí)，F(xiàn)臨界值為4.53，而上述公式所求得的F值為459.75，遠(yuǎn)大于F臨界值，說(shuō)明自變量與租賃價(jià)格之間的高度相關(guān)不具有偶然性。

但是，眾所周知，在進(jìn)行多元回歸分析時(shí)，由于各個(gè)自變量對(duì)因變量的影響程度不同，精確評(píng)估與預(yù)測(cè)某一因變量還取決于合適自變量的選擇。因此，進(jìn)行數(shù)據(jù)統(tǒng)計(jì)時(shí)，需要分別考察各自變量對(duì)因變量的影響程度，然后進(jìn)行相互比較，通過(guò)選取其中影響較大的進(jìn)行回歸建模。優(yōu)化選擇自變量的方法有多種，較理想的方法是使用逐步回歸法、回歸參數(shù)標(biāo)準(zhǔn)化法等，但這些方法往往計(jì)算起來(lái)比較復(fù)雜，對(duì)非統(tǒng)計(jì)學(xué)專業(yè)人員來(lái)說(shuō)難度較大。所以，通常情況下為了方便起見(jiàn)，我們可以直接根據(jù)構(gòu)建的回歸方程計(jì)算判定系數(shù)，并進(jìn)行各項(xiàng)檢驗(yàn)。

4　結(jié)論

利用LINEST函數(shù)不僅可以對(duì)已知數(shù)據(jù)進(jìn)行最佳線性擬合，還可以返回描述此曲線的數(shù)組。由于此函數(shù)返回?cái)?shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。當(dāng)操作者輸入LINEST函數(shù)后，如果直接點(diǎn)擊電腦鍵盤(pán)的Enter鍵只能得到一個(gè)數(shù)值。所以，必須在選定輸入公式的單元格及相應(yīng)數(shù)據(jù)輸出區(qū)域，在按鍵盤(pán)F2鍵的同時(shí)還需要按“Ctrl+Shift+Enter”才能得到所需的數(shù)組值。這一操作對(duì)于具有基本計(jì)算機(jī)操作技能的人來(lái)說(shuō)簡(jiǎn)便、快捷，只要公式中各數(shù)組區(qū)域的數(shù)值輸入正確，可直接得到相應(yīng)多元回歸方程的參數(shù)。利用該函數(shù)，無(wú)論多項(xiàng)式有多少項(xiàng)或多元回歸中有多少個(gè)自變量，均可快速、準(zhǔn)確地計(jì)算出相關(guān)的回歸參數(shù)。

此外，在進(jìn)行回歸分析時(shí)，LINEST函數(shù)會(huì)計(jì)算每一點(diǎn)y的估計(jì)值和實(shí)際值的平方差。這些平方差之和稱為殘差平方和（ssresid）?；貧w平方和（ssreg）可通過(guò)公式“ssreg=sstotal（總平方和）-ssresid”計(jì)算得到。殘差平方和與總平方和的比值越小，判定系數(shù)R2（R2=ssreg／sstotal且R2取值在0～1）的值越接近1，則說(shuō)明所擬合的模型越合理。這一判定簡(jiǎn)單直觀，利于使用者迅速做出科學(xué)的判斷和決策。

綜上，利用LINEST函數(shù)通過(guò)一元直線回歸或多元直線回歸建立市場(chǎng)營(yíng)銷(xiāo)所需的預(yù)測(cè)模型，此法對(duì)于樣本容量無(wú)特殊要求，能在樣本容量小于變量個(gè)數(shù)的情況下進(jìn)行回歸建模。此外，還可以同時(shí)實(shí)現(xiàn)回歸建模、數(shù)據(jù)結(jié)構(gòu)簡(jiǎn)化以及兩組變量間的相關(guān)分析，在實(shí)際操作時(shí)不需要進(jìn)行復(fù)雜的計(jì)算編程，是一種簡(jiǎn)便、快捷的方法。

參考文獻(xiàn)：

［1］ 杜躍東.淺析現(xiàn)代企業(yè)的市場(chǎng)營(yíng)銷(xiāo)預(yù)測(cè)［J］.商場(chǎng)現(xiàn)代化，2007（2）：123-124.

［2］ 李華，胡奇英.預(yù)測(cè)與決策［M］.西安：西安電子科技大學(xué)出版社，2005.

［3］ 王順金，陳立波.財(cái)務(wù)預(yù)測(cè)中構(gòu)建Excel回歸分析模型［J］.中國(guó)管理信息化，2011，14（6）：11-14.

［4］ 蔡章利，石為人，刁綾.回歸分析在連續(xù)型數(shù)據(jù)目標(biāo)預(yù)測(cè)中的應(yīng)用［J］.重慶大學(xué)學(xué)報(bào)，2005，28（10）：91-93.

［5］ 周自瑋.利用Excel內(nèi)部函數(shù)LINEST進(jìn)行多元回歸和多項(xiàng)式回歸分析［J］.草業(yè)與畜牧，2007（8）：48-52.

［6］ 王岱.LINEST函數(shù)在最小二乘法求直線擬合中的應(yīng)用［J］.考試周刊，2010（54）：202-203.

［7］ 徐繼初.生物統(tǒng)計(jì)及實(shí)驗(yàn)設(shè)計(jì)［M］.北京：農(nóng)業(yè)出版社，1992：112-120.

［8］ 郝敬雷，姜永明，馬彥金，等.LINEST函數(shù)在離心泵流量-揚(yáng)程性能曲線擬合中的應(yīng)用［J］.當(dāng)代化工，2014（1）：636-638.