total cumulé (également appelésomme cumulée) est très couramment utilisé dans de nombreux cas.Il s'agit d'une métrique qui vous indique quelle est la somme des valeurs jusqu'à présent.
Par exemple, si vous disposez de données de ventes mensuelles, un total cumulé vous indiquera le nombre de ventes réalisées entre le premier jour du mois et une date spécifique.
Il existe d'autres situations où les totaux sont souvent utilisés, comme le calcul des soldes de trésorerie dans les relevés bancaires/grands livres, le calcul des calories dans les plans de repas, etc.
Dans Microsoft Excel, il existe plusieurs façons de calculer les totaux cumulés.
La méthode que vous choisissez dépend également de la structure des données.
Par exemple, si vous avez des données tabulaires simples, vous pouvez utiliser une simple formule SOMME, 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 tous les couvrirCalculer un total cumulé dans Exceldifférentes méthodes.
Alors, commençons!
Table des matières
Calculer les totaux cumulés à l'aide de données tabulaires
Si vous avez des données tabulaires (c'est-à-dire des tableaux dans Excel qui ne sont pas convertis en tableaux Excel), vous pouvez utiliser des formules simples pour calculer les totaux cumulés.
Utiliser l'opérateur d'addition
Supposons que vous ayez des données de ventes par date et que vous vouliezDans dans la colonne CCalculTotal cumulé.
Voici les étapes pour le faire.
Étape 1 – Dans la cellule C2, la première cellule pour laquelle vous voulez faire le total, entrez
= B2
Cela obtient juste la même valeur de vente dans la cellule B2.
Étape 2– Dans la cellule C3, saisissez la formule suivante :
= C2 + B3
Étape 3– Appliquer la formule à toute la colonne.Vous pouvez utiliser la poignée de remplissage pour la sélectionner et la faire glisser, ou simplement copier-coller la cellule C3 dans toutes les cellules restantes (cela ajustera automatiquement la référence et donnera le résultat correct).
Cela vous donnera le résultat indiqué ci-dessous.
C'est une méthode très simple qui fonctionne bien dans la plupart des cas.
La logique est simple - chaque cellule prend la valeur dessus (qui est la somme cumulée jusqu'au jour précédent) et ajoute la valeur dans la cellule adjacente (qui est la valeur des ventes du jour en cours).
Il n'y a qu'un seul inconvénient : si vous supprimez une ligne existante dans cet ensemble de données, toutes les cellules ci-dessous renverront une erreur de référence (#REF !)
Si votre ensemble de données a cette possibilité, utilisez la méthode suivante en utilisant la formule SOMME
Utiliser SUM avec des références de cellules partiellement verrouillées
Supposons que vous disposiez de données de ventes par date et que vous souhaitiez calculer un total cumulé dans la colonne C.
Vous trouverez ci-dessous la formule SOMME qui vous donnera un total cumulé.
= SOMME ($ B $ 2: B2)
Laissez-moi vous expliquer comment cette formule fonctionne.
Dans la formule SUM ci-dessus, j'ai utilisé la référence à ajouter comme $B$2:B2
- $B$2 - Il s'agit d'une référence absolue, ce qui signifie que cette référence ne change pas lorsque je copie la même formule dans la cellule ci-dessous.Ainsi, lorsque vous copiez la formule dans la cellule ci-dessous, la formule devient SUM($B$2:B3)
- B2 - C'est la deuxième partie de la référence, c'est une référence relative, ce qui signifie que lorsque je copie la formule vers le bas ou vers la droite, cela s'ajuste.Ainsi, lors de la copie de la formule dans la cellule ci-dessous, la valeur devient B3
La bonne chose à propos de cette approche est que si vous supprimez des lignes dans l'ensemble de données, cette formule s'ajuste et vous donne toujours le total cumulé correct.
Calculer le total cumulé dans une feuille Excel
Lorsque vous travaillez avec des données tabulaires dans Excel, il est préférable de les convertir en tableau Excel.Cela facilite la gestion des données et facilite également l'utilisation d'outils tels que Power Query et Power Pivot.
L'utilisation d'un tableau Excel offre plusieurs avantages, tels que des références structurées (qui facilitent le référencement des données dans le tableau et leur utilisation dans des formules) et l'ajustement automatique des références lorsque vous ajoutez ou supprimez des données du tableau.
Bien que vous puissiez toujours utiliser la formule ci-dessus que je vous ai montrée dans une feuille Excel, laissez-moi vous montrer quelques meilleures façons de le faire.
Supposons que vous disposiez d'un tableau Excel comme celui ci-dessous et que vous souhaitiez calculer un total cumulé dans la colonne C.
Voici la formule pour ce faire :
=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 une formule de sommation est appelé une référence structurée, et c'est un moyen efficace pour 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 Sales dans le tableau SalesData (SalesData est le nom du tableau Excel que j'ai donné lors de la création du tableau)
Alors que [@Sale] fait référence à la valeur d'une cellule de la même ligne de la colonne Vente.
J'explique simplement cela ici pour votre compréhension, mais même si vous ne savez rien des références structurées, vous pouvez toujours créer facilement cette formule.
Voici les étapes à suivre :
- Dans la cellule C2, entrez =SOMME(
- Sélectionnez la cellule B1, qui est l'en-tête de la colonne avec la valeur de vente.Vous pouvez utiliser la souris ou utiliser les touches fléchées.Vous remarquerez qu'Excel entre automatiquement une référence structurée à la cellule
- Ajouter : (deux-points)
- Sélectionnez la cellule B2.Excel insérera automatiquement à nouveau des références structurées dans les cellules
- Fermez le crochet et appuyez sur Entrée
Vous remarquerez également que vous n'avez pas besoin de copier la formule dans toute la colonne, la feuille Excel le fait automatiquement pour vous.
Un autre avantage de cette approche est que si vous ajoutez un nouvel enregistrement à ce jeu de données, la feuille Excel calcule automatiquement un total cumulé pour tous les nouveaux enregistrements.
Bien que nous ayons inclus les en-têtes de colonne dans la formule, gardez à l'esprit que la formule ignore le texte de l'en-tête et ne prend en compte que les données de la colonne
Calculer les totaux cumulés avec Power Query
Power Query est un outil incroyable lorsqu'il s'agit de se connecter à des bases de données, d'extraire des données de plusieurs sources et de les transformer avant de les mettre dans Excel.
Si vous utilisez déjà Power Query, il est plus efficace d'ajouter des totaux cumulés lors de la transformation des données dans l'éditeur Power Query lui-même (plutôt que d'obtenir d'abord les données dans Excel, puis d'ajouter des totaux cumulés à l'aide de l'une des méthodes ci-dessus).
Bien qu'il n'y ait pas de fonctionnalité intégrée dans Power Query pour ajouter des totaux cumulés (j'aurais aimé qu'il y en ait), vous pouvez toujours le faire avec une formule simple.
Supposons que vous ayez un tableau Excel qui ressemble à ceci et que vous souhaitiez ajouter des totaux cumulés à ces données :
Voici les étapes à suivre :
- Sélectionnez n'importe quelle cellule dans un tableau Excel
- cliquez sur les données
- Dans l'onglet Get & Transform, cliquez sur l'icône from Table/Plage.Cela ouvrira la table dans Power Query Editor
- [Facultatif] Si votre colonne de date n'est pas déjà triée, cliquez sur l'icône de filtre dans la colonne de 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 le menu déroulant Colonne d'index (ne cliquez pas sur l'icône Colonne d'index, mais sur la petite flèche noire inclinée à côté pour révéler plus d'options)
- Cliquez sur l'option "À partir de 1".Cela ajoutera une nouvelle colonne d'index qui commencera à 1 et entrera des nombres incrémentés de 1 dans toute la colonne
- Cliquez sur l'icône Colonnes personnalisées (également dans l'onglet Ajouter des colonnes)
- Dans la boîte de dialogue Colonnes personnalisées qui s'ouvre, entrez 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 suivante :List.Sum(List.Range(#"Index ajouté"[Sale],0,[Index]))
- Assurez-vous qu'il y a une case à cocher en bas de la boîte de dialogue indiquant "Aucune erreur de syntaxe détectée"
- Cliquez sur OK.Cela ajoutera une nouvelle colonne de total cumulé
- supprimer la colonne d'index
- Cliquez sur l'onglet Fichier, puis cliquez sur Fermer et charger
Les étapes ci-dessus inséreront une nouvelle feuille de calcul dans votre classeur avec un tableau avec des totaux cumulés.
Maintenant, si vous pensez que ce sont trop d'étapes par rapport à l'approche précédente avec des formules simples, vous avez raison.
Si vous disposez déjà d'un jeu de données et que vous n'avez qu'à ajouter des totaux cumulés, il est préférable de ne pas utiliser Power Query.
L'utilisation de Power Query est logique dans les situations où vous devez extraire des données d'une base de données ou combiner des données de plusieurs classeurs différents et également leur ajouter des totaux cumulés dans le processus.
De plus, une fois que vous automatisez cela avec Power Query, la prochaine fois que votre jeu de données change, vous n'avez pas à le refaire, vous pouvez simplement actualiser la requête et cela vous donnera des résultats basés sur le nouveau jeu de données.
Comment cela marche-t-il?
Maintenant, permettez-moi d'expliquer rapidement ce qui se passe avec cette approche.
La première chose que nous faisons dans l'éditeur Power Query est d'insérer une colonne d'index qui commence à 1 et s'incrémente au fur et à mesure qu'elle descend dans la cellule.
Nous procédons ainsi car nous devons utiliser cette colonne lors du calcul du total cumulé dans une autre colonne insérée à l'étape suivante.
Ensuite, nous insérons une colonne personnalisée et utilisons la formule suivante
List.Sum(List.Range(#"Index ajouté"[Sale],0,[Index]))
Il s'agit d'une formule List.Sum qui vous donnera la somme de la plage spécifiée.
La plage est spécifiée à l'aide de la fonction List.Range.
La fonction List.Range prend en sortie la plage spécifiée dans la colonne de vente, et la plage varie en fonction de la valeur Index.Par exemple, pour le premier enregistrement, la plage correspond à la première valeur de vente.Cette plage s'agrandit au fur et à mesure que vous descendez dans la cellule.
Donc, pour la première cellule.List.Sum ne vous donnera que la somme de la première valeur de vente, pour la deuxième cellule, il vous donnera la somme des deux premières valeurs de vente, et ainsi de suite.
Bien que cette approche fonctionne bien, elle peut devenir très lente pour les grands ensembles de données (des milliers de lignes).
Calculer les totaux cumulés en fonction de critères
Jusqu'à présent, nous avons vu des exemples de calcul d'un total cumulé de toutes les valeurs d'une colonne.
Mais dans certains cas, vous souhaiterez peut-être calculer un total cumulé pour un enregistrement spécifique.
Par exemple, ci-dessous, j'ai un ensemble de données où je veux compter le total cumulé des imprimantes et des scanners dans deux colonnes différentes.
Cela peut être fait en utilisant la formule SUMIF, qui calcule un total cumulé tout en s'assurant que les conditions spécifiées sont remplies.
Voici la formule pour le faire pour la colonne de l'imprimante :
=SUMIF($C$2:C2,$D$1,$B$2:B2)
De même, pour calculer le total cumulé du scanner, utilisez la formule suivante :
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Dans la formule ci-dessus, j'ai utilisé SUMIF qui me donnera une somme dans une plage lorsque les conditions spécifiées sont remplies.
La formule prend trois paramètres :
- gamme: Il s'agit de la plage de conditions qui seront vérifiées par rapport à la condition spécifiée
- critères: c'est le critère qui ne sera vérifié que si ce critère est rempli, puis la valeur dans le troisième paramètre, la plage de somme sera additionnée
- [sum_range]: c'est la plage de la somme des valeurs à additionner lorsque la condition est remplie
De plus, dansgammeetplage_sommeparamètre, j'ai verrouillé la deuxième partie de la référence afin que, à mesure que nous descendons dans la cellule, la plage continue de s'étendre.Cela nous permet de ne considérer et d'ajouter que des valeurs jusqu'à cette plage (d'où un total cumulé).
Dans cette formule, j'utilise les colonnes d'en-tête (Imprimantes et Scanners) comme critères.Vous pouvez également coder en dur la norme si vos en-têtes de colonne ne sont pas exactement les mêmes que le texte standard.
Si vous devez vérifier plusieurs conditions, vous pouvez utiliser la formule SUMIFS.
Totaux cumulés dans un tableau croisé dynamique
Si vous souhaitez ajouter un total cumulé aux résultats de votre tableau croisé dynamique, vous pouvez facilement le faire à l'aide de la fonctionnalité intégrée dans les tableaux croisés dynamiques.
Supposons que vous ayez un tableau croisé dynamique comme celui ci-dessous avec des dates dans une colonne et des valeurs de ventes dans une autre.
Voici les étapes pour ajouter une colonne supplémentaire qui affichera les ventes cumulées par date :
- Faites glisser le champ Vente et déposez-le dans la zone Valeur.
- Cela ajoutera une autre colonne avec la valeur des ventes
- Cliquez sur l'option Total Sales 2 dans la zone Value
- Cliquez sur l'option Paramètres du champ de valeur
- Dans la boîte de dialogue Paramètres du champ de valeur, remplacez Nom personnalisé par Total cumulé
- Cliquez sur l'onglet "Afficher la valeur en tant que"
- Dans le menu déroulant Afficher la valeur en tant que, sélectionnez l'option "Total cumulé en"
- Dans les options Champs de base, assurez-vous que Date est sélectionné
- Cliquez sur OK
Les étapes ci-dessus changeront la deuxième colonne des ventes en une colonne "total cumulé".
Voici donc quelques-unes des méthodes que vous pouvez utiliser pour calculer les totaux cumulés dans Excel.Si vous avez des données tabulaires, vous pouvez utiliser des formules simples, et si vous avez des tableaux Excel, vous pouvez utiliser des formules qui utilisent des références structurées.
J'ai également expliqué comment calculer les totaux cumulés à l'aide de Power Query et des tableaux croisés dynamiques.
J'espère que vous avez trouvé ce tutoriel utile.
