ติดตามการประเมินสูตร Excel ทีละขั้นตอนใน Delphi

Excel ซ่อนตัวดีบักเกอร์ขนาดเล็กเอาไว้ในที่ที่เห็นได้ชัดเจน เลือกเซลล์ เปิดแท็บ Formulas แล้วคลิก Evaluate Formula กล่องโต้ตอบจะแสดงสูตรพร้อมขีดเส้นใต้นิพจน์ย่อย (subexpression) หนึ่งรายการ กด Evaluate และนิพจน์ย่อยนั้นจะถูกลดรูปกลายเป็นค่า จากนั้นส่วนถัดไปจะถูกขีดเส้นใต้ และคุณจะได้เห็นนิพจน์ยาวๆ หดลงเหลือตัวเลขเดียวทีละขั้นของการลดรูป นี่เป็นวิธีที่เร็วที่สุดในการหาว่าสาขาใดของ IF ซ้อนทับที่ถูกเรียกใช้งานจริง หรือการอ้างอิงใดที่ส่งผลรวมที่ผิดพลาด HotXLS จำลองพฤติกรรมนี้อย่างแม่นยำผ่าน TXLSFormulaTracer เพื่อให้โปรแกรม Delphi หรือ C++Builder สามารถเรนเดอร์รายการขั้นตอนแบบเดียวกันสำหรับการตรวจสอบเวิร์กบุ๊ก ดีบักสูตรที่สร้างขึ้น หรือสอนใครสักคนว่าทำไมผลลัพธ์ถึงออกมาเป็นเช่นนั้น แต่ละขั้นตอนที่บันทึกไว้จะนำข้อความนิพจน์ย่อยและค่าที่ลดรูปแล้วติดมาด้วย

เครื่องมือลดรูปลำดับความสำคัญของนิพจน์ทำงานอย่างไร

ตัวติดตาม (tracer) จะไม่เจาะลึกเข้าไปในกลไกการคำนวณ มันจะแยกสูตรออกเป็นโทเคน (tokenize) และแยกวิเคราะห์ด้วยตัวแยกวิเคราะห์แบบ recursive-descent จากนั้นจึงลดรูปต้นไม้จากเชิงลึกก่อน (depth-first) โดยเริ่มจากนิพจน์ย่อยที่อยู่ลึกที่สุดที่สามารถประเมินได้ก่อน เมื่อโหนดถูกลดรูปลงเหลือค่า ค่านั้นจะถูกแทนที่กลับเข้าไปในนิพจน์ที่ล้อมรอบในฐานะค่าคงที่ (literal) และเครื่องมือจะขอให้ตัวคำนวณจริงคำนวณนิพจน์ที่ง่ายขึ้นใหม่ เนื่องจากทุกขั้นตอนได้รับการประเมินผ่านเมธอด Calculate สาธารณะของเวิร์กชีต แทนที่จะเป็นทางลัดส่วนตัว แต่ละขั้นตอนจึงตรงกับสิ่งที่จะได้จากการคำนวณเซลล์ใหม่ทั้งหมดอย่างแท้จริง ตัวแยกวิเคราะห์ได้รับการออกแบบมาให้ไม่รุกราน (non-invasive) ซึ่งทำให้สามารถทำงานกับเวิร์กชีตใดๆ ก็ได้โดยไม่รบกวนสถานะของมัน

ตัวแยกวิเคราะห์ตามลำดับความสำคัญของตัวดำเนินการ โดยมีระดับแบบเรียกซ้ำหนึ่งระดับต่อกลุ่มความสำคัญ จากการผูกต่ำสุดไปสูงสุด ได้แก่ ระดับ 0 การเปรียบเทียบ (=, <>, <, >, <=, >=), ระดับ 1 การต่อข้อความ (&), ระดับ 2 การบวกและการลบ, ระดับ 3 การคูณและการหาร, ระดับ 4 การยกกำลัง และสุดท้าย บวกและลบแบบยูนารี (unary) ที่อยู่ต่ำกว่านั้น แต่ละระดับจะวิเคราะห์ระดับที่สูงกว่าสำหรับตัวถูกดำเนินการ ดังนั้นกลุ่มที่สูงกว่าจะผูกแน่นกว่า นี่คือลำดับความสำคัญเดียวกับที่ Excel นำมาใช้ ซึ่งเป็นเหตุผลที่ A1*B1+A2*B1 ลดรูปผลคูณสองชุดก่อนผลรวม: การคูณอยู่ที่ระดับ 3 การบวกอยู่ที่ระดับ 2 ดังนั้นการคูณจึงอยู่ลึกกว่าในต้นไม้และถูกลดรูปก่อน

การติดตามสูตรและการวนซ้ำขั้นตอน

การใช้งานสอดคล้องกับตัวอย่างที่มีให้ใน Demo/Delphi/FormulaTrace/FormulaTrace.dpr สร้างเวิร์กชีต (หรือเปิดเวิร์กบุ๊กที่มีอยู่) สร้างตัวติดตาม (tracer) บนชีต เรียก Trace และวนซ้ำอาร์เรย์ที่ส่งคืน แต่ละ TXLSFormulaStep จะเปิดเผย Depth สำหรับการเยื้อง (indentation), Source สำหรับนิพจน์ย่อยดั้งเดิม, Expression สำหรับนิพจน์ย่อยนั้นโดยมีตัวถูกดำเนินการถูกแทนที่เรียบร้อยแล้ว และ Value สำหรับผลลัพธ์ของขั้นตอนนี้

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;

การอ้างอิงเซลล์จะถูกแก้ไขก่อนและปรากฏเป็นขั้นตอนของมันเอง จากนั้นผลคูณจึงลดรูป แล้วตามด้วยปัจจัยภาษีในวงเล็บ และการคูณขั้นสุดท้ายจะปิดการทำงานทั้งหมด ฟิลด์ Depth ช่วยให้คุณเยื้องได้ เพื่อให้การลดรูปที่อยู่ลึกที่สุดนั่งอยู่ลึกสุดอย่างเห็นได้ชัด เช่นเดียวกับที่ Excel ขีดเส้นใต้พจน์ในสุดก่อนพจน์ภายนอกใดๆ

ข้อผิดพลาดของค่าคงที่ที่ไม่มีค่า locale (locale-free literal)

รายละเอียดที่อันตรายที่สุดในโครงสร้างทั้งหมดนี้ไม่สามารถมองเห็นได้บนเครื่องที่ใช้ภาษาอังกฤษ แต่จะพังอย่างเห็นได้ชัดบนเครื่องภาษาเยอรมัน เมื่อตัวเลขที่คำนวณได้ถูกแทนที่กลับเข้าไปในข้อความของสูตร มันจะต้องเขียนเป็นข้อความสตริงและแยกวิเคราะห์ใหม่ด้วยกลไกการคำนวณ ซึ่งจะจัดการกับ . ในฐานะจุดทศนิยม หากการแทนที่ใช้ locale ของระบบ TFormatSettings ของเยอรมันจะเขียน 1,08 สำหรับปัจจัยภาษี โดยที่จุลภาค (comma) จะถูกอ่านเป็นตัวคั่นอาร์กิวเมนต์ และการคำนวณใหม่ของ A1*B1*1,08 จะถูกแยกวิเคราะห์เป็นรูปร่างที่ผิดพลาดหรือล้มเหลวไปเลย

ตัวติดตามจะหลีกเลี่ยงสิ่งนี้โดยจัดรูปแบบค่าคงที่ตัวเลขทุกตัวผ่าน TFormatSettings ส่วนตัวที่ล็อกไว้เมื่อสร้างขึ้น โดยบังคับให้ DecimalSeparator เป็น . และกำหนด ThousandSeparator เป็น #0 ดังนั้นจึงไม่มีการปล่อยอักขระจัดกลุ่มออกมา FloatToStr จึงสามารถสร้างค่าคงที่ที่กลไกการคำนวณสามารถอ่านกลับมาได้เสมอ ไม่ว่าการตั้งค่าภูมิภาคของผู้ดำเนินการจะเป็นอย่างไร

// 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)

นี่คือบักประเภทที่ไม่เคยปรากฏในการทดสอบของผู้เขียนเองและจะโผล่ขึ้นมาเฉพาะเมื่อลูกค้าใน locale อื่นรันโค้ดเดียวกัน ดังนั้นจึงคุ้มค่าที่จะระบุอย่างชัดเจนว่า: การรับและส่งกลับ (round-tripping) ค่าผ่านข้อความของสูตรเป็นปัญหาของการทำให้เป็นอนุกรม (serialization) และการทำให้เป็นอนุกรมต้องไม่มีการอิง locale ใดๆ

ค่าบูลีนลดรูปเป็น 1 และ 0

การตัดสินใจเกี่ยวกับการแทนที่ซึ่งสัมพันธ์กันนั้นเกี่ยวข้องกับค่าตรรกะ (logical values) เมื่อนิพจน์ย่อยประเมินค่าออกมาเป็นบูลีน ตัวติดตามจะเขียนค่านั้นกลับเป็น 1 หรือ 0 ไม่ใช่เป็น TRUE หรือ FALSE เหตุผลคือค่าคงที่ที่ลดรูปแล้วต้องแยกวิเคราะห์กลับได้อย่างหมดจดในบริบทรอบข้างใดๆ และเลขคณิตคือกรณีที่เข้มงวดที่สุด หากการเปรียบเทียบเช่น A1>A2 ลดรูปลงเป็นข้อความ TRUE และข้อความนั้นมาอยู่ใน TRUE*B1 การคำนวณใหม่จะขึ้นอยู่กับการที่กลไกการคำนวณยอมรับคำสำคัญ (keyword) บูลีนล้วนๆ ในการคูณ การแทนที่ด้วย 1 เป็นการหลีกเลี่ยงปัญหานี้อย่างสิ้นเชิง เนื่องจาก 1*B1 นั้นชัดเจนในตำแหน่งเลขคณิตใดๆ มันยังเข้ากันได้กับการบีบบังคับ (coercion) ของ Excel เองด้วย ซึ่ง TRUE มีพฤติกรรมเป็น 1 และ FALSE เป็น 0 ทันทีที่คาดหวังตัวเลข

การเรียกใช้ฟังก์ชันลดรูปแบบอะตอมมิก (atomically)

กลไกจัดการขั้นตอนแบบง่ายๆ อาจลดรูปอาร์กิวเมนต์ของฟังก์ชันก่อนแล้วจึงเรียกใช้ นั่นเป็นสิ่งที่ผิดสำหรับ Excel และตัวติดตามจงใจไม่ทำเช่นนั้น การเรียกใช้ฟังก์ชันจะได้รับการประเมินเป็นภาพรวม จากข้อความดั้งเดิม ภายในขั้นตอนเดียว เหตุผลคือการทำงานแบบลัดวงจร (short-circuit semantics) IF, CHOOSE และ IFERROR จะประเมินเฉพาะสาขาที่มันเลือกเท่านั้น และการลดรูปอาร์กิวเมนต์ก่อนจะบังคับให้กลไกการคำนวณทำงานในสาขาที่ Excel ไม่เคยแตะต้อง ผู้ที่ได้รับผลกระทบสุดคลาสสิกคือตัวป้องกันการหารด้วยศูนย์ เช่น IF(B1=0,0,A1/B1): หากตัวติดตามลดรูป A1/B1 ก่อนประเมิน IF ตัวป้องกันนี้จะทำงานผิดพลาดและก่อให้เกิดข้อผิดพลาดที่มันถูกตั้งขึ้นมาเพื่อป้องกัน ด้วยการประเมินการเรียกใช้ทั้งหมดแบบอะตอมมิก (รวดเดียว) ตัวติดตามจะรักษาการประเมินแบบขี้เกียจ (lazy evaluation) ที่ทำให้การป้องกันเหล่านี้ทำงานได้

// 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.

สิ่งที่ต้องแลกคือคุณจะไม่เห็นภายในฟังก์ชันว่าถูกแยกเป็นขั้นตอนต่างๆ แต่พฤติกรรมนั้นถูกต้องแล้ว การแสดงการลดรูปอาร์กิวเมนต์ที่ Excel ไม่เคยทำ จะเป็นการติดตามที่ทำให้เข้าใจผิดมากกว่าการถือว่าการเรียกใช้ฟังก์ชันนั้นเป็นหน่วยประเมินหน่วยเดียวที่มันเป็นจริงๆ

ตัวคั่นอาร์กิวเมนต์และช่วงเซลล์ที่ไม่ถูกแบ่งแยก

มีการทำ Normalization อีกสองอย่างที่ช่วยให้การคำนวณใหม่แม่นยำ คอมไพเลอร์ของกลไกการคำนวณคาดหวังให้ใช้ ; เป็นตัวคั่นอาร์กิวเมนต์ของฟังก์ชัน ดังนั้นเมื่อตัวติดตามสร้างฟังก์ชันเรียกใช้ใหม่จากโครงสร้างต้นไม้ที่ถูกแยกวิเคราะห์ มันจะรวมอาร์กิวเมนต์ด้วย ; แม้ว่าผู้ใช้จะพิมพ์ , ในตอนแรกก็ตาม สูตรที่เขียนว่า SUM(A1,A2,A3) จะถูกคำนวณใหม่เป็น SUM(A1;A2;A3) ซึ่งกลไกการคำนวณยอมรับ การแทนที่ค่านั่นเองที่ทำให้จำเป็นต้องมีการสร้างใหม่นี้ และการตั้งตัวคั่นให้ถูกต้องก็คือสิ่งที่ทำให้สามารถแยกวิเคราะห์การสร้างใหม่ได้

การอ้างอิงช่วงเซลล์ (range references) เป็นอีกกรณีหนึ่ง ช่วงเซลล์อย่าง A1:A3 ไม่ใช่สเกลาร์ (scalar) และต้องไม่ถูกแบ่งเป็นสามค่าแยกกัน เนื่องจากฟังก์ชันที่ใช้ประโยชน์จากมันต้องการอาร์กิวเมนต์แบบช่วง ตัวติดตามจะเก็บช่วงเซลล์ไว้ให้สมบูรณ์เหมือนข้อความเดิมและปล่อยให้ฟังก์ชันที่ครอบอยู่ลดรูปในภาพรวม ใน SUM(A1:A3)*B1 ช่วงเซลล์จะยังคงอยู่เหมือนเดิม SUM(A1:A3) จะถูกลดรูปให้เหลือตัวเลขหนึ่งตัวภายในขั้นตอนแบบอะตอมมิกขั้นตอนเดียว และหลังจากนั้นการคูณด้านนอกจึงจะทำงาน นี่คือขอบเขตแบบเดียวกับที่ Excel ขีดเส้นแบ่งระหว่างตัวถูกดำเนินการแบบช่วงกับสเกลาร์ที่มันจะให้ผลลัพธ์ในท้ายที่สุด

// 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));

เมื่อนำมารวมกัน กฎเกณฑ์เหล่านี้ทำให้รายการขั้นตอนเป็นกระจกสะท้อนที่ซื่อตรงของคำสั่ง Evaluate Formula ของ Excel ไม่ใช่แค่การประมาณการ การลดรูปเกิดขึ้นตามลำดับที่ Excel ทำ ค่าคงที่ตัวเลขที่แทนที่สามารถเอาตัวรอดจาก locale ใดๆ ค่าบูลีนถูกบังคับตามแบบฉบับที่ Excel บังคับ และฟังก์ชันขี้เกียจ (lazy functions) ก็ยังคงขี้เกียจ หากคุณต้องการดันกลไกให้ไปไกลกว่าเดิมด้วยฟังก์ชันของคุณเอง บทความ กลไกสูตรและฟังก์ชันที่กำหนดเอง จะแสดงวิธีลงทะเบียนฟังก์ชันเหล่านี้ และสำหรับงานที่เน้นตัวเลขหนักขึ้น บทความ ฟังก์ชันแจกแจงทางสถิติใน Delphi จะครอบคลุมไลบรารีที่สร้างขึ้นภายในซึ่งตัวติดตามใช้ประเมิน ทั้งหมดนี้จัดส่งให้โดยเป็นส่วนหนึ่งของ HotXLS spreadsheet component สำหรับ Delphi และ C++Builder ควบคู่ไปกับ API การอ่าน การเขียน การจัดรูปแบบ และการคำนวณที่ครอบคลุมในส่วนอื่นๆ ของบล็อกนี้