Categorie archief: Excel

Gegevensvalidatie

Excel, , , ,
Voorbeeldbestand: GegVal.xlsxDownload

In andere artikelen op deze site is al regelmatig gebruik gemaakt van Gegevensvalidatie. Meestal om er voor te zorgen dat er bij het invoeren van gegevens geen ‘vervuiling’ ontstaat (is de plaatsnaam nu Wanroy of Wanroij, moet de afdeling aangeduid worden met Marketing of met Verkoop, heet die medewerker nu Eric Jansen of Erik Janssen?)

Bij iedere Excel-sheet, waarin gegevens worden ingevoerd, is consistentie van groot belang om te zorgen dat bij analyses of rapporten de juiste zaken bij elkaar worden genomen.

In dit artikel kijken we naar de mogelijkheden van Gegevensvalidatie. Daarbij maken we gebruik van keuzelijsten. Niet echt ingewikkeld, maar het wordt wel leuk als we gebruik gaan maken van ‘meervoudige’ of afhankelijke keuzelijsten.

Gegevensvalidatie

In het Voorbeeldbestand op het tabblad Decl staat een ‘systeem’ waarmee binnen een bedrijf declaraties kunnen worden vastgelegd.

Niet alle invoer is toegestaan; aan alle 4 kolommen worden bepaalde eisen gesteld.

  1. Datum: in de cellen H2 en H3 (met de namen DtmVan en DtmTot) liggen een onder- en bovengrens vast.
    De ondergrens is een harde datum, de bovengrens is de datum van vandaag.
  1. Maand: dit is de maand waarop de declaratie betrekking heeft. Deze moet altijd kleiner of gelijk zijn aan de declaratiedatum
  2. voor Afd zijn maar 6 verschillende namen toegestaan
  3. het Bedrag moet altijd groter zijn dan 0 en kleiner dan 1000

Datum

  1. selecteer alle cellen uit de eerste kolom (behalve de kop natuurlijk)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie

  3. In de tab Instellingen zorgen we dat er alleen een datum is toegestaan.
    Bij Begindatum vullen we de naam van de betreffende cel in (druk op F3 en kies de naam), bij Einddatum verwijzen we uiteraard naar de betreffende cel.
    LET OP bij de validatie worden de twee grenzen als toegestaan gerekend
    NB1 vul je de naam handmatig in, vergeet dan het =-teken niet!
    NB2 in ons voorbeeld is het onderste vinkje niet nodig (we hebben alle betreffende cellen geselecteerd). Wil je later een wijziging aanbrengen, kies dan één cel en plaats het vinkje.
    NB3 de datum zal altijd ingevuld moeten worden; verwijder dus het vinkje bij Lege cellen negeren.
  1. Op de tweede tab kun je een Invoerbericht maken. Deze info wordt zichtbaar wanneer de betreffende cel is geselecteerd.
  1. Het is ook mogelijk om een eigen foutmelding te genereren die getoond wordt wanneer de invoer niet overeenkomt met de aangegeven grenzen.
    Wanneer je als Stijl de optie Stop kiest, zal Excel een juiste invoer afdwingen:

Maand

  1. selecteer alle cellen uit de tweede kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. Bij Toestaan kiezen we de optie Aangepast.
    Excel biedt dan de mogelijkheid om een formule in te tikken:
    =C3<=MAAND(B3)
    LET OP deze formule gaat er van uit dat een cel in regel 3 actief/geselecteerd is; anders een verwijzing naar de betreffende regel gebruiken.
  1. Ook voor deze kolom is een Invoerbericht aangemaakt.
  1. Een foutmelding geeft de gebruiker houvast wanneer de invoer niet juist is.
    Hier hebben we bij Stijl gekozen voor Waarschuwing; de gebruiker kan er dan altijd voor kiezen om toch door te gaan:

Afd

  1. selecteer alle cellen uit de derde kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. de toegestane afdelingsnamen staan in de cellen J3:J8.
    NB Zorg dat het vinkje aanstaat bij Vervolgkeuzelijst; de gebruiker kan dan kiezen uit de lijst en hoeft niets handmatig in te tikken.
  1. Op de tab Foutmelding is de Stijl Info gekozen. Dat betekent dat bij een ‘verkeerde’ invoer wel een waarschuwing op het scherm komt:

    maar door het klikken op OK, wordt de invoer toch geaccepteerd.

Bedrag

  1. selecteer alle cellen uit de vierde kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. voor het bedrag kiezen we bij Toestaan de optie Decimaal.
    Als grenzen wordt verwezen naar 2 cellen met de namen BedrVan en BedrTot.
    LET OP de waardes van de grenzen zijn allebei toegestaan.

Controle

Wil je achteraf weten of er toch nog fouten zitten in de invoer, kies dan in de menutab Gegevens in het blok Hulpmiddelen voor gegevens het vinkje onder de optie Gegevensvalidatie:

Excel omcirkelt alle foute invoer. De naam test2 hoort uiteraard daar niet thuis.

In de lege regel van de tabel tblDeclaraties wordt aangegeven, dat de Datum en de Afd verplicht zijn.

Wil je de ovalen rond de fouten weer verwijderen: kies de derde optie bij Gegevensvalidatie.

Afhankelijke keuzelijst

In het vorige voorbeeld hebben we gebruik gemaakt van Namen om te verwijzen naar bepaalde cellen. Bij de Afd bestaat de toegestane Lijst uit een bereik/range van cellen. Ook die hadden we met behulp van een naam kunnen aangeven.

Op het tabblad AfhKeuze van het Voorbeeldbestand is de kolom Afd van een gegevensvalidatie voorzien door als Bron te kiezen voor de naam Afd.
Dit is een verwijzing naar het bereik G2:I2

Maar ook de bereiken met persoonsnamen in de kolommen hebben een Naam gekregen; exact gelijk aan de omschrijvingen in regel 2.

NB dat is ook de reden dat er een underscore is gebruikt in plaats van een afbreek-streepje. In een Naam is dat laatste teken niet toegestaan. Maar ook een spatie, een /, een \ enzovoort kun je niet gebruiken in de definitie van een naam.

De cellen in de kolom Naam krijgen ook een gegevensvalidatie. Maar het zou natuurlijk mooi zijn als de reeks toegestane namen afhankelijk is van de invoer in de kolom Afd.

Uitgaande van cel D3 willen we dus alleen maar de namen zien die bij de afdeling Dir voorkomen.
Wanneer we bij de Bron van de gegevensvalidatie rechtstreeks zouden verwijzen naar cel C3, dan gaat het niet werken; dan is de naam Dir als enige toegestaan. Gebruiken we echter de functie Indirect dan zijn alle namen uit het bereik Dir toegestaan.

LET OP wanneer achteraf de afdeling wordt gewijzigd dan krijg je niet automatisch een foutmelding dat de persoonsnaam niet is toegestaan. Gebruik dus regelmatig de optie Ongeldige gegevens omcirkelen.
Met behulp van een klein beetje VBA is het makkelijk te regelen dat bij een wijziging van afdeling automatisch de persoon gewist wordt.

Afhankelijke keuzelijst 2

Er zijn (natuurlijk) nog wel meer manieren om een afhankelijke lijst te maken. In het tabblad Afh2 van het Voorbeeldbestand hebben we een meer dynamische methode geïmplementeerd.
Daar is een overzicht te vinden van de 50 gemeentes van Nederland met het grootste inwoner-aantal (op 1 jan 2021). In plaats van dat een gebruiker in de lijst moet gaan zoeken (dat is met 50 namen nog wel te doen, maar bij meer dan 300 is het wat lastiger) kan hij een keuze maken in cel F3, waarna in cel G3 het bijbehorende aantal inwoners wordt getoond.
De mogelijkheden voor de keuze in cel F3 zijn via Gegevensvalidatie beperkt; de toegestane lijst zijn alle namen uit kolom B.

Maar het is natuurlijk handiger om eerst de provincie te kiezen en afhankelijk daarvan pas de gemeentenaam; het aantal mogelijkheden is dan een stuk beperkter.

De validatie in cel G6 is recht toe recht aan.

Ook die van cel G7 komt bekend voor (zie hiernaast): we beperken de gemeentekeuze tot het bereik GemPerProv.

Maar in dat laatste zit nu net de crux: deze naam verwijst niet naar een bereik van cellen maar is via Formules/Namen beheren ingevoerd:
=VERSCHUIVING(
tblProvGem[[#Kopteksten];[Gem]];
VERGELIJKEN(ProvZoek;tblProvGem[Prov];0);
0;
AANTAL.ALS(tblProvGem[Prov];ProvZoek);
1)

In het tabblad Afh2 van het Voorbeeldbestand staat een Excel-tabel (met de naam tblProvGem) met alle betreffende provincies en bijbehorende gemeentes; gesorteerd eerst op Prov en dan op Gem.

Met behulp van de functie Verschuiving bepalen we de cellen met de gemeentes die bij een bepaalde provincie (in de cel met de naam ProvZoek) horen.

  • de functie Verschuiving start in de cel met de koptekst van de kolom Gem
  • maar het resultaat begint net zoveel regels lager als door de functie Vergelijken wordt gegenereerd
  • en 0 kolommen naar rechts of naar links, dus het resultaat staat in de kolom Gem
  • het resultaat bestaat uit zoveel regels als dat de gezochte provincie in de kolom Prov voor komt
  • en de breedte van het resultaat is 1 kolom

NB Wat een gedoe eigenlijk voor iets wat je met een draaitabel met een paar slicers in 1 minuut kunt bouwen. 😉 Zie het tabblad Afh2 in het Voorbeeldbestand.

Maar het gaat om het principe; op een ander moment is deze methode misschien dé oplossing voor uw probleem.

Alternatieve keuzelijsten

cel NIET geselecteerd

Een groot nadeel van gegevensvalidatie is dat je pas weet dat je een keuze kunt maken wanneer de betreffende cel is geselecteerd.

cel WEL gesecteerd

Gelukkig kent Excel nog andersoortige keuzelijsten (zie het tabblad KzLijst van het Voorbeeldbestand). Het kost wel wat meer moeite om dit te implementeren.

Keuzelijst met invoervak

We gaan eerst een Keuzelijst met invoervak maken:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
    NB ziet u de menutab niet: klik met de rechtermuisknop ergens in het lint, kies de optie Lint aanpassen en zet bij Hoofdtabbladen het vinkje aan bij Ontwikkelaars
  2. kies binnen de Formulierbesturingselementen de optie Keuzelijst met invoervak
    NB binnen de ActiveX-elementen vindt u een vergelijkbare keuzelijst. Deze is nog iets ingewikkelder te implementeren. Daar krijg je dan wel meer flexibiliteit voor terug. Bijvoorbeeld kan de inhoud qua lettertype en -grootte aangepast worden.
  3. ’teken met de muis’ waar je het invoervak wilt hebben en hoe groot deze moet zijn
  4. geef bij Invoerbereik de cellen op met de toegestane gegevens (in het voorbeeld de cellen B3:B14)
  5. kies bij Koppeling een lege cel (bijvoorbeeld J2)
  6. geef aan hoeveel regels er zichtbaar moeten worden wanneer het invoervak wordt geactiveerd

Maar we zijn nog niet klaar. Wanneer je een keuze maakt in de lijst wordt het overeenkomende volgnummer in de gekoppelde cel geplaatst (hier dus J2).
In het voorbeeld op het tabblad KzLijst hebben we de naam van de gekozen provincie nodig; die halen we in cel K2 op met de formule: =INDEX(tblProv2[Prov];J2)

In cel I3 halen we dan het totaal van het aantal inwoners van de gekozen provincie op:
=SOM.ALS(tblProvGem2[Prov];K2;tblProvGem2[InwAantal])

LET OP: het overzicht bevat alleen de 50 grootste steden, dus het totaal is niet echt het totaal van de provincie!

Keuzelijst

Als laatste kijken we nog naar een andere keuzelijst (zie tabblad KzLijst van het Voorbeeldbestand):

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. kies binnen de Formulierbesturingselementen de optie Keuzelijst
  3. ’teken met de muis’ waar je de lijst wilt hebben en hoe groot deze moet zijn
  4. geef bij Invoerbereik de cellen op met de toegestane gegevens (in het voorbeeld de cellen D3:D52)
  5. kies bij Koppeling een lege cel (bijvoorbeeld M8)
  6. zorg dat als Selectietype de optie Enkelvoudig is geselecteerd
    NB de andere 2 opties zijn alleen interessant als u deze keuzelijst met behulp van VBA wilt gebruiken

NB in het voorbeeld hebben we de gekoppelde cel ‘verstopt’ achter de keuzelijst.

In cel I8 zoeken we de gemeentenaam op die hoort bij het getal in cel M8.

Het ophalen van het inwoneraantal van die gemeente kan het handigst met de avz-methode (zie Zoeken: Index en vergelijken).

Om het resultaat van de keuze ook snel in de tabel te kunnen terugvinden hebben we daar een Voorwaardelijke opmaak aan toegevoegd.

Op koers

Excel, , , , , , ,
Voorbeeldbestand: VluchtKoersen.xlsxDownload

Soms overkomt het je: krijg je een Excel-vraag en er gaat een heel nieuwe wereld voor je open.
Een paar weken geleden kreeg ik de vraag hoe je er voor zorgt dat, bij het uitzetten van een te vliegen traject, bij het berekenen van een gecorrigeerde koers er geen negatieve waarden of waarden groter dan 360o ontstaan.
Redelijk eenvoudig, maar toen kwam de volgende vraag: kun je gecorrigeerde koersen ook in Excel berekenen?

Na wat over en weer mailen werd het me duidelijk dat het een amateur-vlieger (zeg je dat zo?) heel wat tijd kost voordat hij kan gaan vliegen: routes, koersen moeten van te voren worden uitgestippeld, een zogenaamde vluchtlog moet worden opgesteld etcetera.

Maar zo’n nieuwe wereld is dan weer mooi een aanleiding om een Excel-artikel te schrijven.

Opmaak koersen

Bij het invullen van een vluchtlog moet niet alleen de koers worden uitgestippeld, ook moet deze theoretische koers nog gecorrigeerd worden voor wat betreft het effect van wind. Voor ons buitenstaanders: als de wind van rechts komt, dan zullen we ook iets meer naar rechts moeten ‘sturen’ dan de theoretische koers aangeeft.
Bij zo’n correctie kan het natuurlijk voorkomen dat we een koers kleiner dan 0 of groter dan 360 krijgen; dat moet dan nog aangepast worden.

In het Voorbeeldbestand op het tabblad KK staat een recht toe recht aan berekening. In kolom B staat de kaartkoers (theoretische koers), in kolom C is de correctie vanwege de wind opgenomen. In kolom D tellen we die 2 waardes op. Maar als die optelling negatief is, dan tellen we er nog eens 360 bij op. Wordt de som groter dan 360 dan trekken we er 360 vanaf.

Dit was eigenlijk de oorspronkelijke vraag, die ik kreeg. Maar uit onze correspondentie bleek, dat vliegeniers gewend zijn om koersen altijd met 3 getallen weer te geven en de correctie krijgt altijd een + of – teken.

Het eerste is in Excel snel opgelost. De cellen met koersen krijgen allemaal de opmaak zoals hiernaast weergegeven:

  1. selecteer de cellen die dezelfde opmaak moeten krijgen (eventueel Ctrl ingedrukt houden als de cellen niet aaneengesloten zijn)
  1. klik rechts en kies Celeigenschappen (of druk op Ctrl-1)
  2. kies de categorie Aangepast
  3. en het veld onder Type krijgt de waarde 000 (dus altijd 3 cijfers weergeven)

Om het tweede ‘probleem’ op te lossen krijgen de cellen in kolom C een andere opmaak:
Met het gedeelte voor de eerste punt-komma geven we aan dat positieve getallen altijd 2 cijfers bevatten en van een plus-teken moeten worden voorzien; via het tweede blokje bepalen we hoe negatieve getallen er uit moeten zien en als laatste dat een nul-waarde door 2 nullen wordt weergegeven.

NB de inhoud van de cellen verandert hierdoor niet, we hebben alleen de opmaak aangepast.

Bij de berekening van de Kompaskoers (kolom D) hebben we een geneste Als-functie gebruikt. Zoals zo vaak: in Excel kunnen we dat ook op een kortere manier oplossen.

Met behulp van de functie Rest bepalen we wat er overblijft wanneer we delen door 360 en en het gehele deel ‘weggooien’. Bij getallen tussen 0 en 360 is de rest na deling het getal zelf, bij getallen groter dan 360 wordt er 360 vanaf getrokken.

Maar de formule werkt ook als de optelling een negatief getal oplevert!
Dat blijkt ook uit kolom F, waar we de werking van de Rest-functie hebben nagebootst:
=D3-360*GEHEEL(D3/360)

Windcorrectie

Na het beantwoorden van de eerste vraag, kreeg ik een mail terug met een dankwoord en de opmerking: “Maar de windcorrectie berekenen in Excel is zeker te ingewikkeld?“

Wat bleek: als de windsnelheid en -richting bekend zijn moeten er allerlei handmatige acties op aparte apparaten uitgevoerd worden om de windcorrectie te bepalen.

Eerste reactie mijnerzijds was: dat kan niet zo moeilijk zijn, een beetje rekenen met vectoren, daar hebben ze goniometrie voor uitgevonden! Maar dat viel toch wel tegen. Dus ik moet bekennen dat ik toen toch maar even Google heb gebruikt.

Op de site en.wikipedia.org/wiki/E6B vond ik het volgende (zie ook het tabblad Heading in het Voorbeeldbestand):

Op het tabblad Heading staat een voorbeeld van deze berekeningen.

NB zorg wel dat de snelheden in dezelfde eenheden zijn uitgedrukt!

Aangezien Excel binnen de goniometrische functies met radialen werkt is de 2e set formules gebruikt. De cellen in kolom C hebben een toepasselijke naam gekregen, zodat de formules beter leesbaar en dus beter controleerbaar zijn.

NB De Excel-vertaling van sin-1 is de functie Boogsin; de waarde voor π krijgen we door de functie Pi() te gebruiken.

In kolom D zorgen we voor een afronding naar gehele getallen:

Uiteraard kunnen deze berekeningen ook in één keer (zie cel C15):
=AFRONDEN(180/PI()*BOOGSIN(Vw*SIN(PI()*(w-d)/180)/Va);0)

Vluchtlog

Als voorbeeld kreeg ik het voorbereidende materiaal voor een vlucht van Teuge naar Texel. Een uittreksel:

In het tabblad Route van het Voorbeeldbestand hebben we op basis van bovenstaande informatie de vluchtlog enigszins nagebouwd.

De vluchtlog is een Excel-tabel met de naam tblHeading. In de gebruikte formules wordt dan ook verwezen naar de kolom-koppen (een @ betekent: haal de waarde uit een kolom in dezelfde regel waar de formule staat). De formule in kolom L wordt alleen berekend als geen van de cellen in dezelfde regel in de kolommen G, H, I of J leeg is.

NB1 het rekenen met richtingen/graden gaat in de vliegerij iets anders dan in de wiskunde. Vliegen naar het noorden is richting 0o, naar het oosten 90o etc. Een windrichting betekent dat de wind vanuit die richting komt.

NB2 het omrekenen van graden naar radialen en vv kunnen we ook aan Excel overlaten (zie kolom M):
=ALS(OF([@d]=””;[@Va]=””;[@w]=””;[@Vw]=””);””;GRADEN(BOOGSIN([@Vw]*SIN(RADIALEN([@w]-[@d]))/[@Va])))

In kolom N wordt dan de te vliegen koers bepaald: =ALS([@∆a]=””;””;REST([@d]+[@∆a];360))

Uiteraard willen we ook weten hoelang we straks onder weg zullen zijn. In kolom O wordt de zogenaamde grondsnelheid bepaald, rekening houdend met wind mee of tegen:
=ALS([@Heading]=””;””;WORTEL([@Va]^2+[@Vw]^2-2[@Va][@Vw]COS(PI()([@d]-[@w]+[@∆a])/180)))

Dan kunnen we per stap (of in vliegtermen Leg) de benodigde tijd uitrekenen (kolom P):
=ALS([@Vg]=””;””;[@Afstand]/[@Vg]/24)

NB we delen ook nog eens door 24, omdat het resultaat van Afstand/snelheid het aantal uren is. Op deze manier komt in kolom P een waarde, die het dagdeel aangeeft. Door dan de cellen een opmaak van uren en minuten te geven is het resultaat voor ons makkelijk interpreteerbaar.

Route-grafiek

Nou we toch bezig zijn: we willen natuurlijk ook nog wel ‘zien’ hoe we gaan vliegen; een plaatje zou wel mooi zijn.

Alles kan! (?)
We moeten dan wel eerst de gewenste koers (dus NIET de te vliegen koers) vertalen naar wiskundige hoeken. Wat blijkt: dat is niet zo ingewikkeld, hoek = 90o – koers.

Aangezien de x-coördinaat van het eindpunt van een stap berekend kan worden via de cosinus van de hoek en de y-coördinaat met de sinus zijn we er al bijna.

In kolom S op het tabblad Route van het Voorbeeldbestand worden de x-coördinaten van de stappen bepaald, in kolom T de y-coördinaten.
Door de vorige coördinaten daar telkens bij op te tellen krijgen we de vliegroute.

NB wanneer een stap niet gevuld is wordt met behulp van de functie NB() aangegeven dat die coördinaat niet bestaat. Bij het maken van een grafiek worden die punten niet meegenomen door Excel (zie Grafiek zonder nullen).

Op basis van deze tabel is bovenstaande grafiek gemaakt. Gebruik daarvoor een spreidingsdiagram.

NB1 het omrekenen van graden naar radialen gebeurt, ter illustratie, in de kolommen S en T op een verschillende manier.

NB2 door de grafiek te laten tekenen met vloeiende lijnen ontstaat een iets reëler beeld.

NB3 om een goede weergave van de vliegroute te krijgen moet je de hoogte en/of breedte van de grafiek zodanig aanpassen, dat de rasterlijnen vierkanten vormen.

NB4 disclaimer: ik ga er wel van uit dat piloten van bijvoorbeeld een 737 Max deze spreadsheet niet gaan gebruiken!

De labels van de grafiek kunnen bijvoorbeeld nog aangevuld worden met de te vliegen koers, zie het tabblad grafRoute van het Voorbeeldbestand.

Hyperlinks

Excel,
Voorbeeldbestand: Hyperlinks.xlsxDownload

Het internet hangt van hyperlinks aan elkaar: dé manier om van de ene site naar de andere door te klikken.
Maar ook op andere plaatsen in de geautomatiseerde wereld kom je ze tegen en dus ook in Excel.

Tijd om daar eens aandacht aan te besteden en de mogelijkheden te onderzoeken.

Links

In Excel gebruiken we de hyperlinks meestal om te verwijzen naar internetpagina’s, die als bron hebben gediend voor de gegevens die we gebruiken.

In het tabblad Links van het Voorbeeldbestand staat een overzicht met enkele voorbeelden.
De eerste optie is in Excel als tekst in een cel ingevoerd. Door de www ‘weet’ Excel dat het een internet-adres is en zet de tekst automatisch om in een hyperlink.

Ga je met de muis over de link heen, dan krijg je aanwijzingen hoe met de link om te gaan:

Het tweede voorbeeld wordt niet omgezet naar een hyperlink. Wel als de tekst begint met http:// (of https:// als het een website met een beveiligde communicatie betreft); zie het derde voorbeeld.

Een andere mogelijkheid is om de internetverwijzing vanuit de adresbalk van een browser te kopiëren en in een cel te plakken (de vierde optie). Helaas: dan wordt het niet automatisch een hyperlink. We kunnen Excel wel even helpen: selecteer de betreffende cel, klik ergens in de formulebalk en druk op Enter. Op dat moment lijkt het op een handmatige invoer en is het vergelijkbaar met voorbeeld 3.

Wil je zeker weten, dat Excel een hyperlink genereert, gebruik dan de functie Hyperlink. In het vijfde voorbeeld staat de formule:
=HYPERLINK(“http://www.ginfo.nl/tips-trucs/”)

LET OP de verwijzing naar de internet-pagina moet een tekst zijn, dus staat tussen aanhalingstekens.

Mooier is om de uitgebreide versie van deze functie te gebruiken:
=HYPERLINK(“http://www.ginfo.nl/tips-trucs/”;”Tips en trucs”)

Hierbij vullen we een tweede parameter van deze functie met de tekst, die moet worden weergegeven in de cel.

Links 2

Soms is het handig om vanuit Excel een ander bestand te openen zonder dat je precies hoeft te weten waar dat bestand precies staat.
Op hetzelfde tabblad Links van het Voorbeeldbestand staan drie voorbeelden.

In het eerste geval is het bestand inclusief het pad daarnaartoe als tekst in de cel ingevoerd. Helaas: Excel begrijpt ons niet; het wordt niet automatisch een hyperlink.

Gelukkig helpt de functie Hyperlink ons uit de brand. In de tweede cel staat de formule
=HYPERLINK(“C:\Users\gijsv\Downloads\WebPQ2.xlsx”)

Klik je op deze link, dan opent het bestand zich vanzelf in Excel. Tenminste: als de bestandsnaam voorkomt in de aangegeven directory.

Ook hier kun je gebruik maken van de uitgebreide versie van de functie (3e voorbeeld):
=HYPERLINK(“C:\Users\gijsv\Downloads\Histogram.xlsx”;”Voorbeeldbestand: Histogrammen”)

Bestanden

De hiervoor gebruikte optie om bestanden te openen is natuurlijk heel handig als je bijvoorbeeld diverse bestanden hebt per maand of per afdeling, die af en toe geraadpleegd moeten worden vanuit een totaal-sheet. Maak in de totaal-sheet op bovenstaande manier de benodigde links en je hoeft nooit meer te zoeken.

Een ander voorbeeld: je hebt een map/directory met diverse bestanden. Zijn het heel veel bestanden, dan is het lang niet altijd makkelijk om te zien welk bestand je moet nemen.
Maak dan een hulp-bestand met daarin de links naar de gewenste bestanden. Omdat Hyperlink je de mogelijkheid geeft om een verklarende tekst weer te geven kan dat heel wat zoekwerk besparen.

In het tabblad Bestanden van het Voorbeeldbestand staat een voorbeeld gebaseerd op mijn Download-map; cel C3 heeft de naam MapNaam.
De formule in D6 is dan:
=HYPERLINK(MapNaam&[@Bestandsnaam];[@Naam])

Binnen de functie worden de MapNaam en de overeenkomende cel in de kolom Bestandsnaam aan elkaar ‘geknoopt’ met de &; wanneer je op deze cel klikt wordt dit bestand geopend. In cel D6 wordt de info uit de kolom Naam weergegeven.

NB1 de diverse bestanden en de links staan in een Excel-tabel.

NB2 de kolommen B, C en D zijn gegroepeerd. Door op het min-teken boven D te klikken, klappen de kolommen B en C dicht, waardoor alleen de kolom met hyperlinks zichtbaar blijft.

LET OP vergeet niet in cel C3 de mapnaam af te sluiten met een backslash.

Bestanden 2

Op hetzelfde tabblad Bestanden van het Voorbeeldbestand staat nog een andere toepassing. We kunnen met behulp van Hyperlink niet alleen een ander bestand openen maar ook direct de cursor naar een bepaald onderdeel laten ‘springen’. Dat kan door naar een bestaande naam in dat bestand of naar een bepaalde cel te verwijzen. Wel moet de combinatie van pad en bestandsnaam dan tussen rechte haken komen.

In cel D14 staat daarom de formule:
=HYPERLINK(“[“&MapNaam&BestNaam&”]”&[@Onderdeel];[@Onderdeel]&” in “&BestNaam)

De eerste parameter in cel D14 wordt dan [C:\Users\gijsv\Downloads\Histogram.xlsx]IntvCadans

Koppelingen

Excel kent nog een andere methode om ‘harde’ links te maken naar bestanden (hard omdat je geen gebruik kunt maken van namen in andere cellen; zie voor resultaten het tabblad Koppelingen in het Voorbeeldbestand).

  1. Selecteer de cel waar de hyperlink moet komen.
  2. Kies in de mentab Invoegen de optie Koppeling:
  3. In het vervolgscherm kun je de gewenste map opzoeken:

    en daarna het bestand. Klik dan op OK.

Zoals in het eerste voorbeeld hierboven-rechts (onder Bestanden) te zien is, laat Excel maar een gedeelte van het pad zien. Beweeg je de cursor boven de link, dan zie je wel waar het bestand te vinden is/zou moeten zijn.
De tweede regel is op dezelfde manier gegenereerd, met dien verstande dat in stap 3 ook het veld Weer te geven tekst is gevuld (met de tekst Histogrammen).

NB wil je zien hoe de hyperlink is gemaakt (of wil je hem wijzigen) klik rechts op de link en kies de optie Hyperlink bewerken.

In het derde voorbeeld is in stap 3 de optie Bladwijzer gebruikt. In het vervolgscherm hebben we Voorblad aangeklikt:

NB bij Celverwijzing staan alle tabbladen uit het Excel-bestand; standaard wordt dan cel A1 gekozen. Dit kun je daarboven wijzigen.

Zoals hierboven te zien is, kun je ook een gedefinieerde naam kiezen (zie het vierde voorbeeld).

NB Koppeling als Excel-optie is ontwikkeld voordat Excel-tabellen zijn geïntroduceerd. Bij Gedefinieerde namen zie je geen tabel-namen (Tabel1Kopie is een eigen naam). Toch kun je wel een koppeling maken door zelf de naam achter de bestandsnaam te plaatsen, gescheiden door een # (zie het vijfde voorbeeld: Histogram.xlsx#tblZwift).

Koppelingen 2

Niet alleen kun je op deze manier koppelingen maken naar bestanden, ook links naar websites kun je op die manier maken (zie het tabblad Koppelingen in het Voorbeeldbestand). Vul in stap 3 bij Adres de URL van de gewenste site in.

Wanneer je met de muis over de derde link beweegt, zul je niet de standaard uitleg van de link zien. In dit geval hebben we in stap 3 ook de optie Scherminfo gebruikt.

Ook aan afbeeldingen in een Excel-sheet kun je links toevoegen: klik rechts op de afbeelding en kies de optie Koppeling.

NB1 is er al een koppeling, kies dan de optie Koppeling bewerken.

NB2 ook grafieken kun je van een link voorzien (bijvoorbeeld naar een bestand met gegevens waarop de grafiek is gebaseerd): selecteer het Grafiekgebied en kies dan de optie Koppeling en de menutab Invoegen.

Het is op een vergelijkbare manier mogelijk om een link naar een email-programma te maken:

NB test wel of deze link op iedere gewenste computer werkt. Het is op deze manier niet mogelijk om een on-line mail-programma (zoals bijvoorbeeld Gmail) aan te sturen.

Menu

Met de Koppeling-optie is het ook mogelijk om een menustructuur in een Excelbestand op te nemen, waarmee je snel naar de diverse tabbladen of onderdelen daarvan kunt ‘springen’ (zie het tabblad Menu in het Voorbeeldbestand).

Maak in stap 3 gebruik van de optie Plaats in dit document:

NB1 om het menu een rustiger aanblik te geven is de onderstreping van de diverse items verwijderd.

NB2 wanneer je deze menustructuur toepast, plaats dan op ieder tabblad links-boven een link naar het tabblad met het menu:

Power Query en het Web 2

Excel, , , ,
Voorbeeldbestand: WebPQ2.xlsxDownload

In het vorige artikel had ik aangegeven dat ik zou beschrijven hoe je gegevens die over meerdere internet-pagina’s zijn verspreid toch met Power Query in één keer kunt overhalen naar Excel.

Belofte maakt schuld ….

Basis

Allereerst een disclaimer: je kunt (natuurlijk) alleen maar gegevens van meerdere pagina’s combineren, wanneer die pagina’s qua structuur op elkaar lijken.

We nemen als voorbeeld de pagina Tips & trucs van G-Info.

Daar staan alle artikelen die in de loop van de jaren zijn gepubliceerd; tenminste het eerste gedeelte van die artikelen.
Om het overzichtelijk te houden vind je daar de artikelen in blokken van 5.
Kies je oudere artikelen dan zie je in de adresbalk van je browser: www.ginfo.nl/tips-trucs/page/2/, waarbij het paginanummer gaat oplopen.
NB gelukkig kunnen we ook op dezelfde manier de eerste pagina ophalen: www.ginfo.nl/tips-trucs/page/1/

We gaan de gegevens van de eerste pagina binnenhalen in Excel:

  1. Analyseer de internetpagina via de optie Inspecteren zoals in het vorige artikel uitgelegd.
  2. Kies in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web en geef als URL in: ginfo.nl/tips-trucs/
  3. Klik in de Navigator op Document en kies Bewerken.
  4. Klik 2x op Table en dan op de Table achter Body.
  5. Dan 4x op Table achter de (eerste) DIV

  6. Nu we op het niveau van de artikelen zijn gekomen kiezen we Uitvouwen achter Children.
    LET OP zorg dat je bij het uitvouwen alleen de kolommen Children en Text meeneemt
  7. Kies nog 3x Uitvouwen met als resultaat:
  8. Selecteer de kolommen Text.4 en Text.3 en kies op de menutab Transformeren de optie Kolommen samenvoegen. Geef de nieuwe kolom de naam Artikel.
  9. Verwijder alle rijen, die we niet nodig hebben door er rechts op te klikken:
  10. Verwijder alle overbodige kolommen, geef de query de naam q_Tips1 en kies in de menutab Startpagina de optie Sluiten en laden.

Dat is al gelukt (zie het tabblad Tips1 van het voorbeeldbestand): we hebben de belangrijkste gegevens van de internetpagina in Excel.
Maar het zou mooier zijn als de kop van het artikel en de inhoud naast elkaar zou staan.

Van rijen naar kolommen

De methode die we hiervoor gaan gebruiken is een alternatief voor die uit het vorige artikel:

  1. Dubbelklik op de query q_Tips1.
  2. Kies in de menutab Kolom toevoegen de optie Indexkolom en direct daarna binnen Standaard de optie Delen (geheel getal) .
    Vul in het tussenscherm de waarde 2 in en geef de nieuwe kolom de naam Rij.
  3. Met de kolom Index geselecteerd, kies opnieuw Standaard, maar dan de optie Modulo; zorg dat door 2 gedeeld wordt.
    Geef deze kolom de naam Kolom.
    NB Met Modulo wordt de rest berekend van een deling.
  4. Verwijder de kolom Index.
  5. Selecteer de kolom Kolom, kies in de menutab Transformeren de optie Draaikolom en vul het vervolgscherm als volgt in:
  6. Verwijder de kolom met de naam Rij en wijzig de 2 overblijvende kolomnamen in Naam en Inhoud. Kies de optie Sluiten en laden (zie het tabblad Tips2 van het voorbeeldbestand).

NB pas wel even de cel-eigenschappen van de tabel aan: kies Terugloop bij de Tekstopties en de optie Boven bij Verticaal.

Query als functie gebruiken

De query, q_Tips2, die we hiervoor hebben gemaakt, willen we nu voor verschillende internetpagina’s van G-Info gebruiken. Hij moet zich dus als een functie gedragen, waar we door middel van een parameter kunnen aangeven, welke pagina moet worden opgehaald:

  1. Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
  2. Selecteer aan de linkerkant de query q_Tips2 en kies de optie Geavanceerde editor in de menutab Startpagina.
  1. Kopieer alle stappen van deze query; kies Annuleren.
  2. Klik met de rechter muisknop in de linkerkolom en voeg een Lege query toe:
  3. Ga weer naar de Geavanceerde editor en plak de gekopieerde stappen uit punt 2.
  4. Voeg vooraan een regel toe:
    let AllePaginas=(PagNr) =>
  5. Wijzig de Bron-stap in:
    Bron = Web.Page(Web.Contents(“ginfo.nl/tips-trucs/page/”&Number.ToText(PagNr))),
  6. Voeg nog een laatste regel toe:
    in AllePaginas
  7. Klik op Gereed. Test de functie door als parameter een 1, 2 etc in te tikken en dan Aanroepen te kiezen.
  8. Geef de functie de naam fAllePags en kies de optie Sluiten en laden.

Functie gebruiken

De functie die we net gemaakt hebben gaan we gebruiken om met behulp van Power Query alle tips van G-Info tegelijkertijd op te halen:

  1. Allereerst moeten we aangeven welke paginanummers we willen importeren. We leggen dat in een Excel-tabel vast (met de naam tblPagNrs; zie het tabblad AlleTips van het voorbeeldbestand)
  1. Klik ergens in die tabel en kies in de menutab Gegevens in het blok Gegevens ophalen en transformeren de optie Van tabel/bereik.
  2. Kies in de menutab Startpagina de optie Kolom splitsen/Op scheidingsteken en vul het scherm als volgt in:

    NB we splitsen in 50 kolommen zodat bij uitbreiding van het aantal tips-pagina’s de routine goed blijft werken.
  3. Kies in de menutab Transformeren de optie Transponeren.
  4. Filter nu de lege rijen uit (rechtsklikken op een lege cel).
  5. Wijzig de kolomnaam in Pagina.
  6. Kies in de menutab Kolom toevoegen de optie Aangepaste functie aanroepen. Kies in het vervolgscherm bij Functiequery onze hiervoor gecreëerde functie; de rest van de opties zijn oké:

    LET OP de routine gaat nu de gegevens van alle pagina’s ophalen; dat kost even wat tijd!
  7. Vouw de kolom fAllePags uit.
  8. Verwijder de kolom Pagina.
  9. Geef de query de naam q_AlleTips en kies Sluiten en laden naar.

Helaas, we hebben bij het maken van de tips-query iets over het hoofd gezien: als er reacties zijn op een artikel dan ‘raakt het systeem in de war’ (zie het tabblad AlleTips van het voorbeeldbestand).

Ook staat er ergens nog zoiets als Een reactie tot dusver.
Bij het filteren van regels moeten we ook de reacties weglaten:

  1. Kies in de menutab Gegevens in het blok Gegevens ophalen de optie Power Query-editor starten.
  2. Klik rechts op de functie fAllePags en kies Dupliceren; wijzig de naam van de nieuwe functie in fAllePags2.
  3. Kies de optie Geavanceerde editor.
  4. Voeg als volgt na gefilterd4 2 stappen toe en wijzig de stap daarna:
    #”Rijen gefilterd4″ = Table.SelectRows(#”Rijen gefilterd3″, each [Name] <> “nav”),
    #”Rijen gefilterd5″ = Table.SelectRows(#”Rijen gefilterd4″, each not Text.Contains([Artikel], ” reactie tot dusver”)),
    #”Rijen gefilterd6″ = Table.SelectRows(#”Rijen gefilterd5″, each not Text.Contains([Artikel], ” reacties bekijken”)),
    #”Andere kolommen verwijderd” = Table.SelectColumns(#”Rijen gefilterd6“,{“Artikel”}),
  5. Klik op Gereed.
  6. Test de nieuwe functie door deze aan te roepen met als parameter bijvoorbeeld 3.
  7. Kies de optie Sluiten en laden.

NB Door de paginanummers op het tabblad AlleTips2 van het voorbeeldbestand te wijzigen en de tabel daaronder te Vernieuwen kun je het overzicht aanpassen aan je wensen.

Power Query en het Web

Excel, , ,
Voorbeeldbestand: WebPQ.xlsxDownload

Dit artikel gaat over het gebruik van Power Query als hulpmiddel om gegevens van het WEB te halen.

Hebben we eenmalig een overzicht nodig dan volstaat meestal een eenvoudige kopieer-plak-actie.

Maar wijzigen de gegevens op een internet-pagina periodiek dan moeten we die actie iedere keer opnieuw uitvoeren, inclusief eventuele extra berekeningen etc.

In de afgelopen jaren heeft G-Info al diverse methoden besproken (zie bijvoorbeeld Excel en het World-Wide-Web en MS-Query: een alternatief voor Vert.Zoeken), maar de opties die daar besproken zijn, zijn door Microsoft bij het opleveren van nieuwe versies steeds meer gesloopt.
Maar niet getreurd: we hebben daar Power Query voor teruggekregen; vanaf versie 2016 Gegevens ophalen en transformeren genoemd.

Hieronder enkele ‘simpele’ voorbeelden, maar ook een uitleg hoe je iedere willekeurige internet-pagina kunt uitlezen.
Volgende keer zullen we kijken hoe je gegevens die over verschillende pagina’s verspreid staan, kunt overhalen naar Excel.

Inwoneraantal per land

Op Wikipedia (nl.wikipedia.org/wiki/Lijst_van_landen_naar_inwonertal) is een mooi overzicht te vinden van het aantal inwoners per land. Dit wordt natuurlijk niet dagelijks bijgewerkt; op dit moment staan hier gegevens van 2020.

Maar hiermee kunnen we wel makkelijk de kracht van Power Query laten zien.

Hoe haal je deze gegevens binnen in Excel?

  1. open een nieuw werkblad
  2. kies in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web
  3. kopieer de Wikipedia-URL uit de adresbalk van je internet-browser naar het tussenscherm in Excel
  1. in de Navigator zie je in dit geval dat we kunnen kiezen uit het hele document (de internet-pagina) of een tabel.
    Klik één keer op de naam Table 0 en je zult zien dat dit precies de gegevens zijn die we willen ophalen.
  2. kies rechtsonder het ‘vinkje’ naast Laden
  1. Kies daar Laden naar.
    In het vervolgscherm geven we aan dat we als resultaat een Tabel willen.
    Geef aan of deze op een bestaand werkblad moet komen of op een nieuwe.

Daar zijn de gegevens zoals we die op de internet-pagina hebben gezien (zie het tabblad Inwoners in het Voorbeeldbestand. Helaas wel zonder de mooie vlaggetjes!).
Zijn de gegevens op internet gewijzigd: klik rechts op de tabel en kies Vernieuwen.

Wel jammer van de derde kolom: doordat er een jaar achter het aantal staat, ziet Excel dit aantal niet als getal.
Daar gaan we gauw iets aan doen; we gaan uit die kolom het aantal extraheren:

  1. open zo nodig het scherm met het overzicht van de query’s in deze werkmap: de optie Query’s weergeven in de menutab Gegevens
  2. dubbelklik op q_Inwoners (of klik rechts en kies de optie Bewerken)
  3. klik ergens in de kolom Inwonertal
  4. kies in de menutab Kolom toevoegen in het blok Uit tekst de optie Extraheren; kies Tekst voor scheidingsteken en vul het vervolgscherm in (tik op de (-toets, haakje openen)
  5. verander het gegevenstype van de nieuwe kolom (klik op ABC en kies de optie Geheel getal) en wijzig de naam van de kolom via dubbelklikken in AantInwoners
  6. verwijder de oude kolom Inwonertal en klik in de menutab Start op de button Sluiten en laden

Weer wat geleerd: ik wist niet dat Indonesië zo veel inwoners heeft!

NB Excel haalt alleen letters en cijfers op; je mist dus plaatjes (in dit geval de land-vlaggen) en bijvoorbeeld ook de opmerking bij het aantal van China.

Corona-cijfers

De gegevens in het vorige voorbeeld hadden we ook nog ‘handmatig’ kunnen overnemen; ze wijzigen hooguit één keer per jaar.

Een ander voorbeeld vinden we op de site news.google.com/covid19; de corona-data, die je daar kunt vinden, worden enkele keren per dag bijgewerkt. Een geautomatiseerd proces is dan een uitkomst.

Op het tabblad Corona van het Voorbeeldbestand staat het resultaat van een query:

Ook deze internet-pagina bevat een tabel, dus een koppeling via Power Query is snel gemaakt.
In de kolom Nieuwe gevallen is de tekst vervangen door de waarde 0 (nul).
Als laatste is nog een index toegevoegd; op die manier zien we dat Nederland het 21e land is, rekenend met het aantal Corona-gevallen.
De regel met Nederland wordt in Excel met behulp van Voorwaardelijke opmaak geaccentueerd.

Klik ergens rechts in de tabel en kies Vernieuwen.

Wil je weten welke stappen er zijn doorlopen om dit resultaat te bereiken: klik dubbel op de query q_Covid ( of klik rechts en kies Bewerken).

Je komt dan in de Power Query Editor. Klik op één van de stappen aan de rechterkant en boven in de formulebalk zie je wat deze stap daadwerkelijk inhoudt. De opdracht hierboven ziet er ingewikkeld uit, maar gelukkig is dit makkelijk te implementeren: klik rechts op een cel en kies de optie Waarden vervangen.

Maar zo eenvoudig als in de vorige 2 voorbeelden gaat het vaak niet; Excel kan alleen maar een tabel ophalen als in de internet-opmaak gebruik is gemaakt van de zogenaamde table-tag. Deze opmaak-techniek was ‘vroeger’ de standaard als je een overzicht op internet wilde plaatsen; met de opkomst van allerlei hulpprogramma’s om internet-pagina’s te genereren (bijvoorbeeld WordPress) worden steeds vaker alternatieve opmaak-opties gebruikt, die door Excel (nog) niet als tabel worden herkend.

Opmaak internet-pagina’s

Om de rest van het artikel beter te kunnen volgen moeten we het daarom (kort) over de opmaak van internet-pagina’s hebben.
Voor deze opmaak zijn diverse regels afgesproken zodat alle verschillende browsers weten hoe ze de informatie moeten interpreteren. Deze regels zijn vastgelegd in een taal: HTML (HyperText Markup Language). We kunnen hier geen uitgebreide uitleg daarvan geven, maar zullen de belangrijkste punten (voor ons doel) belichten.

Wanneer we rechts klikken op de Wikipedia-pagina van ons eerste voorbeeld, is één van de opties Paginabron weergeven.

Binnen HTML wordt gewerkt met zogenaamde tags; deze kun je herkennen aan de tekens < en >. Tags kun je zien als commando’s voor je internet-browser.
Als eerste krijgt de browser te zien dat het documenttype HTML is, daaronder dat binnen de html voor de site-inhoud de Nederlandse taal wordt gehanteerd. Class wordt onder andere gebruikt om elementen een bepaalde opmaak mee te geven.
Iedere internet-pagina moet een head hebben en ook een body (waar de echte inhoud van de pagina staat). Vaak wordt ook gebruik gemaakt van een footer.
De tag title wordt gebruikt om het tabblad in je browser een naam te geven.

Ieder blok van een internet-pagina wordt voorafgegaan door een tag en afgesloten met een sluit-tag (dezelfde naam maar met een /).

We kunnen deze paginastructuur nog op een andere manier bekijken. Wanneer je rechts klikt op het inwoneraantal van China, kies dan Inspecteren:

Je ziet dan ‘direct’ waar het element waar je op hebt geklikt, zich in de pagina-structuur bevindt. De head is dichtgeklapt (het pijltje wijst naar rechts en je ziet puntjes tussen de open- en sluit-tag), de body is geopend (pijltje naar beneden) dus daar zit het inwonertal in.
De body begint met twee div‘s, die direct weer worden gesloten; die zijn hier voor opmaak-doeleinden bedoeld.

NB div is de afkorting van division; hiermee kunnen makkelijk bepaalde secties van een pagina worden afgebakend.

Dan komen we in een div met als id de naam content. Daarbinnen komen we iets verderop in de div bodyContent.
Uiteindelijk zien we dan de tag table, met daarin een kop (thead) en een tbody. Binnen de tbody komt een regel-tag (tr) en daarbinnen 3 keer een td (data-cell).

De derde staat in een gekleurde balk; klik op het pijltje en je ziet de inhoud van die tabel-cel.

NB misschien heb je geprobeerd om in het eerste voorbeeld bij het extraheren van het inwoneraantal als scheidingsteken het haakje-openen vooraf te laten gaan door een spatie. Hier kun je zien waarom dat niet werkte: tussen de html-spatie (&nbsp;) en het haakje staat nog een tag.

Artikelen van G-Info

Op iedere pagina van ginfo.nl staat rechts een overzicht van alle Tips & trucs. We gaan de gegevens uit die kolom overhevelen naar Excel (zie voor het resultaat het tabblad Artikelen in het Voorbeeldbestand en voor de uiteindelijke query q_G-Info-artikelen):

  1. eerst gaan we die internet-pagina analyseren: klik in de browser rechts op de naam van het eerste artikel (in het voorbeeld Histogrammen) en kies de optie Inspecteren.
  1. het eerste wat opvalt is dat de table-tag nergens te vinden is.
    Binnen de body moeten we de eerste div hebben. Daarbinnen de eerste div na de header, daarbinnen de tweede div, en dan twee keer de eerste div en dan een blok aside.
  1. we zijn er bijna: We slaan een h3 over (daar zien we de kop van de kolom) en vinden dan binnen een ul (unordered list) diverse li-tags (list items). Dat zijn de artikelen, die we zoeken.
  2. nu kunnen we aan de slag: kies in Excel in de menutab Gegevens in het blok Gegevensophalen en transponeren de optie Van het web
  3. vul als URL in: www.ginfo.nl en klik OK
  4. zoals we al hadden verwacht kan Power Query (PQ) geen tabel vinden, alleen de document-tag. Klik daarop en kies dan Bewerken.

  5. in de editor zien we maar 1 regel, het HTML-document. Maar in de kolom Children zitten alle blokken die we bij de analyse hebben gezien.
    Wanneer je die cel activeert (klik NAAST het woord Table), dan zie je beneden dat deze cel 2 elementen bevat, een HEAD en een BODY.
  6. klik nu op het woord Table; in de editor zien we dan 2 regels:

    In de analyse hebben we gezien dat we in de body moeten zijn; klik op Table in die regel.
  7. we gaan verder inzoomen zoals we hiervoor in de analyse hebben gezien: de eerste div, de eerste div na de header, dan de 2e div, de eerste div, nog een keer de eerste div en dan de aside.
    De h3 zouden we overslaan: kies de ul-table. En daar zijn ze!
  8. klik NIET op een Table achter een LI: dan zoomen we in op één artikel en zien we de andere namen niet meer.
    We willen de gegevens van alle ‘children‘ hebben: klik op de button met de 2 pijltjes achter de kolomnaam
  9. vul het tussenscherm als volgt in:
  10. en nog een keer op dezelfde manier uitvouwen levert het volgende op:

NB heb je ergens een fout gemaakt? Klik aan de rechterkant Navigatie open (dubbelklikken of klikken op het ’tandwieltje’) en je kunt één of meerdere stappen terug gaan.

Nu we de gegevens hebben gevonden, moeten we nog wel wat opschoonacties doen (maar bedenk wel: dit hoef je binnen PQ allemaal maar één keer te doen, daarna kun je het overzicht met Vernieuwen iedere keer snel actualiseren):

  1. verwijder alle kolommen, behalve de kolom waar de teksten in staan:
  2. de lege regels verwijderen door rechts te klikken op één van de null-waarden:
  3. de naam en de datum van het artikel moeten naast elkaar komen staan, we moeten dus rijen combineren. Dat kan bijvoorbeeld op de volgende manier (in het volgende artikel komt een andere methode aan bod):
    * kies in de menutab Kolom toevoegen de optie Indexkolom (de index moet met 0 beginnen)
    * direct gevolgd door , vul in het vervolgscherm de waarde 2 in; we willen 2 regels bij elkaar nemen

    NB Microsoft gebruikt hier een vreemde terminologie; wat bedoeld wordt is, dat we na deling een geheel getal overhouden (dus eventuele decimalen weg laten)
    * nu gaan we, met de laatst toegevoegde kolom geselecteerd, de rijen groeperen: kies in de menutab Startpagina de optie Groeperen op, kies als Bewerking de optie Som en als Kolom de kolom met teksten (we gaan teksten ‘optellen’)
    * dat kan natuurlijk niet! We moeten even wat corrigeren: wijzig in de formulebalk List.Sum([Text.2]) door Text.Combine([Text.2], “||”) en druk op Enter.
    LET OP PQ is hoofdletter-gevoelig.
    NB we hebben hier || als scheidingsteken gebruikt; we hebben iets nodig waar we van weten dat dit nergens in de tekst voorkomt.
    * selecteer de kolom met teksten, kies in de menutab Startpagina de optie Kolom splitsen en voer als aangepast scheidingsteken || in.
    PQ splitst de kolom én verandert het type van de nieuwe kolom automatisch naar datum.
    * nog een paar aanpassingen: verwijder de eerste kolom en wijzig de kolomkoppen.
    * kies Sluiten en laden

Weersverwachtingen

Nog een voorbeeld, nu van de website knmi.nl/nederland-nu/weer/verwachtingen.

Een korte analyse van deze site laat zien dat ook hier geen table-tag te vinden is. Dus dat wordt weer tellen!
NB de span-tag is ook een html-hulpmiddel om de pagina in secties te verdelen.
Met wat experimenteren moet je in PQ tot een overzicht van li-tags kunnen komen.

Vouw de kolom Children uit en daarna nog een keer.

Daarmee hebben we kolom gevonden met de informatie die we zoeken. Nu moeten er nog wat schoningsacties worden uitgevoerd en de lay-out aangepast vergelijkbaar met het vorige voorbeeld, inclusief het omzetten van rijen naar kolommen (zie de query q_KNMI in het Voorbeeldbestand).

Om de dag en de datum in één kolom te krijgen hebben we de optie Kolommen samenvoegen in de menutab Transformeren gebruikt.

Power Query vraagt daarbij naar een gewenst scheidingsteken: kies Aangepast en vul dan in Character.FromNumber(10). Standaard wordt deze scheiding als tekst ingevuld, dat is niet de bedoeling. Verwijder in de formulebalk de twee ” en druk op OK.

Nog een paar kleine aanpassingen en kies dan Sluiten en laden:

En zo hebben we de meest recente weersverwachtingen in Excel. Op ieder gewenst moment kunnen we dit overzicht actualiseren.

NB1 om de lay-out in Excel mooi te krijgen hebben alle cellen van het overzicht een opmaak meegekregen (via Ctrl-1 of rechtsklikken; zie het tabblad KNMI van het Voorbeeldbestand).

NB2 zoals al eerder aangegeven kunnen we helaas op deze manier niet de plaatjes overhalen.

NB3 op de site van het KNMI staat ook de datum en tijd aangegeven waarop het overzicht is bijgewerkt. Een mooie oefening om die ook terug te vinden!