Calcul moyenne entre deux dates selon condition par vba

Version imprimable

Bonjour à tous,

Je sais pas si il fallait plutôt poser la question dans conception ou pas:

Voilà ma question:

je cherche à calculer le délai moyen entre deux dates à plusieurs condition:
- que la date 2 en col K soit supérieure à la date 1 en col J (sinon ne pas compter car erreur de remplissage de cellule) pour info les date sont au format personnalisé dd/mm hh:mm
- que le numéro de semaine au format AAAAS (20152 par exemple) en cellule O2 de la feuille TdB soit égale au meme numéro de semaine de la colonne E de la feuille Liste
- Je veux faire tout ceci par macro et non pas pas formule pour gagner du temps lors de la sauvegarde du fichier car sinon c'est extremement long vu toutes les données.

Je ne sais pas trop par ou commencer... ceci sert à faire des indicateurs par semaine

j'ai commencé par ca mais la je bloque pour continuer .... Je crois que je suis même mal parti:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 DL_SUIVI = Workbooks("SUIVI_LOTS1.xls").Worksheets("Liste T&F").Cells(Rows.Count, 3).End(xlUp).Row + 1 Set K = ThisWorkbook.Worksheets("Liste").Range("A3:C54").Find(ThisWorkbook.Worksheets("TdB").Range("O2").Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not K Is Nothing Then L = K.Row 'pour définir l'année/sem en cours de test End If montableau() = Workbooks("SUIVI_LOTS1.xls").Worksheets("Liste T&F").Range("A3:K" & DL_SUIVI).Value For j = 3 To L 'boucle sur les année/sem du TdB col 1 du début à l'année/sem en cours définit en range "O2" feuille tdb For i = LBound(montableau, 1) To UBound(montableau, 1) If Year(montableau(i, 5)) & DatePart("ww", montableau(i, 5)) = ThisWorkbook.Worksheets("Liste").Cells(j, 1).Value Then ' et la que faire pour calculer le délai moyen en heure de toutes les données du tableau qui remplissent la condition ci-dessus ' car en faisant une boucle pas moyen de calculer une moyenne avec worksheetfunction.average... End If Next i 'pprochaine boucle dans tableau ThisWorkbook.Worksheets("Liste").Range("C" & j).Value = moy 'on incrémente la moy (définir moy) Next j 'passe au la prochaine annne/sem Erase montableau 'réinitalise le tableau
Merci de votre aide

27/05/2015, 10h37
Menhir

Reviens aux fondamentaux : une moyenne, c'est une somme divisée par une quantité.

Tu déclares deux variables, par exemple MaSom et MonNb qui contiendront respectivement somme et quantité.
Tu fais une boucle "For to" qui scrute toutes tes lignes.
Dedans tu mets un test "If" correspondant aux conditions que tu as décrites.
Si la condition est vrai, tu ajoutes la valeur de différence de date à MaSom et tu incrémentes MonNb de 1.
Une fois ta boucle terminée, tu calcules ta moyenne avec MaSom / MonNb.

Ca manque d'élégance mais c'est efficace.
27/05/2015, 10h49
joe.levrai

Bonjour,

pour ajouter un peu d'élégance (dans ce monde de brutes :ptdr:), tu peux coupler l'excellente suggestion de Menhir par l'utilisation des filtres automatiques (autofilter), qui te permettra d'effectuer tes opérations sur la plage visible après filtrage

ça t'évitera de boucler sur toute ta base de données à chaque fois (et plus besoin de "If", c'est le filtrage qui s'en occupe)

bonjour,

Merci a vous deux pour vos très bons conseils. je pensai pas pouvoir faire la différence entre deux date à vrai dire.

J'ai donc ce code:

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
Sub test_heure()
 
Dim i As Long 'variable du tableau
Dim DL_SUIVI As Long 'derniere ligne suivi lot
Dim MaSom As Date 'compteur de lot trouvés(date2- date1)
Dim MonNb As Long 'nb total de test fait
Dim K As Range 'range de l'année et numéro de semaine en cours pour aller jusqu'a la dernière semaine actuelle feuille tdb
Dim L As Long 'ligne du find du range année /sem
Dim j As Long 'variable servant a passer chaque année et semaine de la feuille lsite du TdB CQ
Dim montableau() As Variant
 
Application.ScreenUpdating = False
 
 
'=====================================================================================
'              Comptage temps entre fin réelle et fin prévu
'=====================================================================================
 
'T&F
DL_SUIVI = Workbooks("SUIVI_LOTS1.xls").Worksheets("Liste T&F").Cells(Rows.Count, 3).End(xlUp).Row + 1
Set K = ThisWorkbook.Worksheets("Liste").Range("A3:C54").Find(ThisWorkbook.Worksheets("TdB").Range("O2").Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not K Is Nothing Then
L = K.Row
End If
montableau() = Workbooks("SUIVI_LOTS1.xls").Worksheets("Liste T&F").Range("A3:K" & DL_SUIVI).Value
j = L
MaSom = 0 'remise compteur 0
MonNb = 0 'remise compteur 0
For i = LBound(montableau, 1) To UBound(montableau, 1) ' 1 car car on parcours les lignes (2 pour col)
    If montableau(i, 5) <> "N/A" Then 'si pas de ste pour t&f
        If Year(montableau(i, 5)) & DatePart("ww", montableau(i, 5)) = ThisWorkbook.Worksheets("Liste").Cells(j, 1).Value And montableau(i, 11) > montableau(i, 10) And montableau(i, 11) <> VIDE Then
           MaSom = montableau(i, 11) - montableau(i, 10)
           MonNb = MonNb + 1
        End If
     End If
Next i 'pprochaine boucle dans tableau
If MaSom <> 0 And MonNb <> 0 Then
ThisWorkbook.Worksheets("TdB").Range("H18").Value = MaSom / MonNb
End If
Erase montableau 'réinitalise le tableau
 
End Sub

j'ai fait un test avec en valeur dans les cellules en date1 16/01 12:00 et en date 2 17/01 13:00

J'attends une moyenne donc de 25h mais cela me renvoie 12:30 ce qui n'est pas bon.

J'ai fait le code en mode pas à pas et pour la soustraction des deux dates à la ligne

Code:

MaSom = montableau(i, 11) - montableau(i, 10)

au lieu de renvoyer 01/01/2015 01:00 cela renvoit 31/12/1899 01:00:00

Merci de votre aide
je pense qu'une subtilité quant à la déclaration de la variable Masom m'a échappé...

27/05/2015, 11h36
vinc_bilb

Bonjour ....

Heuu .... pourquoi ne pas utiliser la fonction DateDiff ? Il me semble que c'est exactement ce qui est demandé, non?
27/05/2015, 12h14
Menhir

Pour faire les calculs, inutile de faire intervenir les formats Date.
Dans Excel, les dates sont traités simplement comme des nombres de jours (les heures étant les décimales de ces valeurs).
Traites-les comme tel.

Déclare la somme comme un simple Double.
Fais tes calculs en nombre de jours.
Il ne faut pas confondre la valeur elle-même et la façon dont elle est affichée dans Excel.

Si, à la fin, tu veux les transformer en heures, il suffira de multiplier par 24.
27/05/2015, 13h01
bboy-eazy

Citation:

Bonjour ....

Heuu .... pourquoi ne pas utiliser la fonction DateDiff ? Il me semble que c'est exactement ce qui est demandé, non? .

Bonjour vinc,

C'est pour eviter d'avoir pein de formules dans le fichier afin que ca aille au plus vite et qu'il ne bug pas.

Merci menhir pour l'aide,
J'avais tester en double, long, date mais rien ne faisait et c'est normal...; quel bobet que je suis...

j'avais fait une erreur a la ligne:

Code:

MaSom = montableau(i, 11) - montableau(i, 10)

qui devient pour la moyenne:

Code:

MaSom = MaSom + (montableau(i, 11) - montableau(i, 10))

et là ca fonctionne :)

Merci à tous
27/05/2015, 13h08
Menhir

Citation:

Envoyé par bboy-eazy

C'est pour eviter d'avoir pein de formules dans le fichier afin que ca aille au plus vite et qu'il ne bug pas.

DateDiff n'est pas une fonction Excel, c'est une fonction VBA.