HotXLS Precision as displayed: Excels avrundningsregler

Excel precision as displayed avrundar varje lagrat tal till decimalerna som dess talformat visar: den formatsektion som matchar värdets tecken, två extra decimaler per %, tre färre per tusentals-skalningskommatecken, med avrundning av halva från noll. HotXLS tillämpar samma regel i båda sina Delphi-motorer när TXLSXWorkbook.FullPrecision eller TXLSWorkbook.UseFullPrecision är False. Det låter som en enraderare tills en kund rapporterar att dina exporterade fakturasummor skiljer sig från Excel med ett öre, eller att en kolumn av varaktigheter i [ss].00 kollapsade till noll. Båda hände, och båda spårades tillbaka till att en av de reglerna fått fel. Sedan v2.384.57 delar de två motorerna en enda implementation vars förväntade värden mättes i Excel 16 med Workbook.PrecisionAsDisplayed påslaget

Vad ändrar precision as displayed egentligen i en arbetsbok?

Precision as displayed är en enda flagga på arbetsboksnivå som talar om för beräkningsmotorn att lagra tal som de ser ut, inte som de beräknades. I Excel-gränssnittet sitter den under Arkiv, Alternativ, Avancerat, "Vid beräkning av den här arbetsboken", som "Ställ in precision som visas". På disken är det en bit. En BIFF8-fil bär den i CalcPrecision-posten ($000E, [MS-XLS] §2.4.35), vars fFullPrec-fält är 1 för normal full precision och 0 när alternativet är på. Ett XLSX-paket bär den som attributet fullPrecision på calcPr-elementet i workbook.xml, definierat i ECMA-376 del 1, där standardvärdet är true och fullPrecision="0" slår på avrundningen

Flaggan är ingen visningsinställning. Kryssar du i rutan varnar Excel för att data permanent förlorar noggrannhet, och det menar det: värden skrivs om till sin visade precision, och de siffror som klipptes av är borta. Att rensa rutan senare ger inte tillbaka de gamla siffrorna. En 0.1234 visad som 12.3% blir 0.123 för alltid

HotXLS läser och skriver flaggan i båda formaten och exponerar den i båda motorer:

• TXLSXWorkbook.FullPrecision: Boolean på XLSX-motorn, läst från och sparad till calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean på den klassiska motorn (också på IXLSWorkbook), läst från och sparad till CalcPrecision-posten
• Båda har standardvärdet True, vilket är det säkra, icke-förstörande läget och Excels standard

Var HotXLS tillämpar avrundningen spelar roll. HotXLS avrundar vid den punkt där det beräknar ett värde: varje formelresultat avrundas till sin visade precision innan det lagras som cellens cachade värde, under Recalculate och under utvärdering vid behov. Konstanter du tilldelar via Value lagras exakt som givna. Om din utdata måste reproducera vad Excel lagrar efter att rutan kryssats i, avrunda de konstanterna själv innan du skriver dem, till exempel med hjälparen som visas senare

Hur avgör Excel hur många decimaler som ska behållas?

Excel härleder antalet behållna decimaler från den specifika formatsektion som visar värdet, inte från formatsträngen som helhet. Reglerna nedan mättes i Excel 16 och är vad XlsApplyDisplayedPrecision i lxNumFormat implementerar för båda HotXLS-motorer

1. Välj sektionen efter tecken. Ett format med två sektioner använder den andra sektionen för negativa värden. Ett format med tre eller fler sektioner använder den andra för negativa värden och den tredje för exakt noll. Allt annat använder den första sektionen
2. Räkna decimalplatshållarna. Varje 0, # eller ? efter decimaltecknet i den sektionen lägger till en behållen decimal
3. Lägg till två per procenttecken. 0.0% visar 0.1234 som 12.3%, så det lagrade värdet är en hundradel av det du ser och behåller tre decimaler, inte en
4. Subtrahera tre per skalningskommatecken. Ett kommatecken efter den sista heltalsplatshållaren (0,, 0.0,, 0,.0) delar visningen med 1000. 0.0, visar 12345.678 som 12.3, så Excel behåller en decimal minus tre, vilket är ett negativt antal: värdet avrundas till hundratalen och lagras som 12300. Ett kommatecken mellan heltalsplatshållare, som i #,##0, är ren siffergruppering och ändrar ingenting
5. Lämna icke-numeriska sektioner ifred. General, datum- och tidssektioner (inklusive förflutna [h], [mm] och [ss]), vetenskapliga, bråk- och textsektioner, och sektioner utan någon sifferplatshållare behåller full precision

Uppmätt mot Excel 16 är dessa de värden båda HotXLS-motorer nu lagrar för ett formelresultat i varje format:

Talformat	Beräknat värde	Lagrat värde	Regel som gäller
0.0%	0.1234	0.123	En decimal plus två för procenttecknet
0	2.5	3	Halva från noll, inte till jämnt
0	-2.5	-3	Halva från noll på den negativa sidan också
0.00;(0.0)	-1.2345	-1.2	Den negativa sektionen visar en decimal
0.00;(0.0)	1.2345	1.23	Den positiva sektionen visar två decimaler
#,##0.0	1234.5678	1234.6	Grupperingskommatecken, ingen skalning
0.0,	12345.678	12300	En decimal minus tre: avrunda till hundratalen
0.0%;(0.00%)	-0.0125	-0.0125	Den negativa sektionen behåller två plus två decimaler
0.00	1.005	1.01	Tolerans för binärt representeringsfel
0;-0;0.0	0.5	1	Inte noll, så den positiva sektionen avgör

Sista raden är en fin fälla. Värdet 0.5 avrundas till ett heltal, och nollsektionen kommer aldrig in i bilden, för Excel väljer sektionen från det beräknade värdet innan avrundning. En ärlig begränsning på HotXLS-sidan: sektioner väljs bara efter tecken, så ett format vars sektioner bär egna hakparentesvillkor som [>=1000] delas ändå efter tecken. Kontrollera sådana format mot Excel om de spelar roll för dig

Varför avrundas 1.005 till 1.01 och inte till 1.00?

Excel avrundar 1.005 i en 0.00-cell till 1.01 trots att doubeln närmast 1.005 ligger strax under mittpunkten, och HotXLS matchar det med en tolerans på några ulp. Literalen 1.005 kan inte representeras i binär flyttal. Den närmaste IEEE 754-doubeln är 1.00499999999999989341858963598497211933135986328125, och multiplikation med 100 ger 100.49999999999999. En läroboks-Floor(x * 100 + 0.5) / 100 returnerar därför 1.00, vilket strider mot talet användaren skrev, mot vad Excel visar och mot vad Excel lagrar

Delphi lägger till sin egen vri. System.Round avrundar oavgjort till jämnt, så Round(2.5) är 2 och Round(3.5) är 4. Det är bankiravrundning, ett rimligt standardval för statistik och fel regel här: Excel lagrar 3 för 2.5 i en 0-cell och -3 för -2.5. HotXLS-implementationen arbetar på absolutvärdet, lägger till 0.5 plus en relativ tolerans av 2^-51 gånger det skalade värdet (några ulp vid den storleken, aldrig mindre än två ulp av 1.0), trunkerar, skalar tillbaka och återställer tecknet. Följande funktion är en fristående illustration av den principen, inte bibliotekskoden själv, och den hanterar negativa sifferantal för skalningskommatecken på samma sätt:

// Principiell skiss: avrunda halva från noll till ADigits decimaler,
// med en tolerans på några ulp så att 1.005 når 1.01.
// ADigits < 0 avrundar till tiotal, hundratal, ... ("0.0," ger -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, två ulp av 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // bortom dubbel precision: lämna värdet ifred
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // skalning skulle spilla över
    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); // halva från noll, inte 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-baserad: 1.00)
// RoundAsDisplayed(2.5, 0)        = 3      (Round: 2)
// RoundAsDisplayed(-2.5, 0)       = -3
// RoundAsDisplayed(0.1234, 3)     = 0.123  ("0.0%": 1 + 2 siffror)
// RoundAsDisplayed(12345.678, -2) = 12300  ("0.0,": 1 - 3 siffror)

Toleransen är en medveten avvägning. Ett värde som verkligen ligger två ulp under ett halvsteg avrundas också uppåt, men på det avståndet är skillnaden omöjlig att skilja från representeringsfel, och att behandla det som ett halvsteg är det som gör att inskrivna decimaler uppför sig som användarna förväntar sig

Vad gick fel före v2.384.57?

Före v2.384.57 hade XLSX-motorn och den klassiska motorn var sin precision-as-displayed-kod, och var och en var fel på sitt sätt. Producerar du arbetsböcker med alternativet på är dessa symptomen att leta efter i filer genererade av äldre byggen

XLSX-motorn: bara första sektionen, ingen procent, bankiravrundning

Den gamla XLSX-vägen begärde decimalantalet för formatsträngen som helhet, som bara tittade på första sektionen och ignorerade %, och avrundade sedan med Round. En 0.1234 i 0.0% lagrades som 0.1, vilket är 10% i stället för de 12.3% på skärmen. En 2.5 i 0 lagrades som 2 i stället för 3. Negativa värden i ett format som 0.00;(0.0) avrundades till den positiva sektionens två decimaler. Sedan v2.384.57 anropar XLSX-motorn samma delade rutin som den klassiska motorn, som också fick stöd för skalningskommatecken i den utgåvan

Klassiska motorn: TRUE blev -1

Den klassiska motorn vaktede sin avrundning med VarIsNumeric, och VarIsNumeric returnerar True för en varBoolean-Variant. Konverterar man den Varianten med Double(V) blir det -1, för ett booleskt True i COM-stil lagras som -1. En formel som =A1>0 i en cell formaterad 0.00 kom därför ut ur omräkningen som talet -1. Sedan v2.384.57 utesluts booleska resultat före varje numerisk kontroll, och ett logiskt resultat förblir ett logiskt resultat i båda motorer

Förfluten-tid-format lästa som färger (v2.384.9)

Den tredje buggen satt i talformatsmodellen snarare än i avrundningen. Parsaren klassificerade varje hakparenteserad token som inte var ett villkor som en färg, så [h], [mm] och [ss] märkte aldrig sin sektion som datum/tid. Visningen var opåverkad, för formateringen kör på en separat väg, men precision as displayed förlitar sig på den flaggan för att hoppa över tidsvärden. En varaktighet på fem sekunder är 5/86400 av en dag, ungefär 0.0000579, och ett format som [ss].00 såg ut som ett vanligt tvådecimalstal, så med FullPrecision av avrundades varaktigheten till 0.00 dagar. Sedan v2.384.9 parsas en hakparenteserad följd av en enda h-, m- eller s-bokstav som en förfluten-tid-token och sektionen behandlas som datum/tid. Samma utgåva fixade minutdetektering i h:mm, där kolon mellan tokenen brukade gömma timmen för parsaren

Slå på precision as displayed i HotXLS från Delphi

För att få Excel-motsvarande lagrade värden, sätt flaggan före den omräkning som ska hedra den, läs sedan de cachade resultaten eller spara. På XLSX-motorn är FullPrecision en enkel flagga: att ändra den ogiltigförklarar inte resultat som en tidigare Recalculate redan lagrat, så sätt den direkt efter Create eller Open och före den första Recalculate. Exemplet använder formler för det är där HotXLS tillämpar avrundningen:

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

    // Måste sättas före den första Recalculate på XLSX-motorn
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // Cachade resultat stämmer nu med Excel 16: 0.123, 3 och 12300.
    // Konstanterna i kolumn A behåller sin fulla precision.
    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'); // skriver <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Den klassiska motorn beter sig likadant, med en bekvämlighet: att tilldela TXLSWorkbook.UseFullPrecision markerar varje formel i beroendegrafen smutsig, så nästa Recalculate utvärderar hela arbetsboken på nytt enligt den nya regeln. Att ändra en NumberFormat medan alternativet är på markerar också de berörda formelcellerna smutsiga, för formatet avgör nu det lagrade värdet. Observera att den klassiska Recalculate returnerar antalet formelceller den inte kunde utvärdera, så noll betyder framgång:

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; // markerar varje formel smutsig
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: den negativa sektionen "(0.0)" visar en decimal
    // C1 förblir booleskt True (byggen före v2.384.57 lagrade -1)
    Wb.SaveAs('report.xls'); // CalcPrecision-post med fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Båda motorer hedrar också flaggan som kommer in med en fil. Öppna en arbetsbok sparad med alternativet på och FullPrecision eller UseFullPrecision är redan False, så en Recalculate efter inläsning avrundar precis som Excel skulle. Behöver du bara läsa talen Excel redan lagrat kan du hoppa över omräkningen helt, som beskrivs i läsning av cachade formelvärden utan omräkning. För hur serienummer och datumformat samspelar med den formatsmodell som driver datum/tid-kontrollen, se Excel-datuserier, 1904-systemet och numFmt i Delphi

När ska du slå på precision as displayed, och när inte?

Slå på precision as displayed bara när arbetsbokens lagrade tal måste vara lika med dess visade tal, och du accepterar att förlora de extra siffrorna för alltid. Det klassiska legitima fallet är ett finansiellt schema där kolumner av avrundade belopp måste summera till den avrundade summan på skärmen, utan dolda bråkdelar av ett öre som ger en summa som är av med ett på sista siffran. Att matcha en kunds befintliga arbetsbok som redan har alternativet satt är det andra goda skälet, och HotXLS bevarar flaggan vid rundturn så att du inte tyst ställer tillbaka dem till full precision

Undvik det i de flesta andra situationer:

• Teknisk och vetenskaplig data. Att avrunda en mätning för att någon valde ett tvådecimalsformat för en rapport förstör information som ingen senare formatändring kan återställa
• Procent med grova format. Ett 0%-format behåller bara två decimaler av den lagrade kvoten, så 0.1234 blir 0.12, och varje formel nedströms som läser cellen arbetar med 0.12
• Skalade visningar. Ett 0, eller 0.0,-format använt för att visa tusental avrundar det lagrade värdet till tusentalen eller hundratalen, vilket sällan är vad personen som valde formatet avsåg
• Delade mallar. Flaggan gäller hela arbetsboken. Vem som helst som senare lägger till ett blad ärver beteendet, oftast utan att veta att det är på

Vill du egentligen ha avrundade resultat i några specifika celler, skriv ROUND i de formlerna i stället. ROUND är explicit, lokal för cellen, synlig för vem som helst som läser formeln, och utvärderas av HotXLS formelmotor som vilken annan funktion som helst, utan arbetsboksvida sidoeffekter

Snabbreferens för precision as displayed

• Filflagga: CalcPrecision $000E med fFullPrec = 0 i BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" i XLSX (ECMA-376 del 1)
• HotXLS-brytare: TXLSXWorkbook.FullPrecision := False och TXLSWorkbook.UseFullPrecision := False, båda med standard True
• Sektion: vald efter det beräknade värdets tecken; tredje sektionen bara för exakt noll
• Siffror: decimalplatshållare, plus två per %, minus tre per skalningskommatecken; antalet kan vara negativt
• Avrundning: halva från noll med en tolerans på några ulp, så 2.5 ger 3, -2.5 ger -3 och 1.005 ger 1.01
• Hoppas över: General, datum/tid och förfluten tid, vetenskapligt, bråk, text, booleska och felvärden
• Omfattning i HotXLS: formelresultat när de beräknas; konstanter lagras som tilldelade
• XLSX-motorn: sätt FullPrecision före den första Recalculate; den klassiska sättaren gör alla formler smutsiga igen själv
• Versioner: matchad mot Excel 16 i båda motorer sedan v2.384.57; förfluten-tid-format skyddade sedan v2.384.9

HotXLS läser, skriver och räknar XLS- och XLSX-arbetsböcker nativt från Delphi och C++Builder, inklusive de beräkningsoptioner för arbetsböcker som tas upp här. Detaljer, utgåvor och nedladdningen av testversionen finns på sidan för HotXLS Delphi spreadsheet component