HotXLS, component bảng tính Excel native cho Delphi và C++Builder, đã phát hành hai bản sửa AGGREGATE có liên quan với nhau trong tháng 9 năm 2026. Phiên bản 2.382.0 sửa lại tham số options để các mã 1/3/5/7 bỏ qua dòng ẩn, 2/3/6/7 bỏ qua giá trị lỗi, và 0 tới 3 bỏ qua các ô SUBTOTAL cùng AGGREGATE lồng bên trong, đúng như Microsoft ghi trong tài liệu. Phiên bản 2.382.3 sau đó chặn những cờ lựa chọn ấy rò rỉ vào việc đánh giá chính những ô mà hàm tham chiếu tới. Khiếm khuyết thứ nhất đáng xấu hổ theo đúng cái cách mà các bug sao chép bảng biểu luôn đáng xấu hổ: các vị trí bit bị hoán đổi, nên mọi công thức dùng mã options khác không đều nhận một chính sách mà tác giả của nó không hề yêu cầu. Cái thứ hai thú vị hơn, vì nó là một hình dạng bạn sẽ gặp ở bất kỳ bộ đánh giá nào dùng một trường tạm thời để truyền ngữ cảnh vào một phép duyệt đệ quy. Một phép tổng hợp bên ngoài giương một cờ lên, duyệt một vùng, và kéo ra một ô có công thức chưa được tính. Công thức đó chạy trên cùng bộ tính, nhìn thấy đúng cái cờ đang giương, và lặng lẽ tổng hợp nhầm những dòng, tạo ra một con số lệch đi một lượng mà không ai giải thích được chỉ từ nội dung công thức
Các tùy chọn AGGREGATE từ 0 tới 7 thật ra chọn cái gì?
Tham số options của AGGREGATE là một ma trận ba bit, và ba bit đó độc lập với nhau. Bit 0 (giá trị 1) nghĩa là bỏ qua dòng ẩn, bit 1 (giá trị 2) nghĩa là bỏ qua giá trị lỗi, còn bit 2 (giá trị 4) nghĩa là thôi bỏ qua các ô SUBTOTAL và AGGREGATE lồng nhau, vì việc bỏ qua chúng là mặc định cho các mã thấp. Có hai điều ở đây rất dễ hiểu ngược. Bit dòng ẩn là bit thấp, không phải bit giữa, nên AGGREGATE(9,1,...) là dạng tổng đã lọc còn AGGREGATE(9,2,...) là dạng chịu được lỗi. Và chính sách với aggregate lồng thì ngược so với hai bit kia: chỉ các mã 4 tới 7 mới coi một ô mà công thức của chính nó là SUBTOTAL hay AGGREGATE như một giá trị bình thường. ECMA-376 Part 1 §18.17.7 định nghĩa SUBTOTAL với đúng cách chia bao gồm hay loại trừ dòng ẩn qua các mã 1-11 và 101-111, còn AGGREGATE, được lưu trong tệp OOXML dưới tiền tố _xlfn., tổng quát hóa cách chia đó thành tham số options, nên bảng mà Microsoft công bố cho hàm AGGREGATE là khế ước mà một engine phải đáp ứng chứ không phải một tiện ích
| Tùy chọn | Dòng ẩn | Giá trị lỗi | SUBTOTAL / AGGREGATE lồng |
|---|---|---|---|
| 0 | được tính | được lan truyền | bị bỏ qua |
| 1 | bị bỏ qua | được lan truyền | bị bỏ qua |
| 2 | được tính | bị bỏ qua | bị bỏ qua |
| 3 | bị bỏ qua | bị bỏ qua | bị bỏ qua |
| 4 | được tính | được lan truyền | được tính |
| 5 | bị bỏ qua | được lan truyền | được tính |
| 6 | được tính | bị bỏ qua | được tính |
| 7 | bị bỏ qua | bị bỏ qua | được tính |
Vì sao HotXLS lại có các tùy chọn AGGREGATE ngược?
Vì TXLSCalculator.CalcAggregateFunc ban đầu được viết từ một bản diễn giải bảng chứ không phải từ chính cái bảng. Nó tính ignoreErrors := (optCode >= 4) and (optCode <= 7) và giương cổng bỏ qua dòng ẩn cho các mã 2, 3, 6 và 7, trong khi chính sách với aggregate lồng hoàn toàn không được cài. Bài viết trước về dòng ẩn trong SUBTOTAL và AGGREGATE có liệt kê khoảng trống đó như một giới hạn còn bỏ ngỏ và mô tả cách ánh xạ cũ đúng như nó được phát hành khi ấy; mô tả đó đúng với đoạn mã và sai với Excel, và không ai để ý trong một thời gian dài vì hai chính sách mà phần lớn người ta kết hợp, ẩn cộng lỗi, đều rơi vào mã 3 và 7 dưới cả hai bảng. Chỉ những mã một bit mới phơi ra cú hoán đổi: AGGREGATE(9,1,A1:A4) trả về tổng chưa lọc, còn AGGREGATE(9,2,...) bỏ qua dòng ẩn trong khi vẫn lan truyền #DIV/0!. Khiếm khuyết lộ ra từ một lượt soát tĩnh lxCalc.pas, được ghi thành HXLS-008 trong sổ vấn đề đã biết của dự án, chứ không đến từ tệp của khách hàng, và điều đó nói lên các mã một bit hiếm khi xuất hiện trong workbook sản xuất đến mức nào. Phiên bản 2.382.0 viết lại phần giải mã thành ba phép kiểm tra thuộc tập hợp và thêm một cổng thứ hai cho chính sách lồng, nối qua callback TXLSIsSubtotalCell mới mà workbook cung cấp bên cạnh TXLSIsRowHidden
// TXLSCalculator.CalcAggregateFunc, v2.382.3 form
if (optCode < 0) or (optCode > 7) then
begin
Result := lxErrorValue; // Excel từ chối các mã nằm ngoài 0..7
Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
// ... ánh xạ function_num sang iftab bên trong, duyệt ref1..refN ...
finally
FIgnoreHiddenRows := prevIgnoreHidden;
FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;Hãy để ý rằng hai cờ được gán vô điều kiện thay vì chỉ được bật khi tùy chọn yêu cầu. Bản v2.382.0 vẫn dùng if ... then FIgnoreHiddenRows := True, nghĩa là một AGGREGATE với mã 4 lồng bên trong một SUBTOTAL(109, ...) sẽ thừa hưởng cổng bỏ qua dòng ẩn của lớp ngoài thay vì xóa nó đi. Gán giá trị đã giải mã ngay khi vào và khôi phục giá trị trước đó trong khối finally khiến mỗi lệnh gọi AGGREGATE tự sở hữu chính sách của nó trong suốt lượt duyệt và không hơn. Phiên bản 2.382.0 cũng làm cho dạng mảng trở nên trung thực: khi một đối số đánh giá ra một Variant array một hoặc hai chiều, CalcAggregateFunc giờ duyệt từng phần tử và áp chính sách lỗi theo từng phần tử, trong khi đoạn mã cũ chỉ kiểm tra một double NaN và nếu không thì giao nguyên mảng cho ExcelSum
Vì sao một AGGREGATE bên ngoài lại rò rỉ vào chính những công thức nó tham chiếu?
Vì FIgnoreHiddenRows và FIgnoreSubtotalCells là các trường trên bộ tính, mà bộ tính thì được chia sẻ cho mọi công thức được đánh giá trong một lần tính lại. Hai cổng này được thiết kế thành trường tạm đúng để sáu vòng lặp duyệt ô có thể tra cứu chúng mà không phải luồn một tham số qua mọi chữ ký hàm, và thiết kế đó vững chừng nào mọi thứ chạy trong lúc một cổng đang giương đều thuộc về phép tổng hợp đã giương nó. Giả định ấy vỡ ở đúng một điểm: FGetValue. Khi một bộ duyệt hỏi workbook giá trị của một ô mà ô đó chứa công thức chưa có kết quả cache, workbook biên dịch công thức rồi đánh giá ngay tại chỗ, trên cùng một TXLSCalculator, với các cổng của lớp ngoài vẫn đang giương. Fixture hồi quy trong HotXLS.WorkbookApiTests.pas cho thấy lỗi này chỉ với bốn ô. A1 chứa 10, A2 chứa 20 trên một dòng bị ẩn, A3 chứa =1/0, và A4 chứa =SUBTOTAL(9,A1:A2), với giá trị đúng là 30. Giờ đánh giá =AGGREGATE(9,7,A1:A4): bỏ qua dòng ẩn, bỏ qua lỗi, tính subtotal lồng như một giá trị. Excel trả về 10 + 30 = 40. Với A4 chưa được cache, engine trước 2.382.3 giương cổng bỏ qua dòng ẩn, duyệt tới A4, kích hoạt việc đánh giá nó, và CalcSubtotalFunc cho mã 9 thừa hưởng cổng đang giương, vì nó chỉ bật cờ cho các mã 101 tới 111 và không bao giờ xóa nó đi. A4 đánh giá ra 10 thay vì 30, và tổng bên ngoài trả về 20. Không công thức nào trong hai công thức ấy nhắc tới dòng ẩn trên con đường đã tạo ra con số sai
Cổng aggregate lồng cũng rò rỉ theo đúng cách đó nhưng ở chiều ngược lại. Với các mã 0 tới 3, FIgnoreSubtotalCells được giương lên, và bộ duyệt vùng tổng quát trong GetValueItemRange tôn trọng nó, nên một tiền đề có công thức =SUM(B1:B3) sẽ âm thầm bỏ B2 nếu B2 tình cờ chứa một SUBTOTAL. Tệ hơn, CalcSubtotalFunc đặt FIgnoreSubtotalCells về False khi thoát thay vì khôi phục giá trị trước đó, nên một tiền đề SUBTOTAL chưa cache bị chạm tới giữa lượt duyệt sẽ làm tắt cổng của lớp ngoài cho mọi ô sau nó. Sổ vấn đề đã biết của dự án ghi việc này dưới mã HXLS-008 là rò rỉ trạng thái lựa chọn lồng nhau, và đó là cái tên đúng cho cả nhóm lỗi này: một cờ tạm toàn cục đúng với khung đã đặt nó và sai với mọi khung thừa hưởng nó
AggregateGetCellValue và AggregateGetItemValue cách ly phép duyệt thế nào
Bản sửa trong v2.382.3 đặt một ranh giới quanh mọi điểm mà AGGREGATE đọc một giá trị mà nó không tự tính ra. TXLSCalculator.AggregateGetCellValue bọc lệnh gọi FGetValue thô: nó lưu cả hai cờ, xóa chúng đi, thực hiện việc lấy giá trị, rồi khôi phục chúng trong một khối finally. Phép tổng hợp bên ngoài vẫn áp chính sách của chính nó lên ô vừa lấy, vì các phép kiểm tra dòng ẩn và ô lồng diễn ra trong bộ duyệt quanh chỗ lấy giá trị, nhưng bản thân công thức tiền đề thì chạy mà không có chính sách nào cả, đúng như Excel làm
function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
var Value: Variant; var OutOfRange: Boolean): Integer;
var
Hidden, Nested: Boolean;
begin
Hidden := FIgnoreHiddenRows;
Nested := FIgnoreSubtotalCells;
FIgnoreHiddenRows := False; // công thức tiền đề tự sở hữu chính sách của nó
FIgnoreSubtotalCells := False;
try
Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
finally
FIgnoreHiddenRows := Hidden;
FIgnoreSubtotalCells := Nested;
end;
end;AggregateGetItemValue làm điều tương tự cho các đối số không phải vùng, và nó phải làm nhiều hơn việc xóa cờ, vì một đối số như A1:A4/(B1:B4-20) là một mảng được tính mà hình dạng các phần tử của nó phải sống sót. Lớp bọc này hiện thực hóa một vùng thuần thành một Variant array hai chiều qua AggregateGetCellValue, ánh xạ một ô trả về mã lỗi thành VarAsError để chính sách lỗi vẫn áp được theo từng phần tử, và nó đệ quy qua các node toán tử hai ngôi và một ngôi (SA_ADD, SA_DIV, SA_UNARMINUS, cùng những node còn lại) bằng ApplyArrayBinaryOp và ApplyArrayUnaryOp; mọi thứ khác rơi xuống GetValueItem thông thường. Hai chốt canh nằm trước bước hiện thực hóa: một vùng lớn hơn EffectiveFormulaArrayMemoryLimit trả về lxErrorResourceLimit, còn một vùng trải nhiều sheet hay bị đảo ngược trả về #VALUE!. Mã giới hạn tài nguyên cố ý không bị coi là lỗi ô có thể bỏ qua ngay cả dưới các tùy chọn 2/3/6/7, vì một engine nuốt mất chính tín hiệu hết bộ nhớ của nó chỉ vì người dùng yêu cầu bỏ qua #N/A thì đang nói dối. Cả ba bộ duyệt AGGREGATE — AggregateCollectRange cho họ SUM, AggregateReduceVariance cho STDEV, VAR và PRODUCT, và AggregateReduceWithK cho MEDIAN cùng các dạng phân vị — đều được chuyển từ FGetValue và GetValueItem sang hai lớp bọc này, và mỗi bộ có thêm phép kiểm tra ô lồng qua FIsSubtotalCell
AGGREGATE trả về lỗi nào khi nó không bỏ qua lỗi?
Lỗi gốc, kể từ v2.382.3. Phiên bản 2.382.0 phát hiện ô lỗi đúng nhưng gộp mọi ô lỗi như vậy thành lxErrorValue, nên AGGREGATE(9,4,A1:A3) trên một ô #DIV/0! trả về #VALUE!, trong khi Excel lan truyền nguyên trạng lỗi đầu tiên nó gặp. Helper thay thế AggregateErrorCode ánh xạ một Variant sang mã lxError* tương ứng, bất kể Variant đó là một varError thật hay một trong bảy chuỗi lỗi, và AggregateValueIsError giờ chỉ còn là phép kiểm tra kết quả khác không. Mỗi bộ duyệt ghi lại mã lỗi đầu tiên nó thấy và trả về mã đó, nghĩa là một ô có công thức chưa từng được tính, và do đó lỗi của nó đến dưới dạng mã trả về từ FGetValue thay vì một Variant đã cache, cũng lan truyền y như một ô đã cache. Hai hàm đếm được xử lý riêng bên trong AggregateCollectRange, và cách xử lý đó khớp với SUBTOTAL chứ không phải SUM. Với hàm bên trong số 0, COUNT, một ô lỗi không bao giờ được đếm và không bao giờ được lan truyền bất kể mã options, vì COUNT chỉ đếm số. Với hàm bên trong số 169, COUNTA, một ô lỗi là một giá trị khác rỗng và được tính là 1 trừ khi mã options bỏ qua lỗi, khi đó nó bị bỏ. Sự bất đối xứng đó đúng là cách Excel đối xử với COUNT và COUNTA cả bên ngoài AGGREGATE, và nó thuộc kiểu chi tiết mà một quy tắc chung chung kiểu “nếu lỗi thì lan truyền” âm thầm làm sai
Ma trận hồi quy tám tùy chọn kiểm chứng điều gì
Fixture mô tả ở trên được chạy như một ma trận đầy đủ trong AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: với mỗi mã options từ 0 tới 7, nó đánh giá cả dạng SUM lẫn dạng MEDIAN trên A1:A4 rồi đối chiếu kết quả với một kỳ vọng được suy ra bằng tay. Các mã 0, 1, 4 và 5 phải lan truyền #DIV/0! từ A3, vì không mã nào trong số đó bỏ qua lỗi. Mã 2 cho SUM 30 và MEDIAN 15, từ 10 và 20 với A4 lồng bên trong bị bỏ. Mã 3 cho 10 và 10. Mã 6 cho 60 và 20, vì con số 30 trong A4 giờ được tính. Mã 7 cho 40 và 20, đúng trường hợp từng trả về 20 trước bản sửa rò rỉ. Lượt chạy nghiệm thu rộng hơn được ghi trong sổ vấn đề đã biết bao phủ cả mười chín số hàm với cả tám mã, với mọi tiền đề đều ở trạng thái đã cache lẫn chưa cache, tổng cộng 304 tình huống trên Win32 và Win64
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Data');
Sheet.Cells[1, 1].Value := 10;
Sheet.Cells[2, 1].Value := 20;
Sheet.Cells[3, 1].Formula := '=1/0';
Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)'; // subtotal nhóm = 30
Sheet.RowHidden[2] := True;
Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0! bỏ dòng ẩn, lỗi vẫn lan truyền
Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10 bỏ ẩn + lỗi + lồng
Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60 chỉ bỏ lỗi
Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40 từng là 20 trước v2.382.3
Book.Recalculate;
Book.SaveAs('aggregate-options.xlsx');
finally
Book.Free;
end;
end;Ranh giới vẫn còn ở đâu
Ba giới hạn đáng biết trước khi bạn xây tiếp trên đây. Thứ nhất, vị từ nhận diện aggregate lồng là vị từ văn bản. TXLSXWorkbook.GetCalcIsSubtotalCell cùng bản song sinh của nó trong engine classic trả về True khi công thức của một ô bắt đầu bằng SUBTOTAL(, AGGREGATE(, hay _xlfn.AGGREGATE(, có hay không có dấu bằng ở đầu, nên một công thức như =IF(C1,SUBTOTAL(9,B1:B9),0) hay =SUBTOTAL(9,B1:B9)*2 không được nhận ra là lồng và sẽ bị các mã 0 tới 3 đếm hai lần, trong khi Excel sẽ bỏ qua nó; một bộ sinh tệp phát ra các subtotal đã tính nên giữ lệnh gọi tổng hợp ở ngay đầu công thức. Thứ hai, lớp cách ly chỉ nằm trong ba bộ duyệt AGGREGATE. CalcSubtotalFunc vẫn duyệt qua GetValueItemRange, CollectRangeValues và SubtotalReduceVariance, những hàm gọi FGetValue trực tiếp, nên một SUBTOTAL(109, ...) có vùng chứa một công thức tiền đề chưa cache vẫn có thể truyền cổng bỏ qua dòng ẩn của nó vào chính tiền đề đó. Một lần Recalculate đầy đủ đánh giá các tiền đề trước các ô phụ thuộc, nên đường đã cache được chọn và cổng không bao giờ bị thừa hưởng; rủi ro chỉ giới hạn ở việc đánh giá tùy hứng qua Calculate và ở những workbook được nạp mà không có giá trị cache, và nếu bạn dựa vào tính lại tăng dần trên đồ thị phụ thuộc để giữ cho những mô hình lớn phản hồi nhanh, chính bảo đảm về thứ tự đó là thứ giữ cho rò rỉ này nằm im. Thứ ba, cả hai cổng đều được đặt điều kiện theo Assigned(FIsRowHidden) và Assigned(FIsSubtotalCell). Cả hai facade workbook đều nối các callback trong constructor của chúng, nhưng đoạn mã tự dựng một TXLSCalculator bằng tay chỉ với hai tham số gốc sẽ nhận hành vi cũ là tính hết mọi thứ cho mọi mã options, trong im lặng. Khi một tổng trông sai mà nội dung công thức trông đúng, truy vết từng bước quá trình đánh giá là cách nhanh nhất để thấy liệu một tiền đề đã bị đánh giá dưới một cổng thừa hưởng hay một callback đơn giản là chưa từng được gắn vào
Engine tính toán mô tả ở đây, bộ giải mã tùy chọn, các lớp bọc lấy giá trị đã cách ly, và ma trận hồi quy chốt lại chúng đều được phát hành kèm mã nguồn trong component bảng tính HotXLS cho Delphi, thứ đọc, ghi và tính lại các workbook XLS, XLSX và ODS trong Delphi và C++Builder mà không cần cài Excel