Precision as displayed v HotXLS: zaokrouhlování podle Excelu

Excel v režimu precision as displayed zaokrouhlí každé uložené číslo na desetinná místa, která ukazuje jeho číselný formát: sekce formátu odpovídající znaménku hodnoty, dvě desetinná místa navíc na každý znak %, tři místa méně na každou škálovací čárku tisíců, s polovinami odcházejícími od nuly. HotXLS aplikuje totéž pravidlo v obou svých Delphi jádrech, když je TXLSXWorkbook.FullPrecision nebo TXLSWorkbook.UseFullPrecision False. To zní jako jeden řádek kódu, dokud zákazník nenahlásí, že součty vaší exportované faktury se od Excelu liší o cent, nebo že sloupec dob v [ss].00 splaskl na nulu. Obojí se stalo a obojí se dohledalo k jednomu z pravidel, které se pokazilo. Od v2.384.57 sdílejí obě jádra jedinou implementaci, jejíž očekávané hodnoty se měřily v Excelu 16 se zapnutým Workbook.PrecisionAsDisplayed

Co v sešitu precision as displayed doopravdy mění?

Precision as displayed je jediný příznak na úrovni sešitu, který říká výpočetnímu jádru, aby čísla ukládal tak, jak vypadají, ne tak, jak vyšla z výpočtu. V UI Excelu sedí pod File, Options, Advanced, „When calculating this workbook“, jako „Set precision as displayed“. Na disku je to jeden bit. Soubor BIFF8 ho nese v záznamu CalcPrecision ($000E, [MS-XLS] §2.4.35), jehož pole fFullPrec je 1 pro normální plnou přesnost a 0, je-li volba zapnutá. Balíček XLSX ho nese jako atribut fullPrecision prvku calcPr v workbook.xml, definovaného v ECMA-376 Part 1, kde je výchozí hodnota true a fullPrecision="0" zapíná zaokrouhlování

Tenhle příznak není předvolba zobrazení. Když zaškrtnete políčko, Excel varuje, že data natrvalo ztratí na přesnosti, a myslí to vážně: hodnoty se přepíší na zobrazovanou přesnost a vystřižené číslice jsou pryč. Pozdější odškrtnutí staré číslice nevrátí. 0.1234 zobrazená jako 12.3% se nadobro stane 0.123

HotXLS čte i zapisuje příznak v obou formátech a vystavuje ho v obou jádrech:

• TXLSXWorkbook.FullPrecision: Boolean na XLSX jádře, načítaný a ukládaný do/z calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean na klasickém jádře (i na IXLSWorkbook), načítaný a ukládaný do/z záznamu CalcPrecision
• Obojí má výchozí hodnotu True, což je bezpečný, ničemu neubližující režim a zároveň výchozí chování Excelu

Záleží, kde HotXLS zaokrouhlování aplikuje. HotXLS zaokrouhluje v místě, kde hodnotu počítá: výsledek každé formule se před uložením jako hodnota do mezipaměti buňky zaokrouhlí na zobrazovanou přesnost, během Recalculate i během vyhodnocování na požádání. Konstanty, které přiřadíte přes Value, se ukládají přesně tak, jak jsou. Má-li váš výstup reprodukovat to, co Excel uloží po zaškrtnutí políčka, zaokrouhlete ty konstanty sami před zápisem, třeba pomocníkem ukázaným níže

Jak si Excel vybírá, kolik desetinných míst uchová?

Excel odvozuje počet uchovaných desetinných míst z konkrétní sekce formátu, která hodnotu zobrazuje, ne z formátovacího řetězce jako celku. Níže uvedená pravidla se měřila v Excelu 16 a je to to, co implementuje XlsApplyDisplayedPrecision v lxNumFormat pro obě jádra HotXLS

1. Vyberte sekci podle znaménka. Dvousekční formát užívá pro záporné hodnoty druhou sekci. Formát se třemi a více sekcemi užívá druhou pro záporné a třetí pro přesnou nulu. Všechno ostatní používá první sekci
2. Spočítejte desetinné zástupné znaky. Každý 0, # nebo ? za desetinnou tečkou v té sekci přidává jedno uchované desetinné místo
3. Přičtěte dvě na každé procento. 0.0% ukazuje 0.1234 jako 12.3%, takže uložená hodnota je setina toho, co vidíte, a drží tři desetinná místa, ne jedno
4. Odečtěte tři na každou škálovací čárku. Čárka za posledním celočíselným zástupným znakem (0,, 0.0,, 0,.0) dělí zobrazení 1000. 0.0, ukazuje 12345.678 jako 12.3, takže Excel drží jedno desetinné místo minus tři, což je záporný počet: hodnota se zaokrouhlí na stovky a uloží se jako 12300. Čárka mezi celočíselnými zástupnými znaky, jako v #,##0, je obyčejné seskupování číslic a nic nemění
5. Nechte být nečíselné sekce. General, sekce data a času (včetně uplynulých [h], [mm] a [ss]), vědecké, zlomkové a textové sekce a sekce bez jakéhokoli číslicového zástupného znaku si nechají plnou přesnost

Měřeno proti Excelu 16, tohle jsou hodnoty, které obě jádra HotXLS teď ukládají pro výsledek formule v jednotlivých formátech:

Číselný formát	Spočítaná hodnota	Uložená hodnota	Pravidlo, které se uplatní
0.0%	0.1234	0.123	Jedno desetinné místo plus dvě za procento
0	2.5	3	Polovina od nuly, ne na sudou
0	-2.5	-3	Polovina od nuly i na záporné straně
0.00;(0.0)	-1.2345	-1.2	Záporná sekce ukazuje jedno desetinné místo
0.00;(0.0)	1.2345	1.23	Kladná sekce ukazuje dvě desetinná místa
#,##0.0	1234.5678	1234.6	Seskupovací čárka, žádné škálování
0.0,	12345.678	12300	Jedno desetinné místo minus tři: zaokrouhlení na stovky
0.0%;(0.00%)	-0.0125	-0.0125	Záporná sekce drží dvě plus dvě desetinná místa
0.00	1.005	1.01	Tolerance chyby binární reprezentace
0;-0;0.0	0.5	1	Není nula, takže rozhoduje kladná sekce

Poslední řádek je hezká past. Hodnota 0.5 se zaokrouhluje na celé číslo a sekce nula se vůbec neuplatní, protože Excel vybírá sekci podle spočítané hodnoty před zaokrouhlením. Jedna upřímná mezera na straně HotXLS: sekce se vybírají jen podle znaménka, takže formát, jehož sekce nesou vlastní podmínky v hranatých závorkách jako [>=1000], se pořád dělí podle znaménka. Pokud vám takové formáty leží na srdci, ověřte je proti Excelu

Proč se 1.005 zaokrouhlí na 1.01 a ne na 1.00?

Excel zaokrouhlí 1.005 v buňce 0.00 na 1.01, i když double nejbližší k 1.005 leží mírně pod půlí, a HotXLS se srovná pomocí tolerance několika ulp. Literál 1.005 se v binárním plovoucí řádu nedá znázornit. Nejbližší double IEEE 754 je 1.00499999999999989341858963598497211933135986328125 a vynásobení 100 dá 100.49999999999999. Učebnicový Floor(x * 100 + 0.5) / 100 proto vrátí 1.00, což se rozbíhá s číslem, které uživatel napsal, s tím, co Excel ukazuje, i s tím, co Excel ukládá

Delphi přidává vlastní twist. System.Round zaokrouhluje remízy na sudé, takže Round(2.5) je 2 a Round(3.5) je 4. To je bankéřské zaokrouhlování, rozumný výchozí stav pro statistiku a špatné pravidlo tady: Excel ukládá pro 2.5 v buňce 0 hodnotu 3 a pro -2.5 hodnotu -3. Implementace HotXLS pracuje s absolutní hodnotou, přičte 0.5 plus relativní toleranci 2^-51 násobek škálované hodnoty (pár ulp na téhle velikosti, nikdy méně než dva ulp od 1.0), ořízne, přeškáluje zpět a obnoví znaménko. Následující funkce je samostatná ilustrace toho principu, nikoli kód knihovny, a záporné počty číslic pro škálovací čárky řeší stejným způsobem:

// Principová skica: zaokrouhlení s polovinou od nuly na ADigits desetinných míst,
// s tolerancí pár ulp, aby 1.005 dosáhlo 1.01.
// ADigits < 0 zaokrouhluje na desítky, stovky, ... ("0.0," dává -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, dva ulp od 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // mimo přesnost double: nech hodnotu být
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // škálování by přeteklo
    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); // polovina od nuly, ne 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   (floorově: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 číslic)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 číslic)

Tolerance je záměrný kompromis. Hodnota, která leží doopravdy dva ulp pod půlkrokem, se taky zaokrouhlí nahoru, ale na té vzdálenosti už se rozdíl nedá rozeznat od chyby reprezentace a brát ji jako půlkrok je právě to, co donutí napsaná desetinná čísla chovat se, jak uživatelé čekají

Co se kazilo před v2.384.57?

Před v2.384.57 mělo XLSX jádro i klasické jádro každé vlastní kód pro precision as displayed a každé bylo špatně jinak. Vyrábíte-li sešity se zapnutou volbou, tohle jsou příznaky, po kterých se máte dívat v souborech ze starších buildů

XLSX jádro: jen první sekce, žádná procenta, bankéřské zaokrouhlování

Stará cesta XLSX si vyžádala počet desetinných míst formátovacího řetězce jako celku, což se dívalo jen na první sekci a ignorovalo %, a pak zaokrouhlila přes Round. 0.1234 v 0.0% se uložilo jako 0.1, což je 10% místo 12.3% na obrazovce. 2.5 v 0 se uložilo jako 2 místo 3. Záporné hodnoty ve formátu jako 0.00;(0.0) se zaokrouhlily na dvě desetinná místa kladné sekce. Od v2.384.57 volá XLSX jádro tutéž sdílenou rutinu jako klasické jádro, které v tom vydání navíc dostalo podporu škálovacích čárek

Klasické jádro: TRUE se stalo -1

Klasické jádro si svoje zaokrouhlování střežilo přes VarIsNumeric a VarIsNumeric vrací True pro Variant varBoolean. Převod takového Variantu přes Double(V) dá -1, protože COM-ovské logické True se ukládá jako -1. Formule jako =A1>0 v buňce formátované 0.00 tedy vyšla z přepočtu jako číslo -1. Od v2.384.57 se logické výsledky vyřazují před jakýmkoli číselným testem a logický výsledek zůstává logickým výsledkem v obou jádrech

Formáty uplynulého času čtené jako barvy (v2.384.9)

Třetí bug seděl spíš v modelu číselného formátu než v zaokrouhlování. Parser klasifikoval každý token v hranatých závorkách, který nebyl podmínkou, jako barvu, takže [h], [mm] a [ss] nikdy neoznačily svou sekci jako datum/čas. Zobrazení tím neutrpělo, protože formátování běží po samostatné cestě, ale precision as displayed se na tenhle příznak spoléhá, aby hodnoty času vynechala. Doba pěti sekund je 5/86400 dne, zhruba 0.0000579, a formát jako [ss].00 vypadal jako obyčejné dvojmístné desetinné číslo, takže s vypnutým FullPrecision se doba zaokrouhlila na 0.00 dne. Od v2.384.9 se běh jediného písmene h, m nebo s v hranatých závorkách parsuje jako token uplynulého času a sekce se bere jako datum/čas. Táž verze spravila detekci minut v h:mm, kde dvojtečka mezi tokeny dřív skrývala hodiny před parserem

Zapínání precision as displayed v HotXLS z Delphi

Aby se uložené hodnoty rovnaly Excelu, nastavte příznak před tím přepočtem, který ho má respektovat, a pak čtěte výsledky z mezipaměti nebo ukládejte. Na XLSX jádře je FullPrecision holý příznak: změna neznemožní výsledky, které dřívější Recalculate už uložil, takže nastavte ho hned po Create nebo Open a před prvním Recalculate. Příklad užívá formule, protože tam HotXLS zaokrouhlování aplikuje:

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%';   // ukazuje 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // ukazuje 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // ukazuje 12.3 (tisíce)

    // Nutno nastavit před prvním Recalculate na XLSX jádře
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Výsledky v mezipaměti teď odpovídají Excelu 16: 0.123, 3 a 12300.
    // Konstanty ve sloupci A si drží plnou přesnost.
    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'); // zapíše <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Klasické jádro se chová stejně, s jedním pohodlím: přiřazení TXLSWorkbook.UseFullPrecision označí každou formuli v grafu závislostí jako špinavou, takže příští Recalculate přehodnotí celý sešit pod novým pravidlem. Změna NumberFormat za zapnuté volby označí jako špinavé i dotčené buňky s formulěmi, protože formát teď rozhoduje o ukládané hodnotě. Vezměte na vědomí, že klasické Recalculate vrací počet formulových buněk, které nedokázalo vyhodnotit, takže nula znamená úspěch:

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; // označí každou formuli jako špinavou
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: záporná sekce "(0.0)" ukazuje jedno desetinné místo
    // C1 zůstává logické True (buildy před v2.384.57 ukládaly -1)
    Wb.SaveAs('report.xls'); // záznam CalcPrecision s fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Obě jádra respektují i příznak, který přichází se souborem. Otevřete sešit uložený se zapnutou volbou a FullPrecision nebo UseFullPrecision už je False, takže Recalculate po načtení zaokrouhlí přesně tak, jak by Excel. Potřebujete-li jen přečíst čísla, která Excel už uložil, přepočet můžete vynechat úplně, jak popisuje čtení hodnot formulí z mezipaměti bez přepočtu. Jak spolupracují sériová čísla a formáty data s formátovým modelem, na kterém stojí test datum/čas, viz sériová čísla data v Excelu, systém 1904 a numFmt v Delphi

Kdy precision as displayed zapnout a kdy ne?

Zapínejte precision as displayed jen tehdy, když se uložená čísla sešitu musejí rovnat zobrazovaným číslům a přijímáte, že ztracené číslice už se nevrátí. Klasický legitimní případ je finanční rozvrh, kde sloupce zaokrouhlených částek musejí dát dohromady zaokrouhlený součet na obrazovce, bez skrytých zlomků centu, které by součet posunuly o jedničku na posledním místě. Vystihnout sešit zákazníka, který už volbu má nastavenou, je druhý dobrý důvod a HotXLS si příznak na round-tripu udržuje, takže je nepřepnete potichu zpět na plnou přesnost

Ve většině ostatních situací se mu vyhněte:

• Inženýrská a vědecká data. Zaokrouhlit měření, protože si někdo vybral dvoumístný formát pro report, ničí informaci, kterou žádná pozdější změna formátu nevrátí
• Procenta s hrubými formáty. Formát 0% drží z uloženého poměru jen dvě desetinná místa, takže 0.1234 se stane 0.12 a každá formule po proudu, která buňku čte, pracuje s 0.12
• Škálované zobrazení. Formát 0, nebo 0.0, použitý pro tisíce zaokrouhlí uloženou hodnotu na tisíce nebo stovky, což bývá málokdy to, co zamýšlel ten, kdo formát vybral
• Sdílené šablony. Příznak je celosešitový. Kdokoli později přidá list, dědí to chování, většinou ani nevědoma si, že je zapnuté

Chcete-li doopravdy zaokrouhlené výsledky v pár konkrétních buňkách, napište do těch formulí ROUND. ROUND je explicitní, lokální pro buňku, viditelný komukoli, kdo formuli čte, a vyhodnocuje ho vzorcové jádro HotXLS jako jakoukoli jinou funkci, bez celosešitových vedlejších účinků

Rychlý přehled precision as displayed

• Souborový příznak: CalcPrecision $000E s fFullPrec = 0 v BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" v XLSX (ECMA-376 Part 1)
• Přepínače HotXLS: TXLSXWorkbook.FullPrecision := False a TXLSWorkbook.UseFullPrecision := False, obě výchozí True
• Sekce: vybírá se podle znaménka spočítané hodnoty; třetí sekce jen pro přesnou nulu
• Číslice: desetinné zástupné znaky, plus dvě na každé %, mínus tři na škálovací čárku; počet může být záporný
• Zaokrouhlování: polovina od nuly s tolerancí pár ulp, takže 2.5 dává 3, -2.5 dává -3 a 1.005 dává 1.01
• Vynecháno: General, datum/čas a uplynulý čas, vědecké, zlomkové, textové, logické a chybové hodnoty
• Rozsah v HotXLS: výsledky formulí v momentě výpočtu; konstanty se ukládají tak, jak byly přiřazeny
• XLSX jádro: nastavte FullPrecision před prvním Recalculate; klasický setter označí všechny formule za špinavé sám
• Verze: srovnáno s Excelem 16 v obou jádrech od v2.384.57; formáty uplynulého času chráněny od v2.384.9

HotXLS čte, zapisuje a počítá sešity XLS a XLSX nativně z Delphi a C++Builderu, včetně výpočetních voleb sešitu rozebraných tady. Detaily, edice a zkušební stažení najdete na stránce tabulkové komponenty HotXLS pro Delphi