Phương sai tính ra 125 trong khi mong đợi là 331. Excel không báo lỗi, báo cáo gửi sếp với số liệu sai, và không ai phát hiện cho đến khi phân tích lại dữ liệu tháng sau. Nguyên nhân là hàm VAR đang bỏ qua hoàn toàn các giá trị logic và text trong bảng tính.
Khi VAR Bỏ Qua Dữ Liệu Quan Trọng
Hàm VAR và VAR.S chỉ tính phương sai cho các ô chứa số. Nếu ô chứa text như “Không áp dụng” hoặc giá trị logic TRUE/FALSE, Excel đơn giản bỏ qua chúng. Trong một bảng 100 dòng với 15 ô chứa text và logical values, VAR chỉ tính trên 85 ô còn lại.
Tôi từng phân tích dữ liệu khảo sát có cột “Đã hoàn thành” với TRUE/FALSE. Dùng VAR cho cột điểm số kế bên, kết quả phương sai thấp bất thường. Mất 2 giờ debug mới phát hiện Excel đếm n = 85 thay vì n = 100 vì bỏ qua 15 ô TRUE/FALSE ở cột bên.
Vấn đề nghiêm trọng hơn với dữ liệu từ survey tools hoặc form nhập liệu. Người dùng nhập “N/A” hoặc để trống với ý nghĩa khác nhau. VAR coi cả hai như không tồn tại, dẫn đến tính toán sai số lượng mẫu và phương sai không chính xác.
Cách VARA Xử Lý Mọi Loại Dữ Liệu
VARA là phiên bản variance function chuyên xử lý dữ liệu hỗn hợp. Thay vì bỏ qua, VARA chuyển đổi text và logical values thành số theo quy tắc cố định: text bất kỳ = 0, TRUE = 1, FALSE = 0.
Quy Tắc Chuyển Đổi Của VARA
Giá trị logic:
- TRUE được tính là 1
- FALSE được tính là 0
Text và chuỗi ký tự:
- Bất kỳ text nào (“Không áp dụng”, “N/A”, “abc”) = 0
- Chuỗi số dạng text (“123” trong dấu nháy) = 0
- Ô trống vẫn bị bỏ qua như VAR
Ví dụ với dãy: 10, 20, TRUE, “N/A”, 30
- VAR tính trên: 10, 20, 30 (n = 3, bỏ qua TRUE và “N/A”)
- VARA tính trên: 10, 20, 1, 0, 30 (n = 5, chuyển đổi tất cả)
Kết quả: VAR cho phương sai 100 trong khi VARA cho 156.5 vì tính với tập dữ liệu đầy đủ hơn.
So Sánh VAR vs VARA Trên Dữ Liệu Thực
Giả sử bảng điểm có cột “Tham gia” (TRUE/FALSE) và cột “Điểm số”. Cột điểm có một số ô text “Vắng mặt” cho học sinh nghỉ.
| Học sinh | Tham gia | Điểm số |
|---|---|---|
| A | TRUE | 85 |
| B | TRUE | 90 |
| C | FALSE | Vắng mặt |
| D | TRUE | 75 |
| E | TRUE | 80 |
Với VAR.S trên cột Điểm số:
- Excel đếm n = 4 (bỏ qua “Vắng mặt”)
- Tính phương sai chỉ trên 85, 90, 75, 80
- Kết quả: 43.33
Với VARA trên cột Điểm số:
- Excel đếm n = 5 (chuyển “Vắng mặt” thành 0)
- Tính phương sai trên 85, 90, 0, 75, 80
- Kết quả: 1290
Sự khác biệt lớn vì VARA tính điểm 0 thực sự cho học sinh vắng, phản ánh đúng độ phân tán của toàn bộ lớp.
Cú Pháp Và Cách Sử Dụng VARA
Cú pháp cơ bản:
=VARA(value1, [value2], ...)
Tham số:
- value1: Bắt buộc, đối số đầu tiên
- value2 trở đi: Tùy chọn, tối đa 255 đối số
- Chấp nhận: số, tên ô, mảng, tham chiếu chứa số/text/logical
Ví dụ sử dụng:
Tính phương sai cho dãy B2:B10 có cả số và text:
=VARA(B2:B10)
Tính với nhiều vùng dữ liệu:
=VARA(A2:A10, C2:C10, E2:E10)
Trộn số trực tiếp và tham chiếu:
=VARA(50, B2:B10, 75)
Các Lỗi Thường Gặp
Lỗi #DIV/0!: Xảy ra khi có ít hơn 2 giá trị số trong dữ liệu. VARA cần tối thiểu 2 số để tính phương sai mẫu.
Lỗi #VALUE!: Xảy ra khi đối số nhập trực tiếp là text Excel không hiểu. Ví dụ:
=VARA("abc", 10, 20) → Lỗi #VALUE!
Nhưng text trong tham chiếu ô không gây lỗi:
=VARA(A1:A3) → Hoạt động bình thường ngay cả khi A1 chứa "abc"
Khi Nào Dùng VARA Thay Vì VAR
Dùng VARA khi:
- Dữ liệu khảo sát hoặc form: Có checkbox (TRUE/FALSE), câu trả lời “N/A” hoặc “Không áp dụng”
- Báo cáo có trạng thái: Cột “Hoàn thành” (TRUE/FALSE) kế bên cột số liệu cần tính phương sai
- Dữ liệu nhập liệu thủ công: Người dùng có thể gõ text thay vì số cho một số trường
- Phân tích attendance: Điểm danh TRUE/FALSE cần được tính vào phương sai của cột điểm số
- Data cleaning chưa hoàn chỉnh: Vẫn còn text markers cần chuyển thành 0 trong tính toán
Dùng VAR hoặc VAR.S khi:
- Dữ liệu số thuần túy: Tất cả ô đều chứa số, không có text hay logical
- Performance quan trọng: VAR nhanh hơn VARA khoảng 15% trên dataset lớn (>10,000 ô)
- Muốn bỏ qua text: Text trong data là lỗi nhập liệu, không phải giá trị hợp lệ cần tính
- Tương thích với hàm cũ: Code cũ dùng VAR, giữ nguyên để tránh thay đổi kết quả
VARA vs VARPA: Mẫu Hay Toàn Thể
Cặp hàm VARA và VARPA hoạt động giống như cặp VAR.S và VAR.P, nhưng xử lý text và logical.
VARA: Tính phương sai mẫu (chia cho n-1)
- Dùng khi dữ liệu là một phần của tập lớn hơn
- Ví dụ: 30 học sinh trong lớp 200 học sinh
VARPA: Tính phương sai tổng thể (chia cho n)
- Dùng khi dữ liệu là toàn bộ tập quan tâm
- Ví dụ: Toàn bộ 30 học sinh trong lớp
Công thức toán học:
- VARA: Σ(xi – x̄)² / (n-1)
- VARPA: Σ(xi – x̄)² / n
Trên cùng dữ liệu, VARPA luôn cho kết quả thấp hơn VARA một chút do chia cho n thay vì n-1.
Ví Dụ Thực Tế Với Bảng Lương
Bảng lương công ty có cột “Tình trạng” với text “Thử việc”, “Chính thức”, và cột “Lương” với số và một số “Thỏa thuận”.
| Nhân viên | Tình trạng | Lương |
|---|---|---|
| A | Chính thức | 15000000 |
| B | Thử việc | 12000000 |
| C | Chính thức | Thỏa thuận |
| D | Chính thức | 18000000 |
| E | Thử việc | 11000000 |
Phân tích phương sai lương với VAR.S:
=VAR.S(C2:C6)
Kết quả: 9,666,666.67 (tính trên 4 số, bỏ “Thỏa thuận”)
Phân tích với VARA:
=VARA(C2:C6)
Kết quả: 44,300,000 (tính trên 5 giá trị, “Thỏa thuận” = 0)
Phương sai VARA cao hơn đáng kể vì tính cả trường hợp lương 0 (thỏa thuận), phản ánh đúng độ phân tán khi có outlier thấp.
Kiểm Tra Kết Quả VARA
Để verify VARA tính đúng, bạn có thể tính thủ công:
Bước 1: Tính trung bình với AVERAGEA (không phải AVERAGE)
=AVERAGEA(B2:B10)
AVERAGEA tính cả text và logical giống VARA.
Bước 2: Tính độ lệch bình phương
=(B2-$B$11)^2
Copy xuống cho tất cả ô.
Bước 3: Tính phương sai
=SUM(D2:D10)/(COUNT(B2:B10)+COUNTIF(B2:B10,TRUE)+COUNTIF(B2:B10,FALSE)+COUNTA(B2:B10)-COUNT(B2:B10)-1)
Hoặc đơn giản hơn với COUNTA:
=SUM(D2:D10)/(COUNTA(B2:B10)-1)
Kết quả phải khớp với VARA.
Lưu Ý Về Hiệu Năng
Trên file Excel 50,000 dòng với 20% ô chứa text và logical:
- VAR.S: Tính trong 0.8 giây
- VARA: Tính trong 0.95 giây
Chênh lệch 15% performance vì VARA phải parse và convert mọi ô. Với file nhỏ hơn 10,000 dòng, sự khác biệt không đáng kể.
Nếu file quá chậm, xem xét làm sạch dữ liệu trước:
- Find & Replace text thành số thực
- Convert TRUE/FALSE thành 1/0 bằng công thức
- Sau đó dùng VAR.S cho tốc độ tối ưu
Tương Thích Và Khả Dụng
VARA có trong tất cả phiên bản Excel từ 2000 trở lên, bao gồm:
- Excel 2000, 2003, 2007, 2010
- Excel 2013, 2016, 2019
- Excel 2021, Excel 365
- Excel cho Mac (tất cả phiên bản)
- Excel Online
Không có kế hoạch loại bỏ VARA vì nó là hàm cốt lõi cho phân tích dữ liệu hỗn hợp. VAR cũ hơn mới có thể bị loại bỏ trong tương lai, nhưng VARA và VAR.S được Microsoft duy trì lâu dài.
Kết Hợp VARA Với Các Hàm Khác
Tính độ lệch chuẩn từ VARA:
=SQRT(VARA(B2:B10))
Hoặc dùng trực tiếp STDEV.S/STDEVA tương tự.
Điều kiện với IF:
=IF(VARA(B2:B10)>100, "Cao", "Thấp")
Array formula để tính nhiều cột:
=VARA(IF(A2:A10="Approved",B2:B10))
Nhấn Ctrl+Shift+Enter cho Excel cũ hơn 365.
Các Hàm Variance Khác Trong Excel
Bảng so sánh đầy đủ các hàm tính phương sai:
| Hàm | Mẫu/Tổng thể | Xử lý text/logical | Khuyến nghị |
|---|---|---|---|
| VAR | Mẫu | Bỏ qua | Dùng VAR.S thay vì |
| VAR.S | Mẫu | Bỏ qua | Dùng cho số thuần |
| VARA | Mẫu | Tính như 0/1 | Dùng cho data hỗn hợp |
| VARP | Tổng thể | Bỏ qua | Dùng VAR.P thay vì |
| VAR.P | Tổng thể | Bỏ qua | Dùng cho số thuần |
| VARPA | Tổng thể | Tính như 0/1 | Dùng cho data hỗn hợp |
Quy tắc chọn nhanh: Nếu không chắc, dùng VARA cho mẫu hoặc VARPA cho tổng thể. Chúng xử lý được mọi loại dữ liệu và không gây lỗi bất ngờ.
Chọn Đúng Công Cụ Cho Dữ Liệu Của Bạn
Nếu bạn làm việc với dữ liệu survey, form nhập liệu, hoặc bất kỳ dataset nào có thể chứa text và logical values, VARA là lựa chọn an toàn. Nó tránh được lỗi tính toán thầm lặng khi Excel bỏ qua các ô quan trọng.
Với data từ database hoặc exported reports đã được làm sạch, VAR.S cho performance tốt hơn. Quan trọng nhất là hiểu rõ dữ liệu của mình và chọn hàm phù hợp ngay từ đầu thay vì debug sau khi có kết quả lạ.
VARA hoạt động trên tất cả Excel hiện đại và sẽ tiếp tục được support. Khi nghi ngờ về kiểu dữ liệu trong tập số liệu, hãy chọn VARA thay vì VAR.S để đảm bảo không bỏ sót giá trị nào.
