Créer une somme intelligente et dynamique sur Excel sans VBA

Dans ce tutoriel, je vais vous apprendre à créer une somme intelligente dans Excel.

En effet, vous êtes sûrement déjà tombé sur le problème de la fonction SOMME() qui ne prend pas en compte les nouvelles lignes insérées. Avec ce tutoriel, je vais vous montrer comment utiliser la fonction DECALER() pour générer une plage de cellules dynamique et intelligente, qui saura s'adapter automatiquement aux nouvelles lignes insérées dans le tableau.

Pour finir, je vais également vous présenter une solution simple pour inclure automatiquement de nouvelles lignes dans le total, sans avoir besoin de modifier votre formule. Suivez ce tutoriel étape par étape pour découvrir toutes les astuces d'Excel sur la création de sommes intelligentes.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Créer une somme intelligente.

Il vous est sûrement déjà arrivé de vouloir utiliser la fonction SOMME() pour récupérer la valeur des cellules situées juste au-dessus.

C’est justement à ça que sert cette fonction.

Le problème lorsque nous utilisons cette méthode, c’est que si nous en venons à insérer une ligne entre le dernier élément utilisé et la ligne dans laquelle se trouve le total, alors celle-ci ne sera pas prise en compte.

Pour illustrer ce tutoriel, nous retrouvons le tableau suivant, qui reprend les montants des ventes annuelles par région et par pays :

Si nous souhaitons ajouter un nouveau pays dans la zone Europe, nous procéderons en principe de la manière suivante :

• Nous commençons par sélectionner la ligne 14 dans laquelle se trouve le total de la zone Europe,

• Puis nous insérons une nouvelle ligne au-dessus en effectuant un clic droit, puis en choisissant « Nouvelle ligne », ou alors en utilisant le raccourci clavier [Ctrl]+[+] :

• Ensuite, nous saisissons les informations relatives à cette ligne : il des ventes réalisées en Belgique, pour un montant de 12 000 €

Sauf que, comme vous pouvez le constater, ces 12 000 € ne sont pas pris en compte dans notre sous-total de la région, étant donné que si nous regardons la place des cellules utilisées dans la fonction SOMME(), nous pouvons constater que celle-ci s’arrête aux ventes réalisées en Allemagne et ne tiennent pas compte de la nouvelle ligne :

Pour que la formule puisse s’adapter automatiquement, lorsque nous insérons de nouvelles lignes, nous allons pouvoir utiliser une plage dynamique en tant qu’argument de la SOMME().

Pour cela, nous allons pouvoir utiliser la fonction DECALER() que nous avions déjà découverte dans un tutoriel précédent et qui permet de générer une référence de cellules à partir d’une cellule de départ, à laquelle nous allons vouloir effectuer une un décalage.

Ici, pour être sûr que ce soit toujours la cellule située juste au-dessus de la cellule dans laquelle nous allons insérer la formule qui soit prise en compte, nous allons justement partir de cette cellule, mais nous ne pouvons pas insérer directement ses coordonnées, et c’est là que la fonction DECALER() va nous être utile.

Revenons dans la formule SOMME() que nous avons placée dans la cellule C15, nous allons supprimer la plage de cellule insérée, et à la plage, nous allons y appeler la fonction DECALER :

Pour point de référence de départ, nous allons sélectionner la cellule située juste au-dessus de la première cellule que nous souhaitons inclure dans le calcul, ici la cellule B8 :

Ensuite, nous allons effectuer un décalage d’une cellule vers le bas, en saisissant simplement « 1 » comme second paramètre « ligne » de la fonction DECALER :

Pour rester sur la même colonne, nous saisissons ensuite « 0 » pour paramètre « colonnes » :

Et pour finir, nous allons compter le nombre de cellules à inclure dans la plage générée par la fonction DECALER en calculant la différence entre le numéro de la ligne dans laquelle est inséré le total, et la ligne située juste au-dessus.

Pour cela, nous allons pouvoir utiliser la fonction Excel LIGNE() que nous avions découverte dans un tutoriel précédent.

Celle-ci permet en effet d’obtenir le numéro de la ligne d’une cellule passée en paramètre. Si nous omettons de spécifier une cellule en paramètre, alors la fonction LIGNE() retournera le numéro de la ligne dans laquelle celle-ci a été insérée :

 =SOMME(DECALER(B8;1;0;LIGNE()-LIGNE(B8)-1)) 

Il ne reste plus qu’à retirer une ligne à ce résultat.

Lorsque nous validons, nous pouvons constater que maintenant la formule prend bien en compte la Belgique dans son résultat.

Nous pouvons maintenant ajouter une nouvelle ligne « Portugal » à 24000€ :

Celui-ci sert bien inclus dans le calcul !

Pour comprendre comment fonctionne ce calcul, nous pouvons nous rendre dans le menu « Formules » du ruban afin de sélectionner l’option d’évaluation de la formule :

Comme nous l’avions vu dans un article précédent, cette fonctionnalité permet en effet de décomposer chaque étape de résolution d’une formule pour bien comprendre comment Excel parvient à nous retourner le résultat correspondant :

Nous voyons ainsi que la fonction DECALER() permet de reconstituer la plage des cellules à inclure dans le calcul de la fonction SOMME().

2. Inclure automatiquement de nouvelles lignes dans le total

Sinon, sachez qu’il existe une solution encore plus simple à mettre en place pour éviter le piège de calcul automatique auquel nous avons été confrontés dans la partie précédente, qui consiste tout simplement à insérer la nouvelle ligne, nous pas à la fin de la liste, mais au milieu de celle-ci :

De cette manière, Excel va effectuer un décalage du bas de la plage de cellules utilisées dans la fonction SOMME.

Ici, la nouvelle ligne « Vietnam » que nous avons insérée dans la région « ASIE » a bien été incluse dans le calcul correspondant.

Ensuite, si nous voulons absolument faire apparaître cette nouvelle ligne, tout à la fin, il suffira de sélectionner les lignes situées en dessous pour les faire remonter, soit en effectuant un couper-coller, soit en effectuant un déplacement de lignes sélectionnées en appuyant sur la touche [Majuscule] du clavier, tout en faisant glisser le curseur de la souris vers le haut du tableau :

Les ventes réalisées au « Vietnam » sont toujours incluses dans le total.