XL 2019 Regroupement données dans un tableau

[Shimano]

Bonjour la Communauté.
Je vous avais déjà sollicité l'an dernier, mais entretemps, j'ai du modifier et tenter de m'adapter aux changements imposés.
En joignant mon fichier, je souhaite vous solliciter.

J'ai une base de données dans le 1er onglet (export) et dans le second une liste (Listes-Srv).
Je souhaiterai dans un 3ème onglet (Résultats) avoir un tableau regroupant selon les critères de la liste les données de mon export.

Ex : Tableau Douane avec regroupement selon le type de service, idem avec la gendarmerie et la police.
Ensuite, un autre avec les infractions selon les catégories.
Un autre selon les départements et le reste.
Un autre avec les provinces et le reste.

Je ne sais pas si un TCD permet de le faire et surtout si cela s'actualisera automatiquement.
Si cela doit se faire avec Power Query (dont je ne maîtrise absolument pas), je veux bien apprendre pour assimiler les rouages de "logiciel" très puissant !, mais je préférai éviter.
Une explication (détaillée si possible), je suis plus un "novice" qu'un "NOOBS" pour pouvoir ajouter d'autres informations dans la liste pour effectuer des regroupements sur le même principe.

Mon document EXCEL peut être utilisé sur une version 2019.

 

[Shimano]

Bonsoir à toutes & à tous, bonsoir @Shimano
Me voilà de nouveau avec mon PC. J'ai essayé la formule en post#7 avec Excel2007, mais toutes mes tentatives pour la faire fonctionner ont échoué (en une seule formule une partie matricielle est ignorée).

Bref du coup je suis passé par une autre voie, plus simple ma foi. en mettant les formules utilisées pour la douane, la gendarmerie, la police et les infractions dans des noms pour éviter la validation matricielle (les formules dans les noms sont traitées comme des formules matricielles) comme cela plus de CTRL MAJ ENTREE.
Attention dans le fichier exemple les lignes 707/2024, 1164/2024 et 1467/2024 comportent un service dans les colonnes Gendarmerie et Police.
Regarde la pièce jointe 1189989

Voilà les 2 noms utilisés
Pour la douane, Gendarmerie et Police
(Administration en A$1, types de service en lignes à partir de A2, localisation lors de la définition du nom en B2)
Nom : Cpte_Sces
Définition :

=SOMME((tb_export[Administration]=A$1)*(INDIRECT("tb_export["&A$1&"]")=TRANSPOSE(SI(tb_Sces[Type de Service]=Resultats!A2;tb_Sces[Nom du Service];"#N/A"))))

Explications :

• TRANSPOSE(SI(tb_Sces[Type de Service]=Resultats!A2;tb_Sces[Nom du Service];"#N/A"))
  • tb_Sces[Type de Service]=Resultats!A2 compare chaque lignes des Types de services avec la valeur contenu en A2 ce qui renvoie un tableau de VRAI (il y a égalité) et FAUX il n'y a pas égalité).
  • SI(tb_Sces[Type de Service]=Resultats!A2;tb_Sces[Nom du Service];"#N/A") permet de renvoyer lorsque l'on a VRAI le Nom du Service, et lorsque l'on a FAUX le texte "#N/A"
  • TRANSPOSE(SI(tb_Sces[Type de Service]=Resultats!A2;tb_Sces[Nom du Service])) transforme le tableau vertical renvoyé par SI(...) en un tableau horizontal
• (INDIRECT("tb_export["&A$1&"]")=TRANSPOSE(SI(...)))
  compare chaque ligne de la colonne INDIRECT("tb_export["&A$1&"]") avec chaque colonne du tableau renvoyé par TRANSPOSE(SI(...))
  (ici INDIRECT("tb_export["&A$1&"]") = tb_export[DOUANE])
  On obtient un tableau de VRAI/FAUX d'autant de lignes qu'il y en a dans INDIRECT("tb_export["&A$1&"]") et autant de colonnes qu'il y en a dans TRANSPOSE(...)
• (tb_export[Administration]=A$1)
  renvoie un tableau avec pour chaque ligne de tb_export[Administration] , VRAI si la ligne vaut A$1 et FAUX sinon (ici A$1 = "DOUANE").
• En faisant le produit de ces deux derniers tableaux les VRAI sont convertis en 1 et les FAUX en 0.
  Faire la somme du tableau résultant renvoie le nombre d'enregistrements qui correspondent aux 2 Critères (Administration = A$1 (ici "DOUANE" et Type de Service = A2)

Il suffit d'écrire

• En A1 "DOUANE" et en B2 jusqu'en B5 =Cpte_Sces pour la Douane
• En D1 "GENDARMERIE" et en E2 jusqu'en E5 =Cpte_Sces pour la Gendarmerie
• En G1 "POLICE" et en H2 jusqu'en H5 =Cpte_Sces pour la Police

Pour les Infractions, même principe en plus simple
(Infractions en lignes à partir de J2, localisation lors de la définition du nom en K2)
Nom : Cpte_MOTIF_Infractions
Définition :

SOMME(1*(tb_export[Motif]=TRANSPOSE(SI(tb_Motifs[Catégories]=Resultats!C25;tb_Motifs[Infractions];"#N/A"))))

Explications

• (tb_export[Motif]=TRANSPOSE(SI(tb_Motifs[Catégories]=Resultats!C25;tb_Motifs[Infractions];"#N/A")))
  même explication que pour le nom Cpte_Sces
• 1* est là pour transformer les VRAI/FAUX en 1/0

Voilà voir le fichier joint,
A bientôt

Re,
J'ai ajouté quelques données, mais le résultat reste à "0".
J'avoue être perdu !
Une aide salvatrice sera TOP.
Merci de ton retour.

 

[AtTheOne]

Re bonjour @Shimano
En fait avec ces nouvelles données tu ne vas pas chercher les informations dans le même type de colonnes :
L'administration (DOUANE, GENDARMERIE, POLICE) est dans la colonne Administration et les services sont dans des colonnes reprenant le nom de cette administration (Police, Douane, Gendarmerie) le nom contenant la formule est adapté à cette configuration.

Maintenant tu veux aussi regrouper d'autres administrations en ne cherchant que dans la colonne "Administration", il faut une autre formule qui tient compte de cette nouvelle configuration.

D'où un nouveau nom (toujours pour t'affranchir du CTRL MAJ ENTREE) : Cpte_Autres_Administrations
(Défini avec cellule active B8)

=SOMME(1*(tb_Export[Administration]=TRANSPOSE(SI((tb_Sces[Service]=Resultats!A$7)*(tb_Sces[Type de Service]=Resultats!A8);tb_Sces[Nom du Service];"#N/A"))))

Voir la liste des noms ci-dessous

J'exploite le nouvel export de la feuille "New Export" (j'ai renommé les tableaux tb_Export en tb_Export_Old et Tableau1 en tb_Export)
J'ai modifié les nouveaux libellés de tb_Sces : Dans Service "Autres Administrations", dans type de services les libellés correspondant à ton tableaux feuille "Resultats', Nom du service inchangé.

Si tu dois récupérer de nouveau export, vide ton tableau tb_Export (supprimer lignes du tableau, les données pas les en-têtes) et colle les données de ton nouvel export (collage spécial valeurs) sous la ligne d'en-têtes, le tableau s'ajustera à ces nouvelles données.

Essaie de comprendre la mécanique des formules utilisées, je ne peux guère plus détailler que dans le post#13
Bon courage et à bientôt.

 

[David Aubert]

Bonjour à tous,
Ces fichiers ne me semblent pas suffisamment anonymisés et ils contiennent des données sensibles permettant de faire des statistiques qui pourraient être gênantes.
Ne souhaitant pas prendre de risques, je vais supprimer les pièces jointes publiées dans le fil de discussion.
Merci pour votre compréhension.
Bonne soirée
David

 

[Shimano]

Bonjour à tous,
Ces fichiers ne me semblent pas suffisamment anonymisés et ils contiennent des données sensibles permettant de faire des statistiques qui pourraient être gênantes.
Ne souhaitant pas prendre de risques, je vais supprimer les pièces jointes publiées dans le fil de discussion.
Merci pour votre compréhension.
Bonne soirée
David

Bonsoir David,
Les informations contenues sont des données que l'on trouve sur le Net. Certes, elles sont précises mais non confidentielles. Ensuite, je comprends le principe. Bonne soirée.