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 結果 | 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) | 10 | 10 | 一般公式,不變 |
最後一列跟前面四列一樣重要。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 陣列公式
整件事不涉及任何新 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、一次換一個變數時才現形:
- 延伸 GUID 必須全小寫。
xl/metadata.xml裡的ext uri必須剛好是{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}。大小寫混著拼,Excel 16 拒開整個封裝,不只那一格 - 陣列根文字不帶前導
=。XLSX 寫入器把陣列根的儲存文字原樣寫進<f>。轉換後的儲存格若留著=,元素會變成<f t="array" ref="E5">=SUM(...)</f>,Excel 開檔時同樣拒收。HotXLS 在轉換時剝掉它,這也是TXLSXCell.Formula讀回來沒有它的原因 - 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 是哪一種,同一個區域參照於是有三種寫法:
| Token | Reference 類別 | 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 自己的寫法
不理會類別位元的讀取器,這三種錯誤全都能順利往返。若您自己維護 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 產品頁