ตรวจสอบ formula cache ของ Excel ด้วย Deep Recalc ใน HotXLS

HotXLS ตอบคำถามที่ pipeline สเปรดชีตทุกสายสักวันต้องถาม คือตัวเลขที่เก็บอยู่ในเวิร์กบุ๊กยังตรงกับสูตรที่ผลิตมันออกมาหรือไม่ CalculateAndVerify คำนวณกราฟ dependency ทั้งหมดใหม่ลง overlay ที่แยกขาดจากตัวเวิร์กบุ๊ก เทียบผลลัพธ์แต่ละตัวกับค่า cache ที่นั่งอยู่ในเซลล์ แล้วรายงานจุดที่ไม่เห็นพ้อง โดยค่าเริ่มต้นมันไม่แตะอะไรเลย

เหตุผลที่เรื่องนี้สำคัญคือไฟล์สเปรดชีตเก็บสองอย่างต่อหนึ่งเซลล์สูตร สูตร และค่าสุดท้ายที่มีใครคำนวณไว้ให้ Excel คุมสองอย่างนี้ให้ตรงกัน แต่ทุกสิ่งรอบนอกอาจไม่เคยทำไฟล์ที่ผ่านไลบรารีเก่า การคำนวณซ้ำแบบไม่ครบ, XML part ที่ถูกแก้มือ หรือเครื่องมือที่เขียนค่าโดยไม่คำนวณใหม่ จะยื่นยอดรวมที่ไม่ต่อเนื่องจาก input ของมันให้คุณโดยไม่อาย และไม่มีอะไรในฟอร์แมตไฟล์ติดธงเรื่องนี้เลย

ทำไมค่า cache ที่ไม่เห็นพ้องกับสูตรจึงอันตรายถึงขั้นนั้น

เพราะมันมองไม่เห็นในเส้นทางการอ่านธรรมดาทุกเส้น เปิดไฟล์ใน viewer อ่านเซลล์ผ่าน API export เป็น CSV หรือ PDF คุณได้เลขจาก cache สูตรก็นั่งอยู่ตรงเซลล์เดียวกันนั่นแหละ แต่ไม่มีใครเทียบมัน ความไม่ตรงกันจะโผล่เฉพาะตอนที่ใครเปิดเวิร์กบุ๊กใน Excel ซึ่งคำนวณใหม่ตอนโหลดภายใต้การตั้งค่าส่วนใหญ่ แล้วรายงานที่ถูกเซ็นรับรองไปเมื่อไตรมาสก่อนก็แสดงยอดที่ต่างออกไปเฉย ๆ

การตรวจสอบนี้มีอยู่เพื่อเปลี่ยนการเทียบแบบนั้นเป็น operation ที่ถูกวางแผนและกำหนดเวลาไว้ ไม่ใช่อุบัติเหตุ มันคือรุ่นพี่น้องของการตรวจ checksum ในโลกสเปรดชีต ถูกพอที่จะรันใน pipeline รับงาน และเป็นสิ่งเดียวที่เปลี่ยนปัญหาความสมบูรณ์ของข้อมูลที่เงียบสนิทให้เป็นรายงานที่คุณลงมือจัดการได้

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

มีสาม overload และมันตอบคำถามต่างกันสามข้อ ตัว parameterless CalculateAndVerify คืนจำนวนความไม่ตรงกัน ซึ่งเพียงพอกับ health check overload ที่รับ out array ของความไม่ตรงกันให้เซลล์มาให้คุณ ส่วน overload ที่รับ TXLSRecalcAuditOptions คืน TXLSCalculationAuditReport เต็มรูป ซึ่งเป็นตัวที่ควรเอื้อมไปหาเมื่อคุณอยากรู้ไม่แค่ว่าค่าไหนไม่เห็นพ้อง แต่ทำไมการตรวจถึงประเมินบางอย่างไม่ได้

overlay และเหตุผลที่การตรวจไม่เขียนอะไร

ค่าที่คำนวณใหม่ทุกตัวไปลงที่ overlay ไม่ใช่ cell cache และ overlay ถูกฉีดไว้หน้าสุดของ callback อ่านเซลล์ในเอนจินเวิร์กบุ๊กทั้งสองตัว ตำแหน่งนี้แหละที่ทำให้การตรวจสอบสอดคล้องกับตัวเอง เมื่อ B1 ถูกคำนวณใหม่และ C1 พึ่ง B1, C1 จะเห็นค่าจากรอบตรวจนี้ ไม่ใช่ค่า cache ค้างสมัย ปราศจากจุดนี้ error ปลายน้ำต้นจะถูกรายงานครั้งเดียวแล้วถูกดูดกลืนไป และเซลล์ปลายทางทุกตัวจะดูเหมือนเห็นพ้องกับ input ที่ผิดอย่างน่าเชื่อถือ

เซลล์ที่ค่าคำนวณใหม่ตรงกับ cache จะไม่เข้า overlay เลยแม้แต่ตัวเดียว นั่นไม่ใช่ micro-optimization แต่มันคือสิ่งที่ทำให้การตรวจจ่ายจ่ายได้ เวิร์กบุ๊กสะอาดที่มีสูตรแสนสูตรทำการเขียน overlay เป็นศูนย์ครั้ง และรอบตรวจยังอยู่ในงบประมาณ 1.35 เท่าเทียบกับการคำนวณใหม่เต็มรูป ซึ่งคือระยะห่างระหว่างสิ่งที่รันได้ทุกการรับงานกับสิ่งที่รันได้ครั้งละไตรมาส

pipeline การตรวจสอบ deep recalc ของ HotXLS: เวิร์กบุ๊กถูกโหลดโดย cache คงเดิม ทุก node ของ dependency ถูก mark dirty และประเมินหนึ่งครั้งตามลำดับ topological ค่าที่คำนวณใหม่ลง overlay แยกที่ถูกพิจารณาก่อนโดย callback อ่านเซลล์ในทั้งสองเอนจิน ผลลัพธ์ถูกเทียบกับค่า cache และจัดหมวดผ่าน CalculateAndVerify เป็น TXLSCalculationAuditReport โดยไม่มีอะไรถูกเขียนลงดิสก์ — ค่าที่คำนวณใหม่ลงที่ overlay หน้า callback อ่านเซลล์ เซลล์ที่ตรงกันไม่แตะ overlay เลย และเวิร์กบุ๊กบนดิสก์คงเดิม เว้นแต่ ApplyResults จะ commit รอบตรวจที่สะอาดครบ

การประเมินวิ่งตามลำดับ topological แบบ serial ที่ derive จากกราฟ dependency โดย mark dirty ทุก node ไว้ก่อน แต่ละเซลล์จึงถูกคำนวณเป๊ะหนึ่งครั้งหลัง input ของมัน ถ้าคุณอยากได้กลไกแบบเพิ่มรายการที่คุมเวิร์กบุ๊กที่กำลังทำงานอยู่ให้ทันสถานะ แทนการตรวจเวิร์กบุ๊กที่เก็บไว้ นั่นคือกลไกคนละตัว มีเล่าไว้ใน การคำนวณแบบเพิ่มรายการกับกราฟ dependency

ความล้มเหลวถูกจัดหมวด ไม่ใช่กองรวมกัน

เซลล์ที่การตรวจประเมินไม่ได้ไม่ใช่ข้อสรุปชนิดเดียวกับเซลล์ที่ค่าไม่เห็นพ้อง และ TXLSCalculationAuditIssueKind คุมหมวดพวกนี้แยกกัน xlcaiCacheMismatch คือความไม่ตรงกันของค่า xlcaiMissingFunction กับ xlcaiMissingName บอกว่า evaluator เจอสิ่งที่มันไม่ได้ implement หรือ resolve ไม่ได้ xlcaiUnsupportedArguments ครอบรูปร่างอาร์กิวเมนต์ที่อยู่นอกชุดที่รองรับ xlcaiExternalReferenceDenied กับ xlcaiExternalReferenceMissing แยกการปฏิเสธตามนโยบายออกจากเวิร์กบุ๊กที่หายไป xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled และ xlcaiInternalFailure ปิดชุดให้ครบ

การจัดหมวดประเด็นการตรวจสอบของ HotXLS: TXLSCalculationAuditIssueKind แยกความไม่ตรงกันของค่าที่รายงานเป็น xlcaiCacheMismatch ออกจากหมวดความล้มเหลวของการประเมินอย่าง xlcaiMissingFunction, xlcaiMissingName, xlcaiUnsupportedArguments, คู่ของ xlcaiExternalReferenceDenied กับ xlcaiExternalReferenceMissing และ xlcaiCircularReference ขณะที่รหัส error ของ Excel ที่เป็นบวกนับเป็นผลลัพธ์ ไม่ใช่ความล้มเหลว — มีหนึ่งชนิดที่รายงานความไม่ตรงกันของค่า ที่เหลือรายงานว่าทำไม evaluator จึงตัดสินเซลล์ไม่ได้ ค่า error ของ Excel เป็นผลลัพธ์ที่ถูกคำนวณมา เซลล์ error ที่ตั้งใจไว้จึงให้ประเด็นเป็นศูนย์

ความต่างหนึ่งข้อสมควรถูกพูดให้ชัดเพราะมันกลับสมมติฐานที่คนทั่วไปถือ รหัส error ของ Excel ที่เป็นบวกคือผลลัพธ์ ไม่ใช่ความล้มเหลว เซลล์ที่ประเมินได้ #DIV/0! อย่างถูกกฎหมายได้คำนวณอย่างถูกต้องแล้ว การตรวจจึงเก็บ error นั้นลง overlay และเทียบกับ cache เหมือนค่าอื่นทุกประการ เวิร์กบุ๊กที่เต็มไปด้วยเซลล์ error ที่ตั้งใจให้ผลิตประเด็นเป็นศูนย์ และเวิร์กบุ๊กที่ error โผล่ขึ้นหรือหายไปหลังค่าถูก cache ไว้ผลิตประเด็นตรงที่คุณอยากรู้พอดี

circular reference ได้การจัดการของตัวเอง node ในวงวนจะไม่เข้าลำดับ topological ตลอดกาล แต่ละตัวถูกรายงานเป็นรายตัวเป็น xlcaiCircularReference และการตรวจไม่รัน solver แบบทำซ้ำ นั่นเป็นสัญญา read-only ที่ตั้งใจไว้: การเปิด iteration หรือไม่มีผลกับว่ารหัสผลลัพธ์ควรถูกตีความอย่างไร ไม่ได้มีผลกับว่าการตรวจทำอะไร กลไกของการประเมินแบบทำซ้ำมีเล่าแยกไว้ใน การคำนวณแบบ iterative กับ circular reference

การอ่านห่วงโซ่ความล้มเหลว

เมื่อสูตรประเมินไม่ผ่าน การรู้ว่าเซลล์ไหนล้มแทบไม่เคยพอ เพราะความล้มเหลวมักอยู่ลึกลงไปสามชั้นในห่วงโซ่ของ reference ประเด็นแต่ละรายการจึงพกสตริง Stack ที่เรนเดอร์กรอบนอกสุดมาก่อน ในรูป Sheet1!A1 > Sheet1!B2 > Data!C7 รายงานจึงชี้ไปที่เซลล์ที่แตกจริง ไม่ใช่เซลล์ที่คุณบังเอิญมองอยู่

ตัวบันทึกมีขอบเขต MaxStackFrames ค่าเริ่มต้น 64 มีพื้นขั้นต่ำที่ 8 และห่วงโซ่ที่ล้มลึกที่สุดคืออันที่ถูกเก็บไว้ กรอบชั้นในจะบันทึกห่วงโซ่เมื่อความล้มเหลวมีต้นกำเนิดที่นั่น และกรอบชั้นนอกที่คลายกลับทีหลังไม่เขียนทับมัน ถ้าห่วงโซ่ใดยาวเกินงบประมาณ Report.StackTruncated จะถูกตั้ง ซึ่งบอกคุณให้ได้ว่าความต่างระหว่างห่วงโซ่สั้นกับห่วงโซ่ที่คุณไม่ได้เห็นครบ

ห่วงโซ่ความล้มเหลวของการตรวจสอบ HotXLS: เมื่อสูตรที่อยู่ลึกลงไปสาม reference ล้ม Stack จะเรนเดอร์กรอบนอกสุดก่อน คือ Sheet1!A1 แล้ว Sheet1!B2 แล้ว Data!C7 กรอบชั้นในสุดบันทึกห่วงโซ่และกรอบที่คลายกลับไม่เขียนทับ MaxStackFrames ค่าเริ่มต้น 64 พื้นขั้นต่ำ 8 และ Report.StackTruncated ติดธงห่วงโซ่ที่คุณไม่ได้เห็นครบ — Stack เรนเดอร์กรอบนอกสุดก่อน รายงานจึงชี้เซลล์ที่แตกจริง ห่วงโซ่ที่ล้มลึกที่สุดคืออันที่ถูกเก็บ และ StackTruncated แยกห่วงโซ่สั้นออกจากห่วงโซ่ที่ถูกตัด

// ค่าเริ่มต้นเป็น read-only ApplyResults จะ commit overlay ก็ต่อเมื่อ
// การตรวจสอบสำเร็จครบรอบ ภายใต้ write guard ที่ปฏิเสธการ commit
// ถ้าโครงสร้างเวิร์กบุ๊กเปลี่ยนไประหว่างรอบตรวจกำลังวิ่ง
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // เทียบแบบเป๊ะ ให้ drift โผล่ให้เห็น
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // การตรวจหยุดที่ขอบ node ถัดไป
end;

เมื่อไรที่ควรปล่อยให้การตรวจซ่อมเวิร์กบุ๊ก

ก็ต่อเมื่อรอบตรวจกลับมาสะอาดจากประเด็นหมวดความล้มเหลวอย่างสมบูรณ์ ซึ่งเป๊ะกับเงื่อนไขที่ ApplyResults บังคับให้คุณอยู่แล้ว การ commit เกิดหลังรอบตรวจสำเร็จเต็มรูป ไม่ถูกยกเลิก และผ่าน guard เชิงโครงสร้าง: เอนจินไบนารีจ้องตัวระบุการเปลี่ยนแปลงของเวิร์กบุ๊ก เอนจิน OOXML สแนปชอต generation ของโครงสร้างรายชีต ถ้ามีอะไรขยับระหว่างรอบตรวจวิ่ง ผลลัพธ์จะบรรยายเวิร์กบุ๊กที่ไม่มีตัวตนแล้ว การ commit จึงถูกปฏิเสธ

สังเกตความไม่สมมาตรที่ตั้งใจไว้ cache mismatch ไม่ได้เป็นตัวขวางการ apply เพราะมันคือสิ่งที่การ commit ถือกำเนิดมาเพื่อซ่อม ประเด็นหมวดความล้มเหลวเป็นตัวขวางจริง เพราะเวิร์กบุ๊กที่สูตรบางตัวประเมินไม่ได้จะถูกซ่อมไปครึ่ง ๆ กลาง ๆ และเวิร์กบุ๊กครึ่งซ่อมแย่กว่าเวิร์กบุ๊กไม่ซ่อมที่คุณรู้ว่าควรไม่เชื่อ

ค่า tolerance เป็นการตัดสินนโยบาย ไม่ใช่ค่าเริ่มต้น

การเทียบค่าเริ่มต้นคือ absolute tolerance 1E-6 โดยปิด relative tolerance ซึ่งรักษาพฤติกรรมคลาสสิกและยอมรับ drift ขนาด 4E-7 อย่างเงียบ ๆ โดยทั่วไปนั่นถูกแล้ว ความต่างของลำดับการประเมิน floating point ระหว่างสิ่งที่ผลิตไฟล์กับ evaluator ปัจจุบันผลิตความต่างขนาดนี้บนผลรวมยาว ๆ ได้ และการรายงานมันเป็นประเด็นด้านความสมบูรณ์คือสัญญาณรบกวน

ตั้ง tolerance ทั้งคู่เป็นศูนย์เมื่อคำถามต่างออกไป เมื่อคุณกำลังหาว่า evaluator เปลี่ยนพฤติกรรมระหว่างเวอร์ชันหรือเปล่า หรือเครื่องมือของบุคคลที่สามเขียนค่าใหม่ด้วยวิถีที่เพี้ยนเบา ๆ หรือไม่ ที่ศูนย์ drift 4E-7 ตัวเดิมกลายเป็นสิ่งที่มองเห็น และทุกอย่างอื่นก็เช่นกัน เลือก tolerance ตามคำถามที่คุณกำลังถาม แล้วบันทึกการเลือกไว้ข้างรายงาน เพราะรายงานที่ไม่มี tolerance ของมันคือรายงานที่ตีความไม่ได้

อีกสองความสามารถที่อยู่ติดกันช่วยปิดภาพ เมื่ออยากรู้ว่าทำไมสูตรหนึ่งตัวให้ค่าที่มันให้ มุมมองทีละขั้นใน ตัวติดตามการประเมินสูตร คือเครื่องมือที่ถูกต้อง เมื่อคุณตั้งใจจะให้ค่า cache ถูกเชิดชูโดยไม่มีการคำนวณใหม่เลย อย่างบนเส้นทางรับงานที่ต้อง reproduce ไฟล์ให้ตรงกับตอนมาเป๊ะ โหมดนั้นมีเล่าไว้ใน การอ่านค่าสูตรจาก cache โดยไม่คำนวณใหม่ การตรวจสอบนี้คือสิ่งที่นั่งระหว่างสองตัวนั้น มันบอกคุณว่าการเชื่อ cache ปลอดภัยหรือยัง และมันมาพร้อมกับ HotXLS Delphi spreadsheet component สำหรับทั้งเอนจินไบนารีและ OOXML