HotXLS Delphi Component đánh giá =1<2<3 thành FALSE, đúng câu trả lời mà Excel 16 đưa ra, vì từ v2.384.3 parser formula của nó gộp các toán tử so sánh từ trái sang phải: 1<2 thành TRUE, và TRUE<3 là FALSE vì boolean xếp trên mọi số. Cùng release đó làm cho một operand trống bằng với cả 0 lẫn "", và cho phép SUMIF kéo giãn một vùng tính tổng một ô theo hình dáng của vùng tiêu chí. Mỗi cái đều trông như chuyện vặt cho tới khi một workbook tính trong Delphi mâu thuẫn với chính workbook đó mở trong Excel
Sự mâu thuẫn thường bắt đầu từ một formula ai đó viết theo trực giác. Người ta gõ =0<B2<100 để kiểm tra một số lượng có nằm trong khoảng không, Excel lặng lẽ trả FALSE cho mọi dòng, và sheet được ship kèm sẵn bug đó. Một calculation engine không có quyền sửa ý định của người dùng; việc của nó là sinh ra giá trị mà Excel sẽ sinh, để kết quả cached mà HotXLS ghi vào file khớp với những gì Excel hiện sau một lần tính lại. Trước v2.384.3, HotXLS trả TRUE cho phép kiểm khoảng đó trên mọi dòng, sai theo chiều ngược lại, và một báo cáo sinh trên server sẽ mâu thuẫn với chính báo cáo đó khi mở trên desktop
Vì sao =1<2<3 trả về FALSE trong Excel?
Excel trả FALSE vì nó đọc một chuỗi so sánh như (1<2)<3, và TRUE bên trong rồi thua cuộc xếp hạng kiểu trước số 3. Parser cũ của HotXLS đọc cùng văn bản đó thành 1<(2<3): TXLSSyntax.Parse_expr trong lxFormula.pas parse một operand, thấy một token so sánh, và đệ quy vào Parse_expr cho vế phải, điều khiến toán tử kết bên phải. Kết quả là 1<TRUE, mà số thì dưới boolean, nên kết quả thu được là TRUE. Sai sót mang tính đối xứng: =3>2>1 là TRUE trong Excel và đã là FALSE trong HotXLS, còn =1=1=TRUE là TRUE trong Excel và đã là FALSE trước bản sửa. Regression CalculateFormula_ComparisonChainsFoldLeftToRight ghim bảy formula như vậy vào các giá trị mà Excel 16 trả về, và chạy từng cái qua cả hai kiến trúc engine, classic TXLSWorkbook và XLSX-native TXLSXWorkbook, bằng method Calculate đã trình bày trong tổng quan về formula engine của HotXLS
const
Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
'=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
// Excel 16 trả về: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create;
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculate đánh giá trên sheet active và
// trả Null khi workbook chẳng có sheet nào
Xlsx.Sheets.Add('Data');
for i := 0 to High(Formulas) do
Writeln(Formulas[i], ' classic=', VarToStr(Classic.Calculate(Formulas[i])),
' xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
finally
Xlsx.Free;
end;
end;
Bản sửa biến Parse_expr thành một vòng lặp cùng hình dạng với Parse_expr1 vốn đã dùng cho +, - và &. Nó parse operand đầu tiên bằng Parse_expr1, và trong khi token kế tiếp là một trong =, <>, <, >, <= hay >=, nó tạo một node so sánh, gắn kết quả trái tích lũy làm con đầu tiên, parse operand kế tiếp bằng Parse_expr1 thay vì Parse_expr, và đưa node mới thành kết quả trái cho vòng sau. Hai chi tiết dễ làm sai khi chuyển đệ quy thành vòng lặp, và cả hai đều nằm trong ghi chú của maintainer: node tích lũy phải được bàn giao (lChild := Item; Item := nil) đúng thứ tự đó, và đường lỗi phải Exit sau khi free nốt node dựng dở thay vì rơi ra khỏi vòng lặp và trả về một cây lơ lửng
HotXLS xếp hạng số, văn bản và boolean trong phép so sánh thế nào?
HotXLS xếp hạng các kiểu lẫn loại đúng như Excel: mọi số nhỏ hơn mọi giá trị văn bản, và mọi giá trị văn bản nhỏ hơn mọi boolean. TXLSCalculator.CompareVariants trong lxCalc.pas phân loại cả hai operand bằng GetRetValueType vào enumeration TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), và khi hai lớp khác nhau nó đơn giản so ordinal của chúng, nên thứ tự khai báo của enum đó chính là luật liên kiểu. Trong cùng một lớp, phép so sánh là phép tự nhiên, với một nét riêng kiểu Excel cho văn bản: cả hai string đi qua lxUpperCase trước, nên ="abc"="ABC" là TRUE. Chính xếp hạng này là lý do không thể suy luận về kết quả của chuỗi nếu thiếu nó. TRUE<3 không phải là ép TRUE về 1, mà là một boolean đem so với một số, và boolean thắng. Ngày tháng là serial number đối với engine (varDate được phân loại thành xlNumberValue), nên một ngày luôn dưới mọi văn bản, kể cả văn bản trông tưởng là ngày
Một ô trống bằng với gì trong phép so sánh?
Một ô trống dùng làm operand so sánh thì bằng 0 khi vế kia là số, bằng "" khi vế kia là văn bản, và từ v2.384.53 bằng FALSE khi vế kia là giá trị logic, nên với A1 rỗng, =A1=0, =A1="" và =A1=FALSE đều TRUE. TXLSCalculator.CompareVarValues, thứ phục vụ cả sáu toán tử so sánh, thay thế ô trống trước khi gọi CompareVariants: nếu đúng một operand là Null, nó trở thành WideString('') khi partner là string, False khi partner là boolean, và 0 trong các trường hợp còn lại. Hai ô trống vẫn so bằng nhau mà không cần thay thế. Đường arithmetic từ trước đã biến ô trống thành 0, đó là lý do =A1+1 cho 1, nhưng CompareVariants giữ Null như một hạng thấp nhất riêng của nó, dưới mọi số, và các toán tử so sánh dùng thẳng hạng đó
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['B1', 'B1'].Value := 5; // A1 được cố ý để trống
Writeln(VarToStr(Wb.Calculate('=A1=0'))); // True
Writeln(VarToStr(Wb.Calculate('=A1=""'))); // True
Writeln(VarToStr(Wb.Calculate('=A1<B1'))); // True: ô trống được so như 0
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False; True trước v2.384.3
end;
Dòng cuối là dòng gây đau nhất trong thực tế. Dưới hạng cũ, một ô trống nhỏ hơn mọi số, kể cả số âm, nên =IF(A1<0,"overdrawn","ok") dán nhãn vỡ nợ cho mọi ô số dư rỗng, còn =A1=0 là FALSE với một ô mà bất kỳ người dùng nào cũng sẽ gọi là số 0. Một biên còn sót lại sau v2.384.3: phép thay thế chỉ chọn giữa 0 và chuỗi rỗng, nên một ô trống đem so với boolean trở thành 0, thứ xếp dưới cả TRUE lẫn FALSE, và =A1=FALSE trên A1 rỗng đánh giá thành FALSE. Từ HotXLS 2.384.53, ô trống so với giá trị logic được coi là FALSE trong cả hai engine XLS lẫn XLSX, đúng như Excel: với A1 rỗng, =A1=FALSE và =A1<TRUE trả TRUE còn =A1=TRUE trả FALSE. Điều đó cũng có nghĩa là phép so sánh không thể phân biệt ô trống với FALSE, dù trong Excel hay HotXLS; khi một sheet cần sự phân biệt đó, hãy test bằng ISBLANK hay =A1=""
Vì sao SUMIF với vùng tính tổng một ô trả về 0?
SUMIF trả về 0 vì HotXLS chặn vòng lặp ở hai vùng nào nhỏ hơn, trong khi Excel giữ hình dáng của vùng tiêu chí và chỉ mượn vùng tính tổng ở ô trên-trái của nó. =SUMIF(A1:A10,">5",B1) vì thế nghĩa là B1:B10 trong Excel, một sự tiện lợi mà nhiều template dựng tay dựa vào. Worker dùng chung TXLSCalculator.GetValueItemRange2 từng thu nhỏ số dòng và số cột của mình về của vùng giá trị, điều khiến ví dụ còn lại đúng một phép so A1 với B1. v2.384.3 gỡ cái chặn đó: vòng lặp giờ đi qua vùng tiêu chí và đọc mỗi giá trị tại cùng offset tính từ góc trên-trái của vùng tính tổng. Vì CalcSumIF và CalcAverageIF đều gọi worker đó, AVERAGEIF nhận cùng phép nới rộng, và một vùng tính tổng lớn hơn vùng tiêu chí bị cắt về hình dáng tiêu chí vì cùng lý do. Tham số tiêu chí ở giữa là tham số hạng giá trị còn hai tham số ngoài là hạng tham chiếu, sự phân biệt đã trình bày trong bài về implicit intersection và các lớp tham số
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Sales');
for Row := 1 to 10 do
begin
Sheet.Cells[Row, 1].Value := Row; // cột tiêu chí: 1..10
Sheet.Cells[Row, 2].Value := Row * 100; // số tiền: 100..1000
end;
Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)'; // vùng tính tổng một ô
Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // vùng tính tổng tường minh
if Book.Recalculate = lxOk then
// D1 lẫn D2 đều là 4000 (600+700+800+900+1000); D1 là 0 trước v2.384.3
Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
finally
Book.Free;
end;
end;
INDIRECT và YEARFRAC: hai chỉnh sửa êm thấm hơn
INDIRECT giờ tôn trọng tham số thứ hai của mình, và văn bản đứng sau một tham chiếu hợp lệ là một lỗi thay vì bị bỏ qua. Với a1 là FALSE, văn bản được parse như R1C1 tuyệt đối, nên =INDIRECT("R2C3",FALSE) đọc C2; code cũ bỏ qua cờ, đọc “R2” thành cột R, dòng 2, và lặng lẽ trả về ô sai. Cờ được dispatch theo kiểu variant của nó (boolean, số hay văn bản) vì ép thẳng một variant string về Double sẽ ném exception. Văn bản R1C1 tương đối như R[1]C[1] trả #REF!, vì INDIRECT không có gốc ô formula nào để phân giải nó, và văn bản A1 có ký tự đuôi, "B2 junk", cũng trả #REF!. YEARFRAC với basis 0 giờ áp các luật NASD cho ngày cuối tháng Hai mà DAYS360 đã cài sẵn: khi cả hai ngày đều là ngày cuối cùng của tháng Hai thì ngày kết thúc thành 30, rồi một ngày bắt đầu rơi vào ngày cuối tháng Hai cũng thành 30. Từ 2024-02-29 tới 2025-02-28, số đếm giờ là 360 ngày, một phân số đúng bằng 1, trong khi Days360US trước đây đếm 359
Các bản sửa này đảm bảo điều gì, và bài học là gì?
Hành vi chuỗi so sánh được đảm bảo bằng một test so cả hai engine với các giá trị đo được trong Excel 16, và test đó tồn tại vì mô tả đầu tiên về bản sửa là sai. Release note v2.384.3 ban đầu nói rằng việc gộp trái-sang-phải làm =1<2<3 thành TRUE, chính xác là thứ mà parser kết-bên-phải cũ sinh ra và ngược với những gì cả Excel lẫn code mới trả về. Chẳng ai từng đánh giá ví dụ đó; nó được viết theo trực giác “1 nhỏ hơn 2, mà 2 nhỏ hơn 3”. Release note được sửa và test bảy formula được thêm trong một commit nối tiếp, và luật rút ra từ đó áp dụng cho bất kỳ ai viết tài liệu về ngữ nghĩa spreadsheet: hãy chạy ví dụ trong Excel trước khi ghi giá trị kỳ vọng xuống. Phép thay thế operand trống và phép nới vùng SUMIF theo cùng hành vi Excel, kể cả ca trống-so-với-boolean từ v2.384.53, còn các aggregate có điều kiện cũng phải bỏ qua dòng đã lọc hay ẩn thì theo các luật riêng trong bài về dòng ẩn của SUBTOTAL và AGGREGATE
HotXLS là một spreadsheet component Delphi và C++Builder nguyên bản, đọc, tính lại và ghi XLS, XLSX, ODS và CSV mà không cần cài Excel, và các luật so sánh, ô trống và SUMIF mô tả trong bài nằm trong calculation engine mà cả hai kiến trúc workbook dùng chung. Danh sách hàm đầy đủ và các tùy chọn licensing nằm trên trang sản phẩm HotXLS Delphi spreadsheet component