Microsoft 365 Épurer les lignes vides d'une cellule puis fractionner la cellule en plusieurs lignes

de_hanstrapp · 21 Avril 2024

Bonjour le forum,

Dans le fichier exemple joint, je cherche à :
1/ supprimer toutes les lignes vides contenues dans les cellules de la colonne "C" pour arriver au résultat de l'onglet 2
2/ fractionner les lignes contenues dans les cellules pour arriver au résultat de l'onglet 3

J'ai cherché sur le forum mais dans les codes d'épurage trouvés, il ne prend jamais en compte la première ligne et pour spliter en plusieurs lignes on fait référence à un caractère alors que la il s'agit d'une tabulation.

Des idées ?

Merci par avance,

De_Hanstrapp

Staple1600 · 21 Avril 2024

Bonjour

Une solution avec PowerQuery

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"JOUR", Int64.Type}, {"DISCIPLINE", type text}, {"PROGRAMME", type text}}),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Type modifié", {"JOUR", "DISCIPLINE"}, "Attribut", "Valeur"),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Tableau croisé dynamique des colonnes supprimé", "Valeur", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Valeur.1", "Valeur.2", "Valeur.3", "Valeur.4", "Valeur.5", "Valeur.6", "Valeur.7", "Valeur.8", "Valeur.9", "Valeur.10", "Valeur.11", "Valeur.12"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"Valeur.1", type text}, {"Valeur.2", type text}, {"Valeur.3", type text}, {"Valeur.4", type text}, {"Valeur.5", type text}, {"Valeur.6", type text}, {"Valeur.7", type text}, {"Valeur.8", type text}, {"Valeur.9", type text}, {"Valeur.10", type text}, {"Valeur.11", type text}, {"Valeur.12", type text}}),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Type modifié1", {"JOUR", "DISCIPLINE", "Attribut"}, "Attribut.1", "Valeur"),
    #"Lignes filtrées" = Table.SelectRows(#"Supprimer le tableau croisé dynamique des autres colonnes", each ([Valeur] <> null and [Valeur] <> "") and ([Attribut.1] <> "Valeur.1")),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Attribut.1"})
in
    #"Colonnes supprimées"

Il doit y avoir un code M plus simple
Celui-ci a été obtenu avec l'Assistant PQ

Voici le résultat obtenu dans Excel

Efgé · 21 Avril 2024

Bonjour
Réssultat1:

VB:

=JOINDRE.TEXTE(CAR(10);1;FRACTIONNER.TEXTE(SUBSTITUE(C2;CAR(32);"");;CAR(10);1))

Résultat2

Code:

=FRACTIONNER.TEXTE(SUBSTITUE(C3;CAR(32);"");;CAR(10);1)

Cordialement

EDIT : Oups, pas vu : Bonjour @Staple1600

Staple1600 · 21 Avril 2024

Bonjour @Efgé

Pour coller plus au résultat à obtenir

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"JOUR", Int64.Type}, {"DISCIPLINE", type text}, {"PROGRAMME", type text}}),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Type modifié", {"JOUR", "DISCIPLINE"}, "Attribut", "Valeur"),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Tableau croisé dynamique des colonnes supprimé", "Valeur", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Valeur.1", "Valeur.2", "Valeur.3", "Valeur.4", "Valeur.5", "Valeur.6", "Valeur.7", "Valeur.8", "Valeur.9", "Valeur.10", "Valeur.11", "Valeur.12"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"Valeur.1", type text}, {"Valeur.2", type text}, {"Valeur.3", type text}, {"Valeur.4", type text}, {"Valeur.5", type text}, {"Valeur.6", type text}, {"Valeur.7", type text}, {"Valeur.8", type text}, {"Valeur.9", type text}, {"Valeur.10", type text}, {"Valeur.11", type text}, {"Valeur.12", type text}}),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Type modifié1", {"JOUR", "DISCIPLINE", "Attribut"}, "Attribut.1", "Valeur"),
    #"Lignes filtrées" = Table.SelectRows(#"Supprimer le tableau croisé dynamique des autres colonnes", each ([Valeur] <> "" and [Valeur] <> " ")),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Attribut", "Attribut.1"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"Valeur", "PROGRAMME"}})
in
    #"Colonnes renommées"

Staple1600 · 21 Avril 2024

Re

@Efgé
Avec ta seconde formule, j'ai #PROPAGATION!
(je pense que je dois pas faire ce qu'il faut)

Efgé · 21 Avril 2024

Re
@Staple1600
Il y a des lignes non vides sous la formule.
La formule ne peux pas s'étendre (se propager)
Cordialement

Staple1600 · 21 Avril 2024

Re

@Efgé
Tu les mets où tes formules ?
Sur la feuille INITIAL (en colonne D et E du Tableau1) ?

Efgé · 21 Avril 2024

Re
@Staple1600

Le corps du délit

de_hanstrapp · 21 Avril 2024

Staple1600 à dit:

Bonjour @Efgé

Pour coller plus au résultat à obtenir

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"JOUR", Int64.Type}, {"DISCIPLINE", type text}, {"PROGRAMME", type text}}),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Type modifié", {"JOUR", "DISCIPLINE"}, "Attribut", "Valeur"),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Tableau croisé dynamique des colonnes supprimé", "Valeur", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Valeur.1", "Valeur.2", "Valeur.3", "Valeur.4", "Valeur.5", "Valeur.6", "Valeur.7", "Valeur.8", "Valeur.9", "Valeur.10", "Valeur.11", "Valeur.12"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"Valeur.1", type text}, {"Valeur.2", type text}, {"Valeur.3", type text}, {"Valeur.4", type text}, {"Valeur.5", type text}, {"Valeur.6", type text}, {"Valeur.7", type text}, {"Valeur.8", type text}, {"Valeur.9", type text}, {"Valeur.10", type text}, {"Valeur.11", type text}, {"Valeur.12", type text}}),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Type modifié1", {"JOUR", "DISCIPLINE", "Attribut"}, "Attribut.1", "Valeur"),
    #"Lignes filtrées" = Table.SelectRows(#"Supprimer le tableau croisé dynamique des autres colonnes", each ([Valeur] <> "" and [Valeur] <> " ")),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Attribut", "Attribut.1"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"Valeur", "PROGRAMME"}})
in
    #"Colonnes renommées"

Merci Staple1600.
J'essaie de suivre la démarche via powerquery mais je bloque assez vite...
Comment procèdes-tu pour fractionner par délimiteur (tu es dans quelle colonne à ce moment la ? Valeur ?

de_hanstrapp · 21 Avril 2024

Efgé à dit:
Re
@Staple1600

Le corps du délit

Merci Efgé pour tes deux formules. Elles fonctionnent parfaitement mais cela nécessite que je calibre le fichier avec le nombre de lignes à intégrer car cela ne se fait pas automatiquement.

Efgé · 21 Avril 2024

Re
Pour le résultat 2 une autre proposition peut-être plus adéquate en repartant du résultat 1

VB:

=FRACTIONNER.TEXTE(JOINDRE.TEXTE(";";1;ASSEMB.V(E2:E3));;CAR(10);1)

Cordialement

mapomme · 21 Avril 2024

Bonjour @de_hanstrapp , @Staple1600 , @Efgé ,

Comme j'avais fait, je publie même en retard...

Les formule sont sur la ligne 2 de la feuille "Résultat" :

en A2 à recopier vers le bas
en B2 à recopier vers le bas
en C2, une unique formule

Staple1600 · 21 Avril 2024

Re, Bonjour @mapomme

@de_hanstrapp
J'ai fait un peu de ménage

PowerQuery:

let
  Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
   #"Fractionner la colonne par délimiteur" = Table.SplitColumn(Source, "PROGRAMME", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"PROGRAMME.1", "PROGRAMME.2", "PROGRAMME.3", "PROGRAMME.4", "PROGRAMME.5", "PROGRAMME.6", "PROGRAMME.7", "PROGRAMME.8", "PROGRAMME.9", "PROGRAMME.10", "PROGRAMME.11", "PROGRAMME.12"}),
    #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Fractionner la colonne par délimiteur", {"JOUR", "DISCIPLINE"}, "Attribut", "Valeur"),
    #"Lignes filtrées" = Table.SelectRows(#"Supprimer le tableau croisé dynamique des autres colonnes", each ([Valeur] <> "" and [Valeur] <> " ")),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Attribut"}),
    #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"Valeur", "PROGRAMME"}})
in
    #"Colonnes renommées"

Pour tester, voici comment tu peux procéder
Depuis ton classeur exemple
Obtenir les données/A partir d'autres sources/Requête vide
Ce qui te mène dans PowerQuery
Là sur l'onglet Accueil, choisir Editeur avancé
Puis copier/coller le code M de mon dernier message
Tu dois obtenir ceci

Ensuite il te reste plus qu'à cliquer sur Fermer et Charger

@Efgé
Merci pour le fichier exemple.
Ce m'a remis les yeux en face des trous

de_hanstrapp · 21 Avril 2024

Staple1600 à dit:
Re, Bonjour @mapomme

@de_hanstrapp
J'ai fait un peu de ménage

PowerQuery:

let Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content], #"Fractionner la colonne par délimiteur" = Table.SplitColumn(Source, "PROGRAMME", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"PROGRAMME.1", "PROGRAMME.2", "PROGRAMME.3", "PROGRAMME.4", "PROGRAMME.5", "PROGRAMME.6", "PROGRAMME.7", "PROGRAMME.8", "PROGRAMME.9", "PROGRAMME.10", "PROGRAMME.11", "PROGRAMME.12"}), #"Supprimer le tableau croisé dynamique des autres colonnes" = Table.UnpivotOtherColumns(#"Fractionner la colonne par délimiteur", {"JOUR", "DISCIPLINE"}, "Attribut", "Valeur"), #"Lignes filtrées" = Table.SelectRows(#"Supprimer le tableau croisé dynamique des autres colonnes", each ([Valeur] <> "" and [Valeur] <> " ")), #"Colonnes supprimées" = Table.RemoveColumns(#"Lignes filtrées",{"Attribut"}), #"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"Valeur", "PROGRAMME"}}) in #"Colonnes renommées"

Pour tester, voici comment tu peux procéder
Depuis ton classeur exemple
Obtenir les données/A partir d'autres sources/Requête vide
Ce qui te mène dans PowerQuery
Là sur l'onglet Accueil, choisir Editeur avancé
Puis copier/coller le code M de mon dernier message
Tu dois obtenir ceci
Regarde la pièce jointe 1195469
Ensuite il te reste plus qu'à cliquer sur Fermer et Charger

@Efgé
Merci pour le fichier exemple.
Ce m'a remis les yeux en face des trous

Merci Staple1600, je souhaitais simplement comprendre quelle manip tu as utilisée pour intégrer le séparateur #(If) car je n'y arrive pas.

de_hanstrapp · 21 Avril 2024

de_hanstrapp à dit:
Merci Staple1600, je souhaitais simplement comprendre quelle manip tu as utilisée pour intégrer le séparateur #(If) car je n'y arrive pas.

C'est bon j'ai trouvé.
Merci !

Microsoft 365 Épurer les lignes vides d'une cellule puis fractionner la cellule en plusieurs lignes

de_hanstrapp

XLDnaute Occasionnel

Pièces jointes

Staple1600

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

Pièces jointes

de_hanstrapp

XLDnaute Occasionnel

de_hanstrapp

XLDnaute Occasionnel

Efgé

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

de_hanstrapp

XLDnaute Occasionnel

de_hanstrapp

XLDnaute Occasionnel

Discussions similaires