Xin chào mọi người.

Đây là Enta.

Bạn đã bao giờ gặp trường hợp đang tổng hợp số lượng trên Excel mà phát hiện ra rằng “khi cộng tất cả lại, kết quả chênh lệch 1 yên so với kết quả tính bằng tay” chưa?

Ngay cả trong việc tính toán chi phí thi công mái dốc, nếu cứ lặp đi lặp lại các công thức như diện tích × đơn giá × thuế tiêu dùng, thì,Ở dòng cuối cùng, chắc chắn sẽ có sự chênh lệch 1 yên hoặc 1 m²Đó là một hiện tượng.

Điều này không phải là lỗi của Excel mà là...IEEE 754 – Tiêu chuẩn toàn cầu về số thập phân độngĐó là lý do đấy w

Hồi xưa tớ từng rất bực mình nên đã tìm hiểu về chuyện này, nên cũng chia sẻ với mọi người luôn w

Dù nói vậy, nhưng ngày nay người ta có còn dùng Excel để tính toán nhiều không nhỉ?

Tôi vẫn là người thích dùng Excel!

Cái này là thứ có thể làm được mọi thứ nhất nên... w

---

Thôi, quay lại chủ đề chính

Lần này,Bản chất của sai số tích lũy do số thập phân gây ra trong Excel (và nhiều phần mềm khác),Thông số kỹ thuật của số thập phân động độ chính xác gấp 754 lần theo tiêu chuẩn IEEETừ đó,

Cách khắc phục đúng bằng hàm ROUNDTôi sẽ chia sẻ kinh nghiệm của mình khi phải thực hiện việc tính toán số lượng một cách bực bội cho đến lúc đó.

Đây là thông tin được nêu rõ trong tài liệu chính thức của Microsoft, nên cơ sở này chắc chắn là 100%.

Bí mật đằng sau những “thói quen” của Excel | Tiêu chuẩn IEEE 754

Nói ngắn gọn thì,Sai số tính toán trong Excel không phải là lỗi phần mềm, mà là kết quả của việc tuân thủ nghiêm ngặt tiêu chuẩn tính toán số thập phân động IEEE 754Đó là.

Chắc bạn đang nghĩ: “Hả?” phải không?

 

Microsoft đã nêu rõ về độ chính xác tính toán của Excel như sau: Microsoft Excel được thiết kế dựa trên tiêu chuẩn IEEE 754, quy định cách lưu trữ và phương pháp tính toán số thập phân động. Excel có thể lưu trữ các giá trị từ 1,79769313486232E308 đến 2,2250738585072E-308, nhưng,Số chữ số có ý nghĩa không quá 15 chữ sốchỉ giới hạn ở đó.

Tài liệu chính thức của Microsoft Learn: “Việc sử dụng phép tính số thập phân động có thể dẫn đến kết quả không chính xác trong Excel”

Nói cách khác, Excel thực hiện bên trongTất cả các giá trị đều được xử lý dưới dạng nhị phânDo đó, khi chuyển đổi số được viết dưới dạng thập phân sang hệ nhị phân thìSai số làm trònsẽ ra đời.

Bản chất của hiện tượng này chính là việc các phép tính này sẽ tích lũy dần theo thời gian.

Tại sao lại xảy ra sai số khi chuyển đổi từ hệ thập phân sang hệ nhị phân?

Một số hữu hạn dưới dạng số thập phân như “0,1”,Trong hệ nhị phân, số này sẽ trở thành một số thập phân lặp lại vô hạnlà nguyên nhân.

Số thập phân	Biểu diễn số nhị phân	Ghi chú
0.5	0.1	Có thể diễn đạt một cách chính xác
0.25	0.01	Có thể diễn đạt một cách chính xác
0.1	0,0001100110011… (lặp lại)	Vòng lặp vô hạn, phát sinh sai số làm tròn
0.2	0,0011001100110… (lặp lại)	Như trên
0.3	0,0100110011001… (lặp lại)	Như trên

Trong tiêu chuẩn IEEE 754 độ chính xác kép (64 bit), vòng lặp vô hạn nàyCắt ngắn ở một chữ số nhất định rồi lưu trữDo đó, sẽ phát sinh một sai số rất nhỏ (khoảng ±2,8 × 10⁻¹⁷) (Microsoft Learn).

Ví dụ cụ thể | Những sai lệch điển hình thường xảy ra trong Excel

Kiến thức cơ bản | Ý nghĩa của TRUE và FALSE

Trước khi đi vào các ví dụ cụ thể, trong Excel, ĐÚNG / SAI Chúng ta sẽ ôn lại nội dung này.

Dù đã muộn, nhưng đây là kiến thức cơ bản nhất trong số các kiến thức cơ bản, nên các bạn nhất định phải ghi nhớ nhé!

Trong Excel “=” hay “>” hay “<” Khi sử dụng, kết quả sẽ là TRUE (Đúng · Bằng nhau / Đúng) か FALSE (Sai, không bằng nhau/không đúng) và sẽ trả lời lại.

Công thức toán học	Ý nghĩa	Kết quả
=1=1	1 và 1 có bằng nhau không?	TRUE
=1=2	1 và 2 có bằng nhau không?	FALSE
=5 > 3	5 lớn hơn 3?	TRUE

Nói cách khác 「=A1=0,3 "và trả về giá trị FALSE" = "Giá trị trong ô A1 không bằng 0,3" Đó là ý nghĩa của câu này. Hãy ghi nhớ điều này khi xem ví dụ dưới đây.

※Do yêu cầu về cách trình bày, các công thức được ghi bằng dấu bằng (=) toàn ký tự. Khi nhập vào Excel, vui lòng chuyển sang dấu bằng “=” nửa ký tự.

Ví dụ 1｜Bí ẩn của phép tính 0,1 + 0,2 ≠ 0,3

Hãy thử nhập nội dung sau vào ô trong Excel

Ô	Công thức toán học	Kết quả hiển thị	Giá trị bên trong
A1	= 0,1 + 0,2	0.3	0,30000000000000004…
A2	=A1=0,3	FALSE	(Nói chính xác thì không hoàn toàn giống nhau)

Mặc dù giá trị hiển thị là “0,3” đúng, nhưng,=A1=0,3 Khi so sánh thì Trở thành FALSE. Đây chính là “tính cách” của tiêu chuẩn IEEE 754 đó www

Thật là bực mình nhỉ w

Ví dụ 2｜Khi cộng thêm 100, kết quả sẽ không còn là số nguyên

=SUM(A1:A100) Khi cộng tổng nhiều ô như vậy, sai số nhỏ trong từng ô sẽXếp chồng lên nhau thành 100 cái：

• Cộng từng số 0,1 × 100 một cách riêng lẻ → Kết quả là 9,99999999999998…
• Giá trị kỳ vọng của 10 có thể không hoàn toàn khớp

Trong việc tổng hợp khối lượng công trình xây dựng Tổng hợp 100 dòng theo công thức “diện tích × đơn giá” Khi thực hiện như vậy, ở dòng cuối cùng sẽ là Sự chênh lệch từ vài yên đến vài chục yên Đó là lý do tại sao điều này xảy ra.

Ví dụ 3 | Tích lũy số thập phân khi tính diện tích mặt dốc (Tình huống thường gặp tại công trường xây dựng mặt dốc)

Các trường hợp cụ thể thường hay xảy ra khi tính toán diện tích công trình gia cố taluy:

• Kỹ thuật khung pháp lý:Sử dụng hàm căn bậc hai trong Excel để tính diện tích, sau đó cộng các diện tích đó lại với nhau
• Ở dòng SUM cuối cùng, so sánh với kết quả tính bằng tay và Sự chênh lệch từ 0,1 đến 0,5 m²

Đây là trường hợp mất cả nửa ngày mà vẫn không tìm ra nguyên nhân.

Tôi còn nhớ hồi xưa mình đã dùng cái này để tính toán đi tính toán lại biết bao lần! w

Giải pháp thay thế | Làm tròn từng lần bằng hàm ROUND

Chỉ “giảm số chữ số thập phân” trong cài đặt định dạng hiển thị là chưa đủ

Cách giảm số chữ số thập phân trong tính năng “Định dạng ô” của Excel là,

Chỉ làm tròn số hiển thị, còn giá trị thực bên trong vẫn còn sai số。

Nếu chuyển sang phép tính tiếp theo, sai số vẫn còn tồn tại, do đó đây không phải là giải pháp triệt để.

Giải pháp thực sự là làm tròn chính giá trị đó trong hàm.

Cách sử dụng hàm ROUND tùy theo từng trường hợp

Hàm	Hoạt động	Mục đích sử dụng
=ROUND(giá trị, số chữ số)	Làm tròn	Mục đích chung・Tính toán số tiền
=ROUNDDOWN(giá trị, số chữ số)	Cắt bỏ	Tính toán dự phòng theo hướng an toàn
=ROUNDUP(giá trị, số chữ số)	Làm tròn lên	Số lượng đặt hàng nguyên vật liệu (để tránh tình trạng thiếu hụt)
=INT(giá trị)	Lấy giá trị nguyên (số âm là hàm sàn)	Nếu muốn giới hạn ở các giá trị nguyên
=FLOOR(giá trị, đơn vị)	Làm tròn xuống theo đơn vị quy định	Làm tròn theo đơn vị 10 yên và 100 yên
=CEILING(giá trị, đơn vị)	Làm tròn lên theo đơn vị quy định	Như trên, để tránh thiếu hụt

Khi lập dự toán hay tính toán sơ bộ, nhất định phải làm như thế này!

Quy trình tính toán và ước tính chi phí công trình mà tôi đang thực hiện như sau:

• Việc tính toán số tiền luôn luôn =ROUND(số lượng * đơn giá * 1,10, 0) và làm tròn theo đơn vị yên mỗi lần
• Tính toán số lượng (diện tích, thể tích) là =ROUND(giá trị, 2) và đến 2 chữ số thập phân Cuộn lại và cố định
• Không tính lại ở dòng cuối cùng của bảng tổng hợp(Tổng đơn giản của các giá trị đã làm tròn trong quá trình tính toán)

Nhờ vậy, bạn có thể lập được bảng tính không chênh lệch dù chỉ 1 yên so với kết quả tính bằng tay.

Cũng có thể sử dụng kết hợp với “Tùy chọn độ chính xác tính toán của Excel”

Trong Excel có Tùy chọn “Tính toán dựa trên số chữ số hiển thị”Cũng có tùy chọn này (Tệp → Tùy chọn → Cài đặt nâng cao → Khi tính toán sổ làm việc này).

Khi bật tính năng này,Tính toán và làm tròn giá trị bên trong theo định dạng hiển thị của ôHọ sẽ làm điều đó. Tuy nhiên,

• Áp dụng cho toàn bộ cuốn sách (không thể áp dụng từng phần)
• Kết quả tính toán hiện tại có khả năng sẽ thay đổi hoàn toàn
• Tốt nhất là không nên sử dụng trong bản dự toán được chia sẻ trong nhóm

Nói thẳng ra, trên thực tế thì chúng ta sẽ không sử dụng tùy chọn này,Sử dụng hàm ROUND để điều khiển từng trường hợp riêng lẻ sẽ an toàn hơnĐây là lựa chọn được khuyên dùng.

Không chỉ riêng Excel｜Giới hạn của tiêu chuẩn IEEE 754

Sai số số thập phân động theo tiêu chuẩn IEEE 754 là,Đây không phải là vấn đề riêng của Excel, mà là vấn đề xảy ra trên tất cả các phần mềm bảng tính chính như Google Spreadsheet, Numbers, OpenOffice, v.v.Sẽ làm.

Hơn nữa, hầu hết các ngôn ngữ lập trình như C, Python, JavaScript, SQL… cũng đều tuân theo cùng một tiêu chuẩn.

Nói cách khác,Không phải là “vì dùng Excel nên không chính xác”, mà là tiêu chuẩn chung trên toàn thế giới. Với tư cách là một kỹ sư xây dựng, việc hiểu điều đó và sử dụng hàm ROUND mới là cách giải đúng.

5 điểm cần lưu ý

Khi sử dụng Excel để tính diện tích, tính số lượng, v.v., hãy lưu ý 5 điểm sau đây.

• ✅ Việc tính toán số tiền luôn luôn =ROUND(…,0) và làm tròn đến đơn vị yên
• ✅ Việc tính toán số lượng là =ROUND(…,2) và cố định 2 chữ số thập phân
• ✅ Việc làm tròn trong định dạng hiển thị không làm thay đổi giá trị bên trongCó ý thức về điều đó
• ✅ =A1=0,3 Tránh so sánh trực tiếp như vậy(Cái bẫy của kết quả FALSE)
• ✅ Trong bảng tính tổng chi phí dùng cho nhóm, không sử dụng “tùy chọn độ chính xác”

---

Nếu bạn còn băn khoăn, trước tiên hãy cân nhắc xem “có nên chèn thêm một bước hàm ROUND hay không”.

Chỉ cần chèn thêm 1 vòng lặp (ROUND) là sai số do tiêu chuẩn IEEE 754 gây ra sẽ biến mất hoàn toàn.

Không phải là Excel có “thói quen”, mà là,Những đặc điểm riêng của phép tính số thập phân động trên thế giớiChúng ta cần hiểu rõ và vận dụng thành thạo điều đó.

 

Hẹn gặp lại nhé.

Mặc dù có nhiều ý kiến đặt câu hỏi về sự cần thiết của sai số bố trí trong quá trình đổ bê tông, nhưng câu trả lời luôn chỉ có một!