Rapport de message :*
 

Re: formule de recherche !!

Titre du sujet : Re: formule de recherche !!
par JeanMarie le 05/09/2011 22:26:35

 Bonjour Mishka, Icedarts, Didier, le forum
Pour résoudre ton problème, tu as plusieurs solutions :
Pour commencer l'exemple, place les valeurs suivantes 0 ; 4 ; 1 ; 4 ; 3 ; 3 ; 0 ; 3 ; 4 ; 0 ; 0 ; 1 ; 4 ; 4 ; 0 ; 4
sur une feuille dans la plage B5:B20.
la valeur à rechercher est en cellule G4
Le résultat des formules doit donner pour la valeur 
  • 0 les lignes 5;11;14;15;19
  • 1 les lignes 7 et 16
  • 2 pas de ligne
  • 3 les lignes 9;10;12
  • 4 les lignes 6;8;13;17;18;20
Comme tu l'as compris et mentionné dans ton post, les fonctions de recherche s'arrêtent à la première occurence qu'elles trouvent.
La formule pour la première ligne sera placée en cellule E6
=EQUIV(G4;B5:B20;0)
Pour les lignes suivantes, Il faudrait pouvoir redéfinir la plage de recherche en repartant de la cellule cituée en dessous de la cellule précédement trouvée par la fonction EQUIV()
C'est-à dire pour la valeur 3, passer la référence en B10:B20; B11:B20 et B13:B20.
De manière automatique, pour définir une plage de cellules, Excel dispose de 2 fonctions, il s'agit de DECALER et d'INDIRECT.
Premièrement la fonction INDIRECT
Cette fonction transforme une référence de cellules écrite sous forme de texte une référence reconnune dans les autres fonctions.
  • =INDIRECT("B"&6) correspond à la formule =B6,
  • =INDIRECT("B5:B"&9) correspond à la formule =B5:B9,
la chaîne passée doit avoir la même forme que la référence écrite dans une formule.
la fonction =EQUIV(G4;B5:B20;0) est équivalente à la formule =EQUIV(G4;INDIRECT("B5:B20");0) et vise et versa.
Dans l'exemple, E6 a retourné le N° de la ligne de la première, il suffit de remplacer le N° de la ligne dans la référence texte.
=EQUIV(G$4;INDIRECT("B"&E6+1&":B20");0)+E6
Tu vas donc obtenir le n° de la ligne par rapport à la feuille entière, ci tu veux obtenir le n° de la ligne dans le tableau des valeurs, utilise la formule
=EQUIV(G$4;INDIRECT("B"&E6+5&":B21");0)+E6
Tu fais un glisser de la formule vers le bas.
Si tu essaies avec ces formules et la valeurs de test 4, tu vas obtenir des N° de lignes que tu ne devrais pas avoir.
En 7ème occurence la plage calculée donnera B21:B20, Excel la traduisant en B20:B21.
Pour corriger ce problème, il suffit de passer dans les formules la fin de la plage à la ligne 21.

Deuxièment la fonction DECALER
Cette fonction retourne une cellule ou une plage de cellules en fonction d'une référence de départ (Argument 1), et de valeurs représentants le décalage.
=EQUIV(G$4;DECALER(B$1:B$21;E6;0);0)+E6
=EQUIV(G$4;DECALER(B$5:B$21;F6;0);0)+F6
Tu noteras les références de départ entre les résultats pour le N° de la ligne pour la feuille entière ou pour le tableau de valeurs.
La première formule correspond au n° de la ligne pour la feuille entière et la deuxième pour le n° dans le tableau des valeurs

Tu as une autre possibilité.
Sans passer par les fonctions EQUIV et des fonctions de définition de références.
Tu rajoutes à ton tableau de valeurs une colonne qui retournera le N° de la ligne si le test d'égalité est vrai,
en D5 la formule
=SI(B5=J$4;LIGNE(B5);"")
ou
=SI(B5=J$4;LIGNE(B1);"")
la 1ère pour le n° dans la feuille entière et la 2ème pour le tableau de valeurs.
Tu noteras les références passées à la fonction LIGNE
Ensuite il suffit de récupérer dans un tableau les valeurs de cette valeurs. Pour cela on utilise la fonction PETITE.VALEUR()
PETITE.VALEUR() retourne les valeurs numériques de la plage, de la plus petite à la plus grande.
Essaye de construire la formule avec PETITE.VALEUR(), sans regarder le fichier
A mon avis, c'est la solution la plus simple.
Pour info, il y a deux autres méthodes par formules pour résoudre ton problème.
Bon courage pour la suite.