Décompte quotidien avec condition

[armitage92]

Bonsoir a tous,

J'aurais besoin de votre aide, pour une formule dont je ne trouve une solution "simple et rapide en temps d'exécution" pour avoir un résultat :

Dans le fichier exemple joint, deux colonnes sont remplis : la date, et l'ID d'un agent donné.

Je souhaiterais savoir entre deux date données, le nombre de jour ou un agentID donné a été présent

(entre le 15 Janvier et le 30 Janvier, combien de jour l'agentID MGARCIA est remonté , par exemple).

Avez vous une idée pour m'aider, car je sèche malheureusement assez fortement, et j'ai un fichier de 45000 ligne qui doit être mis a jour quotidiennement qui m'attend

 

[Modeste]

Re : Décompte quotidien avec condition

Bonjour armitage92, le forum,

Si j'ai bien compris, une proposition en pièce jointe.
Comme tu évoques le temps d'exécution, on peut imaginer que tu avais déjà essayé certaines choses ... si c'est le cas, tu aurais dû le mentionner, pour éviter qu'on ne propose "un peu plus de la même chose".
La formule proposée est un bon vieux SOMMEPROD(). Le temps d'exécution ne peut être déterminé que sur ton fichier réel ... Tu nous diras ce qu'il en est?
J'ai remis le calcul en Automatique pour faire des tests (les 2-3 que j'ai effectués semblent donner les résultats attendus si je compare le résultat avec ceux obtenus après un tri)

 

[GuillaumA]

Re : Décompte quotidien avec condition

armitage... seb ? ESG ?

 

[armitage92]

Re : Décompte quotidien avec condition

Bonsoir Modeste, et le forum

Alors je viens de voir ta proposition par sommeprod, qui malheureusement n'est pas le résultat que je recherche.

A titre d'exemple dans ce petit bout de fichier que j'ai fourni, il faudrait pour MGARCIA avoir pour résultat : 5 car il y a 5 jour ou l'agentID MGARCIA remonte.

le 99 que tu fais remonter par ce sommeprod est le nombre de fois ou MGARCIA est nommé dans l'intervalle de temps données, mais malheureusement (enfin heureusement pour moi et les stats de MGARCIA ^^), il fait plusieurs actions par jour ou il travaille, et son AgentID apparait alors plusieurs fois sur la même journée.

Pour le moment je n'ai pas trouvé de solution "simple" a mettre en oeuvre, et c'est bien mon problème (ce calcul sera incorporé dans un fichier excel de plus de 4Mo de données, avec grosso modo 2000 cellule ayant deja des formules assez lourdes, donc j'essaie de simplifier autant que possible ces dites formules :/


GuilmaumeA : non désolé, je ne suis pas Seb de l'ESG moi c'est rv dans la vie courante ^^

 

[armitage92]

Re : Décompte quotidien avec condition

Pas d'idée de quiquonque ?

 

[JNP]

Re : Décompte quotidien avec condition

Bonsoir le fil ,
En PJ, une version avec fonction personnalisé. J'ai juste tenu compte des jours de présence sans doublons. Il est possible de rajouter un filtre par date, mais comme tu crains d'alourdir le fichier et les temps de calcul, j'ai déjà fait cette proposition .
A +

 

[armitage92]

Re : Décompte quotidien avec condition

Re bonsoir le fil, et JNP

Merci pour cette piste, qui semble prometteuse

Pour mon fichier final, il me faudrait par contre pas que la plage soit "fixe" comme dans ton fichier, mais "flottante" , c'est à dire qu'il faut que la date de début soit fonction d'une date dans une cellule donnée, et la date de fin soit fonction d'une autre date donnée.

Pour expliquer mon objectif, final, c'est que via une feuille excel de données, je pourrais faire un tableau de présence de mes AgentID qui sera sur 13 mois glissants.

 

[JNP]

Re : Décompte quotidien avec condition

Re ,
Voilà, mais sur 45000 lignes, va pas falloir être pressé ...
Bon dimanche

 

[armitage92]

Re : Décompte quotidien avec condition

Merci pour l'aide JPN, je test cela tout de suite ^^

pour la longueur de mes fichiers... je sais

C'est mon combat de trouver petit a petit comment les simplifier, pour les rendre "viable"

Je suis passé d'un seul fichier qui mettait environ 2h pour ce mettre a jour, a 2 fichier qui mette environs 10 min ... donc j'y arrive petit a petit ^^

 

[armitage92]

Re : Décompte quotidien avec condition

Ah petit souci imprévu, il semblerait que la fonction n'apprécie pas que ma plage soit sur une feuille différente de la cellule ou la fonction est utilisé :x

=Présencelimité(ACD!$A$2:$B$60000;B$3;C$3;RECHERCHEV($B$5;Parametre!$A$2:$C$100;3;FAUX))

Cela ne fonctionne pas, alors que si la plage est sur la même feuille que le reste, ca fonctionne.

 

[armitage92]

Re : Décompte quotidien avec condition

Re-bonsoir

Pour rappel pour d'autres personnes, la fonction que m'a proposé JNP est la suivante :

Function PrésenceLimité(Plage As Range, DateDébut As Date, datefin As Date, Nom As String)
Application.Volatile
Dim MonDico, I As Long, J As Long
J = Plage.Rows.Count
Set MonDico = CreateObject("Scripting.Dictionary")
For I = 1 To J
If Cells(I, 2) = Nom Then
If Cells(I, 1) > DateDébut And Cells(I, 1) < datefin Then
MonDico(Format(Cells(I, 1), "ddmmyy")) = "toto"
End If
End If
Next I
PrésenceLimité = MonDico.Count
End Function

J'ai compris pourquoi elle dysfonctionne actuellement pour mon utilisation, (ma "Plage" de cellule est sur une feuille autre que les cellules ou j'utilise cette fonction).

Vu que pour mon utilisation, la fonction ainsi créé visera toujours la plage "ACD!$A$2:$B$60000" et l'appellera toujours depuis d'autres feuilles du classeur, peut être y a t'il moyen d'integrer cette variable dans la fonction plutôt que de l'appeller dans l'intitulé de la fonction au moyen de la variable Plage, et de faire que la fonction fonctionne depuis une autre feuille que celle de la plage de cellule étudié.

Mais je ne trouve pas comment le faire

 

[JNP]

Re : Décompte quotidien avec condition

Re ,
Ça ne faisait pas partie du cahier des charges ...
Non, c'est surtout qu'au départ, j'utilisais directement la plage envoyée, alors que pour pouvoir vérifier les dates, je suis passé en référence de feuille ...

[COLOR=blue]Function[/COLOR] PrésenceLimité(Plage [COLOR=blue]As[/COLOR] Range, DateDébut [COLOR=blue]As Date[/COLOR], datefin [COLOR=blue]As Date[/COLOR], Nom [COLOR=blue]As String[/COLOR])
Application.Volatile
[COLOR=blue]Dim[/COLOR] MonDico, I [COLOR=blue]As Long[/COLOR], J [COLOR=blue]As Long[/COLOR]
J = Plage.Rows.Count
[COLOR=blue]Set[/COLOR] MonDico = CreateObject("Scripting.Dictionary")
[COLOR=blue]With[/COLOR] Plage
[COLOR=blue]For[/COLOR] I = 1 [COLOR=blue]To[/COLOR] J
[COLOR=blue]If[/COLOR] .Cells(I, 2) = Nom [COLOR=blue]Then[/COLOR]
[COLOR=blue]If[/COLOR] .Cells(I, 1) >= DateDébut [COLOR=blue]And[/COLOR] .Cells(I, 1) < datefin [COLOR=blue]Then[/COLOR]
MonDico(Format(.Cells(I, 1), "ddmmyy")) = "toto"
[COLOR=blue]End If[/COLOR]
[COLOR=blue]End If[/COLOR]
[COLOR=blue]Next[/COLOR] I
[COLOR=blue]End With[/COLOR]
PrésenceLimité = MonDico.Count
[COLOR=blue]End Function[/COLOR]

devrait fonctionner, mais attention avec ton RechercheV, les noms sont rentrés avec une ' devant ...
D'autre part, j'ai modifié pour la date de début en >=, dès fois qu'il y en ait un qui pointe à 00:00 , et surtout, comme tout le calcul est basé sur des dates avec les heures, la date de fin doit être 16/01/2009 00:00 pour prendre le 15/01/09 inclus... Si tu veux pouvoir saisir la date de fin incluse, il faut modifier

[COLOR=#0000ff]If[/COLOR] .Cells(I, 1) >= DateDébut [COLOR=blue]And[/COLOR] .Cells(I, 1) < datefin + 1 [COLOR=blue]Then[/COLOR]

mais mathématiquement, c'est faux . Pour faire propre, tu devrais saisir "15/01/2009 23:59" !
Bon dimanche

 

[armitage92]

Re : Décompte quotidien avec condition

Merci beaucoup, JPN, ca fonctionne parfaitement


Je peux faire le gourmand ? en supplément de cette fonction, il m'en faudrait une qui me dise le nombre d'agent qui ont travaillé sur une période de temps donnée (oui je sais, j'abuse... )

L'objectif final, c'est que :

Pour un agent donné, je connais maintenant le nombre d'action qu'il a traité (merci SOMMEPROD), le nombre de jour qu'il a travaillé grace a la fonction réalisé juste au dessus, et donc par conséquent, je connais le nombre d'action par jour travaillé pour un agent donné.

Le but est de pouvoir le comparer avec le nombre d'action global (que SOMMEPROD sait me donner), et le nombre de jour de présence de l'ensemble des agents sur la période données, pour pouvoir connaitre le nombre d'action par jour travaillé sur la prestation.

Ainsi je pourrais voir si l'agent séléctionné est plus productif, ou moins productif, que la moyenne de la prestation.

 

[armitage92]

Re : Décompte quotidien avec condition

Bon j'ai voulu tenté sans, mais la taille de la formule pour ma cellule est trop longue si je tente d'additionner la fonction de jnp qui vas pour un collaborateur à chaque collaborateur

 

[JNP]

Re : Décompte quotidien avec condition

Re ,
C'est vraiment de la gourmandise ...
Fais gaffe, trop de stat tue la stat ...

Function PrésenceEffectifs(Plage As Range, DateDébut As Date, datefin As Date)
Application.Volatile
Dim MonDico, I As Long, J As Long
J = Plage.Rows.Count
Set MonDico = CreateObject("Scripting.Dictionary")
With Plage
For I = 1 To J
If .Cells(I, 1) >= DateDébut And .Cells(I, 1) < datefin Then
If .Cells(I, 2) <> "" Then
MonDico(CStr(.Cells(I, 2))) = "toto"
End If
End If
Next I
End With
PrésenceEffectifs = MonDico.Count
End Function

A +