VBA Variables Tableaux - Trouver une valeur sans boucle

[La braise]

Bonjour à Tous,

Mon problème du jour...

J'utilise une variable tableau (2D) que je souhaite utiliser comme une base pour faire du mapping.

Je cherche VAR_TAB(z, 2) pour un z donné.

Pour identifier z, j'utilise une boucle du genre :

For i = 1 To UBound(VAR_TAB, 1)
If VAR_TAB(i, 1) = Val_Cherchee Then Z = i
Next i
MsgBox VAR_TAB(Z, 2)

Existe t-il un moyen plus efficace que passer par une boucle qui balaye toutes les valeurs la colonne 1 du tableau?

En gros, comment lire une valeur donnée dans un tableau?

D'avance merci pour votre aide toujours aussi efficace.

 

[Misange]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Re hello
pas de boucle pour chercher mais une boucle pour construire le dictionnaire si...
affiche le résultat d'une seule recherche dans une cellule et dis moi ce que tu trouves comme temps

 

[BOISGONTIER]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

-On ne crée pas un tableau ou un dictionnaire pour une seule recherche.
-Un tableau ou un dictionnaire se construit en début de programme pour y effectuer plusieurs recherches ou écritures.
-La construction du dictionnaire pour 10.000 items prend 0,03s

Pour 500 recherches

-Dico: 0,015 sec
-Tableau 4 Sec
-Find: 2,65
-Match: 21 sec

JB

 

[La braise]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Boisgontier,

Merci pour ces test qui sont plus que significatifs.

Juste une précision sur le Dictionnary, on ne peut associer qu'une seule "données" pour une clef?
Est-il possible d'associer plusieurs données pour une même clef, l'équivalent de plusieurs colonnes?

Si j'ai bien compris A=>B
mais il n'est pas possible d'avoir A,1 =B A,2=C A,3=D ... (comme une variable tableau)

c'est l'équivalent d'une traduction?

Si j'ai une base à mapper de plusieurs colonnes pour une même clef.
Pour chaque colonne, je dosi redéfinir le Dictionnary :

mondico(a(i, 1)) = a(i, 2)
pour mapper la colonne 2

puis
mondico(a(i, 1)) = a(i, 3)
pour mapper la colonne 3

etc...

c'est bien ça?

 

[BOISGONTIER]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

S'il y a plusieurs données associées à une clé, l'item peut être un tableau

Objet dictionary

-L'objet Dictionary associe des valeurs à des clefs. L'objet Dictionary permet notamment de générer des listes sans doublons. Cet objet, simple à programmer, est très performant. Ne pas utiliser l'objet Collection qui est très lent.
-Dictionary peut être vu comme un tableau à une dimension. On accède aux éléments par une clé et non pas un indice.

http://boisgontierjacques.free.fr/pages_site/images/mondico.GIF

-Pour les ajouts/suppressions, l'objet Dictionary est + facile à utiliser qu'un tableau (Redim + indice à gérer).

Combinaison Dictionary/Tableaux

Suppression de doublons rapide

Correction

Les items d'un dictionnaire peuvent être des tableaux.

Sub EssaiDictionnaire()
  Set d = CreateObject("Scripting.Dictionary")
  ville = "Lyon"
  d.Item("Martin") = Array(ville, 5000, #12/10/1980#)
  MsgBox d.Item("Martin")(0)

  Dim a(1 To 3)
  a(1) = "Paris": a(2) = 5000: a(3) = #12/13/1945#   ' tableau a()
  d.Item("Dupont") = a
  MsgBox d.Item("Dupont")(2)
  b = d.Item("Dupont")              ' éléments de Dupont dans un tableau b()
  MsgBox b(3)
  For Each c In d.Item("Dupont")
    MsgBox c
  Next c
End Sub

JB

 

[pierrejean]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Re

Bravo Misange !!!
Tu as obtenu que JB s'exprime , belle performance
Pour un peu je lui demanderai ce qu'il pense de la solution que j'ai proposée (laquelle use justement du dictionary)

@ labraise

Une possibilité pour plusieurs colonnes avec un seul dictionnaire:
Dico(col1 & ";" & col2 & ";" & col3 )= col1 & ";" & col2 & ";" & col3
et en lecture
col1=split(dico(....),";")(0)
col2=split(dico(....),";")(1)
col3=split(dico(....),";")(2)
suppose qu'il n'y a pas de ; dans col1 col2 col3 , sinon choisir un autre delimiteur

 

[La braise]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Merci pour toutes ces sources...
Des heures de bonheur en perspective!

 

[BOISGONTIER]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

@ PierreJean

Je viens de parcourir ton post. Je crois comprendre que tu as indexé la première colonne d'un tableau 2D par un Dictionnaire.
Si la recherche ne doit s'effectuer que sur la première colonne, c'est une bonne idée.

En simplifié, on a:

Sub RechercheDico()
  Set mondico = CreateObject("scripting.dictionary")
  a = [A1:C20000]
  For i = 1 To 20000
    mondico(a(i, 1)) = i
  Next i
  clé = "Nom15000"
  ligne = mondico(clé)
  val1 = a(ligne, 2)
  val2 = a(ligne, 3)
  MsgBox val1 & " " & val2
End Sub

ou

Sub RechercheDico2()
  Set mondico = CreateObject("scripting.dictionary")
  a = [A1:C20000]
  For i = 1 To 20000
    mondico(a(i, 1)) = i
  Next i
  clé = "Nom15000"
  ligne = mondico(clé)
  b = Application.Index(a, ligne)
  MsgBox b(1) & " " & b(2) & " " & b(3)
End Sub

JB

 

[pierrejean]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Re

Merci JB

 

[Misange]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

-On ne crée pas un tableau ou un dictionnaire pour une seule recherche.
-Un tableau ou un dictionnaire se construit en début de programme pour y effectuer plusieurs recherches ou écritures.
-La construction du dictionnaire pour 10.000 items prend 0,03s

Pour 500 recherches

-Dico: 0,015 sec
-Tableau 4 Sec
-Find: 2,65
-Match: 21 sec

JB

Très démonstratif en effet !
voilà un fort bel exemple.
Bravo et merci

 

[BOISGONTIER]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Bonjour,

Un dictionnaire n'accepte que des clés uniques.
Si la colonne ne contient pas que des clés uniques (la ville par exemple en colonne D contient plusieurs fois Paris), on fabrique alors des pseudos clés.

http://boisgontierjacques.free.fr/fichiers/Cellules/RechercheTableauDico2.xls

Sub RechercheDicoVille()
  '--- construction index ville (plusieurs fois la même ville)
  Set mondico = CreateObject("scripting.dictionary")
  a = [A1:D20000]
  For i = 1 To 20000
    CléBase = a(i, 4)
    Clé = CléBase
    indice = 1
    Do While mondico.exists(Clé)
      Clé = CléBase & indice
      indice = indice + 1
    Loop
    mondico(Clé) = i
  Next i
  '--recherche (0,03 sec pour 1.000 recherches)
  CléBase = "Paris"
  Clé = CléBase
  indice = 1
  Do While mondico.exists(Clé)
    ligne = mondico(Clé)
    val1 = a(ligne, 1)
    val2 = a(ligne, 4)
    MsgBox val1 & " " & val2
    Clé = CléBase & indice
    indice = indice + 1
  Loop
End Sub

JB

 

[pierrejean]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Re

Une autre façon de proceder pour recherche multiple:

Sub RechercheDicoVille_b()
Set mondico = CreateObject("scripting.dictionary")
  a = [A1:D20000]
  For i = 1 To 20000
    Z = a(i, 4)
    mondico(Z) = mondico(Z) & i & "|"
  Next i
  CléBase = "Paris"
  x = Split(mondico(CléBase), "|")
  For n = LBound(x) To UBound(x) - 1
    MsgBox (a(x(n), 1) & " " & a(x(n), 4))
  Next n
End Sub

Qui, a priori , ne devrait pas etre plus lente

 

[La braise]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Re,

Retour d'exéprience sur vos différentes solutions sur un fichier test.

http://www.casimages.com/img.php?i=120410012135156196.jpg

La solution la plus efficace que j'ai retenu :

une fois le tableau initial dans la Variable Tableau,
je rempli le dico avec la valeur de la colonne souhaitée de la Variable TAbleau
Je crée une deuxième variable tableau contenant les valeurs cherchées dans la colonne 1 V(n,1)
j'effectue le mapping sur la colonne 2 V(n,2) à l'aide du dico sur la première variable tableau
et je colle la deuxième variable tableau dans un range.

Efficacité foudroyante

mon code :

'Dico Tableau sur Tableau
t = Timer()
VAR_TAB = Sheets("Base").Range("A1:B2766")
VAR_CALC = Sheets("Dest").Range("A3:B14007")
Set mondico2 = CreateObject("scripting.dictionary")
For i = 1 To UBound(VAR_TAB, 1)
mondico2(VAR_TAB(i, 1)) = VAR_TAB(i, 2)
Next i
For j = 1 To UBound(VAR_CALC, 1)
Val_Cherchee = VAR_CALC(j, 1)
VAR_CALC(j, 2) = mondico2(Val_Cherchee)
Next j
Sheets("Dest").Range("I3:J14007") = VAR_CALC
Columns("I:I").Delete Shift:=xlToLeft
T6 = Timer() - t
T6 = Format(T6, "#0.00")
T6 = "Dico Tab sur Tab : " & T6
Sheets("Dest").Range("i1") = "Dico Tab sur Tab"


Précision :
le "Val_Cherchee = VAR_CALC(j, 1)" m'est utile suite à une différence de format texte et nombre entre le fichier source et dest.


Merci pour la nouvelle info, en cas d'entrées multiples,
je vais analyser avec la plus grande attention les fichiers joint.

Merci encore pour votre aide toujours aussi efficace!!

 

[jlgi]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Bonjour,

Recherche dans la première colonne d'un tableau 2D

a = [A1:B4]
x = "cc"
p = Application.Match(x, Application.Index(a, , 1), 0)
MsgBox p
MsgBox a(p, 2)

JB

Bonjour,
Je cherche depuis un moment, un équivalent à "recherchev" dans une variable tableau comportant 4700 lignes et 60 colonnes, et la solution "application.match..." fonctionne mais est excessivement longue.
Existe-t-il un équivalent plus adapté aux tableau vba de grande dimensions?
D'avance merci

 

[Roland_M]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

bonjour à tous,

pour effectuer une recherche dans une liste triée j'utilise la méthode dite hachoir binaire
' recherche l'Idx de MaVar
' MaVal et Liste() à adapter Val.Num ou Alphanum !?
' exemple recherche MaVar dans Liste()
X = LBound(Liste()): Y = UBound(Liste()): IdxTrouve = - 1
Do While Y >= X: I = Int((Y + X) * 0.5)
If MaVar = Liste(I) Then IdxTrouve = I: Exit Do 'IdxTrouve dans le tableau trouvé !
If MaVar < Liste(I) Then Y = I - 1 Else X = I + 1
Loop
If IdxTrouve>=0 Then MsgBox "Trouvé: " & vbLf & Liste(IdxTrouve)

on pourrait faire la même chose sur une colonne de données triée !
le principe est rapide puisque la recherche s'effectue par Idx/2 à chaque fois !

 

[pierrejean]

Re : VBA Variables Tableaux - Trouver une valeur sans boucle

Re

Il existe la solution du Find

Voir fichier joint
La macro remplir va créer un tableau de 4700 lignes et 60 colonnes
Modifier quelques valeurs pour avoir des doublons , dans la macro test mettre cette valeur doublon et la lancer
Chez moi et pour 3 valeurs identiques les adresses sont trouvées en 0,375 seconde
Il est ensuite particulièrement aisé de se reporter a une autre valeur sur la même ligne (ou la même colonne)