Microsoft 365 Fusion de lignes avec condition pour certaines colonnes

Icedarts · 5 Avril 2023

Bonjour

J'ai un fichier avec plusieurs milliers de lignes de stats de joueurs.
J'ai quelques doublons, entre 70 et 100 ce qui représente du coup 140-200 a traiter.

Les colonnes A et B ne doivent pas changer et sont donc fusionnés
Les colonnes C D E F c'est la moyenne
Les colonnes G H I J K L M N c'est la somme
Et enfin la colonne O c'est a nouveau la moyenne

Fusion de ligne 2 par 2

Exemple en fichier joints ça sera peut-être plus parlant.

Merci d'avance pour votre aide

Cousinhub · 11 Avril 2023

Bonjour,
Voici la V3.
J'ai modifié le code pour simplifier les étapes.
Pour ajouter ta colonne "Stats14", je l'ai inclus dans le groupe Nom/Équipe pour effectuer les dépivotages et regroupements (calcul des sommes et des moyennes)
Le code tel que visible dans l’Éditeur Avancé quand tu es dans l'éditeur PQ, ruban "Accueil"

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    UnPivot1 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 13", "Stats 14"}, "Attribut", "Valeur"),
    UnPivot2 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 14"}, "Attribut", "Valeur"),
    GroupMoy =...

Cousinhub · 5 Avril 2023

Bonjour,
Avec Power Query, (en natif dans ta version), voici ce qu'on obtient en quelques clics...
Bonne apm

Icedarts · 5 Avril 2023

Merci pour la réponse rapide.
Alors je ne sais pas comment fonctionne le power querry mais la c'est une moyenne partout mais pour les colonnes G H I J K L M N c'est la somme qu'il faut

Cousinhub · 5 Avril 2023

Re-,
Scuse, mal lu..
Je regarde

Cousinhub · 5 Avril 2023

Re-,
La V2...

Icedarts · 5 Avril 2023

Merci après un rapide coup d'oeil ça semble fonctionner impec

Cousinhub · 6 Avril 2023

Bonjour,
Hier, j'ai fait un peu à l'arrache....
Avec ce code, un peu plus propre, ça devrait être plus véloce..
Bonne journée

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    UnPivot1 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 13"}, "Attribut", "Valeur"),
    UnPivot2 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12"}, "Attribut", "Valeur"),
    GroupMoy = Table.Group(UnPivot2, {"Nom", "Equipe", "Attribut"}, {{"Nombre", each List.Average([Valeur]), type number}}),
    GroupSomme = Table.Group(UnPivot1, {"Nom", "Equipe", "Attribut"}, {{"Nombre", each List.Sum([Valeur]), type number}}),
    Combine = Table.Combine({GroupSomme, GroupMoy}),
    Pivot = Table.Pivot(Combine, List.Distinct(Combine[Attribut]), "Attribut", "Nombre", List.Sum),
    Reorder = Table.ReorderColumns(Pivot,{"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 13"})
in
    Reorder

Icedarts · 6 Avril 2023

C'est gentil merci pour l'optimisation.
Tu m'aurais fait une macro je saurai ou modifier le code mais comme je le disais hier, le power querry je n'y connais absolument rien.
Je vois bien comment aller dans l'éditeur power querry

Onglet Données > Obtenir des données > lancer l'éditeur
Je vois bien une ligne de code comme une formule en haut de l'éditeur mais le code en lui-même j'en sais rien ^^

[EDIT]
Google est mon ami.
TROUVÉ ^^

Cousinhub · 6 Avril 2023

Re-,
Dans la requête, tu fais un clic droit, "Table/Modifier la requête"
Dans le ruban "Accueil", tu as un petit bouton "Éditeur avancé", tu cliques dessus
Tu verras alors le code, qui commence par "let"
Tu copies-colles ce dernier code à la place de l'ancien, et tu valides.
Puis "Fermer/Charger"

Hasco · 6 Avril 2023

Bonjour,

Une version avec formules Moyenne.Si.Ens et somme.Si.Ens (fusions.xlsx)

MOYENNE.SI.ENS(C$2:C$11;$A$2:$A$11;$A16;$B$2:$B$11;$B16)

Et une par power query (agregation tables) mais il faut mettre la main à la pâte.

let
Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
#"Colonnes Moyennes" = let cols = List.Range(Table.ColumnNames(Source),2,4) in List.Transform({0..List.Count(cols)-1}, each {cols{_},List.Average, "Moyenne " & Text.From(_+1)}),
#"Colonnes Sommes" = let cols = List.Range(Table.ColumnNames(Source),6) in List.Transform({0..List.Count(cols)-1}, each {cols{_},List.Sum, "Somme " & Text.From(_+1)}),
#"Source groupée" = Table.Group(Source, {"Nom", "Equipe"}, {{"Datas", each _, type table}}),
Agregation = Table.AggregateTableColumn(#"Source groupée","Datas",#"Colonnes Moyennes" & #"Colonnes Sommes")
in
Agregation

L'étape #"Colonnes Moyennes" et l'étape #"Colonnes Sommes" pourraient être fusionnées en une seule mais pour plus de clarté j' ai séparé.

cordialement

Icedarts · 11 Avril 2023

Merci @Hasco pour la réponse.
Avec la version "formule" il faudrait d'abord traiter les doublons pour pouvoir utiliser la formule ensuite non?
Tandis qu'avec le power querry ça fait tout d'un coup?

@bhbh et @Hasco je tente de rajouter une colonne P dans laquelle je dois faire une fusion de texte comme pour les colonnes A et B.
Pouvez-vous m'expliquer comment faire pour que je comprenne un peu comment ça fonctionne.

Merci d'avance

[EDIT]
Ça fonctionne mais je ne sais pas si la syntaxe est correcte

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    UnPivot1 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 13", "Stats 14"}, "Attribut", "Valeur"),
    UnPivot2 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 14"}, "Attribut", "Valeur"),
    GroupMoy = Table.Group(UnPivot2, {"Nom", "Equipe", "Stats 14", "Attribut"}, {{"Nombre", each List.Average([Valeur]), type number}}),
    GroupSomme = Table.Group(UnPivot1, {"Nom", "Equipe", "Stats 14", "Attribut"}, {{"Nombre", each List.Sum([Valeur]), type number}}),
    Combine = Table.Combine({GroupSomme, GroupMoy}),
    Pivot = Table.Pivot(Combine, List.Distinct(Combine[Attribut]), "Attribut", "Nombre", List.Sum),
    Reorder = Table.ReorderColumns(Pivot,{"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 13", "Stats 14"})
in
    Reorder

Cousinhub · 11 Avril 2023

Bonjour,
Voici la V3.
J'ai modifié le code pour simplifier les étapes.
Pour ajouter ta colonne "Stats14", je l'ai inclus dans le groupe Nom/Équipe pour effectuer les dépivotages et regroupements (calcul des sommes et des moyennes)
Le code tel que visible dans l’Éditeur Avancé quand tu es dans l'éditeur PQ, ruban "Accueil"

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    UnPivot1 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 13", "Stats 14"}, "Attribut", "Valeur"),
    UnPivot2 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 14"}, "Attribut", "Valeur"),
    GroupMoy = Table.Group(UnPivot2, {"Nom", "Equipe", "Stats 14", "Attribut"}, {{"Nombre", each List.Average([Valeur]), type number}}),
    GroupSomme = Table.Group(UnPivot1, {"Nom", "Equipe", "Stats 14", "Attribut"}, {{"Nombre", each List.Sum([Valeur]), type number}}),
    AjtReq = Table.Combine({GroupSomme, GroupMoy}),
    Pivot = Table.Pivot(AjtReq, List.Distinct(AjtReq[Attribut]), "Attribut", "Nombre", List.Sum),
    Reorder = Table.ReorderColumns(Pivot,{"Nom", "Equipe", "Stats 14", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 13"})
in
    Reorder

Bonne apm, et bonne semaine

Icedarts · 11 Avril 2023

Merci beaucoup c'est ce que j'avais finalement réussi a faire

Cousinhub · 11 Avril 2023

RE-,

Icedarts à dit:

[EDIT]
Ça fonctionne mais je ne sais pas si la syntaxe est correcte

PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    UnPivot1 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 13", "Stats 14"}, "Attribut", "Valeur"),
    UnPivot2 = Table.UnpivotOtherColumns(Source, {"Nom", "Equipe", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 14"}, "Attribut", "Valeur"),
    GroupMoy = Table.Group(UnPivot2, {"Nom", "Equipe", "Stats 14", "Attribut"}, {{"Nombre", each List.Average([Valeur]), type number}}),
    GroupSomme = Table.Group(UnPivot1, {"Nom", "Equipe", "Stats 14", "Attribut"}, {{"Nombre", each List.Sum([Valeur]), type number}}),
    Combine = Table.Combine({GroupSomme, GroupMoy}),
    Pivot = Table.Pivot(Combine, List.Distinct(Combine[Attribut]), "Attribut", "Nombre", List.Sum),
    Reorder = Table.ReorderColumns(Pivot,{"Nom", "Equipe", "Stats 1", "Stats 2", "Stats 3", "Stats 4", "Stats 5", "Stats 6", "Stats 7", "Stats 8", "Stats 9", "Stats 10", "Stats 11", "Stats 12", "Stats 13", "Stats 14"})
in
    Reorder

Heu, j'avais même pas vu que tu avais trouvé par toi-même... Félicitations
Nickel, je vois que tu as compris la requête, et ça fait toujours plaisir de voir quelqu'un s'investir dans une possibilité trop peu utilisée d'Excel. (et tellement puissante)

Hasco · 12 Avril 2023

Bonjour,

Icedarts à dit:
Avec la version "formule" il faudrait d'abord traiter les doublons pour pouvoir utiliser la formule ensuite non?

Avec office 365 vous pouvez utiliser en A16 :

=UNIQUE(A2:B11)

Qui vous donnera la liste des des Nom/Equipe uniques

Microsoft 365 Fusion de lignes avec condition pour certaines colonnes

Icedarts

XLDnaute Occasionnel

Pièces jointes

Cousinhub

Cousinhub

XLDnaute Barbatruc

Pièces jointes

Icedarts

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Pièces jointes

Icedarts

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Icedarts

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

Pièces jointes

Icedarts

XLDnaute Occasionnel

Pièces jointes

Cousinhub

XLDnaute Barbatruc

Pièces jointes

Icedarts

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Hasco

XLDnaute Barbatruc

Discussions similaires