[Toutes versions] heures onpeak offpeak [Résolu]

[sarah2001]

Bonjour,

Je suis débutante en VBA excel, je dois coder une fonction qui me permet de calculer un volume en fonction des heures onpeak et offpeak(heures creuses et heures pleines), mais j'avoue que je ne sais pas comment faire j'ai beau réfléchir , je n'y arrive pas , c'est pour cela que je me tourne vers vous, si quelqu'un pouvez m'aider .
Voici un exemple du fichier excel : Nous avons une date en pas demi horaire du 01/01/2010 au 31/12/2013 et à chaque date correspond un volume.
Ce que je voudrai faire c'est que pour chaque mois de l'année je récupère les données(volumes) qui correspondent aux heures creuses (entre 21h et 7h ) je fait la somme pour avoir le volume pour cette plage de donnée.
Et je fait la même chose pour les heures pleine(entre 8h et 20h) .


exemple de fichier excel avant :

date volume
01/01/2010 00:00 155
01/01/2010 00:30 155
01/01/2010 01:00 155
01/01/2010 01:30 155
01/01/2010 02:00 155
01/01/2010 02:30 155
01/01/2010 03:00 155
01/01/2010 03:30 155
01/01/2010 04:00 155
01/01/2010 04:30 155
01/01/2010 05:00 155
01/01/2010 05:30 155
01/01/2010 06:00 155
01/01/2010 06:30 458
01/01/2010 07:00 458
01/01/2010 07:30 458
01/01/2010 08:00 458
01/01/2010 08:30 458
01/01/2010 09:00 458
01/01/2010 09:00 458
01/01/2010 10:00 458
01/01/2010 10:30 458
01/01/2010 11:00 458
01/01/2010 11:30 458
01/01/2010 12:00 458
01/01/2010 12:30 458
01/01/2010 13:00 458
01/01/2010 13:30 300
01/01/2010 14:00 300
01/01/2010 14:30 300
01/01/2010 15:00 300
01/01/2010 15:30 300
01/01/2010 16:00 300
01/01/2010 16:30 300
01/01/2010 17:00 300
01/01/2010 17:30 300
01/01/2010 18:00 154
01/01/2010 18:30 154
01/01/2010 19:00 154
01/01/2010 19:30 154
01/01/2010 20:00 154
01/01/2010 20:30 154
01/01/2010 21:00 154
01/01/2010 21:30 154
01/01/2010 22:00 154
01/01/2010 22:30 154
01/01/2010 23:00 154
01/01/2010 23:30 54
02/01/2010 00:00 154
02/01/2010 00:30 154
02/01/2010 01:00 154
02/01/2010 01:30 154
02/01/2010 02:00 154
02/01/2010 02:30 4
02/01/2010 03:00 154
02/01/2010 03:30 154
02/01/2010 04:00 154
02/01/2010 04:30 154
02/01/2010 05:00 154
02/01/2010 05:30 154
02/01/2010 06:00 154
,,, ,,,
,,,
,,,
,,,
,,,
,,,
,,,


exemple de fichier excel aprés:

date volume onpeak volume offpeak
janv-10 8508 4159
févr-10
mars-10
avr-10
mai-10
juin-10
juil-10
août-10
sept-10
oct-10
nov-10



Merci encore pour votre aide

[jfontaine]

Bonjour,

Pas besoin de VBA, une formule suffira
Pour les heures pleines de janvier 2010 (en G2)

Code :

=SOMMEPROD((ANNEE($A$2:$A$1000)=2010)*(MOIS($A$2:$A$1000)=1)*(HEURE($A$2:$A$1000)>=HEURE("8:00"))*(HEURE($A$2:$A$1000)<=HEURE("20:00"));$B$2:$B$1000)

EDIT : pour les heures creuses la différence entre le total et les heures pleines suffira

Code :

=SOMMEPROD((ANNEE($A$2:$A$1000)=2010)*(MOIS($A$2:$A$1000)=1);$B$2:$B$1000)-G2

[sarah2001]

Merci de ta réponse, en réalité c'est une modification que je dois apporter à une application faite en VBA, ce qui fait que doit coder cette modification dans ce langage , au lieu d'avoir mes résultats en pas demi horaire ,je voudrai les avoirs en onpeak et offpeak.

Merci encore

[bbil]

Il y as combien de colonne dans ton tableau ?

[sarah2001]

j'ai deux colonnes ,une colonne date et une colonne volume.

Voici un exemple du format de fichier que j'ai:


date volume
01/01/2010 00:00 155
01/01/2010 00:30 155
01/01/2010 01:00 155
01/01/2010 01:30 155
01/01/2010 02:00 155
01/01/2010 02:30 155
01/01/2010 03:00 155
01/01/2010 03:30 155


et voici le format de fichier que je voudrai avoir:

date volume onpeak volume offpeak
janv 2011 8662 4467
fev 2011 ...... ......


Merci beaucoup

[jfontaine]

Tu peux utiliser cette formule en VBA

Code :

Range("G2").FormulaLocal = "=SOMMEPROD((ANNEE($A$2:$A$1000)=2010)*(MOIS($A$2:$A$1000)=1)*(HEURE($A$2:$A$1000)>=HEURE(""8:00""))*(HEURE($A$2:$A$1000)<=HEURE(""20:00""));$B$2:$B$1000)"

Et ainsi récupérer le résultat de la cellule G2

[MattChess]

Bonjour,

Envoyé par sarah2001

c'est une modification que je dois apporter à une application faite en VBA
au lieu d'avoir mes résultats en pas demi horaire, je voudrai les avoir en onpeak et offpeak.

Pouvez-vous poster entre les balises [code] et [/code] les constantes, si nécessaire les variables globales et la procédure de votre application VBA en pas demi horaire afin que l'on puisse avoir le contexte pour passer en mode onpeak / offpeak ?

[sarah2001]

Bonjour

Malheureusement je n'ai pas l’application chez moi , c'est une application sur laquelle je devrai travailler en stage.
On m'a juste expliqué le contexte et les modifications que je devrai apporter à cette application.(On m'a fait comprendre que je n'avais pas le droit de faire sortir le logiciel de l'entreprise)

Merci

[MattChess]

Envoyé par sarah2001

pour chaque mois de l'année je récupère les données (volumes) qui correspondent aux heures creuses (entre 21h et 7h ) je fait la somme pour avoir le volume pour cette plage de donnée.
Et je fait la même chose pour les heures pleine(entre 8h et 20h) .

On suppose que l'heure pleine de fin est jusqu'à 20:59:59

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
Option Explicit ' Statistiques mensuelles des heures on peak vs. off peak
' ============================== Nom de la feuille source
Public Const nomFeuilleSource = "Relevées"
Public Const rowReleve = 2 ' Première rangée de relevé
Public Const colDate = 1 ' Première colonne A de date en pas demi horaire
Public Const colVolume = colDate + 1 ' Volume du relevé
Public Const dateOnPeakStart = #8:00:00 AM# ' Heure de début heure pleine
Public Const dateOnPeakEnd = #8:59:59 PM#   ' Heure de fin heure pleine
' ============================== Nom de la feuille résultat
Public Const nomFeuilleCible = "Statistique par mois"
Public Const rowStatMonth = 2 ' Première rangée de statistique mensuelle
Public Const colMonth = 1 ' Première colonne d'affichage des statistiques par mois
Public Const colOnPeak = colMonth + 1 ' Colonne heures pleines
Public Const colOffPeak = colOnPeak + 1 ' Colonne heures creuses
' Point d'entrée principal qui parcourt les relevées par demi pas horaires
Sub OnOffPeak()
Dim indRowDate As Integer, indRowMonth As Integer
Dim dateRelevee As Date, dateMonth As Date, monthCurrent As Integer, timeReleve As Date
Dim volumeReleve As Long, volOnPeak As Long, volOffPeak As Long
Dim wSheetSource As Worksheet, wSheetCible As Worksheet
    Set wSheetSource = FeuilleParNom(nomFeuilleSource)
    Set wSheetCible = FeuilleParNom(nomFeuilleCible)
    If wSheetSource.Name = wSheetCible.Name Then Stop: End ' Erreur pas dans la même feuille
    indRowDate = rowReleve      ' Première ligne de la feuille source
    indRowMonth = rowStatMonth  ' Première ligne de la feuille cible
    monthCurrent = 0: volOnPeak = 0: volOffPeak = 0
    With wSheetSource
        While .Cells(indRowDate, colDate) <> "" ' Boucle tant que la ligne n'est pas vide
            dateRelevee = DateUsToFr(.Cells(indRowDate, colDate)) ' Date du relevé
            If Month(dateRelevee) <> monthCurrent Then ' Nouveau mois
                If monthCurrent > 0 Then ' Il y a un cumul de relevés du précédent mois
                    DisplayVolumeByMonth wSheetCible, dateMonth, volOnPeak, volOffPeak, indRowMonth
                End If
                dateMonth = dateRelevee
                monthCurrent = Month(dateRelevee) ' Changement du mois courant
                volOnPeak = 0: volOffPeak = 0 ' Remise à zéro des cumuls des heures
            End If
            volumeReleve = .Cells(indRowDate, colVolume) ' Conso du relevé
            timeReleve = dateRelevee - Int(dateRelevee) ' Extrait l'heure de la date
            If timeReleve >= dateOnPeakStart And timeReleve <= dateOnPeakEnd Then
                volOnPeak = volOnPeak + volumeReleve   ' Cumul heures pleines
            Else
                volOffPeak = volOffPeak + volumeReleve ' Cumul heures creuses
            End If
            indRowDate = indRowDate + 1 ' Prochaine ligne
        Wend
    End With
    If volOnPeak > 0 Or volOffPeak > 0 Then ' Dernier mois
        DisplayVolumeByMonth wSheetCible, dateMonth, volOnPeak, volOffPeak, indRowMonth
    End If
End Sub
 
' Convertir de "MM/DD/YYY" vers "DD/MM/YYYY"
Function DateUsToFr(ByVal strDateUs As String) As Date
Const indSlash1 = 3, indSlash2 = indSlash1 * 2
Dim strDateFr As String
 
    If Mid(strDateUs, indSlash1, 1) = "/" And Mid(strDateUs, indSlash2, 1) = "/" Then
        strDateFr = Mid(strDateUs, indSlash1 + 1, indSlash1) + Mid(strDateUs, 1, indSlash1) + _
            Mid(strDateUs, indSlash2 + 1) ' Inverse le mois et le jour
    Else
        strDateFr = strDateUs ' Conversion non possible
    End If
    On Error Resume Next
    DateUsToFr = CDate(strDateFr)
    If Err.Number <> 0 Then Stop: End ' Date au format inconnu
    On Error GoTo 0
End Function
 
' Accède à une feuille par son nom. C'est la feuille courante si non trouvée
Function FeuilleParNom(ByVal strNomFeuille As String) As Worksheet
    On Error Resume Next
    Set FeuilleParNom = Worksheets(strNomFeuille)
    If Err.Number <> 0 Then Stop: End ' Gestion d'erreur. Feuille inconnue
    On Error GoTo 0
End Function
' Affichage du résultat des statistiques mensuelles des heures on peak vs. off peak
Sub DisplayVolumeByMonth(wSheetCible As Worksheet, ByVal dateMonth As Date, _
        ByVal volOnPeak As Long, ByVal volOffPeak As Long, ByRef indRowMonth As Integer)
Const strFormatDate = "MMM-YY"
Dim strMonthYear As String
 
    strMonthYear = Format(dateMonth, strFormatDate)
    With wSheetCible
        .Cells(indRowMonth, colMonth) = strMonthYear
        .Cells(indRowMonth, colOnPeak) = volOnPeak
        .Cells(indRowMonth, colOffPeak) = volOffPeak
    End With
    Debug.Print strMonthYear & " " & volOnPeak & " " & volOffPeak
    indRowMonth = indRowMonth + 1 ' Prochaine ligne de statistiques du mois
End Sub

Dans la fenêtre d'Exécution immédiate (Ctrl+G) du VBE d'Excel, copier-coller et valider par ENTER :

Code :

OnOffPeak

janv-10 8662 4313
févr-10 0 2918
janv-10 8662 924
févr-10 11410 7231
janv-10 1374 0
févr-10 4540 924
mars-10 8662 4313
avr-10 8662 3842

Dans la feuille cible "Statistique par mois" on trouve :

Code :

1
2
3
4
5
6
7
8
9
date    volume onpeak   volume offpeak
janv-10   8662   4313
févr-10   0   2918
janv-10   8662   924
févr-10   11410   7231
janv-10   1374   0
févr-10   4540   924
mars-10   8662   4313
avr-10   8662   3842

[sarah2001]

Merci pour ton aide, seulement voilà j'ai une erreur d'incompatibilité de type au niveau de la ligne ( dateRelevee = .Cells(indRowDate, colDate) ' )
je ne comprend pas

Merci

[bbil]

Envoyé par sarah2001

Merci pour ton aide, seulement voilà j'ai une erreur d'incompatibilité de type au niveau de la ligne ( dateRelevee = .Cells(indRowDate, colDate) ' )
je ne comprend pas

Merci

Pour aider à ce qu'on t'aide... donne plus d'explication lorsque le message "Erreur d'exécution ... erreur 13..." apparait clique sur le bouton Débogage ..
puis passe la souris sur ton code pour voir les valeurs de tes variables (grâce au bulle d'aide) .. ainsi dans ce cas tu devrais peu-être t'apercevoir que la cellule .Cells(ind.... ne renvoi pas une date...

[MattChess]

On ne devrait être pas loin d'une solution. Cela doit être un problème de réglage des rangées / colonnes où trouver la 1ère date et dans quel format elle se présente.

Session de mise au point de OnOffPeak()

Ouvrir le classeur .xls avec les relevés horaires.
Ouvrir le Visual Basic Editeur (VBE) par Alt+F11 ou
Cliquez sur l'Excel menu "Outil" > "Macro" > "Visual Basic Editeur"

A la ligne 30 du module contenant OnOffPeak() :

Code :

dateRelevee = .Cells(indRowDate, colDate) ' Date du relevé

cliquez dans la marge jusqu'à obtenir un point rouge.

Si la ligne devient bleue, c'est que vous avez sélectionné la ligne. Annulez la sélection.
Cliquez plus à gauche dans la colonne grise de la marge sans aller cliquer dans la fenêtre Projet.

Vous avez mis un point d'arrêt. Toute la ligne doit devenir rouge.

Ouvrir la fenêtre d'Exécution immédiate (Ctrl+G) ou
Cliquez sur le VBE menu "Affichage" > "Fenêtre d'Exécution"
Copier-coller et valider par ENTER :

Code :

OnOffPeak

Le programme s'arrête sur le point d'arrêt ligne 30.

Copier-coller et valider par ENTER la variable de rangée suivante pour savoir où on en est dans la boucle ?

Code :

? Cells(indRowDate, colDate)

01/01/2010

Avancez en pas à pas avec le menu "Mise au point" (Debug) > "Pas à pas détaillé" (F8) (Step Into)

L'erreur se produit :
Dans la fenêtre d'Exécution immédiate, copier-valider par ENTER :

Code :

? Err.Number

0
Zéro signifie qu'il n'y a pas d'erreur.

Code :

? Err.Description

message d'erreur

Indiquez exactement dans quelle rangée et colonne on trouve la première date.
Précisez sa valeur. Exemple : 29/12 leurs n° de colonne respectifs.

[sarah2001]

Je ne sais pas si c'est ça l'erreur au lieu de mettre rowReleve = 1 on met rowReleve = 2 ,mais j'ai une autre erreur à la ligne volOnPeak = volOnPeak + volumeReleve ,j'ai une erreur de type dépassement de capacité?

Je suis vraiment désolée ,mais je suis vraiment nulle en VBA

J'ai résolu le probléme de la ligne dateRelevee = .Cells(indRowDate, colDate)
j'ai une autre erreur à la ligne volOnPeak = volOnPeak + volumeReleve ,j'ai une erreur de type dépassement de capacité.
Voici le fichier excel sur lequel je travaille les données vont du 01/01/2010 au 31/12/2015.

[bbil]

Envoyé par sarah2001

J'ai résolu le probléme de la ligne dateRelevee = .Cells(indRowDate, colDate)
j'ai une autre erreur à la ligne volOnPeak = volOnPeak + volumeReleve ,j'ai une erreur de type dépassement de capacité.
Voici le fichier excel sur lequel je travaille les données vont du 01/01/2010 au 31/12/2015.

même réponse que précédemment tu clique sur le bouton débugger .. et tu regarde les valeurs de tes variables ...
et sachant que "volOnPeak" est un integer tu as dépassement si > à 32767 .. si ce dépassement est normal passe en type long ..

[sarah2001]

Merci j'ai résolu le problème ,mais je trouve pas le même résultat pour le mois de janvier j'ai trouvé en faisant l'addition des volumes onpeak et offpeak
le volume onpeak= 8662 et le volume offpeak =4159 et l’exécution de la macro ne me donne le résultat que pour le mois de janvier hors je voulais pour chaque mois le l'année les volumes onpeak et offpeak correspondants

[MattChess]

J'ai mis à jour le message #9 avec la dernière version :

• Feuille source et feuille cible doivent être différentes et nommées comme les constantes.
• Extension au type Long pour les volumes cumulés pour éviter un possible overflow.
• Conversion des dates du format américain MM/DD/YYYY au format français DD/MM/YYYY.

Envoyé par sarah2001

je trouve pas le même résultat pour le mois de janvier j'ai trouvé en faisant l'addition des volumes onpeak et offpeak
le volume onpeak= 8662 et le volume offpeak =4159

Pour l'onpeak, on trouve la même chose.
Pour l'offpeak, vous trouvez moins parce que vous comptez dans l'intervalle 21 h à 7 h.
Le programme compte de 21 h à 7 h 59:59.
C'est une question d'interprétation de bornes d'intervalles que vous corrigerez aisément selon ce que vous souhaiterez en adaptant les constantes et/ou en ajoutant explicitement la gestion de l'intervalle heures creuses qui est actuellement implémentée comme le complémentaire des heures pleines.

Dans l'énoncé initial, il y a potentiellement un flou entre d'une part 20 h et 21h et d'autre part 7 h et 8 h. Il faut bien répartir ces zones ? C'est à vous de définir si ce sont des heures pleines ou creuses. Les bases de l'algorithme ayant été données, vous pouvez le mettre au point comme vous voulez.

Envoyé par sarah2001

l’exécution de la macro ne me donne le résultat que pour le mois de janvier hors je voulais pour chaque mois le l'année les volumes onpeak et offpeak correspondants

Les dates sont éventuellement en format américain MM/DD/YYYY au lieu de DD/MM/YYYY.
La langue du format de la date est une autre ambiguïté à lever.

On a fourni un convertisseur minimal qui impose d'avoir une date qui commence par "MM/DD/". Vous pourrez l'améliorer s'il y a des dates au format "M/D/YYYY".

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
' Convertir de "MM/DD/YYY" vers "DD/MM/YYYY"
Function DateUsToFr(ByVal strDateUs As String) As Date
Const indSlash1 = 3, indSlash2 = indSlash1 * 2
Dim strDateFr As String
 
    If Mid(strDateUs, indSlash1, 1) = "/" And Mid(strDateUs, indSlash2, 1) = "/" Then
        strDateFr = Mid(strDateUs, indSlash1 + 1, indSlash1) + Mid(strDateUs, 1, indSlash1) + _
            Mid(strDateUs, indSlash2 + 1) ' Inverse le mois et le jour
    Else
        strDateFr = strDateUs ' Conversion non possible
    End If
    On Error Resume Next
    DateUsToFr = CDate(strDateFr)
    If Err.Number <> 0 Then Stop: End ' Date au format inconnu
    On Error GoTo 0
End Function

Il faudra améliorer la gestion d'erreur car actuellement j'ai mis Stop: End pour arrêter le programme dans le débogueur.
Voir la procédure Warning "NNNN: message" ligne 117 dans Cellules d'Excel vers tableau Word en VBA.

[sarah2001]

Merci à tous pour votre aide, je n'aurai pas pu trouver la solution sans vous

MERCI MERCI MERCI MERCI MERCI MERCI MERCI MERCI MERCI MERCI MERCI !!!!!

[sarah2001]

Bonjour,

Je reviens encore fois vers vous , car en lançant des tests aujourd'hui en changeant de mois et d'année, je n'obtiens que l'affichage du mois de janvier, je vous met mon fichier en pièce jointe, je commence vraiment à déseperer


Merci d'avance

[MattChess]

Quand la discussion a été résolue, vous aviez des dates US MM/DD/YYYY

Code :

1
2
3
4
    With wSheetSource
        While .Cells(indRowDate, colDate) <> "" ' Boucle tant que la ligne n'est pas vide
'            dateRelevee = DateUsToFr(.Cells(indRowDate, colDate)) ' Date US du relevé
            dateRelevee = .Cells(indRowDate, colDate) ' Date française du relevé

Maintenant vous avez des dates françaises JJ/MM/AAAA sur votre PC !
On a mis en commentaire la conversion des dates US vers FR.

[sarah2001]

Merci à tous encore une fois