Empêcher la formule Excel de changer lors de l'insertion/suppression de lignes

Ce qu'il faut comprendre, c'est que le caractère absolu des références absolues, tel qu'il est spécifié par l'attribut $ n'est pas absolument absolu ;-)

Maintenant que ce virelangue a disparu, laissez-moi vous expliquer.

Le caractère absolu ne s'applique que lors du copier-coller ou du remplissage de la formule. L'insertion de lignes au-dessus, ou de colonnes à gauche, d'une plage référencée de manière absolue "décalera" l'adresse de la plage de sorte que la formule données l'étendue vers laquelle il pointe reste la même.

En outre, l'insertion de lignes ou de colonnes dans le fichier milieu de la plage l'élargira pour englober les nouvelles lignes/colonnes. Ainsi, pour "ajouter" une ligne de données à une plage (tableau), vous devez l'insérer dans le tableau. après la première ligne de données.

La façon la plus simple de permettre l'ajout d'une ligne de données sobre la plage de données actuelle doit toujours avoir une ligne d'en-tête, et inclure la ligne d'en-tête dans la plage réelle. C'est exactement la solution proposée par cybernetic.nomad dans ce commentaire .

Mais il reste encore un problème à résoudre, à savoir l'ajout d'une rangée de données après l'élément d'information de l'utilisateur. fin de la table. Il ne suffit pas de taper les nouvelles données dans la ligne qui suit la dernière ligne de données. Pas plus que l'insertion d'une ligne avant la ligne après la dernière ligne.

La solution la plus simple est d'utiliser une "dernière" ligne spéciale, d'inclure cette ligne dans la plage de données et de toujours ajouter de nouvelles lignes en insérant l'option avant cette rangée spéciale.

En général, je réduis la hauteur des lignes et je remplis les cellules avec une couleur appropriée :

Pour votre exemple, la formule complète la plus "simple" serait donc la suivante :

=SUMIF(JUN!$G$1:$G$501,"Utilities",JUN!$H$1:$H$501)

Une autre façon d'atteindre le même objectif consiste à utiliser une formule dynamique qui s'adapte automatiquement à la quantité de données du tableau. Il existe plusieurs variantes de cette méthode, en fonction des circonstances exactes et de ce que l'on souhaite précisément faire subir au tableau.

Si, comme c'est généralement le cas (dans votre exemple, par exemple), le tableau commence en haut de la feuille de calcul, a un en-tête d'une ligne et les données sont contiguës sans aucun vide, une formule dynamique simple serait la suivante :

=SUMIF(INDEX(JUN!$G:$G,2):INDEX(JUN!$G:$G,COUNTA(JUN!$G:$G)),"Utilities",INDEX(JUN!$H:$H,2):INDEX(JUN!$H:$H,COUNTA(JUN!$G:$G)))

C'est une meilleure solution que d'utiliser INDIRECT() comme

1. Il est non-volatile et donc la feuille de calcul calcule plus rapidement, et
2. Il n'y aura pas de rupture si vous insérez des colonnes à gauche du tableau.

La technique de la formule dynamique peut être encore améliorée en l'utilisant dans un système de gestion de la qualité. Formule nommée .

Bien sûr, le meilleur La solution consiste à convertir le tableau en un véritable tableau et utiliser références structurées .

Donc vous dites que, si vous insérez une nouvelle rangée 2 (entre la rangée 1 et la rangée 2), vous voulez que la formule regarde la nouvelle rangée 2 ?  Voici quelques variantes :

=SUMIF(INDIRECT("JUN!$G$2:$G$500"),"Utilities", INDIRECT("JUN!$D$2:$D$500"))

regardera toujours les rangées 2 à 500, sans tenir compte des lignes qui sont renumérotées par des insertions (ou des suppressions).  Cela signifie que, si vous insérez une ligne, la ligne originale 500 d'origine sera renumérotée en 501 et sortira de la plage.  Si vous souhaitez consulter la rangée 2 actuelle jusqu'à la rangée 500 d'origine, utilisez la commande

=SUMIF(INDIRECT("JUN!$G$2"):JUN!$G$500,"Utilities", INDIRECT("JUN!$D$2"):JUN!$D$500)

Au cas où ce ne serait pas évident, INDIRECT() prend un argument de type chaîne (texte) et l'interprète comme une adresse.  Il vous permet de faire de l'adressage invariant, parce que les chaînes (qui ressemblent à des adresses) ne seront pas ajustées lorsque d'autres adresses sont ajustées à cause de l'insertion/suppression de lignes/colonnes.

Notez que le $ Les caractères dans les chaînes d'adresse sont facultatifs ; ils n'ont aucun effet.

L'offset est fastidieux à mettre en place, mais il en vaut la peine si vous recueillez des années de données, une semaine ou un mois à la fois.

pour calculer l'adresse de la dernière ligne de données de la colonne D :
\=OFFSET(D$1,1,0)-D Rangée actuelle-dernière +1
D Rangée actuelle-dernière sera incrémenté lors de l'insertion, le décalage de D$1 ne sera pas

vous pouvez utiliser cette formule pour les moyennes, les fourchettes, etc :
R2 : =OFFSET(D$1,1,0)-D3180+1
Faites la moyenne de toutes les valeurs de la colonne N, sauf les zéros.
\=MOYENIF(OFFSET(N$1,1,0):OFFSET(N$1,R2,0),"<>0")