Supposons que vous ayez une feuille de calcul contenant une série de formules dans les cellules A1: A3. La cellule A1 contient la formule = Sheet1! B4, la cellule A2 contient = Sheet1! B18 et la cellule A3 contient = Sheet1! B32. Vous devrez peut-être continuer ce modèle dans la colonne, de sorte que A4 contienne = Sheet1! B46, etc.

Le problème est que si vous copiez simplement les cellules, le modèle ne se poursuit pas. Au lieu de cela, les formules sont ajustées en fonction de la relation entre la cellule cible et la cellule source. Ainsi, si vous collez A1: A3 dans A4: A6, alors A4 contiendra = Sheet1! B7, ce qui n’est pas ce que vous voulez. (Cela se produit si vous copiez et collez spécifiquement ou remplissez les cellules en faisant glisser la poignée de recopie.)

Il n’y a aucun moyen de continuer un modèle tout en copiant une formule. Au lieu de cela, vous devez revoir la façon dont vous avez assemblé la formule en premier lieu.

Par exemple, considérons cette formule:

=INDIRECT("Sheet1!B"&((ROW()-1)*14)+4)

Cette formule construit une référence basée sur la position de la cellule dans laquelle la formule est placée. Si cette formule est placée dans la cellule A1, la fonction ROW renvoie 1, la ligne dans laquelle la formule est placée.

Ainsi, la formule devient ceci:

=INDIRECT("Sheet1!B"&((1-1)*14)+4)

=INDIRECT("Sheet1!B"&(0*14)+4)

=INDIRECT("Sheet1!B"&0+4)

=INDIRECT("Sheet1!B"&4)

=INDIRECT("Sheet1!B4")

Ce qui est renvoyé est la valeur de Sheet1! B4, exactement comme initialement souhaité.

Cependant, lorsque vous copiez cette formule dans la colonne, la fonction ROW renvoie quelque chose de différent dans chaque ligne. En effet, la fonction ROW devient un moyen d’augmenter le décalage de chaque formule de 14 lignes par rapport à celle qui la précède – exactement ce que vous vouliez.

Vous pouvez également utiliser une approche légèrement différente, cette fois en utilisant la fonction OFFSET:

=OFFSET(Sheet1!$B$4,(ROW()-1)*14,0)

Cette formule saisit une valeur basée sur la ligne dans laquelle la formule est placée (encore une fois, en utilisant la fonction ROW) et le décalage de la cellule Sheet1! B4.

Placée dans la première ligne d’une colonne, puis copiée dans cette colonne, la formule renvoie des valeurs selon le modèle souhaité.

Une autre approche consiste à créer directement les formules souhaitées. Vous pouvez le faire au mieux en suivant ces étapes:

  1. Commencez avec une nouvelle feuille de calcul vierge dans un classeur contenant une feuille de calcul nommée «Sheet1».

  2. Dans la cellule A1, entrez la formule = Sheet1! B4.

  3. Étant donné que le modèle de formule est toutes les 14 lignes, mettez en surbrillance la plage A1: A14. Seule la première cellule doit avoir la formule; les 13 autres sont vierges.

  4. Faites glisser la poignée de recopie (le petit carré dans le coin inférieur droit de la sélection vers le bas pour un bon nombre de lignes – peut-être 1 000 environ.

La plage A1: A1000 doit toujours être mise en surbrillance.

  1. Choisissez Edition | Aller à | Spécial. Excel affiche la boîte de dialogue Aller à Spécial. (Voir la figure 1.)

  2. Cliquez sur le bouton d’option Blancs, puis sur OK. Tous les espaces (vides)

les cellules de la sélection sont sélectionnées.

  1. Choisissez Edition | Supprimer | Décaler les cellules vers le haut /

Le résultat est que vous vous retrouvez avec uniquement les formules, avec le motif souhaité.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (3067) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.