Vous vous demandez comment filtrer les 3 meilleures valeurs dans chaque groupe dans un rapport de tableau croisé dynamique sur Google Sheets ? Actuellement, nous devons utiliser la formule personnalisée dans l’éditeur de tableau croisé dynamique pour y parvenir. Il n’existe pas de fonctionnalité intégrée pour cela.
Ce tutoriel a pour but de vous aider à rédiger la formule requise à utiliser dans le champ de formule personnalisée de l’éditeur de tableau croisé dynamique.
En réalité, nous pouvons utiliser la même formule (la formule que nous allons écrire) pour filtrer les « n » premiers (par exemple, les 1, 2, 3, 4, 5, 6…10, etc.) dans chaque groupe du tableau croisé dynamique.
Ce tutoriel contient deux formules à utiliser dans deux scénarios différents. C’est-à-dire qu’il existe deux types de rapports.
Vous trouverez ci-dessous les deux images correspondant aux deux types de rapports :
Rapport n°1 (Résumé des ventes par entreprise des 3 meilleurs produits de chaque groupe) :
Rapport n°2 (Résumé des ventes des 3 meilleurs produits de chaque groupe) :
Explication des rapports de tableau croisé dynamique et des données d’exemple
Les données d’exemple (dans le Rapport n°1 et le Rapport n°2 ci-dessus) font en réalité partie d’un rapport de ventes de produits concasseurs.
Il y a deux produits de vente :
- Sable noir 0-5 mm.
- Base de route – Concassé 0-50 mm.
Les 3 meilleures ventes de chaque produit sont les suivantes :
- Sable noir 0-5 mm – 800, 700 et 600.
- Base de route – Concassé 0-50 mm – 800, 500 et 300.
Nous souhaitons filtrer ces 3 meilleures valeurs (ventes) dans chaque groupe dans les rapports de tableau croisé dynamique.
Le premier rapport comporte trois colonnes dans les données d’exemple : Produit, Entreprise et Ventes.
Ainsi, dans le premier rapport de tableau croisé dynamique (Rapport n°1), il y a une colonne produit, une colonne entreprise, puis les 3 meilleures ventes (si une entreprise apparaît plus d’une fois dans les 3 meilleures ventes, ces valeurs seront regroupées).
Dans les données d’exemple du deuxième rapport de tableau croisé dynamique (Rapport n°2), il y a deux colonnes : Produit et Ventes. Le rapport de tableau croisé dynamique n’a donc que deux colonnes : Produit et résumé des 3 meilleures ventes.
Voyons comment filtrer les 3 meilleures valeurs de vente dans chaque groupe dans le rapport de tableau croisé dynamique sur Google Sheets.
Comment sélectionner les 3 meilleures valeurs dans chaque groupe dans un tableau croisé dynamique sur Google Sheets
Avant de commencer à rédiger la formule, créons le rapport de tableau croisé dynamique. Ensuite, nous pourrons écrire et insérer la formule dans le champ de formule correspondant de l’éditeur de tableau croisé dynamique.
Rapport de tableau croisé dynamique
Je sais que vous êtes habitué à créer des rapports de tableau croisé dynamique sur Google Sheets. Cependant, je vais inclure ci-dessous les étapes pour les débutants.
Voici les étapes pour le tableau croisé dynamique requis qui filtre les « n » premières valeurs dans chaque groupe (en utilisant les données d’exemple du rapport n°1 [veuillez consulter la première image en haut]) :
- Sélectionnez la plage A1:C14. Cliquez ensuite sur Données > Tableau croisé dynamique.
- Activez/sélectionnez « Feuille existante » car nous créons le rapport dans la même feuille contenant les données d’exemple.
- Sélectionnez la cellule E2 et cliquez sur « Créer ».
Vous obtiendrez un « aperçu » du rapport et le panneau de l’éditeur de tableau croisé dynamique s’ouvrira à l’écran.
Suivez maintenant les étapes ci-dessous pour finaliser le rapport :
- Dans le panneau de l’éditeur, cliquez sur « Ajouter » à côté de « Lignes » et sélectionnez « Produit » (activez « Afficher le total »).
- Répétez l’étape précédente et sélectionnez « Entreprise » (activez « Afficher le total »).
- Cliquez sur « Ajouter » à côté de « Valeurs » et sélectionnez « Ventes ». À cette dernière étape, assurez-vous que « SUM » est sélectionné en dessous de « Résumer par » et que « Afficher » est défini sur « Par défaut ».
Vous obtiendrez le rapport de tableau croisé dynamique suivant :
Comment filtrer les 3 meilleures valeurs de chaque groupe, c’est-à-dire « Sable noir 0-5 mm » et « Base de route – Concassé 0-50 mm » ?
Logique pour filtrer les 3 meilleures valeurs de chaque groupe dans le tableau croisé dynamique sur Google Sheets
La logique est la suivante (si vous trouvez la logique difficile à comprendre, vous pouvez ignorer cette partie logique ou la lire après avoir terminé d’écrire la formule) :
Nous allons trier les produits par ventes dans l’ordre décroissant. Cela signifie que nous allons trier la colonne « Produit » par ordre croissant (A-Z) puis la colonne « Ventes » par ordre décroissant (Z-A). Nous allons le faire à l’aide d’une formule, pas physiquement dans la feuille.
Ensuite, nous allons utiliser ma formule de tableau de comptage en cours d’exécution (RC) pour retourner le comptage en cours d’exécution des « Produits ».
En utilisant un test logique SI, nous allons retourner les valeurs des lignes où le comptage en cours d’exécution est inférieur ou égal à 3 ou « N ».
L’étape suivante consiste à combiner le « Produit » et les « Ventes » correspondants.
Enfin, nous allons utiliser la fonction Regexmatch pour filtrer les valeurs combinées dans la plage d’origine A2:C du tableau croisé dynamique.
Nous allons suivre la logique ci-dessus pour coder une formule personnalisée à utiliser dans l’éditeur de tableau croisé dynamique pour filtrer les 3 meilleures valeurs dans chaque groupe dans le rapport de tableau croisé dynamique sur Google Sheets.
Codage de la formule personnalisée pour le rapport n°1
J’ai quelques tutoriels sur le comptage en cours d’exécution. Voici le lien spécifique pour apprendre ce qui est approprié à notre objectif – Comptage cumulatif de tous les articles dans une liste triée.
Ne vous laissez pas confondre par le terme « trié » dans le titre du lien. Il s’agit en réalité de trier les « Produits », pas les « Ventes ».
Étape 1 – Formule RC
Dans la cellule D2, insérez la formule RC suivante (nous codons simplement la formule dans la feuille de calcul pour l’utiliser ultérieurement dans l’éditeur de tableau croisé dynamique) :
=ARRAYFORMULA(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)))
Résultat :
J’ai utilisé une formule ARRAYFORMULA basée sur COUNTIFS pour RC. Vous pouvez voir que le comptage recommence dans le résultat lorsque le « Produit » change.
Étape 2 – Tri de RC en Triant les Ventes
C’est la deuxième étape pour filtrer les 3 meilleures ventes (« N ») de chaque groupe dans le rapport de tableau croisé dynamique sur Google Sheets.
Ici, nous allons légèrement modifier la formule ci-dessus pour trier la plage par « Produit » en ordre croissant (A-Z) et par « Ventes » en ordre décroissant (Z-A).
Remplacez simplement A2:A (les deux premières occurrences) dans la formule par la formule suivante :
=array_constrain(sort(A2:C,1,1,3,0),9^9,1)
La formule SORT trie la première colonne de A2:C par ordre croissant et la dernière colonne de A2:C par ordre décroissant.
La formule ARRAY_CONSTRAIN limite le nombre de colonnes dans A2:C à 1, c’est-à-dire A2:A.
Voici la formule après la modification susmentionnée :
=ARRAYFORMULA(COUNTIFS(array_constrain(sort(A2:C,1,1,3,0),9^9,1),array_constrain(sort(A2:C,1,1,3,0),9^9,1),ROW(A2:A),"<="&ROW(A2:A)))
Étape 3 – Combinaison du Produit et des Ventes en Fonction du RC Trié
Formule générique :
=Si(résultat_étape_2<=3;produit_trié&ventes_triées;)
Pour obtenir le produit_trié, utilisez la formule SORT suivante :
=array_constrain(sort(A2:C,1,1,3,0),9^9,1)
La formule pour ventes_triées :
=array_constrain(sort({C2:C,A2:A},2,1,1,0),9^9,1)
Voici la formule de l’étape 3 selon la formule générique ci-dessus :
=ArrayFormula(if(COUNTIFS(array_constrain(sort(A2:C,1,1,3,0),9^9,1),array_constrain(sort(A2:C,1,1,3,0),9^9,1),ROW(A2:A),"<="&ROW(A2:A))<=3,array_constrain(sort(A2:C,1,1,3,0),9^9,1)&array_constrain(sort({C2:C,A2:A},2,1,1,0),9^9,1),))
Vous devez simplement filtrer les lignes de A2:C correspondant aux sorties ci-dessus dans la colonne D pour sélectionner uniquement les 3 meilleures valeurs de chaque groupe dans le tableau croisé dynamique sur Google Sheets. Lisez la suite pour savoir comment faire cela.
Étape 4 – Regexmatch pour Filtrer les Valeurs « N » Supérieures dans Chaque Groupe du Tableau Croisé Dynamique sur Google Sheets
La formule générique de Regexmatch serait la suivante :
regexmatch(Produit&Ventes,résultat_étape_3_sous_forme_d_expression_régulière)
Dans cette formule, « Produit » et « Ventes » sont les étiquettes des colonnes (A1:A et C1:C). Nous pouvons utiliser les étiquettes des colonnes au lieu des références de tableau/plage dans le champ de formule de l’éditeur de tableau croisé dynamique.
Le résultat de l’étape 3 (résultat de l’étape 3 de la formule) doit être formaté en expression régulière. TextJoin fera cela.
Voyez la partie surlignée qui est la partie ajoutée à la formule de l’étape 3 pour former l’expression régulière.
Voici la formule tant attendue pour filtrer les 3 meilleures valeurs par groupe dans le tableau croisé dynamique sur Google Sheets :
=regexmatch(Produit&Ventes;"^"&textjoin("$|^";1;ArrayFormula(if(not(len(A2:A));;if(COUNTIFS(array_constrain(sort(A2:C;1;1;3;0);9^9;1);array_constrain(sort(A2:C;1;1;3;0);9^9;1);ROW(A2:A);"<="&ROW(A2:A))<=3;array_constrain(sort(A2:C;1;1;3;0);9^9;1)&array_constrain(sort({C2:C;A2:A};2;1;1;0);9^9;1))))&"$")
Comment utiliser cette formule dans le tableau croisé dynamique ?
Pour insérer la formule ci-dessus qui filtre/sélectionne les 3 meilleures valeurs dans chaque groupe dans le tableau croisé dynamique sur Google Sheets, suivez les étapes ci-dessous :
- Cliquez sur la cellule E2 pour activer le panneau de l’éditeur de tableau croisé dynamique.
- Faites défiler vers le haut de l’éditeur et modifiez la « Plage de données » de A1:C14 en A1:C100 ou jusqu’à la plage souhaitée (vous pouvez inclure des lignes vides pour accueillir les futures ventes).
- Faites défiler vers le bas du panneau et cliquez sur « Ajouter » à côté de « Filtre ».
- Sélectionnez « Produit », qui est le champ de groupe principal.
- Cliquez sur la liste déroulante (par défaut, elle peut afficher « Afficher tous les éléments ») et sélectionnez « Filtrer par condition » > « La formule personnalisée est ».
- Insérez la formule ci-dessus et cliquez sur « OK ».
C’est tout !
Codage de la formule personnalisée pour le rapport n°2
Au début de cet article, je vous ai montré deux types de rapports. J’ai détaillé le rapport n°1 (3 colonnes : Produit, Entreprise et Ventes) ci-dessus.
Dans le rapport n°2 (2 colonnes : Produit et Ventes), nous pouvons utiliser la formule ci-dessus avec quelques modifications. Ces modifications se trouvent à l’étape 2 et à l’étape 3.
Étant donné que nous avons deux colonnes, nous devons modifier la plage de tri en conséquence. Cela signifie que, à l’étape 2, A2:A doit être remplacé par la formule suivante : array_constrain(sort(A2:B,1,1,2,0),9^9,1).
Ici, à l’étape 3, le produit_trié sera array_constrain(sort(A2:B,1,1,2,0),9^9,1) et les ventes_triées seront array_constrain(sort({B2:B,A2:A},2,1,1,0),9^9,1).
Il n’y a pas d’autres changements. Voici la formule finale (après avoir incorporé les modifications susmentionnées) à utiliser dans l’éditeur de tableau croisé dynamique pour filtrer les 3 meilleures valeurs dans chaque groupe :
=regexmatch(Produit&Ventes;"^"&textjoin("$|^";1;ArrayFormula(if(not(len(A2:A));;(if(COUNTIFS(array_constrain(sort(A2:B;1;1;2;0);9^9;1);array_constrain(sort(A2:B;1;1;2;0);9^9;1);ROW(A2:A);"<="&ROW(A2:A))<=3;array_constrain(sort(A2:B;1;1;2;0);9^9;1)&array_constrain(sort({B2:B;A2:A};2;1;1;0);9^9;1))))))&"$")
Comment filtrer les « N » premières valeurs au lieu de 3 dans chaque groupe dans le tableau croisé dynamique ?
Dans la formule, remplacez <=3 par <=n. Remplacez « n » par 5 ou par le nombre de lignes que vous souhaitez filtrer dans chaque groupe du tableau croisé dynamique.
Et voilà. Amusez-vous bien !
