VBA et regroupement de données

[Dadou99]

Bonjour à tous,

Avant tout, je remercie les gens qui prendront le temps de lire ce post et qui j'espère pourront m'aider à réaliser une macro en code vba pour atteindre le résultat souhaité.

Mon problème est le suivant :
J'ai un fichier de données (onglet données brutes) qui est exportée depuis une application et je souhaite faire un regroupement afin d'avoir un résultat comme dans l'onglet résultats à obtenir.

Tout d'abord, il faut faire un regroupement par la colonne référence (il peut y a voir plusieurs lignes avec cette référence)
Pour une référence, je peux avoir plusieurs documents associés à cette référence.
Pour chaque document, j'ai trois colonnnes de données, soit le nombre de jour, le type de prestation et le prix.

L'idée du regroupement est le suivant :
- Avoir une ligne par référence
- Avoir une colonne qui concatène les différents documents associés
- Avoir une colonne qui cumul le nombre de jour de chaque document
- Avoir une colonne par type de prestation qui indique le prix de ma prestation trouvé dans mon document. A noter qu'iIl peut y en avoir une bonne dizaine de type de prestation, donc si c'est possible de créer les colonnes dynamiquement ce serait vraiment le top.
- Avoir un montant total prestation qui affiche la somme de l'ensemble des types de prestation

Par la suite, je devrai faire des tableaux et ainsi de suite.
J'ai des milliers de lignes à gérer, donc il me faudrait vraiment ce ptit programme.

Je joins un exemple du problème et du résultat souhaité.

D'avance je vous remercie pour votre aide !

 

[Dranreb]

Re : VBA et regroupement de données

Bonjour.
Je l'ai presque réalisé mais j'obtiens une différence qui ne s'explique pas dans votre exemple de résultat.
Faut il ne compter qu'une seule fois une prestation d'un certain type comme le 100 pour les 3 × 100 presta A Doc 10112 Réf 1
ou en faire la somme comme le 100 pour les 2 × 50 presta D Doc 10115 Réf 3 ?

Pareil pour le nombre de jours : vous trouvez 25 pour la réf 3, moi 15. Mais si je fais la somme je trouverai 9 pour la 1 au lieu de 3

 

[Misange]

Re : VBA et regroupement de données

Bonjour

Sans macro mais avec un TCD, quelque chose qui s'en rapproche pas mal.

Cela dit je ne comprends pas trop ton tableau d'arrivée pour la référence 1 : pourquoi ça ne fait que 3 jours et pas 9, et donc 300 au lieux de 900 en prix ?

PLus d'infos pour démarrer avec les TCD (et aller plus loin sur les pages suivantes) ici
Ce lien n'existe plus

 

[Dadou99]

Re : VBA et regroupement de données

Bonjour,

Merci pour votre réponse.

Si il y a plusieurs documents pour une référence mais que le numéro de document est identique, il ne faut pas les cumuler.
Le fichier reçu en entrée (document xml) contient environ 70 colonnes... Raison pour laquelle et ceci dans certains cas, il y a plusieurs lignes pour une prestation avec le même numéro de document.. Il faut donc prendre uniquement les données d'une ligne et non les cumuler.

Par contre, si il y a plusieurs documents avec un numéro différent, alors il faut les cumuler car les documents sont différents.

Et serait-ce possible d'avoir cela en code vba ? Ou impossible ?

En espérant avoir répondu à vos questions...

Merci pour votre aide précieuse...

 

[Misange]

Re : VBA et regroupement de données

Alors il faut ajouter un champ dans la base de données pour identifier les uniques et ajouter ce champ en filtre dans le TCD.

 

[Dranreb]

Re : VBA et regroupement de données

En espérant avoir répondu à vos questions...

Pas tout à fait: pour la prestation 3 il y a 2 documents identiques le 10115, et pourtant ils sont cumulés dans votre exemple.

P.S. Cela dit s'il faut tout cumuler pour une prestation sans se poser de question, dès qu'il y a plusieurs documents, même si certains d'entre eux sont identiques, je peux vous le faire sans problème.

 

[Dadou99]

Re : VBA et regroupement de données

Merci..

En fait, je dois déjà faire un tri depuis un premier fichier (comme mentionné ci-dessous)
Il faudrait donc que l'ajout de la colonne se fasse automatiquement...
Raison pour laquelle je voulais faire cela un vba .. De plus je devrai en faire de même pour un autre fichier..

Et dans le résultat que vous avez fourni, il y a une erreur dans la dernière ligne, dans la colonne Total somme prix..

 

[Dadou99]

Re : VBA et regroupement de données

Pas tout à fait: pour la prestation 3 il y a 2 documents identiques le 10115, et pourtant ils sont cumulés dans votre exemple.

Vous avez parfaitement raison..
Il s'agit d'une erreur de ma part, désolé..

Les montants ne doivent pas être cumulés dans ce cas-là

 

[Dranreb]

Re : VBA et regroupement de données

C'est une formule dans ma solution, elle calcule bien le total de la ligne. Reste à lever l’ambiguïté références 1 et 3. Faut-il appliquer le système proposé en P.S. dans mon dernier poste ?

Ah. Ok ! N'en tenez pas compte alors. Ça convient, donc ?

 

[Dadou99]

Re : VBA et regroupement de données

Non il ne faut pas tout cumuler...

Ca convient parfaitement !! Un grand merci..

Juste une dernière question... Comment puis-je intégrer cela dans mon projet (désolé je suis vraiment un novice).
Je dois recopier l'ensemble de vos fonctions vba ?

Encore merci pour votre aide...

 

[Dranreb]

Re : VBA et regroupement de données

Oui. Les 2 modules ordinaires et les 3 modules de classes sont à glisser vers votre projet VBA. Ce sont ce que j'appelle des modules de service. L'applicatif proprement dit que j'ai du développer, en les utilisant, n'est que la Private Sub Worksheet_Activatee du module Feuil2 (Résultats à obtenir). Naturellement les [A6] que j'y ai mis pour préserver le modèle de résultat sont à remplacer par [A1].

Oups ! Une faute d'inattention m'a fait intégrer le module de classe ListesLiées dont vous n'avez nul besoin. Vous pouvez le supprimer. D'autant que s'il vous intéressait pour un autre usage, vous auriez besoin en plus du module ordinaire MDictionnArbo. (c'est une autre pièce de mon système OutIdx, très modulaire).

 

[Dadou99]

Re : VBA et regroupement de données

Parfait.. Je vais essayer d'intégrer tout cela !
Encore un énorme merci pour votre temps et votre aide..

 

[Dranreb]

Re : VBA et regroupement de données

Bonjour.

Voici une nouvelle version du classeur.
Elle devrait faciliter l'adaptation dans le cas où les données se trouveraient sous la même forme que dans votre feuille Feuil1 (Données brutes) mais dans un autre classeur. Le traitement proprement dit est maintenant effectué par une procédure Regrouper d'un module Applicatif. Il suffit de lui transmettre en paramètre une expression Worksheet représentant la feuille contenant ces donnée dans le classeur source.
Il conviendrait peut être de supprimer la Private Sub Worksheet_Activate de FRésu (Résultats à obtenir) surtout si la Feuil1 venait à disparaître. Je l'ai laissée en attendant mais elle ne contient plus qu'un exemple d'appel de la procédure Regrouper.

 

[Dadou99]

Re : VBA et regroupement de données

Bonjour,

Désolé pour la réponse tardive et merci pour votre message.

Pour un numéro de prestations, il peut y avoir plusieurs lignes (comme dans l'exemple), raison pour laquelle je dois faire un regroupement.

Pour la colonne "commune", et comme il y peut avoir plusieurs par prestation, la commune est renseignée uniquement sur une ligne et non sur toute. Je dois donc récupérer au moins une fois le nom de la commune.

Voici un nouvel exemple avec plus de données et le résultat attendu.

Comment puis-je intégrer cela dans le fichier annexé ??

Merci pour votre aide.

 

[Dranreb]

Re : VBA et regroupement de données

Bonsoir.
Il vous faut glisser avec la souris, depuis le projet VBA du classeur GrpOrgDadou99.xls vers celui de votre classeur, les modules de services MClassement et Utilit ainsi que les modules de classes SsGroup et TableIndex. Il ne seront pas à retoucher.
Prenez aussi le module Applicatif car il contient la procédure Regrouper qu'il y a peut être lieu d'adapter, elle. Elle admet en paramètre une expression Worksheet qui devrait permettre de prendre les informations dans un autre classeur sans avoir besoin de les charger préalablement dans le votre. Son résultat va dans une feuille dont le nom de l'objet Worksheet correspondant dans la rubrique "Microsoft Excel Objets" doit être FRésu. Ou sinon changer en Feuil3 comme dans votre classeur joint.
N'hesitez pas à poser d'autres questions si la nature de ce qui est rendu par la fonction GroupOrg n'est pas suffisamment bien expliquée dans le module MClassement où elle réside. de fait j'y ai changé le commentaire en tête par ceci :

Rem. —— Renvoie une collections d'objets de type SsGroup. Ceux ci ont essentiellement
'       une propriété Id (As Variant) et une autre Contenu (As Collection). Une propriété Tag
'       leur a toutefois été ajoutée à toutes fin utiles à son exploitation.
'       Plg: La plage à partir de laquelle doit être contituée la collection.
'       ColOrd: liste de valeurs entières dont les signes représentent le sens croissant ou décroissant
'            du tri des arguments, et les valeurs absolues leurs numéros de colonnes relatifs dans Plg.
'            Remarque: Cette liste supporte désormais en son sein un élément non numérique ou = 0.
'                      Celui ci marquera la fin des colonnes définissant l'organisation emboîtée du
'                      résultat, mais pas la fin des colonnes spécifiables ensuite, sur lesquelles
'                      portera encore le classement des lignes de détail.
#If False Then ' Le résultat de GroupOrg peut être Affecté à une variable :
        Dim MonGroupeOrganisé As Collection, MaPlage As Range
        Set MonGroupeOrganisé = GroupOrg(MaPlage, 1, 2, -3)
'       Mais dans la plupart des cas on programmera plus tôt quelque chose comme :
        Dim RGlobal As SsGroup, RNiv2 As SsGroup, ValsLigne As Variant
        For Each RGlobal In GroupOrg(MaPlage, 1, 2) ' qui englobera d'autres :
           For Each RNiv2 In RGlobal.Contenu
              For Each ValsLigne In RNiv2.Contenu
                 FaireQuelqueChoseDe ValsLigne(3), pour:=RGlobal.Id, et:=RNiv2.Id
                 Next ValsLigne, RNiv2, RGlobal
#End If ' La collection Contenu de la dernière colonne contiendra dans chacun de ses items
'       une table à une seule dimension des valeurs de la ligne.

Le minuscule module de classe SsGroup aussi s'est vu agrémenté de quelques commentaires. En voici tout le code :

Option Explicit
Rem. —— Bien que vous pourriez l'utiliser à d'autres fins, cette classe définit le type des objets
'       composant une collection rendue par la fonction GroupOrg du module MClassement.
'       Le 1er paramètre passé à GroupOrg définit la plage source à partir de laquelle
'       la collection est fabriquée, et les valeurs absolues des suivants les colonnes
'       sur lesquelles doivent s'opérer les regroupements.

Public Id As Variant '         Propriété. Identification de l'instance d'objet.
               ' GroupOrg la reprend de la 1ère des colonnes qui lui ont été spécifiées.
Public Contenu As Collection ' Propriété. GroupOrg rend pour chaque item de cette collection là :
               ' Pour chaque colonne autre que la dernière, s'il y en a, un objet lui même
               ' de type SsGroup uniquement basé sur les colonnes restantes spécifiées.
               ' Pour la seule dernière colonne restante: un Variant contenant un tableau à une
               ' seule dimension reprenant les valeurs d'une ligne de la plage source.
               ' Contenu contient donc les valeurs de toutes les ligne dont les valeurs de colonnes
               ' spécifiées correspondent à tous les SsGroup.Id successifs impliqués à un moment de
               ' l'exploration de toute la collection, par plusieurs boucles For Each In emboîtées.
Public Tag As Variant '        Propriété. Non gérée puisqu'à votre disposition à toutes fins utiles
               ' lors de l'expoitation de la collection.
'

Public Sub Add(ByVal QuelqueChose) ' On aurait pu s'en passer. Mais un module de classe
Contenu.Add QuelqueChose           ' sans rien d'autre que des propriétés ?… Bizarre !
End Sub