Bạn có thể sử dụng hàm PEARSON[] để tính hệ số tương quan Pearson trong Excel. Nếu các biến của bạn nằm trong cột A và B, hãy nhấp vào bất kỳ ô trống nào và nhập “PEARSON[A. A,B. b]”
Không có chức năng kiểm tra trực tiếp tầm quan trọng của mối tương quan
Hệ số tương quan còn được gọi là Hệ số tương quan Pearson và nó là phép đo mức độ liên quan của hai biến
Phép tính có thể có giá trị từ 0 đến 1
Giá trị 0 cho biết hai biến có mức độ không liên quan cao và giá trị 1 cho biết chúng có mức độ liên quan cao
Ví dụ: bạn có thể có dữ liệu về chiều cao [mét] và cân nặng [kg] của một mẫu người và muốn biết liệu hai biến này có liên quan với nhau hay không.
Theo trực giác, bạn sẽ nghĩ rằng chiều cao và cân nặng của một người có liên quan với nhau, nhưng hệ số tương quan sẽ cho bạn thấy về mặt toán học mức độ liên quan hoặc không liên quan của những điều này
Công thức hệ số tương quan
Hệ số tương quan r có thể được tính theo công thức trên với x và y là các biến mà bạn muốn kiểm tra mối tương quan
Trong ví dụ này, biến x là chiều cao và biến y là cân nặng. r là tương quan giữa chiều cao và cân nặng
Tính hệ số tương quan từ định nghĩa
Hãy xem cách chúng ta có thể tính toán điều này trong Excel dựa trên định nghĩa trên
Có khá nhiều bước liên quan để tính hệ số tương quan từ đầu
- Tính chiều cao trung bình.
= AVERAGE [ C3:C12 ] - Tính trọng lượng trung bình.
= AVERAGE [ D3:D12 ] - Tính toán sự khác biệt giữa chiều cao và chiều cao trung bình cho từng điểm dữ liệu. Công thức này sẽ cần được sao chép xuống cho mỗi hàng.
= C3 - $C$14 - Tính toán sự khác biệt giữa trọng lượng và trọng lượng trung bình cho từng điểm dữ liệu. Công thức này sẽ cần được sao chép xuống cho mỗi hàng.
= D3 - $D$14 - Tính bình phương của sự khác biệt từ bước 3 cho mỗi hàng.
= POWER [ F3, 2 ] - Tính bình phương của sự khác biệt từ bước 4 cho mỗi hàng.
= POWER [ G3, 2 ] - Tính sản phẩm của sự khác biệt từ bước 3 và 4 cho mỗi hàng.
= F3 * G3 - Tính tổng các bình phương chênh lệch từ bước 5.
= SUM [ H3:H12 ] - Tính tổng các bình phương chênh lệch từ bước 6. ________số 8_______
- Tính tổng của tích các chênh lệch từ bước 7.
= SUM [ J3:J12 ] - Tính hệ số tương quan theo công thức sau.
0= AVERAGE [ D3:D12 ]
Đây là một phép tính phức tạp với rất nhiều bước trung gian
Rất may, Excel có một chức năng tích hợp để lấy mối tương quan giúp việc tính toán trở nên đơn giản hơn nhiều
Chức năng CORREL
Đây là hàm chuyên dùng để tính hệ số tương quan Pearson trong Excel
Nó rất dễ sử dụng. Nó nhận hai phạm vi giá trị làm hai đối số duy nhất
= AVERAGE [ D3:D12 ]- Biến1 và Biến2 là hai biến mà bạn muốn tính Hệ số tương quan Pearson giữa
- Đây là những đầu vào bắt buộc và phải là một dãy số một cột hoặc một hàng. Biến1 và Biến2 cũng phải có cùng thứ nguyên
= AVERAGE [ D3:D12 ]Công thức trên là những gì bạn cần để tính toán mối tương quan giữa chiều cao và cân nặng
Wow, dễ dàng hơn nhiều so với tính toán từ đầu
Phương pháp này cũng năng động. Nếu dữ liệu của bạn thay đổi, phép tính tương quan sẽ cập nhật để phản ánh dữ liệu mới
Công cụ thống kê
Excel đi kèm với một bổ trợ công cụ thống kê mạnh mẽ, nhưng bạn cần kích hoạt nó để sử dụng nó trước và nó khá ẩn
Để kích hoạt ToolPak Phân tích
- Chuyển đến tab Tệp rồi chọn Tùy chọn
- Chuyển đến tab Bổ trợ trong Tùy chọn Excel
- Chọn Phần bổ trợ Excel từ danh sách thả xuống và nhấn nút Bắt đầu
- Kiểm tra tùy chọn ToolPak Phân tích từ các bổ trợ có sẵn
- Nhấn nút OK
Bây giờ bạn sẽ có một lệnh Phân tích Dữ liệu có sẵn trong tab Dữ liệu và bạn có thể nhấp vào lệnh này để mở ToolPak Phân tích
Điều này sẽ mở menu Phân tích dữ liệu và sau đó bạn có thể chọn Tương quan từ các tùy chọn và nhấn nút OK
Thao tác này sẽ mở menu Tương quan Phân tích Dữ liệu
- Cung cấp Phạm vi đầu vào để tính toán tương quan. Đây phải là một phạm vi có các giá trị số được sắp xếp thành các cột hoặc hàng
- Chọn tùy chọn Group By của Columns hoặc Rows. Ví dụ này có dữ liệu được sắp xếp theo các cột vì tất cả các giá trị về chiều cao đều nằm trong một cột và các giá trị về cân nặng nằm trong một cột riêng biệt
- Chọn xem phạm vi đầu vào của bạn có Nhãn ở hàng đầu tiên hay không. Các nhãn này được sử dụng sau này trong đầu ra, vì vậy, tốt nhất bạn nên chọn một dải đầu vào bao gồm các nhãn
- Chọn nơi đặt đầu ra trong Tùy chọn đầu ra. Bạn có thể chọn từ một vị trí trong trang tính hiện tại, một vị trí trong trang tính mới hoặc sổ làm việc mới
- Nhấn nút OK thực hiện phép tính
Điều này sẽ tạo ra một ma trận tương quan
Điều này có nghĩa là nếu bạn có nhiều hơn hai cột biến, ma trận sẽ chứa hệ số tương quan cho tất cả các tổ hợp biến
Hạn chế của phương pháp này là đầu ra là tĩnh. Nếu dữ liệu của bạn thay đổi, bạn sẽ cần chạy lại phân tích dữ liệu để cập nhật ma trận tương quan
kết luận
Tương quan là một thống kê rất hữu ích để xác định xem dữ liệu của bạn có liên quan
Tuy nhiên, công thức toán học có thể đáng sợ, đặc biệt là khi cố gắng tính toán nó trong Excel