Lỗi nguy hiểm nhất trong bảng tính không phải lỗi hiện ra mã báo đỏ, mà là công thức chạy trơn tru và trả về một con số sai về mặt toán học. Những lỗi này đến từ khoảng cách giữa cách phần mềm hiểu một phép tính và cách người viết công thức nghĩ rằng nó hiểu.
Bài này không dạy hàm, phần đó thuộc về trang AI Dữ Liệu. Ở đây ta đi qua những chỗ toán học bị hiểu lệch: thứ tự phép tính, phần trăm, ô trống, ngày tháng, số thập phân trong máy tính. Cuối bài là cách kiểm một công thức bằng số tròn và cách nhờ AI giải thích công thức mà không bị dắt đi sai. Ví dụ dựa trên cách các bảng tính phổ biến hoạt động; cách hành xử chi tiết có thể khác giữa các phần mềm và phiên bản, nên hãy thử trên bản bạn đang dùng.
Thứ tự phép tính: bảng tính không phải lúc nào cũng giống sách giáo khoa
Dấu trừ đứng đầu và luỹ thừa. Trong toán học thông thường, âm 2 bình phương được hiểu là âm của 2 bình phương, tức âm 4. Trong Excel, công thức bằng trừ 2 mũ 2 lại cho kết quả 4, vì dấu trừ đứng đầu được tính trước phép luỹ thừa. Khi công thức có dạng âm của một biến rồi mũ lên, kết quả có thể sai dấu mà không báo gì. Cách an toàn là luôn đặt ngoặc rõ ràng, chẳng hạn trừ của (2 mũ 2).
Luỹ thừa chồng luỹ thừa. Trong toán, 2 mũ 3 mũ 2 thường được hiểu là 2 mũ 9, bằng 512, tức tính từ phải sang trái. Excel tính từ trái sang phải: (2 mũ 3) mũ 2, bằng 64. Trường hợp này hiếm gặp trong công việc thường ngày, nhưng xuất hiện trong các công thức tăng trưởng kép lồng nhau. Lại một lần nữa, ngoặc là bạn.
Nhân chia từ trái sang phải. Công thức 10 chia 2 nhân 5 cho 25, vì phép chia được làm trước rồi mới nhân. Nhiều người đọc nhầm thành 10 chia cho (2 nhân 5) bằng 1. Bảng tính tuân đúng quy tắc toán học ở đây, nhưng người viết công thức thì không phải lúc nào cũng nhớ. Khi mẫu số là một tích, hãy đặt cả tích trong ngoặc.
Phần trăm là một số thập phân. Ô hiện 5% thực chất chứa 0,05. Công thức tăng giá 5% phải là giá nhân (1 cộng 5%), hoặc giá nhân 1,05. Viết giá nhân (1 cộng 5) là nhân giá lên 6 lần. Lỗi này hay xảy ra khi tỷ lệ được nhập ở một ô dưới dạng số 5 và ở ô khác dưới dạng 5%; cả hai trông như nhau trong đầu người nhập nhưng khác nhau 100 lần trong máy.
Ô trống, số 0 và chữ: ba thứ trông giống nhau, tính khác nhau
Hàm tính trung bình phổ biến trong bảng tính bỏ qua ô trống và ô chứa chữ, nhưng tính cả ô có số 0. Ví dụ giả định dưới đây là doanh số bốn tuần của một nhân viên mới, nhập bởi ba người khác nhau.
| Cách nhập tuần 2 và tuần 3 | Dữ liệu | Trung bình bảng tính trả về | Ý nghĩa thật |
|---|---|---|---|
| Tuần 2 nhập 0, tuần 3 để trống | 100; 0; trống; 200 | 300 chia 3, bằng 100 | Tuần 3 bị coi như không tồn tại |
| Cả hai tuần nhập 0 | 100; 0; 0; 200 | 300 chia 4, bằng 75 | Đúng nếu hai tuần đó thật sự không bán được |
| Cả hai tuần ghi “nghỉ phép” | 100; chữ; chữ; 200 | 300 chia 2, bằng 150 | Đúng nếu muốn trung bình theo tuần đi làm |
Cùng bốn tuần, ba cách nhập cho ba kết quả 75, 100 và 150. Không cách nào sai về mặt phần mềm; mỗi cách trả lời một câu hỏi khác nhau. Lỗi nằm ở chỗ người nhập không thống nhất ý nghĩa của ô trống. Ô trống có thể nghĩa là “bằng 0”, “chưa có số liệu” hoặc “không áp dụng”, và bảng tính không có cách nào phân biệt.
Quy tắc nên đặt cho nhóm: số 0 chỉ dùng khi giá trị thật bằng 0; ô trống chỉ dùng khi chưa có số liệu; trường hợp không áp dụng thì đánh dấu bằng một ký hiệu thống nhất. Trước khi tính trung bình, đếm xem có bao nhiêu ô trống trong cột và tự hỏi mỗi ô đó nghĩa là gì.
Ngày tháng là số đếm ngày, và điều đó có hệ quả
Trong phần lớn bảng tính, một ngày được lưu dưới dạng số ngày tính từ một mốc quy ước, còn giờ phút là phần thập phân của ngày. Nhờ vậy, lấy ngày kết thúc trừ ngày bắt đầu cho ra số ngày, rất tiện. Nhưng cũng vì vậy, nhiều phép tính tưởng hiển nhiên lại có bẫy.
Bẫy thứ nhất là đổi số ngày sang số năm. Chia cho 365 sẽ lệch một ngày cho mỗi năm nhuận đi qua; với thâm niên 20 năm, sai lệch khoảng 5 ngày, đủ để một người được tính là tròn 20 năm sớm hơn ngày thật gần một tuần, và với mốc nào có quyền lợi kèm theo thì vài ngày đó đáng kể. Chia cho 365,25 tốt hơn nhưng vẫn có thể lệch ngay quanh ngày kỷ niệm. Với những việc có hệ quả như tính thâm niên, tuổi, thời hạn hợp đồng, hãy dùng hàm chuyên tính chênh lệch theo năm tháng của phần mềm và kiểm bằng vài ca biết trước đáp án.
Bẫy thứ hai là tháng có độ dài khác nhau. Cộng một tháng vào ngày 31 tháng 1 cho ra ngày nào? Tuỳ hàm, kết quả có thể là ngày cuối tháng 2, hoặc tràn sang đầu tháng 3. Lịch thanh toán, lịch bảo dưỡng định kỳ mà lệch ngày kiểu này sẽ gây rắc rối kéo dài cả năm.
Bẫy thứ ba là giờ làm việc. Tổng giờ công vượt 24 giờ có thể bị hiển thị như một mốc giờ trong ngày, chẳng hạn 26 giờ hiện thành 2 giờ, nếu ô dùng định dạng giờ thông thường. Giá trị thật vẫn đúng, nhưng người đọc thấy sai. Và khi nhân giờ công với đơn giá, nhớ rằng giá trị lưu là phần của ngày: 8 giờ là một phần ba ngày, nên phải nhân với 24 trước khi nhân đơn giá theo giờ.
Số thập phân trong máy tính không chính xác tuyệt đối
Máy tính lưu số ở dạng nhị phân. Những số trông rất tròn trong hệ thập phân, như 0,1, lại là số vô hạn tuần hoàn trong hệ nhị phân, giống như một phần ba là 0,333 lặp mãi trong hệ thập phân. Vì vậy, máy lưu 0,1 bằng một giá trị rất gần nhưng không bằng đúng 0,1.
Thường thì sai lệch này nhỏ đến mức không ai thấy, vì phần mềm làm tròn khi hiển thị. Nó lộ ra ở hai chỗ. Thứ nhất là phép so sánh bằng: tuỳ cách viết công thức, một phép kiểm xem tổng các khoản có bằng đúng một số cho trước có thể trả về sai dù hai bên hiện ra giống hệt nhau, vì chúng chênh nhau ở chữ số thứ mười mấy. Thứ hai là phép trừ hai số gần bằng nhau, có thể để lại một phần dư cực nhỏ thay vì số 0, khiến điều kiện “nếu bằng 0 thì” không chạy.
Cách xử lý trong công việc: làm tròn về số chữ số cần thiết trước khi so sánh, hoặc so xem chênh lệch tuyệt đối có nhỏ hơn một ngưỡng như một phần nghìn đồng không, thay vì so bằng tuyệt đối.
Một giới hạn liên quan: nhiều bảng tính chỉ giữ khoảng 15 chữ số có nghĩa. Mã số dài hơn, như mã vận đơn hoặc mã tài khoản dài, nếu nhập như số sẽ bị biến các chữ số cuối thành 0 mà không báo. Mọi mã số dài không dùng để tính toán nên được lưu ở dạng chữ ngay từ đầu.
Cộng trùng: dòng tổng phụ, dòng ẩn và vùng cộng không theo kịp dữ liệu
Một nhóm lỗi khác không nằm ở phép tính mà nằm ở việc cộng những gì. Lỗi kinh điển là cộng trùng tổng phụ. Bảng chi phí chia theo bộ phận, mỗi bộ phận có một dòng tổng phụ, rồi ở cuối có một dòng tổng chung cộng cả cột. Nếu vùng cộng bao gồm cả các dòng tổng phụ, mỗi khoản được tính hai lần và tổng chung gần như gấp đôi thực tế. Với bảng lớn, con số gấp đôi đôi khi vẫn trông hợp lý nếu không ai có cảm giác về quy mô.
Lỗi thứ hai là dòng ẩn và dòng lọc. Khi lọc một bảng để chỉ hiện một khu vực, hàm cộng thông thường vẫn cộng cả những dòng đang bị ẩn. Người nhìn màn hình thấy mười dòng nhưng tổng ở dưới là tổng của cả trăm dòng. Có hàm riêng chỉ cộng các dòng đang hiện; nếu bạn cần tổng theo bộ lọc, hãy kiểm xem mình đang dùng hàm nào.
Lỗi thứ ba là vùng cộng không theo kịp dữ liệu. Công thức tổng được viết khi bảng có 50 dòng, sau đó người khác dán thêm 10 dòng bên dưới, và tổng vẫn chỉ cộng 50 dòng cũ. Phép kiểm đơn giản cho cả ba lỗi là đếm: số dòng được cộng có khớp với số dòng dữ liệu thật không, và tổng chung có bằng tổng của các tổng phụ không. Một phép ước nhanh về quy mô, chẳng hạn mỗi bộ phận chi chừng bao nhiêu, cũng thường bắt được tổng gấp đôi.
Kiểm một công thức bằng số tròn trước khi tin nó
Phép thử hiệu quả nhất cho mọi công thức là thay vào những con số bạn tự tính được trong đầu. Quy trình gọn như sau.
- Sao bảng sang một trang nháp, thay dữ liệu thật bằng số tròn: 1, 10, 100. Với công thức tăng trưởng, dùng các số mà bạn biết đáp án, như 100 lên 121 sau hai kỳ là tăng 10% mỗi kỳ.
- Thử các ca biên: giá trị bằng 0, giá trị âm, ô trống, ô có chữ, ngày cuối tháng, năm nhuận. Phần lớn lỗi nằm ở biên chứ không nằm ở ca thông thường.
- Kiểm chéo bằng hai đường: tổng theo hàng phải bằng tổng theo cột; tổng các phần phải bằng toàn bộ; trung bình nhân số lượng phải bằng tổng.
- Đọc to công thức bằng lời, có ngoặc. Nếu không đọc được rõ ràng phép nào làm trước, thêm ngoặc.
- Ghi lại các ca thử và kết quả đúng ở một góc bảng, để lần sau sửa công thức thì chạy lại được ngay.
Trợ lý AI giúp được ở bước đọc công thức. Che tên khách hàng, số tiền thật và các thông tin nội bộ, chỉ dán công thức kèm mô tả cột, rồi gửi câu lệnh: “Giải thích bằng lời công thức sau làm gì, theo đúng thứ tự phần mềm tính. Đề xuất năm ca thử với số tròn và kết quả đúng của từng ca, trong đó có ca biên như ô trống, số 0, số âm.” Sau đó chính bạn chạy các ca thử trên bảng tính, không tin kết quả AI tự tính.
Lưu ý thêm: AI thường viết công thức với dấu phẩy ngăn cách đối số, trong khi máy cài đặt vùng Việt Nam có thể dùng dấu chấm phẩy và dấu phẩy thập phân. Nếu dán vào báo lỗi, đó thường là chuyện dấu phân cách chứ không phải logic.
Câu hỏi thường gặp
Vì sao công thức trừ 2 mũ 2 trong Excel ra 4 chứ không phải âm 4?
Vì trong Excel, dấu trừ đứng đầu được tính trước phép luỹ thừa, nên công thức hiểu là (âm 2) bình phương. Muốn ra âm 4 theo cách hiểu toán học thông thường, hãy viết rõ ngoặc: trừ của (2 mũ 2). Nên đặt ngoặc mọi chỗ có dấu trừ và luỹ thừa.
Ô trống và ô số 0 khác nhau thế nào khi tính trung bình?
Hàm trung bình phổ biến bỏ qua ô trống và ô chứa chữ nhưng tính cả ô bằng 0. Vì vậy cùng một bộ dữ liệu, nhập 0 hay để trống sẽ cho trung bình khác nhau. Nhóm làm việc cần thống nhất ô trống nghĩa là gì trước khi nhập.
Tính thâm niên bằng cách chia số ngày cho 365 có được không?
Không nên với việc có hệ quả, vì cách này lệch một ngày cho mỗi năm nhuận đi qua và có thể tính sai ngay quanh ngày kỷ niệm. Hãy dùng hàm chuyên tính chênh lệch theo năm tháng của phần mềm và kiểm bằng vài ca biết trước đáp án.
Vì sao hai số hiện ra giống nhau mà phép so sánh bằng lại trả về sai?
Vì máy tính lưu số thập phân ở dạng nhị phân, nhiều số như 0,1 không biểu diễn được chính xác, nên hai giá trị có thể chênh ở chữ số rất xa. Hãy làm tròn trước khi so hoặc so chênh lệch tuyệt đối với một ngưỡng nhỏ.
Vì sao mã số dài nhập vào bảng tính bị đổi các chữ số cuối thành 0?
Vì nhiều bảng tính chỉ giữ khoảng 15 chữ số có nghĩa cho một con số. Mã số dài hơn mà không dùng để tính toán nên được định dạng là chữ trước khi nhập hoặc dán vào, để giữ nguyên từng chữ số.
Có nên nhờ AI viết công thức bảng tính không?
Có thể, nhưng hãy nhờ nó giải thích bằng lời và đề xuất ca thử với số tròn, rồi tự chạy ca thử trên bảng tính. Che dữ liệu thật trước khi dán, và để ý dấu phân cách đối số khác nhau giữa cài đặt vùng Việt Nam và tiếng Anh.