Discussion :

[marot_r]

Bonjour à tous et toutes et merci de votre temps et de votre aide.

J'ai une feuille de calcul où je présente une liste dossier, date de dossier, personne et statut de la personne.
Actuellement j'ai une feuille de référence avec un identifiant de personne et le statut.
Un simple RECHERCHEV retrouve le statut en se basant sur l'identifiant.

Maintenant il arrive que mes personnes changent de statut.
Donc j'aurai besoin de trouver le statut à la date du dossier.

Par exemple la personne P01 a le statut

1. S01 de 2019-01-01 au 2019-01-15
2. S02 de 2019-01-16 au 2019-02-28
3. S01 de 2019-03-01 au 9999-12-31

Et je cherche à avoir :

D01	2019-01-04	P01	S01
D02	2019-01-05	P01	S01
D03	2019-02-01	P01	S02
D04	2019-01-04	P01	S01

Je sais écrire une fonction VBA pour faire cela mais je me demandais si il y avait une option avec une formule.

J'ai mis un exemple avec 1 personne et 4 dossiers mais dans la réalité, j'ai environ 10000 dossiers avec un ajout 10 dossiers en moyenne par jour répartis sur à peu près 50 personnes.
Et je ne peux pas "geler" le statut et la personne car j'ai des transferts de dossiers entre personne.

A+

[78chris]

Bonjour

Avec les données sous forme de tableaux structurés (sinon adapter) et sous réserve que les statuts soient triés par Personne et date début



Faisable aussi par PowerQuery plus simplement

[marot_r]

Merci Chris,

Pourrais-tu poster la formule comme du texte et non une image.

J'ai essayé de la refaire à partir de l'image et j'ai une erreur de syntaxe mais vu la longueur et la complexité je n'arrive pas à repérer où.

A+

[marot_r]

Re-Bonjour.

Merci Chris, grâce à ton exemple j'ai pu faire une formule qui donne le résultat attendu.

Code :

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

1
2
3
4
5
=DECALER(tbxStatut[#Tout]; 'Pointe sur la zone de données
                 EQUIV([@Matricule]; tbxStatut[Matricule]; 0) '1ère ligne dans tbxStatut qui contient le matricule cherché.
                      +NB.SI.ENS(tbxStatut[Matricule]; [@Matricule]; tbxStatut[Debut]; "<=" & [@Date]) -1;  '1ère date qui correspond dans les statuts du matricule cherché.
                 COLONNE(tbxStatut[Statut])-COLONNE(tbxStatut[Matricule]); 'Nombre de colonne de décalage pour trouver la colonne du statut par à la 1ère colonne de la zone.
                 1; 1)

Donc en résumé je trouve sur quelle ligne commence les statuts de la personne, et j'y ajoute le nombre de dates jusqu'à la date courante et j'enlève 1 pour compenser le double comptage de la ligne.
Il ne me reste plus qu'à récupérer la cellule concernée dans la colonne Statut.

A+

[78chris]

Re

Sorry pas vu le message précédent

Belle simplification !

Il y a des jours où je fais Paris Rome via Tombouctou !

[marot_r]

Bonjour.

Belle simplification !

Pour être honnête j'ai simplifié car je ne comprends pas ta méthode pour trouver la date.

Si tu as le temps peut-être pourrais-tu détailler les morceaux.

A+

[78chris]

RE

Recherche du point Y (ligne) d'une matrice correspondant au matricule obtenue ainsi
On part de l'en-tête Statut du tableau Statuts et on se décale sur la ligne du matricule recherché
En comptant le nombre de ce matricule on a la hauteur de la matrice
Pour la ligne à l'intérieur de cette matrice on cherche la date en cours dans la colonne Debut
EQUIV avec le 3ème argument à 1 prend la date <= à celle cherchée

Ton idée d'utiliser NB.SI ajouté à EQUIV permet de trouver directement la ligne dans le tableau sans besoin de passer par le sous-ensemble "matrice du matricule"

Et dire que j'expliquais hier sur un autre fil, que NB.SI permet de simplifier une recherche !

Bonne soirée

[marot_r]

Merci de l'explication.

C'est paramètre de EQUIV qui spécifie le <= que j'avais raté.

A+

[zenpbb]

Bonsoir,

La discussion est résolue, mais si besoin, voici une proposition différente qui fonctionne même si le tableau des statuts n'est pas trié, et prend en compte les éventuelles discontinuités de périodes (et si doublon, on prend la première occurrence)

Code :

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

=SIERREUR(DECALER(tbxStatut[Statut];PETITE.VALEUR(SIERREUR(LIGNE(tbxStatut[Matricule])/((tbxStatut[Matricule]=[@Matricule])*(tbxStatut[Debut]<=[@Date])*(tbxStatut[Fin]>=[@Date]));FAUX);1)-1-LIGNE(tbxStatut[[#En-têtes];[Statut]]);0;1;1);"Aucun statut pour cette date")

L'idée est de trouver la plus petite valeur de ligne satisfaisant les conditions et de récupérer le statut renseigné sur cette ligne.