IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Mettre plusieurs colonnes une à la suite de l'autre sans les cellules vides [XL-2016]


Sujet :

Excel

  1. #1
    Membre du Club
    Homme Profil pro
    Superviseur en transport
    Inscrit en
    août 2019
    Messages
    99
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Superviseur en transport
    Secteur : Transports

    Informations forums :
    Inscription : août 2019
    Messages : 99
    Points : 69
    Points
    69
    Par défaut Mettre plusieurs colonnes une à la suite de l'autre sans les cellules vides
    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é.

    Nom : tableau.jpg
Affichages : 136
Taille : 11,4 Ko

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

    Merci d'avance pour votre aide.

  2. #2
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    7 562
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 7 562
    Points : 14 491
    Points
    14 491
    Par défaut
    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
    Fichiers attachés Fichiers attachés
    Chris
    PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  3. #3
    Membre du Club
    Homme Profil pro
    Superviseur en transport
    Inscrit en
    août 2019
    Messages
    99
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Superviseur en transport
    Secteur : Transports

    Informations forums :
    Inscription : août 2019
    Messages : 99
    Points : 69
    Points
    69
    Par défaut
    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.

  4. #4
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    7 562
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 7 562
    Points : 14 491
    Points
    14 491
    Par défaut
    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
    Chris
    PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  5. #5
    Membre du Club
    Homme Profil pro
    Superviseur en transport
    Inscrit en
    août 2019
    Messages
    99
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Superviseur en transport
    Secteur : Transports

    Informations forums :
    Inscription : août 2019
    Messages : 99
    Points : 69
    Points
    69
    Par défaut
    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.

  6. #6
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    7 562
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 7 562
    Points : 14 491
    Points
    14 491
    Par défaut
    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
    Chris
    PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  7. #7
    Membre du Club
    Homme Profil pro
    Superviseur en transport
    Inscrit en
    août 2019
    Messages
    99
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Superviseur en transport
    Secteur : Transports

    Informations forums :
    Inscription : août 2019
    Messages : 99
    Points : 69
    Points
    69
    Par défaut
    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

  8. #8
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    7 562
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 7 562
    Points : 14 491
    Points
    14 491
    Par défaut
    RE

    J'ai testé cela fonctionne donc ton fichier ne doit pas être comme prévu...
    Chris
    PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  9. #9
    Membre du Club
    Homme Profil pro
    Superviseur en transport
    Inscrit en
    août 2019
    Messages
    99
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : Superviseur en transport
    Secteur : Transports

    Informations forums :
    Inscription : août 2019
    Messages : 99
    Points : 69
    Points
    69
    Par défaut
    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.

  10. #10
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    7 562
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 7 562
    Points : 14 491
    Points
    14 491
    Par défaut
    RE

    Il vaut mieux supprimer les vides avant le tri donc après le décroisement comme je l'avais mentionné
    Chris
    PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  11. #11
    Membre actif
    Inscrit en
    avril 2008
    Messages
    129
    Détails du profil
    Informations personnelles :
    Localisation : Autre

    Informations forums :
    Inscription : avril 2008
    Messages : 129
    Points : 269
    Points
    269
    Par défaut
    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+

  12. #12
    Expert éminent sénior

    Profil pro
    Conseil, Formation, Développement - Indépendant
    Inscrit en
    février 2010
    Messages
    7 562
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Conseil, Formation, Développement - Indépendant

    Informations forums :
    Inscription : février 2010
    Messages : 7 562
    Points : 14 491
    Points
    14 491
    Par défaut
    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
    Chris
    PowerQuery existe depuis plus de 10 ans, est totalement intégré à Excel 2016 &+. Utilisez-le !

    Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
    Confucius

    ----------------------------------------------------------------------------------------------
    En cas de résolution, n'hésitez pas cliquer sur c'est toujours apprécié...

  13. #13
    Membre confirmé
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    157
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur

    Informations forums :
    Inscription : mars 2015
    Messages : 157
    Points : 456
    Points
    456
    Par défaut
    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

  14. #14
    Membre confirmé
    Homme Profil pro
    ingénieur
    Inscrit en
    mars 2015
    Messages
    157
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : ingénieur

    Informations forums :
    Inscription : mars 2015
    Messages : 157
    Points : 456
    Points
    456
    Par défaut
    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [XL-2013] Mettre plusieurs colonne sur plusieurs lignes
    Par runner77 dans le forum Excel
    Réponses: 8
    Dernier message: 08/04/2019, 08h02
  2. [XL-2007] Importer une plage sans les cellules vides
    Par JClmnop dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 12/02/2019, 18h13
  3. [XL-2010] Double de colonne sans les cellules vides
    Par jean780000 dans le forum Excel
    Réponses: 5
    Dernier message: 15/04/2015, 09h26
  4. Afficher une à la suite de l'autre
    Par punisher999 dans le forum Servlets/JSP
    Réponses: 1
    Dernier message: 19/12/2008, 08h46
  5. [VB.Net 2.0][ComboBox]Comment mettre plusieurs colonnes?
    Par bataj dans le forum Windows Forms
    Réponses: 2
    Dernier message: 29/03/2006, 12h37

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo