LlosLab Software Development BlogInženjerske bilješke za programere PDF-a, Delphija i softvera za dokumente.

Tehnički članak

HotXLS precision as displayed: Excel pravila zaokruživanja

HotXLS Delphi Component

Excel precision as displayed zaokružuje svaki spremljeni broj na decimale koje njegov brojčani format prikazuje: sekcija formata koja odgovara predznaku vrijednosti, dvije dodatne decimale po %, tri manje po skalirajućem zarezu za tisućice, zaokruživanje polovice od nule. HotXLS primjenjuje isto pravilo u oba svoja Delphi motora kad je TXLSXWorkbook.FullPrecision ili TXLSWorkbook.UseFullPrecision False. Zvuči kao jedan redak koda sve dok kupac ne prijavi da se vaši izvezeni zbroji računa razilaze s Excelom za cent, ili da je stupac trajanja u [ss].00 slegnuo u nulu. Oboje se dogodilo, i oboje vodi do jednog od tih pravila koje je netko krivo primijenio. Od v2.384.57 oba motora dijele jednu implementaciju čije su očekivane vrijednosti izmjerene u Excelu 16 s uključenim Workbook.PrecisionAsDisplayed

Što precision as displayed stvarno mijenja u radnoj knjizi?

Precision as displayed jedna je zastavica na razini radne knjige koja motoru izračuna govori da brojeve sprema onako kako izgledaju, a ne onako kako su izračunati. U Excel sučelju stoji pod File, Options, Advanced, "When calculating this workbook", kao "Set precision as displayed". Na disku to je jedan bit. BIFF8 datoteka nosi je u CalcPrecision zapisu ($000E, [MS-XLS] §2.4.35), čije je polje fFullPrec 1 za normalnu punu preciznost, a 0 kad je opcija uključena. XLSX paket nosi je kao atribut fullPrecision elementa calcPr u workbook.xml, definiran u ECMA-376 Part 1, gdje je zadano true, a fullPrecision="0" uključuje zaokruživanje

Zastavica nije preferenca prikaza. Kad označite kućicu, Excel upozorava da će podaci trajno izgubiti na točnosti, i misli to ozbiljno: vrijednosti se prerešu u svoju prikazanu preciznost, a znamenke koje su odrezane nestaju. Kasnije brisanje kućice ne vraća stare znamenke. 0.1234 prikazan kao 12.3% postaje 0.123, zauvijek

HotXLS zastavicu čita i piše u oba formata i izlaže je u oba motora:

  • TXLSXWorkbook.FullPrecision: Boolean na XLSX motoru, učitava se iz i sprema u calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean na klasičnom motoru (također na IXLSWorkbook), učitava se iz i sprema u CalcPrecision zapis
  • Oba su po zadanom True, što je siguran, nedestruktivan način rada i Excelova zadana postavka

Gdje HotXLS primjenjuje zaokruživanje bitno je. HotXLS zaokružuje u trenutku kad računa vrijednost: svaki rezultat formule zaokružuje se na svoju prikazanu preciznost prije nego se spremi kao predmemorirana vrijednost ćelije, tijekom Recalculate i tijekom evaluacije na zahtjev. Konstante koje dodijelite kroz Value spremaju se točno onakve kakve ste ih dali. Ako izlaz mora reproducirati ono što Excel sprema nakon označene kućice, te konstante zaokružite sami prije pisanja, na primjer pomoću funkcije prikazane kasnije

Kako Excel odlučuje koliko će decimala zadržati?

Excel broj zadržanih decimala izvodi iz konkretne sekcije formata koja prikazuje vrijednost, a ne iz cijelog formata kao cjeline. Pravila u nastavku izmjerena su u Excelu 16 i to je ono što XlsApplyDisplayedPrecision u lxNumFormat implementira za oba HotXLS motora

  1. Sekcija se bira po predznaku. Format s dvije sekcije koristi drugu sekciju za negativne vrijednosti. Format s tri ili više sekcija koristi drugu za negativne vrijednosti, a treću za točno nulu. Sve ostalo koristi prvu sekciju
  2. Brojite decimalne placeholdere. Svaki 0, # ili ? iza decimalne točke u toj sekciji dodaje jednu zadržanu decimalu
  3. Dodajte dvije po znaku posto. 0.0% prikazuje 0.1234 kao 12.3%, pa je spremljena vrijednost stoti dio onoga što vidite i zadržava tri decimale, ne jednu
  4. Oduzmite tri po skalirajućem zarezu. Zarez iza posljednjeg placeholdera cijelog dijela (0,, 0.0,, 0,.0) dijeli prikaz sa 1000. 0.0, prikazuje 12345.678 kao 12.3, pa Excel zadržava jedna decimala minus tri, što je negativan broj: vrijednost se zaokružuje na stotice i sprema kao 12300. Zarez između placeholdera cijelog dijela, kao u #,##0, obično je grupiranje znamenki i ne mijenja ništa
  5. Nenumeričke sekcije ostavite na miru. General, sekcije datuma i vremena (uključujući proteklo vrijeme [h], [mm] i [ss]), znanstvene, razlomačke i tekstovne sekcije, te sekcije bez ijednog placeholdera znamenki zadržavaju punu preciznost
HotXLS dijagram pravila prikazane preciznosti: odaberite sekciju formata po predznaku vrijednosti, prebrojite placeholdere znamenki iza decimalne točke, dodajte dvije decimale po znaku posto, oduzmite tri po skalirajućem zarezu za tisućice pa broj može ići u negativno, preskočite u potpunosti General i sekcije datuma i vremena, pa zaokružite polovicu od nule
Broj znamenki dolazi iz sekcije koja odgovara predznaku, plus dvije po postu i minus tri po skalirajućem zarezu, a negativan broj zaokružuje na desetice ili stotice; General i sekcije datuma ostaju netaknuti

Izmjereno protiv Excela 16, ovo su vrijednosti koje oba HotXLS motora sada spremaju za rezultat formule u svakom formatu:

Brojčani formatIzračunata vrijednostSpremljena vrijednostPravilo koje se primjenjuje
0.0%0.12340.123Jedna decimala plus dvije za znak posto
02.53Polovica od nule, ne na parno
0-2.5-3Polovica od nule i na negativnoj strani
0.00;(0.0)-1.2345-1.2Negativna sekcija pokazuje jednu decimalu
0.00;(0.0)1.23451.23Pozitivna sekcija pokazuje dvije decimale
#,##0.01234.56781234.6Zarez za grupiranje, bez skaliranja
0.0,12345.67812300Jedna decimala minus tri: zaokruživanje na stotice
0.0%;(0.00%)-0.0125-0.0125Negativna sekcija zadržava dvije plus dvije decimale
0.001.0051.01Tolerancija na grešku binarne reprezentacije
0;-0;0.00.51Nije nula, pa odlučuje pozitivna sekcija

Zadnji red lijepa je zamka. Vrijednost 0.5 zaokružuje se na cijeli broj, a sekcija za nulu nikad ne dolazi na red jer Excel sekciju bira prema izračunatoj vrijednosti prije zaokruživanja. Jedno pošteno ograničenje sa strane HotXLS-a: sekcije se biraju samo po predznaku, pa se format čije sekcije nose vlastite uvjete u zagradama poput [>=1000] i dalje dijeli po predznaku. Takve formate provjerite protiv Excela ako su vam važni

Zašto se 1.005 zaokružuje na 1.01, a ne na 1.00?

Excel 1.005 u ćeliji formata 0.00 zaokružuje na 1.01 iako je double najbliži 1.005 malo ispod točke na pola puta, a HotXLS to poklapa tolerancijom od par ulp. Literal 1.005 ne može se prikazati u binarnom pomičnom zarezu. Najbliži IEEE 754 double jest 1.00499999999999989341858963598497211933135986328125, a množenje sa 100 daje 100.49999999999999. Udžbenički Floor(x * 100 + 0.5) / 100 zato vraća 1.00, što se ne slaže ni s brojem koji je korisnik upisao, ni s onim što Excel prikazuje, ni s onim što Excel sprema

Delphi dodaje svoju crtenu. System.Round polovice zaokružuje na parno, pa je Round(2.5) 2, a Round(3.5) 4. To je bankarsko zaokruživanje, razuman zadatak za statistiku, ali ovdje pogrešno pravilo: Excel za 2.5 u ćeliji formata 0 sprema 3, a za -2.5 sprema -3. HotXLS implementacija radi nad apsolutnom vrijednošću, dodaje 0.5 plus relativnu toleranciju od 2-51 puta skalirana vrijednost (par ulp na toj magnitudi, nikad manje od dva ulp od 1.0), odreže, vrati skaliranje i obnovi predznak. Sljedeća funkcija samostalna je ilustracija tog principa, nije sama bibliotečka kod, i negativnim brojevima znamenki za skalirajuće zareze rukuje na isti način:

HotXLS dijagram zaokruživanja: 2.5 se zaokružuje polovicom od nule na 3, a -2.5 na -3, dok Delphi System.Round daje bankarske odgovore 2 i -2, a budući da je double najbliži 1.005 tik ispod točke na pola puta, tolerancija od par ulp jest ono što floor temeljeni 1.00 pretvara u Excelov odgovor 1.01
Excel polovice zaokružuje od nule i oprašta grešku binarne reprezentacije malom tolerancijom; oba detalja mjera su, i ako bilo koji preskočite, spremate 2 za 2.5 ili 1.00 za 1.005, cent od Excela
// Skica principa: zaokruži polovicu od nule na ADigits decimala,
// s tolerancijom od par ulp da 1.005 dođe do 1.01.
// ADigits < 0 zaokružuje na desetice, stotice, ... ("0.0," daje -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; // izvan double preciznosti: ostavi vrijednost na 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 dalo overflow
    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 nule, 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   (preko 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 znamenke)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 znamenke)

Tolerancija je namjeran kompromis. Vrijednost koja je stvarno dva ulp ispod pola koraka također se zaokružuje gore, ali na tom se rastojanju razlika ne razlikuje od greške reprezentacije, a tretiranje kao pola koraka jest ono što upisane decimale tjera da se ponašaju onako kako korisnici očekuju

Što je bilo krivo prije v2.384.57?

Prije v2.384.57 XLSX motor i klasični motor svaki je imao svoj vlastiti precision-as-displayed kod, i svaki je bio kriv na svoj način. Ako proizvodite radne knjige s uključenom opcijom, ovo su simptomi koje treba tražiti u datotekama generiranim starijim buildovima

XLSX motor: samo prva sekcija, bez posto, bankarsko zaokruživanje

Stari XLSX put tražio je broj decimala cijelog formata kao cjeline, što je gledalo samo prvu sekciju i ignoriralo %, a zatim zaokruživalo s Round. 0.1234 u 0.0% spremalo se kao 0.1, što je 10% umjesto 12.3% na ekranu. 2.5 u 0 spremalo se kao 2 umjesto 3. Negativne vrijednosti u formatu poput 0.00;(0.0) zaokruživale su se na dvije decimale pozitivne sekcije. Od v2.384.57 XLSX motor poziva istu dijeljenu rutinu kao klasični motor, koji je u tom izdanju dobio i podršku za skalirajući zarez

Klasični motor: TRUE je postalo -1

Klasični motor je svoje zaokruživanje čuvao s VarIsNumeric, a VarIsNumeric vraća True za varBoolean Variant. Pretvorba tog Varianta s Double(V) daje -1, jer je COM-stilski logički True spremljen kao -1. Formula poput =A1>0 u ćeliji formatiranoj s 0.00 izašla je iz preračunavanja kao broj -1. Od v2.384.57 logički rezultati isključeni su prije bilo kojeg numeričkog testa, i logički rezultat ostaje logički rezultat u oba motora

Formati proteklog vremena čitani kao boje (v2.384.9)

Treći bug sjedio je u modelu brojčanog formata, a ne u zaokruživanju. Parser je svaki token u uglatim zagradama koji nije bio uvjet klasificirao kao boju, pa [h], [mm] i [ss] nikad nisu označili svoju sekciju kao datum/vrijeme. Prikaz nije bio pogođen jer formatiranje ide na zasebnom putu, ali precision as displayed oslanja se na tu zastavicu da preskoči vremenske vrijednosti. Trajanje od pet sekundi jest 5/86400 dana, otprilike 0.0000579, a format poput [ss].00 izgledao je kao običan dvodecimalni broj, pa je uz isključen FullPrecision trajanje bilo zaokruženo na 0.00 dana. Od v2.384.9 niz u uglatim zagradama od jednog slova h, m ili s parsira se kao token proteklog vremena i sekcija se tretira kao datum/vrijeme. Isto izdanje popravilo je detekciju minuta u h:mm, gdje je dvotočka između tokena skrivala sat od parsera

HotXLS dijagram krivog parsiranja proteklog vremena: pet sekundi spremljeno kao sićušni dio dana u ćeliji formatiranoj tokenom ss u uglatim zagradama, koji je stari parser čitao kao boju i označavao kao običan dvodecimalni broj, pa je precision as displayed zaokruživao trajanje na 0.00 dok nije parsirano kao sekcija proteklog vremena
Formatiranje išlo je svojim putem, pa je ćelija izgledala ispravno dok se spremljena vrijednost zaokruživala u nulu; slovo h, m ili s u uglatim zagradama token je proteklog vremena, ne boje, i sekcija zadržava punu preciznost

Uključivanje precision as displayed u HotXLS-u iz Delphija

Da dobijete spremljene vrijednosti jednake Excelovima, postavite zastavicu prije preračunavanja koje je treba poštovati, a zatim pročitajte predmemorirane rezultate ili spremite. Na XLSX motoru FullPrecision je obična zastavica: njezina promjena ne poništava rezultate koje je raniji Recalculate već spremio, pa je postavite odmah nakon Create ili Open i prije prvog Recalculate. Primjer koristi formule jer je to mjesto gdje HotXLS primjenjuje zaokruživanje:

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

    // Mora se postaviti prije prvog Recalculate na XLSX motoru
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Predmemorirani rezultati sada odgovaraju Excelu 16: 0.123, 3 i 12300.
    // Konstante u stupcu A zadržavaju punu preciznost.
    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;

Klasični motor ponaša se isto, uz jednu pogodnost: dodjela TXLSWorkbook.UseFullPrecision svaku formulu u grafu ovisnosti označava nevažećom, pa sljedeći Recalculate preračunava cijelu radnu knjigu pod novim pravilom. Promjena NumberFormat dok je opcija uključena također označava pogođene ćelije formule nevažećima, jer format sada odlučuje o spremljenoj vrijednosti. Obratite pozornost da klasični Recalculate vraća broj ćelija formule koje nije mogao evaluirati, pa nula znači uspjeh:

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čava sve formule nevažećima
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: negativna sekcija "(0.0)" pokazuje jednu decimalu
    // C1 ostaje logički True (buildovi prije v2.384.57 spremali su -1)
    Wb.SaveAs('report.xls'); // CalcPrecision zapis s fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Oba motora poštuju i zastavicu koja dolazi s datotekom. Otvorite radnu knjigu spremljenu s uključenom opcijom i FullPrecision odnosno UseFullPrecision već je False, pa Recalculate nakon učitavanja zaokružuje točno onako kako bi Excel. Ako samo trebate pročitati brojeve koje je Excel već spremio, preračunavanje možete sasvim preskočiti, kako je opisano u čitanju predmemoriranih vrijednosti formula bez preračunavanja. Za to kako serijski brojevi i formati datuma komuniciraju s modelom formata koji pokreće provjeru datuma/vremena, pogledajte Excel serijske datume, 1904 sustav i numFmt u Delphiju

Kada uključiti precision as displayed, a kada ne?

Precision as displayed uključite samo kad spremljeni brojevi radne knjige moraju biti jednaki njezinim prikazanim brojevima i prihvaćate da dodatne znamenke zauvijek izgubite. Klasičan legitimni slučaj jest financijski raspored u kojem se stupci zaokruženih iznosa moraju zbrojiti u zaokruženi ukupni iznos na ekranu, bez skrivenih dijelova centa koji proizvode ukupni iznos pogrešan za jedan na zadnjem mjestu. Poklapanje s postojećom radnom knjigom kupca koja već ima postavljenu opciju drugi je dobar razlog, a HotXLS zastavicu čuva kroz povratni ciklus pa ih nećete tiho vratiti na punu preciznost

U većini ostalih situacija izbjegavajte ga:

  • Inženjerski i znanstveni podaci. Zaokruživanje mjerenja jer je netko za izvještaj odabrao format s dvije decimale uništava informaciju koju kasnija promjena formata ne može vratiti
  • Postoci s grubi formati. Format 0% zadržava samo dvije decimale spremljenog omjera, pa 0.1234 postaje 0.12, i svaka nizvodna formula koja čita ćeliju radi s 0.12
  • Skalirani prikazi. Format 0, ili 0.0, korišten za prikaz tisućica zaokružuje spremljenu vrijednost na tisućice ili stotice, što rijetko jest ono što je osoba koja je odabrala format htjela
  • Dijeljeni predlošci. Zastavica vrijedi na razini radne knjige. Svatko tko kasnije doda list nasljeđuje ponašanje, obično ne znajući da je uključena

Ako ono što stvarno želite jesu zaokruženi rezultati u nekoliko konkretnih ćelija, u te formule umjesto toga upišite ROUND. ROUND je eksplicitan, stvar ćelije, vidljiv svakomu tko čita formulu, i evaluira ga HotXLS motor formula kao i svaku drugu funkciju, bez nuspojava na razini radne knjige

Precision as displayed brza referenca

  • Zastavica u datoteci: CalcPrecision $000E s fFullPrec = 0 u BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" u XLSX-u (ECMA-376 Part 1)
  • HotXLS prekidači: TXLSXWorkbook.FullPrecision := False i TXLSWorkbook.UseFullPrecision := False, oba po zadanom True
  • Sekcija: bira se po predznaku izračunate vrijednosti; treća sekcija samo za točno nulu
  • Znamenke: decimalni placeholderi, plus dvije po %, minus tri po skalirajućem zarezu; broj može biti negativan
  • Zaokruživanje: polovica od nule s tolerancijom od par ulp, pa 2.5 daje 3, -2.5 daje -3, a 1.005 daje 1.01
  • Preskače se: General, datum/vrijeme i proteklo vrijeme, znanstveni, razlomak, tekst, logičke vrijednosti i vrijednosti grešaka
  • Doseg u HotXLS-u: rezultati formula u trenutku izračuna; konstante se spremaju onakve kakve su dodijeljene
  • XLSX motor: postavite FullPrecision prije prvog Recalculate; klasični setter sve formule sam ponovno označava nevažećima
  • Verzije: usklađeno s Excelom 16 u oba motora od v2.384.57; formati proteklog vremena zaštićeni od v2.384.9

HotXLS čita, piše i računa XLS i XLSX radne knjige nativno iz Delphija i C++Buildera, uključujući opcije izračuna radne knjige pokrivene ovdje. Detalji, izdanja i probno preuzimanje na stranici HotXLS Delphi proračunske komponente