oguruma
XLDnaute Occasionnel
Bonjour le Forum,
De retour après quelques mois d'absence.
Cette fonction GROUPER.PAR avait été abordée dans un autre post. A l'époque cette fonction était en mode INSIDER. Elle a été un long moment disponible via ce mode avec les risques connus par tous. J'y étais aussi à cette époque. Je l'ai abandonné suite à de nombreux problèmes. J'avais commencé quelques travaux sur cette fonction.
Voici la version finalisée avec quelques exemples sur l'utilisation.
Elle présente en quelques sortes les mêmes fonctionnalités de regroupement et d'agrégation de données que les TCD. Les résultats sont présentés sous forme de tableaux qu'il est possible de personnaliser via des MFC pour la présentation.
Je pense que c'est une erreur de croire que les TCD vont disparaître. Il faut considérer cette fonction comme un complément réutilisable pour la conception de tableaux secondaire. Les TCD par leur richesse ont encore je crois de beaux jours devant eux. Avant l'arrivée de cette fonction on contourner cette possibilité par des SOMME.SI, SOMME.SI.ENS, NB.SI, NB.SI.ENS... etc.....
Enfin pour les plus chevronnés il y a aussi l'outillage PowerQuery qui permet d'effectuer des regroupements de données.
On peut l'utiliser soit avec des plages de cellules ou soit des tableaux structurés.
SOURCE : PLAGE DE CELLULES
Syntaxe
Voir la documentation Microsoft si besoin pour plus de détails.
PARTIE 1 :: LES PLAGES DE DONNEES
01 - Cas simple
=GROUPER.PAR(A1:A36;G1:G36;SOMME;3) => nous avons un regroupement par opérateur - pour chaque opérateur on fait la somme des consommations. L'option 3 permet d'afficher les titres des colonnes. Bien entendu prendre la 1ère ligne du tableau dans la plage de cellules.
==> la mise en gras et le cadre est manuel. Rien n'est automatique. Pour cela il faudra passer par les MFC. Je présente un exemple plus bas.
02 - Cas plus évolué sur plusieurs colonnes contigues
=GROUPER.PAR(T_CONSO!A1:C36;T_CONSO!G1:G36;SOMME;3)
03 - Cas plus évolué à deux niveaux et sous-totalisation
=GROUPER.PAR(T_CONSO!$A$1:$D$36;T_CONSO!$G$1:$G$36;SOMME;3;2)
==> sous-total ; idem il faudra passer par les MFC pour le mettre en valeur
Avec un total général : =GROUPER.PAR(T_CONSO!$A$1:$D$36;T_CONSO!$G$1:$G$36;SOMME;3;1)
04 - Colonnes non contigues
=GROUPER.PAR(ASSEMB.H(T_CONSO!$A$1:$A$36;T_CONSO!$B$1:$B$36;T_CONSO!$E$1:$E$36);T_CONSO!$G$1:$G$36;SOMME;3)
L'astuce est d'utiliser la fonction tableau. On va construire le tableau à traitement dynamiquement via ASSEMB.H
On peut aussi passer par CHOISIRCOLS
=GROUPER.PAR(CHOISIRCOLS(T_CONSO;1;2;5);$G$1:$G$36;SOMME;3;2;-1)
05 - Colonnes triées
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;1)
1: Tri croissant sur la 1ère colonne
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;-4)
-4 : tri décroissant sur la colonne conso - la 4ème
06 - Filtrer les données à traiter
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};$A$1:$A$36="Caléo")
Filtre évolué - Voir la fonction FILTRE
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};(($A$1:$A$36="Caléo") * ($E$1:$E$36="X")))
On peut aussi rendre ce filtre dynamique via une liste déroulante
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};(($A$1:$A$36=SEL_OPER) * ($E$1:$E$36="X")))
Filtre numéric
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};$G$1:$G$36>10000)
{-1;-2;3} : syntaxe si on souhaite trier sur plusieurs colonnes (notion de listes)
Utilisation de la fonction LAMBDA qui produit le même résultat : =GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;LAMBDA(conso;SOMME(conso));3;2;{-1;-2;3};$G$1:$G$36>10000)
Calcul de pourcentages par rapport au total
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;POURCENTAGE.DE;3;2;{-1;-2;3};$G$1:$G$36>10000)
07 - Obtenir les données agrégées en texte
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;TABLEAU.EN.TEXTE;3;0)
08 - Les MFC
==> voir le fichier joint afin de voir le montage possible
09 - Plusieurs agrégats de calculs
On passera aussi par la fonction ASSEMB.H. Attention de bien respecter l'ordre des calculs souhaités
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;ASSEMB.H(NBVAL;SOMME;POURCENTAGE.DE;MOYENNE);1;3;{-1;-2;3};$G$1:$G$36>10000)
09 - Réutilisation d'un regroupement
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;LAMBDA(conso;SOMME(conso));3;2;{-1;-2;3};$G$1:$G$36>10000);FILTRE(agreg;INDEX(agreg;;4)>20000))
Pour cela on stock en mémoire le tableau via la fonction LET afin de le réutiliser pour d'autres traitements.
Cas où on veut filtrer sur le résultat. On ne retient que le total des consommations supérieur à 20 000. On doit stocker les agrégats dans une variable via la fonction LET qui va nous renvoyer un tableau de résultats en mémoire puis on va filtrer ce tableau sur la colonne 4 pour les totaux supérieurs à 20 000
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{1;2;3};$G$1:$G$36>10000);FILTRE(agreg;( (INDEX(agreg;;4)>20000) )))
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;POURCENTAGE.DE;3;2;{1;2;3};$G$1:$G$36>10000);FILTRE(agreg;( (INDEX(agreg;;4)>0.1) )))
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{1;2;3};( ($G$1:$G$36>10000) + ($B$1:$B$36="2017") ));FILTRE(agreg;( ( (INDEX(agreg;;4)>10000) * (INDEX(agreg;;3)="X") ) )))
=LET(agreg2;LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{1;2;3};( ($G$1:$G$36>10000) + ($B$1:$B$36="2017") ));FILTRE(agreg;( ( (INDEX(agreg;;4)>10000) * (INDEX(agreg;;3)="X") ) )));GROUPER.PAR(agreg2;INDEX(agreg2;;4);SOMME;3;2))
Bien que ces deux regroupements ci-dessus n'aient certainement aucun sens ils démontrent néanmoins que l'on peut récupérer le résultat d'un regroupement pour l'exploiter de nouveau par un autre regroupement avec position de filtres, etc. etc. etc. On ne retient que le secteur X dans le regroupement final en ce qui concerne le total. On peut filtrer sur les résultats ainsi obtenus.
PARTIE 2 :: TABLEAUX STRUCTURES
=GROUPER.PAR(TBL_CONSO_GAZ[operateur];TBL_CONSO_GAZ[conso];SOMME)
=GROUPER.PAR(TBL_CONSO_GAZ[operateur];TBL_CONSO_GAZ[conso];SOMME)
=GROUPER.PAR(TBL_CONSO_GAZ[[#Tout];[operateur]:[code_categorie_consommation]];TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;3;2)
=GROUPER.PAR(ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[operateur]];TBL_CONSO_GAZ[[#Tout];[libelle_grand_secteur]]);TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;3;2)
=GROUPER.PAR(ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[operateur]];TBL_CONSO_GAZ[[#Tout];[libelle_grand_secteur]]);TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;3;2;;TBL_CONSO_GAZ[conso]>20000)
=GROUPER.PAR(ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[operateur]];TBL_CONSO_GAZ[[#Tout];[libelle_grand_secteur]]);ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[conso]];TBL_CONSO_GAZ[[#Tout];[conso]];TBL_CONSO_GAZ[[#Tout];[conso]];TBL_CONSO_GAZ[[#Tout];[pdl]];TBL_CONSO_GAZ[[#Tout];[pdl]];TBL_CONSO_GAZ[[#Tout];[pdl]]);ASSEMB.H(SOMME;MAX;MIN;SOMME;MIN;MAX);3;2;;TBL_CONSO_GAZ[conso]>20000)
=GROUPER.PAR(INDEX(TBL_CONSO_GAZ;;4);TBL_CONSO_GAZ[conso];SOMME;1)
=GROUPER.PAR(TBL_CONSO_GAZ[code_grand_secteur];TBL_CONSO_GAZ[conso];SOMME;1)
=GROUPER.PAR(TBL_CONSO_GAZ[[#Tout];[code_categorie_consommation]];TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;1)
De retour après quelques mois d'absence.
Cette fonction GROUPER.PAR avait été abordée dans un autre post. A l'époque cette fonction était en mode INSIDER. Elle a été un long moment disponible via ce mode avec les risques connus par tous. J'y étais aussi à cette époque. Je l'ai abandonné suite à de nombreux problèmes. J'avais commencé quelques travaux sur cette fonction.
Voici la version finalisée avec quelques exemples sur l'utilisation.
Elle présente en quelques sortes les mêmes fonctionnalités de regroupement et d'agrégation de données que les TCD. Les résultats sont présentés sous forme de tableaux qu'il est possible de personnaliser via des MFC pour la présentation.
Je pense que c'est une erreur de croire que les TCD vont disparaître. Il faut considérer cette fonction comme un complément réutilisable pour la conception de tableaux secondaire. Les TCD par leur richesse ont encore je crois de beaux jours devant eux. Avant l'arrivée de cette fonction on contourner cette possibilité par des SOMME.SI, SOMME.SI.ENS, NB.SI, NB.SI.ENS... etc.....
Enfin pour les plus chevronnés il y a aussi l'outillage PowerQuery qui permet d'effectuer des regroupements de données.
On peut l'utiliser soit avec des plages de cellules ou soit des tableaux structurés.
SOURCE : PLAGE DE CELLULES
Syntaxe
Voir la documentation Microsoft si besoin pour plus de détails.
PARTIE 1 :: LES PLAGES DE DONNEES
01 - Cas simple
=GROUPER.PAR(A1:A36;G1:G36;SOMME;3) => nous avons un regroupement par opérateur - pour chaque opérateur on fait la somme des consommations. L'option 3 permet d'afficher les titres des colonnes. Bien entendu prendre la 1ère ligne du tableau dans la plage de cellules.
02 - Cas plus évolué sur plusieurs colonnes contigues
=GROUPER.PAR(T_CONSO!A1:C36;T_CONSO!G1:G36;SOMME;3)
03 - Cas plus évolué à deux niveaux et sous-totalisation
=GROUPER.PAR(T_CONSO!$A$1:$D$36;T_CONSO!$G$1:$G$36;SOMME;3;2)
Avec un total général : =GROUPER.PAR(T_CONSO!$A$1:$D$36;T_CONSO!$G$1:$G$36;SOMME;3;1)
04 - Colonnes non contigues
=GROUPER.PAR(ASSEMB.H(T_CONSO!$A$1:$A$36;T_CONSO!$B$1:$B$36;T_CONSO!$E$1:$E$36);T_CONSO!$G$1:$G$36;SOMME;3)
L'astuce est d'utiliser la fonction tableau. On va construire le tableau à traitement dynamiquement via ASSEMB.H
On peut aussi passer par CHOISIRCOLS
=GROUPER.PAR(CHOISIRCOLS(T_CONSO;1;2;5);$G$1:$G$36;SOMME;3;2;-1)
05 - Colonnes triées
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;1)
1: Tri croissant sur la 1ère colonne
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;-4)
-4 : tri décroissant sur la colonne conso - la 4ème
06 - Filtrer les données à traiter
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};$A$1:$A$36="Caléo")
Filtre évolué - Voir la fonction FILTRE
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};(($A$1:$A$36="Caléo") * ($E$1:$E$36="X")))
On peut aussi rendre ce filtre dynamique via une liste déroulante
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};(($A$1:$A$36=SEL_OPER) * ($E$1:$E$36="X")))
Filtre numéric
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{-1;-2;3};$G$1:$G$36>10000)
{-1;-2;3} : syntaxe si on souhaite trier sur plusieurs colonnes (notion de listes)
Utilisation de la fonction LAMBDA qui produit le même résultat : =GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;LAMBDA(conso;SOMME(conso));3;2;{-1;-2;3};$G$1:$G$36>10000)
Calcul de pourcentages par rapport au total
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;POURCENTAGE.DE;3;2;{-1;-2;3};$G$1:$G$36>10000)
07 - Obtenir les données agrégées en texte
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;TABLEAU.EN.TEXTE;3;0)
08 - Les MFC
==> voir le fichier joint afin de voir le montage possible
09 - Plusieurs agrégats de calculs
On passera aussi par la fonction ASSEMB.H. Attention de bien respecter l'ordre des calculs souhaités
=GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;ASSEMB.H(NBVAL;SOMME;POURCENTAGE.DE;MOYENNE);1;3;{-1;-2;3};$G$1:$G$36>10000)
09 - Réutilisation d'un regroupement
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;LAMBDA(conso;SOMME(conso));3;2;{-1;-2;3};$G$1:$G$36>10000);FILTRE(agreg;INDEX(agreg;;4)>20000))
Pour cela on stock en mémoire le tableau via la fonction LET afin de le réutiliser pour d'autres traitements.
Cas où on veut filtrer sur le résultat. On ne retient que le total des consommations supérieur à 20 000. On doit stocker les agrégats dans une variable via la fonction LET qui va nous renvoyer un tableau de résultats en mémoire puis on va filtrer ce tableau sur la colonne 4 pour les totaux supérieurs à 20 000
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{1;2;3};$G$1:$G$36>10000);FILTRE(agreg;( (INDEX(agreg;;4)>20000) )))
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;POURCENTAGE.DE;3;2;{1;2;3};$G$1:$G$36>10000);FILTRE(agreg;( (INDEX(agreg;;4)>0.1) )))
=LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{1;2;3};( ($G$1:$G$36>10000) + ($B$1:$B$36="2017") ));FILTRE(agreg;( ( (INDEX(agreg;;4)>10000) * (INDEX(agreg;;3)="X") ) )))
=LET(agreg2;LET(agreg;GROUPER.PAR(ASSEMB.H($A$1:$A$36;$B$1:$B$36;$E$1:$E$36);$G$1:$G$36;SOMME;3;2;{1;2;3};( ($G$1:$G$36>10000) + ($B$1:$B$36="2017") ));FILTRE(agreg;( ( (INDEX(agreg;;4)>10000) * (INDEX(agreg;;3)="X") ) )));GROUPER.PAR(agreg2;INDEX(agreg2;;4);SOMME;3;2))
Bien que ces deux regroupements ci-dessus n'aient certainement aucun sens ils démontrent néanmoins que l'on peut récupérer le résultat d'un regroupement pour l'exploiter de nouveau par un autre regroupement avec position de filtres, etc. etc. etc. On ne retient que le secteur X dans le regroupement final en ce qui concerne le total. On peut filtrer sur les résultats ainsi obtenus.
PARTIE 2 :: TABLEAUX STRUCTURES
=GROUPER.PAR(TBL_CONSO_GAZ[operateur];TBL_CONSO_GAZ[conso];SOMME)
=GROUPER.PAR(TBL_CONSO_GAZ[operateur];TBL_CONSO_GAZ[conso];SOMME)
=GROUPER.PAR(TBL_CONSO_GAZ[[#Tout];[operateur]:[code_categorie_consommation]];TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;3;2)
=GROUPER.PAR(ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[operateur]];TBL_CONSO_GAZ[[#Tout];[libelle_grand_secteur]]);TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;3;2)
=GROUPER.PAR(ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[operateur]];TBL_CONSO_GAZ[[#Tout];[libelle_grand_secteur]]);TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;3;2;;TBL_CONSO_GAZ[conso]>20000)
=GROUPER.PAR(ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[operateur]];TBL_CONSO_GAZ[[#Tout];[libelle_grand_secteur]]);ASSEMB.H(TBL_CONSO_GAZ[[#Tout];[conso]];TBL_CONSO_GAZ[[#Tout];[conso]];TBL_CONSO_GAZ[[#Tout];[conso]];TBL_CONSO_GAZ[[#Tout];[pdl]];TBL_CONSO_GAZ[[#Tout];[pdl]];TBL_CONSO_GAZ[[#Tout];[pdl]]);ASSEMB.H(SOMME;MAX;MIN;SOMME;MIN;MAX);3;2;;TBL_CONSO_GAZ[conso]>20000)
=GROUPER.PAR(INDEX(TBL_CONSO_GAZ;;4);TBL_CONSO_GAZ[conso];SOMME;1)
=GROUPER.PAR(TBL_CONSO_GAZ[code_grand_secteur];TBL_CONSO_GAZ[conso];SOMME;1)
=GROUPER.PAR(TBL_CONSO_GAZ[[#Tout];[code_categorie_consommation]];TBL_CONSO_GAZ[[#Tout];[conso]:[pdl]];SOMME;1)
