comportement de rechercheV sur une plage de valeurs identiques

[Nammalvar]

Bonjour à tous,

je suis relativement novice en excel, et les tentatives que je fais pour utiliser recherchev me laissent un peu perplexe.

D'abord, une énigme sur son fonctionnement que je comprends mal. Ci-dessous un tableau d'exemple très simple, tellement simple que je me permets de le mettre ici même plutôt qu'en pièce jointe (A2 : C25) :
valeurcherchee5

valeurcherchee1 ATROUVER1
valeurcherchee2 ATROUVER2
valeurcherchee3 ATROUVER3
valeurcherchee4 ATROUVER4
valeurcherchee5 ATROUVER5
valeurcherchee5 ATROUVER6
valeurcherchee5 ATROUVER7
valeurcherchee5 ATROUVER8
valeurcherchee5 ATROUVER9
valeurcherchee5 ATROUVER10
valeurcherchee5 ATROUVER11
valeurcherchee5 ATROUVER12
valeurcherchee5 ATROUVER13
valeurcherchee6 ATROUVER14
valeurcherchee7 ATROUVER15
valeurcherchee8 ATROUVER16
valeurcherchee9 ATROUVER17
valeurcherchee10 ATROUVER18
valeurcherchee11 ATROUVER19
valeurcherchee12 ATROUVER20
valeurcherchee13 ATROUVER21
ATROUVER5

Vous remarquerez que valeurcherchee5 est répétée 9 fois. La premiere ligne du tableau contient la valeur texte cherchée dans D2 (valeurcherchee5), la dernière ligne contient la formule.
La première ligne du tableau avec valeurcherchee5 contient ATROUVER5 en deuxième colonne; la dernière ligne avec valeurcherchee5 contient ATROUVER13 en deuxième colonne.

Or si je mets comme formule =RECHERCHEV(D2;A4:B24;2;FAUX) j'obtiens ATROUVER5 et si je mets =RECHERCHEV(D2;A4:B24;2;VRAI) j'obtiens ATROUVER13 !

Intuitivement, rien dans le concept "valeur proche" ne justifie un tel comportement non ? la première ligne trouvée devrait sortir dans les deux cas ? cela est-il documenté ? j'avoue que je n'en saisis pas la logique. Les versions antérieures d'excel font-elles pareil ?

J'en viens à mon autre question : je voudrais sur la base d'un tel tableau (avec des plages de valeurs texte recherché identiques et contigües) trouver les uniques ATROUVER non vides (i.e. dans le tableau ci-dessus trouver ATROUVER8 si c'est la seule valeur non vide en regard de valeurcherchee5).
Je ne doute pas que je trouverai une solution par moi-même, mais je n'ai pas encore le cerveau excellifié, et je suis sûr qu'on me donnera ici des solutions élégantes qui me feront vite progresser

Merci d'avance pour vos explications et conseils.

 

[job75]

Re : comportement de rechercheV sur une plage de valeurs identiques

Bonjour Nammalvar, bienvenue sur XLD,

(...) cela est-il documenté ? (...) Les versions antérieures d'excel font-elles pareil ?

Oui, toutes les versions Excel fonctionnent ainsi.

Voyez l'aide Excel (touche F1) sur RECHERCHEV et lisez attentivement ce qui est dit sur son 4ème argument.

A+

 

[chris]

Re : comportement de rechercheV sur une plage de valeurs identiques

Bonjour

Les valeurs proches sont plutôt utilisées pour les valeurs numériques car un texte n'a pas de valeur.

On utilise valeur exacte (4ème argument à faux) quand on cherche bien la 1ère ligne correspondante. Dans ce cas le tableau de recherche peut être dans n'importe quel ordre. En cas de doublons, comme ici, c'est la 1ère occurrence qui est trouvée indépendamment des valeurs des autres colonnes.

Valeur proche sert quand on cherche à situer une valeur dans une fourchette : exemple un montant dans une grille de remise selon montant. Dans ce cas la grille doit être ordonnée de façon croissante sur la base de la 1ère colonne et c'est la ligne correspondant à la valeur immédiatement inférieure à la valeur recherchée qui sera trouvée.

Dans le cas de texte, comme il n'y a pas de valeur intrinsèque, c'est la dernière occurrence qui est trouvée.

Dans la mesure où tu cherches sur la base de 2 colonnes, et où il faut en plus vérifier l'unicité, RECHERCHEV n'est pas adapté.

Un exemple plus concret sou forme de fichier Excel et de valeurs plus plus réelles serait utile pour t'orienter.

EDit : Coucou job75. Le temps de rédiger... tu avais réagi .

 

[Nammalvar]

Re : comportement de rechercheV sur une plage de valeurs identiques

Ah ooooook, "proche" signifie aussi contigüe dans ce contexte ! je lisais proche uniquement au sens de valeur, j'avais pas percuté. Merci beaucoup.

 

[chris]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re

Non proche se réfère bien à la valeur pas à la position.

Je n'ai pas parlé de valeur contiguë dans ma réponse

 

[job75]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re,

Pour votre 2ème question voyez le fichier joint et cette formule matricielle en E2 :

=INDEX(B$2:B$23;EQUIV(1;(A$2:A$23=D2)*ESTTEXTE(B$2:B$23);0))

A valider par Ctrl+Maj+Entrée et tirer vers le bas.

Si vous ne savez pas ce qu'est une formule matricielle baladez-vous sur le forum.

Edit : coucou chris

A+

 

[Nammalvar]

Re : comportement de rechercheV sur une plage de valeurs identiques

Alors je ne comprends toujours pas :

valeur_proche Facultatif. Représente une valeur logique indiquant si vous souhaitez que la fonction RECHERCHEV recherche une valeur exacte ou voisine de celle que vous avez spécifiée :
Si l’argument valeur_proche est VRAI ou omis, une donnée exacte ou proche est renvoyée

La correspondance exacte étant trouvée tout de suite, pourquoi continuer ?

ça va pas m'empêcher de dormir, mais ça me chiffonne.

 

[Nammalvar]

Re : comportement de rechercheV sur une plage de valeurs identiques

Quelle célérité ! je suis très impressionné

Je travaille sur un import de fichier xml qui me rend un beau tableau plein de trous que je voudrais synthétiser sur une autre page.
Les données sont malheureusement confidentielles, mais je peux travailler à en faire un simili s'il le faut. Tous les champs sans exception sont du texte.
J'ai tenté la voie du TcD aussi, mais elle est semée d'embûches (vide)

Je vais travailler déjà sur la formule de job75.

Merci encore.
Nammalvar

 

[chris]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re

Question peut-être idiote mais si la synthèse doit être faite sur une autre page et si c'est one shot, un filtre automatique ou avancé sur le valeurs de la seconde colonne et un copier coller peuvent aussi faire l'affaire.

Le TCD sert à faire des calculs statistiques mais cela ne semble pas être ton objectif final (qui reste cependant flou)...

 

[Nammalvar]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re,

Pour votre 2ème question voyez le fichier joint et cette formule matricielle en E2 :
[...]
Si vous ne savez pas ce qu'est une formule matricielle baladez-vous sur le forum.

Oh oui ! puissant et élégant cela ! c'est un début qui me convient parfaitement. La deuxième phase serait de concaténer les valeurs multiples en les séparant par un retour chariot ; dans votre exemple pour valeurcherchee5 je voudrais obtenir "ATROUVER8[CR]ATROUVER10" dans la cellule résultat de la colonne "Trouvée".
Après j'aurais d'autres questions d'automatisation de traitement (à Chris : non ce n'est pas one shot, mais vos conseils ne sont pas tombés dans l'oreille d'un sourd) avec macros, mais pour ça je ferai d'abord une version hem déclassifiée que je posterai ici, quand je serai plus au clair de ce que je veux obtenir.

Très cordialement,

Nammalvar

 

[job75]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re,

La deuxième phase serait de concaténer les valeurs multiples en les séparant par un retour chariot ; dans votre exemple pour valeurcherchee5 je voudrais obtenir "ATROUVER8[CR]ATROUVER10" dans la cellule résultat de la colonne "Trouvée".

C'est un problème très classique avec les formules matricielles, il y a de nombreux exemples sur le forum.

Voyez les fichiers (2) et (2 bis) où les 3 premières occurrences sont concaténées en colonne E.

Les formules utilisent SIERREUR qui ne fonctionne qu'à partir d'Excel 2007.

A+

 

[job75]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re,

Cette formule fonctionne sur toute version Excel :

=SI(MIN(matrice);INDEX(B:B;MIN(matrice));"")&SI(NB(matrice)>1;CAR(10)&INDEX(B:B;PETITE.VALEUR(matrice;2));"")&SI(NB(matrice)>2;CAR(10)&INDEX(B:B;PETITE.VALEUR(matrice;3));"")

Fichier (3).

A+

 

[Nammalvar]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re,

Cette formule fonctionne sur toute version Excel :

=SI(MIN(matrice);INDEX(B:B;MIN(matrice));"")&SI(NB(matrice)>1;CAR(10)&INDEX(B:B;PETITE.VALEUR(matrice;2));"")&SI(NB(matrice)>2;CAR(10)&INDEX(B:B;PETITE.VALEUR(matrice;3));"")

Fichier (3).

A+

Bonjour job75,

ça semble velu à la premières lecture, mais c'est effectivement plutôt simple quand on décompose la logique : matrice contient la liste des lignes qui satisfont les critères, et on applique le traitement qui convient le nombre de fois qu'il faut. Au poil, si j'ose dire ! par contre il faut connaître à l'avance le nombre d'occurences maximum. Quid si on ne le connaît pas ? on doit passer par une macro, ou bien une astuce permet de s'en sortir là aussi ?

J'ai frappé à la bonne porte ma foi.

Nammalvar

 

[job75]

Re : comportement de rechercheV sur une plage de valeurs identiques

Bonjour Nammalvar,

(...) par contre il faut connaître à l'avance le nombre d'occurences maximum. Quid si on ne le connaît pas ? on doit passer par une macro, ou bien une astuce permet de s'en sortir là aussi ?

Oui, l'astuce consiste à ne pas concaténer

Mais à tirer cette formule en E2 vers le bas puis vers la droite jusqu'à ce qu'il n'y ait que des cellules "vides" de textes :

=SI(COLONNES($E2:E2)>NB(matrice);"";INDEX($B:$B;PETITE.VALEUR(matrice;COLONNES($E2:E2))))

Pour la définition de matrice bien mettre les signes $ pour les colonnes.

Fichier (4).

A+

 

[job75]

Re : comportement de rechercheV sur une plage de valeurs identiques

Re,

Sinon en effet avec cette fonction VBA plus de problème :

Function MaRecherche$(valeur, r As Range, col%, sep$)
For Each r In r.Rows
  If r.Cells(1) = valeur And r.Cells(col) <> "" Then _
    MaRecherche = MaRecherche & sep & r.Cells(col)
Next
MaRecherche = Mid(MaRecherche, Len(sep) + 1)
End Function

Le code doit être placé dans un module standard (Alt+F11).

Fichier joint, voyez comment cette fonction est utilisée en colonne E.

A+