XL 2016 Créer une matrice de possibilité

[Tom33700]

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!

 

[mromain]

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.

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+

 

[Tom33700]

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.

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+

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!

 

[mromain]

Bonjour Tom33700, le forum,

Comment adapter la partie Source que je ne comprends absolument pas?

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 :

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

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+

 

[Tom33700]

Bonjour Tom33700, le forum,


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 :

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

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+

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!

 

[Tom33700]

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...
Regarde la pièce jointe 1192248

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


Quand je vais dans modifier la requete, j'ai pourtant bien la matrice :
Regarde la pièce jointe 1192246
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!

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?

 

[Cousinhub]

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

 

[Tom33700]

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?

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

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

 

[Cousinhub]

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

Re-,
Scuse, mon message était mal rédigé (je m'excusais de squatter ton fil) mais je posais la question à mromain
Désolé encore

 

[Tom33700]

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

 

[Cousinhub]

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

 

[Tom33700]

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"
Regarde la pièce jointe 1192251

L'interface (appelé Éditeur Power Query) va s'ouvrir
Regarde la pièce jointe 1192252

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

Regarde la pièce jointe 1192253

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

Regarde la pièce jointe 1192254
Et là, tu cliques sur "OK", puis "Fermer et charger"
Reviens si ce n'est pas clair

Super merci!

Peux-tu juste me dire à quoi correspond "Combinaisons" (derniere ligne du code) stp?

 

[Cousinhub]

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 :

let
    Source = // la source des données à traiter
    // le code d'avant
    Combinaisons = // tout le code permettant d'élaborer les permutations
in
    Combinaisons

 

[mromain]

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

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 :

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?

Je fais ça (uniquement pour les exemples, afin de fournir un jeu de données) avec cette fonction :

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+

 

[Cousinhub]

Re-,
Merci beaucoup Romain pour ce partage.
Hop, dans la besace
Bonne soirée