Nếu SUBTOTAL(109, ...) và SUBTOTAL(9, ...) trả về cùng một con số trên một workbook chứa hàng ẩn, một trong hai chắc chắn đang sai. HotXLS, thành phần bảng tính Excel gốc cho Delphi và C++Builder, hành xử đúng như vậy cho tới phiên bản 2.197.0, vì engine tính toán của nó không có cách nào để hỏi một worksheet xem một hàng cho trước có bị ẩn hay không
Triệu chứng này hiếm khi tới dưới dạng một báo cáo lỗi về mã công thức. Nó tới dưới dạng một sự không khớp: một tác vụ batch trên máy chủ tính ra một tổng, một người dùng mở cùng tệp đó trong Excel với một bộ lọc đang áp dụng, và hai con số lệch nhau đúng bằng tổng của những hàng bị lọc ra. Không ai nghi ngờ hàm tổng hợp, vì chuỗi công thức trong ô giống hệt nhau ở cả hai nơi. Sự khác biệt hoàn toàn nằm ở những gì bộ đánh giá được phép nhìn thấy
Vì sao SUBTOTAL 109 lại bao gồm cả hàng ẩn?
Vì trong hầu hết các thiết kế engine, lớp đánh giá một công thức không bao giờ biết gì về tính khả kiến của hàng. HotXLS là một trường hợp giáo khoa: engine tính toán trong lxCalc.pas tiếp cận giá trị ô qua một callback TXLSGetValue duy nhất trả lời bằng một giá trị cho bộ ba (sheet, row, column) và không gì khác. Tính khả kiến là một thuộc tính trình bày được lưu trên bản ghi hàng, và không phần nào của bản ghi đó đi xuống theo chuỗi lệnh gọi. Vì vậy engine chỉ có một đường tổng hợp duy nhất, và cả hai nửa của bảng mã hàm SUBTOTAL đều giải quyết về đường đó. Đây không phải là loại khiếm khuyết kiểu lỗi làm tròn: nó chính là toàn bộ lý do nửa thứ hai của bảng đó tồn tại. ECMA-376 Phần 1, được xuất bản dưới dạng ISO/IEC 29500-1, định nghĩa SUBTOTAL trong các định nghĩa hàm công thức của nó (§18.17.7) với một tham số đầu tiên chọn cả phép tổng hợp bên trong lẫn chính sách hàng ẩn. Mã 1 đến 11 ánh xạ tới AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, và VARP trong khi vẫn bao gồm các giá trị trên các hàng bị ẩn thủ công. Mã 101 đến 111 chọn cùng mười một phép tổng hợp đó và loại trừ chúng. Một người dùng gõ 109 thay vì 9 đang đưa ra một tuyên bố có chủ đích về dữ liệu ẩn, và một engine gộp chung sự phân biệt đó lại âm thầm phủ quyết tuyên bố đó
Các mã hàm ánh xạ tới đâu bên trong engine
HotXLS giải quyết tham số đầu tiên của SUBTOTAL trong CalcSubtotalFunc, hàm này chuẩn hóa mã 101 đến 111 xuống cùng các định danh hàm bên trong như mã 1 đến 11 rồi mới điều phối theo chính phép tổng hợp. Phần lớn họ hàm này chảy qua bộ tích lũy tăng dần ExcelSum, cái xử lý SUM, COUNT, COUNTA, MIN, MAX, và AVERAGE. Năm cái trong số đó thì không thể: STDEV, VAR, STDEVP, VARP, và PRODUCT cần một lượt xử lý dạng đóng (closed-form) trên dữ liệu, nên CalcSubtotalFunc định tuyến các mã bên trong 12, 46, 193, 194, và 183 tới một bộ rút gọn riêng, SubtotalReduceVariance. Sự phân tách đó là điều đầu tiên đáng lập bản đồ trước khi chạm vào bất cứ thứ gì, vì hai đường tổng hợp độc lập nghĩa là hai vòng lặp duyệt ô độc lập, và một bản sửa chỉ áp dụng cho một trong hai tạo ra kết quả tệ nhất có thể: SUBTOTAL(109, ...) tôn trọng bộ lọc trong khi SUBTOTAL(107, ...) trên cùng dải thì không. Đếm các vòng lặp trong HotXLS cho ra sáu vòng khi tính cả AGGREGATE, trải khắp việc đánh giá dải, thu thập dải thuần túy, và ba bộ rút gọn riêng biệt
Vì sao dùng một trường tạm thay vì sáu chữ ký mới?
Vì việc luồn một tham số mới qua sáu hàm duyệt ô, cộng với mọi thứ gọi tới chúng, là một thay đổi rộng lên một đường mã nóng chỉ vì một boolean duy nhất. HotXLS đã có tiền lệ cho cách thay thế: một trường tạm thời trên bộ tính toán, cùng tinh thần với trường tạm mà GetRangeInfo dùng để ghi lại khi nào một tham chiếu 3D giải quyết vào một workbook bên ngoài. Phiên bản 2.197.0 thêm một trường thứ hai như vậy. Engine nhận một kiểu callback mới, TXLSIsRowHidden, khai báo dưới dạng một hàm của (SheetIndex, row) trả về Boolean, được lưu trong FIsRowHidden, cùng một cờ tạm FIgnoreHiddenRows. Cờ này được bật ở đầu CalcSubtotalFunc khi mã hàm rơi vào khoảng 101 đến 111, và ở đầu CalcAggregateFunc cho các mã tùy chọn AGGREGATE chọn loại trừ hàng ẩn. Mỗi vòng lặp duyệt ô sau đó kiểm tra nó và bỏ qua một hàng khi nó được bật, mỗi vòng lặp chỉ thêm đúng một dòng
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Hai chi tiết trong đoạn mã bật cờ mang tính đúng đắn của toàn bộ cơ chế. Cờ được lưu lại và khôi phục thay vì chỉ đơn giản đặt rồi xóa, vì một tham số SUBTOTAL có thể chứa một biểu thức chạy phép đánh giá riêng của nó trong khi phép tổng hợp bên ngoài vẫn còn trên stack, và công việc lồng nhau đó không được kế thừa hay phá hủy cổng bên ngoài. Và việc khôi phục nằm trong một khối finally, vì CalcSubtotalFunc có nhiều đường thoát sớm cho các mã lỗi; một cờ bị bỏ lại ở trạng thái bật sau một lần trả về lỗi sẽ âm thầm làm hỏng công thức không liên quan tiếp theo trong thứ tự tính lại
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Phép kiểm tra Assigned chính là thứ giữ cho thay đổi này tương thích ngược. HotXLS mở rộng constructor của bộ tính toán với một tham số thứ ba mặc định là nil, nên bất kỳ đoạn mã nào xây dựng một TXLSCalculator với lệnh gọi hai tham số cũ vẫn biên dịch được và vẫn nhận hành vi bao gồm-hàng-ẩn cũ. Không có gì về API hiện có thay đổi hình dạng cả
Bit hàng ẩn thực sự tới từ đâu?
Từ worksheet, qua hai nguồn khác nhau, vì HotXLS mang hai engine workbook. Phía BIFF cũ trả lời từ TXLSRowInfoList.GetHidden, được tới qua TXLSWorkbook.GetRowHidden. Phía OOXML trả lời từ TXLSXWorksheet.GetRowHidden, được tới qua TXLSXWorkbook.GetCalcRowHidden. Cả hai đều được nối dây vào bộ tính toán tại thời điểm khởi tạo, cạnh callback giá trị ô mà chúng phản chiếu. Các quy ước về hàng chính là nơi loại cầu nối này thường bị sai, nên đáng nói rõ ra. Bộ tính toán trao cho callback một hàng đánh số từ 0, khớp với tọa độ mà TXLSGetValue đã dùng. Worksheet XLSX đánh khóa bản đồ hàng-ẩn của nó theo số hàng đánh từ 1, đúng như cách Excel đánh số hàng, cũng chính là điều mà property công khai RowHidden[ARow] lộ ra. Vì vậy cầu nối XLSX cộng thêm một trước khi tra cứu, còn cầu nối BIFF thì không, vì TXLSRowInfoList đã đánh từ 0 sẵn. Cả hai cầu nối đều coi một chỉ số sheet hay hàng nằm ngoài khoảng hợp lệ là khả kiến, nên một truy vấn ngoài phạm vi suy thoái về câu trả lời bao gồm-hàng-ẩn cũ thay vì làm mất dữ liệu
Điều gì thay đổi với các workbook đã lọc
Đây là trường hợp tạo ra các ticket hỗ trợ. Áp dụng một AutoFilter trong HotXLS qua ApplyAutoFilter đánh giá tiêu chí cột và ẩn mọi hàng dữ liệu không khớp, đúng chính xác những gì Excel làm khi một người dùng nhấp vào một dropdown lọc. Trước v2.197.0 những hàng ẩn đó vô hình với người dùng và hoàn toàn khả kiến với engine tính toán, nên một SUBTOTAL(109, ...) phía máy chủ báo cáo tổng chưa lọc. Giờ cùng lệnh gọi đó báo cáo tổng đã lọc
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Việc ẩn thủ công hoạt động theo cùng cách, vì RowHidden[ARow] := True chính là trạng thái mà bộ lọc ghi. Sự tương đương đó có chủ đích trong Excel và giờ cũng đúng trong HotXLS. Một hệ quả đáng một ghi chú trong bất kỳ tài liệu nào đi kèm các workbook bạn tạo ra: một tổng được tính bằng mã 109 là một con số phụ thuộc vào khung nhìn (view), nên một người nhận xóa bộ lọc sẽ thay đổi nó. Khi một báo cáo phải nêu một con số cố định bất kể người đọc làm gì với khung nhìn, mã 9 là lựa chọn đúng và luôn luôn là vậy. Bộ lọc, xác thực dữ liệu, và bảng được trình bày cùng nhau trong bài viết về xác thực dữ liệu, AutoFilter, và bảng. Vì việc ẩn hàng không chạm vào công thức nào cả, nó cũng tự nó không làm bẩn đồ thị phụ thuộc, điều này đáng biết nếu bạn dựa vào tính lại tăng dần trên đồ thị con bị bẩn để giữ các workbook lớn phản hồi nhanh
Mã tùy chọn AGGREGATE và một giới hạn vẫn còn để ngỏ
AGGREGATE là SUBTOTAL với một tham số chính sách thứ hai, và HotXLS xử lý nó trong CalcAggregateFunc. Tham số tùy chọn mã hóa các công tắc độc lập: liệu các lệnh gọi SUBTOTAL và AGGREGATE lồng nhau bên trong dải có bị bỏ qua hay không, liệu các giá trị trên hàng ẩn có bị bỏ qua hay không, và liệu các giá trị lỗi có bị nén lại thay vì lan truyền hay không. HotXLS bật cổng hàng-ẩn dùng chung cho các mã tùy chọn 2, 3, 6, và 7, và nén giá trị lỗi cho các mã tùy chọn 4 đến 7. Tham số mã hàm sau đó chọn phép tổng hợp đúng như SUBTOTAL làm, bao gồm cả việc định tuyến phương sai, độ lệch chuẩn, và tích số qua các bộ rút gọn riêng của chúng. Một khoảng trống đã được ghi tài liệu vẫn còn tồn tại, và tốt hơn là nói rõ ở đây thay vì phát hiện ra trong sản xuất: ngữ nghĩa bỏ-qua-SUBTOTAL-lồng-nhau gắn với các mã tùy chọn thấp chưa được cài đặt trong HotXLS. Phát hiện một SUBTOTAL lồng nhau bên trong một dải được tham chiếu đòi hỏi đánh dấu trạng thái đệ quy của bộ đánh giá để một phép tổng hợp bên trong có thể tự thông báo cho phép tổng hợp bên ngoài, đây là một thay đổi lớn hơn nhiều so với cổng hàng-ẩn. Trong thực tế, mức độ phơi bày là nhỏ, vì các workbook thực tế hầu như luôn đặt các công thức SUBTOTAL nằm ngoài các dải mà các công thức SUBTOTAL khác tổng hợp trên đó. Nếu bộ tạo của bạn thực sự xây dựng các dải tổng hợp chồng lấn, đừng dựa vào các mã tùy chọn thấp để khử trùng lặp chúng
Lớp bảo vệ số lượng tham số đi kèm theo đó
Phiên bản 2.197.0 cũng đóng lại một khoảng trống xác thực trong cùng bộ điều phối đó, và lý do thiết kế giống hệt lý do đã thúc đẩy trường tạm: đặt phép kiểm tra ở nơi nó có thể được viết đúng một lần. Khoảng 280 phần thân hàm tích hợp sẵn mỗi cái tự kiểm tra số lượng tham số của riêng nó so với Item.ChildCount, điều này không để lại ranh giới nhất quán nào cho trường hợp quá nhiều tham số. Một lệnh gọi như =SIN(1,2) tới được một phần thân hàm kiểm tra tham số đầu tiên của nó, bỏ qua phần dư thừa, và trả về một con số hợp lý trong khi Excel trả về #VALUE!. HotXLS đã lưu sẵn số lượng tham số được khai báo của mọi hàm tích hợp trong registry hàm của nó, được lộ ra dưới dạng THashFunc.ArgsCnt với -1 đánh dấu một hàm biến đổi số tham số (variadic) như SUM, IF, hay CONCAT. Phiên bản 2.197.0 chuyển tiếp điều đó qua một property mới TXLSFormula.FuncArgsCntByPtg và thêm một cổng ở đầu GetValueItemFunc, bộ điều phối chính
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Lớp bảo vệ này từ chối quá nhiều tham số và cố tình không nói gì về quá ít tham số. Bỏ qua một tham số tùy chọn ở cuối là hợp lệ trong Excel đối với VLOOKUP, SUBSTITUTE, và một danh sách dài các hàm khác, nên một phép kiểm tra đối xứng sẽ làm hỏng các công thức đúng chỉ để bắt các công thức sai. Các định danh không xác định được báo cáo là variadic và bỏ qua cổng này hoàn toàn, đó chính là điều giữ cho các hàm do người dùng định nghĩa không bị vướng vào; nếu bạn đăng ký hàm của riêng mình, hành vi được mô tả trong hướng dẫn về engine công thức và hàm tùy chỉnh không bị ảnh hưởng. Tập trung hóa trường hợp quá-ít-tham-số là một công việc riêng biệt, vì mỗi phần thân trong số 280 phần thân đó có ngữ nghĩa mã lỗi riêng của nó và chúng phải được xem xét từng cái một thay vì giả định
Engine tính toán được mô tả ở đây, cả hai mặt tiền workbook, và các API AutoFilter cùng khả kiến hàng nuôi nó là một phần của thành phần bảng tính Delphi HotXLS, được cung cấp cùng mã nguồn đầy đủ cho Delphi và C++Builder và không yêu cầu cài đặt Excel trên máy chạy nó