預(yù)測數(shù)據(jù)走勢必會(huì)技巧:為excel圖表添加趨勢線!
?
作者:賦春風(fēng)來源:部落窩教育發(fā)布時(shí)間:2020-05-08 14:21:31點(diǎn)擊:11292
編按:
哈嘍,大家好!對于exceler而言,預(yù)測數(shù)據(jù)走勢,如銷量趨勢預(yù)測、股指走勢預(yù)測等是日常處理、分析數(shù)據(jù)工作中的一部分。面對這類問題,我們可以使用Excel圖表中的趨勢線對數(shù)據(jù)未來走勢進(jìn)行預(yù)測,幫助我們更加直觀地了解數(shù)據(jù)變化的趨勢。下面我們就和春風(fēng)一起來學(xué)習(xí)excel中的趨勢線吧!
趨勢線是使用EXCEL進(jìn)行數(shù)據(jù)預(yù)測時(shí),非常重要的方法,它是圖表中表示數(shù)據(jù)系列趨勢的一種輔助線,在數(shù)據(jù)分析過程中,為了更加直觀地了解數(shù)據(jù)變化的趨勢,可以為圖表中某個(gè)系列添加趨勢線來輔助分析。當(dāng)趨勢線向上傾斜,表示數(shù)據(jù)有增加或上漲趨勢,向下傾斜則表示數(shù)據(jù)有減少或下跌趨勢。相信不少exceler都遇到過與預(yù)測數(shù)據(jù)走勢相關(guān)的問題,下面就讓我們在實(shí)踐中學(xué)習(xí)趨勢線的用法吧。
已知某銷售公司1到6月的銷售數(shù)據(jù),需要預(yù)測出該公司下半年每月的銷售數(shù)據(jù)走勢。
原始表格:
生成圖形:
一、預(yù)測下半年各月的銷售金額
要預(yù)測下半年每月的銷售數(shù)據(jù),就需要進(jìn)行定量數(shù)據(jù)分析,可以使用散點(diǎn)圖和趨勢線建立回歸模型,獲得擬合度最高的回歸方程后,再利用回歸方程計(jì)算出下半年每月的銷售額,然后使用圖表展示出上半年的實(shí)際值和下半年的預(yù)測值。春風(fēng)推薦使用帶直線的散點(diǎn)圖,既可以表現(xiàn)數(shù)據(jù)大小,又可以表現(xiàn)變化趨勢。具體步驟如下:
① 選中“B3:B8”單元格,在功能區(qū)中單擊“插入”菜單,選擇圖表功能區(qū),在“插入散點(diǎn)圖(X、Y)或氣泡圖”菜單下選擇左上角的“散點(diǎn)圖”,單擊“確定”按鈕并關(guān)閉對話框。
② 打開“圖表元素”,勾選“趨勢線”按鈕。
圖
③ 單擊圖表中的趨勢線,打開“設(shè)置趨勢線格式”對話框,選擇“多項(xiàng)式”選項(xiàng),“順序”值使用默認(rèn)的數(shù)值2,勾選“顯示公式”和“顯示R平方值”復(fù)選框,此時(shí)在趨勢線上將獲得回歸方程和R2值。從圖中可以看到,散點(diǎn)與趨勢線結(jié)合緊密,判斷系數(shù)R2=0.9995,回歸方程顯著,可以使用該方程進(jìn)行趨勢預(yù)測。
這里需要指出,趨勢線雖然僅僅是表示趨勢的線條,但線條有直線也有曲線,且彎曲的程度也各不相同,Excel中的趨勢線有6種不同的類型:
? 線性趨勢線:用于簡單線性數(shù)據(jù)集的最佳擬合直線。
? 指數(shù)趨勢線:用于表示數(shù)據(jù)變化越來越快,是一種斜度越來越大的曲線。
? 對數(shù)趨勢線:用于表示數(shù)據(jù)的增加或減小速度剛開始很快,隨即又迅速趨于平穩(wěn)。
? 冪數(shù)趨勢線:用于表示以一個(gè)相對恒定的速率變化的數(shù)據(jù)。
? 多項(xiàng)式趨勢線:用于表示數(shù)據(jù)之間存在較大偏差的數(shù)據(jù),其階數(shù)可由曲線的拐點(diǎn)數(shù)來預(yù)估,一般數(shù)據(jù)越復(fù)雜,階數(shù)越高。
? 移動(dòng)平均趨勢線:用于取鄰近數(shù)據(jù)的平均值,將平均值作為趨勢線中的一個(gè)點(diǎn),然后取下一組鄰近數(shù)據(jù)的平均值,以此類推,以平均值作為趨勢線的點(diǎn)。
④ 在A9:A14單元格中輸入數(shù)字“7-12”,代表7月-12月,在C9單元格中輸入公式“=ROUND(4039.6*A9^2-8170.8*A9+91454,0)”,此公式為第③步中,帶入值后的趨勢線上顯示的公式。拖動(dòng)填充柄將公式復(fù)制到下面對應(yīng)的單元格中,這樣就獲得了7-12月銷售額的預(yù)測值,ROUND()函數(shù)用于對數(shù)據(jù)小數(shù)點(diǎn)位數(shù)進(jìn)行取舍。在C1單元格中輸入C列數(shù)據(jù)標(biāo)題“下半年預(yù)測銷售額”,在C8單元格中輸入B8單元格的值“18709”,即把6月份銷售額復(fù)制到預(yù)測銷售額中,這樣能保證后續(xù)曲線不會(huì)有斷點(diǎn)。
⑤ 由于數(shù)據(jù)值較大,我們以“萬元”作為金額單位,在“E3:E8”“F8:F14”單元格中輸入10000,通過“選擇性粘貼”的“除”運(yùn)算把10000“數(shù)值”粘貼到原數(shù)據(jù)表中,設(shè)置小數(shù)位數(shù)為一位。
二、全年銷售額圖的繪制
選擇“A3:C14”單元格區(qū)域,在功能區(qū)中單擊“插入”菜單,選擇圖表功能區(qū),在“插入散點(diǎn)圖(X、Y)或氣泡圖”菜單下選擇第二行的“帶直線和數(shù)據(jù)標(biāo)記的散點(diǎn)圖”單擊“確定”按鈕關(guān)閉對話框。
設(shè)置橫、縱坐標(biāo)軸格式,單擊橫坐標(biāo)軸,在右側(cè)的“設(shè)置坐標(biāo)軸格式”中選擇“坐標(biāo)軸選項(xiàng)”,設(shè)置“最小值”為1,“最大值”為12,“主要刻度”為1;按照同樣的方法將縱坐標(biāo)軸的“最大值”設(shè)置為60。
右擊圖表中藍(lán)色曲線,點(diǎn)擊“選擇數(shù)據(jù)”按鈕,選擇“系列1”點(diǎn)擊“編輯”,在“系列名稱”中選擇B1單元格;按照同樣的方法,將“系列2”的名稱改為C1單元格的值,單擊“確定”按鈕后,關(guān)閉對話框。這樣就分別為兩條曲線添加了系列名稱“上半年實(shí)際銷售額”和“下半年預(yù)測銷售額”。
在圖表中選擇“下半年預(yù)測銷售額”數(shù)據(jù)系列的第一個(gè)數(shù)據(jù)點(diǎn),在“設(shè)置數(shù)據(jù)點(diǎn)格式”對話框中將其數(shù)據(jù)標(biāo)記的填充顏色設(shè)置與“上半年實(shí)際銷售額”數(shù)據(jù)系列相同。
下面我們將預(yù)測的數(shù)據(jù)用虛線連接。在圖表中選擇“下半年預(yù)測銷售額”數(shù)據(jù)系列的數(shù)據(jù)點(diǎn),設(shè)置連接折線的“短劃線類型”為虛線。
接著為數(shù)據(jù)系列添加“數(shù)據(jù)標(biāo)簽”“橫坐標(biāo)軸標(biāo)題”“縱坐標(biāo)軸標(biāo)題”“圖表標(biāo)題”。
最后美化圖形。在圖表工具中選擇合適的樣式,刪除“主軸主要垂直網(wǎng)格線”,設(shè)置縱坐標(biāo)軸線條為“無線條”,調(diào)整圖例位置、圖表以及圖表區(qū)的大小。
OK,一張含趨勢線的圖表就完成了,掌握趨勢線的用法,你將有種一切未來盡在掌握的感覺。聰明的你有什么別的想法,歡迎留言。
本文配套的練習(xí)課件請加入QQ群:1003077796下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
用Excel制作曲線圖《年終報(bào)告,你需要這張Excel圖表來“撐場面”》
Excel折線圖新玩法《5個(gè)小步驟,excel折線圖比同事漂亮10倍》
Excel紅旗圖表《看膩了折線圖,一起來做創(chuàng)意十足的紅旗圖表》
Excel九宮格圖表《秀翻全場!用excel制作一份“九宮格”圖表》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請收好!