HotXLS precision as displayed: az Excel kerekítési szabályai

Az Excel precision as displayed minden tárolt számot arra a tizedesszámra kerekít, amit a számformátuma mutat: az érték előjeléhez illeszkedő formátumszakaszra, %-onként két plusz tizedesre, ezres-skálázó vesszőnként hárommal kevesebbre, nullától félre kerekítve. A HotXLS ugyanezt a szabályt alkalmazza mindkét Delphi enginejében, amikor a TXLSXWorkbook.FullPrecision vagy TXLSWorkbook.UseFullPrecision False. Ez egysorosnak hangzik, amíg egy ügyfél nem jelenti, hogy az exportált számlaösszegeid egy ccent tévednek az Excelel szemben, vagy hogy egy [ss].00 formátumú időtartam-oszlop nullára zsugorodott. Mindkettő megtörtént, és mindkettő e szabályok egyikének elrontására vezethető vissza. v2.384.57 óta a két engine egyetlen implementációt oszt meg, aminek az elvárt értékeit Excel 16-ban mérték ki, bekapcsolt Workbook.PrecisionAsDisplayed-del

Mit változtat valójában a precision as displayed egy munkafüzetben?

A precision as displayed egyetlen munkafüzetszintű flag, ami azt mondja a számító enginnek, hogy a számokat úgy tárolja, ahogy néznek ki, nem úgy, ahogy kiszámolódtak. Az Excel UI-ban a File, Options, Advanced alatt ül, a „When calculating this workbook" résznél, mint „Set precision as displayed". Lemezen egy bit. Egy BIFF8 fájl a CalcPrecision rekordban hordozza ($000E, [MS-XLS] §2.4.35), aminek a fFullPrec mezője 1 normál teljes pontosságra és 0, ha az opció be van kapcsolva. Egy XLSX csomag a workbook.xml calcPr elemének fullPrecision attribútumaként hordozza, definiálva az ECMA-376 Part 1-ben, ahol az alapérték true, és a fullPrecision="0" kapcsolja be a kerekítést

A flag nem megjelenítési preferencia. Amikor bepipálod, az Excel figyelmeztet, hogy az adat véglegesen pontosságot veszít, és komolyan gondolja: az értékek a megjelenített pontosságukra íródnak át, és a levágott számjegyek elvesznek. A pipa későbbi törlése nem hozza vissza a régi számjegyeket. Egy 12.3%-ként mutatott 0.1234 jóra 0.123 lesz

A HotXLS mindkét formátumban olvassa és írja a flaget, és mindkét engineben teszi elérhetővé:

• TXLSXWorkbook.FullPrecision: Boolean az XLSX enginen, a calcPr/@fullPrecision-ből betöltve és oda mentve
• TXLSWorkbook.UseFullPrecision: Boolean a Classic enginen (szintén az IXLSWorkbook-on), a CalcPrecision rekordból betöltve és oda mentve
• Mindkettő alapértéke True, ami a biztonságos, nem destruktív mód, és az Excel alapértéke

Az számít, hol alkalmazza a HotXLS a kerekítést. A HotXLS ott kerekít, ahol az értéket kiszámolja: minden képleteredmény a megjelenített pontosságára kerekítődik, mielőtt a cella cache-elt értékeként tárolódna, a Recalculate közben és az igény szerinti kiértékelésnél is. A Value-n át hozzárendelt konstansok pontosan úgy tárolódnak, ahogy adtad őket. Ha a kimenetednek reprodukálnia kell, amit az Excel tárol a pipa bekapcsolása után, magad kerekítsd azokat a konstansokat, mielőtt kiírnád, például a később mutatott helperrel

Hogyan dönti el az Excel, hány tizedest tart meg?

Az Excel a megtartott tizedek számát a konkrét formátumszakaszból vezeti le, ami megjeleníti az értéket, nem az egész formátumstringből. Az alábbi szabályokat Excel 16-ban mérték ki, és ezek azok, amiket a lxNumFormat-beli XlsApplyDisplayedPrecision implementál mindkét HotXLS enginen

1. Válaszd ki a szakaszt előjel szerint. Egy kétszakaszos formátum a második szakaszt használja negatív értékekre. Egy három vagy több szakaszos formátum a másodikat a negatív értékekre, a harmadikat pontosan nullára használja. Minden más az első szakaszt használja
2. Számold meg a tizedeshelyőrzőket. Az adott szakaszban a tizedespont utáni minden 0, # vagy ? egy megtartott tizedest ad
3. Adj hozzá kettőt százalékjelenként. A 0.0% a 0.1234-et 12.3%-ként mutatja, tehát a tárolt érték százada annak, amit látsz, és három tizedest tart, nem egyet
4. Vonj le hármat skálázó vesszőnként. Az utolsó egész helyőrző utáni vessző (0,, 0.0,, 0,.0) a megjelenítést ezerrel osztja. A 0.0, a 12345.678-at 12.3-ként mutatja, így az Excel egy tizedes mínusz hármat tart, ami negatív szám: az érték százasokra kerekítődik, és 12300-ként tárolódik. Az egész helyőrzők közt álló vessző, mint a #,##0-ban, sima számjegycsoportosítás, és semmit sem változtat
5. Hagyd békén a nem numerikus szakaszokat. A General, dátum és idő szakaszok (az elapsed [h], [mm] és [ss] is beleértve), a tudományos, törtes és szövegszakaszok, meg a bármilyen számjegyhelyőrző nélküli szakaszok teljes pontosságot tartanak

Excel 16-hoz mérve ezek azok az értékek, amiket most már mindkét HotXLS engine tárol egy képleteredményre mindegyik formátumban:

Számformátum	Kiszámolt érték	Tárolt érték	Érvényesülő szabály
0.0%	0.1234	0.123	Egy tizedes plusz kettő a százalékjel miatt
0	2.5	3	Félre nullától, nem párosra
0	-2.5	-3	Félre nullától a negatív oldalon is
0.00;(0.0)	-1.2345	-1.2	A negatív szakasz egy tizedest mutat
0.00;(0.0)	1.2345	1.23	A pozitív szakasz két tizedest mutat
#,##0.0	1234.5678	1234.6	Csoportosító vessző, skálázás nélkül
0.0,	12345.678	12300	Egy tizedes mínusz három: kerekítés százasokra
0.0%;(0.00%)	-0.0125	-0.0125	A negatív szakasz két plusz kettő tizedest tart
0.00	1.005	1.01	Tolerancia a bináris reprezentációs hibára
0;-0;0.0	0.5	1	Nem nulla, tehát a pozitív szakasz dönt

Az utolsó sor szép csapda. A 0.5 egész számra kerekítődik, és a nulla szakasz soha nem jön szóba, mert az Excel a szakaszt a kiszámolt értékből választja a kerekítés előtt. Egy becsületes korlát a HotXLS oldalon: a szakaszok csak előjel szerint választódnak, tehát egy olyan formátum, aminek a szakaszai egyéni zárójelfeltételeket hordoznak, mint a [>=1000], továbbra is előjel szerint bomlik szét. Ellenőrizd az ilyen formátumokat Excellel szemben, ha számítanak

Miért kerekítődik az 1.005 1.01-re és nem 1.00-ra?

Az Excel az 1.005-öt egy 0.00 cellában 1.01-re kerekíti, annak ellenére, hogy az 1.005-höz legközelebbi double kicsit a felezőpont alatt van, és a HotXLS néhány ulp toleranciával egyezik meg vele. Az 1.005 literál nem reprezentálható bináris lebegőpontosan. A legközelebbi IEEE 754 double az 1.00499999999999989341858963598497211933135986328125, és százzal megszorozva 100.49999999999999-et ad. Egy tankönyvi Floor(x * 100 + 0.5) / 100 ezért 1.00-t ad, ami ellentmond a felhasználó által beütött számnak, annak, amit az Excel mutat, és annak, amit az Excel tárol

A Delphi a maga csavarját teszi hozzá. A System.Round a döntetleneket párosra kerekíti, így a Round(2.5) 2, a Round(3.5) 4. Az banker-kerekítés, értelmes alapértelmezés statisztikához és rossz szabály itt: az Excel 3-at tárol a 2.5-re egy 0 cellában, és -3-at a -2.5-re. A HotXLS implementáció az abszolút értéken dolgozik, hozzáad 0.5-öt plusz a skálázott érték 2^-51-szeres relatív toleranciát (pár ulp ezen a magnitúdon, sosem kevesebb az 1.0 két ulpjánál), csonkol, visszaskáláz és visszaállítja az előjelet. Az alábbi függvény az elv önálló illusztrációja, nem maga a library kódja, és a skálázó vesszők negatív számjegyszámait ugyanígy kezeli:

// Elvi vázlat: kerekítés fél távolságra nullától ADigits tizedesre,
// néhány ulp toleranciával, hogy az 1.005 elérje az 1.01-et.
// ADigits < 0 esetén tízesekre, százasokra, ... kerekít (a "0.0," -2-t ad)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, az 1.0 két ulpja
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // a double pontosságon túl: hagyd a value-t békén
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // a skálázás túlcsordulna
    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); // félre nullától, nem 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-alapú: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 számjegy)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 számjegy)

A tolerancia tudatos kompromisszum. Egy valóban két ulppal a fél lépés alatt álló érték is felfelé kerekítődik, de azon a távolságon a különbség megkülönböztethetetlen a reprezentációs hibától, és fél lépésként kezelni az teszi lehetővé, hogy a beütött tizedesek úgy viselkedjenek, ahogy a felhasználók várják

Mi romlott el v2.384.57 előtt?

v2.384.57 előtt az XLSX engine és a Classic engine mindegyike a maga precision-as-displayed kódját hordozta, és mindegyik másképp volt rossz. Ha bekapcsolt opcióval produkálsz munkafüzeteket, ezek azok a tünetek, amiket az öregebb buildek fájljaiban érdemes keresni

XLSX engine: csak az első szakasz, nincs százalék, banker-kerekítés

A régi XLSX út az egész formátumstring tizedesszámát kérte, ami csak az első szakaszt nézte, és ignorálta a %-ot, aztán Round-dal kerekített. Egy 0.1234 a 0.0%-ban 0.1-ként tárolódott, ami 10% a képernyőn lévő 12.3% helyett. Egy 2.5 a 0-ban 2-ként tárolódott 3 helyett. Negatív értékek egy 0.00;(0.0) szerű formátumban a pozitív szakasz két tizedére kerekültek. v2.384.57 óta az XLSX engine ugyanazt a közös rutint hívja, mint a Classic engine, ami abban a kiadásban skálázó-vessző támogatást is kapott

Classic engine: a TRUE -1 lett

A Classic engine a kerekítését VarIsNumeric-cal őrizte, és a VarIsNumeric egy varBoolean Variantra is True-t ad. Annak a Variantnak a Double(V)-vel való konvertálása -1-et ad, mert egy COM-stílusú Boolean True -1-ként tárolódik. Egy =A1>0 szerű képlet egy 0.00 formátumú cellában ezért -1 számként jött ki az újraszámolásból. v2.384.57 óta a Boolean eredmények minden numerikus teszt előtt kiszűrődnek, és egy logikai eredmény logikai eredmény marad mindkét engineben

Az elapsed-time formátumok színként olvasódtak (v2.384.9)

A harmadik hiba a számformátum-modellben ült, nem a kerekítésben. A parser minden zárójelezett tokent, ami nem feltétel volt, színként osztályozott, így a [h], [mm] és [ss] soha nem jelölte meg a szakaszát dátum/időként. A megjelenítés nem érintett, mert a formázás külön úton fut, de a precision as displayed arra a flagre épít, hogy kihagyja az időértékeket. Egy öt másodperces időtartam egy nap 5/86400-ad része, nagyjából 0.0000579, és egy [ss].00 szerű formátum rendes két tizedes számnak nézett ki, így kikapcsolt FullPrecision-nel az időtartam 0.00 napra kerekült. v2.384.9 óta egyetlen h, m vagy s betű zárójeles futása elapsed-time tokenként parzolódik, és a szakasz dátum/időként kezelődik. Ugyanez a kiadás javította a perc felismerését a h:mm-ben, ahol a tokenek közti kettőspont korábban elrejtette az órát a parser elől

Precision as displayed bekapcsolása HotXLS-ben Delphiből

Hogy Excel-ekvivalens tárolt értékeket kapj, állítsd be a flaget még azelőtt az újraszámolás előtt, aminek tiszteletben kell tartania, aztán olvasd a cache-elt eredményeket vagy ments. Az XLSX enginen a FullPrecision sima flag: megváltoztatása nem érvényteleníti azokat az eredményeket, amiket egy korábbi Recalculate már tárolt, tehát állítsd be közvetlenül a Create vagy Open után, az első Recalculate előtt. A példa képleteket használ, mert ott alkalmazza a HotXLS a kerekítést:

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%-ot mutat
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // 3-at mutat
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // 12.3-at mutat (ezres)

    // Az XLSX enginen az első Recalculate előtt kell beállítani
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // A cache-elt eredmények most egyeznek az Excel 16-tal: 0.123, 3 és 12300.
    // Az A oszlop konstansai megtartják a teljes pontosságukat.
    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"/>-t ír
  finally
    Wb.Free;
  end;
end;

A Classic engine ugyanúgy viselkedik, egy kényelmi funkcióval: a TXLSWorkbook.UseFullPrecision hozzárendelése a függőséggráf minden képletét dirtyként jelöli, így a következő Recalculate az egész munkafüzetet újraértékeli az új szabály alatt. Egy NumberFormat megváltoztatása bekapcsolt opció mellett szintén dirtyként jelöli az érintett képletcellákat, mert a formátum mostantól a tárolt értéket dönti el. Ne feledd, hogy a Classic Recalculate azoknak a képletcelláknak a számát adja vissza, amiket nem tudott kiértékelni, tehát a nulla a siker:

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; // minden képletet dirtyként jelöl
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: a negatív szakasz, a "(0.0)", egy tizedest mutat
    // A C1 Boolean True marad (v2.384.57 előtti buildek -1-et tároltak)
    Wb.SaveAs('report.xls'); // CalcPrecision rekord fFullPrec = 0-mal
  finally
    Wb.Free;
  end;
end;

Mindkét engine tiszteletben tartja azt a flaget is, ami egy fájllal érkezik. Nyiss meg egy bekapcsolt opcióval mentett munkafüzetet, és a FullPrecision vagy UseFullPrecision már False, így a betöltés utáni Recalculate pontosan úgy kerekít, ahogy az Excel tenné. Ha csak azokat a számokat kell kiolvasnod, amiket az Excel már tárolt, teljesen kihagyhatod az újraszámolást, ahogy a cache-elt képletértékek olvasása újraszámolás nélkül írja. Hogy a sorszámok és dátumformátumok hogyan játszanak össze a dátum/idő tesztet vezérlő formátummodellel, lásd a Excel dátumsorszámok, az 1904-es rendszer és numFmt Delphiben cikket

Mikor kapcsoljad be a precision as displayed-et, és mikor ne?

A precision as displayed-et csak akkor kapcsold be, ha a munkafüzet tárolt számainak meg kell egyezniük a megjelenített számaival, és elfogadod, hogy a plusz számjegyek örökre elvesznek. A klasszikus jogos eset egy pénzügyi ütemterv, ahol a kerekített összegek oszlopainak a képernyőn kerekített összegre kell feladniuk, rejtett centtörtek nélkül, amik utolsó helyen eggyel tévedő összeset adnak. Az másik jó ok egy ügyfél meglévő munkafüzetének egyeztetése, amiben az opció már be van állítva, és a HotXLS körutazáskor megőrzi a flaget, hogy ne kapcsold vissza csendben teljes pontosságra

Kerüld a legtöbb más helyzetben:

• Mérnöki és tudományos adat. Egy mérés kerekítése azért, mert valaki kéttizedes formátumot választott egy riportnak, olyan információt tesz tönkre, amit későbbi formátumváltás már nem állít vissza
• Százalékok durva formátumokkal. Egy 0% formátum a tárolt arányból csak két tizedest tart, így a 0.1234 0.12 lesz, és minden lefelé irányuló képlet, ami a cellát olvassa, 0.12-vel dolgozik
• Skálázott megjelenítések. Egy ezresek megjelenítésére használt 0, vagy 0.0, formátum a tárolt értéket ezresre vagy százasra kerekíti, ami ritkán az, amit a formátumot választó ember szándékolt
• Megosztott sablonok. A flag munkafüzet-szintű. Bárki, aki később sheetet ad hozzá, örökli a viselkedést, jellemzően anélkül, hogy tudná, hogy be van kapcsolva

Ha az igazán akart dolog néhány konkrét cellában kerekített eredmény, írd azokba a képletekbe a ROUND-ot. A ROUND explicit, a cellára korlátozott, mindenkinek látható, aki olvassa a képletet, és a HotXLS képletengine bármely más függvényhez hasonlóan értékeli, munkafüzet-szintű mellékhatások nélkül

Precision as displayed gyorsreferencia

• Fájlflag: CalcPrecision $000E fFullPrec = 0-mal BIFF8-ban ([MS-XLS] §2.4.35), calcPr fullPrecision="0" XLSX-ben (ECMA-376 Part 1)
• HotXLS kapcsolók: TXLSXWorkbook.FullPrecision := False és TXLSWorkbook.UseFullPrecision := False, mindkettő alapértéke True
• Szakasz: a kiszámolt érték előjele szerint választva; harmadik szakasz csak pontosan nullára
• Számjegyek: tizedeshelyőrzők, plusz kettő %-onként, mínusz három skálázó vesszőnként; a szám lehet negatív
• Kerekítés: nullától félre néhány ulp toleranciával, így a 2.5 3-at ad, a -2.5 -3-at és az 1.005 1.01-et
• Kihagyva: General, dátum/idő és elapsed time, tudományos, tört, szöveg, Boolean és hibaértékek
• Hatály HotXLS-ben: képleteredmények ahogy kiszámolódnak; a konstansok a hozzárendelésük szerint tárolódnak
• XLSX engine: állítsd a FullPrecision-t az első Recalculate előtt; a Classic setter maga jelöli újra dirtynek az összes képletet
• Verziók: Excel 16-hoz igazítva mindkét engineben v2.384.57 óta; elapsed-time formátumok védve v2.384.9 óta

A HotXLS XLS és XLSX munkafüzeteket olvas, ír és számol natívan Delphiből és C++Builderből, az itt tárgyalt munkafüzet-számítási opciókkal együtt. Részletek, kiadások és a próbaverzió letöltése a HotXLS Delphi spreadsheet component oldalon