การสแกน lookup กับ circular reference หลอกใน HotXLS

ใส่ =VLOOKUP(A1,B:B,1) ลงในเซลล์ในคอลัมน์ B แล้ว Excel คำนวณมันให้โดยไม่มีข้อร้องเรียน แต่พอส่งเวิร์กบุ๊กเดียวกันเข้าเอนจินคำนวณใหม่แบบกราฟ dependency คุณมักเจอ circular reference error เพราะสูตรขึ้นกับช่วงที่มีตัวสูตรอยู่ข้างใน HotXLS รายงานอย่างนั้นจนถึง v2.361.98 ทางแก้ไม่ใช่กรณีพิเศษสำหรับช่วงทั้งคอลัมน์ แต่เป็นการแยกขอบ dependency สองชนิดที่เอนจิน spreadsheet ต้องการ และกราฟ directed ธรรมดาไม่มี

อาร์กิวเมนต์ lookup-array ของตระกูล lookup ทั้ง LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP และ XMATCH ถูกติดเครื่องหมายเป็น scan reference ตอนนี้ scan reference ยังหว่านสถานะ dirty อยู่ การแก้เซลล์ข้างในช่วงจึงทำให้สูตรถูกคำนวณใหม่ แต่มันไม่มีวันมีส่วนร่วมในการตรวจวงวนหรือการจัดลำดับการประเมินอีก วงวนจริงยังถูกจับได้ ส่วนวงวนหลอกหมดไป

ทำไม Excel จึงอนุญาตให้ช่วง lookup มีสูตรอยู่ข้างใน

เพราะอาร์กิวเมนต์ตัวนี้ไม่ได้ถูกบริโภคแบบ operand เชิงเลขคณิต ตระกูล lookup สแกนช่วงหาค่าที่ cache ไว้แล้วคืนรายการที่ตรง มันไม่ต้องการให้ช่วงถูกประเมินจนเสร็จก่อน Excel ปฏิบัติกับช่วง lookup ที่ทับกับตัวเองเป็นการอ่านสิ่งที่เซลล์เหล่านั้นถืออยู่ ณ ขณะนั้น ซึ่งเป็น semantics เดียวกับที่มันใช้กับเวิร์กบุ๊กแบบไม่ทำซ้ำทั่วไป: เซลล์ที่ยังไม่ถูกคำนวณใหม่ในรอบนี้ยกค่าที่คำนวณครั้งสุดท้ายออกมาใช้

การอ้างทั้งคอลัมน์ทำให้เรื่องนี้เป็นกรณีปกติ ไม่ใช่ของแปลก B:B เป็นวิธีเขียนตามครรลองว่า "ตาราง lookup ทั้งตาราง" ในชีตที่แถวถูกเพิ่มเรื่อย ๆ และสูตรใดที่อาศัยอยู่ในคอลัมน์ B ก็จะอยู่ข้างในช่วง lookup ของตัวเองทันที โมเดลการเงิน ชีตกระทบยอด และเวิร์กบุ๊กตรวจสอบบัญชีทำแบบนี้ตลอดเวลา โดยที่มักไม่มีใครสังเกตเลยว่าช่วงมันทับกัน

เซลล์ B7 ถือ VLOOKUP(A1,B:B,1) อยู่ข้างในช่วง lookup ทั้งคอลัมน์ B:B ของตัวเอง เป็นการทับตัวเองที่ Excel คำนวณจากค่า cache โดยไม่มีข้อร้องเรียน — ช่วง lookup ทั้งคอลัมน์ทำให้การทับตัวเองเป็นกรณีปกติในโมเดลการเงินและเวิร์กบุ๊กตรวจสอบบัญชี ไม่ใช่มุมหายาก

กราฟ dependency ทำอะไรกับสูตรเดียวกันนี้

HotXLS คำนวณใหม่แบบ incremental ซึ่งต้องใช้กราฟ dependency จริง: โหนดสำหรับเซลล์ ขอบสำหรับการอ้าง ลำดับเชิงโทโพโลจีสำหรับการประเมิน และรอบ strongly connected component เพื่อจำแนกวงวน กลไกชุดนี้อธิบายไว้ในบทความการคำนวณใหม่แบบ incremental และมันคือเหตุผลพอดีที่ false positive ปรากฏ

ดึง dependency ออกจาก =VLOOKUP(A1,B:B,1) ในเซลล์ B7 แล้วอาร์กิวเมนต์ที่สองให้ช่วงที่มี B7 ตัวเองอยู่ข้างใน กราฟมี self-loop ขึ้นมา in-degree ของโหนดนั้นไม่มีวันถึงศูนย์ รอบ topological จึงจัดคิวมันไม่ได้เลย และรอบ component จำแนกมันเป็นวงวน เอนจินให้เหตุผลกับกราฟที่ได้รับมาอย่างถูกต้อง แต่กราฟต่างหากคือโมเดลที่ผิด เพราะมันเข้ารหัสขอบชนิดเดียว ทั้งที่ spreadsheet มีสองชนิด

ช่วง lookup B:B ให้โหนดกราฟ B7 มี self-loop in-degree จึงไม่เคยถึงศูนย์ และ HotXLS ก่อน v2.361.98 รายงาน circular reference หลอก — เอนจินคำนวณใหม่ให้เหตุผลกับกราฟที่ได้รับมาอย่างถูกต้อง แต่กราฟต่างหากเป็นโมเดลที่ผิดสำหรับ spreadsheet

สองชั้นขอบ หนึ่งกราฟ

การเปลี่ยนแปลงเพิ่ม flag เข้า record การอ้างที่ resolve แล้ว คือ TXLSDepRange.LookupScan ซึ่งตัวดึง dependency จะตั้งเมื่อมันเดินผ่านอาร์กิวเมนต์ lookup-array ของหนึ่งในหกฟังก์ชัน ปลายทาง ขอบที่มาจากการอ้างเหล่านี้ถูกเก็บแยกจากขอบธรรมดา: โหนดกราฟเก็บรายการ ScanDependents และ ScanPrecedents เคียงกับรายการ dependent และ precedent ปกติ

การแยกชุดนี้ทำให้ semantics ถูกต้อง ขอบสแกนถูกเดินโดยการ propagate สถานะ dirty การแก้ที่ใดก็ได้ใน B:B จึงยังติดเครื่องหมาย B7 ให้ dirty และ B7 ถูกคำนวณใหม่ ขอบสแกนไม่เคยถูกนับเข้า in-degree และไม่เคยเข้าตัวสร้าง component จึงสร้าง deadlock เชิงโทโพโลจีไม่ได้ และถูกจำแนกเป็นวงวนไม่ได้ implementation กราฟทั้งสองในไลบรารี ทั้งกราฟรายเวิร์กบุ๊กแบบคลาสสิกและกราฟ workspace ข้ามเวิร์กบุ๊กที่แบกการวิเคราะห์ component ถูกแก้พร้อมกัน ปล่อยให้ทั้งสองหลงทางแยกกันแล้วจะได้เวิร์กบุ๊กที่คำนวณใหม่ต่างกันตามว่ามันถูกเปิดเดี่ยวหรือเปิดเป็นส่วนหนึ่งของ workspace

ขอบสแกนจาก TXLSDepRange.LookupScan ขับเคลื่อนการ propagate dirty เข้า ScanPrecedents และ ScanDependents แต่ไม่เคยถูกนับเข้า in-degree หรือวงวน — การแก้ข้างใน B:B ยังติดเครื่องหมายสูตรให้ dirty ขอบสแกนจึงทำ deadlock รอบ topological หรือแต่งวงวนขึ้นมาไม่ได้

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // ช่วง lookup ครอบคอลัมน์ B และสูตรนี้อาศัยอยู่ข้างในมัน
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // ก่อน v2.361.98 สาขานี้เข้าไม่ได้สำหรับชีตนี้
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

สิ่งที่คุณยอมแพ้เมื่อตัดขอบสแกนออกจากการจัดลำดับ

มีเพียงหนึ่งอย่าง และสมควรพูดตรง ๆ มากกว่าซ่อนไว้ เพราะขอบสแกนไม่มีส่วนร่วมในลำดับ topological สูตร lookup อาจถูกประเมินในรอบเดียวกันก่อนเซลล์บางตัวในช่วง lookup ของมันถูกคำนวณใหม่ แล้วมันจะอ่านค่าเดิมของเซลล์เหล่านั้น ผลลัพธ์จะลู่เข้าค่าที่ถูกในการคำนวณใหม่ครั้งถัดไป

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

ความเสี่ยง regression ที่ซ่อนอยู่ข้างในทางแก้

การเพิ่ม LookupScan เข้า TXLSDepRange นำความเสี่ยงมาที่ไม่เกี่ยวกับ lookup เลย แต่เกี่ยวกับภาษา Pascal ล้วน ๆ TXLSDepRange เป็น record แบบไม่ถูกจัดการ ตัวแปร local ของชนิดนี้จึงไม่ถูกกำหนดค่าเริ่มต้นเป็นศูนย์ ทุกจุดใน codebase ที่สร้างมันด้วยมือ รวมถึงบล็อก dependency ของ data table และตัวช่วยทดสอบอีกหลายตัว ต้องถูกอัปเดตให้ตั้ง field ใหม่อย่างชัดเจน ข้ามจุดหนึ่งแล้วไบต์อะไรก็ตามที่บังเอิญนั่งอยู่บน stack จะตัดสินว่าการอ้างนั้นถูกปฏิบัติเป็นขอบสแกนหรือไม่ ซึ่งให้บั๊กคำนวณใหม่ที่โผล่และหายไปพร้อมการเปลี่ยนโค้ดที่ไม่เกี่ยวข้อง

// field Boolean ใหม่ใน record แบบไม่ถูกจัดการทำให้ทุกจุดสร้างด้วยมือ
// กลายเป็นบั๊กแฝง มีวิถีที่ปลอดภัยสองแบบ:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // เคลียร์เป็นศูนย์ทั้งหมด แล้วค่อยใส่ค่า
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // หรือตั้งทุก field รวมตัวใหม่ ที่ทุกจุด
  R.LookupScan := False;
end;

กฎทั่วไปที่เรื่องนี้สอน: การเพิ่ม field เข้า record ที่ถูกสร้างบน stack ในหลายจุดเกินสักหยิบมือเป็นการเปลี่ยนที่เสี่ยงสูงกว่าที่ดู และคอมไพเลอร์จะไม่ช่วยคุณหาจุดเหล่านั้น ถ้า record เข้าถึงได้จาก hot path ให้เลือก helper ที่กำหนดค่าเริ่มต้นให้ครบถ้วน มากกว่าเชื่อว่า call site ทุกจุดจะถูกอัปเดต

แยกวงวนจริงออกจากการทับกันเชิงสแกน

การเปลี่ยนแปลงนี้ไม่ได้ทำให้การตรวจวงวนอ่อนแอลง =B7+1 ใน B7 ยังเป็นวงวน สายสูตรสามสูตรที่ปิดกับตัวเองยังเป็นวงวน และทั้งคู่ยังถูกรายงานผ่านผลการคำนวณใหม่ โดยสมาชิกของวงวนคงค่า cache เดิมไว้ ขณะที่ทุกอย่างนอกวงวนยังทันสมัย สิ่งที่เปลี่ยนมีเพียงว่าอาร์กิวเมนต์ lookup-array ไม่แต่งวงวนที่ Excel ไม่เห็นขึ้นมาอีกต่อไป

ถ้าคุณกำลัง audit เวิร์กบุ๊กและอยากรู้ว่าการอ้างใดที่เอนจิน resolve จริงและในลำดับใด evaluation tracer คือเครื่องมือสำหรับเรื่องนั้น บทความ formula evaluation tracerอธิบายวิธีอ่านผลลัพธ์ของมัน HotXLS เป็น spreadsheet component แบบ native ของ Delphi และ C++Builder ที่อ่านและเขียน XLS, XLSX, ODS และ CSV โดยไม่ต้องติดตั้ง Excel และเอนจินคำนวณใหม่เป็นตัวเดียวกันบนทุกรูปแบบไฟล์ ความครอบคลุมฟังก์ชันและเอนจินปัจจุบันระบุไว้บนหน้าผลิตภัณฑ์HotXLS Delphi spreadsheet component