Cách sử dụng hàm COVARIANCE.S trong Excel như thế nào?

Hiệp phương sai là gì?

Như chúng ta đã học trong hàm COVERAINCE.P , phép đo mối quan hệ giữa hai biến ngẫu nhiên được gọi là hiệp phương sai. Như tên gợi ý, hiệp phương sai của hai biến cho chúng ta biết một biến thay đổi như thế nào khi biến khác thay đổi. Hiệp phương sai không xác định sự phụ thuộc của một biến vào một biến khác. Có hai loại tính toán hiệp phương sai. Đầu tiên là hiệp phương sai của dân số và thứ hai là hiệp phương sai của mẫu. Trong bài viết này, chúng ta sẽ tìm hiểu cách tính hiệp phương sai của mẫu trong excel.

Giá trị hiệp phương sai có thể là giá trị âm hoặc dương. Một giá trị tiêu cực có nghĩa là cả hai biến di chuyển theo hướng ngược lại. Và, bạn đã đoán đúng, một hiệp phương sai dương có nghĩa là hai biến di chuyển theo cùng một hướng.

Nghe có vẻ giống như một hệ số tương quan nhưng nó lại khác. Chúng tôi sẽ nói về nó cuối cùng.

Làm thế nào để tìm hiệp phương sai của mẫu trong Excel?

Excel cung cấp COVARIANCE.S để tính toán hiệp phương sai của dữ liệu mẫu một cách dễ dàng. Nó được giới thiệu trong Excel 2010 và kể từ phiên bản đó, nó đang được sử dụng rộng rãi. Tôi đang sử dụng nó trong Excel 2016. Có một phiên bản khác của phương pháp này có tên là COVARIANCE.P , được sử dụng để tính toán hiệp phương sai của tổng thể.

Cú pháp của COVARIANCE.S:

Array1:  Các giá trị của biến đầu tiên.

Array2:  Các giá trị của biến thứ hai.

Lưu ý:  Các mảng này có thể được sắp xếp theo bất kỳ thứ tự nào. Độ dài của hai mảng phải giống nhau. Nếu hai mảng có hai độ dài khác nhau thì excel sẽ hiển thị lỗi #N/A.

Bây giờ chúng ta đã biết về hiệp phương sai, hãy lấy một ví dụ để làm cho mọi thứ rõ ràng hơn.

Ví dụ: Tính hiệp phương sai của tổng thể trong Excel

Ở đây tôi có một bộ dữ liệu mẫu. Trong phạm vi A2:A7, tôi có một biến X và trong phạm vi B2:B7, một biến Y khác. Bây giờ, chúng ta hãy tính hiệp phương sai của dữ liệu này và xem hai biến này ảnh hưởng lẫn nhau như thế nào.

Hãy sử dụng hàm COVARAINCE.S của Excel:

Điều này trả về giá trị là 6,533333333.

Giải thích hiệp phương sai

Giá trị hiệp phương sai mà chúng tôi nhận được là một giá trị dương. Nó nói rằng X và Y di chuyển theo cùng một hướng. Nói cách khác, Y sẽ tăng nếu X tăng và ngược lại. Nếu hiệp phương sai là một giá trị âm thì điều ngược lại sẽ đúng.

COVARIANCE.S được tính như thế nào?

Chà, công thức toán học để tính hiệp phương sai của mẫu như sau:

Ở đây X _i  là bất kỳ giá trị nào trong biến X trong đó X bar là giá trị trung bình mẫu của biến X.

Y _i là bất kỳ giá trị nào trong biến Y trong đó Y bar là giá trị trung bình mẫu của biến Y.

n là số quan sát. Chúng tôi trừ 1 từ mẫu số. Nó an toàn vì đây chỉ là dữ liệu mẫu và chúng tôi lưu ý rằng dữ liệu của toàn bộ dân số đã được thu thập. Đây là lý do tại sao nó luôn lớn hơn hiệp phương sai của dân số.

Nếu bạn cố gắng tính hiệp phương sai của mẫu trong excel theo cách thủ công, đây là cách bạn sẽ thực hiện.

1. Đầu tiên, tính giá trị trung bình cộng của các biến X và Y trong ô. Bạn có thể sử dụng hàm AVERAGE.
   

   = AVERAGE (A4:A9)

2. Lấy mỗi giá trị của X trừ đi giá trị trung bình của X. Làm tương tự với Y.
   

   =A4-$A$13

3. Bây giờ nhiều phạm vi X-mean X và Y-mean Y. Xem hình trên.
   

   =D4*C4

4. Bây giờ, tính tổng các giá trị thu được bằng phép nhân.
   

   =SUM(E4:E9)

5. Cuối cùng, chia tổng thu được với một số quan sát. Trong trường hợp của chúng tôi, nó là 6.
   

   =E10/( COUNT (A4:A9)-1)

Con số chúng tôi nhận được là 6.533333333 giống hệt như những gì chúng tôi nhận được từ hàm COVARIANCE.S của Excel.