Excel sheet vrij langzaam en loopt vast.

[Luuk013]

Beste Tweakers,

Ik ben bezig met het combineren van veel data (200.000 regels 35 kolommen) in excel, nu heb ik veel problemen met het berekenen. Het loopt regelmatig vast en anders duurt het 10 minuten tot een uur.

Instellingen processor op maximaal aantal cores in excel ingesteld.

Ik heb 4 kolommen die ik in eerste instantie met vert.zoeken heb laten werken, maar aangezien de template vrij langzaam werd (uur berekenen) heb ik dit aangepast naar de functie index en vergelijken.

Ik heb tabellen ingevoegd, maak gebruik in 6 kolommen van de functie sommen.als en ik heb automatisch berekenen uitgezet.

ik gebruik =DAG(I2)&"-"&MAAND(I2)&"-"&JAAR(I2) in 2 kolomen om de datum incl tijd om te zetten in alleen datum.

Ik gebruik =AFRONDEN(E2*8,0)/8 om tijden af te ronden.

Ik haal tekst ui elkaar doormiddel van deze formule om zoeksleutels te maken. =DEEL(A2,1,3)
en vervolgens om de zoeksleutel te combineren =TEKST.SAMENVOEGEN(B2,F2)

Ik maak gebruik van B:B als kolom, scheelt het als ik dit aanpas naar B1:B200000?

Verder heb ik bij het zoeken met index en sommen.als excel op meerdere kolommen laten zoeken (3 kolommen)

Hebben jullie nog andere tips? het is echt heel belangrijk om dit werkend te krijgen zodat ik mijn werk hiermee kan doen. Het idee is tot nu toe super, alleen de uitvoering is momenteel wat minder..

De data is gevoelig dus kan ik niet publiceren.
En de laptop is een I5 met 8gb ram op netstroom (hoge prestaties ingesteld)

[keur0000]

i5 en 8Gb zegt niet zo veel, werk je op een ssd of HD?, daar kan het aan liggen dat je veel wachttijd kwijt bent
En als je toch bezig bent, kijk even in taakbeheer hoe er met je intern geheugen word omgegaan, swapen op de HD is vaak de oorzaak.

[F_J_K]

Schakel multithreading in in de instellingen, als het niet al is gedaan.

Als een beperkt aantal cellen is verandert en dat ook geen impact heeft op berekeningen in andere cellen, kan je het herberekenen via VBA beperken tot die cellen, met Range.Calculate of misschien het snellere Range.CalculateRowMajorOrder. Wel goed testen of je alles meepakt.

offtopic:
Dus de machine van Luuk013 in "Welke compacte laptop voor zakelijk gebruik?" is al gecrasht?

Edit: en overweeg of je dingen als uitsplitsen en samenvoegen niet eenmalig kunt doen (al was het via VBA). Afronden hoeft vast niet tot je helemaal klaar bent met berekenen, zonde om continu te doen in een sheet dat traag is. En als het traag blijft, overweeg een ander tool (waar je ook de input kunt optimaliseren, zoals de opsplitsingen etc).

[ Voor 26% gewijzigd door F_J_K op 13-05-2019 16:25 ]

[Triggy]

Luuk013 schreef op maandag 13 mei 2019 @ 16:15:

Ik heb 4 kolommen die ik in eerste instantie met vert.zoeken heb laten werken, maar aangezien de template vrij langzaam werd (uur berekenen) heb ik dit aangepast naar de functie index en vergelijken.

Maak een extra kolom voor vergelijken en hergebruik het resultaat

Ik maak gebruik van B:B als kolom, scheelt het als ik dit aanpas naar B1:B200000?

Ja een hoop.

[Luuk013]

Ik ga jullie tips proberen, Het gaat om een i5-6300u maar hdd is server bedrijfsnetwerk..

[ Voor 3% gewijzigd door Luuk013 op 14-05-2019 09:35 ]

[F_J_K]

We houden ons aanbevolen voor beschrijving wat hielp

Luuk013 schreef op dinsdag 14 mei 2019 @ 09:35:
Ik ga jullie tips proberen, Het gaat om een i5-6300u maar hdd is server bedrijfsnetwerk..

Haal als test het bestand dan ook eens over naar lokale SSD.

[heuveltje]

Triggy schreef op maandag 13 mei 2019 @ 16:58:
[...]
Ja een hoop.

Serieuze vraag, mits die kolom maar waardes heeft tot 200.000
Is B1:B20.0000 dan veel sneller dan B:B. ?

Heb af en toe ook van dat soort dramatische tabellen, nooit aan gedacht om dat aan te passen.

[SadisticPanda]

Moet het excel zijn? Kan je al niet beter bij zulke hoeveelheden overstappen naar een SQLITE/ACCESS/MYSQL?

Ik zit ook met vergelijkbaar ding en doe alles gewoon in database en plak dan de gegevens gewoon in excel vanuit de frontend. Waar die anders 10-60Minuten op zit te ronken in excel. Is nu meestal gedaan op aantal seconden.

Btw ook gemerkt dat libreoffice iets minder blijft hangen bij grote workshout dan excel zelf.

[Triggy]

In .xls 65000 rijen maximaal, .xlsx 1 miljoen.

B:B evalueert elke mogelijke rij dus dat is in dit geval 5x zoveel.


Edit: zie onderstaande post, hangt af van welke functies je gebruikt in je sheet.

[ Voor 27% gewijzigd door Triggy op 14-05-2019 11:10 ]

[pyrofielo]

Zou inderdaad eens verder gaan kijken dan Excel.

Dusdanig veel rijen met berekeningen verloopt eigenlijk altijd dramatisch. Dus of een keer uitvoeren kopieren en plakken als waarden.

Of beseffen dat Excel niet de tool is voor alles en wat anders gaan gebruiken.

[Reinier]

Triggy schreef op dinsdag 14 mei 2019 @ 10:17:
In .xls 65000 rijen maximaal, .xlsx 1 miljoen.

B:B evalueert elke mogelijke rij dus dat is in dit geval 5x zoveel.

Ik waag dat te betwijfelen. Excel kan dat vast wel efficiënt doen omdat hij bijhoudt waar de data in kolom B ophoudt.

[tritimee]

heb je al die 35 kolommen nodig? of zou je eventueel een extractie kunnen maken van alleen de benodigde kolommen? Als jij bijv. 10 kolommen kunt verwijderen, scheelt dat 2 miljoen cellen die Excel minder gaat checken.

Heb je een kolom die uniek is per regel? dan zou je die plus je minimaal benodigde data kunnen laten staan en de rest, wanneer het relevant wordt, via een Vlookup binnen kunnen halen.

Mijn tip in ieder geval, probeer zoveel mogelijk kolommen te verwijderen.

[Tazzios]

Luuk013 schreef op maandag 13 mei 2019 @ 16:15:
Beste Tweakers,

Ik ben bezig met het combineren van veel data (200.000 regels 35 kolommen) in excel, nu heb ik veel problemen met het berekenen. Het loopt regelmatig vast en anders duurt het 10 minuten tot een uur.

Instellingen processor op maximaal aantal cores in excel ingesteld.

In de statusbalk kun je zien hoeveel cores er gebruikt worden.

Ik heb 4 kolommen die ik in eerste instantie met vert.zoeken heb laten werken, maar aangezien de template vrij langzaam werd (uur berekenen) heb ik dit aangepast naar de functie index en vergelijken.

Ik heb tabellen ingevoegd, maak gebruik in 6 kolommen van de functie sommen.als en ik heb automatisch berekenen uitgezet.

Gebruik je ook de tabel functie van excel zelf?

ik gebruik =DAG(I2)&"-"&MAAND(I2)&"-"&JAAR(I2) in 2 kolomen om de datum incl tijd om te zetten in alleen datum.

Ik gebruik =AFRONDEN(E2*8,0)/8 om tijden af te ronden.

Ik haal tekst ui elkaar doormiddel van deze formule om zoeksleutels te maken. =DEEL(A2,1,3)
en vervolgens om de zoeksleutel te combineren =TEKST.SAMENVOEGEN(B2,F2)

Ik maak gebruik van B:B als kolom, scheelt het als ik dit aanpas naar B1:B200000?

nee dus, Gebruik de tabel functie van excel dan kun je het bereik als 'Tabel1[Kolom2]' selecteren

Verder heb ik bij het zoeken met index en sommen.als excel op meerdere kolommen laten zoeken (3 kolommen)

Hebben jullie nog andere tips? het is echt heel belangrijk om dit werkend te krijgen zodat ik mijn werk hiermee kan doen. Het idee is tot nu toe super, alleen de uitvoering is momenteel wat minder..

De data is gevoelig dus kan ik niet publiceren.
En de laptop is een I5 met 8gb ram op netstroom (hoge prestaties ingesteld)

Indien de berekening eenmalig is, vervang de formule dan met de waarde zelf. (zoals bij de datums en zoeksleutels)
Zet berekenen op handmatig, dan hoef je tussendoor niet te wachten.

Andere richting, gebruik een applicatie zoals Tableau prep builder (deze kan ook een csv output maken) of ETL software.

[ Voor 4% gewijzigd door Tazzios op 14-05-2019 10:30 ]

[Gomez12]

Tazzios schreef op dinsdag 14 mei 2019 @ 10:22:
[...]
Indien de berekening eenmalig is, vervang de formule dan met de waarde zelf.

Wat ik nog wel eens gebruikt heb bij grotere/onmogelijke excel gebruikers is om een macro te maken die van een formula in rij 1 deze doorkopieerde naar beneden uitrekende en dan opsloeg als waarde zodat de formule alleen in rij 1 bleef staan en de rest waardes waren.

Zorg ervoor dat range-functies niet onnodig uitgevoerd worden (als ze niet nuttig zijn als a1 leeg is, gooi er dan een als-formule omheen die blokkeert de zwaardere formule)

[Triggy]

Reinier schreef op dinsdag 14 mei 2019 @ 10:21:
[...]


Ik waag dat te betwijfelen. Excel kan dat vast wel efficiënt doen omdat hij bijhoudt waar de data in kolom B ophoudt.

Gedeeltelijk waar, hangt af van welke functies je gebruikt.

Zie hier.

En gezien je met grote datasets werkt: refereren naar hele kolommen kan ook veel geheugen kosten.

Zie hier.

[ Voor 21% gewijzigd door Triggy op 14-05-2019 11:16 ]

[Reinier]

Triggy schreef op dinsdag 14 mei 2019 @ 11:08:
[...]


Gedeeltelijk waar, hangt af van welke functies je gebruikt.

Zie hier.

Dank, goed om te weten!

[Tazzios]

Indien je de 'opmaken als tabel' functie gebruikt regelt Excel zelf een dynamische range.

[Gomez12]

Reinier schreef op dinsdag 14 mei 2019 @ 10:21:
[...]


Ik waag dat te betwijfelen. Excel kan dat vast wel efficiënt doen omdat hij bijhoudt waar de data in kolom B ophoudt.

Het nadeel van hiervan uitgaan is dat het in 99% van de gevallen goed werkt en net als het belangrijk wordt dan heeft iemand in rij 1 miljoen een spatie in kolom b gezet.

[Tazzios]

Tazzios schreef op dinsdag 14 mei 2019 @ 10:22:
[...]

Andere richting, gebruik een applicatie zoals Tableau prep builder (deze kan ook een csv output maken) of ETL software.

Ik zie zojuist dat Excel power pivot ook nog steeds bestaat.(gratis plugin van microsoft zelf)
Hiermee ging het geheugen gebruik van mijn excel bestand van 1,6GB naar 400MB en het is gemaakt om relaties te leggen tussen tabellen.

[ Voor 3% gewijzigd door Tazzios op 14-05-2019 12:48 ]

[Luuk013]

Power pivot zal ik eens wat informatie over gaan zoeken dan..

Ik heb alles opnieuw opgebouwd, en ik merk dat het meeste vertragende het sommen.als is. Is er een alternatief voor zoals bij verticaal zoeken met index en vergelijken?

[I-King]

Kijk ook even naar power query. Zeer krachtig en een stuk soepeler. Vanaf 2016 ingebakken geloof ik!

[Gomez12]

Luuk013 schreef op donderdag 16 mei 2019 @ 07:32:
Power pivot zal ik eens wat informatie over gaan zoeken dan..

Ik heb alles opnieuw opgebouwd, en ik merk dat het meeste vertragende het sommen.als is. Is er een alternatief voor zoals bij verticaal zoeken met index en vergelijken?

ALs je die sommen.als gewoon eens uitschrijft in 2 kolommen ipv 1. Dus 1 extra kolom waarin je enkel doet =als(b2="pietje";c2;0) en dan verderop die extra kolom sommen.

Of kijk gewoon eens goed naar je data... Van die 200.000 regels kunnen er daarvan ook reeel 200.000 veranderen? Of is 90% historische data die nooit meer gaat veranderen (en dus als waarde vastgezet kan worden) zodat je formules maar enkel over de laatste 10% (Plus 1 vastgezette waarde) hoeven te gaan.

Kan je bijv niet een subtotaal berekenen van alles voor 2019 en dat vastzetten?

[Luuk013]

Ik heb alles opnieuw opgebouwd, kwam erachter dat er veel lege regels waren, dus op die regels heb ik gekozen voor =als(a2="","",sommen.als(blabla waardoor hij pas gaat zoeken als er ook echt gezocht moet worden. Dat scheelde een hoop. Data verdeeld in maanden ipv jaren, scheelde ook veel. Het is nu redelijk bruikbaar maar wel jammer.. Daarnaast heb ik de methode van Gomez12 zo veel mogelijk nog toegepast, maar dat ging niet overal op.

Helaas hebben wij hier 2010 en kan ik niet zomaar andere software of plug-ins installeren omdat ik geen admin ben.

Bedankt voor jullie hulp!

[Gomez12]

Tja, dat is een beetje excels vloek en de reden dat ik er een absolute hekel aan heb.

Het kan teveel waardoor mensen het gaan inzetten op plekken waar gewoon gespecialiseerde software 1000x beter is.

Als je een database wilt richt dan een database is met een frontend, maar geen spreadsheet.

Een spreadsheet is leuk voor het tonen van data, en zo af en toe een formuletje kan ook nog wel, alleen wat je al heel snel krijgt is dat het aantal x aantal is etc.
Verticaal zoeken over 200.000 regels betekent toch bij1x die formule gebruiken dat excel 200.000 regels moet doorlopen. Doe je dat over die hele kolom dan praat je dus over 200.000 x 200.000 bewerkingen die uitgerekend moeten worden. Doe dat nog eens over 35 kolommen en nog wat andere functies en je praat over miljarden en miljarden bewerkingen die gestart moeten worden als er 1 waarde aangepast wordt.

Excel heeft daarvoor wel veel ingebouwde slimme caching etc alleen ergens trekt dat het ook niet meer simpelweg.

Excel is simpelweg niet bedoeld voor dit soort toepassingen. En de grote grap is dat er wel versnellingen zijn te behalen door het complexer te maken, alleen dan maak je het alleen maar complexer (met meer risico op fouten bij wijziging) en feitelijk is het gewoon de verkeerde tool voor de verkeerde functie.

[Luuk013]

Het uiteindelijke doel is om de ontworpen tool te verwerken in het ERP systeem van het bedrijf. tot die tijd hebben wij niets anders.

[Luuk013]

Nog een vraagje jongens, misschien heeft iemand een oplossing.

Ik heb dus 2 bestanden met verschillende meldingen met tijdstippen en apparaat nummers.

Bestand 1:
Melding met apparaat nummer met een bepaalde storing. tijdstip 1-2-2018 13:00:32
etc..

Bestand 2:
Melding van voorraad met serie nr 123 tijdstip 1-2-2018 14:20:23
Melding van voorraad met serie nr 123 tijdstip 1-2-2018 15:50:23
Melding van voorraad met serie nr 123 tijdstip 1-2-2018 16:20:23
Melding van voorraad met serie nr 256 tijdstip 1-2-2018 16:50:23
Melding van voorraad met serie nr 256 tijdstip 1-2-2018 17:40:23
Melding van voorraad met serie nr 256 tijdstip 1-2-2018 20:42:23

Nu is het mij gelukt deze aan elkaar te koppelen door gebruik te maken van afronden. zodat de tijden hetzelfde worden, dan met index vergelijken zoeken in beide bestanden naar afgeronde tijden Alleen dit is niet nauwkeurig, het probleem is dat wanneer ik afrond hij de getallen afrond tussen de series regels naar bijv. 16.00 waardoor het niet duidelijk is wat de melding geeft. de ene keer is het dit, de andere keer dat.

Nu zoek ik dus iets als: melding 13:22:33 in bestand 1 het eerst volgende tijdstip in bestand 2 lijst is 14:02:12 dus die moet ik hebben.

En anders dat hij het getal (tijdstip) pakt wat het dichtste in de buurt zit.

Nu weet ik dat als je tijdstippen op standaard zet dat excel dit naar een getal aanpast. misschien daar iets mee te doen dat ie het eerste getal pakt wat groter is dan het getal wat ik heb wat voldoet aan het zelfde apparaat nr etc...

[F_J_K]

Maak daar even een los topic voor aan ajb. Anders gaan er zaken door elkaar lopen en krijg je antwoord op de verkeerde vraag.