HotXLS tikslumas pagal rodinį: Excel apvalinimo taisyklės

Excel precision as displayed kiekvieną saugomą skaičių apvalina iki dešimtainių, kiek rodo jo skaičiaus formatas: formato sekcijos, atitinkančios reikšmės ženklą, po dvi papildomas dešimtaines už kiekvieną %, trimis mažiau už kiekvieną tūkstantinių mastelio kablelį, apvalinant pusę nuo nulio. HotXLS tą pačią taisyklę abiejuose savo Delphi varikliuose taiko, kai TXLSXWorkbook.FullPrecision arba TXLSWorkbook.UseFullPrecision yra False. Skamba kaip vienas sakinys, kol klientas nepraneša, kad jūsų eksportuotų sąskaitų sumos nuo Excel skiriasi centu, arba kad [ss].00 formato trukmių stulpelis susmuigėjo į nulį. Abu atvejai buvo, ir abu grįžta prie vienos iš tų taisyklių, padarytos neteisingai. Nuo v2.384.57 abu varikliai dalijasi viena implementacija, kurios tikėtinos reikšmės išmatuotos Excel 16 su įjungtu Workbook.PrecisionAsDisplayed

Ką precision as displayed iš tikrųjų pakeičia darbaknygėje?

Precision as displayed – vienintelė darbaknygės lygio vėliavėlė, sakanti skaičiavimo varikliui saugoti skaičius taip, kaip jie atrodo, o ne taip, kaip buvo apskaičiuoti. Excel sąsajoje ji stovi po File, Options, Advanced, „When calculating this workbook“, kaip „Set precision as displayed“. Diske tai vienas bitas. BIFF8 failas jos neša CalcPrecision įraše ($000E, [MS-XLS] §2.4.35), kurio fFullPrec laukas yra 1 normaliam pilnam tikslumui ir 0, kai parinktis įjungta. XLSX paketas jos neša kaip fullPrecision atributą ant calcPr elemento workbook.xml viduje, apibrėžtame ECMA-376 Part 1, kur numatytoji reikšmė true, o fullPrecision="0" įjungia apvalinimą

Ši vėliavėlė nėra rodymo nuostata. Pažymėję langelį, gaunate Excel įspėjimą, kad duomenys amžinai praras tikslumą, ir tai rimtai: reikšmės perrašomos į savo rodomąjį tikslumą, o nukirpti skaitmenys dingsta. Vėliau nuėmus varnelę senųjų skaitmenų negrąžinsi. 0.1234, rodoma kaip 12.3%, tampa 0.123 visam laikui

HotXLS vėliavėlę skaito ir rašo abiejuose formatuose, o abiejuose varikliuose ją atveria:

• TXLSXWorkbook.FullPrecision: Boolean XLSX variklyje, keliama iš ir saugoma į calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean klasikiniame variklyje (taip pat ant IXLSWorkbook), keliama iš ir saugoma į CalcPrecision įrašą
• Abi pagal numatymą True – saugi, nieko nesunaikinanti veiksena ir Excel numatytoji

Svarbu, kur HotXLS taiko apvalinimą. HotXLS apvalina ten, kur apskaičiuoja reikšmę: kiekvienas formulės rezultatas apvalinamas iki savo rodomojo tikslumo prieš įrašant jį kaip langelio sukaupptąją reikšmę, Recalculate metu ir vertinant pagal pareikalavimą. Konstantos, kurias priskiriate per Value, saugomos tiksliai tokios, kokios duotos. Jei jūsų išvestis turi atkurti tai, ką Excel saugo pažymėjus langelį, tas konstantas apvalinkite patys prieš rašydami, pavyzdžiui, žemiau parodytu pagalbininku

Kaip Excel nusprendžia, kiek dešimtainių palikti?

Paliekamų dešimtainių skaičių Excel išveda iš konkrečios formato sekcijos, rodančios reikšmę, o ne iš visos formato eilutės. Žemiau esančios taisyklės išmatuotos Excel 16 ir yra tai, ką XlsApplyDisplayedPrecision iš lxNumFormat įgyvendina abiem HotXLS varikliams

1. Sekciją rinkitės pagal ženklą. Dviejų sekcijų formatas neigiamoms reikšmėms naudoja antrąją sekciją. Formatas su trimis ar daugiau sekcijų neigiamoms naudoja antrąją, o tiksliai nuliui – trečiąją. Visi kiti atvejai naudoja pirmąją sekciją
2. Skaičiuokite dešimtainių vietų ženklelius. Kiekvienas 0, # arba ? po dešimtainiojo skirtuko toje sekcijoje prideda vieną paliekamą dešimtainę
3. Pridėkite po dvi už procento ženklą. 0.0% rodo 0.1234 kaip 12.3%, tad saugoma reikšmė – šimtoji to, ką matote, dalis, ir laiko tris dešimtaines, o ne vieną
4. Atimkite po tris už mastelio kablelį. Kablelis po paskutiniuoju sveikuoju ženkleliu (0,, 0.0,, 0,.0) rodinį padalija iš 1000. 0.0, rodo 12345.678 kaip 12.3, tad Excel laiko vieną dešimtainę minus tris – neigiamą skaičių: reikšmė apvalinama iki šimtų ir saugoma kaip 12300. Kablelis tarp sveikųjų skaitmenų ženklelių, kaip #,##0, yra paprastas skaitmenų grupavimas ir nieko nekeičia
5. Nelieskite neskaitinių sekcijų. General, datos ir laiko sekcijos (įskaitant praėjusio laiko [h], [mm] ir [ss]), mokslinės, trupmenų ir teksto sekcijos, taip pat sekcijos be jokio skaitmens ženklelio palieka pilną tikslumą

Išmatuota prieš Excel 16, štai kokias reikšmes abu HotXLS varikliai dabar saugo formulės rezultatui kiekviename formate:

Skaičiaus formatas	Apskaičiuota reikšmė	Saugoma reikšmė	Galiojanti taisyklė
0.0%	0.1234	0.123	Viena dešimtainė plius dvi už procento ženklą
0	2.5	3	Pusė nuo nulio, ne iki lyginio
0	-2.5	-3	Pusė nuo nulio ir neigiamoje pusėje
0.00;(0.0)	-1.2345	-1.2	Neigiama sekcija rodo vieną dešimtainę
0.00;(0.0)	1.2345	1.23	Teigiama sekcija rodo dvi dešimtaines
#,##0.0	1234.5678	1234.6	Grupavimo kablelis, be mastelio
0.0,	12345.678	12300	Viena dešimtainė minus trys: apvalinti iki šimtų
0.0%;(0.00%)	-0.0125	-0.0125	Neigiama sekcija laiko dvi plius dvi dešimtaines
0.00	1.005	1.01	Paklaida dvejetainio atvaizdavimo klaidai
0;-0;0.0	0.5	1	Ne nulis, tad sprendžia teigiama sekcija

Paskutinė eilutė – gražūs spąstai. Reikšmė 0.5 apvalinama iki sveikojo skaičiaus, o nulinė sekcija niekada neprijauja, nes Excel sekciją renkasi iš apskaičiuotos reikšmės dar prieš apvalindamas. Vienas sąžiningas HotXLS apribojimas: sekcijos renkamos vien pagal ženklą, tad formato, kurio sekcijos neša savas skliaustines sąlygas, tokias kaip [>=1000], atskyrimas vis tiek vyksta pagal ženklą. Tokius formatus susitikrinkite su Excel, jei jie jums svarbūs

Kodėl 1.005 apvalinama į 1.01, o ne į 1.00?

Excel 1.005 0.00 langelyje apvalina į 1.01, nors artimiausias 1.005 double yra kiek žemiau pusiaukelės, ir HotXLS tai atitinka kelių ulp paklaida. Literalas 1.005 dvejetainiame slankiajame kablelyje nepavaizduojamas. Artimiausias IEEE 754 double yra 1.00499999999999989341858963598497211933135986328125, o sudauginus iš 100 gaunama 100.49999999999999. Vadovėlinė Floor(x * 100 + 0.5) / 100 todėl grąžina 1.00, kas nesutampa nei su vartotojo įrašytu skaičiumi, nei su tuo, ką rodo Excel, nei su tuo, ką Excel saugo

Delphi prideda savą posūkį. System.Round lygiąsias apvalina iki lyginio, tad Round(2.5) yra 2, o Round(3.5) – 4. Tai bankininkų apvalinimas – protingas numatymas statistikoje ir neteisinga taisyklė čia: Excel 2.5 0 langelyje saugo 3, o -2.5 – -3. HotXLS implementacija dirba su absoliučia reikšme, prideda 0.5 plius santykinę 2^-51 pakeltos reikšmės paklaidą (keli ulp toje didumoje, niekada mažiau nei du 1.0 ulp), nukerpa, grąžina mastelį ir atkuria ženklą. Ši funkcija – savarankiškas to principo iliustravimas, o ne pati bibliotekos kodas, ir neigiamus skaitmenų skaičius mastelio kableliams ji apdoroja taip pat:

// Principinis eskizas: apvalinti pusę nuo nulio iki ADigits dešimtainių,
// su kelių ulp paklaida, kad 1.005 pasiektų 1.01.
// ADigits < 0 apvalina iki dešimčių, šimtų, ... ("0.0," duoda -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, du 1.0 ulp
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // už double tikslumo ribų: reikšmę palikti tokia
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // mastelis sukeltų perpildymą
    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); // pusė nuo nulio, 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   (pagal 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 skaitmenys)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 skaitmenys)

Paklaida – sąmoningas kompromisas. Reikšmė, tikrai esanti dviem ulp žemiau pusės žingsnio, irgi apvalinama aukštyn, bet to atstumu skirtumas nuo atvaizdavimo klaidos neatskiriamas, o jos traktavimas kaip pusės žingsnio yra tai, dėl ko įrašyti dešimtainiai elgiasi taip, kaip tikisi vartotojai

Kas buvo negerai iki v2.384.57?

Iki v2.384.57 XLSX variklis ir klasikinis variklis kiekvienas turėjo savąjį precision-as-displayed kodą, ir kiekvienas klysdavo savaip. Jei darbaknyges gaminate su įjungta parinktimi, štai simptomai, kurių ieškokite senesnių variantų sugeneruotuose failuose

XLSX variklis: tik pirmoji sekcija, be procento, bankininkų apvalinimas

Senasis XLSX kelias prašydavo visos formato eilutės dešimtainių skaičiaus, kas žiūrėdavo tik į pirmąją sekciją ir ignoruodavo %, tada apvalindavo su Round. 0.1234 0.0% formate būdavo saugoma kaip 0.1 – tai 10% vietoj ekrane matomų 12.3%. 2.5 0 formate būdavo saugoma kaip 2 vietoj 3. Neigiamos reikšmės formate, tokame kaip 0.00;(0.0), būdavo apvalinamos iki teigiamos sekcijos dviejų dešimtainių. Nuo v2.384.57 XLSX variklis kviečia tą pačią bendrą procedūrą kaip klasikinis variklis, kuris tame leidime taip pat gavo mastelio kablelių palaikymą

Klasikinis variklis: TRUE tapdavo -1

Klasikinis variklis savo apvalinimą saugodavo su VarIsNumeric, o VarIsNumeric grąžina True varBoolean Variant atveju. Tą Variant konvertavus su Double(V) gaunama -1, nes COM stiliaus Boolean True saugomas kaip -1. Formulė, tokia kaip =A1>0, 0.00 formatu suformatuotame langelyje, iš perskaičiavimo todėl išeidavo kaip skaičius -1. Nuo v2.384.57 Boolean rezultatai atskiriami prieš bet kokį skaitinį testą, o loginis rezultatas abiejuose varikliuose lieka loginiu

Praėjusio laiko formatai skaitomi kaip spalvos (v2.384.9)

Trečioji klaida tupėjo skaičiaus formato modelyje, o ne apvalinime. Parseris kiekvieną skliaustais apdėtą tokeną, nesutampantį su sąlyga, klasifikuodavo kaip spalvą, tad [h], [mm] ir [ss] niekada nepažymėdavo savo sekcijos kaip datos/laiko. Rodinys nenukenčiadavo, nes formatavimas lekia atskiru keliu, bet precision as displayed remiasi ta vėliavėle, kad praleistų laiko reikšmes. Penkių sekundžių trukmė yra 5/86400 paros, apie 0.0000579, o toks formatas kaip [ss].00 atrodė kaip paprastas dviejų dešimtainių skaičius, tad su išjungtu FullPrecision trukmė būdavo apvalinama į 0.00 paros. Nuo v2.384.9 skliaustuose esantis vienos h, m arba s raidės junginys analizuojamas kaip praėjusio laiko tokenas, o sekcija traktuojama kaip data/laikas. Tas pats leidimas sutvarkė minučių atpažinimą h:mm, kur dvitaškis tarp tokenų anksčiau pridengdavo valandą nuo parserio

Precision as displayed įjungimas HotXLS iš Delphi

Kad gautumėte Excel atitinkančias saugomas reikšmes, vėliavėlę nustatykite prieš perskaičiavimą, kuris turėtų ją gerbti, tada skaitykite sukaupptus rezultatus arba išsaugokite. XLSX variklyje FullPrecision – paprasta vėliavėlė: jos keitimas ankstesnio Recalculate jau išsaugotų rezultatų nepanaikina, tad nustatykite iškart po Create arba Open ir prieš pirmąjį Recalculate. Pavyzdys naudoja formules, nes būtent ten HotXLS taiko apvalinimą:

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%';   // rodo 12.3%
    Sh.Cells[2, 2].Formula := '=A2';
    Sh.Cells[2, 2].NumberFormat := '0';      // rodo 3
    Sh.Cells[3, 2].Formula := '=A3';
    Sh.Cells[3, 2].NumberFormat := '0.0,';   // rodo 12.3 (tūkstančiai)

    // XLSX variklyje turi būti nustatyta prieš pirmąjį Recalculate
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Sukaupptieji rezultatai dabar atitinka Excel 16: 0.123, 3 ir 12300.
    // A stulpelio konstantos išlaiko pilną tikslumą.
    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'); // rašo <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Klasikinis variklis elgiasi taip pat, su vienu patogumu: TXLSWorkbook.UseFullPrecision priskyrimas kiekvieną priklausomybių grafiko formulę pažymi pasenusia, tad kitas Recalculate pagal naują taisyklę perskaičiuoja visą darbaknygę. NumberFormat keitimas, kol parinktis įjungta, taip pat pažymi paveiktas formulės langelius pasenusiais, nes dabar formatas nusprendžia saugomą reikšmę. Atkreipkite dėmesį, jog klasikinis Recalculate grąžina neįvertintų formulės langelių skaičių, tad nulis reiškia sėkmę:

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; // pažymi visas formules pasenusiomis
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: neigiama sekcija "(0.0)" rodo vieną dešimtainę
    // C1 lieka Boolean True (variantai iki v2.384.57 saugojo -1)
    Wb.SaveAs('report.xls'); // CalcPrecision įrašas su fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Abu varikliai gerbia ir vėliavėlę, atkeliaujančią su failu. Atvėrę su įjungta parinktimi išsaugotą darbaknygę, rasite FullPrecision arba UseFullPrecision jau False, tad Recalculate po įkėlimo apvalins tiksliai taip, kaip apvalintų Excel. Jei reikia tik perskaityti jau Excel saugomus skaičius, perskaičiavimą galite visai praleisti, kaip aprašyta straipsnyje sukaupptų formulės reikšmių skaitymas be perskaičiavimo. O kaip serijiniai skaičiai ir datos formatai sąveikauja su formato modeliu, varančiu datos/laiko patikrą – žiūrėkite Excel datos serijiniai skaičiai, 1904 sistema ir numFmt Delphi

Kada verta įjungti precision as displayed, o kada ne?

Precision as displayed įjunkite tik tada, kai darbaknygės saugomi skaičiai privalo būti lygūs jos rodomiesiems, o papildomų skaitmenų praradimą amžinai priimate. Klasikinis teisėtas atvejis – finansinis grafikas, kuriame apvalintų sumų stulpeliai turi susidėti iki apvalintos ekrane matomos sumos, be paslėptų cento trupmenų, gaminančių vienu paskutiniu skaitmeniu klaidingą sumą. Kliento turimos darbaknygės, kurioje parinktis jau nustatyta, atitikimas – kita gera priežastis, o HotXLS apvalinime vėliavėlę išlaiko, tad jų tyčia negrąžinsite prie pilno tikslumo

Daugumoje kitų situacijų venkite:

• Inžineriniai ir moksliniai duomenys. Matavimo apvalinimas todėl, kad kas nors ataskaitai pasirinko dviejų dešimtainių formatą, naikina informaciją, kurios joks vėlesnis formato keitimas neatkurs
• Procentai su stambiais formatais. 0% formatas iš saugomos santykio reikšmės laiko tik dvi dešimtaines, tad 0.1234 tampa 0.12, ir kiekviena tolesnė formulė, skaitanti langelį, dirba su 0.12
• Masteliniai rodiniai. 0, arba 0.0, formatas, naudotas tūkstančiams rodyti, saugomą reikšmę apvalina iki tūkstančių ar šimtų – retai kada to norėjo tas, kas formatą pasirinko
• Bendrinami šablonai. Vėliavėlė veikia visoje darbaknygėje. Bet kas, vėliau pridedantis lapą, paveldės tokį elgesį, dažniausiai nežinodamas, jog ji įjungta

Jei iš tikrųjų norite apvalintų rezultatų keliuose konkrečiuose langeliuose, vietoj to į tas formules įrašykite ROUND. ROUND aiškus, vietinis langeliui, matomas kiekvienam, skaitančiam formulę, ir vertinamas HotXLS formulių variklio kaip bet kuri kita funkcija, be jokių visos darbaknygės šalutinių padarinių

Precision as displayed trumpa atmintinė

• Failo vėliavėlė: CalcPrecision $000E su fFullPrec = 0 BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" XLSX (ECMA-376 Part 1)
• HotXLS jungikliai: TXLSXWorkbook.FullPrecision := False ir TXLSWorkbook.UseFullPrecision := False, abi pagal numatymą True
• Sekcija: renkama pagal apskaičiuotos reikšmės ženklą; trečioji sekcija – tik tiksliai nuliui
• Skaitmenys: dešimtainių vietų ženkleliai, plius du už %, minus trys už mastelio kablelį; skaičius gali būti neigiamas
• Apvalinimas: pusė nuo nulio su kelių ulp paklaida, tad 2.5 duoda 3, -2.5 duoda -3, o 1.005 duoda 1.01
• Praleidžiama: General, data/laikas ir praėjęs laikas, mokslinis, trupmena, tekstas, Boolean ir klaidų reikšmės
• Taikymo sritis HotXLS: formulės rezultatai, kai jie skaičiuojami; konstantos saugomos kaip priskirtos
• XLSX variklis: FullPrecision nustatykite prieš pirmąjį Recalculate; klasikinis setteris pats vėl sužymi visas formules pasenusiomis
• Versijos: suderinta su Excel 16 abiejuose varikliuose nuo v2.384.57; praėjusio laiko formatai saugomi nuo v2.384.9

HotXLS XLS ir XLSX darbaknyges iš Delphi ir C++Builder skaito, rašo ir skaičiuoja natyviai, įskaitant čia aptartas darbaknygės skaičiavimo parinktis. Detalės, leidimai ir bandomosios versijos atsiuntimas yra HotXLS Delphi skaičiuoklės komponento puslapyje