Fonctions d'ingénierie dans Delphi : Conversion de base, nombres complexes

La famille des fonctions d'ingénierie d'Excel semble être la partie la plus simple de la référence des fonctions. DEC2BIN convertit un nombre en une chaîne binaire. HEX2DEC fait l'inverse. IMSUM additionne deux nombres complexes. Chacune ressemble à un simple exercice de formatage. Ce n'est pas le cas. Derrière ces noms se cachent un encodage en complément à deux sur dix bits que la plupart des développeurs n'ont pas manipulé depuis leurs cours d'architecture informatique, un format de nombre complexe qui réside entièrement dans des chaînes de caractères, et des opérateurs au niveau du bit qui dépasseront silencieusement la capacité d'un entier sur 64 bits si vous effectuez le décalage avant la vérification. Un moteur de feuille de calcul qui reproduit exactement Excel ne peut rien laisser au hasard.

Les fonctions se divisent en trois groupes, et chaque groupe cache un piège différent. La conversion de base concerne les nombres négatifs et les seuils par base. L'arithmétique complexe concerne l'analyse (parsing) et le formatage d'une chaîne. Les opérations au niveau du bit consistent à rester dans les limites du type Int64. Cet article parcourt chaque groupe tel que HotXLS l'implémente, avec les appels de feuille de calcul que vous écririez réellement.

Conversion de base et complément à deux sur dix bits

Le sens direct correspond à ce que tout le monde attend. DEC2BIN(9) donne "1001", et un second argument facultatif complète le résultat à gauche avec des zéros sur une largeur fixe. Le piège réside dans l'entrée négative. Excel n'écrit pas de signe moins. Il encode la valeur sous forme de chaîne en complément à deux sur dix chiffres dans la base cible, c'est pourquoi DEC2BIN(-5,10) renvoie "1111111011" plutôt qu'un texte avec un signe. L'argument de largeur (places) est ignoré dès que la valeur est négative, car l'encodage est déjà fixé à dix chiffres.

Dix chiffres représentent un budget fixe, et ce budget définit la plage représentable par base. En binaire, la valeur absolue qui bascule dans la partie négative est 512, et le module de bouclage est 1024, de sorte qu'une chaîne binaire n'est signée que lorsqu'elle comporte exactement dix caractères et que sa valeur est d'au moins 512. Le même principe s'applique aux autres bases. L'octal utilise un seuil intermédiaire de 2^29 et un module complet de 2^30. L'hexadécimal utilise 2^39 et 2^40. Le lecteur HotXLS applique précisément cette règle : il accumule les chiffres, et uniquement lorsque la chaîne fait dix caractères de large et que la valeur accumulée se situe au niveau ou au-dessus du seuil intermédiaire, il soustrait le module complet pour récupérer la valeur signée. Une chaîne de neuf caractères est toujours non négative, quelle que soit sa longueur.

L'encodeur est son image miroir. Une valeur non négative est convertie chiffre par chiffre et éventuellement complétée par des zéros à la largeur demandée, et elle est rejetée si elle dépasse le plafond positif de la base ou si la largeur demandée est trop étroite pour la contenir. Une valeur négative est d'abord ramenée dans la plage en lui ajoutant le module complet, ce qui la transforme en une valeur dont la représentation de base comporte toujours dix chiffres, puis les chiffres sont émis avec des zéros non significatifs pour remplir la largeur. La vérification de plage unique partagée, les limites inférieures et supérieures symétriques par base, est ce qui maintient la cohérence entre DEC2BIN, DEC2OCT et DEC2HEX à leurs limites.

Cela laisse les conversions d'une base à une autre, telles que HEX2BIN et OCT2HEX, qui changent de base sans passer par le décimal dans le nom de la fonction. L'implémentation ne comporte pas de routine distincte pour chaque paire ordonnée. Elle analyse la chaîne d'entrée en une valeur décimale signée à l'aide de la base source, puis formate cette valeur décimale dans la base de destination. Le décimal est le pivot. Une routine d'analyse et une routine de formatage, composées, courent chaque combinaison, et comme les deux moitiés partagent la même convention signée à dix chiffres, une valeur négative survit au voyage avec son signe intact.

Les nombres complexes sont des chaînes, le travail est donc l'analyse

Excel ne possède pas de type de données complexe. Une valeur complexe est la chaîne "a+bi", et chaque fonction de la famille IM prend ces chaînes en entrée et en renvoie une. COMPLEX construit la chaîne à partir d'une partie réelle et d'une partie imaginaire. IMSUM, IMSUB, IMPRODUCT et IMDIV analysent leurs arguments, effectuent l'arithmétique sur les parties numériques et formatent le résultat en retour sous forme de chaîne. Le travail numérique relève de l'algèbre de base. La difficulté réside entièrement dans le fait de transformer le texte en deux nombres à virgule flottante de manière fiable, et c'est là que l'analyseur interne démontre toute son utilité.

Deux détails de cet analyseur sont faciles à manquer. Le premier est l'unité imaginaire nue. La chaîne "i" signifie une fois i, pas zéro et pas une erreur, donc lorsque le coefficient devant le suffixe est vide ou se résume à un signe plus, l'analyseur doit le lire comme la valeur 1, et un simple moins comme -1. Si vous omettez cela, IMSUM("i","i") cesse de valoir 2i. Le second est la collision entre la notation scientifique et le signe qui sépare les parties réelle et imaginaire. L'analyseur trouve ce séparateur en recherchant un plus ou un moins, mais un nombre écrit comme "1.5E-3" contient un moins qui appartient à l'exposant. La recherche refuse donc de traiter un plus ou un moins comme séparateur lorsque le caractère immédiatement précédent est e ou E. Sans cette protection, la partie réelle serait coupée en deux au niveau du signe de l'exposant et l'analyse échouerait sur une entrée parfaitement valide.

Le suffixe lui-même est conservé plutôt que normalisé. Excel accepte à la fois i et j, et HotXLS se souvient de celui utilisé par l'entrée afin que le résultat formaté porte la même lettre. Le formatage applique ensuite les raccourcis conventionnels : une partie imaginaire valant un s'affiche comme le simple suffixe, moins un sous la forme -i, une partie imaginaire nulle se réduit à un réel simple, et une partie réelle nulle supprime le 0+ initial.

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Engineering');
    // Negative input: a ten-bit two's complement, places argument ignored.
    Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
    // Complex multiply on two "a+bi" strings.
    Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
  finally
    Book.Free;
  end;
end;

Les fonctions complexes transcendantes, dont IMSQRT, IMEXP, IMLN et IMPOWER, ne fonctionnent pas en coordonnées cartésiennes. Elles convertissent la valeur analysée en forme polaire, appliquent l'opération sur le module et l'argument, et effectuent la conversion inverse. Une racine carrée divise l'argument par deux et prend la racine du module. Une puissance multiplie l'argument et élève le module. Procéder autrement impliquerait de redéfinir chaque identité sous forme cartésienne, ce qui représenterait plus de code et moins de stabilité numérique près des points de branchement.

Opérateurs au niveau du bit et le dépassement à vérifier en premier

Excel 2013 a ajouté BITAND, BITOR, BITXOR, BITLSHIFT et BITRSHIFT. Les opérandes sont contraints : chacun doit être un entier non négatif ne dépassant pas 2^48 moins 1, et tout argument fractionnaire ou négatif constitue une erreur numérique. Ce plafond est assez généreux pour couvrir tout ensemble réaliste de drapeaux (flags) tout en restant bien dans la plage de représentation exacte d'un double, ce qui est important car Excel transmet chaque argument numérique sous forme de valeur à virgule flottante.

Les fonctions de décalage comportent la seule règle d'ordonnancement qui pose réellement problème. Un décalage à gauche peut produire une valeur bien supérieure à son entrée, et si vous effectuez d'abord le shl pour inspecter le résultat ensuite, vous avez déjà dépassé la capacité du type Int64 et le test n'a plus de sens. La vérification doit intervenir avant le décalage. HotXLS compare l'opérande au plafond décalé à droite de la quantité de décalage, et n'effectue le décalage à gauche réel que si l'opérande y correspond. Une valeur de décalage au-delà de 53 bits est rejetée d'emblée, et un décalage négatif inverse simplement la direction, de sorte que BITLSHIFT avec un compte négatif se comporte comme un décalage à droite. Ce principe se généralise bien au-delà de cette seule fonction : lorsqu'un garde-fou existe pour empêcher un dépassement, il doit s'exécuter sur les entrées, jamais sur le résultat qu'il était censé protéger.

// Bitwise calls evaluate the same way through Calculate.
Sheet.Cells[3, 1].Value := Sheet.Calculate('=BITAND(13,11)');    // 9
Sheet.Cells[4, 1].Value := Sheet.Calculate('=BITLSHIFT(5,2)');   // 20
Sheet.Cells[5, 1].Value := Sheet.Calculate('=BITRSHIFT(40,3)');  // 5

Les fonctions futures et le préfixe de nom _xlfn

Les opérateurs au niveau du bit et une longue liste d'autres ajouts postérieurs à 2007 interagissent avec un schéma de nommage qui n'a rien à voir avec ce qu'ils calculent et tout à voir avec la manière dont Excel les stocke. Le format binaire d'origine des feuilles de calcul attribuait à chaque fonction intégrée un emplacement numérique dans une table fixe. Les fonctions inventées après le gel de cette table n'ont pas d'emplacement. Pour enregistrer une telle fonction dans un fichier et pour qu'un Excel moderne la reconnaisse, le nom est écrit avec un préfixe _xlfn., de sorte que BITAND est stocké sous la forme _xlfn.BITAND sur le disque bien que l'utilisateur ne saisisse jamais que BITAND.

Le piège est que cette règle n'est pas uniforme. Certaines fonctions plus récentes ont reçu des emplacements de table et sont écrites nues, tandis que quelques fonctions masquées existantes sont également écrites sans préfixe malgré leur ancienneté. HotXLS conserve une liste blanche explicite des noms nécessitant le préfixe, l'ajoute à l'écriture et le supprime à la lecture, afin que le texte de formule que vous définissez et relisez soit toujours le nom propre visible dans Excel. Vous définissez =BITLSHIFT(5,2), le fichier contient _xlfn.BITLSHIFT, et la valeur renvoie 20 quoi qu'il en soit. Le préfixe est un détail de stockage qui ne devrait jamais fuir dans les formules que vous manipulez dans votre code.

Assembler le tout dans une feuille de calcul

La surface publique de tout cela est réduite. Créez un TXLSXWorkbook, ajoutez une feuille de calcul et écrivez une formule dans une cellule via Cells[Row, Col].Formula puis recalculez, ou évaluez une expression directement avec la méthode Calculate de la feuille, qui compile la formule par rapport à cette feuille et renvoie un type Variant. Les exemples ci-dessus utilisent Calculate car il présente le résultat d'un appel d'ingénierie unique sans l'état de la feuille environnante, mais les mêmes fonctions s'évaluent de manière identique dans de réelles formules de cellules lors du recalcul du classeur.

Les encodages sont la partie à garder à l'esprit, pas les sites d'appels. Une chaîne binaire n'est signée qu'à dix chiffres et seulement au-delà du seuil intermédiaire pour sa base. Un nombre complexe est du texte, un coefficient imaginaire vide vaut un et l'analyseur ignore le e d'un exposant. A décalage à gauche est vérifié avant de décaler. Maîtrisez ces quatre faits et la famille d'ingénierie cesse d'être une source de surprises liées au signe.

If you are wiring your own domain math into the same engine, the mechanics of registering a handler and returning values are covered in our article on extending the formula engine with custom functions, and when those formulas have to reach across sheets by name rather than by cell address, the walkthrough on defined names and cross-sheet formulas shows how the references resolve. The engineering functions described here ship as part of the HotXLS spreadsheet component for Delphi and C++Builder, alongside the reading, writing, and calculation APIs covered elsewhere on this blog.