=0<B2<100と書かれた範囲チェックは、ExcelではB2が50を保持している行を含むすべての行でFALSEを返します。Excelはまず0<B2を評価してTRUEを得て、それからTRUEが100より小さいかを問います。論理値はすべての数値より上位にランクされるため、B2が何を含んでいようと答えはFALSEです。空白セルとSUMIFも同種の驚きを持っています。空のセルは0と""とFALSEに同時に等しく、=SUMIF(A1:A10,">5",B1)はB列の10セルを足し上げます
そのようなワークブックをExcelの外で再計算する計算エンジンは、作者の意図から明らかに外れていても、それらの答えを再現しなければなりません。ファイルに保存されたキャッシュ結果は、誰かがF9を押す前に、ビューア、メールプレビュー、下流のインポートが見るものです。サーバーが範囲チェックにTRUEを書き込み、後からExcelがFALSEを見せたら、同じレポートは開いた場所に応じて2つの異なる数値を含み、どちらを信じるべきかは誰にもわかりません。作者の数式を直すのは作者の仕事です
Excelで=1<2<3がFALSEを返す理由
Excelが=1<2<3にFALSEを返すのは、6つの比較演算子がすべて1つの優先順位を共有し、左から右へ結合するためです。数式は(1<2)<3を意味し、内側のTRUEはその後、数値3との型ランクの勝負に負けます。HotXLS Delphi Componentは、2つのワークブックエンジン、クラシックなTXLSWorkbookとXLSXネイティブのTXLSXWorkbookの両方で、比較チェーンを同じようにパースします。パーサーは1つのオペランドを読み、次のトークンが=、<>、<、>、<=、>=の間は、これまでにパースしたすべてを左の子、次のオペランドを右の子とする比較ノードを組み立て、そのノードを次の回へ持ち込みます。これは+、-、&で使っているのと同じループの形です
罠は教科書的な文法に座っています。expr = operand op exprと書くと、再帰下降パーサーは右辺のために自分自身へ再帰し、演算子を静かに右結合にします。その読みでは=1<2<3は1<(2<3)、つまり1<TRUE、つまりTRUEになります。誤りは両方向へ走ります。=3>2>1はExcelではTRUEで、右結合の下ではFALSEです。=1=1=TRUEも同じようにひっくり返ります。括弧付きの形はどちらの読みでも正しく出るため、(a<b)<cだけを検査するテストは、この間違いをすり抜けます
Calculateメソッドは、数式文字列をセルに入れずに評価します。両エンジンに対してチェーンのリスト全体を検査する手軽な方法です。このメソッドはHotXLS数式エンジンの概説でより深く扱っています
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk は lxCalc で宣言されています
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の答え:FALSE、TRUE、FALSE、TRUE、TRUE、TRUE、TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create; // インターフェース経由で参照カウントされる
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculateはアクティブなシートに対して評価し、
// ワークブックにまだシートがないときは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;
数値、テキスト、真偽値:型をまたぐランク
比較の2つの辺の型が違うとき、Excelは片方をもう片方へ変換しません。代わりに型をランク付けします。すべての数値はすべてのテキスト値より小さく、すべてのテキスト値はすべての真偽値より小さい。HotXLSは両オペランドを数値、テキスト、真偽値に分類し、クラスが違うときはクラス同士だけを比較します。だから=TRUE>99999はTRUE、="1">2はTRUE、=FALSE>"zzz"もTRUEです。連鎖した比較が恣意的に感じられる原因はこの部分です。TRUE<3は、1に型強制されたTRUEが3と比較されるのではありません。真偽値と数値の比較であり、真偽値が勝ちます
同じクラスの中での比較は自然なものです。テキストにはひねりがあって、文字列比較は大文字小文字を無視するため、="abc"="ABC"はExcelでもHotXLSでもTRUEを返します。日付はエンジンにとってシリアル番号なので、日付は常にどんなテキストよりも下にランクされます。日付に見えるだけのテキストセルも含まれます。半分のセルが文字列として届いたインポート済み日付の列は、2つの別の帯でソートされ、比較され、=A2>DATE(2026,1,1)は文字列のセルすべてに対してTRUEを返します
比較において空白セルは何と等しいのか
比較のオペランドとして使われた空白セルは、相手の型を取ります。数値に対しては0、テキストに対しては""、論理値に対してはFALSEと等しくなります。A1が空なら、=A1=0、=A1=""、=A1=FALSEはExcelではすべてTRUEです。HotXLSは、6つの比較演算子すべてについて、両エンジンで、2つのオペランドをランク付けする前に同じ置換を行います。両オペランドが空白のときは置換は起こらず、等しいと比較されます
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=FALSE'))); // True
Writeln(VarToStr(Wb.Calculate('=A1<B1'))); // True:空白は0として比較される
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False:0は0より下ではない
end;
実務で刺さるのは最後の行です。算術はすでに空白を0として扱うため、=A1+1はどのエンジンでも1を返します。近道が間違うのは比較です。「空白」をすべての数値より下の独自の最下位ランクとして保持するエンジンは、=A1<0にTRUEと答え、その後=IF(A1<0,"overdrawn","ok")が、空の残高セルすべてを当座貸し越えと判定します。0か空文字列だけを置換するのでも不十分です。論理値に対しては空白が0になり、0はTRUEとFALSEのどちらよりも下にランクされるため、空のセルで=A1=FALSEはFALSEになってしまいます
計画に入れておく価値のある帰結があります。空白は論理値に対してFALSEとして比較されるため、=A1=FALSEは、ExcelでもHotXLSでも、空のセルとFALSEを保持するセルを区別できず、=A1=0は空とゼロを区別できません。誰かが実際に値を入力したかを知る必要のあるテンプレートは、ISBLANK(A1)で検査すべきです。=A1=""の形はより弱く、空文字列を返す数式に対してもTRUEになるからです
1セルの合計範囲を持つSUMIFがなぜ列全体をカバーするのか
Excelは条件範囲の形を保ち、合計範囲はその左上のセルにしか使いません。つまり=SUMIF(A1:A10,">5",B1)はB1:B10を意味します。手作りのテンプレートの多くは、作者が知らないうちにこの省略形に頼っています。素朴な実装、つまり歩きを2つの範囲の小さい方へクランプするやり方は、例をA1対B1の単一のテストへ縮め、0を返します。HotXLSは条件範囲を歩き、各値を合計範囲の左上隅から同じ行と列のオフセットで読みます。AVERAGEIFも同じ歩きを通るため同じリサイズを受け、条件範囲より大きい合計範囲は条件の形へ切り詰められます
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)'; // 明示的な合計範囲
Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)'; // 同じリサイズ
if Book.Recalculate = lxOk then
// D1とD2:4000(600+700+800+900+1000)。D3:800
Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ',
VarToStr(Sheet.Cells[2, 4].Value), ' ', VarToStr(Sheet.Cells[3, 4].Value));
finally
Book.Free;
end;
end;
自分で書くテンプレートには、合計範囲を完全に書き出してください。エンジンが省略形を尊重するのは既存のファイルが頼っているからですが、数式の中のB1は、次にシートを保守する人には単一のセルに読め、その人は追加される金額が1つだけだと妥当に思い込みます。中央の条件は単一の値として評価され、外の2つの引数は参照です。この引数クラスの間の分裂は、暗黙の交差と引数クラスの記事で説明しています。フィルタされた行や非表示の行もスキップすべき条件付き合計は、また別の規則に従い、SUBTOTALとAGGREGATEの非表示行の記事で扱っています
INDIRECTの参照スタイルとYEARFRACのbasis 0
より小さい2つの関数が同じ種類の罠を運んでいます。INDIRECTはオプションの第2引数a1を取り、それがFALSEのときテキストはR1C1参照です。=INDIRECT("R2C3",FALSE)はC2を読みます。フラグを無視するパーサーは"R2"を列R、行2と読みます。それは実在するセルなので、数式は間違った場所からもっともらしい値を返します。HotXLSはフラグを真偽値、数値(ゼロはFALSE)、テキストの"FALSE"として読み、解決の前にR1C1テキストを変換します。R[1]C[1]のような括弧付きオフセットは、数式を保持するセルから測られます。有効な参照の後に何かが残るテキスト、たとえば"B2 junk"は、黙ってB2を使う代わりに#REF!を返します
basis 0のYEARFRACは、US(NASD)の30/360日数を使います。DAYS360が適用する2月最終日の規則を含みます。両方の日付が2月の最終日であるとき、終了日は30になり、それから2月の最終日である開始日も30になります。2024-02-29から2025-02-28まででは、360日、正確に1の分数になります。これらの規則を飛ばす素の30/360のカウントは359日と0.99722を与え、その差は2月の終わりにまたがる利息計上に現れます
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['C2', 'C2'].Value := 'row 2, column 3';
Writeln(VarToStr(Wb.Calculate('=INDIRECT("R2C3",FALSE)'))); // 行2、列3
Writeln(VarToStr(Wb.Calculate('=ISERROR(INDIRECT("B2 junk"))'))); // True:#REF!
Writeln(VarToStr(Wb.Calculate(
'=YEARFRAC(DATE(2024,2,29),DATE(2025,2,28),0)'))); // 1
end;
この種の挙動を文書化したりテストしたりするときは、期待値を、数式が言っていそうなことからではなく、Excel自身から取ってください。「1は2より小さく2は3より小さい」は、=1<2<3がTRUEであることを示唆しますが、それはまさに右結合のパーサーが与える答えであり、Excelが返すものの正反対です。Excelが示す値を隣に置いた短い数式の表は、この記事の4つのケースすべて、チェーン、空白、SUMIFのリサイズ、2月の日数を捕まえます
HotXLSは、マシンにExcelなしで、DelphiとC++BuilderからXLS、XLSX、ODS、CSVを読み、再計算し、書き込みます。上の比較、空白セル、条件付き集計の規則は、両ワークブッククラスが共有する計算エンジンの一部です。対応関数リスト、エディション、ライセンスは、HotXLS Delphiスプレッドシートコンポーネントページに載っています