recherche valeur dans une matrice

[Thomas J.]

bonjour

comment faire pour que dans excel, une fonction retourne toutes les occurences trouvées d'un certain parametre dans une plage donnée.

en effet, rechercheV et rechercheH ne sortent que la premiere occurence trouvée et moi je souhaite avoir la liste exhaustive de toutes ces occurences.

je m'explique : en gros j'ai un tableau comportant des doublons ex.:

A B
peugeot 307
peugeot 407
peugeot 607
renault laguna
citroen xsara


je veux quand je lui donne peugeot comme parametre qu'il me ressorte :

307
407
607

merci !

junty94

 

[Jean-Marie]

Bonjour Thomas

Télécharge ce fichier Lien supprimé

@+Jean-Marie

 

[Monique]

Bonjour,

Avec les fonctions Index Equiv et Decaler.

 

[Thomas J.]

Bonjour,

Tout d'abord merci pour le fichier joint car il est extrêmement bien fait et j'ai appris plusieurs trucs très utiles en quelques minutes !

Malheureument, je n'arrive pas à trouver la réponse à mon problème.

Pour mieux comprendre ma question, cf. le fichier joint.

Ce que je veux c'est qu'en dessous de 206, il m'affiche 307 et 407.

J'ai une idée, mais je ne sais pas si elle est bonne : c'est en fait de modifier dynamiquement la plage de recherche de RECHERCHEV en reduisant a partir de la ligne de la précédente recherche. Ainsi, ici, pour afficher le second resultat, on pourrait refaire RECHERCHEV sur la plage A6:B9.
Mais je n'arrive pas a entrer une adresse dynamique dans recherchev.

Merci pour votre aide !

 

[Thomas J.]

Merci !

En fait, je crois que le fichier de Monique contient exactement ce que je veux.

Je suis en train d'étudier tout ca.

Merci beaucoup !

@ bientôt !

 

[Monique]

Re,

Dans ton classeur, l'option "calcul sur ordre" était cochée.
C'est peut-être pour ça que tu n'as pas réussi à adapter ?

Dans le classeur joint, tu as plusieurs solutions :
celle de ce matin,
recherchev sans gestion des #N/A quand la liste est finie,
recherchev avec gestion des #N/A ,
recherchev sans gestion des #N/A mais avec un format conditionnel pour masquer les #N/A.
C'est la formule Decaler() qui fait que la plage de recherche change à chaque fois qu'un occurrence est trouvée.

Tu aurais intérêt à ne pas mettre ton tableau de recherche juste au-dessous du tableau de données.
Le décalage risque de faire empiéter le tableau du haut sur celui du bas.

 

[Thomas J.]

Bonjour,

Merci pour votre aide.

En réalité, ce que je souhaite faire c'est un programme d'aide pour les jeux de lettres comme le pendu par exemple.

Dans mon classeur, j'ai 2 feuilles. L'une ("dico") contient des centaines de milliers de mots (sur 4 colonnes). L'autre contient le formulaire de saisie.

On lui donne le nombre de lettres minimales et le nombre de lettres maximales ainsi que les premieres lettres et il nous sort un nombre suffisant de resultats (mettons 30 au maximum).

Concretement :

nb de lettres minimum = 3
nb lettres maximum = 5
debut = ta

il doit m'afficher par exemple :

table
tas
talc
tamis
taupe
taie
.
.
.

etc.

je vous joint le fichier excel ou j'ai volontairement retiré des mots du dictionnaire pour l'alléger. (normalement, il y a les colonnes A qui sont remplies : il y a 240.000 mots !).

merci de m'aider car je n'arrive pas a integrer vos formules dans ce document.

merci!

Thomas.

 

[Monique]

Re,

Le principe est le même, mais il a fallu faire des colonnes intermédiaires dans la feuille "Dico"
Tu as encore le choix entre 2 systèmes différents.
Il y a 2 formules principales, mais vu le nombre de données, tu aurais intérêt à donner un nom aux formules (ça accélère la vitesse de recalcul et ça utilise moins d'octets)
J'en ai nommé une partie, tu verras.
Problème : comment faire avec les accents ?

 

[Thomas J.]

Super Monique !

C'est bien çà que je voulais ! mais juste une question, pour la colonne G tu as "nommé" les formules. Ou définit-on les formules et leurs noms ?

Merci beaucoup !

 

[Monique]

Re,

Pour donner un nom à une formule : barre de menu - Insertion - Nom - Définir
Dans la zone du haut, tu tapes le nom choisi,
dans la zone "Fait référence à", tu tapes la formule.
Pour aller plus vite, on peut copier la formule à partir de la barre de formule et la coller dans la zone "Fait référence à"
Il y a un fil qui explique ce copié-collé (le message d'Antoine, à 23:43) :
<http://www.excel-downloads.com/html/French/forum/messages/1_99592_99592.htm>

 

[Jean-Marie]

Bonjour Monique, Thomas

Malgré que je préfère les mises en application par formules (on ne se refait pas), un très beau boulot de Monique. je ne pense pas au vue du nombre de données, que la solution réside par les formules. Donc je te propose une solution par VBA

Public Sub ExtractionDico ()

Dim vFois, vCombien As Long 'Va contenir le résultat
Dim TabValeurs As Variant 'Déclaration d'un tableau dynamique
Dim TabRetour() As Variant 'Declaration d'un tableau à longueur variable
Dim NbLignes As Long
Dim I As Long, J As Long, L As Long
Dim vRecherche As Variant
Dim vLong As Byte

TabValeurs = Worksheets("dico").UsedRange 'met toutes les valeurs dans le tableau dynamique

with Worksheets("Recherche")
vRecherche = .Range("B2")
vLong=.Range("B1")
end with
L = 0

'Effacer les anciens mots
'ATTENTION tous données en dessous de A6 sont supprimées
Range("A6:A" & Range("A65536").end(xlUp).row).clearcontents

For I = 1 To UBound(TabValeurs, 1)
For J = 1 To UBound(TabValeurs, 2)
If Mid(TabValeurs(I, J), 1, Len(vRecherche)) = vRecherche and Len(TabValeurs(I, J)) >= vLong Then
If Int(Rnd() * 3) = 2 Then 'Test aléatoire évite d'avoir toujours les mêmes
ReDim Preserve TabRetour(L) 'Ajoute une donnée au tableau dynamique
TabRetour(L) = TabValeurs(I, J) 'Place la valeur dans la nouvelle du tableau
L = L + 1
End If
End If
Next J
Next I

'Place les données du tableau dynamique dans une plage de cellule variable
Range("A6:A" & UBound(TabRetour) + 5).Value = Application.Transpose(TabRetour)
End Sub

Je voudrais remercie les t'chateurs qui mon aiguillé et corrigé les codes tout au long de la journée, ils se reconnaîtrons. Merci, Merci

@+Jean-Marie

 

[Thomas J.]

Bonjour,

c'est vrai qu'avec les formules ca devient pas possible car le fichier est énorme. Je m'oriente donc effectivement vers le VBA.

ci-joint le fichier que je viens de refaire.

j'ai supprimé la feuille "dico" car elle est très lourde. mais elle a 4 colonnes qui sont quasiment toutes remplies. (colonnes A,B,C entièrement remplies et colonnes D remplies jusqu'à la ligne 42.596)

comme vous pouvez le voir, dans la feuille "recherche" j'ai trois champs :
- nb lettres min
- nb lettres max
- lettres debut mot

il faut donc parcourir les 4 premieres colonnes de la feuille dico et :

1°) ignorer les mots de moins de [min] lettres
2°) ignorer les mots de plus de [max] lettres
3°) ignorer les mots dont le debut differe de [lettres debut mot]

afficher les X (x étant un int pouvant varier) premiers mot trouvés qui correspondent à ces critères.

Je connais un peu le VB mais pas spécialement le VBA. j'ai donc essayé de le faire mais je crois que c'est mal parti. Le code ne fait que 15 lignes pouvez-vous le regarder et me dire ce qui ne va pas.

Si vous avez une question n'hésitez pas!

Concernant le code gentiement fourni par Jean-Marie, je n'arrive pas à l'intégrer dans mon fichier. Mais je pense que ca rejoint mon idée.

Merci beaucoup pour votre aide !

 

[Jean-Marie]

Re... Thomas

J'ai modifié le code suivant les positions de tes données sur la feuille de ton dernier fichier, et le rajout du nombre maxi de caractères, du nombre limité de retour des occurrences.

J'ai laissé la partie aléatoire pour éviter d'avoir toujours les mêmes valeurs.

Copie le code ci-dessous et place le entre les bornes sub et end sub qui tu as déjà dans ton module.

Dim vFois, vCombien As Long 'Va contenir le résultat
Dim TabValeurs As Variant 'Déclaration d'un tableau dynamique
Dim TabRetour(21) As Variant 'Declaration d'un tableau à longueur variable
Dim NbLignes As Long
Dim I As Long, J As Long, L As Long
Dim vRecherche As Variant
Dim vMax As Byte, Vmin As Byte

TabValeurs = Worksheets("dico").UsedRange 'met toutes les valeurs dans le tableau dynamique

With Worksheets("Recherche")
vRecherche = .Range("C4") 'doit commencer par
vMax = .Range("C3") 'un nombre de caractères <= à
Vmin = .Range("C2") 'un nombre de caractères >= à
End With
L = 0

'Effacer les anciens mots
'ATTENTION tous données en dessous de A6 sont supprimées
Range("B6:B26").ClearContents

For I = 1 To UBound(TabValeurs, 1) 'nombre de lignes
For J = 1 To UBound(TabValeurs, 2) 'nombre de colonnes
If TabValeurs(I, J) <> "" Then 'pour éviter des calculs
If Mid(TabValeurs(I, J), 1, Len(vRecherche)) = vRecherche And Len(TabValeurs(I, J)) <= vMax And Len(TabValeurs(I, J)) >= Vmin Then
If Int(Rnd() * 3) = 2 And L <= 20 Then 'Test aléatoire évite d'avoir toujours les mêmes
TabRetour(L) = TabValeurs(I, J) 'Place la valeur dans la nouvelle du tableau
L = L + 1
End If
End If
End If
Next J
Next I

'Place les données du tableau dynamique dans une plage de cellule variable
Range("B6:B26").Value = Application.Transpose(TabRetour)

@+Jean-Marie

 

[Thomas J.]

Super !

C'est exactement çà !

Par contre une petite question : pour l'instant, la recherche ne s'effectue que quand on appuie sur le bouton qui lance la sub. est-ce possible de faire la recherche des qu'on appuie sur entrée après avoir rempli les cases nécessaires ?

Merci !

 

[Thomas J.]

Bon bah en fait, j'ai réussi à faire ce que je voulais : j'ai juste changé la procédure commandbutton en worksheet selectionchange.

je me demande juste une chose : est-ce simple de faire un compteur qui afficherai le nombre total d'occurences répondant aux critères (bien que seraient affichées seulement les 20 premières au maximum)?

Merci beaucoup à Monique et Jean-Marie pour leur travail