Excelbestand wordt zeer traag

[SDaniels]

Beste Excellers,

De volgende formule gebruik ik om productspecificaties op te halen:

=ALS.FOUT((ALS(D83=3;VERT.ZOEKEN(A83;TEST3!$B:$FA;46;ONWAAR);ALS(D83=4;VERT.ZOEKEN(A83;TEST4!$B:$DT;1;ONWAAR);ALS(D83=5;VERT.ZOEKEN(A83;TEST5!$B:$BL;1;ONWAAR);ALS(D83=12;VERT.ZOEKEN(A83;TEST6!$B:$BM;1;ONWAAR))))));VERT.ZOEKEN(A83;TEST7!$B:$BM;1;ONWAAR))

Het probleem is nu dat deze formule in ca. 7500 cellen staat wordt alles heel traag en zowel Excel als mijn mede server gebruikers vinden dat niet leuk.
De betreffende cellen hoeven alleen geupdate te worden als ik het bestand open. Vervolgens moeten de formules geen gegevens meer ophalen.
Heeft iemand een hiervoor een idee/oplossing?

Alvast hartelijk dank voor jullie hulp.

 

[robdgr]

Misschien helpen deze regels in 'This workbook':

Private Sub Workbook_open()
    Application.Calculation = xlManual
End Sub
Private Sub Workbook_close()
    Application.Calculation = xlAutomatic
End Sub

 

[roeljongman]

hoe zou het toch komen dat mijn excel traag word???

ik denk dat het komt omdat je bijna 500 miljoen cellen laat doorrekenen terwijl je het ook met 500.000 cellen afkan als je je vert.zoeken formules fatsoenlijk opbouwt.

- je hebt nu 5 vert.zoeken formules die intotaal 475 kolommen van ruim 1 miljoen rijen doorzoekt, 4 van die 5 vert.zoeken formules moet de waarde ophalen uit kolom 1 dus dat maakt ruim 300 kolommen voor die formules overbodig. en voor de 5e vert.zoeken die in kolom 46 zoekt heb je 157 kolommen geselecteerd.. dat is dus nog eens 100 kolommen besparing. het heeft geen enkel nut 50+ kolommen te te doorzoeken als de gezochte waarde in kolom 1 staat.

- voor je rijen hetzelfde verhaal.. hoeveel rijen zijn gevuld in elk testblad 100? 1000? 10.000? ik ken weinig testsheets die meer dan 1000 voorwaarden kent.. maar goed zelfs als je per testsheet 100.000 mogelijkheden hebt dan nog kun je 90% besparen door niet de hele kolom te doorzoeken.

onderstaande formule gaat uit van 10.000 rijen dan doorzoek je ca 500.000 cellen ipv bijna 500.000.000 (500 miljoen) dat is 1/1000 ste deel ofwel 0,1% van wat jij nu doorzoekt, ik denk dat dat je performance aanzienlijk verbetert

=ALS.FOUT((ALS(D83=3;VERT.ZOEKEN(A83;TEST3!$B$1:$AZ$10000;46;ONWAAR);ALS(D83=4;VERT.ZOEKEN(A83;TEST4!$B$1:$B$10000;1;ONWAAR);ALS(D83=5;VERT.ZOEKEN(A83;TEST5!$B$1:$B$10000;1;ONWAAR);ALS(D83=12;VERT.ZOEKEN(A83;TEST6!$B$1:$B$10000;1;ONWAAR))))));VERT.ZOEKEN(A83;TEST7!$B$1:$B$10000;1;ONWAAR))

Vanaf Excel 2007 heeft excel veel grotere werkbladen en waar je in excel 2003 de pc niet zo gauw gek kreeg dorodat je maar maximaal 65.000 rijen kon selecteren als je een kolom selecteerde, met de introductie van de sheets met 1 miljoen rijen word dat dus al gauw een probleem als heel veel kolommen helemaal selecteert, daar moet je rekening mee houden in het opbouwen van je formules.

 

[popipipo]

Als je de structuur van de 5 tabbladen gelijk kunt krijgen dan kun je ook nog volstaan met 1 'VERT.ZOEKEN' formule
Dit in combinatie met de 'indirect' formule en het door het zoekbereik een naam te geven.

De formule wordt dan iets in de trant van:

=VERT.ZOEKEN(A2;INDIRECT(D83);2;0)

In D83 staat dan de naam van het zoekbereik.
Als je het zoekbereik dan nog dynamisch maakt dan wordt het aantal berekeningen nog verder verkleind.

 

[oeldere]

@popipipo

Ik lees op fora dat het gebruik van indirect de bestanden juist zeer traag maakt.

 

[popipipo]

Misschien haal je daar dan wel geen tijdwinst.
De enige winst die je dan er mee haalt is dat de formule een stuk kleiner is.

 

[Warme bakkertje]

Bij gesorteerde data levert het gebruik van Index-Vergelijken tov Vlookup een tijdswinst op van +/- 30%.

 

[SDaniels]

hoe zou het toch komen dat mijn excel traag word???

ik denk dat het komt omdat je bijna 500 miljoen cellen laat doorrekenen terwijl je het ook met 500.000 cellen afkan als je je vert.zoeken formules fatsoenlijk opbouwt.

- je hebt nu 5 vert.zoeken formules die intotaal 475 kolommen van ruim 1 miljoen rijen doorzoekt, 4 van die 5 vert.zoeken formules moet de waarde ophalen uit kolom 1 dus dat maakt ruim 300 kolommen voor die formules overbodig. en voor de 5e vert.zoeken die in kolom 46 zoekt heb je 157 kolommen geselecteerd.. dat is dus nog eens 100 kolommen besparing. het heeft geen enkel nut 50+ kolommen te te doorzoeken als de gezochte waarde in kolom 1 staat.

- voor je rijen hetzelfde verhaal.. hoeveel rijen zijn gevuld in elk testblad 100? 1000? 10.000? ik ken weinig testsheets die meer dan 1000 voorwaarden kent.. maar goed zelfs als je per testsheet 100.000 mogelijkheden hebt dan nog kun je 90% besparen door niet de hele kolom te doorzoeken.

onderstaande formule gaat uit van 10.000 rijen dan doorzoek je ca 500.000 cellen ipv bijna 500.000.000 (500 miljoen) dat is 1/1000 ste deel ofwel 0,1% van wat jij nu doorzoekt, ik denk dat dat je performance aanzienlijk verbetert

=ALS.FOUT((ALS(D83=3;VERT.ZOEKEN(A83;TEST3!$B$1:$AZ$10000;46;ONWAAR);ALS(D83=4;VERT.ZOEKEN(A83;TEST4!$B$1:$B$10000;1;ONWAAR);ALS(D83=5;VERT.ZOEKEN(A83;TEST5!$B$1:$B$10000;1;ONWAAR);ALS(D83=12;VERT.ZOEKEN(A83;TEST6!$B$1:$B$10000;1;ONWAAR))))));VERT.ZOEKEN(A83;TEST7!$B$1:$B$10000;1;ONWAAR))

Vanaf Excel 2007 heeft excel veel grotere werkbladen en waar je in excel 2003 de pc niet zo gauw gek kreeg dorodat je maar maximaal 65.000 rijen kon selecteren als je een kolom selecteerde, met de introductie van de sheets met 1 miljoen rijen word dat dus al gauw een probleem als heel veel kolommen helemaal selecteert, daar moet je rekening mee houden in het opbouwen van je formules.

Bedankt voor je kritische (licht snerpende ) opmerkingen en aanbevelingen. Je hebt volkomen gelijk dat het onzin is om de gehele kolom door te zoeken terwijl ik ca. 1000 rijen verwacht per tabblad. Kolommen kan ik helaas niet in snijden maar ik verwacht voldoende prestatie te kunnen halen uit het snijden in rijen.
Zodra de nieuwe opbouw getest is zal ik mijn vraag als opgelost neerzetten.

Nogmaals dank voor al jullie reacties.

 

[roeljongman]

sorry als ik wat te scherp overkwam, ik wilde vooral aanzetten tot denken of bewust wording is misschien betere term, sommige gewoontes uit excel2003, zoals hele kolommen selecteren kun je beter afleren omdat het aantal rijen zo groot is geworden.


Waarom denk je dat je het aantal doorzochte kolommen niet kan verminderen? ze zijn namelijk nutteloos in deze formule, ze worden namelijk niet gebruikt maar wel doorzocht.. het is een standaard gewoonte om altijd het hele datagebied in een verticaal zoeken formules te zetten en als je de 15 kolom (of in jou geval de 46e) nodig hebt hoef je alleen die 15 kolommen maar te selecteren, als je uit de eerste kolom data wilt ophalen dan kun je gewoon 1 kolom selecteren. In grote sheets doe ik dat standaard, de laatste kolom van het geselecteerd bereik is ook de kolom waar de waarde word opgehaald. dit scheelt veel doorzoeken van cellen.

 

[snb]

Andere insteek:
Als de 'berekening' alleen maar bij opening van het bestand hoeft plaats te vinden, lijkt mij een macro die bij het openen van het betsand wordt uitgevoerd veel voor de hand liggender.
Formules in Excel zijn er nu juist voor bedoeld om iedere wijziging in het werkboek meteen door te vertalen naar afhankelijke cellen. En dat is hier blijkbaar niet het geval.

als het erom gaat na te gaan of waarden in kolom B van sheets(1) voorkomen in kolom B van een van de 'test'sheets:

Private sub Workbook_open()
  redim sn (5)
  for each sh in sheets
    if left(sh.name,4)="test" then sn(val(mid(sh.name,5)))=columns(2).specialcells(2)
  next

  sp=sheets(1).columns(2).specialcells(2)
  for j=1 to ubound(sn)
    for jj=1 to ubound(sn)
      if not iserror(application.match(sp(j,1),sn(j),0)) then
         x2=sheets("test" & j).cells(application.match(sp(j,1),sn(j),0),46).value
         exit for
      end if
   next
  next
end sub

 

[SDaniels]

sorry als ik wat te scherp overkwam, ik wilde vooral aanzetten tot denken of bewust wording is misschien betere term, sommige gewoontes uit excel2003, zoals hele kolommen selecteren kun je beter afleren omdat het aantal rijen zo groot is geworden.


Waarom denk je dat je het aantal doorzochte kolommen niet kan verminderen? ze zijn namelijk nutteloos in deze formule, ze worden namelijk niet gebruikt maar wel doorzocht.. het is een standaard gewoonte om altijd het hele datagebied in een verticaal zoeken formules te zetten en als je de 15 kolom (of in jou geval de 46e) nodig hebt hoef je alleen die 15 kolommen maar te selecteren, als je uit de eerste kolom data wilt ophalen dan kun je gewoon 1 kolom selecteren. In grote sheets doe ik dat standaard, de laatste kolom van het geselecteerd bereik is ook de kolom waar de waarde word opgehaald. dit scheelt veel doorzoeken van cellen.

Excuses zijn niet benodigd! Ik ben blij met de aangegeven oplossing want mijn bestand is nu een stuk sneller. Zelfs wat optimalisatie kunnen behalen door in de kolommen te snijden. Bedankt Roel!

SNB, bedankt voor de andere insteek echter is het "probleem" nu dusdanig opgelost dat ik de vraag ga afsluiten.

 

[snb]

Dat lijkt me niet opgelost.

Wat blijft is, dat je voor een bepaald doel een niet-optimale methode gebruikt.
De stelling blijft overeind, dat : Als de 'berekening' alleen maar bij opening van het bestand hoeft plaats te vinden, lijkt mij een macro die bij het openen van het bestand wordt uitgevoerd veel voor de hand liggender.
Test het snelheidsverschil maar eens.

 

[SDaniels]

Ik ondervind op dit moment geen beperkte snelheid meer in mijn bestand. Enkel het opslaan duurt lang maar daar kan ik mee leven.
SNB je hebt gelijk dat mijn stelling niet is opgelost maar het doel wat ik voor ogen had (een sneller Excel bestand) is wel bereikt.
Bedankt voor jullie reacties.