Vous rencontrerez probablement le besoin de totaux en cours d’exécution si vous traitez avec n’importe quelle sorte de données quotidiennes.

Imaginez que vous suiviez 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. C’est un total courant, c’est la somme de toutes les ventes jusqu’aux ventes des jours en cours incluses.

Dans cet article, nous aborderons plusieurs façons de calculer un total courant pour vos données quotidiennes. Nous explorerons comment utiliser les formules de feuille de calcul, les tableaux croisés dynamiques, le pivot de puissance avec DAX et la requête de puissance.

Nous allons également explorer ce qu’il advient du calcul total en cours 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.

Totaux d’exécution avec une Formule simple

Il est possible de créer une formule totale d’exécution de base en utilisant l’opérateur +.

Cependant, nous devrons utiliser deux formules différentes pour faire le travail.

1. =C3 sera la première formule et ne sera que dans la première ligne du total courant.
2. =C4 + D3 sera dans la deuxième ligne et peut être copié sur les lignes restantes pour le total en cours.

La formule de 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 provoquerait une VALEUR #! erreur à apparaître dans le total en cours d’exécution car 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 ci-dessus.

Qu’arrive-t-il au total en cours d’exécution 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 en cours d’exécution. Pour résoudre ce problème, nous devrons copier la formule de la première cellule au-dessus des lignes nouvellement insérées jusqu’à la dernière ligne.

La suppression de toutes les lignes entraînera #REF! erreurs depuis la suppression d’une ligne signifie la suppression d’une cellule référencée par la formule ci-dessous. Pour résoudre ce problème, nous devrons copier la formule de la dernière cellule sans erreur jusqu’à la dernière ligne.

Exécution de totaux avec une formule de SOMME

Nous pouvons éviter la maladresse d’utiliser deux formules différentes dans notre colonne de total en cours d’exécution en utilisant la fonction SUM au lieu de l’opérateur +. Lorsque la fonction SUM rencontre une cellule de texte, elle la traitera de la même manière qu’une bien qu’elle contienne 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 référencera l’en-tête de colonne contenant du texte pour la première ligne, mais cela est correct car il est traité comme un 0.

Lors de l’insertion ou de la suppression de lignes, nous rencontrerons toujours les mêmes problèmes avec les cellules vides et les erreurs. Nous pouvons les corriger de la même manière qu’avec les totaux en cours d’exécution dans la méthode de la formule simple.

Exécuter des totaux avec une plage partiellement fixe

Une autre option avec la fonction SOMME consiste à référencer uniquement la colonne Ventes et à utiliser une référence de plage partiellement fixe.

Si nous utilisons la formule suivante =SUM(CC$3: C3), nous pouvons copier et coller ceci dans la plage. Il ne référencera aucun en-tête de colonne et la plage référencée augmentera à chaque ligne.

Malheureusement, cela aussi aura les mêmes problèmes (et solutions) avec l’insertion ou la suppression de lignes.

En exécutant des 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. Cela fera référence à la cellule directement au-dessus, quel que soit le nombre de lignes que nous insérons ou supprimons.

Il s’agit d’une astuce qui consiste à changer temporairement le style de référence Excel de A1 à R1C1. Ensuite, définir une plage nommée en utilisant la notation R1C1. Basculez ensuite le style de référence sur A1.

Dans le style de référence R1C1, les cellules sont désignées par leur distance par rapport à 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 en 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 référente 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 Excel et cochez la case Style de référence R1C1, puis appuyez sur le bouton OK.

Maintenant, nous pouvons ajouter notre plage nommée. Accédez à l’onglet Formule du ruban Excel et choisissez la commande Définir le nom.

Insérez un nom comme « Ci-dessus » comme nom de la plage. Ajoutez la formule = RC dans l’entrée Se 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 de la section Formule > décochez la case de style de référence R1C1 > puis appuyez sur le bouton OK.

Maintenant, nous pouvons utiliser la formule =SUM(, Ci-dessus) dans notre colonne total en cours d’exécution.

La plage nommée Ci-dessus 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, la formule se remplira automatiquement pour toutes les nouvelles lignes car la formule est uniforme pour toute la colonne. Aucune action n’est nécessaire pour copier des formules.

Les totaux exécutés avec un tableau croisé dynamique

Les tableaux croisés dynamiques sont très utiles pour résumer tout type de données. Ils ne se contentent pas d’ajouter, de compter et de trouver des moyennes. Il existe de nombreux autres types de calculs intégrés, et il existe en fait un calcul total en cours d’exécution!

Tout 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 accédez à l’onglet Insérer et choisissez la commande Tableau croisé dynamique. Passez ensuite par la fenêtre Créer un tableau croisé dynamique pour choisir où vous souhaitez 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, puis ajoutez le champ Ventes dans la zone Valeurs du tableau croisé dynamique. Ajoutez maintenant une autre instance du champ Ventes dans la zone Lignes.

Nous devrions maintenant avoir deux champs de vente identiques, l’un d’eux étant étiqueté Sum of Sales2. Nous pouvons renommer cette étiquette à tout moment en tapant simplement dessus avec quelque chose comme Courir Total.

Faites un clic droit sur l’une des valeurs du champ Somme des ventes 2 et sélectionnez Afficher la valeur sous, puis choisissez Exécuter le total dans.

Nous voulons afficher le total en cours par date, donc dans la fenêtre suivante, nous devons sélectionner Date comme champ de base.

Ça y est, nous avons maintenant un nouveau calcul qui affiche le total de nos ventes dans le tableau croisé dynamique.

Que se passe-t-il si nous ajoutons ou supprimons une ligne dans nos données source, comment cela affecte-t-il le total en cours? Les calculs du tableau croisé dynamique sont dynamiques et prendront en compte toutes les nouvelles données dans son calcul total en cours, il nous suffira de rafraîchir le tableau croisé dynamique.

Faites un clic droit n’importe où dans le tableau croisé dynamique et choisissez Actualiser dans le menu.

Totaux d’exécution avec des mesures Power Pivot et DAX

Les deux premières étapes pour cela sont exactement les mêmes en utilisant un tableau croisé dynamique régulier.

Sélectionnez une cellule dans les données et allez dans l’onglet Insérer et choisissez la commande Tableau croisé dynamique.

Lorsque vous accédez 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 l’activer pour une utilisation avec Power pivot.

Placez le champ Date dans la zone Lignes et le champ Ventes dans la zone Valeurs 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 de la table dans la fenêtre Champs du tableau croisé dynamique, puis sélectionnez Ajouter une mesure pour créer un nouveau calcul. Notez que ceci n’est disponible qu’avec le modèle de données.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

Nous pouvons maintenant créer notre nouvelle mesure totale en cours d’exécution.

1. Dans la fenêtre de mesure, nous devons ajouter un nom de mesure. Dans ce cas, nous pouvons nommer la nouvelle mesure comme Total en cours d’exécution.
2. Nous devons également ajouter la formule ci-dessus dans la boîte de formule.
3. Ce qui est cool à propos de power pivot est la possibilité d’attribuer un format de nombre à une mesure. Nous pouvons choisir le format de 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 régulier dans les données.

Nous pouvons utiliser ce nouveau champ comme n’importe quel autre champ et le faire glisser dans la zone Valeurs pour ajouter notre calcul total en cours dans le tableau croisé dynamique.

Qu’arrive-t-il au total en cours d’exécution lorsque nous ajoutons ou supprimons des données de la table source ? Tout comme un tableau croisé dynamique ordinaire, il suffit de cliquer avec le bouton droit de la souris sur le tableau croisé dynamique et de sélectionner Actualiser pour mettre à jour le calcul.

Totaux en cours d’exécution avec une requête de puissance

Nous pouvons également ajouter des totaux en cours d’exécution à nos données à l’aide de la requête de puissance.

Nous devons d’abord importer la table dans power query. Sélectionnez la table de données et accédez à l’onglet Données et choisissez l’option De Table / Plage. Cela ouvrira l’éditeur de requêtes d’alimentation.

Ensuite, nous pouvons trier nos données par date. Il s’agit d’une étape facultative que nous pouvons ajouter afin que si nous modifions l’ordre de nos données source, le total en cours d’exécution apparaisse toujours par date.

Cliquez sur la bascule de filtre dans l’en-tête de colonne date et choisissez Trier par ordre croissant parmi les options.

Nous devons ajouter une colonne d’index. Cela sera utilisé dans le calcul du total en cours plus tard. Allez dans l’onglet Ajouter une colonne et cliquez sur la petite flèche à côté de la colonne d’index pour insérer un index commençant par 1 dans la première ligne.

Nous devons ajouter une nouvelle colonne à notre requête pour calculer le total en cours. Accédez à l’onglet Ajouter une colonne et choisissez la commande Colonne personnalisée.

Nous pouvons nommer la colonne comme Total en cours d’exécution et ajouter la formule suivante.

La liste.La fonction Range crée une liste de valeurs à partir de la colonne Sales à partir de la 1ère ligne (0ème élément) qui s’étend sur un certain nombre de lignes en fonction de la valeur de la colonne index.

La Liste.La fonction Somme additionne ensuite cette liste de valeurs qui est notre total courant.

Nous n’avons plus besoin de la colonne d’index, elle a rempli son objectif et nous pouvons la supprimer. Faites un clic droit sur l’en-tête de colonne et sélectionnez Supprimer dans les options.

Nous avons notre total de fonctionnement et avons fini avec l’éditeur de requêtes. Nous pouvons fermer la requête et charger les résultats dans une nouvelle feuille de calcul. Accédez à l’onglet Accueil de l’éditeur de requêtes et appuyez sur le bouton de chargement Fermer &.

Que se passe-t-il avec le total en cours lorsque nous ajoutons ou supprimons des lignes de nos données source? Nous devrons actualiser la table de sortie power query pour mettre à jour le total en cours avec les modifications. Faites un clic droit n’importe où sur la table et choisissez Actualiser pour mettre à jour la table.

Avec l’étape de tri facultative ci-dessus, si nous ajoutons des dates en panne aux données source, power query triera par date et renverra le bon ordre par date pour le total en cours.

Conclusions

Il existe de nombreuses options différentes pour calculer les totaux en cours d’exécution dans Excel.

Nous avons exploré des options, notamment des formules dans la feuille de calcul, des tableaux croisés dynamiques, des formules DAX power pivot et power query. Certaines offrent une solution plus robuste lors de l’ajout ou de la suppression de lignes des données, d’autres méthodes offrent une implémentation plus facile.

Les formules simples de la feuille de calcul sont faciles à configurer, mais ne gèrent pas facilement l’insertion ou la suppression de nouvelles lignes de données. D’autres solutions telles que les tableaux croisés dynamiques, DAX et power query sont plus robustes et gèrent facilement l’insertion ou la suppression de lignes de données, mais sont plus difficiles à configurer.

Il est bon de connaître les avantages et les inconvénients de chaque méthode et de choisir celle qui convient le mieux. Si vous n’insérez pas ou ne supprimez pas de nouvelles données, des formules de feuille de calcul peuvent être la solution.