Le precision as displayed d’Excel arrondit chaque nombre stocké aux décimales que montre son format de nombre : la section du format qui correspond au signe de la valeur, deux décimales de plus par %, trois de moins par virgule d’échelle en milliers, arrondi des moitiés en s’éloignant de zéro. HotXLS applique la même règle dans ses deux moteurs Delphi quand TXLSXWorkbook.FullPrecision ou TXLSWorkbook.UseFullPrecision vaut False. Cela sonne comme une ligne de code, jusqu’à ce qu’un client signale que vos totaux de factures exportés divergent d’Excel d’un centime, ou qu’une colonne de durées en [ss].00 se soit effondrée à zéro. Les deux sont arrivés, et les deux remontent à une de ces règles prise de travers. Depuis v2.384.57, les deux moteurs partagent une implémentation unique dont les valeurs attendues ont été mesurées dans Excel 16 avec Workbook.PrecisionAsDisplayed activé
Que change réellement le precision as displayed dans un classeur ?
Le precision as displayed est un unique drapeau au niveau du classeur qui dit au moteur de calcul de stocker les nombres tels qu’ils paraissent, pas tels qu’ils ont été calculés. Dans l’interface d’Excel, il siège sous Fichier, Options, Avancé, « Lors du calcul de ce classeur », sous le nom « Set precision as displayed ». Sur disque, c’est un bit. Un fichier BIFF8 le porte dans l’enregistrement CalcPrecision ($000E, [MS-XLS] §2.4.35), dont le champ fFullPrec vaut 1 pour la pleine précision normale et 0 quand l’option est active. Un paquet XLSX le porte comme attribut fullPrecision de l’élément calcPr du workbook.xml, défini dans ECMA-376 Partie 1, où le défaut est true et fullPrecision="0" active l’arrondi
Le drapeau n’est pas une préférence d’affichage. Quand vous cochez la case, Excel avertit que les données perdront définitivement en précision, et il le pense : les valeurs sont réécrites à leur précision affichée, et les chiffres coupés sont perdus. Décocher la case ensuite ne ramène pas les vieux chiffres. Un 0.1234 affiché 12.3% devient 0.123 pour de bon
HotXLS lit et écrit le drapeau dans les deux formats et l’expose dans les deux moteurs :
TXLSXWorkbook.FullPrecision: Booleansur le moteur XLSX, chargée depuis et sauvegardée verscalcPr/@fullPrecisionTXLSWorkbook.UseFullPrecision: Booleansur le moteur Classic (aussi surIXLSWorkbook), chargée depuis et sauvegardée vers l’enregistrement CalcPrecision- Les deux valent True par défaut, le mode sûr et non destructif, qui est aussi le défaut d’Excel
L’endroit où HotXLS applique l’arrondi compte. HotXLS arrondit au point où il calcule une valeur : chaque résultat de formule est arrondi à sa précision affichée avant d’être stocké comme valeur en cache de la cellule, pendant Recalculate et pendant l’évaluation à la demande. Les constantes que vous affectez via Value sont stockées exactement telles quelles. Si votre sortie doit reproduire ce qu’Excel stocke après le cochage de la case, arrondissez ces constantes vous-même avant de les écrire, par exemple avec l’aide montrée plus bas
Comment Excel décide-t-il combien de décimales garder ?
Excel déduit le nombre de décimales gardées de la section précise du format qui affiche la valeur, pas de la chaîne de format dans son ensemble. Les règles ci-dessous ont été mesurées dans Excel 16 et sont ce qu’implémente XlsApplyDisplayedPrecision dans lxNumFormat pour les deux moteurs HotXLS
- Choisir la section selon le signe. Un format à deux sections utilise la seconde pour les valeurs négatives. Un format à trois sections ou plus utilise la seconde pour les négatives et la troisième pour zéro exactement. Tout le reste utilise la première section
- Compter les espaces réservés de décimales. Chaque
0,#ou?après le séparateur décimal dans cette section ajoute une décimale gardée - Ajouter deux par signe pour cent.
0.0%affiche 0.1234 comme 12.3%, donc la valeur stockée est un centième de ce que vous voyez et garde trois décimales, pas une - Retirer trois par virgule d’échelle. Une virgule après le dernier espace réservé entier (
0,,0.0,,0,.0) divise l’affichage par 1000.0.0,affiche 12345.678 comme 12.3, donc Excel garde une décimale moins trois, soit un compte négatif : la valeur est arrondie aux centaines et stockée comme 12300. Une virgule entre espaces réservés entiers, comme dans#,##0, est du simple groupement de chiffres et ne change rien - Laisser tranquilles les sections non numériques. Les sections General, date et heure (y compris les durées
[h],[mm]et[ss]), scientifiques, fractions et texte, et les sections sans aucun espace réservé de chiffre gardent la pleine précision
Mesuré contre Excel 16, voici les valeurs que les deux moteurs HotXLS stockent désormais pour un résultat de formule dans chaque format :
| Format de nombre | Valeur calculée | Valeur stockée | Règle appliquée |
|---|---|---|---|
0.0% | 0.1234 | 0.123 | Une décimale plus deux pour le signe pour cent |
0 | 2.5 | 3 | Moitié en s’éloignant de zéro, pas vers le pair |
0 | -2.5 | -3 | Moitié en s’éloignant de zéro côté négatif aussi |
0.00;(0.0) | -1.2345 | -1.2 | La section négative affiche une décimale |
0.00;(0.0) | 1.2345 | 1.23 | La section positive affiche deux décimales |
#,##0.0 | 1234.5678 | 1234.6 | Virgule de groupement, pas d’échelle |
0.0, | 12345.678 | 12300 | Une décimale moins trois : arrondi aux centaines |
0.0%;(0.00%) | -0.0125 | -0.0125 | La section négative garde deux plus deux décimales |
0.00 | 1.005 | 1.01 | Tolérance à l’erreur de représentation binaire |
0;-0;0.0 | 0.5 | 1 | Pas zéro, donc la section positive décide |
La dernière ligne est un joli piège. La valeur 0.5 s’arrondit à un entier, et la section zéro n’entre jamais en jeu, parce qu’Excel choisit la section d’après la valeur calculée avant l’arrondi. Une limite honnête côté HotXLS : les sections sont choisies selon le seul signe, donc un format dont les sections portent des conditions entre crochets personnalisées telles que [>=1000] est quand même coupé selon le signe. Vérifiez ces formats contre Excel s’ils vous importent
Pourquoi 1.005 s’arrondit-il en 1.01 et pas en 1.00 ?
Excel arrondit 1.005 dans une cellule 0.00 à 1.01 alors que le double le plus proche de 1.005 est légèrement sous le point médian, et HotXLS colle avec une tolérance de quelques ulps. Le littéral 1.005 n’est pas représentable en virgule flottante binaire. Le double IEEE 754 le plus proche est 1.00499999999999989341858963598497211933135986328125, et la multiplication par 100 donne 100.49999999999999. Un Floor(x * 100 + 0.5) / 100 de manuel renvoie donc 1.00, en désaccord avec le nombre tapé par l’utilisateur, avec ce qu’affiche Excel, et avec ce qu’Excel stocke
Delphi ajoute sa propre touche. System.Round arrondit les demi vers le pair, donc Round(2.5) vaut 2 et Round(3.5) vaut 4. C’est l’arrondi du banquier, un défaut sensé pour les statistiques et la mauvaise règle ici : Excel stocke 3 pour 2.5 dans une cellule 0 et -3 pour -2.5. L’implémentation HotXLS travaille sur la valeur absolue, ajoute 0.5 plus une tolérance relative de 2-51 fois la valeur mise à l’échelle (quelques ulps à cette magnitude, jamais moins que deux ulps de 1.0), tronque, remet à l’échelle et restaure le signe. La fonction suivante est une illustration autonome de ce principe, pas le code de la bibliothèque lui-même, et elle traite les comptes de chiffres négatifs des virgules d’échelle de la même façon :
// Esquisse de principe : arrondir au demi en s’éloignant de zéro à ADigits décimales,
// avec une tolérance de quelques ulps pour que 1.005 atteigne 1.01.
// ADigits < 0 arrondit aux dizaines, centaines, ... ("0.0," donne -2)
function RoundAsDisplayed(AValue: Double; ADigits: Integer): Double;
const
Tolerance = 4.440892098500626E-16; // 2^-51, deux ulps de 1.0
var
I: Integer;
Scale, Scaled, Eps: Double;
begin
Result := AValue;
if (ADigits < -15) or (ADigits > 14) then
Exit; // au-delà de la précision double : laisser la valeur tranquille
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 mise à l’échelle déborderait
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); // au demi en s’éloignant de zéro, pas 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 chiffres)
// RoundAsDisplayed(12345.678, -2) = 12300 ("0.0," : 1 - 3 chiffres)La tolérance est un compromis délibéré. Une valeur réellement deux ulps sous un demi-palier s’arrondit aussi vers le haut, mais à cette distance la différence est indiscernable de l’erreur de représentation, et la traiter comme un demi-palier, c’est ce qui fait que les décimales tapées se comportent comme les utilisateurs s’y attendent
Qu’est-ce qui clochait avant v2.384.57 ?
Avant v2.384.57, le moteur XLSX et le moteur Classic avaient chacun leur propre code de precision-as-displayed, et chacun se trompait à sa façon. Si vous produisez des classeurs avec l’option active, voici les symptômes à chercher dans les fichiers générés par des builds plus anciens
Moteur XLSX : première section seulement, pas de pour cent, arrondi du banquier
L’ancien chemin XLSX demandait le compte de décimales de la chaîne de format entière, qui ne regardait que la première section et ignorait %, puis arrondissait avec Round. Un 0.1234 en 0.0% était stocké comme 0.1, soit 10% au lieu des 12.3% à l’écran. Un 2.5 en 0 était stocké comme 2 au lieu de 3. Les valeurs négatives dans un format tel que 0.00;(0.0) étaient arrondies aux deux décimales de la section positive. Depuis v2.384.57, le moteur XLSX appelle la même routine partagée que le moteur Classic, qui a d’ailleurs gagné le support des virgules d’échelle dans cette version
Moteur Classic : TRUE devenait -1
Le moteur Classic gardait son arrondi avec VarIsNumeric, et VarIsNumeric renvoie True pour un Variant varBoolean. Convertir ce Variant avec Double(V) donne -1, parce qu’un True booléen à la COM est stocké comme -1. Une formule telle que =A1>0 dans une cellule formatée 0.00 ressortait donc de la recalculation comme le nombre -1. Depuis v2.384.57, les résultats booléens sont exclus avant tout test numérique, et un résultat logique reste un résultat logique dans les deux moteurs
Formats de durée lus comme des couleurs (v2.384.9)
Le troisième bug siégeait dans le modèle de format de nombre plutôt que dans l’arrondi. L’analyseur classait chaque token entre crochets qui n’était pas une condition comme une couleur, donc [h], [mm] et [ss] ne marquaient jamais leur section comme date/heure. L’affichage n’était pas touché, parce que le formatage tourne sur un chemin séparé, mais le precision as displayed compte sur ce drapeau pour sauter les valeurs d’heure. Une durée de cinq secondes vaut 5/86400 de jour, environ 0.0000579, et un format comme [ss].00 ressemblait à un nombre ordinaire à deux décimales, donc avec FullPrecision désactivé la durée était arrondie à 0.00 jour. Depuis v2.384.9, une séquence entre crochets d’une seule lettre h, m ou s est analysée comme un token de durée écoulée et la section est traitée comme date/heure. La même version a corrigé la détection des minutes dans h:mm, où les deux-points entre les tokens cachaient l’heure à l’analyseur
Activer le precision as displayed dans HotXLS depuis Delphi
Pour obtenir des valeurs stockées équivalentes à Excel, réglez le drapeau avant la recalculation qui doit l’honorer, puis lisez les résultats en cache ou sauvegardez. Sur le moteur XLSX, FullPrecision est un simple drapeau : le changer n’invalide pas les résultats qu’un Recalculate antérieur a déjà stockés, donc réglez-le juste après Create ou Open et avant la première Recalculate. L’exemple utilise des formules parce que c’est là que HotXLS applique l’arrondi :
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%'; // affiche 12.3%
Sh.Cells[2, 2].Formula := '=A2';
Sh.Cells[2, 2].NumberFormat := '0'; // affiche 3
Sh.Cells[3, 2].Formula := '=A3';
Sh.Cells[3, 2].NumberFormat := '0.0,'; // affiche 12.3 (milliers)
// À régler avant la première Recalculate sur le moteur XLSX
Wb.FullPrecision := False;
Wb.Recalculate;
// Les résultats en cache collent désormais à Excel 16 : 0.123, 3 et 12300.
// Les constantes de la colonne A gardent leur pleine précision.
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'); // écrit <calcPr fullPrecision="0"/>
finally
Wb.Free;
end;
end;Le moteur Classic se comporte pareil, avec un confort : affecter TXLSWorkbook.UseFullPrecision marque chaque formule du graphe de dépendances comme périmée, donc la prochaine Recalculate réévalue tout le classeur sous la nouvelle règle. Changer un NumberFormat pendant que l’option est active marque aussi périmées les cellules formules touchées, parce que le format décide désormais de la valeur stockée. Notez que la Recalculate Classic renvoie le nombre de cellules formules qu’elle n’a pas pu évaluer, donc zéro signifie succès :
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; // marque chaque formule périmée
if Wb.Recalculate <> 0 then
raise Exception.Create('Some formulas could not be evaluated');
// B1 = -1.2 : la section négative "(0.0)" affiche une décimale
// C1 reste le booléen True (les builds avant v2.384.57 stockaient -1)
Wb.SaveAs('report.xls'); // enregistrement CalcPrecision avec fFullPrec = 0
finally
Wb.Free;
end;
end;Les deux moteurs honorent aussi le drapeau qui arrive avec un fichier. Ouvrez un classeur sauvegardé avec l’option active et FullPrecision ou UseFullPrecision vaut déjà False, donc une Recalculate après chargement arrondit exactement comme le ferait Excel. Si vous n’avez besoin que de lire les nombres qu’Excel a déjà stockés, vous pouvez sauter la recalculation entièrement, comme décrit dans la lecture des valeurs de formules en cache sans recalculation. Pour la façon dont les numéros de série et les formats de date interagissent avec le modèle de format qui pilote le test date/heure, voir les numéros de série de date Excel, le système 1904 et numFmt en Delphi
Quand activer le precision as displayed, et quand ne pas le faire ?
N’activez le precision as displayed que quand les nombres stockés du classeur doivent égaler ses nombres affichés, et que vous acceptez de perdre les chiffres supplémentaires pour toujours. Le cas légitime classique, c’est un échéancier financier où des colonnes de montants arrondis doivent s’additionner au total arrondi à l’écran, sans fractions de centime cachées qui produiraient un total faux d’une unité au dernier rang. Égaler un classeur existant d’un client qui a déjà l’option réglée est l’autre bonne raison, et HotXLS préserve le drapeau à l’aller-retour pour que vous ne rebasculiez pas en pleine précision en silence
Évitez-le dans la plupart des autres situations :
- Données d’ingénierie et scientifiques. Arrondir une mesure parce que quelqu’un a choisi un format à deux décimales pour un rapport détruit une information qu’aucun changement de format ultérieur ne peut restaurer
- Pourcentages aux formats grossiers. Un format
0%ne garde que deux décimales du ratio stocké, donc 0.1234 devient 0.12, et chaque formule en aval qui lit la cellule travaille avec 0.12 - Affichages à l’échelle. Un format
0,ou0.0,utilisé pour montrer des milliers arrondit la valeur stockée aux milliers ou aux centaines, ce qui est rarement l’intention de la personne qui a choisi le format - Modèles partagés. Le drapeau vaut pour tout le classeur. Quiconque ajoute une feuille ensuite hérite du comportement, généralement sans savoir qu’il est actif
Si ce que vous voulez vraiment, ce sont des résultats arrondis dans quelques cellules précises, écrivez plutôt ROUND dans ces formules. ROUND est explicite, local à la cellule, visible par quiconque lit la formule, et évalué par le moteur de formules HotXLS comme n’importe quelle autre fonction, sans effets de bord à l’échelle du classeur
Aide-mémoire du precision as displayed
- Drapeau fichier : CalcPrecision
$000EavecfFullPrec= 0 en BIFF8 ([MS-XLS] §2.4.35),calcPr fullPrecision="0"en XLSX (ECMA-376 Partie 1) - Interrupteurs HotXLS :
TXLSXWorkbook.FullPrecision := FalseetTXLSWorkbook.UseFullPrecision := False, tous deux True par défaut - Section : choisie selon le signe de la valeur calculée ; troisième section seulement pour zéro exactement
- Chiffres : espaces réservés de décimales, plus deux par
%, moins trois par virgule d’échelle ; le compte peut être négatif - Arrondi : demi en s’éloignant de zéro avec une tolérance de quelques ulps, donc 2.5 donne 3, -2.5 donne -3 et 1.005 donne 1.01
- Sautés : General, date/heure et durées, scientifique, fractions, texte, booléens et valeurs d’erreur
- Portée dans HotXLS : les résultats de formules au fil de leur calcul ; les constantes sont stockées telles qu’affectées
- Moteur XLSX : réglez
FullPrecisionavant la premièreRecalculate; le setter Classic re-périm toutes les formules lui-même - Versions : aligné sur Excel 16 dans les deux moteurs depuis v2.384.57 ; formats de durée protégés depuis v2.384.9
HotXLS lit, écrit et calcule des classeurs XLS et XLSX nativement depuis Delphi et C++Builder, y compris les options de calcul de classeur couvertes ici. Détails, éditions et essai à télécharger sont sur la page du composant tableur HotXLS Delphi