Hàm COVARIANCE.P Trong Excel: 5 Bước Tính Hiệp Phương Sai Chính Xác

Phân tích mối quan hệ giữa hai tập dữ liệu trong Excel có thể phức tạp nếu không biết công cụ phù hợp. Nhiều người vẫn thử so sánh từng cặp số liệu thủ công hoặc dùng các phép tính trung bình đơn giản. Hàm COVARIANCE.P giải quyết vấn đề này bằng cách tính hiệp phương sai của toàn bộ tập dữ liệu chỉ trong một công thức.

Mục Lục

Bước 1: Hiểu Cú Pháp Cơ Bản

Hàm COVARIANCE.P có cú pháp đơn giản nhưng quan trọng phải nắm chính xác. Công thức cơ bản là:

=COVARIANCE.P(array1, array2)

Trong đó:

array1: Dải ô chứa tập dữ liệu đầu tiên (ví dụ: A2:A10)
array2: Dải ô chứa tập dữ liệu thứ hai (ví dụ: B2:B10)

Hàm này xuất hiện từ Excel 2010 trở đi, thay thế hàm COVAR cũ. Nếu làm việc với Excel 2007 hoặc cũ hơn, hàm này không khả dụng. Chữ P trong tên hàm viết tắt của Population, nghĩa là hàm tính hiệp phương sai cho toàn bộ dân số dữ liệu, không phải mẫu.

Bước 2: Chuẩn Bị Dữ Liệu Đúng Cách

Trước khi nhập công thức, dữ liệu cần đáp ứng ba điều kiện bắt buộc. Nếu vi phạm bất kỳ điều nào, Excel sẽ trả về lỗi.

XEM THÊM: Cách Sử Dụng Hàm NORM.DIST Để Tính Xác Suất Trong Excel Nhanh Và Chính Xác

Điều kiện 1: Số lượng điểm dữ liệu phải bằng nhau

Nếu array1 có 10 hàng và array2 có 8 hàng, hàm trả về lỗi #N/A. Kiểm tra bằng cách đếm số hàng của mỗi dải ô trước khi tính.

Điều kiện 2: Không có mảng trống

Một trong hai mảng hoặc cả hai đều trống sẽ gây lỗi #DIV/0!. Dữ liệu bắt buộc phải là số thực, không để trống.

Điều kiện 3: Dữ liệu phải là số

Hàm tự động bỏ qua các ô chứa văn bản, giá trị logic, hoặc ô trống giữa dữ liệu. Tuy nhiên ô chứa số 0 vẫn được tính. Nếu muốn kiểm soát chính xác, loại bỏ các ô không phải số trước khi tính.

Bước 3: Nhập Công Thức Chính Xác

Chọn ô muốn hiển thị kết quả, sau đó nhập công thức theo mẫu cụ thể. Giả sử dữ liệu doanh thu nằm trong A2:A11 và dữ liệu chi phí quảng cáo nằm trong B2:B11.

Công thức:

=COVARIANCE.P(A2:A11, B2:B11)

Nhấn Enter để Excel tính toán. Kết quả xuất hiện dưới dạng số thập phân có thể dương, âm, hoặc gần bằng 0.

Lưu ý về dấu ngăn cách:

Một số phiên bản Excel tiếng Việt yêu cầu dấu chấm phẩy thay vì dấu phẩy:

=COVARIANCE.P(A2:A11; B2:B11)

Nếu công thức báo lỗi #NAME?, thử thay dấu phẩy bằng dấu chấm phẩy.

Bước 4: Đọc Kết Quả Hiệp Phương Sai

Kết quả hiệp phương sai cho biết mối quan hệ tuyến tính giữa hai tập dữ liệu theo ba trường hợp.

XEM THÊM: Hàm VAR.S Trong Excel: Cách Tính Phương Sai Mẫu Chính Xác Trong 3 Bước

Kết quả dương:

Khi hai biến tăng cùng chiều. Ví dụ: doanh thu tăng khi chi phí quảng cáo tăng. Giá trị càng lớn, mối tương quan càng mạnh.

Kết quả âm:

Khi một biến tăng, biến kia giảm. Ví dụ: chi phí sản xuất giảm khi sản lượng tăng do quy mô. Giá trị càng âm, tương quan nghịch càng mạnh.

Kết quả gần 0:

Hai biến không có mối quan hệ tuyến tính rõ ràng. Điều này không có nghĩa chúng hoàn toàn độc lập, chỉ là không tương quan tuyến tính.

Hàm tính dựa trên công thức toán học:

Cov(X,Y) = Σ(x - x̄)(y - ȳ) / n

Trong đó x̄ và ȳ là giá trị trung bình của array1 và array2, n là số lượng điểm dữ liệu.

Bước 5: Xử Lý Lỗi Thường Gặp

Ba lỗi phổ biến nhất khi dùng hàm COVARIANCE.P đều có cách khắc phục cụ thể.

Lỗi #DIV/0!

Xuất hiện khi một hoặc cả hai mảng trống. Kiểm tra lại dải ô đã chọn có chứa dữ liệu hay không. Nếu có ô trống giữa dữ liệu, hàm vẫn chạy được vì nó tự động bỏ qua.

Lỗi #N/A

Xảy ra khi số lượng điểm dữ liệu trong hai mảng không khớp. Đếm lại số hàng của mỗi dải. Nếu array1 có 12 hàng nhưng array2 chỉ có 10 hàng, thêm dữ liệu vào array2 hoặc thu hẹp array1.

Lỗi #NAME?

Thường do viết sai tên hàm hoặc dùng sai dấu ngăn cách. Kiểm tra chính tả: phải là COVARIANCE.P, không phải COVARIANCE_P hoặc COVARIANCEP. Thử đổi dấu phẩy thành dấu chấm phẩy nếu dùng Excel tiếng Việt.

XEM THÊM: 5 Bước Sử Dụng Hàm T.DIST.RT Thay Thế Bảng Tra Giá Trị Tới Hạn

Khắc phục lỗi tự động:

Dùng hàm IFERROR để ẩn lỗi và hiển thị thông báo tùy chỉnh:

=IFERROR(COVARIANCE.P(A2:A11, B2:B11), "Kiểm tra dữ liệu")

COVARIANCE.P Hay COVARIANCE.S?

Chọn hàm phụ thuộc vào loại dữ liệu đang phân tích. COVARIANCE.P dùng cho toàn bộ tập dữ liệu có sẵn, ví dụ tất cả giao dịch trong quý hoặc toàn bộ học sinh trong lớp. COVARIANCE.S dùng khi dữ liệu chỉ là mẫu đại diện cho tập lớn hơn, ví dụ khảo sát 100 khách hàng từ 10,000 khách hàng.

Công thức COVARIANCE.S chia cho (n-1) thay vì n để điều chỉnh độ lệch mẫu. Với tập dữ liệu nhỏ dưới 30 điểm, sự khác biệt giữa hai hàm đáng kể. Với tập lớn trên 100 điểm, kết quả gần như tương đương.

Kết Quả Sau Khi Áp Dụng

Hàm COVARIANCE.P tính toán hiệp phương sai tức thì thay vì phải tính thủ công qua nhiều bước. Phân tích mối quan hệ giữa biến số chỉ cần một công thức đơn giản. Hàm này khả dụng từ Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, và Microsoft 365. Đối với phân tích thống kê nâng cao hơn, kết hợp với hàm CORREL để tính hệ số tương quan Pearson hoặc dùng công cụ Data Analysis có sẵn trong Excel.