LlosLab Software Development BlogInženirske opombe za razvijalce programske opreme PDF, Delphi in dokumentov.

Tehnični članak

HotXLS natančnost kot prikazano: pravila zaokroževanja Excel

HotXLS Delphi Component

Excel natančnost kot prikazano zaokroži vsako shranjeno število na decimalna mesta, ki jih njegov številski format prikazuje: odsek formata, ki se ujema z znakom vrednosti, dva dodatna decimalna mesta na vsak %, tri manj na vsako vejico za skaliranje tisočic, zaokroževanje polovice proč od ničle. HotXLS isto pravilo uporabi v obeh svojih pogonih Delphi, kadar je TXLSXWorkbook.FullPrecision ali TXLSWorkbook.UseFullPrecision False. To zveni kot enovrstičnica, dokler stranka ne poroča, da se vaše izvožene vsote računov razlikujejo od Excela za cent, ali da je stolpec trajanj v [ss].00 sesedel v nič. Oboje se je zgodilo in oboje vodi nazaj do enega od teh pravil, zgrešenega. Od v2.384.57 si oba pogona delita eno samo implementacijo, katere pričakovane vrednosti so bile izmerjene v Excelu 16 z vključenim Workbook.PrecisionAsDisplayed

Kaj natančnost kot prikazano sploh spremeni v delovnem zvezku?

Natančnost kot prikazano je ena sama zastavica na ravni delovnega zvezka, ki računskemu pogonu pove, naj števila shrani tako, kot izgledajo, ne tako, kot so bila izračunana. V uporabniškem vmesniku Excel stoji pod Datoteka, Možnosti, Napredno, »Ob izračunu tega delovnega zvezka«, kot »Nastavi natančnost kot prikazano«. Na disku je en bit. Datoteka BIFF8 jo nosi v zapisu CalcPrecision ($000E, [MS-XLS] §2.4.35), katerega polje fFullPrec je 1 za običajno polno natančnost in 0, kadar je možnost vklopljena. Paket XLSX jo nosi kot atribut fullPrecision elementa calcPr v workbook.xml, definiran v ECMA-376, 1. del, kjer je privzeto true in fullPrecision="0" vklopi zaokroževanje

Zastavica ni nastavitev prikaza. Ko potrdite polje, Excel opozori, da bodo podatki trajno izgubili natančnost, in misli resno: vrednosti so prepisane na svojo prikazano natančnost, odrezane števke pa so izgubljene. Kasnejše odstranjevanje kljukice starih števk ne vrne. 0.1234, prikazan kot 12.3%, postane za zmeraj 0.123

HotXLS zastavico v obeh formatih bere in zapisuje ter jo izpostavi v obeh pogonih:

  • TXLSXWorkbook.FullPrecision: Boolean na pogonu XLSX, naloženo iz calcPr/@fullPrecision in vanj shranjeno
  • TXLSWorkbook.UseFullPrecision: Boolean na klasičnem pogonu (tudi na IXLSWorkbook), naloženo iz zapisa CalcPrecision in vanj shranjeno
  • Oboje privzeto True, kar je varen, nedestruktiven način in privzeto Excela

Kje HotXLS uporabi zaokroževanje, je pomembno. HotXLS zaokroži na točki, kjer izračuna vrednost: vsak rezultat formule se zaokroži na svojo prikazano natančnost, preden se shrani kot predpomnjena vrednost celice, med Recalculate in med vrednotenjem na zahtevo. Konstante, ki jih dodelite prek Value, se shranijo točno tako, kot so dane. Če mora vaš izhod ponoviti tisto, kar Excel shrani po potrditvi polja, zaokrožite te konstante sami, preden jih zapišete, na primer s pomožnikom, prikazanim pozneje

Kako Excel odloči, koliko decimalnih mest obdrži?

Excel izpelje število obdržanih decimalnih mest iz tistega konkretnega odseka formata, ki prikazuje vrednost, ne iz celotnega niza formata. Spodnja pravila so bila izmerjena v Excelu 16 in jih XlsApplyDisplayedPrecision v lxNumFormat implementira za oba pogona HotXLS

  1. Izberite odsek po znaku. Dvo-odsečni format uporabi drugi odsek za negativne vrednosti. Format s tremi ali več odseki uporabi drugega za negativne vrednosti in tretjega točno za nič. Vse ostalo uporabi prvi odsek
  2. Preštejte vnosna mesta decimalk. Vsak 0, # ali ? za decimalno piko v tem odseku doda eno obdržano decimalno mesto
  3. Dodajte dva na znak odstotka. 0.0% pokaže 0.1234 kot 12.3%, shranjena vrednost je torej stotinka tega, kar vidite, in obdrži tri decimalna mesta, ne enega
  4. Odštejte tri na skalirajočo vejico. Vejica za zadnjim celoštevilčnim vnosnim mestom (0,, 0.0,, 0,.0) deli prikaz s 1000. 0.0, pokaže 12345.678 kot 12.3, Excel torej obdrži eno decimalno mesto minus tri, kar je negativen zalogov: vrednost se zaokroži na stotice in shrani kot 12300. Vejica med celoštevilčnimi vnosnimi mesti, kot v #,##0, je navadno grupiranje števk in ne spremeni nič
  5. Nestevilskim odsekom pustite pri miru. General, odseki datuma in časa (vključno s pretečenim [h], [mm] in [ss]), znanstveni, ulomkovni in besedilni odseki ter odseki brez vsakršnega vnosnega mesta števk obdržijo polno natančnost
HotXLS diagram pravil prikazane natančnosti: izberite odsek formata po znaku vrednosti, preštejte vnosna mest števk za decimalno piko, dodajte dve decimalni mesti na znak odstotka, odštejte tri na vejico skaliranja tisočic, tako da lahko zalogov postane negativen, preskočite General ter odseke datuma in časa v celoti, nato pa zaokrožite polovico proč od ničle
Število števk pride iz odseka, ki se ujema z znakom, plus dva na odstotek in minus tri na skalirajočo vejico, negativen zalogov pa zaokroži na desetice ali stotice; General in odseki datumov ostanejo pri miru

Izmerjeno proti Excelu 16 so to vrednosti, ki jih oba pogona HotXLS zdaj shrani za rezultat formule v vsakem formatu:

Številski formatIzračunana vrednostShranjena vrednostPravilo, ki velja
0.0%0.12340.123Eno decimalno mesto plus dva za znak odstotka
02.53Polovica proč od ničle, ne na sodo
0-2.5-3Polovica proč od ničle tudi na negativni strani
0.00;(0.0)-1.2345-1.2Negativni odsek pokaže eno decimalno mesto
0.00;(0.0)1.23451.23Pozitivni odsek pokaže dve decimalni mesti
#,##0.01234.56781234.6Grupirajoča vejica, brez skaliranja
0.0,12345.67812300Eno decimalno mesto minus tri: zaokroži na stotice
0.0%;(0.00%)-0.0125-0.0125Negativni odsek obdrži dve plus dve decimalni mesti
0.001.0051.01Toleranca za napako dvojiške predstavitve
0;-0;0.00.51Ni nič, zato odloči pozitivni odsek

Zadnja vrstica je prijetna past. Vrednost 0.5 se zaokroži na celo število, odsek za nič pa nikoli ne pride na vrsto, ker Excel izbere odsek iz izračunane vrednosti, preden zaokroži. Ena iskrena omejitev na strani HotXLS: odseki so izbrani samo po znaku, zato je format, katerega odseki nosijo pogoje po meri v oglatih oklepajih, kot je [>=1000], še vedno razdeljen po znaku. Take formate, če so vam pomembni, preverite proti Excelu

Zakaj se 1.005 zaokroži na 1.01 in ne na 1.00?

Excel 1.005 v celici 0.00 zaokroži na 1.01, čeprav je dvojno število, najbližje 1.005, rahlo pod srednjo točko, HotXLS pa se mu prilagodi s toleranco nekaj ulp. Literal 1.005 se v dvojiškem plavajočem zapisu ne da predstaviti. Najbližje dvojno število IEEE 754 je 1.00499999999999989341858963598497211933135986328125, množenje s 100 pa da 100.49999999999999. Učbeniški Floor(x * 100 + 0.5) / 100 zato vrne 1.00, kar se ne ujema ne s številom, ki ga je uporabnik vtipikal, ne s tem, kar Excel pokaže, ne s tem, kar Excel shrani

Delphi doda svoj poseben pridih. System.Round izenačenja zaokroži na sodo, zato je Round(2.5) enako 2 in Round(3.5) enako 4. To je bančno zaokroževanje — smiselna privzetost za statistiko, tukaj pa napačno pravilo: Excel za 2.5 v celici 0 shrani 3, za -2.5 pa -3. Implementacija HotXLS dela na absolutni vrednosti, prišteje 0.5 plus relativno toleranco 2-51 krat skalirana vrednost (nekaj ulp pri tej velikosti, nikoli manj kot dva ulp od 1.0), odreže, skalira nazaj in povrne znak. Naslednja funkcija je samozadostna ilustracija tega načela, ne koda knjižnice, in negativna števila mest za skalirajoče vejice obravnava na enak način:

HotXLS diagram zaokroževanja: 2.5 se zaokroži polovico proč od ničle na 3, -2.5 pa na -3, kjer Delphi System.Round da bančna odgovora 2 in -2, in ker je najbližje dvojno število 1.005 tik pod srednjo točko, je toleranca nekaj ulp tisto, ki pretvori na floor osnovano 1.00 v Excelov odgovor 1.01
Excel izenačenja zaokroži proč od ničle in z malo toleranco odpusti napako dvojiške predstavitve; oboje je merljivo, izpuščen katerikoli podatek pa shrani 2 za 2.5 ali 1.00 za 1.005, en cent od Excela
// Načelna skica: zaokroži polovico proč od ničle na ADigits decimalk,
// s toleranco nekaj ulp, da 1.005 doseže 1.01.
// ADigits < 0 zaokroži na desetice, stotice, ... ("0.0," da -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; // čez dvojno natančnost: pusti vrednost pri miru
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // skaliranje bi prekoračilo
    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 proč od ničle, 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   (osnovano na 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 mesta)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 mesta)

Toleranca je namerna izmenjava kompromisov. Vrednost, ki je resnično dva ulp pod polovičnim korakom, se prav tako zaokroži navzgor, a na tej razdalji razlike od napake predstavitve ni mogoče razločiti, obravnava kot polovični korak pa je tisto, kar vtipikane decimalke spravi v obnašanje, ki ga uporabniki pričakujejo

Kaj je šlo narobe pred v2.384.57?

Pred v2.384.57 sta imela pogon XLSX in klasični pogon vsak svojo kodo za natančnost kot prikazano, vsak pa je bil narobe na svoj način. Če izdelujete delovne zvezke z vključeno možnostjo, so to simptomi, na katere bodite pozorni v datotekah starejših gradnjah

Pogon XLSX: samo prvi odsek, brez odstotkov, bančno zaokroževanje

Stara pot XLSX je zahtevala število decimalnih mest celotnega niza formata, kar je gledalo samo prvi odsek in ignoriralo %, nato pa zaokrožilo z Round. 0.1234 v 0.0% se je shranil kot 0.1, kar je 10% namesto 12.3% na zaslonu. 2.5 v 0 se je shranil kot 2 namesto 3. Negativne vrednosti v formatu, kot je 0.00;(0.0), so se zaokrožile na dve decimalni mesti pozitivnega odseka. Od v2.384.57 pogon XLSX kliče isto skupno rutino kot klasični pogon, ta pa je v tej izdaji dobil tudi podporo skalirajočim vejicam

Klasični pogon: TRUE je postal -1

Klasični pogon je svoje zaokroževanje zaščitil z VarIsNumeric, VarIsNumeric pa vrne True tudi za Variant varBoolean. Pretvorba tega Varianta s Double(V) da -1, ker je logični True po stilu COM shranjen kot -1. Formula, kot je =A1>0, v celici, oblikovani z 0.00, je torej iz preračuna prišla kot število -1. Od v2.384.57 so logični rezultati izključeni pred vsakim številskim testom, logični rezultat pa ostane logični rezultat v obeh pogonih

Formati pretečenega časa prebrani kot barve (v2.384.9)

Tretji hrošč je sedel v modelu številskih formatov, ne v zaokroževanju. Razčlenjevalnik je vsak oglatooklepajni žeton, ki ni bil pogoj, uvrstil med barve, zato [h], [mm] in [ss] svojega odseka nikoli niso označili kot datum/čas. Prikaz ni bil prizadet, ker oblikovanje teče po ločeni poti, natančnost kot prikazano pa se zanaša na to zastavico, da vrednosti časa preskoči. Petsekundno trajanje je 5/86400 dneva, približno 0.0000579, format, kot je [ss].00, pa je izgledal kot navadno število z dvema decimalnima mestoma, zato se je trajanje ob izklopljenem FullPrecision zaokrožilo na 0.00 dni. Od v2.384.9 se oglatooklepajni niz ene same črke h, m ali s razčleni kot žeton pretečenega časa, odsek pa se obravnava kot datum/čas. Ista izdaja je popravila zaznavanje minut v h:mm, kjer je dvopičje med žetoni skrivalo uro pred razčlenjevalnikom

HotXLS diagram napačne interpretacije pretečenega časa: pet sekund shranjenih kot drobni ulomek dneva v celici, oblikovani z oglatooklepajnim žetonom ss, ki ga je stari razčlenjevalnik prebral kot barvo in označil kot navadno število z dvema decimalnima mestoma, zato je natančnost kot prikazano zaokrožila trajanje na 0.00, dokler ni bilo razčlenjeno kot odsek pretečenega časa
Oblikovanje je teklo po svoji poti, zato je celica izgledala prav, medtem ko se je shranjena vrednost zaokrožila v nič; oglatooklepajna ena črka h, m ali s je žeton pretečenega časa, ne barva, odsek pa obdrži polno natančnost

Vklop natančnosti kot prikazano v HotXLS iz Delphija

Za shranjene vrednosti, enake Excelovim, nastavite zastavico pred preračunom, ki jo naj upošteva, nato pa preberite predpomnjene rezultate ali shranite. Na pogonu XLSX je FullPrecision navadna zastavica: njena sprememba ne razveljavi rezultatov, ki jih je prejšnji Recalculate že shranil, zato jo nastavite takoj po Create ali Open in pred prvim Recalculate. Primer uporablja formule, ker je to mesto, kjer HotXLS uporabi zaokroževanje:

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%';   // pokaže 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // pokaže 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // pokaže 12.3 (tisočice)

    // Na pogonu XLSX je treba nastaviti pred prvim Recalculate
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Predpomnjeni rezultati se zdaj ujemajo z Excel 16: 0.123, 3 in 12300.
    // Konstante v stolpcu A obdržijo polno natančnost.
    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'); // zapiše <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Klasični pogon se vede enako, z eno priročnostjo: dodelitev TXLSWorkbook.UseFullPrecision označi vsako formulo v grafu odvisnosti kot umazano, zato naslednji Recalculate znova vrednoti celoten delovni zvezek pod novim pravilom. Sprememba NumberFormat, medtem ko je možnost vklopljena, prav tako označi prizadete formulske celice kot umazane, ker format zdaj odloča o shranjeni vrednosti. Upoštevajte, da klasični Recalculate vrne število formulskih celic, ki jih ni mogel vrednotiti, zato ničla pomeni uspeh:

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či vsako formulo kot umazano
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: negativni odsek "(0.0)" pokaže eno decimalno mesto
    // C1 ostane logično True (gradnje pred v2.384.57 so shranile -1)
    Wb.SaveAs('report.xls'); // zapis CalcPrecision s fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Oba pogona spoštujeta tudi zastavico, ki pride z datoteko. Odprite delovni zvezek, shranjen z vključeno možnostjo, in FullPrecision oziroma UseFullPrecision je že False, zato Recalculate po nalaganju zaokroži točno tako, kot bi to storil Excel. Če potrebujete samo branje števil, ki jih je Excel že shranil, lahko preračun v celoti izpustite, kot opisuje branje predpomnjenih vrednosti formul brez preračuna. Kako serijske številke in formati datumov sodelujejo s formatnim modelom, ki poganja preizkus datum/čas, pa je opisano v serijskih številkah datumov Excel, sistemu 1904 in numFmt v Delphiju

Kdaj natančnost kot prikazano vklopiti in kdaj ne?

Natančnost kot prikazano vklopite samo, kadar se morajo shranjena števila delovnega zvezka ujemati s prikazanimi, in kadar sprejmete, da dodatne števke trajno izgubite. Klasičen upravičen primer je finančni razpored, kjer morajo stolpci zaokroženih zneskov dati seštevek do zaokrožene skupne vsote na zaslonu, brez skritih ulomkov centa, ki bi dali vsoto, odmaknjeno za eno na zadnjem mestu. Ujemanje z obstoječim delovnim zvezkom stranke, ki že ima možnost nastavljeno, je drugi dober razlog, HotXLS pa zastavico v krogu ohrani, tako da jih tiho ne vrnete na polno natančnost

V večini drugih situacij se mu izognite:

  • Inženirski in znanstveni podatki. Zaokrožitev meritve zato, ker je nekdo za poročilo izbral format z dvema decimalnima mestoma, uniči informacijo, ki je nobena poznejša sprememba formata ne more povrniti
  • Odstotki z grobimi formati. Format 0% obdrži le dve decimalni mesti shranjenega razmerja, zato 0.1234 postane 0.12, vsaka formula navzdol, ki bere celico, pa dela z 0.12
  • Skalirani prikazi. Format 0, ali 0.0,, uporabljen za prikaz tisočic, zaokroži shranjeno vrednost na tisočice ali stotice, kar redko je to, kar je oseba, ki je format izbrala, namenila
  • Deljeni predloge. Zastavica velja za celoten delovni zvezek. Kdorkoli pozneje doda list, podeduje obnašanje, običajno ne vedoč, da je vklopljeno

Če tisto, kar res želite, so zaokroženi rezultati v nekaj konkretnih celicah, v te formule raje zapišite ROUND. ROUND je izrecen, lokaliziran na celico, viden vsakomur, ki formulo bere, in ga formulski pogon HotXLS vrednoti kot katero koli drugo funkcijo, brez učinkov po vsem delovnem zvezku

Hiter pregled natančnosti kot prikazano

  • Zastavica v datoteki: CalcPrecision $000E s fFullPrec = 0 v BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" v XLSX (ECMA-376, 1. del)
  • Preklopniki HotXLS: TXLSXWorkbook.FullPrecision := False in TXLSWorkbook.UseFullPrecision := False, oboje privzeto True
  • Odsek: izbran po znaku izračunane vrednosti; tretji odsek samo točno za nič
  • Števke: vnosna mesta decimalk, plus dva na %, minus tri na skalirajočo vejico; zalogov je lahko negativen
  • Zaokroževanje: polovica proč od ničle s toleranco nekaj ulp, zato 2.5 da 3, -2.5 da -3, 1.005 pa 1.01
  • Preskočeno: General, datum/čas in pretečen čas, znanstveni, ulomki, besedilo, logične vrednosti in vrednosti napak
  • Obseg v HotXLS: rezultati formul, ko se izračunajo; konstante se shranijo kot dodeljene
  • Pogon XLSX: nastavite FullPrecision pred prvim Recalculate; klasični nastavitelj vse formule sam znova označi kot umazane
  • Različice: ujemanje z Excel 16 v obeh pogonih od v2.384.57; formati pretečenega časa zaščiteni od v2.384.9

HotXLS izvorno bere, zapisuje in preračunava delovne zvezke XLS in XLSX iz Delphija in C++Builderja, vključno z možnostmi izračuna delovnega zvezka, ki so pokrite tukaj. Podrobnosti, izdaje in preizkusni prenos so na strani komponente HotXLS Delphi preglednic