Categorie archief: Excel

Functie Indirect


LET OP: na het downloaden de extensie van het bestand wijzigen in xlsb


De Excel-functie Indirect heb ik de afgelopen tijd weer in menig artikel en project gebruikt.

In de praktijk blijkt het lang niet voor iedereen (direct) helder wat de functie doet en waar die dus voor ingezet kan worden.

Aanleiding voor een artikel; inclusief wat VBA-programmering om het voorbeeld uit het vorige artikel gebruikersvriendelijker te maken.

In 2015 heb ik al eens over deze functie geschreven. Deze keer kiezen we een andere invalshoek voor de uitleg en komen andere facetten van de functie aan bod. Zoals het spreekwoord luidt: dubbel genaaid houdt beter!

Indirect (versie 1)

Op het tabblad Cel van het Voorbeeldbestand is cel B2 gevuld met een tekst die aangeeft welke dag het vandaag is:
=”Vandaag is het “&TEKST(VANDAAG();”d-mm-jjjj”)

Cel B4 bevat de tekst B2 en in cel D4 staat de verwijzing naar cel B4:
=B4

Het resultaat in cel D4 is uiteraard de inhoud van cel B4, dus de tekst B2.

In cel D5 staat de formule =INDIRECT(B4)

Het resultaat is geen directe verwijzing naar cel B4, maar naar de inhoud van cel B4, dus naar cel B2. Een Indirecte verwijzing dus, vandaar de naam van de functie.

Simpel toch!

Nog een (iets ‘intelligenter’) voorbeeld:
in kolom B staan 5 namen. In cel E11 staat een indirecte verwijzing naar cel B12, waardoor de derde naam als resultaat wordt weergegeven.
De tussenstap (cel E10) is enigszins dynamisch gemaakt:
=”B”&D11+9
Dus aan de tekst B wordt het resultaat van de optelling van de inhoud van cel D11 en het getal 9, gekoppeld.

Wijzigen we nu cel D11 dan zal ook het resultaat in cel E11 veranderen.
Maar echt fool-proof is deze constructie niet: voeg maar eens een regel in boven regel 10.

In cel F10 is het statische getal 9 vervangen door het dynamische RIJ(B10)-1.

NB voeg je vóór kolom B een kolom in, dan zal deze formule ook niet meer goed werken. Wil je dit voorkomen dan moet de tekst B in de formule ook dynamisch gemaakt worden.

Indirect (versie 2)

De tussenstap in het vorige voorbeeld (in cel E10 wordt een verwijzing naar een andere cel geconstrueerd) kunnen we overslaan: de parameter die aan de functie Indirect wordt meegegeven kan ook een tekst zijn.

In het tabblad Tekst van het Voorbeeldbestand bevat cel D6 de formule =INDIRECT(“B4”). Het resultaat is de inhoud van cel B4.
De functie zorgt nu voor een indirecte verwijzing door een tekst om te zetten naar een bereik/range en daar de inhoud van op te halen.
De tekst moet het A1-formaat hebben, één of twee letters voor de kolom- en een getal voor de rij-aanduiding.

Deze tekst-versie kent nog een andere variant: cel D7 bevat de formule =INDIRECT(“R4K2”;ONWAAR) en geeft hetzelfde resultaat als de formule in D6. Hierbij wordt het zogenaamde R1K1-formaat voor de tekst gebruikt: eerst een getal voor de rij en dan een getal voor de kolom. Wel moet aan de functie een tweede parameter meegegeven worden met de waarde ONWAAR.

NB de tweede parameter is niet verplicht als je het A1-formaat gebruikt. Maar deze mag wel altijd ingevuld worden; heeft de tekst het A1-formaat dan moet deze tweede parameter de waarde WAAR hebben.

LET OP wanneer deze spreadsheet in een andere taal-versie van Excel wordt ingeladen dan kan het zijn dat de formule niet goed werkt. Om het bijvoorbeeld in de Engelstalige versie te laten werken moet de formule in cel D7 gewijzigd worden in =INDIRECT(“R4C2”;ONWAAR)

In de cellen G3 en G11 worden de twee varianten nog eens in een ander voorbeeld gebruikt:

De kracht van de tekst-versie van de Indirect-functie ligt er uiteraard in, dat de tekst op allerlei manieren geconstrueerd kan worden (zoals in de vorige voorbeelden in eenvoudige vorm wordt getoond).

Cel G12 bevat de formule =INDIRECT(TEKST(G9*10+G10;”R0K0″);ONWAAR). Op deze manier verwijzen we ook naar cel F4. Daarbij is gebruik gemaakt van de functie Tekst waarmee we de weergave van een getal kunnen bepalen. Door de rij-waarde te vermenigvuldigen met 10 en daar de kolomwaarde bij op te tellen krijgen we (in dit geval) de waarde 46. Met de notatie-aanduiding R0K0 geven we aan dat het laatste cijfer van het getal 46 achter de K moet komen en de overige cijfers tussen de R en de K .

Dit gaat echter fout, als we een verwijzing maken naar een kolomwaarde groter dan 9 (probeer maar eens door in cel G10 de waarde 10 in te voeren).

In cel G13 is dit opgelost: =INDIRECT(TEKST(G9*100+G10;”R0K00″);ONWAAR)

Het gebruik van een harde tekst (dus geen samengestelde tekst zoals hiervoor) als parameter kan bijvoorbeeld handig zijn om te testen of een bepaalde cel niet is verplaatst (al dan niet door het invoegen of verwijderen van rijen of kolommen).

In cel G15 wordt getest of cel F17 wel of niet de waarde Maand bevat.
Zo niet dan krijg je een waarschuwing.

Uiteraard hebben we dat met voorwaardelijke opmaak nog wat duidelijker gemaakt (groen is de standaard-kleur, rood als de inhoud afwijkt van de waarde “Ok”).

Gegevensvalidatie 1

In het Voorbeeldbestand vindt u in het tabblad GegVal1 een ‘systeem’ waarmee we de maximumsnelheid van een vervoermiddel kunnen ophalen uit een tabel. Daarbij is gebruik gemaakt van een zogenaamde afhankelijke Gegevensvalidatie (zie het vorige artikel). De keuzelijst in cel F3 is afhankelijk van de keuze in cel F2.

In cel F4 wordt de maximale snelheid van het gekozen merk opgehaald met de functie Vert.Zoeken. Een (flexibeler) alternatief staat in cel F5: =INDEX(C9:C15;VERGELIJKEN(F3;B9:B15;0))

Maar dat ophalen kan ook met behulp van de functie Indirect. Zie cel F6:
=INDIRECT(“C”&RIJ(B8)+VERGELIJKEN(F3;B9:B15;0))

NB1 door niet hard te verwijzen naar rij 8, maar de functie RIJ(B8) te gebruiken werkt de formule ook nog als er regels worden toegevoegd of verwijderd.

NB2 als je rijen toevoegt of verwijdert (of kolommen) dan krijg je een VBA-foutmelding. Kies de optie Beëindigen. De reden van deze foutmelding zullen we hierna uitleggen (en wat daar aan te doen!)

Zoals in het vorige artikel aangegeven zou het mooi zijn als het systeem automatisch het Merk leeg maakt als je het type Vervoermiddel wijzigt.

Daarom hebben we aan het tabblad GegVal1 een VBA-routine gekoppeld (klik rechts op het betreffende tabje onderaan en kies de optie Programmacode weergeven):

Iedere keer, dat er iets is gewijzigd in dit tabblad, wordt deze routine automatisch aangeroepen (Worksheet_Change). Welke cel of cellen er zijn gewijzigd wordt door Excel meegegeven in de parameter Target. We willen alleen maar iets doen (namelijk het merk leeg maken) als het type vervoermiddel wijzigt (cel F2). Daarom wordt in de eerste regel van de sub-routine gecontroleerd of Target daar gelijk aan is. Als dat niet zo is, gaat de routine verder met de opdracht na de laatste End If (de subroutine beëindigen dus).
De variabele VervMiddel bevat de inhoud van cel F2, zoals die was toen de routine vorige keer is aangeroepen. Daarom kijken we of die veranderd is. Is de huidige inhoud niet gelijk aan VervMiddel dan wordt cel F3 leeg gemaakt en maken we de variabele VervMiddel gelijk aan de inhoud van F2.

NB1 ik weet het: de routine is niet helemaal netjes geprogrammeerd. Wanneer je de eerste keer cel F2 wijzigt weet het ‘systeem’ helemaal niet wat er vorige keer in F2 stond. Dus F3 zal dan altijd leeg gemaakt worden.

NB2 de declaratie van de variabele VervMiddel gebeurt buiten de routine. Zouden we dat niet doen dan wordt deze variabele bij iedere Change opnieuw gedeclareerd en is dan dus altijd leeg.

NB3 deze Excel-werkmap bevat VBA. Bij het opslaan van het bestand moet je dan kiezen voor de extensie xlsm of xlsb.

NB4 verschuiven de cellen F2 en F3 dan moet de VBA aangepast worden. In cel I2 is daarom een controle met een waarschuwing ingebouwd (voeg maar eens een kolom in vóór kolom B).

Maar wat gebeurt er als je een rij of kolom toevoegt of verwijdert? Uiteraard wordt de routine Worksheet_Change aangeroepen, maar de parameter Target bevat helemaal niets. VBA wil dit ‘niets’ vergelijken met een Range en dat kan niet; vandaar dat we een foutmelding krijgen. Dat moeten we natuurlijk wel oplossen.

Gegevensvalidatie 2

In het tabblad GegVal2 van het Voorbeeldbestand staat een simpelere versie van het vorige voorbeeld. In de VBA-routine zit een belangrijk verschil:

In de eerste regel wordt via de VBA-functie Intersect gekeken wat de overlap tussen Target en Range(“F2”) is. Als er geen overlap is (ook als Target leeg is) dan wordt de rest van de routine overgeslagen. Nu krijgen we die vervelende foutmelding van VBA niet meer.

Indirect en Namen

Bij de voorbeelden met gegevensvalidatie is al gebruik gemaakt van Excel-namen. Overal waar je verijst naar een cel of naar een bereik van cellen kun je ook namen gebruiken, dus ook bij de functie Indirect.

Hiernaast staat een voorbeeld (zie het tabblad Namen in het Voorbeeldbestand).

De cellen C3:C12 hebben de naam Een en de cellen C13:C22 de naam Twee.

In cel F2 kun je (met behulp van gegevensvalidatie) kiezen uit deze twee namen.

De formule =SOM(INDIRECT(F2)) in cel F3 telt die cellen bij elkaar op, die door de naam in cel F2 worden bepaald.

LET OP Deze formule werkt alleen als de gegevens netjes met blokken bij elkaar staan (dus soort Een bij elkaar en ook soort Twee). De functie Indirect kan alleen een bereik van cellen verwerken als die cellen op elkaar aansluiten.

In de cellen F4 t/m F7 staan formules die ook het juiste resultaat opleveren, wanneer de gegevens niet netjes gesorteerd zijn.

Voorbeeld van het R1K1-formaat

In cel I4 van het tabblad R1K1 van het Voorbeeldbestand staat een formule die gegevens uit een tabel ophaalt, wanneer de gewenste rij en kolom zijn opgegeven.

Daaronder staan twee varianten; bij eentje wordt de Excel-functie Adres gebruikt.

In het blokje daaronder kunt u zelf ‘spelen’ met de mogelijkheden van deze Adres-functie.

Unieke gegevens selecteren

Hiernaast ziet u een overzicht van 3 lijstjes met daarop verschillende soorten fruit (zie het tabblad Uniek in het Voorbeeldbestand).

Als we willen weten welke soorten voorkomen zoeken we dus de unieke elementen van dit overzicht. Dat is een behoorlijke uitdaging. Maar ook hier komt de functie Indirect van pas.

In cel F3 staat de formule:
=INDIRECT(
TEKST(
MIN(
ALS(
($B$3:$D$10<>””)*AANTAL.ALS($F$2:F2;$B$3:$D$10)=0);
RIJ($3:$10)*100+KOLOM($B:$D);7^8)
);”R0K00″);
ONWAAR)
&””
Wil je ‘zien’ hoe de formule werkt, gebruik dan in de menutab Formules de optie Formule evalueren.

Aangezien het hier een zogenaamde matrix- of array-formule betreft moet deze worden afgesloten door op Ctrl-Shift-Enter te drukken.
Kopieer de formule nu net zo lang naar beneden totdat een of meer lege cellen het resultaat is.

Indirect en bereik-verwijzing

Nog een laatste voorbeeld van het gebruik van de functie Indirect; nu om een bereik van cellen aan te geven (zie het tabblad Range van het Voorbeeldbestand).

In cel F3 halen we vanuit een maand-overzicht het totaal tot en met een bepaalde maand op. Deze maand kan in cel F2 ingevoerd worden.

NB1 cel F2 heeft een gegevensvalidatie, zodat er een keuzelijst ontstaat.
Ook al lijkt deze toegestane lijst absoluut ($B$3:$B$14), wanneer je een rij toevoegt aan de tabel dan wordt deze lijst ook automatisch uitgebreid.

NB2 in cel F3 wordt voor het vergelijken gemakshalve naar de hele kolom B gekeken; wanneer je een complex en/of groot systeem hebt gebouwd kan dit vertragend werken. Het is beter om alleen maar in de cellen te zoeken die relevant zijn. In cel F4 staat de formule
=SOM(INDIRECT(“C3:C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]])))
Een variant daarop in cel F5:
=SOM(C3:INDIRECT(“C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]])))

In cel I4 wordt het totaal van een groepje te kiezen maanden bepaald.

De gegevensvalidatie van de twee grens-maanden is anders dan bij het vorige voorbeeld.
Hier gebruiken we de functie Indirect om te verwijzen naar de kolom Maand van de Excel-tabel met de naam Tabel3. Zonder Indirect is dit niet mogelijk.

LET OP gebruik aanhalingstekens rond de kolom-aanduiding.


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


Gegevensvalidatie



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.


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


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


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: