Comment ajouter un total cumulé dans un tableau croisé dynamique sur Google Sheets

Si vous avez déjà essayé d’ajouter un total cumulé dans un tableau croisé dynamique sur Google Sheets sans succès, voici les raisons et comment le faire :

Avant d’ajouter un total cumulé à un tableau croisé dynamique sur Google Sheets, considérez ces points clés que nous discuterons en détail dans ce tutoriel :

1. Assurez-vous que vos données source sont triées dans le même ordre que les champs de regroupement dans le tableau croisé dynamique pour des calculs de total cumulé précis.
2. Insérez une colonne d’aide dans les données source pour afficher le total cumulé dans le tableau croisé dynamique. Cette colonne comprendra une formule matricielle dans la cellule supérieure.
3. Placez les champs uniquement dans la section « LIGNES » du tableau croisé dynamique. Incluez les champs dans les sections « LIGNES » et « COLONNES » uniquement si vous êtes à l’aise avec la présence de cellules vides entre les totaux cumulés.

Par exemple, considérons deux catégories : A et B. A a une production en janvier, février, avril et mai, tandis que B a une production de janvier à mai, chaque mois avec une quantité de production de 5 tonnes. Les totaux cumulés résultants dans le tableau croisé dynamique peuvent apparaître comme suit :

Cependant, notez que la valeur dans la cellule vide (mars) devrait idéalement être de 10, en reportant la valeur du mois précédent. Malheureusement, cela ne se produira pas.

Il est important de prendre en compte ces considérations pour réussir à ajouter un total cumulé à un tableau croisé dynamique sur Google Sheets.

Avantages de l’inclusion d’un total cumulé dans un tableau croisé dynamique sur Google Sheets

1. Facilite la comparaison : Ajouter un total cumulé permet de comparer facilement les valeurs cumulatives entre différentes catégories ou groupes.
2. Expansion et réduction des données : Les utilisateurs peuvent développer ou réduire les données (exploration en profondeur) dans un tableau croisé dynamique, offrant ainsi une flexibilité dans l’exploration des données.
3. Utilisation de la fonction de regroupement du tableau croisé dynamique : Si vos données source incluent un champ de date (colonne), la fonction « Créer un groupe de tableau croisé dynamique » dans le tableau croisé dynamique peut être utilisée. La somme cumulée répondra automatiquement à ce regroupement.
4. Compréhension séquentielle des tendances : Ces fonctionnalités combinées contribuent à la compréhension séquentielle des tendances et des motifs, offrant ainsi des informations précieuses sur l’évolution des données.

En incorporant un total cumulé et en utilisant les fonctionnalités d’un tableau croisé dynamique sur Google Sheets, les utilisateurs peuvent améliorer leur capacité à analyser, comparer et interpréter les tendances des données de manière plus efficace.

Je vais vous guider à travers deux exemples pour vous familiariser avec l’ajout d’une somme cumulative aux tableaux croisés dynamiques de Google Sheets.

L’utilisation de deux exemples est cruciale car les niveaux de regroupement dans les sections « LIGNES » et « COLONNES » peuvent varier en fonction de vos données source. De plus, vous devrez peut-être ajuster les colonnes dans la formule de la colonne d’aide en fonction de votre ensemble de données spécifique.

Voici mon exemple de feuille en mode copie :

Sample Sheet

Ajout de totaux cumulés dans des tableaux croisés dynamiques regroupés par lignes

Les données d’exemple fournies comprennent des matériaux fournis pour deux projets différents provenant de sources distinctes, ainsi que leurs quantités. Il existe également deux types de matériaux : Roadbase et Sand.

Structure des données : Projet, Matériau et Source dans les colonnes A à C, et Quantité fournie dans la colonne D.

Création d’un tableau croisé dynamique avec une somme cumulative : Guide étape par étape

J’ai organisé les étapes en quatre catégories : Création du tableau croisé dynamique, Tri des données, Ajout de la formule et Réglages finaux pour ajouter le total cumulé dans le tableau croisé dynamique.

1. Création du tableau croisé dynamique

Pour créer le tableau croisé dynamique, suivez les étapes ci-dessous :

1. Sélectionnez la plage A1:E.
2. Cliquez sur « Insertion » > « Tableau croisé dynamique ».
3. Choisissez « Feuille existante » et sélectionnez G1 dans le champ ci-dessous, puis cliquez sur « Créer ».
4. Ajoutez les champs comme indiqué dans la capture d’écran ci-dessous.

Nous avons ajouté Projet, Matériau et Source tout en décochant « Afficher le total » dans chaque champ. Vous pouvez choisir de cocher ou de décocher « Répéter les étiquettes de ligne » dans ces champs selon vos besoins.

En plus de ce qui précède, j’ai inclus le champ « Projet » sous la section « FILTRES » pour exclure les lignes vides du rapport. Cette étape est inutile si vous utilisez la plage de données réelle, A1:E19. Cependant, comme nous avons utilisé une plage plus grande, A1:E1000, comprenant diverses lignes vides, ce filtrage devient essentiel.

Remarque : Si vous êtes nouveau dans l’utilisation des tableaux croisés dynamiques, consultez ce tutoriel : Comment créer un rapport de tableau croisé dynamique pour résumer les données sur Google Sheets.

2. Tri des données sources

Assurez-vous de trier les données dans l’ordre dans lequel vous avez ajouté les champs dans le tableau croisé dynamique, en particulier, triez par « Projet », « Matériau » et « Source ».

1. Sélectionnez la plage A1:D19.
2. Cliquez sur « Données » > « Trier la plage » > « Options de tri avancées de la plage ».
3. Cochez « La plage contient des en-têtes » et triez par « Projet », « Matériau » et « Source ».

3. Ajout d’une formule pour la colonne d’aide

Ensuite, nous allons utiliser une formule matricielle dans la première ligne de la colonne d’aide, ici en E2. Dans cette formule, nous allons appliquer le regroupement aux champs à l’exception du dernier champ de la section « LIGNES ». En d’autres termes, nous utiliserons les champs « Projet » et « Matériau » dans la formule, en excluant le champ « Source ».

Dans la cellule E2, insérez la formule matricielle suivante :

=ARRAYFORMULA(MAP(A2:A, B2:B, LAMBDA(a, b, IF(a="", , SUMIFS(D2:D, A2:A, a, B2:B, b, ROW(A2:A), "<="&ROW(a)) ))))

La formule fournie renvoie le total cumulé regroupé par A2:A et B2:B, en excluant C2:C comme mentionné précédemment.

Anatomie de la formule :

Commençons par la syntaxe de la fonction SOMME.SI.ENS.

SOMME.SI.ENS(plage_somme, plage_critère1, critère1, [plage_critère2, ...], [critère2, ...])

Où :

• plage_somme : D2:D
• plage_critère1 : A2:A
• critère1 : a
• plage_critère2 : B2:B
• critère2 : b
• plage_critère3 : ROW(A2:A)
• critère3 : « <= »&ROW(a)

La formule additionne la plage D2:D si A2:A est égal à a, B2:B est égal à b et ROW(A2:A) est inférieur ou égal à ROW(a). Les identificateurs a et b correspondent à A2 et B2 dans la première ligne, A3 et B3 dans la deuxième ligne, et ainsi de suite. La fonction MAP est utilisée à cette fin.

Référez-vous à mon tutoriel, Somme cumulative avec plusieurs sous-catégories, pour une explication détaillée de la formule. Ici, je vais vous aider à modifier la formule pour ajouter ou supprimer des champs de regroupement.

La formule utilise actuellement deux champs (colonnes) pour le regroupement : A2:A et B2:B. J’ai spécifié ces tableaux dans MAP comme MAP(A2:A, B2:B, et dans SUMIFS, utilisez A2:A, a, B2:B, b,.

Si vous avez un champ supplémentaire (colonne), par exemple, C2:C, vous pouvez l’incorporer comme MAP(A2:A, B2:B, C2:C, et A2:A, a, B2:B, b, C2:C, c,.

Suivez une approche similaire pour ajouter plus de niveaux de regroupement ou supprimer des niveaux existants.

Lors de l’ajout ou de la suppression de colonnes dans la formule, assurez-vous de conserver la cohérence dans le tri et également dans la section « LIGNES » du tableau croisé dynamique. S’il y a 4 champs dans la section « LIGNES », il y aura 4 colonnes pour le tri et 3 colonnes dans la formule pour le regroupement.

4. Étapes finales pour ajouter un total cumulé dans le tableau croisé dynamique

Ensuite, allez dans le panneau d’édition du tableau croisé dynamique et ajoutez le champ « Total cumulé » sous la section « VALEURS ». Choisissez la fonction MAX dans le menu déroulant de « Résumer par ».

Double-cliquez sur la cellule J1 et remplacez « MAX du total cumulé » par « Total cumulé ».

Cette méthode vous permet d’ajouter facilement des totaux cumulés aux tableaux croisés dynamiques lorsque vous utilisez des champs exclusivement dans la section « LIGNES » sur Google Sheets.

Ajout de totaux cumulés dans des tableaux croisés dynamiques regroupés par lignes et colonnes

Que se passe-t-il si nous ajoutons des totaux cumulés dans les lignes du tableau croisé dynamique sur Google Sheets ?

Dans l’exemple ci-dessus, les sommes cumulées sont ajoutées dans une colonne du tableau croisé dynamique car nous avons regroupé les données par lignes.

Si nous utilisons des champs dans les sections « LIGNES » et « COLONNES » de l’éditeur de tableaux croisés dynamiques, les totaux cumulés doivent être ajoutés dans plusieurs colonnes et lus ligne par ligne.

Apprenons cela avec un exemple. Ici aussi, nous avons des données dans A1:D, où A, B, C et D contiennent la Date, la Catégorie, la Sous-catégorie et la Quantité, respectivement.

Considérez les paramètres suivants pour l’éditeur de tableau croisé dynamique.

J’ai ajouté les champs « Catégorie » dans les « LIGNES » et « Sous-catégorie » et « Date » dans les « COLONNES ». De plus, j’ai inclus le champ « Catégorie » dans la section « FILTRES » pour filtrer les lignes vides dans la plage évaluée.

Formule et réglages supplémentaires

Triez les données dans l’ordre Catégorie > Sous-catégorie > Date. Cela suit les champs des sections « LIGNES » et « COLONNES ». C’est similaire à nos paramètres précédents.

1. Sélectionnez A1:D13 et cliquez sur « Données » > « Trier la plage » > « Options de tri avancées de la plage ».
2. Cochez « La plage contient des en-têtes ».
3. Triez par Catégorie > Sous-catégorie > Date.

Utilisez les champs « Catégorie » et « Sous-catégorie » pour le regroupement dans la formule. Comme précédemment, nous n’avons pas besoin de tenir compte du dernier champ pour le regroupement ; ici, il s’agit de la « Date ».

Insérez la formule suivante dans la cellule E2 :

=ARRAYFORMULA(MAP(B2:B, C2:C, LAMBDA(b, c, IF(b="", , SUMIFS(D2:D, B2:B, b, C2:C, c, ROW(A2:A), "<="&ROW(b))))))

Pour ajouter un total cumulé dans le tableau croisé dynamique, vous avez besoin de quatre réglages supplémentaires pour terminer.

1. Cliquez avec le bouton droit de la souris sur n’importe quelle date dans le rapport du tableau croisé dynamique et sélectionnez « Créer un groupe de dates de tableau croisé dynamique » > « Mois » (vous pouvez choisir « Année », « Trimestre », etc., en fonction de vos besoins).
2. Ouvrez le panneau d’édition du tableau croisé dynamique et ajoutez le champ « Total cumulé » sous la section « VALEURS ».
3. Sélectionnez « Max » sous « Résumer par ».
4. Double-cliquez sur la cellule G1 et remplacez « MAX du total cumulé » par « Total cumulé ».

Notes supplémentaires

Si vous regroupez par « LIGNES » et « COLONNES », n’incluez qu’un seul champ dans la section « LIGNES » du tableau croisé dynamique. L’ajout d’un autre champ entraînera une exploration en profondeur dans le rapport du tableau croisé dynamique, ce qui peut ne pas être utile car les totaux cumulés sont disposés horizontalement.

Il se peut que vous rencontriez parfois le problème de la cellule vide, comme expliqué au début de ce tutoriel.

Principales conclusions

Les totaux cumulés fournissent des informations précieuses pour le suivi des performances et l’analyse comparative. Les intégrer aux tableaux croisés dynamiques sur Google Sheets améliore leur utilité.

Dans ce tutoriel, nous avons expliqué comment incorporer des totaux cumulés dans les tableaux croisés dynamiques sur Google Sheets. Voici les principales conclusions :

1. Commencez par créer le tableau croisé dynamique souhaité.
2. Triez les données source selon l’ordre de regroupement dans le panneau d’édition du tableau croisé dynamique.
3. Dans une colonne d’aide, utilisez une formule pour calculer les totaux cumulés en fonction du regroupement choisi. Le regroupement doit correspondre à l’ordre de tri, mais exclure le dernier champ.
4. Sous la section « VALEURS », ajoutez le champ de la colonne d’aide pour les totaux cumulés et résumez-le par « MAX ». Il n’est pas nécessaire d’utiliser le champ calculé.

C’est tout !