Complex en traag excel bestand ombouwen

[JJ de zeeuw]

Goedemiddag,

De vraag die ik nu ga stellen zal zeer waarschijnlijk ver boven mijn eigen kennis gaan, dus handig om vooraf te weten.

Ik afgelopen jaar heb ik met behulp van jullie hulp een complex maar zeer goed werkend excel bestand opgebouwd.

Inhoud en doel bestand:
Een systeem bouwen wat door uitvoerder gebruikt kan worden voor het registreren van activiteiten op de projecten en mbv automatisering dit kunnen gebruiken voor diverse handelingen, zoals opdrachtbonnen en mandagenregisters maken. Verder wordt aan de hand van dit systeem heel inzichtelijk gemaakt welke kosten er zijn gemaakt voor een project. De beide excel bestanden moeten open staan om zo tarieven over te nemen uit de brongegevens.

Problemen huidig
Het systeem zit in een half jaar op 19000 gevulde rijen (natuurlijk niet in testbestand ingevuld aanwezig), over algemeen zijn we tevreden of hoe het werkt en het is redelijk waterdicht. Wat wel een probleem is dat het autofilter, opslaan steeds langer gaat duren, tot erg lang toe. Verder neemt de bestandsgrote ook te grote vormen aan tot zo een 19 MB, wat voor emailverkeer via een dongel niet leuk maakt of soms onmogelijk.

Doel oplossing
Graag zou ik een bestand als eindproduct krijgen wat qua layout en werking totaal niet veranderd. Maar wel sneller en kleiner wordt. Ben al enige tijd aan zoeken, oplossingen waren "gevulde" cellen natrekken, als dit meer zijn dan werkelijk nodig deze verwijderen (geen resultaat en niet aan orde), celopmaak terug brengen naar minimun of eventuele opvulkleuren (tot minimum terug gebracht.) Logo's tijdelijk verwijderd.
Dit alles had geen doel.

Nu schijnt het zo te wezen dat als je kolommen het waarin grote aantallen dezelfde formules zijn ingevoerd, je dit beter kunt inrichten met macro's waardoor het aanzienlijk sneller en kleiner wordt (net wat mijn doel is).

Het probleem is echter dat ik zelf nooit met macro's heb gewerkt en daarom wil ik hier mijn testbestand voorleggen. Mogelijk kan iemand mij opweg helpen of de macro's in het testbestand invullen.

Ik hoop dat iemand kan helpen, me weer een stap verder te brengen, heb er alle vertrouwen in want vorige malen zijn we ook ver gekomen.

Juist nu ik mijn bestand wil gaan inpakken om het als zip te plaatsen kom ik er achter dat het bestand van 19 MB naar 294 KB is gegaan dus een aanmerkelijke verkleining. Ik denk dus zelf dat de oorzaak in het verkleinen van het aantal formulles zit door het aantal ingevulde rijen van 20000 terug te brengen naar 10 voor de test.


Alvast bedankt groeten,

Jan

Hierbij de links naar de bijlagen:
http://www.helpmij.nl/leden/epic/Brongegevens.xlsx
http://www.helpmij.nl/leden/epic/Test projectadministratie 2010.xlsx

 

[Efsix]

Goedemiddag,

De vraag die ik nu ga stellen zal zeer waarschijnlijk ver boven mijn eigen kennis gaan, dus handig om vooraf te weten.

Ik afgelopen jaar heb ik met behulp van jullie hulp een complex maar zeer goed werkend excel bestand opgebouwd.

Inhoud en doel bestand:
Een systeem bouwen wat door uitvoerder gebruikt kan worden voor het registreren van activiteiten op de projecten en mbv automatisering dit kunnen gebruiken voor diverse handelingen, zoals opdrachtbonnen en mandagenregisters maken. Verder wordt aan de hand van dit systeem heel inzichtelijk gemaakt welke kosten er zijn gemaakt voor een project. De beide excel bestanden moeten open staan om zo tarieven over te nemen uit de brongegevens.

Problemen huidig
Het systeem zit in een half jaar op 19000 gevulde rijen (natuurlijk niet in testbestand ingevuld aanwezig), over algemeen zijn we tevreden of hoe het werkt en het is redelijk waterdicht. Wat wel een probleem is dat het autofilter, opslaan steeds langer gaat duren, tot erg lang toe. Verder neemt de bestandsgrote ook te grote vormen aan tot zo een 19 MB, wat voor emailverkeer via een dongel niet leuk maakt of soms onmogelijk.

Doel oplossing
Graag zou ik een bestand als eindproduct krijgen wat qua layout en werking totaal niet veranderd. Maar wel sneller en kleiner wordt. Ben al enige tijd aan zoeken, oplossingen waren "gevulde" cellen natrekken, als dit meer zijn dan werkelijk nodig deze verwijderen (geen resultaat en niet aan orde), celopmaak terug brengen naar minimun of eventuele opvulkleuren (tot minimum terug gebracht.) Logo's tijdelijk verwijderd.
Dit alles had geen doel.

Nu schijnt het zo te wezen dat als je kolommen het waarin grote aantallen dezelfde formules zijn ingevoerd, je dit beter kunt inrichten met macro's waardoor het aanzienlijk sneller en kleiner wordt (net wat mijn doel is).

Het probleem is echter dat ik zelf nooit met macro's heb gewerkt en daarom wil ik hier mijn testbestand voorleggen. Mogelijk kan iemand mij opweg helpen of de macro's in het testbestand invullen.

Ik hoop dat iemand kan helpen, me weer een stap verder te brengen, heb er alle vertrouwen in want vorige malen zijn we ook ver gekomen.

Juist nu ik mijn bestand wil gaan inpakken om het als zip te plaatsen kom ik er achter dat het bestand van 19 MB naar 294 KB is gegaan dus een aanmerkelijke verkleining. Ik denk dus zelf dat de oorzaak in het verkleinen van het aantal formulles zit door het aantal ingevulde rijen van 20000 terug te brengen naar 10 voor de test.


Alvast bedankt groeten,

Jan

Jan, ik krijg je zip niet geopend

groet, Romé

 

[JJ de zeeuw]

Het bestand is een kleine 300 KB groot dus kan het niet uploaden hier direct. Dus ik heb (voor het eerst) een bestand weggezet op mijnbestand.nl maar waarschijnlijk werkt dat niet.

Kun je me vertellen hoe ik het wel kan doen of welke site ik beter kan gebruiken, dan kunnen we weer stap verder.

 

[popipipo]

Ik kreeg hem ook niet rechtstreeks geopend.
Na eerst het opgeslagen te hebben en dan proberen te openen vroeg hij om een wachtwoord.
En die heb ik uiteraard niet.

 

[JJ de zeeuw]

Ik kreeg hem ook niet rechtstreeks geopend.
Na eerst het opgeslagen te hebben en dan proberen te openen vroeg hij om een wachtwoord.
En die heb ik uiteraard niet.

Als iemand mij verteld waar ik het kan uploaden zal ik dan doen en komen we verder want ik heb er nooit wachtwoord opgezet, dus er zal iets anders fout zijn denk ik.

 

[JJ de zeeuw]

Ik heb diverse wegen geprobeerd maar ik kom niet verder, kan iemand even zeggen hoe ik het moet doen, want anders vrees ik dat niemand me verder kan helpen.
Heb geprobeerd via de site's Esnips en Yousendit

Wie helpt mij even verder?

Gr. Jan

 

[JJ de zeeuw]

Zo de bijlagen zijn als links toegevoegd in het eerste bericht.

Dus als iemand zin heeft.

Groeten,

Jan

 

[JJ de zeeuw]

Kan iemand mij verder helpen hiermee?

 

[cow18]

1. dat 2e bestand waarnaar er koppelingen zitten bevat niet veel nuttige info. Het zou misschien gemakkelijker zijn om die info gewoon in het huidige bestand te kopieren zodat die koppelingen weg zijn. Als je dan met gedefinieerde namen gaat werken is die moeilijke indirect(substitueer.. -constructie ook eruit.
2. 20.000 regels, daarvan zijn er stel 15.000 louter archief, daar verander je nooit meer iets aan. Dus zou je kunnen zeggen alles ouder dan 6 maand kopieer die boel en plakken speciaal als waarden op dezelfde plaats, gevolg geen formules meer, enkel het resultaat.

 

[JJ de zeeuw]

1. dat 2e bestand waarnaar er koppelingen zitten bevat niet veel nuttige info. Het zou misschien gemakkelijker zijn om die info gewoon in het huidige bestand te kopieren zodat die koppelingen weg zijn. Als je dan met gedefinieerde namen gaat werken is die moeilijke indirect(substitueer.. -constructie ook eruit.
2. 20.000 regels, daarvan zijn er stel 15.000 louter archief, daar verander je nooit meer iets aan. Dus zou je kunnen zeggen alles ouder dan 6 maand kopieer die boel en plakken speciaal als waarden op dezelfde plaats, gevolg geen formules meer, enkel het resultaat.

Bedankt voor uw reactie!

1. het klopt dat bij dit voorbeeld bestand in de brongegevens niet veel nuttige informatie staat, dit komt echter omdat het sterk vereenvoudigd is als voorbeeld. In werkelijkheid staan hier ruim 25 bedrijven (ieder appart tabblad) met alle prijsafspraken. Zijn hier wijzigingen is kan ik deze doorvoeren in dit bestand en aan de uitvoerders toebedelen waardoor zij ook weer de meest recente informatie hebben.
2. inderdaad zit daar een kern van waarheid in en zou ik kunnen doorvoeren om te zien wat dat oplevert. Echter in tabbladen als opdrachtbon, blijven nog steeds die 20000 regels met formules omdat dat maar een fictieve weergave is van het hoofdbestand, puur een andere volgorde tbv een compacte opdrachtbon of mandagenregister.

De eerste zal ik testen en kijken wat dat oplevert. Maar naar wat ik heb gelezen moet hoe het nu opgezet is ook om te bouwen zijn naar macro's maar geen idee hoe ik daar aan moet beginnen en of het mogelijk is.

 

[JJ de zeeuw]

Ik heb de test om plakken speciaal net doorgevoerd, voor ruim 12000 regels, dit levert op 20 mb een 'besparing' op van 1 mb, dus helaas nog niet echt heel veel.

inmiddels in mijn 'verwijzende' tabbladen ook doorgevoerd, dit levert een besparing op van nog eens 7 mb en kom nu op 12,5 mb, dus wel aanzienlijke verbetering, maar nog steeds geen bestand wat je met een dongel vannuit het 'veld' naar het kantoor kunt mailen.

 

[cow18]

moet ik het anders eens privé bekijken, geef anders hier even je emailadres op, dan stuur ik je mijn emailadres en dan wisselen we dat bestandje even uit.

 

[JJ de zeeuw]

moet ik het anders eens privé bekijken, geef anders hier even je emailadres op, dan stuur ik je mijn emailadres en dan wisselen we dat bestandje even uit.

Liever geef ik het oorspronkelijke bestand niet vrij aangezien hier bedrijfsgevoelige informatie in staat.

In het excel bestand Brongegevens staat puur de info zoals ik nu online heb gezet. Heb alleen als voorbeeld nu maar twee bedrijven er ingezet, namelijk Testbedrijf A en Testbedrijf B. Mij lijkt dat het niet uitmaakt voor het voorbeeld dat er nu maar twee bedrijven staan ipv 25, of zie ik dit verkeerd?

Fijn dat je meedenkt trouwens hoor, want ik ben zelf op deze manier toch al weer wat verder gekomen, maar mijn doen is nog lang niet bereikt.
Waar denk je zelf dat het dan in zit, mede door je vraag naar het origineel?

 

[Roncancio]

Het maakt inderdaad niet uit of er 2 of 25 bedrijven vermeld staan.
In je 1e reactie schreef je dat je de opmaak al hebt verminderd.
De opmaak in Excel (vooral via kopiëren) is berucht voor het vergroten van het bestand.
Je zou in een kopie van het bestand de cellen kunnen selecteren (blokje boven de 1 en naast de A) en vervolgens de opmaak geheel verwijderen.

Met vriendelijke groet,


Roncancio

 

[JJ de zeeuw]

Het maakt inderdaad niet uit of er 2 of 25 bedrijven vermeld staan.
In je 1e reactie schreef je dat je de opmaak al hebt verminderd.
De opmaak in Excel (vooral via kopiëren) is berucht voor het vergroten van het bestand.
Je zou in een kopie van het bestand de cellen kunnen selecteren (blokje boven de 1 en naast de A) en vervolgens de opmaak geheel verwijderen.

Met vriendelijke groet,


Roncancio

Levert slechts een paar honderd kb, en ben dan alle opmaak kwijt inclusief euro tekens enz....

 

[Roncancio]

Levert slechts een paar honderd kb, en ben dan alle opmaak kwijt inclusief euro tekens enz....

Dat schiet inderdaad niet op.
Heb je steeds al de gegevens nodig of loont het de moeite om de gegevens ergens te bewaren?

Met vriendelijke groet,


Roncancio

 

[cow18]

ik heb nog niet gehoord of je die koppelingen al verwijderd hebt, door een kopie van die gegevens van je 2e bestand in dit bestand te zetten en daarnaar te verwijzen ipv de koppelingen, dat zal in opslagruimte niets uitmaken wel in de rekentijd ... .

 

[JJ de zeeuw]

Dat schiet inderdaad niet op.
Heb je steeds al de gegevens nodig of loont het de moeite om de gegevens ergens te bewaren?

Met vriendelijke groet,


Roncancio

Uitvoerders gebruiken dit bestand voor het registeren van activiteiten op de projecten, door de diversiteit, zijn dit inderdaad allemaal actuele en regelmatig te gebruiken bedrijven. Bedrijven waar we niet veel zaken mee doen zet ik de tarieven er niet bij.

Dus daarin nog verder te schuiven zie ik zo snel geen mogelijkheden.

 

[JJ de zeeuw]

ik heb nog niet gehoord of je die koppelingen al verwijderd hebt, door een kopie van die gegevens van je 2e bestand in dit bestand te zetten en daarnaar te verwijzen ipv de koppelingen, dat zal in opslagruimte niets uitmaken wel in de rekentijd ... .

Je bedoelt dat ik dus de +- 25 tabbladen met de verschillende bedrijven uit brongegevens in het hoofdbestand moet zetten?
Dit haalt het doel van het bestand Brongegevens onder uit in mijn ogen (tarieven zijn dan alleen in de uitvoerdersadminstratie bij te werken door per bedrijf de tarieven te overschrijven....)

Persoonlijk vind ik het doel van de grootte van het bestand op dit moment zwaarder wegen, aangezien dat op dit moment het meeste problemen geeft. Snelheid is variabel, soms gaat het goed, maar soms duurt bijvoorbeeld het opslaan ook al enige tijd...

Zou het mogelijkwezen om de gebruikte formules om te zetten in macro's en dezelfde functionaliteit te houden zoals het nu is?

 

[cow18]

Nu heb je koppelingen naar je brongegevens, als je VBA laat werken, dan neemt hij de gegevens van dat ogenblik, dus veronderstel dat de prijzen zich aanpassen in je bronbestand, dan wordt dat niet meer meegenomen. Nu lijkt me dat bijna meer een voordeel dan een nadeel, maar je moet dat dus weten. Natuurlijk zou je er nadien nog een keer kunnen op gaan staan en iets wijzigen, dan heb je de nieuwe prijzen mee.
Tegelijk zijn we van die lompe indirect-formule af.
Je bronbestand moet eigenlijk ook maar open staan als je zaken gaat veranderen, niet als je zaken opzoekt.