All posts tagged DECALER

Fonction DECALER : transposer une liste en tableau

Une fonction assez méconnue d’Excel, la fonction DECALER peut permettre de transposer des données en ligne (voir copie ci-dessous) en tableau. Nous avons une liste contenant séquentiellement un nom, un âge et une taille (ces infos pourraient par exemple être issues d’un fichier XML, dont on aurait « nettoyé » les balises). Nous voulons l’afficher dans un tableau, sans passer par une macro. C’est possible avec la fonction DECALER.

La fonction utilisée dans l’onglet tableau en A2 est :

=DECALER(liste!$A$1;3*(LIGNE(A2)-2)+COLONNE(A2)-1;0)

A chaque cellule de notre nouveau tableau, nous copions la valeur de l’onglet liste A1 notée $A$1 en référence absolue (quand on fera glisser la formule sur les autres cellules on fera toujours référence à la même cellule de départ A1), et nous décalons vers le bas pour lire les valeurs suivantes avec ce subtil calcul : 3*(LIGNE(A2)-2)+COLONNE(A2)-1

Pour la cellule A2, LIGNE(A2) = 2 et COLONNE(A2) = 1. Donc, 3*(LIGNE(A2)-2)+COLONNE(A2)-1 = 0. Donc on ne décale pas A1 vers le bas.

Quand on copie la formule dans les cellules B2 et C2, la formule s’auto-incrémente, pour les références relatives, pas pour les références absolues ($) :

En B2 :

=DECALER(liste!$A$1;3*(LIGNE(B2)-2)+COLONNE(B2)-1;

Cela fera en fait pour B2 =DECALER((liste!$A$1;3*(2-2)+2-1;0), donc on décale la lecture d’une ligne vers le bas. Et de 2 pour C2.

En A3 :

=DECALER(liste!$A$1;3*(LIGNE(A3)-2)+COLONNE(A3)-1;0)

Cela fera en fait pour A3 =DECALER((liste!$A$1;3*(3-2)+1-1;0), donc on décale la lecture de 3 lignes vers le bas. Et ainsi de suite.

Si nous avions eu un tableau à 5 colonnes, on aurait bein sûr remplacé le 3* par 5* !

Pour finir, pour avoir une fonction plus propre quand on fait glisser la formule trop bas dans le tableau et qu’on ne veut pas voir des « 0 » s’afficher, on peut écrire :

=SI(ESTVIDE(DECALER(liste!$A$1;3*(LIGNE(A2)-2)+COLONNE(A2)-1;0)); » »;DECALER(liste!$A$1;3*(LIGNE(A2)-2)+COLONNE(A2)-1;0))

Top
Page 1 of 1

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes