LlosLab 軟體開發部落格面向 PDF、Delphi 與文件軟體開發者的工程筆記

技術文章

HotXLS 陣列公式：Excel 為什麼插入 @ 與 #VALUE!

HotXLS Delphi Component

Excel 365 會在 =SUM(A1:B1*{10,100}) 這類公式裡插入 @，檔案若把公式存成一般公式，儲存格還會顯示 #VALUE!——因為這時 Excel 會對每個運算子的運算元套用舊式隱含交集。從 v2.384.68 起，HotXLS Delphi Component 改用 Excel 365 的方式存放這類陣列運算子公式：XLSX 存成單一儲存格的動態陣列公式，XLS 存成單格陣列公式

這個症狀連程式碼審查都攔不住。您的 Delphi 服務寫出活頁簿，HotXLS 重新計算後為 =SUM(A1:B1*{10,100}) 快取了 210，客戶用 Excel 16 一開啟，資料編輯列裡卻是 =SUM(@A1:B1*@{10,100})，儲存格裡是 #VALUE!。檔案本身沒有任何畸形之處，缺的是告訴 Excel「這條公式是按動態陣列規則寫的」那份中繼資料；少了它，Excel 就退回動態陣列問世前的求值模型

Excel 365 為什麼給 HotXLS 算對的公式加 @？

Excel 365 之所以加 @，是因為按定義，沒有動態陣列標記的公式就是舊式公式，而舊式公式只要遇到運算子要單一值的地方，就會把多儲存格範圍縮成一格。這個縮減動作就是隱含交集：Excel 取範圍裡與公式同列（直向範圍）或同欄（橫向範圍）的那個儲存格，找不到就用 #VALUE! 收場。Excel 365 對舊式公式保留這層語意，並顯示 @ 讓縮減看得見

把 =SUM(A1:B1*{10,100}) 放進 E5，舊式解讀立刻現形：A1:B1 是橫向範圍，公式卻坐在 E 欄，範圍裡沒有 E 欄的儲存格，於是 @A1:B1 得到 #VALUE!，整條 SUM 跟著中鏢。同一串字在動態陣列規則下會逐元素相乘，1 × 10 + 2 × 100，回傳 210。HotXLS 的公式引擎從 v2.384.61 與 v2.384.63 兩個版本起就是這麼求值的，只是檔案格式一直沒把這件事說出口。A1:B2 放 1、2、3、4 時，下面是探測公式與 Excel 16 實際顯示的結果：

HotXLS 示意圖，比較 SUM(A1:B1*{10,100}) 在儲存格 E5 的隱含交集與動態陣列兩種求值：舊式模型在 E 欄找不到橫向範圍 A1:B1 的儲存格而回傳 #VALUE!，動態陣列模型則把 1 乘 10、2 乘 100，回傳 210
Excel 給一般公式插入 @ 並顯示 #VALUE!，因為隱含交集在 E 欄一無所獲；加上 HotXLS 的動態陣列標記後，同一條公式逐元素相乘，落在 210
公式HotXLS 結果Excel 16，存成一般公式時自 v2.384.68 起的存法
=SUM(A1:B1*{10,100})210#VALUE!動態陣列，Excel 顯示 210
=SUM((A1:B2>2)*1)2隱含交集，結果錯誤或出錯動態陣列，Excel 顯示 2
=SUMPRODUCT((A1:B2>2)*1)2隱含交集，結果錯誤或出錯動態陣列，Excel 顯示 2
=MAX(A1:B2-1)3隱含交集，結果錯誤或出錯動態陣列，Excel 顯示 3
=SUM(A1:B2)1010一般公式，不變

最後一列跟前面四列一樣重要。SUM(A1:B2) 把範圍直接傳給接受參照的函數參數，運算子從頭到尾沒碰過多儲存格範圍，交集自然無從發生。Excel 365 自己也把這條公式存成一般公式，HotXLS 比照辦理

HotXLS 如何在 XLSX 與 XLS 裡存放陣列運算子公式

HotXLS 在 XLSX 裡把陣列運算子公式寫成單一儲存格的動態陣列：<c> 元素帶 cm="1"，公式本身是 <f t="array" ref="E5">，套件裡多出 xl/metadata.xml，其中註冊了 XLDAPR 中繼資料類型，其延伸內容放著 dynamicArrayProperties fDynamic="1"。cm 屬性是從 1 起算的索引，指向該 part 的 cellMetadata 區塊；背後那筆 XLDAPR 記錄，就是在告訴 Excel「這條公式按動態陣列規則求值」。Excel 16 自己輸入同樣公式再存檔，寫出來的就是這個結構——目標版面當初就是這樣定出來的

XLS 沒有中繼資料 part，HotXLS 便動用 BIFF8 對陣列求值唯一的建構：單格陣列公式。儲存格拿到一筆 FORMULA 記錄，token 序列只有一個指向自己的 PtgExp，後面跟著 ARRAY 記錄（$0221），把真正剖析後的公式套在單格範圍上。Excel 365 把動態陣列公式寫進 XLS 用的也是這一招，較舊的 Excel 讀到檔案，看到的就是經典的 Ctrl+Shift+Enter 陣列公式

HotXLS 對陣列運算子公式 SUM(A1:B1*{10,100}) 的存放示意圖：XLSX 引擎寫出 cm 等於 1 的單格動態陣列、型別為 array 的 f 元素，以及 xl/metadata.xml 裡 GUID 必須全小寫的 XLDAPR 記錄；XLS 引擎則寫出帶 PtgExp 的 FORMULA 記錄，加上一筆 ARRAY 記錄 0221
XLSX 引擎以 cm=1 加一筆 XLDAPR 中繼資料記錄標記儲存格，傳統引擎則把 PtgExp 的 FORMULA 與涵蓋單格的 ARRAY 記錄配成一對；Excel 365 把動態陣列存進 XLS 也是這麼做

整件事不涉及任何新 API。兩個引擎都是在您透過一般儲存格 API 指派公式時加上標記。XLSX 這邊就是 TXLSXCell.Formula：

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // 運算子作用在範圍或內嵌陣列上：存成動態陣列
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // 範圍直接傳給函數：維持一般 <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // 陣列根儲存格保留的文字不含前置的 '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 與 E6 會加上 cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

轉換之後，TXLSXCell.Formula 回傳的文字不含 =，與 TXLSXRange.SetDynamicArrayFormula 存放的形式相同，所以指派後要比對公式字串的程式碼，記得先把前置的 = 正規化

傳統引擎走同一套規則，入口是單一儲存格的 IXLSRange.Formula。指派公式時內部會改走單格陣列路徑，存出的 XLS 於是帶著 FORMULA 加 ARRAY 這一對：

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // ARRAY 記錄
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY 記錄
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // 一般 FORMULA

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

如果您要錨定的是多格結果而非純量彙總，明確的 API 仍是正確工具：預先指定大小的矩形用 SetArrayFormula，HotXLS 的動態陣列溢出公式一文有完整說明；想在自己指定大小的範圍上掛 XLSX 動態陣列標記，則用 TXLSXRange.SetDynamicArrayFormula。本文的自動路徑只涵蓋輸入到單一儲存格的公式

HotXLS 把哪些公式標記成動態陣列？

只有當某個運算子底下掛著會產生陣列的運算元子樹，HotXLS 才會標記公式。這個檢查跑在編譯後的語法樹上：運算元只要是多儲存格範圍、內嵌陣列常數，或本身帶著這類運算元的另一個運算子運算式，就算會產生陣列。括號是透明的。算數的運算子有算術類（+ - * / ^）、串接（&）、六種比較、一元加減號與百分比：

  • A1:B1*{10,100}、(A1:B2>2)*1、--(B1:B2>0) 與 A1:B2-1 都會被標記，無論出現在公式哪個位置，SUMPRODUCT 裡面也算
  • SUM(A1:B2) 與 SUMPRODUCT(A1:A2,{1;10}) 不會被標記，因為範圍與陣列直接進了函數引數，沒有任何運算子碰它們
  • A1*2 或 SUM(A1,B1)*2 不會被標記：對這個檢查來說，單格參照與函數結果都是純量

三條界線都是刻意畫的。第一，只有經 API 輸入的公式才會標記，具體指 XLSX 引擎的 TXLSXCell.Formula，以及傳統引擎對單一儲存格的 Formula 或 Value 指派。從檔案載入的公式原樣寫回，因為其他生產者寫出的舊式公式，可能刻意依賴隱含交集。第二，既無 : 也無 { 的文字直接跳過，不再多編譯一次。第三，會溢出的公式，例如單獨一條 =A1:B1*2，仍標記成錨定在您輸入位置的單格動態陣列；HotXLS 不替它溢出，下次 Excel 重新計算時，會自己把結果延伸到相鄰儲存格

這條運算元規則，與 HotXLS 已定名稱的隱含交集一文談的引數類別規則是姊妹篇：那篇講宣告成值類別的函數參數，這篇講運算子——在舊式模型裡，運算子永遠只收值

計算引擎改了什麼，才讓兩邊結果對上

v2.384.68 的存放修正，是站在 HotXLS 公式引擎早已回傳 Excel 365 數值的基礎上，而那靠的是兩個引擎先前的好幾輪修正。最顯眼的是 SUMPRODUCT：v2.384.61 之前它只收兩個以上的純範圍，SUMPRODUCT((B1:B2>0)*1)、SUMPRODUCT(--(B1:B2>0))，連單引數的 SUMPRODUCT(B1:B2) 都回傳 #N/A。如今 HotXLS 依照 Excel 的規則，對運算式引數逐元素求值：

  • 所有引數的形狀必須完全一致，純量按 1 × 1 計，否則結果是 #VALUE!
  • 任一引數裡的錯誤值會直接成為結果
  • 文字與邏輯元素都算 0，所以仍要 (B1:B2>0)*1 或 -- 才能把 TRUE 變成 1
  • 引數清一色是純範圍時仍走原本的串流迴圈，大範圍不會被具體化成陣列

SUM 家族（SUM、COUNT、AVERAGE、MIN、MAX、COUNTA）在引數是範圍上的運算子運算式時，用同一套逐元素求值器，所以 =SUM((B1:B2>0)*1) 會把兩列都算進去，而不是只盯著第一格。v2.384.62 讓空格交集運算子回傳兩個參照的共同矩形，不重疊時給 #NULL!，於是 =SUM(A1:B2 B1:B2) 是 6 而非 2，結果還能餵給 ROWS、INDEX 這類吃參照的參數。v2.384.63 為剖析器加上 {1,2;3,4} 這類內嵌陣列常數（逗號分欄、分號分列）與 (A1:B2,D4) 這類參照聯集。逐元素比較也讓空白元素沿用另一邊的型別、對邏輯值就給 FALSE，與 v2.384.53 的純量規則一致，詳見 HotXLS 的比較鏈與空白儲存格

var
  V: Variant;
begin
  // Book 是第一個範例裡的 TXLSXWorkbook；
  // 其使用中工作表放著 A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10，單一引數
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6，共同範圍 B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16，重疊部分算了兩次
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1，v2.384.61 之前是 -1
end;

TXLSXWorkbook.Calculate 對使用中工作表求一條公式字串的值、不落地存放，是驗證引擎行為的快捷方式。關於 @ 本身有個提醒：HotXLS 一直以來都接受兩個參照之間的 @ 當二元交集，如今也用真正的交集語意來求這種形式的值。但在 Excel 365 裡，@ 是一元的隱含交集前綴。別把 @ 寫進公式文字還期待它是 Excel 的意思；交集用空格表達，動態陣列語意交給上面的存放規則處理

Excel 為什麼拒開檔案，或算出錯的值？

要讓 Excel 收下動態陣列標記，總共補了三個修正，自家往返測試一個都抓不到——因為 HotXLS 讀自己的輸出，每一種情況都正確。三個都是拿 HotXLS 的輸出用 Excel 16 開啟、一次替換一個變數才挖出來的：

  1. 延伸的 GUID 必須全小寫。xl/metadata.xml 裡的 ext uri 必須一字不差是 {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}。較舊的 HotXLS 範本把它寫成大小寫混用，Excel 16 連整個套件都拒開，不只是那個儲存格。v2.384.68 之前用 TXLSXRange.SetDynamicArrayFormula 建立的活頁簿有同樣問題
  2. 陣列根儲存格的文字不帶前置 =。XLSX 寫入器把陣列根的存放文字原樣寫進 <f>。轉換過的儲存格若留著 =，元素就會變成 <f t="array" ref="E5">=SUM(...)</f>，Excel 開檔時一樣退件。HotXLS 在轉換時就把它剝掉，這也是 TXLSXCell.Formula 讀回來沒有它的原因
  3. Delphi 裡 Double(True) 是 -1。Variant 轉換遵循 COM 慣例，TRUE 是所有位元全設，VarIsNumeric(True) 也回傳 True。v2.384.61 之前，這讓 =TRUE*1 回傳 -1，邏輯陣列元素也被歸類成數字，(B1:B2>0)=TRUE 這種比較於是出錯。如今 HotXLS 在純量算術、陣列算術與陣列元素分類裡，把 Variant 當數字之前都先測 varBoolean，TRUE 算 1

BIFF8 運算元類別：寫給格式實作者的位元組級細節

在 BIFF8 裡，每個運算元 token 的類別就寫在 token 位元組本身，而 Excel 對這個類別的信任高於公式的結構。[MS-XLS] 把類別定義成 token 第 5、6 位元上的兩位元 PtgDataType 欄位：1 是 reference、2 是 value、3 是 array。低五位元決定 token 是哪一種，同一個區域參照於是有三種寫法：

TokenReference 類別Value 類別Array 類別
PtgRef$24$44$64
PtgArea$25$45$65
PtgArray$20$40$60

這當中 HotXLS 在不同地方寫錯了三處，每一處在 Excel 裡都各有症狀，HotXLS 自己讀回來卻一切正常：

  • 陣列常數被寫成 reference 類別。編碼器依上下文挑類別，而 SUM、ROWS 的參數是 reference 類別，=SUM({1,2}) 於是被寫成 reference 類別的 PtgArray，即 $20。Excel 把整條公式顯示成 =#N/A。陣列常數絕不可能是參照，所以從 v2.384.63 起，上下文要參照的地方，HotXLS 一律寫陣列類別 $60
  • PtgIsect 與 PtgUnion 的運算元被寫成 value 類別。二元運算子收 value 類別運算元，對 * 來說沒錯，對參照運算子卻是災難。帶 $45 的區域參照接在 PtgIsect（$0F）前面時，Excel 把 =SUM(A1:B2 B1:B2) 讀成 =SUM(@A1:B2 @B1:B2)，回傳 #VALUE!。從 v2.384.62 起，PtgIsect 與 PtgUnion（$10）的運算元一律寫成 reference 類別，即 $25
  • ARRAY 記錄裡的運算元被寫成 value 類別。就算在陣列公式裡，只要運算元是 value 類別，Excel 照樣套隱含交集。HotXLS 那裡寫的是 $45，於是 =SUM(A1:B1*{10,100}) 的單格陣列公式在 Excel 裡求值成 10。從 v2.384.68 起，ARRAY 記錄的 token 序列把每個 value 類別的參照與陣列常數都升級成陣列類別，$65 與 $60，這也正是 Excel 的寫法
HotXLS 的 BIFF8 示意圖：每個 token 位元組的第 5、6 位元決定 reference、value 或 array 類別，PtgArea 因此有 25、45、65 三種拼法；圖中標出三個已修正的缺陷——陣列常數寫成 20 時顯示 #N/A、PtgIsect 的運算元寫成 45 時回傳 #VALUE!、ARRAY 記錄裡的運算元寫成 45 時讓 SUM(A1:B1*{10,100}) 算出 10
每個 BIFF8 運算元 token 的類別都寫在第 5、6 位元，Excel 對這兩個位元的信任高於公式結構；HotXLS 把陣列常數寫成 60、PtgIsect 的運算元寫成 25，並把 ARRAY 記錄的 token 升級成陣列類別

不理會類別位元的讀取器，這三種錯誤全都能順利往返。若您自己維護 BIFF8 寫入器，請把每個運算元 token 的類別位元，拿去跟 Excel 存出的同一條公式比對，別只對 token 編號

速查

  • 一般未標記公式裡的運算子收到多儲存格範圍或內嵌陣列時，Excel 365 會顯示 @
  • HotXLS v2.384.68 起把這類公式存成 XLSX 單格動態陣列（cm="1"、t="array"、XLDAPR 中繼資料），以及 XLS 單格陣列公式（帶 PtgExp 的 FORMULA 加 ARRAY $0221）
  • 只有運算子的運算元才算數；直接傳進函數引數的範圍維持一般公式
  • 只有經 TXLSXCell.Formula 或傳統引擎單格 Formula / Value 輸入的公式會被標記；載入的公式原封不動
  • 轉換後的根儲存格讀回來不含前置 =
  • 動態陣列 ext uri 的 GUID 必須小寫，否則 Excel 退回整套件
  • Delphi 裡 Double(True) 是 -1；轉數字前先測 varBoolean
  • BIFF8：陣列常數絕不使用 reference 類別、PtgIsect / PtgUnion 的運算元用 reference 類別、ARRAY 記錄的運算元用陣列類別

HotXLS 讓 Delphi 與 C++Builder 原生讀寫、計算 XLS 與 XLSX 活頁簿，存放陣列運算子公式的方式，保證 Excel 365 開啟時看到的值與 HotXLS 算出的一致。各版本、文件與試用下載請見 HotXLS Delphi spreadsheet component 產品頁