Identifier des dates continues avec critères

[cyko_benz]

Bonjour à tous,

J'ai cherché sur le forum. Mais ne sachant pas exactement comment formuler ma demande, je ne sais pas quoi chercher ... .

Bon, j'essaie de résumer. J'extrais des données brutes d'une application tierce. Chaque ligne représente une date (=un jour) où on retrouve : une colonne avec l'EmplID (identifiant unique), date, jours "D" et des jours "OFF". Je dois déterminer des périodes (date MIN et date MAX) où les dates sont consécutives ET où il y a au moins un jour "D".

J'ai mis en annexe un extrait du fichier (le vrai fichier compte presque 100.000 enregistrements). Pour l'instant, j'ai identifié les dates consécutives en:
> triant sur les EmplID et sur les dates
> attribuant un numéro de rang (cellule du dessus +1)
> en soustrayant la date (format numérique) par le numéro de rang.
Voir colonne E et F du fichier en annexe.

Là où je coince c'est pour isoler les dates (colonne F) identiques qui contiennent au moins un jour "D".

Je ne sais pas si j'attaque le problème de la bonne façon, ni si une solution est possible via des formules Excel ou du VBA ; mais j'espère que vous pourrez éclairer ma lanterne avec vos supers connaissances Excel
Pour plus de clarté, j'ai mis en colonne G le résultat souhaité.

Merci d'avance pour vos conseils
Benoît,

 

[Victor21]

Re : Identifier des dates continues avec critères

Bonsoir, cyko_benz.

Solution partielle, en G2, à recopier vers le bas :

=SI(ET(C2="D";OU(NON(ESTNA(RECHERCHEV(B2+1;B3:B$364;1;0)));NON(ESTNA(RECHERCHEV(B2-1;B1:B$1;1;0)))));"Période";"")

mais qui ne prend en compte que les dates où il y a un D.

Je reviendrai si je trouve la solution pour compléter votre demande.

 

[cyko_benz]

Re : Identifier des dates continues avec critères

Bonsoir Victor,

Merci d'avoir consacré du temps pour me dépanner. Ta solution, basée sur un VLOOKUP, faisait planter mon Excel sur le gros dataset (100.000 records) mais par contre, elle m'a fait réfléchir

Je trouvé une formule qui fonctionne (plus ou moins) mais qui n'est pas très propre. En gros c'est une longe série de IF imbriqué qui vérifie si les x (de 1 à 10) cellules au dessus et en dessous correspondent à la date pré calculée ET si la colonne Jour D (de la ligne x) contient bien D. Il n'est pas possible (à ma connaissance) d'avoir 10 jours OFF entre un jour T. Pour mettre en évidence les jours D uniques, j'ai ajouté un dernier IF.

Cala donne ça:

=IF(AND(F7=F8;C8="D");"Période";IF(AND(F7=F9;C9="D");"Période";IF(AND(F7=F10;C10="D");"Période";IF(AND(F7=F11;C11="D");"Période";IF(AND(F7=F12;C12="D");"Période";IF(AND(F7=F13;C13="D");"Période";IF(AND(F7=F14;C14="D");"Période";IF(AND(F7=F15;C15="D");"Période";IF(AND(F7=F16;C16="D");"Période";IF(AND(F7=F17;C17="D");"Période";IF(AND(F7=F6;C6="D");"Période";IF(AND(F7=F5;C5="D");"Période";IF(AND(F7=F4;C4="D");"Période";IF(AND(F7=F3;C3="D");"Période";IF(AND(F7=F2;C2="D");"Période";IF(AND(F7=F1;C1="D");"Période"; IF(C7="D";"Un jour";"")))))))))))))))))

C'est sur le dernier IF que cela coince ; en effet, parfois il indique "Un jour" alors que cela fait partie d'une "Période". Est-ce qu'il y a un ordre d'exécution dans les IF imbriqué ? Je pensais que c'était le premier IF qui donnait un résultat positif qui affichait le résultat ...

Dans tous les cas, je peux m'en sortir avec cela ! Je vais concatener le résultat de la formule avec la date pré-calculée ; je pourrais facilement faire un TCD avec Max et Min.
Si c'est une opération à répéter plusieurs fois je réfléchirai à un code VBA plus propre (mais là c'est pour demain 8h ^_^)

Encore merci pour tes conseils !
Bonne soirée,
Benoît,

Bonsoir, cyko_benz.

Solution partielle, en G2, à recopier vers le bas :


mais qui ne prend en compte que les dates où il y a un D.

Je reviendrai si je trouve la solution pour compléter votre demande.

 

[Victor21]

Re : Identifier des dates continues avec critères

Re,

La même en plus court en G7 :

=SI(OU(ET(F7=F1;C1="D");ET(F7=F2;C2="D");ET(F7=F3;C3="D");ET(F7=F4;C4="D");ET(F7=F5;C5="D");ET(F7=F6;C6="D");ET(F7=F8;C8="D");ET(F7=F9;C9="D");ET(F7=F10;C10="D");ET(F7=F11;C11="D");ET(F7=F12;C12="D");ET(F7=F13;C13="D");ET(F7=F14;C14="D");ET(F7=F15;C15="D");ET(F7=F16;C16="D");ET(F7=F17;C17="D"));"Période";SI(C7="D";"Un jour";""))

Renvoie le même résultat avec le même problème.
Peut-être tester en colonne B le J+1 de la ligne suivant un "D" en colonne C et le J-1 de la ligne le précédant.