技術文章

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

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

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

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

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 的公式引擎就是按動態陣列方式求值的,檔案格式也必須把這件事說出口。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,存成一般公式時HotXLS 的存法
=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:

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk 宣告於 lxCalc

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 已定名稱的隱含交集一文談的引數類別規則是姊妹篇:那篇講宣告成值類別的函數參數,這篇講運算子——在舊式模型裡,運算子永遠只收值

計算引擎怎麼對上 Excel 365 的值

儲存層的標記要有價值,前提是公式引擎一開始就回傳 Excel 365 的數值,而且兩個引擎都是。SUMPRODUCT 是最顯眼的案例:只收兩個以上純範圍的實作,對 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) 數的是兩列,而不是只看第一格。空格交集運算子回傳兩個參照的共同矩形,不重疊時給 #NULL!,所以 =SUM(A1:B2 B1:B2) 是 6 而不是 2,結果還能餵給 ROWS、INDEX 這類參照參數。剖析器接受 {1,2;3,4} 這類內嵌陣列常數(逗號分欄、分號分列)與 (A1:B2,D4) 這類參照聯集。逐元素比較也讓空白元素取另一側的型別——對邏輯值取 FALSE——與HotXLS 的比較鏈與空白儲存格描述的純量規則一致

var
  V: Variant;
begin
  // Book is the TXLSXWorkbook from the first example;
  // its active sheet holds A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, single argument
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, common range B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, overlap counted twice
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1,TRUE 算 1
end;

TXLSXWorkbook.Calculate 對活動工作表求值公式字串而不儲存,是快速檢查引擎行為的方法。關於 @ 本身有個提醒:HotXLS 接受 @ 作為兩個參照之間的二元交集,並以真正的交集語意求值這種形式。在 Excel 365 裡,@ 是一元隱含交集前綴。不要把 @ 寫進公式文字還期待 Excel 的意思;交集用空格,動態陣列語意交給上面的儲存規則

Excel 開啟動態陣列輸出時檢查的三個細節

要讓 Excel 接受動態陣列標記,落到三個自我往返測試抓不到的細節上——函式庫自己讀自己的輸出,怎麼都讀得對。每一個都只在把輸出放進 Excel 16、一次換一個變數時才現形:

  1. 延伸 GUID 必須全小寫。xl/metadata.xml 裡的 ext uri 必須剛好是 {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}。大小寫混著拼,Excel 16 拒開整個封裝,不只那一格
  2. 陣列根文字不帶前導 =。XLSX 寫入器把陣列根的儲存文字原樣寫進 <f>。轉換後的儲存格若留著 =,元素會變成 <f t="array" ref="E5">=SUM(...)</f>,Excel 開檔時同樣拒收。HotXLS 在轉換時剝掉它,這也是 TXLSXCell.Formula 讀回來沒有它的原因
  3. Delphi 裡 Double(True) 是 -1。Variant 轉換遵循 COM 慣例——TRUE 是全位元為 1——而 VarIsNumeric(True) 也回傳 True。天真的轉換於是讓 =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

BIFF8 寫入器有三個容易做錯的地方,每一個都在 Excel 裡產生明顯症狀,而在寫入器自己的讀取器裡讀回來一切正常:

  • 參考類的陣列常數。按上下文挑類別的編碼器看到 SUM 或 ROWS 參數是參考類,就把 =SUM({1,2}) 的 PtgArray 寫成 $20。Excel 把整條公式顯示成 =#N/A。陣列常數永遠不可能是參照,所以上下文要求參考類時,HotXLS 一律寫陣列類 $60
  • PtgIsect 與 PtgUnion 的值類運算元。給二元運算子值類運算元,對 * 是對的,對參照運算子是錯的。PtgIsect($0F)前面放 $45 區域,Excel 把 =SUM(A1:B2 B1:B2) 讀成 =SUM(@A1:B2 @B1:B2)、回傳 #VALUE!。HotXLS 把 PtgIsect 與 PtgUnion($10)的運算元寫成參考類 $25
  • ARRAY 記錄裡的值類運算元。運算元是值類時,Excel 連陣列公式裡都套隱含交集。那裡放了 $45,=SUM(A1:B1*{10,100}) 的單格陣列公式在 Excel 算出 10。HotXLS 因此在 ARRAY 記錄的 token 流裡,把每個值類參照與陣列常數提升為陣列類 $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 把這類公式存成 XLSX 單格動態陣列(cm="1"、t="array"、XLDAPR 中繼資料)與 XLS 單格陣列公式(FORMULA 帶 PtgExp 加 ARRAY $0221)
  • 只有運算子的運算元才算數;範圍直接傳給函數引數時,仍是普通公式
  • 只有經 TXLSXCell.Formula 或傳統引擎單格 Formula / Value 輸入的公式會被標記;載入的公式原樣不動
  • 轉換後的根儲存格讀回來沒有前導 =
  • 動態陣列 ext uri 的 GUID 必須小寫,否則 Excel 拒收封裝
  • Delphi 裡 Double(True) 是 -1;數值轉換前先測 varBoolean
  • BIFF8:陣列常數絕不用參考類,PtgIsect / PtgUnion 的運算元用參考類,ARRAY 記錄的運算元用陣列類

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