Article complet: Tirer une formule en sautant des lignes (ou des colonnes) avec EXCEL

On doit parfois travailler avec des données qui, bien qu'ordonnées, ne permettent pas une analyse efficace. Il en est ainsi dans le cas de données qui sont positionnées en bloc verticaux,alors qu'on les souhaiterait disposées en lignes sans trop de manipulations et sans devoir écrire une macro en VBA.

Comment faire ? Suivez-moi, c’est par ici…

[Suite:]

La solution réside dans l’utilisation astucieuse des fonctions DECALER(), LIGNE() et COLONNE().

DECALER() permet de pointer vers une plage de cellules en faisant varier le chemin relatif entre une plage de référence et la cible. Vous noterez que c’est une des très rares fonctions d’Excel qui renvoient une plage au lieu d'une valeur. Elle utilise cinq paramètres, trois obligatoires et deux optionnels :
DECALER(PlageOrigine; DécalageLignes; DécalageColonnes; [NombreLignesPlageCible]; [NombreColonnesPlageCible]).
Vous retiendrez que lorsqu’ils sont omis, les deux derniers paramètres sont identiques aux nombres de lignes et de colonnes de PlageOrigine.

LIGNE() utilisée sans paramètres renvoie le numéro de ligne de la cellule qui utilise la fonction.

COLONNE() utilisée sans paramètres renvoie le numéro de la colonne qui utilise la fonction.

Avant de mettre cela en place et à la lueur de la fonction DECALER(), raisonnons sur base du schéma suivant.

En fixant PlageOrigine en $B$1(*), nous remarquons que pour chaque ligne de la plage d’arrivée (D2 : D5), le décalage est de 0 lignes à partir de $B$1, puis de 4 en 4 lignes. En effet :

Nous avons un bel exemple de résolution d’une équation linéaire de type y = ax+b, a étant le pas de progression (la pente de la droite).
Isolons b pour avoir y – ax = b, soit pour la paire (2,0), 0 – 4*2 = -8. L’équation devient y = 4x-8.

Cette équation sera utilisée au sein de DECALER() pour progresser de 4 lignes à partir de B1 grâce à la formule
=DECALER($B$1;4*LIGNE()-8;0)

Si nous la tirons vers le bas, Excel « sautera les lignes » pour afficher en D5 la valeur de D13.

Nous serions tentés d'écrire une nouvelle formule en E pour la date et en F pour la valeur. Cependant, il est préférable de n’écrire qu’une seule formule au sein d’un tableau lorsque c’est possible.

Pour tirer vers la droite à partir de D alors que l'on descend en B, nous utiliserons COLONNE(), ici aussi de façon astucieuse. En D, colonne 4, nous décalons de 0 lignes vers le bas. En E, colonne 5, nous décalerons d’une ligne vers le bas obtenir la date, soit 5 – 4, et en F, colonne 6, nous décalerons deux lignes vers le bas pour récupérer le montant, soit 6-4.

La formule utilisée en D2 sera donc
=DECALER($B$1;4*LIGNE()-8+COLONNE()-4;0)

Tirée sur les colonnes D et E puis jusqu’à la ligne 6, la formule permet de récupérer la liste de nos données. La ligne remplie de 0 montre la fin du tableau, et il ne restera qu'à en effacer les formules.

Excel est plein de ressources, soyez astucieux…

Vous souhaitez d'autres astuces? Vous connaissiez celle-ci?

(*) Si l’utilisation des $ pour référencer une plage ne vous est pas familière, consultez mon tutoriel sur le sujet