Total cumulé (également appelé somme cumulée) est assez couramment utilisé dans de nombreuses situations. C'est une métrique qui vous indique quelle est la somme des valeurs jusqu'à présent.
Par exemple, si vous disposez des données de ventes mensuelles, un total cumulé vous indiquera combien de ventes ont été réalisées jusqu'à un jour spécifique à partir du premier jour du mois.
Il existe également d'autres situations où le total cumulé est souvent utilisé, comme le calcul de votre solde de trésorerie dans vos relevés bancaires/grand livre, le comptage des calories dans votre plan de repas, etc.
Dans Microsoft Excel, il existe plusieurs façons différentes de calculer les totaux cumulés.
La méthode que vous choisirez dépendra également de la façon dont vos données sont structurées.
Par exemple, si vous avez des données tabulaires simples, vous pouvez utiliser une formule SUM simple, mais si vous avez un tableau Excel, il est préférable d'utiliser des références structurées. Vous pouvez également utiliser Power Query pour ce faire.
Dans ce tutoriel, je vais couvrir toutes ces différentes méthodes pour calculer les totaux cumulés dans Excel.
Alors, commençons!
Calcul du total cumulé avec des données tabulaires
Si vous avez des données tabulaires (c'est-à-dire un tableau dans Excel qui n'est pas converti en un tableau Excel), vous pouvez utiliser des formules simples pour calculer les totaux cumulés.
Utilisation de l'opérateur d'addition
Supposons que vous disposiez de données de ventes par date et que vous souhaitiez calculer le total cumulé dans la colonne C.
Vous trouverez ci-dessous les étapes pour le faire.
Étape 1 - Dans la cellule C2, qui est la première cellule où vous voulez le total cumulé, entrez
=B2
Cela obtiendra simplement les mêmes valeurs de vente dans la cellule B2.
Étape 2 - Dans la cellule C3, entrez la formule ci-dessous :
=C2+B3
Étape 3 - Appliquer la formule à toute la colonne. Vous pouvez utiliser la poignée de remplissage pour le sélectionner et le faire glisser, ou simplement et copier-coller la cellule C3 dans toutes les cellules restantes (ce qui ajusterait automatiquement la référence et donnerait le bon résultat).
Cela vous donnera le résultat comme indiqué ci-dessous.
C'est une méthode très simple et qui fonctionne bien dans la plupart des cas.
La logique est simple - chaque cellule récupère la valeur au-dessus d'elle (qui est la somme cumulée jusqu'à la date précédente) et ajoute la valeur dans la cellule adjacente (qui est la valeur de vente pour ce jour).
Il n'y a qu'un seul inconvénient : si vous supprimez l'une des lignes existantes de cet ensemble de données, toutes les cellules ci-dessous renverraient une erreur de référence (#REF !)
Si c'est une possibilité avec votre ensemble de données, utilisez la méthode suivante qui utilise la formule SUM
Utilisation de SUM avec une référence de cellule partiellement verrouillée
Supposons que vous disposiez de données de ventes par date et que vous souhaitiez calculer le total cumulé dans la colonne C.
Vous trouverez ci-dessous la formule SUM qui vous donnera le total cumulé.
=SOMME($B$2:B2)
Laissez-moi vous expliquer comment fonctionne cette formule.
Dans la formule SUM ci-dessus, j'ai utilisé la référence pour ajouter comme $B$2:B2
- $B$2 - il s'agit d'une référence absolue, ce qui signifie que lorsque je copie la même formule dans les cellules ci-dessous, cette référence ne changera pas. Ainsi, lors de la copie de la formule dans la cellule ci-dessous, la formule deviendrait SUM($B$2:B3)
- B2 - il s'agit de la deuxième partie de la référence qui est une référence relative, ce qui signifie que cela s'ajustera lorsque je copierai la formule vers le bas ou vers la droite. Ainsi, lors de la copie de la formule dans la cellule ci-dessous, cette valeur deviendra B3
Lisez également : Références de cellules absolues, relatives et mixtes dans Excel
L'avantage de cette méthode est que si vous supprimez l'une des lignes de l'ensemble de données, cette formule s'ajustera et vous donnera toujours les bons totaux cumulés.
Calcul du total cumulé dans un tableau Excel
Lorsque vous travaillez avec des données tabulaires dans Excel, il est judicieux de les convertir en un tableau Excel. Cela facilite beaucoup la gestion des données et permet également de faciliter l'utilisation d'outils tels que Power Query et Power Pivot.
L'utilisation des tableaux Excel présente des avantages tels que des références structurées (ce qui permet de se référer très facilement aux données du tableau et de les utiliser dans des formules) et l'ajustement automatique des références au cas où vous ajouteriez ou supprimeriez des données du tableau.
Bien que vous puissiez toujours utiliser la formule ci-dessus que je vous ai montrée dans un tableau Excel, laissez-moi vous montrer quelques meilleures méthodes pour le faire.
Supposons que vous ayez un tableau Excel comme indiqué ci-dessous et que vous souhaitiez calculer le total cumulé dans la colonne C.
Vous trouverez ci-dessous la formule qui le fera :
=SUM(SalesData[[#Headers],[Sale]]:[@Sale])
La formule ci-dessus peut sembler un peu longue, mais vous n'avez pas à l'écrire vous-même. ce que vous voyez dans la formule de somme s'appelle des références structurées, ce qui est le moyen efficace d'Excel de faire référence à des points de données spécifiques dans un tableau Excel.
Par exemple, SalesData[[#Headers],[Sale]] fait référence à l'en-tête Sale dans le tableau SalesData (SalesData est le nom du tableau Excel que j'ai donné lorsque j'ai créé le tableau)
Et [@Sale] fait référence à la valeur de la cellule de la même ligne dans la colonne Vente.
Je viens de l'expliquer ici pour votre compréhension, mais même si vous ne connaissez rien aux références structurées, vous pouvez toujours créer facilement cette formule.
Vous trouverez ci-dessous les étapes à suivre :
- Dans la cellule C2, entrez =SUM(
- Sélectionnez la cellule B1, qui est l'en-tête de la colonne contenant la valeur de vente. Vous pouvez utiliser la souris ou utiliser les touches fléchées. Vous remarquerez qu'Excel entre automatiquement la référence structurée pour cette cellule
- Ajouter un : (symbole deux-points)
- Sélectionnez la cellule B2. Excel insère à nouveau automatiquement la référence structurée pour la cellule
- Fermez le crochet et appuyez sur Entrée
Vous remarquerez également que vous n'avez pas à copier la formule dans toute la colonne, un tableau Excel le fait automatiquement pour vous.
Un autre avantage de cette méthode est que si vous ajoutez un nouvel enregistrement dans cet ensemble de données, le tableau Excel calculera automatiquement le total cumulé de tous les nouveaux enregistrements.
Bien que nous ayons inclus l'en-tête de la colonne dans notre formule, n'oubliez pas que la formule ignorerait le texte de l'en-tête et ne considérerait que les données de la colonne.
Calcul du total cumulé à l'aide de Power Query
Power Query est un outil incroyable lorsqu'il s'agit de se connecter à des bases de données, d'extraire les données de plusieurs sources et de les transformer avant de les mettre dans Excel.
Si vous travaillez déjà avec Power Query, il serait plus efficace d'ajouter des totaux cumulés pendant que vous transformez les données dans l'éditeur Power Query lui-même (au lieu d'obtenir d'abord les données dans Excel, puis d'ajouter les totaux cumulés en utilisant l'un des éléments ci-dessus méthodes décrites ci-dessus).
Bien qu'il n'y ait pas de fonctionnalité intégrée dans Power Query pour ajouter des totaux cumulés (ce que j'aurais aimé qu'il y ait), vous pouvez toujours le faire en utilisant une formule simple.
Supposons que vous ayez un tableau Excel comme indiqué ci-dessous et que vous souhaitiez ajouter les totaux cumulés à ces données :
Vous trouverez ci-dessous les étapes à suivre :
- Sélectionnez n'importe quelle cellule du tableau Excel
- Cliquez sur Données
- Dans l'onglet Obtenir et transformer, cliquez sur l'icône de la table/plage. Cela ouvrira la table dans l'éditeur Power Query
- [Facultatif] Si votre colonne Date n'est pas déjà triée, cliquez sur l'icône de filtre dans la colonne Date, puis cliquez sur Trier par ordre croissant
- Cliquez sur l'onglet Ajouter une colonne dans l'éditeur Power Query
- Dans le groupe Général, cliquez sur la liste déroulante Colonne d'index (ne cliquez pas sur l'icône Colonne d'index, mais sur la petite flèche noire inclinée juste à côté pour afficher plus d'options)
- Cliquez sur l'option « À partir de 1 ». Cela ajoutera une nouvelle colonne d'index qui commencerait à partir d'un et entrerait des nombres incrémentés de 1 dans toute la colonne
- Cliquez sur l'icône « Colonne personnalisée » (qui se trouve également dans l'onglet Ajouter une colonne)
- Dans la boîte de dialogue de colonne personnalisée qui s'ouvre, saisissez un nom pour la nouvelle colonne. dans cet exemple, j'utiliserai le nom « Running Total »
- Dans le champ Formule de colonne personnalisée, saisissez la formule ci-dessous : List.Sum(List.Range(#"Index ajouté"[Vente],0,[Index]))
- Assurez-vous qu'il y a une case à cocher au bas de la boîte de dialogue qui dit - "Aucune erreur de syntaxe n'a été détectée"
- Cliquez sur OK. Cela ajouterait une nouvelle colonne de total cumulé
- Supprimer la colonne d'index
- Cliquez sur l'onglet Fichier, puis sur « Fermer et charger »
Les étapes ci-dessus inséreraient une nouvelle feuille dans votre classeur avec un tableau contenant les totaux cumulés.
Maintenant, si vous pensez que ce sont tout simplement trop d'étapes par rapport aux méthodes précédentes d'utilisation de formules simples, vous avez raison.
Si vous disposez déjà d'un ensemble de données et que tout ce que vous avez à faire est d'ajouter des totaux cumulés, il est préférable de ne pas utiliser Power Query.
L'utilisation de Power Query est logique lorsque vous devez extraire des données d'une base de données ou combiner des données de plusieurs classeurs différents et, dans le processus, y ajouter également des totaux cumulés.
De plus, une fois que vous avez effectué cette automatisation à l'aide de Power Query, la prochaine fois que votre ensemble de données change, vous n'aurez plus à le faire, vous pouvez simplement actualiser la requête et cela vous donnera le résultat basé sur le nouvel ensemble de données.
Comment cela marche-t-il?
Maintenant, permettez-moi d'expliquer rapidement ce qui se passe dans cette méthode.
La première chose que nous faisons dans l'éditeur Power Query est d'insérer une colonne d'index en commençant par un et en incrémentant de un au fur et à mesure qu'elle descend dans les cellules.
Nous le faisons parce que nous devons utiliser cette colonne pendant que nous calculons le total cumulé dans une autre colonne que nous insérons à l'étape suivante.
Ensuite, nous insérons une colonne personnalisée et utilisons la formule ci-dessous
List.Sum(List.Range(#"Index ajouté"[Vente],0,[Index]))
Il s'agit d'une formule List.Sum qui vous donnerait la somme de la plage qui y est spécifiée.
Et cette plage est spécifiée à l'aide de la fonction List.Range.
La fonction List.Range donne la plage spécifiée dans la colonne de vente en tant que sortie et cette plage change en fonction de la valeur d'index. Par exemple, pour le premier enregistrement, la plage serait simplement la première valeur de vente. Et au fur et à mesure que vous descendez dans les cellules, cette plage s'étendrait.
Donc, pour la première cellule. List.Sum ne vous donnerait que la somme de la première valeur de vente, et pour la deuxième cellule, elle vous donnerait la somme des deux premières valeurs de vente et ainsi de suite.
Bien que cette méthode fonctionne bien, elle devient très lente avec des ensembles de données volumineux - des milliers de lignes. Si vous avez affaire à un ensemble de données volumineux et que vous souhaitez y ajouter des totaux cumulés, consultez ce didacticiel qui présente d'autres méthodes plus rapides.Calcul du total cumulé en fonction de critères
Jusqu'à présent, nous avons vu des exemples où nous avons calculé le total cumulé pour toutes les valeurs d'une colonne.
Mais il peut arriver que vous souhaitiez calculer le total cumulé pour des enregistrements spécifiques.
Par exemple, ci-dessous, j'ai un ensemble de données et je souhaite calculer le total cumulé pour les imprimantes et les scanners séparément dans deux colonnes différentes.
Cela peut être fait à l'aide d'une formule SUMIF qui calcule le total cumulé tout en s'assurant que la condition spécifiée est remplie.
Vous trouverez ci-dessous la formule qui le fera pour les colonnes Imprimante :
=SUMIF($C$2:C2,$D$1,$B$2:B2)
De même, pour calculer le total cumulé des Scanners, utilisez la formule ci-dessous :
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Dans les formules ci-dessus, j'ai utilisé SUMIF, ce qui me donnerait la somme dans une plage lorsque les critères spécifiés sont remplis.
La formule prend trois arguments :
- gamme: il s'agit de la plage de critères qui serait vérifiée par rapport aux critères spécifiés
- Critères: ce sont les critères qui seraient vérifiés uniquement si ce critère est rempli, alors les valeurs du troisième argument, qui est la plage de somme, seraient ajoutées
- [somme_plage]: c'est la plage de somme à partir de laquelle les valeurs seraient ajoutées si les critères sont remplis
Aussi, dans le gamme et sum_range argument, j'ai verrouillé la deuxième partie de la référence, de sorte qu'au fur et à mesure que nous descendons dans les cellules, la plage continue de s'étendre. Cela nous permet de ne considérer et d'ajouter des valeurs que jusqu'à cette plage (d'où les totaux cumulés).
Dans cette formule, j'ai utilisé la colonne d'en-tête (Imprimante et Scanner) comme critère. vous pouvez également coder en dur les critères si vos en-têtes de colonnes ne sont pas exactement les mêmes que le texte des critères.
Si vous avez plusieurs conditions à vérifier, vous pouvez utiliser la formule SUMIFS.
Total cumulé dans les tableaux croisés dynamiques
Si vous souhaitez ajouter des totaux cumulés dans un résultat de tableau croisé dynamique, vous pouvez facilement le faire à l'aide d'une fonctionnalité intégrée dans les tableaux croisés dynamiques.
Supposons que vous ayez un tableau croisé dynamique comme indiqué ci-dessous où j'ai la date dans une colonne et la valeur de vente dans l'autre colonne.
Vous trouverez ci-dessous les étapes pour ajouter une colonne supplémentaire qui affichera le total cumulé des ventes par date :
- Faites glisser le champ Vente et placez-le dans la zone Valeur.
- Cela ajoutera une autre colonne avec les valeurs de vente
- Cliquez sur l'option Somme des ventes2 dans la zone Valeur
- Cliquez sur l'option « Paramètres du champ de valeur »
- Dans la boîte de dialogue Paramètres du champ de valeur, remplacez le nom personnalisé par « Totaux cumulés »
- Cliquez sur l'onglet "Afficher la valeur en tant que"
- Dans la liste déroulante Afficher la valeur en tant que, sélectionnez l'option « Total cumulé dans »
- Dans les options du champ Base, assurez-vous que Date est sélectionné
- Cliquez sur OK
Les étapes ci-dessus changeraient la deuxième colonne de vente en colonne Total cumulé.
Voici donc quelques-unes des méthodes que vous pouvez utiliser pour calculer le total cumulé dans Excel. Si vous avez des données sous forme de tableau, vous pouvez utiliser des formules simples, et si vous avez un tableau Excel, vous pouvez utiliser des formules qui utilisent des références structurées.
J'ai également expliqué comment calculer le total cumulé à l'aide de Power Query et dans les tableaux croisés dynamiques.
J'espère que vous avez trouvé ce tutoriel utile.
