Premier article d'une série s'intéressant à la réalisation de diverses opérations de calculs sur des nombres de plus de 15 chiffres sans perte de précision comme c'est normalement le cas.
(MAJ 17/03/2014)
Problématique
Il existe une limite au nombre de chiffres significatifs utilisable par Excel.
Si l'on saisit un nombre de plus de 15 chiffres, Excel enregistrera la saisie comme une chaîne de texte (gênant pour faire des calculs).
Saisie d'un nombre de 15 chiffres
(Saisie sans les guillemets)
Avec 15 chiffres, pas de problème, c'est bien un nombre sans perte de précision.
Saisie d'un nombre de 16 chiffres
Dans ce cas Excel mémorise la saisie en tant que chaîne de texte (alphanumérique).
Formule ayant un résultat dépassant 15 chiffres
Si l'on génère une formule renvoyant un résultat de plus de 15 chiffres, les chiffes après le 15eme (les moins significatifs) seront remplacés par des 0.
Pourquoi s'embêter avec cela, qui en a besoin !
J'ai été une fois confronté à ce problème :
J'en ai eu besoin pour vérifier un IBAN (International Bank Account Number) via le calcul de sa clé. Or ce numéro peut aller jusqu'à 34 caractères (27 pour la France) !
De plus cette question a été postée sur le forum plusieurs fois et à cette époque la seule réponse a été du VBA (macro) mais maintenant nous avons de puissantes fonctions permettant d'aller plus loin dans les formules.
Le défi sera donc de créer des fonctions de calculs similaires à SOMME, PRODUIT, MAX, MIN, MOYENNE, GRANDE.VALEUR, PETITE.VALEUR... pouvant travailler sur des chaînes de texte représentant des nombres de plus de 15 chiffres.
Des fonctions LAMBDA pour dépasser cette limite
Je vous proposer de commencer pas créer deux fonctions LAMBDA, GMAX et GMIN permettant de trouver la plus grande/petite valeur.
Voir :
Cahier de charges :
- Fonctionnera sur les valeurs réelles (positif, négatif).
- Fonctionnera sur des chaînes de texte ou sur du numérique.
- On pourra ignorer ou non les cellules vides.
- On pourra activer un formatage en groupe de 3 chiffres.
Les formules sont proposées en l'état. Je ne garantis pas la justesse des résultats bien que je les ai testées sur de nombreux exemples.
Fonction GMAX
Principe général de la fonction
On va trier les valeurs en ordre décroissant et récupérer la première de la liste qui devrait donc être la plus grande.
Les principales difficultés :
- Le tri se fait alphabétiquement en se basant sur le 1er caractère puis le suivant etc.
On a donc par exemple cet ordre : 1>10>12>2>200>3>300... (d'abord ce qui commence par 1 puis par 2 ...).- On va donc ne trier que les valeurs ayant le plus de caractères (dans les valeurs d'exemple précédentes, on évaluera seulement 200 et 300).
- Le signe - des valeurs négatives fausse le décompte des caractères.
- On va traiter les valeurs positives et négatives séparément.
- La fonction TRIER renvoie par exemple ce classement (ordre décroissant) : -92>9>-3>3>-2>2. On a -92 puis 3, on doit donc bien séparer les valeurs positives des négatives. Mais on a -92 puis -3 puis -2 (ordre incorrecte, pour nous -2 est plus "grand" que -3).
- Les valeurs positives devront être en ordre décroissant et les valeurs négatives en ordre croissant.
- En se basant sur le nombre de caractères, on est confronté à un problème avec les nombres décimaux (par exemple 5,32 a plus de caractères que 8).
- On uniformisera préalablement le nombre de caractères de la partie décimale (pour les valeurs de l'exemple précédent, on évaluera 5,32 à 8,00).
Facile ! 😊
Création des fonctions préliminaires
Nous allons créer 3 fonctions outils qui nous serviront a réaliser certaines tâches présentes aussi dans la fonction GMIN.
Fonction _NET
Rôle
Nettoyer les chaines des caractères indésirables, supprimer les séparateurs de milliers (ceci pouvant fausser les résultats).
Principes
- On remplace les espaces, espaces insécables (CAR(160)) par une chaîne vide et on supprime les caractères de contrôles avec la fonction EPURAGE.
Syntaxe
=LAMBDA(Chaine;EPURAGE(SUBSTITUE(SUBSTITUE(Chaine;" ";"");CAR(160);"")))Argument
- Chaîne : Chaîne ou matrice de chaînes à nettoyer (obligatoire).
Retour
On renvoie une chaine ou une matrice de chaîne.
Fonction _UNIF.DEC
Rôle
Uniformiser le nombre de décimales.
Principes
- On ajoute autant de 0 que nécessaire afin d'avoir le même nombre de décimales en se passant sur la valeur ayant le plus de caractère dans sa partie décimale.
Syntaxe
=LAMBDA(Plage;LET(
mPD; MAP(Plage;LAMBDA(v;INDEX(FRACTIONNER.TEXTE(v;",");1;2)));
mNbCPD; SIERREUR(NBCAR(mPD);0);
mxNbComp; MAX(mNbCPD);
mNbComp; mxNbComp-mNbCPD;
ASSEMB.H(SI(Plage="";0;"")&Plage&SI((mNbCPD=0)*(mxNbComp);",";"")&REPT("0";mNbComp);mNbComp)
))Argument
- Plage : Valeur ou matrice de valeurs dont la partie décimale est à uniformiser (obligatoire).
Variables
- Plage : Plage à traiter.
- mPD : Matrice des valeurs la partie décimale.
- mNbCPD : Matrice du nombre de caractères (SIERREUR pour gérer le cas des nombres entiers = sans partie décimale).
- mxNbComp : Plus grand nombre de décimales.
- mNbComp : Matrice du nombre de 0 nécessaire pour faire le complément.
Retour
On renvoie une matrice de 2 colonnes. La 1ere colonne contient les chaînes complétées, la deuxième, le nombre de 0 ajouté.
On gère le cas des valeurs vides en mettant un 0 en remplacement, pour le cas des entiers on ajoute une virgule sauf s'il n'y a pas de décimales.
Fonction _SEP
Rôle
Répartir les chiffres de la partie entière d'un nombre en groupe de 3 (séparateur de milliers).
Principes
- On intercale un espace tous les 3 chiffres dans la partie entière en allant dans le sens droite>gauche.
Syntaxe
=LAMBDA(Chaine;LET(
mCh; FRACTIONNER.TEXTE(Chaine;",");
mPe; INDEX(mCh;1;1);
nbC; NBCAR(mPe);
s; SEQUENCE(;nbC;nbC;-1);
CONCAT(SI(
SI(s<>nbC;MOD(s;3)=0);
" ";
"")
&STXT(mPe;nbC-s+1;1))&SIERREUR(","&INDEX(mCh;1;2);"")
))Argument
- Chaîne : Chaîne unique à fractionner (obligatoire).
Variables
- Chaîne : La chaîne à traiter.
- mCh : Matrice séparant la partie entière de la partie décimale.
- mPe : Matrice d'une valeur contenant la partie entière.
- nbC : Nombre de caractères de la partie entière.
- s : Séquence de valeurs à rebours pour parcourir la chaîne mPe de droite à gauche.
Retour
On renvoie une chaine constituée de la partie entière modifiée, concaténée à une virgule et à la partie décimale si elle existe.
Pour la partie entière, on ajoute un à un les caractères en commençant par celui de droite (d'où s à rebours). Si la valeur de s est multiple de 3 (via MOD), on ajoute, à gauche, un espace sauf dans le cas où c'est le dernier caractère (on obtient 123_456 et non _123_455).
Création de la fonction GMAX
Rôle
Renvoyer la plus grande valeur.
Principes
- On cherche la 1ere valeur des valeurs positives classées en ordre décroissant et à défaut la 1ere des valeurs négatives classé en ordre croissant.
Syntaxe
=LAMBDA(Plage;Ignorer_vide;Separateur;LET(
mVNet; DANSCOL(_NET(Plage)&"");
mVP; _UNIF.DEC(FILTRE(mVNet;(GAUCHE(mVNet;1)<>"-")*(SI(Ignorer_vide;mVNet<>"";1))));
mVN; _UNIF.DEC(FILTRE(mVNet;GAUCHE(mVNet;1)="-"));
mNbCP; NBCAR(INDEX(mVP;0;1));
mNbCN; NBCAR(INDEX(mVN;0;1));
mVPMax; SIERREUR(INDEX(TRIER(FILTRE(mVP;mNbCP=MAX(mNbCP));;-1);1;0);CAR(23));
mVNMin; SIERREUR(INDEX(TRIER(FILTRE(mVN;mNbCN=MIN(mNbCN));;1);1;0);CAR(23));
mMax; SI(mVPMax=CAR(23);SI(mVNMin=CAR(23);#N/A;mVNMin);mVPMax);
vMax; INDEX(mMax;1;1);
vMaxNet1; STXT(vMax;1;NBCAR(vMax)-INDEX(mMax;1;2));
vMaxNet2; SI(DROITE(vMaxNet1;1)=",";SUBSTITUE(vMaxNet1;",";"");vMaxNet1);
SI(Separateur;_SEP(vMaxNet2);vMaxNet2)
))Arguments
- Plage : Plage/Matrice de valeurs à évaluer (obligatoire).
- Ignorer_vide : Booléen, indiquant si on ignore les valeurs vides sinon elles sont prises en compte comme des 0 (obligatoire).
- Separateur : Booléen, indiquant si le résultat doit afficher des séparateurs de milliers (obligatoire).
Variables
- mVNet : Matrice des valeurs de Plage passées en une colonne, nettoyées et converties en texte.
- mVP : Matrice des valeurs positives ou vides avec uniformisation du nombre de décimales.
- mVN : Matrice des valeurs négatives avec uniformisation du nombre de décimales.
- mNbCP: Matrice du nombre de caractères des valeurs positives.
- mNbCN : Matrice du nombre de caractères des valeurs négatives.
- mVPMax: Matrice d'une ligne du résultat des valeurs positives. En cas d'erreur, pas de valeurs positives, on renvoie un caractère peu courant (CAR(23)):
- Colonne 1 : La valeur positive ayant le plus de caractères (1ère valeur dans la liste triée en ordre décroissant).
- Colonne 2 : Son complément en 0 (généré par _UNIF.DEC).
- mVNMin : Matrice d'une ligne du résultat des valeurs négatives. En cas d'erreur, pas de valeurs négatives, on renvoie un caractère peu courant (CAR(23)):
- Colonne 1 : La valeur négative ayant le moins de caractères (1ère valeur dans la liste triée en ordre croissant).
- Colonne 2 : Son complément en 0 (généré par _UNIF.DEC).
- mMax : Matrice de la valeur maximale positive (mVPMax) ou à défaut celle de la valeur maximale négative (mVNMin), sinon #N/A (Plage entièrement vide).
- vMax : Valeur maximale.
- vMaxNet1 : Valeur maximale sans les 0 en trop à droite.
- vMaxNet2 : Valeur maximale sans la virgule si plus de décimale (entier).
Retour
On renvoie le résultat en appliquant un séparateur de milliers si demandé via Separateur.
Illustration des valeurs des variables
Fonction GMIN
Même principe que GMAX.
=LAMBDA(Plage;Ignorer_vide;Separateur;LET(
mVNet; DANSCOL(_NET(Plage)&"");
mVP; _UNIF.DEC(FILTRE(mVNet;(GAUCHE(mVNet;1)<>"-")*(SI(Ignorer_vide;mVNet<>"";1))));
mVN; _UNIF.DEC(FILTRE(mVNet;GAUCHE(mVNet;1)="-"));
mNbCP; NBCAR(INDEX(mVP;0;1));
mNbCN; NBCAR(INDEX(mVN;0;1));
mVPMin; SIERREUR(INDEX(TRIER(FILTRE(mVP;mNbCP=MIN(mNbCP));;1);1;0);CAR(23));
mVNMax; SIERREUR(INDEX(TRIER(FILTRE(mVN;mNbCN=MAX(mNbCN));;-1);1;0);CAR(23));
mMin; SI(mVNMax=CAR(23);SI(mVPMin=CAR(23);#N/A;mVPMin);mVNMax);
vMin; INDEX(mMin;1;1);
vMinNet1; STXT(vMin;1;NBCAR(vMin)-INDEX(mMin;1;2));
vMinNet2; SI(DROITE(vMinNet1;1)=",";SUBSTITUE(vMinNet1;",";"");vMinNet1);
SI(Separateur;_SEP(vMinNet2);vMinNet2)
))Merci pour votre attention bienveillante.
Un commentaire