HotXLS Precision as Displayed: Luật làm tròn của Excel

Excel precision as displayed làm tròn từng số đã lưu về số chữ số thập phân mà number format của nó hiện: section định dạng khớp với dấu của giá trị, thêm hai chữ số cho mỗi %, bớt ba cho mỗi dấu phẩy scale-nghìn, làm tròn nửa về phía xa không. HotXLS áp cùng luật đó ở cả hai engine Delphi khi TXLSXWorkbook.FullPrecision hay TXLSWorkbook.UseFullPrecision là False. Nghe như một dòng code cho tới khi khách hàng report rằng tổng hóa đơn bạn export lệch với Excel một cent, hay rằng một cột thời lượng trong [ss].00 sụp xuống thành không. Cả hai đều đã xảy ra, và cả hai đều truy về việc làm sai một trong các luật ấy. Kể từ v2.384.57, hai engine dùng chung một bản hiện thực với các giá trị kỳ vọng được đo trong Excel 16 với Workbook.PrecisionAsDisplayed bật

Precision as displayed thực chất đổi gì trong một workbook?

Precision as displayed là một cờ cấp workbook duy nhất bảo bộ máy tính toán lưu số theo dáng vẻ của chúng chứ không theo kết quả tính được. Trong UI của Excel nó nằm dưới File, Options, Advanced, "When calculating this workbook", với tên "Set precision as displayed". Trên đĩa nó là một bit. Tệp BIFF8 mang nó trong record CalcPrecision ($000E, [MS-XLS] §2.4.35), mà trường fFullPrec bằng 1 cho chế độ đủ độ chính xác thường và bằng 0 khi tùy chọn bật. Một gói XLSX mang nó như thuộc tính fullPrecision của phần tử calcPr trong workbook.xml, định nghĩa trong ECMA-376 Part 1, nơi mặc định là true và fullPrecision="0" bật làm tròn

Cờ này không phải một sở thích hiển thị. Khi bạn tick hộp, Excel cảnh báo dữ liệu sẽ mất chính xác vĩnh viễn, và nó nói thật: các giá trị bị viết lại theo độ chính xác hiển thị, còn những chữ số bị cắt thì mất hẳn. Bỏ tick sau đó không đưa các chữ số cũ trở lại. Một 0.1234 hiện thành 12.3% biến thành 0.123 mãi mãi

HotXLS đọc và ghi cờ ở cả hai định dạng và phơi nó ở cả hai engine:

• TXLSXWorkbook.FullPrecision: Boolean trên engine XLSX, nạp từ và lưu vào calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean trên engine Classic (cũng có trên IXLSWorkbook), nạp từ và lưu vào record CalcPrecision
• Cả hai mặc định True, chế độ an toàn, không phá hủy và cũng là mặc định của Excel

HotXLS áp phép làm tròn ở đâu mới là chuyện đáng lẽ. HotXLS làm tròn tại chỗ nó tính ra một giá trị: mỗi kết quả công thức được làm tròn về độ chính xác hiển thị trước khi được lưu thành giá trị cache của ô, trong Recalculate lẫn khi đánh giá theo yêu cầu. Các hằng bạn gán qua Value được lưu đúng như đưa vào. Nếu output của bạn phải tái hiện được những gì Excel lưu sau khi hộp được tick, hãy tự làm tròn những hằng đó trước khi ghi, ví dụ bằng helper sẽ hiện sau

Excel quyết định giữ bao nhiêu chữ số thập phân thế nào?

Excel suy ra số chữ số được giữ từ section định dạng cụ thể mà hiện giá trị, không phải từ cả chuỗi định dạng. Các luật dưới đây được đo trong Excel 16 và là những gì XlsApplyDisplayedPrecision trong lxNumFormat hiện thực cho cả hai engine HotXLS

1. Chọn section theo dấu. Định dạng hai section dùng section hai cho giá trị âm. Định dạng ba section trở lên dùng section hai cho giá trị âm và section ba cho đúng bằng không. Mọi trường hợp khác dùng section một
2. Đếm placeholder thập phân. Mỗi 0, # hay ? sau dấu thập phân trong section đó cộng một chữ số được giữ
3. Thêm hai cho mỗi dấu phần trăm. 0.0% hiện 0.1234 thành 12.3%, nên giá trị lưu là một phần trăm cái bạn thấy và giữ ba chữ số, không phải một
4. Bớt ba cho mỗi dấu phẩy scale. Một dấu phẩy sau placeholder nguyên cuối cùng (0,, 0.0,, 0,.0) chia hiển thị cho 1000. 0.0, hiện 12345.678 thành 12.3, nên Excel giữ một chữ số trừ ba, tức một số âm: giá trị được làm tròn tới hàng trăm và lưu thành 12300. Một dấu phẩy giữa các placeholder nguyên, như trong #,##0, chỉ là nhóm chữ số và không đổi gì
5. Bỏ qua các section phi số. Section General, ngày giờ (gồm cả elapsed [h], [mm] và [ss]), khoa học, phân số và text, cùng các section không có placeholder chữ số nào, giữ nguyên độ chính xác đầy đủ

Đo đối chiếu với Excel 16, đây là các giá trị cả hai engine HotXLS giờ lưu cho một kết quả công thức ở mỗi định dạng:

Định dạng số	Giá trị tính ra	Giá trị lưu	Luật áp dụng
0.0%	0.1234	0.123	Một chữ số cộng hai cho dấu phần trăm
0	2.5	3	Nửa về xa không, không phải về số chẵn
0	-2.5	-3	Nửa về xa không cả ở phía âm
0.00;(0.0)	-1.2345	-1.2	Section âm hiện một chữ số
0.00;(0.0)	1.2345	1.23	Section dương hiện hai chữ số
#,##0.0	1234.5678	1234.6	Dấu phẩy nhóm, không scale
0.0,	12345.678	12300	Một chữ số trừ ba: làm tròn tới hàng trăm
0.0%;(0.00%)	-0.0125	-0.0125	Section âm giữ hai cộng hai chữ số
0.00	1.005	1.01	Dung sai cho lỗi biểu diễn nhị phân
0;-0;0.0	0.5	1	Không phải không, nên section dương quyết định

Hàng cuối là một cái bẫy đẹp. Giá trị 0.5 được làm tròn thành số nguyên, và section không bao giờ được gọi vào, vì Excel chọn section từ giá trị tính được trước khi làm tròn. Một hạn chế thẳng thắn phía HotXLS: section chỉ được chọn theo dấu, nên một định dạng mà các section mang điều kiện ngoặc tùy chỉnh như [>=1000] vẫn bị tách theo dấu. Hãy kiểm tra những định dạng đó với Excel nếu chúng quan trọng với bạn

Vì sao 1.005 làm tròn thành 1.01 chứ không phải 1.00?

Excel làm tròn 1.005 trong ô 0.00 thành 1.01 dù double gần 1.005 nhất nằm hơi dưới điểm giữa, và HotXLS khớp điều đó bằng một dung sai vài ulp. Literal 1.005 không biểu diễn được trong số thực nhị phân. Double IEEE 754 gần nhất là 1.00499999999999989341858963598497211933135986328125, và nhân với 100 cho ra 100.49999999999999. Một công thức sách giáo khoa Floor(x * 100 + 0.5) / 100 vì thế trả về 1.00, bất đồng với số người dùng gõ, với thứ Excel hiện, và với thứ Excel lưu

Delphi thêm một nét riêng. System.Round làm tròn hòa về số chẵn, nên Round(2.5) là 2 và Round(3.5) là 4. Đó là banker's rounding, một mặc định hợp lý cho thống kê và là luật sai ở đây: Excel lưu 3 cho 2.5 trong ô 0 và -3 cho -2.5. Bản hiện thực của HotXLS làm việc trên giá trị tuyệt đối, cộng 0.5 cùng một dung sai tương đối bằng 2^-51 lần giá trị đã scale (vài ulp ở cỡ đó, không bao giờ ít hơn hai ulp của 1.0), cắt phần lẻ, scale ngược và trả lại dấu. Hàm dưới đây là một minh họa tự trọn vẹn cho nguyên lý đó, không phải mã của thư viện, và nó xử lý số đếm chữ số âm cho dấu phẩy scale theo cùng một cách:

// Phác thảo nguyên lý: round half away from zero tới ADigits chữ số thập phân,
// với dung sai vài ulp để 1.005 với tới 1.01.
// ADigits < 0 round tới hàng chục, trăm, ... ("0.0," cho -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, hai ulp của 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // vượt độ chính xác double: để nguyên giá trị
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // scale sẽ tràn
    Scaled := Abs(AValue) * Scale;
  end
  else
    Scaled := Abs(AValue) / Scale;
  Eps := Scaled * Tolerance;
  if Eps < Tolerance then
    Eps := Tolerance;
  Scaled := Int(Scaled + 0.5 + Eps); // half away from zero, không phải Round()
  if ADigits >= 0 then
    Result := Scaled / Scale
  else
    Result := Scaled * Scale;
  if AValue < 0 then
    Result := -Result;
end;

// RoundAsDisplayed(1.005, 2)      = 1.01   (theo Floor: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 chữ số)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 chữ số)

Dung sai là một đánh đổi có chủ đích. Một giá trị thật sự thấp hơn nửa bước đúng hai ulp cũng được làm tròn lên, nhưng ở khoảng cách đó sự khác biệt không phân biệt nổi với lỗi biểu diễn, và coi nó là nửa bước chính là thứ khiến các số thập phân gõ tay hành xử theo kỳ vọng của người dùng

Điều gì đã sai trước v2.384.57?

Trước v2.384.57, engine XLSX và engine Classic mỗi bên có một đoạn code precision-as-displayed riêng, và mỗi bên sai một kiểu. Nếu bạn tạo workbook với tùy chọn bật, đây là các triệu chứng cần tìm trong tệp do các bản dựng cũ sinh ra

Engine XLSX: chỉ section đầu, không percent, banker's rounding

Đường XLSX cũ hỏi số chữ số thập phân của cả chuỗi định dạng, thứ chỉ nhìn section đầu và bỏ qua %, rồi làm tròn bằng Round. Một 0.1234 trong 0.0% bị lưu thành 0.1, tức 10% thay vì 12.3% trên màn hình. Một 2.5 trong 0 bị lưu thành 2 thay vì 3. Giá trị âm trong định dạng như 0.00;(0.0) bị làm tròn theo hai chữ số của section dương. Kể từ v2.384.57, engine XLSX gọi cùng routine dùng chung với engine Classic, bên cũng nhận hỗ trợ dấu phẩy scale trong bản phát hành đó

Engine Classic: TRUE biến thành -1

Engine Classic canh phép làm tròn bằng VarIsNumeric, mà VarIsNumeric trả về True cho một Variant varBoolean. Chuyển Variant đó bằng Double(V) cho ra -1, vì một Boolean True kiểu COM được lưu là -1. Một công thức như =A1>0 trong một ô định dạng 0.00 vì thế chui ra khỏi lần tính lại thành số -1. Kể từ v2.384.57, kết quả Boolean bị loại trước mọi phép thử số, và một kết quả logic vẫn là kết quả logic ở cả hai engine

Định dạng thời gian trôi qua bị đọc thành màu (v2.384.9)

Bug thứ ba nằm ở mô hình number format chứ không ở phép làm tròn. Bộ parse xếp mọi token trong ngoặc không phải điều kiện vào loại màu, nên [h], [mm] và [ss] không bao giờ đánh dấu section của chúng là date/time. Hiển thị không bị ảnh hưởng, vì định dạng chạy trên một đường riêng, nhưng precision as displayed dựa vào cờ đó để bỏ qua giá trị thời gian. Một thời lượng năm giây là 5/86400 ngày, cỡ 0.0000579, và một định dạng như [ss].00 trông như một số hai chữ số thập phân bình thường, nên với FullPrecision tắt, thời lượng bị làm tròn thành 0.00 ngày. Kể từ v2.384.9, một chuỗi trong ngoặc chỉ gồm một chữ h, m hay s được parse thành token elapsed-time và section được coi là date/time. Cùng bản phát hành đó sửa luôn việc nhận phút trong h:mm, nơi dấu hai chấm giữa các token từng che giấu giờ khỏi bộ parse

Bật precision as displayed trong HotXLS từ Delphi

Muốn có các giá trị lưu tương đương Excel, hãy đặt cờ trước lần tính lại mà phải tôn trọng nó, rồi đọc các kết quả cache hay lưu. Trên engine XLSX, FullPrecision là một cờ trơn: đổi nó không vô hiệu các kết quả mà một Recalculate trước đó đã lưu, nên hãy đặt nó ngay sau Create hay Open và trước Recalculate đầu tiên. Ví dụ dùng công thức vì đó là nơi HotXLS áp phép làm tròn:

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb := TXLSXWorkbook.Create;
  try
    Sh := Wb.Sheets.Add('Totals');
    Sh.Cells[1, 1].Value := 0.1234;
    Sh.Cells[2, 1].Value := 2.5;
    Sh.Cells[3, 1].Value := 12345.678;

    Sh.Cells[1, 2].Formula := '=A1';
    Sh.Cells[1, 2].NumberFormat := '0.0%';   // hiện 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // hiện 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // hiện 12.3 (nghìn)

    // Phải đặt trước Recalculate đầu tiên trên engine XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Kết quả cache giờ khớp Excel 16: 0.123, 3 và 12300.
    // Các hằng ở cột A giữ nguyên độ chính xác đầy đủ.
    Assert(Abs(Double(Sh.Cells[1, 2].Value) - 0.123) < 1E-12);
    Assert(Double(Sh.Cells[2, 2].Value) = 3);
    Assert(Double(Sh.Cells[3, 2].Value) = 12300);

    Wb.SaveAs('totals.xlsx'); // ghi <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Engine Classic hành xử như vậy, với một tiện lợi: gán TXLSWorkbook.UseFullPrecision đánh dấu mọi công thức trong đồ thị phụ thuộc là dirty, nên Recalculate kế tiếp đánh giá lại toàn bộ workbook dưới luật mới. Đổi một NumberFormat khi tùy chọn bật cũng đánh dấu các ô công thức bị ảnh hưởng là dirty, vì định dạng giờ quyết định giá trị lưu. Chú ý rằng Recalculate của Classic trả về số ô công thức nó không đánh giá nổi, nên số không nghĩa là thành công:

var
  Wb: TXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  try
    Sh := Wb.Sheets.Add;
    Sh.Range['A1', 'A1'].Value := -1.2345;
    Sh.Range['B1', 'B1'].Formula := '=A1';
    Sh.Range['B1', 'B1'].NumberFormat := '0.00;(0.0)';
    Sh.Range['C1', 'C1'].Formula := '=A1<0';
    Sh.Range['C1', 'C1'].NumberFormat := '0.00';

    Wb.UseFullPrecision := False; // đánh dấu mọi công thức dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: section âm "(0.0)" hiện một chữ số
    // C1 giữ Boolean True (bản dựng trước v2.384.57 lưu -1)
    Wb.SaveAs('report.xls'); // record CalcPrecision với fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Cả hai engine cũng tôn trọng cờ đi kèm trong tệp. Mở một workbook lưu với tùy chọn bật và FullPrecision hay UseFullPrecision đã là False sẵn, nên một Recalculate sau khi nạp làm tròn đúng cách Excel sẽ làm. Nếu bạn chỉ cần đọc các số Excel đã lưu, có thể bỏ hẳn phép tính lại, như đã nói trong đọc giá trị công thức cache không cần tính lại. Còn serial số ngày và định dạng ngày tương tác với mô hình định dạng dẫn dắt phép kiểm tra date/time ra sao, xem Excel date serial, hệ 1904 và numFmt trong Delphi

Khi nào nên bật precision as displayed, khi nào thì đừng?

Chỉ bật precision as displayed khi các số lưu của workbook phải bằng các số hiển thị, và bạn chấp nhận mất các chữ số thừa mãi mãi. Trường hợp chính đáng kinh điển là một bảng tài chính nơi các cột số đã làm tròn phải cộng đúng thành tổng đã làm tròn trên màn hình, không để các phần cent ẩn nào tạo ra một tổng lệch một đơn vị ở chữ số cuối. Khớp với một workbook sẵn có của khách hàng mà đã đặt tùy chọn này là lý do tốt còn lại, và HotXLS giữ cờ qua round-trip để bạn không lặng lẽ đưa họ về chế độ đủ độ chính xác

Tránh nó trong phần lớn các tình huống khác:

• Dữ liệu kỹ thuật và khoa học. Làm tròn một phép đo vì ai đó chọn định dạng hai chữ số cho một báo cáo là phá hủy thông tin mà không thay đổi định dạng nào sau đó cứu nổi
• Phần trăm với định dạng thô. Một định dạng 0% chỉ giữ hai chữ số của tỉ lệ đã lưu, nên 0.1234 thành 0.12, và mọi công thức phía dưới đọc ô đó đều làm việc với 0.12
• Hiển thị có scale. Một định dạng 0, hay 0.0, dùng để hiện hàng nghìn làm tròn giá trị lưu tới hàng nghìn hay trăm, thứ hiếm khi là ý định của người chọn định dạng
• Template dùng chung. Cờ là toàn workbook. Bất kỳ ai sau đó thêm một sheet đều kế thừa hành vi này, thường mà không hay biết nó đang bật

Nếu điều bạn thật sự muốn là các kết quả đã làm tròn ở vài ô cụ thể, hãy viết ROUND vào các công thức đó. ROUND tường minh, nằm gọn trong ô, ai đọc công thức cũng thấy, và được engine công thức HotXLS đánh giá như mọi hàm khác, không có tác dụng phụ nào phủ cả workbook

Tra nhanh precision as displayed

• Cờ tệp: CalcPrecision $000E với fFullPrec = 0 trong BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" trong XLSX (ECMA-376 Part 1)
• Công tắc HotXLS: TXLSXWorkbook.FullPrecision := False và TXLSWorkbook.UseFullPrecision := False, cả hai mặc định True
• Section: chọn theo dấu của giá trị tính được; section ba chỉ cho đúng bằng không
• Chữ số: placeholder thập phân, cộng hai cho mỗi %, trừ ba cho mỗi dấu phẩy scale; số đếm có thể âm
• Làm tròn: nửa về phía xa không với dung sai vài ulp, nên 2.5 cho 3, -2.5 cho -3 và 1.005 cho 1.01
• Bỏ qua: General, date/time và elapsed time, khoa học, phân số, text, Boolean và giá trị lỗi
• Phạm vi trong HotXLS: kết quả công thức ngay khi tính; hằng được lưu như đã gán
• Engine XLSX: đặt FullPrecision trước Recalculate đầu tiên; setter Classic tự đánh dấu dirty lại mọi công thức
• Phiên bản: khớp Excel 16 ở cả hai engine kể từ v2.384.57; định dạng elapsed time được bảo vệ kể từ v2.384.9

HotXLS đọc, ghi và tính các workbook XLS và XLSX thuần nhất từ Delphi và C++Builder, gồm cả các tùy chọn tính toán workbook nói trong bài này. Chi tiết, các edition và bản dùng thử nằm trên trang HotXLS Delphi spreadsheet component