Inclure automatiquement les valeurs de la ligne insérée directement au-dessus de la fonction SOMME

Aujourd’hui, dans cette formation Excel, vous allez voir comment conserver la somme à jour même si vous insérez une ligne en dessus de cette fonction et vous y ajoutez un nouveau nombre ou montant.

En effet, parfois il peut vous arriver d’ajouter une ligne dans votre tableau, et que la prise en compte dans la fonction SOMME de cette ligne ne se fait pas automatiquement. Ce qui vous oblige à réexécuter la fonction somme à chaque fois que vous ajoutez une nouvelle valeur.

Utilisez la somme automatique

Quand vous utilisez la somme automatique, et comme vous l’avez vu dans l’article Fonction SOMME, Excel sélectionne automatiquement la plage de cellules à additionner.

Prenons cet exemple : Essayez de créer ce petit tableau avant de passer aux étapes suivantes !

Exemple de tableau à y insérer une fonction somme automatique

Dans la cellule B8, insérez une somme automatique, et faites la même chose dans la cellule F2

Sélectionnez donc la cellule B8 et cliquez sur SOMME automatique soit dans le groupe Edition sous l’onglet Accueil soit sous l’onglet Formules.
Excel insère la fonction Somme et sélectionne la plage de cellules juste en dessus contenant les montants à additionner B2:B7.
Tapez Entrée pour valider.
Sélectionnez ensuite la cellule F2 et insérez la somme automatique. Excel sélectionne cette fois la plage de gauche B2:E2.
Validez.

Maintenant, insérez une ligne en dessus de la cellule B8 qui contient la fonction SOMME.
La fonction SOMME se trouve maintenant dans la cellule B9 et contient toujours la syntaxe =SOMME(B2:B7)

Insertion d'une ligne dans une plage de cellules référencées dans la fonction SOMME

Saisissez un montant dans la cellule B8 puis tapez Entrée.
Remarquez alors que la somme prend en considération le nouveau montant ajoutée et la fonction SOMME est mise à jour en incluant la référence B8 dans ses arguments :

Dans la cellule B9 vous avez : =SOMME(B2:B8)

Somme mise à jour après ajout d'une nouvelle valeur

Essayez à présent d’insérer une colonne avant la cellule F2 contenant la fonction SOMME, et ajoutez un nouveau montant puis remarquez ce qui va arriver.

Mise à jour de la somme après ajout d'une nouvelle colonne

Note : l’intérêt de cette solution c’est que la somme automatique référence toujours une plage de cellules contiguës, alors qu’elle n’est pas utile si vous référencez des cellules non contiguës dans la fonction Somme.

Notez aussi que les références des cellules utilisées dans la somme automatique ne doivent pas être des références absolues.

Par exemple, si vous avez écrit =SOMME($B$2:$B$7) et que vous insérez une nouvelle ligne, le calcul ne sera pas mis à jour en ajoutant un nouveau montant.

Somme non mise à jour à cause des références absolues

La somme est calculée automatiquement après suppression d’une ligne ou d’une colonne

Reprenez l’exemple précédent, et supprimez une ligne du tableau ou une colonne puis remarquez qu’Excel recalcule la somme automatiquement :

Somme mise à jour après avoir supprimer une ligne ou une colonne

Appliquez un style de tableau

Comme vous l’avez vu dans la leçon précédente "la fonction SOMME", et quand on applique un style de tableau, ça nous permet d’ajouter une ligne de totaux en bas du tableau.

Alors, si vous insérez une ligne en dessus de la ligne de totaux, Excel prendra en compte cette ligne insérée dans le calcul du total.

Insérer une ligne dans un tableau mis sous forme de tableau

Aidez-vous de la fonction SOUS.TOTAL

Vous pouvez aussi recourir à cette solution qui se pose sur l’utilisation de la fonction SOUS.TOTAL.

Si vous revenez au dernier exemple: Appliquer un style de tableau, et que vous sélectionnez la cellule qui calcule le total, vous allez remarquer dans la barre de formule que la fonction utilisée ici est SOUS.TOTAL.

Vous allez donc vous servir de cette fonction qui est SOUS.TOTAL, pour que le calcul du total prenne en compte les valeurs se trouvant dans la ligne qui va être insérée en dessus de cette fonction :

Sélectionnez la cellule qui va afficher la somme. Ici par exemple la cellule B8
Tapez = puis Sou et cliquez sur le nom de la fonction SOUS.TOTAL
Dans la liste qui s’affiche cliquez sur SOMME, elle correspond, comme vous le remarquez, aux numéros 9 et 109. Sélectionnez 9 par exemple.

Tapez point-virgule ensuite.
Sélectionnez, après, la plage de cellules à additionner.

ajout de l'argument réf de la fonction SOUS.TOTAL

Fermez la parenthèse et validez.

Insérez maintenant une ligne en dessus de la cellule contenant la fonction SOUS.TOTAL et saisissez un nouveau nombre puis tapez Entrée.
Excel effectue donc le calcul en prenant en compte le nouveau nombre ajouté.

Gardez à l’esprit la syntaxe de la fonction SOUS.TOTAL

SOUS.TOTAL(no_fonction,réf1,[réf2],...)

no_fonction : est le numéro qui spécifie la fonction à utiliser. Pour la fonction SOMME c’est 9 ou 109.
Réf1 … : référence de la plage de cellules à calculer son sous-total.

Servez-vous de la fonction DECALER

La fonction DECALER est une fonction qui permet de renvoyer la référence d’une cellule (plage de cellules) qui se trouve à une certaine distance d’une autre cellule et ceci selon le nombre de lignes et de colonnes défini dans ses arguments.

Sa syntaxe est la suivante : DECALER(réf, lignes, colonnes, [hauteur], [largeur])

réf, lignes et colonnes sont des arguments obligatoires, tandis que [hauteur], [largeur] sont facultatifs.

Dans l’exemple suivant : on veut renvoyer le contenu de la cellule qui se trouve en dessous de la cellule B2 (qui est la cellule de départ) de trois lignes, et à sa droite de deux colonnes.

Cellule de départ

On écrit donc =DECALER(B2;3;2), et on obtient 6425,04 c’est-à-dire la valeur contenue dans la cellule D5.

Schématisation de la fonction DECALER Excel

Voyons un autre exemple :

On écrit dans une cellule =DECALER(D13;-6;-2)
Dans ce cas, on a utilisé des arguments négatifs des lignes et des colonnes.
Alors on cherche la cellule qui se trouve en dessus de la cellule D13 (cellule de départ) de 6 lignes et à sa gauche de 3 colonnes. C’est la cellule B7 évidemment. Excel affiche F006.

Exemple d'utilisation de la fonction DECALER

La faisabilité de la fonction DECALER est fort impressionnante lorsqu’elle est imbriquée dans d’autres fonctions Excel.

Par exemple et si vous reprenez l’exemple du tableau des montants de ventes par mois en haut, la fonction DECALER sera très utile si vous l’utilisez pour que la somme s'effectue automatiquement lors d’une insertion d’une nouvelle ligne.

Dans la cellule B8 tapez donc =SOMME(B2:DECALER(B8;-1;0))

Imbriquer fonction DECALER dans la fonction SOMME

Remarquez que nous avons modifié la plage de cellules B2 :B7 par B2:DECALER(B8;-1;0)
Dans ce cas DECALER(B8;-1;0) renvoie B7.
Insérez maintenant une nouvelle ligne en dessus de la cellule B8
La fonction précédente est décalée vers B9 et est devenue =SOMME(B2:DECALER(B9;-1;0))

C’est-à-dire que la fonction SOMME s’effectuera sur les valeurs de la plage de cellules B2:B8.
Tapez donc un nouveau montant dans la cellule B8
Excel affiche alors le bon résultat.