Để tính toán các tham chiếu vòng có chủ ý trong Delphi, HotXLS cung cấp tính toán lặp trên công cụ XLSX của nó: thiết lập TXLSXWorkbook.Iterate thành True và TXLSXWorkbook.Recalculate sẽ duyệt qua từng chu kỳ tham chiếu được phát hiện để hướng tới một điểm cố định — tối đa qua IterateCount lượt chạy, hoặc cho đến khi mỗi ô thay đổi ít hơn IterateDelta — thay vì trả về lỗi #REF! và bỏ cuộc
Sự khác biệt đó quan trọng hơn những gì một giá trị boolean đơn thuần gợi ý. Cùng một công cụ phát hiện chu kỳ tham chiếu và từ chối lặp lại trên nó sẽ, khi một thuộc tính được bật lên, đánh giá chu kỳ đó một cách chủ động cho đến khi nó ổn định. Việc phân biệt rõ ràng hai hành vi — khi nào một chu kỳ là lỗi cần báo cáo và khi nào nó là một mô hình cần giải quyết — chính là toàn bộ chủ đề của bài viết này
Tại sao theo mặc định một tham chiếu vòng lại báo lỗi?
Theo mặc định, HotXLS xử lý bất kỳ chu kỳ tham chiếu nào như một lỗi soạn thảo và báo cáo nó thay vì tính toán. TXLSXWorkbook.Recalculate xây dựng một biểu đồ phụ thuộc công thức, đánh giá từng ô công thức theo thứ tự tô pô, và trả về lxErrorRef ngay khi phát hiện một chu kỳ — các nút không bao giờ có thể được giải phóng trong quá trình sắp xếp tô pô. Các thành viên chu kỳ đó giữ nguyên các giá trị đã được lưu trong bộ nhớ đệm trước đó; mọi công thức bên ngoài chu kỳ vẫn được đánh giá bình thường. Cơ chế hoạt động của biểu đồ đó, và lý do tại sao các thành viên chu kỳ bị bỏ qua thay vì lặp lại, được đề cập trong bài viết đi kèm về tính toán lại công thức tăng dần và biểu đồ phụ thuộc
Lựa chọn mặc định là lựa chọn an toàn bởi vì hầu hết các chu kỳ đều là lỗi: một hàng tổng hợp vô tình bị kéo vào chính phạm vi SUM của nó, một thao tác sao chép - dán làm dịch chuyển một tham chiếu vào chính nó. 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 muốn cho các trường hợp đó. Tuy nhiên, một nhóm mô hình cụ thể và quan trọng được thiết kế có vòng lặp một cách có chủ đích. Các lịch trình tính lãi kép (lãi trên lãi), phân bổ chi phí hoặc chi phí chung có tính chất vòng giữa các phòng ban, và các phép tính phí dựa trên số dư đều mô tả một giá trị phản hồi lại các đầu vào của chính nó một cách hợp lệ — và Excel chỉ tính toán chúng sau khi người dùng tích chọn File → Options → Formulas → Enable iterative calculation
Làm thế nào để bật tính toán lặp trong HotXLS?
HotXLS phản chiếu hộp chọn Excel đó bằng ba thuộc tính trên TXLSXWorkbook: Iterate bật chế độ này lên, và khi nó là True một chu kỳ được phát hiện sẽ được giao cho một bộ giải lặp thay vì tạo ra lxErrorRef. Hãy xem xét cặp ô lãi trên lãi kinh điển, nơi số dư cuối kỳ phụ thuộc vào lãi suất và lãi suất phụ thuộc vào số dư
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: opening principal
Sheet.Cells[2, 2].Value := 0.05; // B2: period rate
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: balance = principal + interest
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interest = balance * rate
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 converges to 1052.63..., B4 to 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 tham chiếu B4 và B4 tham chiếu B3, do đó biểu đồ phụ thuộc báo cáo một chu kỳ hai nút. Với Iterate được để ở giá trị mặc định là False, cặp này sẽ trả về lỗi lxErrorRef và không có ô nào ổn định giá trị. Với giá trị là True, Recalculate sẽ lấy các giá trị lưu tạm hiện tại làm hạt giống cho chu kỳ và đánh giá lại các thành viên của nó qua từng lượt chạy, đưa đầu ra của lượt chạy này trở lại làm đầu vào của lượt chạy tiếp theo, cho đến khi các con số ngừng thay đổi. Ở đây, công thức dạng đóng là principal / (1 - rate), do đó số dư ổn định ở mức 1052.63 và tiền lãi ở mức 52.63 — giống hệt các con số Excel tạo ra khi bật tính năng lặp
Điều gì làm cho quá trình lặp dừng lại?
Hai điều kiện dừng độc lập sẽ ràng buộc bộ giải, và việc hiểu rõ cả hai điều kiện này là thứ giúp một mô hình không bị báo lỗi hoặc bị lặp vô hạn. IterateCount là giới hạn tối đa số lần các thành viên chu kỳ được đánh giá lại; nó mặc định là 100, khớp với Excel. IterateDelta là ngưỡng hội tụ: sau mỗi lượt chạy, bộ giải sẽ đo lường mức thay đổi số lượng lớn nhất trên tất cả các ô trong chu kỳ, và một khi mức thay đổi tối đa đó giảm xuống dưới IterateDelta — mặc định 0.001 — vòng lặp sẽ thoát sớm. Bất kỳ điều kiện nào đạt được trước tiên đều sẽ kết thúc quá trình lặp
Book.Iterate := True;
Book.IterateCount := 1000; // hard cap: at most 1000 passes over the cycle
Book.IterateDelta := 0.0001; // convergence: stop once every cell moves < 0.0001
case Book.Recalculate of
lxOk:
// the cycle converged, OR it hit the 1000-pass cap and kept its
// last-iteration values -- both paths return lxOk when Iterate is True
SaveWorkbook(Book);
lxErrorRef:
// only reachable with Iterate = False: the cycle was reported, not solved
LogWarning('Circular reference with iteration disabled');
end;
Một hậu quả đáng được nêu rõ, bởi vì nó là ranh giới thực tế của tính năng này. Khi đạt đến giới hạn tối đa mà mức thay đổi không giảm xuống dưới IterateDelta, SolveCycleIteratively không nâng lỗi — nó trả về lxOk và để các ô giữ lại các giá trị của lượt chạy cuối cùng, chính xác theo cách Excel ghi các số tính toán cuối cùng khi đạt đến giới hạn lặp của nó mà không hội tụ. Vì thế, một mã phản hồi thành công từ Recalculate dưới chế độ lặp nghĩa là "bộ giải đã chạy", chứ không phải "bộ giải đã hội tụ". Một mô hình có vòng lặp phản hồi phân kỳ hoặc dao động sẽ âm thầm tiêu thụ hết số lượt chạy IterateCount và đưa lại các con số hoàn toàn không phải là một điểm cố định, và không có ngoại lệ nào đánh dấu sự khác biệt đó
Thiết lập được lưu vào các tệp XLSX và XLS như thế nào?
Các thiết lập tính toán lặp tồn tại trong cả hai định dạng bảng tính, do đó một sổ làm việc được mở trong Excel sẽ hoạt động theo cách mà mã Delphi của bạn đã cấu hình. Về phía XLSX, trình ghi chỉ phát ra phần tử OOXML <calcPr> khi Iterate là True, và nó bỏ qua bất kỳ thuộc tính nào vẫn ở giá trị mặc định của nó để giữ cho đầu ra ở mức tối giản: một sổ làm việc sử dụng các mặc định chỉ ghi <calcPr iterate="1"/>, trong khi iterateCount chỉ xuất hiện khi nó khác 100 và iterateDelta chỉ khi nó khác 0.001. Khi mở, TXLSXWorkbook đọc lại ba thuộc tính tương tự, do đó quá trình truyền nhận hai chiều là đối xứng
Công cụ BIFF8 (.xls) cũ hơn, TXLSWorkbook, mang trạng thái tương đương thông qua ba bản ghi riêng biệt dưới một bộ ba thuộc tính khác. EnableIteration ánh xạ tới bản ghi CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations tới bản ghi CalcCount ($000C, [MS-XLS] §2.4.31), và MaxIterationChange tới bản ghi CalcDelta ($0010, [MS-XLS] §2.4.32). Các setter thực thi các phạm vi đặc tả — CalcCount phải nằm trong khoảng 1..32767, do đó MaxIterations được giới hạn, và một MaxIterationChange giá trị âm sẽ tự động quay về mặc định 0.001. Thiết lập chúng trên một sổ làm việc .xls đã tải và ba bản ghi tính toán (calc records) đó sẽ được ghi lại một cách trung thực khi lưu
var
Book: TXLSWorkbook; // BIFF8 (.xls) engine
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount record $000C (clamped to 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // the three calc records round-trip
finally
Book.Free;
end;
end;Lưu ý sự chia tách tên gọi chủ ý: công cụ XLSX sử dụng các thuộc tính Iterate / IterateCount / IterateDelta (từ vựng OOXML), trong khi công cụ BIFF8 sử dụng EnableIteration / MaxIterations / MaxIterationChange (phù hợp với tên bản ghi [MS-XLS]). Cả hai bộ ba thuộc tính đều mô tả cùng ba núm điều khiển — công tắc bật/tắt, giới hạn vòng lặp, và độ lệch hội tụ — với cùng các mặc định là tắt, 100, và 0.001
Khi nào một tham chiếu vòng là một lỗi thay vì một mô hình?
Bật tính năng lặp không phải là cách để làm biến mất các cảnh báo tham chiếu vòng, và coi nó như vậy chính là một cái bẫy. Việc bật Iterate trên toàn cầu sẽ chuyển đổi mọi chu kỳ vô tình xuất hiện — những chu kỳ mà mã lỗi mặc định sinh ra để phát hiện — thành một con số hội tụ âm thầm hoặc không hội tụ âm thầm. Quy tắc làm việc là ngược lại: hãy giữ Iterate là False làm chế độ thông thường để các lỗi soạn thảo thực sự vẫn hiển thị dưới dạng lỗi lxErrorRef, và chỉ bật tính năng lặp trên các sổ làm việc có tính chất vòng lặp theo thiết kế và đã được thấu hiểu
Khi một chu kỳ xuất hiện và bạn không chắc chắn nó thuộc loại nào, trình theo dõi đánh giá công thức (formula evaluation tracer) là công cụ giúp phân biệt chúng: theo dõi công thức nghi ngờ và chuỗi tham chiếu gập lại chính nó sẽ hiển thị rõ ràng từng bước, nhờ đó bạn có thể quyết định xem nó mã hóa một vòng lặp phản hồi thực tế hay một tham chiếu tự thân đi lạc. Việc ghi nhớ rằng một ô trong chu kỳ có thể gọi bất kỳ hàm tích hợp sẵn nào trên đường lặp cũng rất hữu ích — cùng bộ tính toán giải quyết một công thức kỹ thuật hoặc số phức sẽ đánh giá các thành viên chu kỳ trong mỗi lượt chạy — vì vậy một mô hình phân kỳ thường là một vấn đề công thức bên trong vòng lặp, chứ không phải là vấn đề thiết lập vòng lặp
Danh sách kiểm tra thực tế rất ngắn gọn. Hãy xác nhận vòng lặp có một điểm cố định thực tế trước khi bạn phụ thuộc vào tính năng lặp; giữ IterateDelta đủ chặt chẽ để trạng thái "hội tụ" có ý nghĩa đúng như mô hình của bạn yêu cầu; và sau một lệnh Recalculate mà bạn mong đợi hội tụ, hãy kiểm tra tính hợp lý của một đầu ra đã biết thay vì chỉ tin tưởng vào mã lxOk đơn thuần, do mã đó không thể phân biệt trạng thái hội tụ với trạng thái đạt tới giới hạn lặp tối đa
Tính toán lặp cho tham chiếu vòng là một phần của công cụ XLSX trong HotXLS Delphi Excel Component, bên cạnh việc tính toán lại biểu đồ phụ thuộc tăng dần mà nó dựa trên đó và OOXML cũng như BIFF8 hỗ trợ mang thiết lập này vào mọi tệp bạn ghi. Đối với các mô hình tài chính và kỹ thuật có tính chất vòng lặp một cách chủ đích, nó tạo ra sự khác biệt giữa một mã lỗi và một câu trả lời thực tế