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 INE
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 INE
XCELHet '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 INE
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.xlsxWerk 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 INE
XCELSunny 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 INE
XCELKilometerheffing – 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 INE
XCELJe 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.