mardi 14 octobre 2014

Excel: comment afficher dans une case le libellé de sa colonne avec le format A, B, ...AA, AB, ...

Voici un petit exercice pour se dégourdir l'esprit:
comment afficher dans une case, le libellé de la colonne de cette case, avec le format A, B, C, ...., Z, AA, AB, .....
J'ai eu besoin de cette fonction pour réaliser l'inventaire des colonnes d'un tableau. En d'autre terme, j'avais besoin de faire correspondre à chaque libellé de colonne, son libellé de colonne Excel.
Malheureusement, la fonction "=colonne()" sans argument est parfaite pour obtenir le n° d'indice de la colonne (à partir de 1), mais pas pour obtenir l'équivalent du format traditionnel, à savoir A, B, C, etc....

Pour cela, j'ai choisi de traduire le numéro d'indice de la colonne en base 26 (car il y a 26 lettres dans notre alphabet, de A à Z). Par simplicité, l'opération se fait en deux temps. Sur une première case, je récupère le numéro d'indice, c'est dire le résultat de la fonction "=COLONNE( )". Ci dessous, on peut voir que j'obtiens 3 pour la colonne C, 4 pour la colonne D, etc...
Ensuite, sur la case juste en dessous de la première contenant le n° d'indice de la colonne, j'utilise cette formule:

=SI(C1 < 27;CAR(64+C1);CAR(ENT((C1-1)/26)+64) & CAR(MOD(C1-1;26)+65))

Explications:
  • 65 est le code ASCII de la lettre majuscule "A".
  • MOD( ), la fonction qui donne le reste de la division entière (cad modulo).
  • ENT( ), la fonction qui donne la partie entière d'une division.
  • CAR( ), la fonction qui renvoie le caractère d'un code ASCII: CAR(65)='A'
Remarque importante:
Cette fonction n'est valable que pour le valeurs d'indice de 1 à 26*26 (cad 676). Pour les colonnes au delà de 676, je vous laisse libre de trouver soit une autre formule, soit la même en ajoutant un étage supplémentaire de "=SI( C1 < 677 ; ..... )" .

En résumé, voici les deux formules pour les 676 premières colonnes.
Formule en C1: =COLONNE( )
Formule en C2: =SI(C1 < 27;CAR(64+C1);CAR(ENT((C1-1)/26)+64) & CAR(MOD(C1-1;26)+65))

A votre bon cœur pour trouver une formule plus élégante, car il faut bien l'admettre, celle que je propose étant plutôt rustique.