怎么做圖表趨勢線？

提問人：周麗平發(fā)布時間：2021-07-09

一、預(yù)測下半年各月的銷售金額

要預(yù)測下半年每月的銷售數(shù)據(jù)，就需要進行定量數(shù)據(jù)分析，可以使用散點圖和趨勢線建立回歸模型，獲得擬合度最高的回歸方程后，再利用回歸方程計算出下半年每月的銷售額，然后使用圖表展示出上半年的實際值和下半年的預(yù)測值。春風(fēng)推薦使用帶直線的散點圖，既可以表現(xiàn)數(shù)據(jù)大小，又可以表現(xiàn)變化趨勢。具體步驟如下：

① 選中“B3:B8”單元格，在功能區(qū)中單擊“插入”菜單，選擇圖表功能區(qū)，在“插入散點圖（X、Y）或氣泡圖”菜單下選擇左上角的“散點圖”，單擊“確定”按鈕并關(guān)閉對話框。

② 打開“圖表元素”，勾選“趨勢線”按鈕。

③ 單擊圖表中的趨勢線，打開“設(shè)置趨勢線格式”對話框，選擇“多項式”選項，“順序”值使用默認(rèn)的數(shù)值2，勾選“顯示公式”和“顯示R平方值”復(fù)選框，此時在趨勢線上將獲得回歸方程和R2值。從圖中可以看到，散點與趨勢線結(jié)合緊密，判斷系數(shù)R2=0.9995，回歸方程顯著，可以使用該方程進行趨勢預(yù)測。

這里需要指出，趨勢線雖然僅僅是表示趨勢的線條，但線條有直線也有曲線，且彎曲的程度也各不相同，Excel中的趨勢線有6種不同的類型：

? 線性趨勢線：用于簡單線性數(shù)據(jù)集的最佳擬合直線。

? 指數(shù)趨勢線：用于表示數(shù)據(jù)變化越來越快，是一種斜度越來越大的曲線。

? 對數(shù)趨勢線：用于表示數(shù)據(jù)的增加或減小速度剛開始很快，隨即又迅速趨于平穩(wěn)。

? 冪數(shù)趨勢線：用于表示以一個相對恒定的速率變化的數(shù)據(jù)。

? 多項式趨勢線：用于表示數(shù)據(jù)之間存在較大偏差的數(shù)據(jù)，其階數(shù)可由曲線的拐點數(shù)來預(yù)估，一般數(shù)據(jù)越復(fù)雜，階數(shù)越高。

? 移動平均趨勢線：用于取鄰近數(shù)據(jù)的平均值，將平均值作為趨勢線中的一個點，然后取下一組鄰近數(shù)據(jù)的平均值，以此類推，以平均值作為趨勢線的點。

④ 在A9:A14單元格中輸入數(shù)字“7-12”，代表7月-12月，在C9單元格中輸入公式“=ROUND(4039.6*A9^2-8170.8*A9+91454,0)”，此公式為第③步中，帶入值后的趨勢線上顯示的公式。拖動填充柄將公式復(fù)制到下面對應(yīng)的單元格中，這樣就獲得了7-12月銷售額的預(yù)測值，ROUND（）函數(shù)用于對數(shù)據(jù)小數(shù)點位數(shù)進行取舍。在C1單元格中輸入C列數(shù)據(jù)標(biāo)題“下半年預(yù)測銷售額”，在C8單元格中輸入B8單元格的值“18709”，即把6月份銷售額復(fù)制到預(yù)測銷售額中，這樣能保證后續(xù)曲線不會有斷點。

⑤ 由于數(shù)據(jù)值較大，我們以“萬元”作為金額單位，在“E3:E8”“F8:F14”單元格中輸入10000，通過“選擇性粘貼”的“除”運算把10000“數(shù)值”粘貼到原數(shù)據(jù)表中，設(shè)置小數(shù)位數(shù)為一位。

二、全年銷售額圖的繪制

選擇“A3:C14”單元格區(qū)域，在功能區(qū)中單擊“插入”菜單，選擇圖表功能區(qū)，在“插入散點圖（X、Y）或氣泡圖”菜單下選擇第二行的“帶直線和數(shù)據(jù)標(biāo)記的散點圖”單擊“確定”按鈕關(guān)閉對話框。

設(shè)置橫、縱坐標(biāo)軸格式，單擊橫坐標(biāo)軸，在右側(cè)的“設(shè)置坐標(biāo)軸格式”中選擇“坐標(biāo)軸選項”，設(shè)置“最小值”為1，“最大值”為12，“主要刻度”為1；按照同樣的方法將縱坐標(biāo)軸的“最大值”設(shè)置為60。

右擊圖表中藍(lán)色曲線，點擊“選擇數(shù)據(jù)”按鈕，選擇“系列1”點擊“編輯”，在“系列名稱”中選擇B1單元格；按照同樣的方法，將“系列2”的名稱改為C1單元格的值，單擊“確定”按鈕后，關(guān)閉對話框。這樣就分別為兩條曲線添加了系列名稱“上半年實際銷售額”和“下半年預(yù)測銷售額”。