Info-books. Toegepaste Informatica. Deel 2d: Het rekenblad Excel Boekhouden-informatica Informaticabeheer. BI2d. Jos Gils Erik Goossens

Info-books

BI2d

Toegepaste Informatica Boekhouden-informatica Informaticabeheer

Deel 2d: Het rekenblad Excel 2003 Jos Gils – Erik Goossens

Woord vooraf

Info-books geeft een reeks informaticahandboeken uit die opgesteld zijn voor leerlingen van alle onderwijsniveaus, zowel het middelbaar als het hoger onderwijs. Bovendien is er een specifiek boek voor leerkrachten voorzien. Enerzijds is er een reeks boeken die voornamelijk bestemd is voor de derde graad van het middelbaar onderwijs. Deze reeks is een vervolg op de reeks Informatica 3 en Informatica 4 die door de uitgeverij Pelckmans voor de tweede graad uitgegeven wordt. Anderzijds richt Info-books zich ook naar het hoger onderwijs. Bepaalde boeken uit de vorige reeks werden herwerkt, andere zijn zonder meer bruikbaar en meer gespecialiseerde werken voor de richting Toegepaste Informatica werden toegevoegd. Bij het samenstellen van een boek probeert Info-books systematisch van een probleemstelling te vertrekken. Pas daarna wordt het materiaal aangereikt om dat probleem op te lossen. Informatica is een middel om problemen op te lossen. Het is niet louter een opsomming van hardwarecomponenten en programmeringstechnieken. Een ander punt waar Info-books veel belang aan hecht is integratie. Leerstofonderdelen zijn geen eilandjes. Het is dan ook logisch dat door vrijwel de hele boekenreeks een rode draad loopt. Een klein bedrijf wordt voortdurend met nieuwe problemen geconfronteerd en lost die met behulp van de computer op. Het gaat hier om concrete problemen zoals een facturatie die tot in het detail uitgewerkt wordt. Als het nodig is worden rekenblad, databaseprogramma en tekstverwerking samengebracht om tot een oplossing te komen. Integreren doe je niet om het integreren zelf maar om een probleem op te lossen. Niettemin is elk boekdeel zelfstandig bruikbaar. Je hoeft je dus bijvoorbeeld geen zorgen te maken dat voorkennis van Word noodzakelijk is om PowerPoint of FrontPage te kunnen gebruiken… In Info-books wordt op een praktische manier in één omgeving, Windows en meestal Microsoft Office, gewerkt. De opdrachten zijn gedeeltelijk in de hoofdstukken opgenomen, gedeeltelijk gegroepeerd op het einde ervan. Na elk hoofdstuk volgen een besluit en de leerdoelen of eindtermen ('Wat je moet kennen en kunnen:'). Het is niet de bedoeling dat alle opdrachten in de klas gemaakt worden. Dat hangt van de beschikbare tijd af.

De auteurs 2006

De publicaties van Info-books werden samengesteld in een Windows XP-, en Internet Explorer 6.0omgeving. Er werd gebruik gemaakt van Office 2002 (XP) en Office 2003. Van zodra een recentere versie in de omgeving of in het Officepakket de belangrijkste op de markt wordt, zullen de publicaties aangepast worden.

De reeks Informatica voor het Secundair 0nderwijs (Toegepaste Informatica) bestaat uit de volgende boeken: 

Boekhouden-informatica, Informaticabeheer: BI1c Deel 1: Systeemsoftware, Netwerken, Internet en Hardware (Win XP) BI2cd Deel 2: Het rekenblad Excel (*) BI3cd Deel 3: Gegevensbeheer in Access (*) BI4ac Deel 4: Programmeren in Visual Basic for Applications (**) (a: DAO-objecten, c: ADO-objecten) BI5 Deel 5: Programmeren voor een bedrijfsecon. toepassing: de facturatie (**)



Handel, Secretariaat-talen: HA1c Deel A: Systeembeheer, Netwerken en Internet (WinXP) HA2cd Deel B: Het rekenblad Excel (*) HA3cd Deel C: Gegevensbeheer in Access (*) HA40 Deel 40: Gegevensbeheer en algoritmen in Access

Voor alle richtingen, zowel Secundair als Hoger Onderwijs werden uitgewerkt: AL6cd AL7cd AL8c AL9cd AL10 AL11cd AL12 AL13 AL20

Deel 6: Deel 7: Deel 8: Deel 9: Deel 10: Deel 11: Deel 12: Deel 13: Deel 20:

Tekstverwerking met Word (*) Elektronisch presenteren met PowerPoint (*) Elektronisch publiceren: HTML (korte versie) en FrontPage (*) Elektronisch publiceren: FrontPage (*) Elektronisch publiceren: HTML (uitgebreide versie) Communicatie via Outlook 2002, 2003 JavaScript VBScript Algoritmen en programmeren in Access en Excel (Basis)

Voor het Hoger Onderwijs biedt Info-books de volgende publicaties aan: HO31 Deel 31: Dubbel boekhouden HO33a Deel 33a: ERP met Navision, Basis en Financieel 1 HO33b Deel 33b: ERP met Navision, Financieel 2 HO34 Deel 34: Internetapplicaties HO36 Deel 36: JavaScript (uitgebreide versie) HO37 Deel 37: Programmeren in Java HO38ac Deel 38: Gegevensbeheer in Access (basis en uitbreiding) (*) HO39 Deel 39: Scripttalen HO40a,b Deel 40a,b: Programmeren in Java: AWT en SWING HO40c,d Deel 40c,d,e: Java voor het internet: Servlets, JSP, Persitentie en Threads (*) Deze boeken worden zowel in de versie 2002/XP [c] en 2003 [d] aangeboden. (**) Deze boeken zijn geschikt voor de versie 2002/XP en 2003. Inlichtingen kunt u ook bekomen op onze website: http://www.info-books.be Jos Gils Boskant 26, 2970 Schilde Telefoon: 03 383 57 24 E-mail: [email protected]

Erik Goossens Eekstraat 67, 9240 Zele Telefoon: 052 44 64 40 E-mail: [email protected]

2006, Schilde – Zele Niets uit deze uitgave mag verveelvoudigd en/of openbaar gemaakt worden door middel van druk, fotokopie, digitale media, microfilm of op welke andere wijze ook, zonder voorafgaandelijke schriftelijke toestemming van de auteurs.

Hoofdstuk 1

1.1

Herhaling van enkele basisbegrippen

Probleemstelling De tijd van de rekenmachine is nog niet volledig voorbij, maar toch…als je veel met cijfers moet werken kan een rekenblad handig zijn. Een rekenblad dient in de eerste plaats om te rekenen. Het kan zelfs veel meer dan een gesofistikeerd rekenmachientje zijn. Je kunt een rekenblad gebruiken voor een eenvoudige facturatie, om je budget te beheren, om je puntenrapport samen te stellen, om verkoopcijfers grafisch voor te stellen, … Een rekenblad kan aan getallen een gezicht geven. Bekijk het onderstaande voorbeeld aandachtig: de firma Jong & Dorst verkoopt verschillende frisdranken (limonade, fanta, …) in diverse verkooppunten (Brugge, Kortrijk, Aalst en Lommel) en wenst een overzicht van de omzet per frisdrank en per verkooppunt. Deze gegevens kunnen in een overzichtelijke tabel opgenomen worden, samen met de totalen per verkooppunt (eventueel ook per frisdrank) en de procentuele aandelen van elke frisdrank en van elk verkooppunt in het totaal. Die procentuele aandelen kunnen in een eenvoudige grafiek voorgesteld worden.

1.2

Excel opstarten Bij het opstarten van MS Office Excel wordt het startscherm verticaal in twee delen gesplitst:  

De linkerhelft bevat de cellen waarin de gegevens geplaatst worden; De rechterhelft wordt het taakvenster genoemd en bevat in dit voorbeeld een menusysteem 'Nieuwe werkmap'. Het taakvenster groepeert verschillende soorten acties. Standaard wordt het taakvenster ‘Aan de slag’ getoond. Het taakvenster kun je activeren via de menuoptie BEELD, TAAKVENSTER.

J. Gils – E. Goossens

Rekenblad: Excel 2003

5

1.3

De werkomgeving Wanneer het Taakvenster gesloten wordt vergroot het gegevensgebied:

6



1.3.1 Het werkblad Dit is de plaats waar het grote werk gebeurt. Een werkblad bestaat uit rijen en kolommen. De rijen worden genummerd van 1 tot … en de kolommen van A tot …. Kolom

Dit is de aaneengesloten reeks van alle cellen die op een verticale lijn liggen. De verwijzing naar kolommen gebeurt meestal met letters. Na de letter Z gaat de nummering verder met AA, AB,…, AZ, BA,…

Rij

Dit is de aaneengesloten reeks van alle cellen die op een horizontale lijn liggen. De verwijzing naar rijen gebeurt meestal met nummers.

Cel

Het snijpunt van een rij en een kolom noemt men een cel. Het is de kleinste eenheid van een werkblad.

Adres

Elke cel heeft een adres: eerst een kolomverwijzing en vervolgens een rijverwijzing. De cel op het snijpunt van kolom B en rij 3 heeft als adres B3. De cel waarin de cursor staat wordt de actieve cel genoemd.

Bereik (selectie)

Dit is meestal een rechthoek van meerdere cellen die geselecteerd zijn, bijvoorbeeld B6 tot en met C7. Elk rekenbladprogramma heeft zijn eigen notatie om een bereik aan te geven. In Excel is dat bijvoorbeeld B6:C7.

Met bepaalde toetsencombinaties kun je vlug over het werkblad surfen. Sneltoets CTRL+HOME CTRL+END HOME PGUP PGDN CTRL-PGDN CTRL-PGUP

Resultaat naar cel A1 naar meest rechtse cel onderaan die in gebruik is naar de eerste kolom in dezelfde rij één scherm naar boven rollen één scherm naar beneden rollen volgend werkblad vorig werkblad

1.3.2 Werkbladen en werkmappen Een werkmap is een verzameling van werkbladen die als een geheel bewaard wordt. TIP

Met een Excel-map wordt een Excel-document bedoeld dat als 1 bestand weggeschreven wordt. In een Windows-map zitten meestal verschillende documenten.

Afhankelijk van de instellingen van Excel, bestaat elke nieuwe werkmap standaard uit 3 werkbladen. Je kunt echter naar behoefte werkbladen toevoegen of verwijderen. Via de bladtabs kun je van blad tot blad wandelen. Je kunt een werkblad ook een naam geven, standaard krijgen de werkbladen de namen Blad1, Blad2,… Je kunt de naam van een werkblad wijzigen door op de bladtab te dubbelklikken of door een snelmenu met de rechtermuisknop vanaf de bladtab op te roepen.



7

TIP TIP

Ook met de sneltoetsen CTRL+ PGDN en CTRL+PGUP kun je snel van werkblad tot werkblad wandelen. Je krijgt een overzicht van alle werkbladen in een werkmap als je met de rechtermuisknop op de navigatieknoppen naast de bladtabs klikt. Klik je op een bladtab, dan krijg je een contextgevoelig menu. Hierin vind je o.a. de optie INVOEGEN waarmee je een nieuw werkblad aan de huidige werkmap kunt toevoegen.

Een nieuwe werkmap voeg je toe via menu BESTAND, NIEUW. In het taakvenster wordt dan de optie NIEUWE WERKMAP geopend.

1.3.3 De werkbalken Werkbalken bevatten kleine knoppen die snel toegang geven tot bepaalde taken. Deze taken zijn ook beschikbaar via de menubalk. Meestal kun je een dergelijke werkbalk naar eigen smaak inrichten door knoppen toe te voegen of knoppen te verwijderen. Excel start meestal op met twee werkbalken, de STANDAARD werkbalk en de werkbalk OPMAAK. De knoppen van deze werkbalken zijn min of meer universeel. Via de optie BEELD, WERKBALKEN kun je meerdere werkbalken op het scherm krijgen.

8



TIP

Zorg dat de optie SCHERMINFO WEERGEVEN OP WERKBALKEN altijd aangevinkt is. Deze optie zorgt ervoor dat een klein infovenster verschijnt als je de muiswijzer even op een knop in de werkbalk zet.

TIP

Hulp kun je in een dialoogvenster, bijvoorbeeld het dialoogvenster AANPASSEN, altijd krijgen via het vraagteken in de rechterbovenhoek. Sleep bijvoorbeeld het vraagteken naar de tekst GROTE PICTOGRAMMEN en ga na wat het effect is.

Opdracht 1. Knoppen aan de STANDAARD werkbalk toevoegen a. Ga naar BEELD, WERKBALKEN, AANPASSEN. b. Kies de categorie OPMAAK en selecteer de opdracht ONDER UITLIJNEN.

c. Sleep de knop naar de werkbalk OPMAAK. Als je een knop uit een werkbalk wilt verwijderen, maak je het venster AANPASSEN actief en sleep je de te verwijderen knop naar het venster.



9

d. Zoek in de groep OPMAAK naar de knop DUBBEL ONDERSTREPEN en zet hem in de werkbalk OPMAAK. Test de werking van de knop: typ in een willekeurige cel een stukje tekst, druk ENTER, klik opnieuw in de cel en klik op de knop DUBBEL ONDERSTREPEN. TIP

Je verzamelt je eigen knoppen bij voorkeur in een nieuwe werkbalk. Daartoe klik je op de knop NIEUW in het tabblad WERKBALKEN. Standaard krijgt deze werkbalk de naam ‘Aangepast 1’, een naam die je vrij kunt wijzigen.

1.3.4 De formulebalk

In de formulebalk vind je altijd het adres en de inhoud van de huidige cel. Indien de cel een formule bevat dan wordt de formule in de formulebalk en het resultaat in de cel afgebeeld. De inhoud van een cel kun je als volgt wijzigen:  klik in de formulebalk;  gebruik de functietoets F2;  dubbelklik in de cel. 1.3.5 De statusbalk In de statusbalk kun je informatie over de gekozen opdracht aflezen.

1.4

De inhoud van een cel De inhoud van een cel kun je in drie types onderverdelen:  alfanumeriek (tekst);  numeriek (getallen);  formules en functies.

10



Met de knoppen LETTERTYPE, PUNTEN, VET, CURSIEF, ONDERSTREPEN, OPVULKLEUR en TEKSTKLEUR op de werkbalk OPMAAK kun je de celinhoud een mooier uitzicht geven. Eventueel kun je de inhoud van een cel ook nog uitlijnen met de knoppen LINKS UITLIJNEN, RECHTS UITLIJNEN, CENTREREN, UITVULLEN. TIP

Het procentteken [%], het minteken [-], het plusteken [+], het sterretje [*] en de schuine streep [/] worden niet herkend als tekst, maar wel als wiskundig symbool. Wil je dat teken invoeren als tekst, dan moet je het doen voorafgaan door het aanhalingsteken [‘].

Formules zijn vergelijkingen waarmee je nieuwe waarden op basis van bestaande waarden kunt berekenen. Men noemt ze de actieve structuurelementen van het rekenblad, de teksten en de getallen zijn de passieve structuurelementen. Formules beginnen altijd met het gelijkteken [=] en kunnen de volgende elementen bevatten: getallen, adressen, operatoren, haakjes en functies. Bijvoorbeeld: =A6*(SOM(B5:B9)+100) Er zijn 5 operatoren, in volgorde van prioriteit:  machtsverheffing (^) heeft de hoogste prioriteit;  vervolgens vermenigvuldigen (*) en delen (/);  en ten slotte optellen (+) en aftrekken (-). Haakjes zul je gebruiken om af te wijken van de standaardprioriteiten van de operatoren. Functies zijn vooraf gedefinieerde formules die berekeningen uitvoeren met gebruik van specifieke waarden, die argumenten worden genoemd, in een specifieke volgorde, die door de syntaxis van de functie bepaald wordt.

=SOM(C2:C4) =MAX(D5:G10) =NU() =GEMIDDELDE(C2:C4)

de som van het bereik C2 tot en met C4 de hoogste waarde in de rechthoek D5 tot G10 de datum en tijd van vandaag de gemiddelde waarde van een bereik

Datums en tijdsaanduidingen worden ook bij het numerieke type gerekend, omdat die als seriële getallen bewaard worden. In feite wordt de datum voorgesteld door een getal dat aangeeft hoeveel dagen verlopen zijn sinds 1 januari 1900. In het werkblad wordt de datum bewaard als een serieel getal van 1 tot 65 380, dat overeenkomt met een datum tussen 1 januari 1900 en 31 december 2078. De decimale fractie van de datum geeft de tijd weer. Het seriële getal 36 255 komt overeen met het aantal dagen dat vanaf 1 januari 1900 tot 5 april 1999 verlopen is.



11

Opdrachten 2. Open een nieuwe werkmap en noem ze OEFENXP.XLS. Ga naar een leeg werkblad, noem het DATUM. Los in dit werkblad de volgende oefeningen op. a. Bereken het aantal dagen dat je al op de wereld bent. Gebruik de functie NU(). b. Hoe oud is iemand van 18 000 dagen?

1.5

TIP

Via EXTRA, OPTIES, ALGEMEEN kun je bij de optie WERKMAP het aantal lege werkbladen instellen.

WERKBLADEN IN NIEUWE

TIP

Lege werkbladen toevoegen: klik met de rechtermuisknop op de naam van een werkblad. Er verschijnt een snelmenu: kies hierin INVOEGEN. Er verschijnt een dialoogvenster: kies hierin WERKBLAD.

TIP

De naam van een werkblad wijzigen: klik met de rechtermuisknop op de naam van het werkblad en kies NAAM WIJZIGEN.

De hulptroepen De optie HELP, MICROSOFT EXCEL HELP uit de menubalk kan je flink op weg helpen als je iets niet weet:

12



Bijzonder handig is ook de optie INHOUDSOPGAVE in het venster HELP VOOR EXCEL. Hiermee krijg je toegang tot een immense hoeveelheid informatie.

Opdrachten 3. Zoek hoe je namen van bladen kunt wijzigen. 4. Zoek onder de optie WERKEN MET GEGEVENS informatie over gegevens en cellen selecteren.

1.6

Automatische herberekening Standaard herberekent het programma altijd alle formules als je een waarde wijzigt, toevoegt of verwijdert. Als je altijd kleine toepassingen ontwerpt met weinig formules, dan zal die automatische herberekening niet storen. In zeer grote toepassingen, met honderden formules, kan die automatische berekening wel storen. Daarom is er een optie aanwezig om de automatische herberekening af te zetten. Wil je een herberekening uitvoeren, dan moet je het programma een seintje geven. Omdat de snelheid van de huidige processors tientallen malen hoger is dan die van de vroegere processors (8088, 8086, …) zul je in klasverband de automatische herberekening kunnen laten aan staan. Kies hiervoor EXTRA, OPTIES, BEREKENEN.



13

1.7

Getallen opmaken Getallen kunnen onder diverse formaten weergeven worden. De meest voorkomende formaten voor een getal zijn: Naam

Voorbeeld

Betekenis

STANDAARD

35400,47168

geen opmaak

GETAL

35 400,45

de duizendtallen gegroepeerd, het aantal cijfers na de komma is in te stellen

VALUTA

€ 35 400,00

de duizendtallen gegroepeerd, het aantal cijfers na de komma is in te stellen, het valutasymbool wordt automatisch toegevoegd De valutanotatie wordt voor algemene geldzaken gebruikt.

FINANCIEEL

€ 35 400,00

In de financiële notatie worden de decimale tekens en de valutasymbolen in een kolom uitgelijnd.

DATUM

1 mei 1996

Hier zijn meestal verschillende notaties mogelijk.

TIJD

11:19:14

Ook hier zijn verschillende notaties mogelijk.

PERCENTAGE

21 %

0,21 het aantal cijfers na de komma is in te stellen

WETENSCHAPPELIJK 3,54E04

Hier staat in feite 3,54 maal 10 tot de 4de macht, het aantal cijfers na de komma is in te stellen.

TIP

Financiële- en valutanotatie zetten allebei het muntsymbool achter het getal. Bij financiële notatie zullen de decimale komma’s in een kolom netjes onder mekaar staan, ongeacht het aantal decimalen, bij de valutanotatie niet.

TIP

Zelfs al wordt een getal via opmaak als een geheel getal afgebeeld, dan nog wordt het volledige getal met alle cijfers na de komma in het rekenblad bewaard. Er is een duidelijk verschil tussen hoe een getal getoond wordt en hoe een getal bewaard wordt.

TIP

Het valutasymbool ( € of EUR) moet je via Windows instellen, niet via het rekenblad! Kies in Windows CONFIGURATIESCHERM, LANDINSTELLINGEN en verander eventueel de valuta- en getalnotatie.

Om het getalformaat te wijzigen selecteer je eerst het bereik en wijzig je daarna de opmaak via een van de volgende mogelijkheden:  via de knoppen uit de werkbalk OPMAAK;  via OPMAAK, CELEIGENSCHAPPEN, GETAL;  via het snelmenu, CELEIGENSCHAPPEN, GETAL.

14



1.8

Aangepaste notaties Via de categorie AANGEPAST kun jij je eigen aangepaste indelingen voor getallen en datums maken door gebruik te maken van bepaalde symbolen. Tekst die je letterlijk wilt afdrukken zet je tussen aanhalingstekens. TIP

Zelfs al wordt een getal via opmaak als een geheel getal afgebeeld, dan nog wordt het volledige getal met alle cijfers na de komma in het rekenblad bewaard. Er is een duidelijk verschil tussen de manier waarop een getal getoond wordt en de wijze waarop een getal bewaard wordt.

Een kg-opmaak kun je op die manier als volgt instellen: # ##0,00” kg”.

TIP

Je kunt Excel zo instellen dat de scheidingstekens van het systeem (Windows XP: CONFIGURATIESCHERM, LANDINSTELLINGEN) gebruikt worden of dat je het decimaalteken en scheidingsteken voor duizendtallen zelf intikt.



15

Codes voor getalopmaak Symbool

Beschrijving

, (komma)

decimaal scheidingsteken

(spatie)

scheidingsteken voor duizendtallen

0

Tijdelijke cijferaanduiding, geeft een cijfer of 0 weer.

#

Tijdelijke cijferaanduiding, geeft een cijfer of niets weer.

“liter”

Geeft de letters ‘liter’ weer, in combinatie met bijv. # ##0,00” liter”.

%

Percentage, de waarde wordt vermenigvuldigd met 100 en er wordt een procentteken toegevoegd.

Codes voor datumopmaak Symbolen

Voorbeeld

Betekenis

jj jjjj

06 2006

jaartal met twee cijfers jaartal met vier cijfers

m mm mmm mmmm

5, 10 05, 10 okt oktober

maand met één of twee cijfers maand met twee cijfers eerste drie letters van de maand de maand voluit

d dd ddd dddd

6, 23 06, 23 di dinsdag

de dag met één of twee cijfers de dag met twee cijfers de eerste twee letters van de dag de dag voluit geschreven

jjjj-mm-dd d mmmm jjjj TIP

2006-04-07 7 april 2006

Hoe zou je een datum als volgt afdrukken: ‘Vandaag, zaterdag 28 december 2002’?

Opdrachten 5. Getallen met behulp van een aangepaste notatie opmaken Open de werkmap OEFENXP en ga naar een leeg werkblad.

16



De inhoud van kolom B en C is identiek. De formule in kolom B wordt getoond omdat ze als tekst ingebracht is: begin met een aanhalingsteken en vervolg dan met de formule: ‘=NU(). Tracht in kolom C dezelfde weergave te bekomen als in de figuur. Wijzig daarom voor elke cel de getalnotatie via CELEIGENSCHAPPEN, GETAL, AANGEPAST. Noem dit blad AGTNOTATIE en bewaar de werkmap.

6. Bereken hoe oud je bent, enerzijds in dagen en anderzijds in jaren. a. Zet in cel B3 je geboortedatum. In cel C4 zet je de datum van vandaag, gebruik hiervoor een functie. b. De inhoud van de cellen B5:B7 is dezelfde als B4, maar de opmaak verschilt. Idem voor C5:C7 en C4. c. Omdat een datum een serieel getal is kun je hiermee het aantal dagen berekenen. d. Om het aantal jaren te berekenen maak je gebruik van de functie Jaar() die het jaartal uit een datum kan halen. e. In B10 wordt het jaartal via opmaak aan de celinhoud toegevoegd. Op die manier kun je nog rekenen met dit getal. f. Noem dit werkblad LEEFTIJD.

1.9

Werkmappen in een werkruimtebestand opslaan Je kunt een groep werkmappen tegelijk openen door deze op te slaan in een werkruimtebestand. In een dergelijk bestand wordt informatie opgeslagen over alle geopende werkmappen. Dit omvat de locatie, het formaat van de vensters en de posities op het scherm. Wanneer je een werkruimtebestand opent met de opdracht BESTAND, OPENEN worden automatisch alle bestanden geopend die in de werkruimte opgeslagen zijn. Het werkruimtebestand bevat niet de werkmappen zelf. Je moet wijzigingen die je in de afzonderlijke werkmappen maakt dan ook gewoon blijven opslaan. Een werkruimte sla je op via BESTAND, WERKRUIMTE OPSLAAN.



17

Besluit Een rekenblad bestaat uit een groot aantal rijen en kolommen. Het snijpunt van een rij en een kolom noemt men een cel. Elke cel heeft een adres. Het adres bestaat uit een kolomverwijzing gevolgd door een rijverwijzing. Een rechthoek met verticaal en/of horizontaal geselecteerde cellen is een bereik. Excel werkt met mappen. Een map bestaat uit een aantal werkbladen en wordt altijd in een venster geopend. In een dergelijk venster zie je de titelbalk, de menubalk, de werkbalken, de formulebalk, de statusbalk en ten slotte het werkblad. Een cel kan een alfanumerieke waarde, een numerieke waarde of een formule bevatten. Met behulp van speciale codes maakt het programma een onderscheid tussen die types. Getallen kunnen onder verschillende vormen weergegeven worden: valuta, met of zonder decimale cijfers, als een datum… Het getal wordt nochtans altijd in zijn oorspronkelijke vorm in het rekenblad bewaard. Ook datums worden als getallen behandeld en worden als seriële getallen bewaard. Dank zij de automatische herberekening worden in het rekenblad bij elke wijziging alle formules herberekend. Via de opmaakknoppen op de werkbalk kun je de presentatie van de inhoud van een cel verfraaien.

Wat je moet kennen en kunnen:       

Excel opstarten met een nieuw werkblad / nieuwe werkmap; de betekenis van de verschillende elementen van een rekenblad opsommen en verklaren; verschillende types van celinhouden opsommen en met een voorbeeld illustreren; eenvoudige formules samenstellen en functies gebruiken waar het mogelijk is; het verschil tussen automatische en handmatige herberekening uitleggen; een getal een gepaste opmaak geven; de inhoud van een cel verfraaien met de knoppen uit de werkbalk.

Opdrachten 7. Wat is de betekenis van de volgende knoppen ?

8. Zoek het aantal rijen en kolommen in je werkblad. 9. Welke soorten celinhoud kun je opnoemen? 10. Geef vier getalformaten en leg uit in welke omstandigheden je ze gebruikt. 18



11. Zijn de volgende gegevens numeriek of alfanumeriek? Inkomsten 22,5 % 789-4563456-36 5 sept 1996 € 12 345 ‘440330 12. Een datum wordt als een serieel getal bewaard. Wat bedoelt men hiermee? Welke datum krijg je met het seriële getal 412? 13. Als je in een procentnotatie het getal 0,25 % ziet staan, welke waarde wordt dan in het werkblad opgeslagen? 14. Indien de valutanotatie 324 BF aangeeft in plaats van € 324, wat is daar de oorzaak van? 15. Hoe de inhoud van een cel op het scherm verschijnt is niet noodzakelijk hetzelfde als de wijze waarop die inhoud in het rekenblad bewaard wordt. Verklaar met een voorbeeld. 16. Verklaar de betekenis van ‘prioriteit’ bij wiskundige operatoren aan de hand van een voorbeeld. 17. Verkoopcijfers “Jos Computers”

a. Open de werkmap OEFENXP en ga naar een leeg werkblad. Tracht het voorbeeld na te maken. In de kolom D en E moet je de nodige formules zetten. b. Gebruik de knop AUTOSOM om het algemene totaal te bepalen in D13. c. Let op de uitlijning en de getalopmaak. d. Noem dit werkblad OMZET en bewaar de map.



19

Hoofdstuk 2

2.1

Basistechnieken

Probleemstelling In elk programma moet je een aantal basistechnieken beheersen om er vlot mee te kunnen werken. Sommige van die basistechnieken kun je ook in tekstverwerking en gegevensbeheer terugvinden, andere zijn eigen aan een rekenblad. Deze basistechnieken zijn niet gebonden aan één bepaald type van rekenbladprogramma, ze zijn nagenoeg universeel bruikbaar.

2.2

Cellen selecteren Voordat je op een cel een opdracht uit kunt voeren moet je die cel selecteren. Dat doe je door even met de muiswijzer in die cel te klikken. Je kunt dezelfde opdracht ineens op meerdere cellen uitvoeren als je al die cellen selecteert. Opdrachten in verband met kopiëren, verplaatsen, opmaken, verwijderen, beveiligen worden meestal op een selectie van meerdere cellen toegepast. Cellen kun je op verschillende manieren in een werkblad selecteren onder andere zoals in deze figuur:

 







20

het hele werkblad: - met de sneltoets CTRL+A; - klik op de knop ALLES SELECTEREN. de hele rij: - klik op het rijnummer; - je selecteert meerdere rijen door de muiswijzer over de verschillende rijnummers te slepen. de hele kolom: - klik op de kolomletter; - je selecteert meerdere kolommen door de muiswijzer over de verschillende kolomletters te slepen. een aaneengesloten rechthoek van cellen: - klik op de eerste cel, zet de muiswijzer in de laatste cel en klik met SHIFT+linkermuisknop om het hele gebied te selecteren; - sleep de muiswijzer diagonaal van de eerste naar de laatste cel. een aantal niet aan mekaar grenzende cellen - klik op de eerste cel en selecteer vervolgens de andere cellen met CTRL+linkermuisknop; - Op dezelfde wijze kun je meerdere afzonderlijke kolommen en/of rijen selecteren.



TIP

Met de TAB-toets kun je de celaanwijzer in een selectie verplaatsen.

TIP

Met een eenvoudige klik buiten een selectie kun je de selectie ongedaan maken.

Opdracht 1. Test in een leeg werkblad de verschillende selectiemogelijkheden even uit. Eventueel kun je het geselecteerde gebied met de knop OPVULKLEUR een andere kleur geven.

2.3

telkens

Kopiëren, knippen en plakken Kopiëren kopieert de geselecteerde cellen naar het klembord, knippen verplaatst ze naar het klembord. Plakken plakt de inhoud van het klembord op de plaats van de geselecteerde cel. Kopiëren, knippen en plakken verloopt meestal in vier stappen:  eerst de cellen selecteren;  vervolgens het programma een seintje geven om die cellen naar het klembord te kopiëren of te verplaatsen;  dan de celaanwijzer op de nieuwe locatie zetten, dat kan eventueel een ander werkblad of een andere map zijn;  en ten slotte aan het programma een seintje geven dat er mag geplakt worden. Je kunt op verschillende manieren kopiëren, knippen en plakken. De meeste daarvan ken je al vanuit tekstverwerking. Eerst en vooral selecteer je de cel(len) die je wilt kopiëren en vervolgens kies je een van de verschillende kopieermogelijkheden. menubalk

werkbalk

muis

sneltoets

snelmenu

vulgreep


BEWERKEN, KOPIËREN of KNIPPEN, nieuwe locatie kiezen, BEWERKEN, PLAKKEN. knop KOPIËREN of KNIPPEN, nieuwe locatie kiezen, knop PLAKKEN. zet de muiswijzer onder de selectie, de muiswijzer verandert in een pijl, sleep de selectie met CTRL+linkermuisknop naar de nieuwe locatie om te kopiëren of gewoon met de linkermuisknop om te plakken. CTRL+C of CTRL+X nieuwe locatie kiezen, CTRL+V. blijf met de muiswijzer in de selectie staan, roep het snelmenu op met de rechtermuiskop, kies KOPIËREN of KNIPPEN, nieuwe locatie kiezen, kies PLAKKEN. Met de vulgreep kun je slechts naar naburige cellen kopieren. Sleep de vulgreep met de linkermuisknop over de naburige cellen.


21

TIP

Als je één cel naar meerdere cellen wilt kopiëren, selecteer dan voor het plakken de cellen waar je overal een kopie wilt zetten.

TIP

Je kunt de bovenstaande methodes onderling mengen: je kopieert bijvoorbeeld met een sneltoets CTRL+C en je plakt met de knop PLAKKEN.

TIP

Wil je twee kolommen van plaats omwisselen, dan moet je eerst een lege kolom toevoegen en vervolgens de tweede kolom hierop plaatsen.

Tijdens het kopiëren en verplaatsen door slepen verandert de muiswijzer van uitzicht. Ook de vulgreep heeft haar eigen symbool. De vulgreep verslepen is meestal de snelste manier om de celinhoud naar naburige cellen te kopiëren.  Beweeg de muiswijzer naar de rechterbenedenhoek van de te kopiëren cel.  De muiswijzer verandert in een klein kruis.  Sleep dat kruis met de linkermuisknop naar beneden of naar rechts.  Laat de muisknop los.

Een heel werkblad kopieer je best door de bladtab met CTRL+linkermuisknop te slepen van de ene map naar de andere. Zorg dat allebei de mappen op het scherm zichtbaar zijn via de optie VENSTER, ALLE VENSTERS. Hierin kies je best de optie ALLE VENSTERS NAAST ELKAAR.

Door de bladtab met de linkermuisknop te slepen kun je een werkblad verplaatsen van de ene plaats naar de andere. Het is ook mogelijke om meerdere werkbladen als groep te verplaatsen. In dat geval selecteer je de gewenste werkbladen met een ingedrukte CTRL-toets.

22



Hieronder wordt getoond hoe je de bladtabs onderling van plaats verwisselt. Op die manier kun je de bladtabs alfabetisch rangschikken.

2.4

Plakken speciaal In feite worden er tijdens het kopiëren verschillende onderdelen samen gekopieerd. Een cel heeft namelijk een inhoud, dat kan een waarde of een formule zijn, en een opmaak. Het is mogelijk om een van die elementen afzonderlijk te kopiëren. Het is zelfs mogelijk om niet de formule te plakken, maar wel het resultaat van die formule! Nadat het te kopiëren gebied geselecteerd is krijg je toegang tot deze optie via:  BEWERKEN, PLAKKEN SPECIAAL;  het snelmenu en daarin de optie PLAKKEN SPECIAAL;  wens je alleen maar de opmaak te kopiëren, dan kun je ook gebruik maken van de knop OPMAAK KOPIËREN.

Hoe werkt de knop OPMAAK KOPIËREN?  Selecteer het gebied waarvan je de opmaak wilt kopiëren.  Klik op de knop OPMAAK KOPIËREN.  Selecteer de cellen die een bepaalde opmaak moeten krijgen. TIP

Transponeren betekent dat de rijen met de kolommen verwisseld worden.

TIP

Als je de opmaak naar niet aaneengrenzende cellen wilt kopiëren, dan moet je die cellen met CRTL+linkermuisknop selecteren.



23

TIP

De opmaak kun je wissen via BEWERKEN, WISSEN, OPMAAK. Wissen met de DEL-toets verwijdert alleen de inhoud, niet de opmaak.

Opdracht 2. Bestelbonnen kopiëren Maak deze bestelbon na. a. Zet de juiste formule in D4 en kopieer die naar de cellen D5:D10. Zet de formule voor het eindtotaal in D12. b. Selecteer vervolgens alle cellen die in het vet moeten komen en zet die in het vet. c. Selecteer het gebied A1:D12 en kopieer het naar A14. Zijn de formules en de opmaak ook mee gekopieerd? d. Selecteer opnieuw het gebied A1:D12 en kopieer het zodanig dat de opmaak en niet de formules, maar wel het resultaat van de formules gekopieerd worden naar cel F1. Is dit in één stap mogelijk? e. Geef dit blad de naam BESTBON.

2.5

Breedte van kolommen en hoogte van rijen Meestal is de standaardbreedte van een kolom niet voldoende om de volledige inhoud van een cel te tonen, zeker niet bij tekst. De hoogte van de rijen is meestal voldoende, zolang je geen groter lettertype gebruikt. Er zijn drie manieren om de hoogte of breedte te wijzigen:  via OPMAAK, RIJ of KOLOM;  via het snelmenu als de muiswijzer op de rij- of kolomkop staat;  via slepen of dubbelklikken met de linkermuisknop.

Alleen de eerste twee methodes kun je toepassen op een selectie van rijen en kolommen.

24



Het is ook mogelijk de breedte en de hoogte door de inhoud van de cel te laten bepalen. Dan kies je meestal de optie AUTOAANPASSEN AAN SELECTIE of je dubbelklikt op de scheidingslijn tussen de kolom- of rijkoppen. Met behulp van de muisknoppen kun je vrij eenvoudig de breedte van een kolom en de hoogte van een rij wijzigen. Deze methode kun je maar op één kolom tegelijk toepassen.

TIP

Als je meerdere kolommen selecteert, en je wijzigt de breedte van één van de geselecteerde kolommen, dan zullen ook de andere geselecteerde kolommen even breed worden.

Het volgende voorbeeld toont wat er gebeurt als de celinhoud niet in de cel past.

 Cel A1: Als een tekst langer is dan de breedte van de cel, dan loopt die tekst gewoon door in de volgende cel als die cel leeg is.  Cel A5: Als de cel ernaast niet leeg is, dan wordt de tekst op het scherm afgebroken, maar hij wordt wel volledig in het rekenblad bewaard.  Cel B2: Is een getal langer is dan de breedte van de cel, dan verschijnen in de cel speciale tekens, afhankelijk van het programma. Bij Excel is dat het spoorwegteken [#] . Opdracht 3. Eigenaardigheden bij het kopiëren Benodigde formules:  SOM()  GEMIDDELDE()  MAX()  MIN()  AANTAL()



25

De functie AANTAL() telt het aantal niet lege cellen van een gebied. De functie GEMIDDELDE() berekent het gemiddelde van de niet lege cellen met een numerieke waarde van een gebied. a. Neem het voorbeeld over en vul in B11, B12, B13 en B14 de gepaste formules in. Kopieer deze formules in eenmaal naar het gebied C11:E14. Vul ook de juiste formule in cel G5 in en kopieer deze formule in eenmaal naar het gebied G6:G9. De uitkomsten van de gemiddelden moeten 1 cijfer na de komma tonen. Selecteer de kolommen B, C, D, E, en F en zet daarvan de kolombreedte op 4. De breedte van kolom G is 6. De hoogte van rij 10 is 6. Dubbelklik op de scheidingslijn tussen de kolomkop A en B om de breedte van de eerste kolom passend te maken. Zet in eenmaal de gewenste cellen in het vet. b. Geef dit werkblad de naam TOETS. Kopieer het gebied A1:G14 met behulp van het snelmenu naar cel A1 van een werkblad in een nieuwe werkmap. Wat gebeurt er met de kolombreedtes? Zou er een mogelijkheid zijn om met PLAKKEN SPECIAAL ook de kolombreedtes mee te kopiëren? c. Selecteer het hele werkblad TOETS met de sneltoets CTRL+A. Ga naar een leeg werkblad in de nieuwe werkmap en plak vervolgens de inhoud van het klembord in cel A1. Wat gebeurt er nu met de kolombreedtes? d. Kopieer het werkblad TOETS naar de nieuwe werkmap door middel van slepen met CTRL+linkermuisknop. Is het resultaat bevredigend? Sluit de nieuwe werkmap af zonder ze te bewaren.

2.6

Wissen Er is duidelijk een onderscheid tussen een cel wissen en een cel uit het werkblad verwijderen. Bij wissen wordt meestal een onderscheid gemaakt tussen:  de opmaak wissen; Dit geeft terug de standaard opmaak.  de celinhoud wissen; De opmaak blijft behouden, maar de cel is leeg.  alles wissen: opmaak en inhoud. Het resultaat is een lege cel met standaardopmaak. Wil je cellen wissen, dan moet je die cellen eerst selecteren. Er zijn een drietal mogelijkheden om te wissen:  de DEL-toets: de meest eenvoudige methode als je alleen maar de inhoud wilt wissen;  het snelmenu: de optie WISSEN (niet VERWIJDEREN!) wist ook alleen maar de inhoud;  de menubalk BEWERKEN, WISSEN: met deze optie kun je kiezen tussen ALLES, OPMAAK, INHOUD en OPMERKINGEN.

26



Opdracht 4. Cellen wissen a. Neem de gegevens van A1:B6 volledig over in een leeg werkblad. Cel B6 bevat een formule! Zet de waarden in kolom A in het vet. Geef B3, B4 en B6 de gepaste opmaak. b. Kopieer dit gebied naar D1, inhoud zowel als opmaak. c. Verwijder de opmaak van D1:E6. Verklaar het resultaat. d. Kopieer de opmaak van A1:B6 naar D1. Welke methode heb je gebruikt en voldoet het resultaat? e. Verwijder de inhoud, niet de opmaak, van D1:E6 en typ de gegevens van A1:B6 terug over in het gebied D1:E6. f. Wijzig de naam van dit blad in WISSEN.

2.7

Reeksen doorvoeren

2.7.1 Reeksen met de vulgreep doorvoeren

Soms zal het kopiëren met de vulgreep schijnbaar niet correct werken. Dit gebeurt als de te kopiëren cel een onderdeel is van een gekende reeks, bijvoorbeeld een datum, een dag van de week, een maand, een zelf gedefinieerde reeks … In dat geval geeft de vulgreep de volgende elementen van de reeks. Welke reeksen (van woorden) gekend zijn kun je aflezen via EXTRA, OPTIES, AANGEPASTE LIJST. Standaard zie je alleen maar de dagen van de week en de maanden van het jaar. Wil je een nieuwe lijst toevoegen, typ die dan gewoon in het venster GEGEVENS IN LIJST in en klik vervolgens op de knop TOEVOEGEN.



27

Opdracht 5. Een reeks ontwerpen met de woorden ‘een’ tot en met ‘tien’ a. Open een nieuwe werkmap en ga naar EXTRA, OPTIES, AANGEPASTE LIJST. b. Typ in het venster GEGEVENS IN LIJST de woorden ‘een’, ‘twee’, …, ‘tien’. De woorden mogen onder mekaar of achter mekaar geschreven worden. Achter mekaar moeten ze door een komma gescheiden worden. c. Klik op de knop TOEVOEGEN. Test de reeks in een leeg werkblad. In cel A1 zet je ‘drie’, met de vulgreep vul je de cellen tot en met A15 op. Wat merk je? d. Selecteer opnieuw cel A1 en sleep de vulgreep tot cel L1. Wat merk je? (Deze werkmap hoef je niet te bewaren.) TIP

De lijst kan ook rechtstreeks uit het werkblad geïmporteerd worden via het dialoogvenster LIJST IMPORTEREN UIT CELLEN. In dat venster geef je het cellenbereik van de lijst aan.

2.7.2 Via een dialoogvenster reeksen doorvoeren Reeksen doorvoeren kun je niet alleen met de vulgreep. Het is ook mogelijk bepaalde reeksen, zoals de dagen van de week, de maanden van het jaar, een reeks van getallen met hetzelfde interval, … automatisch op het scherm te krijgen via BEWERKEN, DOORVOEREN, REEKS.

28



Opdracht 6. Reeksen doorvoeren

a. Open een leeg werkblad en typ de drie eerste rijen over. Neem ook de waarden van A4 tot en met D4 over. Kopieer de inhoud van D4 naar E4, F4 en G4. Welke kopieermethode heb je gebruikt? b. Het is de bedoeling dat je de reeksen in de kolommen A tot en met G automatisch ontwikkelt. Selecteer cel A4 en breid de selectie uit tot A10. Kies het juiste onderdeel van de optie BEWERKEN, DOORVOEREN, REEKS uit de menubalk. Klik op LINEAIR of GETAL, neem als interval [1] en klik op OK. Welk interval neem je in kolom B? Welk interval neem je in kolom C? c. Tracht ook kolom C, D, E, F en G in te vullen. Merk je het verschil tussen kolom D en E? d. Bewaar het werkblad onder de naam REEKS.

2.8

Relatieve en absolute adressering Het verschil tussen een relatieve en een absolute adressering kun je vergelijken met de weg aan iemand wijzen. Je staat in een krantenwinkel in Wuustwezel en vraagt daar de weg naar bakkerij ‘t Kramiekske. Je kunt dan twee soorten van antwoorden krijgen:  deze straat ten einde, dan linksaf, en de tweede straat rechts het vijfde huis; Dit is een relatief adres. Het geeft een routebeschrijving aan. Sta je toevallig in een andere straat, dan verwijst deze uitspraak naar een ander huis en is deze routebeschrijving totaal fout.  in de Korenstraat 7 te Wuustwezel. Dit is een absoluut adres. Waar je ook staat, het verwijst altijd naar hetzelfde punt.



29

2.8.1 Relatieve adressering De celverwijzing [A2] in cel B5 is een relatief adres. In tegenstelling tot wat je zou denken, verwijst een relatief adres niet naar een vaste positie in het werkblad. Ze geeft eerder de afstand aan, in de zin van drie rijen hoger en één kolom naar links. Als je die uitspraak kopieert naar de cel D4, dan krijg je daar een verwijzing naar cel C1. Als je de inhoud van cel A2 verplaatst naar cel D5 dan verandert de verwijzing in cel B5 van [=A2] naar [=D5]. M.a.w. de verwijzing in cel B5 blijft de inhoud van cel A2 altijd volgen bij een verplaatsing. 2.8.2 Absolute adressering De celverwijzing [$A$2] in de cel B5 is een absoluut adres. Ze verwijst naar een vaste positie in het werkblad. Kopieer je deze verwijzing naar cel D4, dan blijft de verwijzing altijd wijzen naar cel A2. Als je de inhoud van cel A2 verplaatst naar cel D5 dan verandert de verwijzing in cel B5 van [=$A$2] naar [=$D$5]. Op dit vlak is er geen onderscheid tussen relatief en absoluut. 2.8.3 Gemengde adressering Een gemengde adressering bevat zowel een relatieve als een absolute verwijzing. $A2 A$2

De kolom is absoluut, verandert niet bij kopiëren, de rij is relatief, ze verwijst naar een afstand. De kolom is relatief en verwijst naar een afstand, de rij is absoluut en verandert niet bij kopiëren.

TIP Als je een adres in een formule invoert, dan kun je met behulp van de functietoets F4 de dollartekens [$] toevoegen aan dat adres.

30



Opdracht 7. Wisselkoersen Maak een kaartje waarop je snel de omzetting van EUR naar USD, CAD, NZD, AUD en GBP kunt zien. a. Neem de gegevens van de rijen 1 tot en met 6 letterlijk over. In kolom A begin je vanaf A7 met 5 en je vervolledigt de reeks tot 100 met een verhoging van 5. b. De kolommen hebben een breedte van 6 en rij 2 en 5 hebben een hoogte van 6. c. Zet in B7 de juiste formule en kopieer ze met de vulgreep naar beneden en naar rechts. Noteer de formule. d. Rond alle getallen af tot op twee cijfers na de komma. e. Bewaar ten slotte het werkblad onder de naam KOERS.

2.9

Verwijderen en invoegen Wat kun je zoal verwijderen en invoegen?  cellen of een selectie van cellen;  rijen en kolommen;  werkbladen. TIP

Er zijn nog andere zaken die je kunt invoegen. Het menu INVOEGEN geeft een overzicht van de opties. Een aantal van die opties worden later nog besproken.

Je kunt invoegen en verwijderen via verschillende mogelijkheden:  het menu INVOEGEN om zowel cellen, rijen, kolommen als werkbladen in te voegen; J. Gils – E. Goossens


31

    

het menu BEWERKEN, VERWIJDEREN om zowel cellen, rijen als kolommen te verwijderen; het menu BEWERKEN, BLAD VERWIJDEREN om een werkblad te verwijderen; het snelmenu vanuit het werkblad om zowel cellen, rijen als kolommen in te voegen of te verwijderen; het snelmenu vanuit de kolom- of rijkoppen om kolommen en rijen in te voegen of te verwijderen; het snelmenu vanuit de bladtabs om werkbladen toe te voegen of te verwijderen.

Opdracht 8. Kolommen en rijen verwijderen

a. Voeg een nieuw werkblad toe aan de map OEFENXP en los het voorbeeld in een leeg werkblad op. Noem dit werkblad PUNTEN. Zorg dat de kolommen B, D en F even breed zijn. b. Voeg nogmaals een leeg werkblad toe en noem het DUBBEL. c. Kopieer de oefening naar dit werkblad DUBBEL. Zorg ervoor dat de breedte van de kolommen en rijen niet gewijzigd is. Welke kopieermethode heb je gebruikt? d. Verwijder in één enkele actie de kolommen B, D en F en verwijder in de volgende actie de rijen 4 en 9. Is er iets gewijzigd met de uitkomsten van de formules? e. Gebruik het snelmenu om een rij in te voegen voor rij 6. Voeg hier het vak ‘Dactylo’ toe, het maximum van de punten is 20 en de leerling behaalde 17. Zorg dat de uitkomsten van de formules zich aanpassen. f. Voeg opnieuw lege kolommen en rijen toe zodat je opnieuw afscheiding krijgt zoals in het werkblad PUNTEN. g. Hernoem het blad PUNTEN2 en verwijder het vervolgens.

2.10 Kolommen of rijen verbergen De winkelier die de aankoopprijzen en de verkoopprijzen van zijn artikelen in een rekenblad verwerkt, wil niet dat de klant inzage krijgt in de kolommen inkoopprijs en winstmarge. Hij kan die kolommen verbergen. Verborgen kolommen worden niet afgedrukt op het scherm of via de printer. Verbergen is niet hetzelfde als verwijderen!

32



Een andere toepassing van verborgen kolommen is het gebruik van cellen waarin tijdelijke resultaten bewaard worden die best niet zichtbaar zijn voor de gebruiker en die ook niet afgedrukt mogen worden. Een toepassing daarvan zal besproken worden bij het ontwerpen van een factuur.

Eerst en vooral selecteer je de kolommen of rijen die je wilt verbergen. Vervolgens roep je de verbergactie op via:  het snelmenu, de optie VERBERGEN;  de menuoptie OPMAAK, KOLOM of RIJ, VERBERGEN. In feite volstaat het hier de gewenste cellen te selecteren en vervolgens de juiste optie KOLOM of RIJ te kiezen. Vergis je dan ook niet! Er is nog een derde methode, namelijk de rijhoogte of de kolombreedte op 0 zetten. Dit kan door slepen met de linkermuisknop, met het snelmenu of via de optie OPMAAK. TIP

Als je een hele rij selecteert en je kiest daarna voor OPMAAK, KOLOM, VERBERGEN, dan wordt het hele werkblad verborgen. Is dat logisch?

Verborgen kolommen kun je op drie manieren zichtbaar maken:  Selecteer de kolommen/rijen aan weerszijden van de verborgen kolommen/rijen en kies:  het snelmenu, de optie ZICHTBAAR MAKEN;  de menuoptie OPMAAK, KOLOM of RIJ, ZICHTBAAR MAKEN.  Sleep met de linkermuisknop de kolommen of rijen terug te voorschijn.



33

Opdracht 9. Vertaaloefening Het is de bedoeling dat je in kolom B de vertaling zet van het overeenkomstige woord uit kolom A. Als de vertaling juist is moet in kolom C het woordje ‘Goed’ verschijnen, in het andere geval het woordje ‘Fout’. Kolom E is verborgen.

a. Open een nieuw werkblad en neem de inhoud van kolom A en E volledig over. b. Vul ook B5 en C5 in en pas de opmaak aan. c. In cel C6 komt een formule die test of het woord dat je intikt in cel B6 identiek is aan het woord dat in cel E6 zit. Vervolgens kopieer je die formule naar C7:C12. Deze test gebeurt met de ALS()-functie. =ALS(voorwaarde ; wat als de voorwaarde waar is ; wat als de voorwaarde fout is) d. =ALS(B6=E6 ; “Goed” ; “Fout”) Als de inhoud van B6 gelijk is aan die van E6, dan komt in C6 de boodschap ‘Goed’, anders de boodschap ‘Fout’. Nu is het zo dat wanneer je in B6 nog niets ingetikt hebt, B6 verschillend zal zijn van E6 en dan zul je de boodschap ‘Fout’ krijgen. Dat is uiteraard niet de bedoeling. Daarom moet je een ‘geneste’ ALS() gebruiken: =ALS(B6=”” ; “” ; ALS(B6=E6;”Goed”;”Fout”) ) Als in B6 niets staat (=””), zet dan in C6 ook niets. Kijk in het andere geval of de inhoud van B6 gelijk is aan E6 en zet in dat geval in C6 de boodschap ‘Goed’, anders de boodschap ‘Fout’. e. Verberg de kolom E zodat de antwoorden niet meer zichtbaar zijn. f. Noem het blad ten slotte VERTAAL. ALS(logische-test;waarde-als-waar;waarde-als-onwaar) Met ALS kun je conditionele tests uitvoeren op waarden en formules. Deze functie geeft een bepaalde waarde als een voorwaarde die je opgegeven hebt resulteert in WAAR en een andere waarde als die voorwaarde resulteert in ONWAAR.  logische-test: is een waarde of expressie die WAAR of ONWAAR kan zijn.  waarde-als-waar: is de waarde die wordt verkregen als logische-test WAAR is. Als logische-test WAAR is en waarde-als-waar weggelaten is, dan gebeurt er niets. Waarde-als-waar kan een andere formule zijn.  waarde-als-onwaar: is de waarde die wordt verkregen als logische-test ONWAAR is. Als de logische-test ONWAAR is en waarde-als-onwaar is weggelaten, dan gebeurt er niets. Waarde-als-onwaar kan een andere formule zijn.

34



10. Uitbreiding Vertaaloefening De gebruiker krijgt de mogelijkheid om zijn naam in te tikken, bijvoorbeeld Sofie. Als het antwoord goed is verschijnt er ‘Goed Sofie!’ in het andere geval ‘Fout Sofie!’. Tevens verschijnt de score onderaan de oefening.

a. De tekst ‘Goed Sofie!’ plaatsen Wijzig de printopdracht in de formule van cel C6 als volgt: "Goed "& $B$3&"! ". Daardoor verschijnt de tekst ‘Goed ‘, dan de inhoud van B3 en vervolgens de tekst "! ". Doe hetzelfde met de foutboodschap. Kopieer deze cel naar het hele bereik C7:C12. b. De score berekenen Zet in cel F6 de volgende voorwaarde: Als in B5 niets staat, doe dan niets. In het andere geval: als het ingevoerde woord in B6 gelijk is aan de oplossing in E6, zet dan het cijfer 1 in F6, in het andere geval het cijfer 0. Kopieer deze formule naar het hele bereik F7:F12. c. Bereken het aantal punten in F14 en het aantal oefeningen in F15. De functie SOM() telt alle getalwaarden in een bereik op, de functie AANTAL() het aantal niet-lege cellen in een bereik. d. De score afdrukken Als het aantal toetsen groter is dan nul, druk dan de score af. e. De eindbeoordeling afdrukken Als het aantal toetsen groter is dan nul, druk dan de beoordeling af. Als minstens de helft of meer van de oefeningen juist zijn, druk ‘Geslaagd!’, in het andere geval ‘Onvoldoende’. f. Verberg de kolommen E en F. g. Vul hieronder de volgende formules in: C6 formule ‘Goed Sofie!’ B14 formule score B15 formule beoordeling



35

2.11 Het werkblad beveiligen Dikwijls is het nuttig dat de gebruiker de inhoud van bepaalde cellen niet kan wijzigen. Dit geldt ZEKER voor alle cellen die formules bevatten. Immers, als je iets in een cel waarin een formule zit intikt, dan ben je die formule kwijt! Neem als voorbeeld het werkblad VERTAAL. De gebruiker mag alleen maar in de cellen B4 tot en met B10 een waarde intikken; van de andere cellen moet hij afblijven! Beveiligen gebeurt in twee stappen:  eerst moet je via OPMAAK, CELEIGENSCHAPPEN, BESCHERMING aanduiden welke cellen je blokkeren wilt, m.a.w. welke cellen in aanmerking komen om te beveiligen; Als er slechts een beperkt aantal cellen niet beveiligd mogen zijn, selecteer je best het hele werkblad, zet voor alle cellen de blokkering aan en selecteer vervolgens door middel van CTRL+linkermuisknop de cellen die niet geblokkeerd mogen worden en zet hiervan de blokkering af.



vervolgens moet je de beveiliging activeren via EXTRA, BEVEILIGING, BLAD BEVEILIGEN.

TIP

36

Gebruik in de klas liefst geen wachtwoord om de cellen te beveiligen want dan kan de leerkracht geen wijzigingen meer aanbrengen in je werkblad!



Opdracht 11. Beveilig het werkblad VERTAAL zodanig dat alleen de cellen B4:B10 gewijzigd kunnen worden.

2.12 Wachtwoordbeveiliging in Excel In Excel kun je een wachtwoord instellen voor het bewerken en opslaan van een werkmap. Alleen wie het wachtwoord kent krijgt dan toegang tot de werkmap. Opdracht 12. Een werkmap met een wachtwoord beveiligen a. Open een nieuwe werkmap, kopieer hierin het werkblad VERTAAL uit de werkmap OEFENXP. b. Kies BESTAND, OPSLAAN ALS, EXTRA, ALGEMENE OPTIES. Je kunt twee wachtwoorden ingeven: één om het bestand te kunnen openen en één om schrijfbevoegdheid toe te staan.

Typ het wachtwoord nogmaals en klik op OK. Bij wachtwoorden wordt onderscheid gemaakt tussen kleine letters en hoofdletters. Typ het wachtwoord precies zoals de gebruikers dit moeten invoeren, met inbegrip van hoofdletters en kleine letters.

c. Klik ten slotte op OPSLAAN om de map te bewaren: noem ze VEILIG.XLS. Klik indien nodig op JA om de bestaande werkmap te vervangen door de geopende werkmap. d. Sluit de map en test de werking van de wachtwoorden door de map opnieuw te openen. Eerst wordt het wachtwoord gevraagd om het bestand te kunnen openen.



37

Vervolgens wordt het wachtwoord voor schrijfbevoegdheid gevraagd. Klik op ALLEEN-LEZEN als je geen schrijfbevoegdheid hebt.

38



Besluit Je kunt werkbladen, rijen, kolommen en cellen kopiëren, wissen en verplaatsen. Daartoe moet altijd eerst een bereik geselecteerd worden. De inhoud van een cel kan onafhankelijk van de opmaak gewist worden en omgekeerd. Bij het kopiëren en verplaatsen van cellen kan een celverwijzing in een formule plots veranderen. Dergelijke celverwijzingen noemt men relatieve adressen: het zijn adressen die verwijzen naar een afstand in het werkblad. De absolute adressen verwijzen naar een vaste positie in het werkblad en worden niet door kopiëren of verplaatsen beïnvloed. Een speciaal geval van kopiëren is het doorvoeren van reeksen. De handigste manier om dat te doen is meestal de vulgreep. Je kunt ook je eigen reeksen ontwerpen. Werkbladen, kolommen, rijen of cellen kunnen ook ingevoegd of verwijderd worden. Ook hierdoor kan de adressering veranderen. Voor onbevoegde kijkers kunnen rijen en kolommen verborgen worden. Uiteindelijk is er nog de mogelijkheid dat de gebruiker de inhoud van de cellen niet meer kan wijzigen, m.a.w. dat de celinhoud beveiligd kan worden. Beveiligen gebeurt altijd in twee fasen. Eerst moet de eigenschap BLOKKEREN ingesteld worden voor de cellen die in aanmerking komen voor beveiliging en vervolgens moet de beveiliging geactiveerd worden. In Excel kun je een werkmap met een wachtwoord beveiligen.

Wat je moet kennen en kunnen:         

vlot de inhoud van een reeks cellen verplaatsen of kopiëren; het verschil tussen relatieve en absolute adressering inzien, in functie van kopiëren en verplaatsen; cellen wissen, zowel wat de inhoud als wat de opmaak betreft; werkbladen, rijen, kolommen en cellen invoegen en verwijderen; snel reeksen ontwerpen en eigen reeksen definiëren; de functies ALS(), AANTAL() en SOM() zinvol toepassen; rijen, kolommen en cellen verbergen; bepaalde cellen in een werkblad beveiligen; een werkmap met een wachtwoord beveiligen.

Opdrachten 13. Is er een verschil tussen een cel wissen en een cel verwijderen? 14. Verklaar het verschil tussen de inhoud van een cel kopiëren en de inhoud van een cel verplaatsen. 15. Vergelijk een drietal mogelijkheden om een cel te kopiëren en te verplaatsen. 16. Verklaar het verschil tussen een relatieve en een absolute adressering met behulp van een voorbeeld. 17. Hoe moet je een werkblad beveiligen zodanig dat je maar een beperkt aantal cellen kunt wijzigen? J. Gils – E. Goossens


39

18. De jaarlijkse vlaaienactie

a. Open een nieuw werkblad en neem de gegevens van kolom A, de gegevens in het gebied B3:D10 en F3 en de prijs per vlaai in B18 volledig over. De breedte van kolom A is 13, de kolommen B, C, D en F zijn 9 en kolom E is 1,43 breed. De hoogte van rij 11 en rij 13 is 8,25. Geef de tekst dezelfde opmaak als in het voorbeeld. b. Zet in F5 een formule om het totale aantal rijstvlaaien te berekenen en kopieer deze formule naar F6:F12. Verwijder de formule in F11. Noteer het resultaat van de formule in cel F12. c. Zet in B12 een formule om het totale aantal vlaaien te berekenen die in Wezel verkocht zijn. Kopieer deze formule naar C12:F12. Verwijder de formule in cel E12. Is het resultaat in F12 nog altijd hetzelfde als in opdracht b? d. Bereken de laagste, hoogste en gemiddelde waarde in het gebied B14:D16. e. Kopieer het gebied A1:F16 zodanig naar cel H1 dat de kolombreedtes automatisch mee gekopieerd worden. Hoe lukt je dat? f. Wis de titel in H1, de inhoud van I5:K10. De formules in de overige cellen moeten blijven staan. g. Zet in I5 een formule die de waarde berekent van de 486 rijstvlaaien die in Wezel verkocht zijn. Verwijs naar cel B18 waar de prijs van één vlaai staat. Deze formule moet zodanig samengesteld worden dat je ze kunt kopiëren naar I6:I10. Vervolgens moet je het hele gebied I5:I10 kopiëren naar J5:K10. h. Pas de getalopmaak van kolom M aan zodat de valutanotatie getoond wordt. i. Beveilig het werkblad zodanig dat je alleen nog in het gebied B5:D10 en B18 een wijziging kunt aanbrengen. j. Geef dit werkblad als naam VLAAI. k. Selecteer het gebied H3:M10 en kopieer het naar het klembord. Open een nieuw werkblad, selecteer cel A5 en kies BEWERKEN, PLAKKEN SPECIAAL. Vink in het dialoogvenster de optie TRANSPONEREN aan en klik op OK. Is het resultaat wat je verwachtte? TIP

40

Als je transponeert naar een nieuw werkblad moet je ‘Waarden” transponeren, anders krijg je een foutmelding die zegt dat bepaalde verwijzingen niet gevonden zijn: #VERW!$B18 in C6.



19. De verkoopcijfers van de firma Jong & Dorst

a. Neem de gegevens van het voorbeeld over. b. Bereken per woonplaats het totale verkoopcijfer en het procentuele aandeel in de totale verkoop. Bereken per artikel het totale verkoopcijfer en het procentuele aandeel in de totale verkoop. c. Zet in cel F5 de juiste formule en kopieer die naar F6:F9. d. De formule van B11 kun je ook kopiëren naar C11:F11. e. Zorg dat je ook de formule in G5 kunt kopiëren naar G6:G9. Hetzelfde geldt voor de formule in B12 die gekopieerd moet worden naar C12:E12. f. Pas de opmaak aan en noem dit werkblad VERKOOP. g. Open een nieuwe werkmap en kopieer het werkblad VERKOOP naar deze nieuwe map door middel van slepen met de linkermuisknop. Daardoor zit er in die nieuwe map nu ook een werkblad met de naam VERKOOP. (Deze nieuwe map moet op het einde van de oefening niet bewaard worden.) h. Verander de naam van dit gekopieerde werkblad in TEST. i. Experimenteer nu even met het werkblad TEST.  Verwijder de kolom C. Geven de overgebleven formules nog altijd het juiste resultaat?  Voeg een lege kolom toe tussen de kolommen A en B. Kopieer de inhoud van kolom C van het werkblad VERKOOP naar die nieuwe kolom C van TEST.  Verwissel de kolommen D en E van plaats zonder dat de gegevens verloren gaan. Hoe los je dit probleem op?  Verplaats rij 11 en 12 en zet ze tussen rij 3 en rij 4. Hoe ga je tewerk? Werken de formules nog? j. Ga terug naar het werkblad VERKOOP en beveilig ten slotte het werkblad zodat je alleen maar de gegevens van B5:E9 kunt wijzigen.



41

Hoofdstuk 3

3.1

De beautycase van Excel

Probleemstelling Alleen maar met een rekenblad kunnen werken om correcte berekeningen uit te voeren is niet voldoende. De gegevens en de resultaten moeten ook op een nette en ordelijke manier gepresenteerd worden. Vaak is het ook nodig om het hele rekenblad of een gedeelte ervan af te drukken. Afhankelijk van de schikking van de gegevens moet je liggend of verticaal ('staand') afdrukken. Soms ben je niet geïnteresseerd in het volledige rekenblad, maar wel in een samenvattend werkblad waarbij een aantal details verborgen worden.

3.2

Het werkblad verfraaien

Welke opties staan ter beschikking? 





42

breedte van kolommen en hoogte van rijen; Dit onderdeel is vroeger al besproken. Alle kolommen zijn breder gemaakt zodat de inhoud in alle cellen volledig zichtbaar is. De hoogte van rij 1 is extra vergroot. getalopmaak; Dit onderdeel is ook al eerder besproken. De getallen in kolommen B en D hebben een valutaopmaak met € als valutasymbool. De getallen in kolom E hebben een PERCENTAGE-opmaak. uitlijning; De titel ‘Verkoopcijfers …’ staat in cel A1, maar is via een optie CENTREREN OVER SELECTIE gecentreerd over de cellen A1 tot en met E1. Deze titel is ook verticaal gecentreerd in de cel. De titels in rij 5 zijn allemaal zowel verticaal als horizontaal gecentreerd. Rekenblad: Excel 2003










De getallen zijn standaard naar rechts uitgelijnd. lettertype, grootte en tekstkleur; Samen met het lettertype kun je meestal nog een aantal andere opties instellen, zoals de puntgrootte, de kleur, de opmaak (vet, cursief, …), … De meeste opties zijn ook beschikbaar via knoppen op de werkbalk. Voor de inhoud van cel A1 gelden de volgende eigenschappen: lettertype Arial, opmaak Vet, puntgrootte 14, kleur Zwart. randen of lijnen; De buitenrand van het gebied A5:E11 is een dikke rand, de binnenste randen zijn van een dunner type. In het deel van het rekenblad dat niet in gebruik is zie je zelfs geen rasterlijnen omdat die afgezet zijn via OPTIES, WEERGAVE, RASTERLIJNEN. Het feit dat de rasterlijnen afgezet zijn betekent nog niet dat ze niet zouden afgedrukt worden. Dit wordt via BESTAND, PAGINA-INSTELLING geregeld en zal later behandeld worden. opvulkleur; Hiermee wordt de achtergrond van de cel bedoeld. Het gebied A1:E1 heeft een grijze achtergrond. Je kunt meestal uit tal van kleuren kiezen. bescherming / beveiliging. Dit onderdeel is vroeger al besproken. Bescherming biedt de mogelijkheid om de celinhoud te beschermen tegen ongewild wijzigen. Daartoe moet je eerst de cel blokkeren en vervolgens de beveiliging activeren.

Hoe ga je tewerk om bijvoorbeeld effecten zoals in de figuur hieronder te bekomen?

Eerst en vooral selecteer je het op te maken gebied en vervolgens roep je de gewenste opmaakopties op via:  sommige knoppen op de werkbalk;  het snelmenu CELEIGENSCHAPPEN;  het menu OPMAAK, CELEIGENSCHAPPEN.



43

TIPS  Op de werkbalk OPMAAK vind je de belangrijkste opmaakopties zoals lettertype, uitlijnen, getalopmaak, randen, opvulkleur, …  De opmaak doe je best als alle formules op het werkblad staan, anders is het mogelijk dat je per vergissing de opmaak, voornamelijk de celranden, mee kopieert als je de formules kopieert.  De opvulkleur en het patroon beïnvloeden alleen de achtergrond van de cel. De voorgrond wordt door de celinhoud en de randen bepaald.  Hoe meer opmaak je aan het werkblad geeft, hoe omvangrijker het werkblad wordt. Dit is zeker het geval wanneer je veel achtergrondkleur gebruikt. Dan bestaat de kans dat je laserprinter protesteert bij het afdrukken van dit blad omdat hij te weinig inwendig geheugen heeft. Opdrachten 1. Het werkblad OMZET opmaken Open het werkblad OMZET en tracht de opmaak van het voorbeeld aan het begin van dit hoofdstuk zoveel mogelijk na te bootsen. Hou rekening met de hoger vermelde opties. Wees creatief! 2. Het werkblad VLAAI opmaken Open dit werkblad en geef het een opmaak naar eigen smaak.

44



3.3

Voorwaardelijke opmaak Het is mogelijk om de opmaak van cellen te doen afhangen van een voorwaarde. Neem het werkblad punten (bladzijde 32). Het zou interessant zijn om de procenten die lager zijn dan 50 in het rood af te drukken. Opdracht 3. Voorwaardelijke opmaak toepassen a. Kopieer het werkblad PUNTEN naar een nieuw blad en noem het PUNTEN2. b. Selecteer het gebied G5:G13 en kies OPMAAK, VOORWAARDELIJKE OPMAAK. Een dialoogvenster verschijnt.

c. Voer de voorwaarde in. d. Klik op de knop OPMAAK: het venster CELEIGENSCHAPPEN wordt geopend. Kies hierin de rode kleur voor het lettertype. e. Test de werking van de opmaak. TIP

Via de knop TOEVOEGEN kun je meerdere voorwaarden ingeven en aan elke voorwaarde een opmaak koppelen



45

3.4

Pagina-instelling Voordat je een werkblad afdrukt controleer je best de pagina-instelling. Deze optie vind je terug in het menu BESTAND, PAGINA-INSTELLING. 

 



46

Het tabblad PAGINA  Afdrukstand Je moet kiezen tussen STAAND en LIGGEND. In een tekstverwerker druk je meestal af in de lengterichting van het blad. Dit noemt men staand. Bij een rekenblad gebeurt het vaak dat je in de breedterichting van het blad afdrukt. Dat noemt men liggend.  Aanpassen Deze optie biedt de mogelijkheid om je werkblad verkleind of vergroot af te drukken zodat er meer of minder op een blad kan. Je kunt er zelfs voor zorgen dat alles op één blad afgedrukt wordt.  Papierformaat Kies hier meestal het standaardformaat, namelijk A4.  Afdrukkwaliteit Dit hangt af van de kwaliteit die je printer aankan. In het voorbeeld is dat 600 dpi (600 dots per inch), wat een zeer goede kwaliteit geeft. Het tabblad MARGES  Linkermarge, rechtermarge, …  Centreren: het werkblad horizontaal of verticaal centreren. Het tabblad KOP- EN VOETTEKST Via de optie AANGEPASTE KOP-/VOETTEKST kun je een eigen koptekst samenstellen.

Het tabblad BLAD  Afdrukbereik: Om slechts een deel van het blad af te drukken geef je hier het bereik aan.  Afdruktitels  Afdrukken: Rasterlijnen en Rij- of kolomkoppen Via de optie EXTRA, OPTIES, WEERGAVE kun je de rasterlijnen afzetten en de rijen kolomkoppen verbergen in het werkblad. Het feit dat je de rasterlijnen wel of niet op het scherm ziet heeft niets te maken met het wel of niet afdrukken van de rasterlijnen op papier.  Paginavolgorde: Bij grote werkbladen kun je beslissen of de bladen eerst omlaag afgedrukt worden en dan pas opzij of omgekeerd. Dit is belangrijk voor de paginanummers. Rekenblad: Excel 2003


3.5

Een werkblad afdrukken Je kunt de opdracht tot afdrukken geven via de knop AFDRUKKEN op de werkbalk of via BESTAND, AFDRUKKEN. Via deze laatste optie kom je altijd in een dialoogvenster terecht, via de knop AFDRUKKEN niet. Een aantal opties in het dialoogvenster AFDRUKKEN zijn zeer universeel. naam van de printer

Naar welke printer wil je afdrukken? Een lokale printer zoals Brother HL-10h, of een netwerkprinter zoals \\ C31\brother.

aantal exemplaren

Wil je het werkblad op één of meer exemplaren afdrukken?

wat afdrukken

Wil je een geselecteerd gebied, het hele werkblad of zelfs de hele map afdrukken?

afdrukbereik

Indien het afdrukbereik niet op één pagina kan, wil je dan alle pagina’s afdrukken, of slechts bepaalde pagina’s?



47

afdrukvoorbeeld

TIP

Alvorens de gegevens naar de printer te sturen is het altijd verstandig een afdrukvoorbeeld op het scherm te vragen. Voldoet dat voorbeeld, dan kun je afdrukken op de printer. Deze optie is meestal ook beschikbaar via een knop op de werkbalk.

Kies altijd eerst voor een afdrukvoorbeeld op het scherm, alvorens op papier af te drukken. Je zult merken dat je zo een gunstige bijdrage levert tot het behoud van het regenwoud. Greenpeace zal je dankbaar zijn.

Opdracht 4. De eerste drukproef a. Open het werkblad OMZET. b. Gebruik de volgende instellingen voor de pagina-instelling:  koptekst:  de datum: links uitgelijnd;  het paginanummer: rechts uitgelijnd;  Pagina &[PAGINA] van &[PAGINA’S];  een titel: ‘Verkoopcijfers’, gecentreerd.  voettekst:  de naam van de school: links uitgelijnd;  je naam en nummer: gecentreerd;  de tijd: rechts uitgelijnd.  rasterlijnen: afdrukken;  kolomkoppen en rijnummers: afdrukken;  afdrukstand: staand. c. Bekijk het afdrukvoorbeeld op het scherm. d. Roep het dialoogvenster ADRUKKEN op en kijk na of de juiste printer geselecteerd is. Via welke printer ga je afdrukken? e. Druk ten slotte het werkblad af.

48



3.6

Kolommen en rijen tijdens schuiven zichtbaar houden Het is mogelijk om kolommen en rijen te blokkeren zodanig dat ze tijdens het scrollen zichtbaar blijven. Het zijn alleen maar de rijen boven en de kolommen links van de actieve cel die geblokkeerd kunnen worden via de optie VENSTER, TITELS BLOKKEREN. Opdracht 5. Het werkblad WAGENS opmaken en de eerste rij en kolom A en B vastzetten a. Open de werkmappen OPGAVENXP.XLS en OEFENXP.XLS. Kopieer het werkblad WAGENS van de map OPGAVENXP naar OEFENXP.

b. Voeg aan dit werkblad de nodige kolommen en rijen toe om subtotalen en totalen te berekenen. Tracht de opmaak van het bijgevoegde voorbeeld te benaderen.

c. Blokkeer rij 1 en kolom A en B: activeer cel C2 en kies VENSTER, TITELS BLOKKEREN. Activeer een cel in kolom W om het effect van de blokkering te tonen.

3.7

Vensters splitsen Als je verschillende delen van een werkblad onafhankelijk van mekaar wilt weergeven en verschuiven, kun je een werkblad horizontaal en verticaal in afzonderlijke deelvensters splitsen. Werkwijze:



49

3.8

Een samenvattend werkblad realiseren

Opdracht 6. In het werkblad WAGENS alleen maar de overzichten tonen a. Open de werkmap OEFENXP.XLS en kopieer het werkblad WAGENS naar een nieuw werkblad dat je SAMENVATTING noemt. b. Selecteer de kolommen C, D en E. Kies DATA, OVERZICHT, GROEPEREN. Als je niet de hele kolom selecteert dan wordt gevraagd of het groeperen volgens kolommen of rijen moet gebeuren. Kies in dat geval KOLOMMEN. Herhaal dezelfde bewerking voor de kolommen van de andere kwartalen TIP

De werkbalk DRAAITABEL bevat de knoppen GROEPEREN en GROEP OPHEFFEN. Zet deze werkbalk op het scherm.

c. Selecteer vervolgens de kolommen C tot en met V en kies opnieuw GROEPEREN. Merk je de groeperingssymbolen boven de rijkoppen? d. Selecteer de rijen 6, 7 en 8 en klik op de knop GROEPEREN. Doe hetzelfde met de rijen 11, 12 en 13 en nog eens met de rijen 5 tot en met 14. e. Test de werking van de groeperingssymbolen. TIP

50

Kies DATA, OVERZICHT, OVERZICHT WISSEN om het overzicht te verwijderen.



Besluit Via opmaak kun je het werkblad verfraaien. Er wordt een onderscheid gemaakt tussen de celinhoud, de celrand en de achtergrond van de cel. In de drie gevallen kan de kleur aangepast worden. Bij de celinhoud of de voorgrond van de cel kan ook nog het lettertype, de tekengrootte, de tekenstijl en het uitlijnen aangepast worden. Bij de celrand is het lijntype te veranderen en bij de achtergrond kan nog een bepaald patroon ingesteld worden. Via afdrukken wordt het resultaat naar de printer gestuurd. Met de pagina-instelling kun je nogal wat afdrukopties wijzigen zoals wel of niet afdrukken van rasterlijnen, kolom- en rijkoppen, koptekst en voettekst, alsook de afdrukstand, marges, … Door titels te blokkeren kun je beletten dat de titels in de meest linkse kolommen en de bovenste rijen van het scherm verdwijnen als je de cursor naar onder of naar rechts in het werkblad beweegt. Door het venster te splitsen kun je delen van een werkblad onafhankelijk van mekaar weergeven en verschuiven. De optie GROEPEREN maakt het mogelijk om kolommen en rijen tijdelijk te verbergen en ze op een handige manier terug te voorschijn te laten komen.

Wat je moet kennen en kunnen:     

een werkblad mooi presenteren via de mogelijkheden van opmaak; een werkblad klaarmaken om af te drukken en het ook afdrukken; inzicht hebben in de verschillende opties van de pagina-instelling, zoals bladstand, marges, rasterlijnen, kopen voettekst, …; titels blokkeren en vensters splitsen; een samenvattend werkblad realiseren.

Opdracht 7. Wat is de betekenis van staand en liggend afdrukken? 8. Wanneer gebruik je de optie ‘centreren over selectie’? 9. Hoe kun je een aangepaste voettekst ontwerpen? 10. Wat is de bedoeling van het afdrukvoorbeeld? 11. Waarom zou je de rijen kolomkoppen mee afdrukken? 12. Open het werkblad VLAAI, zorg voor een eigen opmaak (wees creatief) en druk het liggend af. 13. Taaloefening: zoek de juiste werkwoordvorm In kolom C wordt de juiste werkwoordvorm ingevuld. Het resultaat van de verbetering verschijnt in kolom D. Geef het werkblad als naam TAAL.



51

TIP

Via OPMAAK, CELEIGENSCHAPPEN, RAND kun je alleen de bovenkant, de onderkant of een zijkant van een cel een rand geven.

TIP

De titel is gecentreerd over de kolommen A, B, C en D, maar bevindt zich in cel A1. Kies OPMAAK, CELEIGENSCHAPPEN, UITLIJNING, HORIZONTAAL UITLIJNEN: CENTREREN OVER SELECTIE.

14. Belgische rivieren Geef het werkblad als naam RIVIEREN.

15. Eventjes rekenen Tracht de volgende opgave zo goed mogelijk na te bootsen. De bewerkingstekens in kolom C moet je als tekst ingeven. Via een ingewikkelde ALS(C6=”x”;B6*D6;ALS(…)) worden de uitkomsten in een verborgen kolom bijgehouden. Beveilig het werkblad zodat alleen in de cellen G3 en F6:F15 gegevens gewijzigd kunnen worden. De rasterlijnen kun je afzetten via EXTRA, OPTIES, WEERGAVE, RASTERLIJNEN. 52



Geef het werkblad als naam REKENEN en druk het af.

TIP

Als je al thuis bent in VBA, dan zou je de boodschappen in kolom G via een zelf geschreven functie kunnen afdrukken. Je moet de functie met vier parameters oproepen: het eerste getal, het bewerkingsteken en het tweede getal en de uitkomst. In dat geval heb je geen verborgen kolommen nodig. In dat geval wordt de functie als volgt opgeroepen: =Controle(B6;C6;D6;F6)

Function Controle(intGetal1 As Integer, strBewerking As String, intGetal2 As Integer, intUitkomst As Integer) As String Dim intHulp As Integer Dim strResultaat As String Select Case strBewerking Case "+" intHulp = intGetal1 + intGetal2 Case "-" intHulp = intGetal1 – intGetal2 Case "x" intHulp = intGetal1 * intGetal2 Case ":" intHulp = intGetal1 \ intGetal2 End Select If intHulp = intUitkomst Then strResultaat = "Goed " Else strResultaat = "Fout " End If Controle = strResultaat End Function



53

Hoofdstuk 4

54

De facturatie



Probleemstelling De firma Jos Computers bvba wil vanaf nu de facturatie aan haar klanten automatiseren. De gegevens van de klant moeten automatisch in de factuur gebracht worden na het intikken van een klantnummer. Daarom moeten deze gegevens ook ergens in een werkblad aanwezig zijn. Ook de verkochte artikelen worden via een artikelnummer ingebracht. De verkoopprijs en de verschuldigde BTW worden automatisch berekend. Ten slotte moet het totale bedrag aan BTW opgesplitst worden volgens BTW-percentage.

4.1

Probleemanalyse Je vertrekt best met een lege werkmap die je de naam FACTUURXP.XLS geeft. Hierin zijn minstens drie werkbladen nodig: FACTUUR, KLANTEN en ARTIKELEN. De gegevens van de klanten worden geïmporteerd uit de database CENTEN en opgeslagen in het werkblad KLANTEN. Elke klant moet een klantnummer krijgen. De artikelgegevens worden ingetypt in het werkblad ARTIKELEN. Elk artikel krijgt ook een code. In het werkblad FACTUUR wordt de factuur ontworpen die er ongeveer moet uitzien zoals het bijgevoegde voorbeeld. Het is de bedoeling dat na het intikken van het klantnummer de rest van de klantgegevens automatisch opgezocht worden in het werkblad KLANTEN om ze daarna ook op de voorziene plaats in het werkblad FACTUUR in te vullen. De artikelen worden ook via een code ingevoerd. Na de invoer van het aantal stuks wordt de totaalprijs zonder BTW berekend. Ten slotte moet onderaan op de factuur een opsplitsing gemaakt worden volgens BTW-percentage. Hiervoor wordt gebruik gemaakt van hulpkolommen die achteraf verborgen worden. Uiteindelijk ontwerp je nog een menusysteem dat via opdrachtknoppen, die aan macro’s gekoppeld zijn, toegang tot de verschillende werkbladen geeft.

4.2

Het werkblad Artikelen en BTW-codes

4.2.1 Een artikeltabel samenstellen Opdrachten 1. De artikelgegevens Deze gegevens moet je zelf in het werkblad ARTIKELEN intikken. Belangrijk is dat je naast de artikelcode ook de omschrijving, de inkoopprijs zonder BTW, de winstmarge en de BTW-code voor elk artikel bepaalt. De verkoopprijs zonder BTW en het BTWpercentage worden via formules berekend.



55

a. Neem de beide werkbladen volledig over. Het eerste werkblad noem je “Artikelen” en het tweede “BTW-codes”. De waarden in de kolom verkoopprijs zonder BTW en BTW % mag je niet overnemen: het zijn immers formules. Voor de artikelcode gebruik je best een combinatie van letters en cijfers. b. Bereken in kolom E de verkoopprijs zonder BTW. Het resultaat moet een ‘echt’ afgerond getal worden tot op twee cijfers na de komma. Gebruik hiervoor de functie AFRONDEN(). Welke formule zet je in E4 om de verkoopprijs zonder BTW te berekenen? AFRONDEN(getal;aantal-decimalen)

rondt een getal af op het opgegeven aantal decimalen.  

getal: is het getal dat je wilt afronden. aantal-decimalen: geeft het aantal decimalen aan waarop je het getal wilt afronden.  Als aantal-decimalen groter dan 0 (nul) is, wordt getal afgerond op het opgegeven aantal decimalen.  Als aantal-decimalen gelijk aan 0 is, wordt getal afgerond op het dichtstbijzijnde gehele getal.  Als aantal-decimalen kleiner dan 0 is, wordt getal afgerond op tientallen (-1), honderdtallen (-2), enz.

c. Zoek in een computerboekje een aantal artikelen en bereken hiervan een gepaste inkoopprijs en voeg die aan de lijst met artikelen toe. d. Geef ten slotte het werkblad een opmaak zoals in het voorbeeld. e. Bewaar de werkmap onder de naam FACTUURXP.XLS TIP

De tekst “Inkooppr. zonder BTW” in cel C3 van het werkblad ARTIKELEN is horizontaal uitgelijnd naar links en verticaal uitgevuld.

TIP

Via OPMAAK van getallen kun je ook afronden. Dit afronden gebeurt alleen tijdens de weergave op het scherm. De getallen worden nog altijd met al hun decimalen in het werkblad bewaard. Met de functie AFRONDEN() verliest het getal daarentegen de rest van de decimalen en wordt dan ook zo in het werkblad bewaard.

56



4.2.2 Gegevens met de functie Vert.Zoeken opzoeken Opdracht 2. Het BTW-percentage opzoeken met de functie VERT.ZOEKEN() In kolom F van het werkblad ARTIKELEN wordt de BTW via een code ingegeven, de betekenis hiervan wordt met de functie VERT.ZOEKEN() opgezocht in het werkblad BTW-CODES en automatisch ingevuld in kolom G. VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal;benaderen)



zoekwaarde: is de waarde die je wilt zoeken in de eerste kolom van de matrix. De zoekwaarde kan een waarde, een verwijzing of een tekenreeks zijn.  tabelmatrix: is de gegevenstabel waarin je naar gegevens wilt zoeken. Je kunt een verwijzing naar een bereik of een bereiknaam opgeven, zoals Database of Lijst.  Als benaderen WAAR is, moeten de waarden in de eerste kolom van de tabelmatrix in oplopende volgorde gesorteerd zijn (...; -2; -1; 0; 1; 2; ... ; A-Z; ONWAAR; WAAR), anders kan VERT.ZOEKEN een onjuist resultaat geven.  Als benaderen ONWAAR is, hoeft de tabelmatrix niet gesorteerd te zijn.  kolomindex-getal: is het nummer van de kolom in de tabelmatrix waaruit je de gezochte waarde moet ophalen. Als kolomindex_getal 2 is, wordt de waarde uit de tweede kolom in de tabelmatrix opgehaald, als kolomindex_getal 3 is uit de derde kolom, enz.  benaderen: is een logische waarde die aangeeft of VERT.ZOEKEN wel of niet exact overeenkomende waarden moet zoeken.  Als benaderen WAAR is of weggelaten wordt, dan geeft deze functie altijd de grootste waarde die kleiner is dan de zoekwaarde als een exacte overeenkomst niet gevonden kan worden.  Als benaderen ONWAAR is, wordt alleen naar een exacte overeenkomst gezocht. Wordt deze niet gevonden, dan resulteert de functie in de foutwaarde #N/B. Welke formule komt in cel G4?  zoekwaarde: de inhoud van cel F4;  tabelmatrix: er moet gezocht worden in het gebied A4:B8, waarbij de gezochte waarde in de eerste kolom van het zoekgebied moet staan;  kolomindex-getal: in de tweede kolom van het zoekgebied staat de gewenste waarde, namelijk het BTW-percentage in de 2de kolom;  benaderen: ONWAAR: de zoekwaarde moet exact overeenkomen met een waarde in de eerste kolom van het zoekgebied. Samengevat: =VERT.ZOEKEN(F4;'BTW-codes'!$A$4:$B$8;2;ONWAAR) TIP

Maak de verwijzing naar het zoekgebied altijd absoluut.

TIP

Als de zoekwaarde kleiner is dan de kleinste waarde in de eerste kolom van de tabelmatrix of als de zoekwaarde niet gevonden wordt dan geeft VERT.ZOEKEN de foutwaarde #N/B als resultaat: zet daarom als eerste element in die tabel altijd de



57

kleinst mogelijke waarde, bijvoorbeeld 0, een spatie,… als er naar een benaderende waarde gezocht wordt. 4.2.3 Een naam aan een bereik geven Opdrachten 3. Het bereik 'BTW-codes'!$A$4:$B$8 een naam geven a. Ga naar het werkblad BTW-codes en selecteer het gebied A4:B8. b. Kies INVOEGEN, NAAM, DEFINIËREN.

c. Geef het bereik de volgende naam: “BTW”. Spaties of leestekens zijn niet toegelaten in de naam. d. Selecteer vervolgens het gebied A4:B8 en klik op OK. Merk op dat de naam van het werkblad BTW-CODES is (met een koppelteken!). Omwille van dit koppelteken verschijnt deze naam tussen aanhalingstekens in de verwijzing: = ‘btw-codes’!$A$4:$B$8 De naam van het werkblad en de naam van het bereik mogen dezelfde zijn. e. Verander de formule van cel G4 in =VERT.ZOEKEN(F4;BTW;2;ONWAAR) Wat is het nut van een dergelijke bereiknaam? TIP

De lege cellen in het gebied A7:B8 kunnen achteraf gebruikt worden als er nog andere BTW-tarieven nodig zouden zijn.

4. Geef het bereik A4:G… met de gegevens van de artikelen de naam ARTIKELLIJST. TIP

58

Als benaderen ONWAAR is in de functie Vert.Zoeken(), dan mag je wat reserve inbouwen in de ondergrens. Voor de artikellijst mag het bereik zonder problemen A4:G50 zijn. Je hebt dan zeker geen problemen met de grenzen van het bereik als je gegevens toevoegt. Die grenzen passen zich automatisch aan als je gegevens tussenvoegt.



4.2.4 Een tabel sorteren Opdracht 5. De tabel met artikelen volgens OMSCHRIJVING sorteren a. Selecteer het gebied A3:G9 en kies DATA, SORTEREN. Zorg ervoor dat de rij met de veldnamen ook geselecteerd wordt samen met de artikelgegevens. b. Selecteer achtereenvolgens  SORTEREN OP: OMSCHRIJVING  VERVOLGENS OP: CODE TIP Vergeet niet aan te geven dat de lijst een veldnamenrij bevat want anders wordt deze rij gewoon mee gesorteerd en belandt ze tussen de artikelen.

TIP

4.3

Als je sorteert via de knop SORTEREN uit de werkbalk dan wordt de tabel gesorteerd volgens de kolom waar de cursor in staat. De cursor moet wel in de tabel staan!

Het werkblad Klanten Opdrachten 6. De klantengegevens kopiëren uit de database CENTENXP Open de database CENTENXP. Ontwerp een query QRYMAAKKLANT op basis van de tabel TBLSPONSOR die een selectie maakt van een beperkt aantal sponsors, oplopend gesorteerd volgens BEDRIJF. Neem bijvoorbeeld alleen de sponsors waarvan naam van de straat begint met de letter “A” of “S” en al de sponsors uit Wuustwezel. De query moet alle velden tonen die nodig zijn voor het factuurontwerp: de naam van het bedrijf, de straat, de postcode en woonplaats, het BTW-nummer en het telefoonnummer. Met behulp van een rekenveld moet je het klantnummer samenstellen: dit nummer moet beginnen met “40” en gevolgd worden door een volgnummer, bijvoorbeeld het veld KLANT_ID. Code: "40"+Right("0000"+Trim(Str([SPONSOR_ID]));4) Zet de getalwaarde van SPONSOR_ID om naar een tekenreeks (String), verwijder de spaties voor en achter de tekenreeks, zet er vier nullen voor en neem de vier laatste tekens uit deze reeks. Voeg die achteraan bij de tekenreeks “40”.



59

Het resultaat van de query kun je op twee manieren naar de geopende werkmap FACTUUR overbrengen. 

Voer de query uit, selecteer alle records in het gegevensblad, kopieer ze naar het klembord en plak ze vervolgens in een leeg werkblad van de map FACTUURXP.XLS. Noem dit werkblad ‘Klanten’.



Selecteer de query QRYMAAKKLANT in het tabblad QUERY’S en kies VERVOLGENS EXTRA, OFFICE-KOPPELINGEN, ANALYSEREN IN MS EXCEL. De gegevens zullen in een rekenblad van een nieuwe map verschijnen. Kopieer dit werkblad, QRYMAAKKLANT, naar de map FACTUUR en noem het werkblad ‘Klanten’.

TIP

Kies VENSTERS, ALLE VENSTERS en sleep het werkblad QRYMAAKKLANT van de ene map naar de andere.

TIP

Het is goed mogelijk dat de opmaak van de tabel in het rekenblad er nogal raar uitziet. In dat geval selecteer je het hele werkblad, ga naar OPMAAK, CELEIGENSCHAPPEN, UITLIJNING. Kies ‘Standaard’ voor de horizontale uitlijning, ‘Onder’ voor de verticale uitlijning en verwijder alle vinkjes in de optie TEKSTCONFIGURATIE.

TIP

De kolommen met de code en de postcode bevatten getallen die als tekst opgemaakt zijn - je merkt dat aan de uitlijning naar links. Klik op het uitroepteken en kies FOUT NEGEREN.

7. Geef het bereik A2:H50 de naam KLANTENLIJST. TIP

60

Als je een klant aan de lijst moet toevoegen, voeg je die best niet achteraan toe, maar ergens tussenin omdat dan de verwijzing naar het gebied KLANTENLIJST automatisch aangepast wordt. Door sorteren komt de toegevoegde klant wel op de juiste plaats terecht.



4.4

Het werkblad FACTUUR Het grootste probleem is hier de breedte van de kolommen. Je merkt dat er twee kolommen gebruikt worden voor de omschrijving van de artikelen. In feite zou één kolom volstaan als je die maar breed genoeg zou maken, maar dan zou de opmaak van de rest van de factuur wel wat problemen geven. TIP

Maak de kolommen niet extreem breed: dat geeft problemen met de opmaak!



61

4.4.1 De factuur- en klantgegevens toevoegen Opdrachten 8. Het gebied met de factuurgegevens [C17:G18] ontwerpen a. Neem de inhoud van rij 17 volledig over. Wacht met de opmaak tot de formules in rij 18 ingevuld zijn. b. In C18 wordt het klantnummer ingetikt. c. Via een formule wordt in de klantenlijst het telefoonnummer opgezocht. = VERT.ZOEKEN($C$18;Klanten!$A$4:$H$30;7;ONWAAR) Dit kan ook door gebruik te maken van de naam van de klantenlijst:

=VERT.ZOEKEN($C$18;klantenlijst;6;ONWAAR) Als in C18 niets ingevuld is, dan verschijnt in D18 de foutboodschap #N/B. Daarom mag er slechts in de klantenlijst gezocht worden als er een klantnummer gekend is. Wijzig daarom de formule in: =ALS($C$18<>"";VERT.ZOEKEN($C$18;Klantenlijst;7;ONWAAR);"") TIP

Het is mogelijk dat in cel C18 het klantnummer als een getal ervaren wordt. Dan zal de functie VERT.ZOEKEN() de fout #N/B geven. Verander in dat geval, via CELEIGENSCHAPPEN, de opmaak van cel C18 in TEKST, verwijder het nummer en typ het opnieuw in.

d. Vul zelf de formule in om het BTW-nummer op te zoeken. e. De factuurdatum wordt via een functie opgeroepen: = NU(). Via CELEIGENSCHAPPEN, GETAL, AANGEPAST kun je de datum volgens BIN-normen afdrukken. Notatie jj jjjj m mm mmm mmmm d dd ddd dddd jjjj-mm-dd d mmmm jjjj 62

Voorbeeld 98 1998 5, 10 05, 10 okt oktober 6, 23 06, 23 di dinsdag

Betekenis jaartal met twee cijfers jaartal met vier cijfers maand met één of twee cijfers maand met twee cijfers eerste drie letters van de maand de maand voluit de dag met één of twee cijfers de dag met twee cijfers de eerste twee letters van de dag de dag voluit geschreven 1999-04-07 7 april 1999 Rekenblad: Excel 2003


Hoe zou je een datum als volgt afdrukken: ‘Vandaag, woensdag 7 april 1999’? f. De vervaldag is 30 dagen verder dan de factuurdatum: =NU()+30 g. Pas de opmaak van rij 17 en 18 aan volgens je eigen keuze. TIP

Vergeet niet regelmatig de werkmap te bewaren.

9. Het adres van de klant automatisch doen verschijnen Via het klantnummer in cel C18 wordt het adres van de klant opgezocht in de klantenlijst.  cel F11: de naam van het bedrijf  cel F13: de straat  cel F14: de postcode samen met de woonplaats. Denk eraan dat niets mag verschijnen in de cellen F11, F13 en F14 als in cel C18 geen klantnummer ingevuld is. Welke formule zul je in cel F14 intikken? TIP

Het is handiger om de formule van D18 te kopiëren naar F11, F13 en F14 en daarna de kolomverwijzing in deze formules aan te passen.

4.4.2 De invullijst met de artikelgegevens Opdracht 10. Het gebied met de artikelgegevens [A20:H40] ontwerpen a. Neem rij 20 volledig over en wacht met de opmaak tot de formules in rij 21 ingevuld zijn. b. In cel B21 moet je het artikelnummer invullen en in cel C21 het aantal stuks. c. Vul in de andere cellen van die rij de gewenste formules in. Het is wel de bedoeling dat er in de cellen met formules niets verschijnt als er geen artikelnummer ingevuld is. d. Zorg er wel voor dat het bedrag van de stukprijs in cel F21 en het totaal in cel H21 een ‘echt’ afgerond getal is, tot twee cijfers na de komma. Gebruik de functie AFRONDEN(). Welke formule heb je ingetikt? Weet je nog het verschil tussen afronden via de functie AFRONDEN() en afronden via OPMAAK, AANTAL DECIMALEN? e. Als de formules in orde zijn, kopieer ze dan tot rij 40 en geef het gebied een gepaste opmaak. TIP

Als je uitgebreidere tests wilt samenstellen, kun je maximaal zeven ALS-functies nesten door deze als argumenten waarde-als-waar en waarde-als-onwaar op te geven. =ALS(A6<”C”; “Kleiner”; ALS(A6 >”K”;”Groter”;”Tussenin”))

TIP

De tekst “Omschrijving” is gecentreerd over de cellen D20 en E20. Daartoe selecteer je de beide cellen, kies dan CELEIGENSCHAPPEN, UITLIJNEN, HORIZONTAAL, CENTREREN OVER SELECTIE.



63

4.4.3 De opsplitsing van de BTW-bedragen Opdracht 11. De BTW-bedragen opsplitsen en berekenen a. De opsplitsing van de eindbedragen volgens BTW-code gebeurt in een aantal extra kolommen die achteraf verborgen zullen worden.

b. In cel J20 komt een verwijzing naar het BTW-percentage uit het werkblad BTW-CODES dat overeenkomt met code 0: =‘BTW-CODES’!$B$4 TIP

Als je de cel ‘BTW-CODES’!$B$4 een naam geeft, bijv. “BTW0”, dan kun je die naam ook als verwijzing gebruiken: =BTW0.

Zet ook de gepaste formule in de cellen K20 en L20. Is de opmaak van deze cellen correct? Hoe pas je die aan? c. In J21 komt een formule in de aard van: als het BTW-percentage dat in G21 staat gelijk is aan het BTW-percentage dat in J20 staat, zet dan in deze cel (J21) de inhoud van H21, in het andere geval zet je hier niets. =ALS(G21=J20;H21;"") d. Wijzig de formule in L21 zodanig dat je ze zonder problemen kunt kopiëren naar K21 en J21 en kopieer ten slotte J21:L21 tot J40:L40. Welke is de juiste formule? e. Geef deze hulptabel ook een weinig opmaak. f. Zet op analoge wijze als in punt a. de BTW-percentages in de cellen E42, F42 en G42. g. Zet in de cellen E43 en E44 de gepaste formules en kopieer ze naar F43:G44. Zorg er wel voor dat de uitkomsten van deze formules ‘echte’ afgeronde getallen zijn, tot op twee cijfers na de komma. Welke formule staat er in E43? Welke formule staat er in E44? h. Zet nu de formules voor de eindtotalen in cellen H43 en H44.

64



4.4.4 De eindtotalen berekenen Opdracht 12. De eindtotalen [D42:H46] a. Neem de opmaak van het gebied D42:H46 over. b. Zet in de cellen D43, D44, D46 en H42 de afgebeelde tekst, in de andere cellen komen formules. Heb je al een idee welke? c. De BTW-codes in E42, F42 en G42 moeten afgelezen worden uit het werkblad BTW-CODES. Welke formule gebruik je? d. In cel E43 komt een formule die het totale bedrag weergeeft waarop 0% BTW geheven wordt. Zorg er voor dat je deze formule ook naar F43 en G43 kunt kopiëren. Welke formule staat in E43? e. Het eindtotaal in H46 is de som van de totalen in H43 en H44. Wat is de volledige formule? 4.4.5 De factuurkop Opdracht 13. De factuurkop Het adres van de firma komt in C2, C4, C5 en C6. De rest van de gegevens zet je in G2:H6. Het logo wordt er later bijgezet. Het factuurnummer zal voorlopig gewoon ingetypt worden.

4.5

Formules controleren aantekeningen maken Excel beschikt over functies waarmee je de oorzaken van problemen in een werkblad kunt opsporen. Een aantal functies kun je via menu’s of via de werkbalk FORMULE CONTROLEREN oproepen.

Met behulp van de broncelpijlen en doelcelpijlen en de formulecontrole kun je de volgorde van de berekeningen in een werkblad zichtbaar maken. Je kunt zien uit welke cellen een formule haar gegevens haalt, de broncellen, of welke formules gebruik maken van de gegevens in een bepaalde cel, de doelcellen.



65

4.5.1 De gegevensstroom zichtbaar maken Opdracht 14. Bron- en doelcellen opsporen a. Zet de werkbalk FORMULE CONTROLEREN op het scherm of kies de gewenste optie via EXTRA,FORMULE CONTROLEREN. b. Selecteer cel H21 van het werkblad FACTUUR en kies de optie BRONCELLEN AANWIJZEN. De blauwe controlepijlen maken de onderling gekoppelde cellen zichtbaar. De pijlpunten geven de richting van de stroom aan.

De formule in cel H21 gebruikt informatie uit de cellen B21, C21 en F21. Cel B21 is een broncel van cel H21, m.a.w. cel H21 is een doelcel van B21. c. Kies de optie DOELCELLEN AANWIJZEN.

De waarde van cel H21 wordt in de cellen J21, K21 en L21 gebruikt. d. Toon waar de cel G42 haar gegevens haalt en welke cellen van deze cel gebruik maken. e. Het is mogelijk om de gegevensstroom te volgen: Dubbelklik op de controlelijn in cel B21: automatisch wordt cel H21 geselecteerd. Dubbelklik hierin op de controlelijn die naar de doelcel wijst: cel L21 wordt geselecteerd. Dubbelklik hierin op de controlelijn die naar de doelcel wijst: cel G43 wordt geselecteerd. f. Verwijder alle controlelijnen met de optie ALLE CONTROLEPIJLEN VERWIJDEREN. g. Zoek de gegevensstroom die vertrekt vanaf cel B22. Waar eindigt de stroom? TIP

De controlelijnen zijn beter zichtbaar als je de rasterlijnen uitschakelt.

4.5.2 Foutcontrole Opdrachten 15. Een rekenblad op fouten controleren a. Activeer het blad FACTUUR en kies menu EXTRA, FOUTCONTROLE. b. Het is goed mogelijk dat de volgende fout weergegeven wordt:

66



De fout wordt veroorzaakt doordat het klantennummer dat in feite een getal voorstelt als tekst opgeslagen is. Kies in dit geval FOUT NEGEREN. TIP

Op deze manier kun je getallen die opgeslagen zijn als tekst opnieuw naar getallen converteren.

16. Foutieve formules onderzoeken a. Wijzig in cel D18 de inhoud als volgt: =ALS($C$18<>"";VERT.ZOEKEN($C$18;klantenlijst;17;ONWAAR);"") Er wordt verwezen naar een kolom (17) die niet bestaat. In cel D18 krijg je dan de foutmelding #VERW!. b. Kies menu EXTRA, FOUTCONTROLE.

c. Kies vervolgens BEREKENINGSSTAPPEN WEERGEVEN.

Via de knop EVALUEREN wordt de formule stap na stap doorlopen. Op het moment dat je de boodschap ‘De volgende evaluatie resulteert in een fout.’ krijgt is de fout gelokaliseerd.



67

4.5.3 Aantekeningen toevoegen De werkbalk FORMULE CONTROLEREN bevat een knop NIEUWE OPMERKING die het mogelijk maakt om een notitie aan een cel toe te voegen. Ook het menu INVOEGEN bevat deze optie (OPMERKING). Opdracht 17. Een notitie aan de cel met het factuurnummer koppelen a. Selecteer cel H2 in het werkblad FACTUUR. b. Klik op de knop NIEUWE OPMERKING en typ de gewenste tekst in het tekstvak. Een rood driehoekje in de rechterbovenhoek van de cel, de opmerkingaanwijzer, geeft aan dat er een notitie toegevoegd is. De notitie wordt zichtbaar als de muiswijzer in de cel staat. TIP

4.6

Als je de opmerkingen altijd zichtbaar wil maken moet je in het dialoogvenster EXTRA, OPTIES, WEERGAVE de optie OPMERKINGEN & AANWIJZER aanvinken.

Grafische objecten om de opmaak te verfraaien De werkbalk TEKENEN bevat tal van interessante opmaakmogelijkheden.

TIP

68

Het begrip ‘tekstvak’ moet je hier nogal letterlijk opvatten: het is een vak met tekst. In feite komt dit overeen met het type Label uit Access. Een echt TEKSTVAK is een besturingselement dat aan een gegevensbron gekoppeld kan worden.



Opdrachten 18. De adresgegevens van de klant opmaken

a. Zet de werkbalk TEKENEN op het scherm. Kies AUTOVORMEN, BASISVORMEN en daaruit de rechthoek met afgeronde hoeken. b. Teken een rechthoek rond de adresgegevens. Het is mogelijk dat de adresgegevens verborgen worden achter de grijze achtergrond van de rechthoek. Selecteer de rechthoek door met de linkermuisknop op de rand te klikken en roep met de rechtermuisknop het snelmenu op.

Kies voor geen opvulling en een lijndikte van 1,5 punt. Klik vervolgens op de tab KENMERKEN en kies daarin VERPLAATSING EN FORMAAT GERELATEERD AAN CELLEN. De betekenis hiervan is dat het kader zich automatisch wijzigt als de rijhoogte of de kolombreedte verandert. c. Klik op de knop TEKSTVAK en teken een tekstvakje, zoals aangegeven in het voorbeeld, waarin je het woordje ‘klant’ zet. Maak de achtergrond grijs. Als het onderliggende kader in het tekstvak zichtbaar is, selecteer dan het tekstvak, roep het snelmenu op en kies hierin VOLGORDE, NAAR VOORGROND.



69

Kijk ook even naar de optie TEKSTVAK OPMAKEN en wijzig de instellingen naar eigen smaak. TIP

De verschillende tekenobjecten kun je selecteren met de selectiepijl op de werkbalk TEKENEN. Als deze pijl geselecteerd is dan heb je toegang tot alle objecten die aan het werkblad toegevoegd zijn. Je kunt niet meer aan de celinhoud van de cellen. Vergeet daarom niet om de selectiepijl af te klikken als je de celinhoud wilt wijzigen.

19. De lijn en het tekstvak onder de factuurkop Trek een dikke en een dunne lijn onder de factuurkop en zet in het midden een tekstvak met de tekst ‘Factuur’. 20. De betalingswijze

a. Zet in cel C51 ‘Naam’ en in cel C52 ‘Nummer’ en teken in de ruimte erachter telkens een dunne lijn. b. Teken hierrond een kader met afgeronde hoeken. c. Teken een tekstvak linksboven het kader met als inhoud ‘Betalingswijze’. d. Zet de werkbalk FORMULIEREN op het scherm en kies hieruit het keuzerondje.

70



e. Teken een keuzerondje onder het tekstvak ‘Betalingswijze’ en verander het bijbehorende label in ‘contant’. Doe hetzelfde met de keuzerondjes ‘Bancontact of Mister Cash’ en ‘cheque’. f. Geen enkel van de keuzerondjes mag geselecteerd zijn. Klik met de rechtermuisknop op het keuzerondje om een snelmenu op te roepen en kies hierin BESTURINGSELEMENT OPMAKEN, BESTURINGSELEMENT, WAARDE: NIET INGESCHAKELD.

TIP

4.7

KOPPELING MET CEL. Als je hier een celadres zet, dan zal daarin de waarde van het keuzerondje gezet worden als het geselecteerd is. Het eerste keuzerondje dat je aanmaakt heeft waarde 1, het tweede waarde 2, ...

Het firmalogo invoegen Figuren kunnen op verschillende manieren ingevoegd worden. Al naargelang de methode kunnen de figuren volledig opgeslagen worden in het werkblad, of kan er alleen maar een verwijzing naar de plaats van de figuur opgeslagen worden. In het laatste geval spreekt men van gekoppelde figuren. Gekoppelde figuren nemen zo goed als geen plaats in het werkblad in. 

INVOEGEN, AFBEELDING, UIT BESTAND Een bestand, bijvoorbeeld DIPLOMA.BMP, dat je zo invoegt wordt volledig toegevoegd aan het werkblad. In Word kun je met deze optie een bestand wel koppelen.



71



INVOEGEN, OBJECT

Je merkt dat hier wel een mogelijkheid is om een bestand te koppelen aan het werkblad. 

INVOEGEN, AFBEELDING, ILLUSTRATIE Het Taakvenster verschijnt met de optie ILLUSTRATIE INVOEGEN. Ofwel zoek je naar de figuur ‘Diploma’, ofwel kies je een nieuwe figuur, bijv. ‘Computer’ uit de Mediagalerie. TIP

De eerste keer dat je een illustratie toevoegt kun je een lijst laten opmaken van alle illustraties die op je pc aanwezig zijn. De volgende keren kun je uit die collecties je keuze maken.

Opdrachten 21. Voeg de afbeelding van het diploma, de computer of eventueel een andere afbeelding aan de factuur toe. Je merkt dat de afbeelding vrij groot is. Door met de hoeken te slepen kun je de figuur tot een gewenst formaat aanpassen. Verwijder de rand rond de figuur via het snelmenu, OBJECTEIGENSCHAPPEN. 22. Kopieer deze figuur naar PAINT, voeg de nodige tekst toe en bewaar ze onder de naam DIPLOMA.BMP of COMPUTER.BMP. Kopieer ze ook naar de factuur en verwijder de oorspronkelijke figuur. Verwijder opnieuw de rand rond de figuur.

72



4.8

De factuur beveiligen Opdracht 23. Beveilig de factuur zodanig dat alleen het factuurnummer, het klantnummer, de artikelnummers en de artikelaantallen gewijzigd kunnen worden.

4.9

De factuur afdrukken Als alles klaar is kun je eindelijk de factuur afdrukken.  Selecteer het af te drukken gebied en stel het afdrukbereik in via BESTAND, AFDRUKBEREIK, AFDRUKBEREIK BEPALEN.  Zorg ervoor dat alleen het gewenste gebied op één blad afgedrukt wordt via BESTAND, PAGINA-INSTELLING.  Een koptekst en een voettekst zijn niet nodig, rasterlijnen en rijen kolomkoppen ook niet.  Controleer de marges: 1 cm volstaat voor onder- en bovenmarge. TIP

Kies BEELD, PAGINA-EINDEVOORBEELD om het afdrukbereik te zien.

Opdracht 24. Vul gegevens in een factuur in, stel het afdrukbereik in en druk de factuur af.

4.10 Een macro opnemen en aan een knop toekennen Een macro is een verzameling van acties die meestal opgeroepen worden via een opdrachtknop. Je kunt een macro ‘opnemen’ of helemaal zelf intikken. Deze acties worden weggeschreven als Visual Basic-code in aparte modulebladen. Daardoor is het mogelijk om de code van een macro te wijzigen. De macro’s worden als programmacode bijgehouden in modulebladen die slechts bereikbaar zijn via de Visual Basic-editor. Opdracht 25. Zet op het werkblad FACTUUR een knop met als opschrift ‘Leeg’ die een macro LEEG_FACTUUR oproept om het klantnummer, de artikelnummers en de artikelaantallen te verwijderen. A. De macro opnemen a. Kies EXTRA, MACRO, NIEUWE MACRO OPNEMEN. Geef een zinvolle naam aan de macro. Zet geen spaties in de naam, gebruik liever het onderlijnteken!



73

In het werkblad verschijnt meestal de knop OPNAME STOPPEN. TIP

Als deze knop niet verschijnt, dan moet je via EXTRA, OPNAME STOPPEN de macro beëindigen.

b. Neem de macro op: Selecteer eerst alle cellen die leeggemaakt moeten worden. Klik vervolgens op DELETE. Beëindig ten slotte de macro met de knop OPNAME STOPPEN of via EXTRA, MACRO, OPNAME STOPPEN. TIP

Van de klantgegevens moet alleen het klantnummer verwijderd worden, zeker niet de formules waarmee de klantgegevens gezocht worden.

TIP

Tijdens het opnemen van een macro verschijnt in de statusbalk het woordje ‘Opname’.

TIP

Als je de macro-opname vergeet te stoppen, dan kan dat zeer rare gevolgen geven bij het uitvoeren van die macro. Stel dat je denkt dat de opname gestopt is en je voegt een opdrachtknop toe die je aan die macro toewijst. Bij het uitvoeren van deze macro zal die zichzelf blijven oproepen tot je een geheugenprobleem krijgt. Ondertussen staan er al wel meer dan 100 opdrachtknoppen op het werkblad.

B. De macro aan een knop toewijzen a. Zet via BEELD, WERKBALKEN de werkbalk FORMULIEREN op het scherm en teken een opdrachtknop op de gewenste plaats.

74



b. Vervolgens wordt gevraagd naar een macronaam die met deze knop verbonden moet worden. Kies in dit geval Leeg_Factuur. De lijst toont alle macro’s die al opgenomen zijn. Klik op OK als je klaar bent. c. Klik in de opdrachtknop om de knop een gepast opschrift te geven: ‘Leeg’. Wil je achteraf de naam van de knop wijzigen, klik dan met de rechtermuisknop op die knop en kies de optie TEKST BEWERKEN uit het snelmenu. Wil je het lettertype, de kleur, … wijzigen, kies dan de optie BESTURINGSELEMENT OPMAKEN.

4.11 Macrobeveiliging Een macrovirus is een soort computervirus dat opgeslagen wordt in een macro die zich in een bestand, sjabloon of invoegtoepassing bevindt. Je kunt bestanden tegen macrovirussen beveiligen door speciale antivirussoftware te installeren. Je kunt het risico van virusbesmetting van Office-bestanden verkleinen door het beveiligingsniveau voor macro’s in te stellen op HOOG of GEMIDDELD of te werken met digitale handtekeningen. De digitale handtekening wordt gebruikt als bewijs dat de macro afkomstig is van de ondergetekende en dat de macro niet gewijzigd is. Een macro met een digitale handtekening is te vergelijken met een verzegelde envelop. Hiermee wordt aangegeven dat de macro direct en ongewijzigd afkomstig is van de ontwikkelaar die de handtekening heeft gezet en dat met de VBA-code zeker niet geknoeid is. De beveiliging kun je wijzigen via menu EXTRA, MACRO, BEVEILIGING.

Als je een document opent waarin macro’s opgeslagen zijn, en de beveiliging staat op GEMIDDELD dan krijg je de volgende boodschap:



75

Als je klikt op de knop MACRO’S UITSCHAKELEN dan kunnen geen macro’s uitgevoerd of opgenomen worden.

4.12 Een menusysteem ontwerpen

Opdracht 26. Ontwerp in een leeg werkblad een menusysteem waarmee je vlot naar de verschillende werkbladen FACTUUR, KLANTEN en ARTIKELEN kunt schakelen. Het is ook nodig dat je vanuit deze bladen terug kunt schakelen naar het menu. a. Voeg een nieuw werkblad toe en noem dit MENU. “Jos Computers bvba” is ontworpen met WORD-ART via de werkbalk TEKENEN. De straat, de woonplaats en het telefoonnummer staan in een tekstvak. Ontwerp eventueel je eigen logo. b. Neem vier macro’s op om telkens één van de werkbladen FACTUUR, KLANTEN, ARTIKELEN of MENU te openen. Het is niet belangrijk vanuit welk werkblad je deze macro’s opneemt. Noem deze macro’s NAAR_FACTUUR, NAAR_KLANTEN, NAAR_ARTIKELEN en NAAR_MENU. Teken een opdrachtknop in het werkblad MENU en geef die knop een gepaste opmaak. Kopieer deze knop driemaal. Geef de verschillende knoppen achtereenvolgens het opschrift ‘Factuur’, ‘Klanten’, ‘Artikelen’ en ‘Einde’ en verbind de knoppen met de overeenkomstige macro’s. c. Selecteer de vier knoppen met SHIFT+LINKERMUISKNOP en kopieer ze naar de verschillende werkbladen. Je moet dan in elk werkblad het opschrift van de knop met de naam 76



van het werkblad vervangen door ‘Menu’ en vervolgens deze knop koppelen aan de macro NAAR_MENU. TIP

De macro EINDE, die het programma Excel afsluit, kun je niet opnemen: de code hiervoor moet je zelf in een module intikken. (Zie verder)

TIP

Het is veel handiger om één knop een volledige opmaak te geven en die knop vervolgens een aantal keren te kopiëren. De gekopieerde knoppen zijn dan even groot en hebben allemaal dezelfde opmaak.

Een knop kun je op twee verschillende manieren selecteren:  met de rechtermuisknop;  met de pijl uit de werkbalk TEKENEN. Als de pijl geactiveerd is kun je niet meer aan de celinhoud! TIP

Merk het verschil op tussen een knop selecteren en de tekst op de knop bewerken.

27. Voeg aan het werkblad KLANTEN nog twee knoppen toe. De ene sorteert de klanten volgens CODE, de andere volgens BEDRIJF. 28. Voeg aan het werkblad ARTIKELEN ook nog twee knoppen toe: de ene om de artikelen te sorteren volgens CODE en de andere om te sorteren volgens OMSCHRIJVING.

4.13 Macro’s, programmacode en modulebladen 4.13.1 Modulebladen Opdracht 29. Waar zit de programmacode van de macro LEEG_FACTUUR? a. Roep de Visual Basic-editor op via ofwel:  EXTRA, MACRO, VISUAL BASIC-EDITOR;  de toetsencombinatie ALT+F11;  de knop VISUAL BASIC-EDITOR op de werkbalk VISUAL BASIC



Activeer de projectverkenner door in de titelbalk van de projectverkenner te klikken of door BEELD, PROJECTVERKENNER te kiezen:



77

Dubbelklik achtereenvolgens op de module MODULE1 Bekijk de macrocode van:  Naar_Factuur:  Naar_Artikelen;  Naar_Klanten.

b. Wijzig ten slotte de naam van deze module (= Module 1) in ‘Proc_Factuur’ via het venster EIGENSCHAPPEN en sluit vervolgens het eigenschappenvenster.

c. Indien MODULE 2 zou bestaan, kopieer dan de inhoud hiervan naar PROC_FACTUUR.

78



Opdracht 30. Een module toevoegen a. Klik op de map MODULES, roep het snelmenu op met de rechtermuisknop en kies hierin INVOEGEN, MODULE.

b. Geef de nieuwe module een gepaste naam: Proc_algemeen. TIP

Zo’n module kun je gebruiken om macro’s in te bewaren die voor elk werkblad bruikbaar zijn. Je moet dan altijd de macrocode naar deze module kopiëren.

4.13.2 Het volgende factuurnummer genereren Opdracht 31. Voeg aan de code van de macro NIEUW_FACTUUR één lijntje toe en het factuurnummer wordt automatisch verhoogd als je de gegevens van de vorige factuur verwijdert. Sub Nieuw_Factuur() Range("C18,B21:C40").Select Selection.ClearContents [H2] = [H2] + 1 Range("C18").Select End Sub

Range("H2,C18,B21:C40") moet gewijzigd worden in Range("C18,B21:C40"). Waarom? Verklaar de betekenis van [H2] = [H2] +1. Via RANGE("C18").SELECT zet je de cursor klaar op de plaats waar je het klantnummer intikt. Als de beveiliging van de factuur aangezet is en cel H2 is geblokkeerd, dan kun je de inhoud van die cel niet wijzigen, ook niet via de macro NIEUW_FACTUUR. In dat geval moet je in de macro de beveiliging afzetten, het factuurnummer verhogen en de beveiliging terug opzetten. ActiveSheet.Unprotect Range("C18,B21:C40").Select Selection.ClearContents



79

[H2] = [H2] + 1 ActiveSheet.Protect

4.13.3 Excel met de macro “Einde” afsluiten Opdracht 32. Een knop “Einde” toevoegen die de toepassing Excel afsluit a. Neem een macro “Einde” op die de alleen maar de cel A1 (van een willekeurig werkblad) selecteert en wijs deze macro toe aan de knop “Einde” in het werkblad MENU. b. Ga naar de programmacode in het overeenkomstige moduleblad en wijzig de inhoud van de macro EINDE als volgt: Sub Einde() Range("A1").Select Application.Quit End Sub

‘ verwijder deze regel ‘ voeg deze regel toe

c. Bewaar de werkmap en test de macro. 4.13.4 Een knop toevoegen om de factuur af te drukken Probleemstelling Zet in het werkblad FACTUUR een knop, met als opschrift ‘Afdrukken’. Als je op deze knop klikt moet er een dialoogvenster verschijnen met de vraag of je een afdrukvoorbeeld wilt. Het venster moet tevens de knoppen JA, NEE en ANNULEREN bevatten.  JA: het afdrukvoorbeeld wordt getoond;  NEE: de factuur wordt afgedrukt;  ANNULEREN: de gebeurtenis wordt geannuleerd en er gebeurt niets.

Probleemanalyse Om dit probleem op te lossen heb je drie macro’s nodig: AFDRUKKEN, AFDRUKVOORBEELD en AFDRUK_OP_PRINTER. De knop ‘Afdrukken’ roept de macro AFDRUKKEN op. In deze macro wordt een dialoogvenster opgeroepen met als vraag ‘Wil je een afdrukvoorbeeld?’ en de knoppen JA, NEE en ANNULEREN worden getoond. De knop JA roept de macro AFDRUKVOORBEELD op, de knop NEE roept de macro AFDRUK_OP_PRINTER op en de knop ANNULEREN heeft geen actie. In zowel de macro AFDRUKVOORBEELD als de macro AFDRUK_OP_PRINTER moet je eerst het af te drukken gebied selecteren en daarna de gewenste afdrukopdracht geven.

80



Schema macro Afdrukken Dialoogvenster: ‘Afdrukvoorbeeld tonen?’ Ja Nee Annuleren macro AFDRUKVOORB. macro AFDRUK_PAPIER geen actie Programmacode van de verschillende macro’s Sub Afdrukvoorbeeld() Range("A1:H57").Select Selection.PrintPreview Range("H2").Select End Sub

het af te drukken gebied selecteren het afdrukvoorbeeld tonen de selectie ongedaan maken

Je kunt de vorige macro veel korter schrijven zoals ook blijkt uit de volgende macro: Sub Afdruk_op_printer() Range("A1:H57").PrintOut End Sub

het aangegeven gebied via de printer afdrukken

Sub Afdrukken() Dim intKeuze As Integer intKeuze = MsgBox("Wil je een afdrukvoorbeeld ?", vbYesNoCancel, "De factuur afdrukken") Select Case intKeuze Case vbCancel 'case 2 Case vbYes 'case 6 Call Afdrukvoorbeeld Case vbNo 'case 7 Call Afdruk_op_printer End Select End Sub

De macro’s AFDRUKVOORBEELD() en AFDRUK_OP_PRINTER() kunnen in de macro AFDRUKKEN() geïntegreerd worden. Sub Afdrukken() Dim intKeuze As Integer intKeuze = MsgBox("Wil je een afdrukvoorbeeld ?", vbYesNoCancel, "De factuur afdrukken") Range("A1:H57").Select Select Case intKeuze Case vbCancel 'case 2 Case vbYes 'case 6 Selection.PrintPreview Case vbNo 'case 7 Selection. PrintOut End Select Range("H2").Select End Sub

Verklaring programmacode Afdrukken() Dim intKeuze As Integer

Er wordt in het werkgeheugen een plaatsje gereserveerd om tijdelijk een waarde te bewaren. Daartoe moet je aan die geheugenplaats een naam toekennen, bijv. ”intKeuze” en moet je zeggen welk type van waarde er zal bewaard worden. In dit geJ. Gils – E. Goossens


81

val is het type Integer, dit wil zeggen een geheel getal dat ongeveer tussen -32 000 en + 32 000 ligt. intKeuze = MsgBox("Wil je een afdrukvoorbeeld ?", vbYesNoCancel, "Gegevens afdrukken")

De functie MSGBOX() roept een dialoogvenster op dat op initiatief van de gebruiker gesloten wordt. Syntaxis: MSGBOX(bericht[, welke knoppen tonen] [, titel] [, helpfile, context]) Kenmerkend voor een functie is dat ze na oproep een waarde teruggeeft. In dit geval is die waarde afhankelijk van de knop waarmee je het dialoogvenster verlaat en wordt ze in de variabele intKeuze bewaard. Hoe je het dialoogvenster verlaat is afhankelijk van de knoppen die getoond worden: dit wordt in de tweede parameter aangegeven met een getal of met de naam van een constante. Constante Waarde Beschrijving vbOKOnly 0 Uitsluitend de knop OK weergeven. vbOKCancel 1 De knoppen OK en Cancel weergeven. vbAbortRetryIgnore 2 De knoppen Abort, Retry en Ignore weergeven. vbYesNoCancel 3 De knoppen Yes, No en Cancel weergeven. vbYesNo 4 De knoppen Yes en No weergeven. vbRetryCancel 5 De knoppen Retry en Cancel weergeven. vbCritical 16 Het pictogram Critical Message weergeven. vbQuestion 32 Het pictogram Warning Query weergeven. vbExclamation 48 Het pictogram Warning Message weergeven. vbInformation 64 Het pictogram Information Message weergeven. Als je het dialoogvenster verlaat geeft de functie een waarde aan intKeuze. Constante vbOK vbCancel vbAbort vbRetry vbIgnore vbYes vbNo

Waarde 1 2 3 4 5 6 7

Beschrijving OK Cancel Abort Retry Ignore Yes No

Deze waarde of constante wordt in de ‘Select Case’-structuur gebruikt om de gewenste macro op te roepen. TIP

Er bestaat ook een actie MSGBOX. Een actie is geen functie en kan daarom geen waarde teruggeven.

Een uitgebreide hulp krijg je altijd als je op het codewoord klikt en vervolgens F1 drukt. Test dit even om meer uitleg te krijgen over de functie MSGBOX(). Ook het snelmenu, m.a.w. de rechtermuisknop geeft een bondige hulp.

82



TIP

Je kunt de waarde van de functie ook rechtstreeks in een cel zetten, bijv. cel H1, dan hoef je zelfs geen variabele te declareren. [H1] = MsgBox("Wil je een afdrukvoorbeeld ?", vbYesNoCancel, "Gegevens afdrukken") Select Case [H1] .. End Select

Opdracht 33. Voeg de knop ‘Afdrukken’ aan het werkblad FACTUUR toe. Tik de programmacode van de macro’s rechtstreeks in de module PROC_FACTUUR in en verbind de knop ‘Afdrukken’ met de macro AFDRUKKEN.

4.14 De klant via een keuzelijst kiezen Opdracht 34. De naam van de klant via een keuzelijst kiezen en het klantnummer automatisch in cel C18 invullen a. Ga naar het werkblad FACTUUR, zet de werkbalk WERKSET BESTURINGSELEMENTEN op het scherm, teken een keuzelijst met invoervak (ComboBox) en roep het eigenschappenvenster van deze keuzelijst op. Kies hierin de optie GECATEGORISEERD.



83

b. Via het eigenschappenvenster moet je een verbinding maken met het werkblad KLANTEN waaruit de naam van de klant gekozen wordt. Vervolgens moet het nummer van de klant in cel C18 van het werkblad FACTUUR bewaard worden.

Overige eigenschappen: (NAME)

Elk besturingselement heeft een naam: cboKlant. Via deze naam kun je vanuit VBA de eigenschappen van de keuzelijst wijzigen.

LISTFILLRANGE

Uit welke tabel moet de klant gekozen worden: Klanten!$A$2:$H$50 of het bereik Klantenlijst

LINKEDCELL

In welke cel moet je de gekozen waarde opslaan: $C$18

Gegevens:

84

BOUNDCOLUMN (Afhankelijke kolom)

Deze eigenschap bepaalt welk element uit die rij als de waarde van het besturingselement opgeslagen wordt. Dit is ook de waarde die in de LINKEDCEL getoond wordt. Kolommen worden genummerd vanaf 1. Kies 1 omdat de eerste kolom de gewenste waarden, namelijk de klantnummers, bevat.

COLUMNCOUNT (Aantal kolommen)

Met deze eigenschap bepaal je het aantal kolommen dat in de keuzelijst weergegeven wordt. Kies in dit geval 2, namelijk het klantnummer en de klantnaam.

COLUMNWIDTHS (Kolombreedten)

Geeft de breedte van de kolommen aan. De kolommen worden gescheiden door het lijstscheidingsteken [;]. Vul hier in: 1,5 cm; 3 cm. Deze waarden worden automatisch in punten omgezet: 42,5 pt; 85,05 pt. Vul 0 in als je een kolom wilt verbergen!

LISTROWS (Aantal rijen)

Bepaalt hoeveel rijen in de lijst getoond worden: 5.



TEXTCOLUMN

Deze eigenschap zegt welke kolom getoond wordt als de combobox niet geselecteerd is. Zet hier de waarde 2 zodat altijd de naam van de klant getoond wordt. De waarde van de eigenschap TEXTCOLUMN voor de eerste kolom is 1, de waarde van de tweede kolom is 2, enzovoort.

TEXT

Deze eigenschap bevat de getoonde tekst als de keuzelijst niet geselecteerd is. De waarde wordt door TEXTCOLUMN bepaald.

PRINTOBJECT

Zet deze waarde op FALSE als de keuzelijst niet afgedrukt moet worden.

c. Sluit de ontwerpmodus af via de knop ONTWERPMODUS AFSLUITEN in de werkset en test de werking van de keuzelijst. TIP TIP

Als je de combobox wilt wijzigen moet je altijd via de werkset naar de ontwerpmodus. Zet de keuzelijst op de klantnaam boven cel F11: de keuzelijst wordt immers toch niet afgedrukt.



85

Besluit Om het factuurprobleem op te lossen wordt gebruik gemaakt van minstens drie werkbladen: een werkblad waar de factuur ontworpen wordt, een werkblad met de artikelgegevens en een werkblad met de klantgegevens. Als toemaatje kun je nog een werkblad toevoegen waarin een menusysteem uitgewerkt wordt. Drie functies zijn hier heel belangrijk:  Met de functie Vert.zoeken() worden gegevens in de verschillende tabellen opgezocht. Vooral de vierde parameter (waar/onwaar) is van belang omdat die bepaalt of het gezochte mag benaderd worden of niet. Een gevolg hiervan is dat een tabel wel (waar) of niet (onwaar) gesorteerd moet zijn.  De Als()-functie is handig om beslissingen te nemen. Als het klantnummer leeg is dan moeten geen klantgegevens opgezocht worden.  De functie Afronden() gebruik je om getallen tot een bepaald aantal cijfers na de komma af te ronden. Aan een bereik kan een naam toegekend worden. Dit is vooral handig in formules. Kies daarom zinvolle namen voor een bereik. Grafische objecten zoals lijnen, kaders en tekstvakken zijn bereikbaar via de werkbalk TEKENEN, andere objecten zoals opdrachtknoppen, labels en keuzerondjes vind je terug op de werkbalk FORMULIER. Via de selectiepijl uit de werkbalk TEKENEN kun je alle objecten selecteren. De eigenschap Volgorde bepaalt welke objecten zichtbaar zijn als ze bovenop mekaar liggen. Ook afbeeldingen zijn objecten. Zij worden niet ter plaatse getekend, maar ingevoerd vanuit een bestand dat al op de schijf staat. Er is een verschil tussen invoegen en koppelen. Met behulp van macro’s die verbonden zijn met opdrachtknoppen kun je ingewikkelde taken op een meer eenvoudige wijze uitvoeren. Een macro is een verzameling van opdrachten die altijd op dezelfde manier uitgevoerd worden. De code van een macro is bereikbaar via de Visual Basic-editor en kun je zelf ook wijzigen.


inzicht hebben in het totale ontwerp van een factuur; de formules Vert.zoeken(), Als() en Afronden() zinvol toepassen; een bereik een naam geven en die naam in formules gebruiken; een tabel volgens bepaalde sleutels sorteren; grafische objecten en besturingselementen gebruiken om een werkblad te verfraaien; met behulp van opdrachtknoppen bepaalde taken, die in macro’s vastgelegd zijn, oproepen en de programmacode van de macro’s terugvinden en aanpassen; een zelf ontworpen werkblad klaarmaken om af te drukken.

Opdrachten 35. Stel dat de regering beslist om het BTW-tarief van 6% op te trekken tot 7%. Kun je deze wijziging eenvoudig aanpassen in het ontwerp? 36. Is het een probleem als er een BTW-code 15 % zou bijkomen? Kun je het oplossen? 86



Hoofdstuk 5

5.1

Gegevens analyseren en grafieken maken

Soorten grafieken



Een kolomdiagram geeft de evolutie van één of meer variabelen weer. Het wordt voornamelijk gebruikt als de waarden die op de X-as uitgezet worden niet continu veranderen.



Een cirkeldiagram geeft de veranderingen van één variabele ten opzichte van de totale verandering weer. Het is altijd een procentuele verhouding ten opzichte van het totaal. Hoeveel winst was er in het jaar 93 ten opzichte van de totale winst in de loop der jaren?



Een lijndiagram geeft de wijzigingen van een variabele weer over bepaalde intervallen. Een lijndiagram wordt voornamelijk gebruikt om de trend van een verandering te bepalen. Uit de vorm van de doopcurve kun je een idee krijgen over de inkomsten die je het volgende jaar zou kunnen verwachten.



Een XY- of spreidingsgrafiek toont het verband tussen twee variabelen die onderling van mekaar afhankelijk zijn. De ene variabele wordt op de X-as uitgezet, de andere op de Y-as. Bijvoorbeeld: in een smeltcurve zet je de temperatuur uit in functie van de tijd.

Het onderstaande voorbeeld toont dat er geen verschil tussen een spreidings- en een lijngrafiek is als de waarden van de X-as continu met dezelfde intervallen veranderen.



87

De schaalverhouding van de X-as klopt helemaal niet meer bij de lijngrafiek. Daardoor worden de curven vervormd.

De op de X-as uitgezette schaal is bij de lijngrafiek gebaseerd op het aantal waarden dat in de tabel opgenomen werd en niet op de afstand tussen die waarden. Bij de XY-grafiek daarentegen wordt de X-as niet verdeeld op basis van het aantal waarnemingen maar op basis van de afstand tussen de kleinste en de grootste waarde. Die afstand wordt gelijkmatig verdeeld en de waarden van sinus en cosinus worden op die gelijkmatige schaal uitgezet.

88



5.2

Zelf een frequentietabel samenstellen

5.2.1 Probleemstelling Van twintig gezinnen telt men het aantal kinderen: gezin aantal

1 2

2 1

3 4

4 0

5 5

6 3

7 2

8 0

9 10 11 12 13 14 15 16 17 18 19 20 5 1 2 3 2 3 6 4 3 7 2 4

Ontwerp een turftabel waarmee je automatisch het aantal gezinnen met 0, 1, 2, 3, … tot en met 9 kinderen telt. De resultaten vormen de basis van een frequentietabel. Ten slotte zet je in een kolomgrafiek de resultaten van de absolute frequentie (hoeveel gezinnen) uit t.o.v. het waarnemingsgetal (aantal kinderen). 5.2.2 Probleemanalyse Bekijk het afdrukvoorbeeld onder de titel ‘Oplossing’.  Alleen de getallen in A8:B27 worden werkelijk ingetypt, m.a.w. het nummer van het gezin en het aantal kinderen in dat gezin.  De reeks F3:O3 begint met het kleinste waarnemingsgetal, in dit geval 0, en stopt automatisch bij het hoogste waarnemingsgetal, in dit geval 7. De hele rij wordt automatisch gegenereerd met behulp van de laagste en de hoogste waarde in het bereik B8:B27. Je mag aannemen dat er hoogstens 10 opeenvolgende waarnemingsgetallen zijn waarbij het verschil tussen de kleinste en hoogste waarde maximaal 9 eenheden is. In dit geval mag in de cellen N3 (waarde 8) en O3 (waarde 9) geen waarde getoond worden.  Het turven gebeurt in de turftabel. Zet in F8 de juiste formule en kopieer die zowel naar rechts als naar onder. Reserveer nog wat ruimte zodat eventueel ook gezinnen met 8 en 9 kinderen automatisch geteld kunnen worden. Test dit even uit door de gezinnen met 0 kinderen te vervangen door 9 kinderen. De formule in F8 luidt als volgt: als het aantal kinderen uit het eerste gezin (B8) gelijk is aan het kleinste waarnemingsgetal (F3) zet dan in cel F8 de waarde 1, anders de waarde 0.  Het aantal gezinnen per waarnemingsgetal is gelijk aan de som van de waarden in de rijen 8 tot 27 binnen de kolom van dat waarnemingsgetal. Voor het waarnemingsgetal 1 (1 kind, cel G3) is het aantal gezinnen gelijk aan de som van de waarden in F8:F27.  Zet ten slotte twee opdrachtknoppen op het werkblad. De ene knop, met als opschrift ‘Wijzigen’, roept de macro ‘Wijzigen’ op die bij de twee gezinnen zonder kinderen het aantal kinderen wijzigt in 9. De andere knop met als opschrift ‘Herstellen’ roept de macro ‘Herstellen’ op die het aantal kinderen bij deze gezinnen terug op 0 zet. Blijven de resultaten in de frequentietabel correct?  Als later ook de grafiek gemaakt is kun je de turftabel F8:O27 voorbij kolom P verplaatsen en hem verbergen.



89

5.2.3 Oplossing Opdracht 1. De frequentietabel samenstellen a. Open een nieuwe werkmap, bewaar ze als GRAFIEKXP.XLS en werk de hoger vermelde probleemstelling uit in een nieuw werkblad aan de hand van de figuur hieronder.

b. Bewaar het werkblad onder de naam FREQ_TABEL. c. Schrijf hieronder de formules die je in de volgende cellen gebruikt hebt. Cel Formule [F3] [G3] [F4] [F8] [B29] [B30]

5.3

Gegevens door Excel laten analyseren Microsoft Excel bevat een verzameling analyse-hulpmiddelen, de Analysis ToolPak, waarmee je tijd kunt besparen bij het ontwikkelen van complexe statistische of technische analyses.

5.3.1 Analysis Toolpak installeren en activeren Als de opdracht GEGEVENSANALYSE niet in het menu EXTRA voorkomt, moet je de invoegtoepassing Analysis ToolPak installeren met behulp van het programma SETUP. Na de installatie moet je de invoegtoepassing Analysis ToolPak activeren in het dialoogvenster EXTRA, INVOEGTOEPASSINGEN.

90



Als Analysis ToolPak niet in het dialoogvenster INVOEGTOEPASSINGEN vermeld wordt, klik je op BLADEREN en zoek je het bestand ANALYS32.XLL. Normaal gezien zit dit bestand in de map C:\Program Files\Microsoft Office\Office11\Biblio\Analysis. 5.3.2 Een frequentietabel genereren Voordat je een analyse-hulpmiddel kunt gebruiken, moet je de te analyseren gegevens in kolommen of rijen in het werkblad rangschikken.  het invoerbereik is een kolom of een rij met de waarnemingsgetallen, in dit geval de kolom B met het aantal kinderen per gezin;  het verzamelbereik is de kolom of de rij waarin de verschillende mogelijke waarden van de waarnemingsgetallen in oplopende volgorde opgegeven worden, in dit geval zou dat F3:N3 kunnen zijn. Als je het verzamelbereik weglaat, wordt automatisch een verzameling van vijf grenswaarden gegenereerd die gelijkmatig verdeeld is tussen de minimum- en de maximumwaarden. Opdracht 2. Een frequentietabel met een histogram genereren a. Maak het werkblad FREQ_TABEL actief. b. Kies EXTRA, GEGEVENSANALYSE, HISTOGRAM.



91

c. Met het dialoogvenster HISTOGRAM kun je afzonderlijke en cumulatieve frequenties berekenen voor een cellenbereik met gegevens en grenswaarden. Je kunt bepalen hoe vaak een bepaalde waarde in een gegevensverzameling voorkomt.

d. Klik op OK als je klaar bent en vergelijk het resultaat met de waarden die je zelf berekend hebt. Vergeet niet de optie Grafiek maken aan te vinken! Noem dit werkblad ‘Histogram’. De grafiek kun je achteraf nog een gepaste opmaak geven. e. Herhaal punt b en c, maar vul het verzamelbereik niet in. Vodoet het resultaat nog aan je verwachting?

5.4

Een kolomgrafiek via de Wizard Grafieken ontwerpen Opdracht 3. De gegevens uit de frequentietabel grafisch voorstellen

Stap 1  

92

Open het werkblad FREQ_TABEL in de werkmap GRAFIEKXP.XLS. Klik op de knop WIZARD GRAFIEKEN of kies menu INVOEGEN, GRAFIEK.





Kies het gewenste type en subtype. Zoals je merkt staat er een uitgebreide keuze grafieken ter beschikking. Kies het type KOLOM en kies daarin de gegroepeerde kolom. Klik ten slotte op VOLGENDE. (Wil je de wizard afbreken, klik dan op ANNULEREN.)

Nu op VOLTOOIEN klikken heeft weinig zin omdat er nog geen gegevensgebied geselecteerd is. Indien er wel een gegevensgebied geselecteerd zou zijn, zorgt Excel voor de standaardgrafiek met standaardinstellingen, zoals diagramtype, grafiektitel, labels, legenda, … als je op VOLTOOIEN klikt en zo beëindig je de wizard. Stap2 Selecteer de tab REEKS om de gegevensreeksen zelf in te geven.



93



Je kunt het gegevensbereik rechtstreeks intikken of je klikt op de knop om het gegevensbereik aan te duiden met de muiswijzer. Het oorspronkelijke dialoogvenster verdwijnt en er verschijnt dan een ander klein dialoogvenster.

Sleep met de muiswijzer over de variabelen en de waarden die je in een grafiek wilt uitzetten. Je merkt dat de juiste waarden van het gegevensbereik ingevuld worden in het dialoogvenster. TIP

Merk op dat de naam van het werkblad gekoppeld wordt aan het bereik: =Freq_Tabel!$F$4:$O$4. Dit heeft als voordeel dat je de grafiek in om het even welk werkblad kunt neerzetten. Dit kan ook een nadeel zijn want als je de grafiek met de gegevens naar een ander werkblad kopieert dan blijven de verwijzingen in de grafiek toch naar het oorspronkelijk werkblad verwijzen.

TIP

Zorg dat de rij die de labels voor de X-as bevat altijd vooraan in het bereik staat als je Excel automatisch de gegevensreeksen laat kiezen.

Stap3



Geef aan je grafiek een titel. Typ [Frequentie van het aantal kinderen per gezin] Druk niet op ENTER want dan beëindig je de Wizard!



Welke titel komt bij de X-as? Klik in het tekstvak bij Categorie (X).

94



Typ [Aantal kinderen] 

Welke titel komt bij de Y-as? Klik in het tekstvak bij Waarde (Y). Typ [Aantal gezinnen]



Vervolg de Wizard door op VOLGENDE te klikken.

Stap4

Hier kun je kiezen of je de grafiek als een nieuw blad in de map wilt bewaren of dat je de grafiek als een object aan een werkblad wilt toevoegen; dit hoeft niet noodzakelijk het huidige werkblad te zijn. Het is de bedoeling dat je de grafiek toevoegt aan het werkblad FREQ_TABEL. Klik ten slotte op VOLTOOIEN om de wizard te beëindigen. Je merkt dat de grafiek in het werkblad gezet wordt. Als de grafiek op een nogal ongelukkige plaats staat kun je hem verplaatsen door de muiswijzer in de grafiek te zetten en vervolgens met de linkermuisknop te slepen. Vergeet de werkmap niet te bewaren. TIP Als je snel een grafiek wilt ontwerpen, selecteer dan eerst het gegevensbereik, roep vervolgens de wizard GRAFIEKEN op, kies het juiste lijntype en klik op VOLTOOIEN. TIP Als je een XY-grafiek wilt voor een oefening van wiskunde, fysica, … kies dan het grafiektype SPREIDING.

5.5

Wijzigingen in een grafiek aanbrengen Het grafiekgebied is een object waarop tal van andere objecten, zoals de legenda, de gegevensreeksen, de astitels, het tekengebied, … geplakt zijn. Al deze objecten zijn afzonderlijk te bewerken. Het volstaat om ze met de linkermuisknop aan te klikken en vervolgens met de rechtermuisknop het snelmenu op te roepen. TIP

Als je één bepaald object uit de grafiek selecteert, kun je met de pijltoetsen de selectie naar een ander object verplaatsen. Dit is handig om in een kleine grafiek toch de y-as te kunnen selecteren.



95

TIP

De afgebeelde vensters hebben voornamelijk betrekking op de lijnen spreidingsgrafiek SIN_COS2.

Raadpleeg HELP als je meer wilt weten over de manier waarop je de verschillende objecten kunt wijzigen. 

grafieken opmaken

de rand van de grafiek opmaken, het lettertype van alle afgebeelde letters en getallen wijzigen, instellen of het object mee afgedrukt moet worden, het object blokkeren om het achteraf te kunnen beveiligen, …



grafiektype wijzigen

de mogelijkheid om een ander grafiektype te kiezen



brongegevens wijzigen

gegevensreeksen toevoegen, verwijderen, wijzigen, …



grafiekopties

de volgende objecten kunnen wel of niet getoond worden: de grafiektitels, de assen, de rasterlijnen, de legenda, de gegevenslabels



as opmaken

het patroon, de schaal, het lettertype, de getalopmaak en de uitlijning van een as wijzigen



gegevensreeks opmaken

hoe moet de grafieklijn er uitzien?

96



Opdracht 4. Wijzig de frequentiegrafiek als volgt:

5.6

Een trendlijn aan een grafiek toevoegen

5.6.1 Probleemstelling Volgende maand moeten de begrotingscijfers ingediend worden, ook wat het elektriciteitsverbruik betreft. Aan de hand van de cijfers van de laatste 19 jaar kan een prognose gemaakt worden voor het verbruik van volgend jaar. 5.6.2 Probleemanalyse Je zet in een lijngrafiek het verbruik uit t.o.v. het jaar. Excel biedt de mogelijkheid om een trendlijn toe te voegen. Trendlijnen worden gebruikt voor de bestudering van voorspellingsvraagstukken, ook wel regressie-analyse genoemd. Met regressie-analyse kun je een trendlijn in een grafiek uitbreiden naar de toekomst of naar het verleden om een trend aan te duiden.



97

Maak eerst de grafiek. Selecteer dan de lijn die het verbruik weergeeft en roep het snelmenu op. Klik hierin op de optie TRENDLIJN TOEVOEGEN. Kies LINEAIR als trendtype en geef in het dialoogvenster OPTIES aan dat je een voorspelling voor de twee volgende jaren wilt doen. Indien je het wenst kun je deze lijn een andere naam geven, in plaats van de voorgestelde naam: ‘Lineair (Verbruik)’. 5.6.3 Oplossing

Opdracht 5. Het elektriciteitsverbruik van de jaren 2002 en 2003 voorspellen a. Open een nieuw werkblad in de werkmap GRAFIEKXP.XLS en noem het TRENDLIJN. b. Neem de waarden in de kolommen A en B volledig over. De reeks met de jaartallen hoef je niet volledig in te tikken! c. Ontwerp de afgebeelde grafiek met trendlijn en tracht de getoonde opmaak te benaderen. Welk verbruik verwacht je in het jaar 2002? TIP

98

Ook met de Analysis ToolPak kun je een regressie doen. De resultaten daarvan zet je bij voorkeur in een nieuw werkblad.



Besluit Een grafische voorstelling van cijferwaarden kan verhelderend werken. Maar niet elke toepassing vraagt dezelfde soort grafiek. Een cirkelgrafiek is bijvoorbeeld zinvol om de samenstelling van een portefeuille weer te geven, een lijngrafiek kan de evolutie van een veranderlijke over gelijke intervallen tonen, XY-grafieken tonen het verband tussen twee variabelen. In de wiskunde gebruik je vaak XY-grafieken, geen lijngrafieken, omdat je meestal een verband hebt tussen twee variabelen (X en Y). Het grafiekgebied is een object waarop tal van andere objecten, zoals de legenda, de gegevensreeksen, de astitels, het tekengebied, … geplakt zijn. Al deze objecten zijn afzonderlijk te bewerken. Met behulp van een trendlijn kun je ramingen naar de toekomst doen. Microsoft Excel bevat een verzameling analyse-hulpmiddelen, de Analysis ToolPak, waarmee je complexe statistische of technische analyses kunt doen. Een voorbeeld daarvan is het histogram waarmee automatisch een frequentietabel ontworpen kan worden. Een ander voorbeeld is regressie.


verschillende grafieken gebruiken en omschrijven voor welke toepassingen ze geschikt zijn; de verschillende objecten van een grafiek oordeelkundig opmaken; de gepaste grafische voorstelling kiezen voor een bepaald probleem; een bestaande grafiek wijzigen en een gepaste opmaak geven; gegevens met behulp van een rekenbladprogramma analyseren; commentaar op een grafiek aanbrengen, opmaken en wijzigen; de verschillende objecten van een grafiek afzonderlijk bewerken.

Opdrachten 6. Het verloop van de sinus en cosinus In een van de vorige voorbeelden werd het verloop van de sinus en cosinus getoond in functie van het aantal graden. Voeg een nieuw werkblad, SINUS, toe aan de werkmap GRAFIEKXP.XLS. Maak een tabel waarin de sinus en de cosinus berekend worden als de hoek  van 0° tot 540° verandert met stappen van 30°. Gebruik deze waarden om het verloop van de sinus en de cosinus grafisch voor te stellen. De functies Sin() en Cos() vragen een argument in radialen. Gebruik de functie Radialen() om de 60-delige graden naar radialen om te zetten. 7. De smeltkromme van een stof Tijdens een proef in het labo Natuurwetenschappen het je een vaste stof verwarmd. Op regelmatige tijdstippen heb je de temperatuur van de stof genoteerd. De tijdsintervallen zijn niet altijd even groot. Zet deze cijfers in een grafiek en noem het werkblad SMELTCURVE. Tracht het bijgevoegde voorbeeld zo goed mogelijk te benaderen. Welk type grafiek moet je hier gebruiken? J. Gils – E. Goossens


99

8. Vergelijking tussen een benzine- en een dieselwagen De firma Jos Computers bvba wenst voor een van haar personeelsleden een nieuwe personenwagen aan te kopen. Wordt het een benzine- of een dieselwagen? Daarom is een vergelijking gemaakt tussen allebei de wagens. Zo is bij de aankoop een diesel merkelijk duurder, ook de wegenbelasting is een flink stuk hoger maar anderzijds is de brandstof goedkoper. Deze vergelijkende gegevens vind je onder de titel ‘Opgave’.

Om het probleem op te lossen moet je een onderscheid maken tussen de vaste en de veranderlijke kosten. De vaste kosten zijn de kosten die jaarlijks terugkomen en die onafhankelijk van het aantal afgelegde kilometers zijn. De veranderlijke kosten zijn de kosten die wel afhankelijk zijn van het aantal afgelegde kilometers: hoe meer kilometers, hoe hoger de kosten. Tot de vaste kosten behoren onder andere de afschrijving, de wegenbelasting en de verzekering. De veranderlijke kosten worden bepaald door het verbruik en de onderhoudskosten: Bereken de kilometerprijs per interval van 2 500 km tussen 2 500 en 22 500 km, zowel voor benzine en diesel en zet deze waarden in een lijngrafiek. Tracht uit de grafiek af te leiden vanaf welk jaarlijks kilometersaantal een benzine- of dieselwagen goedkoper wordt. Zet je naam op dit werkblad, bewaar het onder de naam AUTO en druk het af.

100



9. Een leerling heeft op 15 toetsen punten gekregen. Het maximum aantal punten is 10. Probeer hiermee de onderstaande grafiek. Noem dit werkblad PUNTEN.



101

Hoofdstuk 6

6.1

Afschrijvingen

Probleemstelling Duurzame investeringsgoederen kun je afschrijven. Dit wil zeggen dat je de kosten van investeringsgoederen over verschillende jaren kunt spreiden. Daarbij kun je uitgaan van de levensduur van een product Een auto wordt bijvoorbeeld meestal in vijf jaar afgeschreven. Voor computers is de minimale afschrijvingstermijn drie jaar, maar je kan een computer ook op vier of vijf jaar afschrijven. Voor bepaalde goederen is er na de afschrijving nog een restwaarde. Excel beschikt over functies om een lineaire en een degressieve afschrijving te berekenen. Nochtans kun je de methode van degressieve afschrijving zoals in Excel voorgesteld, volgens de Belgische wetgeving niet volledig gebruiken. De lineaire afschrijving zonder restwaarde schrijft het volledige bedrag van een aankoop over een bepaalde periode af. Kenmerkend is dat het afschrijvingsbedrag voor elke periode hetzelfde is. Je koopt bijvoorbeeld een sportwagen van € 50 000,00 en wilt deze wagen over een periode van 5 jaar afschrijven. Je deelt de aanschafprijs door het aantal jaar dat je de auto wilt afschrijven. Het resultaat hiervan is het jaarlijkse bedrag dat je als kost van je belastbaar inkomen kan aftrekken. Dus: € 50 000,00 / 5 = € 10 000,00 De lineaire afschrijving met restwaarde Als de auto na de afschrijvingsperiode nog een restwaarde van bijvoorbeeld € 5 000,00 heeft, dan kun je jaarlijks (€ 50 000,00 – € 5 000,00) / 5 = € 9 000,00 afschrijven. De degressieve afschrijving daarentegen schrijft het eerste jaar het grootste bedrag af en vermindert dit afgeschreven bedrag elk jaar. De fiscale wet bepaalt:  De onderneming mag starten met het dubbel van het lineaire afschrijvingspercentage, maar het mag niet hoger zijn dan 40 %.  De volgende jaren wordt dit dubbele lineaire afschrijvingspercentage toegepast op de boekwaarde en niet op de aanschafwaarde.  De onderneming mag overschakelen op de lineaire afschrijving indien het afschrijvingsbedrag volgens de degressieve methode kleiner wordt dan het lineaire afschrijvingsbedrag.

102



Voorbeeld : Een sportwagen met een aanschafwaarde van € 50 000,00 lineair of degressief over 5 jaar afschrijven: Lineair Periode

Aansch.waarde Restwaarde Gebruiksduur

Lineair + Degressief Lineair + restwaarde Degressief 50 000 50 000 50 000 50 000 0 5 000 0 5 jaar 5 jaar 5 jaar 5 jaar

Jaar 1

Aansch.waarde Afschrijving 1 Boekwaarde 1 Afschrijving 2 Boekwaarde 2 Afschrijving 3 Boekwaarde 3 Afschrijving 4 Boekwaarde 4 Afschrijving 5

50 000 10 000 40 000 10 000 30 000 10 000 20 000 10 000 10 000 10 000

50 000 9 000 41 000 9 000 32 000 9 000 23 000 9 000 14 000 9 000

50 000 20 000 30 000 12 000 18 000 *7 200 10 800 4 320 6 480 2 592

50 000 20 000 30 000 12 000 18 000 *10 000 8 000 8 000 0

0

5 000

3 888

0

Jaar 2 Jaar 3 Jaar 4 Jaar 5

Restwaarde

* Omdat de degressieve afschrijving kleiner is dan de lineaire mag je vanaf hier overschakelen naar de lineaire afschrijving. Bemerking: wegens plaatsgebrek zijn de cijfers na de komma weggelaten.

6.2

De lineaire afschrijving Gebruik de functie LIN.AFSCHR(), uit de categorie FINANCIEEL, om de lineaire afschrijving te berekenen. LIN.AFSCHR(kosten;restwaarde;duur)

TIP

kosten

zijn de aanschafkosten van de activa

restwaarde

is de waarde van de activa aan het einde van de afschrijving

duur

is het aantal termijnen waarover de activa afgeschreven worden (ook wel de levensduur van de activa genoemd)

Je kunt ook zelf een formule samenstellen om een lineaire afschrijving te berekenen: (kosten – restwaarde)/duur.

Opdrachten 1. De firma Stella NV kocht in 1997 voor € 6 000,00 kantooruitrusting en schrijft deze goederen volledig lineair af over een periode van 9 jaar. Ontwerp een universeel afschrijvingsplan dat voor gelijk welk bedrag en voor gelijk welke periode, gaande van 1 tot 10 jaar, de boekwaarde volgens de lineaire afschrijvingsmethode berekent.



103

a. Open een nieuwe werkmap en bewaar ze als FINANCIEELXP.XLS. Zet de gegevens uit de kolommen A en B en ook de inhoud van de cellen D3:F3 in een leeg werkblad. Noem dit blad LIN1. b. De jaartallen automatisch invullen 

Het eerste jaartal in cel D4: Als in B5 een gebruiksduur ingevuld is, begin dan met het startjaar in B6, anders vul je niets in.Welke formule vul je in?



De volgende jaartallen in de cellen D5:D13: Als in de cel boven de huidige cel een jaartal ingevuld is en als dit jaartal kleiner is dan het startjaar vermeerderd met één jaar minder dan de gebruiksduur, neem dan het volgende jaartal, in het andere geval zet je niets in deze cel. Welke formule vul je in cel D5 in?

c. Zet in E4 de formule van de lineaire afschrijving. Gebruik eventueel de knop WIZARD FUNCTIES en kies de functie LIN.AFSCHR() uit de categorie FINANCIEEL. - kosten: B3 - restwaarde: B7 - duur: B5 Gebruik je hier best een relatieve of een absolute adressering? d. Je zou de formule van E4 kunnen kopiëren naar E5:E8 en de rest van het bereik E9:E13 openlaten. Dit is de eenvoudigste oplossing. Maar als je dit rooster ook wilt gebruiken voor een duurtijd van 6 tot 10 jaar dan zou het handig zijn om een formule samen te stellen die in het hele bereik E4:E13 bruikbaar is. Er zijn verschillende oplossingen voor dit probleem. De eenvoudigste oplossing is te testen of er een jaartal staat in de overeenkomstige cel van kolom D. Staat er geen jaartal, dan moet je niets berekenen, in het andere geval moet je de afschrijving berekenen. Welke formule zet je in cel E4? Kopieer die formule naar de cellen E5:E13. e. Bereken in F4 de boekwaarde. Dit is de aanschafwaarde min de totale afgeschreven waarde. Als er in de overeenkomstige cel van kolom D een waarde ingevuld is moet een boekwaarde berekend worden, in het andere geval moet er niets gebeuren. Kopieer deze formule naar de rest van het bereik. 104



Zet in del F4 de volgende formule: =ALS( E4<>"" ; $B$3-SOM($E$4:E4) ; "" ) Kopieer deze formule naar F5:F13. Wat gebeurt er met SOM($E$4:E4)? Waarom moet de eerste verwijzing absoluut zijn en de laatste verwijzing relatief? f. TIP

Verzorg ten slotte de opmaak van het werkblad. Als de firma Stella NV na 9 jaar het meubilair nog voor € 5000 verkoopt, dan moet dit bedrag als meerwaarde op vaste activa geboekt worden.

Sjablonen in Excel Het is mogelijk om een werkmap als een sjabloon op te slaan. In dit geval zou het handig zijn om een sjabloon van het werkblad LIN1 te maken, dan hoef je de inhoud van dit werkblad niet altijd naar een nieuw werkblad te kopiëren. Sjabloonbestanden (*.XLT) worden standaard in de map …\APPLICATION DATA\MICROSOFT\SJABLONEN bewaard. Het zijn deze sjablonen die op tabblad ALGEMEEN van het dialoogvenster NIEUW (opdracht in menu BESTAND) weergegeven worden. In Windows ME zul je de sjablonen terugvinden onder: C:\ WINDOWS\PROFILES\GEBRUIKERSNAAM\APPLICATION DATA\... In Windows XP vind je ze terug onder: C:\DOCUMENTS AND SETTINGS\GEBRUIKERSNAAM\APPLICATION DATA\... Ingebouwde sjablonen worden op het tabblad WERKBLADMODELLEN vermeld. Ze worden bewaard in de map C:\PROGRAM FILES\MICROSOFT OFFICE\TEMPLATES\1043 Als je aangepaste sjablonen op een afzonderlijk tabblad wilt groeperen, sla de sjablonen dan in een submap van de map SJABLONEN op, bijvoorbeeld MIJN SJABLONEN. Elk Microsoft Excel-werkmapbestand (.xls) dat je in de map SJABLONEN of in een van de submappen daarvan opslaat, werkt eveneens als een sjabloon. Opdracht 2. Een sjabloonbestand van het werkblad LIN1 maken a. Open een nieuwe werkmap en kopieer hierin het werkblad LIN1 en noem het LIN AFSCHR. Verwijder de inhoud van de cellen B3:B6. Als er foutmeldingen in het werkblad verschijnen moet je de formules aanpassen zodat de foutmeldingen verdwijnen. b. Verwijder de overige lege werkbladen uit deze map. c. Kies BESTAND, OPSLAAN ALS. Vul als bestandsnaam ‘Lineaire afschrijving’ in en kies om het als SJABLOON op te slaan. Automatisch wordt de inhoud van de map SJABLONEN getoond. Maak hierin een nieuwe map, de map EXCEL, en bewaar de sjabloon hierin.



105

3. De firma Stella NV kocht in 1996 een auto voor € 23 750 en schrijft deze volledig lineair af over een periode van 6 jaar. Maak een afschrijvingsplan. a. Voeg een nieuw werkblad toe op basis van de sjabloon ‘Lineaire afschrijving’ en noem het LIN2. b. Pas de inhoud van de cellen B3:B6 aan de opgave aan. Welk bedrag wordt er elk jaar afgeschreven? [€ 3 958,33] 4. De firma Stella NV kocht in 1999 een zaagtafel voor € 8 255,99 en zal deze lineair afschrijven over een periode van 5 jaar. Na die periode is er nog een restwaarde van € 1 375,00. Maak een afschrijvingsplan. a. Voeg via de sjabloon ‘Lineaire afschrijving’ een nieuw werkblad toe en noem het LIN3. b. Pas de inhoud van de cellen B3:B6 aan de opgave aan. Welk bedrag wordt er elk jaar afgeschreven? [€ 1376,00] 5. Maak een verzamelstaat waarin een overzicht per jaar gegeven wordt van de totale afschrijving van alle goederen. Noem dit werkblad LIN4. Verwijs zoveel mogelijk naar de gegevens in de werkbladen LIN1, LIn2 en LIN3.

TIP

6.3

De formule in A3 is bijvoorbeeld =lin1!A3. In A10 staat de minimale waarde van het gebied B6 tot D6. De rest van de jaren in kolom A kun je berekenen: als het jaar in de vorige cel kleiner is dan het maximum van het (B6+B5, C6+C5, D6+D5)-1, zet dan in de huidige cel de waarde van de vorige cel +1, in het andere geval zet je niets.

De degressieve afschrijving We gaan er van uit dat:  er gestart wordt met het dubbel lineaire afschrijvingspercentage, maar dit mag niet hoger zijn dan 40 %;

106



 

er op de lineaire afschrijving overgeschakeld wordt, indien het afschrijvingsbedrag volgens de degressieve methode, kleiner wordt dan het lineaire afschrijvingsbedrag; er geen restwaarde is.

Met behulp van de 'double declining balance'-methode, ook wel degressieve afschrijvingsmethode genoemd of kortweg DDB, kun je eveneens de afschrijving berekenen van activa over een bepaalde termijn. Zolang de duur 5 jaar of meer is kun je de functie DDB() gebruiken. DDB(kosten;restwaarde;duur;termijn;factor) Voor alle argumenten geef je een positief getal op. kosten

zijn de aanschafkosten van de activa

restwaarde

is de waarde van de activa aan het einde van de afschrijving (Laat hier steeds de waarde 0 staan.)

duur

is het aantal termijnen waarover de activa afgeschreven worden (ook wel de levensduur van de activa genoemd)

termijn

is de termijn waarover je de afschrijving wilt berekenen Termijn wordt in dezelfde eenheden opgegeven als duur.

factor

is de snelheid waarmee afgeschreven wordt. Als je factor weglaat, wordt uitgegaan van de waarde 2 (dubbele degressieve afschrijving).

Bij de dubbele degressieve afschrijvingsmethode worden de activa versneld afgeschreven. De afschrijving is het hoogst in de eerste termijn en wordt altijd kleiner in de volgende termijnen. DDB gebruikt de volgende formule om de afschrijving over een bepaalde termijn te berekenen: kosten – restwaarde (totale afschrijving uit vorige termijnen) * factor / duur Opdrachten 6. De firma Stella NV koopt een sportwagen voor € 50 000 en schrijft die volledig af over een periode van 5 jaar. Er is geen restwaarde. Maak eerst een afschrijvingsplan volgens de DDB-methode. Wijzig vervolgens het afschrijvingsplan zodanig dat de methode van de lineaire afschrijving gebruikt wordt als het bedrag van de degressieve afschrijving kleiner wordt dan dat van de lineaire afschrijving. a. Voeg een nieuw werkblad toe en noem het DDB1. b. Neem de kolommen A en B volledig over alsook de cellen D3,E3 en F3.



107

c. De termijngetallen in kolom D moeten automatisch ingevuld worden. Zet in D4 een formule in de aard van: als er geen gebruiksduur in B5 vermeld is, doe dan niets, in het andere geval zet je het cijfer 1 in deze cel. De volgende cellen kun je vullen met de volgende uitspraak: als de waarde van de bovenliggende cel, kleiner is dan de gebruiksduur, zet dan de waarde van de bovenste cel, vermeerderd met 1 in de huidige cel; in het andere geval doe je niets. d. Zet in E4 de formule voor de degressieve afschrijving. Gebruik eventueel de knop WIZARD FUNCTIES en kies de functie DDB() uit de categorie FINANCIEEL. – kosten: B3 – restwaarde: B6 – duur: B5 – termijn: D4 Welke waarden moeten relatief en welke waarden moeten absoluut geadresseerd worden? e. Je zou de formule van E4 naar E5:E8 kunnen kopiëren en de rest van het bereik E9:E11 openlaten. Dit is de eenvoudigste oplossing. Maar als je dit rooster ook wilt gebruiken voor een duurtijd van maximum 10 jaar dan zou het handig zijn om een formule samen te stellen die bruikbaar is in het hele bereik E4:E13. Er moet maar een afschrijving berekend worden als de termijn in kolom D kleiner is dan of gelijk aan de duur in B5, m.a.w. als de waarde in kolom D groter is dan de duur in B5 dan moet er niets berekend worden. Verander daarom de formule in E4 als volgt: =ALS( D4>$B$5; "" ; DDB($B$3;$B$6;$B$5;D4) ) Kopieer deze formule naar E5:E13. Zijn er problemen? Wat denk je van de volgende formule? =ALS( D4=""; "" ; DDB($B$3;$B$6;$B$5;D4) ) f.

Bereken in kolom F de boekwaarde. De berekening is alleen maar nodig als in de cel ernaast in kolom E een waarde ingevuld is. =ALS( E4<>"" ; $B$3-SOM($E$4:E4) ; "" )

g. Je merkt dat in de derde periode de afschrijving (€ 7 200) kleiner is dat de overeenkomstige lineaire afschrijving (€ 10 000). Daarom mag vanaf hier de lineaire afschrijving gebruikt worden.

108



Kopieer de inhoud van dit werkblad naar een nieuw werkblad en noem het DDB1b. Ga hierin verder met de oefening. Wijzig de titel in ‘Degressieve en lineaire afschrijving’. h. Wijzig de formule in E4 zodanig dat als de degressieve afschrijving groter is dan de lineaire, de degressieve genomen wordt, anders de lineaire. ALS(D4="" ; "" ; ALS( DDB($B$3;$B$6;$B$5;D4) > LIN.AFSCHR($B$3;$B$6;$B$5) ; DDB($B$3;$B$6;$B$5;D4) ; LIN.AFSCHR($B$3;$B$6;$B$5) )) Omdat er een moment is waarop de boekwaarde kleiner is dan de lineaire afschrijving, moet je het bedrag van de boekwaarde als afschrijving gebruiken. Dit kun je maar testen vanaf de tweede termijn. Vervang daarom in de vorige formule de actie van de lineaire afschrijving door: ALS( LIN.AFSCHR($B$3;$B$6;$B$5)>F4 ; F4 ; LIN.AFSCHR($B$3;$B$6;$B$5) ) De totale formule wordt dan: =ALS(D5="" ;""; ALS(DDB($B$3;$B$6;$B$5;D5)>LIN.AFSCHR($B$3;$B$6;$B$5) ; DDB($B$3;$B$6;$B$5;D5) ; ALS(LIN.AFSCHR($B$3;$B$6;$B$5)>F4;F4;LIN.AFSCHR($B$3;$B$6;$B$5)) ) ) Kopieer deze formule naar E6:E13. i.

Voeg een extra kolom toe waarin vermeld staat welke afschrijvingsmethode er gebruikt is. Daartoe test je opnieuw of de degressieve afschrijving groter is dan de lineaire. Indien dat waar is, zet je het woord ‘Degressief’ in de cel, anders het woord ‘Lineair’. Er moet uiteraard maar getest worden als er in de overeenkomstige cel van kolom E een waarde staat. =ALS(E4="";"";ALS(DDB($B$3;$B$6;$B$5;D4)>LIN.AFSCHR($B$3;$B$6;$B$5); "Degressief";"Lineair"))

j.

Het resultaat:

TIP

De foutmelding ‘Fout bij laden van .dll’ wordt meestal veroorzaakt door een foutief geschreven functienaam in een formule.

7. De firma Stella NV koopt een auto voor € 18 500 en schrijft die goederen af over een periode van 7 jaar. Maak een afschrijvingsplan volgens de DDB-methode, eventueel gecombineerd met de lineaire afschrijving. Vertrek van een nieuw werkblad, noem het DDB2 en kopieer hierin het werkblad DDB1. Toon in een grafiek het verband tussen de boekwaarde en de termijn. J. Gils – E. Goossens


109

110



Besluit Er zijn twee afschrijvingsmethodes die veel toegepast worden: de lineaire zonder restwaarde en de combinatie van de (dubbel) degressieve en de lineaire. De functie LIN.AFSCHR() is zonder problemen te gebruiken. De functie DDB() mag je alleen maar gebruiken als het afschrijvingspercentage niet hoger dan 40% ligt. Daarom moet de duur van de afschrijving minstens 5 jaar zijn. Werkbladen kunnen als een sjabloon opgeslagen worden. In dat geval kan een nieuw werkblad op basis van deze sjabloon aan een werkmap toegevoegd worden.

Wat je moet kennen en kunnen:        

ingebouwde functies opzoeken en gebruiken; gevorderde functies in een expressie gebruiken en het resultaat interpreteren; de lineaire afschrijving toepassen; van meerdere werkbladen met dezelfde opbouw samenvattende informatie berekenen; de dubbel degressieve afschrijving verklaren en toepassen; een voorbeeld uitwerken waarbij er een overgang is van de degressieve naar de lineaire afschrijving; werkbladen als een sjabloon bewaren; het nut van het werken met sjablonen en standaardwerkbladen inzien en toepassen.

Opdracht 8. Afschrijvingsplan van de firma KantoPuters bvba De firma KantoPuters bvba startte op 1 januari 1995 een bedrijf in computers en kantoormeubilair. Op dat moment kocht men enkele investeringsgoederen aan: Gebouwen: € 250 000,00 Af te schrijven op: 30 jaar Machines: € 500 000,00 Af te schrijven op: 10 jaar Rollend materieel: € 37 500,00 Af te schrijven op: 6 jaar Kantooruitrusting: € 12 500,00 Af te schrijven op: 5 jaar Door de goede gang van zaken kocht de firma KantoPuters bvba op 1 januari 1996 nog twee personenwagens aan om meer vertegenwoordigers de baan op te kunnen sturen. 2 personenwagens: € 32 500,00 Af te schrijven op: 5 jaar Op 1 januari 1998 investeert de firma KantoPuters bvba in een bijkomende machine. Machine: € 18 750,00 Af te schrijven op: 10 jaar Maak van deze gegevens en met behulp van functies, een afschrijvingstabel over 30 jaar, die voor elk jaar de gekende afschrijvingen optelt. Gebruik alleen de lineaire methode. Bewaar deze oefening onder de naam KANTO1 en druk het resultaat af. TIP

Zet de juiste formule in cel B10 en kopieer die zowel naar links als naar onder zodat de afschrijvingen automatisch op de juiste plaats ingevuld worden.



111

Zeg eerst in woorden wanneer er een waarde berekend moet worden en stel dan pas de formule samen! Noteer hiernaast de formule in cel B10:

TIP

112

De jaartallen in kolom A zou je op dezelfde manier kunnen invullen als in de oefening LIN4. In dat geval zal de tabel beginnen bij het jaar 1995.



Hoofdstuk 7

7.1

Leningen, rente en kapitaalaflossing

Probleemstelling Geld lenen, geld sparen: het hoeft allemaal niet zo moeilijk meer te zijn. Excel beschikt over een aantal financiële functies waarmee je op een snelle manier bijvoorbeeld de opbrengst van een investering of de maandelijks terug te betalen bedragen van een lening kunt uitrekenen.

7.2

Samengestelde intrest Opdracht 1. Jan Spaermans zou graag weten hoe zijn kapitaal van € 100,00 elk jaar aangroeit als hij het gedurende 20 jaar tegen een intrest van 4%, 5% of 6% uitzet. Formule: eindkapitaal = beginkapitaal x (1 + intrest)periode Bewaar deze oefening in de werkmap FINANCIEELXP.XLS en noem het werkblad INTREST. Maak gebruik van de functies MACHT() en AFRONDEN() om het resultaat tot op twee cijfers nauwkeurig te berekenen. Zet in cel E4 de gepaste formule zodat je ze over het hele bereik E4:G23 kunt kopieren. Welke formule zet je in E4?

7.3

Matrixformules Een matrixformule kan meerdere berekeningen uitvoeren en ofwel een enkel resultaat ofwel meerdere resultaten berekenen. Matrixformules werken met twee of meer reeksen waarden, matrixargumenten genoemd. Elk matrixargument moet hetzelfde aantal rijen en kolommen hebben. Je maakt matrixformules op dezelfde wijze als basisformules met een enkele waarde. Selecteer de cel of de cellen waarin je de formule wilt opslaan, maak de formule en druk op CTRL + SHIFT+ ENTER om de formule in te voeren. De formule =B3*MACHT(1+E3;D4) berekent de eindwaarde in cel E4. De verwijzingen E3 en D4 veranderen van cel tot cel. Het is mogelijk om al deze verwijzingen als een matrix in de formule in te voeren: = B3*MACHT(1+E3:G3;D4:D23). In dat geval spreekt J. Gils – E. Goossens


113

men van een matrixformule, de reeksen E3:G3 en D4:D23 noemt men de matrixargumenten. Opdracht 2. Met een matrixformule het eindkapitaal berekenen a. Kopieer het vorige werkblad naar een nieuw werkblad en noem het MATRIX. b. Verwijder alle formules uit het gebied E4:G23. Selecteer het gebied E4:G23 en zet in de formulebalk de volgende formule: = B3*MACHT(1+E3:G3;D4:D23) Druk op CTRL + SHIFT+ ENTER om de formule in te voeren. In alle cellen van dit bereik staat nu dezelfde formule tussen accolades { }. TIP

7.4

Een matrixformule heeft als voordeel dat je geen rekening moet houden met absolute adresseringen omdat je de formule maar eenmaal moet invoeren.

Het termijnbedrag of de annuïteit De firma Stella NV wil dit jaar een nieuwe vrachtwagen kopen die € 62 500 zal kosten. Wegens een gebrek aan liquide middelen zal het bedrijf dit bedrag moeten lenen. De financiële directeur vroeg inlichtingen bij een financiële instelling met betrekking tot het bedrag dat de firma maandelijks zal moeten terugbetalen. Keuzemogelijkheden: Het bedrag in 60 maanden terugbetalen: rente = 8,9% Het bedrag in 48 maanden terugbetalen: rente = 9,45% Het bedrag in 36 maanden terugbetalen: rente = 10,25% Een termijnbedrag of annuïteit is een reeks vaste betalingen die over een aaneensluitende periode verricht worden. Een lening voor een auto en een hypotheek worden bijvoorbeeld vaak in annuïteiten terugbetaald. De functie BET() berekent het termijnbedrag van een lening op basis van vaste betalingen en een vast intrestpercentage. BET(rente;aantal-termijnen;hw;tw;type_getal) rente

het intrestpercentage van de lening

aantal-termijnen het totale aantal termijnen van de lening hw

de huidige waarde, of het totaalbedrag dat een reeks toekomstige betalingen op dit moment waard is

tw

de toekomstige waarde, ofwel het saldo dat je wilt bereiken als de laatste betaling verricht is. Als je tw weglaat wordt van de waarde 0 (nul) uitgegaan, dat wil zeggen dat de toekomstige waarde van een lening 0 is.

type_getal

een getal dat aangeeft wanneer betalingen voldaan moeten worden: 0 of niets aan het einde van de termijn 1 aan het begin van de termijn

114



TIPS  In feite slaat de term ‘rente’ alleen maar op een vergoeding voor het gebruik van een onroerend goed en zou beter de term ‘intrest’ gekozen zijn.  Het resultaat van deze functie is een negatief getal: het is geld dat betaald moet worden.  Wees consequent in het gebruik van de eenheden waarmee je rente en aantal-termijnen opgeeft. Als je maandelijkse aflossingen verricht op een lening met een looptijd van vier jaar tegen een rentepercentage van 12 % per jaar, gebruikt dan 12 % / 12 voor rente en 4 * 12 voor aantal-termijnen. Als je jaarlijkse aflossingen verricht op dezelfde lening gebruik dan 12 % voor rente en 4 voor aantal-termijnen. Strikt gezien is de maandelijkse rente niet gelijk aan de jaarlijkse rente gedeeld door 12 maar daar gaan we hier niet verder op in.  Als je het totaalbedrag wilt weten dat je over de gehele looptijd van de lening betaald hebt, vermenigvuldig dan het resultaat van BET() met het aantal-termijnen. Met behulp van volgend werkblad kan de financiële directeur de maandelijks terug te betalen bedragen berekenen.

Opdracht 3. Het termijnbedrag berekenen a. Open een nieuw werkblad in de werkmap FINANCIEELXP en noem het TERMIJN1. b. Neem de gegevens van het voorbeeld over, behalve de oplossingen in B6:D6. c. Selecteer B6 en kies de functie BET (periodieke betalingen) met de functiewizard (fx) in de categorie FINANCIEEL. In het dialoogvenster vul je de volgende gegevens in:  Rente: het rentepercentage van de lening, klik in cel B4. TIP De looptijd die we gebruiken is in maanden uitgedrukt, maar het percentage in B4 is een jaarpercentage. Daarom delen we B4 door 12.  Aantal-termijnen: het aantal maanden waarin het kapitaal terugbetaald moet worden, klik in B5.  Hw : de huidige waarde, d.i. het geleende bedrag, klik in B3. Wil je dit later kopiëren, maak dan B3 absoluut door op de functietoets F4 te drukken. Klik op OK. d. Kopieer de formule van B6 naar C6 en D6. Doordenkertjes  De berekende maandelijkse aflossingen zijn negatieve getallen. Waarom?  Hoeveel is de maandelijkse aflossing bij een looptijd van 60 maanden?  Hoe evolueert de aflossing naargelang de looptijd korter is? Hoe evolueert de rente? Waarom? Waarschijnlijk kiest de financiële directeur van een jong bedrijf voor een lange looptijd, omdat de maandelijkse lasten dan lager zijn.



115

Opdracht 4. De firma Stella NV wil een lening van € 50 000 aangaan. De rentevoet is 7,75 % en de terugbetalingstermijn is 20 jaar. Wat is de maandelijkse annuïteit? [- € 410,47] Noem het werkblad TERMIJN2.

7.5

De rente en de kapitaalaflossing Het is belangrijk te weten welk deel van het maandelijkse aflossingsbedrag rente en welk deel kapitaalaflossing is. Soms is alleen de rente aftrekbaar van het belastbaar inkomen en het kapitaal niet. De financiële directeur van Stella NV kiest ervoor om het geleende kapitaal in 60 maanden terug te betalen.

Opdracht 5. Het werkblad ‘Rente en aflossing’ klaarmaken a. Open een nieuw werkblad in de werkmap FINANCIEELXP, noem het REN_KAP en voer de gegevens van de eerste zeven rijen letterlijk in. b. Voer in kolom A van cel A9 t.e.m. A68 de waarden 1 t.e.m. 60 in. Gebruik de vulgreep. c. Vul in B9 de datum 1998-01-01 in en in B10 1998-02-01, kopieer deze data met de vulgreep tot B68. 7.5.1 De rente- en het kapitaalaandeel De functie IBET() berekent de te betalen rente en de functie PBET() berekent de kapitaalaflossing op de hoofdsom van een investering voor een bepaalde termijn, op basis van periodieke, vaste betalingen en een vast rentepercentage. IBET(rente;termijn;aantal-termijnen;hw;tw;type_getal) PBET(rente;termijn;aantal-termijnen;hw;tw;type_getal) rente

het rentepercentage per termijn

termijn

de termijn waarvoor je de rente of het kapitaal wilt berekenen Dit argument moet tussen 1 en aantal-termijnen liggen.

aantal-termijnen

het totale aantal termijnen in een annuïteit

hw

de huidige waarde, ofwel het totaalbedrag dat een reeks toekomstige

116



betalingen op dit moment waard is tw

de toekomstige waarde, ofwel het saldo dat je wilt bereiken als de laatste betaling verricht is Als je tw weglaat, wordt van de waarde 0 uitgegaan (de toekomstige waarde van bijvoorbeeld een lening is 0).

type_getal

een getal dat aangeeft wanneer betalingen voldaan moeten worden: 0 of niets aan het einde van de termijn 1 aan het begin van de termijn

TIP

Voor alle argumenten geldt dat bedragen die je betaalt, zoals stortingen op een spaarrekening, als negatieve getallen weergegeven worden. Bedragen die je ontvangt, zoals dividenden, worden als positieve getallen weergegeven.

Opdrachten 6. Het renteaandeel van een termijnbetaling berekenen a. Vervolg met het werkblad REN_KAP. b. Klik in cel C9 en kies de functiewizard, categorie FINANCIEEL, de functie IBET. Vul het dialoogvenster met de volgende gegevens in:  Rente: selecteer D3 en maak D3 absoluut. TIP: dit is een jaarlijkse rente en je hebt een maandelijkse rente nodig!  Termijn: selecteer A9. Dit is de maand waarvoor je het rente-aandeel wilt berekenen.  Aantal-termijnen: selecteer D5 en maak D5 absoluut.  Hw: het geleende kapitaal, selecteer D4 en maak D4 absoluut. Klik op OK als je klaar bent. 7. Het kapitaalaandeel van een termijnbetaling berekenen a. Vervolg met het werkblad REN_KAP. b. Klik in cel E9 en kies de functiewizard, categorie FINANCIEEL, de functie PBET. Vul het dialoogvenster met de volgende gegevens in:  Rente: selecteer D3 en maak D3 absoluut.  Termijn: selecteer A9.  Aantal-termijnen: selecteer D5 en maak D5 absoluut.  Hw: het geleende kapitaal, selecteer D4 en maak D4 absoluut. Klik op OK als je klaar bent. 7.5.2 De cumulatieve rente en het restkapitaal Opdracht 8. De cumulatieve rente en het restkapitaal berekenen a. Vervolg met het werkblad REN_KAP. b. In cel D9 moet een formule komen die de tot dan toe afbetaalde rente optelt. Hiervoor gebruik je best de functie SOM(). =SOM($C$9:C9) Kun je verklaren waarom het eerste adres absoluut is en het tweede relatief? Gebruik –SOM() als je positieve getallen wilt hebben. c. In cel F9 moet een formule komen die het tot dan toe afbetaalde kapitaal aftrekt van het totaal terug te betalen kapitaal. Het reeds afbetaalde kapitaal vind je met J. Gils – E. Goossens


117

SOM($E$9:E9).

Omdat dit een negatief getal is zal de uiteindelijke formule er als volgt uitzien: =$D$4+SOM($E$9:E9)

7.5.3 De controle via de termijnbetaling Opdracht 9. De terugbetaalde rente en het terugbetaalde kapitaal controleren a. Vervolg met het werkblad REN_KAP. b. Zet in G9 de gepaste functie om de termijnbetaling van een lening te berekenen. Welke formule heb je gebruikt? c. Zet in H9 een formule waarmee je de afbetaalde rente optelt bij het afbetaalde kapitaal. Welke formule heb je gebruikt? Is het resultaat in G9 en H9 wat je verwacht? 7.5.4 De eindafwerking en een grafische voorstelling Opdrachten 10. De formules kopiëren naar de overige cellen a. Kopieer de inhoud van C9:H9 met de vulgreep tot rij 68. b. Wat kun je uit deze tabel afleiden? In het begin bestaat het termijnbedrag uit veel rente en weinig kapitaal. Tegen het einde bestaat het termijnbedrag uit veel kapitaalaflossing en een klein deel rente. 11. Grafische voorstelling a. Zet ten slotte de waarden van IBET(), PBET() en de ANNUÏTEIT uit in een grafiek. b. Toon ook de verandering van het restkapitaal in functie van de periode.

7.6

De huidige en toekomstige waarde van een investering De functie HW() berekent de huidige waarde van een investering. De huidige waarde is het totale bedrag dat een reeks toekomstige betalingen op dit moment waard is. Als je bijvoorbeeld geld leent, is het geleende bedrag de huidige waarde voor de verstrekker van de lening. De functie TW() berekent de toekomstige waarde van een investering, op basis van periodieke, vaste betalingen en een vast rentepercentage.

118



TW(rente;aantal-termijnen;bet;hw;type_getal) HW(rente;aantal-termijnen;bet;tw;type_getal) Opdrachten 12. Jan Spaermans kan gedurende 20 jaar maandelijks een bedrag van € 410,48 sparen. a. Welk bedrag zou hij op dit moment hiermee kunnen lenen tegen een rente van 7,75 %? [€ 50 000,70]. b. Hoeveel zou hij hiermee kunnen lenen als de rente slechts 4,5 % zou zijn? [€ 64 882,00] c. Noem dit werkblad HW. 13. Jan Spaermans zal gedurende 20 jaar maandelijks een bedrag van € 410,48 sparen. a. Onderstel dat hij dit geld op een spaarrekening zet en dat hij de hele periode een rente van 7,75 % krijgt. Hoeveel zal hij op 20 jaar gespaard hebben? [€ 234 405,65] b. Welk bedrag zou hij bekomen bij een rendement van: 4,5 % [€ 159 317,29] 9,5 % [€ 292 230,38] c. Noem dit werkblad TW.

7.7

De rente van een annuïteit De functie RENTE() berekent het periodieke rentepercentage voor een annuïteit. Een annuïteit is een reeks vaste betalingen die worden verricht over een aaneensluitende periode. Een lening voor een auto en een hypotheek worden bijvoorbeeld vaak in annuïteiten terugbetaald. RENTE(aantal-termijnen;bet;hw;tw;type_getal;schatting) schatting

je schatting van het rentepercentage Als je schatting weglaat, wordt uitgegaan van 10 procent.

Opdracht 14. Jan Spaermans gaat een lening van € 50 000 aan. Hij betaalt maandelijks € 410,48 gedurende een periode van 20 jaar. Bereken de jaarlijkse rente. [7,75%] Noem dit werkblad RENTE. TIP

Bereken eerst de maandelijkse rente en vermenigvuldig achteraf met 12.

TIP

BET is een negatief getal!



119

7.8

Het aantal termijnen voor een investering De functie NPER() berekent het aantal termijnen voor een investering op basis van periodieke, vaste betalingen en een vast rentepercentage. NPER(rente; bet; hw; tw; type_getal) Opdracht 15. Jan Spaermans betaalt maandelijks € 410,48 terug voor een lening van € 50 000. De rentelast bedraagt 7,75 %. Hoeveel periodes zal hij moeten terugbetalen? [240] Noem dit blad NPER.

120



Besluit In een rekenblad vind je een aantal functies terug in verband met leningen, rente en kapitaalaflossing. Enkele belangrijke functies zijn: 

BET():

berekent de maandelijkse aflossingen van een lening;



IBET()

berekent het renteaandeel in de maandelijkse aflossingsbedragen;



PBET()

berekent het gedeelte kapitaalaflossing in de maandelijkse aflossingsbedragen;



TW()



HW()



RENTE()



NPER()

berekent de toekomstige waarde van een investering, op basis van periodieke, vaste betalingen en een vast rentepercentage;

berekent de huidige waarde van een investering; De huidige waarde is het totale bedrag dat een reeks toekomstige investeringen op dit moment waard is. berekent het periodieke rentepercentage voor een annuïteit; Een annuïteit is een reeks vaste betalingen die worden verricht over een aaneensluitende periode

berekent het aantal termijnen voor een investering op basis van periodieke, vaste betalingen en een vast rentepercentage.

Voor alle argumenten geldt dat bedragen die je betaalt, zoals stortingen op een spaarrekening, als negatieve getallen weergegeven worden. Bedragen die je ontvangt, zoals dividenden, worden als positieve getallen weergegeven.

Wat je moet kennen en kunnen:    

matrixformules opstellen en gebruiken; ingebouwde functies opzoeken en gebruiken; gevorderde functies in een expressie gebruiken en het resultaat interpreteren; een bestaand werkblad op efficiënte wijze actualiseren.

Opdrachten 16. Maak een tabel zoals in het voorbeeld waarbij de maandelijkse terugbetaling getoond wordt voor een lening tussen € 25 000 en € 250 000 tegen een rente van 4,00% tot 8,00%. De duurtijd van de lening wordt uitgedrukt in jaren en kan vrij ingesteld worden. Zet in cel B6 de juiste formule zodat je ze zowel naar rechts als naar onder kunt kopieren. Gebruik eventueel een matrixformule. Noem het werkblad TERMIJN3.



121

17. Toen de firma KantoPuters bvba in 1995 het bedrijf opstartte leende het bij de bank € 350 000. De financiële directeur koos een lening tegen een rente van 8% en een looptijd van 60 maanden. Bereken in een nieuw werkblad, KANTO2, de maandelijkse termijnbedragen die de firma moet betalen. [€ 7 096,74] Maak in een ander werkblad, KANTO2B, een aflossingstabel waarin voor elke maand de terugbetaalde rente en intrest berekend wordt. Bewaar deze oefeningen onder de naam KANTO2 en KANTO2B in de werkmap FINANCIEELXP.XLS. 18. Stel dat de bovenstaande firma KantoPuters bvba hetzelfde bedrag leende, maar voor een duur van 36 maanden en tegen een rentepercentage van 10%. Bereken eveneens de maandelijkse termijnbetalingen. Vervang het rentepercentage en de termijn uit de vorige oefening. Vergelijk de maandelijkse termijnbetalingen. Vergelijk de gecumuleerde rente van beide oefeningen. Bewaar deze versie onder KANTO3. 19. Julia Suykertante doet haar petekind Lukske het volgende voorstel:  ofwel krijgt hij elke maand € 25,00 zakgeld gedurende 10 jaar;  ofwel eenmalig € 2 250. Lukske is ook niet van gisteren en hij weet dat de bank een rente garandeert van 8% gedurende 10 jaar. Wat doet hij? [€ 2 250 → € 4 573,65] Hoe zou de situatie zijn als de rente slechts 4% zou zijn? [€ 25 → € 3 681,25] Bewaar het werkblad onder de naam JULIA. 20. De heer Spaermans heeft een zero-obligatie van € 4 000,00 met een looptijd van 10 jaar gekocht tegen een huidige notering van 40%. Hij zou nu graag het rentepercentage kennen van zijn belegging. Noem dit werkblad SPAERMANS. [9,60%] 21. Marina wil ooit een sportwagen van € 35 000,00 kopen. Hiervoor kan zij € 500,00 per maand sparen die zij op een spaarrekening belegt tegen 6%. Hoelang zal zij nog met haar 2 pk’tje moeten blijven rijden voordat zij voldoende geld heeft om de wagen van haar dromen te kopen? Noem dit werkblad MARINA. [7 jaar]

122



Hoofdstuk 8

8.1

Draaitabellen

Inleiding Een draaitabel is een interactieve tabel in een werkblad waarmee je snel een samenvatting kunt maken van een grote hoeveelheid gegevens. De tabel wordt een “draaitabel” genoemd omdat je de rijen kolomkoppen kunt draaien rond een centraal gegevensgebied. Als de brongegevens wijzigen kan de draaitabel bijgewerkt of herberekend worden. In een draaitabel wordt een samenvatting gemaakt aan de hand van samenvattingsfuncties die je zelf kunt kiezen. Je kunt ook zelf bepalen hoe subtotalen en eindtotalen berekend worden. Welke gegevens komen in aanmerking als bron voor een draaitabel?  een willekeurig cellenbereik waarvan de kolommen van een label voorzien zijn;  een databasebestand;  gegevens uit een bestaande draaitabel.

8.2

Probleemstelling De database VERKOOP98 bevat een tabel, TBLVERKOOP waarin maandelijks tweemaal de verkoopcijfers van de drie vertegenwoordigers, Els, Jan en Sofie, genoteerd worden. De verantwoordelijke voor de verkoop wil een mooie grafische voorstelling waarin de evolutie van de verkoopcijfers van het jaar 1998 voor elke verkoper zichtbaar is. De verkoopcijfers moeten per maand gegroepeerd worden.

8.3

Probleemanalyse De gegevens zitten in de database VERKOOP98XP: ze moeten naar het rekenblad geëxporteerd worden. De tabel TBLVERKOOP bevat geen namen van verkopers, wel het nummer van de verkoper. Ontwerp daarom een query QRYVERKOOP0 die de naam van de verkoper, het verkochte bedrag en de datum toont, gesorteerd volgens datum. Zorg voor zinvolle veldnamen! Exporteer deze query naar een Excel-werkblad via menu BESTAND, OPSLAAN ALS. Selecteer in het vak OPSLAAN ALS: MICROSOFT EXCEL 87-2003.XLS. Het werkblad krijgt dezelfde naam als de query, uiteraard met een andere extensie. Kopieer dit werkblad naar de werkmap GRAFIEKXP. De gegevens in Excel zien er als volgt uit:



123

Omdat de gegevens per maand gegroepeerd moeten worden heb je nog een aparte kolom met de maandnummers nodig. Deze kolom kun je ofwel in EXCEL toevoegen, ofwel kun je ze als een rekenveld aan de query QRYVERKOOP0 toevoegen. Het is ook mogelijk om de verkoopcijfers per kwartaal te groeperen. In dat geval heb je een extra kolom met het kwartaalnummer nodig. Het kwartaal kun je met een ingewikkelde ALS() berekenen: als het maandnummer groter is dan of gelijk is aan 10, dan is het kwartaalnummer gelijk aan 4, anders als het maandnummer groter is dan of gelijk is aan 7, dan is het kwartaalnummer gelijk aan 3, … Het is ook mogelijk om hiervoor een speciale functie in VBA te schrijven. Deze functie kun je zowel in de query QRYVERKOOP0 als in het rekenblad gebruiken, op voorwaarde dat je de programmacode ofwel aan een module in de werkmap GRAFIEKXP of aan een module in de database VERKOOP98XP toevoegt. Public Function Kwartaal(dteDatum As Date) As Byte Dim bytKwartaal As Byte Select Case Month(dteDatum) Case 1 To 3 bytKwartaal = 1 Case 4, 5, 6 bytKwartaal = 2 Case 7, 8, 9 bytKwartaal = 3 Case 10, 11, 12 bytKwartaal = 4 Case Else bytKwartaal = 5 End Select Kwartaal = bytKwartaal End Function

In Excel zou de functie als volgt opgeroepen kunnen worden: = Kwartaal(B2). De gegevens per vertegenwoordiger en per maand groeperen gebeurt in een draaitabel. Het resultaat van de draaitabel wordt gebruikt om de grafische voorstelling te maken.

8.4

Een draaitabel ontwerpen Opdracht 1. Een draaitabel ontwerpen die de verkoopcijfers per maand en per vertegenwoordiger groepeert a. Ontwerp de query QRYVERKOOP0 in de database VERKOOP98 die de naam van de verkoper, uit de tabel TBLVERKOPER, aan het bedrag en de datum uit de tabel TBLVERKOOP koppelt. Exporteer het resultaat van deze query naar een Excel-werkblad via menu BESTAND, OPSLAAN ALS. Selecteer in het vak OPSLAAN ALS: MICROSOFT EXCEL 872003.XLS. Kopieer ten slotte dat werkblad naar de werkmap GRAFIEKXP en noem het DRAAITABEL. (De opmaak van het datumveld in het Excel-bestand gebeurt volgens de landinstellingen van Windows!) 124



b. Voeg een extra kolom toe waarin je het maandnummer van elke betaaldatum berekent. Als je dit in de query doet komt die kolom automatisch in Excel. c. Kies DATA, DRAAITABEL- EN DRAAIGRAFIEKRAPPORT.

Je hebt de mogelijkheid om de gegevens rechtstreeks in de database te gaan halen. Het is zelfs mogelijk dat de gegevens over verschillende werkbladen verspreid zijn. d. In stap 2 wordt gevraagd waar de gegevens staan: selecteer de volledige tabel, ook de kolomnamen.

e. In stap 3 wordt gevraagd waar de draaitabel gezet moet worden. Kies BESTAAND WERKBLAD en neem bijvoorbeeld cel G5 van dit werkblad. Er gebeurt een standaard opbouw van de nieuwe tabel.

Kijk even naar het dialoogvenster OPTIES voordat je op de knop INDELING klikt, klik zeker niet op VOLTOOIEN. J. Gils – E. Goossens


125

f.

Via de knop INDELING kun je de standaard indeling aanpassen en de draaitabel opbouwen:

De gegevens van elke vertegenwoordiger moeten elk in een aparte kolom komen: sleep daarom de knop NAAM naar ‘KOLOM’: zo wordt er voor elke vertegenwoordiger een nieuwe kolom aangemaakt. Sleep de knop MAAND naar ‘RIJ’: dan wordt er voor elke maand een rij toegevoegd. Sleep de knop BEDRAG naar ‘GEGEVENS’: dan worden voor elke vertegenwoordiger de bedragen uit dezelfde maand opgeteld en in de overeenkomstige rij afgedrukt. Naast de bewerking Som kan Excel nog andere bewerkingen op een veld uitvoeren, bijvoorbeeld Aantal, Max, … Deze bewerkingen kun je instellen bij de veldeigenschappen van de knoppen (zie verder). TIP

Indien er ook nog een knop JAAR zou zijn en je zou deze knop tot boven de knop MAAND slepen, dan zou je eerst een opsplitsing per jaar krijgen en binnen de opsplitsing per jaar zou een verdere opsplitsing per maand gebeuren.

TIP

Met ‘PAGINA’ kun je de gegevens pagineren. Als je de knop NAAM ook nog naar ‘PAGINA’ sleept, dan kun je via deze knop selecteren van welke vertegenwoordiger de verkoopcijfers getoond moeten worden.

g. De opties: Geef de tabel een naam: ‘Verkoop’. Standaard worden er eindtotalen voor de kolommen en rijen toegevoegd. De optie VERNIEUWEN BIJ OPENEN werkt automatisch de draaitabel bij als de werkmap geopend wordt.

126



h. Het resultaat ziet er als volgt uit:

TIP

Vergeet niet om de opmaak van de getallen aan te passen! Klik met de rechtermuisknop op ‘Som van Bedrag’, kies de optie VELD, GETAL en kies dan de gewenste opmaak.

TIP

Als je op VOLTOOIEN klikt voordat je de draaitabel opgebouwd hebt krijg je een ander opbouwscherm. In dat geval sleep je de velden vanaf de lijst met draaitabelvelden naar de gewenste plaats.



127

TIP

Als je buiten het draaitabelgebied klikt, verdwijnt de lijst met draaitabelvelden.

2. Een tweede draaitabel maken die dezelfde gegevens per kwartaal toont a. Voeg de kolom ‘Kwartaal’ aan het gegevensblad toe. b. Ontwerp een nieuwe draaitabel zoals de vorige maar via de knop INDELING sleep je de knop KWARTAAL naar ‘PAGINA’.

TIP

Als je dubbelklikt op de knop SOM VAN BEDRAG wordt het dialoogvenster DRAAIgeopend. Hierin kun je bijvoorbeeld een andere bewerking kiezen, de naam van het veld wijzigen … TABELVELD

c. Vervolg het ontwerp. Voeg ook deze tabel aan het huidige werkblad toe en kies cel M3 als begincel. Noem deze tabel ‘Kwartaal’. Het resultaat ziet er als volgt uit:

128



d. Sleep de knop NAAM naar de knop KWARTAAL en je krijgt alleen de cijfers van ofwel één ofwel alle verkopers en van één ofwel alle kwartalen.

8.5

TIP

Een paginaveld gedraagt zich als een filter. Het is mogelijk om bepaalde gegevens te verbergen.

TIP

Zet de muiswijzer in de draaitabel en klik met de rechtermuisknop. Kies DRAAIGRAFIEK. Wat is het effect? Welke bladtab wordt toegevoegd?

De grafische voorstelling



129

Opdracht 3. De verkoopcijfers grafisch voorstellen a. Ontwerp de bovenstaande grafiek op basis van de eerste draaitabel. Tracht dezelfde opmaak te benaderen. Kijk vooral naar de assen en de opmaak van de gegevensreeksen. b. Activeer de werkbalk TEKENEN en voeg de pijl en de tekst “Verlofperiode” aan de grafiek toe.

8.6

Een draaitabel wijzigen Opdrachten 4. Selecteer in de eerste draaitabel de cellen met 'Els' en 'Sofie' en roep via de rechtermuisknop een menu op waarin je VERBERGEN kiest. Wat is het resultaat in de grafiek? 5. Dubbelklik in de eerste draaitabel op het veld NAAM. Het volgende dialoogvenster verschijnt:

Klik op de knop VERBERGEN. Wat is het effect (ook in de grafiek)? TIP

130

Je kunt een werkblak DRAAITABEL op het scherm zetten. Als je op de knop WIZARD DRAAITABELLEN klikt wordt automatisch de derde stap van de wizard getoond. Zorg er wel voor dat de cursor in een draaitabel staat. Ook via DATA, DRAAITABEL- EN DRAAIGRAFIEKRAPPORT kom je in de derde stap van de wizard terecht.



Besluit Draaitabellen maken het mogelijk om gegevens te groeperen en om gegevens vanuit verschillende gezichtspunten te bekijken. Op de gegroepeerde gegevens worden bewerkingen uitgevoerd zoals Som, Aantal, Max, … Een draaitabel bevat een gebied voor de paginavelden, een voor de kolomvelden en een voor de rijvelden. Elk van die velden kan binnen het eigen gebied of naar een ander gebied versleept worden. De brongegevens hoeven niet noodzakelijk uit hetzelfde werkblad te komen. Ze kunnen uit verschillende werkbladen afgelezen worden en kunnen zelfs rechtstreeks uit een database opgehaald worden.

Wat je moet kennen en kunnen:      

een draaitabel maken; een kolomveld, rijveld, paginaveld en gegevensveld instellen; een draaitabel aanpassen; de bewerking op een gegevensveld veranderen; gegevens uit een draaitabel grafisch voorstellen; commentaar op een grafiek aanbrengen.

Opdrachten 6. De doorlichting komt! Het werkblad OPGAVEN.XLS bevat een werkblad ATTEST met de volgende inhoud: Deze tabel bevat de attesten van de leerlingen van de schooljaren 1989-1990 tot en met 1992-1993. De school heeft twee afdelingen: TSO en BSO. Dirk Teur, de directeur van een grote handelsschool aan de rand van de grootstad, wil naar aanleiding van een nakende doorlichting de volgende informatie uit deze cijfers afleiden:  Hoeveel A-, B- en C-attesten zijn er per studierichting gedurende die vier jaar uitgereikt?  Hoe evolueert het leerlingenaantal in de richting BI (Boekhouden-informatica) en H (Handel)? Hoeveel studenten mag hij in 5BI verwachten voor het schooljaar 1993-1994?  Welke attesten behaalde een willekeurige leerling gedurende zijn schoolcarrière? Neem bijvoorbeeld leerling 3402. Kopieer dit werkblad naar de werkmap GRAFIEKXP.XLS en maak de nodige draaitabellen om aan de vraag van de heer Dirk Teur te voldoen.



131

Hoofdstuk 9

9.1

Problemen door middel van gegevensanalyse oplossen

Doelgericht zoeken Dikwijls ken je wel het resultaat van een formule, maar weet je niet met welke invoerwaarden dit resultaat bereikt kan worden. De techniek van doelzoeken bestaat erin een waarde in een bepaalde cel zodanig te doen variëren totdat een formule die van die cel afhankelijk is het gewenste resultaat geeft. Probleemstelling De verkoopprijs van een product wordt bepaald door de inkoopprijs vermeerderd met de winstmarge. Daarbij worden nog kosten gerekend zoals de verpakking en de controle. Op dit alles komt nog 21% BTW. In het voorbeeld wordt een verkoopprijs van € 138,82 berekend. Stel dat de verkoopprijs omwille van reclamedoeleinden tot € 134,99 moet zakken en dat alleen de winstmarge aangepast mag worden. Probleemanalyse Je zou de winstmarge met kleine intervallen kunnen doen dalen en dan kijken of de verkoopprijs voldoet. Het zal dan wel een tijdje proberen en testen zijn totdat je de gewenste eindwaarde bereikt. Excel heeft een optie, namelijk doelzoeken, waarmee vlot een gewenst eindresultaat bereikt wordt. Oplossing: Opdrachten 1. Een vooropgestelde verkoopprijs berekenen a. Open een nieuwe werkmap en bewaar ze als WAT ALSXP.XLS. b. Zet het volgende voorbeeld in een nieuw werkblad en noem het DOELZOEKEN.

Het is belangrijk dat het resultaat in B9 met een formule gevonden wordt. 132



c. Kies in de menubalk EXTRA, DOELZOEKEN. Het dialoogvenster DOELZOEKEN verschijnt. op de cel B9 met de verkoopprijs;  Cel instellen moet op waarde 134,99 gezet worden;  Op waarde door wijziging van de winstmarge in de cel B4.  Door wijzigen van cel d. Nadat je op de OK-knop geklikt hebt wordt de oplossing tijdelijk ingevuld en verschijnt een nieuw dialoogvenster. Als je niet akkoord bent klik je op ANNULEREN, in het andere geval klik je op OK en de waarde wordt blijvend ingevuld.

2. Aan welke prijs moet je inkopen opdat de verkoopprijs € 130,00 zou zijn? [€ 93,76]

9.2

Eendimensionale ‘Wat als?’-analyse Als je formules in een werkblad ingevoerd hebt kun je met behulp van een ‘Wat als?’-tabel een ‘Wat als?’-analyse uitvoeren om na te gaan welke invloed wijzigingen van bepaalde waarden in de formules hebben op de resultaten van deze formules. Dergelijke gegevenstabellen hebben de volgende voordelen:  je kunt in één bewerking een groot aantal varianten berekenen;  je kunt de resultaten van alle varianten naast mekaar in een werkblad bekijken en deze met mekaar vergelijken. Er zijn twee soorten ‘Wat als?’-tabellen:  een enkele of eendimensionale invoertabel gaat na wat het effect is van één variabele voor één of meer formules;  een dubbele of tweedimensionale invoertabel gaat na wat het effect is van twee variabelen op één formule. In dat geval spreekt men van een tweedimensionale ‘Wat als?’analyse. Probleemstelling Ik wens € 100 000 te lenen en maandelijks terug te betalen over een periode van 10 jaar. Toon in een enkele invoertabel wat het effect van de rente op de annuïteit en op het totaal terug te betalen bedrag is.



133

Probleemanalyse Zet de vaste gegevens zoals het te lenen bedrag, de rente per jaar en het aantal termijnen in een werkblad. Voorzie ruimte voor de ‘Wat als’-tabel die het verband tussen de rente, de annuïteit en het terug te betalen bedrag toont. Zet de verschillende waarden die je van de rente wilt testen in de gegevenstabel. Voorzie in de gegevenstabel een lege kolom voor elke formule waarin je de variabele rente wilt invoegen. De formules komen bovenaan in de gegevenstabel, zoals op het voorbeeld getoond wordt. De annuïteit bereken je met de formule BET(). Het totale terugbetaalde bedrag is gelijk aan de annuïteit maal het totale aantal termijnen. Genereer ten slotte de enkele ‘Wat als?’-tabel op basis van de rente die variabel is. Oplossing: Opdracht 3. Een enkelvoudige invoertabel ontwerpen a. Open de werkmap WAT ALSXP.XLS. b. Zet het volgende voorbeeld in een nieuw werkblad en noem het INVOERTABEL1. De cellen E3 en F3 bevatten een formule. Absolute adressen zijn helemaal niet nodig in deze formules: ze worden toch niet gekopieerd. c. Selecteer de tabel D3:F9 en open het TABEL-dialoogvenster via DATA, TABEL. TIP

Het is zeer belangrijk dat je de formules in E3 en F3 ook selecteert. Vandaar dat deze formules juist boven de lege kolommen moeten staan.

Zet de verwijzing naar de variabele, $B$4, in KOLOMINVOERCEL omdat de invoerwaarden (D4:D9) in een kolom staan. De formules in de cellen E3 en E5 bevatten elk een verwijzing naar die cel. d. Klik op de OK-knop om de tabel automatisch te berekenen. In feite wordt de rente van B4 in de formules van E3 en F3 vervangen door de rente in D4 en het resultaat wordt in E4 en F4 afgedrukt. Hetzelfde herhaalt zich voor de andere rente-waarden. Er is één variabele (de rente) die in twee formules vervangen wordt.

134



e. Het resultaat ziet er als volgt uit:

Alle formules in het gebied E4:F9 zien er als volgt uit: {=TABEL(;B4)}.

9.3

Tweedimensionale ‘Wat als?’-analyse Probleemstelling Je wenst € 100 000 te lenen en maandelijks terug te betalen over een periode van 10 jaar. Toon in een tweedimensionale invoertabel wat het effect van de rente en het terugbetalingstermijn is op de annuïteit. Probleemanalyse In dit geval worden twee variabelen in de functie BET() vervangen. De functie BET() kan alleen maar in de linkerbovenhoek van de gegevenstabel staan. De verschillende waarden van de twee variabelen, de termijn en de rente, staan respectievelijk in een rij en in een kolom afgebeeld. Men spreekt van rijen kolominvoerwaarden. Zie het bijgevoegde voorbeeld. Oplossing: Opdracht 4. Een tweevoudige invoertabel ontwerpen a. Open de werkmap WAT ALSXP.XLS. b. Zet het volgende voorbeeld in een nieuw werkblad en noem het INVOERTABEL2. Alleen de cel D3 bevat een formule. Absolute adressen zijn ook hier overbodig. Zoals je in het voorbeeld kunt zien varieert de rente van 4,25 tot 5,50% en het aantal termijnen kan 120, 180 en 240 zijn. Selecteer de tabel D3:G9 en open het TABEL-dialoogvenster via DATA, TABEL



135

Omdat de invoerwaarden voor het aantal termijnen in een rij opgesomd staan, is $B$5 de RIJ-INVOERCEL. De invoerwaarden voor het rentepercentage staan in een kolom opgesomd. Daarom is $B$4 de KOLOMINVOERCEL. c. Klik op de OK-knop om de tabel automatisch te berekenen. De twee variabelen worden in de formule D3 en het resultaat wordt op de juiste positie in de gegevenstabel afgedrukt. d. Het resultaat ziet er als volgt uit met in elke cel ook hier dezelfde formule: {=TABEL(B5;B4)}

9.4

‘Wat als’-scenario’s Doelzoeken berekent invoerwaarden op basis van een resultaat. Een scenario laat invoervariabelen veranderen en kijkt na wat het resultaat van deze verandering is. Probleemstelling De firma Kluns & Co wil eindelijk overgaan tot de aanschaf van een beperkt computerpark. Daarom wordt een informaticaverantwoordelijke aangeduid die de opdracht krijgt om een begrotingsvoorstel in te dienen. Probleemanalyse

Zet het begrotingsvoorstel in een werkblad. Het eindbedrag lijkt misschien nogal veel, daarom wordt een goedkoper alternatief voorstel uitgewerkt waarbij er maar acht computers, geen laserprinter maar wel twee inkjetprinters voorzien worden en waarbij het aantal werkuren tot vijf herleid wordt. Een ander, maar duurder alternatief is dat er twaalf computers, drie laserprinters, een inkjetprinter en vijftien werkuren voorzien worden.

136



Al deze alternatieven kunnen via een zogenaamd scenario in het werkblad opgeslagen worden. Oplossing: Opdracht 5. Drie scenario’s opstellen i.v.m. de computerbegroting a. Open de werkmap WAT ALSXP.XLS. b. Zet het bovenstaande voorbeeld in een nieuw werkblad en noem dit werkblad SCENARIO. Alle resultaten in kolom D zijn van formules afkomstig. Verzorg de opmaak van de tabel. c. Geef de cellen B4 (Pc), B5 (Laserprinter), B6 (Inkjetprinter), B8 (Werkuren) en D10 (Totaal EUR) een zinvolle naam. Dit is niet echt noodzakelijk, maar het is wel handig. d. Roep via EXTRA, SCENARIOBEHEER het dialoogvenster SCENARIOBEHEER op en voeg een nieuw scenario toe via de knop TOEVOEGEN.

Vul een gepaste scenarionaam in, in dit geval het goedkoopste scenario. Welke cellen mogen veranderd worden? De cellen met het aantal computers, het aantal printers en het aantal werkuren. (We veronderstellen dat de prijzen constant zijn.) Klik op de OK-knop als je klaar bent. e. In het volgende dialoogvenster, SCENARIOWAARDEN, kun je de alternatieve waarden intikken. Klik op TOEVOEGEN om nog een scenario toe te voegen. Noem dit scenario ‘Gewoon’ en laat in het venster SCENARIOWAARDEN de oorspronkelijke waarden staan. Voeg nog een scenario toe en noem het ‘Duurste’. Vul hier de waarden in die in de probleemanalyse voorgesteld zijn.



137

f.

9.5

Uiteindelijk ziet het dialoogvenster SCENARIOBEHEER er als volgt uit:

WEERGEVEN

Als je een scenario kiest en op WEERGEVEN klikt worden automatisch de cellen gewijzigd die in aanmerking komen om gewijzigd te worden en je kunt het nieuwe eindtotaal aflezen.

VERWIJDEREN

Het geselecteerde scenario wordt verwijderd.

BEWERKEN

Het geselecteerde scenario kun je wijzigen.

SAMENVOEGEN

Hiermee voeg je scenario's uit werkbladen in een geopende werkmap samen.

SAMENVATTING

Hiermee maak je een overzichtsrapport van het scenario of een scenariodraaitabel als een nieuw tabblad in de werkmap. Zie het voorbeeld hieronder:

Problemen via de Oplosser oplossen Met de OPLOSSER kun je naar een oplossing zoeken waarbij meerdere invoercellen gewijzigd worden. Het is zelfs mogelijk om die wijzigingen binnen bepaalde grenzen te laten gebeuren.

138



TIP

Als de OPLOSSER niet voorkomt in het menu EXTRA, dan moet je via EXTRA, INVOEGTOEPASSINGEN de optie INVOEGTOEPASSING OPLOSSER nog aanvinken.

Probleemstelling Het bedrijf Dynavison nv maakt televisies, radio's en luidsprekers. Voor deze producten wordt gebruik gemaakt van dezelfde onderdelen. De levering van deze onderdelen is aan beperkingen onderhevig, en je moet bepalen bij welke pakketsamenstelling je de meeste winst maakt. Bij een productie van 50 stuks van elk is de totale winst € 7 064,50. Probleemanalyse Zet de volgende gegevens in een werkblad:

Zoek met de methode van de OPLOSSER welke aantallen je moet produceren om zoveel mogelijk winst te maken en zo weinig mogelijk onderdelen over te houden. De OPLOSSER vraagt naar drie soorten invoer:  Welke cel moet een bepaalde waarde krijgen? Dit noemt men de doelcel.  Welke zijn de cellen die gewijzigd mogen worden?  Welke beperkingen of restricties gelden voor deze cellen?

TIP

Doelcel

B16

Je wilt zoveel mogelijk winst maken.

Veranderende cellen

C4:E4

Het aantal stuks dat van elk product gefabriceerd moet worden.

Beperkingen

H10:H14 <= G10:G14

Het aantal gebruikte onderdelen moet kleiner zijn dan of gelijk zijn aan het aantal onderdelen in voorraad

C4:E4 >= 0

Het aantal te fabriceren stuks moet groter zijn dan of gelijk zijn aan 0.

Als OPLOSSER niet voorkomt in het menu EXTRA dan moet je die toepassing via EXTRA, INVOEGTOEPASSINGEN, INVOEGTOEPASSING OPLOSSER toevoegen.



139

Oplossing: Opdracht 6. Met de OPLOSSER de maximale winst zoeken a. Open de werkmap WAT ALSXP.XLS en zet het voorbeeld in een leeg werkblad. Gebruik formules waar nodig! Verander de naam van het werkblad in OPLOSSER. b. Via EXTRA, OPLOSSER roep je het dialoogvenster PARAMETERS OPLOSSER op.

c. Klik op de knop TOEVOEGEN om de restricties toe te voegen en voeg de twee beperkingen toe.

d. Klik ten slotte op de knop OPLOSSEN. Je krijgt een boodschap dat de OPLOSSER een oplossing gevonden heeft en er wordt gevraagd of je de oplossing behouden wilt. Welke oplossing wordt er voorgesteld?

140



Besluit De Doelzoeker en de Oplosser maken het mogelijk om invoerwaarden te berekenen op basis van een bepaald resultaat dat bereikt moet worden. De Oplosser werkt veel krachtiger dan de Doelzoeker: via de Oplosser kun je bijvoorbeeld ook beperkingen instellen. Met behulp van een ‘Wat als?’-tabel kun je een ‘Wat als?’-analyse uitvoeren op formules in een werkblad om na te gaan welke invloed wijzigingen van bepaalde waarden in de formules op de resultaten van deze formules hebben. Een eendimensionale gegevenstabel kan het effect van één variabele op meerdere formules tonen, een tweedimensionale tabel toont het effect van twee variabelen op één formule. Een scenario laat invoerwaarden veranderen en kijkt vervolgens wat het resultaat van die verandering is. Via scenario’s kunnen een aantal alternatieven getoond worden waaruit een beleidsbeslissing kan afgeleid worden.

Wat je moet kennen en kunnen:    

scenario’s opstellen om beleidsbeslissingen voor te bereiden; eendimensionale en tweedimensionale ‘Wat als?’-analyses opstellen; eenvoudige problemen uit de lineaire programmatie via Doelzoeken oplossen; de Oplosser gebruiken om gewenste doelstellingen te realiseren.

Opdrachten 7. Voeg een nieuw werkblad aan de map WAT ALSXP.XLS toe en noem het COMMISSIE. De firma Kluns & Co heeft 8 vertegenwoordigers in dienst die een commissie van 1,50% op de omzet krijgen. De bedrijfsleider wil weten hoeveel commissie hij in het totaal moet betalen als het commissiepercentage verandert van 0,25 tot 2,75% met stappen van 0,25%. Toon het resultaat in een eendimensionale invoertabel. [2,75% € 82 208,50] 8. Voeg een nieuw werkblad aan de map WAT ALSXP.XLS toe en noem het OMZET. In 1991 bedroeg de omzet van de firma Kluns & Co € 23 500. De kosten op de omzet bedragen 45%. Hieruit kun je de winst berekenen. Stel dat de omzet elk jaar met 9% stijgt en dat het kostenpercentage onveranderd blijft. Bereken de omzet, de kosten en de winst voor elk van de volgende jaren tot in 1999.



141

Ga vervolgens voor het jaar 1999 na wat het effect is op de omzet, de kosten en de winst als het groeipercentage varieert van 10,5% tot 12,75% met stappen van 0,25%. Toon het resultaat in een eendimensionale invoertabel. [groei 12,75%, omzet € 61 376,24] 9. Open nogmaals het werkblad COMMISSIE. Stel dat de totale commissie maar € 32 500 mag zijn. Hoeveel bedraagt dan het commissiepercentage? Welke methode gebruik je om tot het resultaat te komen? [1,09%] 10. Maak in het werkblad COMMISSIE twee scenario’s en noem ze ‘laagste’ en ‘hoogste’. Het scenario ‘laagste’ toont het resultaat als het commissiepercentage 0,5% is en het scenario ‘hoogste’ toont het resultaat bij 3,5%.

142



Hoofdstuk 10

De loonberekening

10.1 Probleemstelling Elke maand moet de firma Jos Computers bvba het loon van haar werknemers uitrekenen en betalen. De arbeiders krijgen een uurloon, de bedienden een maandloon. De bedienden hebben een vaste maandwedde. De arbeiders worden per uur betaald: ze werken 8 uur per dag, de overige uren zijn overuren. De overuren worden elke dag apart opgeschreven en worden volgens een variabel tarief vergoed, bijvoorbeeld tegen 150 %. De loonberekening zou geautomatiseerd moeten worden. Als uitbreiding kun je een tabel met het aantal werkuren per maand voorzien, zodat er niet elke maand evenveel werkuren hoeven te zijn. Het is hier niet onze bedoeling om exacte en up-to-date tabellen voor de bedrijfsvoorheffing en de vermindering voor kinderen ten laste weer te geven. We willen gewoon een schema aanreiken dat gebruikt kan worden om een loonberekening uit te voeren.

10.2 Probleemanalyse ARBEIDER

BEDIENDE

uurloon invoer aantal uren invoer aantal overuren berekening brutoloon brutomaandwedde basis RSZ = bruto * 1,08 berekening 13,07 % RSZ berekening 13,07 % RSZ berekening brutoloon berekening brutowedde opzoeken bedrijfsvoorheffing opzoeken vermindering kinderen ten laste opzoeken vermindering alleenstaande berekening van het nettoloon berekening voorschot De oplossing van dit probleem gebeurt in Excel in een map LOONBEREKENING. Deze map bestaat uit verschillende werkbladen: 



Verschillende werkbladen met opzoektabellen: de bedrijfsvoorheffing: BEDRIJFSVOORHEFFING, vermindering voor kinderen ten laste en alleenstaanden: VERMINDERING, de codes burgelijke stand: BURG_STAND, de variabelen zoals Overuur, Basis RSZ,…: VARIABELEN; WERKNEMER: de persoonlijke gegevens van de werknemer;



143

 

LOONSTAAT: de verzameltabel van de loon- en salarisstaat van alle werknemers waarin alle berekeningen gebeuren; LOONBRIEF: de individuele loon- of salarisbrief die uiteindelijk afgedrukt wordt.

TIP

Voor een werknemer is er een probleem i.v.m. het aantal gewerkte uren per maand: dat is niet elke maand evenveel. Daarom starten we voorlopig met 152 uren per maand, en we voegen er nog een aantal overuren aan toe.

10.2.1 Welke opzoektabellen heb je nodig? Opdracht 1. Ontwerp de hieronder afgebeelde werkbladen a. Open een nieuwe werkmap en noem ze LOONBEREKENINGXP.XLS. b. het werkblad BEDRIJFSVOORHEFFING De twee schalen worden in het werkblad BEDRIJFSVOORHEFFING getoond.

Het opzoekbereik omvat het gebied A4:C249. Geef dit bereik de naam “voorheffing”. De lege plaatsen in Schaal 1 en Schaal 2 zijn met 0 opgevuld. Waarom? TIP

Je werknemers en bedienden mogen niet meer dan € 3 346,56 per maand verdienen.

TIP

De kleinste waarde in kolom A moet 0 zijn. Waarom?

c. het werkblad VERMINDERING met de verminderingen voor kinderen ten laste en voor alleenstaanden

144



Noem het bereik A5:B15 “kinderen” en de cel E4 “alleenstaande”. d. het werkblad BURG_STAND Noem het bereik A4:C7 “BurgStand”. Spaties in de naam zijn niet toegestaan.

e. het werkblad VARIABELEN Geef de volgende cellen een naam: cel A3 “basis”, cel A4 “RSZ”, cel A5 “overuur” cel A6 “EUR”.

TIP

In een formule zul je nergens 13,07 intikken, wel de naam “RSZ” die naar de inhoud van cel A4 verwijst.

10.2.2 Het werkblad WERKNEMER Dit werkblad bevat de persoonlijke gegevens van de werknemers:  een werknemerscode;  de naam en het adres;  de functie: arbeider of bediende;  de code voor de burgerlijke stand:  1 = alleenstaande;  2 = gehuwd en beiden werken;  3 = gehuwd en slechts één partner werkt;  4 = gescheiden; J. Gils – E. Goossens


145

 het aantal kinderen ten laste;  het uurloon en het voorschot voor de arbeiders;  de maandwedde voor de bedienden.

TIP

Ter verduidelijking zijn de kolommen genummerd in rij 3. Dit is handig om via de formule VERT.ZOEKEN() informatie in deze tabel op te zoeken.

Opdracht 2. Ontwerp het werkblad WERKNEMER waarin je de persoonlijke gegevens van de werknemers bewaart. Tracht dezelfde opmaak als in het voorbeeld te bekomen. Noem het bereik A6:M20 “werknemer”. TIP

Dit werkblad bevat geen enkele formule: alle waarden worden ingetypt!

10.2.3 Het werkblad LOONSTAAT Dit is het werkblad waarin alle berekeningen gebeuren.

Het is een verzameltabel van de loon- en salarisstaat van alle werknemers met daarin:  de werknemerscode;  eventueel ook de naam, burgerlijke stand, aantal kinderen, de functie, het maandloon;  een verwijzing naar de gepaste schaal van de bedrijfsvoorheffing; 146



          

het aantal gewerkte uren en het aantal overuren voor de arbeiders; de berekening van het brutoloon; de berekening van de basis voor de RSZ-berekening; de berekening van de werknemersbijdrage; de berekening van het belastbare inkomen; de bepaling van de bedrijfsvoorheffing; de bepaling van de aftrek voor kinderen ten laste; de bepaling van de aftrek voor alleenstaanden; de berekening van de crisisbelasting; de berekening van de nettowedde; het voorschot van de arbeiders.

TIPS  Alleen in de kolom met het aantal gewerkte uren en de overuren (kolom I en J) mag je een waarde kunnen intikken; alle andere kolommen bevatten formules die de getoonde waarden berekenen of ergens opzoeken.  Nummer ook hier tijdelijk de kolommen.  Als in de kolom A geen werknemersnummer ingevuld is of als je een werknemersnummer verwijdert, dan mag er nergens in die rij iets afgedrukt worden, zelfs geen foutmeldingen.  Het woord ‘Arbeider’ of ‘Bediende’ mag nooit in een formule voorkomen, je moet daar altijd de hoofdletters ‘A’ of ‘B’ gebruiken.  De verminderingen op de bedrijfsvoorheffing kunnen niet hoger zijn dan het bedrag van de bedrijfsvoorheffing zelf!  Zet in rij 8 de juiste formules zodat je de hele rij naar beneden kunt kopiëren: arbeider of bediende, de formule moet er gepast op reageren. Opdracht 3. Ontwerp het werkblad LOONSTAAT waarin het loon van elke werknemer uitgerekend wordt. Tracht dezelfde opmaak als in het voorbeeld te bekomen, zeker voor de zesde rij. Noem het bereik A8:T20 “loonstaat”. Formules: [A8]

=ALS(Werknemer!A6="";"";Werknemer!A6) Op deze wijze kopieer je het werknemersnummer van de eerste werknemer uit het werkblad WERKNEMER.

[B8]

Als in A8 geen werknemersnummer staat, moet er niets gebeuren, anders moet je in de tabel met de bereiknaam “werknemer” dat nummer zoeken en de naam en de voornaam van de werknemer aflezen . Formule:

[C8]

Op dezelfde wijze lees je de code van de burgerlijke stand af. Formule:

[G8]

Als in cel E8 de code “B” staat, dan wordt de maandwedde in de tabel “werknemer” opgezocht, in het andere geval gebeurt er niets.



147

Formule:

148

[H8]

Als in cel E8 de code “A” staat, dan wordt het uurloon opgezocht. In het andere geval gebeurt er niets. Formule:

[K8]

Als in cel E8 de code “A” staat, dan wordt het maandloon van de arbeider berekend, in het andere geval wordt de maandwedde van de bediende uit cel G8 gekopieerd. Maak in deze formule gebruik van de variabele “overuur”. Formule:

[L8]

Als er in K8 geen waarde staat, doe dan niets, in het andere geval test je of de werknemer een arbeider is. In dat geval bereken je dan de basis van de RSZheffing, in het andere geval kopieer je de totale brutowedde. Formule:

[M8]

Als er in K8 geen waarde staat, doe dan niets, in het andere geval bereken je de RSZ-bijdrage. Formule:

[N8]

Als er in K8 geen waarde staat, doe dan niets, in het andere geval bereken je het belastbare inkomen. Dit is het brutoloon verminderd met de werknemersbijdrage. Formule:

[O8]

Als er in N8 geen waarde berekend is moet er niets gebeuren, in het andere geval moet de bedrijfsvoorheffing opgezocht worden in het bereik “voorheffing”. Welke schaal je gebruiken moet, vind je in cel F8. Formule:

[P8]

Als er in N8 geen waarde berekend is moet er niets gebeuren, in het andere geval moet er in de tabel “kinderen” naar het bedrag van de vermindering gezocht worden. Het aantal kinderen vind je in cel D8. Formule:

[Q8]

Als er in N8 geen waarde berekend is moet er niets gebeuren, in het andere geval moet in deze cel de vermindering voor een alleenstaande gezet worden als de werknemer een alleenstaande is, anders zet je best 0 in deze cel. Formule:

[R8]

Als er in N8 geen waarde berekend is moet er niets gebeuren, in het andere geval bereken je de totale bedrijfsvoorheffing: dit is de bedrijfsvoorheffing in Rekenblad: Excel 2003


cel O8 min de aftrek voor kinderen en alleenstaande. Als de totale aftrek groter is dan de bedrijfsvoorheffing dan wordt de totale bedrijfsvoorheffing 0. Formule: [S8]

Als er in N8 geen waarde berekend is moet er niets gebeuren, in het andere geval trek je de totale bedrijfsvoorheffing af van het belastbare inkomen. Formule:

10.2.4 Het werkblad LOONBRIEF Naast het logo van de firma en de firmagegevens, kun je de gewenste werknemer invoeren via een keuzelijst of gewoon via zijn werknemersnummer. De gegevens van het onderstaande voorbeeld moeten zeker op je afdruk terug te vinden zijn.

Als titel vermeld je ‘Loonbrief’ voor een arbeider en ‘Salarisbrief’ voor een bediende. J. Gils – E. Goossens


149

Zorg er ook voor dat je bij een bediende spreekt van nettowedde en bij een arbeider van nettoloon. Op de salarisbrief van een bediende wordt het aantal gewerkte uren niet vermeld maar wel een maandwedde. TIPS  Alle uitkomsten moeten via formules opgezocht worden in de werkbladen WERKNEMER en LOONSTAAT. Alleen het nummer van de werknemer mag ingetikt worden.  Als je de functiecode “A” of “B” van de werknemer opzoekt en in het werkblad vermeldt (cel E22) dan worden de formules in de cellen, waarvan de inhoud van het type werknemer afhankelijk is, veel eenvoudiger.  In de tekst “Overuren 150 %” moet 150 als een variabele afgedrukt worden: = "Overuren " & overuur * 100 & " %". Verklaar deze formule.  Bij een bediende wordt de inhoud van de cellen B25 en B26 niet getoond.  Schrijf de formules eerst in woorden en vertaal ze dan! Opdracht 4. Ontwerp het werkblad LOONBRIEF waarin het loon van elke werknemer uitgerekend wordt. Tracht dezelfde opmaak als in het voorbeeld te bekomen. Formules: [D2]

De titel “Loonbrief / Salarisbrief” Als er in cel E22 geen code staat moet er niets gebeuren, maar als de code “A” is moet “Loonbrief” anders “Salarisbrief” afgedrukt worden. Formule:

[E12]

De naam en de voornaam van de werknemer Als cel G9 leeg is moet er niets gebeuren, in het andere geval moet je via dit nummer de naam en de voornaam van de werknemer in de tabel “werknemer” opzoeken. Formule:

[C20]

Alleenstaande Als cel G9 leeg is moet er niets gebeuren, in het andere geval moet je eerst in de tabel “werknemer” naar de code van de burgerlijke stand zoeken en dan hiermee de betekenis van die code in de tabel “burg_stand” opzoeken. Formule: =ALS($G$9="";"";VERT.ZOEKEN(VERT.ZOEKEN($G$9;werknemer;8; ONWAAR);BurgStand;3;ONWAAR))

[C24]

Het uurloon of de maandwedde Als G9 leeg is moet er niets gebeuren, anders moet je naar de code in E22 kijken: als het een arbeider is, lees dan in de tabel Loonstaat het aantal gewerkte uren aan 100% af in kolom 8; in het andere geval lees je de maanwedde in de 7de kolom af. Formule:

150



[E45]

Het nettoloon Als er geen werknemersnummer in cel G9 ingevuld is moet je niets doen, in het andere geval moet je via dat nummer het nettoloon in de tabel “loonstaat” aflezen. Formule:

10.3 De werkbladen beveiligen Opdracht 5. Beveilig de verschillende werkbladen van je loonberekening. Het werkblad WERKNEMER wordt niet beveiligd. In het werkblad LOONSTAAT zijn alleen de cellen met het aantal gewerkte uren en overuren niet beveiligd. In het werkblad LOONBRIEF is alleen de cel waarin het werknemersnummer komt niet beveiligd; de rest van de cellen mag je niet kunnen wijzigen. TIP

Gebruik in de klas liefst geen wachtwoord om de cellen te beveiligen want dan kan de leraar geen wijzigingen meer aanbrengen in je werkblad!

10.4 Macro’s toevoegen en een menusysteem ontwerpen Opdrachten 6. Ontwerp een menusysteem in een leeg werkblad waarmee je vlot van het ene blad naar het andere beweegt en terug.

a. Voeg een nieuw werkblad toe en noem het MENU. b. Zet ergens het logo van de firma via INVOEGEN, FIGUUR, UIT BESTAND en voeg via een tekstvak de firmanaam en het adres toe. c. Neem vier macro’s op: NAAR_LOONSTAAT, NAAR_LOONBRIEF, NAAR_WERKNEMER en NAAR_MENU. d. Neem ten slotte nog een vijfde macro op die alleen maar cel A1 van het huidige werkblad selecteert. Noem deze macro EINDE. Wijzig de programmacode van deze macro zodanig dat EXCEL afgesloten wordt.



151

e. Voeg een eerste opdrachtknop toe en geef deze knop de gewenste opmaak via SNELMENU, BESTURINGSELEMENT OPMAKEN. f. Kopieer deze knop viermaal en geef de knoppen het volgende opschrift: “Loonstaat”, “Loonbrief”, “Werknemer”, “Menu”en “Einde”. g. Verbind deze knoppen met de overeenkomstige macro’s. h. Kopieer vervolgens de eerste vier knoppen naar de werkbladen WERKNEMER, LOONSTAAT en LOONBRIEF en verwijder telkens de overbodige knop. TIP

Meerdere knoppen kun je selecteren met SHIFT + LINKERMUISKNOP, één enkele knop met de rechtermuisknop of met de PIJL uit de werkbalk TEKENEN.

Enkele handige commando’s: Excel afsluiten

Application.quit

Alle werkmappen sluiten

Workbooks.Close

Een bepaalde map sluiten

Workbooks("Map1.xls").Close SaveChanges:=False

Een bepaalde map openen

Workbooks.Open filename:="Map1.xls"

Een bepaalde map activeren

Workbooks("MAP1").Activate

Het huidige venster sluiten

ActiveWindow.Close

Een andere toepassing openen Application.ActivateMicrosoftApp xlMicrosoftAccess 7. Zet op het werkblad LOONBRIEF een knop die een macro LOONBRIEF_AFDRUKKEN oproept om deze loonbrief af te drukken. Neem eerst de macro LOONBRIEF_AFDRUKKEN op en wijs die vervolgens toe aan de knop AFDRUKKEN die je ook nog op het werkblad moet zetten. 8. Zet op het werkblad LOONBRIEF een knop die een macro LOONBRIEF_LEEG oproept om deze loonbrief leeg te maken. Neem eerst de macro LOONBRIEF_LEEG op en wijs die vervolgens toe aan de knop LEEG die je ook nog op het werkblad moet zetten. 9. Zet in het werkblad LOONBRIEF een knop, met als opschrift ‘Afdrukken’. Als je op die knop klikt moet er een dialoogvenster verschijnen met de vraag of je een afdrukvoorbeeld wilt. Als je geen afdrukvoorbeeld wilt, wordt automatisch via de printer afgedrukt. Het dialoogvenster moet ook een knop ANNULEREN tonen waarmee de gebeurtenis geannuleerd kan worden. 10. Zet op het werkblad WERKNEMER een knop die de macro WERKNEMER_SORTEREN oproept om de werknemers alfabetisch te sorteren. Neem eerst de macro op en wijs hem vervolgens toe aan de knop SORTEREN.

152



10.5 Een waarde in een cel via een dialoogvenster invullen Opdracht 11. Als je op de knop NUMMER klikt verschijnt een dialoogvenster waarin naar een werknemersnummer gevraagd wordt. De waarde die je hier intikt wordt vervolgens in cel G9 gezet.

a. Zet NIEUWE MACRO OPNEMEN aan. Noem deze macro NIEUW_NUMMER, klik vervolgens in cel G9 en zet hierin de waarde A101. Beëindig de macro en bekijk de code in de Visual Basic-editor Sub Nieuw_nummer() Range("G9").Select ActiveCell.FormulaR1C1 = "A101" Range("G10").Select End Sub Range("B3").Select ActiveCell.FormulaR1C1=

TIP

ga naar cel G9, verander de inhoud van deze cel, nadat je op Enter drukt kom je in cel G10

met deze uitdrukking selecteer je cel B3. met deze uitdrukking kun je de inhoud van de actieve cel wijzigen.

De uitdrukking ‘Range("G9").Select’ en ‘ActiveCell.FormulaR1C1 = "A101"’ kan een stuk eenvoudiger geschreven worden: [G9] = “A101”.

b. Vervang in de code = “A101” door: = InputBox("Geef het nummer","Welke werknemer ?", , 5000, 4000)

De functie INPUTBOX() Geeft een aanwijzing weer in een dialoogvenster, wacht tot de gebruiker tekst invoert of een knop kiest en geeft een String met de inhoud van het tekstvak als resultaat. Syntaxis: InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]) (Zoek via de helpfunctie de betekenis van de verschillende argumenten.) c. Zet een knop NUMMER in het werkblad en wijs aan deze knop de macro NIEUW_NUMMER toe. Test de werking van deze knop. TIP

Het is niet altijd nodig om een macro op te nemen: je kunt de code ook rechtstreeks in de gewenste module intikken.



153

10.6 De combobox (keuzelijst met invoervak) Opdracht 12. Via een keuzelijst de naam van de werknemer kiezen in het werkblad LOONBRIEF a. Ga naar het werkblad LOONBRIEF, zet de werkbalk WERKSET op het scherm en teken een keuzelijst met invoervak en roep het eigenschappenvenster van deze keuzelijst op. Kies hierin de optie GECATEGORISEERD. b. Via het eigenschappenvenster moet je een verbinding maken met het werkblad WERKNEMER waaruit de naam van de werknemer gekozen wordt. Vervolgens moet het nummer van de werknemer bewaard worden in cel G9 van het werkblad LOONBRIEF.

Vul de volgende eigenschappen van de keuzelijst in: (NAME)

cboWerknemer

LISTFILLRANGE LINKEDCELL BOUNDCOLUMN COLUMNCOUNT COLUMNWIDTHS COLUMNWIDTHS LISTROWS TEXTCOLUMN TEXT

154



10.7 Het aantal gewerkte uren opvragen 10.7.1 Probleemstelling Het is niet correct te stellen dat de arbeiders een vast aantal uren per maand werken want er zijn maanden met veel werkdagen en maanden met weinig werkdagen. De controle van het aantal gewerkte uren gebeurt met een tikklok: dit is een uurwerk waarmee ’s morgens het beginuur en ’s avonds het einduur op een kaart gestempeld wordt. Een kaart geldt maar voor één week en een nieuwe maand begint sowieso met een nieuwe kaart. 10.7.2 Probleemanalyse Wekelijks worden de gewerkte uren opgeteld en gesplitst in normale uren en overuren. Elke week is er een nieuwe kaart: er zijn maximaal vijf weken in een maand. Breng de eindtotalen over naar een werkblad; een werkblad voor elke werkweek: WEEK1, WEEK2, …, WEEK5. Ten slotte wordt op het werkblad MAAND de som van de werkuren uitgerekend.

De begin- en einddatum van elke week bepalen Als je de datum, altijd het begin van de maand, in de cel C3 van het werkblad MAAND intikt, dan moet automatisch in de werkbladen WEEK1, WEEK2, … de begindatum (B5) en de einddatum (D5) van de week ingevuld worden. De functie WEEKDAG(datum) geeft als resultaat het nummer van de dag in de week: zondag is de 1ste en zaterdag is de 7de dag. De volgende problemen kunnen zich voordoen:  als de eerste dag van de maand een zaterdag of een zondag is, dan zal de eerste week beginnen op de 3de of de 2de dag van de maand;  als de eerste week op een maandag begint, dan begint de tweede, de derde, vierde en vijfde week respectievelijk 7, 14, 21 of 28 dagen later dan de eerste week;  als de eerste week niet op een maandag begint, bijvoorbeeld op een woensdag, dan zal de volgende week (zeven dagen – aantal dagen van maandag tot woensdag) later beginnen;  het is mogelijk dat er geen vijfde week is: dan moet deze datum een nul-waarde krijgen. J. Gils – E. Goossens


155

De laatste dag van de week is altijd de eerste dag van de week plus 5 dagen. Het is mogelijk dat je in de laatste week dan al in de volgende maand zit. In dat geval neem je 1 dag minder en je herhaalt dat totdat je terug in dezelfde maand zit. In dit geval lost programmeercode heel wat problemen op alhoewel het zonder programmacode ook mogelijk is, maar veel complexer. De functie BEGINWEEK() wordt met twee parameters opgeroepen: de startdatum van de maand en het nummer van de week. Ze berekent de datum van de maandag van elke week. De functie wordt als volgt opgeroepen: (‘startdatum’ is de naam van cel C3 in het werkblad MAAND.) = BEGINWEEK(startdatum;B3) Omdat het mogelijk is dat deze functie, in geval van de vijfde week, een nulwaarde als resultaat geeft moet deze nulwaarde in een lege tekenreeks omgezet worden. =ALS(BEGINWEEK(startdatum;B3)=0;"";BEGINWEEK(startdatum;B3)) Public Function BeginWeek(dteDatum As Date, intWeek As Integer) As Date Dim dteHulp As Date Dim IntDagenVoorbijMaandag As Integer IntDagenVoorbijMaandag = 0 ' de 1ste week Select Case WeekDay(dteDatum) Case 1 ' Zondag dteHulp = dteDatum + 1 Case 7 ' Zaterdag dteHulp = dteDatum + 2 Case Else ' Werkdag dteHulp = dteDatum IntDagenVoorbijMaandag = WeekDay(dteHulp) – 2 End Select Select Case intWeek Case 2 ' de 2de week dteHulp = dteHulp + 7 – IntDagenVoorbijMaandag Case 3 ' de 3de week dteHulp = dteHulp + 14 – IntDagenVoorbijMaandag Case 4 ' de 4de week dteHulp = dteHulp + 21 – IntDagenVoorbijMaandag Case 5 ' de 5de week dteHulp = dteHulp + 28 – IntDagenVoorbijMaandag If Month(dteHulp) <> Month(dteDatum) Then dteHulp = 0 End If End Select BeginWeek = dteHulp End Function

De functie EINDEWEEK() wordt met één parameter opgeroepen, namelijk de begindatum van de week. Ze berekent de einddatum van die week en als de einddatum in de volgende maand valt, wordt de einddatum verminderd totdat deze datum terug in de huidige maand valt. De functie wordt als volgt opgeroepen: = EINDEWEEK(B5). Omdat het mogelijk is dat de begindatum van de week een lege tekenreeks bevat moet je dit ook nog even testen: =ALS(B5="";"";EindeWeek(B5)).

156



Public Function EindeWeek(dteDatum As Date) As Date Dim dteHulp As Date Dim IntDagenTotVrijdag As Integer IntDagenTotVrijdag = 4 Select Case WeekDay(dteDatum) Case 1, 2 ' vier dagen van maandag tot vrijdag dteHulp = dteDatum + IntDagenTotVrijdag Case Else IntDagenTotVrijdag = 6 - WeekDay(dteDatum) dteHulp = dteDatum + IntDagenTotVrijdag End Select Do While Month(dteHulp) <> Month(dteDatum) dteHulp = dteHulp – 1 Loop EindeWeek = dteHulp End Function

Het werkblad MAAND Aan de cel C3 is een opmerking toegevoegd. Geef deze cel een naam: ‘StartDatum’. De knop ‘Werkuren afvagen’ start een macro die de werkbladen WEEK1, WEEK2, … als groep selecteert en de werkuren in groep afvaagt. Alleen de code van de arbeiders wordt in het werkblad MAAND ingetikt. De naam wordt in de tabel WERKNEMERS opgezocht. De som van de werkuren vind je door de overeenkomstige cellen uit de werkbladen WEEK1, … op te tellen. Deze bewerking moet alleen maar gebeuren als in de overeenkomstige cel van kolom A een werknemerscode ingevuld is. De werkbladen WEEK1, WEEK2, … Deze werkbladen kunnen als groep ontworpen en bewerkt worden. Als je vijf lege werkbladen toevoegt en ze alle vijf selecteert dan volstaat het om één werkblad te ontwerpen: automatisch heb je dezelfde inhoud en opmaak in de andere werkbladen. Het enige wat je achteraf nog moet wijzigen is het weeknummer. De namen van de arbeiders worden via een verwijzing naar de code en de naam in het werkblad MAAND ingevuld. TIP

Hou de controletoets ingedrukt en klik met de linkermuisknop op de bladtabs van de werkbladen die je als groep wilt bewerken.

10.7.3 Oplossing Opdracht 13. De werkbladen MAAND, WEEK1, … WEEK5 ontwerpen a. Voeg zes nieuwe werkbladen toe en geef ze de gevraagde naam. b. Ontwerp het werkblad Maand. De eindtotalen kun je pas toevoegen als de werkbladen Week1, … ontworpen zijn. c. Selecteer de werkbladen Week1, … als groep en ontwerp het werkblad Week1, zowel de inhoud, de formules als de opmaak. Maak de selectie ongedaan door even op een andere bladtab te klikken, selecteer één na één de werkbladen en pas het weeknummer aan.



157

d. Maak in het werkblad Maand de som van het aantal gewerkte uren. Als er geen werknemerscode ingevuld is moet het resultaat van de som een lege tekenreeks zijn. e. Verwijs in de tabel Loonstaat naar de uren in het werkblad MAAND.

158



Besluit Bij een loonberekening wordt van dezelfde soort formules en functies als bij de facturatie gebruik gemaakt: naast de functies Vert.Zoeken(), Als() en Afronden() zijn twee nieuwe functies van belang: InputBox() en MsgBox(). De functie MsgBox() toont een boodschap op het scherm. De gebruiker moet op die boodschap reageren door op een bepaalde knop te drukken. De functie geeft als resultaat de waarde van de knop waarop de gebruiker gedrukt heeft. Afhankelijk van die waarde kun je een gepaste actie ondernemen, meestal via de meervoudige keuze Select Case … End Select. De functie InputBox() toont een dialoogvenster waarin de gebruiker een invoer kan doen. Die invoer wordt dan toegekend aan een cel. Handig is de keuzelijst met invoervak: dit geeft de gebruiker de mogelijkheid om een werknemer uit een keuzelijst te kiezen. De keuzelijst uit de Werkset is nuttiger dan de keuzelijst uit de werkbalk Formulieren. Het is mogelijk om werkbladen met dezelfde opbouw als groep te ontwerpen en te bewerken.

Wat je moet kennen en kunnen:         

voor een gegeven probleem - de lonen moeten berekend worden - een oplossingsstrategie ontwerpen en realiseren; een bereik een naam geven en die naam in formules gebruiken; macro’s ontwerpen, ze toekennen aan knoppen en de code wijzigen; een keuzelijst met invoervak ontwerpen en de nodige eigenschappen wijzigen; de werkbladen zinvol beveiligen; de functies InputBox() en MsgBox() toepassen; werkbladen groeperen om gemeenschappelijke bewerkingen uit te voeren; werkbladen met dezelfde opbouw als groep bewerken; van meerdere werkbladen met dezelfde opbouw samenvattende informatie berekenen.

Opdrachten 14. Is het een probleem als het BTW-percentage van 21 naar 22 zou veranderen? 15. Is het een probleem als de firma beslist de overuren aan 160 % te betalen i.p.v. 150%? 16. Kun je een arbeider vlot promoveren tot een bediende? Welke gegevens moet je verwijderen of toevoegen? 17. Hoe zou je de volgende boodschap in het invoervenster kunnen weergeven? Gebruik de helpfunctie en test regel na regel.



159

Inhoudsopgave

WOORD VOORAF ......................................................................................................................................... 3 HOOFDSTUK 1 HERHALING VAN ENKELE BASISBEGRIPPEN .................................................... 5 1.1 PROBLEEMSTELLING ....................................................................................................................... 5 1.2 EXCEL OPSTARTEN .......................................................................................................................... 5 1.3 DE WERKOMGEVING ....................................................................................................................... 6 1.3.1 Het werkblad ............................................................................................................................. 7 1.3.2 Werkbladen en werkmappen ...................................................................................................... 7 1.3.3 De werkbalken ........................................................................................................................... 8 1.3.4 De formulebalk ........................................................................................................................ 10 1.3.5 De statusbalk ........................................................................................................................... 10 1.4 DE INHOUD VAN EEN CEL .............................................................................................................. 10 1.5 DE HULPTROEPEN.......................................................................................................................... 12 1.6 AUTOMATISCHE HERBEREKENING ................................................................................................. 13 1.7 GETALLEN OPMAKEN .................................................................................................................... 14 1.8 AANGEPASTE NOTATIES ................................................................................................................ 15 1.9 WERKMAPPEN IN EEN WERKRUIMTEBESTAND OPSLAAN ............................................................... 17 BESLUIT ...................................................................................................................................................... 18 WAT JE MOET KENNEN EN KUNNEN: ............................................................................................................ 18 OPDRACHTEN .............................................................................................................................................. 18 HOOFDSTUK 2 BASISTECHNIEKEN ................................................................................................... 20 2.1 PROBLEEMSTELLING ..................................................................................................................... 20 2.2 CELLEN SELECTEREN .................................................................................................................... 20 2.3 KOPIËREN, KNIPPEN EN PLAKKEN .................................................................................................. 21 2.4 PLAKKEN SPECIAAL ...................................................................................................................... 23 2.5 BREEDTE VAN KOLOMMEN EN HOOGTE VAN RIJEN ........................................................................ 24 2.6 WISSEN ......................................................................................................................................... 26 2.7 REEKSEN DOORVOEREN ................................................................................................................ 27 2.7.1 Reeksen met de vulgreep doorvoeren ...................................................................................... 27 2.7.2 Via een dialoogvenster reeksen doorvoeren ............................................................................ 28 2.8 RELATIEVE EN ABSOLUTE ADRESSERING ....................................................................................... 29 2.8.1 Relatieve adressering .............................................................................................................. 30 2.8.2 Absolute adressering ............................................................................................................... 30 2.8.3 Gemengde adressering ............................................................................................................ 30 2.9 VERWIJDEREN EN INVOEGEN ......................................................................................................... 31 2.10 KOLOMMEN OF RIJEN VERBERGEN ................................................................................................ 32 2.11 HET WERKBLAD BEVEILIGEN......................................................................................................... 36 2.12 WACHTWOORDBEVEILIGING IN EXCEL ......................................................................................... 37 BESLUIT ...................................................................................................................................................... 39 WAT JE MOET KENNEN EN KUNNEN: ............................................................................................................ 39 OPDRACHTEN .............................................................................................................................................. 39 HOOFDSTUK 3 DE BEAUTYCASE VAN EXCEL ................................................................................ 42 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8

PROBLEEMSTELLING ..................................................................................................................... 42 HET WERKBLAD VERFRAAIEN ....................................................................................................... 42 VOORWAARDELIJKE OPMAAK ....................................................................................................... 45 PAGINA-INSTELLING ..................................................................................................................... 46 EEN WERKBLAD AFDRUKKEN ........................................................................................................ 47 KOLOMMEN EN RIJEN TIJDENS SCHUIVEN ZICHTBAAR HOUDEN ..................................................... 49 VENSTERS SPLITSEN ...................................................................................................................... 49 EEN SAMENVATTEND WERKBLAD REALISEREN ............................................................................. 50



161

BESLUIT ....................................................................................................................................................... 51 WAT JE MOET KENNEN EN KUNNEN: ............................................................................................................. 51 OPDRACHT ................................................................................................................................................... 51 HOOFDSTUK 4 DE FACTURATIE .......................................................................................................... 54 4.1 PROBLEEMANALYSE ...................................................................................................................... 55 4.2 HET WERKBLAD ARTIKELEN EN BTW-CODES ............................................................................... 55 4.2.1 Een artikeltabel samenstellen................................................................................................... 55 4.2.2 Gegevens met de functie Vert.Zoeken opzoeken ....................................................................... 57 4.2.3 Een naam aan een bereik geven ............................................................................................... 58 4.2.4 Een tabel sorteren .................................................................................................................... 59 4.3 HET WERKBLAD KLANTEN ............................................................................................................ 59 4.4 HET WERKBLAD FACTUUR ............................................................................................................. 61 4.4.1 De factuur- en klantgegevens toevoegen .................................................................................. 62 4.4.2 De invullijst met de artikelgegevens ......................................................................................... 63 4.4.3 De opsplitsing van de BTW-bedragen ...................................................................................... 64 4.4.4 De eindtotalen berekenen ......................................................................................................... 65 4.4.5 De factuurkop ........................................................................................................................... 65 4.5 FORMULES CONTROLEREN AANTEKENINGEN MAKEN..................................................................... 65 4.5.1 De gegevensstroom zichtbaar maken ....................................................................................... 66 4.5.2 Foutcontrole ............................................................................................................................. 66 4.5.3 Aantekeningen toevoegen ......................................................................................................... 68 4.6 GRAFISCHE OBJECTEN OM DE OPMAAK TE VERFRAAIEN ................................................................ 68 4.7 HET FIRMALOGO INVOEGEN ........................................................................................................... 71 4.8 DE FACTUUR BEVEILIGEN .............................................................................................................. 73 4.9 DE FACTUUR AFDRUKKEN.............................................................................................................. 73 4.10 EEN MACRO OPNEMEN EN AAN EEN KNOP TOEKENNEN .................................................................. 73 4.11 MACROBEVEILIGING ...................................................................................................................... 75 4.12 EEN MENUSYSTEEM ONTWERPEN ................................................................................................... 76 4.13 MACRO’S, PROGRAMMACODE EN MODULEBLADEN ....................................................................... 77 4.13.1 Modulebladen ...................................................................................................................... 77 4.13.2 Het volgende factuurnummer genereren ............................................................................. 79 4.13.3 Excel met de macro “Einde” afsluiten ................................................................................ 80 4.13.4 Een knop toevoegen om de factuur af te drukken ................................................................ 80 4.14 DE KLANT VIA EEN KEUZELIJST KIEZEN ......................................................................................... 83 BESLUIT ....................................................................................................................................................... 86 WAT JE MOET KENNEN EN KUNNEN: ............................................................................................................. 86 OPDRACHTEN ............................................................................................................................................... 86 HOOFDSTUK 5 GEGEVENS ANALYSEREN EN GRAFIEKEN MAKEN ....................................... 87 5.1 SOORTEN GRAFIEKEN .................................................................................................................... 87 5.2 ZELF EEN FREQUENTIETABEL SAMENSTELLEN ............................................................................... 89 5.2.1 Probleemstelling ...................................................................................................................... 89 5.2.2 Probleemanalyse ...................................................................................................................... 89 5.2.3 Oplossing ................................................................................................................................. 90 5.3 GEGEVENS DOOR EXCEL LATEN ANALYSEREN............................................................................... 90 5.3.1 Analysis Toolpak installeren en activeren ................................................................................ 90 5.3.2 Een frequentietabel genereren ................................................................................................. 91 5.4 EEN KOLOMGRAFIEK VIA DE WIZARD GRAFIEKEN ONTWERPEN .................................................... 92 5.5 WIJZIGINGEN IN EEN GRAFIEK AANBRENGEN ................................................................................. 95 5.6 EEN TRENDLIJN AAN EEN GRAFIEK TOEVOEGEN............................................................................. 97 5.6.1 Probleemstelling ...................................................................................................................... 97 5.6.2 Probleemanalyse ...................................................................................................................... 97 5.6.3 Oplossing ................................................................................................................................. 98 BESLUIT ....................................................................................................................................................... 99 WAT JE MOET KENNEN EN KUNNEN: ............................................................................................................. 99 OPDRACHTEN ............................................................................................................................................... 99 HOOFDSTUK 6 AFSCHRIJVINGEN ..................................................................................................... 102 6.1 6.2 162

PROBLEEMSTELLING .................................................................................................................... 102 DE LINEAIRE AFSCHRIJVING ......................................................................................................... 103 Rekenblad: Excel 2003


6.3 DE DEGRESSIEVE AFSCHRIJVING ................................................................................................. 106 BESLUIT .................................................................................................................................................... 111 WAT JE MOET KENNEN EN KUNNEN: .......................................................................................................... 111 OPDRACHT ................................................................................................................................................ 111 HOOFDSTUK 7 LENINGEN, RENTE EN KAPITAALAFLOSSING................................................ 113 7.1 PROBLEEMSTELLING ................................................................................................................... 113 7.2 SAMENGESTELDE INTREST .......................................................................................................... 113 7.3 MATRIXFORMULES...................................................................................................................... 113 7.4 HET TERMIJNBEDRAG OF DE ANNUÏTEIT ...................................................................................... 114 7.5 DE RENTE EN DE KAPITAALAFLOSSING ........................................................................................ 116 7.5.1 De rente- en het kapitaalaandeel ........................................................................................... 116 7.5.2 De cumulatieve rente en het restkapitaal .............................................................................. 117 7.5.3 De controle via de termijnbetaling ........................................................................................ 118 7.5.4 De eindafwerking en een grafische voorstelling .................................................................... 118 7.6 DE HUIDIGE EN TOEKOMSTIGE WAARDE VAN EEN INVESTERING ................................................. 118 7.7 DE RENTE VAN EEN ANNUÏTEIT ................................................................................................... 119 7.8 HET AANTAL TERMIJNEN VOOR EEN INVESTERING ...................................................................... 120 BESLUIT .................................................................................................................................................... 121 WAT JE MOET KENNEN EN KUNNEN: .......................................................................................................... 121 OPDRACHTEN ............................................................................................................................................ 121 HOOFDSTUK 8 DRAAITABELLEN ..................................................................................................... 123 8.1 INLEIDING ................................................................................................................................... 123 8.2 PROBLEEMSTELLING ................................................................................................................... 123 8.3 PROBLEEMANALYSE.................................................................................................................... 123 8.4 EEN DRAAITABEL ONTWERPEN .................................................................................................... 124 8.5 DE GRAFISCHE VOORSTELLING.................................................................................................... 129 8.6 EEN DRAAITABEL WIJZIGEN ........................................................................................................ 130 BESLUIT .................................................................................................................................................... 131 WAT JE MOET KENNEN EN KUNNEN: .......................................................................................................... 131 OPDRACHTEN ............................................................................................................................................ 131 HOOFDSTUK 9 PROBLEMEN DOOR MIDDEL VAN GEGEVENSANALYSE OPLOSSEN ...... 132 9.1 DOELGERICHT ZOEKEN ............................................................................................................... 132 9.2 EENDIMENSIONALE ‘WAT ALS?’-ANALYSE ................................................................................. 133 9.3 TWEEDIMENSIONALE ‘WAT ALS?’-ANALYSE .............................................................................. 135 9.4 ‘WAT ALS’-SCENARIO’S .............................................................................................................. 136 9.5 PROBLEMEN VIA DE OPLOSSER OPLOSSEN .................................................................................. 138 BESLUIT .................................................................................................................................................... 141 WAT JE MOET KENNEN EN KUNNEN: .......................................................................................................... 141 OPDRACHTEN ............................................................................................................................................ 141 HOOFDSTUK 10 DE LOONBEREKENING ........................................................................................... 143 10.1 PROBLEEMSTELLING ................................................................................................................... 143 10.2 PROBLEEMANALYSE.................................................................................................................... 143 10.2.1 Welke opzoektabellen heb je nodig? ................................................................................. 144 10.2.2 Het werkblad WERKNEMER ................................................................................................ 145 10.2.3 Het werkblad LOONSTAAT .................................................................................................. 146 10.2.4 Het werkblad LOONBRIEF .................................................................................................. 149 10.3 DE WERKBLADEN BEVEILIGEN .................................................................................................... 151 10.4 MACRO’S TOEVOEGEN EN EEN MENUSYSTEEM ONTWERPEN ....................................................... 151 10.5 EEN WAARDE IN EEN CEL VIA EEN DIALOOGVENSTER INVULLEN ................................................. 153 10.6 DE COMBOBOX (KEUZELIJST MET INVOERVAK) ........................................................................... 154 10.7 HET AANTAL GEWERKTE UREN OPVRAGEN ................................................................................. 155 10.7.1 Probleemstelling ............................................................................................................... 155 10.7.2 Probleemanalyse ............................................................................................................... 155 10.7.3 Oplossing .......................................................................................................................... 157 BESLUIT .................................................................................................................................................... 159 WAT JE MOET KENNEN EN KUNNEN: .......................................................................................................... 159 OPDRACHTEN ............................................................................................................................................ 159 J. Gils – E. Goossens


163

Info-books. Toegepaste Informatica. Deel 2d: Het rekenblad Excel Boekhouden-informatica Informaticabeheer. BI2d. Jos Gils Erik Goossens

Recommend Documents