HotXLS:n tarkkuus näytettyinä: Excelin pyöristyssäännöt

Excelin tarkkuus näytettyinä pyöristää jokaisen tallennetun luvun desimaaleihin, jotka sen lukumuoto näyttää: arvon etumerkin mukaan valittu muoto-osa, kaksi lisädesimaalia jokaista %-merkkiä kohden, kolme vähemmän jokaista tuhansia skaalaavaa pilkkua kohden, pyöristys puolen välin yli nollasta poispäin. HotXLS soveltaa samaa sääntöä kummassakin Delphi-moottorissaan, kun TXLSXWorkbook.FullPrecision tai TXLSWorkbook.UseFullPrecision on False. Se kuulostaa yhden rivin jutuelta, kunnes asiakas ilmoittaa, että viedyn laskun yhteissumat eroavat Excelistä sentin verran, tai että [ss].00-muotoisten kestojen sarake luhistui nollaan. Molemmat tapahtuivat, ja molemmat johtuivat siitä, että jokin kyseisistä säännöistä oli saatu väärin. Versiosta v2.384.57 alkaen kaksi moottoria jakavat yhden toteutuksen, jonka odotusarvot mitattiin Excel 16:ssa, kun Workbook.PrecisionAsDisplayed oli päällä

Mitä tarkkuus näytettyinä oikeasti muuttaa työkirjassa?

Tarkkuus näytettyinä on yksi työkirjatason flagi, joka kertoo laskentamoottorille tallennettavan luvut niin kuin ne näyttävät, ei niin kuin ne laskettiin. Excelin käyttöliittymässä se istuu kohdassa Tiedosto, Asetukset, Lisäasetukset, "Kun tätä työkirjaa lasketaan", nimellä "Määritä tarkkuus näytettyinä". Levylle se on yksi bitti. BIFF8-tiedosto kantaa sen CalcPrecision-tietueessa ($000E, [MS-XLS] §2.4.35), jonka fFullPrec-kenttä on 1 normaalia täyttä tarkkuutta varten ja 0, kun asetus on päällä. XLSX-paketti kantaa sen fullPrecision-attribuuttina workbook.xmlin calcPr-elementissä, määriteltynä ECMA-376 Part 1:ssä, missä oletus on tosi ja fullPrecision="0" kytkee pyöristyksen päälle

Flagi ei ole näkymäasetus. Kun laitat ruksin ruutuun, Excel varoittaa, että data menettää tarkkuuttaan pysyvästi, ja se tarkoittaa sitä: arvot kirjoitetaan uudelleen näytetylle tarkkuudelleen, ja katkaistut numerot ovat poissa. Ruksin poistaminen myöhemmin ei tuo vanhoja numeroita takaisin. Arvo 0.1234, joka näkyy muodossa 12.3%, muuttuu arvoksi 0.123 lopullisesti

HotXLS lukee ja kirjoittaa flagin molemmissa muodoissa ja paljastaa sen kummassakin moottorissa:

• TXLSXWorkbook.FullPrecision: Boolean XLSX-moottorissa, ladattu kohteesta ja tallennettu kohteeseen calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean klassisessa moottorissa (myös IXLSWorkbookissa), ladattu CalcPrecision-tietueesta ja tallennettu siihen
• Molempien oletus on True, turvallinen, tuhoamaton tila ja Excelin oletus

Sillä, missä HotXLS soveltaa pyöristystä, on merkitystä. HotXLS pyöristää sillä hetkellä, kun se laskee arvon: jokainen kaavan tulos pyöristetään näytetylle tarkkuudelleen ennen kuin se tallennetaan solun välimuistitetuksi arvoksi, Recalculaten aikana ja tarvittaessa tapahtuvan laskennan aikana. Vakiot, jotka sijoitat kohteen Value kautta, tallennetaan täsmälleen annettuina. Jos tuotoksesi on toistettava se, mitä Excel tallentaa ruksin jälkeen, pyöristä kyseiset vakiot itse ennen kuin kirjoitat ne, esimerkiksi myöhemmin näytettävällä apurilla

Miten Excel päättää, kuinka monta desimaalia säilytetään?

Excel johtaa säilytettävien desimaalien määrän muoto-osasta, joka näyttää arvon, ei muotoilumerkkijonosta kokonaisuutena. Alla olevat säännöt mitattiin Excel 16:ssa, ja ne ovat se, mitä lxNumFormatin XlsApplyDisplayedPrecision toteuttaa kummallekin HotXLS-moottorille

1. Valitse osa etumerkin mukaan. Kaksiosainen muoto käyttää toista osaa negatiivisille arvoille. Muoto, jossa on kolme tai useampi osa, käyttää toista negatiivisille ja kolmatta täsmälleen nollalle. Kaikki muu käyttää ensimmäistä osaa
2. Laske desimaalien paikanpitäjät. Jokainen kyseisen osan desimaalipisteen jälkeinen 0, # tai ? lisää yhden säilytettävän desimaalin
3. Lisää kaksi jokaista prosenttimerkkiä kohden. 0.0% näyttää 0.1234:n muodossa 12.3%, joten tallennettu arvo on sadasosa siitä, mitä näet, ja säilyttää kolme desimaalia, ei yhtä
4. Vähennä kolme jokaista skaalauspilkkua kohden. Pilkku viimeisen kokonaislukupaikanpitäjän jälkeen (0,, 0.0,, 0,.0) jakaa näytön 1000:lla. 0.0, näyttää 12345.678:n muodossa 12.3, joten Excel säilyttää yhden desimaalin miinus kolme, mikä on negatiivinen määrä: arvo pyöristetään sadoiksi ja tallennetaan muodossa 12300. Pilkku kokonaislukupaikanpitäjien välissä, kuten muodossa #,##0, on pelkkää numeroryhmittelyä eikä muuta mitään
5. Jätä ei-numeeriset osat rauhaan. General-, päivämäärä- ja aikaosat (mukaan lukien kulunut aika [h], [mm] ja [ss]), tieteelliset, murto- ja tekstiosat sekä osat ilman mitään numeropaikanpitäjää säilyttävät täyden tarkkuuden

Excel 16:een mitattuna nämä ovat arvot, jotka molemmat HotXLS-moottorit nykyään tallentavat kaavan tulokselle kussakin muodossa:

Lukumuoto	Laskettu arvo	Tallennettu arvo	Sääntö, jota sovelletaan
0.0%	0.1234	0.123	Yksi desimaali plus kaksi prosenttimerkin takia
0	2.5	3	Puolen välin yli nollasta poispäin, ei parilliseen
0	-2.5	-3	Puolen välin yli nollasta poispäin myös negatiivisella puolella
0.00;(0.0)	-1.2345	-1.2	Negatiivinen osa näyttää yhden desimaalin
0.00;(0.0)	1.2345	1.23	Positiivinen osa näyttää kaksi desimaalia
#,##0.0	1234.5678	1234.6	Ryhmittelypilkku, ei skaalausta
0.0,	12345.678	12300	Yksi desimaali miinus kolme: pyöristä sadoiksi
0.0%;(0.00%)	-0.0125	-0.0125	Negatiivinen osa säilyttää kaksi plus kaksi desimaalia
0.00	1.005	1.01	Sieto binäärisen esityksen virheelle
0;-0;0.0	0.5	1	Ei nolla, joten positiivinen osa ratkaisee

Viimeinen rivi on mukava ansa. Arvo 0.5 pyöristyy kokonaisluvuksi, eikä nollaosa koskaan tule kuvaan, koska Excel poimii osan lasketusta arvosta ennen pyöristystä. Yksi rehellinen raja HotXLS:n puolella: osat valitaan vain etumerkillä, joten muoto, jonka osat kantavat omia hakasulkeehtoja kuten [>=1000], jaetaan silti etumerkillä. Tarkista tällaiset muodot Exceliä vasten, jos ne merkitsevät sinulle

Miksi 1.005 pyöristyy arvoon 1.01 eikä arvoon 1.00?

Excel pyöristää arvon 1.005 0.00-solussa arvoon 1.01, vaikka lähin double-arvo luvulle 1.005 on hieman puolivälin alapuolella, ja HotXLS vastaa sitä muutaman ulpin siedolla. Literaalia 1.005 ei voi esittää binääriliukulukuna. Lähin IEEE 754 double on 1.00499999999999989341858963598497211933135986328125, ja kertolasku 100:lla antaa arvon 100.49999999999999. Oppikirjamainen Floor(x * 100 + 0.5) / 100 palauttaa siksi arvon 1.00, mikä eriää käyttäjän kirjoittamasta luvusta, Excelin näyttämästä ja Excelin tallentamasta

Delphi lisää oman käänteensä. System.Round pyöristää tasapelit parilliseen, joten Round(2.5) on 2 ja Round(3.5) on 4. Kyseessä on pankkiirin pyöristys, järkevä oletus tilastotieteessä ja väärä sääntö tässä: Excel tallentaa 3:n arvolle 2.5 0-solussa ja -3:n arvolle -2.5. HotXLS:n toteutus toimii itseisarvolla, lisää 0.5:n plus suhteellisen siedon 2^-51 kertaa skaalattu arvo (muutama ulp tuolla suuruusluokalla, ei koskaan vähemmän kuin kaksi ulpia luvusta 1.0), typistää, skaalaa takaisin ja palauttaa etumerkin. Seuraava funktio on itsenäinen havainnollistus kyseisestä periaatteesta, ei itse kirjastokoodi, ja se käsittelee negatiivisia numeromääriä skaalauspilkuille samalla tavalla:

// Periaatepiirros: pyöristä puolen välin yli nollasta poispäin ADigits desimaaliin,
// muutaman ulpin siedolla, jotta 1.005 yltää arvoon 1.01.
// ADigits < 0 pyöristää kymmeniksi, sadoiksi, ... ("0.0," antaa -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, kaksi ulpia luvusta 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // double-tarkkuuden yli: jätä arvo rauhaan
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // skaalaus vuotaisi yli
    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); // puolen välin yli nollasta poispäin, ei 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-pohjainen: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 numeroa)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 numeroa)

Sieto on tahallinen kompromissi. Arvo, joka on aidosti kaksi ulpia puolen askeleen alapuolella, pyöristyy myös ylöspäin, mutta tuolla etäisyydellä ero on erottelematon esitysvirheestä, ja sen käsittely puolen askeleena on se, mikä saa kirjoitetut desimaalit käyttäytymään niin kuin käyttäjät odottavat

Mitä meni pieleen ennen versiota v2.384.57?

Ennen versiota v2.384.57 XLSX-moottorilla ja klassisella moottorilla oli kummallakin oma tarkkuus-näytettyinä-koodinsa, ja kumpikin oli väärin eri tavalla. Jos tuotat työkirjoja asetuksen ollessa päällä, nämä ovat oireet, joita kannattaa etsiä vanhempien koontiversioiden tuottamista tiedostoista

XLSX-moottori: vain ensimmäinen osa, ei prosenttia, pankkiirin pyöristys

Vanha XLSX-polku kysyi muotoilumerkkijonon desimaalimäärän kokonaisuutena, mikä katsoi vain ensimmäistä osaa ja ohitti %-merkin, ja pyöristi sitten funktiolla Round. Arvo 0.1234 muodossa 0.0% tallennettiin arvona 0.1, joka on 10% ruudulla näkyvän 12.3%:n sijaan. Arvo 2.5 muodossa 0 tallennettiin arvona 2 eikä 3. Negatiiviset arvot muodossa kuten 0.00;(0.0) pyöristettiin positiivisen osan kahteen desimaaliin. Versiosta v2.384.57 alkaen XLSX-moottori kutsuu samaa jaettua rutiinia kuin klassinen moottori, joka sai myös skaalauspilkkutuen kyseisessä julkaisussa

Klassinen moottori: TRUE muuttui arvoksi -1

Klassinen moottori varjeli pyöristystään funktiolla VarIsNumeric, ja VarIsNumeric palauttaa Truen varBoolean-Variantille. Kyseisen Variantin muuntaminen funktiolla Double(V) antaa -1:n, koska COM-tyylinen looginen True on tallennettu -1:nä. Kaava kuten =A1>0 solussa, jonka muoto on 0.00, tuli siis uudelleenlaskennasta luvuna -1. Versiosta v2.384.57 alkaen loogiset tulokset suljetaan pois ennen mitään numeerista testiä, ja looginen tulos pysyy loogisena tuloksena kummassakin moottorissa

Kuluneen ajan muodot luettiin väreinä (v2.384.9)

Kolmas vika istui lukumuotimallissa pyöristyksen sijaan. Jäsentäjä luokitteli jokaisen hakasulkeisen tokenin, joka ei ollut ehto, väriksi, joten [h], [mm] ja [ss] eivät koskaan merkinneet osaansa päivämäärä/ajaksi. Näyttö ei kärsinyt, koska muotoilu ajetaan eri polulla, mutta tarkkuus näytettyinä nojaa kyseiseen flagiin ohittaakseen aika-arvot. Viiden sekunnin kesto on 5/86400 päivästä, noin 0.0000579, ja muoto kuten [ss].00 näytti tavalliselta kaksidesimaaliselta luvulta, joten ollessa FullPrecision pois päältä kesto pyöristettiin arvoon 0.00 päivää. Versiosta v2.384.9 alkaen hakasulkeinen yksittäisen h-, m- tai s-kirjaimen jakso jäsennetään kuluneen ajan tokenina, ja osa käsitellään päivämäärä/ajana. Sama julkaisu korjasi minuutin tunnistuksen muodossa h:mm, jossa tokenien välinen kaksoispiste piilotti tunnin jäsentäjältä

Tarkkuus näytettyinä käyttöön HotXLS:ssä Delphistä

Saadaksesi Excelin kaltaiset tallennetut arvot, aseta flagi ennen uudelleenlaskentaa, jonka pitää noudattaa sitä, ja lue sitten välimuistitetut tulokset tai tallenna. XLSX-moottorissa FullPrecision on pelkkä flagi: sen muuttaminen ei mitätöi tuloksia, jotka aiempi Recalculate jo tallensi, joten aseta se heti Createn tai Openin jälkeen ja ennen ensimmäistä Recalculatea. Esimerkki käyttää kaavoja, koska siellä HotXLS soveltaa pyöristystä:

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

    // On asetettava ennen ensimmäistä Recalculatea XLSX-moottorissa
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Välimuistitetut tulokset vastaavat nyt Excel 16:ta: 0.123, 3 ja 12300.
    // Sarakkeen A vakiot säilyttävät täyden tarkkuutensa.
    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'); // kirjoittaa <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Klassinen moottori käyttäytyy samoin, yhdellä mukavuudella: kohteen TXLSWorkbook.UseFullPrecision sijoittaminen merkitsee jokaisen riippuvuusgraafin kaavan likaiseksi, joten seuraava Recalculate laskee koko työkirjan uudelleen uudella säännöllä. NumberFormatin muuttaminen asetuksen ollessa päällä merkitsee myös kyseiset kaavasolut likaisiksi, koska muoto päättää nyt tallennetun arvon. Huomaa, että klassinen Recalculate palauttaa niiden kaavasolujen määrän, joita se ei pystynyt laskemaan, joten nolla tarkoittaa onnistumista:

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; // merkitsee jokaisen kaavan likaiseksi
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: negatiivinen osa "(0.0)" näyttää yhden desimaalin
    // C1 pysyy loogisena Truena (koontiversiot ennen v2.384.57:tä tallensivat -1)
    Wb.SaveAs('report.xls'); // CalcPrecision-tietue, jolla fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Molemmat moottorit noudattavat myös flagia, joka tulee tiedoston mukana. Avaa työkirja, joka on tallennettu asetuksen ollessa päällä, ja FullPrecision tai UseFullPrecision on jo False, joten latauksen jälkeinen Recalculate pyöristää täsmälleen niin kuin Excel tekisi. Jos tarvitset vain lukea numerot, jotka Excel jo tallensi, voit ohittaa uudelleenlaskennan kokonaan, kuten artikkelissa välimuistitettujen kaava-arvojen lukeminen ilman uudelleenlaskentaa kuvataan. Siitä, miten sarjanumerot ja päivämäärämuodot ovat vuorovaikutuksessa muotimallin kanssa, joka ohjaa päivämäärä/aika-tarkistusta, kerrotaan artikkelissa Excelin päivämääräsarjanumerot, 1904-järjestelmä ja numFmt Delphissä

Milloin tarkkuus näytettyinä kannattaa kytkeä päälle, ja milloin ei?

Kytke tarkkuus näytettyinä päälle vain, kun työkirjan tallennettujen lukujen on oltava yhtä suuret kuin sen näytettyjen lukujen, ja hyväksyt ylimääräisten numeroiden menettämisen ikuisesti. Klassinen perusteltu tapaus on talousaikataulu, jossa pyöristettyjen summien sarakkeiden on laskuttava yhteen ruudulla näkyvän pyöristetyn yhteissuman kanssa, ilman piilotettuja sentin murto-osia, jotka tuottavat yhdessä viimeisessä kohdassa harhautuvan yhteissuman. Asiakkaan olemassa olevan työkirjan vastaaminen, jossa asetus on jo päällä, on toinen hyvä syy, ja HotXLS säilyttää flagin kierrosten yli, joten et vaihda niitä hiljaisesti takaisin täyteen tarkkuuteen

Vältä sitä useimmissa muissa tilanteissa:

• Insinöörilaskenta ja tieteellinen data. Mittauksen pyöristys siksi, että joku valitsi kaksidesimaalisen muodon raportille, tuhoaa tietoa, jota mikään myöhempi muodon muutos ei palauta
• Prosentit karkeilla muodoilla. Muoto 0% säilyttää vain kaksi desimaalia tallennetusta suhteesta, joten 0.1234 muuttuu arvoksi 0.12, ja jokainen alaspäin suuntautuva kaava, joka lukee solun, työskentelee arvolla 0.12
• Skaalatut näytöt. Muoto 0, tai 0.0,, jota käytetään näyttämään tuhannet, pyöristää tallennetun arvon tuhansiksi tai sadoiksi, mikä on harvoin se, mitä muodon valinnut henkilö tarkoitti
• Jaetut mallipohjat. Flagi on työkirjanlaajuinen. Kuka tahansa, joka myöhemmin lisää taulukon, perii käytöksen, yleensä tietämättä, että se on päällä

Jos oikeasti haluat pyöristettyjä tuloksia muutamaan tiettyyn soluun, kirjoita sen sijaan ROUND kyseisiin kaavoihin. ROUND on eksplisiittinen, solukohtainen, näkyvä kaikille kaavan lukijoille, ja HotXLS:n kaavamoottori laskee sen kuten minkä tahansa muun funktion, ilman työkirjanlaajuisia sivuvaikutuksia

Tarkkuus näytettyinä -pikaopas

• Tiedostoflagi: CalcPrecision $000E, jolla fFullPrec = 0, BIFF8:ssa ([MS-XLS] §2.4.35), calcPr fullPrecision="0" XLSX:ssä (ECMA-376 Part 1)
• HotXLS:n kytkimet: TXLSXWorkbook.FullPrecision := False ja TXLSWorkbook.UseFullPrecision := False, molempien oletus True
• Osa: valitaan lasketun arvon etumerkillä; kolmas osa vain täsmälleen nollalle
• Numerot: desimaalipaikanpitäjät, plus kaksi jokaista %ä kohden, miinus kolme jokaista skaalauspilkkua kohden; määrä voi olla negatiivinen
• Pyöristys: puolen välin yli nollasta poispäin muutaman ulpin siedolla, joten 2.5 antaa 3:n, -2.5 antaa -3:n ja 1.005 antaa 1.01:n
• Ohitetaan: General, päivämäärä/aika ja kulunut aika, tieteellinen, murto-osa, teksti, loogiset arvot ja virhearvot
• Laajuus HotXLS:ssä: kaavojen tulokset niin kuin ne lasketaan; vakiot tallennetaan sellaisina kuin ne sijoitetaan
• XLSX-moottori: aseta FullPrecision ennen ensimmäistä Recalculatea; klassisen moottorin asettaja merkitsee itse kaikki kaavat likaisiksi uudelleen
• Versiot: sovitettu Excel 16:een kummassakin moottorissa versiosta v2.384.57 alkaen; kuluneen ajan muodot suojattu versiosta v2.384.9 alkaen

HotXLS lukee, kirjoittaa ja laskee XLS- ja XLSX-työkirjoja natiivisti Delphistä ja C++Builderista, mukaan lukien tässä käsitellyt työkirjan laskenta-asetukset. Yksityiskohdat, versiot ja kokeilulataus ovat HotXLS Delphi -laskentataulukkokomponentin sivulla