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.

 

[sylvanu]

Bonjour Shimano,
Pour faire ce genre de synthèse, il faut que dans la base on trouve exactement la chaine recherchée, et savoir où chercher.
Par ex pour la douane "Service territoriaux" et "SNDJ" sont des chaines absentes. Est ce normal ou alors la définition est différente ?
j'ai trouvé des "trucs" correspondant pour Gie, Police, Dpts et Provinces (voir PJ), avec :

=NB.SI.ENS(Tableau3[Administration];"GENDARMERIE";Tableau3[Gendarmerie];D2)

Il faut faire de même avec Douane et Infractions.

Fonction NB.SI.ENS - Support Microsoft

Comment utiliser la fonction COUNTIFS dans Excel pour appliquer des critères à des cellules, sur plusieurs plages, et compter le nombre de fois où tous les critères sont remplis.

support.microsoft.com

 

[Shimano]

Bonjour Shimano,
Pour faire ce genre de synthèse, il faut que dans la base on trouve exactement la chaine recherchée, et savoir où chercher.
Par ex pour la douane "Service territoriaux" et "SNDJ" sont des chaines absentes. Est ce normal ou alors la définition est différente ?
j'ai trouvé des "trucs" correspondant pour Gie, Police, Dpts et Provinces (voir PJ), avec :

=NB.SI.ENS(Tableau3[Administration];"GENDARMERIE";Tableau3[Gendarmerie];D2)

Il faut faire de même avec Douane et Infractions.

Fonction NB.SI.ENS - Support Microsoft

Comment utiliser la fonction COUNTIFS dans Excel pour appliquer des critères à des cellules, sur plusieurs plages, et compter le nombre de fois où tous les critères sont remplis.

support.microsoft.com

Bonjour Sylvanu,
Merci de ton retour.
Par contre, je ne comprends pas la formule ?
A quoi correspond "Tableau3" ? C'est un nommage de cellules ?
Également, [administration] fait référence à quoi ?
SNDJ correspond à SEJF (Nouvelle appellation)
Services Territoriaux regroupent "Brigade douane (BSE-BSI), CLI-CODT et CRPC)
Merci de ton retour pour les explications.
Cordialement.

 

[sylvanu]

A quoi correspond "Tableau3" ? C'est un nommage de cellules ?
Également, [administration] fait référence à quoi ?

Ca correspond .... à votre fichier.
En feuille Export vous avez un tableau structuré, il est nommé automatiquement ainsi que les colonnes, lorsque vous le creez.
Si vous faites par ex dans une cellule : "=somme(" puis vous sélectionnez la colonne AL du début du tableau à la fin, alors XL va traduire par :

=SOMME(Tableau3[FVA])

Votre tableau s'appelle Tableau3, et la colonne AL s'appelle FVA.

 

[Shimano]

Ca correspond .... à votre fichier.
En feuille Export vous avez un tableau structuré, il est nommé automatiquement ainsi que les colonnes, lorsque vous le creez.
Si vous faites par ex dans une cellule : "=somme(" puis vous sélectionnez la colonne AL du début du tableau à la fin, alors XL va traduire par :

=SOMME(Tableau3[FVA])

Votre tableau s'appelle Tableau3, et la colonne AL s'appelle FVA.

D'accord, j'ai compris.
Mais la somme (par ex pour la gendarmerie - Brigade de recherche) doit être de 24+12) soit 36 car dans ce calcul, il y a 2 "services : BDRIJ et la Brigade de recherche). Et c'est là où je ne sais pas faire la formule.
Merci.

 

[sylvanu]

il y a 2 "services : BDRIJ et la Brigade de recherche

Vous n'avez pas le choix, il faut en faire la somme. Qui plus est, comme "BDRIJ" n'est pas en colonne D il faut le mettre en dur.
En E2 mettez cette formule, elle renvoie bien 36 :

=NB.SI.ENS(Tableau3[Administration];"GENDARMERIE";Tableau3[Gendarmerie];D2)+NB.SI.ENS(Tableau3[Administration];"GENDARMERIE";Tableau3[Gendarmerie];"BDRIJ")

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @Shimano, bonjour @sylvanu
J'ai une proposition qui fonctionne sur mon téléphone (Excel365 allégé) mais il faudra sans doute revalider les formules en formules matricielles (CTRL+MAJ+ENTREE)
ou mettre la partie matricielle (en violet) des formules dans des noms (ce que je ne peux pas faire maintenant)
La formule est pour la douane (A1 contenant "DOUANE" :

=SOMME(NB.SI.ENS(Tableau3[Administration];A$1;Tableau3[Douane];INDEX(Tableau2[Nom du Service];GRANDE.VALEUR((Tableau2[Service]="Douane")*(Tableau2[Type de Service]=A2)*(LIGNE(Tableau2[Service])-LIGNE(Tableau2[#En-têtes]));LIGNE(DECALER($A$1;0;0;NB.SI.ENS(Tableau2[Service];"Douane";Tableau2[Type de Service];A2);1))))))

La partie en violet génére un tableau contenant tous les services correspondant au service et au type de service.
J'ai transformé les plages de la feuille "Listes-Srv" en tableaux structurés
Bon courage

 

[Shimano]

Bonjour à toutes & à tous, bonjour @Shimano, bonjour @sylvanu
J'ai une proposition qui fonctionne sur mon téléphone (Excel365 allégé) mais il faudra sans doute revalider les formules en formules matricielles (CTRL+MAJ+ENTREE)
ou mettre la partie matricielle (en violet) des formules dans des noms (ce que je ne peux pas faire maintenant)
La formule est pour la douane (A1 contenant "DOUANE" :

=SOMME(NB.SI.ENS(Tableau3[Administration];A$1;Tableau3[Douane];INDEX(Tableau2[Nom du Service];GRANDE.VALEUR((Tableau2[Service]="Douane")*(Tableau2[Type de Service]=A2)*(LIGNE(Tableau2[Service])-LIGNE(Tableau2[#En-têtes]));LIGNE(DECALER($A$1;0;0;NB.SI.ENS(Tableau2[Service];"Douane";Tableau2[Type de Service];A2);1))))))

La partie en violet génére un tableau contenant tous les services correspondant au service et au type de service.
J'ai transformé les plages de la feuille "Listes-Srv" en tableaux structurés
Bon courage

Bonjour AtTheOne
Merci de cette avancée.
J'avoue être encore plus perdu qu'avant en voyant les formules utilisées.
Puis-je te solliciter pour finaliser mon tableau ?
Il y avait 3 erreurs dans mon export que j'ai corrigé, mais en revalidant les formules, rien ne se produit.
Est-ce du à la version d'EXCEL (je suis en 2019) ?
J'ai ajouté dans le second onglet quelques données supplémentaires et le tableau correspondant dans le 3ème onglet.
Je te joins le tableau.
Sautais-tu au vue de la complexité de la formule tenter de me l'expliquer pour que je puisse être autonome ?
Merci de ton retour.

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @Shimano
Comme je te l'ai dit, je n'ai pour l'instant que mon téléphone, impossible d'en faire plus avant dimanche soir.
Chez moi j'ai aussi une version 2007, je pourrai tester tout cela et même créer un nom pour éviter la validation matricielle ( par CTRL Maj Entre)
Il te faut patienter un peu, je te donnerai des explication sur la formule à ce moment là.
À bientôt

 

[Shimano]

Bonjour à toutes & à tous, bonjour @Shimano
Comme je te l'ai dit, je n'ai pour l'instant que mon téléphone, impossible d'en faire plus avant dimanche soir.
Chez moi j'ai aussi une version 2007, je pourrai tester tout cela et même créer un nom pour éviter la validation matricielle ( par CTRL Maj Entre)
Il te faut patienter un peu, je te donnerai des explication sur la formule à ce moment là.
À bientôt

Merci à toi.
Pas de pb.
Je patienterai.
C'est sympa de ta part de te pencher sur mon pb.
@ bientôt.
Cordialement.

 

[JHA]

Bonjour à tous,

Un essai avec des TCD

JHA

 

[Shimano]

Bonjour à tous,

Un essai avec des TCD

JHA

Bonjour JHA,
Merci de ton fichier.
Peut-on "regrouper" ou "trier" les résultats (dans un TCD ou autre) de l'export à partir de l'onglet "Listes-Srv" ?
Car la finalité est là !
Automatiser les calculs.
Power Query le fait (il me semble), mais, je ne saisi pas la logique. Et dans les TCD, je ne sais pas comment "regrouper" selon une liste prédéfinie.
Si tu as une idée ?

 

[AtTheOne]

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.

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

 

[Shimano]

Bonjour à tous,

Un essai avec des TCD

JHA

Bonjour JHA,
Merci de ton fichier.
Peut-on "regrouper" ou "trier" les résultats (dans un TCD ou autre) de l'export à partir de l'onglet "Listes-Srv" ?
Car la finalité est là !
Automatiser les calculs.
Power Query le fait (il me semble), mais, je ne saisi pas la logique. Et dans les TCD, je ne sais pas comment "regrouper" selon une liste prédéfine.
Si tu as une idée ?

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

Bonjour AtTheOne,
Quel boulot !
Maintenant, je dois appréhender la bête.
Selon toi, peut-on ajouter dans l'onglet "Liste" d'autres données et appliquer une formule équivalente ?
Puis-je abuser de ton aide et te solliciter ?
Merci de ton retour.

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @Shimano

Selon toi, peut-on ajouter dans l'onglet "Liste" d'autres données et appliquer une formule équivalente ?

Oui, si ces tableaux supplémentaires ont pour but de regrouper sous des libellés plus globaux des libellés détaillés, tu peux appliquer le même principe, je te conseille de faire de ces nouveaux tableaux des tableaux structurés comme les précédents.
À bientôt