IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Calcul moyenne entre deux dates selon condition par vba [Toutes versions]


Sujet :

Macros et VBA Excel

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre chevronné
    Homme Profil pro
    autodidacte
    Inscrit en
    Novembre 2013
    Messages
    517
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : autodidacte
    Secteur : Industrie

    Informations forums :
    Inscription : Novembre 2013
    Messages : 517
    Par défaut Calcul moyenne entre deux dates selon condition par vba
    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 : 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
    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

  2. #2
    Expert éminent Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Par défaut
    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.

  3. #3
    Expert confirmé

    Homme Profil pro
    Curieux
    Inscrit en
    Juillet 2012
    Messages
    5 169
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Curieux
    Secteur : Arts - Culture

    Informations forums :
    Inscription : Juillet 2012
    Messages : 5 169
    Billets dans le blog
    5
    Par défaut
    Bonjour,

    pour ajouter un peu d'élégance (dans ce monde de brutes ), 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)

  4. #4
    Membre chevronné
    Homme Profil pro
    autodidacte
    Inscrit en
    Novembre 2013
    Messages
    517
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : autodidacte
    Secteur : Industrie

    Informations forums :
    Inscription : Novembre 2013
    Messages : 517
    Par défaut
    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 : 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
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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é...

  5. #5
    Membre Expert
    Homme Profil pro
    Ingénieur développement matériel électronique
    Inscrit en
    Septembre 2013
    Messages
    783
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Ingénieur développement matériel électronique
    Secteur : High Tech - Électronique et micro-électronique

    Informations forums :
    Inscription : Septembre 2013
    Messages : 783
    Par défaut
    Bonjour ....

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

  6. #6
    Expert éminent Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Par défaut
    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.

  7. #7
    Membre chevronné
    Homme Profil pro
    autodidacte
    Inscrit en
    Novembre 2013
    Messages
    517
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : autodidacte
    Secteur : Industrie

    Informations forums :
    Inscription : Novembre 2013
    Messages : 517
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    MaSom = montableau(i, 11) - montableau(i, 10)
    qui devient pour la moyenne:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    MaSom = MaSom + (montableau(i, 11) - montableau(i, 10))
    et là ca fonctionne

    Merci à tous

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Requête sql server pour calculer moyenne entre deux dates
    Par JérômeKGS dans le forum Développement
    Réponses: 2
    Dernier message: 17/04/2015, 12h04
  2. [XL-2013] Calcule la somme entre deux dates en condition sur 6 mois
    Par undo74 dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 24/07/2014, 16h30
  3. Réponses: 8
    Dernier message: 15/12/2008, 13h29
  4. Valeur calculée difference entre deux dates #NOMBRE!
    Par gracouille dans le forum SharePoint
    Réponses: 3
    Dernier message: 13/06/2008, 11h59
  5. calcul délais entre deux dates avec plages horaires
    Par leila eco dans le forum SAP Crystal Reports
    Réponses: 3
    Dernier message: 03/01/2007, 16h51

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo