Discussion :

[Darkangel2014]

Bonjour à toutes et tous,

j'ai une question concernant le script ci dessous (fonctionnant correctement):

comment procéder afin de trier l'affichage rendu

En effet Statut peut correspondre à

Active / Rejected / Negociation / Submitted / Drafting / Closed (celui là non affiché)

à l'heure actuelle tout se reporte bien mais je voudrais par exemple afficher les rejected tout à la fin, aussi est il possible d'organiser l'affichage de l'array dans l'ordre souhaité et si oui comment svp?
En effet j'aimerais les lister comme ceci (Active / Negociation / Submitted / Drafting / Rejected)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
If statut <> "Closed" Then
If Not dic_statuts.exists(statut) Then Set dic_statuts(statut) = CreateObject("Scripting.Dictionary")
Set dic_projects = dic_statuts(statut)
dic_projects(id_projet) = Array(statut, Acronym, Topic, début, fin, commentaire, Investigator_firstname, Investigator_name, CLng(Budget_Requested))
Set dic_statuts(statut) = dic_projects
 
End If

Merci à vous par avance
bonne journée

[Darkangel2014]

Bonjour,

voici à toutes fins utiles un exemple pour illustrer mon soucis
le fichier test contient les données et va compléter un rapport dans le document dest

je souhaiterais donc organiser l'affichage de sorte à ne pas avoir les abandonned et rejected juste après les active

j'aimerais avoir dans l'ordre Active / Submitted / negociation / Drafting / Abandonned / Closed (celui là non affiché)

merci par avance à vous pour votre aide
Bonne journée

test.zip

[Pierre Fauconnier]

Salut.

Un dictionary n'est ni trié ni triable, de sorte que tu vas devoir trier ton array via un algo de ton choix (tri à bulles très lent, autres algos à mettre en place).


La question est: Pourquoi passer par un dictionary alors que tu pourrais passer par un tableau structuré, éventuellement dans une feuille cachée, que tu trierais comme tu le souhaites et que tu restituerais filtré et trié en VBA. Si u nous disais d'où tu pars et où tu veux arriver, plutôt que comment, nous pourrions t'aider à trouver le bon chemin plutôt que de t'accompagner sur une mise en place qui, de mon point de vue, n'est pas la meilleure.

Une solution (tag 2016) sans VBA ou à intégrer dans ta solution VBA serait de passer par Power Query au départ de ton tableau structuré des tâches t_Tâches et d'un tableau des statuts t_Statuts qui renseigne l'ordre de tri:

1. Importer les deux tableaux dans Power Query
2. Fusionner dans une nouvelle requête t_Ouvertes sur base de la colonne commune Statut
3. Développer la table de statuts dans t_Ouvertes pour récupérer la colonne permettant le tri
4. Filtrer pour exclure les 999 que l'on ne veut pas voir;
5. Trier sur la colonne d'ordre
6. Supprimer cette colonne
7. Restituer le tout dans Excel

En pur VBA, je ferais pareil avec une feuille masquée pour préparer les résultats.

[Pierre Fauconnier]

Si tu souhaites le réaliser en VBA, voici, sur base des trois tableaux précités t_Tâches, t_Statuts, t_Ouvertes et d'un tableau t_Temp, une solution qui utilise les tableaux structurés en VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sub PrepareTasks()
  Dim Target As Range
  Dim Pos, CountOf As Long
 
  ' Nettoyage du tableau temporaire
  If Not Range("t_Temp").ListObject.DataBodyRange Is Nothing Then Range("t_Temp").ListObject.DataBodyRange.Delete
 
  ' Copie des taches dans le temporaire
  Set Target = Range("t_Temp").ListObject.ListRows.Add().Range(1)
  Target.Resize(Range("t_Tâches").Rows.Count, 2).Value = Range("t_Tâches").Value
 
  ' Ajout de l'ordre et tri
  Range("t_Temp[Ordre]").Formula = "=Index(t_Statuts[Ordre],match([@Statut],t_Statuts[Statut],0))"
  With Range("t_Temp").ListObject.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("t_Temp[Ordre]")
    .Apply
  End With
 
  ' Suppression des statuts 999
  Pos = Application.Match(999, Range("t_Temp[Ordre]"), 0)
  If Not IsError(Pos) Then
    CountOf = Application.CountIfs(Range("t_Temp[Ordre]"), 999)
    Range("t_Temp[Tâche]")(Pos).Resize(CountOf, 3).Delete
  End If
 
  ' Copie dans le tableau final
  If Not Range("t_Ouvertes").ListObject.DataBodyRange Is Nothing Then Range("t_Ouvertes").ListObject.DataBodyRange.Delete
  Set Target = Range("t_Ouvertes").ListObject.ListRows.Add().Range(1)
  Target.Resize(Range("t_Temp").Rows.Count, 2).Value = Range("t_Temp[[Tâche]:[Statut]]").Value
End Sub

[Darkangel2014]

Bonjour à vous et merci pour vos retours.

merci pour cette remarque plus que pertinente sur le fait que les dicos ne soit pas triable.
cela m'évite de m'entêter dans cette direction.

en fait je pars de ma base de donnée comprenant plusieurs données projets segmentés et je dois désormais générer un rapport pour mes supérieurs
j'ai décidé d'automatiser cette tâche avec un tableau structuré segmentant et classant les projets selon leurs statuts.

je n'ai pas su comment procéder et une âme charitable m'a proposer d'utiliser la méthode dico qui fonctionne au demeurant mais avec la limite que cela ne peut donc se classer m'obligeant à reformatter le doc manuellement ... ce qui me fait perdre du temps

powerquery que j'avais testé très peu avait une tendance à ralentir le processus global de ma base et ne suis donc pas donc tenté de me réorienter vers cela si je peux éviter. quant au tableau structuré pourquoi pas mais j'ai pas encore saisis la méthode...

[Pierre Fauconnier]

A toutes fins utiles, voici le lien vers mon tuto sur les tableaux structurés.

Power Query n'est actif qu'à l'actualisation, de sorte que je ne comprends pas bien ce que tu veux dire par "ralentir le traitement". A moins que tu aies des centaines de milliers de lignes et des centaines d'étapes dans ta solution Power Query, je doute que ce soit "lent", même si cette notion est hautement subjective. Pas certain que ce soit plus lent que de passer par du VBA.

Voici deux solutions, l'une Power Query et l'autre VBA, sur lesquelles tu pourrais t'appuyer.


Tâches-Statuts-PowerQuery.xlsx

Tâches-Statuts-VBA.xlsm

[Darkangel2014]

A la vue de votre retour je dois donc conclure ne pas avoir utilisé powerquery de manière adéquate dans le passé

je vous remercie pour vos deux fichiers
je veux bien tenter cette méthode si effectivement ne se lance qu'actualisation.


dans mon cas le soucis c'est que je dois récupérer des données de différents onglets:
- projects
- investigators
- budget

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 With tb_projects
        For i = 0 To .ListRows.Count
            id_projet = .ListColumns("Id_Project").DataBodyRange.Rows(i)
            Acronym = .ListColumns("Acronym").DataBodyRange.Rows(i)
            Topic = .ListColumns("Topic").DataBodyRange.Rows(i)
            début = .ListColumns("Start_Date").DataBodyRange.Rows(i)
            fin = .ListColumns("End_Date").DataBodyRange.Rows(i)
            commentaire = .ListColumns("Comments").DataBodyRange.Rows(i)
            statut = .ListColumns("Status").DataBodyRange.Rows(i)
 
             With Worksheets("Investigators")
                On Error Resume Next
               project_Range = Application.Match(id_projet, .Range("B:B"), 0)
 
 
                Investigator_firstname = Empty: Investigator_name = Empty
                If Err = 0 Then
                    Investigator_firstname = .Range("A:G")(project_Range, 3)
                    Investigator_name = .Range("A:G")(project_Range, 4)
                End If
            End With
 
 
                  With Worksheets("Budget")
                On Error Resume Next
               project_Range = Application.Match(id_projet, .Range("A:A"), 0)
 
                                 Budget_Requested = Empty
                If Err = 0 Then
                    Budget_Requested = .Range("A:O")(project_Range, 15)
                           End If
            End With

et je n'ai pas forcement besoin de l'ensemble des données juste certaines
aussi je dois bien a avouer ne pas trop savoir comment m'y prendre

faut il importer tous les tableaux de chaque onglet?

[Darkangel2014]

bion bah de toute facon tournant sur mon pc portable de boulot je ne peux activer powerquery sous excel car non visible des complements com

oups rien dit je ne regardais pas au bon endroit déoslé

[Pierre Fauconnier]

Normalement, les règles de bonne conception requièrent que l'on dispose un seul tableau structuré par feuille. Power Query permet de récupérer tous les tableaux du classeur et, en les nommant de façon rigoureuse et systématique, on peut exploiter uniquement les tableaux souhaités, en utilisant un préfixe ou un suffixe pour désigner les tableaux à récupérer.

Dans le cas des tableaux mentionnés et au vu de leurs noms, je suppose qu'il faut recréer un tableau qui va chercher les données dans les différents tableaux de base, un peu comme un RECHERCHEV, puis filtrer, trier, supprimer des colonnes, éventuellement ajouter des colonnes formulées => Bref, rien de bien compliqué pour Power Query. Dans cette vidéo de blog, j'explique comment on peut fusionner trois tableaux.

Pour pouvoir aller plus loin, il faudrait avoir la structure des tableaux de base avec quelques données anonymisées dedans, le tableau de résultat qui reprend ces données et les règles qui président à son élaboration.

[Darkangel2014]

la structure est dans le fichier test partagé

[Pierre Fauconnier]

Dans le fichier Dest, il n'y a qu'une feuille blanche, et ça n'explique pas le traitement. Faut-il filtrer certaines données, les trier, ....?

Aide-toi, DVP t'aidera ... ^^

[Darkangel2014]

oui c'est normal tout est généré depuis le fichier test

[Pierre Fauconnier]

Tu t'imagines réellement que je vais lire ton code pour découvrir ce que tu peux m'expliquer en quelques lignes? Manifestement, nous n'avons pas toi et moi la même façon de voir à quoi sert un forum d'entraide informatique

Bon courage pour la suite!

[Darkangel2014]

Tout d'abord Veuillez m'excuser je pensais que vous aviez déjà parcouru le code question et merci pour le temps pris pour votre assitance

j'ai une base de donnée globale comprenant une liste de projets avec leurs work-packages, leur deliverables, le personnel impliqués etc.
mon objectif ici est ici de généré un document récapitulatif des projets en cours de gestion qu'il soit déja actif, en cours de nego, déja soumis et meme nouvellement rejeté
pour se faire:

Tout d'abord et dans l'onglet projet je récupère les données suivantes
- Id_Project
- Acronym
- Topic
- Start_Date
- End_Date
- Comments
- statut

avec l'id projet de chaque projet je récupère ensuite

dans l'onglet investigator:
- l'investigateur principal du projet avec son prenom et nom

et dans l'onglet budget
- le montant demandé pour chaque projet

ce que je recherche au final c'est pouvoir classer les projets avec leurs informations propres par leurs statuts dans un ordre donné à savoir Active / Negociation / Submitted / Drafting / Rejected
je ne souhaite pas pour information affficher le statut closed

merci par avance pour l'aide que vous pourrez m'apporter

[Pierre Fauconnier]

Tu as les bases du travail à réaliser, tant en Power Query qu'en VBA.

Avec Power Query, tu peux parcourir les étapes du travail du fichier que j'ai fourni, elles sont en général simples à comprendre. En VBA, j'ai commenté les quelques lignes qui servent à réaliser cela. Il te suffit de fusionner les tableaux de départ pour en récupérer les données. Avec la vidéo que je t'ai fournie en lien, cela ne devrait pas poser de problème.

Le code VBA que je t'ai fourni (17 lignes hors commentaires et déclarations!) illustre comment on peut lier les tableaux par VBA (application.match... qui est l'équivalent VBA de Power Query). C'est déjà une belle base de travail, non? En gros, il te suffit d'un peu de mise en forme à la fin (et on peut faire simple à ce niveau, surtout avec des tableaux structurés). Je veux bien aider, mais faire le travail gratos alors que c'est mon métier et que je facture mes clients pour ce genre de boulot, ça je ne fais pas.

Si tu veux de l'aide (de ma part en tout cas), retrousse-toi les manches, mets les mains dans le cambouis et montre ce que tu as essayé, où ça bloque, etc etc...

Et si tu l'a déjà fait en VBA avec le dictionary, peut-être te suffit-il alors de trier ton tableau final (au passage, je te donne un code dans la solution VBA dont tu peux t'inspirer). Que te faut-il de plus? le sourire de la crémière ?

Pour conclure en attendant que tu montres que tu as cherché, mon approche sans dictionary est une approche Excel (quand on programme en VBA pour Excel, on pense d'abord Excel avant de penser VBA, surtout si on n'est pas programmeur). L'exécution du code est parfois plus lente, mais au moins tu la maîtrise car elle réalise en VBA ce que tu aurais fait en Excel:

• Lier des tableaux avec RECHERCHEV, RECHERCHEX (XL 365), INDEx-EQUIV;
• Trier des tableaux;
• Filtrer des tableaux;
• Copier des tableaux;
• Supprimer des colonnes;
• Faire un peu de mise en forme.

Il n'y a rien de bien mystérieux dans les quelques lignes de code (ou les quelques étapes Power Query) nécessaires à ces manipulations qui sont des manipulations de base lorsque l'on traite des données pour les analyser

[Darkangel2014]

pas de soucis je suis effectivement disposé à m'y mettre
je te remercie par ailleurs pour le lien est les documents soumis
je vais désormais regarder cela de plus près

[Thumb down]

j'aimerais les lister comme ceci (Active / Negociation / Submitted / Drafting / Rejected)

Bonsoir,
Ici il n'est ocunement question d'un tri mais un ordonnancement selon critaire et je ne vois pas Powerquerry réglé cela nativement. Mais Pierre est bien plus compétant que moi pour confirmer ou infirmer !

[Pierre Fauconnier]

Salut Thumb,

Pour résoudre le problème de l'ordonnancement, j'ai ajouté une colonne donnant le rang de chaque statut (voir le message #3). Dès lors, Power Query et VBA sont capables de trier sur base d'une clé de tri personnalisée spécifiée dans le tableau. J'ai mis 999 comme clé de tri pour les statuts que l'on ne souhaite pas retrouver dans le tableau de résultat, de manière à pouvoir filtrer et exclure les lignes qui possèdent le statut 999.

J'ai mis deux fichiers d'exemple dans le message #6, un pour la solution Power Query et l'autre pour la solution VBA, qui s'appuie du reste sur le même principe de l'ajout d'une colonne d'ordre, le tri ou l'ordonnancement n'étant pas naturel Il suffit d'ouvrir les fichiers et de regarder chaque étape Power Query de la requête t_Ouvertes ou d'exécuter le VBA pas à pas pour voir que c'est en gros le même raisonnement qui s'applique et que les étapes sont les mêmes.

[jurassic pork]

Hello,
pour faire un tri suivant une liste on peut utiliser une liste personnalisée (voir ici)
voici un exemple de code qui copie le contenu de la feuille Projects dans une feuille Temp. Dans cette feuille il y a une liste personnalisée de tri et une liste de filtre. On effectue un tri suivant la liste en P et un filtre suivant la liste en Q :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sub CustomTriFiltre()
Dim r As Range
Dim rngLstTri As Range
Sheets("Temp").Range("A1:N78").ClearContents
Sheets("Projects").Range("A1:N78").Copy
Sheets("Temp").Range("A1").PasteSpecial Paste:=xlPasteValues
Set r = Sheets("Temp").Range("A2:N78")
Set rngLstTri = Sheets("Temp").Range("P2", Sheets("Temp").Range("P2").End(xlDown))
 
 
    Application.AddCustomList rngLstTri
    ' on trie en fonction de la liste personnalisée
    Sheets("Temp").Sort.SortFields.Clear
    r.Sort key1:=[M2], order1:=1, ordercustom:=Application.CustomListCount + 1, _
           key2:=[G2], order2:=1
 
    Application.DeleteCustomList Application.CustomListCount
    ' on  enlève ce qu'il y a en colonne filtre
    r.AutoFilter Field:=13, Criteria1:="<>" & Sheets("Temp").Range("Q2")
End Sub

voici le contenu des listes :




et voici le résultat :




Ami calmant, J.P

[Pierre Fauconnier]

Salut JP,

C'est vrai, mais je n'aime pas car la liste existe dans Excel et pas uniquement dans le classeur (et elle est chargée à l'ouverture d'Excel). Je ne recommande donc cette solution que dans de très rares cas.

Evidemment, tu la crées et la supprime après, mais ça induit du VBA là où ce n'est pas forcément nécessaire. De plus, si l'ordre de tri change, ça impose une modification du code VBA alors que la colonne d'ordre peut être modifiée par "n'importe quel utilisateur" sans connaissance de VBA. De plus, ça ne résout pas le problème des statuts à ne pas prendre en compte