Precision as displayed in HotXLS: come arrotonda Excel

La precision as displayed di Excel arrotonda ogni numero memorizzato ai decimali che il suo formato numerico mostra: la sezione di formato che combacia col segno del valore, due decimali in più per ogni %, tre in meno per ogni virgola di scala alle migliaia, arrotondando il mezzo lontano da zero. HotXLS applica la stessa regola in entrambi i suoi motori Delphi quando TXLSXWorkbook.FullPrecision o TXLSWorkbook.UseFullPrecision è False. Sembra una riga di codice, finché un cliente non segnala che i totali delle tue fatture esportate divergono da Excel di un centesimo, o che una colonna di durate in [ss].00 è crollata a zero. Sono successe entrambe, e entrambe risalgono a una di quelle regole sbagliata. Dalla v2.384.57 i due motori condividono una singola implementazione i cui valori attesi sono stati misurati in Excel 16 con Workbook.PrecisionAsDisplayed acceso

Che cosa cambia davvero precision as displayed in una cartella di lavoro?

La precision as displayed è un flag unico a livello di cartella di lavoro che dice al motore di calcolo di memorizzare i numeri come appaiono, non come erano stati calcolati. Nella UI di Excel sta sotto File, Opzioni, Avanzate, "Quando si calcola questa cartella di lavoro", come "Imposta precisione come visualizzato". Su disco è un bit. Un file BIFF8 lo porta nel record CalcPrecision ($000E, [MS-XLS] §2.4.35), il cui campo fFullPrec è 1 per la normale piena precisione e 0 quando l'opzione è attiva. Un pacchetto XLSX lo porta come attributo fullPrecision dell'elemento calcPr in workbook.xml, definito in ECMA-376 Parte 1, dove il default è true e fullPrecision="0" accende l'arrotondamento

Il flag non è una preferenza di visualizzazione. Quando spunti la casella, Excel avverte che i dati perderanno precisione in modo permanente, e lo pensa sul serio: i valori vengono riscritti alla loro precisione visualizzata, e le cifre tagliate sono andate. Deselezionare la casella dopo non riporta indietro le vecchie cifre. Uno 0.1234 mostrato come 12.3% diventa 0.123 per sempre

HotXLS legge e scrive il flag in entrambi i formati e lo espone in entrambi i motori:

• TXLSXWorkbook.FullPrecision: Boolean sul motore XLSX, caricato da e salvato in calcPr/@fullPrecision
• TXLSWorkbook.UseFullPrecision: Boolean sul motore Classic (anche su IXLSWorkbook), caricato da e salvato nel record CalcPrecision
• Entrambi hanno default True, che è la modalità sicura e non distruttiva e il default di Excel

Dove HotXLS applica l'arrotondamento conta. HotXLS arrotonda nel punto in cui calcola un valore: ogni risultato di formula viene arrotondato alla sua precisione visualizzata prima di essere memorizzato come valore in cache della cella, durante Recalculate e durante la valutazione on-demand. Le costanti che assegni tramite Value vengono memorizzate esattamente come fornite. Se il tuo output deve riprodurre ciò che Excel memorizza dopo che la casella è stata spuntata, arrotonda quelle costanti da te prima di scriverle, per esempio con l'helper mostrato più avanti

Come decide Excel quanti decimali conservare?

Excel ricava il numero di decimali da conservare dalla specifica sezione di formato che mostra il valore, non dalla stringa di formato nel suo complesso. Le regole qui sotto sono state misurate in Excel 16 e sono ciò che XlsApplyDisplayedPrecision in lxNumFormat implementa per entrambi i motori HotXLS

1. Scegli la sezione per segno. Un formato a due sezioni usa la seconda sezione per i valori negativi. Un formato con tre o più sezioni usa la seconda per i negativi e la terza per lo zero esatto. Tutto il resto usa la prima sezione
2. Conta i segnaposto decimali. Ogni 0, # o ? dopo il punto decimale in quella sezione aggiunge un decimale conservato
3. Aggiungi due per segno percento. 0.0% mostra 0.1234 come 12.3%, quindi il valore memorizzato è un centesimo di ciò che vedi e conserva tre decimali, non uno
4. Sottrai tre per virgola di scala. Una virgola dopo l'ultimo segnaposto intero (0,, 0.0,, 0,.0) divide la visualizzazione per 1000. 0.0, mostra 12345.678 come 12.3, quindi Excel conserva un decimale meno tre, che è un conteggio negativo: il valore viene arrotondato alle centinaia e memorizzato come 12300. Una virgola tra segnaposto interi, come in #,##0, è semplice raggruppamento delle cifre e non cambia nulla
5. Lascia stare le sezioni non numeriche. Le sezioni General, data e ora (compresi i tempi trascorsi [h], [mm] e [ss]), scientifiche, frazionarie e testuali, e le sezioni senza alcun segnaposto cifra conservano la piena precisione

Misurate contro Excel 16, questi sono i valori che entrambi i motori HotXLS ora memorizzano per il risultato di una formula in ciascun formato:

Formato numerico	Valore calcolato	Valore memorizzato	Regola che si applica
0.0%	0.1234	0.123	Un decimale più due per il segno percento
0	2.5	3	Mezzo lontano da zero, non al pari
0	-2.5	-3	Mezzo lontano da zero anche sul lato negativo
0.00;(0.0)	-1.2345	-1.2	La sezione negativa mostra un decimale
0.00;(0.0)	1.2345	1.23	La sezione positiva mostra due decimali
#,##0.0	1234.5678	1234.6	Virgola di raggruppamento, nessuna scala
0.0,	12345.678	12300	Un decimale meno tre: arrotonda alle centinaia
0.0%;(0.00%)	-0.0125	-0.0125	La sezione negativa conserva due più due decimali
0.00	1.005	1.01	Tolleranza per l'errore di rappresentazione binaria
0;-0;0.0	0.5	1	Non zero, quindi decide la sezione positiva

L'ultima riga è una bella trappola. Il valore 0.5 si arrotonda a un numero intero, e la sezione dello zero non entra mai in gioco, perché Excel sceglie la sezione dal valore calcolato prima dell'arrotondamento. Un limite onesto sul lato HotXLS: le sezioni vengono scelte solo per segno, quindi un formato le cui sezioni portano condizioni tra parentesi quadre personalizzate come [>=1000] viene comunque diviso per segno. Verifica formati del genere contro Excel se ti importano

Perché 1.005 si arrotonda a 1.01 e non a 1.00?

Excel arrotonda 1.005 in una cella 0.00 a 1.01 benché il double più vicino a 1.005 stia appena sotto il punto di metà, e HotXLS lo riproduce con una tolleranza di qualche ulp. Il literal 1.005 non è rappresentabile in virgola mobile binaria. Il double IEEE 754 più vicino è 1.00499999999999989341858963598497211933135986328125, e moltiplicarlo per 100 dà 100.49999999999999. Una Floor(x * 100 + 0.5) / 100 da manuale quindi restituisce 1.00, che non va d'accordo né con il numero che l'utente ha digitato, né con ciò che Excel mostra, né con ciò che Excel memorizza

Delphi aggiunge il suo tocco. System.Round arrotonda i pareggi al pari, quindi Round(2.5) è 2 e Round(3.5) è 4. Quello è l'arrotondamento del banchiere, un default sensato per la statistica e la regola sbagliata qui: Excel memorizza 3 per 2.5 in una cella 0 e -3 per -2.5. L'implementazione HotXLS lavora sul valore assoluto, aggiunge 0.5 più una tolleranza relativa di 2^-51 per il valore in scala (qualche ulp a quella grandezza, mai meno di due ulp di 1.0), tronca, ridimensiona e restituisce il segno. La funzione seguente è un'illustrazione autonoma di quel principio, non il codice della libreria, e gestisce i conteggi di cifre negativi per le virgole di scala allo stesso modo:

// Schizzo di principio: arrotonda il mezzo lontano da zero a ADigits decimali,
// con una tolleranza di qualche ulp così 1.005 arriva a 1.01.
// ADigits < 0 arrotonda a decine, centinaia, ... ("0.0," dà -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
  Tolerance = 4.440892098500626E-16; // 2^-51, due ulp di 1.0
var
  I: Integer;
  Scale, Scaled, Eps: Double;
begin
  Result := AValue;
  if (ADigits < -15) or (ADigits > 14) then
    Exit; // oltre la precisione del double: lascia il valore com'è
  Scale := 1;
  for I := 1 to Abs(ADigits) do
    Scale := Scale * 10;
  if ADigits >= 0 then
  begin
    if Abs(AValue) > 1E300 / Scale then
      Exit; // la scalatura andrebbe in 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); // mezzo lontano da zero, non 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   (via 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)

La tolleranza è un compromesso deliberato. Un valore che sta davvero a due ulp sotto un mezzo passo si arrotonda anch'esso verso l'alto, ma a quella distanza la differenza è indistinguibile da un errore di rappresentazione, e trattarlo come mezzo passo è ciò che fa comportare i decimali digitati come gli utenti si aspettano

Che cosa andava storto prima della v2.384.57?

Prima della v2.384.57 il motore XLSX e il motore Classic avevano ciascuno il proprio codice precision-as-displayed, ed erano sbagliati ciascuno a modo suo. Se produci cartelle di lavoro con l'opzione attiva, questi sono i sintomi da cercare nei file generati da build più vecchie

Motore XLSX: solo prima sezione, niente percento, arrotondamento del banchiere

Il vecchio percorso XLSX chiedeva il conteggio dei decimali della stringa di formato nel suo complesso, che guardava solo la prima sezione e ignorava %, poi arrotondava con Round. Uno 0.1234 in 0.0% veniva memorizzato come 0.1, che è 10% anziché il 12.3% a schermo. Un 2.5 in 0 veniva memorizzato come 2 anziché 3. I valori negativi in un formato come 0.00;(0.0) venivano arrotondati ai due decimali della sezione positiva. Dalla v2.384.57 il motore XLSX chiama la stessa routine condivisa del motore Classic, che in quella release ha anche guadagnato il supporto delle virgole di scala

Motore classico: TRUE diventava -1

Il motore classico custodiva il suo arrotondamento con VarIsNumeric, e VarIsNumeric restituisce True per un Variant varBoolean. Convertire quel Variant con Double(V) dà -1, perché un Boolean True in stile COM è memorizzato come -1. Una formula come =A1>0 in una cella formattata 0.00 quindi usciva dal ricalcolo come il numero -1. Dalla v2.384.57 i risultati Boolean vengono esclusi prima di qualsiasi test numerico, e un risultato logico resta un risultato logico in entrambi i motori

Formati di tempo trascorso letti come colori (v2.384.9)

Il terzo bug stava nel modello dei formati numerici anziché nell'arrotondamento. Il parser classificava ogni token tra parentesi quadre che non fosse una condizione come colore, così [h], [mm] e [ss] non segnavano mai la loro sezione come data/ora. La visualizzazione non ne risentiva, perché la formattazione gira su un percorso separato, ma la precision as displayed si affida a quel flag per saltare i valori orari. Una durata di cinque secondi è 5/86400 di giorno, circa 0.0000579, e un formato come [ss].00 sembrava un ordinario numero a due decimali, quindi con FullPrecision spenta la durata veniva arrotondata a 0.00 giorni. Dalla v2.384.9 una sequenza tra parentesi quadre di una sola lettera h, m o s viene parseata come token di tempo trascorso e la sezione viene trattata come data/ora. La stessa release ha corretto il riconoscimento dei minuti in h:mm, dove i due punti tra i token nascondevano l'ora al parser

Attivare precision as displayed in HotXLS da Delphi

Per ottenere valori memorizzati equivalenti a Excel, imposta il flag prima del ricalcolo che deve rispettarlo, poi leggi i risultati in cache o salva. Sul motore XLSX, FullPrecision è un flag semplice: cambiarlo non invalida i risultati che un Recalculate precedente aveva già memorizzato, quindi impostalo subito dopo Create o Open e prima del primo Recalculate. L'esempio usa formule perché è lì che HotXLS applica l'arrotondamento:

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

    // Va impostato prima del primo Recalculate sul motore XLSX
    Wb.FullPrecision := False;
    Wb.Recalculate;

    // I risultati in cache ora combaciano con Excel 16: 0.123, 3 e 12300.
    // Le costanti nella colonna A conservano la loro piena precisione.
    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'); // scrive <calcPr fullPrecision="0"/>
  finally
    Wb.Free;
  end;
end;

Il motore Classic si comporta allo stesso modo, con una comodità in più: assegnare TXLSWorkbook.UseFullPrecision marca ogni formula nel grafo delle dipendenze come dirty, così il prossimo Recalculate rivaluta l'intera cartella di lavoro sotto la nuova regola. Cambiare un NumberFormat mentre l'opzione è attiva marca anch'esso come dirty le celle formula interessate, perché ora il formato decide il valore memorizzato. Nota che il Recalculate Classic restituisce il numero di celle formula che non è riuscito a valutare, quindi zero significa successo:

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; // marca ogni formula come dirty
    if Wb.Recalculate <> 0 then
      raise Exception.Create('Some formulas could not be evaluated');

    // B1 = -1.2: la sezione negativa "(0.0)" mostra un decimale
    // C1 resta Boolean True (le build prima della v2.384.57 memorizzavano -1)
    Wb.SaveAs('report.xls'); // record CalcPrecision con fFullPrec = 0
  finally
    Wb.Free;
  end;
end;

Entrambi i motori onorano anche il flag che arriva con un file. Apri una cartella di lavoro salvata con l'opzione attiva e FullPrecision o UseFullPrecision è già False, quindi un Recalculate dopo il caricamento arrotonda esattamente come farebbe Excel. Se ti serve solo leggere i numeri che Excel ha già memorizzato, puoi saltare del tutto il ricalcolo, come descritto in leggere i valori formula in cache senza ricalcolo. Per come i numeri seriali e i formati data interagiscono col modello di formato che guida il controllo data/ora, vedi seriali data Excel, il sistema 1904 e numFmt in Delphi

Quando attivare precision as displayed, e quando no?

Attiva la precision as displayed solo quando i numeri memorizzati della cartella di lavoro devono eguagliare i suoi numeri visualizzati, e accetti di perdere per sempre le cifre extra. Il classico caso legittimo è un piano finanziario dove colonne di importi arrotondati devono sommare al totale arrotondato a schermo, senza frazioni nascoste di centesimo che producono un totale sbagliato di uno nell'ultima posizione. Ricalcare una cartella di lavoro di un cliente che ha già l'opzione impostata è l'altro buon motivo, e HotXLS conserva il flag nel round trip così non riporti in silenzio qualcuno alla piena precisione

Evitala nella maggior parte delle altre situazioni:

• Dati ingegneristici e scientifici. Arrotondare una misurazione perché qualcuno ha scelto un formato a due decimali per un report distrugge informazione che nessuna successiva modifica di formato può restaurare
• Percentuali con formati grossolani. Un formato 0% conserva solo due decimali del rapporto memorizzato, così 0.1234 diventa 0.12, e ogni formula a valle che legge la cella lavora con 0.12
• Visualizzazioni in scala. Un formato 0, o 0.0, usato per mostrare le migliaia arrotonda il valore memorizzato alle migliaia o alle centinaia, cosa che raramente è ciò che chi ha scelto il formato intendeva
• Template condivisi. Il flag è a livello di cartella di lavoro. Chiunque aggiunga dopo un foglio eredita il comportamento, di solito senza sapere che è attivo

Se quello che vuoi davvero sono risultati arrotondati in qualche cella specifica, scrivi ROUND in quelle formule. ROUND è esplicito, locale alla cella, visibile a chiunque legga la formula, e valutato dal motore di formule HotXLS come qualsiasi altra funzione, senza effetti collaterali a livello di cartella di lavoro

Riferimento rapido precision as displayed

• Flag nel file: CalcPrecision $000E con fFullPrec = 0 in BIFF8 ([MS-XLS] §2.4.35), calcPr fullPrecision="0" in XLSX (ECMA-376 Parte 1)
• Interruttori HotXLS: TXLSXWorkbook.FullPrecision := False e TXLSWorkbook.UseFullPrecision := False, entrambi con default True
• Sezione: scelta in base al segno del valore calcolato; terza sezione solo per lo zero esatto
• Cifre: segnaposto decimali, più due per %, meno tre per virgola di scala; il conteggio può essere negativo
• Arrotondamento: mezzo lontano da zero con tolleranza di qualche ulp, così 2.5 dà 3, -2.5 dà -3 e 1.005 dà 1.01
• Salta: General, data/ora e tempi trascorsi, scientifico, frazione, testo, valori Boolean e errore
• Portata in HotXLS: risultati delle formule mentre vengono calcolati; le costanti vengono memorizzate come assegnate
• Motore XLSX: imposta FullPrecision prima del primo Recalculate; il setter Classic marca da solo tutte le formule come dirty
• Versioni: combaciante con Excel 16 in entrambi i motori dalla v2.384.57; formati di tempo trascorso protetti dalla v2.384.9

HotXLS legge, scrive e calcola cartelle di lavoro XLS e XLSX nativamente da Delphi e C++Builder, comprese le opzioni di calcolo della cartella di lavoro trattate qui. Dettagli, edizioni e download di prova sono sulla pagina del componente foglio di calcolo Delphi HotXLS