Articolo tecnico

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 corrisponde al segno del valore, due decimali in più per ogni %, tre in meno per ogni virgola di scala alle migliaia, arrotondando a metà via 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 fatture esportate non tornano con Excel di un centesimo, oppure che una colonna di durate in [ss].00 è collassata a zero. Entrambe le cose risalgono a una di quelle regole applicata male. I due motori HotXLS condividono una singola implementazione i cui valori attesi sono stati misurati in Excel 16 con Workbook.PrecisionAsDisplayed attivo

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
Diagramma HotXLS delle regole di precisione visualizzata: scegli la sezione di formato in base al segno del valore, conta i segnaposto cifra dopo il punto decimale, aggiungi due decimali per segno percento, sottrai tre per virgola di scala alle migliaia così il conteggio può andare negativo, salta del tutto General e le sezioni data e ora, poi arrotonda il mezzo lontano da zero
Il conteggio delle cifre viene dalla sezione che combacia col segno, più due per percento e meno tre per virgola di scala, e un conteggio negativo arrotonda alle decine o centinaia; General e le sezioni data vengono lasciate in pace

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

Formato numericoValore calcolatoValore memorizzatoRegola che si applica
0.0%0.12340.123Un decimale più due per il segno percento
02.53Mezzo lontano da zero, non al pari
0-2.5-3Mezzo lontano da zero anche sul lato negativo
0.00;(0.0)-1.2345-1.2La sezione negativa mostra un decimale
0.00;(0.0)1.23451.23La sezione positiva mostra due decimali
#,##0.01234.56781234.6Virgola di raggruppamento, nessuna scala
0.0,12345.67812300Un decimale meno tre: arrotonda alle centinaia
0.0%;(0.00%)-0.0125-0.0125La sezione negativa conserva due più due decimali
0.001.0051.01Tolleranza per l'errore di rappresentazione binaria
0;-0;0.00.51Non 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 di arrotondare. HotXLS sceglie le sezioni per segno, quindi per un formato le cui sezioni portano condizioni tra parentesi quadre come [>=1000], confronta i valori memorizzati con Excel se per te contano

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:

Diagramma di arrotondamento HotXLS: 2.5 si arrotonda a mezzo lontano da zero verso 3 e -2.5 verso -3, dove Delphi System.Round dà le risposte da banchiere 2 e -2, e visto che il double più vicino a 1.005 sta appena sotto il punto di metà, la tolleranza di qualche ulp è ciò che trasforma un 1.00 basato su floor nella risposta Excel 1.01
Excel arrotonda i pareggi lontano da zero e perdona l'errore di rappresentazione binaria con una piccola tolleranza; entrambi i dettagli sono misurabili, e saltarne uno memorizza 2 per 2.5 oppure 1.00 per 1.005, a un centesimo da Excel
// 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

Quali trappole di implementazione rompono la precision as displayed?

Tre trappole spiegano la maggior parte delle discrepanze della precision as displayed, e ognuna memorizza un valore che non concorda con lo schermo. Se implementi la regola per conto tuo, o fai auditing di workbook generati con l'opzione attiva, questi sono i sintomi da cercare

Solo prima sezione, niente percento, arrotondamento del banchiere

La scorciatoia allettante chiede il conteggio dei decimali della stringa di formato nel suo complesso, che guarda solo la prima sezione e ignora %, poi arrotonda con Round. Un 0.1234 in 0.0% viene allora memorizzato come 0.1, cioè 10% invece del 12.3% a schermo. Un 2.5 in 0 viene memorizzato come 2 invece di 3. I valori negativi in un formato come 0.00;(0.0) vengono arrotondati ai due decimali della sezione positiva. HotXLS instrada entrambi i motori attraverso una routine condivisa che sceglie la sezione per segno, conta i segni di percento e le virgole di scala e arrotonda a metà via da zero

TRUE diventa -1

Proteggere l'arrotondamento con VarIsNumeric è la seconda trappola, perché VarIsNumeric restituisce True anche per un Variant varBoolean. Convertire quel Variant con Double(V) dà -1, perché un Boolean True in stile COM viene memorizzato come -1. Una formula come =A1>0 in una cella formattata 0.00 uscirebbe così dal ricalcolo come il numero -1. HotXLS esclude i risultati Boolean prima di qualsiasi test numerico, così un risultato logico resta un risultato logico in entrambi i motori

I formati di tempo trascorso letti come colori

La terza trappola sta nel modello del formato numerico più che nell'arrotondamento. Un parser che classifica ogni token tra parentesi quadre che non è una condizione come un colore non marca mai le sezioni [h], [mm] e [ss] come data/ora. La visualizzazione non ne risente, perché il formatting gira su un percorso separato, ma la precision as displayed si affida a quel flag per saltare i valori temporali. Una durata di cinque secondi è 5/86400 di giorno, circa 0.0000579, e un formato come [ss].00 allora sembra un normale numero a due decimali, così con FullPrecision disattivata la durata si arrotonda a 0.00 giorni. HotXLS parserizza una sequenza tra parentesi quadre di una sola lettera h, m o s come token di tempo trascorso e tratta la sezione come data/ora. Il riconoscimento dei minuti in h:mm richiede la stessa cura, perché il due punti tra i token può nascondere l'ora a un parser

Diagramma HotXLS di un tempo trascorso parserizzato male: cinque secondi memorizzati come minuscola frazione di giorno in una cella formattata con il token ss tra parentesi quadre, che un parser che tratta ogni parentesi non-condizione come un colore flagga come un semplice numero a due decimali, così la precision as displayed arrotonderebbe la durata a 0.00 a meno che la sezione venga parserizzata come tempo trascorso
Il formatting gira su un percorso a sé, così la cella può sembrare corretta mentre il valore memorizzato si arrotonda a zero; una singola lettera h, m o s tra parentesi quadre è un token di tempo trascorso, non un colore, e la sezione conserva la precisione completa

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 tutte le formule come sporche
    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, non il numero -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 del 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 True per impostazione predefinita
  • 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: a metà via da zero con tolleranza di pochi ulp, così 2.5 dà 3, -2.5 dà -3 e 1.005 dà 1.01
  • Saltati: General, data/ora e tempo trascorso, scientifico, frazione, testo, valori Boolean ed errori
  • Ambito in HotXLS: i risultati delle formule così come vengono calcolati; le costanti vengono memorizzate come assegnate
  • Motore XLSX: imposta FullPrecision prima del primo Recalculate; il setter del Classic rimarchia da sé tutte le formule come sporche
  • Riferimento: valori attesi misurati in Excel 16, condivisi da entrambi i motori

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