Synthèse de données avec calcul

[chrisw]

Bonjour,

je débute sous excel en programmation mais souhaite en apprendre d'avantage.
Je voudrais obtenir une synthèse des données par identifiant avec une somme des montants en utilisant une macro.
La feuille de données comporte plusieurs informations liés à l'identifiant.

Un exemple avec la synthèse souhaitée.

Merci d'avance

 

[Dranreb]

Re : Synthèse de données avec calcul

Bonjour.
Imaginons que vous ayez une Collection. Que chaque élément de cette collection possède une propriété Id qui soit votre identifiant colonne A de vos donnée et une propriété Contenu qui soit une autre collection. Que chaque élément de cette dernière collection contienne une table à 1 dimension de 6 éléments, reprenant les valeurs des données uniquement d'une ligne où la colonne A est égale à l'identifiant. Sauriez vous écrire deux boucles For Each In imbriquées qui fassent ce que vous voulez ?

 

[thebenoit59]

Re : Synthèse de données avec calcul

Bonjour Chris. Bonjour Dranreb.

A quoi correspondent tes dates 1 et 2 ?
Souhaites-tu regrouper seulement par Identifiant ou également par date ?
Pourquoi ne pas utiliser des formules au lieu de macros ?

 

[chrisw]

Re : Synthèse de données avec calcul

Re,

les dates sont à conservés et rattachés à l’identifiant.
Je souhaite regrouper seulement par identifiant.
il existe une formule pour regrouper des données ? Je pensais que seul un TCD ou une macro arriverais au bout.

J'ai essayé d'écrire mon raisonnement :

Tant que cellule pas vide
pour chaque identifiant
élément 1=élément 1
élément 2=élément 2
élément 3=élément 3
élément 4=élément 4
élément 5=élément 5
élément 6=somme de tous les éléments 6
Fin pour
Fin tant que

Es-ce le bon principe ?

 

[Dranreb]

Re : Synthèse de données avec calcul

il existe une formule pour regrouper des données ?

Oui. C'est une fonction GroupOrg qui renvoie cette collection. Je l'ai écrite dans un module de service MClassement. Il utilise également deux modules de classe, SsGroup (c'est le type de donnée des éléments de la collection) et TableIndex (usage interne à MClassement) et la bibliothèque Scripting (référence "Microsoft Scripting Runtime", parce qu'une de ses fonctions renvoie un Dictionary). Voulez vous que j'équipe votre classeur de tout ça ? (C'est une programmation assez complexe mais qui ne se retouche pas, et peut être employée dans de nombreuses applications)
Mais j'aimerais d'abord, si vous le voulez bien, voir votre algorithme qui explorera la collection.
Comme ça s'il tourne bien je n'aurais plus qu'à y apporter un interfaçage minimaliste avec la fonction GroupOrg.
Si nécessaire toutefois car ses paramètres sont assez simples: le 1er c'est une expression Range des données à considérer, le ou les suivant(s) le ou les numéro(s) de colonne(s) sur quoi opérer le(s) regroupement(s). Simplement 1 dans votre cas.

 

[thebenoit59]

Re : Synthèse de données avec calcul

J'ai voulu me lancer dans une formule, mais je pense que les puristes te fourniront des calculs bien plus propre

 

[chrisw]

Re : Synthèse de données avec calcul

Finalité de ma demande :
Ne maîtrisant pas toujours mes données (sauf l'identifiant unique dans la première colonne) je souhaitais programmer une macro généraliste et l'adapter en fonction de mes besoins, le plus simplement possible même si ce n'est pas optimisé.
Le fichier excel envoyé n'est qu'un exemple. Je ne cherche pas une macro figée mais une macro adaptable qui n'as pas obligatoirement besoin d'interface

Es-ce que le raisonnement suivant est correct :

Tant que cellule pas vide
pour chaque identifiant
élément 1=élément 1
élément 2=élément 2
élément 3=élément 3
élément 4=élément 4
élément 5=élément 5
élément 6=somme de tous les éléments 6
Fin pour
Fin tant que

 

[Dranreb]

Re : Synthèse de données avec calcul

Est-ce que vous pourriez continuer et maintenir facilement par la suite un code qui commencerait comme ça :

NbL = Worksheets("Données").Cells(Rows.Count, "A").End(xlUp).Row - 1
For Each Ident In GroupOrg(Worksheets("Données").Range("A2:F2").Resize(NbL), 1)

Et qui se poursuivrait plus loin par :

For Each Détail In Ident.Contenu

Sachant que Détail(6) représenterait par exemple dans cette boucle le Montant d'une ligne de cet identifiant Ident.Id ?

 

[chrisw]

Re : Synthèse de données avec calcul

Si j'ai bien compris : NbL est la valeur du nombre de ligne de ma feuille de données.

For Each Ident In GroupOrg(Worksheets("Données").Range("A2:F2").Resize(NbL), 1) signifie : pour chaque Identifiant de la feuille Données de la colonne A2àF2 jusqu'a la ligne Nbl

je suis incapable d'écrire la suite, de plus je ne comprend pas pourquoi le code vb suivant : For Each Détail In Ident.Contenu

 

[Dranreb]

Re : Synthèse de données avec calcul

Oui pour le premier point.
Pour le second c'est plus exactement: pour chaque élément de la collection renvoyée par la fonction GroupOrg à partir de cette plage, à raison d'1 pour chaque valeur différente trouvée dans la colonne 1. Et la plage c'est A2 à F2 redimensionné à NbL lignes, donc jusqu’à la ligne NbL+1, puisqu'on commence à la 2.
Dans la variable Ident il y aura plus de choses que seulement le nom en colonne 1. Mais ce nom y sera aussi: ce sera Ident.Id
Pour le 3ième point:
La suite du premier For Each ça va surtout consister à remettre à 0 une variable destinée à recevoir le cumul des Détail(6)
ou ajouter 1 à un numéro de ligne si vous choisissez de tout stocker dans un tableau de sortie que vous déchargerez tout à la fin en une seule instruction.
Et probablement la seule instruction de la seconde boucle, à les y additionner.
Ident.Contenu sera la collection des lignes se rattachant à Ident.Id.
Rassurez vous, les propriété Id (As Variant) et Contenu (As Collection) seront expliquées par des commentaires d'aide dans le module de classe SsGroup.

 

[Dranreb]

Re : Synthèse de données avec calcul

Information: j'ai modifié mon précédent message.
Avez vous avancé depuis tout à l'heure ?
Ne manquez pas de me signaler si vous voulez que je joigne le corrigé.
Mais il vaudrait mieux que vous finissiez d'imaginer à peu près toute la procédure, au moins dans son principe: vous serez mieux à même de comprendre ce que j'aurai fait, et capable d'en imaginer d'autres.

 

[chrisw]

Re : Synthèse de données avec calcul

Ident est une variable contenant un tableau à 1 dimension à 6 éléments. Le premier élément est nommé Id, les autres éléments sont nommés Contenu.

étapes du programmes :
1) Définir le nombre de ligne
2) Utiliser la fonction sur l'étendue de mon tableau
3) 2 cas :
- Même id, on calcule
- id différent on remet à 0 la variable destinée à recevoir le cumul et on décharge le tableau.

J'ai bon ?

 

[Dranreb]

Re : Synthèse de données avec calcul

Heu peut être… mais si on n'utilise pas GroupOrg.
Celui ci à en effet déjà fait à votre place tous les tests sur l'identifiant, rangé ensemble dans SsGroup.Contenu les lignes se rapportant à un même identifiant, qu'il a mis dans SsGroup.Id, et stocké tous les objets SsGroup dans une Collection.
L'exploration de cette collection devrait normalement être plus simple à programmer que de devoir se taper le contrôle si l'Id est pareil que le précédent. Et s'il n'y en a plus parce que c'était le dernier ?.… vous oubliez alors de produire quand même aussi le dernier ?…
D'ailleurs vous parlez peu de production du résultat, c'est à dire, puisque vous projetez, à juste titre, de décharger à la fin un tableau, d'y stocker les infos trouvées pendant l'exploration. Or c'est ça qui est essentiel, et que je voudrais que vous précisiez dans votre algo.

 

[chrisw]

Re : Synthèse de données avec calcul

J'avoue ne pas connaître GroupOrg mais d'après les informations données ci-dessus, j'en déduis :

Le "tableau" obtenu avec cette fonction est trié

2 cas :
- lorsqu'il n'y a pas de sous-groupe (une seule ligne avec un identifiant), on reporte directement les valeurs.
- si sous-groupe, on additionne (les montants) dans une variable, on reporte les valeurs et la variable.

 

[Dranreb]

Re : Synthèse de données avec calcul

Oui les SsGroup de la Collection (et non tableau) rendue par GroupOrg sont classés par Id.
Lorsqu'il y n'y a qu'une ligne il y a quand même un SsGroup dont Contenu est une Collection, mais d'un seul élément Détail.
Il n'y a pas de raison de ne pas l’additionner, comme les autres à un total initialement remis à 0, même si on ne passe qu'une fois, donc, dans la boucle interne qui additionne.
(On pourrait un peu l'écrire quand même comme vous le dites, parce qu'un SsGroup a une propriété Count qui donne le nombre d'éléments de son Contenu, mais ça n'apporterait rien)