3 Lỗi Thường Gặp Khi Dùng Hàm COVARIANCE.S Và Cách Khắc Phục Trong 30 Giây

Hàm COVARIANCE.S trong Excel thường báo lỗi ngay cả khi công thức trông đúng hoàn toàn. Tôi từng mất 20 phút debug một formula đơn giản chỉ vì không để ý hai dải dữ liệu có độ dài khác nhau. Ba lỗi này xuất hiện liên tục và may mắn là chúng fix được trong vài giây nếu biết đúng nguyên nhân.

Mục Lục

Lỗi #N/A vì độ dài dữ liệu không khớp

Đây là lỗi phổ biến nhất với COVARIANCE.S. Excel trả về #N/A khi hai mảng dữ liệu có số lượng điểm khác nhau. Hàm yêu cầu array1 và array2 phải có đúng cùng độ dài để tính hiệp phương sai.

Nguyên nhân thực tế:

Thường xảy ra khi sao chép dữ liệu từ nguồn khác nhau. Doanh số có 12 tháng nhưng chi phí quảng cáo chỉ có 10 tháng. Hoặc một trong hai cột có ô trống ở cuối mà không để ý.

Cách kiểm tra nhanh:

Sử dụng hàm COUNT để đếm số điểm dữ liệu trong mỗi mảng. Nếu =COUNT(A2:A13) trả về 12 nhưng =COUNT(B2:B13) trả về 10, đây chính là nguyên nhân.

Khắc phục:

Chọn cả hai cột dữ liệu
Nhấn Ctrl + G để mở Go To
Click Special > Blanks để highlight ô trống
Delete hoặc fill missing data

XEM THÊM: Hàm COUNTIF: 7 Tình Huống Thực Tế Dân Văn Phòng Cần Biết

Hoặc điều chỉnh range trong công thức để match exactly. Nếu dữ liệu thực tế chỉ có trong A2:A11 và B2:B11, dùng =COVARIANCE.S(A2:A11,B2:B11) thay vì range lớn hơn.

Lỗi này thường mất 30 giây fix nhưng có thể waste 15 phút nếu không biết check độ dài trước.

Lỗi #DIV/0! khi mảng rỗng hoặc có 1 điểm dữ liệu

COVARIANCE.S báo #DIV/0! trong hai trường hợp: một trong hai mảng hoàn toàn trống, hoặc mỗi mảng chỉ có đúng 1 điểm dữ liệu. Công thức tính hiệp phương sai mẫu chia cho n-1, khi n=1 thì mẫu số bằng 0.

Tình huống thực tế:

Xảy ra nhiều khi filter dữ liệu. Bạn filter theo tháng 1 nhưng chỉ có 1 record, COVARIANCE.S ngay lập tức báo #DIV/0!. Hoặc khi reference đến range động mà đang không có data nào.

Kiểm tra:

Công thức =COUNT(A2:A10) nếu trả về 0 hoặc 1 thì chắc chắn sẽ gặp lỗi này. Cần tối thiểu 2 điểm dữ liệu trong mỗi mảng.

Giải pháp:

Wrap công thức với IFERROR để handle gracefully:

=IFERROR(COVARIANCE.S(A2:A10,B2:B10),”Insufficient data”)

Hoặc kiểm tra trước khi tính:

=IF(COUNT(A2:A10)>=2,COVARIANCE.S(A2:A10,B2:B10),”Need 2+ data points”)

Với filtered data, tốt nhất là dùng SUBTOTAL để đếm visible cells trước khi apply COVARIANCE.S. Nếu SUBTOTAL(3,A2:A10)<2 thì skip calculation.

Trong dashboard reports của tôi, việc thêm error handling này ngăn được 90% trường hợp hiển thị lỗi cho user.

Kết quả sai vì nhầm COVARIANCE.S với COVARIANCE.P

Đây không phải lỗi Excel báo mà là lỗi logic nghiêm trọng. COVARIANCE.S dùng cho mẫu (chia n-1), COVARIANCE.P dùng cho tổng thể (chia n). Dùng sai hàm cho kết quả sai 10-20% với dataset nhỏ.

XEM THÊM: Cách Tìm Giá Trị Xuất Hiện Nhiều Nhất Trong Excel Với MODE.SNGL

Phân biệt khi nào dùng:

COVARIANCE.S: Khi dữ liệu là mẫu từ tập lớn hơn. Ví dụ phân tích 100 khách hàng từ database 10,000 khách. Hoặc doanh số 6 tháng đầu năm để dự đoán cả năm.

COVARIANCE.P: Khi có toàn bộ dữ liệu. Ví dụ tất cả 50 states của Mỹ, hoặc toàn bộ 12 tháng đã kết thúc không dự đoán thêm.

Case study thực tế:

Phân tích tương quan giữa chi phí marketing và doanh số cho 8 tuần đầu của campaign dài 52 tuần. Đây là mẫu, phải dùng COVARIANCE.S. Nếu dùng COVARIANCE.P, kết quả sẽ underestimate độ biến động thực tế.

Với n=8, COVARIANCE.S chia cho 7 còn COVARIANCE.P chia cho 8. Difference là 14% – đủ lớn để ảnh hưởng decision.

Cách fix:

Review lại context của dataset. Hỏi: “Đây có phải toàn bộ dữ liệu có thể có không?” Nếu không, dùng COVARIANCE.S. Trong 95% trường hợp phân tích business, answer là không.

Quick check: Nếu đang dùng công thức có chứa COVARIANCE.P mà dataset dưới 30 điểm, rất có thể đang dùng sai. Switch sang COVARIANCE.S và so sánh results.

Lỗi thứ tư: Ô chứa text không được ignore

Mặc dù Microsoft document nói COVARIANCE.S tự động ignore text, logical values và empty cells, thực tế có edge cases gây vấn đề. Nếu cell chứa số được format as text (text-formatted numbers), Excel đôi khi không ignore mà throw error.

Nhận biết:

Numbers aligned left trong cell thay vì right là dấu hiệu text formatting. Hoặc có green triangle ở góc cell. Khi này COVARIANCE.S có thể trả về #VALUE! thay vì tự động skip.

XEM THÊM: 3 Bước Làm Chủ Hàm NORM.S.INV Trong Phân Tích Thống Kê Excel

Fix nhanh:

Select toàn bộ data range
Data tab > Text to Columns
Click Finish (không cần change settings)

Thao tác này convert text-numbers thành proper numbers trong 5 giây. Alternative: Multiply by 1 với paste special.

Cách khác là dùng VALUE function wrap around: =COVARIANCE.S(VALUE(A2:A10),VALUE(B2:B10)) nhưng đây là array formula cần Ctrl+Shift+Enter trong Excel cũ.

Verification checklist trước khi troubleshoot

Trước khi dig deep vào lỗi COVARIANCE.S, chạy nhanh 4 checks này:

=COUNT(array1) và =COUNT(array2) có bằng nhau và >=2 không
Cả hai ranges có data format đúng (numbers không phải text)
Dataset của bạn là mẫu hay tổng thể (S vs P)
Có ô nào bị hidden bởi filter đang active không

Tôi tạo một validation sheet riêng với 4 checks này. Mỗi khi formula báo lỗi, paste data vào validation sheet và chạy checks. 8/10 lần tìm được issue trong 30 giây.

Kết quả sau khi biết 3 lỗi chính

Ba lỗi này chiếm 95% problems với COVARIANCE.S. Lỗi #N/A fix bằng cách match độ dài arrays. Lỗi #DIV/0! fix bằng IFERROR wrapper hoặc pre-check COUNT. Lỗi logic S vs P fix bằng cách hiểu context dataset.

COVARIANCE.S có từ Excel 2010 trở đi, replacing hàm COVAR cũ. Nếu đang dùng Excel 365, consider combining với FILTER function để automatically exclude problematic rows trước khi calculate covariance. Công thức =COVARIANCE.S(FILTER(A:A,A:A<>””),FILTER(B:B,B:B<>””)) tự động loại bỏ blanks và reduce errors.