XL 2016 Concaténer dans une cellule selon critères TCD

[Michel_ja]

Bonjour à tous,
j'ai joint un fichier car j'ai un peu de mal à expliquer mon souci. J'ai un tableau croisé dynamique (Feuille TCD - Col A: Col F) donc avec une longueur et un contenu variable. Les deux critères majeurs sont la marque (col C) et l'énergie (Col E). Et j'ai une base de données Data, qui n'a rien à voir avec ce tableau dynamique, où on retrouve des colonnes avec la marque et l'énergie et une colonne MJ analysis où je dis si c'est disponible ou pas. J'aimerais savoir si on peut obtenir dans la cellule orange (Feuille TCD), la liste (concaténée) des modèles non disponibles en provenance de Data (Col E) qui correspondent aux marques et énergies sélectionnées dans la feuille TCD. On retrouve les marques, énergies et "Non disponible" en colonne D de la feuille Data. Et est ce que cette liste de la cellule orange se mettrait à jour à chaque changement du TCD.
Un peu comme si on concaténait dans une cellule, le résultat de plusieurs recherchev.

Les colonnes H à J où je concatène en J la marque, l'énergie et "Non disponible" correspondent à mes premières pistes.
Merci beaucoup pour votre aide.

 

[djidji59430]

Bonjour,

Un essai :

 

[Michel_ja]

Bonjour @djidji59430
Merci de t'être intéressé à mon problème. Je reviens vers toi, après avoir tenté de reproduire ta proposition sur mon fichier. Ton fichier remonte bien le nom des modèles contenant le texte "non disponible" des marques sélectionnées dans le TCD mais, il le fait pour l'ensemble des lignes de la feuille data et ce quelle que soit l'énergie choisie dans le TCD. Est-ce qu'on pourrait avoir le nom des modèles, répondant à la fois à la marque et à l'énergie ?
Par ailleurs, pour reproduire ta formule matricielle de la Cellule V9 dans les colonnes suivantes, il suffit de copier/coller la formule ? Sur mon essai, il me remonter sur la ligne d'une marque, des modèles d'une autre marque.
Merci Beaucoup.
Michel

 

[chris]

Bonjour à tous

Tu parles de TCD mais ton fichier ne contient plus de TCD juste une copie. Où est la source du TCD ?

Tu as 2016 dont nombre d'outils possibles dont PowerQuery qui croise en quelques clics des données mais sans fichier représentatif...

 

[Michel_ja]

Merci Chris. J'ajoute ici un extract de ma base de données et le lien avec le TCD. Je précise mon besoin.
J'aimerai que dans une cellule (peu importe où mais j'ai choisis la cellule i2 en orange (feuille TCD), figure la liste des "Marques modèles codes énergie" donc la liste de ma Col G de la feuille Data, qui appartiennent aux marques et énergies sélectionnées dans le TCD et contenant le texte "Non disponible" dans la Col F de ma feuille Data. Grâce à djidji j'ai la liste par marque mais le "tri" par énergie n'est pas pris en compte. Aussi, je ne pourrai pas avoir ces Col G et F (feuille Data) dans mon TCD.
Je sais que j'aurai à faire une Concaténation avec renvoie à la ligne dans ma cellule i2 une fois que j'aurai la fameuse liste. J'ai commencé avec la liste des cellules U30=U34 mais je ne sais pas trop comment faire pour passer aux modèles de la marque suivantes qui seront en Col V, etc.
Merci.
Michel

 

[djidji59430]

Re
Avec un tcd on filtre et c'est tout

Crdlmt
Bonjour chris, J'ai bien aimé le tuto sur powerquery. Je l'ai suivi et j'ai été surpris de ce qu'on pouvait faire.

 

[Michel_ja]

Mais djidji59430, je me trompe ou le TCD Choix ne sera pas dynamique! C'est à dire que lorsqu'on choisira une autre marque et une autre énergie dans le TCD de départ, le TCD Choix se mettra à jour également. Je me suis demandé si on pouvait avoir un même slicer pour deux tableaux croisés dynamique et mes recherches me disent qu'il faut que les deux tableaux soient sur la même feuille que les slicers, entre autres.
Dans mon message précédent, j'ai joint un nouveau fichier Test 2_V2 sans doute plus explicite que le premier.

 

[djidji59430]

Si tu transformes ta base de données en tableau, que tu fais ton tcd sur ce tableau, celui ci est dynamique. un ajout de données se répercute sur le tcd quand tu l'actualises .....

 

[chris]

RE

Et oui finalement on peut faire juste avec 2 TCD

Une variante avec une colonne masquée

On peut choisir d'autres colonnes...

 

[Michel_ja]

Je crois qu'on s'est un peu éloigné de mon besoin initial; comme je l'avais souligné dans mon premier message (au sens propre comme au sens figuré), j'ai deux bases de données distinctes. Le TCD initial (à gauche) est créé sur une autre base de données que Data; il est déterminant car c'est à partir de ce tableau (variable) que l'utilisateur m'indique les marques et énergies qui l'intéressent.
Si je crois comprendre mon besoin, et après quelques recherches sur Internet, j'aurai besoin d'un "filtre élaboré" qui se mettrait à jour automatiquement à chaque fois qu'un utilisateur change la marque et/ou l'énergie dans ce TCD. J'ai cherché par exemple "automated advanced filter" ou 'automatic advanced filter" et d'autres personnes ont rencontré le même besoin que moi. Mais, de ce que je lis ici et là, je crois comprendre que cela passera obligatoirement par une macro (sur événement).
Avez vous déjà rencontré ce besoin ?
Merci les gars.

 

[chris]

Re

Au #5 tu bases ton TCD sur Data alors que j'ai demandé "Où est la source du TCD ?"

Forcément cela embrouille...

Donc je repose la question où est la source du TCD ?

Avec PowerQuery on peut croiser 2 sources différentes(y compris autre fichier, base de données, fichier texte...) et obtenir le résultat mais pas utiliser un TCD comme source

 

[Michel_ja]

Bonjour Chris. Oui il y a eu confusion. Je joint le fichier avec 2 bases de données, la source du TCD et la Data où se trouve la Colonne avec "Non disponible". Pour des raisons de confidentialité, les programs sont aléatoires, donc on n'a pas les mêmes programs dans les deux bases; toutefois, dans le TCD l'utilisateur choisit une ou plusieurs marques et une ou plusieurs énergies. Si cela pose problème, dis moi le. je vais de ce pas regarder ce que c'est que PowerQuery.
Merci

 

[chris]

Re

Ci-joint fichier.

Un second TCD sur onglet TECH permet de récupérer choix des segments (connectés aux 2 TCD)

3 requêtes PowerQuery :
Une requête nommée MarqueEnergie extrait de la source du TCD (mise sous forme de tableau structuré nommé Source) la liste des couples marques énergies.
Cette requête est affichée dans un tableau structuré dans l'onglet TECH. Tableau nommé Filtre_TCD complété d'une colonne qui note si le couple est sélectionné dans les segments, donc dans le second TCD.

Une requête Filtre_TCD est créée à partir de ce tableau complété et seuls les couples non présents sont conservés

Les données de l'onglet Data sont mises sous forme de tableau structuré nommé Data.

Une requête nommée Data

• repère dans ce tableau les lignes où apparait "non disponible" et ne conserve que ces lignes
• la requête est croisée avec Filtre_TCD
• les colonnes inutiles sont supprimées
• les 3 colonnes résultantes sont triées
• le résultat est récupéré dans un tableau structuré nommé Indispo

Un bout de VBA a été ajouté pour actualiser la requête Data dès qu'un changement de filtre est fait sur les segments

 

[Michel_ja]

Bonjour Chris. Merci pour ton aide. Le résultat correspond bien à mon besoin. Etant donné que je découvre PowerQuery, j'ai eu du mal à reproduire ton fichier. Je me permet de te poser quelques questions sur la procédure.
J'ai compris qu'il fallait créer une Query "MarqueEnergie" avec les données Source du TCD. J'ai compris que le tableau dans la feuille TECH est construit à partir de cette Query, à laquelle tu as ajouté une colonne TCD (à Doite) avec une formule.
Sur cette même feuille TECH, il y a un TCD en cellule A3. Si je comprends bien, ce TCD a comme source, la même que le tableau d'à coté, et que le TCD d'origine, celui où se font les choix par l'utilisateur, qui figure dans la feuille "TCD et Critères". Est-ce correct ? Je ne sais pas comment ces 2 tableaux TCD sont reliés pour qu'un changement sur TCD et Critères modifie le TCD en feuille TECH.
Puis si je comprends bien, il y a une 2ème Query nommée "TCD Query", faite sur le tableau créé en cellule F3, de la feuille TECH. Suis-je bon ?
En enfin il y a une 3ème Query qui se fait sur la feuille Data. Ici je bloque, je ne sais pas un comment ne conserver que les lignes contenant le texte "Non disponible" et surtout car je ne sais pas comment croisée cette requête avec le TCD Query.
Merci encore.

 

[chris]

Bonjour

le TCD sur onglet TECH à la même source que celui de TCD et Critères à avoir le tableau de l'onglet Source TCD (sur mon exemple)
J'ai mis les données sources sous forme de tableau structuré (indispensable quand on fait des TCD et obligatoire pour POwerQuery

Les deux TCD sont basés sur le même tableau structuré. De ce fait il peuvent être synchronisés par segment : sur chaque segment, clic droit, connexions de TCD et cocher le 2ème TCD.
Ce second TCD permet de pister les choix de segments.

TCD Query s'appelle chez moi TCD_Filtre mai c'est ça : elle permet de récupérer la piste décrite ci-dessus

J'ai aussi mis les données de Data sous forme de tableau structuré puis, depuis une cellule de ce tableau, Données, A partir d'un tableau.
Cela crée la requête Data et ouvre PQ.

Si tu regardes mon exemple, quand tu sélectionne Data, tu as à droite la liste de toutes les actions faites et pour la plupart tu peux voir le détail en cliquant sur la roue dentée au bout de la ligne de l'action :

• les 2 premières sont automatiques
• il y a ensuite ajout d'une colonne personnalisée (onglet Ajouter une colonne) : titre Indispo,
  Formule =if Text.Contains(Text.Upper([MJ analysis]),"NON DISPO") =true then 1 else null
  
• filtre de cette colonne pour ne garder que 1
• fusion avec la requête TCD_Filtre (onglet Accueil) : sélection des colonnes Marque et Energie dans chacune des tables, dans le bon ordre en jointure interne
• suppression des colonnes inutiles
• tri

Reposte si tu as un souci...