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.

Nieuwe functies

Functie ALS.VOORWAARDEN


Veel gebruikers zijn op de hoogte van het nesten van Functies, bijvoorbeeld een geneste als-functie met meerdere testen en bijbehorende acties.
Vanaf Excel versie 2016 is de functie ALS.VOORWAARDEN met de varianten MIN.ALS.VOORWAARDEN en MAX.ALS.VOORWAARDEN hiervoor te gebruiken. U ziet een aantal voorbeelden. In de voorbeelden wordt ook gebruik gemaakt van HORIZ.ZOEKEN en komen de nieuwe functies UNIEK en SORTEREN aan bod.
Let wel deze functies zijn op dit moment alleen beschikbaar voor gebruikers van Excel 365.

Functie X.ZOEKEN


De functie X.Zoeken is veelzijdiger dan de vaak gebruikte functies verticaal- en horizontaal zoeken. Dat kan deze functie ook, bovendien kan worden aangegeven wat er moet gebeuren wanneer de zoekwaarde niet wordt gevonden, in welke richting er moet worden gezocht en of er een exacte overeenkomst moet zijn of de zoekwaarde benaderd moet worden.
Ook kan het resultaat uit meerdere gegevens bestaan die in opvolgende rijen of kolommen worden weergegeven.
Eerst wordt de syntax, de schrijfwijze doorgenomen en daarna volgen zeven praktische voorbeelden. De functie is alleen beschikbaar voor gebruikers van Excel 365.

Functie FILTER


Met behulp van Autofilter of geavanceerd filter kunnen gegevens in een bereik of tabel in Excel gefilterd worden. Bijna iedereen die met Excel werkt kent het knop Autofilter wel. Maar er is ook een functie filter.
Deze functie is nieuw en alleen beschikbaar voor gebruikers van Excel 365. Net als andere functies levert deze functie een gefilterde lijst op buiten een tabel of bereik. Er kan een eenvoudige voorwaarde worden opgesteld of een combinatie. Daardoor is het mogelijk zogenaamde EN-, OF-voorwaarden op te geven.
Door gebruik te maken van geneste FILTER-functies is het ook mogelijk specifieke kolommen (waaronder niet aaneengesloten kolommen) in het resultaat weer te geven. Zorg wel voor voldoende vrije ruimte want anders volgt de foutmelding #Overloop.

Functie Reeks


De functie Reeks (alleen beschikbaar voor gebruikers van Excel 365!) is voor veel gebruikers onbekend.
Vaak wordt de vulgreep gebruikt om een reeks met getallen of datums in te voeren. In dit voorbeeld wordt de functie reeks gebruikt om een kalender te maken. Daarbij wordt Voorwaardelijke opmaak gebruikt in combinatie met formules om de kalender overzichtelijk te maken.
Andere functies die aan bod komen zijn: Datum, Iso.Weeknummer, Weekdag en Rest.

Invoercontrole

Invoercontrole of Gegevensvalidatie


Invoercontrole is de achilleshiel van Excel. Is de invoer niet goed dan zal het resultaat van eenvoudige tot ingewikkelde formules, nooit goed zijn. Met behulp van gegevensvalidatie kan ingesteld worden aan welke voorwaarden de invoer moet voldoen. Dat kan een eenvoudig lijstje zijn, bijvoorbeeld M of V voor man/vrouw of jongen/meisje en N voor Neutraal of een datumcontrole en met behulp van functies kan van alles gecontroleerd worden. Aan de hand van praktijkvoorbeelden worden verschillende opties besproken, waaronder het voorkomen dat twee keer hetzelfde lidmaatschapsnummer wordt gebruikt of dat anderhalf pakje batterijen kan worden besteld. Ook wordt de elftalkeuze voor een voetbalvereniging onder de loep genomen. Pupillen worden in andere elftallen ingedeeld dan junioren. Dit wordt met een afhankelijke keuzelijst gedaan. Wordt achteraf gegevensvalidatie ingesteld dan kunnen met cirkels ongeldige gegevens worden weergegeven. Dit maakt het verbeteren overzichtelijker.

© Imagine...IT 2020