Waarschuwing vooraf: dit is een blog in categorie ‘de nerd in mij’….
Soms knutsel ik teveel aan mijn spreadsheets. En dan ontstaan er wel eens onbedoelde neveneffecten en/of onverwachte fouten. Die dan ook weer knutselarij kosten om op te lossen. En soms verzucht ik dan: ‘Geldnerd, blijf er eens vanaf… Het werkt toch…’.
Zo ook onlangs. Ik had een nieuwe functie gebouwd die per grootboekrekening per maand uitrekent wat ik er aan uitgegeven heb. Niet omdat het moet, maar omdat het kan… En het is handig voor sturing, ontdekken van seizoenspatronen, en het uitrekenen van het spaarpercentage per kwartaal (per maand doe ik niet, dat varieert teveel en daar word ik onrustig van).
Nu is het in Excel zo dat User Defined Functions (UDFs – door een gebruiker zelf geprogrammeerde functies) niet automatisch herberekenen. Tenzij je ze ‘volatile’ maakt. En dat had ik dus gedaan.
En daar kwam het neveneffect. Want de door mij geprogrammeerde functie ‘MaandBedrag’ komt (150 grootboekrekeningen maal 12 maanden) 1.800 keer voor in de spreadsheet. En die werden dus allemaal herberekend als ergens in de spreadsheet iets verandert. Dus de import van mijn bankboekingen, die normaal een paar seconden duurt, had ineens ruim 10 minuten nodig.
Dat was niet de bedoeling. Dus heb ik ‘Volatile’ maar weer uitgezet. En een andere list bedacht. Ik heb een ‘dummy-veld’ als parameter toegevoegd aan de ‘Maandbedrag’ functie. Want als een parameter wijzigt, wordt de functie wel automatisch herberekend. Als ik een herberekening wil (en dat is eigenlijk alleen aan het eind van een import van boekingen) dan hoog ik de waarde van de dummy op met 1. Dat triggert de ‘Maandbedrag’ functie en die wordt dan keurig bijgewerkt. Kost een paar seconden, maar niet 10 minuten.
Ik heb ook een wijziging doorgevoerd in het starten van Excel. Daar heb ik de parameter ‘/HIGH’ aan toegevoegd. Die zorgt ervoor dat Excel standaard meer processorcapaciteit en geheugen krijgt toegewezen. Want ik merkte dat Excel, zelfs tijdens zware berekeningen, maximaal 10 – 15 procent van de processorcapaciteit gebruikte. Nu is dat meer, en dat scheelt ook weer tijd.
Heb jij ook wel eens last van onbedoelde neveneffecten in je spreadsheets?
Dat soort dingen niet. Daar programmeer ik niet genoeg voor in excel. Ik moet nog wel iets verzinnen om de draaitabel waarmee ik mijn beleggingsportefeuille maak uit alle aankopen en verkopen automatisch laten updaten. Dus als je daar nog een tip voor hebt….
Ik zal even in mijn spreadsheet duiken om te kijken hoe ik dat ook alweer opgelost heb…
Ben ik ook benieuwd naar. Nu moet ik manueel refresh doen
Zie mijn antwoord bij de comment van Hypotheekweg.
Ik heb een simpele macro hiervoor. Die heb ik gekoppeld aan een knop op het werkblad waar de pivot staat. Een knop kun je maken vanuit het tabblad Ontwikkelaar | Invoegen | Knop.
De macro:
“).PivotCache.Refresh
——
Sub RefreshPivot()
‘ RefreshPivot Macro
ActiveSheet.PivotTables(“
End Sub uiteraard vervangt door de naam van het werkblad waarop de pivot tabel zich bevindt.
——
Waarbij je
Als je de knop indrukt, wordt de pivot tabel ververst.
Wat is het voordeel van een zelfgemaakte knop ten opzichte van ALT-F5 op je toetsenbord intikken? Lijkt mij dezelfde tijd kosten en je hebt je muis niet nodig (voordeel voor mij, werk liever met toetsenbord dan met de muis)
Bas
Ik heb nooit udf’s gemaakt, maar ik kan het me goed voorstellen. De ingebouwde functies in excel zijn gecompileerd en geoptimaliseerd voor snelheid, dat kan je nooit bereiken met zelfgemaakte functies. Dit probleem heeft bijvoorbeeld matlab ook.
Ja, dat klopt. Toch merk ik ook verschillen, LibreOffice Calc is een stuk sneller dan Excel op dezelfde laptop. Nog een extra reden om te migreren.
Je doet je blog naam wel eer aan met deze post. Ik hou het gewoon simpel en doe de meeste zaken manueel, zoveel tijd kost het niet en je kan gelijk zien als er iets vreemds tussen zit of iets niet klopt.
Ik vind het ook zo irritant om naar de losse maanden te kijken, veel te veel fluctuaties. Daarom heb ik een 12 maands moving average ingebouwd zodat de grafiek met de uitgaven en uitgaven categorien iets geleidelijker loopt. Aan het eind van het jaar heb je dan automatisch het gemiddelde over het hele jaar.
Bas