Excel’s Numerical Limitations

Liên hệ QC

levanduyet

Hãy để gió cuốn đi.
Thành viên danh dự
Tham gia
30/5/06
Bài viết
1,798
Được thích
4,699
Giới tính
Nam
Nhân nói chuyện với DVC về vấn đề trên, tôi xin copy một đoạn trong sách Excel 2007 Bible như sau:
You may be curious about the types of values that Excel can handle. In other words, how large can numbers be? And how accurate are large numbers?
Excel’s numbers are precise up to 15 digits. For example, if you enter a large value, such as
123,456,789,123,456,789 (18 digits), Excel actually stores it with only 15 digits of precision. This 18-digit number displays as 123,456,789,123,456,000. This precision may seem quite limiting, but in practice, it rarely causes any problems.
One situation in which the 15-digit accuracy can cause a problem is when entering credit-card numbers.
Most credit-card numbers are 16 digits long. But Excel can handle only 15 digits, so it will substitute a zero
for the last credit-card digit. Even worse, you may not even realize that Excel made the card number invalid.
The solution? Enter the credit-card numbers as text. The easiest way is to preformat the cell as Text (choose Home ➪ Number and choose Text from the drop-down Number Format list). Or you can precede the creditcard
number with an apostrophe. Either method prevents Excel from interpreting the entry as a number.
Here are some of Excel’s other numerical limits:
Largest positive number: 9.9E+307
Smallest negative number: –9.9E+307
Smallest positive number: 1E–307
Largest negative number: –1E-307
These numbers are expressed in scientific notation. For example, the largest positive number is “9.9 times 10
to the 307th power.” (In other words, 99 followed by 306 zeros.) But keep in mind that this number has only
15 digits of accuracy.
Cũng xin nói luôn, DVC nêu vấn đề việc Excel sử lý số liệu không chính xác nếu con số dài hơn 15 số.
Tôi xin không dịch để tránh việc hiểu sai nghĩa.

Thân,

Lê Văn Duyệt
 
Dear all,
--------
Vấn đề mà anh levanduyet đang bàn luận tại đây (Thắc mắc của anh aThuan) và anh Hướng có nêu trong chủ đề: Những vấn đề với tính toán dấu chấm động

Em thấy các bài viết hầu như chỉ bàn đến lỗi này trong lập trình chứ chưa đề cập đến các xử lý trực tiếp trên Cell. Điều mà em thắc mắc là trong trường hợp nào thì xảy ra lỗi này vì, trong một số trường hợp, khi thay đổi giá trị của A, B, D (như trong hình dưới đây) thì không xảy ra lỗi này?

Err_InterCell1.jpg


Tìm ra quy luật là để có hướng giải quyết. Trong lập trình thì chúng ta có thể VAL cho biến, còn làm việc với các Formula trực tiếp trên Cell, không lẽ lại cứ phải dùng hàm VALUE() hay sao?
 
Vấn đề này có bài tôi đã giải thích một lần rồi.
Về bản chất, kiểu giá trị của Cell trên Sheet là dạng VARIANT
Trong VARIANT, giá trị kiểu số có 2 kiểu:
Short Int = Integer nhận giá trị trong khoản [-32,768 đến 32,767].
double nhận giá trị trong khoảng:Nếu số âm -1.79769313486231E308 đến -4.94065645841247E-324; nếu số dương 4.94065645841247E-324 đến 1.79769313486232E308.

Gấn như các trường hợp giá trị trên cell nhập là số, Excel đều convert về double.

Phép tính với các giá trị kiểu double, khi con số lẻ rất nhỏ (.000###) thì kết quả so sánh bị sai. Điều này là nguyên nhân phần cứng (chip xử lý), vì vậy tất cả các ngôn ngữ lập trình C/C++, Delphi, Foxpro, VB, Java,...đều bị như vậy chứ không phải chỉ Excel đâu.

Các khắc phục:
- Trong code hãy khai báo số lẻ là Currency thay cho Double
- Trên Cell cần phải dùng hàm là tròn như là hàm ROUND
Số đơn vị phần thập phân (dau dấu chấm) được làm tròn phải nằm trong khoảng 0..14 thì đúng (vì max của Double là 1.79769313486232E308, có 14 con số sau dấu chấm "." là số thực).

Tôi chỉ biết đến đó thôi. Tôi nghĩ phải có lý do rất "chính đáng" mà nhà sản xuất phần cứng và phần mềm ngôn ngữ vẫn để nguyên không hề sửa, vấn đề này người ta phát hiện rất rất lâu rối không phải mới đây đâu. Với chúng ta cho là lỗi nhưng với các "đại gia" trên có thể họ không nghĩ như vậy.
Help MS:
Mã:
Variant Data Type
      

The Variant data type is the data type for all variables that are not explicitly declared as some other type (using statements such as Dim, Private, Public, or Static). The Variant data type has no type-declaration character.

A Variant is a special data type that can contain any kind of data except fixed-length String data. (Variant types now support user-defined types.) A Variant can also contain the special values Empty, Error, Nothing, and Null. You can determine how the data in a Variant is treated using the VarType function or TypeName function.

Numeric data can be any integer or real number value ranging from -1.797693134862315E308 to -4.94066E-324 for negative values and from 4.94066E-324 to 1.797693134862315E308 for positive values. Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer. However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double.[Color=Blue] An error occurs when Variant variables containing Currency, Decimal, and Double values exceed their respective ranges.[/Color]

You can use the Variant data type in place of any data type to work with data in a more flexible way. If the contents of a Variant variable are digits, they may be either the string representation of the digits or their actual value, depending on the context. For example:

Dim MyVar As Variant
MyVar = 98052

In the preceding example, MyVar contains a numeric representation—the actual value 98052. Arithmetic operators work as expected on Variant variables that contain numeric values or string data that can be interpreted as numbers. If you use the + operator to add MyVar to another Variant containing a number or to a variable of a numeric type, the result is an arithmetic sum.

The value Empty denotes a Variant variable that hasn't been initialized (assigned an initial value). A Variant containing Empty is 0 if it is used in a numeric context and a zero-length string ("") if it is used in a string context.

Don't confuse Empty with Null. Null indicates that the Variant variable intentionally contains no valid data.

In a Variant, Error is a special value used to indicate that an error condition has occurred in a procedure. However, unlike for other kinds of errors, normal application-level error handling does not occur. This allows you, or the application itself, to take some alternative action based on the error value. Error values are created by converting real numbers to error values using the CVErr function.
 
Dear TuanVNUNI,
----------------
1. Nhận định "lỗi phát sinh là do phần cứng" thì em chưa có dịp kiểm tra nhưng em thấy anh aThuan đã bác bỏ khả năng này:

Bác giải thích thế mình thấy vẫn ........chưa tâm phục khẩu phục:

- Trên cùng 1 máy (không có bất cứ thay đổi nào về phần cứng) thử trên Excell 2000: thì không sao (kết quả đúng) còn excell 2003 thì ...có sao.=> không thể kết luận là do lỗi xử lý của phần cứng được.

- Như bác giải thích 1.15 là kiểu double=> xử lý bị lỗi. Vậy thay bằng (=11,7-7,2-4,116) có phải là double không??? Trong phép tính này lại tính đúng
2. Em công nhận các giá trị chứa trên Cell có kiểu Double dẫn đến kết quả sai. Nhưng em không thể lý giải được một phép tính có giá trị lẻ tương đương lại không xảy ra như vậy, ví dụ phép tính:
= 11,23 - 5,6 - 4,12= 1,51
3. Như em đã nhấn mạnh, lỗi này xảy ra trong công thức tính trực tiếp trên Cell, và có ảnh hưởng chủ yếu đến những người mới tiếp cận với MS Excel (chẳng hạn như học sinh tiểu học, THPT). Không lẽ với một phép tính đơn giản của học sinh bậc tiểu học:
=11,7-7,2-4,35= 0,15
MS Excel lại tính sai để rồi các cháu phải đọc Help để biết cách sử dụng hàm Round()?!
Điều này cũng dẫn đến làm em có tâm lý không tin tưởng vào kết quả tính toán của các phép tính tương tự. Chẳng hạn:
Trước, thay vì viết:
= 11,23 - 5,6 - 4,12 = 1.51
thì nay phải viết:
= ROUND(11,23 - 5,6 - 4,12; 2) = 1,51
em mới dám tin!
Những phép tính nhìn rất đơn giản như vậy, chẳng nhẽ lúc nào cũng phải ROUND()? Nó phải có quy luật gì để cho em lưu ý chứ! Và còn những trường hợp bắt buộc không được làm tròn thì xử lý ra sao (nhắc lại rằng, em không biết tí gì về lập trình cả!)
Thực ra, chẳng khó khăn gì để xử lý lỗi này nhưng em sẽ phải thận trọng hơn trước các phép tính tương tự như vậy!
4. Liệu có đáng quan tâm?
 
Web KT
Back
Top Bottom