HotXLS, thư viện Excel gốc cho Delphi và C++Builder, thực hiện việc tự động tính toán lại công thức lũy tiến thông qua hàm TXLSXWorkbook.Recalculate. Lệnh gọi đầu tiên sẽ xây dựng đồ thị phụ thuộc công thức và đánh giá mọi ô công thức; mọi lệnh gọi tiếp theo chỉ tính toán lại các ô bị ảnh hưởng bởi việc thay đổi giá trị kể từ lượt xử lý trước, theo thứ tự topo, trong một lượt quét duy nhất với chi phí xử lý tỷ lệ thuận với số lượng ô bị thay đổi (dirty cells) chứ không phụ thuộc vào kích thước của sổ làm việc
Quyết định thiết kế đó tạo ra sự khác biệt giữa một mô hình tài chính phản hồi các chỉnh sửa giả định trong vài mili giây và một mô hình bị treo trong nhiều giây. Nếu bạn tạo ra các báo cáo nơi một số ít ô đầu vào cung cấp dữ liệu cho hàng ngàn công thức ở hạ nguồn, phần còn lại của bài viết này sẽ giải thích cơ chế hoạt động của đồ thị, các hàm không tham gia vào quá trình lũy tiến, và cách các tham chiếu vòng (circular references) được báo cáo thay vì lặp vô hạn
Tại sao thay đổi một ô lại tính toán lại hàng trăm nghìn công thức?
Một trình tính toán công thức thô sơ không ghi nhớ mối quan hệ phụ thuộc giữa các ô, vì vậy biện pháp an toàn duy nhất của nó sau bất kỳ chỉnh sửa nào là tính toán lại toàn bộ mọi thứ. Tệ hơn nữa, chiến lược đệ quy cổ điển — khi công thức A tham chiếu đến công thức B thì tính toán B ngay lập tức — sẽ đánh giá lại ô được tham chiếu một cách vô điều kiện mà bỏ qua các giá trị đã được lưu đệm. Một chuỗi gồm n công thức tham chiếu lẫn nhau theo dạng bậc thang sẽ tốn chi phí O(n²) lượt tính toán cho mỗi lượt xử lý đầy đủ, và một tham chiếu vòng sẽ khiến tiến trình đệ quy bị sập do lặp vô tận. Bất kỳ nhà phát triển bảng tính nào từng tích hợp một mô hình phân cấp vào một bộ đánh giá đệ quy đều đã chứng kiến cả hai kịch bản thất bại này xảy ra
Bản thân Excel đã giải quyết vấn đề này từ nhiều thập kỷ trước bằng chuỗi tính toán của nó: một thứ tự sắp xếp của các ô công thức được duy trì để một chỉnh sửa chỉ đánh dấu một tập hợp nhỏ các ô là bị thay đổi và trình tính toán chỉ đi qua phần đuôi bị ảnh hưởng của chuỗi. HotXLS áp dụng cùng một ý tưởng đó dưới dạng một đồ thị phụ thuộc rõ ràng, được xây dựng một lần từ các cây công thức đã biên dịch và được tái sử dụng qua các lượt tính toán lại. Mục tiêu không phải là để thể hiện sự thông minh; mà là để chi phí tính toán lại bám sát quy mô của chỉnh sửa, chứ không phải kích thước của sổ làm việc
Đồ thị phụ thuộc chuyển đổi một chỉnh sửa thành một lượt xử lý duy nhất như thế nào
Đồ thị phụ thuộc của HotXLS gán cho mỗi ô công thức một nút (node), với các cạnh (edges) nối từ ô tiền lệ sang ô phụ thuộc. Khi mã nguồn của bạn ghi giá trị cho một ô, sổ làm việc sẽ đánh dấu ô đó là bị thay đổi; khi hàm Recalculate chạy, trạng thái thay đổi sẽ lan truyền theo các cạnh đến mọi công thức ở hạ nguồn, và đồ thị con bị thay đổi sẽ được tính toán chính xác một lần duy nhất theo thứ tự topo sử dụng thuật toán của Kahn. Vì một công thức không bao giờ được xử lý trước các tiền lệ của nó, mỗi nút chỉ cần tính toán đúng một lần — giúp lượt xử lý đạt hiệu năng O(dirty)
Thứ tự topo cũng khắc phục triệt để vấn đề đệ quy từ gốc rễ. Trong một lượt tính toán lại, trình tính toán sẽ chuyển sang một chế độ chuyên dụng, trong đó bất kỳ tham chiếu nào đến một ô công thức khác sẽ đọc trực tiếp giá trị lưu đệm của ô đó thay vì tính toán lại nó — thứ tự sắp xếp đảm bảo dữ liệu đệm đã được cập nhật mới nhất. Cơ chế tương tự giúp đảm bảo một vòng lặp tham chiếu không thể kích hoạt đệ quy vô hạn: không có thao tác nào bên trong lượt xử lý thực hiện gọi lại bộ đánh giá cho một ô lân cận
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs');
Model := Book.Sheets.Add('Model');
Inputs.Cells[2, 2].Value := 0.05; // growth assumption
Model.Cells[2, 2].Formula := 'Inputs!B2*1000'; // XLSX formulas take no leading '='
Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
// ... thousands more rows cascading off the same assumption ...
Book.Recalculate; // first call: builds the graph, full evaluation
Inputs.Cells[2, 2].Value := 0.07; // one edit marks one cell dirty
Book.Recalculate; // second call: only the downstream chain runs
finally
Book.Free;
end;
end;Mỗi kết quả tính toán được đưa vào giá trị lưu đệm Value của ô, vì vậy sau khi Recalculate hoàn tất, bạn có thể đọc kết quả đầu ra giống như cách đọc bất kỳ ô nào khác. Trong một vòng lặp tạo báo cáo, mô hình sử dụng chính xác như đoạn mã trên: tải hoặc xây dựng mô hình một lần, sau đó luân phiên giữa viết một vài ô đầu vào và gọi hàm Recalculate, chỉ chi trả tài nguyên xử lý cho các công thức thực sự phụ thuộc vào những gì đã thay đổi
Hững hàm Excel nào bắt buộc phải tính toán lại trong mỗi lượt xử lý?
HotXLS coi các hàm NOW, TODAY, RAND, OFFSET, và INDIRECT là các hàm biến đổi (volatile): bất kỳ công thức nào chứa một trong số chúng sẽ được tính toán lại trong mọi lượt xử lý Recalculate, bất kể nội dung ở thượng nguồn có thay đổi hay không. Ba hàm đầu tiên mang tính biến đổi vì cùng một lý do giống như trong Excel — kết quả của chúng phụ thuộc vào thời điểm đánh giá, chứ không phụ thuộc vào các ô khác. Hàm OFFSET và INDIRECT mang tính biến đổi vì một lý do tinh tế hơn: các ô mà chúng đọc được tính toán tại thời điểm chạy (run time), do đó đồ thị không thể biết trước để vẽ các cạnh phụ thuộc cho chúng
Quy tắc thận trọng tương tự cũng được áp dụng cho các tham chiếu mà bộ dựng đồ thị không thể xác định chính xác thành một vùng hình chữ nhật đơn nhất. Một công thức đi qua một vùng đặt tên nhiều khu vực (multi-area named range), hoặc một công thức tham chiếu đến một sổ làm việc bên ngoài, cũng sẽ bị hạ cấp thành biến đổi và được tính toán lại trong mỗi lượt xử lý. Chính sách này là có chủ ý: một lượt tính toán dư thừa chỉ tốn một chút thời gian, nhưng một cạnh phụ thuộc bị bỏ sót sẽ tạo ra một giá trị lỗi lỗi thời ngầm trong báo cáo gửi đi, đó là lỗi nghiêm trọng hơn nhiều. Nếu mô hình của bạn dựa vào các vùng đặt tên cấp sổ làm việc (workbook-scoped names), bài viết đi kèm về vùng đặt tên và công thức liên kết trang tính trình bày chi tiết cách phân giải các vùng đặt tên đơn khu vực — các vùng này tham gia vào đồ thị một cách bình thường
Hướng dẫn thực tế rất rõ ràng. Hãy giữ các đường dẫn xử lý chính (hot paths) của một mô hình lớn trên các tham chiếu ô và vùng thông thường để đồ thị có thể hoạt động tối ưu, và cô lập OFFSET cũng như INDIRECT vào một số ít vị trí thực sự cần định địa chỉ động. Một mô hình có hàng ngàn công thức biến đổi sẽ chạy lại hàng ngàn công thức đó trong mỗi lượt xử lý bất kể chỉnh sửa nhỏ đến mức nào — chính xác là hành vi mà người dùng Excel thường gặp ở các sổ làm việc "tính toán lại sau mỗi phím gõ"
HotXLS báo cáo các tham chiếu vòng như thế nào?
Hàm TXLSXWorkbook.Recalculate trả về lxOk cho một lượt xử lý thành công sạch sẽ và lxErrorRef khi phát hiện một vòng lặp tham chiếu. Các phần tử của vòng lặp được xác định trong quá trình sắp xếp topo — đó là các nút mà thuật toán Kahn không bao giờ có thể giải phóng — và chúng sẽ bị bỏ qua chứ không lặp vô tận: giá trị lưu đệm của chúng giữ nguyên như cũ, trong khi mọi công thức nằm ngoài vòng lặp vẫn được tính toán bình thường theo thứ tự. Nơi gọi hàm sẽ nhận được một mã lỗi rõ ràng thay vì bị treo chương trình
case Book.Recalculate of
lxOk:
SaveReport(Book);
lxErrorRef:
// a reference cycle exists; cycle members kept their previous
// cached values and everything outside the cycle is up to date
LogWarning('Circular reference detected - review model inputs');
end;Việc tìm kiếm ô nào tạo nên vòng lặp là công việc gỡ lỗi, và công cụ theo dõi đánh giá công thức là giải pháp phù hợp: theo dõi công thức nghi ngờ và chuỗi tham chiếu tự gộp vào chính nó sẽ hiển thị rõ ràng từng bước một. Các vòng lặp trong mô hình thực tế hầu như luôn là lỗi soạn thảo — ví dụ một hàng tổng hợp vô tình gộp cả chính nó vào phạm vi SUM — vì vậy một mã lỗi rõ ràng tại thời điểm tính toán lại chính xác là những gì bạn cần
Công thức mảng, theo dõi trạng thái thay đổi và thời điểm dựng lại đồ thị
Các công thức mảng CSE nhận một nút duy nhất cho toàn bộ vùng hình chữ nhật neo giữ, chứ không phải một nút cho mỗi ô. Công thức gốc được đánh giá một lần duy nhất cho mỗi lượt xử lý; ma trận kết quả được ghi trực tiếp vào từng ô thành viên, và một công thức tham chiếu đến bất kỳ ô nào bên trong vùng neo giữ — chứ không chỉ ô neo góc trên bên trái — sẽ nhận được một cạnh phụ thuộc từ nút gốc đó. Các kết quả vô hướng (scalar) được phát rộng ra khắp vùng chữ nhật theo cách mà ngữ nghĩa mảng cũ của Excel quy định
Việc theo dõi trạng thái thay đổi được liên kết with các trình thiết lập thuộc tính thông thường, vì vậy mã nguồn của bạn không cần thay đổi gì. Việc ghi thuộc tính Value của một ô sẽ thông báo cho sổ làm việc và đánh dấu các ô phụ thuộc là bị thay đổi; việc gán một Formula mới là một thay đổi cấu trúc, do đó nó đánh dấu toàn bộ đồ thị là lỗi thời, và lượt gọi Recalculate tiếp theo sẽ dựng lại đồ thị trước khi đánh giá. Việc thêm, xóa, hoặc di chuyển các trang tính cũng làm vô hiệu hóa đồ thị, vì định danh nút chứa thông tin chỉ số trang tính. Khi không có đồ thị nào hoạt động — ví dụ một sổ làm việc bạn không bao giờ gọi hàm Recalculate — các liên kết này chỉ tốn một phép kiểm tra nil duy nhất cho mỗi lượt gán, do đó các tác vụ đọc-ghi thông thường hoàn toàn không bị ảnh hưởng
Một giới hạn cần được nêu rõ ràng: đồ thị theo dõi sự phụ thuộc giữa các ô, vì vậy một hàm tự định nghĩa (UDF) được đăng ký qua OnUserFunction sẽ được tính toán lại khi các ô cung cấp đối số cho nó thay đổi, giống như bất kỳ công thức nào khác. Nếu bạn đang mở rộng trình tính toán theo cách đó, bài viết về các hàm tùy chỉnh trong trình tính toán công thức HotXLS sẽ hướng dẫn chi tiết quy ước gọi lại và cách các giá trị đối số được truyền tới
Tính toán lại lũy tiến là một phần của trình xử lý XLSX tiêu chuẩn trong HotXLS Delphi Excel Component, đi kèm với bộ tính toán công thức, các vùng đặt tên, và đường ống nhập/xuất tệp tin mà nó tăng tốc. Nếu ứng dụng Delphi hoặc C++Builder của bạn duy trì các mô hình hoạt động liên tục — bảng tính giá, sổ làm việc hợp nhất, báo cáo phân tầng — hàm Recalculate là điểm mấu chốt để chuyển đổi từ việc tính toán lại toàn bộ sổ làm việc sang việc chỉ tính toán lại phần được chỉnh sửa