Doorgaan naar hoofdcontent

Excel: performance of hoe optimaliseer ik grote spreadsheets II

Excel en performance

Ik krijg de laatste tijd herhaaldelijk verzoeken om een blik te werpen op Excel spreadsheets die te traag en onhandelbaar zijn geworden. Men vraagt mijn dan als VBA expert, maar is VBA altijd de (enige) oorzaak?

Laten we eens de factoren op een rijtje zetten die invloed hebben op de snelheid.

Grootte van het bestand.

Dit is misschien wel de belangrijkste factor. Zodra een bestand erg groot wordt, kan het opslaan en openen erg lang gaan duren. Het XML formaat heeft bestanden weliswaar flink verkleind, maar tegelijkertijd is het grotere aantal rijen en kolommen ook een uitnodiging geweest nog veel meer gegevens op ter nemen.

Herkomst van de data.

Data kunnen verschillende herkomst hebben, bijvoorbeeld:

  • In Excel zelf; voorbeeld 121.317 regels uit SalesOrderDetail van AdventureWorks: grootte 9.625 kb.
  • Koppeling met een ander Excel bestand.
  • Via Microsoft Query en ODBC; voorbeeld 121.317 regels uit SalesOrderDetail van AdventureWorks: grootte 9.414 kb.
  • Via Power Query en SQL Server; voorbeeld 121.317 regels uit SalesOrderDetail van AdventureWorks: grootte 9.536 kb.
Wat betreft grootte ontloopt het elkaar dus niet zo veel. Wel zijn data die via externe verbindingen binnenkomen zo te filteren dat Excel ook alleen die data bevat die uitgefilterd zijn. SalesOrderDetail gefilterd op ProductId 777 is nog maar 35 kb groot. Via een Excel eigen filter filteren maakt voor de bestandsgrootte vrijwel niets uit.

Excel formules.

Er is een aantoonbaar verschil in performance tussen verschillende functies die wel het zelfde resultaat hebben. Zo is de functie INDEX sneller dan VERT.ZOEKEN. Kunnen we het een en ander testen? Jawel! De voorbeelden zijn op snelheid van uitvoering te vergelijken met behulp van VBA routines. 

Excel draaitabellen

Draaitabellen kunnen een Excel bestand veel groter en trager maken. Een simpel voorbeeld met een draaitabel op basis van de tabellen Product en
SalesOrderDetail uit Adventureworks. Direct in Excel: grootte bijna 17 MB. Via PowerPivot: iets meer dan 7 MB.

VBA routines.

Veel VBA maakt een bestand niet veel groter en trager. Wel maakt het uit wat er met VBA gedaan wordt. Zo maakt het wat betreft snelheid enorm uit of we een kolom met getallen via een VBA loop procedure optellen of dat we daarvoor de reguliere Excel functie SOM gebruiken. Zo kan het binnen VBA veel schelen via WorksheetFunction een beroep te doen op reguliere Excel functies. Ook kunnen we de werklast slim verdelen: het rekenwerk in Excel zelf en de variatie van bijvoorbeeld parameters met VBA.

Bestandsformaat.

Excel kent de formaten XLSX, XLSM en XLSB. Ze zijn vergelijkbaar in die zin dat ze allemaal ZIP bestanden zijn. We kunnen de inhoud zichtbaar maken als we de extensie vervangen door ZIP en ze vervolgens openen. XLSB is niet XML-gebaseerd based maar in een binair formaat. En dat is vooral voordelig als je met grote bestanden werkt. 

XLSX en XLSM bestanden laden vier keer langzamer dan XLSB en het opslaan ervan durt twee keer zo lang. Het uiteindelijk bestand is anderhalf keer zo groot.  Getest met een Excel bestand met tienduizend rijen en duizend kolommen met simpele formules.

Bij een Excel bestand dat zijn gegevens via PowerPivot ophaalt, kon ik geen verschil zien tussen opslag in XLSB of XLSX.

Het enige echte nadeel van het XLSB formaat is compatibiliteit: als er gewerkt met software die XML nodig heeft in plaats van binair.

Alle formaten ondersteunen dezelfde functionaliteit.

Tips.

  • Volgens mij is het belangrijkste punt bij Excel dat je de inhoud beperkt moet houden. Doe je dat niet, dan kan zeker in combinatie met bepaalde berekeningen, kunnen de bestanden heel erg traag worden.
  • Als je gebruik maakt van externe gegevens uit een database, laat dan - in het geval grote hoeveelheden data - de berekeningen zoveel mogelijk in de database doen en niet in Excel zelf. Als we Excel zelf de berekeningen uit laten voeren zal bij elke verandering vaak heel veel formules opnieuw doorgerekend moeten worden.
  • Draaitabellen kunnen wel tot honderd keer of meer sneller rekenen dan functies als SOMMEN.ALS.
  • Gebruik PowerPivot voor draaitabellen. Uiteraard zorgt dit instrument ervoor dat de data in de database blijven. De draaitabel maakt het Excel bestand niet veel groter. 
  • Een normale draaitabel maakt het bestand wel veel groter maar dit is aan te passen door bij de Optie voor draaitabel de optie Brongegevens bij bestand opslaan uit te zetten.
  • Opslag als binair bestand, XLSB, verkleint grote bestanden aanzienlijk en maakt laad- en opslagtijd korter. 
Voor verder Excel tips klik hier.

Labels:

Reacties

Een reactie posten

Populaire posts van deze blog

Friesland: nu ruim 1500 Friese familiewapens doorzoekbaar

Inleiding Het totale overzicht is nu ook als PDF te downloaden: rptWapens Al jaren verzamel ik de teksten van grafzerken en andere voorwerpen om ze in een database bijeen te brengen. Op heel veel oude grafzerken, rouwborden of andere voorwerpen in Friesland treffen we naast tekst ook vaak familiewapens aan. Ruim een jaar geleden ben ik gestart met het koppelen van familiewapens aan deze teksten. Met behulp van deze database kunnen we nu steeds meer voorheen onbekende wapens thuisbrengen of andersom, initialen herleiden tot de bijbehorende namen. Een voorbeeld. Zo kennen we uit het boek Fries Zilver nummer 894 met de tekst: TDH RBB 1669 Tjitte D. Wijngaarden geboren 6 october 1874 Daarbij een mannen- en een vrouwenwapen. Mannenwapen blijkt dat van Hoitinga te zijn. De letters blijken vervolgens te herleiden tot Tjepke Douwes Hoitinga en Rienkje Bouwes Bruinsma uit Nijland. Vermoedelijk getrouwd in 1669! Opgelost. Ander voorbeeld. Op een lepel uit het boek Dokk...
1 reactie
Meer lezen

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik: Sub WachtwoordCrack()     Dim a As Integer, b As Integer, c As Integer, d As Integer, _     e As Integer, f As Integer, g As Integer, h As Integer, _  I As Integer, j As Integer, k, m As Integer     Dim begin As Date, eind As Date     Dim duur As String     Dim objSheet As Worksheet     begin = TimeValue(Time)     On Error Resume Next     For Each objSheet In Application.Worksheets         For a = 65 To 66: For b = 65 To 66: For c = 65 To 66             For d = 65 To 66: For e = 65 To 66: For f = 65 To 66                 For g = 65 To 66: For h = 65 To 66: For I = 65 To 66                     For j = 65 To 66: For k = 65 To...
1 reactie
Meer lezen

Excel: Creating Your Own Tabs With XML And VBA

In order to create you own nice looking tabs you need the Custom UI Editor for Microsoft Office. You can download it for free. How is this different from choosing File , Options , Customize Ribbon and adding a new tab with new groups and then commands to the group? You can also move the new tab to any position. T hen your new tab is always there. Even when you don't open the specific file with the right VBA. In my example the outcome looks like this, a nice tab, placed in front of the Home Tab. You will only get this tab when you start the specific example (which you can download). The XML behind this: <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">    <ribbon startFromScratch="false">     <tabs>       <tab id="MaxIlze" label="Dossier Overzicht" insertBeforeMso="TabHome">         <group id="customGroup1" label="Adressen verrijken">         ...
Een reactie posten
Meer lezen