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.
Inclure automatiquement les valeurs de la ligne insérée directement au-dessus de la fonction SOMME


Parce que ; 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 :

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 et qui contient 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.
Somme automatique

  • 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 automatiques 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 du 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.

Exemple de la fonction SOUS.TOTAL


Vous allez donc suivre la même procédure. Voici comment utiliser la fonction SOUS.TOTAL :

  • 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, c’est le numéro 9 ou 109 (Vous allez voir leurs rôles dans le prochain article).
Insertion de la fonction SOUS.TOTAL

  • 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.
Résultat de la fonction SOUS.TOTAL

  • 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 dans la fonction DECALER
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, elle sera très utile si vous l’utilisez pour conserver la somme 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))
Cellule décalée avec son contenu

  • 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.
Mise à jour automatique de la fonction somme avec Decaler imbriquée

  • Répétez l’opération tant que vous voulez.


PARTAGEZ

Excel Info

  • Image
  • Image
  • Image
  • Image
  • Image