Microsoft 365 Séparer les données d'un fichier Source en fonction de la valeur dans une colonne

[Magnio]

Bonjour à tous,

J'ai tenté de faire ce projet mais je me heurte à une difficulté (ou plutôt un manque de connaissances de ma part ^^) qui requièrent votre aide.

Le fichier principal est Séparation Rapports, on clique sur le bouton Rouge et on va choisir le fichier REQUETE v1 d'où extraire les données.

Le but: en fonction de la valeur de la cellule en colonne H, trier les données et les incrémenter au fil de l'eau dans les onglets appropriés.
(Vous pouvez voir le résultat souhaité dans le fichier: Séparation Rapports Résultats Souhaités)

Je sais aller piocher la valeur d'une cellule en fonction d'une autre, mais je n'ai encore jamais expérimenter de sélectionner une ligne entière (du moins dans ce cas précis de la colonne B à S).

Si quelqu'un peut éclairer ma lanterne, cela me sera très utile et me fera progresser dans mes connaissances en VBA.
Merci. =)

 

[Magnio]

Pour ce qui est de la façon d'analyser les données, les collègues auront chacun leur façon de faire mais je prend cette solution tout prête, merci. 🙂

 

[job75]

Bonjour Magnio, le forum,

Encore une amélioration, j'espère la dernière, avec les fichiers .xlsx joints.

Dans ces fichiers j'ai enlevé les textes (Pâtisserie, Prépa) en C71 et C10.

Alors avec la dernière macro il se glisse une ligne vide dans les résultats, c'est à cause de l'expression .Offset(1).Copy dest

Pour y remédier je la remplace par :

Intersect(.Offset(1), .Cells).Copy dest

A+

 

[Magnio]

Merci pour cette dernière retouche.
Les fichiers que j'ai fourni étaient allégés en données sur le haut du documents, il manque d'autres informations dans des cellules et des boutons.

Je reste sur la précédente solution du coup. Encore merci. 🙂

 

[oguruma]

Bonsoir Le Forum,
une solution rapidos en powerquery
1/ Elle consiste à charger le fichier de données initiales.
2/ On procède au nettoyage pour obtenir un tableau structuré
3/ On éclate en deux requêtes

Ou on se passe de l'éclatement en deux requêtes et on génère les résultats par deux TCD avec le filtre sur Delta <=0.4

voici un début de solution, je te laisse créer la suite à partir du rapport réorganisé en tableau structuré. Tu as déjà une base d'évolution.

Rapport compilé en tableau structuré

éclatement <=0.4

éclatement >0.4

let
    Source = Excel.Workbook(File.Contents("C:\Users\oguru\Downloads\REQUETE v1.xlsx"), null, true),
    #"Rapport 1_Sheet" = Source{[Item="Rapport 1",Kind="Sheet"]}[Data],
    TB_SUPPR_NULL = Table.SelectRows(#"Rapport 1_Sheet", each ([Column1] <> null)),
    TB_PROMOTE_01 = Table.Skip(TB_SUPPR_NULL,1),
    TB_PROMOTE_02 = Table.PromoteHeaders(TB_PROMOTE_01, [PromoteAllScalars=true]),
    TB_SUPPR_CUVE = Table.SelectRows(TB_PROMOTE_02, each ([Cuve] <> "Cuve")),
    TB_REMPLIR_VERS_BAS = Table.FillDown(TB_SUPPR_CUVE,{"Salle"}),
    TB_SUPPR_COL_INUTILES = Table.RemoveColumns(TB_REMPLIR_VERS_BAS,{"Column19", "Column20", "Column21"})
in
    TB_SUPPR_COL_INUTILES
 
 
let
    Source = TB_RAPPORT_1,
    #"Lignes filtrées" = Table.SelectRows(Source, each [Delta] <= 0.4)
in
    #"Lignes filtrées"
 
 
let
    Source = TB_RAPPORT_1,
    #"Lignes filtrées" = Table.SelectRows(Source, each [Delta] > 0.4)
in
    #"Lignes filtrées"

A partir du tableau structuré compilé tu peux créer l'équivalent de la fonction FILTRE afin d'avoir une requête dynamique sur le champ que tu désires. J'ai posté un billet ici qui est base de travail. Bien entendu il faut avoir quelques connaissances en Langage M et PowerQuery mais rien d'insurmontable.

Egalement avec PowerQuery on peut charger un dossier contenant plusieurs fichiers soit en utilisant les fonctions natives de PowerQuery soit par du code PowerQuery que tu pilotes de manière dynamique via une boucle qui va charge à un à un les fichiers en les mettant en forme que je l'ai dans le fichier d'exemple.

Si intéressé je peux te faire un POC afin de te mettre sur les rails. Bon il faudra attendre un peu... je ne suis pas une ESN que je suis dans une ESN nationale.

Une occasion pour toi de te lancer dans l'aventure PowerQuery qui est à mon sens incontournable désormais.
J'ai largement alimenté la rubrique Trucs & Astuces sur ce mode développement en PowerQuery avec un billet sur la fusion de plus dossier à partir d'un dossier tout ceci de manière dynamique.

 

[oguruma]

Bonjour le Fil,

Pour alimenter mon post d'hier soir voici des pistes d'évolutions. Même si ça ne répond pas exactement au problème que tu soulèves ça te donnera sans des idées pour d'autres développements.

Hier hoir ma solution consistait à importer un seul fichier en PowerQuery.
Je te remets le code.

let
    Source = Excel.Workbook(File.Contents("D:\DATA\XLD_DATA\REQUETE v1.xlsx"), null, true),
    #"Rapport 1_Sheet" = Source{[Item="Rapport 1",Kind="Sheet"]}[Data],
    TB_SUPPR_NULL = Table.SelectRows(#"Rapport 1_Sheet", each ([Column1] <> null)),
    TB_PROMOTE_01 = Table.Skip(TB_SUPPR_NULL,1),
    TB_PROMOTE_02 = Table.PromoteHeaders(TB_PROMOTE_01, [PromoteAllScalars=true]),
    TB_SUPPR_CUVE = Table.SelectRows(TB_PROMOTE_02, each ([Cuve] <> "Cuve")),
    TB_REMPLIR_VERS_BAS = Table.FillDown(TB_SUPPR_CUVE,{"Salle"}),
    TB_SUPPR_COL_INUTILES = Table.RemoveColumns(TB_REMPLIR_VERS_BAS,{"Column19", "Column20", "Column21"}),
    #"Index ajouté" = Table.AddIndexColumn(TB_SUPPR_COL_INUTILES, "Index", 1, 1, Int64.Type),
    #"Valeur remplacée" = Table.ReplaceValue(#"Index ajouté",null,0,Replacer.ReplaceValue,{"Tare Contrôlée"})
in
    #"Valeur remplacée"

Dans ta demande tu sembles indiquer que tu as plusieurs rapports à intégrer. Ceux-ci seraient dans le même dossier. PowerQuery possède les fonctions natives pour importer le contenu d'un dossier.

J'ai donc créé 3 versions identiques de ton fichier RAPPORT afin de simuler l'importation d'un dossier.

Je trouve cependant que l'importation native est un peu trop verbeuse et lourde. Il y a moyen de faire plus simple et ça sera l'objet d'un prochain billet sur ton post.

Voici l'architecture de l'espace de travail PowerQuery

TB_BDD-RAPPORTS c'est la version mono fichier.

TB_RAPPORT_DOSSIER c'est la version qui embarque le contenu d'un dossier. PowerQuery s'appuie sur les autres requêtes plus haut pour fusionner les fichiers du dossier. Si tu souhaites apporter des modifications, surtout ne pas les modifier. Il faut intervenir sur la requête TB_RAPPORT_DOSSIER comme je l'ai fait.

let
    Source = Folder.Files("D:\DATA\XLD_DATA"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonne de tables développée1",{"Source.Name"}),
    #"Lignes filtrées" = Table.SelectRows(#"Colonnes supprimées", each ([Column1] <> null)),
    #"Premières lignes supprimées" = Table.Skip(#"Lignes filtrées",1),
    #"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]),
    #"Rempli vers le bas" = Table.FillDown(#"En-têtes promus",{"Salle"}),
    #"Lignes filtrées1" = Table.SelectRows(#"Rempli vers le bas", each ([Cuve] <> "Cuve" and [Cuve] <> "Production Boulangerie")),
    #"Valeur remplacée" = Table.ReplaceValue(#"Lignes filtrées1",null,0,Replacer.ReplaceValue,{"Tare Contrôlée"}),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Valeur remplacée",{"Column19", "Column20", "Column21"})
in
    #"Colonnes supprimées1"

Voir le résultat dans l'onglet TB_RAPPORT_DOSSIER


Pour revenir au problème que tu poses je t'ai orienté vers plusieurs solutions qui ont leurs avantages et leurs inconvénients.

Solution 1
Voir les onglets

Je passe par les fonctions Office 365 en espérant que tu aies cette version.

Et tu as sa petite sœur avec <0.4

Je fais appel aux fonctions FILTRE, ASSEMB.H et ASSEMB.V. Là-dessus j'ai publié dernièrement un billet dans les Trucs & Astuces du site.

Un des inconvénients : il n'y a pas de mise en forme automatique. Il te faudra passer par les MFC.
Un avantage : c'est assez rapide à coder et le tableau s'actualise automatiquement en cas d'évolution des données sources. C'est la magie des formules.


Solution 2
Les tableaux croisés dynamiques.
Pour l'exemple je suis parti BDD_RAPPORTS tableau structuré [TB_BDD_RAPPORTS].

Exemple :

cas particulier de la colonne Index
Elle a été créée dans la requête PowerQuery (voir le code). Elle permet en fait de respecter l'ordre d'importation des fichiers et de coller au résultat que tu souhaites. Je l'ai laissée visible pour la démonstration mais dans la réalité tu peux la masquer.

Tu peux te contenter d'un TCD et jongler avec des deux boutons

Code des Macro

Sub TOGGLE_CD_INF04()
    ActiveSheet.PivotTables("TCD_01").PivotFields("Delta"). _
        ClearAllFilters
    ActiveSheet.PivotTables("TCD_01").PivotFields("Delta"). _
        PivotFilters.Add2 Type:=xlCaptionIsGreaterThan, Value1:="0.4"
End Sub

Sub TOGGLE_CD_SUP04()
    ActiveSheet.PivotTables("TCD_01").PivotFields("Delta"). _
        ClearAllFilters
    ActiveSheet.PivotTables("TCD_01").PivotFields("Delta"). _
        PivotFilters.Add2 Type:=xlCaptionIsLessThanOrEqualTo, Value1:="0.4"
End Sub

Sub TOGGLE_CD_INF04_2()
    ActiveSheet.PivotTables("TCD_02").PivotFields("Delta"). _
        ClearAllFilters
    ActiveSheet.PivotTables("TCD_02").PivotFields("Delta"). _
        PivotFilters.Add2 Type:=xlCaptionIsGreaterThan, Value1:="0.4"
End Sub

Sub TOGGLE_CD_SUP04_2()
    ActiveSheet.PivotTables("TCD_02").PivotFields("Delta"). _
        ClearAllFilters
    ActiveSheet.PivotTables("TCD_02").PivotFields("Delta"). _
        PivotFilters.Add2 Type:=xlCaptionIsLessThanOrEqualTo, Value1:="0.4"
End Sub

Ces boutons font appel aux procédures TOGGLE_CD_INF04() et TOGGLE_CD_SUP04().

A propos de la colonne [Tare Contrôlée] il y a des lignes non renseignées. C'est vivement déconseillé dans un TS et dans un TCD. Donc via PowerQuery je les ai remplacé par 0. Afin de ne pas afficher ces 0 je suis passé par un format personnalisé 0;0;;@

Solution 3
Une alternative à la solution 2 si tu souhaites ce regroupement de tes fichiers par cuve

Le gros avantage des TCD : la mise en forme est plus facile car Excel te propose des modèles que tu peux aussi personnaliser.
Tu peux organiser et hiérarchiser les colonnes comme tu l'entends, y placer des calculs, calculer des mesures de champs, etc. etc. etc.
Un petit inconvénient : la recalcul n'est pas automatique en cas d'évolution des données. Il faut l'actualiser. Je ne m'étalerai pas ici pourquoi Excel ne fait pas les maj en "live". Il y a certes quelques petits contournements.

J'espère que tout ceci amènera "de l'eau à ton moulin". 🙂 - bonne journée du 8 mai.

 

[Magnio]

Bonjour oguruma,
Je te remercie pour tes interventions mais malheureusement mon entreprise ne considère pas PowerQuery aussi incontournable que toi. ^^
Nous n'en disposons pas sur nos machines, je ne peux même pas essayer tes propositions.

Encore merci.

 

[oguruma]

Bonjour oguruma,
Je te remercie pour tes interventions mais malheureusement mon entreprise ne considère pas PowerQuery aussi incontournable que toi. ^^
Nous n'en disposons pas sur nos machines, je ne peux même pas essayer tes propositions.

Encore merci.

Bonjour, ahhhh... c'est bien dommage... c'est qu'ils n'ont rien compris au DataBi car Pwq est aussi intégré à PowerBI qui est l'un des outils par excellence du traitement de la Data qui fait partie des outils des DataScientist ou DataAnalyst. Pwq est un ETL en quelques sorte Extract Transform and Load.
Bon... ils vont avoir une guerre de retard bientôt à la DSI 😉
De nombreux forums spécialisés incitent désormais à utiliser PowerQuery.
Il existe même une certif Pwq chez MS.
Fusionne un dossier contenant des fichiers Excel et ceux-ci contiennent plusieurs onglets comme de Janvier à Décembre, réorganiser les données, placer des filtres, effectuer des calculs intermédiaires, mettre des jointure, extraire les colonnes que l'on souhaite.... amuses toi à le faire en VBA avec des formules... bien entendu cela doit être dynamique, variable, paramétrable.... je m'y suis frotté en VBA avec Excel 2010 qui n'intégrait pas Pwq.... et le client ne voulait de produit add-on externes, je ne te raconte pas la galère pour avoir un résultat stable et sans plantage quand les données évoluaient.
Pwq va devenir un produit phare chez MS à mon sens.
Idem quand tu dois traiter une série de fichiers .csv, .txt qui se trouvent dans un dossier ou quand tu dois te connecter à une BDD pour extraire des requêtes et les injecter soit dans Excel ou soit dans PowerBI qui est plus approprié à ce genre de traitements.....
Idem pour traiter des PDF et les injecter. Idem pour extraire des données d'un site ou soit appeler des API.
Ou alors ils passent par un autre ETL décliné de WebSphere, j'ai connu un outil dans le passé appelé MERCATOR... pour traiter des flux bancaires comme les SEPA, CFONB etc... un ETL....d'une galère !
Dommage ma solution avec un peu de paramétrage dynamique t'aurait certainement bcp aidé et tiré d'affaires 🙂
Par ailleurs étrange que tu n'en disposes pas car depuis la V2016 il est directement intégré à Excel. Ou dans la négative vous êtes encore en V2010 ou V2013. Pour ces versions il existait en Add-On gratuit avec ses défauts de l'époque mais il commençait à rendre de bon services pour l'avoir utilisé.
Bonne journée.
Bon Courrrage alors.