Precision as displayed v HotXLS: zaokrúhľovanie ako Excel

Excel precision as displayed zaokrúhľuje každé uložené číslo na desatinné miesta, ktoré ukazuje jeho číselný formát: sekcia formátu zodpovedajúca znamienku hodnoty, dva desatinné navyše za každý %, tri menej za tisícovú čiarku, zaokrúhľanie polovice smerom od nuly. HotXLS aplikuje to isté pravidlo v oboch svojich Delphi engineoch, keď je TXLSXWorkbook.FullPrecision alebo TXLSWorkbook.UseFullPrecision False. Znie to ako jednoriadkovec, kým zákazník nenahlási, že vaše exportované súčty faktúr sa s Excelom mýlia o cent, alebo že stĺpec durácií v [ss].00 sa zrútil na nulu. Oboje sa stalo a oboje sa vystopovalo k tomu, že jedno z týchto pravidiel bolo zlé. Od v2.384.57 zdieľajú oba engine jedinú implementáciu, ktorej očakávané hodnoty boli odmerkané v Exceli 16 so zapnutým Workbook.PrecisionAsDisplayed

Čo precision as displayed v zošite vlastne mení?

Precision as displayed je jediný príznak na úrovni zošita, ktorý prikáže výpočtovému engine ukladať čísla tak, ako vyzerajú, nie tak, ako boli vypočítané. V Excel UI sedí pod File, Options, Advanced, „When calculating this workbook“, ako „Set precision as displayed“. Na disku je to jeden bit. Súbor BIFF8 nesie príznak v zázname CalcPrecision ($000E, [MS-XLS] §2.4.35), ktorého pole fFullPrec je 1 pre normálnu plnú presnosť a 0, keď je voľba zapnutá. Balík XLSX ho nesie ako atribút fullPrecision elementu calcPr v workbook.xml, definovaný v ECMA-376 Part 1, kde je predvolená hodnota true a fullPrecision="0" zapína zaokrúhľanie

Príznak nie je preferencia zobrazenia. Keď začiarknete políčko, Excel varuje, že dáta navždy stratia presnosť, a myslí to vážne: hodnoty sa prepíšu na zobrazovanú presnosť a číslice, ktoré sa odstrihli, sú preč. Neskoršie odškrtnutie políčka staré číslice nevráti. 0.1234 zobrazené ako 12.3% sa stane 0.123 naveky

HotXLS príznak v oboch formátoch číta aj zapisuje a vystavuje ho v oboch engineoch:

TXLSXWorkbook.FullPrecision: Boolean na engine XLSX, načítavané z a ukladané do calcPr/@fullPrecision
TXLSWorkbook.UseFullPrecision: Boolean na klasickom engine (aj na IXLSWorkbook), načítavané z a ukladané do záznamu CalcPrecision
Obe majú predvolenú hodnotu True, čo je bezpečný nedeštruktívny režim a zároveň predvolené nastavenie Excelu

Kde HotXLS zaokrúhľovanie aplikuje, záleží. HotXLS zaokrúhľuje v bode, kde hodnotu počíta: každý výsledok formuly sa zaokrúhli na zobrazovanú presnosť, skôr než sa uloží ako cached hodnota bunky, počas Recalculate aj počas výpočtu na požiadanie. Konštanty, ktoré priradíte cez Value, sa ukladajú presne tak, ako boli zadané. Ak musí váš výstup reprodukovať to, čo Excel uloží po začiarknutí políčka, zaokrúhlite tie konštanty sami pred zápisom, napríklad pomocnou funkciou ukázanou nižšie

Ako Excel rozhodne, koľko desatinných miest ponechá?

Excel odvodzuje počet ponechaných desatinných miest z konkrétnej sekcie formátu, ktorá hodnotu zobrazuje, nie z formátového reťazca ako celku. Nižšie uvedené pravidlá boli odmerkané v Exceli 16 a sú to to, čo XlsApplyDisplayedPrecision v lxNumFormat implementuje pre oba engine HotXLS

Sekcia sa volí podľa znamienka. Dvojsekčný formát používa druhú sekciu pre záporné hodnoty. Formát s tromi a viac sekciami používa druhú pre záporné hodnoty a tretiu pre presnú nulu. Všetko ostatné používa prvú sekciu
Spočítajte desatinné placeholdery. Každý 0, # alebo ? za desatinnou bodkou v tej sekcii pridá jedno ponechané desatinné miesto
Pridajte dva za každý znak percenta. 0.0% ukazuje 0.1234 ako 12.3%, takže uložená hodnota je stotina toho, čo vidíte, a drží tri desatinné miesta, nie jedno
Odpočítajte tri za každú škálovaciu čiarku. Čiarka za posledným celočíselným placeholderom (0,, 0.0,, 0,.0) delí zobrazenie 1000. 0.0, ukazuje 12345.678 ako 12.3, takže Excel drží jedno desatinné mínus tri, čo je záporný počet: hodnota sa zaokrúhli na stovky a uloží ako 12300. Čiarka medzi celočíselnými placeholdermi, ako v #,##0, je obyčajné zoskupovanie číslic a nič nemení
Numerické mimo čísiel nechajte tak. General, dátumové a časové sekcie (vrátane uplynulých [h], [mm] a [ss]), vedecké, zlomkové a textové sekcie a sekcie bez akéhokoľvek placeholderu číslice držia plnú presnosť

Diagram pravidiel zobrazovanej presnosti v HotXLS: vyberte sekciu formátu podľa znamienka hodnoty, spočítajte placeholdery číslic za desatinnou bodkou, pridajte dva desatinné za každý znak percenta, odpočítajte tri za tisícovú škálovaciu čiarku, takže počet môže byť záporný, celkom preskočte sekcie General a dátum/čas, potom zaokrúhlite polovicu od nuly — Počet číslic pochádza zo sekcie zodpovedajúcej znamienku, plus dva za percento a mínus tri za škálovaciu čiarku a záporný počet zaokrúhľuje na desiatky či stovky; sekcie General a dátumové sa nijak nedotknú

Namerané proti Excelu 16, toto sú hodnoty, ktoré oba engine HotXLS teraz ukladajú pre výsledok formuly v jednotlivých formátoch:

Číselný formát	Vypočítaná hodnota	Uložená hodnota	Pravidlo, ktoré sa aplikuje
`0.0%`	0.1234	0.123	Jedno desatinné plus dva za znak percenta
`0`	2.5	3	Polovica od nuly, nie na párne
`0`	-2.5	-3	Polovica od nuly aj na zápornej strane
`0.00;(0.0)`	-1.2345	-1.2	Záporná sekcia ukazuje jedno desatinné
`0.00;(0.0)`	1.2345	1.23	Kladná sekcia ukazuje dve desatinné
`#,##0.0`	1234.5678	1234.6	Zoskupovacia čiarka, žiadne škálovanie
`0.0,`	12345.678	12300	Jedno desatinné mínus tri: zaokrúhlenie na stovky
`0.0%;(0.00%)`	-0.0125	-0.0125	Záporná sekcia drží dva plus dva desatinné
`0.00`	1.005	1.01	Tolerancia na chybu binárnej reprezentácie
`0;-0;0.0`	0.5	1	Nie nula, takže rozhoduje kladná sekcia

Posledný riadok je pekná pasca. Hodnota 0.5 sa zaokrúhli na celé číslo a sekcia nuly nikdy nevstúpi do hry, lebo Excel volí sekciu z vypočítanej hodnoty pred zaokrúhlením. Jedno úprimné obmedzenie na strane HotXLS: sekcie sa volia len podľa znamienka, takže formát, ktorého sekcie nesú vlastné zátvorkové podmienky ako [>=1000], sa aj tak rozdelí podľa znamienka. Také formáty si proti Excelu skontrolujte, ak na nich záleží

Prečo sa 1.005 zaokrúhli na 1.01 a nie na 1.00?

Excel zaokrúhli 1.005 v bunke 0.00 na 1.01, hoci double najbližší k 1.005 je tesne pod polovičným bodom, a HotXLS to pokrýva toleranciou pár ulp. Literál 1.005 sa v binárnom pohyblivom rade nedá reprezentovať. Najbližší IEEE 754 double je 1.00499999999999989341858963598497211933135986328125 a vynásobenie 100 dáva 100.49999999999999. Učebnicové Floor(x * 100 + 0.5) / 100 preto vráti 1.00, čo sa rozchádza s číslom, ktoré používateľ napísal, s tým, čo Excel ukazuje, aj s tým, čo Excel ukladá

Delphi pridáva vlastnú zápletku. System.Round zaokrúhľuje remízy na párne, takže Round(2.5) je 2 a Round(3.5) je 4. To je bankérske zaokrúhľovanie, rozumný predvolený postup pre štatistiku a zlé pravidlo tu: Excel ukladá 3 pre 2.5 v bunke 0 a -3 pre -2.5. Implementácia HotXLS pracuje s absolútnou hodnotou, pridá 0.5 plus relatívnu toleranciu 2^-51 násobok škálovanej hodnoty (pár ulp pri tejto veľkosti, nikdy menej než dva ulp od 1.0), odsektne, vráti späť mierku a obnoví znamienko. Nasledujúca funkcia je sebestačná ilustrácia tohto princípu, nie samotný kód knižnice, a záporné počty číslic pre škálovacie čiarky rieši rovnakým spôsobom:

Diagram zaokrúhľovania v HotXLS: 2.5 sa zaokrúhli polovicou od nuly na 3 a -2.5 na -3, kde Delphi System.Round dáva bankérske odpovede 2 a -2, a keďže najbližší double k 1.005 sedí tesne pod polovičným bodom, tolerancia pár ulp je to, čo z floor založeného 1.00 urobí Excelovu odpoveď 1.01 — Excel zaokrúhľuje remízy od nuly a malou toleranciou odpúšťa chybu binárnej reprezentácie; obidva detaily sa dajú odmerkať a preskočenie ktoréhokoľvek uloží 2 pre 2.5 alebo 1.00 pre 1.005, o cent vedľa Excelu

// Náčrt princípu: zaokrúhli polovicu od nuly na ADigits desatinných,
// s toleranciou pár ulp, aby 1.005 dosiahlo 1.01.
// ADigits < 0 zaokrúhľuje na desiatky, stovky, ... ("0.0," dáva -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; // za hranicou presnosti double: nechaj hodnotu tak
  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álovanie by preteklo
    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); // polovica od nuly, nie 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   (cez 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 číslic)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 číslic)

Tolerancia je zámerný kompromis. Hodnota, ktorá je skutočne dva ulp pod polovičným krokom, sa tiež zaokrúhli nahor, ale na tej vzdialenosti je rozdiel neoddeliteľný od chyby reprezentácie a považovať ju za polovičný krok je presne to, vďaka čomu sa zadané desatinné čísla správajú tak, ako používatelia čakajú

Čo bolo zle pred v2.384.57?

Pred v2.384.57 mal engine XLSX aj klasický engine každý vlastný kód precision-as-displayed a každý bol zlý inak. Ak produkujete zošity so zapnutou voľbou, toto sú príznaky, ktoré treba hľadať v súboroch generovaných staršími zostaveniami

Engine XLSX: len prvá sekcia, žiadne percentá, bankérske zaokrúhľovanie

Stará cesta XLSX si vyžiadala počet desatinných formátového reťazca ako celku, čo sa pozeralo len na prvú sekciu a ignorovalo %, potom zaokrúhlila cez Round. 0.1234 v 0.0% sa uložilo ako 0.1, čo je 10% namiesto 12.3% na obrazovke. 2.5 v 0 sa uložilo ako 2 namiesto 3. Záporné hodnoty vo formáte ako 0.00;(0.0) sa zaokrúhlili na dve desatinné kladnej sekcie. Od v2.384.57 volá engine XLSX tú istú zdieľanú rutinu ako klasický engine, ktorý v tom istom vydaní získal aj podporu škálovacej čiarky

Klasický engine: TRUE sa stalo -1

Klasický engine chránil svoje zaokrúhľovanie cez VarIsNumeric a VarIsNumeric vracia True pre Variant varBoolean. Konverzia toho Variantu cez Double(V) dá -1, lebo COM štýl Boolean True sa ukladá ako -1. Formula ako =A1>0 v bunke naformátovanej 0.00 preto vyšla z prepočtu ako číslo -1. Od v2.384.57 sa Boolean výsledky vylúčia pred akýmkoľvek číselným testom a logický výsledok zostáva v oboch engineoch logickým výsledkom

Formáty uplynulého času čítané ako farby (v2.384.9)

Tretí bug sedel v modeli číselného formátu, nie v zaokrúhľovaní. Parser klasifikoval každý zátvorkový token, ktorý nebol podmienkou, ako farbu, takže [h], [mm] a [ss] nikdy neoznačili svoju sekciu ako dátum/čas. Zobrazenie nebolo ovplyvnené, lebo formátovanie beží na samostatnej ceste, ale precision as displayed sa na ten príznak spolieha, aby časové hodnoty preskočil. Päťsekundová durácia je 5/86400 dňa, asi 0.0000579 a formát ako [ss].00 vyzeral ako obyčajné dvojdesatinné číslo, takže so vypnutým FullPrecision sa durácia zaokrúhlila na 0.00 dňa. Od v2.384.9 sa zátvorkový beh jediného písmena h, m alebo s parsuje ako token uplynulého času a sekcia sa berie ako dátum/čas. To isté vydanie opravilo rozpoznávanie minút v h:mm, kde dvojbodka medzi tokenmi bývala skrývala hodinu pred parserom

Formátovanie bežalo na vlastnej ceste, takže bunka vyzerala správne, kým sa uložená hodnota zaokrúhlila na nulu; zátvorkové jediné písmeno h, m alebo s je token uplynulého času, nie farba, a sekcia drží plnú presnosť

Zapnutie precision as displayed v HotXLS z Delphi

Ak chcete uložené hodnoty rovnocenné Excelu, nastavte príznak pred prepočtom, ktorý ho má brať do úvahy, potom si prečítajte cached výsledky alebo ukladajte. Na engine XLSX je FullPrecision holý príznak: jeho zmena nezneplatní výsledky, ktoré už skorší Recalculate uložil, takže ho nastavte hneď po Create alebo Open a pred prvým Recalculate. Príklad používa formuly, lebo práve tam HotXLS aplikuje zaokrúhľovanie:

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)

    // Musí sa nastaviť pred prvým Recalculate na engine XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Cached výsledky teraz sedia s Excelom 16: 0.123, 3 a 12300.
    // Konštanty v stĺpci A si držia plnú presnosť.
    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'); // zapisuje <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Klasický engine sa správa rovnako, s jedným pohodlím: priradenie TXLSWorkbook.UseFullPrecision označí každú formulu v grafe závislostí za dirty, takže ďalší Recalculate znovu vyhodnotí celý zošit pod novým pravidlom. Zmena NumberFormat, kým je voľba zapnutá, tiež označí dotknuté formulové bunky za dirty, lebo formát teraz rozhoduje o uloženej hodnote. Vezmite na vedomie, že klasický Recalculate vracia počet formulových buniek, ktoré nedokázal vyhodnotiť, takže nula znamená úspech:

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ždú formulu za dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: záporná sekcia "(0.0)" ukazuje jedno desatinné
    // C1 ostáva Boolean True (zostavenia pred v2.384.57 ukladali -1)
    Wb.SaveAs('report.xls'); // záznam CalcPrecision s fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Oba engine zároveň rešpektujú príznak, ktorý príde so súborom. Otvorte zošit uložený so zapnutou voľbou a FullPrecision alebo UseFullPrecision je už False, takže Recalculate po načítaní zaokrúhli presne tak, ako by to urobil Excel. Ak potrebujete len prečítať čísla, ktoré Excel už uložil, môžete prepočet celkom vynechať, ako popisuje čítanie cached hodnôt formulí bez prepočtu. Ako spolupôsobia sériové čísla a dátumové formáty s modelom formátu, ktorý poháňa kontrolu dátum/čas, popisuje Excel dátumové sériové čísla, systém 1904 a numFmt v Delphi

Kedy precision as displayed zapnúť a kedy nie?

Precision as displayed zapínajte len vtedy, keď sa uložené čísla zošita musia rovnať zobrazovaným číslam a prijímate, že číslice navyše stratíte naveky. Klasický legitímny prípad je finančná plánovacia tabuľka, kde stĺpce zaokrúhlených čiastok musia sčítať na zaokrúhlený celok na obrazovke, bez skrytých zlomkov centu, ktoré vyrobia celok odchýlný o jednu v poslednom mieste. Zhoda so súčasným zošitom zákazníka, ktorý má voľbu už nastavenú, je druhý dobrý dôvod a HotXLS príznak pri round-tripe zachováva, aby ste ich poticho neprepli späť na plnú presnosť

Vo väčšine ostatných situácií sa mu vyhnite:

Inžinierske a vedecké dáta. Zaokrúhlenie merania preto, lebo si niekto pre report zvolil dvojdesatinný formát, ničí informáciu, ktorú neskôršia zmena formátu neobnoví
Percentá s hrubými formátmi. Formát 0% drží len dve desatinné uloženého pomeru, takže 0.1234 sa stane 0.12 a každá formula po prúde, ktorá bunku číta, pracuje s 0.12
Škálované zobrazenia. Formát 0, alebo 0.0, použitý na ukazovanie tisícov zaokrúhli uloženú hodnotu na tisíce či stovky, čo je zriedka to, čo osoba, ktorá formát zvolila, zamýšľala
Zdieľané šablóny. Príznak platí na celý zošit. Ktokoľvek, kto neskôr pridá hárok, zdedí toto správanie, zvyčajne bez toho, aby vedel, že je zapnutý

Ak tým, čo skutočne chcete, sú zaokrúhlené výsledky v pár konkrétnych bunkách, napíšte radšej do týchto formulí ROUND. ROUND je explicitný, lokálny na bunku, viditeľný pre každého, kto formulu číta, a vyhodnocuje ho formulový engine HotXLS ako akúkoľvek inú funkciu, bez vedľajších účinkov na úrovni zošita

Rýchly prehľad precision as displayed

Príznak súboru: CalcPrecision $000E s fFullPrec = 0 v BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" v XLSX (ECMA-376 Part 1)
Prepínače HotXLS: TXLSXWorkbook.FullPrecision := False a TXLSWorkbook.UseFullPrecision := False, oboje predvolene True
Sekcia: volená podľa znamienka vypočítanej hodnoty; tretia sekcia len pre presnú nulu
Číslice: desatinné placeholdery, plus dva za %, mínus tri za škálovaciu čiarku; počet môže byť záporný
Zaokrúhľovanie: polovica od nuly s toleranciou pár ulp, takže 2.5 dá 3, -2.5 dá -3 a 1.005 dá 1.01
Preskočené: General, dátum/čas a uplynulý čas, vedecké, zlomkové, textové, Boolean a chybové hodnoty
Rozsah v HotXLS: výsledky formulí v momente výpočtu; konštanty sa ukladajú, ako boli priradené
Engine XLSX: nastavte FullPrecision pred prvým Recalculate; klasický setter si sám znova označí všetky formuly za dirty
Verzie: zladené s Excelom 16 v oboch engine od v2.384.57; formáty uplynulého času chránené od v2.384.9

HotXLS číta, zapisuje a počíta zošity XLS a XLSX natívne z Delphi a C++Builder, vrátane výpočtových volieb zošita popísaných tu. Detaily, edície a skúšobnú verziu na stiahnutie nájdete na stránke tabuľkového komponentu HotXLS pre Delphi