XL 2019 Regroupement données dans un tableau

Shimano

XLDnaute Occasionnel
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

XLDnaute Occasionnel
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 :
Enrichi (BBcode):
=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 :
Enrichi (BBcode):
Enrichi (BBcode):
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.
 
Dernière modification par un modérateur:

AtTheOne

XLDnaute Accro
Supporter XLD
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)
Enrichi (BBcode):
=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
1707149252477.png


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

XLDnaute Barbatruc
Administrateur
Modérateur
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

XLDnaute Occasionnel
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.
 

Discussions similaires

Réponses
9
Affichages
458
  • Question
XL 2019 jj
Réponses
11
Affichages
443

Membres actuellement en ligne

Aucun membre en ligne actuellement.

Statistiques des forums

Discussions
312 860
Messages
2 092 961
Membres
105 571
dernier inscrit
MAT30190