Lỗi Floating Point Trong Excel: Tại Sao 0.1+0.2≠0.3 Và Cách Khắc Phục

Bạn đã bao giờ gặp trường hợp công thức =A1=B1 trả về FALSE, dù hai ô trông hoàn toàn giống nhau? Hay SUM cho ra kết quả lệch vài phần triệu so với giá trị kỳ vọng? Đó không phải lỗi của bạn — đó là lỗi Floating Point. Bài viết này giải thích tại sao Excel tính sai, và hướng dẫn cách khắc phục đơn giản.

1. Floating Point Error Là Gì? #

1.1 Hệ thập phân vs Hệ nhị phân #

Chúng ta dùng hệ thập phân (base-10) hàng ngày: 10 chữ số từ 0 đến 9. Nhưng máy tính — bao gồm cả Excel — lưu trữ số bằng hệ nhị phân (binary, base-2) với chỉ 2 chữ số: 0 và 1.

Vấn đề nằm ở đây: nhiều số thập phân "tròn trịa" KHÔNG THỂ biểu diễn chính xác trong hệ nhị phân.

• 0.1 trong binary = 0.0001100110011... (lặp vô hạn)

• 0.2 trong binary = 0.0011001100110011... (lặp vô hạn)

• Giống như 1/3 = 0.3333... lặp vô hạn trong hệ thập phân

Excel lưu trữ số theo chuẩn IEEE 754 (double-precision floating-point), cho phép tối đa 15 chữ số có nghĩa. Khi một số không thể biểu diễn chính xác, Excel lưu giá trị xấp xỉ — và sai số cực nhỏ này chính là "floating-point error".

1.2 Tại sao 0.1 + 0.2 = 0.3 trong Excel? #

Câu hỏi hay! Thực ra, nếu bạn gõ =0.1+0.2 trong Excel, kết quả hiển thị là 0.3 và =0.1+0.2=0.3 trả về TRUE. Điều này xảy ra vì Excel có một cơ chế "thông minh" tự động làm tròn kết quả khi sai số đủ nhỏ (khoảng 15 chữ số có nghĩa).

Tuy nhiên, floating-point error VẪN xảy ra trong nhiều tình huống khác — đặc biệt khi có phép trừ, cộng lặp, hoặc so sánh kết quả tính toán.

2. Bẫy Số 1: Number Format Gây Hiểu Lầm #

Trước khi nói về floating-point error thật sự, cần phân biệt với lỗi hiểu lầm do Number Format:

2.1 Ví dụ: Discount 10% #

Cột C chứa giá gốc, cột D tính chiết khấu 10%:

=C5*10%

Cột F chứa giá trị chiết khấu nhập tay. Công thức kiểm tra:

=D5=F5   // Trả về FALSE!

Tại sao? Vì cột D được format hiển thị 2 chữ số thập phân, nhưng giá trị thực có thể là 3.775 (trong khi F5 = 3.78). Number Format KHÔNG thay đổi giá trị thực — chỉ thay đổi hiển thị.

2.2 Cách kiểm tra #

• Chọn ô → nhìn thanh Formula Bar để thấy giá trị thực

• Hoặc nhấn Ctrl + Shift + ~ để tạm chuyển sang format General

• Hoặc Ctrl + 1 → Number → đặt 16 chữ số thập phân

2.3 Cách fix #

// Cách 1: Round khi so sánh
=ROUND(D5,2) = F5

// Cách 2: Round ngay khi tính
=ROUND(C5*10%, 2)

Lưu ý: Đây KHÔNG phải floating-point error — đây là lỗi hiểu nhầm do Number Format. Nhưng triệu chứng giống nhau: hai số trông giống mà so sánh ra FALSE.

3. Bẫy Số 2: Phép Trừ Gây Sai Số #

Đây mới là floating-point error thật sự. Xem ví dụ:

// Cột B và C chứa số "tròn" 2 chữ số thập phân
// Cột D = B5 - C5
// Cột E = giá trị kỳ vọng (nhập tay)
// Cột F kiểm tra: =D5=E5 → FALSE !?

Khi format cột D với 16 chữ số thập phân, bạn sẽ thấy:

• 19.58 - 14.39 = 5.1899999999999995 (thay vì 5.19)

• 15.33 - 9.27 = 6.0600000000000005 (thay vì 6.06)

• Sai số ở chữ số thứ 16 — cực nhỏ, nhưng đủ để = trả FALSE

3.1 Cách fix: ROUND #

// Round kết quả trước khi so sánh
=ROUND(D5, 2) = F5   // TRUE

3.2 Cách fix: Tolerance-based (ABS) #

Thay vì so sánh bằng, kiểm tra "gần bằng" trong ngưỡng cho phép:

=ABS(D5 - F5) < 0.0000000001

Ưu điểm: không cần round, giữ nguyên precision. Bạn tự chọn tolerance phù hợp (ở đây: 10^-10).

4. Bẫy Số 3: Cộng Lặp Tích Lũy Sai Số #

Floating-point error nghiêm trọng nhất khi bạn cộng lặp cùng 1 giá trị nhiều lần:

// SEQUENCE tạo dãy từ -1.9, bước nhảy 0.1
=SEQUENCE(20, , -1.9, 0.1)

Kỳ vọng: đến hàng thứ 20, giá trị sẽ là -1.9 + 19×0.1 = 0. Nhưng thực tế, Excel cho ra 1.000000000000001 hoặc -0.999999999999999 ở một số hàng. Tại sao?

• 0.1 không phải số chính xác trong binary

• Mỗi lần cộng 0.1, sai số tích lũy thêm một chút

• Sau 10-20 phép cộng, sai số đủ lớn để nhìn thấy

4.1 Cách fix #

// Bọc ROUND quanh SEQUENCE
=ROUND(SEQUENCE(20, , -1.9, 0.1), 1)

Round theo precision bạn cần: bước nhảy 0.1 → round 1 chữ số. Bước nhảy 0.01 → round 2 chữ số.

5. Ảnh Hưởng Thực Tế Trong Công Việc #

5.1 Kế toán / Tài chính #

• Đối chiếu số liệu giữa hệ thống ERP và Excel khớp → nhưng so sánh bằng công thức ra FALSE

• Bảng lương: tổng NET SALARY lệch 0.01 so với kỳ vọng → khó giải thích cho kiểm toán

• Tính thuế: tròn sai 1 đồng có thể gây sai cả chuỗi tính toán

5.2 Data Analysis #

• VLOOKUP / INDEX MATCH không tìm thấy giá trị do floating-point

• COUNTIF đếm thiếu vì giá trị "trông giống" nhưng "khác" ở chữ số thứ 16

• Pivot Table gộp nhầm hoặc tách nhầm nhóm

5.3 Conditional Formatting #

• Rule =A1=0 không highlight ô chứa -2.77556E-17 (gần 0 nhưng không đúng 0)

• Fix: =ABS(A1)<0.0001 thay vì =A1=0

6. Tổng Hợp Cách Khắc Phục #

6.1 Dùng ROUND (khuyến nghị) #

// Round kết quả tính toán theo precision cần
=ROUND(A1*B1, 2)

// Round khi so sánh
=ROUND(A1, 2) = ROUND(B1, 2)

Nguyên tắc: round càng muộn càng tốt — chỉ round ở bước cuối hoặc khi so sánh. Round quá sớm gây mất precision tích lũy.

6.2 Dùng ABS + Tolerance #

=ABS(A1 - B1) < 0.0001

Không cần round, giữ nguyên precision. Phù hợp khi cần so sánh số lớn hoặc cần linh hoạt tolerance.

6.3 Các hàm Round khác trong Excel #

• ROUND(number, num_digits) — Làm tròn đến n chữ số

• ROUNDUP(number, num_digits) — Luôn làm tròn lên

• ROUNDDOWN(number, num_digits) — Luôn làm tròn xuống

• MROUND(number, multiple) — Làm tròn đến bội số gần nhất

• CEILING(number, significance) — Làm tròn lên đến bội số

• FLOOR(number, significance) — Làm tròn xuống đến bội số

• INT(number) — Lấy phần nguyên (round xuống)

• TRUNC(number, num_digits) — Cắt bỏ phần thập phân (không round)

6.4 "Set Precision as Displayed" — Dùng cẩn thận #

File → Options → Advanced → "Set precision as displayed" sẽ ép Excel lưu giá trị đúng như hiển thị:

• Ưu điểm: fix tất cả floating-point error ngay lập tức

• Nhược điểm nghiêm trọng: thay đổi VĨNH VIỄN giá trị gốc — không thể undo

• Áp dụng toàn workbook — ảnh hưởng tất cả sheet, kể cả sheet không liên quan

• Chỉ dùng khi bạn THẬT SỰ hiểu hậu quả và đã backup file

7. Best Practices Tránh Floating-Point Error #

• Round muộn, không round sớm — Tính toán chính xác trước, chỉ round ở bước cuối cùng hoặc khi hiển thị kết quả

• Không so sánh trực tiếp số thực — Thay =A1=B1 bằng =ROUND(A1,2)=ROUND(B1,2) hoặc =ABS(A1-B1)<threshold

• Cẩn thận với phép cộng lặp — Dùng SEQUENCE + ROUND thay vì cộng dồn trong nhiều ô

• Kiểm tra bằng Format 16 decimal — Khi kết quả "lạ", format 16 chữ số thập phân để thấy sai số ẩn

• Dùng ROUND cho tiền tệ — Luôn round 2 chữ số cho VND/USD để tránh lệch 0.01

• Document precision requirement — Ghi chú trong sheet rằng bạn round ở đâu, tại sao

Câu Hỏi Thường Gặp (FAQ) #

Floating-point error có phải bug của Excel không? #

Không. Đây là giới hạn của TẤT CẢ hệ thống máy tính dùng IEEE 754 — bao gồm Python, JavaScript, Java, C, Google Sheets. Không phải lỗi riêng của Excel.

Lỗi này có ảnh hưởng đến số nguyên không? #

Không. Số nguyên (integer) được biểu diễn chính xác trong binary. Lỗi chỉ xảy ra với số thập phân (decimal). Tuy nhiên, nếu số nguyên rất lớn (> 15 chữ số), Excel sẽ mất precision ở các chữ số cuối.

Google Sheets có bị lỗi này không? #

Có. Google Sheets cũng dùng IEEE 754, vậy nên gặp cùng vấn đề. Cách fix tương tự: dùng ROUND hoặc so sánh bằng ABS tolerance.

Tôi nên round bao nhiêu chữ số? #

Phụ thuộc vào ngữ cảnh. Tiền tệ: 2 chữ số (VND round 0). Tỷ lệ phần trăm: 4-6 chữ số. Dữ liệu khoa học: tùy yêu cầu precision. Nguyên tắc: round theo precision của dữ liệu đầu vào.

Tại sao mentioning "15 chữ số có nghĩa"? #

IEEE 754 double-precision lưu 52 bit cho phần mantissa, tương đương khoảng 15.95 chữ số thập phân có nghĩa. Bất kỳ chữ số nào sau vị trí thứ 15 đều có thể bị sai do floating-point representation.

Kết Luận #

Floating-point error là "kẻ phá hoại thầm lặng" trong Excel — bạn không thấy nó cho đến khi công thức trả kết quả sai. Hiểu nguyên nhân (IEEE 754 binary representation) giúp bạn không hoang mang khi gặp.

Giải pháp đơn giản nhất: dùng ROUND hoặc ABS tolerance khi so sánh số. Nhớ nguyên tắc: "round muộn, không round sớm" — và bạn sẽ không bao giờ bị floating-point error làm khó nữa. 🔢