Có một lỗi tính toán xấu xí đã xuất hiện trong Excel. Có vẻ như vấn đề đã đi sâu vào công cụ tính toán của Excel và sẽ không dễ sửa chữa.
Cốt lõi của vấn đề là một thực tế đơn giản: Excel lưu trữ 15 chữ số chính xác trong một ô. Bạn có thể có các số có 20 chữ số, nhưng bất kỳ chữ số nào giữa chữ số có nghĩa cuối cùng và chữ số thập phân phải bằng không.
Gần đây tôi đã gặp hai trường hợp trong đó công cụ tính toán của Excel trả về kết quả sai. Khi tôi tìm hiểu vấn đề và xem xét XML bên dưới, tôi đã rất ngạc nhiên khi thấy rằng Excel đang bí mật lưu trữ 17 chữ số trong XML.
Vấn đề là Excel sẽ chỉ hiển thị 15 chữ số. Vì vậy, bạn nghĩ rằng bạn có một số được lưu trữ dưới dạng 0,123456789012345 nhưng nó thực sự được lưu trữ dưới dạng 0,12345678901234567.
Bạn không thể nhìn thấy hai chữ số cuối cùng. Và hầu hết các hàm của Excel đều bỏ qua hai chữ số cuối cùng đó. Nếu * tất cả * các hàm bỏ qua hai chữ số cuối cùng, chúng ta sẽ không gặp vấn đề gì. Nhưng cho đến nay, sắp xếp, RANK và FREQUENCY đang sử dụng tất cả 17 chữ số.
Dưới đây là một thủ thuật nổi tiếng để xếp hạng các ô. Nếu bạn cần mọi thứ hạng xuất hiện chính xác một lần, bạn có thể kết hợp RANK và COUNTIF. Trong hình ảnh bên dưới, Claire, Flo, Ivana và Lucy bị ràng buộc ở mức 115%. Sử dụng công thức RANK + COUNTIF, chúng sẽ được xếp hạng 5, 6, 7 và 8.
Nhưng công thức không thành công. Hai hàng được xếp hạng là 7. Điều đó không bao giờ xảy ra. Bốn công thức trong cột D đảm bảo rằng 115% trong B6, B9, B12 và B15 là giống nhau. Công =B6=B15thức báo cáo rằng cả hai ô đều chứa cùng một dữ liệu.
Khi tôi cố gắng cô lập vấn đề, chỉ nhìn vào chức năng RANK. Nó sẽ báo cáo tỷ lệ hòa 4 bên ở vị trí thứ 4 cho những người với 115%. Nhưng bằng cách nào đó, Lucy ở Hàng 15 lại được xếp trên ba người còn lại.
Để tìm ra điều đó, tôi đã gửi yêu cầu trợ giúp đến các MVP khác của Excel. Jan Karel Pieterse đã bẻ khóa mở tệp Excel và xem trong tệp XML. Trong XML, bạn có thể thấy rằng chúng đang lưu trữ 17 chữ số chính xác. Bốn ô trông giống như một cột trong Excel không bị ràng buộc trong XML. Một trong số 115% được lưu trữ là 1.1500000000000001 và những người khác là 1.1499999999999999.
Cho đến nay, sắp xếp, xếp hạng và hàm FREQUENCY đang sử dụng các chữ số phụ. Tại sao đó là một vấn đề? Bởi vì chúng tôi dựa vào RANK và COUNTIF để sử dụng cùng một số chữ số. Với một hàm sử dụng 15 chữ số và hàm kia sử dụng 17 chữ số, bạn có một vấn đề.
Hiện tại, giải pháp dường như đang chuyển đổi tất cả các câu trả lời của bạn bằng cách sử dụng =ROUND(A4,15).
Thứ sáu hàng tuần, tôi kiểm tra một lỗi hoặc các hành vi khó hiểu khác trong Excel. Lỗi tính toán này khó phát hiện và đủ tiêu chuẩn là một con cá lớn.
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Mỗi khi bạn hợp nhất các ô, bạn sẽ giết một con mèo con"
Szilvia Juhasz
