EXCEL :: Cases à cocher - Connaître les détails d'un regroupement issu d'un GROUPER.PAR

[oguruma]

Bonjour, pour creuser le sujet sur cette nouvelle option que j'avais mis en oeuvre chez un client et afin d'avoir la main sur le détail des données regroupées dans un GROUPER.PAR pourquoi pas mettre une case cocher et afficher le détail.... ça évite de partir en recherche dans le tableau.... Dans une précédente précédente prestation j'étais passé par un TCD à la volée....Segments, FILTRES, etc.... J'ai revu tout cela en passant par les cases à cocher.
L'exemple développé ci-dessous est une base de travail. C'est un plan de montage qui prendra un peu de temps.... mais moins que les plans de montages de meubles où on cherche toujours la pièce manquante 🙂 😀

Exemple à partir d'un tableau "Les musées en france" - données publiques

A partir d'un regroupement comme celui-ci calculant le nbr de musées par région pouvoir en connaître la liste quand on sélectionne un regroupement comme suit :
=GROUPER.PAR(TBL_MUSEES[[#Tout];[REGION_ADM]];TBL_MUSEES[[#Tout];[REGION_ADM]];NBVAL;1)

Pour plus de compréhension image avec les repères colonnes et lignes

et tout ceci SANS AUCUNE LIGNE DE CODE EN VBA !

La formule magique :
=SIERREUR(LET(
currentRow;SIERREUR(EQUIVX(VRAI;COL_CHECK;0);-1);
tbl;TRIER(FILTRE(TBL_MUSEES[[NOM_MUSEE]:[CODE_POSTAL]];TBL_MUSEES[REGION_ADM]=INDEX(COLONNE_MATCH;currentRow);""));
SI(currentRow=-1;"Sélection ?";SI(currentRow=1;ASSEMB.V(TBL_MUSEES[[#En-têtes];[NOM_MUSEE]:[CODE_POSTAL]];tbl);ASSEMB.V(ETENDRE($E$3:$H$3;currentRow-1;;"");TBL_MUSEES[[#En-têtes];[NOM_MUSEE]:[CODE_POSTAL]];tbl))));"Erreur")

C'est un peu "brut de fonderie" - ça mérite un peu d'explications
Etant donné que l'on a besoin de stocker des résultats de calculs pour les récupérer dans d'autres et cela en une seul formule on a donc besoin de la fonction LET

1/ On va rechercher sur quelle ligne on se trouve - pour quelle région on souhaite connaître la liste des musées. Cette ligne on va la mémoriser dans une variable
currentRow;SIERREUR(EQUIVX(VRAI;COL_CHECK;0);-1)
COL_CHECK : Nom de champ
=DECALER(CheckBox!$B$4;;2;NBVAL(INDEX(CheckBox!$B$4#;;1))-1) - sa profondeur est calculée dynamiquement
INDEX(CheckBox!$B$4#;;1)) ==> on va cibler la colonne où il y a les cases à cocher
NBVAL(INDEX(CheckBox!$B$4#;;1))-1 : on détermine la profondeur en calculant le nbr de lignes occupées - 1 pour ne pas prendre en compte la ligne totale
Et l'adresse du champs démarre en $B$4
Tout ceci se matérialise comme suit - à adapter en fonction de vos tableaux

EQUIVX : va donc renvoyer la ligne - valeur recherchée => VRAI dans la colonne COL_CHECK et la ligne trouvée est mémorisée dans la variable currentRow et en cas d'échec (non trouvé) on renvoie la valeur -1 qui sera réutilisée dans à la fin du LET
On arrive au début des complications pour obtenir cette liste

2/ tbl;TRIER(FILTRE(TBL_MUSEES[[NOM_MUSEE]:[CODE_POSTAL]];TBL_MUSEES[REGION_ADM]=INDEX(COLONNE_MATCH;currentRow);""));

Pour récupérer les enregistrements dans le tableaux de données on passera par la fonction FILTRE dans laquelle on précisera les colonnes que l'on souhaite afficher ici

puis ensuite vient le filtre
TBL_MUSEES[REGION_ADM]=INDEX(COLONNE_MATCH;currentRow);"")

Le filtre fait appel à un nom de champ calculé dynamiquement comme ceci
=DECALER(CheckBox!$B$4;;;NBVAL(INDEX(CheckBox!$B$4#;;1))-1) - Le principe est identique à COL_CHECK

Le résultat du FILTRE est mémorisé dans tbl

On arrive au traitement final qui consiste à afficher sur la bonne ligne le contenu du tableau en mémoire tbl
SI(currentRow=-1;"Sélection ?";SI(currentRow=1;ASSEMB.V(TBL_MUSEES[[#En-têtes];[NOM_MUSEE]:[CODE_POSTAL]];tbl);ASSEMB.V(ETENDRE($E$3:$H$3;currentRow-1;;"");TBL_MUSEES[[#En-têtes];[NOM_MUSEE]:[CODE_POSTAL]];tbl))));"Erreur")

Déjà on traite le cas où on n'a rien trouvé où aucune case n'a été cochée currentRow=-1;"Sélection ?"

On récupère les titres des colonnes dans le tableau de données TBL_MUSEES[[#En-têtes];[NOM_MUSEE]:[CODE_POSTAL]] que l'on va concaténer au tableau de données avec ASSEMB.V

On passera pour la fonction ETENDRE afin de bien fixer la position si la première ligne n'est pas sélectionnée - ainsi on conserve la profondeur initiale ce qui permet de placer le résultat sur la même ligne que celle sélectionnée ETENDRE($E$3:$H$3;currentRow-1;;"")

On aurait pu se passer de la fonction ETENDRE mais dans ce cas l'affichage n'aurait pas été à côté de la ligne sélectionnée... ce qui aurait été gênant pour un tableau dépassant la hauteur de l'écran... Pas bête CROSOFT d'avoir penser à cette fonction 🙂

Bien entendu comme dit en introduction, c'est une méthode. A vous de vous en inspirer pour l'adapter à vos propres tableaux. Ce n'est pas du clic and collect !

Solution peut-être un peu ardue... si plus simple je suis preneur... en attendant j'y regarde.... cependant face à l'urgence pour répondre à la demande qui m'avait été faite "ça a fait le job" et gain de temps quand vous animez une conf... et qu'il faut expliquer les détails d'un regroupement face à des beaucéens EXCEL 🙂
Enfin.... ne pas oublier que la mise en forme est réalisée par les MFC. Je vous lire la conception de celles-ci.

Il y a une MFC qui efface les zéros de la ligne 1 provoqués par la fonction ETENDRE... je n'ai pas trouvé d'autres astuces pour les masquer 🙁