Discussion :

[MLvba]

Bonjour,

J'aurais besoin d'aide après plusieurs heures de recherches.

J'ai un tableau de données, avec des clients qui peuvent être similaires et ayant des articles différents, des dates de livraisons et des dates prévisionnelles.
Nb : lorsque la date de livraison n'est pas encore envoyé j'ai la valeur "Non lancé"
Je souhaiterais pour cela : compter le nombre d'articles en comparant la date de livraison et la date prévisionnelle, par client et par mois.
Ainsi j'ai trois cas que je voudrais aussi différencier:
la date de livraison > à la date prévisionnelle
la date de livraison < à la date prévisionnelle
la date de livraison = "Non lancé"

Ma première solution a été d'attribuer des couleurs :
Si la date de livraison > à la date prévisionnelle alors je mets ma colonne date de livraison en rouge
Si la date de livraison < à la date prévisionnelle alors je mets ma colonne date de livraison en vert
Si la date de livraison = "Non lancé" alors je mets ma colonne date de livraison en violet

Et ensuite je crée une fonction qui me permet de compter le nombre de couleurs dans une plage de données et je reporte la valeur dans un tableau.
Exemple de ma fonction :

Code :

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

=nbcoul(L4:L969;"Rouge")

Le problème c'est que c'était trop long.

Deuxième solution est de passer par la fonction SOMMEPROD (où je bornes mes dates du 01/01/2016 aux 31/01/2016 pour avoir chaque mois, ensuite je choisis ma comparaison, j'exclus les "non lancés" et je choisis mon client)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Dim sh2 as Worksheets
Set sh2 = Sheets("Feuil2")
'la date de livraison < à la date prévisionnelle (Vert)
With sh2
    .Cells(31, 6).FormulaR1C1 = "=SUMPRODUCT((Feuil1!R4C14:R2500C14>=""01/01/2016""*1)*(Feuil1!R4C12:R2500C12<>""Non lancé"")*(Feuil1!R4C12:R2500C12<=Feuil1!R4C14:R2500C14)*(Feuil1!R4C1:R2500C1=""Client1"")*(Feuil1!R4C14:R2500C14<=""31/01/2016""*1))"
.
.
.
    .Cells(55, 6).FormulaR1C1 = "=SUMPRODUCT((Feuil1!R4C14:R2500C14>=""01/01/2018""*1)*(Feuil1!R4C12:R2500C12<>""Non lancé"")*(Feuil1!R4C12:R2500C12<=Feuil1!R4C14:R2500C14)*(Feuil1!R4C1:R2500C1=""Client1"")*(Feuil1!R4C14:R2500C14<=""31/01/2018""*1))"
End With

Et j'ai écris ça pour chaque mois de 01/01/2016 à 01/01/2018 et de 31/01/2016 à 31/01/2018, pour mes trois cas et pour une partie de mes clients.
Cependant ce programme fonctionne très bien mais il est long à écrit et je voudrais l'étendre jusqu'en 2020 minimum, je voudrais donc passer par une boucle pour diminuer sa longueur .
J'ai essayé de passer par un filtre avancé mais je n'ai pas réussit à imbriquer la fonction "count".
J'ai aussi essayé avec un countIf mais je n'ai pas réussit à faire une boucle dans mes critères et je n'obtiens pas le résultat désiré.

J'espère que vous m'avez suivit :
Merci

[riaolle]

Bonjour,
En effet, ce serait plus logique de le faire avec CountIfs. Qu'est-ce que tu n'arrives pas à faire avec la boucle ?

Code :

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

1
2
3
4
5
With sh2
    For i = 2016 to 2020
        .Cells(i - 1985, 6).FormulaR1C1 = "=SUMPRODUCT((Feuil1!R4C14:R2500C14>=" & Format(DateSerial(i, 1, 1), "dd/mm/yyyy") & "*1)*(Feuil1!R4C12:R2500C12<>""Non lancé"")*(Feuil1!R4C12:R2500C12<=Feuil1!R4C14:R2500C14)*(Feuil1!R4C1:R2500C1=""Client1"")*(Feuil1!R4C14:R2500C14<=" & Format(DateSerial(i, 12, 31), "dd/mm/yyyy") & "*1))"
    Next i
End With

DateSerial(2016, 12, 31) représente la date du 31/12/2016.
Format(DateSerial(2016, 12, 31), "dd/mm/yyyy") permet de la mettre sous le format jour/mois/année
A adapter avec CountIfs, car CountIfs me paraît plus approprié dans ce cas là.

[MLvba]

Merci pour cette réponse rapide. mais dans ton programme tu incrémentes les années et non les mois alors qu'ils me faut les deux, j'ai donc fait une double boucle mais la valeur renvoyé et égale à 0 alors que j'ai bien des articles à cet intervalle de dates.

Code :

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

1
2
3
4
5
    For i = 2016 To 2018
        For j = 1 To 12
    .Cells(j + 30, 6).FormulaR1C1 = "=SUMPRODUCT((Feuil1!R4C14:R2500C14>=" & Format(DateSerial(i, j, 1), "dd/mm/yyyy") & "*1)*(Feuil1!R4C12:R2500C12<>""Non lancé"")*(Feuil1!R4C12:R2500C12<=Feuil1!R4C14:R2500C14)*(Feuil1!R4C1:R2500C1=""Client1"")*(Feuil1!R4C14:R2500C14<=" & Format(DateSerial(i, j, 31), "dd/mm/yyyy") & "*1))"
        Next j
Next i

Avec : CountIfs j'ai des problèmes sur mes plages de données et sur l'écriture de mes critères.

[riaolle]

Attention, en écrivant :

Code :

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

1
2
3
4
5
For i = 2016 To 2018
        For j = 1 To 12
            .Cells(j + 30, 6).FormulaR1C1 = ...
        Next j
Next i

chaque année, tu réécris par dessus l'année precedente, car tu ne prends en compte que l'indice j, tu devrais plutôt écrire quelque chose comme :

Code :

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

1
2
3
4
5
For i = 2016 To 2018
        For j = 1 To 12
            .Cells(i - 2016 + j + 30, 6).FormulaR1C1 = ...
        Next j
Next i

[MLvba]

D'accord je prends note, mais j'ai toujours comme valeur renvoyé = 0.
Pour moi cela viendrait de mes critères dans la formule mais je ne le trouves pas.