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 !

 

[Dadou99]

Re : VBA et regroupement de données

Merci pour votre réponse rapide.

J'en demande peut-être beaucoup, mais est-ce possible de joindre ce code dans le fichier annexé ? Je suis désolé, mais je suis vraiment novice dans la matière.
Et cela évitera certainement encore d'autres questions et embêtement de ma part.

Puis j'essaierai de faire un test demain.

Je vous remercie infiniment pour votre aide précieuse.

Merci

 

[Dranreb]

Re : VBA et regroupement de données

Vous n'êtes pas, j'espère, encore un de ceux qui préfèrent continuer à mendier du poisson plutôt que d'apprendre à pêcher ?
Cela vous fera d'excellents exercices qui vous permettront de ne plus être aussi novice.
Je ne veux pas éviter vos questions, bien au contraire. Avez vous des difficultés à appliquer ce que je vous ai dit de faire, et si oui lesquelles ?

 

[Dadou99]

Re : VBA et regroupement de données

Non pas du tout, je vous rassure. Mes questions étaient plus sur votre fonction de regroupement.

J'ai intégré le tout avec des données tests en adaptant quelque peu votre code.
Toutefois, j'ai un problème avec la fonction qui est effectue le regroupement appelée "BillRegroupement" dans le module applicatif.
Il y a un problème avec les indices lors de la boucle qui plante le programme.

Serait-ce possible d'apporter quelques commentaires dans votre code afin que je puisse comprendre votre code ?
Et peut-être identifié le problème ?

Je vous joins le nouveau fichier.

Merci pour votre aide

 

[Dranreb]

Re : VBA et regroupement de données

Bonjour.
Le 1er problème que je rencontre en exécutant votre code c'est lors de l'inventaire préalable sur le ColPrst.Add pour noter les colonnes des différentes prestations. Vous prenez la colonne BX. Or celles ci ne contient que des nombres, et encore pas à toutes les lignes.
Vous ne pouvez utiliser qu'un String comme clé pour une collection.

 

[Dadou99]

Re : VBA et regroupement de données

Désolé il s'agissait de la mauvaise version des données de test..
Voici la bonne et la colonne est bien un champs texte..

 

[Dranreb]

Re : VBA et regroupement de données

Pfff. Pouvez pas envoyer des .xls ? Celui là m'a fait planter Excel après conversion.
Bon. après avoir recommencé, je l'ai eu. Il y a toujours un problème sur ce titre de colonne pourquoi le prenez vous de Vlgn(4) alors que la colonne BX c'est la 76 ? De toutes façon vous aurez un problème parce qu'il n'est pas répété sur chaque ligne.
Peut être est-il possible d'explorer toute la collection Doc.Contenu jusqu'à trouver celui qui n'est pas vide, je ne sais pas. Je ne sais pas ce que vous voulez faire.

 

[Dadou99]

Re : VBA et regroupement de données

Désolé pour le fichier.. J'enverrai des xls si nécessaire.

En fait, ce fichier est bien pourri et me donne des cheveux gris.

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), appelé "Référence" (colonne F).
Pour une référence, je peux avoir plusieurs documents associés à cette référence, colonne "Numéro_du_document" (colonne D)
Pour chaque document, j'ai plusieurs colonnes de données, soit le nombre de jour (colonne "Nombre_jours, colonne Q), la commune (colonne "commune", colonne BD), la catégorie de prestation (colonne "Catégorie_de_prestation", colonne BX) et le prix (colonne "somme", colonne CG).

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 par référence
- Avoir une colonne qui cumul le nombre de jour de chaque document
- Avoir une colonne par catégorie 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, soit 1 par catégorie de prestation) . Toutefois si il y a plusieurs lignes pour un document, la catégorie de prestation est uniquement indiquée sur une ligne et pas toute. Il faut donc trouver la bonne ligne pour trouver les informations.
- Avoir une colonne Commune qui reprend la commune pour la référence. La valeur de la commune se trouve uniquement sur une ligne et non sur toutes.
- Avoir un montant total prestation qui affiche la somme de l'ensemble des catégorie de prestation

 

[Dranreb]

Re : VBA et regroupement de données

Oui ça semble confirmer que vous ne pouvez simplement prendre Doc.Contenu(1). Vous devez faire un for Each Détail In Doc.Contenu, y chercher et noter les colonnes non vides qui vous intéressent. Déclarez Détail As Variant: on ne peur utiliser un tableau comme variable d'une boucle For Each. Mais ce Détail contiendra bien un tableau de Variant à une dimension. Donc vous pourrez prendre Détail(76) pour vérifier et capturer le renseignement colonne BX par exemple.
Suggestion: demandez provisoirement l'affichage en style de référence L1C1 pour avoir les titres de colonnes en chiffres, pour réduire les risques de vous tromper dans les numéros de Détail.

 

[Dadou99]

Re : VBA et regroupement de données

En effet, les colonnes dans la collection ne sont pas dans le même ordre d'affichage que les données.
Ce n'est pas possible d'accéder à la colonne par le nom de la colonne ?

Car si je fais detail(1), la valeur est celle de la colonne 6 .. Bizarre

 

[Dranreb]

Re : VBA et regroupement de données

Pas possible à priori. Si la plage spécifiée au second GroupOrg commence à partir de la cellule A2 (éventuellement étendue à tous le reste à l'aide de la fonction PlgUti) Détail(1) devrait contenir la valeur colonne A. Votre boucle sur Détail est bien incluse dans le For Each Doc in Réf.Contenu au moins ? Seuls les items de la collection de l'argument de la dernière colonne spécifiée est sous forme de tableaux de valeurs de lignes. Ceux qui l'encadrent sont des SsGroup.

 

[Dadou99]

Re : VBA et regroupement de données

J'ai encore un ptit souci pour comprendre vos deux boucles fournies dans votre code :

Première :
For Each Réf In GroupOrg(PlgUti(FeuiSrc.[A2]), 6, 2)
Lr = Lr + 1: Tr(Lr, 1) = Réf.Id
ReDim TDoc(0 To Réf.Contenu.Count - 1): ND = -1: NbJ = 0

-> Cela permet de ramener les données de la feuille source en les triant par une colonne passée en paramètre.
Pouvez-vous m'expliquer les deux instructions suivantes de cette boucle (Lr = ...)

Seconde :
For Each Doc In Réf.Contenu
Permet de lire les documents pour chaque référence ?

Pourquoi je dois encore faire une boucle permettant de lire chaque ligne de doc.contenu ?
Je n'arrive pas à le faire directement dans la deuxième ?

Merci pour votre aide et patience

 

[Dranreb]

Re : VBA et regroupement de données

Bonsoir
GroupOrg(PlgUti(FeuiSrc.[A2]), 6, 2)
Renvoie une collection d'objets de type SsGroup (voir leur structure dans les explications du module de classe du même nom) construite à partir de toute la plage utilisée à partir de la cellule A2 de l'objet Worksheet FeuiSrc, en classant sur la 6 ième puis la 2 ième colonne. Le regoupement de plus haut niveau se fait donc d'abord sur la 6ième colonne.
Lr est ma ligne courante de remplissage du tableau de sortie Tr. Je l'incrémente pour ajouter une ligne puis je commence par y mettre cette valeur de colonne 6 qui se retrouve en Réf.Id (toutes les valeurs des colonnes précisées se retrouvent toujours dans des Id de SsGoup. Il sont emboités les un dans les autres, puisque le Contenu d'un SsGroup d'une colonne autre que la dernière est lui même une sous-collection d'objets SsGroup, c'est pour ça qu'on peut faire des For Each imbriqués qui sont tout l'intérêt de ces collections.
TDoc est ma table String qui sert au Join(TDoc, " / ") pour former la liste. Je la dimensionne au nombre de Doc qu'il y a dans Réf.Contenu
For Each Doc In Réf.Contenu permet donc en effet d'explorer les documents pour chaque référence.

Pourquoi je dois encore faire une boucle permettant de lire chaque ligne de doc.contenu ?

Parce que d'après ce que j'ai compris vous ne savez pas dans laquelle d'entre elles se trouvent certaines informations qui vous intéressent, dont notamment le titre de colonne ou ça doit aller.