【進階】使用Excel進行回歸分析,預測真實值

2021-02-13 CDA數據分析師

作者 Airy

本文為轉自AiryData，轉載需授權

昨天學習了 Excel 中的相關分析，在數據分析中，相關分析和回歸分析關係緊密，今天來學習下 Excel 中的回歸分析。

回歸分析（regressionanalysis)是確定兩種或兩種以上變量間相互依賴的定量關係的一種統計分析方法。

我們在得到兩組數據之間的相關程度之後，就可以使用回歸分析進行預測了，換言之，相關分析是回歸分析的基礎和前提，回歸分析是相關分析的深入和繼續。但只有當數據之間存在高度相關時，進行回歸分析尋求相關的具體形式才有意義。

在我們的日常數據分析過程中，回歸分析是應用十分廣泛的一種數據分析方法，該方法主要用於分析單個因變量是如何受到一個或多個自變量影響的。如分析某個產品的銷售情況與產品質量、價格、促銷活動、天氣等因素之間的關係。根據已知的一組數據，我們就可以知道這幾個因素對銷售額的影響，然後對同類產品的銷售額進行預測。


回歸分析的分類如下圖1所示：

　　

實例應用：使用多元線性回歸分析預測銷售額

1、實例描述

某品牌汽車經銷商的經理了解到投放廣告對於汽車銷售額增長具有很大的作用，但是他並不明確在電視臺投放廣告與在各個視頻網站投放廣告哪種方式對增加汽車銷售額更有效。在2017年1月，若在電視臺和視頻網站分別投入的廣告費為20萬和30萬，那麼應估算汽車的銷售額為多少萬元？針對這種情況，經理手機本公司去年各月的汽車銷售額數據及每月在以上兩種媒介上投入的廣告費用數據，如下圖2所示。

2、實例分析

簡單根據上面的數據，我們並不能確定這兩種廣告投放渠道哪種更有效，所以，這裡我們使用Excel中的回歸分析方法，先檢驗這兩組數據與銷售額的相關性程度，隨後再根據回歸分析過程中所得到的線性回歸方程預測確定廣告費時的銷售額。

3、操作分析

使用Excel進行多元線性回歸分析的因變量是銷售額，自變量是兩種渠道的廣告費，具體步驟如下：

第1步：選擇回歸分析工具並設置參數。打開「數據分析」對話框，選擇「回歸」分析工具，單擊「確定」，如圖3所示，彈出「回歸」對話框，設置「Y值輸入區域」為「$D$2：$D$14」，「X值輸入區域」為「$B$2：$C$14」；勾選「標誌」「置信度」複選框，並設置置信度為「95%」；單擊「輸出區域」，並設置該區域為「$F$1」；單擊確定即可。如圖4所示。

第2步：顯示回歸分析結果。此時返回工作表，就能得到詳細的各項參數值。如圖5所示。

操作解析：

回歸分析的計算結果一共包括三個模塊：

（1）第一個模塊為回歸統計表，其中主要包含MultipleR、RSquare、AdjustedRSquare、標準誤差和觀測值。MultipleR為復相關係數，也就是前面說的相關係數，用來衡量x和y之間的相關程度大小，RSquare為複測定係數R2，其用來說明自變量解釋因變量變差的程度，從而測量同因變量y的擬合效果，AdjustedRSquare為調整後的複測定係數R2，標準誤差衡量擬合程度大小，值越小，說明擬合程度越好，觀測值指的是用於估計回歸方程數據的觀測值個數。

（2）第二個模塊為方差分析表。其主要作用是通過假設檢驗中的F-檢驗來判斷回歸模型的回歸效果。

（3）第三個模塊是回歸參數表。第一列表示截距，第二列表示對應模型的回歸係數，包括了截距和斜率，可以根據這個建立回歸模型。第三列為回歸係數的標準誤差，值越小，表明參數的精確度越高，第四列對應的是統計量t值，用於檢驗模型參數。第五列為各個回歸係數的P值，當P<0.05時，可以認為模型在α=0.05的水平上顯著，或置信度達到了95%。最後幾列為回歸係數置信區間的上限和下限。

4、決策分析

上面的結果中可以看到，R值為0.9863，表示廣告支出費與銷售額之間的關係為高度正相關，複測定係數為0.9727，表明用自變量可解釋因變量變差的97.27%，AdjustedRSquare為0.9666，說明自變量能說明因變量的96.66%，因變量剩餘的3.33%則由其他因素來解釋。

回歸參數表中，回歸方程的截距和兩個斜率分別為-316.29，9.13，51.06。又因為P值小於0.05，說明了這兩個自變量對汽車銷售額均有顯著影響，但是，兩個斜率中，視頻網站對應的回歸係數更大，說明在視頻網站上投放廣告更有效。

由此可得該回歸分析的線性回歸方程為：y=-316.29+9.13x1+51.06x2。

預測一下，當電視廣告費和視頻廣告費分別為20萬和30萬時，汽車銷售額的預測值為：y=-316.29+9.13*20+51.06*30=1398.11（萬）。

上面就是今天的主題內容了，今天學習一下 Excel 中如何使用相關分析進行預測，這對數據分析很有幫助，可以看到不同渠道投放的廣告對我們銷售額的影響大小。希望通過上面的操作能幫助大家。如果你有什麼好的意見，建議，或者有不同的看法，我都希望你留言和我們進行交流、討論。

北京空氣品質數據可視化

幾個提高工作效率的Python內置小工具

Python 自然語言處理《釜山行》人物關係

函數 | 這8組Excel函數，幫您解決工作中80%的難題

國外公司是如何挖掘社交媒體數據的？

大數據輿情情感分析，如何提取情感並使用什麼樣的工具？（貼情感標籤）

【乾貨】Pandas速查手冊中文

四步搭建企業服務數據分析體系

【進階】代碼這樣寫不止於優雅( Python 版)

【乾貨】找不到適合自己的編程書？我自己動手寫了一個熱門編程書搜索網站（附PDF書單）

玩轉Excel動態圖表，必須學會的3個套路

相關焦點

  • 像Excel一樣使用R進行數據分析(3)
    Excel是數據分析中最常用的工具，本篇文章通過R與excel的功能對比介紹如何使用R通過函數式編程完成excel中的數據處理及分析工作。
  • 線性回歸 - 用Excel預測 (6)
    SLOPE（數組1，數值2）=所述的回歸線的斜率INTERCEPT（數組1，數值2）=如上所述的回歸線的y截距Excel 2016引入了一個新函數FORECAST.LINEAR，它等效於FORECAST的線性回歸簡單的線性回歸，可以直接用excel自帶函數進行預測，但通常，我們的數據都是有季節性，趨勢性的，所以，這個時候我們需要同時考慮季節和趨勢
  • Logistic(邏輯)回歸分析及癌症分類預測案例
    Logistic(邏輯)回歸分析一、邏輯回歸數學背景1、邏輯回歸（Logistic回歸）分析概要如果現在想對某件事情發生的概率進行預估，比如一個非醫用口罩，在疫情期間是否有人願意買？這裡的Y變量是「是否願意購買」，屬於分類數據，所以不能使用回歸分析。如果Y為類別性(定性)數據，研究影響關係，正確做法是選擇Logistic回歸分析。
  • Excel裡的這個工具,做財務分析一定要學會
    有過編制預算經驗的朋友，應該會很熟悉，在預測技術裡，回歸分析應用非常廣泛，是用來進行定量數據分析的方法。線性回歸分析是一種統計方法，用於確定某個變量（或一組變量）對另一個變量的影響。
  • 如何使用excel做比對試驗結果的回歸分析
    如何使用excel做一元線性回歸分析
  • 線性回歸-如何對數據進行回歸分析
    通常，我們的模型是通過多個特徵值來預測一個目標值，那麼線性回歸模型的數學公式為：其中：線性回歸模型與數學中的解方程不同，後者的結果是精確解，而前者則是一個近似解。因此在公式中存在一個 ε 。最小二乘法的主要思想是讓真實值與預測值之差（即誤差）的平方和達到最小。用公式表示如下：
  • excel數據分析技巧:預測未來,為圖表添加趨勢線
    對於exceler而言，預測數據走勢，如銷量趨勢預測、股指走勢預測等是日常處理、分析數據工作中的一部分。面對這類問題，我們可以使用Excel圖表中的趨勢線對數據未來走勢進行預測，幫助我們更加直觀地了解數據變化的趨勢。下面我們就和春風一起來學習excel中的趨勢線吧！學習更多技巧，請收藏部落窩教育excel圖文教程。
  • 如何用Excel進行預測分析?
    請使用Excel進行分析。【分析思路】第1日(次日)留存用戶數=第1日新增用戶數*次日留存率第2日活躍用戶數=第2日新增用戶數+第1日留存用戶數第3日活躍用戶數=第3日新增用戶數+第2日留存用戶數（第2日新增用戶數*第2日留存率）+第1日留存用戶數...
  • 機器學習:回歸分析——多元線性回歸分析
    生活中的現象常常是與多個因素相聯繫的，由多個自變量的最優組合共同來預測或估計因變量，比只用一個自變量進行預測或估計更有效，更符合實際。所以相比一元線性回歸，多元線性回歸的實際意義更大。本節我們將使用來自UIC數據集中的能效數據集（ENB2012_data.xlsx）進行演示，探索如何使用Python對數據集進行多元回歸分析。
  • 用Excel做一元線性回歸預測分析
    回歸分析法是根據事物的因果關係對應的變量的預測方法，是定量預測方法的一種。例如，下表是1-6月每月銷量數據，根據這個數據預測7-12月銷量數據。預測方法：一、運用數據分析工具操作步驟如下：step1:根據原始數據製作折線圖，如下：
  • 線性回歸原理及其Excel實現方式 誰都能學會 調查問卷分析 問卷調查資料
    二是如何使用excel軟體實現回歸分析，對於沒有學過專門的統計軟體的朋友來說，用自己熟悉的excel實現簡單的回歸分析是不錯的選擇。「回歸分析」是解析「自變量」和「因變量」並明確兩者關係的統計方法。此時，我們把因子變量稱為「說明變量」，把自變量稱為「目標變量址(被說明變量)」。
  • 如何用EXCEL線性回歸分析法快速做數據分析預測
    回歸分析法，即二元一次線性回歸分析預測法先以一個小故事開始本文的介紹。十三多年前，筆者就職於深圳F集團時，曾就做年度庫存預測報告，與筆者新入職一臺籍高管Edwin分別按不同的方法模擬預測下一個年度公司總存貨庫存。令我吃驚的是，本人以完整的數據推算做依據，做出的報告結果居然與僅入職數周，數據不齊全的Edwin制定的報告結果吻合度達到99%以上。仍清楚記得，筆者曾用得是標準的周轉天數計算公式反推法，而Edwin用的正是本文重點介紹的二元一次線性回歸分析法。
  • 使用spss進行多元回歸分析
    在大多數的實際問題中，影響因變量的因素不是一個而是多個，我們稱這類回問題為多元回歸分析。
  • python回歸分析總結--回歸模型及調優
    回歸分析及模型優化1、回歸分析概括目標值（因變量）是連續型數據，通過某種函數關係找到因變量和自變量之間的關係，進而預測目標。通過不斷擬合縮小預測值與真實值的差距：最終使得這個差距（誤差項）成為一組均值為0，方差為1的隨機數。
  • excel數據分析技巧：預測未來，為圖表添加趨勢線
    對於exceler而言，預測數據走勢，如銷量趨勢預測、股指走勢預測等是日常處理、分析數據工作中的一部分。面對這類問題，我們可以使用Excel圖表中的趨勢線對數據未來走勢進行預測，幫助我們更加直觀地了解數據變化的趨勢。下面我們就和春風一起來學習excel中的趨勢線吧！
  • 從零開始學Python數據分析【21】--線性回歸(實戰部分)
    關於如何構建並求解多元線性回歸模型的理論部分我們已經在《從零開始學Python數據分析【20】--線性回歸（理論部分）》中做了詳細的梳理，包括模型的偏回歸係數的計算、模型的顯著性檢驗和偏回歸係數的檢驗。如果你對理論部分還不是很明白的，建議你先看一下我之前寫的文章。
  • 藉助Excel的統計學電路分析進行良率分析
    僅僅使電路設計符合規範目標是遠遠不夠的，精確地預測一定範圍條件下電路的表現也很重要，包括各組件的真實值變化。此過程通常稱為電路良率分析。一旦較好地了解了這種表現，設計人員便可以有效地利用預計的製造容差來選擇電路和組件進行設計。　　眾所周知，正常運行的電路，即使電路參數發生變化，其構建、測試和支持成本也相對較低。
  • 用 Scikit-learn 與 Pandas 進行線性回歸預測
    ，這裡給出一個完整的例子，詳細學完這個例子，對用scikit-learn來運行線性回歸，評估模型不會有什麼問題了。:) 這裡我們用UCI大學公開的機器學習數據來跑線性回歸。下載後的數據可以發現是一個壓縮文件，解壓後可以看到裡面有一個xlsx文件，我們先用excel把它打開，接著"另存為""csv格式，保存下來，後面我們就用這個csv來運行線性回歸。打開這個csv可以發現數據已經整理好，沒有非法數據，因此不需要做預處理。但是這些數據並沒有歸一化，也就是轉化為均值0，方差1的格式。
  • 像Excel一樣使用R進行數據分析(1)
    Excel是數據分析中最常用的工具，本篇文章通過R與excel的功能對比介紹如何使用R通過函數式編程完成excel中的數據處理及分析工作。
  • Excel中的預測建模–如何從頭開始創建線性回歸模型
    Microsoft Excel使我們能夠創建預測模型，而不必編寫複雜的代碼，這些代碼會飛過大多數人的腦海。我們可以輕鬆地在MS Excel中建立一個簡單的模型，例如線性回歸，可以幫助我們通過幾個簡單的步驟進行分析。而且，我們不需要成為Excel或Statistics的高手就能執行預測建模！目錄什麼是線性回歸？