Pierre Fauconnier

- Pierre, tu sais combien ça fait 1101101101_bin en décimal?
- Oui, ça vaut 877
- Ha ha, gros malin, tu t'es trompé, ça vaut -147
- Ok, toi, t'a encore joué avec BINDEC()... J'me trompe?
- Heu... Ben oui, j'ai utilisé BINDEC("1101101101") et Excel me renvoie -147
- Hé oui, BINDEC est limité à 9 bits pour les valeurs positives. Si tu utilises 10 bits, Excel va considérer que tu travailles avec une valeur négative. C'est comme ça, faudra t'y faire, mon bonhomme.
- ...
- A moi de te poser une colle: Ca vaut combien 101110110101101_bin?
- Attends, Pierre, Excel est mon ami, je passe par BINDEC et... Arf, ça me renvoie #NOMBRE!
- Ben oui, je t'ai dit que c'était limité à 9 ou 10 bits selon le signe du nombre.
- Mais comment je fais, alors, pour convertir un grand nombre binaire en décimal?
- Tu utilises ton cerveau et tu écris une jolie formule


Toutes versions avec SOMMEPROD

Par exemple, acceptée par toutes les versions d'Excel, on peut utiliser la formule suivante:

=SOMMEPROD(STXT(A1;LIGNE(INDIRECT("a1:a" & NBCAR(A1)));1)*2^(NBCAR(A1)-LIGNE(INDIRECT("a1:a" & NBCAR(A1))))))

- Dis, t'as pas plus simple?
- Plus simple au niveau du fonctionnement, non. Par contre, pour Excel 365, j'ai plus court:

=LET(V;A1;L;NBCAR(V);P;LIGNE(INDIRECT("a1:a" & L));SOMME(STXT(V;P;1)*2^(L-P)))

- Ok... Tu m'expliques?

On décortique la formule

Regardons d'abord la première formule, qui convient à toutes les versions d'Excel. Prenons par exemple 10111001_bin qui vaut 185_dec. On peut calculer comme ceci:
1*2^7 + 0*2^6+1*2^5+1*2^4+1*2^3+0*2^2+0*2^1+1*2^0 => 128+0+32+16+8+0+0+1=185.


Avec 10111001 en A1, on peut décomposer comme suit:

• INDIRECT("a1:a" & NBCAR(A1)) recompose l'adresse A1:A8 grâce à NBCAR puisque nous passons 8 digits;
• LIGNE(INDIRECT("a1:a" & NBCAR(A1))) va prendre chaque ligne de A1:A8 en matricielle, grâce à SOMMEPROD;
• STXT(A1;LIGNE(INDIRECT("a1:a" & NBCAR(A1)));1) prend chaque caractère de A1 en boucle;
• NBCAR(A1)-LIGNE(INDIRECT("a1:a" & NBCAR(A1)))) va donc passer de 8-1 à 8-8, c'est-à-dire par 7, 6, 5, 4, 3, 2, 1 et 0. On retrouve dès lors le développement vu ci-dessus.

SOMMEPROD, qui veut dire SOMME des PRODUITS, va donc multiplier le premier caractère par 2^7, le deuxième par 2^6, ... le dernier par 2^0.


Voici le développement sur une feuille Excel. Il ne reprend pas INDIRECT, qui sert simplement à recomposer l'adresse A1:A8 dans le cas d'une valeur binaire sur 8 bits. Cette adresse sert simplement à déterminer le nombre de boucles effectuées par la matricielle, on aurait tout aussi bien pu prendre B1:B8 ou XFD1:XFD8...




ET 365, ça apporte quoi?

La version EXCEL 365 utilise le même fonctionnement, mais LET permet de ne pas répéter les calculs dans la formule en utilisant des variables. Si l'on examine la formule toutes versions, on s'aperçoit que certains calculs sont effectués plusieurs fois:
=SOMMEPROD(STXT(A1;LIGNE(INDIRECT("a1:a" & NBCAR(A1)));1)*2^(NBCAR(A1)-LIGNE(INDIRECT("a1:a" & NBCAR(A1)))))). Ici, on s'aperçoit que A1 étant répété alors que c'est la seule cellule utilisée, la placer dans une variable permet de ne devoir changer A1 qu'à un seul endroit, celui de sa déclaration, si vous devez utiliser cette formule sur une autre cellule.

=LET(V;A1;L;NBCAR(V);P;LIGNE(INDIRECT("a1:a" & L));SOMME(STXT(V;P;1)*2^(L-P))

On s'aperçoit au passage que la variable P utilise la variable L déclarée avant, ce qui signifie qu'une variable de LET peut utiliser une autre variable déclarée avant elle dans la fonction. Sympa, non?


Conclusions

On a fait sauter la limite des 9 digits de BINDEC pour la conversion d'une valeur binaire en valeur décimale




Outre la transformation d'un binaire en décimal, j'espère que ce billet vous aura fait découvrir

• une manière de créer des formules qui bouclent grâce à INDIRECT et LIGNE;
• l'utilisation de LET qui n'est pas loin de révolutionner l'écriture de formules complexes dans Excel.