在 Delphi 中使用 HotXLS 建立動態陣列溢位公式

HotXLS 在 Delphi 和 C++Builder 中原生評估 Excel 365 動態陣列溢位公式：您將其提供一個儲存格中包含 XLOOKUP 或 FILTER 的活頁簿，其公式引擎就會計算結果集，並將其溢位至定位的輸出矩形中，產生與 Excel 相同的數值。HotXLS Delphi Excel 元件將溢位公式模型化為一個擁有計算權限的根儲存格，外加一個從中讀取資料的相依儲存格區塊，這正是您在撰寫任何程式碼前需要瞭解的完整概念

該模型非常重要，編譯為日常工作並非抽象的。客戶傳送了一個在 Excel 365 中建立的 .xlsx 檔案，其工作表中填滿了 =FILTER(...) 和 =XLOOKUP(...)，而您的服務必須在未安裝 Excel 的機器上以無介面模式重現完全相同的數值，然後讀取溢位的值或自行寫入一個新的溢位區域。動態陣列將計算約定從「一個公式對應一個儲存格」變更為「一個公式對應一個儲存格矩形」，且 HotXLS 引擎遵循該約定，而不是使用預先展開的網格來模擬它

如何在 Delphi 中評估 XLOOKUP 和 FILTER？

HotXLS 透過 lxCalc.pas 中的單一評估器進入點 CalcDynArrayFunc 分派每個動態陣列函式，因此整個系列共用一個溢位程式碼路徑。支援的集合包括 XLOOKUP 和 FILTER（最初的兩個），並加入了 XMATCH、SORT、UNIQUE 和 SEQUENCE。每個函式都傳回一個二維 Variant 陣列，而非純量：SEQUENCE(3;2;1;1) 產生一個 3x2 的網格，SORT 依據任何鍵值欄位以遞增或遞減重新排序行，UNIQUE 摺疊重複的行並保留首次出現的行，而 XMATCH 在與 XLOOKUP 相同的精確、萬用字元、下一個較小值和下一個較大值比對模式下，回報以 1 為起始的數值位置。與每次呼叫傳回一個數字的純量統計分配函式不同，這些函式會傳回一個完整的陣列形狀

要計算已經包含這些公式的活頁簿，請開啟外，呼叫一次 Recalculate，然後讀取溢位落入的儲存格。TXLSXWorkbook.Recalculate 走訪相依性圖表，並以拓撲順序評估每個已變更的公式，因此溢位根儲存格僅計算一次，且其元素會直接寫入成員儲存格中

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // 評估每個公式，包括溢位
    Sheet := Book.Sheets[1];          // Sheets 是以 1 開始的
    // 讀回 XLOOKUP / FILTER 溢位落入的區塊，例如 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;

如果活頁簿需要引擎未附帶的函式，同一個評估器可讓您透過自訂工作表函式掛鉤插入自己的邏輯，且自訂函式可以自由傳回 Variant 陣列，使其溢位效果與內建函式完全相同

使用 SetArrayFormula 定位溢位範圍

HotXLS 從不猜測溢位應該有多大：您指定輸出矩形，然後 TXLSRange.SetArrayFormula 將公式定位於其上。該方法編譯公式一次，並將語法樹與定位的範圍一起儲存在左上角的根儲存格中，同時給予矩形中的每個其他儲存格一個弱引用根儲存格的輕量級公式。在重新計算時，根儲存格會評估一次，且其矩陣元素會直接廣播至每個成員儲存格，這也是溢位根儲存格在增量重新計算相依性圖表中顯示為單一節點的管道。當工作是將溢位區域寫回檔案中而非讀取出來時，應使用此路徑

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // 將矩形大小調整為結果：3 行 2 列的網格。
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // 以行向下在 A1:B3 中填入 1..6
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

明確定位的後果是值得清楚說明的邊界限制：HotXLS 不會像互動式 Excel 那樣自動調整溢位的大小。Excel 會隨著輸入變更而增大或縮減溢位區域，並在目標儲格被佔用時引發 #SPILL!。在 HotXLS 中，您傳遞給 SetArrayFormula 的矩形就是您獲得的矩形，因此您需要根據預期的結果調整其大小，如果一個函式產生的列數比您定位的更多，多餘的部分將無處落腳

跨運算子的元素級陣列廣播

當運算的任一側是陣列時，HotXLS 會逐個元素地套用算術運算子。協助工具 ApplyArrayBinaryOp 涵蓋了一維和二維 Variant 陣列上的 +、-、*、/ 和 ^：純量運算元會被廣播至每個元素，兩個陣列的形狀必須相符否則運算會被拒絕，且除以零會以錯誤形式傳播而不會導致崩潰。純量算術路徑保持不變，因此這僅在至少一個運算元確實是陣列時才會啟用，例如溢位範圍或矩陣函式結果。這意味著像定位於一欄上的公式 =D2:D13*1.1 會依次乘以每個元素，而 =D2:D13*E2:E13 則會逐個位置乘以兩個形狀相同的欄

// 純量廣播：定位點的每個儲存格獲得 D(n) * 1.1。
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// 兩個相同形狀的陣列逐個元素相乘。
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

@ 運算子與舊版 CSE 陣列的作用有何區別？

HotXLS 將明確的 @ 解讀為參照交集運算子，而非即時 Excel 中用於挑選列的隱式交集。在引擎中，A1:A3 @ B1:B3 傳回兩個範圍相交的單一儲存格，且該運算子的結合度比 ^ 更緊密，比 % 更鬆散。需要坦白說明的一點是：隱式的空格分隔交集形式尚未進行 Token 解析（tokenised），因為字彙分析器（lexer）仍會跳過空格，因此您只能透過明確的 @ 符號獲得交集

更深層的區別在於傳統的 CSE 陣列公式與現代動態陣列，而在 HotXLS 中，兩者都流經相同的定位機制。傳統的陣列公式是在預先調整大小的選取範圍上透過 Ctrl+Shift+Enter 輸入的 {=...} 區塊，每個儲存格共用一個編譯後的公式。動態陣列則是單一公式，其結果決定了其自身的形狀。HotXLS 將它們統一在 SetArrayFormula 下：不論公式是舊式的矩陣運算式還是新的 SORT，您都需要指定矩形、由根儲存格擁有編譯樹，並由成員儲存格對其進行參照。您絕對無法獲得 Excel 那種區域會默默自動重長的效果，牢記這一點可以防止這兩種思維模型發生混淆

動態陣列支援的限制

瞭解限制可以節省一下午的除錯時間。SORT 遵循每個鍵值的排序方向，預設為遞增，當排序引數為負值時為遞減；比較方向是一個值得針對 Excel 測試您自己鍵值欄位的地方，因為反向比較只會默默傳回相反順序的行，而不會失敗。UNIQUE 保留每個重複行中首次出現的行，引擎透過掃描先前的行來確認，而非依賴原始重複計數。TABLE 函式（模擬分析資料表）可被識別，因此它可以在檔案中雙向往返，但它會評估為預留位置，因為實際的替代結果是 Excel 已經儲存的快取值，而 HotXLS 不會重新執行模擬分析網格

LET 是最需要明確指出的限制。HotXLS 註冊了 LET 並為其提供了評估路徑，但剖析器會拒絕非已定義名稱的單純名稱，因此 =LET(x;10;x) 在剖析時就會失敗，甚至還沒到達評估器。在剖析器獲得其綁定名稱的範圍感知之前，請將 LET 視為不支援。另一個較小的移植性注意事項：這些範例中的公式字串使用的是引擎的清單分隔符號（分號），因此在組合公式文字時，請符合您的建置版本所期望的任何分隔符號。此處介紹的動態陣列函式（從 XLOOKUP 和 FILTER 到 SORT、UNIQUE、SEQUENCE 和 XMATCH）均隨附於 HotXLS Delphi Excel 元件中，其公式參考手冊中列出了完整的函式清單以及每個函式支援的引數模式