Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2016 Créer une matrice de possibilité

T

Tom33700

XLDnaute Nouveau
Bonjour,

Je dispose d'un tableau de 10 colonnes.
Chaque colonnes contient les valeurs possibles que la colonne peut contenir. Cela me sert de liste pour la validation des données d'un autre onglet.
Selon les cas, j'ai entre 3 et 4 lignes par colonne.

Comment pourrais-je créer en automatique une matrice regroupant TOUTES les combinaisons possibles? Chaque lignes rerésentant donc une combinaison...

Par avance merci pour vos retours!
 

Pièces jointes

  • Matrice.xlsx
    12.5 KB · Affichages: 15
Trier par date Trier par votes

mromain

XLDnaute Barbatruc
Bonjour Tom33700, le forum,

Une solution avec PowerQuery normalement intégré à ta version.
L'étape Source est à adapter pour se baser sur ton tableau d'entrée.
PowerQuery: 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XYuxDcBACAN3oWajFwURDdIrXfYPGPJIKWzA+NYiJaYrlNNa2srcsQtHExG6KNtnOOfrXmcxFRdVmB3XJqfjDYO+n715LKFmY/yex0Re", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t, I = _t, J = _t]),
    Combinaisons = 
        let
            listColumns = List.Buffer(Table.ColumnNames(Source)),
            MergeValues = Table.FromColumns(List.Transform(listColumns, each {List.RemoveNulls(Table.Column(Source, _))}), listColumns)
        in
            List.Accumulate(List.Reverse(listColumns), MergeValues, (state, current) => Table.ExpandListColumn(state, current))
in
    Combinaisons

Cet exemple représente les données que tu as fournies et génère bien les 104 976 combinaisons possibles.

A+
 
Vote positif 0 Vote négatif
T

Tom33700

XLDnaute Nouveau

Merci beaucoup @mromain pour ce retour.
J'avoue ne pas connaitre du tout ce PowerQuery, qui semble puissant!
2 points qui me bloquent à ce stade :
- Comment adapter la partie Source que je ne comprends absolument pas?
- Existe-t-il une autre solution car le fichier est destiné à être utilisé sur plusieurs ordinateurs/ utilisateurs qui n'ont pas forcément cette même version d'Excel...

Par avance, merci pour l'aide!
 
Vote positif 0 Vote négatif

mromain

XLDnaute Barbatruc
Bonjour Tom33700, le forum,

Comment adapter la partie Source que je ne comprends absolument pas?
Cliquez pour agrandir...
Là ça dépend de comment est ton fichier.
Tu trouveras un exemple ci-joint où les données d'entrée sont dans un tableau structuré nommé Tab_ListeValeurs.
La requête PowerQuery devient alors :
PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="Tab_ListeValeurs"]}[Content],
    Combinaisons =
        let
            listColumns = List.Buffer(Table.ColumnNames(Source)),
            MergeValues = Table.FromColumns(List.Transform(listColumns, each {List.RemoveNulls(Table.Column(Source, _))}), listColumns)
        in
            List.Accumulate(List.Reverse(listColumns), MergeValues, (state, current) => Table.ExpandListColumn(state, current))
in
    Combinaisons

Existe-t-il une autre solution car le fichier est destiné à être utilisé sur plusieurs ordinateurs/ utilisateurs qui n'ont pas forcément cette même version d'Excel...
Cliquez pour agrandir...
C'est assurément faisable en VBA, mais plus galère et moins flexible je pense.
PowerQuery est dispo en add-on sur Excel 2010 et 2013 et intégré entièrement à Excel depuis la 2016.

Quelle(s) version(s) possèdent tes utilisateurs ?

A+
 

Pièces jointes

  • Exemple.xlsx
    19 KB · Affichages: 6
Vote positif 0 Vote négatif
T

Tom33700

XLDnaute Nouveau

Merci encore pour ton retour!

J'ai testé ton fichier, mais quand je fais actualiser tout, je me retrouve avec un tableau à une colonnes, des milliers de lignes contenant les valeurs a,aa ou aaa...


Et parfois j'ai un message d'erreur inattendue...


Quand je vais dans modifier la requete, j'ai pourtant bien la matrice :

Pourrais-tu me dire comment tu fais pour créer cette requête stp?
Je fouille mais je ne sais pas trop par où commencer...

Encore merci!
 
Vote positif 0 Vote négatif
T

Tom33700

XLDnaute Nouveau
Bon, j'ai réussi à faire marcher ton fichier en passant par la mise à jour de la requête... C'est vraiment top merci! Et hyper puissant!

Par contre, pourrais-me faire un pas à pas pour créer ce genre de choses stp?

Si j'ai bien compris, tu commences par créer un tableau avec la liste des valeurs possible? Mais ensuite?
 
Vote positif 0 Vote négatif

Cousinhub

XLDnaute Barbatruc
Inactif
Bonjour,
@mromain
Tom, désolé de m'incruster
C'est bizarre, avec ta première solution, environ 4 à 5 secondes (la première fois, puis 1 seconde...)
Avec la 2ème, j'en suis à plus de 70 secondes
Comment fais-tu pour charger en Json?
Merci
 
Vote positif 0 Vote négatif
T

Tom33700

XLDnaute Nouveau
Aors je t'avoue que je découvre... mais pour réussir à charger les données, je vais dans la requete et je charge d'ici. l'aperçu confirme ce que je veux. Je clique ensuite sur "Fermer et Charger"... Et c'est bon...
 
Vote positif 0 Vote négatif

Cousinhub

XLDnaute Barbatruc
Inactif
Tom33700 à dit:
Aors je t'avoue que je découvre... mais pour réussir à charger les données, je vais dans la requete et je charge d'ici. l'aperçu confirme ce que je veux. Je clique ensuite sur "Fermer et Charger"... Et c'est bon...
Cliquez pour agrandir...
Re-,
Scuse, mon message était mal rédigé (je m'excusais de squatter ton fil) mais je posais la question à mromain
Désolé encore
 
Vote positif 0 Vote négatif
T

Tom33700

XLDnaute Nouveau
Bon j'avance un peu... j'essaie de refaire en partant du début.
J'en suis ici :
Je crée mon tableau avec la liste des champs possibles.
Je crèe ensuite une requête à partir d'un tableau (dans un autre onglet).
Je colle le code mais j'ai un message d'erreur qui me dit qu'il ne connais pas "Combinaisons"...
A quoi correspond ce nom dans le requêtes? Les tableaux n'ont pas ce nom...
 
Vote positif 0 Vote négatif

Cousinhub

XLDnaute Barbatruc
Inactif
Re-,
Je vais essayer de t'expliquer...
Lorsque tu as sélectionné la cellule A1 (par exemple), tu cliques sur "Données/A partir de Tableau ou d'une plage"


L'interface (appelé Éditeur Power Query) va s'ouvrir


En jaune, tu peux voir le nom du Tableau Structuré
Et en vert, le bouton d'accès à l’Éditeur avancé
Lorsque tu cliques dessus, tu accèdes à l'interface de code



Et c'est ici qu'il faut remplacer tout ce code par le code fourni par romain (exemple avec la requête Json)


Et là, tu cliques sur "OK", puis "Fermer et charger"
Reviens si ce n'est pas clair
 
Vote positif 0 Vote négatif
T

Tom33700

XLDnaute Nouveau
Super merci!

Peux-tu juste me dire à quoi correspond "Combinaisons" (derniere ligne du code) stp?
 
Vote positif 0 Vote négatif

Cousinhub

XLDnaute Barbatruc
Inactif
Re-,
En VBA, on commence un code par Sub nom_de_la_sub(), et on la termine par End Sub()
Avec le langage M (de bien meilleures et plus amples explications sous ce lien), on commence par let
Puis, afin de terminer la requête, on appelle la dernière étape
Ici, la dernière est :
PowerQuery: 
let
    Source = // la source des données à traiter
    // le code d'avant
    Combinaisons = // tout le code permettant d'élaborer les permutations
in
    Combinaisons
 
Vote positif 0 Vote négatif

mromain

XLDnaute Barbatruc
Re-bonjour, bonjour @Cousinhub,

@Tom33700 :
Si tu commences avec PowerQuery, et en complément des infos données par @Cousinhub, tu peux aussi regarder les exemple de problématiques sur excel-formations.fr ainsi que les différentes leçons de Pierre Fauconnier.

@Cousinhub :
C'est bizarre, avec ta première solution, environ 4 à 5 secondes (la première fois, puis 1 seconde...)
Avec la 2ème, j'en suis à plus de 70 secondes
Cliquez pour agrandir...
Effectivement, il y a une bonne différence de performances... c'est pas forcément évident de comprendre comment fonctionne le moteur de PowerQuery...
On peut néanmoins retrouver un niveau de performances acceptable sur l'exemple du post #4 en ajoutant un buffer au niveau de la source :
PowerQuery: 
let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Tab_ListeValeurs"]}[Content]),
    Combinaisons = 
        let
            listColumns = List.Buffer(Table.ColumnNames(Source)),
            MergeValues = Table.FromColumns(List.Transform(listColumns, each {List.RemoveNulls(Table.Column(Source, _))}), listColumns)
        in
            List.Accumulate(List.Reverse(listColumns), MergeValues, (state, current) => Table.ExpandListColumn(state, current))
in
    Combinaisons

Comment fais-tu pour charger en Json?
Cliquez pour agrandir...
Je fais ça (uniquement pour les exemples, afin de fournir un jeu de données) avec cette fonction :
PowerQuery: 
let

    EncodeSourceTable = (table as table) as text =>
        let
            TableColumnsNames = Table.ColumnNames(table),
            NewColumnName = Text.Combine(TableColumnsNames) & "_",
            ToLists = Table.RenameColumns(Table.SelectColumns(Table.AddColumn(table, NewColumnName, each Record.ToList(_)), {NewColumnName}), {NewColumnName, "List"})[List],
            BinaryTxt = Binary.ToText(Binary.Compress(Json.FromValue(ToLists), Compression.Deflate), BinaryEncoding.Base64),
            ColumnsTxt = Text.Combine(List.Transform(TableColumnsNames, each _ & " = _t"), ", "),
            TxtRes = "Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(""" & BinaryTxt & """, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [" & ColumnsTxt & "])"
        in 
            TxtRes

in
    EncodeSourceTable

Elle prend en entrée la table de données à "convertir en json" et retourne la chaine de caractère attendue : Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(….

Il suffit alors de la copier pour la mettre dans une étape.

A+
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

T
  • Question
Formule Matricielle
Réponses
2
Affichages
244
tahiti66
T
A
  • Question
Microsoft 365 recherche multicritère de la valeur suivante
2
Réponses
29
Affichages
781
alexga78
A
  • Résolu(e)
XL 2013 (RESOLU) Automatisation Copie Lignes Feuilles selon leurs nom
Réponses
10
Affichages
547
chaelie2015
J
  • Résolu(e)
XL 2013 Créer une macro avec un fichier pour envoyer par mail
Réponses
7
Affichages
300
TooFatBoy
J
  • Question
Power BI Mesure dans visuel Matrice
Réponses
0
Affichages
234
jozerebel
J
T
  • Question
XL 2016 formulaire de pré-inventaire ( dont le TCD est déjà rempli )
Réponses
1
Affichages
146
laurent950
A
  • Résolu(e)
XL 2021 Calcule consommation de carburant
Réponses
3
Affichages
989
ayoub456
A
T
  • Résolu(e)
Microsoft 365 Créer un onglet pour chaque thème contenant les titres en doublons associés à ce thème
2
Réponses
25
Affichages
654
Toucan72
T
F
  • Question
XL 2016 Nom de la ligne avec la Valeur max
Réponses
4
Affichages
449
fanfanus
F
  • Résolu(e)
XL 2010 [Résolu] Modification macro
Réponses
8
Affichages
280
AtTheOne
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…