Discussion :

[Miraie]

Bonjour,

J'ai un tableau avec 17 colonnes, qui possède beaucoup de cellules vide. Ce tableau peut avoir un nombre varié de lignes entre 5 à 60 et chaque ligne n'a qu'une seule cellule avec une valeur. J'aimerais trouver une formule afin de recopier toutes les colonnes en une seule, une à la suite de l'autre et ce, sans les cellules vides. Je vous laisse un exemple du tableau que j'ai avec la colonne "Total" qui a le résultat escompté.



Auriez-vous une idée de la formule que je pourrais utiliser?

Merci d'avance pour votre aide.

[78chris]

Bonjour

Faisable par PowerQuery intégré à ta version Excel

Avec le tableau mis sous forme de Tableau nommé Données

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
let
    Source = Excel.CurrentWorkbook(){[Name="Donnees"]}[Content],
    #"Index ajouté" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Index ajouté", {"Index"}, "AJ", "Total"),
    #"Lignes triées" = Table.Sort(#"Tableau croisé dynamique des colonnes supprimé",{{"AJ", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes triées",{"Index", "AJ"})
in
    #"Colonnes supprimées"

Ceci fonctionnera quel que soit le nombre de lignes et de colonnes et le nom de celles-ci

Actualiser par clic droit dans le tableau de résultat

[Miraie]

Bonjour et merci,

Je n'ai jamais travaillé avec PowerQuery. J'ai fait quelques tests avec votre fichier et tout semble bien fonctionner. J'ai omis de mentionner que toutes les cellules de mon tableau ont une formule. Est-ce que cela peut créer des conflits? J'aimerais aussi savoir s'il y a possibilité de faire que la colonne "total" sois mise à jour automatiquement sans avoir à faire d'Actualiser?

Merci encore pour votre aide.

[78chris]

Bonjour

Non la formule ne gêne en rien

Il faut actualiser soit manuellement soit par une ligne de VBA déclencher à l'ajout de ligne dans le tableau source

[Miraie]

Bonjour,

J'assemble mon ficher avec le code PowerQwery, il me sort les 119 cellules de mon tableau en une seule colonne. Même celle qui ont un résultat vide "=SIERREUR(...;"")". l'erreur semble venir de l'étape "Tableau croisé dynamique des colonnes supprimées" qui ne semble pas supprimé les cellules. Aurriez-vous une idée de mon erreur ou, est-ce qu'il y a une formule afin de supprimer toutes les lignes ayant une formule avec un résultat "" que je devrais ajouter?

Merci encore pour votre aide.

[78chris]

Bonjour

POwerQuery différencie chaîne vide et cellule vide

Après l'étape de décroisement que tu cites, filtrer Total pour enlever Vide

[Miraie]

Bonjour,

Pourtant, il me garde toutes les cellules vides. Je vais essayer d'enlever mes SIERREUR pour voir si powerquery enlèvera les cellules avec erreurs. Sinon j'ai pensé à avoir des résultats donnant "0" et trouver une formule pour retirer les 0.

Merci encore

[78chris]

RE

J'ai testé cela fonctionne donc ton fichier ne doit pas être comme prévu...

[Miraie]

Bonjour,

Je suis finalement arrivé à mes fins. J'ai dû ajouter une ligne au code suggéré par 78chris qui m'a été de grande aide. Pour les intéresser, voici le code PowerQuery final.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
let
    Source = Excel.CurrentWorkbook(){[Name="Donnees"]}[Content],
    #"Index ajouté" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Index ajouté", {"Index"}, "AJ", "Total"),
    #"Lignes triées" = Table.Sort(#"Tableau croisé dynamique des colonnes supprimé",{{"AJ", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes triées",{"Index", "AJ"}),
    #"Lignes vides supprimées" = Table.SelectRows(#"Colonnes supprimées", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Lignes vides supprimées"

Merci 78chris pour me permettre d'ouvrir mes horizons, grâce à toi, j'ai plein de nouvelles possibilités.

[78chris]

RE

Il vaut mieux supprimer les vides avant le tri donc après le décroisement comme je l'avais mentionné

[mromain]

Bonjour Miraie, Chris, le forum,

Juste pour le fun (vu que Chris a déjà résolu le problème), une autre approche, toujours en PowerQuery :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
let
    Source = Excel.CurrentWorkbook(){[Name="Donnees"]}[Content],
    listColumnNames = Table.ColumnNames(Source),
    listColumnDatas = List.Transform(listColumnNames, each Table.Column(Source, _)),
    listConcatData = List.Combine(listColumnDatas),
    listDataRemoveNulls = List.RemoveNulls(listConcatData),
    tableRes = Table.FromList(listDataRemoveNulls, Splitter.SplitByNothing(), {"Total"})
    // Result = Table.FromList(List.RemoveNulls(List.Combine(List.Transform(Table.ColumnNames(Source), each Table.Column(Source, _)))), Splitter.SplitByNothing(), {"Total"})
in
    tableRes

A+

[78chris]

Bonjour

Reste à filtrer les chaînes vides mais approche intéressante

Cela donnerait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
let
    Source = Excel.CurrentWorkbook(){[Name="Donnees"]}[Content],
    listColumnNames = Table.ColumnNames(Source),
    listColumnDatas = List.Transform(listColumnNames, each Table.Column(Source, _)),
    listConcatData = List.Combine(listColumnDatas),
    listDataRemoveNulls_vides = List.RemoveItems(listConcatData, {null, ""})
in
    listDataRemoveNulls_vides

[Raccourcix]

Bonjour

Je vous propose de dépivoter TOUTES les colonnes

Pour ce faire, vous en choisissez une, vous appliquez le "Dépivot" puis à la main vous la supprimez dans le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
let
    Source = Excel.CurrentWorkbook(){[Name="Donnees"]}[Content],
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(Source, {}, "Attribut", "Valeur"),
    #"Lignes triées" = Table.Sort(#"Supprimer le tableau croisé dynamique des autres colonnes",{{"Attribut", Order.Ascending}}),
    Valeur = #"Lignes triées"[Valeur]
in
    Valeur

Stéphane

[Raccourcix]

Ma réponse précédente ne doit pas être satisfaisante dans tous les cas (le tri des colonnes Aj1, Aj2...)

j'imagine une autre possibilité dans Power Query

Avec tous les données en type "text", ajouter une colonne personnalisée "Valeur" Text.Combine(Record.FieldValues(_)) qui récupère la valeur du seul champ rempli de chaque ligne
puis List.PositionOf(Record.FieldValues(_),[Valeur])) pour obtenir le n° de la colonne associée
il ne reste plus qu'à trier par cette position.

Stéphane