Tim a une feuille de calcul dans laquelle la cellule B1 contient la formule = SOMME (A1: A7).

Il veut copier cette formule vers le bas et avoir la plage incrémentée de 7 lignes, de sorte que la cellule B2 contienne la formule = SUM (A8: A14), la cellule B3 contiendrait = SUM (A15: A21), etc. Le problème est que quand il la copie, chaque « extrémité » de la plage n’est incrémentée que de 1 où elle doit être incrémentée de 7 pour répondre à son besoin. Il se demande comment il peut faire en sorte qu’Excel effectue l’incrémentation appropriée.

Vous ne pouvez pas faire en sorte qu’Excel effectue l’incrémentation appropriée en utilisant le copier-coller; ça ne le fera tout simplement pas. La raison est simple: il y a des moments où l’incrémentation de 1 est logique du point de vue des formules. Étant donné qu’Excel ne peut pas lire dans vos pensées (au moins jusqu’à la prochaine version:>)), il suppose qu’il ne devrait augmenter que de 1.

La solution est de changer votre formule. En utilisant quelques fonctions de feuille de calcul, vous pouvez demander à Excel de construire la plage souhaitée pour la sommation. Prenons l’exemple suivant d’une formule qui fournira la somme souhaitée:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

Si vous placez cette formule dans la cellule B1, cela fonctionne car il examine le numéro de ligne (retourné par la fonction ROW) de la ligne dans laquelle la formule est contenue. Puisqu’il est dans la ligne 1, alors la formule est évaluée de cette manière par Excel:

=SUM(INDIRECT("A" & (ROW()-1)7+1 & ":A" & (ROW()-1)7+7))

=SUM(INDIRECT("A" & (1-1)7+1 & ":A" & (1-1)7+7))

=SUM(INDIRECT("A" & 07+1 & ":A" & 07+7))

=SUM(INDIRECT("A" & 0+1 & ":A" & 0+7))

=SUM(INDIRECT("A" & 1 & ":A" & 7))

=SUM(INDIRECT("A1:A7"))

=SUM(A1:A7)

Ce que vous obtenez en B1 est la somme que vous souhaitez. (La fonction INDIRECT utilise la valeur de la chaîne comme s’il s’agissait d’une plage réelle, ce que vous voulez.) Lorsque vous copiez la formule dans la colonne, à mesure que le numéro de ligne s’incrémente, la formule fournit les incréments appropriés de 7 aux deux extrémités de la gamme.

Il existe d’autres variantes de cette technique que vous pouvez utiliser. La seule différence est que les variantes utilisent différentes fonctions de feuille de calcul pour accomplir la même tâche. Par exemple, la variation suivante utilise toujours la fonction ROW, mais repose finalement sur la fonction OFFSET pour calculer la plage souhaitée:

=SUM(OFFSET(A1,((ROW()-1)6),0):OFFSET(A7,((ROW()-1)6),0))

Une approche plus courte qui utilise OFFSET est la suivante:

=SUM(OFFSET($A$1,ROW()*7-7,0,7,1))

Quelle que soit l’approche, vous pouvez probablement dire que l’idée est de proposer une formule qui utilise la ligne dans laquelle la formule apparaît afin de construire la plage que vous voulez vraiment. Chacun des exemples jusqu’à présent suppose que vous commencez dans la cellule B1. Si vous souhaitez commencer dans la cellule B2, vous devrez modifier les formules pour tenir compte de la ligne sur laquelle vous commencez. Pour vous donner juste une idée de comment cela fonctionne, si vous commenciez dans la cellule B2, les trois formules présentées dans cette astuce seraient modifiées de la manière suivante:

=SUM(INDIRECT("A" & (ROW()-2)7+2 & ":A" & (ROW()-2)7+8))

=SUM(OFFSET(A2,((ROW()-2)6),0):OFFSET(A8,((ROW()-2)6),0))

=SUM(OFFSET($A$2,(ROW()-1)*7-7,0,7,1))

Commencez à un emplacement différent et vous devrez apporter d’autres modifications à la formule que vous choisissez d’utiliser.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (8387) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Incrementing_References_by_Multiples_when_Copying_Formulas [Incrémentation des références par des multiples lors de la copie de formules].