LlosLab Software Development BlogEngineering notes for PDF, Delphi, and document software developers

Articol tehnic

HotXLS precision as displayed: regulile de rotunjire Excel

HotXLS Delphi Component

Precision as displayed din Excel rotunjește fiecare număr stocat la zecimalele pe care le arată formatul lui de număr: secțiunea de format care se potrivește cu semnul valorii, două zecimale în plus per %, trei în minus per virgulă de scalare a miilor, rotunjire half away from zero. HotXLS aplică aceeași regulă în ambele motoare Delphi când TXLSXWorkbook.FullPrecision sau TXLSWorkbook.UseFullPrecision e False. Sună a o singură linie de cod până când un client raportează că totalurile facturii exportate de voi se contrazic cu Excel cu un cent, sau că o coloană de durate în [ss].00 s-a prăbușit în zero. Ambele s-au întâmplat, iar ambele duc la o regulă nimerită greșit. Din v2.384.57 cele două motoare împart o singură implementare, ale cărei valori așteptate au fost măsurate în Excel 16 cu Workbook.PrecisionAsDisplayed pornit

Ce schimbă de fapt precision as displayed într-un workbook?

Precision as displayed e un flag unic la nivel de workbook care le spune motorului de calcul să stocheze numerele așa cum arată ele, nu așa cum au fost calculate. În UI-ul Excel stă sub File, Options, Advanced, „When calculating this workbook”, ca „Set precision as displayed”. Pe disc e un singur bit. Un fișier BIFF8 îl poartă în înregistrarea CalcPrecision ($000E, [MS-XLS] §2.4.35), al cărei câmp fFullPrec e 1 pentru precizie completă normală și 0 când opțiunea e pornită. Un pachet XLSX îl poartă ca atributul fullPrecision al elementului calcPr din workbook.xml, definit în ECMA-376 Partea 1, unde implicit e true, iar fullPrecision="0" pornește rotunjirea

Flag-ul nu e o preferință de afișare. Când bifați căsuța, Excel avertizează că datele vor pierde definitiv acuratețe, și chiar asta vrea să spună: valorile sunt rescrise la precizia lor afișată, iar cifrele tăiate dispar. Debifarea căsuței mai târziu nu aduce cifrele vechi înapoi. Un 0.1234 afișat ca 12.3% devine 0.123 pentru totdeauna

HotXLS citește și scrie flag-ul în ambele formate și îl expune în ambele motoare:

  • TXLSXWorkbook.FullPrecision: Boolean pe motorul XLSX, încărcat din și salvat în calcPr/@fullPrecision
  • TXLSWorkbook.UseFullPrecision: Boolean pe motorul Classic (și pe IXLSWorkbook), încărcat din și salvat în înregistrarea CalcPrecision
  • Ambele au implicit True, care e modul sigur, nedistructiv, și valoarea implicită a Excel-ului

Locul unde HotXLS aplică rotunjirea contează. HotXLS rotunjește în punctul în care calculează o valoare: fiecare rezultat de formulă e rotunjit la precizia lui afișată înainte să fie stocat ca valoare cached a celulei, în timpul lui Recalculate și în timpul evaluării la cerere. Constantele pe care le atribuiți prin Value sunt stocate exact cum sunt date. Dacă output-ul vostru trebuie să reproducă ce stochează Excel după bifarea căsuței, rotunjiți voi acele constante înainte să le scrieți, de exemplu cu helper-ul arătat mai jos

Cum decide Excel câte zecimale păstrează?

Excel derivă numărul de zecimale păstrate din secțiunea specifică de format care afișează valoarea, nu din șirul de format ca întreg. Regulile de mai jos au fost măsurate în Excel 16 și sunt ce implementează XlsApplyDisplayedPrecision din lxNumFormat pentru ambele motoare HotXLS

  1. Alege secțiunea după semn. Un format cu două secțiuni folosește a doua secțiune pentru valorile negative. Un format cu trei sau mai multe secțiuni folosește a doua pentru negative și a treia pentru exact zero. Tot restul folosește prima secțiune
  2. Numără placeholder-ele de zecimale. Fiecare 0, # sau ? după punctul zecimal din secțiunea aceea adaugă o zecimală păstrată
  3. Adaugă două per semn de procent. 0.0% arată 0.1234 ca 12.3%, deci valoarea stocată e a suta parte din ce vedeți și păstrează trei zecimale, nu una
  4. Scade trei per virgulă de scalare. O virgulă după ultimul placeholder de întreg (0,, 0.0,, 0,.0) împarte afișarea la 1000. 0.0, arată 12345.678 ca 12.3, deci Excel păstrează o zecimală minus trei, ceea ce e un număr negativ: valoarea e rotunjită la sute și stocată ca 12300. O virgulă între placeholder-e de întreg, ca în #,##0, e simplă grupare de cifre și nu schimbă nimic
  5. Lasă în pace secțiunile non-numerice. Secțiunile General, de dată și timp (inclusiv cele trecute [h], [mm] și [ss]), științifice, de fracții și de text, și secțiunile fără niciun placeholder de cifră păstrează precizia completă
Diagramă HotXLS a regulilor de precizie afișată: alege secțiunea de format după semnul valorii, numără placeholder-ele de cifre după punctul zecimal, adaugă două zecimale per semn de procent, scade trei per virgulă de scalare a miilor, astfel încât numărul poate deveni negativ, sări complet secțiunile General și de dată-timp, apoi rotunjește half away from zero
Numărul de cifre vine din secțiunea care se potrivește cu semnul, plus două per procent și minus trei per virgulă de scalare, iar un număr negativ rotunjește la zeci sau sute; secțiunile General și de dată rămân neatinse

Măsurat contra Excel 16, acestea sunt valorile pe care ambele motoare HotXLS le stochează acum pentru un rezultat de formulă în fiecare format:

Format de numărValoare calculatăValoare stocatăRegula care se aplică
0.0%0.12340.123O zecimală plus două pentru semnul de procent
02.53Half away from zero, nu la par
0-2.5-3Half away from zero și pe partea negativă
0.00;(0.0)-1.2345-1.2Secțiunea negativă arată o zecimală
0.00;(0.0)1.23451.23Secțiunea pozitivă arată două zecimale
#,##0.01234.56781234.6Virgulă de grupare, fără scalare
0.0,12345.67812300O zecimală minus trei: rotunjire la sute
0.0%;(0.00%)-0.0125-0.0125Secțiunea negativă păstrează două plus două zecimale
0.001.0051.01Toleranță pentru eroarea de reprezentare binară
0;-0;0.00.51Nu e zero, deci decide secțiunea pozitivă

Ultimul rând e o capcană frumoasă. Valoarea 0.5 se rotunjește la un număr întreg, iar secțiunea de zero nu intră niciodată în joc, fiindcă Excel alege secțiunea din valoarea calculată înainte de rotunjire. O limitare sinceră pe partea HotXLS: secțiunile sunt alese doar după semn, deci un format ale cărui secțiuni poartă condiții custom cu paranteze precum [>=1000] este tot împărțit după semn. Verificați asemenea formate contra Excel dacă vă contează

De ce 1.005 se rotunjește la 1.01 și nu la 1.00?

Excel rotunjește 1.005 într-o celulă 0.00 la 1.01 deși double-ul cel mai aproape de 1.005 e puțin sub punctul de jumătate, iar HotXLS se potrivește cu el printr-o toleranță de câteva ulp. Literalul 1.005 nu poate fi reprezentat în virgulă mobilă binară. Cel mai apropiat double IEEE 754 e 1.00499999999999989341858963598497211933135986328125, iar înmulțirea cu 100 dă 100.49999999999999. Un Floor(x * 100 + 0.5) / 100 de manual întoarce prin urmare 1.00, ceea ce se contrazice cu numărul tastat de utilizator, cu ce arată Excel și cu ce stochează Excel

Delphi adaugă propria ei răscroială. System.Round rotunjește egalurile la par, deci Round(2.5) e 2 și Round(3.5) e 4. Asta e banker's rounding, un implicit sensibil pentru statistici și regula greșită aici: Excel stochează 3 pentru 2.5 într-o celulă 0 și -3 pentru -2.5. Implementarea HotXLS lucrează pe valoarea absolută, adaugă 0.5 plus o toleranță relativă de 2-51 ori valoarea scalată (câteva ulp la acea magnitudine, niciodată mai puțin de două ulp ale lui 1.0), trunchiază, re-scalează și restabilește semnul. Funcția de mai jos e o ilustrare autoportantă a principiului, nu codul bibliotecii în sine, și tratează numerele negative de cifre pentru virgulele de scalare exact la fel:

Diagramă de rotunjire HotXLS: 2.5 se rotunjește half away from zero la 3, iar -2.5 la -3, acolo unde System.Round din Delphi dă răspunsurile banker 2 și -2, și cum double-ul cel mai aproape de 1.005 stă chiar sub punctul de jumătate, toleranța de câteva ulp e ceea ce transformă un 1.00 pe bază de floor în răspunsul Excel 1.01
Excel rotunjește egalurile departe de zero și iartă eroarea de reprezentare binară cu o toleranță mică; ambele detalii sunt măsurabile, iar săritrea oricăruia stochează 2 pentru 2.5 sau 1.00 pentru 1.005, la un cent distanță de Excel
// Schiță de principiu: rotunjire half away from zero la ADigits zecimale,
// cu o toleranță de câteva ulp ca 1.005 să ajungă la 1.01.
// ADigits < 0 rotunjește la zeci, sute, ... ("0.0," dă -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, două ulp ale lui 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // dincolo de precizia double: lasă valoarea în pace
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // scalarea ar da 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); // half away from zero, nu 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   (pe bază de 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 cifre)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 cifre)

Toleranța e un compromis deliberat. O valoare aflată cu adevărat cu două ulp sub o treaptă de jumătate se rotunjește și ea în sus, dar la distanța aceea diferența e de nedespărțit de eroarea de reprezentare, iar tratarea ei ca treaptă de jumătate e ceea ce face ca zecimalele tastate să se comporte cum se așteaptă utilizatorii

Ce mergea greșit înainte de v2.384.57?

Înainte de v2.384.57 motorul XLSX și motorul Classic aveau fiecare propriul cod de precision-as-displayed, iar fiecare greșea într-un fel diferit. Dacă produceți workbook-uri cu opțiunea pornită, acestea sunt simptomele de căutat în fișierele generate de build-uri mai vechi

Motorul XLSX: doar prima secțiune, fără procent, banker's rounding

Vechea cale XLSX cerea numărul de zecimale al șirului de format ca întreg, care se uita doar la prima secțiune și ignora %, apoi rotunjea cu Round. Un 0.1234 în 0.0% era stocat ca 0.1, ceea ce e 10% în loc de 12.3% de pe ecran. Un 2.5 în 0 era stocat ca 2 în loc de 3. Valorile negative într-un format precum 0.00;(0.0) erau rotunjite la cele două zecimale ale secțiunii pozitive. Din v2.384.57 motorul XLSX apelează aceeași rutină partajată ca motorul Classic, care a căpătat în acea versiune și suport pentru virgula de scalare

Motorul Classic: TRUE a devenit -1

Motorul Classic își păzea rotunjirea cu VarIsNumeric, iar VarIsNumeric întoarce True pentru un Variant varBoolean. Convertirea Variant-ului aceluia cu Double(V) dă -1, fiindcă un Boolean True în stil COM e stocat ca -1. O formulă precum =A1>0 într-o celulă formatată 0.00 ieșea prin urmare din recalculare drept numărul -1. Din v2.384.57 rezultatele Boolean sunt excluse înaintea oricărui test numeric, iar un rezultat logic rămâne rezultat logic în ambele motoare

Formatele de timp scurs citite drept culori (v2.384.9)

Al treilea bug stătea în modelul de formate de numere, nu în rotunjire. Parser-ul clasifica fiecare token între paranteze pătrate care nu era o condiție drept culoare, deci [h], [mm] și [ss] nu-și marcau niciodată secțiunea drept dată/timp. Afișarea nu era afectată, fiindcă formatarea rulează pe o cale separată, dar precision as displayed se bazează pe flag-ul acela ca să sară valorile de timp. O durată de cinci secunde e 5/86400 dintr-o zi, cam 0.0000579, iar un format precum [ss].00 părea un număr obișnuit cu două zecimale, deci cu FullPrecision oprit durata era rotunjită la 0.00 zile. Din v2.384.9 un șir între paranteze dintr-o singură literă h, m sau s e parsat drept token de timp scurs, iar secțiunea e tratată drept dată/timp. Aceeași versiune a reparat detecția minutelor în h:mm, unde două puncte dintre token-uri ascundeau ora de parser

Diagramă HotXLS a unui pars greșit de timp scurs: cinci secunde stocate ca o fracție minusculă de zi într-o celulă formatată cu token-ul ss între paranteze, pe care vechiul parser îl citea drept culoare și îl marca drept număr simplu cu două zecimale, deci precision as displayed rotunjea durata la 0.00 până când a fost parsată drept secțiune de timp scurs
Formatarea rula pe calea ei proprie, deci celula arăta corect în timp ce valoarea stocată se rotunjea la zero; o literă unică h, m sau s între paranteze e un token de timp scurs, nu o culoare, iar secțiunea păstrează precizia completă

Activarea precision as displayed în HotXLS din Delphi

Ca să obțineți valori stocate echivalente cu Excel, setați flag-ul înainte de recalcularea care trebuie să-l respecte, apoi citiți rezultatele din cache sau salvați. Pe motorul XLSX, FullPrecision e un flag simplu: schimbarea lui nu invalidează rezultatele pe care un Recalculate anterior le stocase deja, deci setați-l imediat după Create sau Open și înainte de primul Recalculate. Exemplul folosește formule pentru că acolo aplică HotXLS rotunjirea:

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

    // Trebuie setat înainte de primul Recalculate pe motorul XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Rezultatele din cache se potrivesc acum cu Excel 16: 0.123, 3 și 12300.
    // Constantele din coloana A își păstrează precizia completă.
    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'); // scrie <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Motorul Classic se poartă la fel, cu o comoditate: atribuirea lui TXLSWorkbook.UseFullPrecision marchează fiecare formulă din graful de dependențe ca dirty, deci următorul Recalculate re-evaluează tot workbook-ul sub regula nouă. Schimbarea unui NumberFormat cât timp opțiunea e pornită marchează de asemenea celulele de formule afectate ca dirty, fiindcă formatul decide acum valoarea stocată. Rețineți că Recalculate-ul Classic întoarce numărul de celule de formule pe care nu le-a putut evalua, deci zero înseamnă succes:

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; // marchează fiecare formulă ca dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: secțiunea negativă "(0.0)" arată o zecimală
    // C1 rămâne Boolean True (build-urile de dinainte de v2.384.57 stocau -1)
    Wb.SaveAs('report.xls'); // înregistrare CalcPrecision cu fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Ambele motoare respectă de asemenea flag-ul care vine cu un fișier. Deschideți un workbook salvat cu opțiunea pornită și FullPrecision sau UseFullPrecision e deja False, deci un Recalculate după încărcare rotunjește exact așa cum ar face Excel. Dacă aveți nevoie doar să citiți numerele pe care Excel le-a stocat deja, puteți sări complet recalcularea, cum e descris în citirea valorilor de formule din cache fără recalculare. Pentru cum interacționează numerele de serie și formatele de dată cu modelul de format care acționează verificarea dată/timp, vezi numerele de serie de dată Excel, sistemul 1904 și numFmt în Delphi

Când merita pornit precision as displayed, și când nu?

Porniți precision as displayed doar când numerele stocate ale workbook-ului trebuie să egaleze numerele lui afișate, și acceptați să pierdeți cifrele în plus pentru totdeauna. Cazul legitim clasic e un grafic financiar în care coloanele de sume rotunjite trebuie să se adune la totalul rotunjit de pe ecran, fără fracții ascunse de cent care produc un total greșit cu unu în ultima poziție. Potrivirea cu un workbook existent al unui client care are deja opțiunea setată e celălalt motiv bun, iar HotXLS păstrează flag-ul la round-trip, ca să nu-i comutați tăcut înapoi la precizie completă

Evitați-l în majoritatea celorlalte situații:

  • Date de inginerie și științifice. Rotunjirea unei măsurători pentru că cineva a ales un format cu două zecimale pentru un raport distruge informații pe care nicio schimbare ulterioară de format nu le poate recupera
  • Procente cu formate grosiere. Un format 0% păstrează doar două zecimale ale raportului stocat, deci 0.1234 devine 0.12, iar fiecare formulă din aval care citește celula lucrează cu 0.12
  • Afișări scalate. Un format 0, sau 0.0, folosit ca să arate mii rotunjește valoarea stocată la mii sau sute, ceea ce e rar intenția persoanei care a ales formatul
  • Template-uri partajate. Flag-ul e la nivel de workbook. Oricine adaugă mai târziu un sheet moștenește comportamentul, de obicei fără să știe că e pornit

Dacă ce vreți de fapt sunt rezultate rotunjite în câteva celule concrete, scrieți ROUND în formulele acelea în loc. ROUND e explicit, local celulei, vizibil oricui citește formula și evaluat de motorul de formule HotXLS ca orice altă funcție, fără efecte secundare la nivel de workbook

Referință rapidă precision as displayed

  • Flag de fișier: CalcPrecision $000E cu fFullPrec = 0 în BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" în XLSX (ECMA-376 Partea 1)
  • Comutatoare HotXLS: TXLSXWorkbook.FullPrecision := False și TXLSWorkbook.UseFullPrecision := False, ambele cu implicit True
  • Secțiune: aleasă după semnul valorii calculate; a treia secțiune doar pentru exact zero
  • Cifre: placeholder-e zecimale, plus două per %, minus trei per virgulă de scalare; numărul poate fi negativ
  • Rotunjire: half away from zero cu toleranță de câteva ulp, deci 2.5 dă 3, -2.5 dă -3, iar 1.005 dă 1.01
  • Sărite: General, dată/timp și timp scurs, științifice, fracții, text, valori Boolean și erori
  • Arie în HotXLS: rezultatele formulelor pe măsură ce sunt calculate; constantele sunt stocate cum sunt atribuite
  • Motor XLSX: setați FullPrecision înainte de primul Recalculate; setter-ul Classic re-marchează singur toate formulele ca dirty
  • Versiuni: potrivit cu Excel 16 în ambele motoare din v2.384.57; formatele de timp scurs protejate din v2.384.9

HotXLS citește, scrie și calculează workbook-uri XLS și XLSX nativ din Delphi și C++Builder, inclusiv opțiunile de calcul de workbook acoperite aici. Detalii, ediții și descărcarea de probă sunt pe pagina componentei HotXLS Delphi spreadsheet