Decaler recherche

Bonjour,
Je cherche une formule qui ressemblerait à une RECHERCHEV complétée d'un décalé.
J'ai un colonne de référence et une colonne heure, je souhaite dans un autre tableau ramener la première valeur heure croisée pour telle référence puis la seconde puis la troisième et si pas de quatrième laisser la cellule vide. Je ne peux pas utiliser de TCD car le tableau source et de résultats sont intouchables et il y'a bien sur beaucoup d'autres colonnes.

Référence  Heure
154        06:00
154        13:00
154        20:00
10         06:30
10         07:30
23         13:30
23         14:56
23         11:30
23         12:30
23         12:30

Résultat souhaité :

référence  heure 1  heure 2  heure 3  heure 4  heure 5
154        06:00    13:00    20:00 
10         06:30    07:30 
23         13:30    14:56    11:30    12:30    12:30

Un fichier est joint si besoin.
Merci beaucoup de votre aide, j'ai tenté des décaler avec la recherche v des index des EQUIV de partout... mais je n'y arrive pas !
D'avance merci de votre aide.

Posté le 23 juillet 2015, 11:01
par bb

signaler

Répondre

Ajouter un commentaire

Bonjour,

Voici une solution POUR AUTANT QUE VOTRE BASE SOIT TRIEE PAR REFERENCE comme dans votre exemple :

1 il faut ajouter une colonne devant votre base de données (si vous ne pouvez pas y toucher : copier la dans un autre classeur ou faites un lien d'un nouveau classeur vers votre base de données)

2 dans cette colonne (A), il faut numéroter les lignes par "série de références" par la formule :

SI(B3<>B2;(ENT(A2/100))*100+100;A2+1) (copier la formule sur toutes les lignes du tableau)

où la colonne B contient les références. Tu obtiendras ainsi une numérotation de 100 à 199 pour la référence 154 ; 200 à 299 pour la référence 10 etc.. Il va de soi que si le nombre de données dépasse 100 il faudra adapter la formule en passant à 1000 au lieu de 100.

3 nous sommes maintenant prêt à établir le tableau de conversion :

3.1 positionnons nous en A15 (colonne qui va contenir les références) et introduisons la formule :

=RECHERCHEV((LIGNE()-LIGNE($A$15)+1)*100;A3:B12;2;FAUX) que nous copions sur les 2 lignes suivantes - ou plus si plus de références)

Cette recherche va détecter les premières de chaque références (100 ; 200 ; 300) en se basant sur le numéro de la ligne exemple de la ligne 17 soit la référence 23 : (17 - 15 +1 )*100=300

3.2 il reste à compléter les heures par références par la formule :

=SI(ESTNA(RECHERCHEV(((LIGNE()-LIGNE($A$15)+1)*100)+COLONNE()-COLONNE($B$3);$A$3:$C$12;3;FAUX))=VRAI;"";RECHERCHEV(((LIGNE()-LIGNE($A$15)+1)*100)+COLONNE()-COLONNE($B$3);$A$3:$C$12;3;FAUX))

qu nous dupliquons autant de fois verticalement et horizontalement en fonction des données.

Cela semble un peu lourd mais en fait il s'agit de la même recherche mais en paramétrant en fonction de la colonne où la formule se trouve complété par un test qui vérifie si la recherche a aboutit ou s'il n'y a plus de donnée ; si(estna...) de façon à mettre un blanc plutôt que le vilain N/A.