formule de recherche !!
#1
Aspirant XLPages

Inscription: 07/03/2009

Messages: 35

Système d'exploitation:
PC
Version Excel utilisée:
2003
Posté le : 01-09-2011 17h49
Bonjour le Forum!!

Je recherche une formule permettant de rechercher toutes les lignes possédants une même valeur !!

J'ai essayé avec RechercheV et index equiv mais ni l'un ni l'autre ne me donnent TOUTES les lignes mais la première.

Une bonne âme pour m'aider??


D'avance merci.

Michel.G
Hors Ligne
Rapport   Haut 

Re: formule de recherche !!
#2
Régulier XLPages

Inscription: 02/10/2008

Messages: 56

Système d'exploitation:
PC
Version Excel utilisée:
365
Posté le : 01-09-2011 20h46
Bonsoir,

Avec crtl + F, tu rentres la valeur recherchée et tu cliques sur "rechercher tout"
En dessous tu devrais avoir la liste des cellules ou se trouve la valeur.
Hors Ligne
Rapport   Haut 

Re: formule de recherche !!
#3
Accro XLPages

Inscription: 09/05/2007
De Courcelles-les-Montbéliard

Messages: 286

Système d'exploitation:
PC & Mac
Version Excel utilisée:
Mac 2004 - 2011, et PC 2003 - 2010
Posté le : 05-09-2011 22h26
 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.



Pièce jointe:
zip Mishka num ligne.zip   [ Taille: 6.12 Ko - Téléchargements: 474 ]
Hors Ligne
Rapport   Haut 


Vous pouvez voir les sujets.
Vous ne pouvez pas débuter de nouveaux sujets.
Vous ne pouvez pas répondre aux contributions.
Vous ne pouvez pas éditer vos contributions.
Vous ne pouvez pas effacez vos contributions.
Vous ne pouvez pas ajouter de nouveaux sondages.
Vous ne pouvez pas voter en sondage.
Vous ne pouvez pas attacher des fichiers à vos contributions.
Vous ne pouvez pas poster sans approbation.

[Recherche avancée]


Qui consulte actuellement ce sujet ?   1 Utilisateur(s) anonymes