Fonctions d'ingénierie Delphi : conversion de base, mathématiques complexes

La famille d'ingénierie dans Excel se lit comme le coin le plus facile de la référence des fonctions. DEC2BIN transforme un nombre en chaîne binaire. HEX2DEC le retransforme (turns it back). IMSUM ajoute deux nombres complexes. Chacune ressemble à un exercice de formatage. Ce n'est pas le cas (They are not). Derrière ces noms se cache un encodage (encoding) en complément à deux (two's complement) sur dix bits que la plupart des développeurs n'ont pas touché depuis un cours d'architecture informatique, un format de nombres complexes qui vit entièrement à l'intérieur de chaînes, et des opérateurs au niveau du bit (bitwise operators) qui feront déborder (overflow) silencieusement un entier de 64 bits si vous décalez (shift) avant de vérifier. Un moteur de feuille de calcul qui reproduit exactement Excel ne peut rien en arrondir (round off)

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 (per-base thresholds). L'arithmétique complexe concerne l'analyse (parsing) et le formatage d'une chaîne. Les opérations au niveau du bit (Bitwise operations) consistent à rester dans les limites (bounds) de Int64. Cet article parcourt chaque groupe tel que HotXLS l'implémente, avec les appels de feuille de calcul (worksheet calls) que vous écririez réellement

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

La direction avant (forward direction) est la partie à laquelle tout le monde s'attend. DEC2BIN(9) donne "1001", et un deuxième argument facultatif remplit à gauche (left-pads) le résultat à une largeur fixe. Le piège est l'entrée négative. Excel n'écrit pas de signe moins. Il code la valeur sous forme de chaîne en complément à deux à dix chiffres dans la base cible, c'est pourquoi DEC2BIN(-5,10) renvoie "1111111011" plutôt que n'importe quoi avec un signe. L'argument de places (places argument) est ignoré une fois que la valeur est négative, car l'encodage est déjà épinglé à dix chiffres

Dix chiffres sont un budget fixe, et ce budget définit la plage (range) représentable par base. En binaire, la grandeur (magnitude) qui bascule dans la moitié négative est 512, et le module de bouclage (wrap modulus) est 1024, donc une chaîne binaire n'est signée que lorsqu'elle fait exactement dix caractères de long et que sa valeur est d'au moins 512. La même idée évolue (scales) avec la base. L'octal utilise un demi-seuil (half threshold) de 2^29 et un module complet (full modulus) de 2^30. L'hexadécimal utilise 2^39 et 2^40. Le lecteur HotXLS applique exactement cette règle : il accumule les chiffres, et ce n'est que lorsque la chaîne a une largeur de dix caractères et que la valeur accumulée se situe au niveau ou au-dessus du demi-seuil qu'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 taille

L'encodeur est l'image miroir. Une valeur non négative est convertie chiffre par chiffre et éventuellement remplie de zéros (zero-padded) à la largeur demandée, et elle est rejetée si elle dépasse le plafond (ceiling) positif de la base ou si la largeur demandée est trop étroite pour la contenir. Une valeur négative est d'abord amenée dans la plage (range) en ajoutant le module complet, ce qui la transforme en une valeur dont la représentation de base est toujours de dix chiffres, puis les chiffres sont émis avec des zéros non significatifs (leading zeros) pour remplir la largeur. La vérification de plage (range check) partagée unique, les limites inférieures et supérieures symétriques par base, est ce qui maintient DEC2BIN, DEC2OCT et DEC2HEX cohérentes les unes avec les autres sur leurs bords

Il reste les conversions inter-bases (cross-base conversions), celles comme HEX2BIN et OCT2HEX qui changent de base sans passer par la décimale dans le nom de la fonction. L'implémentation ne comporte pas de routine distincte pour chaque paire ordonnée (ordered pair). Elle analyse (parses) 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. La décimale est le pivot. Une routine d'analyse (parse routine) et une routine de formatage (format routine), composées, couvrent toutes les combinaisons, et parce que 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 (parsing)

Excel n'a 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 (hands one back). COMPLEX construit la chaîne à partir d'une partie réelle et d'une partie imaginaire. IMSUM, IMSUB, IMPRODUCT et IMDIV analysent (parse) leurs arguments, font l'arithmétique sur les parties numériques, et formatent le résultat en une chaîne. Le travail numérique est de l'algèbre de premier cycle (undergraduate algebra). La difficulté réside entièrement dans la transformation fiable du texte en deux nombres à virgule flottante, et c'est là que l'analyseur interne (internal parser) gagne son salaire (earns its keep)

Deux détails de cet analyseur (parser) sont faciles à tromper (get wrong). 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 est un signe plus solitaire (lone plus sign), l'analyseur doit le lire comme la valeur 1, et un moins solitaire comme -1. Ignorez cela et IMSUM("i","i") cesse d'être 2i. Le second est la notation scientifique en collision avec le signe qui sépare les parties réelle et imaginaire. L'analyseur trouve ce séparateur en recherchant (scanning) un plus ou un moins, mais un nombre écrit "1.5E-3" contient un moins qui appartient à l'exposant. L'analyse (scan) refuse donc de traiter un plus ou un moins comme séparateur lorsque le caractère qui le précède immédiatement est e ou E. Sans cette garde (guard), la partie réelle serait déchiré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 que l'entrée a utilisé pour que le résultat formaté porte la même lettre. Le formatage applique ensuite les raccourcis conventionnels (conventional shorthands) : une partie imaginaire de un s'imprime comme juste le suffixe, moins un comme -i, une partie imaginaire nulle se réduit (collapses) à un simple réel, et une partie réelle nulle supprime le 0+ de début (leading)

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 (transcendental), parmi lesquelles IMSQRT, IMEXP, IMLN et IMPOWER, ne fonctionnent pas en coordonnées rectangulaires. Elles convertissent la valeur analysée (parsed value) sous forme polaire, appliquent l'opération sur le module et l'argument, et reconvertissent (convert back). Une racine carrée divise par deux (halves) l'argument et prend la racine du module. Une puissance multiplie l'argument et élève (raises) le module. Faire autrement signifierait redériver (re-deriving) chaque identité sous forme rectangulaire, ce qui représente à la fois plus de code et moins de stabilité numérique près des coupures de branche (branch cuts)

Les opérateurs au niveau du bit (Bitwise operators) et le dépassement (overflow) que vous devez 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 est une erreur numérique. Ce plafond (cap) est assez généreux pour couvrir tout ensemble d'indicateurs (flag set) réaliste tout en restant bien à l'intérieur de la plage exactement représentable d'un double, ce qui compte car Excel transmet chaque argument numérique sous forme de valeur à virgule flottante

Les fonctions de décalage portent l'unique règle d'ordonnancement qui mord (bites) véritablement. Un décalage vers la gauche (left shift) peut produire une valeur beaucoup plus grande que son entrée, et si vous effectuez le shl en premier et inspectez le résultat ensuite, vous avez déjà débordé (overflowed) Int64 et le test n'a pas de sens. La vérification doit venir avant le décalage. HotXLS compare l'opérande au plafond décalé vers la droite du montant du décalage, et ce n'est que si l'opérande correspond qu'il effectue le décalage réel vers la gauche. Une magnitude de décalage au-delà de 53 bits est rejetée d'emblée (outright), et un décalage négatif inverse simplement la direction, de sorte que BITLSHIFT avec un nombre (count) négatif se comporte comme un décalage vers la droite. Le principe se généralise bien au-delà de cette seule fonction : lorsqu'une garde (guard) existe pour empêcher le dépassement (overflow), elle doit s'exécuter sur les entrées, jamais sur le résultat qu'elle était censée 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 futures fonctions et le préfixe de nom _xlfn

Les opérateurs au niveau du bit (bitwise operators) et une longue liste d'autres ajouts d'après 2007 interagissent avec un schéma de nommage (naming scheme) qui n'a rien à voir avec ce qu'ils calculent et tout à voir avec la façon dont Excel les stocke. Le format d'origine de la feuille de calcul binaire attribuait à chaque fonction intégrée (built-in function) un emplacement numérique (numeric slot) dans un tableau fixe (fixed table). Les fonctions inventées après le gel de ce tableau n'ont pas d'emplacement. Pour enregistrer une telle fonction dans un fichier et la faire reconnaître par un Excel moderne, le nom est écrit avec un préfixe _xlfn., de sorte que BITAND est stocké sous le nom _xlfn.BITAND sur le disque, même si l'utilisateur ne tape jamais que BITAND

Le hic (catch) est que la règle n'est pas uniforme. Certaines nouvelles fonctions ont reçu des emplacements dans le tableau (table slots) et sont écrites nues (bare), tandis que quelques fonctions cachées héritées (legacy) sont également écrites sans préfixe malgré leur âge. HotXLS conserve une liste blanche (whitelist) explicite des noms nécessitant le préfixe, l'ajoute lors de l'écriture et le supprime (strips it) lors de la lecture, de sorte que le texte de la formule que vous définissez et relisez est toujours le nom propre destiné à Excel. Vous définissez =BITLSHIFT(5,2), le fichier contient _xlfn.BITLSHIFT et la valeur revient (comes back) à 20 quoi qu'il en soit. Le préfixe est un détail de stockage qui ne devrait jamais fuir (leak) dans les formules avec lesquelles vous travaillez dans le code

Mettre tout cela ensemble dans une feuille de calcul

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

Les encodages sont la partie à garder à l'esprit, pas les sites d'appel (call sites). Une chaîne binaire n'est signée qu'à dix chiffres et seulement passé le demi-seuil (half threshold) pour sa base. Un nombre complexe est du texte, un coefficient imaginaire vide est un, et l'analyseur enjambe (steps over) le e d'un exposant. Un décalage vers la gauche est vérifié avant qu'il ne se décale (shifts). Obtenez ces quatre faits (facts) correctement et la famille d'ingénierie cesse d'être une source de surprises décalées d'un signe (off-by-a-sign surprises)

Si vous câblez (wiring) vos propres mathématiques de domaine (domain math) dans le même moteur, les mécanismes d'enregistrement d'un gestionnaire (handler) et de renvoi de valeurs sont couverts dans notre article sur l'extension du moteur de formule avec des fonctions personnalisées, et lorsque ces formules doivent atteindre d'autres feuilles par leur nom plutôt que par leur adresse de cellule, la procédure pas à pas sur les noms définis et les formules inter-feuilles (cross-sheet) montre comment les références sont résolues. Les fonctions d'ingénierie décrites ici sont fournies dans le cadre du Composant de feuille de calcul HotXLS pour Delphi et C++Builder, aux côtés des API de lecture, d'écriture et de calcul abordées ailleurs sur ce blog