Extraire et additionner des références

Le 02/09/2023 à 16:09

8fourniture-pour-3-ensembles.xlsx (9.84 Ko)

Bonjour à tous,

Je voudrais savoir s'il existe une formule, une macro ou n'importe quelle solution, me permettant d'extraire des références uniques et de les additionner.

Explications :

Pour la fabrication de 3 ensembles différents, je me retrouve avec 3 listes d'équipement distinctes, ayant certaines références en commun et d'autres qui leurs sont propres. Certaines références peuvent apparaitre plusieurs fois dans une même liste.

Chaque liste est composé comme suit :

Colonne A : référence / Colonne B : désignation / Colonne C : quantité

Ligne 3 à x : les différents éléments entrants dans la fabrication de l'assemblage.

Lorsque je rassemble ces 3 listes je me retrouve avec une feuille de 9 colonnes et x lignes.

Comment puis-je extraire chaque référence, sans doublons, et additionner les quantités présentes dans les colonnes C, F et I ?

Ci-joint un document permettant d'illustrer mon propos, évidement en réalité il y a bien plus de références, avec le résultat que je recherche.

Le 02/09/2023 à 16:54

Bonjour à tous !

Une proposition via Power Query (nativement intégré dans Excel depuis 2016) :

28varok-saurcroc-pq-fourniture-pour-3-ensembles-v1.xlsx (27.02 Ko)

Le 02/09/2023 à 18:01

Merci JFL c'est bien ca que je veux faire.

Par contre comment tu as fais ? Tu es passé par l'éditeur de requête ? Quelle est la formule ?

Lorsque je change une valeur du tableau tData ou que j'ajoute une référence, j'obtiens ce message après actualisation : [Expression.Error] 2 arguments ont été passés à une fonction qui en attendait 1. D'ailleurs même si je ne change absolument rien et que j'actualise j'obtiens ce même message.

Le 02/09/2023 à 18:51

Bonsoir à tous !

Comme indiqué dans mon message précédent, cette proposition est basée sur Power Query. Pour prendre connaissance des étapes appliquées, via le menu "Données / Obtenir des données / lancer l'éditeur Power Query".

Les étapes sont situées à droite de l'éditeur.

Concernant l'ajout (ou la modification d'une données), je ne reproduis pas votre souci :

Pour acquérir les bases, il existe une multitude de tutoriels sur le web. Ne surtout pas hésiter !

Le 02/09/2023 à 19:04

Bonsoir,

Juste pour une précision, quant à l'erreur rencontrée...

Peut-être un typage en trop, dans l'AddIndex....?

Bonne soirée

Le 02/09/2023 à 19:09

Re-,

Et comme je l'avais fait..

Une alternative, mais qui se limite à 3 ensembles dans mon fichier, avec un nombre indéterminé de lignes, bien sûr...

Le code :

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Ajout_Table = Table.AddColumn(Source, "Data", each Table.FromColumns({{[Référence],[Référence2],[Référence5]},{[Désignation],[Désignation3],[Désignation6]},{[Quantité],[Quantité4],[Quantité7]}},{"Référence","Désignation","Quantité"})),
    SupprCol = Table.SelectColumns(Ajout_Table,{"Data"}),
    Expand = Table.ExpandTableColumn(SupprCol, "Data", {"Référence", "Désignation", "Quantité"}, {"Référence", "Désignation", "Quantité"}),
    Filtr = Table.SelectRows(Expand, each ([Référence] <> null)),
    GroupBy = Table.Group(Filtr, {"Référence", "Désignation"}, {{"Quantité", each List.Sum([Quantité]), type number}})
in
    GroupBy

Bonne soirée

9pq-table-from-colonne.xlsx (19.14 Ko)

Le 02/09/2023 à 19:17

Bonsoir à tous de nouveau !

@cousinhub : Hello !

cousinhub a écrit :
Juste pour une précision, quant à l'erreur rencontrée...
Peut-être un typage en trop, dans l'AddIndex....?

Maudit 2016 ! Bien vu

Version 2 adaptée à Excel 2016 :

12varok-saurcroc-pq-fourniture-pour-3-ensembles-v2.xlsx (26.88 Ko)

Le 02/09/2023 à 19:20

Ok donc votre code est le suivant :

let
    Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"En-têtes promus", {}, "Attribut", "Valeur"),
    #"Index ajouté" = Table.AddIndexColumn(#"Tableau croisé dynamique des colonnes supprimé", "Index", 0, 1, Int64.Type),
    #"Colonne divisée par entier" = Table.TransformColumns(#"Index ajouté", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Texte extrait avant le délimiteur" = Table.TransformColumns(#"Colonne divisée par entier", {{"Attribut", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Colonne dynamique" = Table.Pivot(#"Texte extrait avant le délimiteur", List.Distinct(#"Texte extrait avant le délimiteur"[Attribut]), "Attribut", "Valeur"),
    #"Lignes groupées" = Table.Group(#"Colonne dynamique", {"Référence", "Désignation"}, {{"Quantité", each List.Sum([Quantité]), type number}}),
    #"Lignes triées" = Table.Sort(#"Lignes groupées",{{"Référence", Order.Ascending}})
in
    #"Lignes triées"

Quand je reprend les étapes appliquées, elles sont toutes en erreur sauf l'onglet source qui reprend bien le tableau data.

"Atteindre l'erreur" me ramène vers l'onglet En-têtes promus. Voici le message d'erreur en question :

Le 02/09/2023 à 19:21

Re-,

JFL

JFL a écrit :
Maudit 2016 !

Meuh non, de plus, en ce moment, c'est ma version de travail...
Mais effectivement, quand même de sérieuses différences avec les versions plus récentes...

Le 02/09/2023 à 19:25

Re-,

Varok_Saurcroc a écrit :

    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

Une nouvelle différence en vue?

En mettant simplement :

#"En-têtes promus" = Table.PromoteHeaders(Source),

Est-ce mieux?

Le 02/09/2023 à 19:26

Merci à vous tous mais j'ai toujours le même problème.

Ce message, lorsque je clique sur modifier dans l'onglet requête, est peut être la source du problème :

En plus du message d'erreur rapporté dans mon poste précédent, dans l'onglet texte extrait avant le délimiteur j'ai celui ci :

Erreur d'expression: Le nom « Text.BeforeDelimiter » n'a pas été reconnu. Veuillez vérifier qu'il est correctement orthographié.

Le 02/09/2023 à 19:29

Cousin hub j'ai apporté ta modification. "En tête promus"

Du mieux mais de nouvelles erreurs. Meme message quand j'actualise tout.

J'ai plus que les 4 dernières étapes en erreur.

Texte extrait avant le délimiteur:

Erreur d'expression: Le nom « Text.BeforeDelimiter » n'a pas été reconnu. Veuillez vérifier qu'il est correctement orthographié.

Colonne dynamique / lignes groupées / lignes triées

Expression.Error: L'importation Text.BeforeDelimiter ne correspond à aucune exportation. Avez-vous oublié une référence de module ?

Le 02/09/2023 à 19:30

Re-,

Il me semble que Chris en avait fait part également de cette option non prévue dans 2016... (le Fractionner n'était pas implémenté de la même manière...)

Pour le msg d'erreur "Avertissement de compatibilité" n'est pas bloquant en soi, même moi, avec 2021, j'ai ce message lorsque j'ouvre un fichier de JFL, par exemple, qui est en Office 365

Sinon, avec ma solution, est-ce que tu as des erreurs également?

Le 02/09/2023 à 19:50

Je ne sais pas si tu as vu mon dernier poste cousinhub, j'ai des erreurs mais moins d'étapes appliquées sont en erreurs.

Edit : @cousinhub j'ai téléchargé l'excel que tu avais joint plus haut. CA FONCTIONNE !! Merci à vous deux.

Le 02/09/2023 à 20:01

Bonsoir à tous de nouveau !

Code adapté.....(autre approche....)

let
    Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
    #"En-têtes promus" = Table.PromoteHeaders(Source),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"En-têtes promus", {}, "Attribut", "Valeur"),
    #"Index ajouté" = Table.AddIndexColumn(#"Tableau croisé dynamique des colonnes supprimé", "Index", 0, 1),
    #"Colonne divisée par entier" = Table.TransformColumns(#"Index ajouté", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Colonne divisée par entier", "Attribut", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribut.1", "Attribut.2"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Fractionner la colonne par délimiteur",{"Attribut.2"}),
    #"Colonne dynamique" = Table.Pivot(#"Colonnes supprimées", List.Distinct(#"Colonnes supprimées"[Attribut.1]), "Attribut.1", "Valeur"),
    #"Lignes groupées" = Table.Group(#"Colonne dynamique", {"Référence", "Désignation"}, {{"Quantité", each List.Sum([Quantité]), type number}}),
    #"Lignes triées" = Table.Sort(#"Lignes groupées",{{"Référence", Order.Ascending}})
in
    #"Lignes triées"

Le 02/09/2023 à 20:04

Bonsoir à tous de nouveau !

cousinhub a écrit :
Re-,
Varok_Saurcroc a écrit :
    #"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
Une nouvelle différence en vue?
En mettant simplement :
#"En-têtes promus" = Table.PromoteHeaders(Source),
Est-ce mieux?

Il faudra mettre à jour votre bréviaire des différences.... la liste s'allonge.....

Le 02/09/2023 à 21:13

Merci encore JFL, mais la formule de cousinhub est fonctionnelle :

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Ajout_Table = Table.AddColumn(Source, "Data", each Table.FromColumns({{[Référence],[Référence2],[Référence5]},{[Désignation],[Désignation3],[Désignation6]},{[Quantité],[Quantité4],[Quantité7]}},{"Référence","Désignation","Quantité"})),
    SupprCol = Table.SelectColumns(Ajout_Table,{"Data"}),
    Expand = Table.ExpandTableColumn(SupprCol, "Data", {"Référence", "Désignation", "Quantité"}, {"Référence", "Désignation", "Quantité"}),
    Filtr = Table.SelectRows(Expand, each ([Référence] <> null)),
    GroupBy = Table.Group(Filtr, {"Référence", "Désignation"}, {{"Quantité", each List.Sum([Quantité]), type number}})
in
    GroupBy

Le 02/09/2023 à 21:35

Bonsoir à tous de nouveau !

C'est une bonne chose pour vous....

Mais pour ma lanterne, pouvez-vous m'éclairer et préciser si ma nouvelle contribution est opérationnelle sur la version obsolète 2016 ?

Le 02/09/2023 à 21:47

Re-,

Et je plussoie à la requête de JFL, pour confirmer le bon fonctionnement de sa solution

Excel 2016 a un comportement "un peu" différent vs les autres versions

C'est pour cela que j'avais initié ce fil, afin que toute personne, et quelque soit la version utilisée, puisse profiter de ce magnifique outil qu'est Power Query.

Et tout apport de dysfonctionnement ne peut qu'être bénéficiaire à tout un chacun, aussi bien le demandeur, mais surtout le répondeur, afin d'ajuster la réponse..

Bonne soirée à tous

PS, JFL, mise à jour prévue, mais pas ce soir, I Think..... :)

Le 02/09/2023 à 21:58

Alors pour la v2, lorsque j'actualise tout (après une modif ou non) même problème que pour la v1.

Message d'erreur ci-dessous et pas de changement dans le tableau des résultats.

Pour ce qui est des étapes appliquées :

Source : pas de pb, j'ai bien le tableau tData qui apparait sur la gauche

En-têtes promus +Tableau croisé dynamique des colonnes supprimé + Index ajouté + Colonne divisé par entier + Colonne dynamique + Lignes groupées + Lignes triées :

Expression.Error: 2 arguments ont été passés à une fonction qui en attendait 1.
Détails :
    Pattern=
    Arguments=List

Texte extrait avant le délimiteur :

Erreur d'expression: Le nom « Text.BeforeDelimiter » n'a pas été reconnu. Veuillez vérifier qu'il est correctement orthographié.

Lorsque je clic sur "Atteindre l'erreur", ca me renvoie vers l'onglet En-têtes promus. Parce que le problème vient réellement de là ou parce que c'est l'onglet qui apparait en premier sur la totalité des onglets buggés ?

Extraire et additionner des références - Excel 2016