Créer une collection dynamique

Bonjour à tous,

Je suis un peu coincé dans mon projet, je dispose d'une plage de données filtrée (tirée d'un fichier csv avec environ 2000 lignes) ayant des colonnes Date, code véhicule, volume de carburant et compteur du véhicule.
J'aimerais extraire les données relatives à chaque véhicule par mois pour calculer le volume consommé et la consommation/100km afin d'établir un graphique chaque mois qui affiche la conso moyenne de chaque véhicule et la conso moyenne du parc.

Comme chaque ligne représente un volume de carburant pris à la pompe j'aimerais dans un premier temps compiler les "transactions" de chaque véhicule sur le mois et les récupérer sur une feuille pour avoir une synthèse et ensuite en faire un graphique.
Je pensais donc créer une collection Périodes qui contient les mois (et que ces derniers soient ajoutés au fur et à mesure dans cette collection) et une collection Véhicules qui contient les codes véhicules (avec la possibilité d'en rajouter) pour pouvoir boucler dessus.

Je coince complètement sur le traitement à appliquer sur ma plage de données (manque de pratique et de connaissances:aie:) pour arriver à ce résultat, auriez vous des idées?

Bonjour,
si à partir de ta colonne date et véhicule, tu extrait les mois et les codes en les mettant dans une nouvelle colonne, tu obtient une plage de range qui est une collection de range..

mets en exemple la structure de tes dates et de tes véhicules, le reste relève du traitement de chaîne.

Voir si les structures des données sont constantes dans leur colonne ?

cordialement,

Didier

Je crois qu'un tableau croisé dynamique est fait pour répondre à cette question.

Mercatog: un TCD pourrait être adapté mais plutôt à partir des données compilées, or je bloque avant la compilation

Ormonth:
Je vais tester ta piste en creusant la piste du traitement de chaîne (mes données conserve la même structures car elles sont tirées d'un .csv pondu par une autre application)

Citation:

---

mais plutôt à partir des données compilées

---

qu'entends-tu par là ?

sinon, ici c'est le VBA et le TCD te demandera une feuille de données brutes bien tructurées pour être efficace, donc souvent aussi des manips préparatoires. Une fois LA feuille obtenue, suivant le contexte, c'est en effet une solution riche et puissante.

Si tu nous fournit tes structure de chaînes, on pourra te guider, l'utilisation de filtres auto ensuite peut être une piste si tu choisi la pist e VBA qui n'est pas forcément indispensable..

cordialement,

Didier

Mon projet est de créer un fichier de suivi des consommations de carburants par les véhicules de l'entrerprise qui puisse s'actualiser facilement et rapidement (car à part un stagiaire personne n'a le temps de s'en occuper...).

La pompe de l'entreprise enregistre à chaque transaction le n° du véhicule, la date, le volume servi et le kilométrage. J'importe ces données par fichier csv sous excel.
Je voudrais en tirer un historique mensuel par véhicule (volume consommé sur le mois et distance parcourue => conso/100km) et faire un graphique par mois avec les stats de chaque véhicule et la moyenne.

Comme mon stage ne sera pas éternel je voudrais automatiser tout ça d'où VBA!!

En tout cas merci de m'aider
J'ai vraiment une mauvaise connexion ici donc je pourrai éventuellement joindre un fichier quand je serais chez moi (1/2 heure pour répondre)

Citation:

---

Si tu nous fournit tes structure de chaînes, on pourra te guider, l'utilisation de filtres auto ensuite peut être une piste si tu choisi la pist e VBA qui n'est pas forcément indispensable..

---

Pas besoin forcément d'un fichier que peut de contributeurs ouvrent, mais simplement d'indiquer :

les dates sont au format : 21/10/09 ou 13/janvier/2010 etc..., je veux en extraire le mis ou l'année dans une colonne ou pouvoir filtrer...

les donéées véhicules sont ainsi "Camion a dédé ref 12n63p", je veux pouvoir en extraire etc... sachant qu'il y a toujours le m^me nombre de caractères ou qu'il n'y a que les chiffres toujours situés à la fin qui m'intéressent etcc

cordialement,,

Didier

Alors :
Mes dates sont au format jj/mm/aaaa hh:ss et je veux extraire mm/aaaa
Les n° de véhicule sont au format 13##### (toujours 7 chiffres) et j'en ai besoin en intégralité
les volume de carburant sont en litres donc ##,## et je veux les garder tels quels.
le kilométrage est composé uniquement de chiffres

je voudrais si possible récupérer:
le total des volumes de carburant par véhicule par mois
le kilométrage total par véhicule par mois

Dans ma feuille de synthèse je voudrais reprendre ces 2 valeurs et calculer la consommation/100km pour chaque véhicule. Ensuite reprendre ces données dans un graphique qui reprend la conso/100km de tous les véhicules (en histogramme) avec une courbe de la moyenne pour voir ou chaque véhicule se situe par rapport à la moyenne.

++

bonjour à tous,

une possibilité pour la compilation est d'utiliser la fonction sommeprod,

Code:

---

=SOMMEPROD((MOIS(Feuil1!A2:A4)=2)*(Feuil1!B2:B4=A1)*(Feuil1!C2:C4))

---

un TCD est fait pour ça (avec groupement sur mois/année)

hello Sabzzz,

Ok pour la compilation mais je ne suis pas encore arrivé là, il faudrait que j'arrive à regrouper mes données (filtrer je sais pas encore) à la fois par code véhicule et par période (mois).

Question:
Peut on dans une même boucle d'utiliser plusieurs filtres, récupérer les données filtrées et les traiter (pour obtenir mes fameuses données pour la feuille synthèse),les réinitialiser et en appliquer d'autres? (à la base c'est pour ça que je voulais créer des collections)

Je pense que si j'arrive à écrire tout ça en VBA je devrais pouvoir me rapprocher de qqchose de cohérent sur ma feuille synthèse et après en tirer un graphique, un TCD

bonjour à tous,

c'est l'avantage de la fonction sommeprod, les données n'ont pas besoin d'être trier ou filtrer. la fonction se charge de tout, tu n'a qu'à mettre les conditions voulues.

Bonjour,

ton post d'origine aurait du se placer dans le sous-forum conception pour, ensuite résoudre les questions points par points ;)

Donc ce qu'il faudrait que tu décides c'est veux-tu passer pas le VBA ou en feuilles seules ?

Ça aura le mérite de clarifier les réponses sachant que :

Ta solution doit être parfaitement solutionnable sans vba avec l'avantage qu'il n'y a pas à activer les macros par la suite entre-autres...

A partir de là les réponses s'orienteront clairement dans un sens ou l'autre et tu auras ta solution, si ton projet comporte déjà du vba, il est par contre peut-être mieux de continuer ainsi pour construire un traitement global !

cordialement,

Didier

Je préfèrerais passer par le VBA car l'intérêt pour mon entreprise c'est d'avoir un fichier automatisé, qu'ils puissent continuer à exploiter simplement quand j'aurais fini mon stage et qui leur permette d'avoir un historique.

Je pense que je vais devoir passer par une boucle qui applique des filtres auto et récupère les données en feuille de synthese pour chaque periode (mois). Ensuite je calcule les valeurs de la feuille de synthese (consommation L/100km), et enfin j'en tire un graph.
Voila pour la base, ensuite il faudrait simplement que le fichier se mette à jour sans recalculer l'intégralité des données, uniquement les nouvelles.

Pour le moment j'ai écrit (enregistré) le code pour l'import du fichier csv et j'ai une première macro pour supprimer les données inutiles contenues dans le csv.
Je voudrais commencer à m'attaquer à la boucle sur les périodes mais je ne sais pas comment m'y prendre

ça représente potentiellement combien de cellules en gros ?

Didier

Quand j'importe mon csv j'ai environ 3000 lignes * 6 colonnes (en sachant que le nombre de ligne augmente de 1 à chaque fois que quelqu'un passe à la pompe).
Après application de la macro pour ne conserver que les données utiles (je ne m'intéresse qu'à un type de véhicule) je me retrouve avec 1400 lignes * 6 colonnes

Tu as combien de camion ?

Une liste finie peu variable ou ça change pas mal ?

Didier

Salut Ormonth!
Il y a 23 camions (dont 1 en commande), la liste est relativement variable (un ou deux camions par an).

EDIT : bon j'ai définitivement pas les connaissances et la pratique nécésssaire pour ce projet donc je vais me contenter d'une solution plus modeste faite à la main + TCD.

Merci de votre aide, a bientot

Bonsoir,

:) le souci des modifs de message, c'est qu'on n'est pas prévenu comme un nouveau post, j'avais préparé un truc que je te livre quand même sachant qu'avec un peu d'effort ça te servira pour l'avenir ;)
je ne t'avais pas posé toutes ces questions pour rien...

Je te propose une piste à base d’Array (variable tableau), qui a l’avantage d’être très rapide, facile à maintenir et surtout à adapter selon d’autres besoins.
Encore une fois, cette approche n’a de sens que si elle est intégrée à un projet VBA plus conséquent.

Je l’ai testé sur un fichier perso de 38950 lignes et 8 colonnes avec une moyenne de 65 à 70 caractères par lignes, qui ne correspond pas, mais qui avait des colonnes dates au format voulu, des colonnes chiffres affectées arbitrairement à la conso et au Kilométrage et une colonne de 105 noms (clients) différents sur les 38950 lignes affectées aux camions (donc un parc de 105 véhicules).

L’exemple génère une feuille pour visualisation à la fin qui n’est pas utile, selon tes besoins, il vaut mieux tout faire en mémoire et afficher à la fin ce que tu veux…

Pour suivre l’exemple voici comment se présente le tableau de base :

Colonne A = divers
Colonne B = consommation
Colonne C = Dates jj/mm/aaaa hh :mm
Colonne D = camions
Colonne E = divers
Colonne F = Kilométrage journalier

La présence de cellules vides n’interfère pas dans le traitement, certains « camions » n’ont que 3 mois d’activité (je ne me suis pas cassé la tête, mon fichier se présentait ainsi).
Tout le traitement peut se concevoir en code seul sous Array (variable tableau), pour plus de clarté, j’ai découpé pas à pas. Les timer permettent de récupérer les temps de traitement.

Dans un premier temps la première procédure à lancer : Sub ConstruitTab

Elle se sert de mon tableau de base et y ajoute 2 colonnes où on inscrit les jours et les mois relatifs à la date. Ensuite on a le tableau final en Feuil2 (ce qui n’est utile que pour la visualisation et notre découpe).

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Sub ConstruitTab() Dim tbopass() As Variant Dim I As Long Dim Chrono1 As Single Dim Chrono2 As Single   Chrono1 = Timer Worksheets(1).Columns("D:E").Insert Shift:=xlToRight 'notre feuille de base à qui on ajoute temporairement '2 colonnes vierge pour avoir un array de la structure voulue tbopass = Range("A2:H38950") 'mise en Array Worksheets(1).Columns("D:E").Delete ' supression des colonnes de passage For I = 1 To UBound(tbopass, 1) 'on extrait les jours et les mois de la colonne date tbopass(I, 4) = Day(tbopass(I, 3)) tbopass(I, 5) = Month(tbopass(I, 3)) ' monthname( Month(tbopass(I, 3))) te donnerait le mois en lettres Next I Worksheets("Feuil2").Range("A2:H38950") = tbopass 'colle nos données dans la feuil2 vierge Chrono2 = Timer MsgBox Chrono2 - Chrono1 & " secondes" End Sub

---

Dans un deuxième temps, la procédure TruckSoluce qui fait le traitement, via en premier l’extraction de la liste de camions en créant un Array (variable tableau) de la colonne Camions sans doublons.

Ensuite un jeu de boucles met en forme nos données et écrit le résultat en Feuil3 pour visualisation.
La feuil3 a pour en-têtes
Camion conso mois1 km mois1 conso mois km mois2… 12 12 .

Code:

---

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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

Sub TruckSoluce() Dim tbopass() As Variant Dim I As Long, J As Long, K As Long Dim rezultat() As Variant Dim tboCamions() As Variant Dim blnExiste As Boolean Dim Chrono1 As Single, Chrono2 As Single   Chrono1 = Timer Worksheets(2).Select tbopass = Range("A2:H38950") '*** liste camions **** ReDim Preserve tboCamions(1) 'sera l'Array de notre liste de camions For I = 1 To UBound(tbopass, 1) ' on boucle sur les lignes For J = 1 To UBound(tboCamions) ' on boucle sur le nombre de camions (progressif) If tbopass(I, 6) = tboCamions(J) Then blnExiste = True 'si le camion est dans la liste, on met notre booleen à true Next J If blnExiste = False Then 'si le camion n'existe pas dans la liste If tboCamions(1) <> "" Then ReDim Preserve tboCamions(UBound(tboCamions) + 1) 'on lui aloue une place tboCamions(UBound(tboCamions)) = tbopass(I, 6) 'et on l'ajoute End If blnExiste = False 'on remet à false :) Next I   For I = 1 To UBound(tboCamions) 'Inutile, pour visualiser la liste si besoin... Cells(I, 10) = tboCamions(I) Next I '*** fin liste ***   '*** totaux consos et Km mensuels camions *** ReDim rezultat(UBound(tboCamions), 25) '25 = 1 col = nom camion et 12 * col conso et Km For J = 1 To UBound(tboCamions) 'pour chaque camions For K = 1 To 12 ' pour chaque mois For I = 1 To UBound(tbopass, 1) 'pour chaque cellule If tbopass(I, 6) = tboCamions(J) Then ' si cell = camion If tbopass(I, 5) = K Then 'si cell = mois rezultat(J, 1) = tboCamions(J) 'camion rezultat(J, K * 2) = rezultat(J, K * 2) + tbopass(I, 2) 'conso mois rezultat(J, (K * 2) + 1) = rezultat(J, (K * 2) + 1) + tbopass(I, 8) 'Km mois End If End If Next I Next K Next J   Worksheets("feuil3").Range("A2:Y106") = rezultat() ' pour la visualisation si besoin Chrono2 = Timer MsgBox Chrono2 - Chrono1 & " secondes" ':) End Sub

---

vois les tutos suivants :

Utiliser les variables tableaux en VBA Excel de Silkyroad

pour les courbes avec vba :

Support de cours complet pour le VBA d'Excel de JM Rabilloud.
c'est en pdf, tu fais une recherche sur le mot graphique ou du genre... le tout est une bible du vba sinon ;)


cordialement,

Didier

Merci beaucoup Ormonth
Tu as fourni un travail conséquent, du coup j'ai du changer d'idée par faute de temps (en entreprise le temps c'est de l'argent) mais vu que j'ai un peu de temps libre cette semaine grâce au pont de l'ascension je vais essayer de reprendre mon projet de départ avec ce que tu as fourni.

En tout cas beau travail!

++