LlosLab Software Development BlogInženjerske beleške za programere PDF-a, Delphi-ja i softvera za dokumente

Tehnički članak

HotXLS preciznost kao prikaz: pravila zaokruživanja Excela

HotXLS Delphi Component

Excel preciznost kao prikazano zaokružuje svaki sačuvani broj na decimale koje njegov format broja pokazuje: sekcija formata koja odgovara predznaku vrednosti, dve decimale više po svakom %, tri manje po hiljadarskoj skalirajućoj zapeti, zaokruživanje na pola od nule. HotXLS primenjuje isto pravilo u oba svoja Delphi engine-a kad je TXLSXWorkbook.FullPrecision ili TXLSWorkbook.UseFullPrecision False. To zvuči kao jednolinijski kod sve dok kupac ne prijavi da se vaši izvezeni zbirovi fakture ne poklapaju s Excelom za cent, ili da se kolona trajanja u [ss].00 srušila na nulu. Oboje se desilo, i oboje se vraćalo na pogrešno jedno od tih pravila. Od v2.384.57 oba engine-a dele jednu implementaciju čije su očekivane vrednosti merene u Excelu 16 s uključenim Workbook.PrecisionAsDisplayed

Šta preciznost kao prikazano zaista menja u radnoj svesci?

Preciznost kao prikazano je jedan flag na nivou radne sveske koji računskom engine-u govori da čuva brojeve onako kako izgledaju, a ne onako kako su izračunati. U Excel UI-u sedi pod File, Options, Advanced, „When calculating this workbook“, kao „Set precision as displayed“. Na disku je jedan bit. BIFF8 fajl ga nosi u CalcPrecision zapisu ($000E, [MS-XLS] §2.4.35) čije je polje fFullPrec 1 za normalnu punu preciznost i 0 kad je opcija uključena. XLSX paket ga nosi kao atribut fullPrecision elementa calcPr u workbook.xml-u, definisan u ECMA-376 Part 1, gde je podrazumevana vrednost true i fullPrecision="0" uključuje zaokruživanje

Flag nije preferenca prikaza. Kad uključite kućicu, Excel upozorava da će podaci trajno izgubiti tačnost, i misli to ozbiljno: vrednosti se prepišu na svoju prikazanu preciznost, i cifre koje su odsečene nestaju. Kasnije brisanje kućice ne vraća stare cifre. 0.1234 prikazan kao 12.3% postaje 0.123 za svagda

HotXLS čita i piše flag u oba formata i izlaže ga u oba engine-a:

  • TXLSXWorkbook.FullPrecision: Boolean na XLSX engine-u, učitava se iz i čuva u calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean na Classic engine-u (takođe na IXLSWorkbook-u), učitava se iz i čuva u CalcPrecision zapis
  • Oboje je podrazumevano True, što je bezbedan, nedestruktivni režim i Excelov podrazumevani

Gde HotXLS primenjuje zaokruživanje je važno. HotXLS zaokružuje na mestu gde računa vrednost: svaki rezultat formule zaokružuje se na svoju prikazanu preciznost pre nego što se sačuva kao keširana vrednost ćelije, tokom Recalculate i tokom evaluacije na zahtev. Konstante koje dodelite kroz Value čuvaju se tačno takve kakve su date. Ako vaš izlaz mora da reprodukuje ono što Excel čuva posle uključene kućice, zaokružite te konstante sami pre upisa, na primer pomoćnikom prikazanim kasnije

Kako Excel odlučuje koliko decimala zadržava?

Excel izvodi broj zadržanih decimala iz konkretne sekcije formata koja prikazuje vrednost, ne iz format stringa u celini. Pravila ispod merena su u Excelu 16 i to je ono što XlsApplyDisplayedPrecision u lxNumFormat-u implementira za oba HotXLS engine-a

  1. Izaberite sekciju po predznaku. Format s dve sekcije koristi drugu sekciju za negativne vrednosti. Format s tri ili više sekcija koristi drugu za negativne vrednosti i treću za tačno nulu. Sve ostalo koristi prvu sekciju
  2. Izbrojite decimalne rezervisane znakove. Svaki 0, # ili ? iza decimalne tačke u toj sekciji dodaje jednu zadržanu decimalu
  3. Dodajte dva po znaku procenta. 0.0% prikazuje 0.1234 kao 12.3%, pa je sačuvana vrednost stoti deo onoga što vidite i zadržava tri decimale, ne jednu
  4. Oduzmite tri po skalirajućoj zapeti. Zapeta iza poslednjeg celobrojnog rezervisanog znaka (0,, 0.0,, 0,.0) deli prikaz sa 1000. 0.0, prikazuje 12345.678 kao 12.3, pa Excel zadržava jednu decimalu minus tri, što je negativan broj: vrednost se zaokružuje na stotice i čuva kao 12300. Zapeta između celobrojnih rezervisanih znakova, kao u #,##0, je obično grupisanje cifara i ništa ne menja
  5. Pustite nenumeričke sekcije na miru. General, sekcije datuma i vremena (uključujući proteklo [h], [mm] i [ss]), naučne, razlomačke i tekstualne sekcije, i sekcije bez ijednog rezervisanog znaka cifre zadržavaju punu preciznost
HotXLS dijagram pravila prikazane preciznosti: izaberite sekciju formata po predznaku vrednosti, izbrojite rezervisane znakove cifara iza decimalne tačke, dodajte dve decimale po znaku procenta, oduzmite tri po hiljadarskoj skalirajućoj zapeti tako da broj može i u minus, preskočite sasvim General i sekcije datuma i vremena, pa zaokružite na pola od nule
Broj cifara dolazi iz sekcije koja odgovara predznaku, plus dva po procentu i minus tri po skalirajućoj zapeti, a negativan broj zaokružuje na desetice ili stotice; General i sekcije datuma ostaju na miru

Mereni protiv Excela 16, ovo su vrednosti koje oba HotXLS engine-a sada čuvaju za rezultat formule u svakom formatu:

Format brojaIzračunata vrednostSačuvana vrednostPravilo koje se primenjuje
0.0%0.12340.123Jedna decimala plus dva za znak procenta
02.53Pola od nule, ne na parno
0-2.5-3Pola 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 dve decimale
#,##0.01234.56781234.6Grupišuća zapeta, bez skaliranja
0.0,12345.67812300Jedna decimala minus tri: zaokruživanje na stotice
0.0%;(0.00%)-0.0125-0.0125Negativna sekcija zadržava dva plus dve decimale
0.001.0051.01Tolerancija na grešku binarnog zapisa
0;-0;0.00.51Nije nula, pa odlučuje pozitivna sekcija

Poslednji red je finija zamka. Vrednost 0.5 se zaokružuje na ceo broj, i sekcija za nulu nikad ne stupa na scenu, jer Excel bira sekciju iz izračunate vrednosti pre zaokruživanja. Jedno iskreno ograničenje na HotXLS strani: sekcije se biraju samo po predznaku, pa se format čije sekcije nose proizvoljne uslove u zagradama poput [>=1000] i dalje seče po predznaku. Proverite takve formate protiv Excela ako su vam važni

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

Excel zaokružuje 1.005 u ćeliji s 0.00 na 1.01 iako je double najbliži 1.005 malo ispod polovišta, i HotXLS to poklapa s tolerancijom od nekoliko ulp. Literal 1.005 ne može se predstaviti u binarnom pokretnom zarezu. Najbliži IEEE 754 double je 1.00499999999999989341858963598497211933135986328125, i množenje sa 100 daje 100.49999999999999. Udžbenički Floor(x * 100 + 0.5) / 100 zato vraća 1.00, što se ne poklapa ni s brojem koji je korisnik ukucao, ni s onim što Excel pokazuje, ni s onim što Excel čuva

Delphi dodaje svoju smicalicu. System.Round zaokružuje izjednačenja na parno, pa je Round(2.5) 2 a Round(3.5) 4. To je bankarsko zaokruživanje, razuman podrazumevani izbor za statistiku, ali pogrešno pravilo ovde: Excel čuva 3 za 2.5 u ćeliji s 0 i -3 za -2.5. HotXLS implementacija radi s apsolutnom vrednošću, dodaje 0.5 plus relativnu toleranciju od 2-51 puta skalirana vrednost (nekoliko ulp na toj veličini, nikad manje od dva ulp od 1.0), odseca, vraća razmeru i obnavlja predznak. Sledeća funkcija je samostalna ilustracija tog principa, ne sam kod biblioteke, i negativnim brojevima cifara za skalirajuće zapete rukuje na isti način:

HotXLS dijagram zaokruživanja: 2.5 se zaokružuje na pola od nule na 3, a -2.5 na -3, gde Delphi System.Round daje bankarske odgovore 2 i -2, a pošto je double najbliži 1.005 tek malo ispod polovišta, upravo tolerancija od nekoliko ulp pretvara floor-osnovu 1.00 u Excelov odgovor 1.01
Excel zaokružuje izjednačenja od nule i oprašta grešku binarnog zapisa malom tolerancijom; oba su detalja merljiva, i preskakanje ijednog čuva 2 za 2.5 ili 1.00 za 1.005, jedan cent od Excela
// Načelna skica: zaokruži na pola od nule na ADigits decimale,
// s tolerancijom od nekoliko 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; // van double preciznosti: ostavi vrednost 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 prelilo
    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); // pola 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   (Floor osnova: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 cifre)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 cifre)

Tolerancija je namerni kompromis. Vrednost koja je zaista dva ulp ispod pola koraka takođe će se zaokružiti naviše, ali na toj udaljenosti razlika je nerazlučiva od greške predstavljanja, i tretiranje kao pola koraka je ono što čini da se ukućane decimale ponašaju onako kako korisnici očekuju

Šta je išlo naopako pre v2.384.57?

Pre v2.384.57 XLSX engine i Classic engine su svaki imali svoj kod za preciznost kao prikazano, i svaki je bio pogrešan na svoj način. Ako proizvodite radne sveske s uključenom opcijom, ovo su simptomi za traženje u fajlovima iz starijih buildova

XLSX engine: samo prva sekcija, bez procenta, bankarsko zaokruživanje

Stari XLSX put tražio je broj decimala format stringa u celini, što je gledalo samo prvu sekciju i ignorisalo %, pa zaokruživalo s Round. 0.1234 u 0.0% čuvan je kao 0.1, što je 10% umesto 12.3% na ekranu. 2.5 u 0 čuvan je kao 2 umesto 3. Negativne vrednosti u formatu poput 0.00;(0.0) zaokruživane su na dve decimale pozitivne sekcije. Od v2.384.57 XLSX engine zove istu deljenu rutinu kao Classic engine, koja je u tom izdanju dobila i podršku za skalirajuće zapete

Classic engine: TRUE je postao -1

Classic engine je čuvao svoje zaokruživanje s VarIsNumeric, a VarIsNumeric vraća True za varBoolean Variant. Pretvaranje tog Variant-a s Double(V) daje -1, jer je COM-stilski Boolean True sačuvan kao -1. Formula poput =A1>0 u ćeliji formatiranoj s 0.00 zato je izlazila iz preračunavanja kao broj -1. Od v2.384.57 Boolean rezultati se isključuju pre bilo kog numeričkog testa, i logički rezultat ostaje logički rezultat u oba engine-a

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

Treći bag sedeo je u modelu formata broja, a ne u zaokruživanju. Parser je svaki token u zagradi koji nije bio uslov klasifikovao kao boju, pa [h], [mm] i [ss] nikad nisu označili svoju sekciju kao datum/vreme. Prikaz nije bio pogođen, jer formatiranje ide posebnim putem, ali preciznost kao prikazano oslanja se na taj flag da preskoči vremenske vrednosti. Trajanje od pet sekundi je 5/86400 dana, otprilike 0.0000579, i format poput [ss].00 delovao je kao običan dvodecimalni broj, pa je s isključenim FullPrecision-om trajanje zaokruženo na 0.00 dana. Od v2.384.9 niz u zagradi od jednog slova h, m ili s parsira se kao token proteklog vremena i sekcija se tretira kao datum/vreme. Isto izdanje ispravilo je detekciju minuta u h:mm, gde je dvotačka između tokena skrivala sat od parsera

HotXLS dijagram pogrešnog čitanja proteklog vremena: pet sekundi sačuvano kao sitan razlomak dana u ćeliji formatiranoj tokenom ss u zagradi, koji je stari parser čitao kao boju i označavao kao običan dvodecimalni broj, pa je preciznost kao prikazano zaokruživala trajanje na 0.00 dok se nije parsiralo kao sekcija proteklog vremena
Formatiranje je išlo svojim putem, pa je ćelija izgledala ispravno dok se sačuvana vrednost zaokruživala na nulu; niz u zagradi od jednog slova h, m ili s je token proteklog vremena, ne boja, i sekcija zadržava punu preciznost

Uključivanje preciznosti kao prikazano u HotXLS-u iz Delphija

Da dobijete sačuvane vrednosti ekvivalentne Excelu, postavite flag pre preračunavanja koje treba da ga poštuje, pa čitajte keširane rezultate ili čuvajte. Na XLSX engine-u je FullPrecision običan flag: njegova promena ne poništava rezultate koje je raniji Recalculate već sačuvao, pa ga postavite odmah posle Create ili Open i pre prvog Recalculate-a. Primer koristi formule jer je to mesto gde HotXLS primenjuje 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 (hiljade)

    // Mora se postaviti pre prvog Recalculate na XLSX engine-u
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Keširani rezultati sada poklapaju Excel 16: 0.123, 3 i 12300.
    // Konstante u koloni 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'); // piše <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Classic engine se ponaša isto, uz jednu pogodnost: dodela TXLSWorkbook.UseFullPrecision označava svaku formulu u grafu zavisnosti kao prljavu, pa sledeći Recalculate ponovo vrednuje celu radnu svesku pod novim pravilima. Promena NumberFormat-a dok je opcija uključena takođe označava pogođene ćelije formula kao prljave, jer format sada odlučuje o sačuvanoj vrednosti. Primetite da Classic Recalculate vraća broj ćelija formula koje nije mogao da vrednuje, pa nula znači 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čava svaku formulu prljavom
    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 Boolean True (buildovi pre v2.384.57 čuvali su -1)
    Wb.SaveAs('report.xls'); // CalcPrecision zapis s fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Oba engine-a poštuju i flag koji dolazi s fajlom. Otvorite radnu svesku sačuvanu s uključenom opcijom i FullPrecision ili UseFullPrecision je već False, pa Recalculate posle učitavanja zaokružuje baš onako kako bi Excel. Ako samo treba da pročitate brojeve koje je Excel već sačuvao, preračunavanje možete sasvim preskočiti, kako je opisano u tekstu o čitanju keširanih vrednosti formula bez preračunavanja. A o tome kako serijski brojevi i formati datuma sarađuju s modelom formata koji vodi proveru datuma/vremena, pogledajte Excel datumski serijske brojeve, 1904 sistem i numFmt u Delphiju

Kada uključiti preciznost kao prikazano, a kada ne?

Uključite preciznost kao prikazano samo kad sačuvani brojevi radne sveske moraju biti jednaki njenim prikazanim brojevima, i prihvatate da dodatne cifre zauvek izgubite. Klasičan legitimni slučaj je finansijski raspored u kojem kolone zaokruženih iznosa moraju dati zaokruženi zbir s ekrana, bez skrivenih delova centa koji daju zbir promašen za jedan na poslednjem mestu. Poklapanje s postojećom radnom sveskom kupca koja već ima uključenu opciju je drugi dobar razlog, i HotXLS čuva flag kroz round-trip da ih ne biste tiho vratili na punu preciznost

Izbegavajte je u većini drugih situacija:

  • Inženjerski i naučni podaci. Zaokruživanje merenja jer je neko izabrao dvodecimalni format za izveštaj uništava informaciju koju nijedna kasnija promena formata ne može vratiti
  • Procenti s grlim formatima. Format 0% zadržava samo dve decimale sačuvanog odnosa, pa 0.1234 postaje 0.12, i svaka formula nizvodno koja čita ćeliju radi s 0.12
  • Skalirani prikazi. Format 0, ili 0.0, korišćen za prikaz hiljada zaokružuje sačuvanu vrednost na hiljade ili stotice, što retko jeste ono što je osoba koja je izabrala format nameravala
  • Deljeni šabloni. Flag je na nivou cele radne sveske. Svako ko kasnije doda list nasleđuje ponašanje, obično ne znajući da je uključeno

Ako ono što zaista želite su zaokruženi rezultati u par konkretnih ćelija, upišite ROUND u te formule umesto toga. ROUND je eksplicitan, lokal za ćeliju, vidljiv svakome ko čita formulu, i vrednuje ga HotXLS formula engine kao svaku drugu funkciju, bez efekata na nivou cele radne sveske

Brzi podsetnik za preciznost kao prikazano

  • Flag fajla: CalcPrecision $000E s fFullPrec = 0 u BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" u XLSX (ECMA-376 Part 1)
  • HotXLS prekidači: TXLSXWorkbook.FullPrecision := False i TXLSWorkbook.UseFullPrecision := False, oboje podrazumevano True
  • Sekcija: bira se po predznaku izračunate vrednosti; treća sekcija samo za tačno nulu
  • Cifre: decimalni rezervisani znakovi, plus dva po %, minus tri po skalirajućoj zapeti; broj može biti negativan
  • Zaokruživanje: pola od nule s tolerancijom od nekoliko ulp, pa 2.5 daje 3, -2.5 daje -3 a 1.005 daje 1.01
  • Preskakano: General, datum/vreme i proteklo vreme, naučno, razlomak, tekst, Boolean i greške
  • Obuhvat u HotXLS-u: rezultati formula kako se računaju; konstante se čuvaju kao što su dodeljene
  • XLSX engine: postavite FullPrecision pre prvog Recalculate-a; Classic setter sam ponovo prlja sve formule
  • Verzije: poklopljeno s Excelom 16 u oba engine-a 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 sveske nativno iz Delphija i C++Buildera, uključujući opcije računanja radne sveske pokrivene ovde. Detalji, izdanja i probno preuzimanje su na stranici HotXLS Delphi spreadsheet component