Discussion :

[Lucorah]

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) pour arriver à ce résultat, auriez vous des idées?

[Didier Gonard]

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

[mercatog]

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

[Lucorah]

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)

[Didier Gonard]

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

[Lucorah]

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)

[Didier Gonard]

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

[Lucorah]

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.

++

[sabzzz]

bonjour à tous,

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

Code :

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

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

[mercatog]

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

[Lucorah]

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

[sabzzz]

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.

[Didier Gonard]

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

[Lucorah]

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

[Didier Gonard]

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

Didier

[Lucorah]

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

[Didier Gonard]

Tu as combien de camion ?

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

Didier

[Lucorah]

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

[Didier Gonard]

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 :

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
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 :

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
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

[Lucorah]

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!

++