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.
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
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.
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.
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 :
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 ?
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.
Pour résoudre ce problème, vous devez copier la formule de la dernière cellule sans erreur jusqu'à la dernière ligne.
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.
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! » :
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 :
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 :
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 :
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.
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 :
Etape 4- Créer la formule de somme cumulée :
Une fois le nom défini :
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.
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.
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.
Vous devriez maintenant avoir deux champs « Somme de Ventes » identiques, l'un d'entre eux étant intitulé « Somme de Ventes 2 ».
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 :
