• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Project: De snelste LOOKUP methode

Status
Niet open voor verdere reacties.
Piet Bom

Piet Bom

Verenigingslid
Lid geworden
13 nov 2010
Berichten
786
Beste Guru's,
Ik ben op zoek naar de snelste LOOKUP methode.
Ik heb een test programma gemaakt die een aantal methoden vergelijkt.
nl. Match + Index, Vlookup, ZoekBinair (UDF) en de Dubbele Index Methode
De file bestaat uit een zoektabel van 2000 regels en een datatabel met 1000 codes die gematched worden met de zoektabel.
De tests geven geen spectaculaire verschillen omdat Excel ze goed aankan bij deze hoeveelheden.
Daarom heb ik er een tabelgenerator ingedaan die 1.000.000 regels met random codes aanmaakt.
Als je nu de tests doet zie je grote verschillen.
Mijn vraag is: zijn er nog snellere Lookup methoden ?
En heeft iemand ervaring met Lookup via Dictionary of Collection ?
In het voorbeeldje zit een sheet: ReadMe die het gebruik van het testprogramma uitlegt.
 

Bijlagen

  • Binair_Zoeken3h.xlsb
    121,4 KB · Weergaven: 73
mijn topic is al 113 keer bekeken, maar kreeg helaas nog geen reactie.
Is het onderwerp niet interessant ?
De vraag te moeilijk ?
De vraag te uitgebreid (tijdrovend) ?
of...............?
 
Misschien is
- de uitleg te duidelijk
- het werkboek te overzichtelijk
- het aantal verschillende macromodules te klein
- de VBA code te gemakkelijk leesbaar
- het overzicht van de gebruikte formules te evident
- de funktie van de benoemde gebieden te overduidelijk
 
Laatst bewerkt:
Excel liep bij mij vast op een van de codes.
 
Bedankt voor de feedback.
Ik zal de topic dicht zetten en mijn huiswerk gaan doen.
 
Ik heb in sheet2 na kolom B een lege kolom ingevoegd.
Daarna deze code gedraaid:

Code: 
Sub M_snb()
  t1 = Timer
  sn = Sheet1.ListObjects(1).DataBodyRange
  sp = Sheet2.Cells(1).CurrentRegion.Columns(2).Resize(, 2)
    
  With CreateObject("scripting.dictionary")
    For j = 1 To UBound(sn)
      .Item(sn(j, 1)) = sn(j, 2)
    Next
       
    For j = 2 To UBound(sp)
      sp(j, 2) = .Item(sp(j, 1))
    Next
  End With
    
  Sheet2.Cells(1).CurrentRegion.Columns(2).Resize(, 2) = sp
  MsgBox Timer - t1
End Sub
 
Ik zie teveel afhankelijkheden. Je moet ervoor zorgen dat alleen die formules in je model staan die je daadwerkelijk wilt timen. Dan krijg je heel andere resultaten. Het snelst is de niet exacte match benadering waarbij de brontabel dient te zijn gesorteerd op de opzoekkolom, ervan uitgaande dat de gezochte waarde ook in de tabel voorkomt. kan je daar niet van opaan, dan is de snelste methode die van de dubbele index/match waarbij de eerste via de niet-exacte match loopt die je vervolgens checkt op juistheid.
Ik krijg deze resultaten:
Code: 
Exacte VLOOKUP: 12.2296875
Exacte INDEX/MATCH: 12.06901042
Dubbele INDEX/MATCH gesorteerd, ontbrekende items mogelijk: 0.228125
Niet exacte VLOOKUP, gesorteerd, geen ontbrekende items: 0.15859375
Niet exacte INDEX/MATCH gesorteerd, geen ontbrekende items: 0.121875
Bijbehorende functies:
=VLOOKUP(F2;Table1
Code: 
;1;FALSE)
=INDEX(Table1[Mhrs];MATCH(F2;Table1[Code];0))
=IF(INDEX(Table1[Code];MATCH(F2;Table1[Code];1))=F2;INDEX(Table1[Mhrs];MATCH(F2;Table1[Code];1));NA())
=VLOOKUP(F2;Table1[Code];1;TRUE)
=INDEX(Table1[Mhrs];MATCH(F2;Table1[Code];1))
 
Laatst bewerkt:
@snb,
Bedankt voor de code.
Ik heb hem getest en bij mij duurt hij 30 seconden langer dan Match+Index en Vlookup
Die gaat het dus niet worden, helaas.
Toch bedankt voor de moeite.
 
Met de timer moet je altijd een paar keer dezelfde routine laten lopen om een betrouwbare uitlsag te krijgen.
Je kun ook de scripting dictionary vooraf laden en dan met early bindig werken. : with new Dictionary.

Bovendien zul je iedere methode afzonderlijk moeten testen zonder de aanwezigheid van overige te berekenen elementen (zoals, formules, UDF's, named ranges, etc.)
 
Laatst bewerkt:
Piet, die van @snb fietst er in 0,08 sec. door hier.
 
Ik suggereer ook om mijn timings in #7 eens te bekijken (NB: getimed met een loopje van 100 keer herberekenen per soort formule)
 
@Jan Karel,
Ik heb ze getest en ze werken inderdaad heel snel. Dus weer wat geleerd :)
@HSV,
Dan ga ik er nog eens naar kijken. Ik heb waarschijnlijk iets verkeerd gedaan :(
@snb,
New Dictionary heb ik inmiddels getest en werkt snel. Alleen het laden van 1.000.000 items duurt 5 minuten. Ik ga even studeren op Early en Late Binding

Ben toch blij dat jullie er naar hebben gekeken en ga alle adviezen opvolgen.
Op het werk hebben we een bestand van 44 Mb met veel grote tabellen die via lookups aan elkaar geknoopt zijn.
Ook zitten er veel COUNTIFS-formules in.
Dat bestand is niet vooruit te branden.
Met jullie tips verwacht ik veel tijdwinst te kunnen maken.
 
Knoop die tabellen eens aan elkaar middels relaties (uitgaande van Excel 2013 of 2016...), dan worden de INDEX/MATCH functies overbodig...
Vervang alle "IFS" functies eens door draaitabellen, ben je direct van de traagheid af.
 
Is Access of een andere databaseprogramma geen betere oplossing? Er kan natuurlijk veel in Excel maar het aan elkaar knopen van tabellen met zoveel (test) records lijkt mij niet iets voor Excel.
 
@VenA,
Dit Excel bestand is over de jaren heen uitgebreid van een eenvoudig workbook tot uiteindelijk een ingewikkelde tool die door 1 persoon bedient kan worden.
Een database programma zou structureel een veel betere oplossing zijn.
Daar zullen we zeker over na moeten gaan denken voor een volgend project en dan vanaf het begin meteen goed aanpakken.
Voor het huidige project is het te laat om dat ff snel om te bouwen en wel zo dat de wekelijkse updates door kunnen gaan en de rapportages gemaakt kunnen worden.
Hopelijk kunnen we met de tips van hier de formules wat optimaliseren zodat we dit project kunnen uitzingen.
En ik heb al wat resultaat geboekt, dus ik wil voorstellen om die door te voeren op het originele masterbestand.
 
Daarom dacht ik aan deze methode.
Ers is vast wel een Database specialist die de laatste 4 regels wél aan de praat krijgt:

Code: 
Sub M_snb()
    sn = Sheet1.Cells(1).CurrentRegion
    sp = Sheet2.Cells(1).CurrentRegion.Columns(2).Resize(, 2)
    
    With CreateObject("ADODB.recordset")
        .Fields.Append "sleutel", 200, 30
        .Fields.Append "waarde", 200, 30
        .Open
        For j = 1 To UBound(sn)
            .AddNew
            .Fields("sleutel") = sn(j, 1)
            .Fields("waarde") = sn(j, 2)
            .Update
        Next
        
        For j = 2 To UBound(sp)
'            .Open .DataSource, "SELECT `waarde` WHERE `sleutel` = '" & sp(j, 1) & "`"
'           sp(j,2) = .GetString
        Next
    End With
End Sub
 
Excel kent tegenwoordig relaties tussen tabellen. Zolang 1 van beide tabellen unieke sleutels heeft werkt dat en kan je bijvoorbeeld een tabel met unieke klanten koppelen aan een tabel met verkoopinfo. In een draaitabel kan je vervolgens de velden uit beide tabellen gebruiken zonder dat je in de verkoop tabel middels VLOOKUPS gegevens uit de klantentabel hoeft te halen. Heb je dus geen SQL of database voor nodig.
 
Excel kende van oudsher Querytables.
Slechts weinigen wisten die juist te gebruiken.
In de bijlage een voorbeeld.
 

Bijlagen

  • Binair_Zoeken3h(1).xlsb
    92,7 KB · Weergaven: 43
Klopt. En dat is nu dus eenvoudiger gemaakt via het datamodel.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan