Total cumulé dans Excel

Méthode 1. Formules

Commençons, pour l'échauffement, par l'option la plus simple – les formules. Si nous avons un petit tableau trié par date en entrée, alors pour calculer le total cumulé dans une colonne séparée, nous avons besoin d'une formule élémentaire :

La principale caractéristique ici est la fixation délicate de la plage à l'intérieur de la fonction SOMME - la référence au début de la plage est rendue absolue (avec des signes dollar) et à la fin - relative (sans dollars). En conséquence, lors de la copie de la formule dans toute la colonne, nous obtenons une plage en expansion dont nous calculons la somme.

Les inconvénients de cette approche sont évidents :

• Le tableau doit être trié par date.
  
• Lors de l'ajout de nouvelles lignes avec des données, la formule devra être étendue manuellement.

Méthode 2. Tableau croisé dynamique

Cette méthode est un peu plus compliquée, mais beaucoup plus agréable. Et pour aggraver, considérons un problème plus sérieux - un tableau de 2000 lignes de données, où il n'y a pas de tri par colonne de date, mais il y a des répétitions (c'est-à-dire qu'on peut vendre plusieurs fois le même jour) :

Nous convertissons notre tableau d'origine en un raccourci clavier "intelligent" (dynamique) Ctrl+T ou équipe Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau), puis nous construisons un tableau croisé dynamique dessus avec la commande Insérer – Tableau croisé dynamique (Insérer - Tableau croisé dynamique). On met la date dans la zone lignes du récapitulatif, et le nombre de biens vendus dans la zone valeurs :

Veuillez noter que si vous avez une version pas tout à fait ancienne d'Excel, les dates sont automatiquement regroupées par années, trimestres et mois. Si vous avez besoin d'un groupement différent (ou si vous n'en avez pas du tout besoin), vous pouvez le corriger en cliquant avec le bouton droit sur n'importe quelle date et en sélectionnant des commandes Grouper / Dissocier (Grouper / Dissocier).

Si vous souhaitez voir à la fois les totaux résultants par périodes et le total cumulé dans une colonne séparée, il est logique de jeter le champ dans la zone de valeur Vendu à nouveau pour obtenir une copie du champ - nous y activerons l'affichage des totaux cumulés. Pour cela, faites un clic droit sur le champ et sélectionnez la commande Calculs supplémentaires – Total cumulé (Afficher les valeurs en tant que — Totaux cumulés):

Là, vous pouvez également sélectionner l'option d'augmenter les totaux en pourcentage, et dans la fenêtre suivante, vous devez sélectionner le champ pour lequel l'accumulation ira - dans notre cas, il s'agit du champ de date :

Les avantages de cette approche :

• Une grande quantité de données est rapidement lue.
• Aucune formule ne doit être saisie manuellement.
• Lors d'un changement dans les données source, il suffit de mettre à jour le résumé avec le bouton droit de la souris ou avec la commande Données – Actualiser tout.

Les inconvénients découlent du fait qu'il s'agit d'un résumé, ce qui signifie que vous ne pouvez pas y faire ce que vous voulez (insérer des lignes, écrire des formules, construire des diagrammes, etc.) ne fonctionnera plus.

Méthode 3 : Power Query

Chargeons notre table "intelligente" avec les données source dans l'éditeur de requête Power Query à l'aide de la commande Données – À partir du tableau/de la plage (Données – À partir du tableau/de la plage). Dans les dernières versions d'Excel, d'ailleurs, il a été renommé - maintenant il s'appelle Avec des feuilles (De la feuille):

Ensuite nous effectuerons les étapes suivantes :

1. Triez le tableau dans l'ordre croissant de la colonne de date avec la commande Trier par ordre croissant dans la liste déroulante des filtres dans l'en-tête du tableau.

2. Un peu plus tard, pour calculer le total cumulé, nous avons besoin d'une colonne auxiliaire avec le numéro de ligne ordinal. Ajoutons-le avec la commande Ajouter une colonne - Colonne d'index - À partir de 1 (Ajouter une colonne — Colonne d'index — À partir de 1).

3. De plus, pour calculer le total cumulé, nous avons besoin d'une référence à la colonne Vendu, où se trouvent nos données résumées. Dans Power Query, les colonnes sont également appelées listes (list) et pour obtenir un lien vers celle-ci, cliquez avec le bouton droit sur l'en-tête de la colonne et sélectionnez la commande Detailing (Montrer les détails). L'expression dont nous avons besoin apparaîtra dans la barre de formule, composée du nom de l'étape précédente #"Index ajouté", d'où nous prenons la table et le nom de la colonne [Ventes] de ce tableau entre crochets :

Copiez cette expression dans le presse-papiers pour une utilisation ultérieure.

4. Supprimer la dernière étape inutile Vendu et ajoutez à la place une colonne calculée pour calculer le total cumulé avec la commande Ajout d'une colonne - Colonne personnalisée (Ajouter une colonne — Colonne personnalisée). La formule dont nous avons besoin ressemblera à ceci :

Ici la fonction Liste.Plage reprend la liste d'origine (colonne [Ventes]) et en extrait des éléments, en commençant par le premier (dans la formule, il s'agit de 0, puisque la numérotation dans Power Query commence à partir de zéro). Le nombre d'éléments à récupérer est le numéro de ligne que nous prenons de la colonne [Indice]. Donc cette fonction pour la première ligne ne renvoie qu'une première cellule de la colonne Vendu. Pour la deuxième ligne – déjà les deux premières cellules, pour la troisième – les trois premières, etc.

Eh bien, alors la fonction Liste.Somme somme les valeurs extraites et on obtient dans chaque ligne la somme de tous les éléments précédents, c'est à dire le total cumulé :

Il reste à supprimer la colonne Index dont nous n'avons plus besoin et à télécharger les résultats dans Excel avec la commande Accueil - Fermer et charger sur.

Le problème est résolu.

Rapide et furieux

En principe, cela aurait pu être arrêté, mais il y a une petite mouche dans la pommade - la requête que nous avons créée fonctionne à la vitesse d'une tortue. Par exemple, sur mon PC qui n'est pas le plus faible, une table de seulement 2000 lignes est traitée en 17 secondes. Et s'il y a plus de données ?

Pour accélérer, vous pouvez utiliser la mise en mémoire tampon à l'aide de la fonction spéciale List.Buffer, qui charge la liste (liste) qui lui est donnée en tant qu'argument dans la RAM, ce qui accélère considérablement son accès à l'avenir. Dans notre cas, il est logique de mettre en mémoire tampon la liste # "Index ajouté" [Vendu], à laquelle Power Query doit accéder lors du calcul du total cumulé dans chaque ligne de notre table de 2000 lignes.

Pour cela, dans l'éditeur Power Query de l'onglet Principal, cliquez sur le bouton Éditeur avancé (Accueil – Éditeur avancé) pour ouvrir le code source de notre requête dans le langage M intégré à Power Query :

Et puis ajoutez une ligne avec une variable ici Ma liste, dont la valeur est retournée par la fonction de buffering, et à l'étape suivante on remplace l'appel à la liste par cette variable :

Après avoir apporté ces modifications, notre requête deviendra beaucoup plus rapide et traitera une table de 2000 lignes en seulement 0.3 seconde !

Autre chose, non ? 🙂

• Diagramme de Pareto (80/20) et comment le construire dans Excel
• Recherche par mot-clé dans le texte et mise en mémoire tampon des requêtes dans Power Query