Excelのprecision as displayed（表示桁数での丸め）は、保存された各数値を、その数値書式が見せる桁数へ丸めます。値の符号に合う書式セクションを選び、%1個につき小数を2桁足し、千単位スケーリングのカンマ1個につき3桁引き、ゼロから遠い方向へ半分丸める。HotXLSはTXLSXWorkbook.FullPrecisionかTXLSWorkbook.UseFullPrecisionがFalseのとき、両方のDelphiエンジンで同じルールを適用します。一言で済みそうな話ですが、客から「出力した請求書の合計がExcelと1セント違う」「[ss].00の所要時間の列がゼロに潰れた」と報告されると話は変わります。どちらも実際に起きました。どちらも、このルールのどれかを1つ間違えたことにさかのぼります。v2.384.57から、2つのエンジンは単一の実装を共有し、その期待値はWorkbook.PrecisionAsDisplayedをオンにしたExcel 16で実測されました
precision as displayedはワークブックの中で実際に何を変えるのか
precision as displayedは、計算エンジンに数値を計算された通りではなく見た目の通り保存させる、単一のワークブックレベルフラグです。ExcelのUIでは、ファイル、オプション、詳細設定、「このブックの計算設定」の下に「表示桁数で計算精度を設定する」としてあります。ディスク上では1ビットです。BIFF8ファイルはCalcPrecisionレコード（$000E、[MS-XLS] §2.4.35）で運び、そのfFullPrecフィールドは通常の全精度なら1、オプションがオンなら0です。XLSXパッケージは、ECMA-376 Part 1で定義されたworkbook.xmlのcalcPr要素のfullPrecision属性として運びます。デフォルトはtrueで、fullPrecision="0"が丸めをオンにします
このフラグは表示の好みではありません。チェックを入れると、Excelはデータが恒久的に精度を失うと警告します。そして本気です。値は表示精度へ書き直され、切り捨てられた桁は消えます。後でチェックを外しても、古い桁は戻りません。12.3%として表示される0.1234は、恒久的に0.123になります
HotXLSは両フォーマットでこのフラグを読み書きし、両エンジンで公開します:
- XLSXエンジンの
TXLSXWorkbook.FullPrecision: Boolean。calcPr/@fullPrecisionから読み込み、そこへ保存します - クラシックエンジンの
TXLSWorkbook.UseFullPrecision: Boolean（IXLSWorkbookにもあります）。CalcPrecisionレコードから読み込み、そこへ保存します - どちらもデフォルトはTrueです。安全で非破壊なモードであり、Excelのデフォルトでもあります
HotXLSがどこで丸めを適用するかは重要です。HotXLSは値を計算する時点で丸めます。Recalculateの間もオンデマンド評価の間も、各数式の結果は、セルのキャッシュ値として保存される前に表示精度へ丸められます。Value経由で代入した定数は、与えられた通り正確に保存されます。出力が、チェックを入れた後にExcelが保存するものを再現しなければならないなら、それらの定数は書き出す前に自分で丸めてください。たとえば後で示すヘルパーを使います
Excelは保持する小数の桁数をどう決めるのか
Excelは保持する小数の桁数を、値を表示する特定の書式セクションから導きます。書式文字列全体からではありません。以下のルールはExcel 16で実測されたもので、lxNumFormatのXlsApplyDisplayedPrecisionが両HotXLSエンジンのために実装しているものです
- 符号でセクションを選ぶ。2セクションの書式は負の値に2番目のセクションを使います。3セクション以上の書式は、負の値に2番目、正確にゼロに3番目を使います。それ以外はすべて1番目のセクションです
- 小数プレースホルダーを数える。そのセクションの小数点より後の
0、#、?のそれぞれが、保持する小数を1桁足します - パーセント記号1個につき2を足す。
0.0%は0.1234を12.3%と表示します。保存値は見た目の百分の一で、保持するのは3桁です。1桁ではありません - スケーリングのカンマ1個につき3を引く。最後の整数プレースホルダーの後のカンマ（
0,、0.0,、0,.0）は表示を1000で割ります。0.0,は12345.678を12.3と表示するので、Excelは保持する桁を1引く3、つまり負の数とします。値は百の位へ丸められ、12300として保存されます。整数プレースホルダーの間のカンマ、たとえば#,##0は、ただの桁区切りで何も変えません - 数値以外のセクションには触れない。General、日付と時刻のセクション（経過時間の
[h]、[mm]、[ss]を含む）、指数、分数、テキストのセクション、そして数字プレースホルダーをまったく持たないセクションは全精度を保ちます
Excel 16に対する実測で、両HotXLSエンジンが今、各書式の数式結果について保存する値は次のとおりです:
| 数値書式 | 計算値 | 保存値 | 適用されるルール |
|---|---|---|---|
0.0% | 0.1234 | 0.123 | パーセント記号で小数1桁に2を足す |
0 | 2.5 | 3 | ゼロから遠い方向の半分丸め。偶数への丸めではない |
0 | -2.5 | -3 | 負の側でもゼロから遠い方向の半分丸め |
0.00;(0.0) | -1.2345 | -1.2 | 負のセクションは小数1桁 |
0.00;(0.0) | 1.2345 | 1.23 | 正のセクションは小数2桁 |
#,##0.0 | 1234.5678 | 1234.6 | 桁区切りのカンマ。スケーリングではない |
0.0, | 12345.678 | 12300 | 小数1桁マイナス3：百の位へ丸める |
0.0%;(0.00%) | -0.0125 | -0.0125 | 負のセクションは2+2桁を保持 |
0.00 | 1.005 | 1.01 | 2進表現誤差への許容 |
0;-0;0.0 | 0.5 | 1 | ゼロではないので正のセクションが決める |
最後の行は見事な罠です。値0.5は整数へ丸められ、ゼロのセクションは一度も登場しません。Excelは丸める前に、計算値からセクションを選ぶからです。HotXLS側の正直な限界を1つ。セクションは符号だけで選ばれるので、[>=1000]のような独自の括弧条件をセクションに持つ書式でも、符号で分割されます。重要な書式なら、Excelと突き合わせて確認してください
1.005が1.00ではなく1.01に丸まるのはなぜか
Excelは0.00セルの1.005を1.01へ丸めます。1.005に最も近いdoubleが半分の位置よりわずかに下でも、です。HotXLSは数ulpの許容差でこれに合わせます。リテラルの1.005は2進浮動小数点では表現できません。最も近いIEEE 754のdoubleは1.00499999999999989341858963598497211933135986328125で、100を掛けると100.49999999999999になります。教科書どおりのFloor(x * 100 + 0.5) / 100は1.00を返します。ユーザーが打った数とも、Excelの表示とも、Excelの保存値とも食い違う形です
Delphiには独自のひねりがあります。System.Roundは同点を偶数へ丸めます。Round(2.5)は2、Round(3.5)は4です。これはバンカーズ丸めで、統計には理にかなったデフォルトですが、ここでは間違ったルールです。Excelは0セルの2.5に3を、-2.5に-3を保存します。HotXLSの実装は絶対値に対して動き、スケール済みの値に0.5と、2-51倍の相対許容差（この桁では数ulp、1.0の2ulp未満にはならない）を足し、切り捨て、スケールを戻し、符号を復元します。次の関数はその原理の自己完結した説明であって、ライブラリコードそのものではありません。スケーリングのカンマによる負の桁数も同じやり方で扱います:
// 原理のスケッチ：ゼロから遠い方向の半分丸めでADigits桁へ丸める。
// 数ulpの許容差付き。これで1.005が1.01に届く。
// ADigits < 0は10、100…の位へ丸める（"0.0,"なら-2）
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
Tolerance = 4.440892098500626E-16; // 2^-51、1.0の2 ulps
var
I: Integer;
Scale, Scaled, Eps: Double;
begin
Result := AValue;
if (ADigits < -15) or (ADigits > 14) then
Exit; // double精度の外：値はそのまま
Scale := 1;
for I := 1 to Abs(ADigits) do
Scale := Scale * 10;
if ADigits >= 0 then
begin
if Abs(AValue) > 1E300 / Scale then
Exit; // スケーリングするとオーバーフローする
Scaled := Abs(AValue) * Scale;
end
else
Scaled := Abs(AValue) / Scale;
Eps := Scaled * Tolerance;
if Eps < Tolerance then
Eps := Tolerance;
Scaled := Int(Scaled + 0.5 + Eps); // ゼロから遠い方向の半分丸め。Round()ではない
if ADigits >= 0 then
Result := Scaled / Scale
else
Result := Scaled * Scale;
if AValue < 0 then
Result := -Result;
end;
// RoundAsDisplayed(1.005, 2) = 1.01 (Floorベースでは1.00)
// RoundAsDisplayed(2.5, 0) = 3 (Roundなら2)
// RoundAsDisplayed(-2.5, 0) = -3
// RoundAsDisplayed(0.1234, 3) = 0.123 ("0.0%"：1 + 2桁)
// RoundAsDisplayed(12345.678, -2) = 12300 ("0.0,"：1 - 3桁)許容差は意図的なトレードオフです。半分のステップの本当に2ulp下にある値も切り上がりますが、その距離では表現誤差と区別がつきません。半分のステップとして扱うからこそ、打ち込まれた小数がユーザーの期待どおりに振る舞うのです
v2.384.57より前は何が間違っていたのか
v2.384.57より前は、XLSXエンジンとクラシックエンジンがそれぞれ独自のprecision-as-displayedコードを持ち、それぞれが別の仕方で間違っていました。オプションをオンにしてワークブックを作っているなら、古いビルドが生成したファイルで探すべき症状はこの3つです
XLSXエンジン：最初のセクションだけ、パーセントなし、バンカーズ丸め
古いXLSXの経路は、書式文字列全体の小数桁数を要求していました。これは最初のセクションしか見ず、%を無視し、それからRoundで丸めます。0.0%の0.1234は0.1として保存されていました。画面の12.3%ではなく10%です。0の2.5は3ではなく2として保存されました。0.00;(0.0)のような書式の負の値は、正のセクションの小数2桁へ丸められていました。v2.384.57から、XLSXエンジンはクラシックエンジンと同じ共有ルーチンを呼びます。このリリースで共有ルーチンはスケーリングカンマの対応も獲得しました
クラシックエンジン：TRUEが-1になった
クラシックエンジンは丸めをVarIsNumericで守っていましたが、VarIsNumericはvarBooleanのVariantに対してTrueを返します。そのVariantをDouble(V)で変換すると-1になります。COM式の論理Trueは-1として保存されるからです。だから0.00書式のセルの=A1>0のような数式は、再計算から数値の-1として出てきました。v2.384.57から、論理値の結果はどの数値テストより先に除外され、論理の結果は両エンジンで論理のまま保たれます
経過時間フォーマットが色として読まれた（v2.384.9）
3つ目のバグは、丸めではなく数値書式モデルに座っていました。パーサーは条件でないすべての括弧トークンを色として分類したため、[h]、[mm]、[ss]はセクションに日付/時刻の印を付けたことがありませんでした。表示には影響しません。書式化は別の経路で走るからです。しかしprecision as displayedは、時刻の値をスキップするためにその印に頼ります。5秒の所要時間は1日の5/86400、およそ0.0000579で、[ss].00のような書式は普通の小数2桁の数値に見えます。だからFullPrecisionがオフだと、所要時間は0.00日へ丸められていました。v2.384.9から、単一のh、m、s文字の括弧ランは経過時間トークンとしてパースされ、セクションは日付/時刻として扱われます。同じリリースでh:mmの分の検出も直りました。トークンの間のコロンが、パーサーから時を隠していたのです
DelphiからHotXLSでprecision as displayedを有効にする
Excelと同等の保存値を得るには、それを尊重させるべき再計算の前にフラグを設定し、それからキャッシュ結果を読むか保存します。XLSXエンジンでは、FullPrecisionは単なるフラグです。変更しても、以前のRecalculateがすでに保存した結果は無効になりません。だからCreateかOpenの直後、最初のRecalculateの前に設定してください。例が数式を使うのは、HotXLSが丸めを適用するのがそこだからです:
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb := TXLSXWorkbook.Create;
try
Sh := Wb.Sheets.Add('Totals');
Sh.Cells[1, 1].Value := 0.1234;
Sh.Cells[2, 1].Value := 2.5;
Sh.Cells[3, 1].Value := 12345.678;
Sh.Cells[1, 2].Formula := '=A1';
Sh.Cells[1, 2].NumberFormat := '0.0%'; // 12.3%と表示
Sh.Cells[2, 2].Formula := '=A2';
Sh.Cells[2, 2].NumberFormat := '0'; // 3と表示
Sh.Cells[3, 2].Formula := '=A3';
Sh.Cells[3, 2].NumberFormat := '0.0,'; // 12.3と表示（千単位）
// XLSXエンジンでは最初のRecalculateの前に設定すること
Wb.FullPrecision := False;
Wb.Recalculate;
// キャッシュ結果はExcel 16と一致：0.123、3、12300。
// 列Aの定数は全精度を保つ。
Assert(Abs(Double(Sh.Cells[1, 2].Value) - 0.123) < 1E-12);
Assert(Double(Sh.Cells[2, 2].Value) = 3);
Assert(Double(Sh.Cells[3, 2].Value) = 12300);
Wb.SaveAs('totals.xlsx'); // <calcPr fullPrecision="0"/>と書き出す
finally
Wb.Free;
end;
end;クラシックエンジンの挙動も同じです。便利な点が1つあります。TXLSWorkbook.UseFullPrecisionの代入は、依存グラフ内のすべての数式をダーティにします。次のRecalculateが、新しいルールの下でワークブック全体を再評価するのです。オプションがオンの間にNumberFormatを変更しても、影響を受ける数式セルはダーティになります。保存値を決めるのは今や書式だからです。クラシックのRecalculateは評価できなかった数式セルの数を返すことに注意してください。ゼロが成功です:
var
Wb: TXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
try
Sh := Wb.Sheets.Add;
Sh.Range['A1', 'A1'].Value := -1.2345;
Sh.Range['B1', 'B1'].Formula := '=A1';
Sh.Range['B1', 'B1'].NumberFormat := '0.00;(0.0)';
Sh.Range['C1', 'C1'].Formula := '=A1<0';
Sh.Range['C1', 'C1'].NumberFormat := '0.00';
Wb.UseFullPrecision := False; // すべての数式をダーティにする
if Wb.Recalculate <> 0 then
raise Exception.Create('Some formulas could not be evaluated');
// B1 = -1.2：負セクション"(0.0)"は小数1桁を表示
// C1は論理Trueのまま（v2.384.57以前のビルドは-1を保存していた）
Wb.SaveAs('report.xls'); // fFullPrec = 0のCalcPrecisionレコード
finally
Wb.Free;
end;
end;両エンジンとも、ファイルと一緒に入ってくるフラグも尊重します。オプションをオンにして保存されたワークブックを開けば、FullPrecisionかUseFullPrecisionはすでにFalseです。だから読み込み後のRecalculateは、Excelが丸めるのとまったく同じやり方で丸めます。Excelがすでに保存した数値を読むだけなら、再計算は丸ごとスキップできます。再計算なしでキャッシュ済み数式値を読むに書いたとおりです。シリアル番号と日付書式が、日付/時刻チェックを駆動する書式モデルとどう絡むかは、DelphiにおけるExcelの日付シリアル、1904システム、numFmtをご覧ください
precision as displayedをいつオンにすべきで、いつオンにすべきでないのか
precision as displayedをオンにするのは、ワークブックの保存数値が表示数値と等しくならねばならず、余分な桁を恒久的に失うことを受け入れるときだけです。古典的な正当なケースは財務のスケジュールです。丸めた金額の列が、画面上の丸め済み合計と一致せねばならず、隠れたセント未満の端数が、最後の桁で1つずれた合計を作ってはならない。すでにオプションが設定されている客の既存ワークブックに合わせるのも、もう1つの正当な理由です。HotXLSは往復でこのフラグを保持するので、黙って全精度へ戻してしまうことはありません
それ以外のほとんどの場面では避けてください:
- 工学と科学のデータ。誰かがレポート用に小数2桁の書式を選んだという理由で測定値を丸めると、後の書式変更では二度と戻せない情報を破壊します
- 粗い書式のパーセント。
0%の書式は保存された比率の小数2桁しか保たないので、0.1234は0.12になり、そのセルを読む下流のすべての数式が0.12で働きます - スケール表示。千単位を示すために使われる
0,や0.0,の書式は、保存値を千や百の位へ丸めます。書式を選んだ本人が意図していたことは、たいてい違います - 共有テンプレート。このフラグはワークブック全体に効きます。後からシートを足した人は、誰でも挙動を引き継ぎます。オンになっていることを、たいてい知らないままに
本当に欲しいのが、特定のいくつかのセルでの丸め済みの結果なら、代わりにそれらの数式にROUNDを書いてください。ROUNDは明示的で、セルに局所し、数式を読む誰の目にも見え、他の関数と同じくHotXLSの数式エンジンで評価されます。ワークブック全体への副作用はありません
precision as displayedのクイックリファレンス
- ファイルフラグ：BIFF8では
fFullPrec= 0のCalcPrecision$000E（[MS-XLS] §2.4.35）、XLSXではcalcPr fullPrecision="0"（ECMA-376 Part 1） - HotXLSのスイッチ：
TXLSXWorkbook.FullPrecision := FalseとTXLSWorkbook.UseFullPrecision := False。デフォルトはどちらもTrue - セクション：計算値の符号で選ぶ。3番目のセクションは正確にゼロのときだけ
- 桁数：小数プレースホルダー、
%ごとに+2、スケーリングのカンマごとに-3。桁数は負になり得ます - 丸め：数ulpの許容差付きのゼロから遠い方向の半分丸め。2.5は3、-2.5は-3、1.005は1.01
- スキップ対象：General、日付/時刻と経過時間、指数、分数、テキスト、論理値、エラー値
- HotXLSでの適用範囲：数式の結果を計算時に丸める。定数は代入の通り保存
- XLSXエンジン：最初の
Recalculateの前にFullPrecisionを設定する。クラシックのセッターは自分ですべての数式を再ダーティにする - バージョン：v2.384.57から両エンジンでExcel 16に一致。経過時間フォーマットの保護はv2.384.9から
HotXLSは、ここで扱ったワークブック計算オプションを含め、XLSとXLSXのワークブックをDelphiとC++Builderからネイティブに読み書き・計算します。詳細、エディション、トライアルダウンロードは、HotXLS Delphi spreadsheet component pageをご覧ください