Tutoriel Excel : comment faire une somme cumulée
Dans ce tutoriel, nous allons aborder différentes méthodes pour calculer un total cumulé de vos données quotidiennes. Vous allez découvrir comment utiliser les formules de feuilles de calcul, les plages nommées relatives et les tableaux croisés dynamiques.
Le calcul d'un total cumulé (ou d'une somme cumulée, comme connu dans Excel) est facile une fois que vous avez compris le principe de base. Cette méthode est utilisée pour montrer la somme des données au fur et à mesure qu'elles grandissent avec le temps (mises à jour chaque fois qu'un nouveau numéro est ajouté à la séquence).
Cette technique est très courante dans la vie de tous les jours, par exemple pour calculer le score actuel dans les jeux, afficher les ventes depuis le début de l'année ou depuis le début du mois ou encore calculer votre solde bancaire après chaque retrait et dépôt.
Table des matières
Qu'est-ce qu'une somme cumulée ?
Comment calculer le total cumulé (somme cumulée) dans Excel ?
Méthode 1 : somme cumulée avec une formule simple
Méthode 2 : somme cumulée avec une formule SOMME
Méthode 3 : somme cumulée avec une plage nommée relative
Méthode 4 : somme cumulée avec un tableau croisé dynamique
Qu'est-ce qu'une somme cumulée ?
Une somme cumulée, ou un total cumulé, est une séquence de sommes partielles d'un ensemble de données. Elle est utilisée en tant que moyen d'afficher la somme des données au fur et à mesure de leur croissance au fil du temps. Cette technique très courante est utilisée quotidiennement par les étudiants et les professionnels chargés d'utiliser Excel pour calculer et analyser un tableau de données et d'équations complexes. De plus, avoir un total cumulé peut vous éviter de perdre du temps à enregistrer la séquence par vous-même, s'il n'est pas essentiel de connaître les numéros individuels utilisés.
Comment calculer le total cumulé (somme cumulée) dans Excel ?
Imaginez que vous suivez les ventes chaque jour. Vos données contiennent une ligne pour chaque date avec un montant total des ventes, mais vous souhaitez peut-être connaître le total des ventes du mois à chaque jour. Il s'agit d'une somme cumulée ! C'est la somme de toutes les ventes, y compris les ventes du jour en cours.
Méthode 1 : somme cumulée avec une formule simple
Il est possible de créer une formule de total cumulé de base à l'aide de l'opérateur « + ». Cependant, vous devez utiliser deux formules différentes pour faire ce travail :
- =B2 sera la première formule et n'apparaîtra que dans la première ligne de la somme cumulée :
- =B3 + C2 sera dans la deuxième ligne et peut être copiée dans les lignes restantes de la somme cumulée :
La formule de la première ligne ne peut pas ajouter la cellule au-dessus au total, car elle contient une valeur de texte pour un en-tête de colonne. Cela provoquerait une erreur « #VALEUR! » qui pourrait apparaître dans la somme cumulée, car le « + » ne peut pas gérer les valeurs de texte. On évite ce problème avec une formule différente dans la première ligne qui ne fait pas référence à la cellule en dessus.
Qu'advient-il du total cumulé lorsque vous insérez ou supprimez des lignes dans vos données ?
- L'insertion d'une nouvelle ligne entraînera un écart dans la somme cumulée comme le montre la capture ci-dessous :
Pour résoudre ce problème, vous devez copier la formule de la première cellule au-dessus des lignes nouvellement insérées jusqu'à la dernière ligne.
- La suppression de lignes entraînera des erreurs « #REF! » depuis la ligne supprimée. Cette erreur signifie la suppression d'une cellule référencée par la formule en dessous.
Pour résoudre ce problème, vous devez copier la formule de la dernière cellule sans erreur jusqu'à la dernière ligne.
- Une autre limitation : la feuille de calcul est actuellement triée par date. Si vous triez le tableau de données par une colonne autre que « Date », telle que « Ventes », la formule sera rompue. En effet, toutes les valeurs de la cellule C3 vers le bas dépendent de la valeur de C2. Si la valeur de C2 est déplacée ailleurs dans le tableau, les totaux cumulés ne seront plus calculés correctement, comme vous le verrez dans l'exemple ci-dessous. Le tableau a été trié par nombre de « Ventes », du plus petit au plus grand, et la formule qui était en C2 n'est plus la première dans la liste. Le résultat dans cet exemple ressemblera à ceci:
Comme vous pouvez le voir, il y a une erreur « #VALEUR! » dans C2, C3 et C4.
Remarquez comment la formule de C2 essaie maintenant d'ajouter la valeur de C1 à la valeur de B2. L'erreur « #VALEUR! » dans C2 indique que la formule ne peut pas calculer un résultat en raison d'un problème avec les valeurs dans les cellules auxquelles elle fait référence. Dans ce cas, le problème est que C1 contient des mots, pas des nombres.
Méthode 2 : somme cumulée avec une formule SOMME
Pour calculer un total cumulé dans Excel, vous pouvez utiliser la fonction SOMME combinée à une utilisation intelligente des références de cellules absolues et relatives.
Par exemple, pour calculer la somme cumulée des chiffres de ventes de la colonne B commençant dans la cellule B2, entrez la formule suivante dans C2, puis copiez-la dans d'autres cellules :
=SOMME($B$2:B2)
Dans votre formule de total cumulé, la première référence doit toujours être une référence absolue avec le signe « $ » ($B$2). Parce qu'une référence absolue ne change jamais, peu importe où la formule se déplace, elle se réfère toujours à B2. La deuxième référence sans le signe « $ » (B2) est relative et elle s'ajuste en fonction de la position relative de la cellule où la formule est copiée.
Ainsi, lorsque notre formule « Somme » est copiée dans B3, elle devient SOMME($B$2:B3) et renvoie le total des valeurs des cellules B2 à B3.
Dans la cellule B4, la formule se transforme en SOMME($B$2:B4) et totalise les nombres dans les cellules B2 à B4, etc.
De la même manière, vous pouvez utiliser la fonction Excel « SOMME » pour trouver la somme cumulée de votre solde bancaire. Pour cela, entrez les dépôts sous forme de nombres positifs et les retraits sous forme de nombres négatifs dans une certaine colonne (colonne B dans cet exemple). Et puis, pour afficher le total cumulé, entrez la formule suivante dans la colonne D :
=SOMME($B$2:B2)
À première vue, notre formule de somme cumulée semble parfaite, mais elle présente un inconvénient important. Lorsque vous copiez la formule dans une colonne, vous remarquerez que les totaux cumulatifs dans les lignes sous la dernière cellule avec une valeur dans la colonne C affichent tous le même nombre:
Pour résoudre ce problème, on peut améliorer un peu plus la formule de somme cumulée en l'intégrant dans la fonction conditionnelle SI :
=SI(B2=""; ""; SOMME($B$2:B2))
La formule demande à Excel de procéder comme suit : si la cellule « B2 » est vide, retournez une chaîne vide (cellule vide), sinon appliquez la formule de somme cumulée.
Maintenant, vous pouvez copier la formule dans autant de cellules que vous le souhaitez, et les cellules de la formule seront vides jusqu'à ce que vous entriez un nombre dans la ligne correspondante dans la colonne B. Dès que vous le faites, la somme cumulée calculée apparaît à côté de chaque montant:
Maintenant, vérifions ce qui se passe si nous trions le premier tableau. Les deux captures suivantes montrent le tableau trié en premier par la valeur de vente la plus petite à la plus élevée, puis par la valeur de vente la plus élevée à la plus petite, comme dans la méthode 1. Cette fois-ci, cependant, le total cumulé fonctionne toujours, sans l’erreur « # VALEUR! » :
Méthode 3 : somme cumulée avec une plage nommée relative
Vous pouvez éviter les problèmes d'insertion et de suppression de lignes de vos données si vous utilisez une plage nommée relative. Cela fera référence à la cellule directement au-dessus, quel que soit le nombre de lignes que vous insérez ou supprimez.
Afin d'effectuer une somme cumulée avec une plage nommée relative, vous devez apporter des modifications temporaires aux options Excel. Il s'agit d'une astuce qui implique de basculer temporairement le style de référence Excel de A1 à L1C1, définir ensuite une plage nommée à l'aide de la notation L1C1 et puis basculer le style de référence vers A1.
Étape 1- Modifier le style de référence :
- Allez dans l'onglet « Fichier» puis choisissez « Options ».
- Accédez à la section «Formules » du menu « Options Excel ».
- Sous le groupe « Manipulation de formules», cochez la case « Style de référence L1C1 »,
- Appuyez sur le bouton « OK».
Le style de référence L1C1 fait référence à la ligne 1 et la colonne 1. Dans ce style, vous pouvez trouver un signe positif et négatif qui est utilisé pour une raison.
Dans les lignes :
- Le signe + (positif) fait référence à la direction vers le bas.
- Le signe - (négatif) fait référence à une direction ascendante.
Exemple : L(3) se réfère à la cellule qui se trouve 3 lignes en dessous de la cellule actuelle et L(-5) se réfère à la cellule qui est à 5 lignes au-dessus de la cellule actuelle.
Dans les colonnes :
- Le signe + (positif) fait référence à la direction droite.
- Le signe - (négatif) fait référence à la direction gauche.
Exemple : C(2) se réfère à la cellule qui est de 2 colonnes à droite de la cellule actuelle et C(-4) se réfère à la cellule qui est de 4 colonnes à gauche de la cellule actuelle.
Étape 2- Créer une plage nommée :
Afin d'utiliser le style de référence pour calculer le total cumulé, vous devez définir un nom avec certains critères.
Dans cet exemple, vous devez définir le nom par « L(-1)C » parce que vous calculez le cumulatif qui est la somme de la ligne précédente de la cellule et de la colonne avec chaque vente individuelle.
- Accédez à l'onglet « Formules» du ruban Excel.
- Cliquez sur la commande « Définir un nom» dans le groupe « Noms définis ».
- Insérez un nom comme « Dessus» en tant que nom de la plage.
- Ajoutez la formule « =L(-1)C »dans la zone « Fait référence à l'entrée ».
- Pour finir, appuyez sur le bouton « OK».
Etape 3- Ramener Excel au style de référence par défaut :
Vous pouvez maintenant ramener Excel au style de référence par défaut. Pour ce faire :
- Accédez à l'onglet « Fichier».
- Cliquez sur « Options».
- Accédez à la section « Formules».
- Décochez la case du « style de référence L1C1».
- Appuyez sur le bouton OK.
Etape 4- Créer la formule de somme cumulée :
Une fois le nom défini :
- Accédez à la colonne« Somme cumulée » et utilisez la formule =SOMME([@Ventes];Dessus).
La plage nommée « Dessus » fera toujours référence à la cellule directement au-dessus de la cellule actuelle. Lorsque vous insérez ou supprimez des lignes, la plage nommée relative s'ajuste en conséquence et aucune autre action ne serait nécessaire.
- Ensuite, faites glisser la formule jusqu'à la fin du tableau. Vous pouvez voir que les résultats cumulatifs ressembleront aux résultats ci-dessous:
En fait, si vous placez vos données dans un tableau Excel (Comme dans mon cas), la formule se remplira automatiquement pour toutes les nouvelles lignes, car la formule est uniforme pour toute la colonne. Aucune action n'est donc nécessaire pour la copier dans les autres cellules.
Méthode 4 : somme cumulée avec un tableau croisé dynamique
Les tableaux croisés dynamiques sont très utiles pour résumer tout type de données. Ils permettent non seulement d'ajouter, de compter et de trouver des moyennes, mais servent à de nombreux autres types de calculs intégrés. Ils permettent en effet de calculer des sommes ou des totaux cumulés!
Afin de calculer une somme cumulée à l'aide d'un tableau croisé dynamique dans Excel, vous devez d'abord créer un tableau croisé dynamique.
- Créez un tableau croisé dynamique en sélectionnant le tableau « Tableau croisé dynamique» dans l'onglet « Insertion » :
- Parcourez ensuite la fenêtre « Créer un tableau croisé dynamique» pour choisir où vous voulez placer le tableau croisé dynamique : soit dans une nouvelle feuille de calcul, soit quelque part dans une feuille existante :
- Ajoutez le champ « Date» dans la zone « Lignes » du tableau croisé dynamique.
- Ajoutez le champ « Ventes » dans la zone « Valeurs» du tableau croisé dynamique.
- Ajoutez maintenant une autre instance du champ « Ventes» dans la zone « Valeurs ».
Vous devriez maintenant avoir deux champs « Somme de Ventes » identiques, l'un d'entre eux étant intitulé « Somme de Ventes 2 ».
- Vous pouvez renommer cette étiquette à tout moment en cliquant simplement dessus et en tapant quelque chose comme « Total cumulé» :
- Cliquez avec le bouton droit sur l'une des valeurs du champ « Total cumulé» et sélectionnez « Afficher les valeurs » puis choisissez « Résultat cumulé par… » :
- Si vous souhaitez afficher le total cumulé par date, vous devez sélectionner « Date» comme champ de base :
Et voilà ! Vous avez maintenant un nouveau calcul qui affiche le total cumulé de vos ventes dans le tableau croisé dynamique :
Mais que se passe-t-il si vous ajoutez ou supprimez une ligne dans vos données sources, comment cela affecte-t-il le total cumulé ?
En effet, les calculs du tableau croisé dynamique sont dynamiques et prendront en compte toutes les nouvelles données dans le calcul de total cumulé. Il vous suffira d'actualiser le tableau croisé dynamique.
Cliquez avec le bouton droit n'importe où dans le tableau croisé dynamique et choisissez « Actualiser » dans le menu :
Conclusions :
- ·Le calcul du total cumulé /somme Cumulée aide à analyser les informations des données à des fins de prise de décision.
- ·Le type de plage relativement nommée du total cumulé est effectué afin d'éviter les problèmes d'insertion et de suppression de lignes des données, car ce type d'opération se réfère à la cellule selon la condition indiquée, bien que vous insériez ou supprimiez des lignes ou des colonnes.
- ·Le cumul dans Excel est principalement utilisé dans la modélisation financière et la valeur finale du cumul sera la même que la somme totale.
- ·« La somme » et « la somme cumulée » sont différentes et la principale différence est le calcul que nous faisons. La somme effectuera la somme de chaque nombre dans la série de données tandis que « la somme cumulée » additionnera la valeur précédente avec la valeur actuelle des données.