Excel 365は=SUM(A1:B1*{10,100})のような数式に@を挿入し、ファイルが通常の数式として保存していると#VALUE!を表示します。Excelはその場合、演算子のオペランドすべてに旧来の暗黙的な交差を適用するからです。v2.384.68から、HotXLS Delphi Componentはこの種の配列演算子数式をExcel 365と同じ形で保存します。XLSXでは単一セルの動的配列数式、XLSでは1セル配列数式として
この症状はコードレビューをすり抜けます。Delphiのサービスがワークブックを書き出し、HotXLSは再計算して=SUM(A1:B1*{10,100})の結果として210をキャッシュします。ところが客がExcel 16で開くと、数式バーには=SUM(@A1:B1*@{10,100})、セルには#VALUE!が表示されています。ファイルのどこにも不正な箇所はありません。欠けているのは「この数式は動的配列のルールで書かれた」とExcelに伝えるメタデータで、それがないとExcelは動的配列以前の評価モデルへフォールバックします
HotXLSが正しく計算した数式に、なぜExcel 365は@を付けるのか
Excel 365が@を付けるのは、動的配列のマーキングがない数式は定義上レガシー数式であり、レガシー数式では演算子が単一の値を要求する箇所で、複数セルの範囲が1セルへ縮約されるからです。この縮約が暗黙的な交差です。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の結果 | 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) | 10 | 10 | 通常数式、変化なし |
最後の行は最初の4行と同じくらい重要です。SUM(A1:B2)は範囲を、参照を受け付ける関数パラメータへ直接渡します。演算子は複数セルの範囲を一度も見ず、交差も起きません。Excel 365自身がこの数式を通常数式として保存するので、HotXLSも同じことをします
HotXLSがXLSXとXLSに配列演算子数式を保存する方法
HotXLSはXLSXで配列演算子数式を単一セルの動的配列として書き込みます。<c>要素がcm="1"を持ち、数式は<f t="array" ref="E5">という形で、パッケージにはxl/metadata.xmlが加わります。そのメタデータ型はXLDAPRで、extensionにdynamicArrayProperties fDynamic="1"が入ります。cm属性はこのパーツのcellMetadataブロックへの1始まりのインデックスで、その先にあるXLDAPRレコードこそが、Excelに「これは動的配列のルールで評価せよ」と伝えるものです。同じ数式をExcel 16で入力して保存したときに書かれるのと同じ構造で、そもそも目標レイアウトはそこから確立されました
XLSにはメタデータパーツがありません。そこでHotXLSは、BIFF8が配列評価のために持つ唯一の構造、1セル配列数式を使います。セルにはFORMULAレコードが付き、そのトークンストリームは自分自身を指す単一のPtgExpで、続くARRAYレコード（$0221）が実際にパース済みの数式を1セル範囲に載せて運びます。Excel 365も同じやり方で動的配列数式をXLSに書き込むので、古いExcelで開くと古典的なCtrl+Shift+Enterの配列数式に見えます
新しい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を通じて同じルールに従います。数式を代入すると内部で1セル配列の経路へ回され、保存される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を使います。この記事の自動経路が扱うのは、1つのセルに入力した数式だけです
HotXLSが動的配列としてマークする数式はどれか
HotXLSが数式をマークするのは、演算子が配列を生むオペランド部分木を持つときだけです。チェックはコンパイル済みの構文木に対して走り、オペランドは複数セルの範囲、インライン配列定数、あるいは自らもその種のオペランドを持つ別の演算子式であれば、配列を生みます。括弧は透明です。対象になる演算子は算術演算子（+ - * / ^）、連結（&）、6つの比較演算子、単項プラスとマイナス、そしてパーセントです:
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はマークされません。単一セル参照と関数の結果は、このチェックにとってスカラーだからです
境界は3つ、いずれも意図的です。第1に、マーキングはAPI経由で数式を入力したときだけ起こります。XLSXエンジンではTXLSXCell.Formula、クラシックエンジンでは単一セルへのFormulaかValueの代入がそれに当たります。ファイルから読み込んだ数式は、見つかったとおりそのまま書き戻します。他のプロデューサーが作ったレガシー数式は、意図的に暗黙的な交差に依存しているかもしれないからです。第2に、:も{も含まないテキストは再コンパイルせずにスキップします。第3に、単体ではスピルする数式、たとえば=A1:B1*2は、置いた場所に固定された単一セルの動的配列としてマークされます。HotXLSはスピルさせず、次回の再計算でExcelが結果を隣接セルへ広げることになります
このオペランドルールは、HotXLSにおける定義名の暗黙的な交差で扱った引数クラスルールの兄弟です。あちらは値クラスとして宣言された関数パラメータの話、こちらは演算子の話で、レガシーモデルの演算子は常に値を要求します
結果を揃えるために計算エンジンで何を変えたか
v2.384.68の保存修正が成り立つのは、HotXLSの数式エンジンがすでにExcel 365と同じ値を返していたからで、そこには両エンジンでの何度かの先行修正がありました。いちばん目立っていたのはSUMPRODUCTです。v2.384.61までは引数として素の範囲を2つ以上しか受け付けず、SUMPRODUCT((B1:B2>0)*1)もSUMPRODUCT(--(B1:B2>0))も、引数1つのSUMPRODUCT(B1:B2)でさえ#N/Aを返していました。HotXLSは現在、式の引数をExcelのルールで要素ごとに評価します:
- すべての引数が厳密に同じ形を持つこと。スカラーは1 × 1と数えます。形が揃っていなければ結果は
#VALUE!です - どれかの引数にエラー値があれば、それが結果として返されます
- テキストと論理値の要素は0として数えます。TRUEを1に変えるには、今でも
(B1:B2>0)*1か--が必要です - 引数がすべて素の範囲なら元のストリーミングループを保ちます。大きな範囲が配列として実体化されることはありません
SUMファミリー（SUM、COUNT、AVERAGE、MIN、MAX、COUNTA）は、引数が範囲に演算子を適用した式のとき、同じ要素ごとの評価器を使います。=SUM((B1:B2>0)*1)が最初のセルだけを見るのではなく両方の行を数えるのはそのためです。v2.384.62ではスペースの交差演算子が2つの参照の共通矩形を返すようになり、重なりがなければ#NULL!です。=SUM(A1:B2 B1:B2)は2ではなく6になり、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、引数1つ
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、重なりは2回数える
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は歴史的に、2つの参照の間の@を二項の交差として受け付けてきました。今はその形式を本当の交差セマンティクスで評価します。一方Excel 365の@は、単項の暗黙的な交差プレフィックスです。数式テキストに@を書いてExcelの意味を期待してはいけません。交差にはスペースを使い、動的配列のセマンティクスは上述の保存ルールに任せてください
なぜExcelはファイルを開くのを拒んだり、間違った値を計算したりしたのか
Excelに動的配列マーキングを受け入れさせるには、自己往復テストでは捕まらない3つの修正が必要でした。HotXLSはどのケースでも自分の出力を正しく読み戻せていたからです。それぞれ、HotXLSの出力をExcel 16で開いて、変数を一つずつ替えていくうちに見つかっています:
- extensionのGUIDはすべて小文字であること。
xl/metadata.xmlのext uriは正確に{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}でなければなりません。古いHotXLSのテンプレートは大文字小文字混在で綴っており、Excel 16はセルだけではなくパッケージ全体を開くことを拒みました。v2.384.68以前にTXLSXRange.SetDynamicArrayFormulaで作ったワークブックにも同じ問題がありました - 配列ルートのテキストに先頭の
=は付かない。XLSXライターは配列ルートの保存テキストを<f>へそのまま出力します。変換後のセルが=を残していると、要素は<f t="array" ref="E5">=SUM(...)</f>となり、これもオープン時にExcelに拒否されます。HotXLSは変換の際にこれを剥ぐので、TXLSXCell.Formulaが読み戻すテキストには=がありません - 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では、すべてのオペランドトークンがトークンバイト自体にオペランドクラスを運んでおり、Excelは数式の構造よりもそのクラスを信頼します。[MS-XLS]はクラスを、トークンのビット5と6にある2ビットのPtgDataTypeフィールドと定義しています。1が参照、2が値、3が配列です。下位5ビットがトークンを識別するので、同じ領域参照に3つの綴りが存在します:
| トークン | 参照クラス | 値クラス | 配列クラス |
|---|---|---|---|
PtgRef | $24 | $44 | $64 |
PtgArea | $25 | $45 | $65 |
PtgArray | $20 | $40 | $60 |
HotXLSはこのうち3つを、それぞれ別の場所で間違えていました。どれもHotXLSでは正しく読み戻せるのに、Excelでは異なる症状が出るというものです:
- 参照クラスの配列定数。エンコーダーはクラスを文脈から選んでいました。SUMやROWSのパラメータは参照クラスなので、
=SUM({1,2})がPtgArrayとして$20で書かれていました。Excelは数式全体を=#N/Aと表示します。配列定数は決して参照になれないので、v2.384.63からHotXLSは、文脈が参照を要求する場所では配列クラスの$60を書きます PtgIsectとPtgUnionの値クラスオペランド。二項演算子は値クラスのオペランドを取っていました。*なら正しく、参照演算子としては誤りです。PtgIsect（$0F）の前に$45の領域があると、Excelは=SUM(A1:B2 B1:B2)を=SUM(@A1:B2 @B1:B2)として読み、#VALUE!を返しました。v2.384.62から、PtgIsectとPtgUnion（$10）のオペランドは参照クラスの$25で書かれます- ARRAYレコード内の値クラスオペランド。Excelはオペランドが値クラスだと、配列数式の中でも暗黙的な交差を適用します。HotXLSはそこに
$45を書いていたため、=SUM(A1:B1*{10,100})の1セル配列数式がExcelで10に評価されていました。v2.384.68から、ARRAYレコードのトークンストリームは、値クラスの参照と配列定数をすべて配列クラスの$65と$60へ昇格させます。Excelが書くのと同じです
クラスビットを無視するリーダーは、3つとも問題なく往復できます。だから自前のBIFF8ライターを保守しているなら、トークン番号だけでなく、すべてのオペランドトークンのクラスビットを、同じ数式をExcelで保存したファイルと比較してください
クイックリファレンス
- マークのない通常の数式で、演算子が複数セルの範囲かインライン配列を受け取ると、Excel 365は
@を表示します - HotXLS v2.384.68以降は、この種の数式をXLSXの単一セル動的配列（
cm="1"、t="array"、XLDAPRメタデータ）、およびXLSの1セル配列数式（PtgExp付きFORMULAと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のワークブックをネイティブに読み書き・計算し、配列演算子数式を、HotXLSが計算したのと同じ値でExcel 365が開ける形で保存します。エディション、ドキュメント、トライアルダウンロードは、HotXLS Delphi spreadsheet componentの製品ページをご覧ください