Excel數據分析兩大利器,趨勢預測與規劃求解

數據分析工具千千萬，怎麼又能少得了Excel~

01

預測工作表

預測工作表是Excel2016版添加的一個新功能，預測使用基於現有時間的數據和指數平滑（ETS）算法的AAA版本來預測未來值。

下圖中，天貓雙十一從10年至今，銷售額從9億狂奔至19年的2684億（20年雙十一官方發布11.1日至11.11日累計銷售額，當天未公布）

這裡我們用10年至18年的銷售數據來預測19年與20年雙十一當天的銷售額情況。

預測工作表位於【數據】功能區下部（Excel2016及其以上版本，沒有安裝包的小夥伴後臺私我）

選中表格，點擊預測工作表，界面如下所示：

預測開始從2018年以後，預測2年的數據（19&20年），置信區間95%(默認),代表實際值95%會落在預測區間內，其它為默認值。

預測結果如下圖所示：

最上面的橘色線：叫做置信上限，即未來趨勢的上限不超過此線；

最下面的橘色線：叫做置信下限，即未來趨勢的下限不超過此線；

上下兩根橘色線之間：叫做置信區間，即未來趨勢在此區間中波動；

中間加粗的橘色線：叫做趨勢線，即未來趨勢最有可能沿此線的趨勢發展。

圖中預測2019年雙十一銷售額區間在2379億至2823億，最有可能銷售2602億元，我們看下2019年實際銷售額2684億剛好在此區間內，與趨勢預測值誤差在3%左右，完全可以接受。

從圖中預測可以看出，2020年雙十一銷售額大概在2611億元至3527億元之間。

02

規劃求解

《孫子算經》中記載著一道有趣的題目：

「今有雉兔同籠，上有三十五頭，下有九十四足，問雉兔各幾何？」

意思是說：有若干只雞兔同在一個籠子裡，從上面數，有35個頭，從下面數，有94隻腳。問籠中各有多少只雞和兔？

在現在來看，典型的一元二次方程組嘛！

一隻雞有一個頭兩隻腳，一隻兔有一個頭四隻腳，設雞有x只，兔子有y只，可得以下方程組：

今天我們用Excel中給的規劃求解來解答這道題。

Step1 整理已知數據

題目的條件反應在Excel中如下圖所示，通過更改B2&B3單元格的數字（雞兔的量）來使B6&B7單元格成立（這兩單元格皆有公式組成，與B2&B3進行關聯）

Step2 調出規劃求解界面

「規劃求解」功能在數據功能區，但一般默認未展示，需要我們手動調出。

右鍵功能區任意空白位置，選擇【自定義功能區】—【加載項】—【Excel加載項】—勾選【規劃求解加載項】—【確定】。

回到主界面，點擊【數據】，選擇【規劃求解】。

界面講解

規劃求解的界面如下圖所示，首先需要設定一個目標，這個目標一定得是個帶公式的單元格，這裡選擇B6或者B7都是可以的，目標值有三個選項，最大值、最小值與目標值，我們這裡選擇目標值。

如果設置目標中輸入B6，那麼目標值就是35，如果設置目標中輸入B7，那麼目標值就是94。

可變單元格就是B2&B3。

Step3 輸入參數

這裡將B6單元格設置為目標值（35個頭），另外一個已知條件設置到約束條件，同時雞和兔的數量應該為整數，也設置為約束條件。

點擊確認按鈕，可以發現，Excel規劃求解已經求解完畢，正確的數字被輸入到B2&B3單元格內，雞23隻，兔12隻。

趨勢預測和規劃求解是Excel當中比較實用的兩個數據分析小工具，分享給大家，覺得有用的小夥伴記得關注我，每天分享數據小技巧。