สูตรคำนวณอาร์เรย์แบบไดนามิกแบบ Spill ใน Delphi ด้วย HotXLS

HotXLS ประมวลผลสูตรอาร์เรย์แบบไดนามิกแบบ Spill ของ Excel 365 ใน Delphi และ C++Builder ได้โดยตรง: คุณเพียงแค่ส่งเวิร์กบุ๊กที่มีฟังก์ชัน XLOOKUP หรือ FILTER ในเซลล์ และเอนจินสูตรจะคำนวณชุดผลลัพธ์และทำการ Spill (กระจายค่า) ทั่วทั้งรูปสี่เหลี่ยมผืนผ้าเอาต์พุตที่ยึดตำแหน่งไว้ ซึ่งเป็นค่าเดียวกับที่ Excel จะสร้างขึ้น ส่วนประกอบ Excel สำหรับ Delphi ของ HotXLS นี้จำลองสูตร Spill เป็นเซลล์ราก (root cell) หนึ่งเซลล์ที่เป็นเจ้าของการคำนวณ ร่วมกับกลุ่มเซลล์อ้างอิง (dependent cells) ที่อ่านค่ากลับจากเซลล์รากนั้น ซึ่งเป็นภาพรวมทั้งหมดที่คุณต้องรู้ก่อนเริ่มเขียนโค้ด

โมเดลดังกล่าวมีความสำคัญเนื่องจากงานในชีวิตประจำวันไม่ใช่เรื่องสมมติ เมื่อลูกค้าส่งไฟล์ .xlsx ที่สร้างจาก Excel 365 ซึ่งเต็มไปด้วยสูตร =FILTER(...) และ =XLOOKUP(...) บริการของคุณจะต้องสร้างตัวเลขแบบเดียวกันขึ้นมาใหม่โดยไม่มีส่วนติดต่อผู้ใช้ (headless) บนเครื่องที่ไม่ได้ติดตั้ง Excel จากนั้นอ่านค่าที่ Spill ออกมา หรือเขียนขอบเขตการ Spill ใหม่ของคุณเอง อาร์เรย์แบบไดนามิกได้เปลี่ยนข้อตกลงในการคำนวณจาก "หนึ่งสูตรต่อหนึ่งเซลล์" เป็น "หนึ่งสูตรต่อสี่เหลี่ยมผืนผ้าของเซลล์" และเอนจินของ HotXLS ก็ปฏิบัติตามข้อตกลงนั้นแทนที่จะทำเลียนแบบด้วยตารางข้อมูลที่ขยายพื้นที่ไว้ล่วงหน้า

จะประมวลผล XLOOKUP และ FILTER ใน Delphi ได้อย่างไร?

HotXLS ส่งผ่านฟังก์ชันอาร์เรย์แบบไดนามิกทุกฟังก์ชันผ่านจุดเข้าประมวลผลเดียวคือ CalcDynArrayFunc ใน lxCalc.pas ทำให้ฟังก์ชันในตระกูลทั้งหมดใช้พาธโค้ดสำหรับการ Spill ร่วมกัน ชุดฟังก์ชันที่รองรับประกอบด้วย XLOOKUP และ FILTER (สองฟังก์ชันดั้งเดิม) ร่วมกับ XMATCH, SORT, UNIQUE และ SEQUENCE แต่ละฟังก์ชันจะส่งคืนอาร์เรย์ Variant แบบ 2 มิติแทนที่จะเป็นค่าสเกลาร์ (scalar) ตัวอย่างเช่น SEQUENCE(3;2;1;1) จะได้ตารางขนาด 3x2, SORT จะจัดเรียงแถวใหม่ตามคอลัมน์คีย์ที่ระบุไม่ว่าจะเรียงจากน้อยไปมากหรือมากไปน้อย, UNIQUE จะยุบแถวที่ซ้ำกันโดยเก็บแถวแรกที่พบไว้ และ XMATCH จะรายงานตำแหน่งแบบอิงตาม 1 (1-based) ของค่าภายใต้โหมดการจับคู่แบบตรงกันทุกประการ (exact), ไวด์การ์ด (wildcard), ค่าที่น้อยกว่าถัดไป และค่าที่มากกว่าถัดไปเช่นเดียวกับ XLOOKUP ซึ่งแตกต่างจาก statistical distribution functions ที่เป็นค่าสเกลาร์ซึ่งจะส่งคืนตัวเลขหนึ่งตัวต่อการเรียกหนึ่งครั้ง เนื่องจากฟังก์ชันเหล่านี้จะส่งคืนพื้นที่ข้อมูลทั้งหมดกลับมา

ในการคำนวณเวิร์กบุ๊กที่มีสูตรเหล่านี้อยู่แล้ว ให้เปิดไฟล์ เรียกใช้ Recalculate หนึ่งครั้ง และอ่านค่าในเซลล์ที่การ Spill ลงไปประมวลผล TXLSXWorkbook.Recalculate จะไล่ตามกราฟความขึ้นต่อกัน (dependency graph) และประมวลผลแต่ละสูตรที่ยังไม่ได้คำนวณตามลำดับโทโพโลยี (topological order) ทำให้เซลล์รากของ Spill ได้รับการคำนวณเพียงครั้งเดียว และเขียนสมาชิกแต่ละตัวลงในเซลล์ย่อยโดยตรง

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // evaluate every formula, spills included
    Sheet := Book.Sheets[1];          // Sheets is 1-based
    // Read back the block the XLOOKUP / FILTER spilled into, say D2:F9.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

หากเวิร์กบุ๊กต้องการฟังก์ชันที่ตัวเอนจินไม่ได้จัดส่งมาให้ ตัวประมวลผลเดียวกันนี้ก็เปิดโอกาสให้คุณเชื่อมต่อนรรกะของคุณเองผ่านฮุก custom worksheet functions และฟังก์ชันที่กำหนดเองนั้นสามารถส่งคืนอาร์เรย์ Variant เพื่อให้มีการ Spill ได้เช่นเดียวกับฟังก์ชันสำเร็จรูปในระบบ

การยึดตำแหน่งช่วง Spill ด้วย SetArrayFormula

HotXLS จะไม่มีการคาดเดาขนาดการ Spill: คุณจะต้องระบุรูปสี่เหลี่ยมผืนผ้าเอาต์พุต และ TXLSRange.SetArrayFormula จะทำหน้าที่ยึดตำแหน่งสูตรกับพื้นที่นั้น เมธอดจะคอมไพล์สูตรหนึ่งครั้ง จัดเก็บโครงสร้างต้นไม้ไวยากรณ์ (syntax tree) ไว้ในเซลล์รากมุมบนซ้ายร่วมกับช่วงที่ยึดตำแหน่งไว้ และกำหนดสูตรขนาดเล็กในเซลล์อื่นภายในสี่เหลี่ยมผืนผ้าเพื่ออ้างอิงแบบอ่อน (weak-reference) ไปยังเซลล์ราก ในขั้นตอนการคำนวณใหม่ เซลล์รากจะประมวลผลหนึ่งครั้งและส่งข้อมูลสมาชิกเมทริกซ์ไปยังเซลล์ย่อยแต่ละเซลล์โดยตรง ซึ่งทำให้เซลล์รากของ Spill ปรากฏเป็นโหนดหนึ่งใน incremental recalculation dependency graph ด้วย นี่คือแนวทางที่ต้องใช้เมื่อต้องการเขียนขอบเขต Spill กลับลงในไฟล์แทนที่จะเป็นการอ่านข้อมูลออกมา

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

ผลลัพธ์ของการยึดตำแหน่งอย่างชัดเจนทำให้เกิดข้อจำกัดที่ควรชี้แจงตรงๆ คือ: HotXLS จะไม่ปรับขนาด Spill อัตโนมัติในลักษณะเดียวกับที่ตัวโปรแกรม Excel ทำงานจริง โปรแกรม Excel จะขยายและหดขอบเขต Spill ตามอินพุตที่เปลี่ยนไป และแจ้งข้อผิดพลาด #SPILL! เมื่อเซลล์เป้าหมายมีข้อมูลอื่นอยู่แล้ว ทว่าใน HotXLS รูปสี่เหลี่ยมผืนผ้าที่คุณส่งไปยัง SetArrayFormula คือรูปสี่เหลี่ยมผืนผ้าที่คุณจะได้รับ ดังนั้นคุณจึงต้องกำหนดขนาดให้สอดคล้องกับผลลัพธ์ที่คาดหวัง และหากฟังก์ชันสร้างแถวข้อมูลมากกว่าจำนวนแถวที่คุณยึดตำแหน่งไว้ ข้อมูลส่วนเกินก็จะไม่แสดงผลเนื่องจากไม่มีเซลล์รองรับ

การแพร่กระจายอาร์เรย์ทีละส่วน (Element-wise) ข้ามตัวดำเนินการ

HotXLS ใช้ตัวดำเนินการทางคณิตศาสตร์แบบทีละองค์ประกอบเมื่อฝั่งใดฝั่งหนึ่งของการดำเนินการเป็นอาร์เรย์ ตัวช่วย ApplyArrayBinaryOp จะครอบคลุมการทำงานของ +, -, *, / และ ^ บนอาร์เรย์ Variant แบบ 1 มิติและ 2 มิติ: ตัวถูกดำเนินการสเกลาร์จะถูกส่งต่อไปยังทุกองค์ประกอบ อาร์เรย์ทั้งสองตัวต้องมีมิติเท่ากันมิฉะนั้นการดำเนินการจะถูกปฏิเสธ และการหารด้วยศูนย์จะส่งต่อข้อมูลในลักษณะของข้อผิดพลาดแทนที่จะทำให้โปรแกรมแครช ส่วนพาธคณิตศาสตร์สเกลาร์ปกติจะไม่ได้รับการเปลี่ยนแปลงใดๆ ดังนั้นการทำงานนี้จะเปิดใช้เฉพาะเมื่อมีตัวถูกดำเนินการอย่างน้อยหนึ่งตัวเป็นอาร์เรย์จริงเท่านั้น เช่น ช่วงที่ Spill หรือผลลัพธ์ของฟังก์ชันเมทริกซ์ นั่นหมายความว่าสูตรอย่าง =D2:D13*1.1 ที่ยึดตำแหน่งเหนือคอลัมน์จะคูณแต่ละองค์ประกอบตามลำดับ และ =D2:D13*E2:E13 จะคูณสองคอลัมน์ที่มีมิติเท่ากันในแต่ละตำแหน่งคู่กัน

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

ตัวดำเนินการ @ ทำงานอย่างไรเมื่อเทียบกับอาร์เรย์ CSE แบบดั้งเดิม?

HotXLS ตีความเครื่องหมาย @ ที่ระบุอย่างชัดเจนเป็นตัวดำเนินการแยกส่วนอ้างอิงตัดกัน (reference-intersection operator) ไม่ใช่การแยกส่วนตัดกันโดยปริยายของการเลือกแถวในโปรแกรม Excel จริง ในเอนจินนี้คำสั่ง A1:A3 @ B1:B3 จะส่งคืนค่าเซลล์เดียวที่ช่วงข้อมูลทั้งสองมาตัดกัน และตัวดำเนินการจะประมวลผลก่อน ^ และหลัง % ข้อควรระวังประการหนึ่งคือ: รูปแบบการเขียนการตัดกันแบบเว้นวรรคโดยปริยายยังไม่ได้รับการแปลงเป็นโทเค็นเนื่องจากตัวแยกคำ (lexer) ยังคงข้ามช่องว่างสีขาว ดังนั้นคุณจะทำงานตัดกันผ่านสัญลักษณ์ @ ที่ระบุไว้ชัดเจนเท่านั้น

ความแตกต่างที่ลึกซึ้งยิ่งขึ้นคือระหว่างสูตรอาร์เรย์ CSE แบบเก่าและอาร์เรย์แบบไดนามิกสมัยใหม่ ทว่าใน HotXLS ทั้งสองรูปแบบต่างทำงานผ่านกลไกการยึดตำแหน่งเดียวกัน สูตรอาร์เรย์แบบคลาสสิกคือบล็อก {=...} ที่ป้อนด้วย Ctrl+Shift+Enter เหนือพื้นที่ที่กำหนดขนาดไว้ล่วงหน้า โดยทุกเซลล์จะใช้สูตรที่คอมไพล์แล้วร่วมกัน ส่วนอาร์เรย์แบบไดนามิกคือสูตรเดียวที่ผลลัพธ์จะเป็นตัวกำหนดรูปร่างของมันเอง HotXLS นำรูปแบบทั้งสองมารวมกันภายใต้ SetArrayFormula: โดยคุณระบุสี่เหลี่ยมผืนผ้าเสมอ เซลล์รากเป็นผู้ครอบครองโครงสร้างต้นไม้ที่คอมไพล์แล้ว และเซลล์สมาชิกจะอ้างอิงไปยังเซลล์ราก ไม่ว่าสูตรจะเป็นการแสดงออกทางเมทริกซ์แบบเก่าหรือฟังก์ชัน SORT ใหม่ สิ่งที่คุณจะไม่มีวันได้รับในระบบนี้คือการปรับขนาดอาร์เรย์โดยอัตโนมัติของ Excel ที่ขอบเขตจะขยายพื้นที่ออกไปเงียบๆ และการคำนึงถึงขีดจำกัดนี้จะช่วยป้องกันไม่ให้โมเดลการทำงานทั้งสองเกิดความสับสนปนเปกัน

ข้อจำกัดของการรองรับอาร์เรย์แบบไดนามิก

การรู้ถึงข้อจำกัดจะช่วยลดเวลาดีบั๊กได้มาก ฟังก์ชัน SORT จะทำงานตามแนวการเรียงลำดับของคีย์ โดยค่าเริ่มต้นจะเป็นการเรียงจากน้อยไปมาก และเรียงจากมากไปน้อยเมื่ออาร์กิวเมนต์ลำดับมีค่าติดลบ ทิศทางการเปรียบเทียบนี้เป็นจุดที่คุ้มค่าแก่การทดสอบคอลัมน์คีย์ของคุณเทียบกับผลลัพธ์ใน Excel เนื่องจากทิศทางการเปรียบเทียบที่สลับกันจะส่งคืนแถวข้อมูลในลำดับตรงกันข้ามเงียบๆ แทนที่จะล้มเหลว ฟังก์ชัน UNIQUE จะคงรายการแรกที่พบของแต่ละแถวที่แตกต่างกัน ซึ่งเอนจินจะยืนยันโดยการสแกนแถวก่อนหน้าแทนที่จะพึ่งพาการนับแถวซ้ำดิบ ฟังก์ชัน TABLE (ตารางข้อมูลการวิเคราะห์แบบ What-If) ได้รับการจดจำเพื่อให้ส่งผ่านข้อมูลในไฟล์ได้โดยไม่เสียหาย แต่มันจะถูกประมวลผลเป็นตัวสำรอง (placeholder) เนื่องจากผลลัพธ์ทดแทนที่แท้จริงคือค่าที่แคชไว้ซึ่ง Excel บันทึกไว้แล้ว และ HotXLS จะไม่มีการรันตารางข้อมูล What-If ใหม่

ฟังก์ชัน LET คือข้อจำกัดที่ชัดเจนที่สุดที่ต้องแจ้งให้ทราบ HotXLS ลงทะเบียนฟังก์ชัน LET และมีพาธประมวลผลรองรับ แต่วิววิเคราะห์คำ (parser) จะปฏิเสธชื่อตัวแปรลอยๆ ที่ไม่ได้กำหนดชื่อไว้ล่วงหน้า ดังนั้นสูตร =LET(x;10;x) จะล้มเหลวตั้งแต่ขั้นตอนการวิเคราะห์ไวยากรณ์ก่อนจะส่งต่อไปถึงขั้นตอนประมวลผล ขอแนะนำให้ถือว่าฟังก์ชัน LET ยังไม่ได้รับการรองรับจนกว่าตัววิเคราะห์ไวยากรณ์จะสามารถจดจำขอบเขต (scope) ของตัวแปรที่ผูกไว้ได้ ข้อสังเกตเล็กน้อยเรื่องความเข้ากันได้: สตริงสูตรในตัวอย่างเหล่านี้จะใช้ตัวคั่นรายการของเอนจินคือ อัฒภาค (semicolon) ดังนั้นโปรดปรับให้ตรงกับตัวคั่นที่โครงสร้างระบบของคุณคาดหวมเมื่อประกอบข้อความสูตร ฟังก์ชันอาร์เรย์แบบไดนามิกที่กล่าวถึงในที่นี้ ตั้งแต่ XLOOKUP และ FILTER ไปจนถึง SORT, UNIQUE, SEQUENCE และ XMATCH จัดส่งมาพร้อมกับ HotXLS Delphi Excel Component ซึ่งในเอกสารอ้างอิงสูตรจะมีรายการฟังก์ชันและโหมดอาร์กิวเมนต์ที่รองรับของแต่ละฟังก์ชันอย่างครบถ้วน