Bill a une formule qui additionne une colonne dans les lignes 2 à 15, à l’aide de la fonction SOMME. S’il ajoute une ligne n’importe où aux lignes 3 à 15, la formule s’ajuste correctement. S’il ajoute une ligne en haut (juste au-dessus de la ligne 2), la formule ne s’ajuste pas du tout pour inclure la ligne insérée. Bill se demande s’il existe un moyen d’ajuster correctement et automatiquement la formule lorsqu’il ajoute une ligne en haut de ce qui est additionné.

Il existe de nombreuses écoles de pensée sur la façon dont cela peut être fait, chacune mettant en œuvre une technique différente pour accomplir la même chose. Une approche consiste simplement à convertir vos données en un tableau formel dans la feuille de calcul. Le moyen le plus simple consiste à sélectionner une cellule dans vos données, puis à appuyer sur Ctrl + T. Excel reconnaît vos données comme un tableau et si vous insérez des lignes en haut de votre tableau, juste sous vos en-têtes, elles sont automatiquement reflétées dans la sommation du tableau.

Une autre approche consiste à ajuster votre formule SOMME. Vous faites probablement une somme des lignes 2 à 15 car la ligne 1 contient un en-tête de colonne. Si tel est le cas (et si l’en-tête de colonne contient du texte), ajustez simplement votre formule SOMME afin qu’elle référence les lignes 1 à 15. Désormais, lorsque quelqu’un ajoute une nouvelle ligne à la ligne 2, elle est automatiquement incluse dans la somme. De plus, le texte de l’en-tête de colonne n’affectera en rien cette somme.

Si vos en-têtes contiennent des nombres qui peuvent en fait gâcher vos sommes, vous pouvez essayer une variante. Insérez une ligne vide à la ligne 2 et ajustez votre formule SOMME afin qu’elle référence les lignes 2 à 16. (Ce sera la ligne 16 au lieu de 15 car vous avez ajouté la nouvelle ligne vide 2.) Ensuite, masquez la ligne 2 pour qu’elle ne soit pas visible. Désormais, lorsque les utilisateurs ajoutent des lignes en haut, ils les ajoutent en fait à la ligne 3 et la formule SOMME fait toujours référence à la ligne masquée 2 – les ajustements se produiront correctement.

Vous pouvez également utiliser une approche dans laquelle vous ajustez la formule SOMME pour utiliser la fonction INDIRECT ou OFFSET. Supposons, pendant un moment, que votre formule SUM actuelle ressemble à ceci:

=SUM(A2:A15)

Modifiez-le pour qu’il ressemble à l’un des éléments suivants:

=SUM(INDIRECT("A2"):A15)

=SUM(OFFSET(A1,1,0):A15)

Ces deux formules « ancrent » la cellule supérieure de la plage. Lorsque vous insérez des cellules en haut de la plage additionnée (avant la ligne 2), Excel n’ajustera pas les références de cellule dans la fonction INDIRECT ou dans la fonction OFFSET. Le résultat est que votre formule SUM fera toujours référence à la cellule de la plage supérieure souhaitée.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (10999) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.