EXCEL IN EXCEL. Excel Advanced. Danny Devriendt

2

EXCEL IN EXCEL

Excel Advanced

Danny Devriendt

I

NHOUD FORMULEBALK IN

E

XCEL VERGROOT WEERGEVEN

Inhoud formulebalk in Excel vergroot weergeven

Als je met Ctrl + muiswiel in- of uitzoomt, vergroot het lint en de formulebalk niet mee. Maak je gebruik van het Windows vergrootglas voor in- en uitzoomen (o.a. via met + of – op

numerieke klavier) om op je volledige scherm in- en uit te zoomen, dan vergroot ook het lint met de formulebalk relatief mee. Je krijgt Vergrootglas venster dat je met + Esc sluit.

Algemeen kan je ook de lettergrootte in de formulebalk vergroten via de opties van Excel zelf (Bestand > Opties, Algemeen). Je vergroot er de tekengrootte naar bv. 20 pt.

Van zodra je Excel afsluit en opnieuw opent, wordt de inhoud van de formulebalk altijd in 20 pt weergegeven. Hier geldt als nadeel dat in nieuwe Excel-bestanden ook 20 pt in de gewone cellen gebruikt wordt, tenzij je de lettergrootte in je werkblad aanpast via Start > Stijlen >

Celstijlen > Standaard. Weeg zelf af wat best uitkomt.

F

ORMULES ZICHTBAAR MAKEN

1 Formules zichtbaar maken

Normaal krijg je in je rekenblad het berekend resultaat van de formules zien. Je kunt op elk moment ook de achterliggende formules zichtbaar maken via Formules > Formules controleren > Formules

weergeven of Ctrl+T (switch). Als leerkracht kan je dit handig gebruiken!

2 Inleiding - Probleemoplossend werken in Excel: the max()

Excel is veel meer dan een verzameling van losse functies en tools en afhankelijk van je doelgroep en de beschikbare tijd kan je er op verschillende manieren mee aan de slag. Belangrijk is en blijft dat je vooral op probleemoplossend denken en werken focust. Om dat te illustreren gaan we op drie verschillende manieren met een eenvoudige functie aan de slag: de functie max().

2-1 De max()-functie

De max() functie op zich is een eenvoudige functie die uit een reeks getallen de hoogste waarde haalt:

max(getal1;[getal2]…). De argumenten zijn getallen of bereiken* met getallen of berekeningen die getallen bevatten.

Met de drie voorbeeldopdrachten hieronder proberen we te illustreren op welke verschillende manieren je met die functie aan de slag kunt, startend bij het eenvoudig gebruiken tot het probleemoplossend en geïntegreerd gebruik.

*Een bereik is een groep al dan niet aaneengrenzende cellen, van één cel tot het volledige werkblad, waarop een bepaalde formule of actie van toepassing is. Het bereik A1:B2 bevat bv.

de cellen A1, A2, B1 en B2. Je kunt een bereik ook aanspreken via een bereiknaam die je toekent door de betrokken cellen te selecteren en in het naamvak, links van de formulebalk, de gewenste bereiknaam in te typen.

Voortaan kan je de bereiknaam als argument in formules gebruiken, bv. =som(omzet).

Opdracht 1 – kolom met hoogste waarde

Een klassieker, gekoppeld aan de opdracht: plaats in rij 17 voor elke diersoort de hoogste maandwaarde, al iets beter geformuleerd dan Gebruik de functie MAX om in rij 17 de hoogste maandwaarde weer te geven als je dat in een test of opdracht vraagt.

I

NLEIDING

- P

ROBLEEMOPLOSSEND WERKEN IN

E

XCEL

:

THE MAX

()

slachtingen.xlsx

Hier zitten we op het niveau gebruiken van een functie in de meest eenvoudige context. Opzettelijk hebben we deze keer niet voor de alom gebruikte 'puntenlijsten' gekozen.

De formule in B17: max(B4:B15).

Goed als basisoefening, maar we proberen er toch iets meer uit te halen, uiteraard afhankelijk van het niveau van de leerlingen, de beschikbare tijd…

Opdracht 2 – hoogste waarde opvallend opmaken

Deze keer doen we het met vissen én met een concrete probleemstelling/opdracht:

Geef bij elke vissoort de maand met de hoogste vangst opvallend weer: witte letters, blauwe achtergrond.

bodemvissen.xlsx

We gaan er hierbij vanuit dat de leerlingen met voorwaardelijke opmaak leerden werken. Binnen de ingebouwde voorzieningen kan je de Bovenste x items van specifieke opmaak voorzien. Dat zou een mooi alternatief zijn voor de max() functie, ware het niet dat je de bovenste of hoogste waarde in deze opdracht per rij moet accentueren. Voor elke rij moet

je dus hier een opmaakregel voorzien en dat is behoorlijk intensief werk. Geen ideale oplossing dus.

I

NLEIDING

- P

ROBLEEMOPLOSSEND WERKEN IN

E

XCEL

:

THE MAX

()

Toch kunnen we in één opmaakregel de hoogste waarde per rij afzonderlijk opmaken door de inhoud van elke cel op de rij te vergelijken met de hoogste waarde uit die rij. Als die aan elkaar gelijk zijn, dan heb je per definitie de hoogste waarde in die rij te pakken.

De formule die de voorwaardelijke opmaak bepaalt, wordt dan: =B5=MAX($B5:$M5) op de volledige selectie gegevens met B5 als eerste cel in het bereik waarop de voorwaardelijke opmaak van toepassing is.

Bij het opbouwen van de formule test je bovendien uitstekend op het begrijpen en toepassen van absolute/relatieve celadressering, de dollartekens.

Aangezien we elke cel één voor één controleren, mag de B5 in het linker lid van de vergelijking geen dollartekens krijgen. In het rechterlid moet er altijd vergeleken worden met alle waarden uit de rij.

Vandaar dat vóór de B en de M zeker een $-teken moet staan. Vóór de rijen mag dan weer geen dollarteken komen, want je moet natuurlijk telkens met de juiste relatieve rij vergelijken.

Ook deze oefening is dus een toepassing op de MAX-functie in een andere context en een breder kader.

Opdracht 3 – solidariteitsbijdrage CO

₂

Probleemstelling: Op de CO2-uitstoot van een auto wordt een solidariteitsbijdrage betaald, die

afhankelijk is van het type brandstof: benzine, diesel of LPG. De formule voor de maandelijkse bijdrage is:

Solidariteitsbijdrage = [(CO2 uitstoot x € 9) – brandstofbonus]/12

De formule wordt elk jaar geïndexeerd. De indexatiecoëfficiënt voor het voorbije jaar 2014 is 1,2048.

De minimumbijdrage per maand is 25,10 EUR.

In de uitwerking hieronder wordt in de cel A2 een CO2-uitstoot ingetypt. In B2 bied je een keuzelijst aan met keuze uit Benzine, Diesel of LPG. De cel C2 heeft als vaste waarde 1,2048 en de berekening gebeurt in D2.

A B C D

1 CO2-uitstoot (g/km) Brandstof Indexatiecoëfficiënt Solidariteitsbijdrage 2 In te typen waarde Keuzelijst Gegeven Berekenen

De brandstofbonus die in de formule mag afgetrokken worden, vind je in het tabelletje hiernaast. Je kunt die gegevens in je werkblad plaatsen en ze bv. de bereiknaam brandstof geven.

Bij de uitwerking kunnen we de max()-functie gebruiken en wel als volgt:

=MAX(($A$2*9-VERT.ZOEKEN($B$2;brandstof;2;ONWAAR))/12*$C$2;25,1)

Als de berekening van de solidariteitsbijdrage lager is dan 25,1 wordt sowieso de constant opgegeven waarde 25,1, tweede argument van de MAX()-functie gebruikt.

Vaak zijn alternatieven mogelijk, ook hier. Zo kan je de MAX()-functie vermijden door met de ALS()- functie te werken, zoals hieronder uitgewerkt.

=ALS(($A$2*9-VERT.ZOEKEN($B$2;brandstof;2;ONWAAR))/12*$C$2>25,1;($A$2*9- VERT.ZOEKEN($B$2;brandstof;2;ONWAAR))/12*$C$2;25,1)

solidariteitsbijdrage.xlsx

Conclusie

We mogen Excel-functies niet als geïsoleerde elementen bekijken maar moeten ze zoveel mogelijk integreren in probleemoplossende strategieën. Deze visie moet ondersteund worden door aanpak, keuze van oefenmateriaal en evaluatie met een moeilijkheidsgraad die enerzijds uitdagend is voor de leerlingen en anderzijds toch ook haalbaar zodat er geen ontmoediging optreedt.

Benzine 768

Diesel 600

LPG 990

O

PWARMING

…

Voldoende en goed gekozen oefenmateriaal biedt zeker ook mogelijkheden tot differentiatie binnen de klasgroep.

The max() functie diende hier enkel als voorbeeld. Met heel wat andere Excel-functies is een parallelle context te bedenken.

3 Opwarming…

3-1 Waar koop jij jouw TV?

WaarKoopIkMijnTV.xlsx

3-2 Kip aan 't spit – Pluimveeslachterij De Meersman-Van Driessche

Kip aan 't spit De Meersman-Van Driessche verkoopt kleine, middelgrote en grote kippen, respectievelijk voor € 6,00, € 7,00 en € 8,00. Bereken de dagelijkse omzet.

kip_demeersman.xlsx

G

EGEVENS ZOEKEN

:

VERT

.

ZOEKEN

() –

HORIZ

.

ZOEKEN

()

4 Gegevens zoeken: vert.zoeken() – horiz.zoeken()

Heel vaak zoek je in Excel informatie op aan de hand van een waarde, een code of een positie. Je maakt hier kennis met enkele klassieke functies die je helpen.

4-1 Verticaal zoeken

Voorbeeld 1 – studentendata

zoeken.xlsx studenten

Als in de cel H2 een studentID toegevoegd wordt (typen of bv. ook scannen van studentenkaart), moeten in kolom K de data van die student verschijnen. We zoeken dus data op basis van de studentID en alle studentID's staan onder elkaar (verticaal). Het bereik A2:E300 kreeg de bereiknaam studenten.

We maken hier gebruik van de functie vert.zoeken(). De gegevens waarin we zoeken moeten in de eerste kolom van het zoekbereik staan. Gegevens die links van de zoekkolom staan, kan je met vert.zoeken() niet ophalen. In de cel K2 wordt de basisformule dan:

=vert.zoeken($H$2;studenten;3;onwaar)

Een woordje uitleg:

we zoeken met de ID uit H2 (zoekwaarde), zowel bij voornaam, familienaam, groep, vakken; dus zetten we $H$2 vast, want we voeren de formule uit K2 straks door. In deze concrete situatie kan je in $H$2 één van de dollars eventueel verwijderen. Welke?

binnen het bereik studenten (tabelmatrix) wordt in de eerste kolom gezocht.

binnen het bereik studenten staat de voornaam in de derde kolom: 3 (kolom_indexgetal).

het benaderen is onwaar wat betekent dat in de eerste kolom gezocht wordt tot exact de ID uit H2 gevonden wordt. De volgende van de eerste kolom speelt geen rol.

bij het doorvoeren van de formule uit K2, moet je dan natuurlijk wel nog telkens het

kolom_indexgetal aanpassen: familienaam staat in de 2^e kolom, groep in de 5^e kolom en vakken in de 4^e kolom.

Benaderen waar (standaard)

De vierde parameter, benaderen, kan je weglaten. In dat geval wordt het benaderen standaard op waar ingesteld en krijg je nooit de juiste studenteninformatie te zien.

Foutcontrole

Als geen ID is ingevuld in H2, krijg je foutmeldingen in K2 tot K5. We kunnen dat op verschillende manieren vermijden.

Met de functie isleeg()controleer je of een cel leeg is of niet (true/false).

=als(isleeg($H$2);"";vert.zoeken($H$2;studenten;3;onwaar))

Als H2 leeg is, dan verschijnt een lege tekenreeks in K2. Enkel als de cel H2 een inhoud bevat, wordt de functie vert.zoeken uitgevoerd.

G

EGEVENS ZOEKEN

:

VERT

.

ZOEKEN

() –

HORIZ

.

ZOEKEN

()

Met de functie niet() kan je de acties omdraaien:

=als(niet(isleeg($H$2));vert.zoeken($H$2;studenten;3;onwaar);"Vul studentID in")

Als niets ingevuld is in H2, dan verschijnt in K2 de melding Vul studentID in.

Met de functie als.fout() ga je iets verder en controleer je of de ID uit H2 wel een match oplevert. Is dat zo, dan krijg je het resultaat te zien, is dat niet zo, dan wordt het tweede argument van de functie uitgevoerd.

=als.fout(vert.zoeken($H$2;studenten;3;onwaar);"ID ontbreekt of bestaat niet!")

De melding ID ontbreekt of bestaat niet! verschijnt enkel als de ingevoerde ID niet klopt.

Naast de controle of een cel leeg is – isleeg() – zijn er nog functies beschikbaar om de inhoud van cellen te controleren. Deze functies worden meestal binnen de als() functie genest en leveren een

waar/onwaar resultaat op (boolean): ofwel is dat zo, ofwel niet.

isgetal(): controleert of de waarde een getal is.

isteskt(): controleert of de waarde tekst is.

isgeentekst(): controleert of de waarde geen tekst is.

is.even() – is.oneven(): controleert of de waarde even of oneven is.

Voorbeeld 2 – tarieven pakjes PostNL

zoeken.xlsx postnl_vert

Op basis van de gegevens uit D8:F12 (bereik tarieven) krijg je in F4 het juiste tarief te zien. Dat tarief hangt van het gewicht af (invoeren in D4 in gram) en van de zone (EUR1 of EUR2; invullen in E4).

We zoeken hier in eerste instantie op aan de hand van het gewicht. De gewichtscategorieën staan onder elkaar, dus vert.zoeken(). Het grote verschil met het zoeken van studenten, is dat het gewicht niet exact voorkomt in de eerste zoekkolom: vanaf 1 tot 2000 g, vanaf 2001 tot 5000 g… maar het pakje kan ook 676 g of 3765 g wegen. Bij dit zoeken is het benaderen dus waar: we kunnen ook waarden opgeven die tussenin liggen.

Waar bij benaderen onwaar de volgorde in de eerste kolom niet belangrijk is, is ze dat hier wel. De scharnierwaarden in de eerste kolom moeten oplopend gerangschikt zijn.

Naast het gewicht speelt ook de zone een rol: in E4 wordt EUR1 of EUR2 ingevoerd (of geselecteerd uit een lijstje; zie verder in de cursus). De tarieven van EUR1 staan in de tweede kolom; die van EUR2 in de derde kolom. Hier moeten we een beetje creatief zijn. Wat denk je van deze formule in F4:

=VERT.ZOEKEN($D$4;tarieven;RECHTS($E$4;1)+1)

de cellen D4 en E4 zijn absoluut geadresseerd (dollars), maar aangezien we de formule in F4 niet kopiëren is dat niet echt een must.

de derde parameter (kolomindex_getal) is een berekende waarde. Hier kunnen we uit EUR1 en EUR2 het cijfer halen via de functie rechts() die later nog aan bod komt. Bij dat cijfer tellen we nog 1 op, om telkens in de juiste kolom te komen.

G

EGEVENS ZOEKEN

:

VERT

.

ZOEKEN

() –

HORIZ

.

ZOEKEN

()

de vierde parameter (benaderen) ontbreekt. Die is standaard waar. Je mag natuurlijk ook waar als vierde parameter toevoegen.

4-2 Horizontaal zoeken

zoeken.xlsx postnl_hor

Zelfde voorbeeld als hierboven, maar in de tarievenlijst staan de gewichten nu horizontaal naast elkaar.

De cellen I3:M5 zijn als bereik hortarief bekend.

=HORIZ.ZOEKEN($D$4;hortarief;RECHTS($E$4;1)+1)

De formule horiz.zoeken werkt analoog aan vert.zoeken; enkel de zoekrichting verschilt.

Verder in de cursus komt ook nog de index() functie en het nieuwe x.zoeken() aan bod.

H

ANDIGE TOOLS IN

E

XCEL

5 Handige tools in Excel

In dit hoofdstuk passeren een aantal handige tools en functies de revue. Stuk voor stuk functies die je zeker van pas zullen komen.

5-1 Willekeurige waarden – waarden plakken – transponeren

Transponeren

zoeken.xlsx postnl_hor

Het kan wel eens voorvallen dat gegevens die in kolommen voorkomen, beter in rijen omgezet worden of omgekeerd. Dat noemen we transponeren en dat kan in Excel eenvoudig gebeuren door de oorspronkelijke gegevens te selecteren, te kopiëren (Ctrl+C) en via Start > Plakken voor transponeren te kiezen. Op de plaats van de cursor worden de rijen in kolommen omgezet (of omgekeerd). Hieronder links de oorspronkelijke gegevens met rechts het getransponeerde resultaat.

5-2 Tekstfuncties en snel aanvullen (flash fill)

studentenlijst_tekstfuncties.xlsx, chesspirant.xlsx Ledenlijst

Hoewel Excel bij uitstek een rekenprogramma is, kan het ook handig met tekst omgaan. Enkele mogelijkheden maken een en ander duidelijk:

totaalnaam (kolom C). De totaalnaam bestaat uit voor- en familienaam met spatie. Gebruik de & als samenvoegteken (concateneren). Je plakt er inhoud uit cellen met letterlijke tekst – hier de spatie – aan elkaar.

=B2&" "&A2

sorteernaam (kolom D). We plakken hoofd- en kleine letters aaneen en zetten ze om in kleine letters. Functies uit dezelfde familie: hoofdletters(), beginletters().

=KLEINE.LETTERS(A2&B2)

De sorteernaam zal nu wel nog spaties en eventuele '-tekens bevatten. Eventueel kan je die met zoeken/vervangen uiteindelijk verwijderen.

gebruikersnaam (kolom E). De gebruikersnaam start bij iedereen met OM, daarna volgt lkr of bhr en dat hangt af van de inhoud van kolom F: leerkracht of beheerder.

="OM"&ALS(F2="leerkracht";"lkr";ALS(F2="beheerder";"bhr";""))&A2&B2 Ook hier geldt: spaties en '-tekens nog verwijderen.

vakken opsplitsen (kolom H en I). In kolom G staan twee vakken, gescheiden door komma.

De bedoeling is om die vakken op te splitsen. Hier maken we gebruik van de intelligentie van Excel en het snel aanvullen (flash fill). Excel herkent immers snel patronen in lijsten. Typ een voorbeeld in: NW in H2 en TE in I2. Op basis van het voorbeeld kan Excel de rest van de kolom aanvullen.

H

ANDIGE TOOLS IN

E

XCEL Kies nu in de betreffende kolom Gegevens > Hulpmiddelen voor gegevens > Snel aanvullen (Ctrl+E) en de kolom wordt automatisch aangevuld.

groepscode (kolom L). De groepscode is samengesteld uit het eerste teken en de twee laatste tekens van de klasgroep, gescheiden door een liggend streepje en de laatste letter van de groep. De formule ziet er als volgt uit:

=LINKS(J2;1)&"-"&RECHTS(J2;2)&RECHTS(K2;1) Er is dus wel één en ander mogelijk.

Ook voor de totaalnaam had je flash fill kunnen gebruiken door één voorbeeldnaam in te typen. Kijk wel uit naar het resultaat, zeker als sommige namen uit drie woorden bestaan, andere uit twee of één woord. Flash fill voegt tekst toe alsof die ingetypt zou zijn. Met de formule =B2&" "&A2 stel je de totaalnaam samen. Wijzigingen in de kolommen A en B worden hier automatisch aangepast. Soms handig, soms niet.

Flash fill werkt enkel vlot als de opties correct zijn ingesteld (Bestand > Opties > Geavanceerd).

Gebruik je formules om veldinhoud samen te stellen, dan blijf je van de basisinformatie afhangen. Wil je dat niet, dan kan je het resultaat van de formule omzetten in een waarde. De formule verdwijnt dan en het is net alsof je de tekst zelf zou ingetypt hebben. Dit doe je als volgt:

selecteer de betrokken cellen en kopieer ze.

kies bij het plakken voor Waarden plakken > Waarden.

Let wel! Wijzigingen in de bronwaarden hebben nu geen effect meer op je samengestelde waarden.

5-3 Beveiligde werkbladen

zoeken.xlsx postnl_vert

In principe kan iedereen alles wijzigen in je Excel-werkbladen. Het is natuurlijk vaak niet de bedoeling dat iedereen zomaar – moedwillig of per ongeluk – inhoud en formules in een werkblad kan aanpassen.

In het voorbeeld van het berekenen van het juiste tarief voor een postNL postpakket, moet de gebruiken enkel in de cellen D4 (gewicht) en E4 (zone) iets kunnen invoeren.

Om dit te realiseren, zijn twee stappen nodig:

In principe zijn alle cellen in een werkblad beveiligd/geblokkeerd. Via Start > Getal >

Meer getalnotaties of via het

uitklappictogram of via het snelmenu bij de cellen, Celeigenschappen kom je in het venster Cellen opmaken terecht. In het

tabblad Bescherming schakel je de optie Geblokkeerd uit in alle cellen waar je de gebruiker wel toegang toe wilt geven. Daarmee gebeurt nog niets effectiefs. Er is een tweede stap.

H

ANDIGE TOOLS IN

E

XCEL

Kies nu Controleren > Beveiligen > Blad beveiligen en geef een wachtwoord op. De geblokkeerde cellen – en dat zijn dus de meeste cellen – zijn nu niet langer toegankelijk voor de gebruiker. Naast een werkblad, kan je ook een volledige werkmap beveiligen.

Met het wachtwoord kan je de beveiliging later opheffen.

In het tabblad Bescherming kan je ook cellen Verborgen zetten. De gebruiker ziet in dat geval de formules niet in de verborgen cellen als het werkblad beveiligd is.

5-4 Werken met datums

zoeken.xlsx dagen en weken

Je weet al dat achter elke datum een getal zit en dat je via de getalnotatie de weergave van de datum bepaalt. We kunnen gelukkig nog meer met datums. Enkele mogelijkheden:

het weeknummer uit een datum halen met de functies iso.weeknummer() en weeknummer(). Bij weeknummer() is de week waarin 1 januari valt de eerste week. Bij iso.weeknummer() geldt de week met de eerste donderdag van elk jaar als week 1.

Vaak geven beide functies hetzelfde weeknummer, maar niet altijd. 1 januari 2022 valt bv. op een zaterdag. Bij weeknummer() zal dit week 1 zijn; bij iso.weeknummer() blijft dit nog week 52 van het vorige jaar.

met de functies dag(), maand() en jaar() haal je de numerieke waarde van dag, maand of jaar uit een datum.

met de functie weekdag() haal je het nummer van de weekdag uit een datum. Standaard start Excel met zondag als eerste dag. Voeg je bij weekdag als tweede parameter het cijfer 2 toe, dan geldt maandag als dag 1 van de week, bv. weekdag(A3;2) levert in het voorbeeld hierboven 5 op.

wil je de eerstvolgende werkdag berekenen, gebruik dan de functie werkdag(). werkdag(A3;1) levert maandag 6 juli 2020 op, de eerstvolgende werkdag na vrijdag 3 juli.

wil je op basis van een datum de laatste dag van de maand of van de volgende maand berekenen, bv. voor een vervaldag, dan gebruik je de functie laatste.dag(). laatste.dag(A3;0) levert de laatste dag van de maand op. laatste.dag(A3;1) levert de laatste dag van de volgende maand op.

Werkdagen berekenen

In heel wat toepassingen is het interessant om het aantal (werk)dagen tussen een start- en einddatum te berekenen. De functie netto.werkdagen() helpt.

H

ANDIGE TOOLS IN

E

XCEL het aantal dagen berekenen, kan gewoon door de datums van elkaar af te trekken.

B5: =B3-B2

betreft het werkdagen, dan gebruik je de functie netto.werkdagen() die weekenddagen niet meetelt:

B6: =NETTO.WERKDAGEN($B$2;$B$3)

wil je ook officiële feestdagen uitsluiten, dan geef je een lijstje met die feestdagen als derde parameter mee.

B7: =NETTO.WERKDAGEN($B$2;$B$3;$E$2:$E$14)

5-5 Grootste() – kleinste()

zoeken.xlsx omzet

Met min() en max() haal je respectievelijk de laagste en de hoogste waarde uit een reeks getallen. Maar hoe haal je bv. de drie hoogste/laagste waarden uit een reeks getallen? Daarvoor hebben we de functies grootste() en kleinste().

=grootste(omzet;2) haalt de op één na grootste omzet uit het bereik omzet.

=kleinste(omzet;3) haalt de op twee na laagste omzet uit het bereik omzet.

5-6 Aantal.als – aantallen.als – som.als – sommen.als

zoeken.xlsx council meeting

H

ANDIGE TOOLS IN

E

XCEL

In bovenstaand voorbeeld is voor een meeting aangegeven wie een lunch geboekt heeft (X) en hoeveel er moet betaald worden voor de overnachting. De deelnemers komen uit verschillende landen en continenten. Sommige deelnemers zijn vegetariërs of veganisten. Ze zijn board member, council member of honorary member.

De berekeningen onderaan werden als volgt uitgevoerd:

F46: =AANTAL.ALS(F3:F44;"X")

Hier is er één criterium: er moet een X in kolom F staan om meegeteld te worden. Er moet geteld worden, dus aantal.als().

F47: =AANTALLEN.ALS(F3:F44;"X";E3:E44;"Vegan")

Hier zijn er twee criteria: X in kolom F en type lunch Vegan in kolom E. Bij meer dan één criterium gebruik je aantallen.als().

F48: =AANTAL.ALS(D3:D44;"honorary member") Opnieuw tellen met één criterium, dus aantal.als().

G50: =SOM.ALS(D3:D44;"board member";G3:G44)

Hier wordt niet geteld, maar opgeteld. Er moet dus een kolom met numerieke waarden zijn (optelbereik). Die geef je als laatste parameter mee. Er is één criterium, dus som.als().

G51: =SOMMEN.ALS(G3:G44;C3:C44;"Europe";D3:D44;"board member")

Bij optellen met verschillende criteria gebruik je sommen.als(). Het optelbereik wordt hier als eerste parameter opgegeven.

5-7 Willekeurige waarden – aselect() – aselecttussen()

zoeken.xlsx wachtwoord

Er zijn wel wat situaties te vinden waarbij je met willekeurige waarden kunt werken. In principe zijn dat getallen:

aselect() genereert een willekeurig decimaal getal tussen 0 en 1, 1 niet inbegrepen.

aselecttussen(1;1000) genereert een willekeurig geheel getal tussen de opgegeven waarden, beide opgegeven waarden inbegrepen.

Elke keer als je werkblad bijgewerkt wordt, krijg je een ander willekeurig resultaat. Je kunt ook manueel bijwerken via Formules > Nu berekenen (F9).

Door dit te combineren met bv. vert.zoeken kan je ook een willekeurige keuze genereren.

Voorbeeld – een wachtwoord genereren

Wil je bij voorbeeld een lijst met willekeurige wachtwoorden genereren, maak dan vooraf een genummerde lijst met alle tekens waaruit het wachtwoord kan bestaan:

kleine letters, hoofdletters, tekens… Zelf laat ik altijd de letters i, l en o uit de lijst omdat die in sommige lettertypes verwarrend kunnen zijn: O (letter O) en 0 bv. of I en l.

Dit wordt dan de formule om bv. wachtwoorden van 8 willekeurige tekens te selecteren:

=VERT.ZOEKEN(ASELECTTUSSEN(1;62);tekens;2)&VERT.ZOEKEN(ASELECTTUSSEN(1

;62);tekens;2)&VERT.ZOEKEN(ASELECTTUSSEN(1;62);tekens;2)&VERT.ZOEKEN(A SELECTTUSSEN(1;62);tekens;2)&VERT.ZOEKEN(ASELECTTUSSEN(1;62);tekens;2)

&VERT.ZOEKEN(ASELECTTUSSEN(1;62);tekens;2)&VERT.ZOEKEN(ASELECTTUSSEN(1

;62);tekens;2)&VERT.ZOEKEN(ASELECTTUSSEN(1;62);tekens;2) Er is geen controle op bv. minimum één cijfer, twee hoofdletters…

Eenmaal de wachtwoorden gegenereerd kan je ze selecteren, kopiëren en de waarden plakken. De formule erachter is nu weg en nu veranderen de wachtwoorden niet meer.

5-8 Rest() en afronden

zoeken.xlsx koekjes

Met het formuliertje hiernaast kan allerlei gebak besteld worden. In kolom A worden gewenste aantallen ingevuld. De gebakjes zitten in blisters gegroepeerd verpakt en binnen elke blister

individueel verpakt. Het aantal stuks/blister vind je in kolom C.

Bereken op basis van de getallen uit kolom A per product het aantal volle blisters en het eventuele aantal individuele stuks.

In D3 wordt de formule voor het aantal volle blisters:

=AFRONDEN.NAAR.BENEDEN(A3/C3;0)

In E3 kan je natuurlijk met =A3-(C3*D3) de resterende losse stuks berekenen, maar dat kan ook als volgt met de rest() functie die de restwaarde van een deling berekent:

=REST(A3;C3)

Het afronden kan op verschillende manieren gebeuren in Excel:

afronden(): rondt een getal naar onder/boven af op het aantal aangegeven cijfers na de komma.

=afronden(27,68;1) > 27,7

=afronden(27,64;1) > 27,6

=afronden(34540,-3) > 35000

afronden.naar.beneden()/afronden.naar.boven(): je geeft een getal en een aantal decimalen op. Het getal wordt altijd naar beneden afgerond op het aantal decimalen.

=afronden.naar.beneden(12,78;0) > 12

=afronden.naar.beneden(12,78;1) > 12,7

=afronden.naar.beneden(12386,24;-2) > 12300

=afronden.naar.boven(134,12;1) > 134,2

Je kunt dus ook een negatief aantal decimalen ingeven om op tientallen, honderdtallen… af te ronden.

afronden.naar.boven: je geeft een getal en een aantal decimalen op. Het getal wordt altijd naar boven afgerond op het aantal decimalen.

afronden.beneden()/afronden.boven(): je rondt een getal af op basis van een veelvoud.

=afronden.beneden(17;5) > 15

=afronden.boven(12,3;0,5) > 12,5

5-9 Berekening in schijven

notariskosten.xlsx

Er zijn heel wat praktische situaties te bedenken waar de berekening van bedragen schijfsgewijs gebeurt, bv. in de personenbelasting waar het belastbaar inkomen in schijven ingedeeld wordt.

Op het eerste gedeelte tot 12 990 EUR betaal je 25 % personenbelasting. Op het gedeelte tussen 12 990 en 22 290 EUR betaal je 40 % belasting. Verdien je meer dan 22 290 EUR, dan betaal je 45 % belasting op de schijf 22 290 tot 39 660 EUR en 50 % belasting op alles boven de 39 660 EUR.

In ons uitgewerkte voorbeeld gaan we bij de notaris langs voor de aankoop van een onroerend goed. Het ereloon van de notaris wordt schijfsgewijs berekend, zoals je hiernaast kunt zien. In kolommen A en B vind je de schijven met in kolom C het bijhorende procent ereloon.

In de eerste schijf betaalt iedereen. Of je in de hogere schijven moet betalen, hangt van de hoogte van de aankoopprijs af.

In D7 wordt de formule:

=ALS($D$4>$B$7;$B$7;$D$4)

Als je aankoopprijs (D4) hoger is dan de bovengrens van de schijf (B7), dan betaal je op die bovengrens het ereloon, zoniet betaal je dat ereloon op de lagere aankoopprijs.

In de schijven die daarop volgen, wordt de formule iets complexer: er zijn immers 3 mogelijkheden:

is je aankoopprijs lager dan de ondergrens van de schijf, dan betaal je geen ereloon in die schijf.

is je aankoopprijs hoger dan de bovengrens, dan betaal je ereloon op het verschil tussen bovengrens en ondergrens van de schijf, op de volledige schijf dus.

ligt je totale aankoopprijs tussen ondergrens en bovengrens van de schijf, dan betaal je ereloon op de aankoopprijs verminderd met de ondergrens van de schijf. Op het bedrag lager dan de

ondergrens heb je immers al in vorige schijven betaald.

=ALS($D$4<=$A8;0;ALS($D$4>$B8;$B8-$A8;$D$4-$A8)) De laatste berekening (rij 13) is opnieuw iets eenvoudiger:

ofwel is je aankoopprijs hoger dan de ondergrens van de schijf – er is geen bovengrens – en dan betaal je ereloon in die laatste schijf op het verschil tussen de aankoopsom en de ondergrens.

ofwel is je aankoopprijs lager dan de ondergrens en dan betaal je natuurlijk geen ereloon meer in die schijf.

=ALS($D$4>$A$13;$D$4-$A$13;0)

F

ORMULIEROBJECTEN IN

E

XCEL

6 Formulierobjecten in Excel

formulierobjecten_opg.xlsx, scrollen_schuifbalk_bevolking_opg.xlsx.

De eerste vorm van interactie met Excel is natuurlijk het invoeren van gegevens door ze in de cellen van een werkblad in te typen. Maar er is meer mogelijk. In dit artikel maak je kennis met enkele van de klassieke formulierobjecten die Excel rijk is, geïllustreerd met praktische voorbeelden.

6-1 Formulierobjecten toevoegen

Vreemd genoeg zijn formulierobjecten niet toegankelijk na een gewone installatie van Excel. Je vindt immers de formulierobjecten terug via het tabblad ONTWIKKELAARS >INVOEGEN >

FORMULIERBESTURINGSELEMENTEN en dat tabblad is standaard niet actief.

Eenmalig ga je dus via BESTAND >OPTIES >LINT AANPASSEN in de lijst met tabbladen het tabblad Ontwikkelaars moeten activeren.

We beperken ons in dit artikel tot enkele 'klassiekers' uit de gewone formulierbesturingselementen:

keuzelijst met invoervak, selectievakje, keuzerondje, groepsvak en de schuifbalk.

Je selecteert gewoon het gewenste element/object en klikt in je Excel-werkblad op de plaats waar het object moet komen (linkerbovenhoek). In plaats van klikken kan je ook een rechthoek slepen die het object in de gesleepte grootte weergeeft.

In principe kan je elk van de objecten om het even waar in een werkblad opnemen. De plaats waar het

object in het werkblad staat is technisch niet zo belangrijk. Denk hierbij vooral aan het gebruikersgemak van de gebruiker.

Elk van de objecten kan je aanpassen door via het snelmenu BESTURINGSELEMENT OPMAKEN te kiezen.

Bij KENMERKEN bepaal je hoe je het object plaatst:

normaal zal het object mee verplaatsen als je bv.

kolommen breder/smaller maakt, tenzij je kiest voor Verplaatsing en formaat niet gerelateerd aan cellen.

In dat geval behoudt het object zijn absolute plaats op het werkblad en moet je het eventueel zelf verplaatsen.

6-2 De keuzelijst met invoervak

Plaats de keuzelijst in het gepaste formaat op de gewenste plaatst. Je houdt natuurlijk rekening met het soort informatie dat in de keuzelijst komt om de breedte ervan te bepalen zodat alle inhoud zichtbaar is.

Je kunt een object verplaatsen door het te selecteren.

Zo lukt het zeker, want het selecteren valt soms tegen:

gebruik het snelmenu in de keuzelijst en de keuzelijst wordt geselecteerd

F

ORMULIEROBJECTEN IN

E

XCEL

klik nu buiten het snelmenu en versleep de keuzelijst gewoon met je muis.

Via het snelmenu kies je het tabblad BESTURINGSELEMENT om twee heel belangrijke parameters op te geven:

het INVOERBEREIK bepaalt wat in de keuzelijst te zien is. Die gegevens moeten ergens in je werkmap ter beschikking zijn. De elementen moeten altijd verticaal geplaatst worden: elke celinhoud is dan een item in de keuzelijst. Je verwijst op de gewone manier naar het invoerbereik. Je kunt een bereik slepen of gebruik maken van een bereiknaam. Het verwijzen via structured reference naar een kolom in een tabel lukt niet.

KOPPELING MET CEL: als je een keuze maakt in een keuzelijst dan vangt Excel die keuze in een andere, willekeurige cel op. Je kiest hier gewoon een lege cel die je niet voor iets anders gebruikt. In die cel komt telkens een getal terecht dat aangeeft het hoeveelste element in de keuzelijst je kiest. In de illustratie

hiernaast zal de keuze Oost-Vlaanderen dus een 3 opleveren, omdat Oost-Vlaanderen het derde element in de keuzelijst is. Daar moeten we het dan verder mee doen.

AANTAL REGELS NAAR BENEDEN: hiermee bepaal je in langere keuzelijsten hoeveel elementen je tegelijk te zien krijgt, m.a.w. hoe groot de keuzelijst is bij openklappen. Zijn er meer elementen dan het opgegeven aantal, dan wordt automatisch een verticale schuifbalk aan de keuzelijst toegevoegd.

Een concreet voorbeeld. De keuzelijst hieronder bevat de namen van vertegenwoordigers uit kolom F.

De kolommen E-G bevatten de gegevens die we voor de didactische duidelijkheid naast de keuzelijst plaatsten. Kies je een vertegenwoordiger, dan moet de detailinformatie (provincie, omzet) verschijnen zoals geïllustreerd.

De keuzelijst is gekoppeld aan de cel J1. Je kan de waarde eventueel onzichtbaar maken door de tekstkleur op wit in te stellen, de kolom te verbergen...

De formule in I9 wordt:

=INDEX(tblVertegenwoordigers;$J$1+1;2)

Vermits de uitvoer van de keuzelijst een getal is, kunnen we hier niet met verticaal zoeken werken, tenzij je in de gegevenstabel een eerste kolom toevoegt waarin je de gegevens oplopend nummert, maar dat is nergens voor nodig.

We hebben immers de index() functie waarmee we uit een gegevensbereik gegevens oppikken aan de hand van rij- en kolomnummer. Aangezien de weergegeven informatie in de keuzelijst uit het

gegevensbereik komt, beschik je over het bijna correcte rijnummer. De keuze Fran Defour resulteert in de waarde 7 in J1 omdat zij het zevende element in de keuzelijst is. Maar in het gegevensbereik staat haar informatie op de achtste rij, één rij lager omdat het gegevensbereik een titelrij bevat die niet in de keuzelijst is meegenomen. Dus verhogen we de waarde in J1 gewoon met 1.

F

ORMULIEROBJECTEN IN

E

XCEL De derde parameter van de index()-functie is het gewenste kolomnummer. De naam van de

vertegenwoordiger staat in kolom 2.

Om nu de provincie en de omzet op te vragen, kan je de formule gewoon kopiëren, mits je uiteraard absoluut verwijst naar J1: $J$1. In de gekopieerde formule pas je nu gewoon het kolomnummer aan:

Provincie:

=INDEX(tblVertegenwoordigers;$J$1+1;1)

Omzet:

=INDEX(tblVertegenwoordigers;$J$1+1;3)

Een voordeel van de index() functie t.o.v. vert.zoeken() is dat de kolomvolgorde eigenlijk geen rol speelt, terwijl bij vert.zoeken() de kolom waarin je zoekt altijd de eerste kolom van het gegevensbereik moet zijn.

Een keuzelijst kan je dus perfect gebruiken om allerlei informatie in gegevensbereiken/tabellen op te zoeken en weer te geven.

Keuzelijst met invoervak versus lijst via gegevensvalidatie

Naast een keuzelijst-object, kan je ook via gegevensvalidatie in een keuzelijst voorzien. Zo’n keuzelijst beperkt de inhoud van een cel tot een reeks keuzes.

Aan de cel L2 in het voorbeeld hierboven wordt nu een keuzepijl toegevoegd die enkel te zien is als je de cel activeert. Kies je nu bv. Karel Bruynooghe, dan krijgt de cel L2 effectief die inhoud. Om nu informatie over Karel op te zoeken, kan je niet meer met de index() functie werken, maar met vert.zoeken().

In L9 wordt de formule:

=VERT.ZOEKEN($L$2;$F$2:$G$20;1;ONWAAR)

.

Je kunt in L9 eigenlijk ook gewoon =L2 als formule plaatsen en de naam gewoon overnemen. Het benaderen is natuurlijk onwaar want de vertegenwoordigers staan niet eens alfabetisch (voornamen staan eerst).

Let op het bereik van de keuzelijst: de eerste kolom moet de kolom met opzoekwaarden zijn bij vert.zoeken() en dus kunnen we de provincie niet meenemen en ook niet opvragen. Hier zou je de kolommen in het gegevensbereik moeten omwisselen zodat de naam van de vertegenwoordigers de eerste kolom van het bereik is.

Een keuzelijst via gegevensvalidatie heeft wel enkele extra mogelijkheden:

de gegevens waarop de keuzelijst gebaseerd is, hoeven niet in kolomvorm (verticaal) ter beschikking te zijn. Je kunt ook gegevens in rijvorm (naast elkaar) selecteren om in de keuzelijst weer te geven.

Bij een keuzelijst-object kan dat niet.

F

ORMULIEROBJECTEN IN

E

XCEL

je kunt gemakkelijk lettergrootte/weergave… van de gegevens in de keuzelijst aanpassen. Bij een keuzelijst-object lukt dat niet.

je kunt hier wel verwijzen naar een kolom in een tabel zodat je keuzelijst dynamisch aangepast wordt als de tabel uitbreidt.

Wat dat laatste betreft, heb je de functie INDIRECT() nodig om naar de bron voor je keuzelijst te verwijzen:

=INDIRECT("tblVertegenwoordigers[Vertegenwoordiger]")

Met bovenstaande code wordt de kolom VERTEGENWOORDIGER uit de Excel-tabel TBLVERTEGENWOORDIGERS als bron voor de keuzelijst gebruikt. De keuzelijst past zich dynamisch aan de inhoud van die kolom aan, bv. als de tabel uitbreidt.

6-3 Selectievakjes

Je kiest een selectievakje uit de formulierobjecten en klikt in je werkblad om een selectievakje te plaatsen of je sleept de grootte van het selectievakje. Naast het selectievakje verschijnt

selectievakje x. Uiteraard pas je die tekst aan. De tekst heeft duiding bij je keuze maar is verder niet van belang.

Ook hier kan je de grootte van de tekst niet aanpassen, maar via het tabblad Kleuren en lijnen kan je wel achtergrondkleur en omranding instellen.

Belangrijk is dat de keuze van je selectievakje via een gekoppelde willekeurige cel wordt doorgegeven (de cel H1 in ons voorbeeld). Afhankelijk van je keuze zal die cel de inhoud WAAR of ONWAAR bevatten.

Je bepaalt via het snelmenu ook of het selectievakje standaard ingeschakeld is of niet. Een eenvoudig voorbeeld:

zonder korting met korting

Studenten met studentenkaart krijgen 5% korting op het totaal bedrag aan aankopen in F2. Een selectievakje bepaalt of een studentenkaart werd voorgelegd en enkel in dat geval wordt de korting toegekend: zowel het bedrag als de tekst in E3.

E3:

=ALS(H1;"5% studentenkorting";"")

F3:

=ALS(H1;-F2*0,05;"")

Je controleert dus in de cel H1 de status van het selectievakje en combineert dat met de als() functie. Let op de formule. Je kunt gewoon verwijzen naar de cel H1 als die WAAR is: als(H1… . Dit is hetzelfde als de formule als(H1=waar…. De waarde in de cel H1 is immers juist of fout, waar of onwaar, true or false. als(H1="waar"… zal niet werken, want de cel H1 bevat nooit letterlijk de tekst ‘waar’. Verder zullen als(niet(H1)… en als(H1<>WAAR… wel werken, maar natuurlijk het omgekeerde resultaat opleveren.

6-4 Keuzerondjes en groepsvakken

Keuzerondjes gebruik je normaal om een keuze te maken uit twee of meer opties. Keuzerondjes sluiten elkaar uit zodat je slechts één keuze kunt maken.

F

ORMULIEROBJECTEN IN

E

XCEL Plaats een eerste keuzerondje in je werkblad. Om volgende keuzerondjes te plaatsen selecteer je het eerste keuzerondje en sleep je naar in de gewenste richting terwijl je de Ctrl-toets ingedrukt houdt. Op die manier maak je snel verschillende extra keuzerondjes.

Aan elk keuzerondje wordt automatisch een intern oplopend volgnummer toegekend. Dat nummer is de waarde die in de gekoppelde cel verschijnt als je een bepaalde keuze maakt. Het volstaat één

keuzerondje te koppelen aan die 'hulpcel'; alle andere keuzerondjes worden automatisch aan dezelfde hulpcel gekoppeld.

Naast elk keuzerondje voorzie je natuurlijk tekst die de keuze verduidelijkt. Die tekst speelt verder niet mee in de verwerking van de keuze. Controleer zorgvuldig de volgorde van de keuzerondjes en hun nummer dat ze in de gekoppelde cel genereren en pas eventueel aan!

Om de keuzerondjes netjes te ordenen, kan je ze selecteren (via START > ZOEKEN EN SELECTEREN > OBJECTEN SELECTEREN bv.). Via het tabblad OPMAAK dat dan verschijnt, keuze UITLIJNEN kan je de keuzerondjes snel en mooi in alle richtingen uitlijnen en de ruimte tussen de keuzerondjes netjes gelijkmatig verdelen.

Er duikt natuurlijk een probleem op als je in een werkblad verschillende keuzes via keuzerondjes wilt laten maken, zoals bv. de leeftijdscategorie en het geslacht in ons voorbeeld. In dat geval groepeer je de verschillende keuzes in groepsvakken. Je sleept gewoon een groepsvak – ook één van de

formulierbesturingselementen – rond de betrokken keuzerondjes en via het label kan je aangeven waarover de keuze gaat.

De keuzes binnen leeftijdscategorie koppel je uiteraard aan een andere cel, dan de keuzes over geslacht en op die manier kan je heel wat verschillende keuzes laten maken die los van elkaar gebeuren.

In het voorbeeld hierboven krijg je op basis van de gegevens in de kolommen G:K in de cellen D10:E10 informatie over de leeftijdsgrenzen van een gekozen categorie en een gekozen geslacht. Achter de keuzes bij leeftijdscategorie zit de cel D1. Het geslacht wordt in de cel E1 bijgehouden. In beide gevallen betreft het dus het volgnummer van het keuzerondje.

Aangezien we een volgnummer krijgen, is hier het gebruik van de index-functie opnieuw aangewezen:

D10:

=INDEX(categorie;$D$1;2+ALS($E$1=1;0;2))

Het bereik categorie loopt van G5 tot K14 en dat komt netjes overeen met de keuzes: het eerste keuzerondje Senior heeft nummer 1, de eerste rij in het bereik.

Voor het geslacht wordt het iets moeilijker. Dit geslacht moeten we linken aan de kolom waaruit de informatie moet gehaald worden. Bij de mannen is dat kolom 2 en 3; bij de vrouwen kolom 4 en 5.

Hierboven lossen we dat op door bij het kolomgetal 2 op te tellen bij vrouwelijk geslacht. In dit concrete voorbeeld kan je ook zonder als() functie werken:

F

ORMULIEROBJECTEN IN

E

XCEL

D10:

=INDEX(categorie;$D$1;2+($E$1-1)*2)

In E10 vertrek je dan gewoon vanuit kolom 3, één kolom meer naar rechts.

Nog dit: je zou verwachten dat je zo’n groepsvak als object bv. een achtergrondkleur, randkleur… kan meegeven, maar dat is niet zo. Je kan natuurlijk wel de cellen achter het groepsvak inkleuren en het groepsvak mooi boven die cellen positioneren.

Evenmin verplaatsen objecten in het groepsvak automatisch mee als je het groepsvak verplaatst. Je moet dus de objecten mee selecteren vooraleer te verplaatsen.

Ook selectievakjes kan je natuurlijk ook in groepsvakken ordenen om je werkblad visueel overzichtelijk te houden, zeker bij uitgebreidere toepassingen.

6-5 Schuifbalken

De schuifbalk is het laatste formulierobject dat we hier bespreken. Je kunt een schuifbalk zowel horizontaal als verticaal plaatsen.

De uitvoer van de schuifbalk komt in een gekoppelde cel terecht en is een getal waarvan je de waarde zelf bepaalt door bij de eigenschappen een minimumwaarde,

maximumwaarde en een stapsgewijze wijziging op te geven.

De minimumwaarde is de waarde als het schuifblok helemaal links of bovenaan staat. De

maximumwaarde krijg je volledig rechts op onder. Elke keer je op de pijltjestoets drukt, verhoog/verlaag je de waarde met de ingestelde stapsgewijze wijziging. Bij Paginawijziging geef je het interval op waarmee gesprongen wordt telkens je tussen schuifknop en pijltjestoets klikt.

Helaas kan je de waarden in het dialoogvenster enkel intypen en kan je bv. niet naar cellen verwijzen om de schuifbalk iets dynamischer te maken.

Een klein voorbeeld. Achter de schuifbalk zitten de waarden 1 tot 22. Er zijn immers 22 vertegenwoordigers in de lijst erboven.

Via de schuifbalk kan je nu één voor één detailinformatie ophalen. De gegevens staan er nu juist boven, maar kunnen gerust op een totaal andere plaats staan.

De formule in de cel E27 wordt nu:

=INDEX(tblVertegenwoordigers;$H$25;2)

Aangezien de output van de schuifbalk een getal is, gebruiken we opnieuw de index() functie. De cel H25 bevat de waarde van de schuifbalk, niet toevallig ook de rij waaruit informatie moet opgehaald worden.

6-6 Schuifbalken en verschuiven

Concreet voorbeeld: in een beperkt deel van een

overzichtspagina moeten we toch bevolkingsgegevens van honderden gemeenten kunnen

raadplegen/weergeven zonder dat we scherm per scherm moeten scrollen. We voorzien daarvoor een beperkte lijst met telkens tien gemeentes en via een schuifbalk kan de gebruiker dan toch snel doorheen

F

ORMULIEROBJECTEN IN

E

XCEL alle gemeentes scrollen en de informatie die hij nodig heeft raadplegen. De plaats van de schuiver in de schuifbalk bepaalt welke gemeentes je te zien krijgt, zoals je hieronder ziet:

Om dit voor elkaar te krijgen, combineren we enkele mogelijkheden binnen Excel. Het spreekt vanzelf dat je over alle gegevens van alle gemeentes moet beschikken en die zijn in ons voorbeeld alfabetisch per gemeente weggezet in het tabblad gegevens dat we verder enkel als bron gebruiken, zonder in het tabblad zelf te werken. Je kunt eventueel het tabblad gerust verbergen (via snelmenu op tabblad onderaan).

De schuifbalk

Je voegt een schuifbalk/scrollbar aan je werkblad toe via het tabblad Ontwikkelaars > Invoegen > Formulierbesturings- elementen > Schuifbalk. Mocht het tabblad Ontwikkelaars niet op het lint te zien zijn, dan kan je dat via Bestand >

Opties > Lint aanpassen in je lint weergeven.

Als je de schuifbalk in de lijst met formulierbesturings- elementen kiest, kan je die nu op elke gewenste plaats in je werkblad slepen en zo het formaat ervan bepalen. Die schuifbalk plaats je natuurlijk best zo dicht mogelijk bij je

gegevens; in ons voorbeeld voorzien we er een afzonderlijke kolom (C), netjes naast de namen van de gemeentes. Kolom C blijft voor de rest leeg en de breedte van de kolom is op de breedte van de schuifbalk afgestemd.

We voorzien dat er telkens 10 gemeentes tegelijk zichtbaar zijn en passen ook de hoogte van de schuifbalk in functie daarvan aan.

Het resultaat van een keuze via de schuifbalk is altijd een geheel getal dat tussen een opgegeven

minimum- en maximumwaarde ligt. Dat stel je via het snelmenu > Besturingselement opmaken in.

In ons voorbeeld krijg je dus keuzes tussen 0 helemaal bovenaan en 636 helemaal onderaan de schuifbalk (minimumwaarde en maximumwaarde).

Als de schuiver in het midden staat, zal dat dus (ongeveer) 318 als resultaat geven.

Waarom 636? Er zijn 646 gemeentes, maar we gebruiken straks de schuifbalk om te bepalen waar de scrolllijst start in rij 3 van het werkblad. Aangezien er in de scrolllijst 10 items voorkomen, eindigen we zo op 636 + 10 = 646.

Als je op de pijltjes in de schuifbalk klikt, verspring je telkens per 1 omlaag/omhoog (stapsgewijze wijziging). Klik je boven of onder de schuiver, dan verspring je per 10 (Paginawijziging).

F

ORMULIEROBJECTEN IN

E

XCEL

Het 'resultaat' van de schuifbalk is dus altijd een waarde/getal en dat getal wordt in een willekeurige cel naar keuze opgeslagen (Koppeling met cel). In ons voorbeeld gebeurt dat in de cel C2 van het tabblad berekening.

Verschuiving

De functie Verschuiving doet op zich niet zo heel veel spectaculairs, maar we gebruiken die functie straks wel nuttig. Eerst even met een voorbeeld uitleggen wat die functie doet.

In het voorbeeld hieronder met formule =verschuiving(B2;2;3) vertrekken we in de cel B2, schuiven we twee cellen naar onder en daarna drie cellen naar rechts: het resultaat zou dus 185 zijn, de waarde van de cel E4.

Je kunt ook negatieve waarden opgeven om naar boven en/of naar links te schuiven.

Met twee extra parameters kan je een 'verschoven bereik' aanduiden, in combinatie met een functie die dan op dat bereik wordt toegepast, bv.

=som(verschuiving(B2;2;3;2;3)

Vanuit de cel E4 wordt een bereik van 2 rijen en 3 kolommen genomen, dus E4:G5 en daarvan wordt hier de som van de waarden gemaakt (gewoon theoretisch, want praktisch heeft dat in dit voorbeeld natuurlijk geen zin).

Terug naar onze oorspronkelijke opdracht: we hebben alle ingrediënten om die op te lossen.

Het tabblad berekening is een hulptabblad. In de cel C2 komt de keuze die via de schuifbalk gemaakt is (zie hoger).

In de cel C3 komt de hoogste positie die we nodig hebben.

Met de functie AANTALARG() tellen we het aantal ingevulde cellen in één van de kolommen in de gegevenstabel, bv. kolom B, tussen B2 en B1000. Tellen tot B647 zou hier voldoende zijn, maar het kan geen kwaad naar boven af te ronden, zeker niet in gegevenstabellen die kunnen uitbreiden (dit is hier niet het geval). Die extra lege cellen worden immers toch niet meegeteld.

Van dat aantal trekken we op dezelfde manier het aantal rijen in onze scrollijst af omdat we de formule straks bovenaan in de scrollijst starten en daar dus nooit de laatste gemeente mag verschijnen. Als de schuifbalk helemaal onderaan staat willen we de laatste 10 gemeentes zien.

=AANTALARG(gegevens!B2:gegevens!B1000)-AANTALARG(dashboard!A3:A12) Nu komt in de eerste cel van onze scrolllijst, A3, volgende formule:

=VERSCHUIVING(gegevens!A2;berekening!$C$2;0;1;1).

We nemen in die eerste cel ook de eerste cel uit onze gegevens (A2). We schuiven die cel niet op en nemen dus de waarde '1' over, tenminste als de schuifbalk helemaal bovenaan staat en de startwaarde 0 genereert die in de cel C2 van het tabblad berekening wordt gestockeerd.

F

ORMULIEROBJECTEN IN

E

XCEL Naarmate we nu de schuiver naar beneden slepen, verhoogt de waarde in die cel C2 en wordt in onze scrolllijst dus telkens een waarde lager uit de gegevenstabel weergegeven.

Die formule kopiëren we in de volledige scrolllijst, maar wel opgelet bij het horizontaal kopiëren: de kolom C blijft leeg, die dient enkel om onze schuifbalk netjes weer te geven en dus moet je in kolom D de formule aanpassen zodat je daar de gegevens van kolom C uit je gegevenstabel krijgt. In de cel D2 van de scrolllijst wordt de formule:

=VERSCHUIVING(gegevens!C2;berekening!$C$2;0;1;1) In kolom D van de scrolllijst kijken we dus naar kolom C van de gegevens.

Staat de schuifbalk helemaal bovenaan, dan krijgen we de gemeentes 1-10. Van zodra je naar beneden schuift, wordt dat 2-11, 3-12… en zo krijg je het gevoel dat alles mooi meeschuift met de schuifbalk, maar in werkelijkheid wordt er dus op elk moment dat je scrollt een hele berekening en aanpassing doorgevoerd:

de waarde van de schuifbalk in berekening!C2 wordt aangepast en die waarde bepaalt hoeveel rijen er omhoog/omlaag geschoven wordt

en zo krijg je dus telkens informatie uit andere rijen – andere gemeentes – te zien.

6-7 Oefeningen

Posttarieven

posttarieven.xlsx

Werk onderstaande toepassing uit:

Het gewicht in de cel C3 moet tussen 1 en 2000 g liggen. Zorg ervoor dat ofwel geen hogere waarde kan ingetypt worden, ofwel dat bij een te hoge waarde in de cel D3 de foutboodschap Max. 2000 g verschijnt. Het gewicht wordt telkens ingetypt.

De bestemming kies je via een keuzelijstje: België, Europa of Wereld.

Bij Prior vink je aan of het al dan niet om een prior zending gaat.

In C10 verschijnt het juiste aantal zegels met daaronder in C11 de correcte zegelprijs op basis van de data uit de kolommen I tot L. Zorg voor foutenopvang!

In C13 verschijnt de totaalprijs.

Extra

De prijzen zijn normaal berekend op aankoop van zegelbladen van minimum 5 zegels. Je kunt ook individueel een zegel kopen, mits toeslag die in rij 15 is opgegeven. Bereken eventueel de toeslag afzonderlijk en correct: 14 zegels betekent 2 zegelbladen van 5 zegels + 4 individuele zegels.

F

ORMULIEROBJECTEN IN

E

XCEL

Sunny cars – rent a smile

sunny cars.xlsx

Werk onderstaande toepassing zorgvuldig en in detail uit:

tabblad SunnyCars tabblad Data

Aandachtspunten

Zorg dat je in C3 een wagen kunt/moet kiezen uit een lijst met alle beschikbare wagens.

Na de keuze van een wagen, wordt de categorie en de dagprijs automatisch ingevuld.

Zorg bij de keuze van de datums van ophalen (Datum out) en terugbrengen (Datum in) voor een date picker. Zoek zelf een externe add-in.

De datum van terugbrengen mag uiteraard nooit vóór de datum van het ophalen liggen.

Bereken het totaal aantal dagen gebruik in C12.

Plaats een rechthoek met afgeronde hoeken rond het geheel.

Je berekent de totale basishuurprijs (C14) en trekt in C15 eventuele korting af.

In C16 krijg je het uiteindelijk te betalen bedrag.

Zorg voor foutenopvang waar nodig.

Zorg dat formules niet per ongeluk uit de cellen verwijderd kunnen worden.

Extra - Flexservice

Een reservering wijzigen is altijd gratis bij Sunny Cars. Toch meer flexibiliteit nodig? Dan is dit hét moment om onze unieke Flexservice af te sluiten. Hiermee kunt u namelijk uw huurauto tot 4 uur voor huuraanvang annuleren, zonder een reden op te geven. De Flexservice kost 1 Euro per huurdag (minimum 7 Euro en maximum 21 Euro) en kan later niet meer aan de reservering worden

toegevoegd.

Extra - Weekendtoeslag

Pas je document aan met een extra weekendtoeslag. Bij wagens die in het weekend opgehaald worden, reken je een vaste toeslag van € 10,00 aan. Idem bij inleveren: vaste toeslag van € 10,00.

Voor een wagen die op zaterdag opgehaald wordt en op zondag teruggebracht geldt dus in totaal

€ 20,00 toeslag.

F

ORMULIEROBJECTEN IN

E

XCEL

Kilometerheffing – Viapass

kmheffing_zeevisserij_oordopjes.xlsx

Sinds enkele jaren betalen vrachtwagens van meer dan 3,5 ton een kilometerheffing op Belgische (auto)wegen. Daarvoor heeft elke vrachtwagen nu een klein toestelletje aan boord, een On Board Unit (OBU), dat de kilometerheffing berekent en doorstuurt naar het facturatiecentrum. Viapass is de instelling die alles regelt.

In de kolommen H-K vind je de tarieven per km. Het tarief hangt af van het gewicht van de vrachtwagen én van de vervuilingsgraad (emissie fijn stof, C02 uitstoot…) van de vrachtwagen. Volgens de

vervuilingsgraad krijgt elke vrachtwagen een label, gaande van Euronorm0 tot Euronom6: Euronorm0 is sterk vervuilend en zo'n vrachtwagen betaalt natuurlijk meer. Met Euronorm6 vervuil je het minst en betaal je het minst.

Voorzie onder de titel Kies je gewichtscategorie (cel B7) een keuzelijstje waarmee je uit de drie gewichtscategorieën kunt kiezen. Je kunt de cellen H19 tot H21 gebruiken om je keuzelijst te vullen.

Voorzie onder de titel Kies je emissiecategorie (cel B10) een keuzelijstje waarmee je uit de verschillende Euronormen kunt kiezen.

In C13 schat je het aantal km/jaar dat de vrachtwagen op Belgische autowegen zal afleggen. Hier wordt gewoon een geschat aantal km ingetypt. Verzin zelf getallen om te testen.

In de cel C15 moet je op basis van de gekozen gegevens de juiste te betalen heffing/km weergeven.

In de cel C17 bereken je het totaal te betalen bedrag op jaarbasis. Let op: dat bedrag bedraagt altijd minimum 1250 EUR per/jaar, ook al worden er heel weinig kilometers gereden.

Zeevisserij

Met de opening van de nieuwe vistrap in Oostende, bekijken we in deze opdracht de aanvoer van verse vis in de Belgische – of Vlaamse? – kusthavens.

F

ORMULIEROBJECTEN IN

E

XCEL

Je beschikt over de aanvoergegevens per vissoort, zowel in kg als in waarde (EUR) in de havens van Nieuwpoort, Oostende en Zeebrugge (kolommen K-Q).

Zorg ervoor dat je in de cel B4 enkel kunt kiezen uit een lijst met alle beschikbare vissoorten. In de cel D4 laat je enkel keuzes uit de drie havens toe.

Op basis van de gekozen vissoort en de gekozen haven, geef je in de cellen B8 en D8 respectievelijk het aantal aangevoerde kg en de waarde van de vangst in EUR weer. Bereken op basis van die gegevens in de cel B11 de prijs per kg vis.

Voeg een selectievakje Distributie toe. Enkel als het selectievakje geselecteerd is, komt in de cel D10 de titel Prijs/kg distributie en bereken je in de cel D11 de distributieprijs die 80 % hoger is dan de

veilingprijs.

Foutafhandeling

Niet alle vissoorten worden in elke haven aangevoerd. Zorg ervoor dat er in zo'n geval in de cel B11 niet beschikbaar verschijnt en dat de cel D11 leeg blijft zonder mogelijke foutmeldingen.

V

OORWAARDELIJKE OPMAAK

7 Voorwaardelijke opmaak

voorwaardelijke opmaak.xlsx

In het tabblad Start, groep Stijlen, vind je Voorwaardelijke opmaak terug. Zoals de naam het laat vermoeden, kan je met deze mogelijkheid één of meer geselecteerde cellen van een bepaalde opmaak voorzien die afhangt van de inhoud van de betrokken cel(len).

Die opmaak is beperkt tot:

de notatie van de gegevens

de weergave (vet, cursief, onderstreept, doorstreept en tekstkleur); dus geen lettertype of lettergrootte de omranding

de achtergrondkleur.

Elke ingestelde opmaak wordt in een regel bijgehouden. O.a.

via de onderste keuzes in de lijst hiernaast kan je nieuwe regels toevoegen, wissen en beheren.

De regels die je kunt gebruiken gaan van zeer eenvoudig tot meer complex. De eerste opties in de lijst hiernaast helpen je snel enkele veel gebruikte regels in te stellen.

7-1 Gegevens t.o.v. elkaar visualiseren

Wil je de onderlinge verhouding van de waarden in een aantal geselecteerde cellen visualiseren, dan kan je gebruik maken van de mogelijkheden Gegevensbalken, Kleurenschalen en Pictogramseries.

Gegevensbalk Kleurenschaal Pictogram

Zo zie je via kleuren en pictogrammen onmiddellijk dat de gemeente Oostkamp relatief het meeste autowegen op haar grondgebied heeft.

Selecteer je de betrokken cellen, dan zie je via Regels beheren onmiddellijk welke regel(s) er op de geselecteerde cel(len) van toepassing zijn, bv. een pictogrammenset.

V

OORWAARDELIJKE OPMAAK

In het lijstje Opmaakregels weergeven voor kan je naast Huidige selectie ook Dit werkblad kiezen om alle regels uit het actieve werkblad weer te geven. Je kunt er ook de regels uit de eventuele andere

werkbladen weergeven.

Vanuit dit venster kan je een regel gemakkelijk bewerken (Regel bewerken) of zelfs verwijderen (Regel verwijderen). Hier kan je natuurlijk ook een nieuwe regel toevoegen.

Bij het bewerken kan je nog allerlei detailinstellingen regelen. Het venster hieronder spreekt voor zichzelf. Je kunt er zelfs voor opteren om enkel de pictogrammen weer te geven zonder de bijhorende cijfergegevens (Alleen pictogram weergeven).

7-2 Voorwaardelijke opmaak verwijderen

Je kunt voorwaardelijke opmaak per opmaakregel verwijderen (zie hierboven), maar ook in zijn geheel, zowel voor geselecteerde cellen als voor een volledig werkblad.

Vermits voorwaardelijke opmaak deel uitmaakt van de algemene opmaak, verdwijnt de voorwaardelijke opmaak ook als je via Start > Bewerken > Wissen de opmaak verwijdert.

7-3 Tegenstrijdige voorwaardelijke opmaak

Bij tegenstrijdige voorwaardelijke opmaak heeft een hogergeplaatste regel voorrang op onderliggende regels. Bij een waarde 15 in de opmaak hieronder, kleurt die waarde rood op roze achtergrond.

Met de pijltjesknoppen kan je de volgorde waarin regels toegepast worden aanpassen.

V

OORWAARDELIJKE OPMAAK

7-4 Snel voorwaardelijke opmaak toepassen

In de lijst Voorwaardelijke opmaak vind je de keuzes Markeringsregels voor cellen en Regels voor bovenste/onderste waarmee je snel voorwaardelijke opmaak kunt instellen.

Achter deze keuzes zit telkens een dialoogvenster waar je waarden kunt ingeven of verwijzen naar cellen die waarden bevatten. Er is ook een keuzelijstje met veel gebruikte opmaakcombinaties.

de keuze Tekst met… geeft cellen die een bepaald tekstdeel bevatten een bepaalde opmaak.

de keuze Een datum op of in… geeft datumcellen uit een bepaalde periode een bepaalde opmaak (zie lijstje hiernaast)

met Dubbele waarden… geef je waarden die twee of meer keer voorkomen een bepaalde opmaak. Je kunt via die keuze ook waarden die uniek zijn en slechts één keer voorkomen laten opvallen.

bij Bovenste/onderste 10 items en … 10 % kan je die 10 ook aanpassen en bv. de hoogste 3 items weergeven.

7-5 Maatwerk

Kies je in de lijst Voorwaardelijke opmaak rechtstreeks voor Nieuwe regel dan kan je op maat je opmaakregel samenstellen. Er zijn 5 types regels die we hieronder kort bespreken.

V

OORWAARDELIJKE OPMAAK

Alle cellen opmaken op basis van de celwaarden

Hiermee kies je opnieuw voor visualisatie op basis van een gegevensbalk, kleurenbalk of pictogrammen.

Alleen cellen opmaken met

Hiermee maak je cellen op die een waarde bevatten die kleiner, groter of tussen andere waarden ligt, die een bepaalde tekst bevatten, datums die in een bepaalde periode vallen… maar ook alle lege of niet- lege cellen of alle cellen die (geen) foutwaarde genereren.

Alleen waarden met de hoogste of laagste rangschikking opmaken

Deze keuze spreekt voor zich en is een alternatief voor wat ook hoger al is toegelicht.

Alleen unieke of dubbele waarden opmaken

Ook deze keuze is duidelijk en werd eerder vermeld.

Een formule gebruiken om te bepalen welke cellen worden opgemaakt

Deze keuze opent belangrijke mogelijkheden, echt 'maatwerk', en bespreken we hieronder uitgebreid.

Het is deze keuze die verder voortdurend gebruikt wordt in de opdrachten.

7-6 Opmaak op basis van een formule

Met deze keuze bouw je zelf een formule die een logische waarde genereert (waar of onwaar).

De formule begint altijd met een gelijkheidsteken dat je eigenlijk als als kunt lezen.

De juiste combinatie van absolute en relatieve celadressering (de $-tekens!) is heel belangrijk. Met enkele voorbeelden maken we dit duidelijk.

Voorbeeld 1 – gemeenten met meer dan 200 km gemeentewegen

In het voorbeeld hieronder krijgen de gemeentes met meer dan 200 km gemeentelijke wegen (kolom D) een blauwe achtergrond.

Je kunt hier niet zomaar één van de ingebouwde opmaakmogelijkheden toepassen, want we willen alle gegevens uit de betrokken gemeente van die blauwe achtergrond voorzien.

Je selecteert dus alle betrokken cellen (A2:E11) en werkt met een formule. Bestudeer de formule aandachtig:

ze begint met het verplichte gelijkheidsteken, te lezen als als ze genereert als resultaat waar of onwaar.

t.o.v. de begincel van de selectie (A2) wordt altijd een waarde uit de D-kolom vergeleken, vandaar de verplichte $ vóór de D. De rij waaruit de waarde genomen wordt, wijzigt telkens: bij Beernem D2, bij Blankenberge D3… dus zeker geen $-teken vóór de rij.

V

OORWAARDELIJKE OPMAAK Stappenplan bij het opbouwen van formules:

1 Selecteer alle cellen waarop de voorwaardelijke opmaak van toepassing is.

2 Kies Voorwaardelijke opmaak > Nieuwe regel > Een formule gebruiken om…

3 Typ een “=” in dat hier equivalent is voor de als-functie.

4 Typ de formule voor de eerste cel (startcel) uit je geselecteerde bereik (de cel linksbovenaan) in. Als deze formule als resultaat WAAR oplevert dan krijgt deze cel straks de gekozen voorwaardelijke opmaak.

5 Voeg $-tekens toe of verwijder ze waar nodig. De formule uit je startcel wordt als het ware automatisch naar alle andere cellen in je bereik gekopieerd, rekening houdend met de ingestelde $-tekens die dus zeer belangrijk zijn!

6 Ga in gedachten één stap naar rechts (volgende kolom) vanuit de startcel en denk na of de formule nog klopt. Pas eventueel aan door $-tekens toe te voegen of te verwijderen.

7 Ga in gedachten één stap naar beneden (volgende rij) vanuit de startcel en denk na of de formule nog klopt. Pas eventueel aan door $-tekens toe te voegen of te verwijderen.

8 Bepaal de gewenste voorwaardelijke opmaak en druk op OK.

Voorbeeld 2 – Overnachtingen in België

Informatie uit de landen waar in september meer overnachtingen waren dan in mei geven we gemarkeerd weer: witte letters op groene achtergrond.

De formule hieronder hoeft weinig uitleg. Begrijp je de $-tekens? Waar start je selectie?

Voorbeeld 3– Score

Markeer bij elke persoon de hoogste score door die in rode letters in een rood stippelkader weer te geven.

V

OORWAARDELIJKE OPMAAK

De formule vergt enige toelichting. We controleren in elke cel of die waarde gelijk is aan de hoogste waarde uit de rij. De meest linkse cel is B2 en die krijgt geen $-tekens omdat ook cellen in de rijen eronder en kolommen ernaast gecontroleerd worden.

Bij elke cel uit een rij, wordt de volledige rij gecontroleerd, dus altijd van kolom B tot kolom H: $-tekens vóór de kolomverwijzing.

De rij is telkens de rij waarin de cel staat, een lagere cel wordt in een lagere rij gecontroleerd: geen $- tekens vóór de rijverwijzing.

Oefening 1 –Verkoopcijfers_boeken

Enkele basisopdrachten:

visualiseer in de kolom afzet de gegevens met een gegevensbalk.

geef de drie laagste afzetaantallen in rode letters weer.

geef alle gegevens uit de week waarin het meeste boeken werden verkocht met een licht oranje achtergrond weer.

voeg een kolom Evolutie toe en vergelijk vanaf de tweede week de afzetevolutie telkens t.o.v. de week ervoor. Kleur de cel in de kolom Evolutie rood als de afzet gedaald is en groen als de afzet gestegen is.

bereken in rij 57 de gemiddelde omzet en het gemiddeld aantal afgezette boeken. Geef alle weken met een omzet die groter is dan de gemiddelde omzet in witte letters op donkerblauwe achtergrond weer.

Oefening 2 – Klaslijst

Voorzie voor de rijen 2 tot 30 volgende opmaak in de kolommen A tot D:

als in kolom A niets is ingevuld, dan is geen enkele opmaak zichtbaar.

V

OORWAARDELIJKE OPMAAK typ je in kolom A ‘6MO1’ in, dan worden de cellen A tot D automatisch van een donkerblauwe achtergrond voorzien. De gegevens die je intypt verschijnen in witte letters.

typ je in kolom A ‘6MO2’ in, dan worden de cellen A tot D automatisch van een oranje achtergrond voorzien. De gegevens die je intypt verschijnen in witte letters.

Oefening 3 – Faillissementen

bereken in rij 4 het totaal aantal faillissementen. Kopieer die gegevens naar rij 3 en vervang die gegevens daar door pictogrammen zoals geïllustreerd. Gebruik bij die pictogrammen een 20 pt lettertype (gewoon instellen; via voorwaardelijke opmaak kan je geen lettergrootte instellen).

geef vanaf rij 6 per activiteit telkens de maand(en) met de hoogste waarde in witte letters op oranje achtergrond weer.

Oefening 4 - Bevolking

geef alle informatie van alle West-Vlaamse gemeentes in witte letters op paarse achtergrond weer.

geef de namen van de gemeentes waarvan het totale bevolkingsaantal in 2018 lager is dan in 2017 in rode letters weer.

V

OORWAARDELIJKE OPMAAK

Oefening 5 – Voornamen_vrouwen

Je vindt een lijst met de populairste voornamen in Vlaanderen, Wallonië, Brussel en over geheel België.

Zorg ervoor dat in de cel O2 een voornaam kan ingetypt worden.

de ingetypte voornaam wordt opvallend weergegeven waar ze voorkomt in de lijst met voornamen (kan maximum 4 keer zijn).

in de cel P2 lees je af hoeveel keer de ingetypte voornaam voorkwam (max. dus 4 keer). Tip: dit laatste gebeurt niet met voorwaardelijke opmaak natuurlijk.

Oefening 6 – Voornamen_mannen

In deze oefening voorzie je alle even rijen met namen van een lichtblauwe achtergrond naar keuze, zoals hieronder geïllustreerd.

Met de functie rij() kan je het rijnummer opvragen. Denk ook nog aan de functie rest() waarmee je de rest van een deling kunt berekenen. Gebruik eventueel de helpfunctie.

Het afwisselen van opmaak van rijen kan ook nog op andere manieren uitgevoerd worden. Het is in deze opdracht wel de bedoeling het via voorwaardelijke opmaak te doen.

Oefening 7 – Kalender

In de cel A1 voorzie je een keuzelijst met de maanden van het jaar. Bij het kiezen van een maand, krijg je een kalender van die maand naar onderstaand model.

Automatisch moeten in de kolom A alle dagen van die maand verschijnen (voluit). In de kolommen L tot N vind je een lijst met de maanden en hun begin- en einddag die je hier nuttig kunt gebruiken.

Weekdagen krijgen een lichtblauwe achtergrond. Weekends krijgen een iets meer donkere achtergrond.

Tip: de functie weekdag() kan hier zeker helpen.