Misvattingen
Vaak wordt er door mensen nogal lacherig en minachtend gedaan dat Excel niet bedoeld is voor de verwerking en analyse van grote databestanden.
Een opmerking zoals ‘Excel is een spreadsheetprogramma en daar moet je het dan ook alleen voor gebruiken…’ vind ik nogal kort door de bocht. Beetje uit dezelfde categorie dat vrouwen in de keuken thuis zouden horen.
Als het werkt, dan werkt het toch en maakt het toch niet meer uit waar het oorspronkelijk voor bedoeld zou zijn? Komkommers en winterpenen zijn er in eerste instantie ook alleen voor gemaakt om op te eten.
Soms zijn zulke opmerkingen oprechte domheid van mensen. Kwalijker vind ik het dat men dan ook nog eens Excel zwart probeert te maken tegenover hun eigen geweldige product… tja…
Excel en grote databestanden
Heb je het bijvoorbeeld over 100.000 rijen data en je zet er wat formules naast dan is de kans groot dat Excel ontzettend traag wordt.
Dat klopt en heb ik zelf ook zo ervaren!
Maar Excel gaat met zijn tijd mee. Al heel wat jaren kun je in Excel ook gebruik maken van Power Pivot en Power Query, die wél goed met grote en meerdere databestanden om kunnen gaan.
Sterker nog, mensen die om die reden hebben gekozen voor bijvoorbeeld Power BI, weten vaak niet van het bestaan van Power Pivot en Power Query in Excel.
De functionaliteiten die Power BI voor het verwerken van grote databestanden gebruikt, zijn namelijk vrijwel dezelfde functionaliteiten die men in Power Pivot en Power Query al jarenlang daarvoor ook al gebruikte!
Zoals eerder gezegd, voor de meeste gebruikers zijn deze ‘Power’-opties in Excel echter onbekend terrein en ik moet eerlijk zeggen, het is voor mij soms ook nog wat zoeken en proberen. Maar heb recentelijk nog zonder problemen 35 miljoen rijen ingelezen.
Het duurt te lang?
En hoe lang was Excel daarmee bezig? Dat is natuurlijk afhankelijk van hoe snel je computer is en uit hoeveel kolommen zo’n bestand bestaat, maar je moet dan denken aan zo’n tien minuten. Maar als je dit geduld kunt opbrengen, heb je daar later ontzettend veel profijt van, want vanaf dat moment heb je geen last meer van enige traagheid. Bijvoorbeeld het maken van een draaitabel uit die miljoenen rijen is nu goed te doen!
Hoe werkt dat nou allemaal?
Ik ga er even vanuit dat je met de meest recente versie van Excel werkt, dus met Excel 2019.
Je zult daar de term Power Query echter nergens meer tegenkomen, maar in de volksmond heet het nog steeds zo. Het zit nu onder de tab ‘Gegevens’.
Vervolgens kun je met wat oefenen de gewenste bestanden binnenhalen. Is een te importeren bestand nu groter dan de beruchte 1.048.576 rijen in een tabblad, dan dien je de volgende keuzes te maken:
Excel zet de rijen dan niet in een tabblad, maar bewaart ze ergens achter de schermen van het Excel-bestand.
En dan?
Vervolgens kun je kolommen bewerken, verwijderen en ook toevoegen. In Power Pivot doe je dat laatste met behulp van de programmeertaal DAX. Dit heeft wat weg van het gewone Excel, van VBA en ook van tabellen (waar je in een formule niet naar een bepaalde cel, maar na een hele kolom verwijst). Groot voordeel daarvan is dat je maar in één regel de formule hoeft te plaatsen en die wordt dan automatisch doorgetrokken naar alle rijen. Ook als je de gegevens later vernieuwt en de lijst is langer geworden, dan worden daar ook automatisch de formules naast gezet.
Vanuit Power Pivot kun je draaitabellen aanmaken vanuit die miljoenen rijen. Die draaitabel komt dan weer in het ‘gewone’ Excel terecht en kun je mee gaan stoeien zoals je gewend was.
Piece of cake?
Met zoals zoveel dingen in het leven moet je ook dit niet even snel willen doen, maar er rustig de tijd voor nemen en er vanuit gaan dat het niet in één keer lukt. Met zo’n instelling komt het vast goed!