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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - somme intelligente - 01

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,

Excel formation - somme intelligente - 02

  • 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]+[+] :

Excel formation - somme intelligente - 03

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

Excel formation - somme intelligente - 04

 

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 :

Excel formation - somme intelligente - 05

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 :

Excel formation - somme intelligente - 06

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 :

Excel formation - somme intelligente - 07

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 :

Excel formation - somme intelligente - 08

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

Excel formation - somme intelligente - 09

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€ :

Excel formation - somme intelligente - 10

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 :

Excel formation - somme intelligente - 11

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 :

Excel formation - somme intelligente - 12

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 :

Excel formation - somme intelligente - 13

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 :

Excel formation - somme intelligente - 14

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

 



Articles qui pourraient vous intéresser

Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !
Les erreurs de concaténation qui vous font perdre du temps sur Excel
Découvrez la nouvelle fonction Excel que 90% des experts ne connaissent pas !
Boostez vos investissements : maîtrisez le calcul du taux de rentabilité avec Excel en 10 minutes !
Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.