Categorie archief: Excel

Op koers



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 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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Hyperlinks



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:


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Power Query en het Web 2



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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Power Query en het Web



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!


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Histogrammen



In het vorige artikel op deze website (Frequentietabel en histogram) hebben we laten zien hoe je op verschillende manieren een frequentietabel kunt maken.
Door daar dan een grafiek op te baseren krijg je een zogenaamd histogram.

Als je alleen in de grafische weergave van de frequentietabel bent geïnteresseerd dan zijn er enkele alternatieve mogelijkheden om een histogram te maken. Hieronder bespreken we er drie. Lees om de gebruikte terminologie te begrijpen eerst het vorige artikel.

Ingebouwde grafiek

Sinds versie 2016 heeft Excel een ingebouwd grafiek-type om een histogram te maken.
Klik in de menutab Invoegen in het blok Grafieken op Statistische grafieken en kies daarna Histogram.

Om dit soort grafieken te kunnen maken moeten de te turven getallen allemaal onder elkaar (of naast elkaar) staan.
De proefwerkcijfers uit het vorige artikel zijn dan ook omgezet naar een zogenaamde database-indeling (zie het tabblad Data van het Voorbeeldbestand).

Selecteer alle gegevens (inclusief de koppen) en kies de Histogram-grafiek zoals hiervoor aangegeven.

Excel bepaalt zelf de grenzen voor én aantal van de intervallen (zie het tabblad Hist van het Voorbeeldbestand).
In dit geval krijgen we drie intervallen. Onder de horizontale as staan de grenzen van deze intervallen weergegeven.

Daarbij worden 2 symbolen gebruikt: rechte en ronde haken. Een rechte haak geeft aan dat de bijbehorende waarde bij het interval hoort, bij een ronde haak doet de waarde niet meer mee; het tweede interval loopt dus van 4,8 tot en met 7,6.

Meestal voldoen die grenzen niet aan onze wensen, maar daar is gelukkig nog wel wat aan te doen.

Klik met de muis rechts op een van de waarden van de horizontale as en kies As opmaken.

Binnen Excel worden de intervallen Bins genoemd. Als voorbeeld maken we de interval-breedte gelijk aan 1.

NB zie je het invulveld niet op het scherm, maak dan het vak met As opmaken breder door de linkerkant te verschuiven.

Het resultaat is bijna gelijk aan wat we in het vorige artikel zelf hebben gemaakt, behalve het eerste interval; we willen de 2 en 3 in een aparte kolom.

LET OP we hebben hier te maken met een onhebbelijkheid van de ingebouwde grafiek: de onder- en bovengrens kunnen niet buiten het bereik van de onderliggende getallen liggen! Het Aantal bins wijzigen helpt niet en ook niet het aanpassen van de boven- en/of ondergrens (in het eigenschappenscherm Overloop van bin en Negatieve overloop van bin genoemd)

Maar als we de ondergrens instellen op 2,1 dan begint het er op te lijken!
Maar nu ziet de as er niet uit; helaas is daar verder niets aan te doen.

Uiteraard kan de lay-out van de grafiek nog aangepast worden door bijvoorbeeld een titel toe te voegen en de breedte van de kolommen aan te passen (klik rechts op één van de kolommen en kies Gegevensreeks opmaken)

NB vanwege de problemen met de indeling van de intervallen is de bruikbaarheid van dit grafiektype naar mijn mening beperkt.

Gegevensanalyse

Excel heeft een ingebouwde analyse-tool. Klik op de menutab Gegevens. In het blok Analyse zit de optie Gegevensanalyse.

Deze optie is standaard niet actief. Het activeren gaat als volgt:

  1. klik op de menutab Bestand
  2. kies Opties
  3. kies dan Invoegtoepassingen
  4. bij de inactieve toepassingen ziet u Analyses Toolpak
  5. selecteer die en klik op Start
  6. vink in het vervolgscherm de optie Analysis Toolpak aan

Hoe maak je hiermee een histogram?

  1. plaats eerst ergens in de sheet de data voor de gewenste intervallen (beter gezegd de bovengrens van de intervallen).
    Op het tabblad VerzBereik van het Voorbeeldbestand staan die in de cellen D3:D12.
  2. klik op de optie Gegevensanalyse in de menutab Gegevens
  3. selecteer in het vervolgscherm Histogram
  1. vul bij Invoerbereik de cellen in die de getallen bevatten waarvan een histogram gemaakt moet worden (dus zonder de teksten daarnaast en ook zonder de kopregel).
  2. het Verzamelbereik is het gebied met de intervallen uit punt 1.
    NB als je Labels aanvinkt zorg er dan voor dat én het Invoerbereik een kopregel bevat én het Verzamelbereik.
  1. geef dan bij het Uitvoerbereik de cel aan, waar de uitvoer zal komen.
    NB zorg voor voldoende lege ruimte rechts van deze cel en naar beneden. De uitvoer heeft minstens 2 kolommen en het aantal regels is gelijk aan het aantal intervallen + 2
  2. wil je niet alleen de frequenties maar ook een cumulatief, vink dan de betreffende optie aan
  3. we willen natuurlijk een grafiek, dus plaats een vinkje bij de laatste optie
  4. klik dan op OK

Het resultaat van bovenstaande staat in de cellen D2:E13. Het Verzamelbereik is gelijk aan onze opgave bij de invoer, maar met één extra regel, aangeduid met Meer. In deze categorie komen alle getallen die groter zijn dan de laatst opgegeven bovengrens. Deze werking komt overeen met die van de functie INTERVAL (zie het artikel Frequentietabel en histogram); deze analyse-tool zal intern ongetwijfeld van deze functie gebruik maken.

NB deze methode om een histogram te maken is aanzienlijk flexibeler dan het ingebouwde grafiektype.
Een groot nadeel is wel dat, wanneer er aanvullende gegevens zijn of als je andere intervallen wilt gebruiken, alle handelingen opnieuw moeten worden uitgevoerd.

Draaigrafiek

  1. maak een draaitabel op basis van alle gegevens in de kolommen B:D van het tabblad Data van het Voorbeeldbestand.
  2. vul het schema in zoals hiernaast (zie het tabblad Draai)
  3. om de draaigrafiek te maken kies je in de menutab Analyseren van Hulpmiddelen voor draaitabellen in het blok Extra de optie Draaigrafiek

Dit levert een frequentietabel en histogram op voor alle proefwerken. Wil je de resultaten van één speciaal proefwerk zien, selecteer dit dan bij Proefwerk in de draaitabel of de draaigrafiek. Dit kun je bij geen enkele van de voorgaande methodes gemakkelijk doen.

NB1 wil je een andere indeling van de intervallen maak dan gebruik van de groepeer-mogelijkheden binnen een draaitabel (zie het artikel Groeperen in een draaitabel).

NB2 zijn de brongegevens aangevuld, dan volstaat het om de draaitabel of draaigrafiek te vernieuwen (via rechts klikken). Wel moeten deze gegevens dan in een Excel-tabel zijn opgenomen anders moet eerst de bron uitgebreid worden (via de optie Andere gegevensbron).

NB3 met deze derde methode maken we toch eerst een frequentie-tabel en het voorbeeld hoort dus eigenlijk in dit rijtje niet thuis. Maar het is de meest flexibele, handigste en betrouwbaarste methode!

Andere voorbeelden van histogrammen

Histogrammen gebruik je als je frequenties van bepaalde gebeurtenissen zichtbaar wilt maken.

We hebben hiervoor al gezien, dat het bijvoorbeeld een handige manier is om de verdeling van proefwerkresultaten van leerlingen weer te geven.
Daarnaast is het een goed hulpmiddel om te zien hoe de gewichten en/of lengtes van een bepaalde groep mensen zijn verdeeld.

LET OP als je histogrammen gebruikt is het van het grootste belang dat de onderliggende populatie qua samenstelling ‘homogeen’ is. Toon je bijvoorbeeld de verdeling van de gewichten van diverse personen dan is het raadzaam om per geslacht een andere grafiek te maken. Maar als de leeftijden ver uit elkaar lopen, dan moet je ook daarvoor verschillende categorieën maken.

Een ander voorbeeld: als fietstraining heb ik geprobeerd 30 km op een constante, (voor mij) hoge trapfrequentie van 90 omwentelingen per minuut te rijden. Op een koude, regenachtige dag lekker binnen met behulp van Zwift. Hierboven zie je het eerste gedeelte van het resultaat (zie het tabblad Zwift van het Voorbeeldbestand). Iedere seconde legt dat programma vast op welke virtuele hoogte je fietst, welke afstand je in die seconde hebt afgelegd etc. Ook de trapfrequentie ofwel de cadans is geregistreerd. Deze output is niet direct bruikbaar, maar daar kunnen we met behulp van Power Query wel iets aan doen (zie het tabblad Zwift):

Maken we daar een frequentietabel en een histogram van (tabblad ZwiftOverz), dan blijkt het niet helemaal gelukt om in de buurt van die 90 te blijven. Was dat nou in het begin van de ‘tocht’, op het einde of tijdens beklimmingen? Deze vragen kunnen niet via een histogram beantwoord worden; dat vergt een ander soort analyse.

De omvormer van mijn zonnepanelen levert een overzicht van de opbrengst op dagbasis. Ook die dagresultaten nodigen uit tot het maken van een histogram (zie het tabblad ZonPanelen van het Voorbeeldbestand).

Kort samengevat: van ieder overzicht waarin per persoon of per seconde/minuut/uur/dag of per 100m/km of per klas of per …. gegevens vastliggen kan makkelijk een histogram gemaakt worden.
Wel zul je voor nadere analyse vaak nog dieper op de gegevens moeten inzoomen.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: