Formule générique
=OFFSET($B$5,(ROW(A1)*n)-1,0)
Sommaire
Pour copier des valeurs ou générer des références avec un modèle comme toutes les 3 lignes, toutes les 7 lignes, etc., vous pouvez utiliser une formule basée sur les fonctions OFFSET et ROW. Dans l'exemple illustré, la formule en D5 est:
=OFFSET($B$5,(ROW(D1)*3)-1,0)
Qui peut être copié dans la colonne D pour récupérer les nièmes valeurs de la colonne B.
Explication
Dans Excel, vous ne pouvez pas facilement copier des formules qui doivent ignorer des lignes ou des colonnes suivant un certain modèle, car les références dans la formule changeront automatiquement suivant la relation entre la cellule source d'origine et la nouvelle cellule cible. Cependant, avec un peu de travail, il est possible de construire des références de formule qui suivent des modèles spécifiques.
Dans ce cas, nous utilisons la fonction OFFSET, qui est conçue pour créer des références à d'autres cellules, ou plages de cellules, en fonction d'un point de départ ou d'une origine donné.
Dans l'exemple illustré, la cellule de départ est B5, fournie à OFFSET comme argument de référence, et fixée comme référence absolue afin qu'elle ne change pas lorsque la formule est copiée:
=OFFSET($B$5
Pour calculer la valeur correcte de l'argument des lignes, nous utilisons une sous-formule basée sur la fonction ROW:
(ROW(D1)*3)-1
La ligne reçoit la première cellule de la même colonne et renvoie 1. Ce résultat est multiplié par n, qui vaut 3 dans cet exemple pour obtenir 3.
Au fur et à mesure que la formule est copiée dans la colonne, la valeur renvoyée par ROW s'incrémente de 1, ce qui crée le «nième modèle».
La raison pour laquelle nous soustrayons 1 dans chaque cas est que la fonction OFFSET n'inclut pas la cellule de référence lorsque l'argument de lignes est appliqué. En d'autres termes, la compensation d'une ligne à partir de A1 renvoie A2:
=OFFSET(A1,1,0) // returns A2
La soustraction de 1 tient compte de ce comportement.
À partir de 1
Si vous souhaitez commencer à copier à la première ligne, puis suivre le motif tous les nièmes, vous pouvez ajuster la formule comme ceci:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
