進階—使用Excel進行相關分析

每日乾貨好文分享丨請點擊+關注

tstoutiao，邀請你進入頭條資料愛好者交流群，資料愛好者們都在這兒。

前言

前面我們進行資料分析時，我們已經通過描述統計找到了資料的內在規律，

但是在實際工作的時候，我們除了分析資料的規律外，最重要的是預測未來的資料。我們為什麼要資料分析，最終的目的都是為了預測，基於現有的歷史資料，來預測未來的發展狀況。但是預測不能只靠想，這時候我們就必須使用資料工具中的相關分析和回歸分析。

相關分析

相關分析（correlationanalysis），相關分析是研究現象之間是否存在某種依存關係，並對具體有依存關係的現象探討其相關方向以及相關程度，

是研究隨機變數之間的相關關係的一種統計方法。

使用相關分析可分析一組資料和另外一組資料之間的關係，即判斷這兩組資料的變化是否相關。

判斷資料相關性

根據不同的分類方式，相關分析可分為下圖所示的類型（這部分屬於統計學相關知識，如果不知道的話可以先看一下統計學哦）。

上面是最常用的相關分析種類，要研究兩組資料間的相關程度，使用相關係數r即可實現資料的相關描述。在Excel中，要計算相關係數，一般有兩種方式：一是利用相關係數函數，二是利用相關分析係數工具。對於第一種方式，Excel提供了兩個計算兩個變數之間相關係數的函數，即CORREL函數和PEARSON函數（皮爾遜積矩法，

也叫皮爾遜積矩相關係數（統計學常用））。由於這兩個函數是等價的，所以我今天只說CORREL函數的用法。

實例應用：使用CORREL函數及相關分析工具判斷相關性

1、實例描述

某軟體公司在全國有很多代理商，為了研究其軟體產品的年銷售額與哪些費用的相關性較大，其公司的資料分析師X在公司的眾多代理商中選擇了15家進行觀測，並搜集了這15家代理商的年銷售額、年廣告費投入額、成本費用、管理費用資料，

然後將這些資料整理在了同一張表中，如下表所示：

2、實例分析

經過對上表資料進行觀察，X發現：隨著年廣告費投入額和成本費用的增加，年銷售額也會相對增加；而管理費用表面上看起來與其他費用的相關性不大，

直接觀察並不能說明這些資料之間的相關程度和方向，所以，X決定使用Excel中的函數和相關係數工具來進行分析。

3、操作分析

為了加深印象，我們今天同時使用相關係數分析工具和CORREL函數計算相關係數。

第1步：插入CORREL函數。選中H1儲存格，然後在“公式”下按一下“插入函數”，彈出“插入函數”對話方塊，在“或選擇類別”中選擇“統計”選項，然後在“選擇函數”清單方塊中按兩下“CORREL函數”。如下圖所示：

第2步：計算相關係數r1。此時彈出“函數參數”對話方塊，設置Array1為B2：B16區域，Array1為C2：C16區域，最後點擊“確定”。如下圖所示：

第3步：計算相關係數r2。應用相同的方法在H2儲存格插入CORREL函數，設置Array1為B2：B16區域，Array1為D2：D16區域，最後點擊“確定”。如下圖所示。然後使用同樣的方法在H3計算r3。

第4步：選擇“相關係數”分析工具。返回工作表，選中表格中的任意儲存格，打開“資料分析”對話方塊，在“分析工具”清單方塊中選擇“相關係數”分析工具，然後“確定”。

第5步：設置相關係數參數。在彈出的“相關係數”對話方塊，設置輸入區域為“$B$1:$E$16”，按一下“分組方式”中的“逐列”按鈕，然後勾選“標誌位於第一行”核取方塊，按一下“輸出區域”選項按鈕，並設置“輸出區域”為“$G$6”，最後確定即可。如下圖所示：

第6步：顯示相關係數的計算結果。返回工作表，即可看到H1:H3中使用CORREL函數計算出的相關係數r值，以及使用“相關係數”分析工具生成的一個矩陣，其中矩陣的資料交叉處，即H8:H10區域，就是本例需要研究的相關係數r值，如下圖所示：

4、決策分析

在上面的操作中，1、2、3使用的是CORREL函數，4、5、6使用的是資料分析工具，可以看到兩種方式得到的3個相關係數r值是相同的，r值一般都介於-1~1之間，r>0為正相關，r

小結

上面就是今天的主題內容了，今天學習一下Excel中如何進行相關分析，這對資料分析很有説明，可以看到影響我們銷售業務的相關因素。希望通過上面的操作能幫助大家。如果你有什麼好的意見，建議，或者有不同的看法，我都希望你留言和我們進行交流、討論。

tstoutiao，邀請您加入頭條資料愛好者交流群，資料愛好者們都在這兒。

原文連結：https://ask.hellobi.com/blog/airy/7209 。

第2步：計算相關係數r1。此時彈出“函數參數”對話方塊，設置Array1為B2：B16區域，Array1為C2：C16區域，最後點擊“確定”。如下圖所示：

第3步：計算相關係數r2。應用相同的方法在H2儲存格插入CORREL函數，設置Array1為B2：B16區域，Array1為D2：D16區域，最後點擊“確定”。如下圖所示。然後使用同樣的方法在H3計算r3。

第4步：選擇“相關係數”分析工具。返回工作表，選中表格中的任意儲存格，打開“資料分析”對話方塊，在“分析工具”清單方塊中選擇“相關係數”分析工具，然後“確定”。

第5步：設置相關係數參數。在彈出的“相關係數”對話方塊，設置輸入區域為“$B$1:$E$16”，按一下“分組方式”中的“逐列”按鈕，然後勾選“標誌位於第一行”核取方塊，按一下“輸出區域”選項按鈕，並設置“輸出區域”為“$G$6”，最後確定即可。如下圖所示：

第6步：顯示相關係數的計算結果。返回工作表，即可看到H1:H3中使用CORREL函數計算出的相關係數r值，以及使用“相關係數”分析工具生成的一個矩陣，其中矩陣的資料交叉處，即H8:H10區域，就是本例需要研究的相關係數r值，如下圖所示：

4、決策分析

在上面的操作中，1、2、3使用的是CORREL函數，4、5、6使用的是資料分析工具，可以看到兩種方式得到的3個相關係數r值是相同的，r值一般都介於-1~1之間，r>0為正相關，r

小結

上面就是今天的主題內容了，今天學習一下Excel中如何進行相關分析，這對資料分析很有説明，可以看到影響我們銷售業務的相關因素。希望通過上面的操作能幫助大家。如果你有什麼好的意見，建議，或者有不同的看法，我都希望你留言和我們進行交流、討論。

tstoutiao，邀請您加入頭條資料愛好者交流群，資料愛好者們都在這兒。

原文連結：https://ask.hellobi.com/blog/airy/7209 。