XL 2016 Regroupement de calcul par rubrique

[Shimano]

Bonjour à tous,

Je reposte car la solution n'est pas trouvée !
Merci à Sylvanu de son aide !

Si un autre membre peut aider ?
Merci.

Ma question peut paraitre bête et simple à résoudre, mais, je requiert votre aide pour résoudre mon problème.
En Pj, je vous livre un tableau où je dois selon des critères regrouper des valeurs pour les mettre dans un autre tableau.
Ex : PAF, SP, PJ et Autres sont diverses rubriques qu'il faut regrouper en 1 seul.
J'ai plusieurs onglets et je dois faire cela pour chacun.
Pour info, les données recueillies émanent d'un TCD.
Merci de votre aide.

 

[sylvanu]

Bonjour Shimano,
Votre page PN-GN-DF n'est pas très claire ... pour moi. On ne sait pas trop ce qu'il faut remplir.
Alors un essai pour les deux premiers tableaux, avec des formules du genre :

=NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;"*"&'PN-GN-DF'!B$3&"*")

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

A noter qu'en première colonne, plutôt que les mois en lettres, j'ai mis des dates avec un format personnalisé "mmmm", ce qui est directement exploitable dans les formules.

 

[sylvanu]

ATTENTION !!!
Il semblerait que votre fichier ne soit pas anonyme. En particulier la colonne R.
Si c'est le cas, détruisez votre PJ et anonymisée là.
J'ai repris ma PJ si dessus et ait effacé les dernières colonnes.

 

[Shimano]

ATTENTION !!!
Il semblerait que votre fichier ne soit pas anonyme. En particulier la colonne R.
Si c'est le cas, détruisez votre PJ et anonymisée là.
J'ai repris ma PJ si dessus et ait effacé les dernières colonnes.

Bonjour sylvanu,
Merci.
Il faut remplir le tableau de l'onglet "PN-GN-DF" avec les données des colonnes H1:I21 selon la répartition des colonnes en K1:L21.
Apparemment, tu as fais les calculs avec la base.
L'idéal aurait été de faire un TCD avec les données de la base et les mettre dans le tableau récapitulatif.
Est-ce jouable ?
Ensuite, je ferai de même pour les autres "administrations" : Gendarmerie et douane.

 

[sylvanu]

Re,
Je suis désolé, je ne comprends pas la demande, et de plus je suis vraiment nul en TCD.
En PJ un essai au hasard avec un TCD Ser PN vs Année. Je ne sais guère faire mieux.

 

[Shimano]

Re,
Je suis désolé, je ne comprends pas la demande, et de plus je suis vraiment nul en TCD.
En PJ un essai au hasard avec un TCD Ser PN vs Année. Je ne sais guère faire mieux.

Re,
Ce n'est pas grave, personne n'est parfait et tu me fais le plaisir d'essayer de m'aider.
On se concentre sur l'onglet PN-GN-DF
Tu en en H1:I17, le résultat d'un TCD.
A coté, tu as en K1:L22, la décomposition du type de service de la PN.
Ce que je veux, c'est mettre le résultat dans le tableau B4:E15
Donc, quelle formule dois-je mettre en B4 pour avoir la somme des services de la PAF ?
Ce chiffre est la somme des données du tableau H1:I17 selon la "condition" du tableau K1:L22.
Janvier :
PAF : 69 + 1 + 26 + 8 = 104
En C4, idem ? Quelle formule ?
SP : 38 + 214 + 3 + 11 = 266
En D4 ?
PJ : 70
En E4 ?
Autres : la différence du total (452-104-266-70) soit 12.

Est-ce plus clair pour toi ?
Merci.

 

[sylvanu]

Je sais faire ça mais avec des formules à rallonge du type :

=NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$6)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$8)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$11)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$15)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$17)

Je pense qu'il doit y avoir plus simple, mais je ne vois pas comment.

 

[Shimano]

Je sais faire ça mais avec des formules à rallonge du type :

=NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$6)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$8)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$11)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$15)+
NB.SI.ENS(Bases!$B:$B;">="&'PN-GN-DF'!$A4;Bases!$B:$B;"<"&MOIS.DECALER('PN-GN-DF'!$A4;1);Bases!$H:$H;$K$17)

Je pense qu'il doit y avoir plus simple, mais je ne vois pas comment.

Effectivement, il doit y avoir plus simple, mais qui saurait le faire ?
En tout cas, merci à toi pour le temps consacré à m'avoir aider.

 

[Cousinhub]

Bonsoir,
J'ai commencé à regarder ton fichier, mais pour moi, il y a un décalage de colonnes à partir de la ligne 1491.
Les services sont décalés d'une colonne sur la droite, donc aucun résultat pour 2023.
Pour faire ces calculs, je passe par Power Query (en natif dans ta version 2016).
Et j'en soirs 3 TCD (mais faux, parce qu'on ne peut plus faire de jointure entre les différents services à partir de l'année 2023..)
Si tu pouvais mettre un fichier plus cohérent...
@ te relire (demain...)

 

[Shimano]

Bonsoir,
J'ai commencé à regarder ton fichier, mais pour moi, il y a un décalage de colonnes à partir de la ligne 1491.
Les services sont décalés d'une colonne sur la droite, donc aucun résultat pour 2023.
Pour faire ces calculs, je passe par Power Query (en natif dans ta version 2016).
Et j'en soirs 3 TCD (mais faux, parce qu'on ne peut plus faire de jointure entre les différents services à partir de l'année 2023..)
Si tu pouvais mettre un fichier plus cohérent...
@ te relire (demain...)

Bonjour bhbh,
J'ai supprimé la colonne qui posait problème !
Je te le transmets. Ainsi tu pourras poursuivre tes investigations.
Je te remercie par avance.
Bonne réception.
Cordialement.

 

[Cousinhub]

Bonjour,
Dans le fichier joint, j'utilise Power Query (PQ), qui est en natif depuis la version 2016.
Ici, j'ai transformé ton tableau du 1 er onglet en Tableau Structuré (TS), nommé "T_Data".
Dans le 2ème onglet, j'ai également transformé les correspondances "Nom/Type" en 3 TS ("TS_PN", "TS_Gie" et "TS_Douane")
Dans la requête PQ, effectuée en interne, j'effectue des fusions (3) entre le T_Data et les différents TS de correspondances.
Et de chaque fusion, j'en tire un TCD (dans les 3 onglets différents).
Pour adapter à ton fichier, tu n'as qu'à copier tes données réelles dans le 1er onglet (vérifie que toutes les colonnes sont prises en compte, car il semble en manquer...)
Une fois tes données chargées, dans le ruban "Données", clique sur "Actualiser tout"..
Et c'est tout.. (si tout va bien...)
Bon courage

Edit, à noter qu'on pourrait se passer de 3 TCD, et tout regrouper en un seul tableau final..

 

[Shimano]

Bonjour,
Dans le fichier joint, j'utilise Power Query (PQ), qui est en natif depuis la version 2016.
Ici, j'ai transformé ton tableau du 1 er onglet en Tableau Structuré (TS), nommé "T_Data".
Dans le 2ème onglet, j'ai également transformé les correspondances "Nom/Type" en 3 TS ("TS_PN", "TS_Gie" et "TS_Douane")
Dans la requête PQ, effectuée en interne, j'effectue des fusions (3) entre le T_Data et les différents TS de correspondances.
Et de chaque fusion, j'en tire un TCD (dans les 3 onglets différents).
Pour adapter à ton fichier, tu n'as qu'à copier tes données réelles dans le 1er onglet (vérifie que toutes les colonnes sont prises en compte, car il semble en manquer...)
Une fois tes données chargées, dans le ruban "Données", clique sur "Actualiser tout"..
Et c'est tout.. (si tout va bien...)
Bon courage

Edit, à noter qu'on pourrait se passer de 3 TCD, et tout regrouper en un seul tableau final..

Bonjour bhbh,
Merci de ton retour.
J'ai voulu "simplement" supprimer 3 fiches (celles de février et mars 2022) et j'ai supprimé la colonne "en défaut" pour uniformiser.
J'actualise et malheureusement, voilà les messages d'erreur récoltés.
Une idée des messages ?
Merci de ton aide.

 

[Cousinhub]

Re-,
Dans ton dernier fichier, je n'ai aucun problème...
Tu es bien sous Excel 2016?
Lorsque tu vas dans le ruban "Données" :
- Si tu cliques sur "Requêtes et connexions", est-ce que tu as bien ces requêtes à droite?

- Si tu cliques sur "Obtenir des données", tu as bien "lancer l'éditeur Power Query"?

 

[Shimano]

Re-,
Dans ton dernier fichier, je n'ai aucun problème...
Tu es bien sous Excel 2016?
Lorsque tu vas dans le ruban "Données" :
- Si tu cliques sur "Requêtes et connexions", est-ce que tu as bien ces requêtes à droite?
Regarde la pièce jointe 1165957
- Si tu cliques sur "Obtenir des données", tu as bien "lancer l'éditeur Power Query"?

Regarde la pièce jointe 1165958

Je suis en Office 2016 Pro-Plus
Alors, pour obtenir le premier visuel, je dois cliquer sur l'onglet "Données" et "Afficher les requêtes".
J'obtiens ton premier visuel.
Pour le second : RIEN
Doit-on être sur INTERNET pour avoir PQ ?

Je suis perdu, j'ai pas le même bandeau que toi.

 

[Cousinhub]

RE-,
Pas d'inquiétudes..
Comme tu as le premier visuel, tu fais un clic droit sur la première requête ("T_Data"), puis tu cliques sur modifier.
Tu seras alors dans l'éditeur PQ.
Tu cliques sur "Fichier", Options et Paramètres, Options de requête (ou quelque chose de ressemblant)

Puis tu cherches dans les différentes rubriques ce paramètre "Niveaux de confidentialité", et tu valides l'option "Toujours ignorer les paramètres....."

Tu valides, tu cliques sur "Fermer et Charger", tu enregistres le fichier (important!!), puis re-essaie (cette manip' n'est qu'à faire une bonne fois pour toutes)
Bon courage