การคำนวณสูตรซ้ำแบบเพิ่มหน่วยใน HotXLS สำหรับ Delphi

HotXLS ซึ่งเป็นไลบรารี Excel แบบเนทีฟสำหรับ Delphi และ C++Builder จะดำเนินการคำนวณสูตรซ้ำแบบเพิ่มหน่วยผ่านคำสั่ง TXLSXWorkbook.Recalculate การเรียกใช้ครั้งแรกจะทำหน้าที่สร้างแผนภูมิลำดับการขึ้นต่อกันของสูตร (formula dependency graph) และคำนวณผลลัพธ์ของสูตรในทุกเซลล์ การเรียกใช้ในครั้งถัดๆ ไปจะคำนวณซ้ำเฉพาะเซลล์ที่ได้รับผลกระทบจากการเขียนค่าใหม่นับตั้งแต่รอบที่แล้ว เรียงตามลำดับทอพอโลยี (topological order) ในการกวาดข้อมูลเพียงรอบเดียว ซึ่งต้นทุนเวลาในการคำนวณจะแปรผันตรงตามจำนวนของเซลล์ที่เปลี่ยนแปลง (dirty cells) แทนที่จะขึ้นอยู่กับขนาดของสมุดงานทั้งหมด

การตัดสินใจออกแบบจุดนี้คือสิ่งที่สร้างความแตกต่างระหว่างโมเดลทางการเงินที่ตอบสนองต่อการแก้ไขสมมติฐานในระดับมิลลิวินาที กับโมเดลที่ต้องค้างระบบรอนานหลายวินาที หากคุณต้องสร้างรายงานที่ช่องข้อมูลเข้าเพียงไม่กี่เซลล์จะป้อนค่าให้กับสูตรปลายทางนับพันสูตร เนื้อหาส่วนที่เหลือ of บทความนี้จะอธิบายถึงพฤติกรรมการทำงานของแผนภูมินี้ ฟังก์ชันใดบ้างที่ไม่เข้าร่วมกระบวนการเพิ่มหน่วย และวิธีการรายงานความผิดพลาดของวงจรอ้างอิงแบบวงกลม (circular reference) แทนการวนลูปแบบไม่สิ้นสุด

ทำไมการเปลี่ยนช่องเซลล์เดียวจึงทำให้ต้องคำนวณสูตรซ้ำถึงแสนสูตร?

เอนจินสูตรแบบดั้งเดิมที่ไม่มีการเก็บประวัติว่าสูตรใดขึ้นต่อกับสูตรใด ย่อมจำเป็นต้องคำนวณผลใหม่ทั้งหมดหลังการแก้ไขใดๆ เพื่อความปลอดภัย ที่แย่ไปกว่านั้น กลวิธีแบบเรียกซ้ำ (recursive) ทั่วไป — เช่น เมื่อสูตร A อ้างอิงถึงสูตร B จะสั่งให้คำนวณ B ณ วินาทีนั้นทันที — จะทำการคำนวณเซลล์ที่ถูกอ้างอิงใหม่โดยไม่มีเงื่อนไข และละทิ้งค่าที่แคชไว้ ห่วงโซ่ของสูตรจำนวน n สูตรที่แต่ละตัวอ้างอิงตัวก่อนหน้าจะใช้จำนวนรอบการคำนวณในระดับ O(n²) ต่อการกวาดข้อมูลแบบสมบูรณ์หนึ่งรอบ และการอ้างอิงแบบวงกลมจะทำให้การเรียกซ้ำล้มเหลวโดยสิ้นเชิง นักพัฒนาสเปรดชีตทุกคนที่เคยเชื่อมโยงโมเดลแบบลดหลั่นเข้ากับตัวประเมินแบบเรียกซ้ำต่างเคยพบเห็นการล้มเหลวทั้งสองลักษณะนี้มาแล้ว

โปรแกรม Excel ได้แก้ไขปัญหานี้ตั้งแต่หลายทศวรรษก่อนด้วยห่วงโซ่การคำนวณ (calculation chain): ซึ่งเป็นการจัดลำดับเซลล์สูตรไว้เพื่อเมื่อมีการแก้ไขเกิดขึ้น จะมีเพียงเซลล์กลุ่มเล็กๆ เท่านั้นที่ถูกทำเครื่องหมายว่าเปลี่ยนแปลง (dirty) และเอนจินจะดำเนินการตรวจสอบเฉพาะส่วนท้ายของห่วงโซ่ที่ได้รับผลกระทบ HotXLS นำแนวคิดเดียวกันนี้มาประยุกต์ใช้เป็นแผนภูมิลำดับการขึ้นต่อกันที่ชัดเจน ซึ่งสร้างขึ้นเพียงครั้งเดียวจากโครงสร้างสูตรที่คอมไพล์แล้ว และนำกลับมาใช้ซ้ำในแต่ละรอบการคำนวณ จุดสำคัญไม่ใช่เรื่องของความชาญฉลาด แต่เป็นประเด็นที่ว่าต้นทุนของการคำนวณใหม่ควรแปรผันตามขนาดของการแก้ไขของคุณ ไม่ใช่ขนาดของสมุดงาน

แผนภูมิลำดับการขึ้นต่อกันเปลี่ยนการแก้ไขข้อมูลให้เป็นการกวาดข้อมูลรอบเดียวได้อย่างไร

แผนภูมิลำดับการขึ้นต่อกันของ HotXLS จะกำหนดโหนดให้แต่ละเซลล์สูตร โดยมีเส้นขอบชี้จากตัวตั้งต้น (precedent) ไปยังตัวที่ขึ้นต่อกัน (dependent) เมื่อรหัสของคุณเขียนค่าใหม่ลงในเซลล์ สมุดงานจะบันทึกเซลล์นั้นว่าเป็น dirty และเมื่อฟังก์ชัน Recalculate ทำงาน สถานะ dirty จะกระจายตามเส้นขอบไปยังสูตรปลายทางทั้งหมด และแผนภูมิย่อยที่ dirty จะถูกคำนวณใหม่เพียงครั้งเดียวตามลำดับทอพอโลยีด้วยอัลกอริทึมของ Kahn เนื่องจากสูตรปลายทางจะไม่ถูกเข้าไปจัดการก่อนตัวตั้งต้นของมัน โหนดแต่ละโหนดจึงต้องการการประเมินเพียงครั้งเดียว — ซึ่งนี่ทำให้กระบวนการนี้เป็นแบบ O(dirty)

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

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');
    Model  := Book.Sheets.Add('Model');

    Inputs.Cells[2, 2].Value := 0.05;                 // สมมติฐานการเติบโต
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';    // สูตร XLSX ไม่ต้องมีเครื่องหมาย '=' นำหน้า
    Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
    // ... แถวอีกหลายพันแถวที่ลดหลั่นจากสมมติฐานเดียวกัน ...

    Book.Recalculate;                 // เรียกครั้งแรก: สร้างแผนภูมิ และคำนวณผลฉบับเต็ม

    Inputs.Cells[2, 2].Value := 0.07; // แก้ไขจุดเดียวทำให้เซลล์เดียวกลายเป็น dirty
    Book.Recalculate;                 // เรียกครั้งสอง: รันเฉพาะห่วงโซ่ปลายทางเท่านั้น
  finally
    Book.Free;
  end;
end;

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

ฟังก์ชัน Excel ใดบ้างที่บังคับให้ต้องคำนวณใหม่ในทุกรอบการกวาดข้อมูล?

HotXLS จะถือว่าฟังก์ชัน NOW, TODAY, RAND, OFFSET และ INDIRECT เป็นฟังก์ชันผันผวน (volatile): สูตรใดๆ ที่ประกอบด้วยฟังก์ชันเหล่านี้จะถูกคำนวณผลใหม่ในทุกรอบการทำงานของ Recalculate โดยไม่คำนึงว่าข้อมูลต้นทางจะมีการเปลี่ยนแปลงหรือไม่ สามฟังก์ชันแรกเป็นฟังก์ชันผันผวนด้วยเหตุผลเดียวกับใน Excel — คือผลลัพธ์ขึ้นอยู่กับช่วงเวลาที่คำนวณ ไม่ใช่เซลล์ข้อมูลอื่น ส่วนฟังก์ชัน OFFSET และ INDIRECT มีความผันผวนด้วยเหตุผลที่ซับซ้อนกว่า: คือเซลล์ที่มันอ่านจะถูกคำนวณ ณ เวลาทำงานจริง (run time) แผนภูมิจึงไม่สามารถทราบข้อมูลล่วงหน้าว่าควรกำหนดเส้นทางการอ้างอิงไปที่จุดใด

กฎความระมัดระวังแบบเดียวกันนี้ยังครอบคลุมการอ้างอิงที่ตัวสร้างแผนภูมิไม่สามารถระบุพื้นที่สี่เหลี่ยมเดี่ยวๆ ได้อย่างชัดเจน สูตรที่อ้างถึงชื่อช่วงข้อมูลแบบหลายพื้นที่ (multi-area) หรือสูตรที่อ้างอิงถึงสมุดงานภายนอก จะถูกลดระดับให้เป็นสูตรผันผวนและคำนวณใหม่ในทุกรอบเช่นกัน นโยบายนี้ได้รับการกำหนดโดยเจตนา: การคำนวณเผื่อไว้จะเสียเวลาเพิ่มขึ้นเล็กน้อย แต่เส้นขอบการอ้างอิงที่ตกหล่นไปจะหมายถึงค่าข้อมูลเก่าค้างในรายงานที่ส่งมอบ ซึ่งเป็นข้อผิดพลาดที่ร้ายแรงกว่ามาก หากโมเดลของคุณต้องพึ่งพาชื่อช่วงข้อมูลขอบเขตสมุดงาน บทความคู่ขนานเกี่ยวกับการกำหนดชื่อช่วงข้อมูลและสูตรข้ามแผ่นงาน จะครอบคลุมวิธีการจัดทำและเข้าถึงชื่อช่วงข้อมูลแบบพื้นที่เดียว ซึ่งทำงานร่วมกับแผนภูมินี้ได้ตามปกติ

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

HotXLS รายงานวงจรอ้างอิงแบบวงกลมอย่างไร?

ฟังก์ชัน TXLSXWorkbook.Recalculate จะส่งกลับค่า lxOk เมื่อประมวลผลผ่านด้วยดี และส่งกลับค่า lxErrorRef เมื่อตรวจพบวงจรอ้างอิง (reference cycle) สมาชิกของวงจรจะถูกระบุระหว่างขั้นตอนการจัดเรียงทอพอโลยี — ซึ่งก็คือโหนดที่อัลกอริทึมของ Kahn ไม่สามารถปลดปล่อยออกมาได้ — และพวกมันจะถูกข้ามไปแทนการวนลูป: ค่าในแคชจะยังคงเป็นเหมือนเดิม ในขณะที่สูตรทุกตัวภายนอกวงจรยังคงคำนวณผลได้ตามปกติและเรียงตามลำดับอย่างถูกต้อง จุดเรียกใช้รหัสของคุณจะได้รับรหัสข้อผิดพลาดที่ชัดเจนแทนการค้างของระบบ

case Book.Recalculate of
  lxOk:
    SaveReport(Book);
  lxErrorRef:
    // มีวงจรอ้างอิงอยู่ สมาชิกวงจรจะคงค่าเดิม ในแคชไว้ และส่วนนอกวงจรทั้งหมดจะได้รับการปรับปรุง
    LogWarning('Circular reference detected - review model inputs');
end;

การค้นหาว่าเซลล์ใดเป็นตัวสร้างวงจรเป็นเรื่องของกระบวนการดีบัก และเครื่องมือติดตามการคำนวณสูตร (formula evaluation tracer) คือคำตอบสำหรับงานนี้: ทำการติดตามสูตรที่ต้องสงสัยแล้วสายโซ่อ้างอิงที่ย้อนกลับมาทับซ้อนตัวเองจะแสดงให้เห็นทีละขั้นตอน วงจรอ้างอิงในระบบงานจริงเกือบทั้งหมดเกิดจากความผิดพลาดในขั้นตอนเขียนสูตร — เช่น แถวสรุปผลสรุปรวมช่วง SUM ของตัวมันเองเข้าไปโดยบังเอิญ — ดังนั้นการแจ้งรหัสข้อผิดพลาดเมื่อเริ่มคำนวณซ้ำจึงเป็นพฤติกรรมที่คุณต้องการอย่างแท้จริง

สูตรอาร์เรย์, ระบบติดตาม dirty และขั้นตอนสร้างแผนภูมิใหม่

สูตรอาร์เรย์ CSE จะได้รับโหนดเดียวสำหรับพื้นที่สี่เหลี่ยมที่ยึดโยงทั้งหมด ไม่ใช่หนึ่งโหนดต่อหนึ่งเซลล์ สูตรที่โหนดรากจะถูกประเมินเพียงครั้งเดียวในแต่ละรอบ เมทริกซ์ผลลัพธ์จะถูกเขียนลงไปในเซลล์สมาชิกแต่ละตัวโดยตรง และสูตรอื่นๆ ที่อ้างอิงถึงเซลล์ใดๆ ภายในช่วงข้อมูลที่ยึดโยง — ไม่เฉพาะเจาะจงเฉพาะจุดยึดมุมซ้ายบน — จะดึงเส้นขอบการอ้างอิงจากโหนดรากนั้น ผลลัพธ์แบบสเกลาร์ (scalar) จะกระจายตัวทั่วพื้นที่สี่เหลี่ยมตามข้อกำหนดดั้งเดิมของสูตรอาร์เรย์ใน Excel

ระบบติดตาม dirty จะทำงานร่วมกับตัวกำหนดคุณสมบัติทั่วไป (property setter) เพื่อให้รหัสของคุณทำงานได้ปกติไม่มีการเปลี่ยนแปลง การเขียน Value ลงในเซลล์จะแจ้งเตือนสมุดงานและทำเครื่องหมายเซลล์ปลายทางว่าเป็น dirty ส่วนการเขียน Formula ใหม่ถือเป็นการเปลี่ยนแปลงโครงสร้าง จึงทำเครื่องหมายว่าแผนภูมิทั้งหมดหมดอายุ และเมื่อเรียกใช้ Recalculate ครั้งถัดไป ระบบจะสร้างแผนภูมิขึ้นใหม่ก่อนประเมินผล การเพิ่ม การลบ หรือการย้ายแผ่นงาน (sheet) จะทำให้แผนภูมิหมดอายุเช่นเดียวกัน เนื่องจากค่าเอกลักษณ์ของโหนดประกอบด้วยดัชนีของแผ่นงาน เมื่อไม่มีการเปิดใช้งานแผนภูมิ — เช่น สมุดงานที่คุณไม่เคยเรียกใช้ Recalculate เลย — ระบบติดตามจะจ่ายต้นทุนเพียงการตรวจสอบค่า nil หนึ่งครั้งต่อการเขียนค่า ทำให้งานเขียนอ่านทั่วไปไม่ได้รับผลกระทบใดๆ

มีขอบเขตการทำงานประการหนึ่งที่ระบุไว้อย่างตรงไปตรงมา: แผนภูมิจะติดตามการขึ้นต่อกันระหว่างเซลล์ ดังนั้นฟังก์ชันที่ผู้ใช้กำหนด (user-defined function) ที่ลงทะเบียนผ่าน OnUserFunction จะถูกคำนวณซ้ำเมื่อเซลล์ข้อมูลเข้าของอาร์กิวเมนต์มีการเปลี่ยนแปลง เช่นเดียวกับสูตรทั่วไป หากคุณกำลังขยายเอนจินการทำงานด้วยวิธีนี้ บทความเกี่ยวกับการสร้างฟังก์ชันแบบกำหนดเองในเอนจินสูตรของ HotXLS จะนำคุณไปทำความเข้าใจเกี่ยวกับเวิร์กโฟลว์ของคอลแบ็กและวิธีการส่งผ่านข้อมูลอาร์กิวเมนต์

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