HotXLSの比較チェーン、空白セル、SUMIFの挙動（Delphi）

HotXLS Delphi Componentは=1<2<3をFALSEと評価します。Excel 16と同じ答えです。v2.384.3以降、数式パーサーが比較演算子を左から右へ折りたたむためです。1<2はTRUEになり、TRUE<3はFALSEになります。真偽値はすべての数値より上位にランクされるからです。同じリリースで、空白オペランドが0と""のどちらとも等しくなるようになり、SUMIFが1セルの合計範囲を条件範囲の形へ引き伸ばせるようになりました。どれも細部に見えますが、Delphiで計算したワークブックと、同じワークブックをExcelで開いた結果が食い違うと話は変わります

食い違いはたいてい、直感で書かれた数式から始まります。誰かが数量が範囲内かの確認に=0<B2<100と入力すると、Excelは全行に黙ってFALSEと答え、そのバグを焼き付けたままシートは出荷されます。計算エンジンがユーザーの意図を直す権限はありません。仕事はExcelが返すはずの値を返すことです。HotXLSがファイルへ書き込むキャッシュ結果が、再計算後にExcelが表示するものと一致するためです。v2.384.3より前のHotXLSはその範囲チェックに全行TRUEと答えていました。逆方向の間違いです。サーバーで生成したレポートと、同じレポートをデスクトップで開いたものが矛盾します

Excelで=1<2<3がFALSEを返す理由

ExcelがFALSEを返すのは、比較の連鎖を(1<2)<3として読み、内側のTRUEが数値3との型ランク勝負に負けるからです。旧HotXLSパーサーは同じテキストを1<(2<3)として読んでいました。lxFormula.pasのTXLSSyntax.Parse_exprはオペランドを1つパースし、比較トークンを見ると右辺のためにParse_exprへ再帰していました。これで演算子は右結合になります。結果は1<TRUEで、数値は真偽値より下位なので、答えはTRUEでした。誤りは対称です。=3>2>1はExcelではTRUE、HotXLSではFALSE、=1=1=TRUEもExcelではTRUE、修正前はFALSEでした。リグレッションCalculateFormula_ComparisonChainsFoldLeftToRightはその手の数式7つをExcel 16の返す値に対して固定し、どれも2つのエンジンアーキテクチャ、クラシックのTXLSWorkbookとXLSXネイティブのTXLSXWorkbookを通して実行します。HotXLS数式エンジンの概説で述べたCalculateメソッドを使ってです

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // Excel 16の返す値：FALSE、TRUE、FALSE、TRUE、TRUE、TRUE、TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculateはアクティブシートを対象に評価し、
    // ワークブックにシートが1つもないときはNullを返します
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

修正はParse_exprを、+、-、&ですでにParse_expr1が使っているのと同じ形のループへ変えます。最初のオペランドをParse_expr1でパースし、次のトークンが=、<>、<、>、<=、>=のいずれかである間、比較ノードを作り、累積した左結果を最初の子として付け、次のオペランドをParse_exprではなくParse_expr1でパースして、新しいノードを次の周回の左結果にします。再帰を反復へ変えるときに間違えやすい詳細が2つあり、どちらもメンテナーのノートにあります。累積ノードはlChild := Item; Item := nilの順で引き渡すこと。そしてエラーパスは、半完成のノードを解放した後にExitすることです。ループから落ちて宙ぶらりんの木を返すよりは、そちらが正解です

HotXLSは比較で数値、テキスト、真偽値をどうランク付けするか

HotXLSは混合型をExcelと同じ方法でランク付けします。すべての数値はすべてのテキスト値より小さく、すべてのテキスト値はすべての真偽値より小さい。lxCalc.pasのTXLSCalculator.CompareVariantsは両オペランドをGetRetValueTypeで列挙型TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue)へ分類し、2つのクラスが違うときは順序値を単純に比較します。つまりこの列挙型の宣言順が型横断の規則です。同じクラス内の比較は自然なものですが、テキストにはExcel特有のひねりが1つあります。両文字列はまずlxUpperCaseを通るので、="abc"="ABC"はTRUEです。このランクがあるからこそ、チェーンの結果はそれなしでは推理できません。TRUE<3はTRUEの1への型変換ではなく、真偽値と数値の比較であり、真偽値が勝ちます。日付はエンジンにとってシリアル値です（varDateはxlNumberValueに分類される）ので、日付はどんなテキストよりも下位です。日付に見えるテキストであっても

比較において空白セルは何と等しいのか

比較オペランドとして使われた空白セルは、相手が数値なら0と、相手がテキストなら""と、v2.384.53以降は相手が論理値ならFALSEと等しくなります。A1が空なら=A1=0、=A1=""、=A1=FALSEはすべてTRUEです。6つの比較演算子すべてに仕えるTXLSCalculator.CompareVarValuesは、CompareVariantsを呼ぶ前に空白を置換します。片方だけがNullのとき、相手が文字列ならWideString('')に、相手が真偽値ならFalseに、それ以外なら0になります。両方が空白のときは置換なしで互いに等しいままです。算術の経路は昔から空白を0に変えていたので=A1+1が1になったのですが、CompareVariantsはNullを独自の最下位ランク、すべての数値より下に保持しており、比較演算子はそのランクを直接使っていました

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1は意図的に空のまま

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True：空白は0として比較される
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False。v2.384.3より前はTrue
end;

実務で刺さったのは最後の行です。旧ランクでは空白はすべての数値より小さく、負の数も含まれるため、=IF(A1<0,"overdrawn","ok")は空の残高セルをすべて「当座貸し越え」と判定し、誰が見てもゼロと言うセルで=A1=0はFALSEでした。v2.384.3の後も1つ境界が残っていました。置換は0と空文字列のどちらかしか選ばず、真偽値と比較された空白は0になり、これはTRUEとFALSEのどちらよりも下位です。空のA1に対する=A1=FALSEはFALSEと評価されていました。HotXLS 2.384.53以降、論理値と比較された空白はXLSエンジンでもXLSXエンジンでもFALSEとして扱われます。Excelと同じです。A1が空なら=A1=FALSEと=A1<TRUEはTRUEを返し、=A1=TRUEはFALSEを返します。これはまた、比較では空白とFALSEを区別できないことも意味します。ExcelでもHotXLSでもです。シートにその区別が必要なら、ISBLANKか=A1=""でテストしてください

1セルの合計範囲を渡したSUMIFが0を返した理由

SUMIFが0を返したのは、HotXLSが反復を2つの範囲の小さい方へクランプしていたのに対し、Excelは条件範囲の形を保ち、合計範囲は左上のセルにしか用がないからです。つまりExcelでは=SUMIF(A1:A10,">5",B1)はB1:B10を意味し、手作りテンプレートの多くが頼っている便利仕様です。共有ワーカーのTXLSCalculator.GetValueItemRange2は、行数と列数を値範囲のそれへ縮めていたため、例はA1対B1の1回のテストに縮んでいました。v2.384.3はクランプを取り除きます。ループはいま条件範囲を歩き、合計範囲の左上隅から同じオフセットで各値を読みます。CalcSumIFとCalcAverageIFがどちらもこのワーカーを呼ぶため、AVERAGEIFも同じリサイズを得られ、条件範囲より大きい合計範囲は同じ理由で条件の形へ切り詰められます。中央の条件引数は値クラスの引数で、外側の2つは参照クラスです。この区別は暗黙の交差と引数クラスの記事で扱っています

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // 条件列：1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // 金額：100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // 1セルの合計範囲
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // 明示的な合計範囲
    if Book.Recalculate = lxOk then
      // D1もD2も4000（600+700+800+900+1000）。v2.384.3より前はD1が0だった
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECTとYEARFRAC：静かな修正2つ

INDIRECTは第2引数を尊重するようになり、正しい参照の後ろにテキストが続くと無視ではなくエラーになります。a1がFALSEならテキストは絶対R1C1としてパースされ、=INDIRECT("R2C3",FALSE)はC2を読みます。旧コードはフラグを無視し、"R2"をR列2行目として読み、黙って間違ったセルを返していました。フラグはvariant型（真偽値、数値、テキスト）で振り分けます。文字列variantを直接Doubleへ変換すると例外が出るからです。R[1]C[1]のような相対R1C1テキストは#REF!を返します。INDIRECTには解決の基準となる数式セルの原点がないからです。末尾に文字の付いたA1テキスト、"B2 junk"も同じく#REF!を返します。basis 0のYEARFRACはいま、DAYS360がすでに実装していたNASDの2月末規則を適用します。両日付が2月の最終日なら終了日は30になり、次に開始日が2月の最終日なら30になります。2024-02-29から2025-02-28までは、旧Days360USの359に対し、いまは360日、分数はちょうど1です

これらの修正は何を保証し、教訓は何だったのか

比較チェーンの挙動を保証するのは、両エンジンをExcel 16で実測した値と比較するテストです。このテストが存在するのは、修正の最初の説明が間違っていたからです。v2.384.3のリリースノートは当初、左から右への折りたたみが=1<2<3をTRUEにすると書いていました。旧い右結合パーサーが出す値そのままで、Excelと新コードの返す値の正反対です。誰も例を評価していませんでした。「1は2より小さく、2は3より小さい」という直感で書かれたのです。ノートは修正され、7数式のテストは後続のコミットで追加されました。そこから出た規則は、表計算のセマンティクスを文書化する人すべてに当てはまります。期待値を書き下す前に、Excelで例を実行すること。空白オペランドの置換とSUMIFのリサイズは同じExcelの挙動に従い、v2.384.53以降の空白対真偽値のケースも含まれます。フィルタ行や非表示行もスキップしなければならない条件付き集計は、SUBTOTALとAGGREGATEの非表示行の記事にある別の規則に従います

HotXLSは、ExcelをインストールせずにXLS、XLSX、ODS、CSVを読み、再計算し、書き込むネイティブのDelphi / C++Builderスプレッドシートコンポーネントです。ここで述べた比較、空白、SUMIFの規則は、両ワークブックアーキテクチャが共有する計算エンジンの中に住んでいます。機能一覧とライセンスはHotXLS Delphiスプレッドシートコンポーネントの製品ページをご覧ください