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

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 66: For m = 32 To 126                         ActiveSheet.Unprotect Chr(a) & Chr(b) & _   Chr(c) & Chr(d) & Chr(e) & Chr(f) & _   Chr(g) & Chr(h) &  Chr(I) & Chr(j) & C
1 reactie
Meer lezen

Excel 2013: uniek aantal in draaitabel

Tot en met versie 2010 was het in Excel lastig om in een draaitabel een uniek aantal (DISTINCT COUNT) te tellen. We geven een voorbeeld op basis van een verkoperslijst. In deze lijst kunnen we zien welke verkopers welke artikelen hebben verkocht. Willen we nu in een draaitabel laten zien hoeveel artikelen een verkoper heeft verkocht, dan krijgen we wel de aantallen maar niet de unieke aantallen te zien. Om toch de unieke aantallen te laten zien, hebben we een aantal stappen nodig. Op het moment dat we de draaitabel invoegen, krijgen we in Excel 2013 dit dialoogvenster: Onderaan zien we daar een nieuwe optie: Deze gegevens toevoegen aan het gegevensmodel . Deze optie moeten we aanvinken, voor we op OK klikken. We krijgen dan een iets ander beeld dan normaal: Normaliter krijgen we alleen de veldnamen. Nu zien we er het woord Bereik boven staan. Voor het voorbeeld heb ik nu Verkoper toegevoegd aan Rijen en Artikelomschrijving aan Waarden . Het resultaat is identiek
Een reactie posten
Meer lezen

Excel: laatste datum voor een groep, draaitabel of matrixformule?

Via een Excel groep krijg ik de vraag hoe je de laatste datum voor een groep er uit kunt pikken. We geven hier even de voorbeelddata: Voor zover ik kan zien zijn er in ieder geval twee mogelijkheden: met matrixformules en met een draaitabel . Oplossing: draaitabel We zullen het in dit voorbeeld maar even helemaal volgens de regels van de Excel kunst doen. Voor het maken van de draaitabel heb ik de lijst eerst omgezet naar een tabel ( INVOEGEN => DRAAITABEL ). De naam veranderen we dan even van Tabel1 in draaitabel . Vervolgens maken we de draaitabel. Via Waardeveldinstellingen kiezen we dan voor het datumveld voor Max en bij Getalnotatie voor Datum . De kopjes zetten we even om naar Naam en Laatste datum . Klaar. Oplossing: matrixformules Voor dat we de matrixformules gaan maken, creëren we eerst namen met flexibele bereiken: datum =VERSCHUIVING(Blad1!$B$2;0;0;AANTALARG(Blad1!$B:$B)-1;1) naam =VERSCHUIVING(Blad1!$A$2;0;0;AANTALARG(Blad1!$A:$A
Een reactie posten
Meer lezen