การคำนวณแบบวนซ้ำสำหรับการอ้างอิงแบบวงกลมใน Delphi

สำหรับการคำนวณการอ้างอิงแบบวงกลม (circular references) โดยเจตนาใน Delphi นั้น HotXLS มีฟังก์ชันการคำนวณแบบวนซ้ำ (iterative calculation) บนเอนจิน XLSX: โดยกำหนด TXLSXWorkbook.Iterate เป็น True และ TXLSXWorkbook.Recalculate จะไล่ตรวจหาแต่ละวงรอบการอ้างอิงเพื่อลู่เข้าหาจุดคงที่ — สูงสุดตามจำนวนรอบ IterateCount หรือจนกว่าเซลล์ทุกเซลล์จะเกิดการเปลี่ยนแปลงน้อยกว่าค่า IterateDelta — แทนที่จะส่งคืนค่าความผิดพลาด #REF! และยกเลิกการทำงาน

ความแตกต่างดังกล่าวมีความสำคัญมากกว่าที่คุณสมบัติประเภทบูลีนเพียงตัวเดียวจะสื่อถึง เอนจินตัวเดียวกันที่ทำหน้าที่ตรวจหาวงรอบการอ้างอิงและปฏิเสธที่จะวนซ้ำนั้น เมื่อมีการเปลี่ยนค่าคุณสมบัติข้อนี้เพียงจุดเดียว จะเปลี่ยนเป็นการคำนวณวงรอบนั้นโดยเจตนาจนกว่าค่าจะคงที่ การแยกแยะพฤติกรรมทั้งสองแบบนี้ให้ชัดเจน — ว่าเมื่อใดวงรอบคือข้อบกพร่องที่ต้องรายงาน และเมื่อใดคือโมเดลคณิตศาสตร์ที่ต้องคำนวณหาคำตอบ — คือหัวข้อทั้งหมดของบทความนี้

เหตุใดการอ้างอิงแบบวงกลมจึงเกิดข้อผิดพลาดตามค่าเริ่มต้น?

ตามค่าเริ่มต้น HotXLS จะถือว่าวงรอบการอ้างอิงใดๆ เป็นความผิดพลาดในการเขียนสูตรและจะรายงานข้อผิดพลาดนั้นแทนการคำนวณ TXLSXWorkbook.Recalculate จะสร้างกราฟความขึ้นต่อกันของสูตร ประมวลผลสูตรในแต่ละเซลล์ตามลำดับโทโพโลยี และส่งคืน lxErrorRef ทันทีที่ตรวจพบวงรอบ — ซึ่งคือโหนดที่ไม่สามารถจัดระเบียบได้ในขั้นตอนการจัดเรียงลำดับโทโพโลยี สมาชิกในวงรอบเหล่านั้นจะยังคงค่าที่แคชไว้เดิม ในขณะที่สูตรอื่นๆ ทั้งหมดที่อยู่นอกวงรอบจะยังคงได้รับการประมวลผลตามปกติ กลไกการทำงานของกราฟนี้และสาเหตุที่สมาชิกในวงรอบถูกข้ามไปแทนการวนซ้ำจะอธิบายในบทความคู่กันเรื่อง incremental formula recalculation and the dependency graph

การตั้งค่าเริ่มต้นเป็นทางเลือกที่ปลอดภัยเนื่องจากวงรอบส่วนใหญ่คือบั๊ก: เช่น แถวสรุปยอดเงินถูกดึงเข้าไปรวมอยู่ในช่วงคำนวณ SUM ของตัวเองโดยอุบัติเหตุ หรือการคัดลอกและวางที่ทำให้การอ้างอิงชี้กลับมาที่ตัวเอง รหัสข้อผิดพลาดที่แจ้งเตือนชัดเจนในเวลาคำนวณใหม่คือสิ่งที่คุณต้องการในกรณีดังกล่าว อย่างไรก็ตาม มีโมเดลคณิตศาสตร์เฉพาะและสำคัญบางกลุ่มที่ต้องใช้วงรอบอ้างอิงโดยเจตนา เช่น ตารางคำนวณดอกเบี้ยทบต้น, การปันส่วนต้นทุนหรือค่าใช้จ่ายทางอ้อมแบบวงกลมระหว่างแผนก และการคำนวณค่าธรรมเนียมที่อิงกับยอดเงินคงเหลือ ซึ่งทั้งหมดนี้ค่าที่ได้จะป้อนกลับไปยังข้อมูลอินพุตของตัวเองอย่างสมเหตุสมผล — และ Excel จะยอมคำนวณค่าเหล่านี้ก็ต่อเมื่อผู้ใช้เปิดใช้งานตัวเลือก File → Options → Formulas → Enable iterative calculation เท่านั้น

จะเปิดใช้งานการคำนวณแบบวนซ้ำใน HotXLS ได้อย่างไร?

HotXLS มีคุณสมบัติสามประการบน TXLSXWorkbook เพื่อทำหน้าที่สะท้อนช่องทำเครื่องหมายใน Excel: โดย Iterate จะทำหน้าที่เปิดโหมดการทำงานนี้ และเมื่อมีค่าเป็น True วงรอบที่ตรวจพบจะถูกส่งไปยังตัวแก้ปัญหาแบบวนซ้ำแทนการรายงานข้อผิดพลาด lxErrorRef ลองพิจารณาคู่เซลล์คำนวณดอกเบี้ยทบต้นที่เป็นกรณีมาตรฐาน ซึ่งยอดเงินคงเหลือปลายงวดขึ้นอยู่กับดอกเบี้ย และดอกเบี้ยขึ้นอยู่กับยอดเงินคงเหลือ

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 อ้างอิง B4 และ B4 อ้างอิง B3 ดังนั้นกราฟความขึ้นต่อกันจึงรายงานวงรอบที่มีโหมดสองโหนด หากปล่อย Iterate เป็น False ตามค่าเริ่มต้น คู่เซลล์นี้จะส่งคืนข้อผิดพลาด lxErrorRef และค่าในเซลล์จะไม่ลู่เข้าหาคำตอบ แต่เมื่อกำหนดเป็น True เมธอด Recalculate จะนำค่าที่แคชในปัจจุบันมาเป็นค่าเริ่มต้นของวงรอบและคำนวณสมาชิกซ้ำไปเรื่อยๆ ทีละรอบ โดยส่งเอาต์พุตของรอบก่อนหน้ากลับไปเป็นอินพุตของรอบถัดไปจนกระทั่งตัวเลขหยุดเคลื่อนไหว ในตัวอย่างนี้สูตรลู่เข้าสำเร็จคือ principal / (1 - rate) ดังนั้นยอดเงินคงเหลือจะคงที่ที่ 1052.63 และดอกเบี้ยคงที่ที่ 52.63 — ซึ่งเป็นตัวเลขเดียวกับที่ Excel คำนวณเมื่อเปิดทำงานการวนซ้ำ

อะไรทำให้การวนซ้ำหยุดทำงาน?

มีเงื่อนไขการหยุดทำงานสองประการที่เป็นอิสระต่อกันซึ่งคอยควบคุมตัวแก้ปัญหา และการทำความเข้าใจเงื่อนไขทั้งสองจะช่วยป้องกันไม่ให้โมเดลเกิดข้อผิดพลาดหรือทำงานวนซ้ำไม่สิ้นสุด IterateCount คือขีดจำกัดสูงสุดของการคำนวณซ้ำสำหรับสมาชิกในวงรอบ มีค่าเริ่มต้นเป็น 100 ตามมาตรฐาน Excel ส่วน IterateDelta คือเกณฑ์ขีดจำกัดการลู่เข้า (convergence threshold): หลังจากผ่านการคำนวณแต่ละรอบ ตัวแก้ปัญหาจะวัดค่าการเปลี่ยนแปลงทางตัวเลขที่มากที่สุดในเซลล์ของวงรอบทั้งหมด และเมื่อค่าการเปลี่ยนแปลงสูงสุดนั้นต่ำกว่า IterateDelta — ซึ่งมีค่าเริ่มต้นเป็น 0.001 — ลูปการวนซ้ำจะหยุดทำงานทันที เงื่อนไขใดก็ตามที่บรรลุก่อนจะเป็นตัวสิ้นสุดการวนซ้ำ

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;

ผลลัพธ์ประการหนึ่งที่ควรชี้แจงอย่างตรงไปตรงมาเนื่องจากเป็นขีดจำกัดของฟีเจอร์นี้คือ: เมื่อการวนซ้ำทำงานถึงจำนวนรอบสูงสุดแล้วโดยที่ค่าการเปลี่ยนแปลงยังไม่ต่ำกว่า IterateDelta ตัวฟังก์ชัน SolveCycleIteratively จะไม่มีการแสดงข้อผิดพลาด (exception) ใดๆ — มันจะส่งกลับ lxOk และคงค่าตัวเลขที่คำนวณได้ในรอบสุดท้ายค้างไว้ในเซลล์ เช่นเดียวกับพฤติกรรมของ Excel ที่จะเขียนตัวเลขจากการคำนวณรอบสุดท้ายค้างไว้เมื่อหมดจำนวนรอบวนซ้ำแล้วโดยที่ค่าไม่ลู่เข้าหาคำตอบ ดังนั้นรหัสผลลัพธ์สำเร็จจาก Recalculate ภายใต้โหมดการวนซ้ำจึงหมายถึง "ตัวแก้ปัญหาได้ทำงานแล้ว" ไม่ใช่ "ตัวแก้ปัญหาลู่เข้าหาคำตอบสำเร็จ" โมเดลที่วงรอบลู่ออก (diverge) หรือแกว่งตัวจะทำงานไปจนครบจำนวนรอบ IterateCount เงียบๆ และส่งคืนตัวเลขที่ไม่ได้เป็นจุดคงที่เลย และไม่มีข้อผิดพลาดแจ้งเตือนความผิดปกตินี้

การบันทึกการตั้งค่าลงในไฟล์ XLSX และ XLS ทำงานอย่างไร?

การตั้งค่าการคำนวณแบบวนซ้ำจะถูกบันทึกไว้อย่างคงทนในรูปแบบไฟล์สเปรดชีตทั้งสองประเภท ดังนั้นเวิร์กบุ๊กที่เปิดใน Excel จะทำงานสอดคล้องตามที่โค้ด Delphi ของคุณกำหนดไว้ ในฝั่งของ XLSX ตัวเขียนจะส่งออกองค์ประกอบ OOXML <calcPr> เฉพาะเมื่อ Iterate เป็น True และจะละเว้นแอตทริบิวต์ใดๆ ที่ยังคงใช้ค่าเริ่มต้นอยู่เพื่อรักษาขนาดไฟล์ให้เล็กที่สุด: เวิร์กบุ๊กที่ใช้ค่าเริ่มต้นจะเขียนข้อมูลเพียง <calcPr iterate="1"/> ในขณะที่ iterateCount จะปรากฏเฉพาะเมื่อมีค่าต่างจาก 100 และ iterateDelta ปรากฏเฉพาะเมื่อมีค่าต่างจาก 0.001 ในขั้นตอนเปิดไฟล์ TXLSXWorkbook จะอ่านแอตทริบิวต์ทั้งสามนี้กลับคืนมา ทำให้การส่งข้อมูลไปกลับมีความสมมาตรกัน

เอนจิน BIFF8 (.xls) แบบเก่าคือ TXLSWorkbook จะเก็บสถานะการทำงานที่เทียบเท่ากันนี้ผ่านระเบียน (record) แยกต่างหากสามระเบียนภายใต้คุณสมบัติสามตัวที่ต่างออกไป: โดย EnableIteration จะจับคู่กับระเบียน CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations จับคู่กับระเบียน CalcCount ($000C, [MS-XLS] §2.4.31) และ MaxIterationChange จับคู่กับระเบียน CalcDelta ($0010, [MS-XLS] §2.4.32) ตัวกำหนดค่า (setter) จะควบคุมช่วงค่าตามข้อกำหนดการทำงาน — CalcCount จะต้องอยู่ระหว่าง 1 ถึง 32767 ดังนั้น MaxIterations จึงถูกควบคุมช่วง และค่า MaxIterationChange ที่ติดลบจะถูกเปลี่ยนกลับเป็นค่าเริ่มต้น 0.001 อัตโนมัติ การกำหนดค่าเหล่านี้บนเวิร์กบุ๊ก .xls จะทำให้ระบบเขียนระเบียนการคำนวณทั้งสามตัวอย่างถูกต้องเมื่อบันทึกไฟล์

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;

โปรดสังเกตความแตกต่างของการตั้งชื่อโดยเจตนา: เอนจิน XLSX จะใช้คำว่า Iterate / IterateCount / IterateDelta (ตามไวยากรณ์ OOXML) ในขณะที่เอนจิน BIFF8 จะใช้คำว่า EnableIteration / MaxIterations / MaxIterationChange (สอดคล้องตามชื่อระเบียนของ [MS-XLS]) ทั้งสองกลุ่มคุณสมบัตินี้จะอธิบายปุ่มควบคุมสามปุ่มเดียวกัน — ได้แก่ สวิตช์เปิด/ปิด, ขีดจำกัดรอบการวนซ้ำ และเกณฑ์การลู่เข้า — พร้อมค่าเริ่มต้นเดียวกันคือ ปิดใช้งาน, 100 และ 0.001

เมื่อใดการอ้างอิงแบบวงกลมคือบั๊ก ไม่ใช่โมเดลคณิตศาสตร์?

การเปิดใช้งานการวนซ้ำไม่ใช่วิธีการที่จะทำให้คำเตือนเรื่องการอ้างอิงแบบวงกลมหายไป และการจัดการในลักษณะดังกล่าวคือกับดัก การเปิด Iterate เป็น True ในระดับโกลบอลจะเปลี่ยนความผิดพลาดของวงรอบอ้างอิงที่เกิดขึ้นโดยอุบัติเหตุ — ซึ่งเป็นสิ่งที่รหัสข้อผิดพลาดเริ่มต้นถูกออกแบบมาเพื่อแจ้งเตือน — ให้กลายเป็นตัวเลขที่ลู่เข้าหรือไม่ลู่เข้าเงียบๆ แนวทางปฏิบัติที่ถูกต้องคือ: ควรกำหนด Iterate เป็น False ในโหมดการทำงานปกติ เพื่อให้ความผิดพลาดในการเขียนสูตรจริงแสดงรหัสข้อผิดพลาด lxErrorRef ออกมา และเปิดใช้งานการวนซ้ำเฉพาะบนเวิร์กบุ๊กที่มีการอ้างอิงแบบวงรอบโดยการออกแบบและเป็นที่เข้าใจดีแล้วเท่านั้น

เมื่อมีวงรอบอ้างอิงปรากฏขึ้นและคุณไม่แน่ใจว่าเป็นประเภทใด เครื่องมือติดตาม formula evaluation tracer จะช่วยให้คุณแยกแยะความแตกต่างได้: โดยการติดตามสูตรที่ต้องสงสัย และตรวจสอบห่วงโซ่การอ้างอิงที่ชี้กลับมาที่ตัวเองทีละขั้นตอน เพื่อให้คุณตัดสินใจได้ว่าเป็นความต้องการป้อนกลับข้อมูลจริงหรือการอ้างอิงตัวเองโดยอุบัติเหตุ นอกจากนี้พึงระลึกว่าเซลล์ในวงรอบสามารถเรียกใช้ฟังก์ชันสำเร็จรูปใดๆ ในระหว่างการวนลูปได้ — ตัวประมวลผลคำนวณเดียวกันที่ใช้จัดการกับสูตร engineering or complex-number formula จะทำการประมวลผลสมาชิกในวงรอบในทุกๆ รอบการทำงาน — ดังนั้นโมเดลที่ลู่ออกมักจะเป็นปัญหาจากตัวสูตรภายในลูป ไม่ใช่ปัญหาของการตั้งค่าการคำนวณแบบวนซ้ำ

รายการตรวจสอบในทางปฏิบัติสั้นๆ คือ: ตรวจสอบให้มั่นใจว่าลูปการคำนวณมีจุดลู่เข้าคงที่จริงก่อนที่จะเลือกพึ่งพาการวนซ้ำ, กำหนดค่า IterateDelta ให้แคบพอที่จะรับรองความถูกต้องของคำตอบตามที่โมเดลของคุณต้องการ และหลังจากสั่ง Recalculate ที่คุณคาดหวังให้ลู่เข้าหาคำตอบ ให้ทำการสุ่มตรวจสอบผลลัพธ์ของเซลล์ที่ทราบค่าแทนที่จะไว้วางใจรหัสสำเร็จ lxOk เพียงอย่างเดียว เนื่องจากรหัสดังกล่าวไม่สามารถแยกแยะระหว่างการลู่เข้าสำเร็จกับการทำงานถึงขีดจำกัดรอบสูงสุดที่กำหนดไว้ได้

การคำนวณการอ้างอิงแบบวงกลมแบบวนซ้ำเป็นส่วนหนึ่งของเอนจิน XLSX ใน HotXLS Delphi Excel Component ควบคู่ไปกับระบบการคำนวณใหม่แบบเพิ่มพูนตามกราฟความขึ้นต่อกัน และการบันทึกข้อมูลแบบ OOXML และ BIFF8 ที่ช่วยเก็บค่าการตั้งค่านี้ไว้ในทุกไฟล์ที่คุณเขียน สำหรับโมเดลทางการเงินและวิศวกรรมที่มีการอ้างอิงแบบวงรอบโดยเจตนา ฟังก์ชันนี้คือจุดแบ่งแยกระหว่างรหัสข้อผิดพลาดกับตัวเลขคำตอบที่ถูกต้อง