Vous avez probablement remarqué l’option d’ajout d’un champ calculé dans les valeurs d’un tableau croisé dynamique dans Google Sheets. Savez-vous comment l’utiliser ?
Dans ce tutoriel Google Sheets, je vais vous expliquer quand et où utiliser cette fonctionnalité de tableau croisé dynamique dans Google Sheets.
Par exemple, vous pouvez résumer la valeur des ventes d’articles spécifiques sur une année par trimestre, mois ou les deux.
De plus, vous pouvez décomposer ce résumé par zone, par exemple, la valeur des ventes d’ordinateurs portables pour le mois de janvier dans la zone sud, la zone nord, etc.
De la même manière, vous pouvez trouver quel article s’est le plus vendu au cours d’un mois spécifique.
Généralement, vous n’avez pas besoin de formule pour résumer ou regrouper les données dans un tableau croisé dynamique car il dispose de fonctions telles que Somme, Compter, Max, Min, etc. à choisir.
Pour les types de manipulations de données de base, c’est suffisant. Mais vous pouvez également utiliser des formules personnalisées dans le tableau croisé dynamique pour le rendre encore plus performant.
Vous pouvez ajouter un champ calculé aux valeurs et entrer votre formule personnalisée, ce qui ajoutera une nouvelle colonne de champ calculé dans le tableau croisé dynamique. Voici les détails.
Comment ajouter un champ calculé dans un tableau croisé dynamique dans Google Sheets
Voici un jeu de données d’exemple.
C’est un jeu de données très basique qui peut vous aider à comprendre comment ajouter un champ calculé dans un tableau croisé dynamique dans Google Sheets.
1. Préparation d’un tableau croisé dynamique
Dans cet exemple de données, je peux regrouper les deux premières colonnes qui sont la date (colonne A) et le nom du matériau (colonne B).
Par exemple, si je groupe la colonne B, il y aura deux éléments : Gravier et Sable. Je groupe cette colonne dans mon exemple ci-dessous.
Voici comment le regrouper.
Tout d’abord, sélectionnez la plage de données A3:D9. Ensuite, allez dans le menu Insertion puis Tableau croisé dynamique (auparavant, il était dans le menu Données).
Sélectionnez si vous souhaitez le rapport dans la même feuille ou une nouvelle feuille. Je sélectionne « Nouvelle feuille ». Cliquez sur « Créer ».
Le panneau d’édition du tableau croisé dynamique s’ouvrira. Regroupons le champ « nom du matériau ».
Comment faire ?
Cliquez sur le bouton « Ajouter » à côté de « Lignes » et sélectionnez le champ « nom du matériau » pour regrouper les éléments Sable et Gravier.
Comment ajouter une colonne contenant la quantité totale (nombre d’unités) de ces deux éléments regroupés dans le rapport du tableau croisé dynamique ?
C’est similaire à l’ajout du « nom du matériau ».
Cliquez sur le bouton « Ajouter » à côté de « Valeurs » et sélectionnez le champ « nombre d’unités ». N’oubliez pas de choisir la fonction d’agrégation SOMME sous « Résumer par ».
Vous êtes plus près d’ajouter un champ calculé dans ce rapport de tableau croisé dynamique.
2. Ajout d’un champ calculé pour obtenir le prix unitaire le plus récent
J’ai regroupé les « noms de matériaux » dans le rapport ci-dessus. J’ai donc les noms de matériaux uniques et leur SOMME de « nombre d’unités ».
Maintenant, ce que je veux, c’est le « prix unitaire » correspondant. Ici, il y a un problème.
Les prix du gravier et du sable sont différents à différentes dates.
Je ne veux pas ajouter le prix unitaire maximum, minimum ou moyen à l’article regroupé. Ce que je veux, c’est le prix unitaire le plus récent.
Nous pouvons ajouter un champ calculé au tableau croisé dynamique à cette fin dans Google Sheets. Voici comment.
Veuillez noter que ma plage de données source A3:D9 est triée en fonction de la colonne de dates par ordre croissant.
C’est une condition préalable pour le type de formule que j’ajoute au champ calculé du tableau croisé dynamique.
Cela fonctionnera également dans un ensemble de données trié en fonction de la colonne de dates par ordre décroissant. Mais cette formule sera différente. Je la fournirai également plus tard.
- Dans le panneau d’édition du tableau croisé dynamique, ajoutez un « Champ calculé » aux Valeurs en cliquant sur le bouton « Ajouter » à côté de « Valeurs ».
- Supprimez la formule existante, c’est-à-dire = 0 dans le champ de saisie de formule qui apparaît.
- Sélectionnez « Personnalisé » sous « Résumer par ».
- Insérez la formule XLOOKUP suivante.
- Cliquez en dehors du champ de formule pour l’activer, et voilà !
=xlookup('nom du matériau','nom du matériau','prix unitaire',,0,-1)
Nous avons ajouté un champ calculé dans le tableau croisé dynamique dans Google Sheets.
Veuillez noter que chaque modification apportée aux données source se reflétera également dans la colonne du champ calculé.
Outre le « nom du matériau », nous avons une colonne « Somme du nombre d’unités » (quantité) et une colonne « Champ calculé 1 » (prix unitaire le plus récent) dans le tableau croisé dynamique. Veuillez consulter l’image ci-dessus.
Comment obtenir le montant, c’est-à-dire quantité prix unitaire le plus récent (« Somme du nombre d’unités » « Champ calculé 1 ») ?
Ajoutez un deuxième champ calculé et insérez la formule personnalisée suivante.
=somme('nombre d'unités') * xlookup('nom du matériau','nom du matériau','prix unitaire',,0,-1)
Veuillez vérifier le résultat dans ma feuille d’exemple ci-dessous.
Pivot Example 1520
Note : Si vous avez des données triées des dates les plus élevées aux plus basses, vous devez remplacer -1 dans la dernière partie des deux formules personnalisées par 1.
Points à retenir lors de la création de la formule
Voici les points clés à retenir lors de la création d’une formule pour le champ calculé dans Google Sheets.
- La référence doit pointer vers les données source, et non vers le tableau croisé dynamique lui-même.
- Utilisez les noms de champ dans les données source au lieu des références de plage. Par exemple, j’ai utilisé ‘prix unitaire’ au lieu de la plage D4:D9 dans mes formules.
- Les noms de champ dans la formule doivent être placés entre guillemets simples.
Renommer les champs calculés dans Google Sheets
Savez-vous comment renommer un champ calculé dans Google Sheets ?
Vous ne pouvez pas le faire dans le panneau d’édition du tableau croisé dynamique. Alors, comment ?
Allez dans la cellule contenant l’étiquette et écrivez le nom que vous souhaitez. Par exemple, allez dans la cellule C1 et saisissez « Prix ».
C’est tout. Merci pour votre attention. Profitez-en !
Liens : Crawlan.com
