rapidité de recherche....

[erics83]

Bonjour,

J'ai un classeur, 2 feuilles.
Feuille1 : en A, le numéro de siret des entreprises; en B le nom des entreprises. Il y a 350000 lignes.....
En Feuille2, une compilation de différents fichiers commerciaux : en A : le siret de l'entreprise, en B, le type de contact (téléphone, mail, etc...)

Donc, jusqu'à présent, je faisais un index/equiv dans la feuille2 qui allait me chercher en feuille1 le siret et mettait en colonne C, le nom de l'entreprise (récupéré de la feuille1) .

Mon soucis est le temps.... : Ma feuille2 est alimentée chaque mois d'environ 10000 données. Au début la recherche était "rapide", mais maintenant ma feuille2 atteint les 200000 lignes et Index/equiv devant chercher dans les 350000 lignes de la feuille1, cela met beaucoup de temps....

j'ai essayé Dictionary, formules matricielles (merci JB..), mais cela prend beaucoup de temps.

Auriez vous une petite idée et/ou solution ?

En vous remerciant pour votre aide,

 

[eriiic]

Bonjour,

Si la table est triée il y a aussi le double Recherchev(;;;VRAI) qui est très performant.

=SI(RECHERCHEV(A2;Feuil1!A:A;1)=A2;RECHERCHEV(Feuil2!A2;Feuil1!A:B;2);"")

chez moi (i7 mais de 10 ans) : 0.6 s pour 200000 lignes (et je fais sur colonnes entières pour la table)
Le temps est le temps réel de calcul, la mise à jour de la feuille n'est pas prise en compte
A titre de comparaison le dico prend 8.7 s chez moi, soit 60% de plus que job75
Test

Sub test()
    Dim t As Single
    t = Timer
    [D2:D200000].Dirty
    [D2:D200000].Calculate
    MsgBox "Double Recherchev() sur 200000 lignes : " & Timer - t

eric

 

[Roland_M]

Bonjour tout le monde,

Salut Job !

chez moi, rien d'extraordinaire mais ça tourne bien et c'est assez fluide.
un i3 relativement récent, 8 Go , window 7 64bit , Excel 2007 32bit

je n'ai plus de plantage ! résultat des essais (4 bis) dans le même ordre:

création du Dictionary sur 239 520 lignes = 3.43 secondes

- mise à jour des 354 816 lignes en Feuil2 = 11,25 secondes

- mise à jour de 10 000 lignes en Feuil2 = 0,36 seconde


bonne journée.

 

[mapomme]

Bonjour à toutes et tous,

@Nicole :
Effectivement, Recherchev() avec une table triée est très rapide. J'y vois cependant un inconvénient parce que la formule peut renvoyer des valeurs inexactes (voir fichier joint).

Je propose une autre formule qui vérifie d'abord que la valeur cherchée est dans la colonne de recherche avant de renvoyer le résultat. Même si la formule est plus compliquée, ça reste très rapide.

Au lieu de :

=RECHERCHEV(I2;$A$2:$B$200000;2;VRAI)

utiliser :

=SI(RECHERCHEV(I2;$A$2:$A$200000;1;VRAI)=I2;RECHERCHEV(I2;$A$2:$B$200000;2;VRAI);NA())

 

[job75]

Bonjour le fil, le forum,

Comme suite à mes posts #30 et #31, si l'on veut importer les tableaux dont les Siret sont des nombres :

Private Sub CommandButton1_Click() 'Importer
Dim f$, i As Byte, t, j&
Application.ScreenUpdating = False
Set d = Nothing 'RAZ
With Workbooks.Open(ThisWorkbook.Path & "\Source.xlsx") 'à adapter
  f = "00000000000000"
  Application.EnableEvents = False
  For i = 1 To 2 '2 feuilles
    t = .Sheets(i).UsedRange.Resize(, i + 1)
    For j = 1 To UBound(t): t(j, 1) = Format(t(j, 1), f): Next j
    With ThisWorkbook.Sheets(i)
      .[A:A].Resize(, i + 1).ClearContents 'RAZ
      .[A1].Resize(UBound(t), i + 1) = t
    End With
  Next i
  Application.EnableEvents = True
  .Close False
End With
End Sub

Fichier (5) et fichier "Source.xlsx" joints.

A+

 

[job75]

Re,

En effet Feuil1 étant triée RECHERCHEV est la meilleure solution, le code de Feuil2 :

Private Sub Worksheet_Activate()
Dim x As Variant
x = [Modif]
If Not IsNumeric(x) Then x = 1
If x = 0 Then Exit Sub 'si Feuil1 n'a pas été modifiée
If FilterMode Then ShowAllData 'si la feuille est filtrée
Worksheet_Change [A:C] 'lance la macro
ThisWorkbook.Names.Add "Modif", 0 'nom défini
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A:A,C:C]) Is Nothing Then Exit Sub
Dim t#, r As Range
t = Timer
Set r = Intersect(Target.EntireRow, Range("C2:C" & Rows.Count), UsedRange.EntireRow)
If r Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In r.Areas
  r = "=IF(LOOKUP(RC[-2],'" & Feuil1.Name & "'!C1)=RC[-2],""""&VLOOKUP(RC[-2],'" & Feuil1.Name & "'!C1:C2,2),NA())"
  r = r.Value 'supprime les formules
Next r
Application.EnableEvents = True
If Timer - t > 0.1 Then MsgBox "Feuille mise à jour en " & Format(Timer - t, "0.00 \s")
End Sub

La macro entre cette formule en colonne C de Feuil2 :

=SI(RECHERCHE(A2;Feuil1!$A:$A)=A2;""&RECHERCHEV(A2;Feuil1!$A:$B;2);NA())

Fichier joint, résultats chez moi :

- formule entrée sur C2:C354816 => 5,0 s

- formule entrée sur C2:C10000 => [Edit] 0,25 s

A+

 

[erics83]

Merci Bisson Nicole,
Merci eriiiic,
Merci Roland_M,
Merci mapomme, merci pour tes explications

Merci Job75 auprès de qui je m'excuse concernant le time....effectivement, j'avais omis de transformer les N°Siret en texte..(comme signalé en #8)..après avoir refait tourné le code, j'ai obtenu 9,6 s....et merci pour la version avec fichier source qui peut être aussi une solution que je vais approfondir.

je vois avec plaisir qu'il existe d'autres possibilité : rechercheV, notamment que j'avais mis de côté pensant que le code serait plus rapide.....

Merci pour tous vos apports et propositions, c'est vraiment sympa,

Merci pour votre aide,