Excel在財(cái)務(wù)管理中的應(yīng)用課件：銷售管理

銷售管理6.1銷售流向分類匯總6.1.1編制商品基本信息表和銷售明細(xì)匯總表

圖6-1進(jìn)行銷售流向分析,首先要獲取基礎(chǔ)數(shù)據(jù),創(chuàng)建企業(yè)的商品基本信息表和銷售明細(xì)匯總表。企業(yè)的商品基本信息表如圖6-1所示,已在第5章中介紹,在此不再贅述。6.1.1編制商品基本信息表和銷售明細(xì)匯總表

圖6-2銷售明細(xì)匯總表是對企業(yè)銷售商品的供應(yīng)信息和渠道信息的匯總,通常包含銷售日期、銷售地域、商品信息、供應(yīng)商信息、銷售人員、銷售價(jià)格、銷售金額等。圖6-2所示為本章案例,企業(yè)可根據(jù)實(shí)際情況和需要靈活編制及錄入。6.1.2編制銷售流向分類匯總表

圖6-3銷售流向分類匯總表可以按客戶、銷售員或地區(qū)等進(jìn)行匯總,通過Excel的分類匯總功能,可以從不同角度考察和分析銷售數(shù)據(jù)?！纠?-1】以圖6-2為數(shù)據(jù)源,按客戶進(jìn)行銷售流向匯總分析?！静僮鞣椒ā康谝徊?單擊數(shù)據(jù)源區(qū)域內(nèi)的任意單元格,選擇“數(shù)據(jù)”選項(xiàng)卡下“分級顯示”組中的“分類匯總”選項(xiàng)。第二步,在“分類匯總”對話框中,選擇以“客戶簡稱”為分類字段,匯總方式為“求和”,選定匯總項(xiàng)為“金額”,并替換當(dāng)前分類匯總,匯總結(jié)果顯示在數(shù)據(jù)下方,單擊“確定”選項(xiàng),如圖6-3所示。6.1.2編制銷售流向分類匯總表

圖6-4通過分類匯總功能,Excel工作表能夠按客戶簡稱對金額匯總,結(jié)果如圖6-4所示。得到分類匯總結(jié)果后,可以看到工作表左側(cè)顯示分類匯總的三個(gè)級別,單擊1級,只顯示總計(jì)項(xiàng);單擊2級,顯示各類匯總項(xiàng)和總計(jì)項(xiàng);單擊3級,顯示所有明細(xì)。6.1.2編制銷售流向分類匯總表

圖6-5第三步,取消分類匯總。當(dāng)分類匯總表使用完畢,可選擇“數(shù)據(jù)”選項(xiàng)卡下“分級顯示”組中的“分類匯總”選項(xiàng),出現(xiàn)對話框后單擊“全部刪除”選項(xiàng),如圖6-5所示,即可恢復(fù)原來的銷售明細(xì)匯總表。6.2銷售流向分析6.2.1編制銷售流向數(shù)據(jù)透視表

圖6-6數(shù)據(jù)透視表是對大量數(shù)據(jù)快速匯總,并建立交叉分析表的數(shù)據(jù)分析工具。銷售情況分析是對各銷售網(wǎng)點(diǎn)或分公司的銷售數(shù)據(jù)進(jìn)行重新組合,按照需求進(jìn)行統(tǒng)計(jì)和分析。因此,數(shù)據(jù)透視表對銷售情況分析十分有用。本部分基于Excel的數(shù)據(jù)透視表功能對企業(yè)銷售流向分析進(jìn)行講解?！纠?-2】基于圖6-2所示的銷售明細(xì)匯總表中的數(shù)據(jù)信息,制作數(shù)據(jù)透視表?！静僮鞣椒ā康谝徊?選中數(shù)據(jù)源區(qū)域,即A2至O12單元格區(qū)域,單擊“插入”選項(xiàng)卡下“表格”組中的“數(shù)據(jù)透視表”選項(xiàng)。第二步,設(shè)置數(shù)據(jù)透視表字段。將“網(wǎng)點(diǎn)”字段放入“篩選”區(qū)域;將“商品名稱”放入“列”區(qū)域;將“月份”和“客戶簡稱”放入“行”區(qū)域;將“金額”放入“∑值”區(qū)域,如圖6-6所示。6.2.1編制銷售流向數(shù)據(jù)透視表

圖6-7第三步,調(diào)整數(shù)據(jù)透視表布局。單擊“設(shè)計(jì)”選項(xiàng)卡下“布局”組中的“分類匯總”選項(xiàng),選擇“在組的頂部顯示所有分類匯總”;單擊“布局”組中的“報(bào)表布局”選項(xiàng),選擇“以表格形式顯示”。得到的數(shù)據(jù)透視表如圖6-7所示。數(shù)據(jù)透視表可以對網(wǎng)點(diǎn)進(jìn)行篩選,也可以對月份、客戶簡稱、商品名稱進(jìn)行篩選,并進(jìn)行數(shù)據(jù)分析。6.2.2創(chuàng)建銷售流向數(shù)據(jù)透視圖

圖6-8數(shù)據(jù)透視圖是基于數(shù)據(jù)透視表中的數(shù)據(jù)繪制的可視化圖形,通過數(shù)據(jù)透視圖能夠更清晰地了解數(shù)據(jù)之間的關(guān)系?！纠?-3】基于圖6-7的數(shù)據(jù)透視表,繪制數(shù)據(jù)透視圖?！静僮鞣椒ā康谝徊?選中數(shù)據(jù)源區(qū)域,即A4至G16單元格區(qū)域,單擊“插入”選項(xiàng)卡下“圖表”組中的“數(shù)據(jù)透視圖”選項(xiàng)。第二步,在“插入圖表”對話框中選擇需要插入的圖表類型,本例以簇狀柱形圖為例。第三步,在“設(shè)計(jì)”選項(xiàng)卡下選擇合適的圖表樣式,得到數(shù)據(jù)透視圖,如圖6-8所示。6.3銷售預(yù)測銷售預(yù)測是根據(jù)企業(yè)過去的銷售相關(guān)數(shù)據(jù),在盡量考慮未來各種可能影響因素的基礎(chǔ)上,通過一定的分析方法制定銷售目標(biāo)。簡單來說,就是對企業(yè)產(chǎn)品在未來一定時(shí)期內(nèi)的銷售數(shù)量與銷售金額做出估計(jì)。在企業(yè)銷售管理過程中,銷售預(yù)測是極其重要的一個(gè)環(huán)節(jié)。一方面,科學(xué)地進(jìn)行銷售預(yù)測便于企業(yè)更加合理地制定銷售計(jì)劃;另一方面,銷售預(yù)測是企業(yè)預(yù)算的基礎(chǔ),銷售預(yù)測的失敗會導(dǎo)致企業(yè)預(yù)算失敗甚至全面預(yù)算管理的坍塌。6.3.1應(yīng)用Excel進(jìn)行銷售預(yù)測概述企業(yè)進(jìn)行銷售預(yù)測的方法有很多,其中較常用的方法為趨勢預(yù)測法和因果預(yù)測法。前者根據(jù)企業(yè)過去銷售數(shù)據(jù)的時(shí)間趨勢特征進(jìn)行銷售預(yù)測,后者則強(qiáng)調(diào)找出影響企業(yè)銷售的最主要因素。趨勢預(yù)測法包括移動平均法、指數(shù)平滑法等;因果預(yù)測法包括回歸直線分析法、回歸曲線分析法及多元線性分析法等,如表6-1所示。本節(jié)僅對應(yīng)用移動平均法、指數(shù)平滑法、回歸直線分析法和回歸曲線分析法進(jìn)行銷售預(yù)測做詳細(xì)講解。6.3.1應(yīng)用Excel進(jìn)行銷售預(yù)測概述Excel中有專門的數(shù)據(jù)分析工具可以簡便快速地進(jìn)行簡單移動平均計(jì)算。在首次使用Excel數(shù)據(jù)分析工具之前,必須先加載分析工具庫。加載分析工具庫的操作步驟如下:第一步,在菜單欄中單擊“文件”,選擇“選項(xiàng)”;第二步,單擊“加載項(xiàng)”,在“非活動應(yīng)用程序加載項(xiàng)”中找到“分析工具庫”,并單擊“轉(zhuǎn)到”選項(xiàng),如圖6-9所示。

圖6-96.3.1應(yīng)用Excel進(jìn)行銷售預(yù)測概述第三步,選擇“分析工具庫”選項(xiàng),并單擊“確定”選項(xiàng),如圖6-10所示。

圖6-106.3.1應(yīng)用Excel進(jìn)行銷售預(yù)測概述此時(shí),在“數(shù)據(jù)”選項(xiàng)卡下分析組中有“數(shù)據(jù)分析”功能,如圖6-11所示。

圖6-11分析工具庫包含描述統(tǒng)計(jì)、直方圖、相關(guān)系數(shù)、移動平均、指數(shù)平滑、回歸等19種數(shù)據(jù)統(tǒng)計(jì)分析方法。但是與專業(yè)的統(tǒng)計(jì)分析軟件相比,可處理的數(shù)據(jù)量和模型復(fù)雜程度有限。下面對應(yīng)用Excel的分析工具和函數(shù)工具進(jìn)行銷售預(yù)測的方法進(jìn)行講解。6.3.2移動平均法1.簡單移動平均法移動平均法是一種簡單平滑預(yù)測技術(shù),根據(jù)時(shí)間序列資料逐項(xiàng)推移,一次計(jì)算包含一定項(xiàng)數(shù)的序時(shí)平均值,以反映長期趨勢。因此,當(dāng)時(shí)間序列的數(shù)值由于周期變動和隨機(jī)波動的影響起伏較大,不易顯示出事件的發(fā)展趨勢時(shí),使用移動平均法可以消除這些因素的影響,顯示事件的發(fā)展方向和趨勢(即趨勢線),依據(jù)趨勢線分析預(yù)測序列的長期趨勢。應(yīng)用Excel的數(shù)據(jù)分析工具能夠直接根據(jù)歷史數(shù)據(jù)進(jìn)行預(yù)測并生成圖表。【例6-4】圖6-12為某公司2019年上海網(wǎng)點(diǎn)銷售數(shù)據(jù),要求:使用Excel的數(shù)據(jù)分析工具,以移動平均法預(yù)測該公司2020年銷售額。

圖6-126.3.2移動平均法【操作方法】第一步,單擊“數(shù)據(jù)”選項(xiàng)卡下“分析”組中的“數(shù)據(jù)分析”選項(xiàng)。第二步,在“數(shù)據(jù)分析”對話框中選擇分析工具為“移動平均”,并單擊“確定”選項(xiàng)。第三步,在“移動平均”對話框中選擇數(shù)據(jù)區(qū)域?yàn)?019年上海網(wǎng)點(diǎn)銷售數(shù)據(jù)區(qū)域,即B3至M3單元格區(qū)域;選擇“間隔”為2;選擇輸出區(qū)域,如B6單元格;選中“圖表輸出”和“標(biāo)準(zhǔn)誤差”,并單擊“確定”選項(xiàng),如圖6-13所示。注意,使用移動平均法需要設(shè)定“間隔”,移動平均數(shù)的計(jì)算限定在間隔數(shù)之內(nèi)。若移動平均數(shù)為2,則每個(gè)移動平均數(shù)都是前2個(gè)原始數(shù)據(jù)的平均值。本例以間隔為2舉例,在實(shí)際應(yīng)用中,可根據(jù)具體需求更改間隔。

圖6-136.3.2移動平均法第四步,通過上述操作能夠得到2020年上海網(wǎng)點(diǎn)銷售預(yù)測數(shù)據(jù),為生成的表格添加標(biāo)題,得到的結(jié)果如圖6-14所示,其中第6行為Excel自動生成的預(yù)測數(shù)據(jù)。

圖6-146.3.2移動平均法第五步,由于在第三步的“移動平均”對話框中選擇了“圖表輸出”,因此,在得到2020年上海網(wǎng)點(diǎn)銷售預(yù)測數(shù)據(jù)的同時(shí),Excel會同時(shí)生成相應(yīng)的圖表,給生成的圖表重命名其圖表標(biāo)題及坐標(biāo)軸標(biāo)題,得到的結(jié)果如圖6-15所示。

圖6-156.3.2移動平均法上述操作是以2為間隔的簡單移動平均。在實(shí)際銷售預(yù)測工作中,只需要改變上述分析工具中的間隔就可以得到任意間隔的銷售預(yù)測情況。如果將間隔設(shè)置為3,重復(fù)上述操作后得到的2020年上海網(wǎng)點(diǎn)銷售預(yù)測數(shù)據(jù)及銷售預(yù)測圖如圖6-16所示。

圖6-166.3.2移動平均法2.加權(quán)移動平均法加權(quán)移動平均法,是指根據(jù)同一個(gè)移動期內(nèi)不同時(shí)期的數(shù)據(jù)對預(yù)測值的影響程度給予不同的權(quán)數(shù),然后進(jìn)行平均移動以預(yù)測未來值的方法。加權(quán)移動平均法與簡單移動平均法的區(qū)別在于,簡單移動平均法對移動期內(nèi)的各時(shí)期數(shù)據(jù)同等看待,而加權(quán)移動平均法則考慮了移動期內(nèi)不同時(shí)期的數(shù)據(jù)對預(yù)測值的影響程度不同,彌補(bǔ)了簡單移動平均法的不足。通常情況下,近期的數(shù)據(jù)對預(yù)測值的影響程度較大,因此,加權(quán)移動平均法會對近期數(shù)據(jù)賦予較大權(quán)數(shù),而對遠(yuǎn)期的數(shù)據(jù)賦予較小的權(quán)數(shù)。使用加權(quán)移動平均法時(shí),首先需要對每個(gè)時(shí)期的數(shù)據(jù)賦予一定的權(quán)數(shù),并且權(quán)數(shù)總和為1?！纠?-5】現(xiàn)有2019年上海網(wǎng)點(diǎn)銷售數(shù)據(jù),根據(jù)近期數(shù)據(jù)權(quán)數(shù)大而遠(yuǎn)期數(shù)據(jù)權(quán)數(shù)小的特點(diǎn),得到如圖6-17所示數(shù)據(jù)。要求:使用Excel的數(shù)據(jù)分析工具,以加權(quán)移動平均法預(yù)測該公司2020年銷售額。

圖6-176.3.2移動平均法【操作方法】應(yīng)用加權(quán)移動平均法進(jìn)行銷售預(yù)測時(shí),需要使用SUMPRODUCT函數(shù)。SUMPRODUCT函數(shù)即乘積和函數(shù)(詳見本書第3章3.3節(jié))。第一步,在N1至Z4單元格區(qū)域內(nèi),編制2020年上海網(wǎng)點(diǎn)銷售預(yù)測數(shù)據(jù)表,如圖6-18所示。

圖6-186.3.2移動平均法第二步,單擊O4單元格,并輸入“=SUMPRODUCT(B4:M4,$B$3:$M$3)”,函數(shù)返回結(jié)果即為2020年1月份的銷售預(yù)測值。第三步,將鼠標(biāo)移到O4單元格右下角,待光標(biāo)變?yōu)槭中?即為填充柄狀態(tài),向右填充至Z4單元格,即可將單元格O4中的函數(shù)復(fù)制到P4至Z4區(qū)域并顯示函數(shù)結(jié)果,得到的2020年上海網(wǎng)點(diǎn)銷售預(yù)測數(shù)據(jù)如圖6-19所示。

圖6-196.3.3指數(shù)平滑法指數(shù)平滑法也是一種常見的預(yù)測方法,它是在移動平均法的基礎(chǔ)上發(fā)展而來的,或者說,它就是加權(quán)移動平均法的一種特殊形式。指數(shù)平滑法通過計(jì)算指數(shù)平滑值,配合一定的時(shí)間序列預(yù)測模型對未來進(jìn)行預(yù)測。指數(shù)平滑法的基本公式是:

St=αSt-1+(1-α)St-1(6-1)式中,St代表下期預(yù)測數(shù);St-1代表本期實(shí)際數(shù);St-1代表本期預(yù)測數(shù);α為平滑指數(shù)且0<α<1;1-α為阻尼系數(shù)。上式是從公式St=St-1+α(St-1-St-1)變化而來的,后者表示指數(shù)平滑法的基本原理:在本期預(yù)測數(shù)的基礎(chǔ)上加上用平滑指數(shù)調(diào)整過的本期實(shí)際數(shù)與本期預(yù)測數(shù)的差,即可求出下期預(yù)測數(shù)。平滑指數(shù)α的作用是在一定程度上消除偶然因素引起的歷史數(shù)據(jù)的波動。α越大,近期歷史數(shù)據(jù)對預(yù)測結(jié)果的影響越大。使用指數(shù)平滑法進(jìn)行銷售預(yù)測與移動平均法一樣簡單易行,只需錄入銷售歷史數(shù)據(jù)和設(shè)置平滑指數(shù)。平滑指數(shù)可以根據(jù)歷史資料中過去預(yù)測數(shù)與過去實(shí)際數(shù)的差距而定,差距大則可將平滑指數(shù)設(shè)置得大一些;反之,則可將平滑指數(shù)設(shè)置得小一些。根據(jù)平滑次數(shù)的不同,指數(shù)平滑法可分為一次指數(shù)平滑法、二次指數(shù)平滑法和三次指數(shù)平滑法等。在實(shí)際工作中,二次指數(shù)平滑法用得最多,而且二次指數(shù)平滑法必須在一次指數(shù)平滑法的基礎(chǔ)上進(jìn)行。下面分別對一次指數(shù)平滑法和二次指數(shù)平滑法進(jìn)行講解。6.3.2移動平均法1.一次指數(shù)平滑法【例6-6】現(xiàn)有2019年上海網(wǎng)點(diǎn)銷售數(shù)據(jù),使用Excel的數(shù)據(jù)分析工具,以指數(shù)平滑法預(yù)測該公司2020年銷售額。【操作方法】第一步,單擊“數(shù)據(jù)”選項(xiàng)卡下“分析”組中的“數(shù)據(jù)分析”選項(xiàng)。第二步,在“數(shù)據(jù)分析”對話框中選中“指數(shù)平滑”選項(xiàng),并單擊“確定”。第三步,在“指數(shù)平滑”對話框中選擇數(shù)據(jù)區(qū)域?yàn)?019年上海網(wǎng)點(diǎn)銷售數(shù)據(jù)區(qū)域,即B3至N3單元格區(qū)域;選擇“阻尼系數(shù)”為0.3;選擇輸出區(qū)域,如B7單元格;選中“圖表輸出”和“標(biāo)準(zhǔn)誤差”,并單擊“確定”,如圖6-20所示。

圖6-206.3.2移動平均法通過上述操作能夠得到2020年上海網(wǎng)點(diǎn)銷售數(shù)據(jù)。對生成的數(shù)據(jù)添加標(biāo)題,結(jié)果如圖6-21所示。

圖6-216.3.2移動平均法2.二次指數(shù)平滑法在一次指數(shù)平滑的基礎(chǔ)上再次進(jìn)行指數(shù)平滑操作,就是二次指數(shù)平滑法。二次指數(shù)平滑是對一次指數(shù)平滑的再平滑,因此在進(jìn)行二次指數(shù)平滑時(shí)需要以一次指數(shù)平滑的結(jié)果作為基礎(chǔ)數(shù)據(jù)。二次指數(shù)平滑法的操作過程與一次指數(shù)平滑法相同,只是在“分析工具”對話框中的輸出區(qū)域和輸入?yún)^(qū)域的選擇上有所區(qū)別。在上例的基礎(chǔ)上進(jìn)行二次指數(shù)平滑操作,操作步驟如下:第一步,單擊“數(shù)據(jù)”選項(xiàng)卡下“分析”組中的“數(shù)據(jù)分析”選項(xiàng)。第二步,在“數(shù)據(jù)分析”對話框中選中“指數(shù)平滑”選項(xiàng),并單擊“確定”。第三步,在“指數(shù)平滑”對話框中選擇數(shù)據(jù)區(qū)域?yàn)?020年上海網(wǎng)點(diǎn)銷售數(shù)據(jù)區(qū)域,即C7至N7單元格區(qū)域;選擇“阻尼系數(shù)”為0.3;選擇輸出區(qū)域,如C22單元格;選中“圖表輸出”和“標(biāo)準(zhǔn)誤差”,并單擊“確定”,如圖6-22所示。

圖6-226.3.2移動平均法通過上述操作,能夠得到用二次指數(shù)平滑法預(yù)測的2020年上海網(wǎng)點(diǎn)銷售數(shù)據(jù),如圖6-23所示。

圖6-236.3.4回歸直線分析法回歸直線分析法研究的因果關(guān)系只涉及一個(gè)自變量和一個(gè)因變量,并且該因果關(guān)系呈直線分布,即模擬為一條一元一次直線方程y=ax+b。如果將該直線延伸,可以得到較為直觀的預(yù)測值。此處的回歸直線分析法屬于銷售預(yù)測中的趨勢預(yù)測法,即自變量是時(shí)間。后面還會講解因果預(yù)測法中的回歸直線分析法,其自變量是影響企業(yè)銷售的最主要因素。運(yùn)用回歸直線分析法進(jìn)行銷售預(yù)測時(shí),可以應(yīng)用Excel函數(shù),也可以應(yīng)用Excel圖表中趨勢線和趨勢線方程,以下分別講解。6.3.4回歸直線分析法1.應(yīng)用函數(shù)進(jìn)行預(yù)測我們可以應(yīng)用FORECAST函數(shù)進(jìn)行銷售預(yù)測,也可以應(yīng)用SLOPE函數(shù)和INTERCEPT函數(shù),或者LINEST和INDEX嵌套函數(shù)進(jìn)行銷售預(yù)測。這幾種方法的區(qū)別是:應(yīng)用FORECAST函數(shù)只能得到預(yù)測值;而應(yīng)用SLOPE函數(shù)和INTERCEPT函數(shù),或者應(yīng)用LINEST和INDEX嵌套函數(shù)則是通過求出直線方程的斜率a和截距b計(jì)算預(yù)測值。本節(jié)僅以應(yīng)用FORECAST函數(shù)和應(yīng)用LINEST和INDEX嵌套函數(shù)為例進(jìn)行講解。(1)FORECAST函數(shù)。FORECAST函數(shù)是統(tǒng)計(jì)函數(shù),具體含義是,應(yīng)用回歸直線方程y=a+bx,根據(jù)給定的x值返回要預(yù)測的y值。該函數(shù)的語法為FORECAST(x,known_y's,known_x's)。其中,參數(shù)x為需要預(yù)測的數(shù)據(jù)點(diǎn)x值;參數(shù)known_y's為給定的已知y值集合;參數(shù)known_x's為給定的已知x值集合?！纠?-7】現(xiàn)有某公司2019年1月至11月的上海網(wǎng)點(diǎn)銷售數(shù)據(jù),如圖6-24所示。要求:使用FORECAST函數(shù)預(yù)測12月的銷售額。

圖6-246.3.4回歸直線分析法【操作方法】將顯示數(shù)據(jù)的單元格設(shè)置為B9單元格,單擊B9單元格,輸入“=FORECAST(12,B4:L4,B3:L3)”,即可顯示2019年12月份的預(yù)測數(shù)據(jù),如圖6-25所示。

圖6-256.3.4回歸直線分析法(2)LINEST和INDEX嵌套函數(shù)。LINEST函數(shù)是統(tǒng)計(jì)函數(shù),具體含義是通過使用最小二乘法計(jì)算與現(xiàn)有數(shù)據(jù)最佳擬合的直線,并返回由描述此直線的斜率a和截距b形成的數(shù)組。該函數(shù)的語法為LINEST(known_y's,[known_x's],[const],[stats])。其中,參數(shù)known_y's為給定的已知y值集合。參數(shù)known_x's為給定的已知x值集合,如缺省則表示該x值集合為{1,2,3,…},集合大小與known_y's相同。參數(shù)const是一個(gè)布爾值,用于指定是否將截距b強(qiáng)制設(shè)為0,如果const為TRUE或缺省,b將按正常計(jì)算;如果const為FALSE,b將被設(shè)為0。參數(shù)stats也是一個(gè)布爾值,用于指定是否返回附加回歸統(tǒng)計(jì)值,如果stats為TRUE,則函數(shù)返回附加回歸統(tǒng)計(jì)值;如果stats為FALSE或缺省,則函數(shù)只返回斜率a和截距b形成的數(shù)組。需要注意的是,LINEST函數(shù)返回的是數(shù)值數(shù)組,因此單獨(dú)使用時(shí)必須以數(shù)組的形式輸入,否則需要與其他函數(shù)嵌套使用。INDEX函數(shù)是查找與引用函數(shù),具體含義是返回表格或區(qū)域中的值或值的引用。INDEX函數(shù)有兩種使用方法:第一種是返回指定單元格或單元格數(shù)組的值,即數(shù)組形式;第二種是返回指定單元格的引用,即引用形式。6.3.4回歸直線分析法INDEX的數(shù)組形式的功能是返回給定單元格或數(shù)組中行號列標(biāo)的元素的值,其語法為INDEX(array,[row_num],[column_num])。其中,參數(shù)array是給定的單元格區(qū)域或數(shù)組。參數(shù)row_num是array中某行的行號,函數(shù)從該行返回?cái)?shù)值,若array只包含一行,可省略row_num,但必須有參數(shù)column_num。參數(shù)column_num是array中某列的列標(biāo),函數(shù)從該列返回?cái)?shù)值,若array只包含一列,可省略column_num,但必須有參數(shù)row_num。如果同時(shí)使用參數(shù)row_num和column_num,函數(shù)返回row_num和column_num交叉處的單元格的值;如果將row_num或column_num設(shè)置為0或缺省,函數(shù)分別返回整個(gè)列或行的值。INDEX的引用形式的功能是返回給定行列交叉處的單元格引用。如果引用由不連續(xù)的選定區(qū)域組成,可以選擇某一選定區(qū)域,其語法為INDEX(reference,[row_num],[column_num],[area_num])。其中,參數(shù)reference為給定的對一個(gè)或多個(gè)單元格區(qū)域的引用,如果為引用不連續(xù)的區(qū)域,必須用括號括起來,例如,(A1:D3,A5:D7)。參數(shù)row_num代表reference中某行的行號,函數(shù)從該行返回一個(gè)引用。參數(shù)column_num代表reference中某列的列標(biāo),函數(shù)從該列返回一個(gè)引用。參數(shù)area_num代表選擇引用中的一個(gè)區(qū)域,返回該區(qū)域中row_num和column_num的交叉區(qū)域。選中或輸入的第一個(gè)區(qū)域序號為1,第二個(gè)為2,以此類推。缺省則表示函數(shù)使用區(qū)域1。6.3.4回歸直線分析法【例6-8】現(xiàn)有某公司2019年1月至11月的上海網(wǎng)點(diǎn)銷售數(shù)據(jù),如圖6-26所示。要求:應(yīng)用回歸直線分析法,使用LINEST和INDEX嵌套函數(shù)預(yù)測12月的銷售額。

圖6-266.3.4回歸直線分析法【操作方法】使用LINEST和INDEX嵌套函數(shù)計(jì)算斜率a和截距b。第一步,單擊顯示斜率a的單元格,如B12單元格,輸入“=INDEX(LINEST(B4:L4,B3:L3),1)”。第二步,單擊顯示截距b的單元格,如C12單元格,輸入“=INDEX(LINEST(B4:L4,B3:L3),2)”。第三步,單擊顯示12月份銷售額預(yù)測值的單元格,如D12單元格,輸入“=B12*12+C12”。得到的結(jié)果如圖6-27所示。

圖6-276.3.4回歸直線分析法2.應(yīng)用趨勢線和趨勢線方程進(jìn)行預(yù)測為了更加直觀地反映銷售額變化情況,在上例的基礎(chǔ)上可以做一個(gè)折線圖進(jìn)行預(yù)測?！纠?-9】在上例的基礎(chǔ)上,插入折線圖并添加趨勢線?！静僮鞣椒ā康谝徊?選中A4至L4單元格區(qū)域,單擊“插入”選項(xiàng)卡,在“圖表”組中選擇“插入折線圖或面積圖”,選擇“帶數(shù)據(jù)標(biāo)記的折線圖”,即可生成一個(gè)銷量圖。第二步,單擊圖表右上方的十字形符號,選擇“圖例”,設(shè)置為“底部”,勾選“趨勢線”,更改標(biāo)題,選中生成的趨勢線,在“設(shè)置趨勢線格式”窗口中選擇“趨勢線選項(xiàng)”為“線性”,并選中“顯示公式”和“顯示R平方值”,如圖6-28所示。

圖6-286.3.4回歸直線分析法通過上述操作步驟能得到一條線性的趨勢線,如圖6-29所示,這條趨勢線代表計(jì)算出的直線回歸方程。在進(jìn)行銷售預(yù)測時(shí),只需要延長趨勢線,即可得到未來某一時(shí)期的預(yù)測銷售量。

圖6-296.3.5回歸曲線分析法回歸曲線分析法與回歸直線分析法的原理一致,只涉及一個(gè)自變量和一個(gè)因變量,但回歸直線分析法是一元一次回歸,而回歸曲線分析法則是