Formules Excel en JavaScript : les recréer sans perdre en précision

Recréer une formule Excel en JavaScript paraît trivial : =A2*B2 devient a * b. Les écarts apparaissent ailleurs, dans les arrondis, les dates, les recherches et les restes de division, là où les deux outils n’appliquent pas les mêmes règles. Or un centime d’écart sur un devis suffit pour que les utilisateurs ne fassent plus confiance à l’outil.

C’est l’exigence centrale quand on transforme un classeur en application. Chez mon employeur, j’ai converti des classeurs Excel complexes en mini-sites intranet locaux en gardant la même logique de calcul et les mêmes formules. Voici les différences qui comptent entre Excel et JavaScript, le code pour les neutraliser, vérifié avec Node.js, et la méthode pour prouver que l’application donne les mêmes résultats que le classeur.

Même format de nombres, mais pas le même calcul

Excel et JavaScript stockent les nombres au format double précision défini par la norme IEEE 754 (Microsoft, MDN). Ils partagent donc les mêmes approximations : 0,1 n’a pas de représentation binaire exacte, et en JavaScript 0.1 + 0.2 vaut 0.30000000000000004. La différence tient à ce qu’Excel fait autour de ce format :

• 15 chiffres significatifs : Excel limite la précision à 15 chiffres, quand un nombre JavaScript en porte environ 15 à 17. Microsoft cite =(43,1-43,2)+1, qui s’affiche 0,899999999999999 avec 15 décimales ; en JavaScript, le même calcul donne 0.8999999999999986.
• Les quasi-zéros corrigés : depuis Excel 97, quand une addition ou une soustraction donne un résultat très proche de zéro, Excel tente de compenser l’erreur de conversion binaire pour afficher 0. JavaScript ne fait rien de tel.
• La précision au format affiché : si cette option des paramètres avancés est activée dans le classeur, Excel remplace chaque valeur par sa valeur affichée. Le code doit alors arrondir aux mêmes endroits, sous peine d’écarts systématiques.
• Les identifiants longs : Excel peut tronquer à 15 chiffres un long nombre collé ou importé, par exemple depuis un CSV (Microsoft), et JavaScript ne garantit des entiers exacts que jusqu’à Number.MAX_SAFE_INTEGER, soit 9 007 199 254 740 991. Numéros de compte, codes et références restent du texte, des deux côtés.

Conséquence pratique : on ne compare jamais deux résultats décimaux avec === sans les avoir arrondis, et on arrondit exactement aux mêmes étapes que le classeur. S’il arrondit chaque ligne avant de faire le total, le code fait de même ; s’il n’arrondit que le total, aussi.

ARRONDI n’est pas Math.round

La fonction ARRONDI d’Excel arrondit les demis en s’éloignant de zéro : selon la documentation de Microsoft, =ARRONDI(-1,475; 2) donne -1,48. Math.round, lui, arrondit un demi exact vers +∞ : Math.round(-2.5) vaut -2, et MDN précise que ce comportement diffère de celui de nombreux langages (MDN). S’y ajoute le problème des décimales : 1,005 est stocké comme 1,00499999999999989…, donc Math.round(1.005 * 100) / 100 donne 1 au lieu du 1,01 attendu.

La fonction suivante reproduit ces résultats. Elle tient en trois lignes, dans function arrondi(x, n = 0) { … } :

1. const [m, e] = Math.abs(x).toPrecision(15).split('e'); ramène le nombre à 15 chiffres significatifs, comme Excel : 1,005 redevient 1,00500000000000 ;
2. const k = Math.round(Number(m + 'e' + ((Number(e) || 0) + n))); déplace la virgule par la notation exponentielle, sans multiplication, puis arrondit un nombre positif ;
3. return (Math.sign(x) * (n >= 0 ? k / 10 ** n : k * 10 ** -n)) || 0; remet le signe, ce qui revient à arrondir les demis en s’éloignant de zéro, et || 0 évite d’obtenir -0.

Vérifiée avec Node.js, elle donne les résultats de la documentation de Microsoft, 1,01 pour 1,005 et -3 pour -2,5. Elle ne prétend pas imiter Excel au bit près dans tous les cas : seule la comparaison avec votre classeur le prouve, comme on le verra plus bas.

Pour l’affichage, Intl.NumberFormat arrondit par défaut les demis en s’éloignant de zéro, avec l’option roundingMode réglée sur 'halfExpand' (MDN). Mais il produit du texte : gardez arrondi() pour les calculs. Évitez aussi toFixed() : MDN rappelle que (2.55).toFixed(1) renvoie '2.5' (MDN).

Les dates : un numéro de série et un 29 février 1900 fictif

Pour Excel, une date est un nombre. Le 1er janvier 1900 porte le numéro de série 1, et le 1er janvier 2008 le numéro 39448 (Microsoft). La partie décimale représente l’heure : 0,5 correspond à midi (Microsoft). Les anciennes versions d’Excel pour Mac utilisaient un système partant de 1904, décalé de 1 462 jours (Microsoft) : vérifiez lequel utilise votre classeur.

Ce système contient une anomalie connue : Excel considère 1900 comme une année bissextile, ce qu’elle n’est pas. Microsoft l’explique par la compatibilité avec Lotus 1-2-3, qui faisait la même hypothèse, et ne la corrige pas, car presque toutes les dates des classeurs existants seraient décalées d’un jour (Microsoft Learn). Le numéro 60 désigne donc un 29 février 1900 qui n’a jamais existé, et la fonction JOURSEM renvoie des valeurs fausses avant le 1er mars 1900.

Pour convertir, on s’appuie sur le numéro 25569, qui correspond au 1er janvier 1970, l’origine des dates en JavaScript, et on décale d’un jour avant le 1er mars 1900 :

• const dateDepuisExcel = (n) => n === 60 ? null : new Date((n - (n < 60 ? 25568 : 25569)) * 86400000);
• const serieExcel = (d) => d.getTime() / 86400000 + 25569;, pour les dates à partir du 1er mars 1900.

Avec ces deux fonctions, 39448 donne bien le 1er janvier 2008, et 61 le 1er mars 1900. Un piège reste : le fuseau horaire. new Date(2011, 6, 5) crée le 5 juillet à minuit, heure locale, soit la veille à 22 h en temps universel l’été en France ; le numéro de série obtenu tombe alors à 40728,9166… Travaillez en UTC de bout en bout : new Date(Date.UTC(2011, 6, 5)) pour créer une date, en se souvenant que les mois commencent à 0 (MDN), et d.toLocaleDateString('fr-FR', { timeZone: 'UTC' }) pour l’afficher.

RECHERCHEV et RECHERCHEX deviennent des tables de correspondance

RECHERCHEV cherche une valeur dans la première colonne d’une table. Son quatrième argument décide de tout : FAUX exige une correspondance exacte, et s’il est omis, Excel applique une correspondance approximative qui suppose une première colonne triée (Microsoft). Un FAUX oublié sur une table non triée renvoie une valeur fausse plutôt qu’une erreur. RECHERCHEX cherche une correspondance exacte par défaut, mais elle n’existe pas dans Excel 2016 et 2019 (Microsoft).

Correspondance exacte : une Map

On construit la table une fois, au chargement : const prix = new Map(articles.map((a) => [a.code.trim().toUpperCase(), a.prix]));. La recherche devient prix.get(code.trim().toUpperCase()). Deux précautions :

• Excel compare les textes sans tenir compte des majuscules, comme le précise la documentation d’EQUIV, alors qu’une Map distingue « a12 » de « A12 » : normalisez les clés des deux côtés ;
• get() renvoie undefined quand la valeur est absente, là où Excel affiche #N/A. Traitez ce cas explicitement, avec un message à l’écran, plutôt que de laisser un undefined devenir un NaN plus loin dans le calcul.

Correspondance approximative : le dernier palier atteint

Pour un barème par tranches, EQUIV en mode 1 recherche « la plus grande valeur qui est inférieure ou égale » à la valeur cherchée, dans une liste triée par ordre croissant : c’est le principe de la correspondance approximative. En JavaScript, sur un tableau trié par seuil croissant : const palier = paliers.findLast((p) => p.seuil <= montant);. La méthode findLast est disponible dans les navigateurs récents et dans Node.js depuis la version 18 (MDN). Un montant inférieur au premier seuil renvoie undefined, l’équivalent du #N/A. Mieux qu’Excel, le code peut vérifier au chargement que la table est bien triée, et refuser de calculer sinon.

SI imbriqués, MOD, ENT, SOMME.SI : les autres traductions

Les règles de MOD et d’ENT viennent de la documentation de Microsoft (MOD, ENT) ; l’opérateur % de JavaScript, lui, garde le signe du dividende (MDN).

Les saisies demandent la même rigueur. En JavaScript, Number('') vaut 0, si bien qu’un champ vide passe pour un zéro, et Number('12abc') vaut NaN. Les nombres tapés à la française, comme « 1 234,56 », doivent être convertis : Number(texte.replace(/\s/g, '').replace(',', '.')). Contrôlez chaque champ au moment de la saisie, avec un message clair, plutôt qu’au moment du calcul.

Prouver l’équivalence : les tests de non-régression

Aucune relecture ne garantit qu’une formule a été bien traduite. La preuve vient d’une comparaison systématique entre le classeur et le code, sur des cas réels :

1. Créer une feuille de tests dans le classeur d’origine : une ligne par cas, avec les entrées et les résultats calculés par Excel. Prévoyez des cas courants, des cas limites (zéro, valeurs négatives, seuils exacts de barème, fins de mois) et les cas qui ont déjà posé problème.
2. Exporter cette feuille en CSV UTF-8. Attention : ce format enregistre le texte et les valeurs « tels qu’ils sont affichés dans les cellules » (Microsoft). Affichez assez de décimales dans les colonnes de résultats avant l’export, sinon vous comparerez des valeurs déjà arrondies.
3. Écrire un script de comparaison qui lit ce fichier, appelle les mêmes fonctions que l’application et compare chaque résultat. Le lanceur de tests intégré à Node.js, node --test, suffit, sans aucune dépendance (Node.js).
4. Fixer la tolérance à l’avance : égalité stricte pour les montants arrondis au centime, tolérance relative pour les valeurs intermédiaires, par exemple const egal = (a, b, tol = 1e-9) => Math.abs(a - b) <= tol * Math.max(1, Math.abs(a), Math.abs(b));, et comparaison des dates sous forme de numéros de série.
5. Expliquer chaque écart : erreur de traduction à corriger, ou erreur découverte dans le classeur, à trancher avec le métier.

Ce jeu de tests ne sert pas qu’une fois. Conservé avec l’application et relancé à chaque modification d’un barème ou d’une formule, il signale une régression avant que les utilisateurs ne la voient.

Il arrive que la comparaison révèle une erreur dans le classeur lui-même, comme une plage qui s’arrête trop tôt. Ce n’est pas rare : les études sur les erreurs dans les fichiers Excel en trouvent dans la plupart des gros classeurs.

En résumé : traduire les règles, pas seulement les formules

Recréer des formules Excel en JavaScript, c’est reproduire les règles implicites du tableur : arrondi des demis en s’éloignant de zéro, 15 chiffres significatifs, numéros de série des dates et leur 29 février 1900, recherches insensibles à la casse, signe du reste de MOD. Regroupez ces règles dans quelques fonctions utilitaires testées, utilisez-les partout, et prouvez l’équivalence par une comparaison automatique avec le classeur. Pour l’application qui accueille ces calculs, voyez un intranet local en HTML et JavaScript et la méthode complète dans transformer un fichier Excel en application web. Si vous préférez confier ce travail, c’est l’objet de mon accompagnement Excel en application web.