Excel ẩn một trình gỡ lỗi nhỏ ngay trước mắt bạn. Chọn một ô, mở Formulas và nhấn Evaluate Formula, một hộp thoại hiển thị công thức với một biểu thức con được gạch chân. Nhấn Evaluate và biểu thức con đó thu lại thành giá trị của nó, rồi biểu thức tiếp theo được gạch chân, và bạn quan sát một biểu thức dài thu gọn thành một con số duy nhất qua từng lần rút gọn. Đây là cách nhanh nhất để tìm ra nhánh nào của IF lồng nhau thực sự được thực thi, hoặc tham chiếu nào đã đưa ra tổng sai. HotXLS tái tạo chính xác hành vi đó thông qua TXLSFormulaTracer, cho phép chương trình Delphi hoặc C++Builder hiển thị cùng danh sách bước để kiểm tra workbook, gỡ lỗi công thức được tạo ra, hoặc giải thích tại sao kết quả lại như vậy. Mỗi bước được ghi lại mang văn bản biểu thức con và giá trị mà nó rút gọn thành
Cách bộ máy rút gọn duyệt biểu thức
Trình theo dõi không can thiệp vào bộ tính toán. Nó mã hóa (tokenize) công thức và phân tích cú pháp bằng bộ phân tích đệ quy (recursive-descent parser), sau đó rút gọn cây theo chiều sâu trước (depth-first), bắt đầu từ biểu thức con trong cùng có thể đánh giá được. Khi một nút rút gọn thành một giá trị, giá trị đó được thay thế ngược lại vào biểu thức bao quanh dưới dạng literal, và bộ máy yêu cầu trình tính toán thực sự tính lại biểu thức đơn giản hơn này. Vì mỗi bước đều được đánh giá thông qua phương thức Calculate công khai của worksheet thay vì một lối tắt riêng, mỗi bước đều cho kết quả nhất quán với những gì tính toán lại đầy đủ của ô sẽ tạo ra. Bộ phân tích được thiết kế không xâm phạm, đây là điều cho phép nó chạy trên bất kỳ worksheet nào mà không làm xáo trộn trạng thái của nó
Bộ phân tích tuân theo thứ tự ưu tiên toán tử, với một mức đệ quy cho mỗi dải ưu tiên. Từ liên kết yếu nhất đến mạnh nhất, các dải là: cấp 0 so sánh (=, <>, <, >, <=, >=), cấp 1 nối chuỗi (&), cấp 2 cộng và trừ, cấp 3 nhân và chia, cấp 4 lũy thừa, và cuối cùng là cộng và trừ đơn nguyên bên dưới đó. Mỗi cấp phân tích cú pháp cấp trên nó cho các toán hạng của nó, vì vậy dải cao hơn liên kết chặt hơn. Đây là cùng thứ tự ưu tiên mà Excel áp dụng, đó là lý do tại sao A1*B1+A2*B1 rút gọn hai tích trước tổng: phép nhân nằm ở cấp 3, phép cộng ở cấp 2, vì vậy các phép nhân sâu hơn trong cây và rút gọn trước
Theo dõi công thức và duyệt qua các bước
Cách dùng phản ánh demo được cung cấp tại Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Xây dựng một worksheet (hoặc mở workbook hiện có), tạo tracer trên sheet, gọi Trace và lặp qua mảng kết quả trả về. Mỗi TXLSFormulaStep hiển thị Depth để thụt lề, Source cho biểu thức con gốc, Expression cho biểu thức con đó với các toán hạng đã được thay thế, và Value cho kết quả của bước
uses
SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Tracer: TXLSFormulaTracer;
Steps: TXLSFormulaStepArray;
Final: Variant;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Order');
Sheet.Cells[1, 1].Value := 10; // A1 units
Sheet.Cells[1, 2].Value := 25; // B1 unit price
Sheet.Cells[1, 3].Value := 0.08; // C1 tax rate
Tracer := TXLSFormulaTracer.Create(Sheet);
try
Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
for I := 0 to High(Steps) do
Writeln(StringOfChar(' ', Steps[I].Depth * 2),
Steps[I].Source, ' -> ', Steps[I].Expression,
' = ', VarToStr(Steps[I].Value));
Writeln('result = ', VarToStr(Final));
finally
Tracer.Free;
end;
finally
Book.Free;
end;
end;Các tham chiếu ô được giải quyết trước và xuất hiện như các bước riêng của chúng, sau đó các tích rút gọn, rồi hệ số thuế trong ngoặc đơn, và phép nhân cuối cùng kết thúc. Trường Depth cho phép bạn thụt lề để các rút gọn trong cùng nằm sâu nhất theo cách trực quan, chính xác như cách Excel gạch chân thuật ngữ trong cùng trước bất kỳ thuật ngữ nào bên ngoài
Bẫy literal không phụ thuộc locale
Chi tiết nguy hiểm nhất trong toàn bộ sơ đồ này vô hình trên máy tiếng Anh và gây ra lỗi rõ ràng trên máy tiếng Đức. Khi một số đã tính toán được thay thế ngược lại vào văn bản công thức, nó phải được viết dưới dạng chuỗi và sau đó được phân tích lại bởi bộ tính toán, vốn coi . là dấu thập phân. Nếu việc thay thế sử dụng locale hệ thống, TFormatSettings tiếng Đức sẽ viết 1,08 cho hệ số thuế, dấu phẩy sẽ được đọc là dấu phân cách đối số, và việc tính toán lại A1*B1*1,08 sẽ phân tích sai hoặc thất bại hoàn toàn
Tracer tránh điều này bằng cách định dạng mọi literal số thông qua TFormatSettings riêng tư mà nó ghim tại thời điểm khởi tạo, với DecimalSeparator được buộc thành . và ThousandSeparator được đặt thành #0 để không bao giờ phát ra ký tự nhóm. Sau đó FloatToStr tạo ra một literal mà bộ máy luôn có thể đọc lại, bất kể cài đặt vùng của người vận hành
// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)Đây là loại lỗi không bao giờ xuất hiện trong quá trình kiểm tra của chính tác giả và chỉ xuất hiện khi khách hàng ở locale khác chạy cùng code, vì vậy đáng nêu rõ: việc round-trip một giá trị qua văn bản công thức là một vấn đề serialization, và serialization phải không phụ thuộc locale
Giá trị boolean rút gọn thành 1 và 0
Một quyết định thay thế liên quan đến các giá trị logic. Khi một biểu thức con đánh giá thành boolean, tracer ghi lại nó dưới dạng 1 hoặc 0, không phải TRUE hoặc FALSE. Lý do là literal được rút gọn phải phân tích lại rõ ràng trong bất kỳ ngữ cảnh nào bao quanh nó, và số học là trường hợp đòi hỏi cao. Nếu một phép so sánh như A1>A2 rút gọn thành văn bản TRUE và văn bản đó xuất hiện trong TRUE*B1, việc tính toán lại sẽ phụ thuộc vào việc bộ máy chấp nhận từ khóa boolean trần trong một phép nhân. Thay thế 1 giải quyết câu hỏi hoàn toàn, vì 1*B1 là rõ ràng trong bất kỳ vị trí số học nào. Nó cũng khớp với cưỡng chế của chính Excel, nơi TRUE hoạt động như 1 và FALSE như 0 khi một số được mong đợi
Lời gọi hàm rút gọn nguyên tử
Một bộ máy bước ngây thơ sẽ rút gọn các đối số của hàm trước rồi mới gọi hàm. Điều đó sai với Excel, và tracer cố ý không làm vậy. Lời gọi hàm được đánh giá như một tổng thể, từ văn bản gốc của nó, trong một bước duy nhất. Lý do là ngữ nghĩa short-circuit. IF, CHOOSE và IFERROR chỉ đánh giá nhánh mà chúng chọn, và việc rút gọn đối số trước sẽ buộc bộ máy tính toán các nhánh mà Excel không bao giờ chạm tới. Trường hợp điển hình là một bảo vệ chia-cho-không như IF(B1=0,0,A1/B1): nếu tracer rút gọn A1/B1 trước khi đánh giá IF, bảo vệ sẽ kích hoạt sai và gây ra chính lỗi mà nó tồn tại để ngăn chặn. Bằng cách đánh giá toàn bộ lời gọi nguyên tử, tracer bảo toàn đánh giá lười biếng làm cho các bảo vệ như vậy hoạt động
// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.Sự đánh đổi là bạn không thấy bên trong lời gọi hàm như các bước riêng biệt, nhưng đó là hành vi đúng. Hiển thị các rút gọn đối số mà Excel không bao giờ thực hiện sẽ là một trace gây hiểu lầm hơn so với việc coi lời gọi là đơn vị đánh giá duy nhất mà thực sự là như vậy
Dấu phân cách đối số và phạm vi nguyên vẹn
Hai chuẩn hóa nữa giữ cho việc tính toán lại trung thực. Trình biên dịch của bộ tính toán mong đợi ; là dấu phân cách đối số hàm, vì vậy khi tracer xây dựng lại lời gọi hàm từ cây phân tích của nó, nó nối các đối số bằng ;, ngay cả khi người dùng ban đầu gõ ,. Một công thức được viết là SUM(A1,A2,A3) được tính toán lại là SUM(A1;A2;A3), và bộ máy chấp nhận điều này. Việc thay thế giá trị là điều làm cho việc xây dựng lại này cần thiết, và việc đúng dấu phân cách là điều làm cho việc xây dựng lại phân tích được
Tham chiếu phạm vi là trường hợp còn lại. Một phạm vi như A1:A3 không phải là vô hướng và không được tách thành ba giá trị riêng biệt, vì hàm tiêu thụ nó mong đợi một đối số phạm vi. Tracer giữ phạm vi nguyên vẹn như văn bản gốc của nó và để hàm bao quanh rút gọn như một tổng thể. Trong SUM(A1:A3)*B1, phạm vi vẫn nguyên vẹn, SUM(A1:A3) rút gọn thành một số trong một bước nguyên tử, và chỉ sau đó phép nhân bên ngoài mới chạy. Đây là ranh giới tương tự mà Excel vẽ giữa toán hạng phạm vi và vô hướng mà nó cuối cùng đóng góp
// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));Kết hợp lại, các quy tắc này làm cho danh sách bước trở thành một bản phản ánh trung thực của lệnh Evaluate Formula của Excel thay vì một xấp xỉ của nó. Các rút gọn xảy ra theo thứ tự Excel thực hiện chúng, các literal được thay thế tồn tại ở bất kỳ locale nào, các boolean ép buộc theo cách Excel ép buộc chúng, và các hàm lười biếng vẫn lười biếng. Nếu bạn muốn thúc đẩy bộ máy hơn nữa với các hàm của riêng bạn, bài viết về bộ máy công thức và hàm tùy chỉnh chỉ cách đăng ký chúng, và để làm việc số học nặng hơn, bài viết về hàm phân phối thống kê trong Delphi đề cập đến thư viện tích hợp mà tracer đánh giá dựa vào. Tất cả đều được cung cấp như một phần của HotXLS spreadsheet component cho Delphi và C++Builder, cùng với các API đọc, viết, định dạng và tính toán được đề cập ở các bài viết khác trên blog này