Excel

In het najaar van 2020 is de eerste serie instructies voor het werken met Power Query in Excel ontwikkeld en op YouTube geplaatst. Later zullen ook Nieuwe functies in Excel en Invoercontrole, de achilleshiel van Excel, behandeld worden.
Kenmerken van de instructiefilmpjes zijn: 



  • Rustig,
  • Nederlandstalig
    (zowel de uitleg als de versie van Excel)
  • met de volledige tekst in beeld.

Power Query

Introductie Power Query


Voor het uitvoeren van telkens terugkerende taken, bijvoorbeeld het splitsen van kolommen, omzetten van niet herkende gegevenstypen en ook het toepassen van Filters, is Power Query van Excel zeer geschikt.
In dit filmpje ziet u hoe Power Query wordt gestart, welke onderdelen zichtbaar zijn in het venster en als voorbeeld wordt een bestand ,dat eigenlijk niet geschikt is om te gebruiken voor het maken van een draaitabel, aangepast.


Gegevens splitsen


Met behulp van de opdrachten Tekst naar Kolommen en verschillende tekstfuncties zoals Links, Rechts en Deel zijn gegevens in Excel over meerdere kolommen te verdelen. Zeker voor gegevens in bestanden die regelmatig vernieuwd worden is het handig hiervoor de opties van Power Query te gebruiken.
U kunt dan aangeven hoe de kolommen gesplitst moeten worden, bijvoorbeeld bij de overgang van cijfers naar letters of omgekeerd, of op een bepaalde positie van het begin of juist van het einde. Of pas het gegevenstype aan zodat bijvoorbeeld voorloopnullen weer getoond worden en gegevens met een datum niet automatisch een tijd toegevoegd krijgen.
In dit filmpje wordt een bestand met bankencodes (BIC en IBAN) onder handen genomen en worden de gegevens verdeeld over verschillende kolommen. Ook ziet u wat de vermelding null betekent.

Gegevens uit een map


Ook voor het combineren van gegevens uit verschillende bestanden in een map is Power Query goed te gebruiken. In het voorbeeld worden de verkopen van vier filialen samengevat in een Power Query tabel die de basis is voor een draaitabel.
Wat gebeurt er als er in die map ook een afbeelding wordt geplaatst? Stel dat iemand in het Excel-bestand per ongeluk de kolommen heeft verwisseld of een kolomkop heeft aangepast? In dit filmpje ziet u de oplossing.
Komt er een nieuw filiaal dan kan het overzicht makkelijk bijgewerkt worden door het verkoopoverzicht in de gezamenlijke map te plaatsen en de Power Query-tabel te vernieuwen.

Gegevens uit PDF


Veel informatie wordt gedeeld in de vorm van pdf-bestanden. Gegevens uit een pdf-bestand kunnen met behulp van Power Query geschikt gemaakt worden voor verder gebruik in Excel. In dit voorbeeld worden gegevens van Eurostat gebruikt.
Het is een tabel waarin per land het aantal inwoners in de jaren 2009-2020 is opgenomen. De gegevens moeten zo bewerkt worden dat van de 10 landen met het grootste aantal inwoners een grafiek in de vorm van een kaart met cirkels die de bevolkingsomvang aangeven, gemaakt kan worden.
In dit voorbeeld wordt ook uitgelegd hoe getallen die met een decimale punt zijn opgemaakt (wordt standaard behandeld als tekst) omgezet kan worden in een getal met de decimale komma.
Haal zelf de gegevens op van de website: Eurostat

Gegevens van het web


Naast het importeren van gegevens uit PDF is het ook mogelijk om direct van webpagina’s te importeren. In het voorbeeld worden gegevens van hoog- en laagwater gebruikt. Deze gegevens zijn beschikbaar op de website van Rijkswaterstaat en worden zeer regelmatig bijgewerkt. Er zijn wel voorwaarden verbonden aan het gebruik van deze gegevens. Ook in dit voorbeeld wordt de informatie getransformeerd. Zo worden overbodige kolommen en rijen verwijderd, veldnamen ingesteld en zo bewerkt dat op elke regel ook de plaatsnaam wordt vermeld.
In Excel wordt vervolgens een lijngrafiek gemaakt waarbij de gebruiker met behulp van een slicer makkelijk een selectie kan maken.

Gegevens van het web


De meeste gebruikers van Excel zullen niet tegen de grenzen oplopen, maar er is een grens aan het aantal beschikbare rijen en kolommen. In dit voorbeeld wordt een bestand met de unieke adressen in Nederland , dat zijn er in 2017 meer dan 7,5 miljoen, bewerkt. Een tweede tabel met de gemeentenamen (388 in 2017) wordt gebruikt om ook de namen van de gemeenten te kunnen gebruiken. In dit voorbeeld worden query’s samengevoegd, kolommen verwijderd en twee tekstkolommen gecombineerd, dan worden de gegevens per postcode gegroepeerd. Ook wordt zowel op plaats als op aantal adressen gesorteerd. Na het groeperen blijven ruim 450.000 records over, die in Excel geïmporteerd worden. Om het makkelijk te maken om gegevens in de tabel op te zoeken wordt de nieuwe functie x.zoeken gebruikt. Deze functie is nog niet voor iedere gebruiker van Excel beschikbaar en is een alternatief voor de functie verticaal.zoeken.