excel: combi ALS en 2X VERT.ZOEKEN, kan het sneller?

[mispelhage]

hoi,

ik heb een algemene vraag; ik werk op het moment met een grote matrix met daarin formules in de vorm van:

=ALS(ISFOUT(VERT.ZOEKEN(TEKST.SAMENVOEGEN(I$2;" * ";$F12);Bio_v1!$C:$W;21;0));"";VERT.ZOEKEN(TEKST.SAMENVOEGEN(I$2;" * ";$F12);Bio_v1!$C:$W;21;0))

Op zich werkt die goed, alleen komt de formule veel (2 miljoen keer....) voor in de matrix, waardoor het rekenen even tijd kost. Het verticaal zoeken deel komt twee keer voor, ik vraag me af of ik deze formule slanker kan maken om zo de berekeningstijd te verminderen.

Iemand een idee?

 

[KristiaanL]

Mijn idee: Plaats een representatief voorbeeldbestand.

Het is giswerk om zonder bestand een formule te vereenvoudigen, zo niet onmogelijk..

 

[edmoor]

Inderdaad.
Maar zonder voorbeeld, probeer deze eens:

=ALS.FOUT(INDEX(Bio_v1!$W:$W;VERGELIJKEN(I$2 & " * " & $F12;Bio_v1!$C:$C;0);1);"")

 

[mispelhage]

Dank je edmoor,

jij hebt een werkende oplossing gevonden, sjiek! Je suggestie werkt. Ik kan hem ook toepassen als:

=ALS.FOUT(VERT.ZOEKEN(TEKST.SAMENVOEGEN(N$2;" * ";$F12);Bio_v1!$C:$W;21;0);"")

Dat scheelt de helft van mijn oorspronkelijke functieregel.

Wat zou sneller werken, de verticaal zoeken of de index functie?

 

[edmoor]

Geen idee, dat zal je proefondervindelijk moet uit zien te vinden
Maar als je er achter bent over die aantallen dan vernemen we dat graag hier.

 

[AlexCEL]

Nog wat korter:

=ALS.FOUT(VERT.ZOEKEN(N$2&" * "&$F12;Bio_v1!$C:$W;21;0);"")

 

[edmoor]

@mispelhage:
Die TEKST.SAMENVOEGEN functie is inderdaad overbodig in deze.
Daarom gebruikte ik die in mijn voorbeeld ook al niet.

 

[jkpieterse]

Bij mijn weten is INDEX/VERGELIJKEN even langzaam als VERT.ZOEKEN. Sneller kan het worden als je brontabel is gesorteerd op de opzoek kolom. Je kunt dan iets doen in de trant van:

=ALS(INDEX(Bio_v1!$C:$C;VERGELIJKEN(I$2 & " * " & $F12;Bio_v1!$C:$C;1);1)=I$2&" * "&$F12;INDEX(Bio_v1!$W:$W;VERGELIJKEN(I$2 & " * " & $F12;Bio_v1!$C:$C;1);1);"")

Dit is sneller omdat niet-exact zoeken in een gesorteerde lijst zoveel sneller is dan stap-voor-stap exact zoeken in een niet gesorteerde lijst, dat je je het kunt veroorloven twee keer te zoeken. De eerste keer om te checken of je wel de juiste waarde gevonden hebt en de tweede keer om de juiste kolom vervolgens op te halen. Mocht je meer dan 1 gegeven op deze manier nodig hebben, dan is het uiteraard beter om een hulp-kolom te gebruiken die alleen het index-getal geeft:

=ALS(INDEX(Bio_v1!$C:$C;VERGELIJKEN(I$2 & " * " & $F12;Bio_v1!$C:$C;1);1)=I$2&" * "&$F12;VERGELIJKEN(I$2 & " * " & $F12;Bio_v1!$C:$C;1);"")

Waarna je de INDEX functie gebruikt die dan als rij-arhument deze kolom gebruikt.

 

[E v R]

Nog 2 tips:

- Gebruik aantal.als om te kijken of jouw zoekwaarde bestaat (>0) dan kun je die als.fout lekker links laten liggen (waar die hoort...)
- Gebruik geen hele kolommen in jouw formule (bijvoorbeeld een tabel)

 

[jkpieterse]

Ik vraag mij af of aantal.als sneller is dan een niet-exacte match op een gesorteerde lijst (binairy search algoritme). Geen tijd om het te timen helaas

 

[E v R]

@JKP, ik denk dat aantal.als behoorlijk snel is, (Wellicht wil TS testen)
Suggestie was overigens enkel om bij het niet vinden "" te plaatsen in relatie tot ALS.FOUT. Ik zou de foutmelding sowieso lekker laten staan, tenzij de uitkomst gebruikt wordt in een output/cockpit sheet, maar dat lijkt me hier niet het geval met 2 miljoen keer dezelfde formule. Sterker nog; hier zou ik zeker naar andere mogelijkheden kijken.