回覆列表
-
1 # 精進Excel
-
2 # 精英財務的Excel課根據歷年訂單預測未來銷售量
假定有一組歷年每季度的銷售量資料如下圖，如何預測2019年1季度的銷售量？
Excel的實現方法1、計算a截距。公式：
=Intercept(銷售量資料集，X變數集)第一個引數：銷售量資料集相當於迴歸方程y=a+bx中的y;
第二個引數：序號根相當於迴歸方程y=a+bx中的x ;
本例項中公式如下：
=INTERCEPT(C2:C21,A2:A21)結果=62.94
2、計算b斜率。公式：
=Slope(銷售量資料集，X變數集)引數同Intercept函式。
本例項中公式如下：
=SLOPE(C2:C21,A2:A21)結果=6.29
3、直接計算出預測銷售量。公式：
=Forecast(預測的X變數，銷售量資料集，X變數集）本例項中公式如下：
=FORECAST(A22,C2:C21,A2:A21)直接計算出2019年第1季度的預測銷售量為：195.06。
預測結果1、結果資料2、結果圖形
財務管理中的迴歸分析法是在掌握大量觀察資料的基礎上，利用數理統計方法建立因變數與自變數之間的迴歸關係函式表示式（稱迴歸方程式）。迴歸分析中，當研究的因果關係只涉及因變數和一個自變數時，叫做一元迴歸分析；當研究的因果關係涉及因變數和兩個或兩個以上自變數時，叫做多元迴歸分析。此外，迴歸分析中，又依據描述自變數與因變數之間因果關係的函式表示式是線性的還是非線性的，分為線性迴歸分析和非線性迴歸分析。通常線性迴歸分析法是最基本的分析方法，遇到非線性迴歸問題可以藉助數學手段化為線性迴歸問題處理。
迴歸分析法的公式如下：
y=a+bxa稱為截距，b稱為斜率。a、b的計算方法如下：
b=∑xy－n·∑x∑y/[∑x²－n·(∑x)²]a=∑y－b·∑x/n
通常求解線性迴歸方程，在Excel中使用的方法為規劃求解，今天我從另外兩個思路給你講一講如何求解線性迴歸方式給。
先來看一下什麼是線性迴歸方程：
線性迴歸方程是利用數理統計中的迴歸分析，來確定兩種或兩種以上變數間相互依賴的定量關係的一種統計分析方法之一。001 利用散點圖求線性迴歸方程插入散點圖之後，設定趨勢線，即可得到線性迴歸方程。
步驟如下：
Step1：插入散點圖
選擇資料，【插入】選項卡下，圖表區，選擇散點圖：
Step2：設定佈局，新增【系列趨勢線（含公式）】
002 公式法求解線性迴歸方程如圖所示的X和Y的數值，我們來用函式求解線性迴歸方程。
01 函式LINEST和INTSRCEPT
1) LINEST函式計算斜率：
LINEST函式 使用最小二乘法對已知資料進行最佳直線擬合，並返回描述此直線的陣列，函式的語法為：
LINEST(known_y"s,known_x"s,const,stats)【Known_y"s】：必需。 關係表示式 y = mx + b 中已知的 y 值集合；
【Known_x"s】：可選。 關係表示式 y = mx + b 中已知的 x 值集合。
【Const】：可選。 一個邏輯值，用於指定是否將常量 b 強制設為 0。
如果 const 為 TRUE 或省略，b 將按正常計算。
如果 const 為 FALSE，b 將被設為 0，並同時調整 m 值使 y = mx。
【Stat】：可選。 一個邏輯值，用於指定是否返回附加回歸統計值。
如果 stats 為 TRUE，則 LINEST 函式返回附加回歸統計值，這時返回的陣列為 {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。
如果 stats 為 FALSE 或省略，則函式 LINEST 只返回係數 m 和常量 b。
2）INTTRCEPT計算直線與 y 軸交叉點：
INTERCEPT是一個函式，指函式圖形與座標交點到原點的距離，分為X-intercept（函式圖形與X軸交點到原點的距離）和Y-intercept（函式圖形與Y軸交點到原點距離）【Known_y"s】：必需。 因變的觀察值或資料的集合。
【Known_x"s】：必需。 自變的觀察值或資料的集合。
3）函式TREND計算Y值
如果不需要得到方程，只需要得到結果的話，這個函式是很便捷的。
【Known_y"s】：必需。 關係表示式 y = mx + b 中已知的 y 值集合；
【Known_x"s】：可選。 關係表示式 y = mx + b 中已知的 x 值集合。
【New_x"s】：必需。 需要函式 TREND 返回對應 y 值的新 x 值。
【Const】：可選。 一個邏輯值，用於指定是否將常量 b 強制設為 0。
如果 const 為 TRUE 或省略，b 將按正常計算。
如果 const 為 FALSE，b 將被設為 0，並同時調整 m 值使 y = mx。