Vous allez probablement rencontrer le besoin de totaux courants si vous traitez une sorte de données quotidiennes.
Imaginez que vous suivez les ventes chaque jour. Vos données contiennent une ligne pour chaque date avec un montant total des ventes, mais peut-être voulez-vous connaître le total des ventes pour le mois à chaque jour. Il s’agit d’un total courant, c’est la somme de toutes les ventes jusqu’à et y compris les ventes du jour en cours.
Dans ce post, nous allons couvrir plusieurs façons de calculer un total courant pour vos données quotidiennes. Nous explorerons comment utiliser les formules de feuilles de calcul, les tableaux croisés dynamiques, le power pivot avec DAX et le power query.
Nous explorerons également ce qui arrive au calcul du total courant lors de l’insertion ou de la suppression de lignes de données et comment mettre à jour les résultats.
Obtenez le fichier avec tous les exemples.
Table des matières
Total courant avec une formule simple
Il est possible de créer une formule de total courant de base en utilisant l’opérateur +.
Cependant, nous devrons utiliser deux formules différentes pour faire le travail.
- =C3 sera la première formule et ne sera que dans la première ligne du total courant.
- =C4+D3 sera dans la deuxième ligne et peut être copié vers le bas des lignes restantes pour le total courant.
La formule dans notre première ligne ne peut pas ajouter la cellule au-dessus au total car elle contient une valeur de texte pour un titre de colonne. Cela entraînerait l’apparition d’une erreur #VALUE ! dans le total courant puisque le + ne peut pas gérer les valeurs de texte. Nous évitons cela avec une formule différente dans la première ligne qui ne fait pas référence à la cellule au-dessus.
Que se passe-t-il avec le total courant lorsque nous insérons ou supprimons des lignes dans nos données ?
L’insertion d’une nouvelle ligne entraînera un écart dans le total courant. Pour corriger cela, nous devrons copier la formule vers le bas à partir de la première cellule au-dessus des rangées nouvellement insérées, jusqu’à la dernière rangée.
La suppression de toute ligne entraînera des erreurs #REF ! puisque la suppression d’une ligne signifie la suppression d’une cellule référencée par la formule située en dessous. Pour résoudre ce problème, nous devrons copier la formule vers le bas depuis la dernière cellule sans erreur jusqu’à la dernière ligne.
Total courant avec une formule SUM
Nous pouvons éviter la maladresse d’utiliser deux formules différentes dans notre colonne de total courant en utilisant la fonction SUM au lieu de l’opérateur +. Lorsque la fonction SUM rencontre une cellule de texte, elle la traite de la même manière que si elle contenait un 0.
De cette façon, nous pouvons utiliser la formule suivante uniformément pour chaque ligne, y compris la première ligne.
=SUM(C3,D2)
Cette formule fera référence à l’en-tête de colonne contenant du texte pour la première ligne, mais c’est ok car il est traité comme un 0.
Lorsque nous insérons ou supprimons des lignes, nous rencontrerons toujours les mêmes problèmes de cellules vides et d’erreurs. Nous pouvons les résoudre de la même manière que pour l’exécution des totaux dans la méthode de la formule simple.
Exécution des totaux avec une plage partiellement fixe
Une autre option avec la fonction SUM est de ne référencer que la colonne Sales et d’utiliser une référence de plage partiellement fixe.
Si nous utilisons la formule suivante =SUM($C$3:C3), nous pouvons copier et coller ceci dans la plage. Elle ne fera référence à aucun en-tête de colonne et la plage référencée s’agrandira à chaque ligne.
Malheureusement, cela aussi aura les mêmes problèmes (et solutions) avec l’insertion ou la suppression de lignes.
Exécution de totaux avec une plage nommée relative
Nous pouvons éviter les problèmes d’insertion et de suppression de lignes de nos données si nous utilisons une plage nommée relative. Celle-ci fera référence à la cellule directement au-dessus, quel que soit le nombre de lignes que nous insérons ou supprimons.
C’est une astuce qui consiste à changer temporairement le style de référence Excel de A1 à R1C1. Puis de définir une plage nommée en utilisant la notation R1C1. Puis de basculer à nouveau le style de référence sur A1.
Dans le style de référence R1C1, les cellules sont désignées par la distance qui les sépare de la cellule utilisant la référence. Par exemple, =RC fait référence à la cellule 2 en haut et 3 à droite de la cellule utilisant cette formule.
Nous pouvons utiliser ce référencement relatif pour créer une plage nommée qui est toujours une cellule au-dessus de la cellule de référence avec la formule =RC.
Pour changer de style de référence, allez dans l’onglet Fichier puis choisissez Options. Allez dans la section Formule du menu Options d’Excel et cochez la case style de référence R1C1 puis appuyez sur le bouton OK.
Nous pouvons maintenant ajouter notre plage nommée. Allez dans l’onglet Formule du ruban Excel et choisissez la commande Définir le nom.
Insérez un nom comme « Au-dessus » comme nom de la plage. Ajoutez la formule =RC dans l’entrée Réfère à et appuyez sur le bouton OK.
Nous pouvons maintenant basculer Excel vers le style de référence par défaut. Allez dans l’onglet Fichier > Options la section Formule > décochez la case style de référence R1C1 > puis appuyez sur le bouton OK.
Maintenant, nous pouvons utiliser la formule =SUM(,Above) dans notre colonne de total courant.
La plage nommée Above fera toujours référence à la cellule directement au-dessus. Lorsque nous insérons ou supprimons des lignes, la plage nommée relative s’ajustera en conséquence et aucune action n’est nécessaire.
En fait, si nous plaçons nos données dans un tableau Excel, alors la formule se remplira automatiquement vers le bas pour toute nouvelle ligne puisque la formule est uniforme pour toute la colonne. Aucune action n’est nécessaire pour copier vers le bas les formules.
Exécuter des totaux avec un tableau croisé dynamique
Les tableaux croisés dynamiques sont super utiles pour résumer tout type de données. Ils ne se limitent pas à additionner, compter et trouver des moyennes. Il existe de nombreux autres types de calculs intégrés, et il y a en fait un calcul du total courant !
D’abord, nous devons insérer un tableau croisé dynamique basé sur les données. Sélectionnez une cellule à l’intérieur des données et allez dans l’onglet Insertion et choisissez la commande Tableau croisé dynamique. Passez ensuite par la fenêtre Créer un tableau croisé dynamique pour choisir où vous voulez 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 Rows du tableau croisé dynamique, puis ajoutez le champ Sales dans la zone Values du tableau croisé dynamique. Ajoutez maintenant une autre instance du champ Ventes dans la zone Rows.
Nous devrions maintenant avoir deux champs Ventes identiques dont l’un est étiqueté Somme des ventes2. Nous pouvons renommer cette étiquette à tout moment en tapant simplement par-dessus quelque chose comme Running Total.
Cliquez avec le bouton droit de la souris sur l’une des valeurs du champ Sum of Sales2 et sélectionnez Show Value As puis choisissez Running Total In.
Nous voulons afficher le total courant par date, donc dans la fenêtre suivante, nous devons sélectionner Date comme champ de base.
C’est tout, nous avons maintenant un nouveau calcul qui affiche le total courant de nos ventes à l’intérieur du tableau croisé dynamique.
Que se passe-t-il si nous ajoutons ou supprimons une ligne dans nos données sources, comment cela affecte-t-il le total courant ? Les calculs du tableau croisé dynamique sont dynamiques et prendront en compte toute nouvelle donnée dans son calcul du total courant, il nous suffira de rafraîchir le tableau croisé dynamique.
Cliquez avec le bouton droit n’importe où à l’intérieur du tableau croisé dynamique et choisissez Rafraîchir dans le menu.
Total courant avec Power Pivot et mesures DAX
Les deux premières étapes pour cela sont exactement les mêmes en utilisant un tableau croisé dynamique ordinaire.
Sélectionnez une cellule à l’intérieur des données et allez dans l’onglet Insertion et choisissez la commande Tableau croisé dynamique.
Lorsque vous arrivez au menu Créer un tableau croisé dynamique, cochez la case Ajouter ces données au modèle de données pour ajouter les données au modèle de données et les activer pour une utilisation avec power pivot.
Placez le champ Date dans la zone Rows et le champ Sales dans la zone Values du tableau croisé dynamique.
Avec power pivot, nous devrons créer tous les calculs supplémentaires que nous voulons en utilisant le langage DAX. Faites un clic droit sur le nom du tableau dans la fenêtre Champs du tableau croisé dynamique, puis sélectionnez Ajouter une mesure pour créer un nouveau calcul. Remarque, ceci n’est disponible qu’avec le modèle de données.
=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales Maintenant, nous pouvons créer notre nouvelle mesure de total courant.
- Dans la fenêtre de mesure, nous devons ajouter un nom de mesure. Dans ce cas, nous pouvons nommer la nouvelle mesure comme Running Total.
- Nous devons également ajouter la formule ci-dessus dans la case Formula.
- La chose cool avec power pivot est la possibilité d’attribuer un format de nombre à une mesure. Nous pouvons choisir le format Devise pour notre mesure. Chaque fois que nous utilisons cette mesure dans un tableau croisé dynamique, le format sera automatiquement appliqué.
Appuyez sur le bouton OK et la nouvelle mesure sera créée.
Il y aura un nouveau champ répertorié dans la fenêtre Champs du tableau croisé dynamique. Il a une petite icône fx à gauche pour indiquer qu’il s’agit d’une mesure et non d’un champ ordinaire dans les données.
Nous pouvons utiliser ce nouveau champ comme n’importe quel autre champ et le faire glisser dans la zone des valeurs pour ajouter notre calcul du total courant dans le tableau croisé dynamique.
Que se passe-t-il avec le total courant lorsque nous ajoutons ou supprimons des données dans le tableau source ? Tout comme un tableau croisé dynamique ordinaire, nous devons simplement faire un clic droit sur le tableau croisé dynamique et sélectionner Rafraîchir pour mettre à jour le calcul.
Total courant avec une Power Query
Nous pouvons également ajouter des totaux courants à nos données en utilisant une Power Query.
D’abord, nous devons importer le tableau dans la Power Query. Sélectionnez le tableau de données et allez dans l’onglet Données et choisissez l’option Depuis le tableau/la plage. Cela ouvrira l’éditeur de power query.
Suite, nous pouvons trier nos données par date. Il s’agit d’une étape facultative que nous pouvons ajouter afin que si nous changeons l’ordre de nos données sources, le total courant apparaisse toujours par date.
Cliquez sur le basculeur de filtre dans l’en-tête de la colonne de date et choisissez Sort Ascending dans les options.
Nous devons ajouter une colonne d’index. Celle-ci sera utilisée dans le calcul du total courant plus tard. Allez dans l’onglet Ajouter une colonne et cliquez sur la petite flèche à côté de la colonne Index pour insérer un index commençant à 1 dans la première ligne.
Nous devons ajouter une nouvelle colonne à notre requête pour calculer le total courant. Allez dans l’onglet Ajouter une colonne et choisissez la commande Colonne personnalisée.
Nous pouvons nommer la colonne comme Total courant et ajouter la formule suivante.
La fonction List.Range crée une liste de valeurs à partir de la colonne Sales en commençant par la 1ère ligne (0e élément) qui s’étend sur un certain nombre de lignes en fonction de la valeur de la colonne index.
La fonction List.Sum additionne ensuite cette liste de valeurs qui constitue notre total courant.
Nous n’avons plus besoin de la colonne index, elle a rempli son rôle et nous pouvons la supprimer. Faites un clic droit sur l’en-tête de la colonne et sélectionnez Supprimer dans les options.
Nous avons notre total courant et nous en avons terminé avec l’éditeur de requête. Nous pouvons fermer la requête et charger les résultats dans une nouvelle feuille de calcul. Allez dans l’onglet Accueil de l’éditeur de requêtes et appuyez sur le bouton Fermer & Charger.
Que se passe-t-il avec le total courant lorsque nous ajoutons ou supprimons des lignes de nos données source ? Nous devrons rafraîchir le tableau de sortie de power query pour mettre à jour le total courant avec les changements. Faites un clic droit n’importe où sur le tableau et choisissez Rafraîchir pour mettre à jour le tableau.
Avec l’étape de tri optionnel ci-dessus, si nous ajoutons des dates dans le désordre aux données source, power query triera par date et renverra le bon ordre par date pour le total courant.
Conclusions
Il existe de nombreuses options différentes pour calculer les totaux courants dans Excel.
Nous avons exploré les options incluant les formules dans la feuille de calcul, les tableaux croisés dynamiques, les formules DAX de power pivot et power query. Certaines offrent une solution plus robuste lors de l’ajout ou de la suppression de lignes de données, d’autres méthodes offrent une mise en œuvre plus facile.
Les formules simples dans la feuille de calcul sont faciles à mettre en place mais ne gèreront pas facilement l’insertion ou la suppression de nouvelles lignes de données. D’autres solutions comme les tableaux croisés dynamiques, DAX et power query sont plus robustes et gèrent l’insertion ou la suppression de lignes de données facilement mais sont plus difficiles à mettre en place.
Il est bon d’être conscient des avantages et des inconvénients de chaque méthode et de choisir la plus adaptée. Si vous n’avez pas l’intention d’insérer ou de supprimer de nouvelles données, les formules de feuilles de calcul pourraient être la solution.
Les formules de feuilles de calcul sont des outils de gestion de l’information.