內容
相關性分析是一種常用的研究方法，用於確定第一個值對第二個值的依賴程度。 電子表格有一個特殊工具，可讓您實施此類研究。
相關分析的本質
有必要確定兩個不同數量之間的關係。 換句話說，它揭示了值在哪個方向（更小/更大）根據秒的變化而變化。
相關分析的目的
當相關係數的識別開始時，依賴性就建立起來了。 該方法與回歸分析不同，因為只有一個指標使用相關性計算。 間隔從 +1 變為 -1。 如果它是正的，那麼第一個值的增加有助於第二個值的增加。 如果為負，則第一個值的增加會導致第二個值的減少。 係數越高，一個值對第二個值的影響越強。
重要！ 在第 0 個係數處，數量之間沒有關係。
相關係數的計算
讓我們分析幾個樣本的計算。 例如，有表格數據，其中廣告促銷支出和銷售量按月份在單獨的列中描述。 根據表格，我們將找出銷售量對廣告促銷費用的依賴程度。
方法 1：通過函數嚮導確定相關性
CORREL – 一個允許您執行相關分析的函數。 一般形式—— CORREL(massiv1;massiv2)。 詳細說明：
- 有必要選擇計劃顯示計算結果的單元格。 單擊位於文本字段左側的“插入函數”以輸入公式。
- 函數嚮導打開。 在這裡你需要找到 CORREL，單擊它，然後單擊“確定”。
- 參數窗口打開。 在“Array1”行中，您必須輸入第一個值的間隔坐標。 在此示例中，這是“銷售價值”列。 您只需要選擇此列中的所有單元格。 同樣，您需要將第二列的坐標添加到“Array1”行。 在我們的示例中，這是廣告費用列。
- 輸入所有範圍後，單擊“確定”按鈕。
係數顯示在我們行動開始時指示的單元格中。 得到的結果是 0,97。 該指標反映了第一個值對第二個值的高度依賴。
方法 2：使用分析工具庫計算相關性
還有另一種確定相關性的方法。 這裡使用了分析包中的功能之一。 在使用它之前，您需要激活該工具。 詳細說明：
- 轉到“文件”部分。
- 將打開一個新窗口，您需要在其中單擊“設置”部分。
- 點擊“附加組件”。
- 我們在底部找到元素“管理”。 在這裡，您需要從上下文菜單中選擇“Excel加載項”，然後單擊“確定”。
- 一個特殊的附加組件窗口已打開。 在“分析包”元素旁邊放置一個複選標記。 我們點擊“確定”。
- 激活成功。 現在讓我們轉到數據。 出現“分析”塊，您需要在其中單擊“數據分析”。
- 在出現的新窗口中，選擇“相關”元素並單擊“確定”。
- 分析設置窗口出現在屏幕上。 在“輸入區間”行中，必須輸入參與分析的所有列的範圍。 在此示例中，這些是“銷售價值”和“廣告成本”列。 輸出顯示設置最初設置為新工作表，這意味著結果將顯示在不同的工作表上。 或者，您可以更改結果的輸出位置。 完成所有設置後，單擊“確定”。
最終成績出來了。 結果與第一種方法相同 - 0,97。
MS Excel中多重相關係數的定義與計算
為了確定幾個量的相關程度，使用了多個係數。 將來，結果將匯總在一個單獨的表中，稱為相關矩陣。
詳細指南：
- 在“數據”部分，我們找到已知的“分析”塊，然後單擊“數據分析”。
- 在出現的窗口中，單擊“相關”元素，然後單擊“確定”。
- 在“輸入間隔”行中，我們驅動源表的三個或更多列的間隔。 範圍可以手動輸入或簡單地用 LMB 選擇它，它會自動出現在所需的行中。 在“分組”中選擇適當的分組方法。 在“輸出參數”中指定顯示關聯結果的位置。 我們點擊“確定”。
- 準備好！ 建立了相關矩陣。
Excel中的對相關係數
讓我們弄清楚如何在 Excel 電子表格中正確繪製對相關係數。
Excel中對相關係數的計算
例如，您有 x 和 y 值。
X 是因變量，y 是自變量。 需要找到這些指標之間關係的方向和強度。 分步說明：
- 讓我們使用函數找到平均值 心。
- 讓我們計算每個 х и 平均, у и 平均 使用«-»運算符。
- 我們將計算出的差異相乘。
- 我們計算該列中指標的總和。 分子是找到的結果。
- 計算差異的分母 х и x 平均值，y и y-中. 為此，我們將執行平方。
- 使用功能 自動總結，在結果列中找到指標。 我們做乘法。 使用功能 根 平方結果。
- 我們使用分母和分子的值來計算商。
- CORREL 是一個集成功能，可讓您防止複雜的計算。 我們進入“函數嚮導”，選擇 CORREL 並指定指標數組 х и у. 我們構建了一個圖表來顯示獲得的值。
Excel中的成對相關係數矩陣
下面我們來分析如何計算成對矩陣的係數。 例如，有一個由四個變量組成的矩陣。
分步說明：
- 我們轉到位於“數據”選項卡的“分析”塊中的“數據分析”。 從出現的列表中選擇相關性。
- 我們設置了所有必要的設置。 “輸入間隔”——所有四列的間隔。 “輸出間隔”——我們要顯示總數的地方。 我們點擊“確定”按鈕。
- 在選定的地方建立了一個相關矩陣。 行和列的每個交集是一個相關係數。 坐標匹配時顯示數字 1。
在 Excel 中確定關係和相關性的 CORREL 函數
CORREL – 用於計算 2 個數組之間的相關係數的函數。 讓我們看一下這個函數所有能力的四個例子。
在 Excel 中使用 CORREL 函數的示例
第一個例子。 有一個盤子，上面有關於公司員工在 XNUMX 年內的平均工資和美元匯率的信息。 有必要確定這兩個量之間的關係。 該表如下所示：
計算算法如下所示：
顯示的分數接近 1。 結果：
動作對結果影響的相關係數的確定
第二個例子。 兩名投標人向兩個不同的機構尋求幫助，進行為期 XNUMX 天的促銷活動。 每天都會進行一次社會民意調查，以確定每個申請人的支持程度。 任何被採訪者都可以選擇兩個申請人之一或全部反對。 有必要確定每個廣告促銷對申請人的支持程度有多大影響，哪家公司更有效率。
使用下面的公式，我們計算相關係數：
- =相關（A3：A17；B3：B17）。
- =相關（A3：A17；C3：C17）。
結果：
從得到的結果可以看出，隨著廣告宣傳的每一天，對第一個申請人的支持程度都在增加，因此，相關係數接近於1。當廣告投放時，另一個申請人有大量的信任，並且對於1天有一個積極的趨勢。 然後信任度下降，到了第十五天，它跌破了最初的指標。 低分錶明晉升對支持產生了負面影響。 不要忘記，未以表格形式考慮的其他伴隨因素也可能影響指標。
通過視頻觀看和轉發的相關性分析內容流行度
第三個例子。 在 YouTube 視頻託管上宣傳自己的視頻的人使用社交網絡來宣傳該頻道。 他注意到社交網絡中的轉發數量與頻道上的觀看次數之間存在某種關係。 是否可以使用電子表格工具預測未來的表現？ 有必要確定應用線性回歸方程根據轉發次數預測視頻觀看次數的合理性。 帶有值的表：
現在有必要根據以下公式確定2個指標之間是否存在關係：
0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"強正相關";"強逆關係");"弱關係或無關係")' class='公式'>
如果得到的係數高於 0,7，則更適合使用線性回歸函數。 在這個例子中，我們這樣做：
現在我們正在構建一個圖表：
我們應用這個等式來確定 200、500 和 1000 股的觀看次數： =9,2937*D4-206,12。 我們得到以下結果：
功能 預測 允許您確定當前的觀看次數，例如，如果有 XNUMX 次轉發。 我們申請： 0,7;PREDICTION(D7;B3:B8;A3:A8);"數值不相關")' class='公式'>. 我們得到以下結果：
Excel中使用CORREL函數的特點
該功能具有以下特點：
- 不考慮空單元格。
- 不考慮包含布爾和文本類型信息的單元格。
- 雙重否定“-”用於以數字的形式說明邏輯值。
- 研究數組中的單元格數量必須匹配，否則將顯示 #N/A 消息。
評估相關係數的統計顯著性
在檢驗相關係數的顯著性時，原假設是該指標的值為 0，而備擇假設不是。 以下公式用於驗證：
結論
電子表格中的相關性分析是一個簡單且自動化的過程。 要執行它，您只需要知道必要工具的位置以及如何通過程序設置激活它們。