implicit intersection ของ defined name ใน HotXLS Delphi

defined name ที่อ้างถึงทั้งคอลัมน์จะถูก Excel อ่านเป็นเซลล์เดียวเมื่อมันปรากฏในตำแหน่ง scalar: =Vertical+1 ในแถว 7 หมายถึง "เซลล์แถว 7 ของ Vertical" ไม่ใช่ทั้งพื้นที่ HotXLS Delphi Component ใช้ implicit intersection นั้นใน v2.382.4 สองระดับ ทั้งตอนประเมินค่าและตอนดึง dependency เพราะเทมเพลตสินเชื่อที่มี 4805 สูตรแสดงให้เห็นว่าการได้ค่ามาถูกอย่างเดียวยังไม่พอ เมื่อ dependency walker ขยายชื่อออกเป็นพื้นที่เต็ม สูตรปลายทางที่ป้อนค่าให้เซลล์ใดก็ได้ในพื้นที่นั้นก็จะปิด cycle ที่ไม่มีอยู่จริง แล้ว TXLSXWorkbook.Recalculate ก็ปฏิเสธทั้งเวิร์กบุ๊ก

เทมเพลตที่ว่านี้คือเวิร์กบุ๊ก amortization สินเชื่อมาตรฐาน พอ poison ค่า cache ทุกตัวเป็น 777 แล้วรัน Recalculate เต็มรูปแบบ สถาปัตยกรรม engine ทั้งสองแบบก็คืนค่า 23 ซึ่งก็คือ lxErrorRef รหัสของ circular reference มี 3842 จาก 4805 สูตรที่ไม่ตรงกับค่าคาดหวังอิสระ B18 เป็น #VALUE! E18 ยังเป็น 777 และจำนวนงวดใน J7 ไปอ่านค่าตัวแทนที่อยู่ในคอลัมน์ยอดคงเหลือที่ยังคำนวณไม่เสร็จ bug สามตัวที่แยกกันซ่อนอยู่หลัง return code ตัวเดียว และบทความนี้จะไล่ทีละตัวพร้อมซอร์สที่แก้มัน

ทำไมการอ้างชื่อคอลัมน์แบบ scalar ถึงสร้าง cycle ปลอม

เพราะ dependency graph รู้จักแค่ edge และ edge หนึ่งเส้นจากสูตรไปยังพื้นที่ 480 แถวก็คือ 480 edge ซึ่งหนึ่งในนั้นชี้กลับผ่านเซลล์ที่ depend อยู่กับสูตรนั้น ลองนึกถึง =IF(TRUE,Vertical+1,0) ใน B1 โดยที่ Vertical นิยามเป็น Inputs!$A$1:$A$2 และ =B1+1 ใน A2 Excel ประเมิน B1 เป็น A1+1 และ A2 เป็น B1+1 ซึ่งเป็นห่วงโซ่ตรง ๆ walker ที่บันทึกว่า B1 depend อยู่กับ A1:A2 จะทำให้ A2 กลายเป็น precedent ของ B1 ขณะที่ A2 ก็ลิสต์ B1 เป็น precedent อยู่แล้ว และคิว Kahn ที่ขับการคำนวณใหม่แบบเพิ่มทีละส่วนใน HotXLS ก็ไม่เคยเห็น node ไหนมี in-degree เป็นศูนย์เลย นี่คือแพตเทิร์นที่เทมเพลตสินเชื่อสร้างขึ้นมา: ทุกแถวของงวดอ้างคอลัมน์ที่มีชื่อสำหรับยอดคงเหลือ, อัตราดอกเบี้ย และจำนวนงวด ชื่อแต่ละชื่อครอบทั้งตาราง และแต่ละแถวก็เขียนลงคอลัมน์เหล่านั้นด้วย พอขยายชื่อออก กราฟก็กลายเป็น strongly connected component ยักษ์ก้อนเดียว แต่ถ้าประเมินด้วย implicit intersection กราฟจะกลายเป็นห่วงโซ่สั้น ๆ หนึ่งเส้นต่อหนึ่งแถว ซึ่งก็คือสิ่งที่ ECMA-376 Part 1 §18.17.2 อธิบายไว้สำหรับ reference operand ที่ถูกใช้ในตำแหน่งที่ต้องการค่าเดียว

ทำไมชื่อคอลัมน์ถึงปิด cycle ปลอมใน HotXLS: เมื่อ Vertical นิยามเป็น Inputs!$A$1:$A$2 walker จะบันทึกว่า B1 depend อยู่กับ A1:A2 ขณะที่ A2 ลิสต์ B1 เป็น precedent อยู่แล้ว คิว Kahn จึงไม่เคยระบายหมด ขณะที่ intersection ย่อ B1 ลงเหลือเซลล์แถวนั้นคือ A1 และคงห่วงโซ่ต่อแถว A2, B1, A1 ที่ Recalculate จัดลำดับไว้ — การขยายชื่อทำให้กราฟกลายเป็น strongly connected component ยักษ์ก้อนเดียว และการประเมินสูตรเดิมด้วย implicit intersection ก็เปลี่ยนมันเป็นห่วงโซ่สั้น ๆ หนึ่งเส้นต่อหนึ่งแถวของตาราง

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Inputs');
    Book.DefinedNames.Add('Vertical', 'Inputs!$A$1:$A$2');
    Book.DefinedNames.Add('Alias', '=Vertical');
    Sheet.Cells[1, 1].Value := 1;
    // ตำแหน่ง scalar: Vertical ยุบเหลือ A1 เพราะสูตรอยู่ในแถว 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // ชื่อที่นิยามด้วยชื่ออื่นก็ยัง intersect ดังนั้นอันนี้คือ A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // อาร์กิวเมนต์คลาส reference: ทั้งพื้นที่ถูก sum ไม่มี intersection
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // แถว 6 อยู่นอก A1:A2 intersection ว่างเปล่าและ IFERROR จับไว้
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // ก่อน v2.382.4 branch นี้ไปไม่ถึง: B1 -> A2 -> B1 เป็น cycle
    end;
  finally
    Book.Free;
  end;
end;

HotXLS ตัดสินได้อย่างไรว่าอาร์กิวเมนต์ไหนเป็น scalar

HotXLS หาคำตอบจากตารางฟังก์ชัน ไม่ใช่จากรูปทรงของอาร์กิวเมนต์ ทุก entry ใน TXLSFormula.InitFuncHash ถูก register ผ่าน THashFunc.SetValue พร้อมสตริงคลาสต่ออาร์กิวเมนต์ที่เป็นออปชัน: 'IF' พก '100', 'SUMIF' พก '010', 'VLOOKUP' พก '1011' และ 'SUM' ไม่พกเลย อาร์กิวเมนต์ทั้งหมดของมันจึง fallback ไปที่คลาสระดับฟังก์ชันคือ 0 ตัวใหม่ TXLSFormula.FunctionArgumentClass(APtg, AArgument) เปิดเผย byte นั้นผ่าน THashFuncEntry.ArgClass และผลลัพธ์เป็น 1 หมายถึงคลาส value ทั้งสามคลาสนี้คือคลาสเดียวกับที่ [MS-XLS] §2.2.2 กำหนดให้ operand token และ encoder ก็ depend อยู่กับมันมาตลอด: เวลาที่มันเขียน reference มันคำนวณ ptg เป็น $24 + $20 * aClass ซึ่งให้ PtgRef สำหรับคลาส 0, PtgRefV สำหรับคลาส 1 และ PtgRefA สำหรับคลาส 2 ไฟล์ BIFF ที่ Excel เขียนเก็บคลาสนั้นไว้ในทุก reference token ดังนั้น engine ที่ตารางตรงกับสเปกจึงตอบได้ว่า "อาร์กิวเมนต์นี้เป็น scalar ไหม" โดยไม่ต้องดูข้อมูล อาร์กิวเมนต์ตัวกลางของ SUMIF คือ criterion ซึ่งเป็นค่า ส่วนตัวแรกกับตัวที่สามเป็นพื้นที่ เป็น reference SUMPRODUCT ถูก register ด้วยคลาสระดับฟังก์ชัน 2 คือ array นั่นคือเหตุผลที่ =SUMPRODUCT(Vertical,Vertical) ยังคูณทั้งพื้นที่

มีสามฟังก์ชันที่ไม่ปรึกษา entry ในตารางของตัวเองเลยสำหรับอาร์กิวเมนต์ที่เกินตัวแรกไป IF (ptg 1), CHOOSE (ptg 100) และ IFERROR (ptg 255) ส่งผ่านสิ่งที่มันเลือกออกไปตรง ๆ อาร์กิวเมนต์สาขาของพวกมันจึงสืบทอดคลาสของตำแหน่งที่ฟังก์ชันเองอยู่ กฎข้อเดียวนี้เองที่ทำให้ =CHOOSE(1,Vertical,0) ใน G2 resolve เป็น A2 ขณะที่ =SUMIF(Vertical,">0",Vertical) ข้าง ๆ กันยัง sum ทั้งสองแถว และมันคือกฎที่ตาราง amortization ใช้มากที่สุด เพราะเซลล์งวดของมันพึ่ง IF ในการทดสอบว่าสินเชื่อยังเปิดอยู่หรือไม่

ที่ที่ HotXLS อ่านคลาสอาร์กิวเมนต์สำหรับ implicit intersection: IF register 100, SUMIF 010, VLOOKUP 1011 และ SUM ไม่ register อะไรเลย อาร์กิวเมนต์ของมันจึง fallback ไปคลาส 0, encoder เขียน reference token เป็น ptg $24 บวก $20 คูณคลาส ได้เป็น PtgRef, PtgRefV และ PtgRefA และฟังก์ชันที่ส่งผ่านอย่าง IF, CHOOSE และ IFERROR จะสืบทอดคลาสของตำแหน่งที่ตัวเองอยู่ — เพราะตารางคลาสตรงกับสเปก engine จึงตอบได้ว่าอาร์กิวเมนต์ไหนเป็น scalar โดยไม่ต้องดูข้อมูล และการที่ CHOOSE resolve เป็น A2 ข้าง SUMIF ที่ sum ทั้งสองแถวก็มาจากกฎข้อเดียว

พาคลาสเดินผ่าน dependency walk

dependency extractor ใน lxCalc.pas เป็น Walk แบบเรียกซ้ำบน compiled syntax tree และมันมีอยู่สองที่ ตัวหนึ่งใน TXLSCalculator.ExtractDependencies สำหรับกราฟระดับเวิร์กบุ๊ก อีกตัวใน ExtractWorkspaceDependencies สำหรับกราฟข้ามเวิร์กบุ๊ก v2.382.4 ให้ walker ทั้งสองตัวพารามิเตอร์เพิ่มสองตัว AScalar เริ่มเป็น True ที่รากของสูตร ถูกคำนวณใหม่ให้ทุก child ที่เป็นฟังก์ชันจาก FunctionArgumentClass และถูกส่งผ่านไปโดยไม่เปลี่ยนสำหรับอาร์กิวเมนต์สาขาของ ptg 1, 100 และ 255 ANameRoot กลายเป็น True เฉพาะเมื่อ walker ลงไปในนิยามที่ compile แล้วของชื่อหนึ่ง และมันอยู่รอดผ่านเฉพาะ node SA_GROUP ซึ่งก็คือวงเล็บ ดังนั้นชื่อที่นิยามเป็น =A1:A2+1 จึงไม่ถูกเข้าใจผิดว่าเป็นพื้นที่ธรรมดา เมื่อ flag ทั้งสองเป็น True ที่ node SA_RANGE ตัว AddResolvedRange จะย่อพื้นที่ด้วย helper ตัวเดียวกับที่ evaluator ใช้ ก่อนที่มันจะบันทึก dependency helper นั้นสั้นพอจะยกมาทั้งตัว

การตัดสินใจใน IntersectNamedScalarRange ที่เฝ้า dependency ของชื่อใน HotXLS: ช่วงที่เป็นเซลล์เดียวอยู่แล้วผ่านไปได้, คอลัมน์เดียวจะย่อลงเหลือแถวของสูตรเมื่อ CurRow ตกอยู่ในนั้น, แถวเดียวจะย่อลงเหลือคอลัมน์ของสูตร และอย่างอื่น ไม่ว่าจะเป็นพื้นที่สองมิติหรือแถวที่อยู่นอกช่วง จะให้ #VALUE! ตอนประเมินค่าและไม่บันทึก dependency เลย — dependency walker ทั้งสองตัวกับ evaluator เรียก helper ตัวเดียวกัน ค่าที่สูตรอ่านได้กับ edge ที่กราฟบันทึกจึงไม่มีทางขัดกันในเรื่องชื่อที่ถูก intersect

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // เป็นเซลล์อยู่แล้ว
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // คอลัมน์เดียว: เอาแถวนี้
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // แถวเดียว: เอาคอลัมน์นี้
    Result := True;
  end;
end;

อะไรก็ตามที่ helper ปฏิเสธ ไม่ว่าจะเป็นพื้นที่สองมิติ, reference ที่คลุมหลายชีต หรือสูตรที่แถวของมันอยู่นอกคอลัมน์ที่มีชื่อ จะให้ #VALUE! ทางฝั่งการประเมินค่า และไม่ให้ dependency เลยทางฝั่งกราฟ ซึ่งก็คือสิ่งที่ Excel ทำเมื่อ intersection ว่างเปล่า ฝั่งการประเมินค่าอยู่ใน TXLSCalculator.GetValueItemName: มันถอด wrapper SA_GROUP ออกจากนิยามที่ compile แล้ว และถ้ารากเป็น SA_RANGE มันจะเรียก GetRangeInfo, ทำ intersection แล้วดึงเซลล์เดียวผ่าน FGetValue แทนที่จะประเมินทั้งนิยาม external reference ยังอยู่บนเส้นทางเดิม เพราะไม่มีแถวท้องถิ่นให้ intersect ด้วย ส่วนที่มาของ storage และ scope ของชื่ออธิบายไว้ในบทความเรื่อง defined name และสูตรข้ามชีต ประเด็นตรงนี้มีแค่ว่า engine ทำอะไรเมื่อชื่อ resolve ได้แล้ว

ทำไม MATCH บนคอลัมน์ที่คำนวณไปครึ่งเดียวถึงอ่านได้ 777

เพราะอาร์กิวเมนต์ lookup-array ของ MATCH เป็น scan reference และ scan reference ก็ถูกกันออกจากลำดับการประเมินโดยตั้งใจ บทความเรื่อง lookup scan แนะนำ TXLSDepRange.LookupScan และปิดท้ายด้วยหัวข้อที่ชื่อว่า "อะไรที่คุณต้องแลกไปเมื่อกัน scan edge ออกจากลำดับการประเมิน": สูตร lookup อาจรันก่อนที่ทุกเซลล์ในช่วงของมันจะถูกคำนวณใหม่ แล้วอ่านค่าที่ค้างเก่ามา ในเซสชันแบบอินเทอร์แอกทีฟมันลู่เข้าหาค่าที่ถูกในรอบถัดไป แต่ในการคำนวณใหม่แบบ batch บนเทมเพลตที่ถูก poison มันไม่เป็นเช่นนั้น และ PaymentCount ซึ่งนิยามเป็น =MATCH(0.01,Balances,-1)+1 ก็อ่านค่า 777 ที่ยังนั่งอยู่ในคอลัมน์ยอดคงเหลือ แล้วคืนจำนวนงวดที่เป็นไปไม่ได้ว่าจะถูก

ตอนนี้ TXLSDepGraph.TopoOrder มอง scan edge เป็น soft ordering edge ควบคู่กับ hard in-degree มันเก็บอาร์เรย์ ScanInDeg ที่นับ scan precedent ที่ยัง dirty ต่อ node และลดค่าลงเมื่อ precedent เหล่านั้นถูก emit โดยใช้ลิสต์ ScanPrecedents, ScanDependents และ ScanPrecedentCount ที่การเปลี่ยนแปลงรอบก่อนหน้าเก็บไว้อยู่แล้ว ในแต่ละรอบการวน คิว Kahn จะกวาด ready window ของมันหาตัวแรกที่มี ScanInDeg เป็นศูนย์ แล้วสลับมันขึ้นมาหัวคิว ถ้า ready node ทุกตัวยังรอ scan precedent อยู่ ก็จะ pop ตัวหัวตามลำดับเสถียรของมัน scan edge ไม่เคยเข้าไปอยู่ใน hard in-degree ดังนั้น VLOOKUP ที่อ้างคอลัมน์ของตัวเองก็ยังถูกกฎหมาย แต่ lookup ที่รอ precedent ที่จบได้ก็จะรอแล้ว regression ที่ตอกหมุดพฤติกรรมนี้ไว้คือ LookupScan_WaitsForDirtyFormulaValues ซึ่ง poison เซลล์ยอดคงเหลือสามเซลล์เป็น 777 แล้วคาดหวังว่า PaymentCount จะกลับมาเป็น 3 จากนั้นพลิกอินพุตเป็นศูนย์แล้วคาดหวังว่า =IFERROR(PaymentCount,99) จะเห็น #N/A และคืนค่า 99

การตัดทศนิยมเหลือสี่ตำแหน่งมาจากไหน

มาจากการคำนวณ Variant ของ Delphi และเฉพาะในตำแหน่งที่ซ้อนกัน ตัวดำเนินการไบนารีใน TXLSCalculator.GetValueItem คัดลอก + หรือ - ระดับบนสุดลงตัวแปร Double สองตัวอยู่แล้ว =B1-A1 จึงไม่มีปัญหา แต่ภายใน =IF(TRUE,B1-A1,0) การลบเดียวกันรันเป็น Value := Value - SubValue บน Variant สองตัว และเมื่อ operand ตัวหนึ่งเป็นค่าเซลล์ Int64 อีกตัวเป็น Double ผลลัพธ์ที่เราสังเกตได้ก็คือ Currency ซึ่งเป็นชนิด fixed-point ที่มีทศนิยมสี่ตำแหน่ง ดังนั้น 1066.1854641400994 ลบ 120 จึงกลับมาถูกตัดเหลือสี่ตำแหน่ง ในตารางที่ทุกงวดทบต้นจากแถวก่อนหน้า ความคลาดเคลื่อนนั้นเดินผ่านหลายร้อยงวดก่อนจะไปถึงยอดรวม

// TXLSCalculator.GetValueItem สาขาการคำนวณไบนารี (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// การคำนวณ Variant แบบ Int64/Double ปนกันอาจ promote เป็น Currency
// การคำนวณสเปรดชีตต้องคงความแม่นยำแบบ floating-point ไว้
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

guard นี้รันก่อน SA_ADD, SA_SUB, SA_MUL และ SA_DIV เหมือนกันหมด และ regression Arithmetic_MixedInt64AndDoubleKeepsPrecision เก็บ Int64(120) ไว้ใน A1 กับ 1066.1854641400994 ใน B1 จากนั้นตรวจผลต่างและผลบวกแบบซ้อนกันที่ 1E-10 และตรวจผลคูณกับผลหารที่ 1E-8 และ 1E-12 HotXLS ไม่ได้อ้างว่ารู้กฎการ promote ทุกข้อที่ RTL ใช้กับ Variant ชนิดผสมข้ามเวอร์ชันคอมไพเลอร์ มันอ้างแค่ว่าการคำนวณสเปรดชีตคือ IEEE double และตอนนี้มันทำให้ operand ทั้งสองเป็น double ก่อนที่ตัวดำเนินการจะเห็นพวกมัน ซึ่งตัดคำถามนั้นทิ้งไป

การแก้นี้รับประกันอะไร และไม่รับประกันอะไร

หลัง v2.382.4 สถาปัตยกรรม engine ทั้งสองแบบคืน lxOk ให้เทมเพลตที่ถูก poison ค่า cache ทั้ง 4805 ตัวตรงกับค่าคาดหวังทีละแถวแบบอิสระภายใน 1E-7 และ assertion ที่ว่า cache ถูก poison จริง, hash ของซอร์สไม่เปลี่ยน และทุกสูตรยังอยู่ครบ ก็เป็นจริงทั้งหมด ไม่มีการเปิด iteration และไม่มีการปิดรหัส error ใด ๆ เพื่อให้ไปถึงตรงนั้น cycle ที่แท้จริงผ่านชื่อ ไม่ว่าจะเป็น =B1 ใน A1 ขณะที่ B1 ยังอ่าน Vertical อยู่ ก็ยังคืน error และเทสต์ NamedScalarRanges_IntersectWithoutFalseCycles ก็ปิดท้ายด้วยการ assert เรื่องนี้เป๊ะ ๆ

ขอบเขตของมันคุ้มที่จะบอกให้ชัด implicit intersection ใช้เฉพาะกับชื่อที่นิยามซึ่ง compile แล้ว หลังถอดวงเล็บออก เป็นพื้นที่คอลัมน์เดียวหรือแถวเดียวบนชีตเดียว ชื่อแบบสองมิติในตำแหน่ง scalar จะได้ #VALUE! เหมือนใน Excel และฟังก์ชันที่ตารางไม่รู้จักจะได้คลาส 0 จาก FunctionArgumentClass อาร์กิวเมนต์ที่เป็นชื่อของมันจึงยังถูกขยายเต็ม การจัดลำดับแบบ soft เป็นความพยายามเลือก ไม่ใช่การรับประกัน: cycle ที่มีแต่ scan edge ก็ยังถูกประเมินตามลำดับเสถียรและอ่านค่าอะไรก็ตามที่ cache ไว้ ซึ่งเป็นพฤติกรรมที่บทความ lookup scan ยอมรับไว้โดยตั้งใจ และผลลัพธ์ระดับทั้งเทมเพลตถูกตรวจกับสคริปต์ค่าคาดหวังอิสระ ไม่ได้ตรวจกับ engine สเปรดชีตตัวอื่น เพราะ office suite ที่ใช้อ้างอิงคำนวณเทมเพลตต้นฉบับไม่ทันภายในงบ 60 วินาที HotXLS เป็นคอมโพเนนต์สเปรดชีตแบบเนทีฟสำหรับ Delphi และ C++Builder ที่อ่าน คำนวณใหม่ และเขียน XLS, XLSX, ODS และ CSV ได้โดยไม่ต้องติดตั้ง Excel การ intersect ชื่อ, ตารางคลาสอาร์กิวเมนต์ และการจัดลำดับ scan แบบ soft ใช้ได้กับทุกฟอร์แมตเพราะ calculation engine ใช้ร่วมกัน และความครอบคลุมฟังก์ชันปัจจุบันอยู่ในหน้าผลิตภัณฑ์ HotXLS Delphi spreadsheet component