Excel gevorderd. Deze handleiding werd geschreven door: Charles Scheublin - (Kopiëren niet toegestaan)

Excel 2010 gevorderd (Kopiëren niet toegestaan)

Deze handleiding werd geschreven door: Charles Scheublin -

© CTS/Advies: 19 December 2014

Niets uit deze handleiding mag gekopieerd of anderszins vermenigvuldig of gepubliceerd worden, zonder toestemmen van de auteur: CTS/Advies Amstelveenseweg 114-2 1075 XK Amsterdam 020-6248070 [email protected] www.ctsadvies.nl

Vereiste voorkennis De stof van de cursus Excel staat in één basis en één gevorderden handleiding beschreven. In het navolgende, vindt u alleen de stof van de gevorderden-handleiding. In deze handleiding wordt er vanuit gegaan dat u eerst onze Excel-basiscursus gevolgd heeft. Vaardigheden die in deze cursus behandeld zijn worden hier nog maar summier beschreven. Terminologie Deze handleiding is zowel bij een cursus in de Nederlands- als in de Engelstalige-versie van Excel te gebruiken. Bij alle opdrachten zijn beide benamingen weergeven. De Engelse term staat telkens tussen rechte haken achter de Nederlandse. Soms vindt u in de handleiding een verwijzing naar een toetscombinatie die u moet indrukken of een aantal opties die u moet aanklikken. Zijn deze toetsen of opties onderling gescheiden door een streepje, dan moeten deze na elkaar worden ingedrukt of aangeklikt. Zijn deze gescheiden door een + teken dan moet deze tegelijk worden ingedrukt. Voorbeeld ‘Bewerken-Kopiëren’ [Edit-Paste] ‘Alt+Tab’

- (opties na elkaar aanklikken) - (toetsen tegelijkertijd indrukken)

Oefeningen Aan het eind van ieder hoofdstuk staan een aantal oefeningen waarmee u, hetgeen u geleerd heeft, kunt uitproberen. Indien in een oefening wordt verwezen naar het resultaat van een voorgaande oefening en u hebt deze voorgaande oefening niet gedaan of u heeft deze niet bewaard, dan vindt u bij de oefenbestanden het ‘tussentijdse’ resultaat. De tussenresultaten van een oefening zijn oplopend geletterd. Na sommige oefeningen vindt u soms ‘extra oefeningen’ en/of ‘verdiepingsonderwerpen’. Deze extra oefeningen en verdiepingsonderwerpen zullen in het algemeen niet klassikaal behandeld worden en zijn bedoeld voor zelfstandige bestudering door de cursist die al klaar is met de voorgaande oefening.

Inhoudsopgave 1.

HET EXCEL 2010-VENSTER

1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8

Het Lint Stijlen en Bouwstenen De tab ‘Bestand’ [File] Excel-Opties Lint aanpassen De werkbalk Snelle toegang Dialoogkaders zoals in voorgaande versies Het Excel 2010 – venster: oefeningen

Pag.nr. 7 8 9 10 11 12 13 14 15

2.

HERHALING BASIS/VERDIEPING

2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 2.10 2.11 2.12 2.13 2.14 2.15

Maximale afmetingen van Excel-blad Conversie van bestanden Geheugengebruik Berekening van Excel-bladen Tekst en getallen invoeren Gebruik van de somknop Celreferenties Kopiëren en verplaatsen op werkblad Wisselen tussen absoluut en relatief Bijzondere getalsopmaak Voorwaardelijke opmaak Formules in cellen invoeren Cellen namen geven Gegevens in geselecteerde cellen typen Herhaling basis/verdieping: oefeningen

18 19 20 21 22 23 24 25 27 28 30 31 32 33 35

3.

EXCEL OF EEN DATABASE

47 49

4.

HET GEBRUIK VAN NAMEN

4.1 4.2 4.3 4.4 4.5 4.6

Het naamvak in de werkbalk. De opties in het ‘Lint’ [Ribbon] voor namen. Automatisch aanmaken van namen Namen toepassen Zoeken in een tabel met benoemde rijen en kolommen Het gebruik van namen - oefeningen

5.

FUNCTIES

5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8

De functie-wizard Afrond-functies Datum- en tijd-functies Tekst-functies Logische functies Statistische functies Zoekfuncties Functies – oefeningen

6.

ALTERNATIEVEN DOORREKENEN

6.1 6.2 6.3 6.4

Tabellen Iteratie Doelzoeken [Goal Seek] Oplosser [Solver]

17

50 51 52 53 54 55

59 60 62 63 65 66 67 69 71

79 80 84 85 86

6.5 6.6

Scenarios Alternatieven doorrekenen - oefeningen

88 91

7.

LIJSTEN

95

7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8

Definities Het opzetten van een lijst Gegevens invoeren Sorteren van records Subtotalen toevoegen Records selecteren Draaitabellen [Pivot tables] Lijsten – oefeningen

96 97 98 100 101 102 107 115

8.

KOPPELINGEN

8.1 8.2 8.3 8.4 8.5

Verwijzingen aanbrengen Wijziging van het werkblad waarnaar wordt verwezen Het openen van gekoppelde werkbladen Excel als database Koppelingen: oefeningen

9.

MACRO’S

9.1 9.2 9.3 9.4 9.5 9.6 9.7 9.8 9.9 9.10 9.11 9.12 9.13 9.14 9.15 9.16 9.17 9.18 9.19

Macro’s opnemen Een macro uitvoeren Absolute en relatieve registratie Het venster van de VBA-editor Macro’s wijzigen Starting a macro Invoervensters Regels afbreken Foutafhandeling Modulair programmeren VBA Syntax De keuze en de herhaling Mededelingenvenster Variabelen en constanten Dialogbox ontwerpen Een gebeurtenisroutine opnemen. Functiemacro’s Diverse Instructies Macro’s: oefeningen

123

137 138 139 140 141 143 144 146 147 148 149 150 153 156 158 161 165 166 167 169

10. ANTWOORDEN 10.1 10.2 10.3 10.4

124 127 128 129 131

175

Herhalingsoefeningen: Functies Lijsten Visual Basic

176 177 178 179

11. EVALUATIEFORMULIER

181

12. AANTEKENINGEN

183

©CTS/Advies

19-12-2014

Pag.nr.: 6/187

1. Het Excel 2010-venster � Start Excel Vanaf versie 2007 heeft Excel een geheel andere interface gekregen. Bovenaan het scherm ziet u niet meer menu’s en werkbalken maar ziet u hier het ‘Lint’ [Ribbon].

©CTS/Advies

19-12-2014

Pag.nr.: 7/187

Het Excel 2007 - venster

1.1 Het Lint

In het ‘Lint’ [Ribbon] bevinden zich knoppen waarmee u allerlei functies kan oproepen. De opties zijn verdeeld in ‘Groepen’. Op de onderste regel van het ‘Lint’ [Ribbon] vindt u de groepsnamen. Als het venster breder wordt, worden keuzelijsten uitgeklapt en worden knoppen omgezet in pictogrammen. Wordt het ‘Lint’ versmald, dan ziet u nog alleen maar de groepsnamen vermeld. � Maak het venster eerst smaller en daarna weer breder. Boven het ‘Lint’ [Ribbon] vindt u een aantal tabs. Bij aanvang zijn dit de tabs: ‘Bestand’, ‘Start’, ‘Invoegen’, ‘Pagina-indeling’, ‘Formules, ‘Gegevens, ‘Controleren’ en ‘Beeld’ [File, Home, Insert, Page Layout, Formulas, Data, Review, View]. � Klik de verschillende tabs aan en bekijk de opties. De opties die u nu ziet, zijn nog lang niet alle opties. Als u met Excel aan het werk bent, dan zult u ontdekken dat, afhankelijk van het onderdeel waarin u zich bevindt, er meer tabs verschijnen met extra opties. Als u bijvoorbeeld een grafiek invoegt, dan verschijnen er drie nieuwe tabs ‘Ontwerpen’, ‘Indeling’ en ‘Opmaak’ [Design, Layout, Format]. Dit zijn drie tabs met allerlei opties die alleen met grafieken te maken hebben. Als u vindt dat het ‘Lint’ [Ribbon] te veel ruimte op het scherm in beslag neemt, dan kunt u dit minimaliseren tot alleen de groepsnamen. � Klik met de rechter muisknop op het ‘Lint’ [Ribbon] en kies in het snelmenu voor de optie ‘Het lint minimaliseren’ [Minimize the Ribbon]. � Breng het ‘Lint’ [Ribbon] daarna weer terug in haar oorspronkelijke vorm, door op het kleine pijltje rechts boven het Lint [Ribbon] te klikken.

©CTS/Advies

19-12-2014

Pag.nr.: 8/187


1.2 Stijlen en Bouwstenen Excel kent een groot aantal opties voor de opmaak. U kunt kiezen uit allerlei letterypen, lettertgrootten, onderstreping, letter- en achtegrondkleuren, randtypen, randdiktes, schaduwen, 3D-effecten en animaties. Om het u makkelijk te maken, heeft men een aantal opmaak-opties gecombineerd tot standaardstijlen. � Type uw naam in het document. � Selecteer de cel die uw naam bevat. � Klik op ‘Start – Stijlen – Celstijlen’ [Home – Styles – Cell styles]. Als u de muis zonder te klikken over de verschillende stijlen beweegt, ziet u deze direct op de tekst toegepast. Pas als u op één van de stijlen klikt, wordt deze definitief toegepast op de selectie.

©CTS/Advies

19-12-2014

Pag.nr.: 9/187


1.3 De tab ‘Bestand’ [File] In de linker bovenhoek ziet u de tab ‘Bestand’ [File]. Onder deze tab vindt u alle opties die betrekking hebben op het document als geheel. � Klik op de tab ‘Bestand’.. In dit dialoogkader vindt u de volgende opties: Optie Opslaan [Save] Opslaan als [Save as] Openen [Open] Sluiten [Close] Info [Info] Recent [Recent Nieuw [New] Afdrukken [Print] Opslaan en verzenden [Save & Send] Help [Help] Opties [Options] Afsluiten [Exit]

©CTS/Advies

Functie Om het document op te slaan onder zijn huidige naam en op de huidige locatie. U krijgt keuze uit diverse locaties en formaten waarin u uw Excel-document kan opslaan. Openen van document op schijf. Om het document te sluiten. Informatie over: versie, beveiliging, delen met anderen en comatibiltieits modus Om één van de onlangs geopende documenten opnieuw te openen. Voor het openen van een leeg nieuw document of een document gebaseerd op een sjabloon. Om het document af te drukken en de afdrukopties in te stellen. Om het document op schijf of op het WEB te bewaren of als email te verzenden. Help bij het gebruik van Excel Voor wijziging van de standaardinstellingen van Excel Om het Excel-programma af te sluiten.

19-12-2014

Pag.nr.: 10/187


1.4 Excel-Opties Onder de tab ‘Bestand’ [File] vindt u de ‘Opties’ [Options]: � Kies de optie ‘Bestand – Opties’ [File – Options].

De opties zijn naar groepen onderverdeeld. Daar waar dit van toepassing is zal hier in de tekst op terug worden gekomen.

©CTS/Advies

19-12-2014

Pag.nr.: 11/187


1.5 Lint aanpassen Vanaf versie 2010 kunt u het lint ook aanpassen aan eigen wensen. � Klik onder de opties op ‘Lint aanpassen’ [Customize Ribbon]

U ziet in de lijst links alle functies die Excel kent en in de lijst rechts de functies die onder de tabs in de groepsvakken onder knoppen zijn opgenomen. Met de vinkjes kunt u deze aan- en uitschakelen. U kunt ook een nieuw groepsvak aan een tab toevoegen om knoppen in te plaatsen. De tab ‘Ontwikkelaars’ [Developers], met opties om macro’s te maken, staat bij een standaardinstallatie niet geactiveerd. � Schakel de tab ‘Ontwikkelaars’ [Developpers] in, als dit nog niet gebeurd is. � Sluit het venster met de opties.

©CTS/Advies

19-12-2014

Pag.nr.: 12/187


1.6 De werkbalk Snelle toegang Geheel bovenaan het venster vindt u een kort werkbalkje. Dit heet de werkbalk ‘Snelle toegang’. Standaard zitten hier alleen de knoppen: ‘Bewaren’, ‘Ongedaan maken’ en ‘Herhalen’ in. Klikt u echter op het kleine zwarte pijltje aan het eind van de werkbalk, dan krijgt u een keuzelijst met meer knoppen die u aan de werkbalk, kunt toevoegen. � Klik op het kleine zwarte pijltje aan het eind van de werkbalk ‘Snelle toegang’ [Quick access]. � Voeg de knop ‘Snel afdrukken’ [Quick Print] toe aan de werkbalk. Klikt u met de rechter muisknop op een knop in het ‘Lint, dan verschijnt een snel menu met daarin de optie ‘Toevoegen aan werkbalk Snelle toegang’. Zo kunt u dus ook uw eigen werkbalk samenstellen. � Klik met de rechter muisknop op de optie ‘Start – Uitlijning - Centreren’ [Home – Alignment – Center]. � Klik op de optie ‘Toevoegen aan werkbalk Snelle toegang’ [Add to Quick Access Toolbar]. U ziet dat de knop toegevoegd wordt aan het kleine werkbalkje. � Klik in de werkblalk ‘Snelle toegang’ [Quick Access] met de rechter muisknop op de toegevoegde knop. � Klik in het snelmenu dat verschijnt, op de optie ‘Verwijdering uit werkbalk Snelle toegang’ [Remove from Quick Access Toolbar].

©CTS/Advies

19-12-2014

Pag.nr.: 13/187


1.7 Dialoogkaders zoals in voorgaande versies Onder de tab ‘Start’ [Home] vindt u groepsvakken met opties voor de keuze van ‘Lettertype’ [Font], ‘Uitlijning’ [Alignment] en opmaak van een ‘Getal’ [Number]. Kunt u hierin niet de opmaak vinden die u gewend was, dan kunt u een dialoogkader met alle mogelijke opties op het scherm halen. Dit dialoogkader is gelijk aan het dialoogkader uit voorgaande versies van Excel. � Klik in het groepsvak ‘Lettertype’ [Font] op het kleine diagonale zwart pijltje in de rechter onderhoek van het groepsvak.

Op het scherm verschijnt het dialoogkader voor de opmaak, zoals u dat uit voorgaande versies gewend was. � Sluit het dialoogkader weer.

©CTS/Advies

19-12-2014

Pag.nr.: 14/187

1.8 Het Excel 2010 – venster: oefeningen Oefening 1.: Het ‘Lint’. � Start Excel. � Maak het venster eerst smaller en daarna weer breder en bekijk het effect op de pictogrammen in het ‘Lint’. � Maximaliseer het programmavenster. � Klik de verschillende tabs boven het ‘Lint’ [Ribbon] aan en bekijk de opties die deze bevatten. � Klik met de rechter muisknop op het ‘Lint’ [Ribbon] en kies in het snelmenu voor de optie ‘Het lint minimaliseren’ [Minimize the Ribbon] � Breng het ‘Lint’ [Ribbon] daarna weer terug in haar oorspronkelijke vorm, door op het kleine pijltje rechts boven het Lint [Ribbon] te klikken. Oefening 2.: Stijlen en Bouwstenen � Type uw naam in het document. � Selecteer de cel die uw naam bevat. � Klik op ‘Start – Stijlen – Celstijlen’ [Home – Styles – Cell styles]. � Ga met uw muis over de verschillende stijlen. � Klik op een van de stijlen om deze toe te passen. Oefening 3.: De tab ‘Bestand’ [File] � Klik op de tab ‘Bestand’ [File] � Klik op de optie ‘Afdrukken’ [Print] en bekijk uw document in het afdrukvoorbeeld. � Sla uw document op onder de naam “Oefeningen”. Oefening 4.: Opties � Klik op de tab ‘Bestand’ [File]. � Klik op ‘Opties’ [Options]. � Bekijk de opties die in dit menu voorkomen. � Controleer of in de groep ‘Geavanceerd’ [Advanced] of de optie ‘Formulebalk weergeven’ [Show formula bar] aan staat. � Controleer of in de groep ‘Lint aanpassen’ [Curstomize Ribbon] ook de optie om het tabblad ‘Ontwikkelaars’ [Developers] op het ‘Lint’ [Ribbon] weer te geven aan staat. � Bekijk de tab ‘Ontwikkelaars’ [Developers] in het Lint. Onder deze tab vindt u opties om macro’s te maken.

©CTS/Advies

19-12-2014

Pag.nr.: 15/187

Het Excel 2007 – venster: oefeningen

Oefening 5.: De werkbalk ‘Snelle toegang’ [Quick Access] � Klik op het kleine zwarte pijltje aan het eind van de werkbalk ‘Snelle toegang’ [Quick Access]. � Voeg de knoppen ‘Nieuw’ [New] en ‘Snel afdrukken’ [Quick Print] toe aan de werkbalk. � Klik met de rechter muisknop op de optie ‘Start – Uitlijning - Centreren’ [Home – Alignment – Center]. � Klik op de optie ‘Toevoegen aan werkbalk Snelle toegang’ [Add to Quick Access Toolbar]. � Verwijder alle knoppen die u toegevoegd hebt, weer uit de werkbalk ‘Snelle toegang’ [Quick Access] Oefening 6.: Dialoogkaders � Type in een cel de stelling van Phytagoras: A2+B2=C2 Dit is gewoon tekst. � Selecteer de 2 achter de letter A door hier met ingedrukte muisknop overheen te slepen. � Klik in het groepsvak ‘Lettertype’ [Font] op het kleine diagonale zwart pijltje in de rechter onderhoek van het groepsvak. Op het scherm verschijnt het dialoogkader voor de opmaak, zoals dat ook in voorgaande versies voorkwam. Merk op dat bij het selecteren van een deel van de inhoud van een cel, alleen de tab ‘Lettertype’ [Font] beschikbaar is. � Gebruik de optie ‘Superscript’ uit het dialoogkader om de “2” boven aan de regel te plaatsen. � Doe hetzeflde met de twee andere tweëen in de formule. � Bewaar uw document onder de naam “Oefeningen-gevorderd”.

©CTS/Advies

19-12-2014

Pag.nr.: 16/187

2. Herhaling basis/verdieping

©CTS/Advies

19-12-2014

Pag.nr.: 17/187

Herhaling basis/verdieping

2.1 Maximale afmetingen van Excel-blad Het Excel-blad kan maximaal 1.048.576 rijen en 16.384 kolommen bevatten. Dit is waarschijnlijk veel meer dan dat u ooit nodig zal hebben. De werkelijke maximale grootte wordt echter begrensd door de geheugencapaciteit van uw computer. Als u de ‘Control-toets’ ingedrukt houdt en op een pijltjestoets drukt, dan springt de invoercel naar de eerstvolgende ingevulde of lege cel. Is het werkblad verder leeg dan springt deze naar de laatste cel in het werkblad. � Controleer de grote van het werkblad door de ‘Control-toets’ ingedrukt te houden en vervolgens op de pijlen naar beneden, naar rechts, naar boven en naar links te klikken.

CTS/Advies

19-12-14

18/187


2.2 Conversie van bestanden Hebt u een document uit een versie van voor Excel 2007 op uw scherm, dan ziet u slechts 65.536 rijen en 256 kolommen. Uit oogpunt van comptabiliteit worden bestanden van voorgaande versies niet automatisch geconverteerd naar de nieuwe bestandsindeling. � Klik op ‘Bestand – Info’ [File – Info]. Er zijn drie opties de bovenste optie gaat over ‘Machtigingen’ [Permissions]. � Open een bestand uit voorgaande versies. U herkent bestanden uit deze oude versies door de extensie .xls Het bestand wordt niet naar de nieuwe bestandsindeling geconverteerd Achter de naam ziet u tussen haken ‘Compabiliteitsmodus’ [Comability Mode] staan � Controleer het maximaal aantal regels en kolommen van het bestand. � Klik opnieuw op ‘Bestand – Info’ [File – Info]. U ziet dat er nu een nieuwe optie ‘Compabiliteitsmodus’ [Compability Mode] is bijgekomen, waarmee u het bestand naar de nieuwe bestandsindeling kan converteren. � Converteer het bestand naar de nieuwe bestandsindeling. � Controleer opnieuw het aantal regels en kolommen van het bestand. Indien u er voor kiest om bestanden altijd in het formaat van de voorgaande versies te bewaren. Dan worden ook nieuwe documenten hierin geopend. � Kies ‘Bestand – Opties – Opslaan’ [File – Options – Save as]. � Kies achter ‘Bestanden opslaan in deze indeling’ [Save files in this format ] voor ‘Excel 97-2003 werkmap (*.xls)’ [Excel 97-2003 Workbook (*.xls)]. � Open een nieuw document en controleer de afmetingen. Het belangrijkste verschil met voorgaande versies van Excel zijn de maximale mate van de werkbladen. Daarnaast is er ook andere minder belangrijke functionaliteit die niet door voorgaande versies wordt onderstaand zoals bijvoorbeeld nieuwe grafiekvormen en stijlen.

CTS/Advies

19-12-14

19/187


2.3 Geheugengebruik Excel bewaart op schijf alleen een rechthoek waarbinnen de ingevulde cellen op het blad passen. In onderstaand voorbeeld is dat dus het gebied met de dikke lijn er omheen. Plaats met het oog op het geheugengebruik de ingevulde cellen zoveel mogelijk bij elkaar in de linker bovenhoek van het werkblad en voorkom dat, zoals in onderstaand voorbeeld, een enkel gegeven een hele extra kolom doet opslaan (hier de datum boven in kolom D en E)

CTS/Advies

19-12-14

20/187


2.4 Berekening van Excel-bladen Excel wordt in de eerste plaats gebruikt voor het opzetten van een berekening. Plaats de waarden die gebruikt worden voor de berekeningen in cellen boven aan het werkblad. Dit komt de snelheid van berekening ten goeden, omdat Excel de formules in volgorde van boven naar beneden op het werkblad berekent. Plaats in formules geen getallen, maar verwijzingen naar cellen die getallen bevatten. Plaats een duidelijke omschrijving bij de waarden die het uitgangspunt vormen voor de berekeningen. Hierdoor blijft, ook voor latere gebruikers, duidelijk, wat de uitgangspunten van uw berekeningen zijn geweest.

CTS/Advies

19-12-14

21/187


2.5 Tekst en getallen invoeren Bij het intypen van tekst, getallen en formules moet u met een aantal punten rekening houden. Tekst wordt standaard links en getallen standaard rechts in een cel geplaatst. Als Excel een getal of datum meent te herkennen, dan wordt deze door Excel weergegeven in een standaard getals- of datumformaat. Is dit niet de bedoeling, dan typt u eerst een enkel accentteken. Het getal wordt dan geconverteerd naar tekst. (U kunt hiervoor in de plaats ook in het dialoogkader voor de opmaak voor de optie ‘Tekst’ [Text] kiezen) Als uw computer geconfigureerd is met de toetsenbordinstelling ‘VSinternational’ dan kunt u een klinker met een accent-teken typen door eerst het accentteken te typen en daarna de klinker. Werkt dit niet op uw computer, dan kunt u eerst controleren of zich op de taakbalk een optie bevindt om heer naar over te schakelen. Is deze optie niet aanwezig dan kunt u voor het invoegen van diacritische tekens gebruik maken van de optie ‘Invoegen – Symbolen - Symbool’ [Insert – Symbols – Symbol]. Formules beginnen altijd met een =-teken en bevatten verder: � getallen, � verwijzingen naar cellen die getallen bevatten, � rekenkundige operatoren � functies Verwijzingen naar cellen maakt u door, tijdens het intypen van de formule, deze met de muis te selecteren. In de berekening van een formule gaan vermenigvuldigen en delen voor optellen en aftrekken. Indien vermenigvuldigen en delen beiden in een formule voorkomen, dan worden deze op volgorde van links naar rechts berekend. Het zelfde geldt als zowel optellen als aftrekken in een formule voorkomen. Het oude “Mijnheer Van Dalen Wacht Op Antwoord” is hier dus niet meer van toepassing.

CTS/Advies

19-12-14

22/187


2.6 Gebruik van de somknop U kunt de somfunctie [Sum-functie] in een formule invullen door hiervoor op de somknop te klikken. Klikt u onder een rij getallen dan vult Excel, tussen de haken van de functie, een verwijzing naar deze getallen in. Vult u de somfunctie onder een subtotaal in, dan maakt Excel er een totaal van subtotalen van. � Neem nevenstaand voorbeeld over in een leeg tabblad. Selecteer, alvorens de getallen in te typen, eerst de gebieden B1 t/m B3, B5 t/m B7 en B9 t/m B11. Type daarna achter elkaar de getallen 1 t/m 9 in. � Plaats met behulp van de somknop achtereenvolgens een somfunctie in cel B4, B8, B12 en tot slot in B13. � Bekijk de formule in B13

CTS/Advies

19-12-14

23/187


2.7 Celreferenties In functies zoals de somfunctie, verwijst u tussen de haken naar celreferenties. Deze celreferenties kunnen de volgende vorm hebben: Referentie Enkele cel Enkelvoudige selectie Meervoudige selectie Doorsnijding van twee selecties

CTS/Advies

Voorbeeld $B$3 $B$3:$D$7 $B$3:$B$7;$D$3:$D$7 $B$3:$B$7 $A$5:$E$5

19-12-14

24/187


2.8 Kopiëren en verplaatsen op werkblad U kunt de inhoud van een selectie naar een andere plaats op het werkblad verplaatsen, door de rand van de selectie te verslepen. Met muis slepen

+ Control-toets + Shift-toets

aan de rand van de selectie Verplaatsen Kopiëren Invoegen

aan het selectieknopje Reeks doorvoeren Selectie herhalen Cellen invoegen of verwijderen of:

U kunt ook cellen invoegen of verwijderen met respectievelijk de toetscombinaties ‘Control en + teken’ of ‘Control en – teken’. � Neem een leeg tabblad voor u. � Plaats in de cellen A2 t/m E3 de getallen 1 t/m 10, zoals hiernaast aangegeven. � Selecteer de cellen B2 t/m B3 en versleep de selectie aan de rand naar G2 t/m G3 De cellen worden verplaatst. � Plaats de cellen op dezelfde manier ook weer terug. � Selecteer de cellen B2 t/m B3 en versleep deze aan de rand van de selectie met ingedrukte ‘Control-toets’ naar G2 t/m G3. U ziet hier nu een kopie verschijnen. � Wis de kopie in G2 en G3. � Voeg de cellen B2 t/m B3 in tussen de cellen C2 t/m C3 en D2 t/m D3 door deze met ingedrukt ‘Shift-toets’ hiernaar te verslepen. � Plaats de cellen ook weer terug. � Doe dit nogmaals, maar nu met ook nog de ‘Control-toets’ ingedrukt. � Herstel de oorspronkelijke situatie door op de knop ‘Ongedaan maken’ [Undo] te klikken. � Selecteer de cellen C2 t/m C3. � Plaats de muis op het knopje in de rechter onderhoek van de selectie. � Sleep het knopje vijf cellen naar beneden. U ziet dat de reeks wordt voortgezet. � Sleep hetzelfde knopje weer vijf cellen omhoog. De reeks wordt weer verwijderd. � Selecteer de cellen C2 t/m C3. � Sleep het knopje met ingedrukte ‘Control-toets’ vijf cellen naar beneden. U ziet dat de reeks nu gekopieerd wordt. � Laat de kopie staan en selecteer opnieuw de cellen C2 t/m C3.

CTS/Advies

19-12-14

25/187


� Sleep het knopje met ingedrukte ‘Shift-toets’ twee cellen naar beneden. U ziet dat twee lege cellen worden tussengevoegd. � Sleep hetzelfde knopje weer met ingedrukte ‘Shift-toets’ twee cellen omhoog De lege cellen verdwijnen weer. De hiervoor genoemde handelingen, kunt u ook op hele kolommen of rijen uitvoeren. Het selectieknopje verschijnt dan respectievelijk geheel links van de rij of geheel bovenaan de kolom.

CTS/Advies

19-12-14

26/187


2.9 Wisselen tussen absoluut en relatief Bij het kopiëren van formules is het van belang of een verwijzing naar een cel absoluut of relatief is. Is de verwijzing ‘relatief’ dan wordt de verwijzing, na het kopiëren van de formule naar een andere plaats op het werkblad, aangepast aan deze nieuwe plaats. Is de verwijzing ‘absoluut’ dan blijft de verwijzing, na het kopiëren van de formule, naar de oorspronkelijke cel verwijzen. Een verwijzing naar een kolomletter of regelnummer wordt relatief door er in de formule een dollarteken voor te plaatsen. In onderstaand voorbeeld wordt de formule gekopieerd van B4 naar E4. De verwijzing met het dollarteken voor de kolomletter wordt niet gewijzigd

U kunt de aard van de verwijzing tijdens het intypen wijzigen door de cursor in de verwijzing te plaatsen en op F4 te drukken. U krijgt dan achtereenvolgens zowel de kolomletter als het regelnummer absoluut, alleen kolomletter relatief, alleen rijnummer relatief of zowel kolomletter als rijnummer relatief. � Type in cel B6 de formule: =B3. � Versleep cel B6 met ingedrukte ‘Ctrl-toets’ naar E6. U ziet dat de formule nu naar E3 verwijst. � Selecteer de celreferentie in de formule in cel B6 en druk op functietoets F4. Voor de ‘B’ en de ‘3’ verschijnen dollartekens. � Druk op ‘Enter’. � Versleep de cel met de formule opnieuw met ingedrukte ‘Controltoes’ naar E6 en bekijk het effect van de dollartekens.

CTS/Advies

19-12-14

27/187


2.10 Bijzondere getalsopmaak In het dialoogkader ‘Celeigenschappen’ [Cells] vindt u ook opties voor de getalsopmaak. � Plaats in drie cellen onder elkaar de getallen 1000, -1500 en 0. � Selecteer de drie cellen. � Klik nu in het groepsvak ‘Getal’ [Number] op het kleine diagonale zwart pijltje in de rechter onderhoek van het groepsvak. Op het scherm verschijnt het dialoogkader voor de opmaak, maar nu met het tabblad ‘Getal’ [Number] voorgeselecteerd.

Onder de categorie ‘Aangepast’ [Custom] vindt u de verschillende vormen voor getalsopmaak in code weergegeven. In het tekstvak onder het woordje “Type:” kunt u deze getalsopmaak naar eigen smaak verder aanpassen. � U kunt meerdere getalsopmaakvormen achter elkaar typen, onderling gescheiden door een puntkomma (;). De eerste getalsopmaak is dan voor positieve getallen, een eventuele tweede voor negatieve getallen en een eventuele derde voor het getal nul. � Tekst tussen dubbele aanhalingstekens wordt exact zo weergegeven bij het getal als getypt. � Een * doet het teken dat daarna komt net zolang herhalen tot de hele cel er meer is uitgevuld. In nevenstaand voorbeeld wordt de * gevolgd door een spatie-teken en wordt het gebied tussen het woord “Euro” en het getal uitgevuld met spaties. � Het teken _ zorgt voor een spatie ter breedte van het teken dat er direct na komt. In nevenstaand voorbeeld wordt de _ gevolgd door een min-teken. De positieve getallen worden dus voorafgegaan door een spatie met de breedte van een min-teken.

CTS/Advies

19-12-14

28/187


� Geef de geselecteerde cellen de aangepaste getalsopmaak: _-"Euro"* 0,00;-"Euro"* 0,00;"nul" U kunt ook datums van een eigen opmaak voorzien: � Selecteer een cel met een daum en geef deze het aangepaste datumformaat: dddd dd mmm jjjj

CTS/Advies

19-12-14

29/187


2.11 Voorwaardelijke opmaak U kunt ook opmaak toekennen die afhankelijk is van de waarde van de cellen. � Type de getallen 1 t/m 10 onder elkaar in een werkblad. � Selecteer de getallen. � Kies de optie ‘Start – Stijlen – Voorwaardelijke opmaak’ [Home – Styles – Conditional Formating]. � Gaat met de muis over de subopties die u vindt onder de derde tot de zesde optie uit het menu. U ziet deze, als u er met de muis over heen gaat, onmiddellijk toegepast op de geselecteerde cijfers. � Kies voor ‘Markeringregels voor cellen – Groter dan’ [Highlight Cells Rules]. � Maak een regel dat alle cellen met een waarden boven 5 groen worden. � Maak ook een regel dat de cellen met een waarde onder 5 rood worden. � Open opnieuw het menu voor de voorwaardelijk opmaak, � Kies nu voor de optie ‘Regels beheren’ [Manage Rules].

� U ziet dat de regels die u gemaakt hebt op aan een lijst zijn toegevoegd. De regels worden op volgorde van onder naar beneden op de cellen toegepast. � Klik op ‘Nieuwe regel’ [New Rule] � Kies voor ‘Een formule gebruiken om te bepalen welke cellen worden opgemaakt’ [Use a formula to determine wich cells to format]. � Type een formule die de cellen met een positieve waarde blauw maakt tussen de haken komt een ‘relatieve celverwijzing’ naar de eerste cel van de geselecteerde cellen. � Bekijk het resultaat.

CTS/Advies

19-12-14

30/187


2.12 Formules in cellen invoeren U kunt na het typen van een formule, deze op drie verschillende manieren in cel(-len) invoeren. Toetscombinatie Enter ‘Control+Enter’

‘Shift+Control+Enter’

Effect Vult de formule in één cel in. Vult de formule in de actieve cel in en kopieert deze gelijk naar alle andere geselecteerde cellen. Vult in alle geselecteerde cellen een matrixformule in waarbij elke cel uit de selectie één van de antwoorden van de matrix bevat.

� Maak nevenstaand voorbeeld na � Selecteer de cellen B4 t/m D4 � Type in cel B4 de verwijzing naar cel A1 in. � Druk op ‘Control+Enter’.

De formule wordt gekopieerd naar alle cellen uit de selectie. � Maak nevenstaand voorbeeld na � Selecteer B2:D4 � Type in B2 de formule zoals hiernaast weergegeven. � Druk op ‘Shift+Control+Enter’.

Er wordt een matrixformule in de cellen geplaatst, met in ieder cel één van de uitkomsten uit de matrixberekening.

CTS/Advies

19-12-14

31/187


2.13 Cellen namen geven U kunt cellen namen geven. Deze namen kunt u vervolgens in formules gebruiken in plaats van celreferenties. Dit maakt formules veel leesbaarder. Bovendien kunt u op deze manier cellen veel sneller selecteren. � Selecteer de tabel uit bovenstaand voorbeeld. � Zorgt er voor dat de formulebalk op het scherm wordt weergegeven. Is dit niet het geval dan doet u dit met de optie ‘Beeld – Weergeven – Formulebalk’ [View – Show – Formula Bar]. � Klik in de formulebalk op de celreferentie die u geheel links in de balk ziet. � Type hier in plaats van de celreferentie de naam “tabelgegevens” en druk op ‘Enter’. � Type onder de tabel de formule: =SOM(tabelgegevens) [=SUM(…)] U ziet dat door de formule de getallen in de tabel gesommeerd worden. � Klik in de formulebalk op het pijltje achter de celreferentie. � Kies in de keuzelijst de optie “tabelgegevens”. U ziet dat de tabel geselecteerd wordt.

CTS/Advies

19-12-14

32/187


2.14 Gegevens in geselecteerde cellen typen Als gegevens op bepaalde plaatsen op het werkblad moeten worden ingevuld, dan kunt u deze cellen voorafgaand aan het intypen selecteren. Gebruik de toetscombinatie ‘Shift+Klik’ om een selectie uit te breiden en de toetscombinatie ‘Control+Klik’ om een volgende selectie te beginnen. Op deze manier kunt u, alvorens de gegevens in te typen, een meervoudige selectie maken. � Maak een meervoudige selectie, zoals in onderstaand voorbeeld aangegeven. Breidt een selectie uit met ‘Shift+Klik’ en begin een volgende selectie met ‘Control+klik’.

U kunt nu niet meer de muis en cursor-toetsen gebruiken om binnen de selectie te navigeren. In plaats hiervan gebruikt u nu hiervoor de toetsen ‘Tab’ en ‘Enter’. Toetsen Enter Shift+Enter Tab Shift+Tab

Verplaatsing invoercel binnen selectie Cel naar beneden of bovenste cel volgende kolom. Cel omhoog of onderste cel voorgaande kolom. Cel naar rechts of eerste cel volgende regel Cel naar links of laatste cel voorgaande regel.

� Type de cijfers uit bovenstaand voorbeeld, in oplopende volgorde, in. U mag daarbij niet tussentijds de cellen opnieuw selecteren. U mag bij het intypen voor het verplaatsen van de actieve cel, alleen gebruik maken van de ‘Shift-‘, Enter-‘, en ‘Tab-toets’ of een combinatie hiervan.

CTS/Advies

19-12-14

33/187

2.15 Herhaling basis/verdieping: oefeningen Oefening 7.: Gegevens invoeren � Open een nieuw document. � Vul uw scherm in zoals hieronder weergegeven.

� Bewaar de map onder de naam “Factuur”. Oefening 8.: Kolombreedte instellen m.b.v. de kolomgrenzen � Neem uw document “Factuur” uit voorgaande oefening of open het document “Factuur-A-Kolombreedtes”. � Pas de breedte van de kolommen A t/m D aan de inhoud aan, door deze kolommen te selecteren en te dubbelklikken op een van de kolomgrenzen. � Stel breedte van kolom-A in, zoals hieronder weergegeven, door de begrenzing tussen de kolommen A en B te verslepen.

� Selecteer de kolommen B t/m D. � Plaats de muisaanwijzer tussen de kolomletters op de rechter kolomgrens van één van deze drie kolommen en sleep deze tegen de linker kolomgrens aan. De kolommen zijn nu onzichtbaar geworden. De kolommen zijn echter nog wel geselecteerd. � Schakel de selectie uit door op een willekeurige plaats in het werkblad te klikken. ©CTS/Advies

19-12-2014

Pag.nr.: 35/187

Herhaling basis/verdieping: oefeningen

� Plaats de muis iets rechts van de begrenzing tussen de kolommen A en E. Als u de muis goed heeft gepositioneerd, dan vertoont de muisaanwijzer een dubbele verticale streep. Trek nu de kolomgrens naar rechts. Kolom D wordt weer zichtbaar. � Maak op deze manier ook kolom-C en tot slot kolom-B weer zichtbaar. � Klik in het vakje in de linker bovenhoek van het werkblad. Hierdoor wordt het hele werkblad, dus alle kolommen en alle rijen, geselecteerd. � Sleep twee kolomgrenzen tegen elkaar en sleep vervolgens twee rijgrenzen tegen elkaar. Het hele werkblad is nu onzichtbaar geworden. � Sleep de rechter kolomgrens van het vakje een kolombreedte naar rechts en sleep de onderste rand van het vakje één regel naar beneden. Het gehele werkblad wordt nu weer zichtbaar. � Laat het hele werkblad geselecteerd staan en dubbelklik éénmaal op een kolomgrens en éénmaal op een rijrand. Alle kolommen krijgen hun ‘best passende’ hoogte en alle rijen krijgen hun standaardhoogte terug. � Versmal kolom-A zodat de tekst nog net in zijn geheel te lezen is. � Selecteer tot slot de kolommen B t/m D en maak deze beiden 8 tekens breed. De juiste breedte ziet u tijdens het verslepen van de kolomgrenzen, bij de muisaanwijzer vermeld. Oefening 9.: Formules absoluut en relatief kopiëren � Activeer uw document “Factuur” uit voorgaande oefening of open, indien u dit niet meer hebt, het document “Factuur-B-formules”. � Plaats in cel E7 het woordje: “Exclusief”. � Plaats in cel E8 een formule die het tarief in cel C8 en het aantal uren in cel D8 met elkaar vermenigvuldigt. U vindt het antwoord achter in deze handleiding. � Plaats in cel F7 het woordje: “BTW”. � Plaats in cel F8 een formule die het bedrag in E8 vermenigvuldigt met de BTW in cel B6. U brengt de verwijzingen naar cellen aan door, bij het intypen van de formule, deze cellen aan te klikken. Druk echter, na het aanklikken van cel B6 een paar maal op functietoets F4, zodat er een dollarteken voor het cijfer-6 komt te staan.

CTS/Advies

19-12-14

36/187


� Plaats in cel G7 het woordje: “Inclusief”. � Plaats in cel G8 een formule die het bedrag in E8 optelt bij het bedrag in F8. U vindt het antwoord achter in deze handleiding. � Selecteer de cellen E8 t/m G8. � Dubbelklik op het selectieknopje U ziet dat de formules over de hoogte van de tabel, naar beneden gekopieerd worden. Oefening 10.: Rijen en kolommen invoegen � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur C-rijen invoegen”. � Selecteer het gebied A3 t/m G13. � Plaats de muis op de onderrand van het geselecteerde gebied en sleep het gehele gebied één regel naar beneden. � Selecteer de gehele rij 4 door op het rijnummer te klikken. � Plaats de muisaanwijzer op het selectieknopje dat u geheel links op de onderste regel van de geselecteerde regel ziet. � Versleep het selectieknopje met ingedrukte ‘Shift-toets’ één regel naar beneden. Als u deze handeling correct uitvoert, dan wordt een lege regel ingevoegd. � Selecteer regel 9 en druk de ‘Control-toets’ en het plusteken tegelijkertijd in (als het plusteken aan de bovenzijde van een toets staat, dan moet u dus ook de ‘Shift-toets’ indrukken). � Voeg op de zelfde wijze in de tabel een lege regel in onder “Detailtekening”. � Selecteer kolom-E en voeg een lege kolom in door tegelijk op de ‘Control-toets’ en het plusteken te klikken. Het resultaat moet er uit zien als in onderstaand voorbeeld.

CTS/Advies

19-12-14

37/187


Oefening 11.: De Som-knop � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur D-Somfunctie”. � Selecteer cel D13 en klik op de som-knop in het groepsvak ‘Start – Bewerken’ [Home – Editing]. U ziet dat automatisch een somformule wordt ingevuld die de twee getallen er boven optelt. � Klik nogmaals op dezelfde knop of druk op ‘Enter’ om de formule in de cel in te voeren. � Selecteer cel D18 en dubbelklik de som-knop. U ziet dat nu een somformule wordt ingevuld die de overige cijfers uit de kolom optelt. Controleer de telling door met de muis de cellen D14 t/m D17 te selecteren. Het totaal van de getallen binnen de selectie verschijnt dan op de statusregel. � Selecteer cel D19 en klik de som-knop. U ziet dat nu een somformule wordt ingevuld die de twee subtotalen bij elkaar optelt.

� Druk op ‘Enter’ op de formule in te voeren in de cel.

CTS/Advies

19-12-14

38/187


Oefening 12.: kopiëren met de Control-toets � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur E-kopiëren”. � Plaats in cel A13 de tekst “Subtotaal”. � Kopieer de inhoud van deze cel naar de cel A18, door deze er met de ‘Control-toets’ ingedrukt, aan de rand van de selectie, naar toe te slepen. � Type in cel A19 de tekst “Totaal”. � Selecteer cel E11 t/m E19. � Plaats in cel E11 het Euro-teken “€” u kunt dit doen door de toetscombinatie ‘Control+Alt+5’in te drukken of met de optie ‘Invoegen – Symbolen – Symbool’ [Insert – Symbols – Symbol]. � Druk de toetscombinatie ‘Control+Enter’ in. U ziet dat het euroteken niet alleen in de cel wordt, ingevoerd maar tevens naar alle geselecteerde cellen gekopieerd wordt. � Versmal kolom-E door te dubbelklikken op de celgrens tussen “kolomkop-E” en “kolomkop-F”. � Selecteer kolom-C en versleep deze aan de rand met ingedrukte ‘Shift-toets’ naar een positie tussen kolom-D en kolom-E. � Selecteer de cel C13 met de somformule. � Kopieer deze formule naar cellen F13 door deze aan de rand met ingedrukte ‘Control-toets’ hiernaar toe te verslepen. � Kopieer de formule in cel F13 naar de cellen G13 t/m H13 door het selectieknopje twee cellen naar rechts te slepen. � Kopieer op de zelfde wijze de formules in de cellen C18 en C19 naar de cellen F18 t/m H19 � Selecteer de rijen 4 en 5 door met de muis over de rijnummers te slepen. � Versleep de selectie met ingedrukt ‘Shift-toets’ naar een positie tussen regel 9 en 10. � Beide regels worden op de nieuwe positie tussen gevoegd. � Selecteer kolom-E. � Sleep een kopie van kolom-E naar een positie tussen kolom-F en kolom-G door deze met ingedrukte ‘Shift-toets’ en ingedrukte ‘Control-toets’ hier naartoe te slepen. � Sleep vervolgens normaals een kopie van deze kolom naar een positie tussen de kolom-H en kolom-I.

CTS/Advies

19-12-14

39/187


� Plaats op dezelfde manier een kopie voor de laatste kolom met getallen. Oefening 13.: Getalsopmaak met het Lint � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-F-Getalspmaak”. In het navolgende gaan wij het euroteken in de kolom voor de getallen plaatsen. Daartoe gaan wij eerst weer de extra kolommen I, G en E verwijderen. � Selecteer kolom-E, kolom-G en kolom-I. Dit is een meervoudige selectie, dus dat doet u door met de ‘Control-toets’ ingedrukt op de kolomletters te klikken. � Druk vervolgens op ‘Control-toets’ en het minteken. Alle drie de kolommen worden tegelijkertijd verwijderd. � Selecteer in het werkblad de cellen D11 t/m G19. � Klik onder de tab ‘Start’ [Home] in de groep ‘Getal’ [Number] op de knop ‘Duizendtalnotatie’ [Comma Style]. De geselecteerde cellen worden met twee cijfers achter het decimaalteken weergegeven. � Pas zo nodig de kolombreedte aan. � Laat de cellen D11 t/m G19 geselecteerd staan. � Verwijder de getallen achter het decimaalteken door tweemaal op de knop te klikken om het aantal decimalen te verlagen. � Geeft het percentage in cel B6, met de knop in het ‘Lint’, een weergave met één cijfer achter de komma. � Selecteer de cellen D11 t/m G19 en geeft de getallen de ‘Financiële getalnotatie’ [Accounting Number Format].

CTS/Advies

19-12-14

40/187


Oefening 14.: Getalsopmaak met het dialoogkader � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-G-Getalsopmaak-aangepast”. � Selecteer de cellen C11 t/m C19. � Klik op het kleine zwarte diagonale pijltje dat u rechts onder in het groepsvak voor de getalsopmaak ziet. U krijgt nu het dialoogkader voor de getalsopmaak op het scherm. � Klik op de groep ‘Aangepast’ [Custom]. � Type in het tekstvak onder het woordje “Type” de getalsopmaak: 0 “uur”. � Klik op ‘OK’. � In het voorbeeld ziet u achter de getallen het woord “uur” verschijnen. � Selecteer de cellen D11 t/m G19 en bekijk de getalsopmaak die is toegekend in het dialoogkader voor de getalsopmaak. � Wijzig de opmaak zodat voor de getallen het woord “Euro” verschijnt. � Bekijk het resultaat in het werkblad. Pas zonodig de kolombreedte weer aan. Bij negatieve getallen staat het minteken voor het getal en als de waarde 0 is, dan wordt een streepje in de cel weergegeven. � Wijzig de opmaak zodat bij de negatieve getallen het minteken en bij de positieve getallen een spatie ter breedte van een minteken achter de getallen verschijnt. � Bekijk het resultaat in het werkblad. Pas zonodig de kolombreedte weer aan. � Wijzig de opmaak, zodat als het getal ‘0’ is, er helemaal niets in de cel verschijnt.

CTS/Advies

19-12-14

41/187


Oefening 15.: Datumopmaak met het dialoogkader � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-H-Datumopmaak-aangepast”. � Plaats in cel B4 de formule =NU() [=NOW()]. � Geef met het dialoogkader voor de getalsopmaak de datum de opmaak: dddd d mmmm jjjj [dddd d mmmm yyyy]. Maak de cel zo nodig breder om het resultaat te kunnen bekijken. � Geef met het dialoogvenster voor de getalsopmaak, de datum in cel B4 de opmaak : mm-dd-jj [mm-dd-yy] � Dubbelklik de kolomgrens tussen de letters “B” en “C” om de kolom ‘passend’ te maken. U krijgt nu eerst de maand en dan de dag te zien. Dit is de Amerikaanse manier om datums weer te geven. Oefening 16.: Voorwaardelijke opmaak � Selecteer de regels met getallen in de tabel. � Gebruik de voorwaardelijke opmaak om een regel te maken die alle regels rood maakt waarvan de omschrijving in de eerste kolom het woord “Subtotaal” is. Oefening 17.: Tekenopmaak met de knoppen in het ‘Lint’. � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-I-Tekenopmaak”. � Maak met behulp van de knoppen in het ‘Lint’ [Ribbon] de letters van de tekst in de cellen A1 en A2, ‘Schuin’ [Italic], 14 punts en ‘Vet’ [Bold]. � Geef de tekst in cel A8 een dubbele onderstreping [Double underline].

CTS/Advies

19-12-14

42/187


� Maak de inhoud van de cellen, C10 t/m G10 en A13 t/m G13 en A18 t/m G19 allemaal tegelijk (meervoudige selectie dus) ‘Vet’ [Bold]. Oefening 18.: Uitlijning met de knoppen in het ‘Lint’. � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-J-Uitlijning”. � Centreer met behulp van de knoppen in het ‘Lint’ [Ribbon] de tekst in de cellen C10 t/m G10. � Plaats de tekst in cel B5 rechts in de cel. � Selecteer de cellen A1 t/m G1. � Laat de inhoud van de cel A1 klik op de knop ‘Samenvoegen en centreren’ [Merg & Center]. � Doet hetzelfde met de inhoud van cel A2. Het samenvoegen van cellen verstoort de structuur van kolommen en rijen. Dit kan bij bepaalde functies van Excel problemen geven. � Maak met dezelfde knop het samenvoegen van de cellen A1 t/m G1 en de cellen A2 t/m G2 weer ongedaan. � Versleep de inhoud van de cellen A1 en A2 naar E1 en E2. � Klik op de knop ‘Centreren’ [Center]. U hebt visueel nu hetzelfde effect bereikt, terwijl de kolommenstructuur gehandhaafd blijft. Oefening 19.: Randen rondom cellen met de knoppen in het ‘Lint’. � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-K-Randen”. � Maak een meervoudige selectie van de cellen: C10 t/m G10, C13 t/m G13, C18 t/m G18 en C19 t/m G19. � Plaats met behulp van de knop in het ‘Lint’ [Ribbon] een ‘Dikke kaderrand ‘ rondom de cellen [Thick Box Border]. Oefening 20.: Randen rondom cellen met het dialoogkader. � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-L-Randen-dialoogkader”. � Selecteer de cellen C10 t/m G10 en C19 t/m G19. � Klik ergens binnen de selectie op de rechter muisknop en kies ‘Celeigenschappen - Rand’ [Format Cells - Border]. � Geef de cellen een rode dubbele omlijning, door eerst de rode kleur te selecteren, vervolgens een dubbel lijn te kiezen en tot slot dit aan de ‘Omtrek’ [Outline] toe te kennen.

CTS/Advies

19-12-14

43/187


Oefening 21.: Cellen namen geven � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-M-Namen”. � Kies ‘Beeld – Weergeven’ [View – Show]. � Controleer of de optie ‘Formulebalk’ [Formula Bar] aan staat. � Selecteer in de ‘Factuur’ de cellen met de uren (dus niet de cellen met de subtotalen en het totaal meeselecteren). � Klik gheel links in de formulebalk op de celreferentie die u daar ziet. � Type hier voor de cellen de naam “Uren” en druk op ‘Enter’. � Klik in een willekeurige cel in het werkblad, zodat de uren niet meer geselecteerd zijn. � Klik in de formulebalk op het zwarte pijltje achter het vak met de celreferentie. � Selecteer de naam “Uren”. U ziet dat de cellen met de uren weer geselecteerd worden. U kunt niet direct beginnen met andere aantallen uren in te typen. � Selecteer cel C9 en type hier de formule: =SOM(uren) [=SUM(…)] U ziet dat de formule correct wordt uitgerekend onder gebruikmaking van de celnaam. Oefening 22.: Sjabloon maken � Activeer het werkblad uit voorgaande oefening of open het oefenbestand “Factuur-N-Sjabloon”. � Klik geheel links in de formulebalk op het pijltje om het keuzelijstje te openen en selecteer de naam “uren”. U ziet dat nu alle cellen geselecteerd worden die tot de groep “Uren” behoren. � Wis de inhoud van de cellen door op de ‘Delete-toets’ te drukken. � Kies de optie ‘Bestand - Opslaan als’ [File - Save As]. � Kies in de keuzelijst achter ‘Opslaan als’ [Save as type] voor een ‘Excel-sjabloon’ [Excel Template]. Merk op dat Excel een special map op schrijf selecteert, waar de sjablonen in worden opgeslagen. � Sla het bestand op met de naam “Factuur”. � Sluit het document. � Klik op de optie ‘Bestand - Nieuw’ [File - New]. � Klik op ‘Mijn sjablonen’ [My Templates]. � Kies het sjabloon “Factuur” als basis voor uw nieuwe document.

CTS/Advies

19-12-14

44/187


Het sjabloon wordt geopend en u kunt dit opnieuw voor een factuur gebruiken. � Vul waarden in. � Klik in de werkbalk ‘Snelle toegang’ [Quick Access] de optie ‘Bewaren’ [Save]. U ziet dat het sjabloon zich niet laat overschrijven en toch met het dialoogkader ‘Bewaar als’ [Save As] komt. � Klik op ‘Annuleren’ [Cancel] en sluit het document zonder dit te bewaren met ‘Bestand – Sluiten’ [File – Close]. Extra oefening verwijzingen.

23.:

Formule

met

absolute

en

relatieve

Hierna ziet u een tabel met de tafels van 1 tot 10. Deze gaat u zelf maken. � Neem uw document “Oefening-gevorderd” voor u. � Neem een nieuw tabblad voor u en geeft het de naam “absoluutrelatief”. � Plaats in de cellen A2 en A3 de getallen 1 en 2. � Selecteer de cellen A2 en A3 en trek vervolgens het selectieknopje 8 cellen naar beneden. U ziet de getallen 1 t/m 10 verschijnen. � Selecteer de cellen A2 t/m A11. � Kopieer de selectie. � Selecteer cel B1. � Klik met de rechter muisknop in cel B1 en kies in het snelmenu de optie ‘Plakken Speciaal’ [Paste Special]. � Kies voor ‘Transponeren’ [Transpose] en klik ‘OK’ U ziet dat de gekopieerde kolom als rij wordt ingeplakt. � Plaats nu in cel B2 een formule die het getal op de bovenst rij vermenigvuldigd met het getal in de eerste kolom. Om er voor de zorgen dat bij het kopiëren de verwijzingen naar de bovenste rij en de eerste kolom vast blijven, plaatst u dollartekens in de verwijzingen. � Nadat u de formule heeft ingevoerd in cel B2 kopieert u de formule naar de andere cellen van de tabel, door het verslepen van de selectieknop, eerst naar cel K2 en vervolgens naar cel K11. Als u de absolute verwijzingen goed in de formule geplaatst hebt, dan verschijnen de tafels van 1 t/m 10.

CTS/Advies

19-12-14

45/187


U vindt het antwoord achter in deze handleiding. � Bewaar uw document. Extra oefening 24.: Opmaak � Maak uw document “Factuur” uit voorgaande oefening af, zoals onderstaand.

� Sluit het document en bewaar de wijzigingen op schijf.

CTS/Advies

19-12-14

46/187

3. Excel of een database Om te voorkomen dat meermalen dezelfde gegevens moeten worden ingevoerd of gewijzigd, streven wij naar een scheiding tussen: � Invoer van gegevens � Opslag van gegevens � Uitvoer van gegevens

De invoer van gegevens bestaat veelal uit een invoerscherm, ook wel een formulier genoemd, waarin men gegevens kan intypen. Het programma zorgt er vervolgens voor dat de gegevens, gecontroleerd op juistheid en volledigheid, worden weggezet in de opslag. De opslag van gegevens kan bestaan uit een eenvoudige lijst, maar kan ook uit meerdere tabellen bestaan, die onderling aan elkaar gerelateerd zijn. Men spreekt dan van een database. Om gegevens uit de database te selecteren, zullen wij criteria moeten opgeven waaraan de te selecteren gegeven dienen te voldoen. Wij noemen dat een querie. Gegevens die voldoen aan de opgegeven querie worden weergegeven in een zogenaam rapport. Deze uitvoer kan bestaan uit een eenvoudige afdruk van de gegevens als lijst of als een afdruk op afonderlijke enveloppen of etiketten, of in de vorm van een grafiek. Excel kent verschillende functies voor de in- en uitvoer van gegevens: Excel-functies om gegevens in te voeren: � Importeren van gegevens � Zelf geschreven macro Excel-functies om gegevens op te halen � Namen � Zoekfuncties � Filter en databasefuncties � Koppelingen/grafieken � Draaitabellen � Macro´s

©CTS/Advies

19-12-2014

Pag.nr.: 47/187

Excel of een database

Het aantal functies voor de uitvoer van gegevens is in Excel veel groter dan voor de invoer van gegevens. Dit komt omdat Excel in de eerste plaats bedoeld is als een zogenaamde ´front end´ applicatie. Hiermee bedoelen wij een applicatie voor verwerking en presentatie van gegevens. Voor de invoer en opslag van gegevens beschikken bedrijven meestal al over adequate applicaties, zoals een boekhouding of applicaties voor voorraad- of personeelsadministratie. Deze applicaties zijn veel beter geschikt voor de invoer van gegevens dan Excel, omdat zij allerlei voorzieningen kennen om de invoer op juistheid en volledigheid te controleren. Bovendien kan men met deze applicaties dikwijls met meerdere personen tegelijk aan de invoer en mutatie van gegevens werken. Vrijwel al deze applicaties kunnen lijsten genereren met een uittreksel van de verzamelde gegevens. Deze lijsten kunnen dan vervolgens in Excel gebruikt worden voor berekening en presentatie van de gegevens. Beschikt u niet over een applicatie om gegevens in te voeren en wilt u die zelf gaan bouwen, dan kunt daarvoor beter gebruik maken van een databaseprogramma, zoals bijvoorbeeld Access.

CTS/Advies

19-12-14

48/187

4. Het gebruik van namen Cellen kunt u namen geven. In formules kunt u dan, in plaats van celreferenties, celnamen gebruiken. Hierdoor worden formules veel leesbaarder. Een formule luidt dan bijvoorbeeld niet: =B1*(1+B2) Maar: =prijsexclusief*(1+BTW)

©CTS/Advies

19-12-2014

Pag.nr.: 49/187

Het gebruik van namen

4.1 Het naamvak in de werkbalk. Voor het toekennen van namen aan cellen, kunt u gebruik maken van het naamvak in de formulebalk.

� Open het document “Voorbeelden-gevorderd” en klik op het tabblad “Namen”. � Type het tabblad “Namen” voor u. � Selecteer cel B1. � Klik in het vak in de formulebalk waar u de celreferentie B1 ziet staan. � Type in het vak de tekst “Exclusief” en druk op ‘Enter’. � Selecteer cel B2. � Type in het vak met de celreferentie de tekst “BTW” en druk op ‘Enter’. � Type in cel B3 een = teken. � Klik op B1. U ziet dat Excel nu niet de celreferentie maar de celnaam invult in de formule. � Type vervolgens: *(1+ � Klik op B2. Wederom wordt niet de celreferentie maar de celnaam “BTW” ingevuld. � Type haakje sluiten en druk op ‘Enter’ U ziet nu het bedrag ‘inclusief’ berekend onder gebruikmaking van celnamen in plaats van celreferenties.

CTS/Advies

19-12-14

50/187


4.2 De opties in het ‘Lint’ [Ribbon] voor namen. Voor het toekennen en het gebruik van namen voor cellen kent Excel een aantal geavanceerde opties. � Selecteer de cel B3. � Kies ‘Formules – Gedefinieerde namen – Naam definiëren – Naam definiëren’ [Formulas – Defined Names – Define Name].

Excel ziet dat in de cel links een tekst staat en stelt voor dit als naam van de cel te gebruiken. Onder in het dialoogkader staat een referentie naar de geselecteerde cel ingevuld. U hoeft dus niets verder te wijzigen. � Kies ‘OK’. Nadat u een aantal namen hebt aangemaakt, zal u deze willen kunnen beheren. � Kies ‘Formules – Gedefinieerde namen – Namen beheren’ [Formulas – Defined Names – Name Manager].

Met dit dialoogkader kunt u eerder gedefinieerde namen bekijken, wijzigen of weer verwijderen. � Verwijder de namen uit de lijst en keer terug naar uw document. U ziet dat Excel nu de foutmelding #NAAM? [#NAME?]geeft. In de formule worden nu namen gebruikt die niet gedefinieerd zijn. � Verwijder de namen uit de formule en vervang die weer door celreferenties.

CTS/Advies

19-12-14

51/187


4.3 Automatisch aanmaken van namen Gegevens staan op werkbladen dikwijls in de vorm van een tabel. In de eerste kolom en/of in de bovenste rij staan dan omschrijvingen, die van toepassing zijn op de gegevens in de cellen ‘daarnaast’ respectievelijk ‘daaronder’. Voor het toekennen van namen kunnen wij gebruik maken van deze omschrijvingen. � Selecteer in voorgaand voorbeeld de cellen A1 t/m B2 � Kies ‘Formules – Gedefinieerde namen – Maken o.b.v. een selectie’ [Formulas – Defined Names – Create from Selection]. In het dialoogvenster geeft u aan, waar binnen de selectie de omschrijvingen van de kolommen en/of rijen staan, die u als namen wilt gebruiken, namelijk in de: ‘Bovenste rij’ [Top row], ‘Linkerkolom [Left column], ‘Ondersterij” [Bottom row] of ‘Rechterkolom’ [Right column]. Als alles goed is gegaan, dan staat de optie ‘Linkerkolom’ [Left Column] voorgeselecteerd. � Klik op ‘OK’. De cellen B1 en B2 krijgen weer namen toegekend, zoals deze nu als omschrijving staan in de cellen A1 en A2. U kunt dit controleren door in het keuzelijstje in de formulebalk de namen aan te klikken die u daar ziet. De overeenkomstige cellen worden dan geselecteerd.

CTS/Advies

19-12-14

52/187


4.4 Namen toepassen Namen worden dikwijls pas gedefinieerd, nadat de formules al gemaakt zijn. U moet de celreferenties in de formules dan alsnog vervangen door celnamen. Ook hiervoor kent Excel een hulpmiddel. � Kies ‘Formules – Gedefinieerde namen’ [Formulas – Defined Names]. � Klik op het pijltje achter ‘Namen definiëren’ [Define Names] � Kies de optie ‘Namen gebruiken’ [Apply names]. � Selecteer de namen die u in formules wilt gaan toepassen.. � Klik ‘OK’ � Klik de formule in cel B3 en bekijk het resultaat. U ziet dat alle celreferenties weer door de opnieuw gemaakte namen vervangen zijn. Indien u vooraf niet één cel maar een aantal cellen in het werkblad selecteert, dan wordt het vervangen van celreferenties door namen alleen binnen het geselecteerde gebied uitgevoerd.

CTS/Advies

19-12-14

53/187


4.5 Zoeken in een tabel met benoemde rijen en kolommen Indien u een tabel importeert die op de bovenste rij en in de eerst kolom respectievelijk kolom- en rijnamen (velden recordnamen) bevat, dan kunt u deze namen aan de kolommen en rijen toekennen. � Open het document “Schoenen”. � Selecteer het gebied van de tabel van A1 t/m E4. � Kies de menu-optie ‘Formules – Gedefinieerde namen – Maken o.b.v. een selectie’ [Formulas – Defined Names – Create fromSelection]. � Controleer of de opties “Bovensterij’ [Top row] en ‘Linkerkolom’ [Left column] beiden staan aangevinkt. � Klik ‘OK’. � Controleer in de keuzelijst in de formulebalk hoe de namen zijn toegekend. � Type in cel C6 de formule: =herenschoenen kwartaal_2 en bekijk het resultaat.

CTS/Advies

19-12-14

54/187

4.6 Het gebruik van namen - oefeningen Oefening 25.: Namen maken met het naamvak en toepassen � Open het oefenbestand: ”Auto-H-Namen”. � Zorg dat de formulebalk zichtbaar is. Mocht dit niet het geval zijn dan kunt u deze op het scherm halen met de optie ‘Beeld - Weergeven – Formulebalk’ [View – Show – Formula Bar]. � Selecteer cel B7. � Type in het naamvak geheel links in de formulebalk, in plaats van de celreferentie de naam: Prijs_exclusief_BTW . Zoals u ziet moet u spaties in een naam vervangen door onderstreping. Vergeet niet na het intypen van de naam op ‘Enter-toets’ te drukken. � Geef vervolgens de cel B8 de naam: BTWpercentage. � Dubbelklik in uw werkblad op cel: B9. De formule in de cel wordt nu zichtbaar. � Dubbelklik in de formule op de celreferentie: B7. � Kies ‘Formules – Gedefiniëerde namen – Gebruiken in Formule’ [Formulas – Defined Names – Use in Formula]. � Selecteer de naam: “Prijs_exclusief_BTW”. � Druk op ‘Enter’. De naam wordt in de formule in plaats van de celreferentie gezet. � Druk op ‘Enter’. De formules wordt nu opnieuw berekend onder gebruikmaking van de celnaam in plaats van de celreferentie. Oefening 26.: Namen maken met het ‘Lint’ [Ribbon] en toepassen Wij gaan nu de cel B9 als naam de omschrijvingen in A9 geven. � Selecteer cel B9. � Kies de optie in het ‘Lint’ [Ribbon] ‘Formules – Gedefinieerde namen – Naam definiëren – Naam definiëren’ [Formulas – Defined Names – Define Name – Define Name]. Excel heeft de geselecteerde cel B9 en de omschrijving uit cel A9 al als naam vóór geselecteerd. � Klik op de knop ‘OK’. � Ken op dezelfde manier aan cel B10 de tekst van cel A10 als naam toe. � Vervang in de formule van B13, de celreferenties die verwijzen naar cel B9 en B10 door de celnamen, door deze verwijzingen te selecteren en vervolgens te klikken op de broncellen. � Druk op ‘Enter’. De formules wordt nu opnieuw berekend onder gebruikmaking van de celnamen in plaats van de celreferenties ©CTS/Advies

19-12-2014

Pag.nr.: 55/187

Het gebruik van namen - oefeningen

Oefening 27.: Automatisch namen aanmaken � Maak een selectie van de cellen met de overige basisgegevens en hun voorafgaande omschrijvingen. Dat zijn de cellen A12 t/m B22. � Geef de cellen als naam hun omschrijvingen. Dit doet u met de optie ‘Formules – Gedefinieerde namen – Maken o.b.v. selectie’ [Formulas – Defined Names – Create from Selection]. � Als u het goed heeft gedaan dan staat de optie ‘Linkerkolom’ [Left column] al voorgeselecteerd. � Klik ‘OK’. U ziet nu nog niets gebeuren, maar de namen zijn wel aangemaakt. � Kies in de keuzenlijst in de formulebalk de naam: “Rente_per_jaar” U ziet dat de cel B16 geselecteerd wordt. � Druk op F5. � Kies de naam “BTWpercentage” en klik op ‘OK’. U ziet dat nu de cel B8 met de naam “BTWpercentage” geselecteerd wordt. Oefening 28.: Automatisch namen toepassen � Selecteer het gebied B26 t/m B30. � Kies de menu-opdracht ‘Formules – Gedefinieerde namen – Naam definiëren’ – Namen gebruiken’ [Formulas – Defined Names – Define Names – Apply names]. Alleen de celnamen die u de laatste keer heeft aangemaakt zijn voor geselecteerd. � Selecteer ook de ander namen in de lijst door deze aan te klikken. Vergeet niet even op de schuifbalk, rechts van de lijst, te klikken. De lijst met namen is langer dan in de lijst getoond kan worden. � Klik tot slot op ‘OK’. � Bekijk de formules in het geselecteerde gebied en daar buiten. U ziet dat alleen in het geselecteerde gebied de celreferenties zijn vervangen door celnamen. � Kies opnieuw dezelfde optie in het ‘Lint’ [Ribbon] maar zorg er nu voor dat slechts één cel in het werkblad geselecteerd is. � Bekijk opnieuw de formules in het werkblad. U ziet dat nu wel in alle formules de celreferenties zijn vervangen door celnamen, voor zover u deze hiervoor gedefinieerd heeft. � Bewaar uw werkstuk met de wijzigingen op schijf. Oefening 29.: zoeken in een tabel met namen � Open het bestand “Golden”. � Selecteer de tabel, inclusief de veldnamen op de eerste regel.

CTS/Advies

19-12-14

56/187


� Geef de rijen en kolommen in de tabel namen. � Plaats in cel A13 de formule: =Joe Gewicht U ziet dat de uitkomst van de formule het getal is dat op het snijpunt van de rij en de kolom staat.

� Type in cel A14 de formule: =SOM(B4:B9) [=SUM(…)] � Vervang de celreferenties in de formuler door celnamen. U doet dit door gebruik te maken van de menu-optie ‘Formules- Gedefinieerde namen – Naam defniëren – Namen gebruiken’ [Formulas – Defined Name – Define Name – Apply names]. � Bekijk de formule in A14. U ziet dat Excel naar het begin- en eindpunt van de selectie verwijst met behulp van een de kolom- en de rij-namen, gescheiden door een spatie. Verdiepingsoefening 30.: Opties bij ‘Naam gebruiken’ Het dialoogkader ‘Namen gebruikten’ [Apply names] kent nog een aantal opties. � Neem het document uit voorgaande oefening voor u. � Open dialoogkader ‘Formules – Gedefinieerde namen – Naam definiëren – Namen gebruiken’ [Formulas – Defined Names – Define Name – Apply names] Het menu voor het toepassen van namen kent twee opties: Optie Relatief/ absoluut negeren [Ignore Relative/ Absolute]

Rij- en kolomnamen gebruiken [Use Row and Column Names]

Functie Standaard worden celreferenties door namen vervangen onafhankelijk van het feit of beide relatief of absoluut zijn Als u deze optie uitschakelt, dan worden relatieve referentie alleen door relatieve namen vervangen, absolute referenties door absolute namen en samengestelde referenties door samengestelde namen. Indien dezelfde referentie in meerdere namen voorkomt dan wordt prioriteit gegeven aan namen die naar hele kolommen of rijen verwijzen.

� Klik op ‘Opties’ [Options].

Het dialoogkader wordt nog iets verder uitgebreid.

CTS/Advies

19-12-14

57/187


Optie Kolomnaam weglaten, indien zelfde kolom. [Omit column name if same column] Rijnaam weglaten, indien zelfde rij [Omit row name if same row] Benoemingsvolgorde [Name order] - Rij Kolom [Row column] - Kolom Rij [Column row]

Functie Vervangt celreferentie door naam van de rij, indien deze in de zelfde kolom als de formule staat. Vervangt celreferentie door de naam van de kolom, indien deze in dezelfde rij staat als de formule. Volgorde in gebruik van namen: - Rijnaam gaat voor kolomnaam. - Kolomnaam gaat voor rijnaam.

� Sluit het dialoogkader en sluit he t document.

Verdiepingsoefening 31.: de waarden van namen Namen kunt u beschouwen als variabelen, waar waarden aan toegekend worden. Type Celreferentie(-s) Teksten of formules Waarden

CTS/Advies

Voorbeeld “=$B$5” of “=$A$1:$B$4” =”Totaal” of “=A1+B4” =5

19-12-14

58/187

5. Functies Excel kent honderden functies. Het gaat te ver om deze allemaal te behandelen. Veel functies zijn alleen van belang voor mensen met bepaalde beroepen. Hier zullen we ons beperken tot de functies die voor de meeste mensen belangrijk zijn. Als u eenmaal weet hoe een aantal functies werken, dan vindt u de rest meestal wel vanzelf. In de Excel-documentatie en onder ‘Help’ vindt u overigens een volledige beschrijving van alle functies.

©CTS/Advies

19-12-2014

Pag.nr.: 59/187

Functies

5.1 De functie-wizard Tijdens het typen van een formule in de formulebalk, kunt u functies invoeren met behulp van de hiernaast getoonde knop in de formulebalk. U vindt deze knop tevens onder de tab ‘Formules’ in het groepsvak ‘Functiebibliotheek’ [Function Library]. � Open het document “Voorbeelden – Gevorderd ” en klik op het tabblad “Functies”.

. � Selecteer cel C4. � Type het =-teken en klik vervolgensop de knop ‘Functie invoegen’ [Insert-Function]. Nadat u op de knop geklikt heb, verschijnt onderstaand dialoogkader. Kiest u een categorie in de keuzelijst, dan vindt u in de lijst er onder alle functies die tot die categorie behoren. De overige pictogrammen in dit groepsvak komen overheen met de verschillende categorieën functies dat u ook in de keuzelijst van het dialoogkader vindt

� Kies de functiecategorie ‘Wiskunde en trigonometrie’ [Math & Trig]. � Klik in de lijst er onder. � Druk op de letter “s” om naar de eerst functie waarvan de naam met een “s” begint, te gaan � Kies de functie ‘SOM’ [SUM]. � Klik ‘OK’.

CTS/Advies

19-12-14

60/187

Functies

Na de keuze van een functie verschijnt altijd een tweede dialoogkader waarmee u de argumenten van de functie kan invullen.

In de cel van het werkblad of in formulebalk, ziet u al hoe de formule wordt opgebouwd. Iedere functie wordt afgesloten met twee haken waartussen de 'argumenten' van de functie komen. Argumenten zijn nadere aanwijzingen over wat, op welke wijze, berekend dient te worden. Verwijzingen naar cellen kunt u in het dialoogkader invoeren, door met de muis over de cellen in het werkblad te slepen. In ons voorbeeld heeft Excel al een verwijzing naar de cellen C1 t/m C3 opgenomen. � Klik 'OK'. De functie wordt op het invoegpunt in de formulebalk ingevoerd. Naast de functie 'SOM' [SUM] kennen wij nog een aantal andere wiskundige functies: Functie SOM(getallen in referentie) [SUM(...)] PRODUKT(getallen in referentie) [PRODUCT(...)] WORTEL(getal in referentie) [SQRT(...)]

CTS/Advies

Omschrijving Sommeert de getallen binnen de referentie: =SOM(1;2) wordt 3 Vermenigvuldigt de getallen binnen de referentie: =PRODUKT(5;6;7) wordt 210 Geeft wortel van het getal in de referentie; =WORTEL(9) wordt 3

19-12-14

61/187

Functies

5.2 Afrond-functies Excel rekent met maximaal 15 cijfers. Voor het werken met bijvoorbeeld geldbedragen wilt u echter op 2 cijfers achter de komma kunnen afronden. Voor het afronden van getallen kent Excel verschillende functies. Functie AFRONDEN(getal, aantal decimalen) [ROUND(...)] INTEGER (getal; aantal decimalen ) [INTEGER(...)] GEHEEL(getal; aantal decimalen ) [TRUNC(...)] ABS(getal) [ABS(...)] REST(deeltal; deler) [MOD(...)]

Omschrijving Rondt getal af op opgegeven aantal decimalen achter de komma: =AFRONDEN(6,6666;2) wordt 6,7000 Rondt getal naar beneden af, op dichtstbijzijnde gehele getal: =INTEGER(-8,9;0) wordt -9 Verwijdert van het getal het gedeelte achter het opgegeven aantal decimalen: =GEHEEL(-8,9374;2) wordt -8,93 Maakt een negatief getal positief: =ABS(-8) wordt 8 Geeft de restwaarde van het getal na deling door de deler: =REST(8;3) wordt 2

Voor 975 medewerkers van een bedrijf wilt u een voetbaltoernooi organiseren. U formeert teams van 11 medewerkers. � Plaats in een leeg werkblad in twee cellen onder elkaar, het aantal werknemers binnen uw bedrijf en het aantal spelers in een voetbalteam � Gebruik de functie GEHEEL [TRUNC] om te berekenen hoeveel teams u krijgt. � Bereken met de functie REST [MOD] het aantal personen dat u overhoudt als reserve spelers.

CTS/Advies

19-12-14

62/187

Functies

5.3 Datum- en tijd-functies Excel kent afzonderlijke functies voor de weergave van datum en tijd. Een datum wordt door Excel vastgelegd met behulp van het nummer van een dag, beginnend bij 1 is 1-1-1900, 2 is 2-1-1900 etc. Het deel van de dag dat verstreken is, wordt als decimaal getal vastgelegd. Dit wordt gebruikt voor het berekenen van de tijd. De belangrijkste functies voor datum en tijd zijn: Functie NU() [NOW(...)]

DATUM(jaar;maand;dag ) [DATE(... )] TIJD(uur;minuut;seconde ) [TIME(... )] NETTO.WERKDAGEN (begindatum;einddatum) [NET.WORKDAYS(…)

Omschrijving Deze functie geeft een getal met voor het decimaalteken het dagnummer van de systeemdatum, en achter het decimaalteken de tijd als het deel van de dag dat verstreken is. Geeft dagnummer van opgegeven datum. =DATUM (1900;1;1) wordt 1 Geeft decimaal getal van opgegeven tijd. =TIJD(12;0;0) wordt 0,5 Telt het aantal werkdagen tussen twee data. =NETTO.WERKDAGEN(“1-1-7”;”8-1-7”) wordt 6

De feitelijke weergave van datum en tijd, bepaalt u met de getalsopmaak. Aangezien de datum- en tijdfuncties als resultaat een getal opleveren, kunt u deze getallen ook gebruiken om een berekening te maken. U kunt bijvoorbeeld berekenen hoeveel dagen er tussen twee data verlopen zijn. Of hoeveel minuten er tussen twee tijdstippen zitten. � Neem het werkblad met uw oefeningen op uw scherm. � Plaats in een cel de functie: =NU() [=NOW].

� Geef vervolgens de cel de opmaak "0,0000"

U ziet nu de numerieke waarde van datum en tijd van dit moment. � Laat de waarde in cel weer als een datum met daarachter de tijd weergeven. � Plaats in een cel er onder de datum van 7 dagen geleden. � Plaats in de cel daaronder een formule die de twee data van elkaar aftrekt. Excel geeft de uitkomst als een gewoon getal weer. Het resultaat van de berekening moet minstens 7 zijn. Het decimale deel van het getal geeft het deel van de dag dat inmiddels is verstreken.

CTS/Advies

19-12-14

63/187

Functies

� Plaats in een cel er onder de functie om het aantal werkdagen te berekenen tussen “1-7-10” en “8-7-10”. Let er op dat de data als tekst moeten worden ingevoerd. � Controleer het resultaat in uw agenda. Merk op dat ‘start-‘ en ‘einddatum’ beiden worden meegeteld.

CTS/Advies

19-12-14

64/187

Functies

5.4

Tekst-functies

Indien u gegevens importeert uit een andere applicatie, dan worden de gegevens veelal van een tekstbestand naar Excel geconverteerd. De gegevens willen dan nog wel eens overbodige spaties bevatten en de hoofdletters ontbreken. Soms moeten de gegevens in een veld uitgesplitst worden of juist worden samengevoegd. Hiervoor gebruikt u de volgende functies: Functie SPATIES.WISSEN( tekst) [TRIM(…)] BEGINKAP(tekst) [PROPER(…)]

LINKS(tekst;n) [LEFT(…)] RECHTS(tekst;n) RIGHT(…)]

MIDDEN(tekst;s;n)] [MID(…)]

GETAL(“tekst”) VALUE(“…”) VINDEN.ALLES (“zoektekst’;tekst;s) FIND(…) tekst1&tekst2

Omschrijving Verwijdert alle overbodige spaties uit tekst. SPATIES.WISSEN(“ inclusief 10,00”) geeft “inclusief 10,00” De eerste letter van elk woord wodt omgezet in een hoofdletter de overige letters van ieder woord worden kleinen letters. BEGINKAP(“inclusief 10,00”) geeft “Inclusief 10,00” Resulteert in de eerste n tekens van tekst, vanaf links. LINKS(“Inclusief 10,00;9) geeft “Inclusief”) Resulteert in de eerste n tekens van tekst, vanaf links. RECHTS(“Inclusief 10,00”;5) geeft de tekststring “10,00” Geeft de eerste n tekens van tekst, beginnend op positie s. MIDDEN((“Inclusief 10,00”;11;2) geeft de tekststring “10” Wijzigt een tekstwaarde in een nummerieke waarde. GETAL(“10,00”) geeft het getal 10 Geeft de positie waarop zoektekst voor het eerst voorkomt binnen tekst, beginnend bij teken s). VINDEN.ALLES(“ “;Inclusief 10,00” geeft 10. &-teken koppelt twee tekststrings aan elkaar. “Jan”&” “&”Jansen” geeft “Jan Jansen”.

� Neem in het bestand “Oefeningen- gevorderd” het tabblad “Tekstfuncties” voor u. � Type nevenstaande voorbeelden over en bekijker het resultaat. Kijk voor de eventuele Engelse benaming van de functies in bovenstaande tabel.

CTS/Advies

19-12-14

65/187

Functies

5.5 Logische functies Logische functies zijn belangrijk voor het uitvoeren van berekeningen onder bepaalde voorwaarden. De uitkomst van de functie is afhankelijk van een vergelijking die leidt tot de waarde WAAR [TRUE] of ONWAAR [FALSE]. Functie ALS(vergelijking; waarde indien waar; waarde indien niet waar ) [IF(…)]

Omschrijving Afhankelijk of de uitkomst van de vergelijking WAAR of ONWAAR is, leidt dit tot de waarde “indien waar” of de waarde “indien niet waar”.

Op de plaats van de vergelijking kunt u ook gebruik maken van de functies EN of OF waarmee u meerdere vergelijkingen tegelijk kunt testen. Functie EN(vergelijking1,vergelijking2,.....) [AND(…)] OF(vergelijking1,vergelijking2,.....) [OR(… )]

Omschrijving Geeft de waarde "WAAR" indien alle vergelijkingen waar zijn. Geeft de waarde "WAAR" indien één van de vergelijkingen waar is.

� Plaats in een cel de formule: =ALS(REST(NU(),1)<0,5;"Het Is ochtend”;"Het is middag") [=IF(MOD(NOW())…..) Als antwoord krijgt u te zien of het ochtend of middag is. Excel kent ook functies die onder voorwaarden een berekening uitvoeren. Functie AANTAL.ALS(referentiegebied; criterium) [COUNT.IF(…)] SOM.ALS(referentiegebied; criterium) SUM.IF(…)

Omschrijving Telt binnen het referentiegebied het aantal cellen dat een waarde bevat die voldoet aan het criterium. Sommeert binnen het referentiegebied de waarden die voldoen aan het criterium.

� Type in 10 cellen onder elkaar de waarden 1 t/m 10 � Plaats in een cel er onder de functie: =AANTAL.ALS(A1:A10;”>7”). [=COUNTIF(…)] Het antwoord is: 3 namelijk de getallen 8, 9 en 10 � Plaats daaronder de functie: =SOM.ALS(S1:A10;”>7”) [=SUM.IF(…)] Het antwoord is: 27 namelijk =8+9+10

CTS/Advies

19-12-14

66/187

Functies

5.6 Statistische functies In onderstaande tabel vindt u de meest gebruikte functies voor het maken van statistische berekeningen. Functie AANTAL(referentie) [COUNT(...)]

Omschrijving Geeft het aantal getallen in de referentie. Cellen die een logische waarde of tekst bevatten worden niet meegeteld. GEMIDDELDE(referentie) Geeft gemiddelde van de getallen die in de [AVERAGE(...)] referentie voorkomen. MAX(referentie) Maximale waarde van de getallen die bin[MAX(...)] nen de referentie voorkomen. MIN(referentie) Minimale waarde van de getallen die in de [MIN(...)] referentie voorkomen. TREND(bekende y-waarde; Voorspelde waarden volgens een lineaire bekende x-waarden;nieuwe x- benadering van een serie uitkomsten (ywaarden ) waarden), die gemeten zijn bij bepaalde [TREND(…)] bekende waarden (bekende x-waarden), gegeven nieuwe x-waarden. LIJNSCH(bekende y-waarde; Parameters m en b bij een lineaire benabekende x-waarden ) dering van een serie uitkomsten (y-waar[LINEST(…)] den), die gemeten zijn bij bepaalde bekende waarden (bekende x-waarden), volgens de lijn: Y = mX + b GROEI(bekende y-waarde; Waarden naar een exponentiële benadering bekende x-waarden; nieuwe van een serie getallen, volgens eventuele x-waarden) nieuwe x-waarden [GROWTH(…)]

In de finale van de Olympische spelen zijn de prestaties van zes sprinters op de 100 meter gemeten. � Type onderstaand voorbeeld over of open het document “Sprint”.

� Plaats in B2 de formule: =AANTAL(B1:J1) [=COUNT(…)] Het antwoord is 8 want er zijn 8 getallen binnen de referentie. � Plaats in B3 de formule: � =GEMIDDELDE(B1:J1) [=AVERAGE(…)]

CTS/Advies

19-12-14

67/187

Functies

Het antwoord is: 10,10. � Plaats in B4 de formule: =MIN(B1:J1) Het antwoord is: 9,90. Dat is de snelste tijd die gemaakt is. � Plaats in B5 de formule: =MAX(B1:J1) Het antwoord is: 10,27. Dat is de langzaamste tijd die behaald is. Met 'groeifuncties' kunt u voorspellingen doen op basis van bekende meetgegevens. Deze functies geven als antwoord een serie getallen. U dient dan ook, voordat u de functie in de formulebalk intypt, een aantal cellen te selecteren, overeenkomstig het aantal uitkomsten dat de functie oplevert. Bij invoer van de cel vanuit de formulebalk in de cellen drukt u nu niet op 'Enter' maar op 'Shift+Control+Enter'. Er blijkt in voorgaand voorbeeld een verband te bestaand tussen de prestaties van sprinters en het drinken van koffie bij het ontbijt. � Plaats in B14 t/m F14 de formule voor de berekening van de scores bij het drinken van 4 tot 32 koppen koffie volgens een lineaire benadering: {=TREND(B1:I1;B11:I11;B13:F13)} De accolades kunt u niet typen. Selecteer vooraf het gebied waar de antwoorden moeten komen staan. Type de formule in de eerste cel van de selectie, zonder de accoladers. Druk tot slot op ‘Shift+Control+Enter’. � Plaats in B17 t/m F17 de formule voor de berekening van de scores bij het drinken van 4 tot 32 koppen koffie volgens een exponentiële benadering: {=GROEI(B1:F1; B11:F11; B13:F13)} [{GROWTH(…)]}.

CTS/Advies

19-12-14

68/187

Functies

5.7 Zoekfuncties Met "zoekfuncties" kunt u een waarde in een tabel laten opzoeken. Functie VERT.ZOEKEN (zoekwaarde; tabel; kolomnummer; waar/ onwaar) [VLOOKUP(...)]

Omschrijving Zoekt verticaal in de eerste kolom van ‘tabel’ naar ‘zoekwaarde’ en geef als antwoord de waarde op de regel in de kolom met ‘kolomnummer’. In de tabel moeten de rijen gesorteerd zijn, naar alfabetisch of numeriek oplopende zoekwaarden in de eerste kolom. Indien het laatste argument de waarde ONWAAR bevat, dan wordt alleen een antwoord gegeven als de zoekwaarde ook daadwerkelijk gevonden is, anders wordt de waarde van de eerst volgende zoekwaarde weergegeven. HORIZ.ZOEKEN Zoekt horizontaal in de bovenste rij van ‘tabel’ (zoekwaarde;tabel; naar ‘zoekwaarde’ en geeft als antwoord de rijnummer;waar/onwaar) waarde in de kolom op de rij met ‘rijnummer’. [HLOOKUP(...)] In de tabel moeten de kolommen gesorteerd zijn, naar alfabetisch of numeriek oplopende zoekwaarden in de bovenste rij. De functie geeft als antwoord de waarde op rijnummer. Indien het laatste argument de waarde ONWAAR bevat, dan wordt alleen een antwoord gegeven als de zoekwaarde ook daadwerkelijk gevonden is, anders wordt de waarde bij de eerst volgende zoekwaarde weergegeven. INDEX(tabel,rijnummer, Geeft de waarde in de tabel waarvan de cel kolomnummer) rijnummer en kolomnummer heeft. VERGELIJKEN (zoek- Geeft volgnummer van ‘zoekwaarde’ binnen waarde,reeks;1/0/-1) ‘reeks’. Afhankelijk van of de waarde van het [MATCH(…)] derde argument wordt het positienummer van de zoekwaarde gevonden die groter of gelijk, gelijk of kleiner of gelijk aan de zoekwaarde is.

� Open het bestand “Adressen” 1 2 3 4

A Gerritsen Jansen Klaesen Pietersen

B Singel 2 Kerkstraat 8 Stationsplein 45 Spui 16

C Rotterdam Amsterdam Utrecht Den Haag

D 010-5555555 020-1234567 030-7654321 070-3123456

� Plaat in cel B6 de naam van een van de personen uit kolom-A. � Type in cel B7 de formule: =VERT.ZOEKEN(B6;A1:D4;2) [=VLOOKUP(…)] De formule geeft als antwoord: “Stationsplein 45” � Type in cel B8 de formule: =INDEX(A1:D4;VERGELIJKEN(B6,A1:A4,0),3) [=INDEX(…;MATCH(…);…)] De formule geeft als antwoord: “Utrecht”. � Sluit het document zonder dit te bewaren.

CTS/Advies

19-12-14

69/187

5.8 Functies – oefeningen Oefening 32.: Functies invoeren � Zorg er voor dat de formulebalk op het scherm te zien is. � Neem nevenstaand voorbeeld over. In de cellen C2 en C4 staan formules die respectievelijk het inkoopbedrag en het resultaat berekenen.. � Klik cel C5 aan. � Klik in de formulebalk op de knop 'Functie invoegen' [InsertFunction]. � Klik in de keuzelijst op de Functiecategorie 'Alles' [All] en zoek vervolgens in de lijst er onder de functie 'PRODUKT' [PRODUCT] op. � Klik ‘OK’. In het scherm dat nu volgt ziet u de formule weergegeven met tekstvakken voor de argumenten. De cursor staat in het eerste tekstvak. � Klik in het werkblad op cel C4. � In het tekstvak voor het eerste argument wordt een verwijzing naar de aangeklikte cel ingevoegd. � Klik in het tekstvak voor het tweede argument en klik vervolgens in het werkblad op cel B5. � Klik op ‘OK’. Het bedrag aan BTW wordt berekend. Oefening 33.: Functies GEHEEL en REST � Open het document “Wijnen”.

� Plaats in de cellen D9 t/m D11 formules die uitrekenen hoeveel hele dozen er geleverd moeten worden. U gebruikt hier voor de functie ‘GEHEEL’ [TRUNC]. � Plaats in de cellen E9 t/m E11 formules die uitrekenen hoeveel losse flessen geleverd moeten worden. Gebruik hier voor de functie ‘REST’ [MOD]. � Sluit het doucment zonder het te bewaren.

©CTS/Advies

19-12-2014

Pag.nr.: 71/187

Functies - oefeningen

Oefening 34.: Rekenkundige-afronding � Neem in uw document “Oefeningen-gevorderd” onderstaand voorbeeld over.

. � Plaats in C8 de formule: =AFRONDEN(C7/12;2) [ROUND()]. � Kies voor een weergave met vier decimalen. U ziet dat de uitkomst afgerond is op het tweede cijfer achter de komma. Opgave 35.: Rekenen met datums � Plaats onder elkaar op een werkblad de datum van vandaag en uw geboortedatum. � Plaats een formule in het werkblad die uw geboortedatum aftrekt van de datum van vandaag. De uitkomst geeft uw leeftijd in dagen. Verdiepingsopgave 36.: Rekenen met tijd � Plaats onder elkaar de tijd van dit moment en het tijdstip waarop de cursus vandaag begon. � Plaats een formule op het werkbad die beide tijdstippen van elkaar aftrekt. � Laat de uitkomst met een getalsopmaak in uren, minuten en seconden weergeven ("u:mm:ss"]. De uitkomst geeft aan hoe lang u alweer aan het werk bent.

Oefening 37.: Tekstfuncties � Open het document “Tekstfuncties”. � Gebruik het &-teken om in cel E2 “Titel”, “Voorletter” en “Achternaam” tot een gehele naam samen te voegen. Voeg tussen de naamdelen spaties in. Dit doet u ook weer door deze spaties met het &-tekens tussen te voegen. � Kopieer de formule achter het tabelletje naar beneden. Vervolgens gaan wij de naam weer in delen op splitsen. � Gebruik de functie ‘VINDEN.ALLES’ [FIND] om in F2 de positie van de 1ste spatie in de tekst te laten verschijnen. � Kopieer de functie achter het tabelletje naar beneden. � Gebruik de functie ‘LINKS’ [LEFT] om in cel G2 de titel van de naam uit E2 te laten verschijnen. � Kopieer de functie achter het tabelletje naar beneden.

CTS/Advies

19-12-14

72/187


� Gebruik de functie ‘VINDEN.ALLES’ [FIND] om in H2 de positie van de 2e spatie te laten verschijnen. � Kopieer de functie achter het tabelletje naar beneden. � Gebruik de functie ‘MIDDEN’ [MID] om in cel I2 de voorletters uit de naam in E2 te laten verschijnen. � Kopieer de functie achter het tabelletje naar beneden. � Gebruik de functie ‘LENGTIE’ [LENGTH] om in J2 de lengte van de gehele naam te laten verschijnen. � Kopieer de functie achter het tabelletje naar beneden. � Gebruik de functie ‘RECHTS’ [RIGHT] om in cel K2 de achternaam uit de naam in cel E2 te laten verschijnen. � Kopieer de functie achter het tabelletje naar beneden. � Gebruik de functie ‘SUBSTITUTEREN’ [SUBSTITUTE] om in cel L2 het getal uit D2 te laten verschijnen, maar met een decimale komma in plaats van een decimale punt er in. � Kopieer de functie achter het tabelletje naar beneden. � Gebruik de functie ‘GETAL’ [VALUE] om de tekst in L2 in een getalswaarde in M2 te laten verschijnen. � Kopieer de functie achter het tabelletje naar beneden. Verdiepingsoefening 38.: Tekstfuncties � Gebruik de functie ‘LINKS’ [LEFT] om het woord “saldo” uit A6 in C6 te plaatsen. � Gebruik de geneste functies ‘GETAL’ [VALUE] en ‘RECHTS’ [RIGHT] om de tekst “5,00” als getal in E6 te krijgen. � Gebruik de geneste functies ‘BEGINKAP’ [PROPER] ‘MIDDEN’ [MID] en ‘SPATIES.WISSEN’ [TRIM] om het euroteken in de vorm van een hoofdletter in cel C1 te krijgen. U vindt de antwoordrn achter in de handleiding.

Oefening 39.: De ALS-functie � Neem het document “Wijnen” weer voor u. � Plaats in C3 de datum van volgende week maandag.

� Plaats in C4 een formule die de datum van vandaag berekent. � Plaats in C5 een formule die het aantal dagen uitrekent tussen 'datum heden' en 'leverdatum'.

CTS/Advies

19-12-14

73/187


� Plaats in cel D5 een ALS-functie [IF] die, indien het aantal resterende dagen tussen de 'leverdatum' en 'datum van vandaag', kleiner is dan 2, het woord "SPOED" doet verschijnen en anders de tekst “In bestelling’. � Wijzig de leverdatum in de datum van morgen. Oefening 40.: De EN- en OF-functies � Open het document “Alsfuncties”. � Plaats in cel D2 een ALS-functie [IF-functie] die het antwoord “ja” vermeld als de persoon ouder dan 64 is. � Kopieer de functie ook naar de cellen D3 /m D5 en controleer het resultaat. � Vervang in de functie ‘ALS’ [IF] het eerste argument door een ENfunctie die niet alleen test of hij ouder dan 64 is maar tevens controleert of hij nog wel gemotiveerd is om te werken Verdiepingsoefening 41.: De EN- en OF-functies � Plaat in het eerste argument van de ALS-functie [IF-functie] een OF-functie [ORfunctie] die maakt dat iemand onstslagen wordt als hij of boven de 64 jaar en niet gemotiveerd is of als hij ouder dan 80 is.

Verdiepingsopgave 42.: Datum-/Alsfuncties Als u iemands geboorteddatum kent, kunt u zijn leeftijd uitrekenen. U moet daarbij echter wel bedenken dat indien de persoon dit jaar al jarig is geweest, dan is de persoon al een jaar ouder als wanneer deze nog niet jarig is geweest. � Plaats in twee cellen onder elkaar uw geboortedatum en de datum van vandaag. � Bepaal of iemand al jarig is geweest. Gebruik de ALS-Functie en de functies MAAND(datum) [MONTH(…)] en DAG(datum) [DAY(…)] om uit te rekenen of de maand van de verjaardag en de dag van de verjaardag al verstreken zijn.

Oefening 43.: Statistische functies � Open het document “Pandaberen” A

B

1

Gewicht

2

35,5

38,4

3

33,2

36,5

4

47,1

36,4

5

42,9

44,2

6

33,3

44,9

7

44,3

30,2

8

32,8

35,9

9

36,9

42,2

10

37,0

43,0

11

32,2

39,1

Een bioloog heeft het gewicht van twintig volwassen pandabeertjes gemeten. Dit leverde bovenstaand resultaat op.

CTS/Advies

19-12-14

74/187


� Bereken met de functie: =AANTAL(A2:B11) [=COUNT(…)] het aantal metingen in de lijst. � Bereken met de MAX-functie het gewicht van de zwaarste beer. � Bereken met de MIN-functie het gewicht van de lichtste beer. � Bereken het gemiddelde gewicht van de beren. Veridiepingsoefening 44.: variantie en standaarddeviatie Indien onder een populatie de afwijking van het gemiddelde gelijkmatig verdeeld is dan spreekt men van een Normaal-verdeling.

Kenmerkend voor een normaal verdeling is dat 68,26 % van de populatie een waarde heeft van het gemiddeld + of – 1 x de standaarddeviatie, en 95,44% heeft een waarde van het gemiddelde + of – 2 x de standaarddeviatie Functie GEMIDDELDE(referentie getallen) [AVERAGE(...)] STDEV(referentie getallen) [STDEV(...)] STDEVP(referentie getallen) [STDEVP(...)] VAR(referentie getallen) [VAR(...)] VARP(referentie getallen) [VARP(...)]

Omschrijving Geeft gemiddelde van de getallen in referentie. Standaarddeviatie van de getallen in de referentie. Standaarddeviatie van totale populatie waarvan de getallen in referentie een steekproef zijn. Variantie van een serie getallen. Variantie van totale populatie waarvan getallen in referentie een steekproef zijn.

84 % van de volwassen pandabeertjes hebben een gewicht lager dan het gemiddelde plus 1 maal de standaard-deviatie. � Bereken het gewicht dat een pandabeer moet hebben om tot de groep van 16% zwaarste beren te mogen behoren.

Oefening 45.: Lineaire trend � Open het document “Omzetten-trend” 1 2 3 4 5 6 7 8 9

CTS/Advies

A Jaar 2005 2006 2007 2008 2009 2010 2011 2012

B Omzet 1.540.000 1.560.000 1.545.000 1.590.000 1.615.000 1.590.000

19-12-14

C Benadering

75/187


10 11 12 13 14

2013 2014 2015 2016 2017

Een bedrijf heeft een staatje gemaakt van haar omzet, over de laatste 6 jaar. U gaat in de cellen C2 t/m C14 een lineaire benadering maken van de omzetten tot het jaar 2017. � Selecteer de cellen C2 t/m C14 � Plaats in cel C2 die TREND-functie [TREND] die op basis van de omzetten in B2 t/m B7 bij de bekende jaren in A2 t/m A7 de omzet raamt in de cellen C2 t/m C14 voor de jaren in B2 t/m B14. Vul de matrixformule in door op ‘Shift+Control+Enter’ te drukken. Maakt u gebruik van het dialoogkader voor het maken van een functie, dan houdt u de Shift- en de Control-toest ingedrukt als u op ‘OK’ klikt. Opgave 46.: Zoekfuncties � Open het document “Werkuren”.

U ziet dat in deze tabel de rijen alfabetisch in oplopende volgorde staan. � Plaats onder de tabel in cel B13 de functie: =VERT.ZOEKEN(B11;A4:N9;B12+1). [=VLOOKUP(…)] De functie geeft de waarde, die binnen de tabel op rij 4 (Kelvin), in kolom 3 (maandnummer 2+1) staat. Dit is 30. � Wijzig de naam in Jacob en de maand in 5 Het antwoord wordt nu: 28 � Wijzig de naam in “Rudolf”. Deze naam komt niet voor. U krijgt nu de waarden van “Peter”. � Voeg aan de functie als vierde argument de waarde ONWAAR [FALSE] toe. U krijgt nu een foutmelding als de gezochte naam niet in de eerste kolom van de tabel voorkomt.

CTS/Advies

19-12-14

76/187


Verdiepingsopgave 47.: Voorwaardelijke opmaak met zoekfunctie � Gebruik ‘Voorwaardelijke opmaak ‘ [Conditional formatting] om de cel met het aantal gewerkte uren rood te laten worden als de locatie waar gewerkt is nummer 3 is. Opgave 48.: INDEX-functie

� Plaats in cel B11 weer de naam “Kelvin”. � Plaats in het document “Werkuren” in cel D11 de formule: =VERGELIJKEN(B11;A4:A9;0) [=MATCH(…)] U ziet als antwoord het volgnummer van de naam in de kolom. � Plaats in cel C12 de afkorting “feb”. � Plaats in cel D12 een functie die het volgnummer van de afkorting “feb” geeft in de array B3 t/m M3 � Plaats in cel D13 de functie: =INDEX(B4:N9;D11;D12). [=INDEX(…)] De functie geeft de waarde, die binnen de tabel op rij 4 (“Kelvin”), in kolom 2 (“feb”) staat. Dit is 30. Verdiepingsopgave 49.:Geneste-functie � Voeg de functies samen tot één functie, waar de VERGELIJKENfuncties, binnen de INDEX- functie de plaats van het tweede en het derde argument innemen. Verdiepingsopgave 50.: Goniometrie Onder de groep 'Wiskunde en trigonometrie' vindt u ook functies voor de berekening van: het getal PI, sinus, cosinus, tanges, cotanges, boogsinus, en hyperbolische functies. Denk er aan dat u bij de berekening van bijvoorbeeld sinus of cosinus de hoek in radialen moet worden opgegeven. � Plaats in kolom-A op het werkblad een serie die oploopt van 0� tot 360� met stapgrootte van 15�. � Plaats daar achter in kolom-B een formule die deze graden naar radialen omrekent. (360°=2 radialen). � Plaats in kolom-C een formule die de sinus uitrekent van de hoeken. � Plaats in kolom-D een formule die de cosinus uitrekent van de hoeken.

CTS/Advies

19-12-14

77/187


� Plaats in kolom E een formule die de som van de kwadraten van sinus en cosinus van de gegeven hoeken uitrekent. De sinus van een hoek in het kwadraat plus de cosinus van de hoek in het kwadraat is 1.

Verdiepingsopgave 51.: Logaritme Functie LOG(referentie getal,basis) LOG10(getal) LN(getal) EXP(getal)

Omschrijving Geeft logaritme van getal met als grondtal basis. Geeft logaritme van getal met als grondtal 10. Geeft natuurlijke logaritme van getal (met als grondtal e). Geeft e tot de macht getal.

� Bereken de waarde van het getal: e. � Plaats in de cellen H1 en H2 van het werkblad twee willekeurige getallen � Plaats in H � 3 de vergelijking: =LOG(A1^A2)=A2*LOG(A1). � Verklaar het antwoord.

CTS/Advies

19-12-14

78/187

6. Alternatieven doorrekenen

©CTS/Advies

19-12-2014

Pag.nr.: 79/187

Alternatieven doorrekenen

6.1 Tabellen Een tabel geeft een overzicht van de uitkomsten van een berekening voor verschillende waarden van een of meerdere variabelen. Excel kent twee soorten tabellen. Een eenzijdige en een tweezijdige tabel voor berekeningen met respectievelijk één of twee variabelen.. 6.1.1

Eenzijdige tabel

Een eenzijdige tabel is een tabel die op basis van één variabele de uitkomsten van meerdere formules kan weergeven, welke allemaal gebruik maken van diezelfde variabele. � Open het document “Tabellen”. In C2 wordt de inkoop berekend op basis van het percentage dat in B2 staat. In C4 wordt het resultaat berekend als de som van C1 en C2. In de cellen B7 en C7 staan verwijzingen naar de uitkomsten in C2 en C4

� Selecteer het gebied van de toekomstige tabel. Binnen de selectie staan in de eerste kolom de waarden van de variabele (in dit geval het inkooppercentage) en op de bovenste regel de verwijzingen naar de formules die berekend moeten worden � Kies de optie 'Gegevens - Hulpmiddelen voor gegevens – Wat-alsanalyse - Gegevenstabel’ [Data - Data Tools – What-if-analysing Data Table]. Er verschijnt een dialoogkader op het scherm. � Geef als ‘Kolominvoercel’ [Column-Input cel] de cel op, waar de variabele zou moeten worden ingevuld om de formules opnieuw te laten berekenen. In dit geval is dit cel B2. � Klik 'OK'.

CTS/Advies

19-12-14

80/187


Excel berekent voor u de verschillende uitkomsten van de formules voor de verschillende waarden van de variabele. Op analoge wijze zou u ook een éénzijdige tabel in rijen kunnen maken in plaats van in kolommen. De waarden van de variabele komen dan op de bovenste rij en de formules komen in de eerste kolom. In het dialoogkader voor de tabel wordt dan niet de ‘Kolominvoercel’ [Column Input cel] maar de ‘Rij-invoercel’ [Row Input cel] ingevuld. 6.1.2

Tweezijdige tabel

Een tweezijdige tabel is een tabel die op basis van twéé variabelen de verschillende uitkomsten van één formule weergeeft. � Wij nemen hetzelfde voorbeeld als bij de éénzijdige tabel. Wij willen nu echter het resultaat berekend zien, bij variatie van zowel het inkooppercentage als ook het totaal aan verkopen. De eerste variabele komt weer in de voorste kolom en de tweede variabele komt nu op de bovenste rij. In de linker bovenhoek van de toekomstige tabel plaatsen wij een verwijzing naar de formule die wij berekend willen zien. In dit voorbeeld cel C4.

� Selecteer het gebied van de toekomstige tabel inclusief de voorste kolom en de bovenste rij met de variabelen.

CTS/Advies

19-12-14

81/187


� Kies 'Gegevens - Hulpmiddelen voor gegevens – Wat-als-analyse Gegevenstabel’ [Data – Data Tools – What-If-ananlysing - Data Table]. � Geef als ‘Kolom-invoercel’ [Column Input cell] cel B2, en als ‘Rijinvoercel’ [Row Input cell] cel C1 op. � Klik 'OK'.

6.1.3

De werking van een tabel

� Bekijk de formules die in de cellen van de tabel staan. Een tabel bestaat uit één zogenaamde matrix-formule [array]. Alle cellen met uitkomsten zijn feitelijk onderdeel van één en dezelfde formule, die voor verschillende waarden wordt berekend. Wij spreken daarom ook wel van een antwoorden-matrix. Het verwijderen, wissen of invoegen van cellen, is binnen een matrix-formule niet mogelijk.

Tabellen vragen veel rekentijd. Het herberekenen van tabellen kunt u daarom apart uitschakelen. � Kies ‘Bestand - Opties’ [File - Options]. � Klik op groep ‘Formules’ [Formulas]. � Klik de optie ‘Automatisch behalve voor gegevenstabellen’ [Automatic except Tables]. � Klik ‘OK’.

CTS/Advies

19-12-14

82/187


Berekening vindt nu pas plaats, nadat u hiertoe opdracht hebt gegeven. Dit doet u door op functietoets F9 te drukken � Wijzig één van de waarden van een van de variabelen. De tabel wordt nu niet direct herberekend. � Druk op F9. � Zorg er voor dat tabellen weer automatisch berekend worden bij wijziging van een waarde in het werkblad. � Sluit het document, zonder dit te bewaren.

CTS/Advies

19-12-14

83/187


6.2 Iteratie In sommige berekeningen is de uitkomst afhankelijk van zichzelf. U wilt bijvoorbeeld een bedrag aan geld lenen inclusief een extra bedrag om de rente over de lening te kunnen betalen. Het rentebedrag is afhankelijk van het bedrag dat u leent en het bedrag dat u leent is afhankelijk van het rentebedrag. De uitkomst staat niet eenduidig vast, maar nadert wel tot een bepaalde waarde. Deze limietwaarde kunt u nu met een iteratieve berekening laten benaderen. � Neem het bestand “Voorbeelden-gevorderd” voor u en klik op het tabblad “Iterarie”. � In het voorbeeld wordt het “Rentebedrag” berekend als product van het “Bedrag” en de “Rente”. Het “Te lenen bedrag” is de som van het “Bedrag” plus het “Rentebedrag”. � De rente moet echter over het “Te lenen bedrag” berekend worden. Wijzig in de formule voor de berekening van het “Rentebedrag” de verwijzing naar B1 in een verwijzing naar B4. � Druk op ‘Enter’. Excel toont een waarschuwing, dat er sprake is van een kringverwijzing, in het werkblad. Op de statusregel ziet u de cel vermeldt staan, waarin de kringverwijzing geconstateerd is.

� Klik op ‘OK’. Het uitvoeren van de berekening stopt nu. � Kies ‘Bestand - Opties - Formules - Iterative berekening inschakelen’ [File – Options – Formula - Iteration]. U kiest altijd voor zowel een herhaling van de iteratieve berekening voor een vast aantal maal en tot een bepaald verschil is bereikt. � Laat de voorgestelde waarden staan. � Kik ‘OK’. U keert terug naar het werkblad en ziet zeer nauwkeurig berekend het bedrag dat u moet lenen. � Schakel de iteratieve berekening weer uit.

CTS/Advies

19-12-14

84/187


6.3 Doelzoeken [Goal Seek] Soms wilt u weten wat de waarden van de uitgangsvariabelen moeten zijn om een bepaald resultaat te bereiken. U wilt bijvoorbeeld weten wat u maximaal kunt lenen uitgaande van uw maximale aflossingcapaciteit. � Neem in het bestand “Voorbeelden-gevorderden” het tabblad “Doelzoeken” voor u. De aflossing per periode wordt berekend met de functie: =AFLOSSING(rente;aantal termijnen;hoofdsom) [PMT(…)]. � Kies de menu-optie ‘Gegevens – Hulpmiddelen voor gegevens – Wat als-analyse - Doelzoeken’ [Data – Data Tools – What-if analyzing - Goal Seek]. � Stel het maximaal jaarlijks te besteden bedrag aan aflossing op 12000 Euro (negatief). Dit moet bereikt worden door het “Leenbedag” te wijzigen. � Klik ‘OK’.

Excel vindt een resultaat en vraag of dit akkoord is. Klik op ‘OK’ om het resultaat te behouden. Klik op ‘Annuleren’ [Cancel] om de oorspronkelijke berekening terug te krijgen. � Klik op ‘OK’.

CTS/Advies

19-12-14

85/187


6.4 Oplosser [Solver] De oplosser is een instrument om een oplossing te vinden bij variatie van meerdere variabelen. De oplosser is een zogenaamde ‘Invoegtoepassing’ [Add-In] Deze moet eerst geïnstalleerd zijn: � Klik op‘Bestand – Opties – Invoegtoepassingen’ [File – Options – Add Ins]. � Controleer of er een vinkje staat voor de ‘Oplosser’ [Solver]. � Klik ‘OK’. � Neem in het document “Voorbeelden – gevorderd” het tabblad “Oplosser” voor u. Productiemiddelen zijn schaars. Daarom nemen de variabele productiekosten toe naarmate je grotere aantallen probeert te produceren. � Selecteer cel B9. � Kies de optie ‘Gegevens – Analyse - Oplosser’ [Data – Analysis Solver]. � Kies voor optimalisatie van de winst door wijziging van het productie aantal in B1.

� Klik op ‘Oplossen’ [Solve]. Er wordt een oplossing gevonden bij een productie aantal van: 84,42.

CTS/Advies

19-12-14

86/187


U kunt beperkingen opgeven. In bovenstaand voorbeeld zou u bijvoorbeeld kunt bepalen dat het totale bedrag dat u investeert in inkoop niet meer dan 15.000,- euro mag bedragen. � Kies de optie ‘Gegevens – Analyse - Oplosser’ [Data – Analysis Solver]. � Klik in het dialoogvenster op de optie ‘Toevoegen’ [Add]. � Geef op dat de inkoop in cel B5 niet meer dan 10.000 mag zijn. � Laat de berekening opnieuw uitvoeren.

CTS/Advies

19-12-14

87/187


6.5 Scenarios U kunt een aantal alternatieven t.b.v. een presentatie vooraf op schijf vastleggen. � Voeg de knoppen ‘Scenario’ en ‘Scenariobeheer’ [Scenario Manager] toe aan de ‘Werkbalk Snelle toegang’ [Quick Acces Toolbar]. � Open het document “Glasoven.xls”. � Klik op de knop ‘Scenariobeheer’ [Scenario Manager].

� Klik op de knop ‘Toevoegen’ [Add]. � Geef het eerste scenario de naam “Glasoven1” � Vul bij Veranderende cellen’:’ [By changing cells:] een verwijzing in naar de cellen B3 tm B6, D3 t/m D5, F3 t/m F5.

� Klik ‘OK’. U ziet een overzicht met de huidige waarden in de geselecteerde cellen. � Klik ‘OK’. � Maak een tweede scenario met de naam “Glasoven2” die 1.000.000 flessen produceert per maand, een afschrijvingsperiode van 20 jaar kent.en € 2.500.000,- in aanschaf kost.

CTS/Advies

19-12-14

88/187


� Maak een derde scenario met de naam “Glasoven3” die 900.000 flessen produceert per maand, een afschrijvingsperiode van 12 jaar kent.en € 2.000.000,- in aanschaf kost. � Gebruik de knop ‘Scenario’ om de verschillende scenarios te laten weergeven. U kunt de resultaten van de verschillende alternatieven overzichtelijk weer laten geven. � Klik op de knop ‘Scenariobeheer’ [Scenario Manager]. � Klik op de knop ‘Samenvatting’ [Summary]. � Controleer de geselecteerde cellen waarvan de waarden wijzigen voor de verschillende scenario. Past dit eventueel aan. � Klik ‘OK’. U ziet een rapport met de resultaten voor de verschillende alternatieven. U kan de resultaten ook in de vorm van een draaitabel laten weergeven.

CTS/Advies

19-12-14

89/187

6.6 Alternatieven doorrekenen - oefeningen Oefening 52.: Eenzijdige tabel � Neem uw oefen-bestand voor u. � Maak in een nieuw tabblad de berekening voor de gesommeerde opbrengst en de cumulatieve opbrengst van een bedrag van € 15.000,- dat wij tegen 6% rente, gedurende één jaar op de bank zetten.

De gesommeerde opbrengst is de som van de rentebedragen die u ieder jaar kan innen. De formule voor de gesommeerde opbrengst luidt: =B1*(1+B2*B3) De cumulatieve opbrengst is de opbrengst die ontstaat indien de renteopbrengst op de bank blijft staan en u daar ook weer rente over krijgt. De formule voor de cumulatieve opbrengst luidt: =B1*(1+B2)^B3 U gaat een éénzijdige tabel maken voor de berekening van de 'cumulatieve- en de gesommeerde opbrengst 'bij belegging over 1 tot 10 jaar. � Plaats in de cellen A7 t/m A16 de getallen 1 t/m 10. � Selecteer het gebied van de tabel met in de eerste kolom de getallen 1 t/m 10 en op de bovenste rij de twee formules die u wilt laten berekenen voor de verschillende aantallen jaren. � Kies 'Gegevens - Hulpmiddelen voor gegevens – Wat-als-analyse Gegevenstabel’ [Data - Data Tools – What-If-Analysis - Data Table]. � Geef cel B3 op als ‘Kolominvoercel’ [Column-Input cell]. � Klik ‘OK’. Oefening 53.: Tweezijdige tabel � Maak in het gebied E6 t/m K16 een tweezijdige tabel voor de berekening van de 'cumulatieve opbrengst' bij belegging over 1 tot 10

©CTS/Advies

19-12-2014

Pag.nr.: 91/187

Alternatieven doorrekenen - oefeningen

jaar en bij een rentepercentage van 5 tot 10%. Zie voor het resultaat onderstaand voorbeeld.

� Bewaar het resultaat. Oefening 54.: Iteratieve berekening Een arme boer heeft geld nodig om zijn land in te zaaien. Hij leent voor één jaar 1000,- euro tegen 25% rente. Het rentebedrag moet hij vooruit voldoen en wordt berekend als product van het bedrag dat hij leent en het rentepercentage. Dit rentebedrag heeft hij echter ook niet en hij besluit dit ook te lenen. Het leenbedrag wordt dus het ‘Te lenen bedrag’ plus het ‘Rentebedrag’. Hierdoor ontstaat een cirkelredenering. � Voer deze berekening in uw oefenbestand op een leeg tabblad in. Op het moment dat u de formule invult om het lenenbedrag te berekenen als som van het hoofdbedrag en het rentebedrag, Excel toont een waarschuwing. � Klik op ‘Annuleren’ [Cancel]. Het uitvoeren van de berekening stopt nu. � Kies ‘Bestand - Opties - Formules - Iterative berekening inschakelen’ [File – Options - Formula - Iteration]. � Laat de voorgestelde waarden staan. � Kik ‘OK’. U keert terug naar het werkblad en ziet zeer nauwkeurig berekend het bedrag dat u moet lenen. � Schakel de iteratieve berekening weer uit. Oefening 55.: Doelzoeken � Type op een leeg tabblad van uw oefenbestand nevenstaand voorbeeld. De aflossing per periode wordt berekend met de functie: =AFLOSSING(rente;aantal perioden;hoofdsom) [ =PMT(…)]. � Kies de menu-optie ‘Gegevens – Hulpmiddelen voor gegevens – Wat als-analyse - Doelzoeken’ [Data – Data Tools – What-if analyzing - Goal Seek].

CTS/Advies

19-12-14

92/187


� Stel het maximaal jaarlijks te besteden bedrag aan aflossing op 12.000,- Euro (negatief). Dit moet bereikt worden door het “Rentepercentage” te wijzigen. Excel vindt een resultaat en vraag of dit akkoord is. Klik op ‘OK’ om het resultaat te behouden. Klik op ‘Annuleren’ [Cancel] om de oorspronkelijke berekening terug te krijgen. Oefening 56.: Oplosser [solver] � Klik op de ‘Bestand – Opties – Invoegtoepassingen’ [File – Options – Add Ins]. � Controleer of de ‘Oplosser’ [Solver] onder de ‘Actieve invoegtoepassingen’ [Active Add-ins] staat vermeld. � Klik ‘OK’. � Type op een nieuw tabblas in uw oefendoucment onderstaand voorbeeld over. � Selecteer cel B9.

� Kies de optie ‘Gegevens – Analyse - Oplosser’ [Data – Analysis Solver]. � Kies voor optimalisatie van de winst door wijziging van het productie aantal in B1. � Klik op ‘Oplossen’ [Solve]. Er wordt een oplossing gevonden bij een productie aantal van: 84. � Voeg nu de restrictie toe dat de totale productiekosten niet meer dan 35.000,- euro mogen bedragen. � Laat de berekening opnieuw uitvoeren Oefening 57.: Scenario’s � Voeg de knoppen ‘Scenario’ en ‘Scenariobeheer’ [Scenario Manager] toe aan de ‘Werkbalk Snelle toegang’ [Quick Acces Toolbar]. � Open het document “Auto-I-Resultaat.xls”. � Klik op de knop ‘Scenariobeheer’ [Scenario Manager]. � Klik op de knop ‘Toevoegen’ [Add]. � Geef het eerste scenario de naam “Normaal gebruik”

CTS/Advies

19-12-14

93/187


� Vul bij Veranderende cellen’:’ [By changing cells:] een verwijzing in naar de cellen B12. � Klik tweemaal ‘OK’. � Maak een tweede scenario met de naam “Oprijden” waarbij de auto 15 jaar meegaat. � Gebruik de knop ‘Scenario’ om de tweed scenarios te laten weergeven. Bekijk de kosten per kilometer als je de auto ‘Normaal gebruikt’ of als je deze helemaal ‘Oprijdt’. � Klik op de knop ‘Scenariobeheer’ [Scenario Manager]. � Klik op de knop ‘Samenvatting’ [Summary]. � Kies cel H35 als cel waarvan je de resultaten wilt zien. � Laat de resultaten als een draaitabel weergeven. .

CTS/Advies

19-12-14

94/187

7. Lijsten

©CTS/Advies

19-12-2014

Pag.nr.: 95/187

Lijsten

7.1 Definities Bij het werken met lijsten, wordt gebruik gemaakt van een aantal begrippen: Begrip Lijst

Omschrijving Tabel met gegevens van een verzameling gelijksoortige objecten. Record De verzameling gegevens die betrekking hebben op één item uit een lijst. Recordnaam Unieke aanduiding van record, meestal een nummer. Veld Eenzelfde soort gegeven dat in ieder record voorkomt. Veldnaam Unieke aanduiding van het veld. Database Eén of meerdere lijsten met gegevens.

Voorbeeld Lijst met gegevens van aangeboden meubels. Gegevens van één meubel Omschrijving van meubel Prijs van de meubels De kolomkop “Prijs” Bestand met prijslijst, klantenlijst, orderslijst…etc

In een werkblad kunt u de gegevens van een lijst opslaan. In de rijen staan dan de records en in de kolommen staan de veldgegevens. De gegevens in de records en velden kunt u gebruiken om uittreksels samen te stellen. Dit is de 'informatie' die u aan een database wilt ontlenen welke u ook kan gebruiken voor verdere berekenen. Een veldgegeven (de inhoud van een cel), is in principe de kleinste eenheid waaruit informatie kan worden samengesteld. Hebt u bijvoorbeeld in een lijst met persoonsgegevens in het veld “Naam” de achternaam inclusief voorletters opgenomen, dan is het later vrijwel onmogelijk om uit de database een lijst met alleen achternamen te halen zonder de voorletters. Bedenk altijd eerst, welke informatie u later uit de database wilt kunnen samenstellen en hoe de records daartoe in velden moeten worden opgesplitst. Roep hiervoor eventueel de hulp van informatie- en gegevensanalisten in.

CTS/Advies

19-12-14

96/187

Lijsten

7.2 Het opzetten van een lijst In een Excel-werkblad kunt u een lijst met gegevens aanleggen. Deze lijst wordt automatisch door Excel als zodanig herkend, mits deze aan een aantal voorwaarden voldoet. � Plaats op de eerste regel van de lijst de veldnamen. Deze moeten zich van de veldgegevens onderscheiden door type (tekst, getal of datum), uitlijning (links, gecentreerd of rechts) en/of letteropmaak (vet, schuin, onderstreept…etc.) � Plaats direct onder de veldnamen de gegevens van de records. Iedere kolom moet een zelfde soort gegeven bevatten (tekst, getal, of datum). � Houdt tussen de lijst en de overige gegevens op het werkblad minstens een kolom en rij leeg. Nog beter is om in het geheel geen andere gegevens op het werkblad te zetten. U kunt ook zelf aangeven welk deel van het werkblad een lijst bevat. Daarvoor geeft u het betreffende gebied op uw werkblad (records inclusief de veldnamen) de naam ‘Database’.

CTS/Advies

19-12-14

97/187

Lijsten

7.3 Gegevens invoeren U kunt gegevens invoeren uit een andere applicatie of direct in het Excel werkblad invoeren. Om de ‘Integriteit van de database te kunnen garandere is het belang de gegevens, alvorens deze in te voeren, eerst te valideren. � Open het document “Autokosten.xls”. In de eerste kolom moet altijd een datum worden ingevuld. In de tweede en de derde kolom een van de standaard omschrijvingen en in de vierde kolom een bedrag. In de laatste kolom staat een formule. � Selecteer kolom-A. � Selecteer ‘Gegevens – Gegevensvalidatie – Gegevensvalidatie’ [Data – Data Validation – Data Validation]. � Kies onder ‘Toestaan’ [Allow] de optie ‘Datum’ [Date]. � Vul bij ‘Begindatum’ [Start date] de datum 01-01-2010 in. � Vul bij ‘Einddatum’ [End date] de datum 31-12-2010 in.

� Klik ‘OK’ en probeer in de eerste kolom van de tabel een datum in te vullen uit het jaar 2011. Probeer daarna diezelfde datum uit het jaar 2010 . U kunt zelf bepalen welke waarschuwing verschijnt als u incorrecte gegevens invult. � Selecteer kolom-A en kies opnieuw voor ‘Gegevensvalidatie’ [Data Validation]. � Kies op het tabblad ‘Foutmelding’ [Error Alert] voor een ‘Waarschuwing’ [Warning] met de ‘Titel’ [Title] “Datum invoer” en met als ‘Foutbericht’ [Error message] de tekst “Vul een datum van het lopende jaar in”.

CTS/Advies

19-12-14

98/187

Lijsten

� Klik ‘OK’ en probeer in de eerste kolom van de tabel de datum te wijzigen. Als u een ongeldige datum invult, verschijnt onderstaand dialoogkader.

U kunt voor de in te voeren gegevens ook een keuzelijst samenstellen. � Type in de cellen G1 en G2 de teksten “Vaste kosten” en “Variabele kosten”. � Selecteer kolom-C. � Selecteer ‘Gegevensvalidatie’ [Data Validation]. � Kies onder ‘Toestaan’ [Allow] de optie ‘Lijst’ [list]. � Geef bij ‘Bron’ [Source] een verwijzing op naar de cellen G1 en G2. � Bekijk het effect door in de tabel een ‘Rekeningroep’ te wijzigen. � Vul onder aan de tabel een regel met gegevens toe. U ziet dat het laatste veld, dat een formule bevat, automatisch wordt aangevuld.

CTS/Advies

19-12-14

99/187

Lijsten

7.4 Sorteren van records U kunt een lijst in een werkblad op alfabetisch of op numerieke volgorde sorteren. Hiervoor zult u eerst moeten aangegeven in welke kolom de gegevens staan, waarop moet worden gesorteerd. Dit doet u eenvoudig door de betreffende kolom aan te klikken. Deze kolom noemen wij de ‘Sorteersleutel’ [Sort Key]. � Open het document “Autokosten”. U ziet een lijst waarin iemand fictief zijn uitgaven aan zijn auto heeft bijgehouden � Klik in de kolom “Bedrag”. � Kies de optie 'Gegevens – Sorteren en fileren – Sorteren van laag naar hoog' [Data - Sort and filter – Sort Ascending]. Het veld waarop gesorteerd wordt noemen wij de ‘sorteersleutel’ [Sort key] Indien de ‘sorteersleutel’ voor verschillende records eenzelfde 'veldwaarde' bevat, dan kan in tweede instantie gesorteerd worden op basis van een volgende sleutel. Op iedere sleutel afzonderlijk kan op verschillende manieren gesorteerd worden. � Kies de optie 'Gegevens – Sorteren en fileren - Sorteren' [Data-Sort and filter - Sort]. Er verschijnt nu een dialoogkader waarin u de sorteersleutels kunt opgeven.

� Geef “Kostensoort” als eerste sorteersleutel op. � Kies bij ‘Volgorde’ [Sort] de optie van ‘A naar Z’ [Ascending]. � Klik op de knop ‘Niveau toevoegen’ [Add level] om een secundaire sorteersleutel te benoemen. � Geef “Bedrag” als tweede sorteersleutel op. � Kies bij ‘Volgorde’ [Sort] de optie ‘Van groot naar klein’ [Decending]. � Klik 'OK'. � Bekijk het resultaat.

CTS/Advies

19-12-14

100/187

Lijsten

7.5

Subtotalen toevoegen

Nadat de lijst gesorteerd is, kunt u aan de gevormde groepen subtotalen toekennen. � Kies de optie 'Gegevens - Overzicht - Subtotaal' [Data – Outline Subtotals]. Wij gaan voor iedere kostensoort, subtotalen in de kolom “Bedrag” en de kolom “Dollars” invoegen. � Kies bij ‘Bij iedere wijziging in’ [Each change in] voor “Kostensoort”. � Kies bij ‘Functie’ [Use function] voorde optie 'Som’ [Sum]. � Kies bij ‘Subtotalen toevoegen aan’ [Add subtotal to] voor “Bedrag” en “Dollars”. � Klik 'OK'. In het dialoogkader voor de subtotalen vindt u nog drie extra opties. Optie Huidige subtotalen vervangen [Replace current subtotals] Pagina-einde tussen groepen. [Page break between groups] Overzicht onder de gegevens plaatsen. [Summary below data]

CTS/Advies

Functie Eventuele al aanwezige subtotalen worden eerst uit de lijst verwijderd. Als u deze optie uitschakelt, kunt u bijvoorbeeld zowel de som als het gemiddelde onder de groep laten invoegen. Na ieder subtotaal komt een pagina-einde

Geheel onder aan de lijst verschijnt een totaal van de subtotalen.

19-12-14

101/187

Lijsten

7.6 Records selecteren Voor het maken van een uittreksel van één of meerdere records die aan een bepaald criterium voldoen, maakt u gebruik van een zogenaamd 'Filter'. 7.6.1

Automatisch filter

� Klik in het gebied van de lijst. � Kies ‘Gegevens – Sorteren en Filteren - Filter’ [Data – Sort and Filter - Filter]. Naast de veldnamen verschijnen pijltjes.

Als u op een van de pijltjes klikt, dan verschijnt een lijst met veldwaarden die in het veld voorkomen. Door deze veldwaarden aan te klikken geeft u op aan welke veldwaarden de records in de selectie moeten voldoen. � Klik op het pijltje achter “Kostensoort”. � Klik eerst op ‘Alles selecteren’ [Select All] om alle vinkjes te verwijderen � Klik vervolgens alleen het vinkje bij “Brandstof” aan. � Klik ‘OK’. In de lijst worden nog alleen maar de records getoond, waarvan de veldwaarden voldoen aan het opgegeven criteria. Excel doet dit door de regels met records die niet voldoen te verbergen [Hide]. � Kopieer de lijst. � Selecteer de cel A1 van een leeg tabblad. � Plak de geselecteerde records in dit tabblad. � Merk op dat alleen de geselecteerde records gekopieerd zijn.

CTS/Advies

19-12-14

102/187

Lijsten

Dit is anders dan wanneer u handmatig een aantal regels verbogen had. Standaard zouden de verborgen regels gewoon meegekopieerd zijn. � Keer terug naar het eerste tabblad met de lijst met “Uitgaven” � Klik onder “Kostensoort” op ‘(Alles selecteren)’ [(Select All)]. � Klik ‘OK’. U krijgt nu weer alle records te zien. Filteren met behulp van het zoekvak Met behulp van het zoekvak kan u meerdere filterwaarden selecteren. � Klik op het pijltje achter de veldnaam “Kostensoort”. � Type in het zoekvak a* Alle kostensoorten die met de letter a beginnen worden gevonden. (typt u niet de * dan worden alle kostensoorten gevonden waar een a in voorkomt.) � Klik ‘OK’ � Type nu in het zoekvak b* Alle kostensoorten die met de letter beginnen worden gevonden. � Schakel het selectievakje bij ‘Huidige selectie aan filter toevoegen’ [Add curent selection to filter] aan en klik ‘OK’. � U ziet in de lijst nu alle kostensoorten die met een a en met een b beginnen. Tekst- en getal-filters gebruiken Met de menu-optie ‘Tekstfilters [Text Filter.] kunt u zelf zoekwaarden opgeven en bijvoorbeeld een logische waarde opgeven waar de veldwaarden aan moet voldoen. Met de optie ‘Aangepast filter’ binnen dit submenu kunt u meerdere voorwaarden tegelijk opgeven. � Klik op de keuzelijst bij de veldnaam “Bedrag” � Klik in het menu op de optie ‘Getalfilters’ [Number Filter]. � Klik op de optie ‘Groter dan’ [Larger then]. � Vul in het dialoogkader 150 in. � Klik op ‘OK’ om de selectie te laten uitvoeren. U ziet nu nog alleen maar de records waarvan het “Bedrag” groter is dan 150. � Klik in de zelfde keuzelijst op ‘(Alles selecteren)’ [(Select All)] en vervolgens op ‘OK’, om de selectie ongedaan te maken. � Kies nu in de keuzenlijst bij de veldnaam “Bedrag” de optie ‘ Getalsfilter - Aangepast filter’ [Number Filter - Custom Filter].

CTS/Advies

19-12-14

103/187

Lijsten

Er zijn de verschillende mogelijkheden om aan te geven waar de veldwaarden aan moeten voldoen. Met de optie 'EN' [And] geeft u aan dat een veldwaarde aan meerdere criteria tegelijk moet voldoen. Met de optie 'Of' [Of] geeft u aan dat een veldwaarde aan één van de opgegeven criteria moet voldoen. � Vul de criteria uit bovenstaand plaatje in en klik ‘OK’. � Wis vervolgens alle criteria weer. Bij het zoeken wordt door Excel geen onderscheid gemaakt tussen hoofdletters en kleine letters. � Schakel het ‘Autofilter’ weer uit. 7.6.2

Het uitgebreid filter

Met het zogenaamde 'Uitgebreid filter' [Advanced Filter] kunt u gebruik maken van criteria die u zelf vooraf in het werkblad geplaatst heeft � Voeg boven de lijst 7 lege regels in. U kunt dit doen door regel 1 t/m 7 te selecteren en vervolgens op ‘Control+Shift+ +’te klikken � Neem onderstaand voorbeeld over.

� Klik in de lijst. � Kies de menu-optie ‘Gegevens - Sorteren en filteren – Geavanceerd’ [Data – Sort and Filter – Advanced]. Op het scherm verschijnt een dialoogkader. Als u een cel in de lijst geselecteerd had, dan heeft Excel het gebied van de lijst al bij ‘Lijstbereik’ [List Range] ingevuld.

CTS/Advies

19-12-14

104/187

Lijsten

� Vul een verwijzing naar het gebied met de criteriagegevens in, bij 'Criteriumbereik' [Criteria range]. � Klik ‘OK’ om het resultaat te bekijken. In de lijst ziet u nu nog alleen maar records die voldoen aan de criteria. Voor het criteriumbereik gelden de volgende voorwaarden: � In het criteriumbereik mogen één, enkele, alle of meerdere malen de zelfde veldnamen voorkomen. Criteria naast elkaar op dezelfde regel, gelden als een OF-criterium. � Het gebied met de zoekwaarden mag uit meerdere regels bestaan. Criteria onder elkaar op verschillende regel, gelden als een ENcriterium. � Het criteriumbereik mag geen lege regels bevatten. � Plaats het criteriumbereik bij voorkeur op een lege plek bovenaan het werkblad. Dit in verband met uitbreiding van de database naar beneden. Splits eventueel het venster horizontaal, zodat tijdens het zoeken in de database het gebied met de criteria zichtbaar blijft. Met de optie 'Kopiëren naar andere locatie' [Copy To Another Location] kunt u het uittreksel op een andere plaats van het werkblad laten weergeven. Boven aan het ophaalgebied moet u vooraf de veldnamen plaatsen, van de velden die u wilt hebben weergegeven. � Plaats op uw werkblad onder de lijst naast elkaar de namen van de velden die u in het uittreksel wilt hebben weergegeven.

� Benoem de velden als 'Ophaalgebied', door in het dialoogvenster voor het 'Uitgebreid filter' een verwijzing naar het gebied met de veldnamen achter 'Kopiëren naar' in te vullen. Indien u slechts een beperkt aantal velden in het uittreksel opneemt, dan kan het gebeuren dat van verschillende records identieke gege-

CTS/Advies

19-12-14

105/187

Lijsten

vens vermeld worden. Met optie 'Alleen unieke records' [Unique Records only] worden dergelijke records slechts één keer weergegeven. � Sluit het document zonder het te bewaren.

CTS/Advies

19-12-14

106/187

Lijsten

7.7 Draaitabellen [Pivot tables] U kunt de gegevens uit een lijst in een tabel laten weergeven uitgezet tegen tegen één of meer andere veldwaarden. U kunt bijvoorbeeld de bedragen uit onze voorbeeldlijst met autokosten, in een tabel laten uitzetten gesommeerd naar kostensoort en rekeninggroep. � Open het document “Autokosten”. � Selecteer een cel in de lijst. � Kies ‘Invoegen – Tabellen - Draaitabel’ [Insert – Tables – Pivot table]. Op het scherm verschijnt onderstaand dialoogkader.

� Controleer of de lijst juist geselecteerd is en dat de draaitabel op een nieuw werkblad wordt aangemaakt. � Klik ‘OK’. Op het scherm verschijnt nu de layout van een tabel, echter nog zonder gegevens.

� Sleep uit de “Lijst met draaitabelvelden’ de veldnaam “Rekeninggroep” naar de bovenste regel van de tabel (rij 3)

CTS/Advies

19-12-14

107/187

Lijsten

� Sleep de veldnaam “Kostensoort” naar de eerste kolom van de tabel (kolom-A) � Sleep de veldnaam “Bedrag” naar het centrale deel van de tabel.

� Bekijk het resultaat. Zowel boven de eerste kolom als de bovenste rij vindt u nu een keuzelijst, waarin je de veldwaarden kunt kiezen die u in de tabel wilt hebben weergegeven. � Klik op het pijltje achter “Kostensoort” U kunt nog een ‘derde dimensie’ aan de tabel toevoegen, door een extra veldnaam naar de eerste kolom of bovenste regel te slepen. of deze naar de bovenste regel van het werkblad slepen. � Sleep het veld “Datum” eerst naar een positie achter het veld “Kostensoort”. U ziet dat nu achter elke kostensoort de datums vermeld worden waarop uitgaven plaatsvonden. � Sleep het veld “Datum” naar de bovenste regel van de tabel (rij 1). U ziet nu op de bovenste regel van de tabel een keuzelijst waarin u een datum kunt kiezen waarvan u de uitgaven wilt zien. 7.7.1

De tab ‘Opties’ [Options]

De draaitabel heeft twee extra tabs in het Lint. � Selecteer de tab ‘Opties’ [Options].

Groepsvak Draaitabel

Opties [Options] ‘Opties’ [Options]

CTS/Advies

19-12-14

108/187

Lijsten

Indeling & opmaak

Totalen en filters

CTS/Advies

19-12-14

109/187

Lijsten

Weergave

Afdrukken

CTS/Advies

19-12-14

110/187

Lijsten

Gegevens

Actief veld

‘Veldinstellingen’.

Groeperen

Voor het samenvoegen van veldwaarden tot groepen

Sorteren

Voor sortering van tabel op basis van geselecteerde kolom of rij.

Gegevens

Voor wijziging of vernieuwing van de gegevensbron.

Acties

Voor het wissen of selecteren van een deel van de draaitabel of het verplaatsen van de draaitabel naar een ander werk- of tabblad.

CTS/Advies

19-12-14

111/187

Lijsten

Berekening

Voor berekening van veldwaarden en het eventueel toevoegen van extra velden

Extra

Voor het omzetten van de draaitabel naar een draaigrafiek.

Weergeven en verbergen

Voor weergave van de verschillende onderdelen van de draaitabel.

7.7.2

De tab. ‘Ontwerpen’ [Design]

� Selecteer de tab ‘Ontwerpen’ [Design].

Groepsvak Indeling

Opties Voor selectie van de subtotalen. Hiervoor moeten er wel meerdere velden in de eerste kolommen of bovenste rijen voorkomen. Eindtotalen:

Rapportindeling:

Lege rijen:

CTS/Advies

19-12-14

112/187

Lijsten

Opties voor draaitabel/stijlen

Hier kiest u voor het afzonderlijke accentueren van ‘Rij- en kolomkoppen’ en de weergave van gestreepte kolommen en rijen. De opties die u hier aanklikt zijn bepalend voor de stijlen die in het volgende groepsvak geboden worden.

Draaitabelstijlen

Voor de keuze van een stijl voor de draaitabel.

CTS/Advies

19-12-14

113/187

7.8 Lijsten – oefeningen Opgaven 58.: Gegevensvalidatie � Open het document “Autokosten.xls”. � Selecteer kolom-A. � Selecteer ‘Gegevens – Gegevensvalidatie – Gegevensvalidatie’ [Data – Data Validation – Data Validation]. � Kies onder ‘Toestaan’ [Allow] de optie ‘Datum’ [Date]. � Vul bij ‘Begindatum’ een formule in, die er voor zorgt dat er geen datum, ouder dan een jaar, kan worden ingevuld. � Vul bij ‘Einddatum’ een formule in, die er voor zorgt dat er geen datum na vandaag kan worden ingevuld.” Zie voor de antwoorden achter in deze handleiding. � Kies op het tabblad ‘Foutmelding’ [Error Alert] voor een ‘Waarschuwing’ [Warning] met de ‘Titel’ [Title] “Datum invoer” en het ‘Foutbericht’[Error message] “Vul een datum in het verleden in maar niet ouder dan 1 jaar”. � Klik ‘OK’ en probeer in de eerste kolom van de tabel een datum te wijzigen in een datum in de toekomst. Probeer daarna de daum van vandaag in te voeren. � Sorteer de tabel op datum volgorde. Van iedere kostensoort vindt u nu een voorbeeld in de eeste 7 records van de tabel. � Selecteer de eerste 7 kostensoorten en kopieer deze naar de cellen H1 t/m H7 � Selecteer kolom-B. � Selecteer ‘Gegevensvalidatie’. � Kies onder ‘Toestaan’ [Allow] de optie ‘Lijst’ [list]. � Geef bij ‘Bron’ [Source] een verwijzing op naar de cellen H1 t/m H7. � Bekijk het effect door in de tabel een kostensoort te wijzigen. � Type onder de tabel een extra regel. U zietdat het laatste veld automatisch wordt aangevuld. Opgaven 59.: Sorteren � Open, indien u dit nog niet gedaan heeft, het bestand "Autokosten". � Klik in de kolom `Kostensoort`. � Kies de opdracht 'Start – Bewerken - Sorteren en filteren - Sorteren van A naar Z´ [Home – Edit – Sort and Filte – Sort from A to Z]. � Kies de opdracht 'Start – Bewerken - Sorteren en filteren - Sorteren van Z naar A´ [Home – Edit – Sort and Filter – Sort from Z to A].

©CTS/Advies

19-12-2014

Pag.nr.: 115/187

Lijsten - oefeningen

U ziet dat de records gesorteerd worden naar oplopende of aflopende volgorde. � Probeert dezelfde handelingen ook op de kolom “Bedrag” uit. � Kies de opdracht 'Start – Bewerken - Sorteren en filteren Aangepast Sorteren' [Home – Edit – Sort and Filter – Custom Sort ]. � Kies in de keuzelijst onder “Kolom” voor ‘Sorteren op Rekeninggroep. � Kies bij ‘Volgorde’ [Sort] voor van ‘Z naar A’. � Klik op ‘Niveau toevoegen’ [Add Level]. � Kies ‘Vervolgens op Kostensoort’ en wederom van ‘Z naar A”. � Klik op ‘Niveau toevoegen’ [Add level] � Kies tot slot voor ‘Vervolgens op Bedrag’en ‘Van klein naar groot’. � Klik op ‘OK’ om het sorteren te laten uitvoeren. � Bekijk het resultaat. � Herstel de situatie door wederom op datum ‘Van oud naar nieuw’ te sorteren. Extra opgaven 60.: Sorteren op aangepaste lijst � Maak een lijstje van alle kostensoort die in de lijst met autokosten voorkomen, op volgorde zoals hiernaast weergegeven. � Selecteer de lijst. � Selecteer the optie ‘Bestand – Opties – Geavanceerd – Algemeen – Aangepaste lijst bewerken’ [File – Options – Advanced – General – Edit Custom Lists]. � Importeer de geselecteerde lijst. � Klik tweemaal ‘OK’. � Sorteer de lijst op kostensoort en kies bij ‘Volgorde’ [Order] voor ‘Aangepaste lijst’ [Custom List]. � Laat de sortering uitvoeren en bekijk het resultaat. Sortering vindt nu plaats naar de volgorde van de items in uw eigen lijst.

Opgaven 61.: Subtotalen � Sorteer de lijst nogmaals op “Rekeninggroep” en binnen iedere “Rekeninggroep” op “Kostensoort”. � Kies de menu-optie 'Gegevens - Overzicht - Subtotaal' [Data – Outline - Subtotals]. � Laat voor iedere “Rekeninggroep”, subtotalen invoegen in de kolom “Bedrag”. � Klik ’OK”. � Bekijk het resultaat. � Voor nogmaals subtotalen in, in de kolom “Bedrag” maar nu bij wijziging van de kostensoort, laat de subtotalen voor de “Rekenin-

CTS/Advies

19-12-14

116/187


groepen” staan door in het dialoogkader voor subtotalen het betreffende vinkje uit te schakelen. � Bekijk het resultaat opnieuw. � Wis met behulp van het dialoogkader voor de subtotalen, alle aangebrachte subtotalen uit de lijst. Verdiepingsoefening 62.: Sorteervolgorde Onder de knop ‘opties’ kunt u nog een aantal instelling voor het sorteren wijzigen. Optie Hoofdletter gevoelig Richting

Functie Standaard wordt eerst op hoofd en daarna op kleine letters gesorteerd. U kunt dit hier wijzigen. U kunt zowel de rijen als de kolommen onderling sorteren.

� Klik met de muisaanwijzer in de lijst in de derde rij. � Klik in het dialoogvenster voor het sorteren op de knop 'Opties'. � Kies voor het sorteren van de kolommen en klik 'OK'. � Kies voor het sorteren op de derde regel en klik 'OK'. � Verklaar het resultaat. Excel sorteert eerst de getallen en dan de teksten. Een datum is ook een getal maar wel een heel groot getal. � Herstel het sorteren met de knop ‘Ongedaan maken’ [Undo].

Opgave 63.: Records selecteren met automatisch filter � Open, indien u dit nog niet gedaan heeft, het bestand "Autokosten". � Klik op een willekeurige plaats in de lijst. � Kies ‘Gegevens – Sorteren en filteren - Filter’ [Data – Sort and Filter - Filter]. Achter de veldnamen ziet u pijltjes verschijnen. � Klik op het pijltje achter de veldnaam “Kostensoort”. � Klik de optie ‘Alles selecteren’ [Select All] uit en klik vervolgens alleen de optie "Wegenbelasting" aan. � Klik ‘OK’. U ziet nu nog alleen de records met de kostensoort “Wegenbelasting”. Merk op dat de regels er tussen onzichtbaar gemaakt zijn door deze een hoogte ‘0’ te geven. Opgave 64.: Filterwaarden zoeken � Klik op het pijltje achter de veldnaam “Kostensoort”. � Type als zoekcriteria a* en klik ‘OK’. � Alle kostensoorten die met de letter a beginnen worden gevonden. � Klik nogmaals op het pijltje achter de veldnaam “Kostensoort” � Type nu als zoekcriteria w* � Schakel het selectievakje ‘Huidige selectie aan filter toevoegen’ [Add curent selection to filter] aan en klik ‘OK’,

CTS/Advies

19-12-14

117/187


� U ziet nu in de lijst alle kostensoorten die beginnen met de leter a en de letter w. � Herstel de oude situatie door weer de optie ‘Alles selecteren’ [Select All] te kiezen. Opgave 65.: Selecteren op een deel van de tekst � Kies nu bij de 'Kostensoort' voor ‘Tekstfilters – Begint met’. � Type in het dialoogkader in het tekstvak achter “begint met” de letter: a. � Klik op ‘OK’. � U ziet nu nog alleen de records, waarvan de kostensoort begint met de letter “a”. � Schakel het filter weer uit. De pijtjes achter de veldnamen zijn nu weer verdwenen. Verdiepingsopgave 66.: Selecteren met gebruik van wildcards In het ‘Aangepast Autofilter’ kunt u ook gebruik maken van zogenaamde 'Wildcards'. Hiermee worden het vraagteken (?) en de ster (*) bedoeld, die u kunt gebruiken voor één of meerdere onbekende tekens in de veldwaarden. � Zoek nu naar alle tekst die begint met: *premie De * staat voor een onbekend aantal tekens, dat dus voor het woord “premie” vooraf gaat. � Herstel de situatie door weer voor ‘(alles selecteren)’ [(Select all)] te kiezen. � Schakel het filter weer uit. De pijtjes achter de veldnamen zijn nu weer verdwenen.

Oefening 67.: Records selecteren met geavanceerd filter � Open het document “Autokosten-Uitgebreidfilter”. � Type op regel 2 onder de veldnaam 'Kostensoort' de veldwaarde “Brandstof”. � Klik in de lijst. � Kies voor 'Gegevens – Sorteren en filteren - Geavanceerd' [Data – Sort and Filter-Advanced]. Als uw cursor in de lijst stond, dan heeft Excel zelf de locatie van de lijst gevonden en ingevuld bij ‘Lijstbereik’. � Geef als Criteriumbereik een verwijzing op naar het gebied A2 t/m E3. � Laat de selectie van records uitvoeren door op ‘OK’ te klikken. � Controleer het resultaat. Oefening 68.: Zoeken met EN- en OF-criteria � Laat in het gebied met de criteria de zoekwaarde voor de kostensoort "Brandstof" staan. � Verander op regel 2 de veldnaam "Dollars" ook in "Bedrag".

CTS/Advies

19-12-14

118/187


� Geef als zoekwaarde voor het 'Bedrag' nu twee criteria op namelijk >55 en <60 door deze onder de twee veldnamen in het criteriumbereik te typen. � Laat het selecteren van records opnieuw uitvoeren. � Controleer het resultaat. � Plaats onder de veldnamen in het criteriumbereik op regel 4 de voorwaarden waar records aan moeten voldoen opdat de kostensoort 'Verzekeringspremie' is. � Kies weer voor het dialoogkader met het ‘Geavanceerd filter’. � Voeg aan het criteriumbereik een extra regel toe. Het Criteriumbereik wordt nu het gebied A2 t/m E4. � Voer de selectie van records nogmaals uit. U ziet dat nu beide typen records gevonden worden. Oefening 69.: Records ophalen � Plaats onder de lijst in twee cellen naast elkaar de veldnamen "Kostensoort" en "Bedrag". � Kies Gegevens – Sorteren en Filteren - Geavanceerd' [Data – Sort & Filter - Advanced]. � Klik op ‘Kopiëren naar andere locatie’ [Copy to another location] en vul achter ‘Kopiëren naar’ [Copy to] een verwijzing in naar beide cellen onder de lijst die de twee veldnamen bevatten. � Laat het maken van de selectie uitvoeren. U ziet dat in het aangegeven ophaalgebied alleen de kostensoorten en de bedragen vermeld worden. Oefening 70.: Unieke gegevens uit records ophalen � Haal nogmaals de selectie op naar het ophaalgebied, maar nu alleen de veldgegevens “Kostensoort” en “Rekeninggroep”. U ziet dat u meermalen dezelfde gegevens krijgt. � Voer het ophalen nogmaals uit maar klik nu in het dialoogkader voor het ‘Uitgebreid filter’ de optie ‘Alleen unieke records’ [Unique records Only] aan. � Bekijk het resultaat. Verdiepingsoefening 71.: Lijst met veldwaarden maken Door in het geheel geen criteria op te geven, worden alle records gevonden. Kies u vervolgens voor het ophaalgebied slechts één veldwaarde en klikt u de optie ‘Alleen unieke records’ aan, dan krijgt u een lijst met alle in dat veld voorkomende veldwaarden. � Wis in het criteriagebied alle voorwaarden waaraan veldwaarden moeten voldoen, zodat alle records worden gevonden. � Plaats onder de lijst in een cel alleen de veldnaam “Kostensoort”. � Neem het dialoogkader met het ‘Uitgebreid filter' voor u. � Benoem als ophaalgebied nu alleen de cel met de veldnaam "Kostensoort".

CTS/Advies

19-12-14

119/187


� Kies voor de optie ‘Alleen unieke records’ [Unique records only]. � Laat het aanmaken van het uittreksel uitvoeren. U krijgt nu een lijst met alle kostensoorten die in de database voorkomen.

Verdiepingsoefening 72.: Benoemde gebieden gebruiken Voor het maken van selecties kunt u ook gebruikmaken van verwijzingen naar gebieden die respectievelijk de namen “Database”, “Criteria” en “Ophalen” [Extract] hebben. � Geef in het bestand “Autokosten” de eerste 60 records, inclusief de rij met de veldnamen, de naam “Database”. � Selecteer het gebied met de criteria. U ziet dat Excel dit gebied automatisch de naam “Criteria” heeft gegeven. � Selecteer het gebied met de velnamen, waaronder de selectie moet worden aangemaakt. U ziet dat Excel dit gebied automatisch de naam “Ophalen” [Extract] heeft gegeven. � Neem het uitgebreid filter voor u, maar vul nu als referenties bovenstaande namen in.. � Voor de selectie uit. � Vergelijk het resultaat met de vorige keer. U vindt nu ook nog maar de helft van het aantal records omdat de database niet meer de hele lijst beslaat. � Past de criteria aan zodat alleen de regels met de kostensoort “Brandstof” worden gevonden. � Type in een cel onder de lijst de formule: =DBSOM(database,”Bedrag”,Criteria) [=DSUM(…)]. De functie sommeert de uitgaven zoals in de kolom “Bedrag” vermeld voor de regels waarop de kostensoort “Brandstof” is

Oefening 73.: Een draaitabel maken � Open het document “Verkiezingen. � Selecteer een cel in de lijst. � Kies ‘Invoegen – Tabellen - Draaitabel’ [Insert – Tables – Pivot table]. � Controleer in het dialoogkader dat verschijnt of de lijst juist geselecteerd is en dat de draaitabel op een nieuw werkblad wordt aangemaakt. � Klik ‘OK’. � Sleep de veldnaam “Partij” naar de bovenste regel van de tabel. � Sleep de veldnaam “Stad” naar de eerste kolom van de tabel. � Sleep de veldnaam “Stemmen/1000” naar het centrale deel van de tabel. De tabel is nu klaar. U kunt nog een derde dimensie aan de tabel toevoegen door nog een veldnaam naar de bovenste regel van de tabel te slepen. � Sleep de veldnaam “Verkiezing” naar de bovenste regel van het werkblad.

CTS/Advies

19-12-14

120/187


Oefening 74.: Een draaitabel opmaken U kunt een kolom selecteren door op de bovenste rand van de kolom te klikken. Vervolgens kunt u de kolom aan de rand verslepen. � Versleept de veldnamen van de partijen, zodat deze op volgorde van (politiek)links naar (politiek)rechts boven de tabel komen. � Selecteer de rechtse partijen en maak er een groep van door te keizen voor ‘Opties – Groeperen - Groepsselectie’ [Options – Group – Groupselection]. De opmaak van cellen verdwijnt weer als u de draaitabel opnieuw aanmaakt. Voor de opmaak van de getallen maakt u daarom gebruik van een menu-optie. � Kies ‘Opties – Actief veld – Veldinstellingen – Getalsnotatie’ [Options – Active Field – Field properties – Number] en kies voor een weergave van afgeronde getallen.

CTS/Advies

19-12-14

121/187

8. Koppelingen Een van de grote voordelen van Excel is de mogelijkheid om werkbladen onderling aan elkaar te koppelen. Hiermee kunt u een scheiding maken tussen: � Bladen waarop gegevens verzameld worden. � Berekenings- en uitvoerbladen.

©CTS/Advies

19-12-2014

Pag.nr.: 123/187

Koppelingen

8.1 Verwijzingen aanbrengen Een verwijzing naar een cel in een ander werkblad, maakt u eenvoudig door bij het intypen van een formule een cel in een ander werkblad aan te klikken. Bedenk dat nu de eerste klik het werkblad selecteert en de tweede klik de cel. Een externe verwijzing heeft de volgende syntax: =[documentnaam]werkbladnaam!celreferentie Let er wel op dat een verwijzing naar een cel, op hetzelfde blad als de formule, altijd standaard relatief wordt ingevuld, terwijl een verwijzing naar een cel op een ander blad, altijd standaard absoluut wordt ingevuld. � Open de documenten “Leden-2009” en “Leden-contributie”. � Kies ‘Beeld – Venster - Alle vensters - Verticaal’ [View – Window –Arrange All– Vertical] en klik ‘OK’. � Selecteer in het blad “Leden-contributie” de cel B3. � Type het =teken. � Klik in het zelfde blad op cel B2. � Wijzig de relatieve verwijzing naar rij-2 in een absolute verwijzing, door tweemaal op F4 te drukken. De verwijzing wordt: B$2 � Type het vermenigvuldig teken: * � Klik de map “Leden-2009” aan. De eerst klik selecteert alleen het blad, maar nog niet een cel. � Klik vervolgens binnen het blad op cel B3. � Maak de verwijzing relatief door driemaal op F4 te drukken. De formule luidt nu: =B$2*[Leden-2009.xls]Blad1!B3.

� Druk tot slot op ‘Enter’ Het resultaat is: 40.125,00 � Kopieer de formule naar alle andere cellen in de tabel, door de cellen B3 t/m C9 te selecteren, op F2 te drukken (activeert de cel met de formule) en vervolgens op ‘Control+Enter’ (formule opnieuw invoeren en kopiëren naar alle andere cellen van de selectie). U hebt nu een tabel met de contributieopbrengsten van alle steden gebaseerd op de ledenaantallen uit de ledenlijst 2009.

CTS/Advies

19-12-14

124/187

Koppelingen

8.1.1

Verwijzing naar namen aanbrengen

Bij het gebruik van verwijzing heeft het in het algemeen de voorkeur om celnamen te gebruiken. Dit maakt formules veel leesbaarder. De syntax van een formule die naar een naam op een ander werkblad verwijst, luidt: =[documentnaam]werkbladnaam!naam celreferentie � Wis op het blad “Leden-contributie” de cellen B3 t/m C9. � Geef op het blad “Leden-2009” de cel B3 de naam “Senioren_Amsterdam”. � Selecteer op het blad “Leden-contributie” cel B3. � Type in de cel: =B$2* � Klik het blad met de ledenaantallen aan. � Klik op cel B3. U ziet dat Excel een verwijzing invult: =B$2*’[Leden-2009.xls]Blad1’!Senioren_Amsterdam � Druk tot slot op ‘Enter’. De contributie wordt uitgerekend voor het aantal ‘Senioren’ in de afdeling ‘Amsterdam’. U kunt nu iedere cel waarnaar u wilt verwijzen een eigen naam geven, maar dit is erg bewerkelijk. Het is sneller om alle cellen met ledenaantallen één naam te geven. Kopieert u de formule naar andere cellen in het werkblad, dan wordt voor iedere te berekenen waarde een cel uit het naamgebied gebruikt dat op de zelfde regel en de zelfde kolom staat. De ledenaantallen moeten dan dus wel op dezelfde plaats op het werkblad blijven staan. � Geef op het blad “Leden-2009” de cellen met B3 t/m C9 de naam “Ledenaantallen”. � Selecteer op het blad “Leden-contributie” de cellen B3 tot en met C9 en wis de inhoud. � Laat de cellen geselecteerd staan. � Plaats in cel B3de formule: =B$2*[Leden2009.xls]Blad1!Ledenaantallen � Druk tot slot op ‘Control+Enter’. De contributietarieven worden vermenigvuldig met de “Ledenaantallen” � Versleep op het blad me de ledenaantallen de tabel één regel naar beneden. U ziet dat de formules niet meer kloppen omdat de ledenaantallen nu op de verkeerde positie staan. � Sleep de tabel met ledenaantallen weer terug naar de oorspronkelijke positie. Maakt u voor de berekening van de contributies gebruik van een matrixformule, dan wordt voor ieder te berekenen antwoord één

CTS/Advies

19-12-14

125/187

Koppelingen

waarde uit de getallenmatrix gebruikt. Het maakt daarbij dan niet uit waar de matrix met ledenaantallen op het werkblad staat. � Selecteer op het blad “Leden-contributie” de cellen B3 tot en met C9 en wis de inhoud. � Laat de cellen geselecteerd staan. � Plaats in cel B3de formule: =B2:C2*[Leden2009.xls]Blad1!Ledenaantallen � Druk tot slot op ‘Shift+Control+Enter’. De contributietarieven worden vermenigvuldig met de “Ledenaantallen” � Versleep op het blad me de ledenaantallen de tabel één regel naar beneden. U ziet dat de formule nu nog steeds correct worden berekend. � Sleep de tabel met ledenaantallen weer terug naar de oorspronkelijke positie.

CTS/Advies

19-12-14

126/187

Koppelingen

8.2 Wijziging van het werkblad waarnaar wordt verwezen Indien u over een werkblad beschikt met verwijzingen naar een werkblad met basisgegevens, dan kunt u op eenvoudige wijze deze verwijzingen veranderen in verwijzingen naar een ander blad met dezelfde soort gegevens. � Open het blad “Leden-2010”. (Alle afdelingen hebben in 2010 ongeveer tweemaal zoveel leden behalve de afdeling Delft. Die is samengegaan met de afdeling Rotterdam. De penningmeester wil snel een nieuwe inschatting kunnen maken van de contributieopbrengsten in 2010). � Geef op dit werkblad de cellen met de ledenaantallen B3 t/m C9 eveneens de naam “Ledenaantallen”. � Sluit het document en bewaar de wijziging. � Activeer het blad dat de contributie berekend. � Kies de opdracht 'Gegevens – Verbindingen – Kopp. Bewerken' [Data – Connections – Edit Links]. � Selecteer in het dialoogkader de verwijzing die u wilt wijzigen. (Leden-2009.xls).

� Kies de optie 'Bron wijzigen' [Change Source]. � Selecteer in het menu dat getoond wordt het werkblad met gegevens waarnaar u de verwijzingen wilt wijzigen. (het blad “Leden2010”) � Klik 'OK' en sluit het dialoogkader ‘Koppelingen bewerken’ [Edit Links]. Op het werkblad worden alle formules met verwijzingen naar het eerste gegevensblad gewijzigd in formules met verwijzingen naar het nieuwe gegevensblad. � Sluit en bewaar alle mappen.

CTS/Advies

19-12-14

127/187

Koppelingen

8.3 Het openen van gekoppelde werkbladen U kunt een werkblad, dat verwijzingen naar andere werkbladen bevat, openen zonder dat de werkbladen met brongegevens eveneens worden geopend. De formules worden dan toch correct berekend. Feitelijk worden de brongegevens onzichtbaar bij het doelbestand opgeslagen. Nadat gekoppelde bestanden op schijf zijn opgeslagen, kunnen er echter veranderingen zijn aangebracht in de werkbladen met brongegevens. Bij het openen van gekoppelde bestanden zult u dus moeten aangeven of de formules met verwijzingen opnieuw berekend moeten worden. � Open het bestand “Leden-contributie”. Boven aan het scherm verschijnt een beveiligingswaarschuwing die u vertelt dat de automatische bijwerking van koppelingen is uitgeschakeld. � Klik 'Inhoud inschakelen’ [Enable Content]. Indien u deze optie klikt dan zal Excel proberen zo veel mogelijk verwijzingen naar niet geopende werkbladen te herberekenen. Het dialoogkader 'Gegevens – Verbindingen – Kopp. Bewerken' [Data – Links - Edit Links] kent tevens een optie om selectief verwijzingen naar niet geopende bestanden bij te werken 'Waarden bijwerken' [Update Values]. Worden zowel bron- als doelbestand, beiden naar eenzelfde andere map verplaatst, dan blijft de koppeling behouden. Wordt alleen het bronbestand op schijf verplaatst, dan wordt, indien u de gegevens wilt bijwerken, een foutmelding gegeven en verschijnt er een dialoogkader waarin u opnieuw de locatie van het bronbestand moet aangeven.

� Sluit alle bestanden.

CTS/Advies

19-12-14

128/187

Koppelingen

8.4 Excel als database Gegevens in een tabel noemt men in Excel een lijst. Indien gegevens in meerdere bij elkaar behorende lijsten (ofwel tabellen) zijn opgenomen, dan spreekt men van een database. Om te kunnen werken met de gegevens in meerdere lijsten, moeten in de lijsten codes of nummers voorkomen, waarmee in de eene lijst naar bijbehorende gegevens uit een andere lijst wordt verwezen. Het veldgegeven dat het record uniek maakt noemt men de ‘Sleutel’ [Key] het veldgegeven waarmee verwezen wordt naar de ‘Sleutel’ [Key] in een andere lijst noemt men de ‘Verwijzende sleutel’ [Foreign key]. Onderstaand is dit in een heel eenvoudig voorbeeld weergegeven

In de namenlijst vormen de namen de ‘Sleutel’ [Key] en de nummers van de vestigingen de ‘Verwijzende sleutel' [Foreign key]. Deze vormt dan weer op zijn beurt de sleutel in de lijst met vestigingsplaatsen. Om de gegevens uit de verschillende lijsten op te halen en het rapport weer te geven kunt u gebruik maken van geneste ‘Zoekfuncties’ [Lookup functions]. De zoekwaarde van de functie is dan het resultaat van een geneste zoekfuncties die op basis van een ‘Sleutel’ de ‘Verwijzende sleutel’ uit een andere lijst ophaalt.

CTS/Advies

19-12-14

129/187

8.5 Koppelingen: oefeningen Oefening 75.: Verwijzingen intypen � Open het document "Glasoven". In dit document worden fictief de kosten berekend die gemaakt moeten worden voor de productie van een glazen fles uit een glasoven. Boven in het document (Regel 3 t/m 6) staan de uitgangspunten voor de berekeningen die er onder plaatsvinden (Regel 10 t/m 19). � Open de map "Oven1". Het bedrijf beschikt over meerdere glasovens, die ieder een ander kostenplaatje hebben. In het document “Oven1” staan fictief de gegevens van glasoven nummer 1. � Plaats beide werkbladen onder elkaar op het scherm. Zorg er hierbij voor, dat van beide werkbladen minimaal de bovenste 6 regels zichtbaar zijn in de vensters. Men wil op het werkblad met de naam "Glasoven" voor de berekening gebruik maken van de gegevens die op het blad met brongegevens "Oven1" staan. � Wis op het werkblad "Glasoven" de cellen met basisgegevens die u van het werkblad "Oven1" kunt overnemen (B3:B6;D3:D5;F3:F5). � Selecteer de cel voor het invoeren van het aantal "Flessen per maand" (B3). � Type een =-teken en klik vervolgens met de muis op het document “Oven1” om dit te activeren, en klik daarna op de cel B1, om hier een verwijzing naar te maken. In cel B3 van het document “Glasoven” verschijnt de formule: =[Oven1.xls]Blad1!$B$1. � Klik 'OK' of druk op 'Enter'. U ziet dat de verwijzing naar de cel door Excel wordt ingevoerd en berekend. � Voeg een lege regel in boven de eerste regel van het werkblad "Oven1". � Bekijk de zojuist aangebrachte verwijzingsformule op het werkblad "Glasoven". U ziet dat de verwijzingen automatisch worden aangepast. � Verwijder de ingevoegde regel weer en zie hoe de formules zich weer herstellen. � Breng op dezelfde manier verwijzingen aan naar de cellen B2 t/m B4. � Bewaar uw document.

©CTS/Advies

19-12-2014

Pag.nr.: 131/187

Koppelingen - oefeningen

Oefening 76.: Verwijzing naar meerder cellen tegelijk � Activeer het werkblad "Glasoven" uit voorgaande oefening. � Selecteer de cellen D3 t/m D5 en F3 t/m F5. Dit is een meervoudige selectie. Deze maakt u door bij het aanklikken de Control-toets ingedrukt te maken. Uw invoercel is nu waarschijnlijk cel F3. � Typ een =teken. � Klik vervolgens met de muis in het werkblad "Oven1", de cel aan waarnaar de formule in de invoercel moet gaan verwijzen. Zorg dat de cellen geselecteerd blijven. Indien u per ongeluk verkeerd klikt, waardoor de selectie komt te vervallen, dan moet u de opgave in zijn geheel opnieuw doen. � Maak de verwijzing relatief. Dit doet u met de functietoets-F4. � Druk op de toetscombinatie ‘Control+Enter’. In alle cellen van de selectie wordt nu een formule ingevoerd, die overeenkomt met de formule die u in de invoercel hebt ingetypt. � Bewaar uw document. Oefening 77.: Koppeling plakken � Wis op het werkblad "Glasoven" de cellen met basisgegevens die u van het werkblad "Oven1" kunt overnemen (B3:B6;D3:D5;F3:F5). � Selecteer op het werkblad "Oven1" de eerste kolom met gegevens (B1:B4). � Kopieer de cellen. � Activeer het werkblad "Glasoven". � Selecteer de cellen B3 t/m B6. � Plak met "Start – Klembord - Plakken – Koppeling Plakken" [Home - Clipboard - Paste - Paste Link] de gegevens in. U ziet dat door Excel de externe verwijzingen worden aangebracht. � Bewaar uw document. Oefening 78.: Verwijzing m.b.v. matrixformule � Selecteer op het werkblad "Glasoven" D3 t/m D5. � Typ een =teken. � Sleep vervolgens met de muis in het werkblad "Oven1", over de overeenkomstige cellen. Zorg dat de cellen geselecteerd blijven. Indien u per ongeluk verkeerd klikt, waardoor de selectie komt te vervallen, dan moet u de opgave in zijn geheel opnieuw doen. � Druk op ‘Shift+Control+Enter’. In de geselecteerde cellen wordt een matrixformule ingevuld. � Maak een zelfde verwijzing voor de cellen F3 t/m F5.

CTS/Advies

19-12-14

132/187


� Bewaar uw document. Oefening 79.: Koppelingen wijzigen Op schijf staan 3 bestanden met respectievelijk de gegevens van oven 1, oven 2, en oven 3. � Activeer het document "Glasoven". � Kies de opdracht 'Gegevens – Verbindingen – Kopp. bewerken' [Data – Connections – Edit Links]. � Selecteer in de lijst het werkblad "Oven1". � Kies 'Bron wijzigen' [Change Source]. � Selecteer de map "Oven3." op schijf waarnaar u de koppeling wilt wijzigen en druk op 'OK'. � Sluit het dialoogkader. � Bekijk de formules met verwijzingen in het werkblad 'Glasoven". U ziet dat in de formules nu naar "Oven3" wordt verwezen. � Breng na elkaar een koppeling aan tussen het werkblad “Glasoven” en de bladen “Oven2” en “Oven3”. Bepaal welke oven de goedkoopste fles zal kunnen produceren. Oefening 80.: Koppelingen bijwerken � Koppel de gegevens van “Glasoven” aan die uit het bestand “Oven1”. � Bewaar en sluit het document "Glasoven". � Wijzig een aantal cijfers in "Oven1" en bewaar ook dit bestand opnieuw op schijf. � Open het document "Glasoven". Op de bovenste regel verschijnt een ‘Beveiligingswaarschuwing’ dat een bestand gekoppeld is. � Klik op ‘Inhoud inschakelen’[Update]. U ziet dat de wijzigingen uit het gekoppelde bestand ook in "Glasoven" worden doorgevoerd. Opgave 81.: Zoekfuncties met externe verwijzing � Open het document “Werkuren”. � Plaats in B13 een zoekfunctie die het aantal gewerkte uren berekend van de persoon, waarvan de naam op B11 wordt ingevuld. � Open het document “Locaties’. � Plaats beiden documenten naast elkaar op het scherm � Open een leeg document en plaats dat onder beide andere documenten op het scherm. � Knip de cellen B11 t/m B13 van het werkblad “Werkuren” en plak deze in het nieuwe werkblad.

CTS/Advies

19-12-14

133/187


� Bekijk de formule. U ziet dat de verwijzing naar de tabel nu een externe verwijzing is geworden.( U moet wel knippen en niet kopiëren.) � Plaats op het nieuwe document een formule die op basis van de naam van de persoon het ‘Locatienummer’ uit de laatste kolom van de tabel met gewerkte uren ophaalt. � Plaats tot slot op het nieuwe document een formule die op basis van het ‘locatienummer’ de ‘locatieplaats’ uit de lijst met locatiegegevens haalt. Opgave 82.: Gegevens ophalen uit externe database � Open het document “Autokosten”. � Open een nieuw leeg document. � Maak op dit nieuwe blad een ‘Criteriumgebied’ dat op de eerste regel de veldnamen bevat en daaronder de waarden waar de velden aan moeten voldoen. Geef het gebied de naam “Criteria”. � Plaats op dit nieuwe blad naast elkaar de veldnamen “Kostensoort“ en “Bedrag” en geef deze twee cellen de naam “Ophalen”. � Kies de optie ‘Formules – Gedefinieerde namen – Naam definiëren – Naam defniëren’ [Formulas – Defined Names - Define Name – Define Name] en definieer de naam “database” die verwijst naar de lijst met autokosten op het andere blad. � Selecteer op het blad een lege cel. � Maak gebruik van het ‘Uitgebreid filter’ [Advances filter] om uit de lijst met autokosten de records te halen die voldoen aan de gegeven criteria. De verwijzing naar de lijst met autokosten is een externe verwijzing.

Opgave 83.: Tabel met databasefunctie � Wis in bovenstaand voorbeeld alle waarden waar de criteria aan moeten voldoen.

CTS/Advies

19-12-14

134/187


� Benoem alleen de cel B4 (de cel met de veldnaam “Kostensoort”) als het gebied met de naam “ophalen” � Haal opnieuw alle records op die voldoen aan de gegeven criteria. Klik daarbij de optie “alleen unieke records” aan. Omdat u geen criteria opgeeft worden alle records gevonden. Omdat alleen ‘unieke records’ worden weergeven krijgt u een lijstje met daarin de kostensoorten die in de lijst voorkomen. � Plaats in cel B4 de functie: =DBSOM(database;”Bedrag”,criteria) [=DSUM(…)] Deze functie berekent alle uitgaven in de lijst met autokosten. � Maak een eenzijdige tabel waarin voor alle verschillende kostensoorten de uitgaven daaraan berekend worden.

CTS/Advies

19-12-14

135/187

9. Macro’s Een ‘macro’ is een programma dat een aantal instructies automatisch uitvoert. Er zijn twee soorten macro’s. In de eerste plaats zijn er opdrachten-macro’s. Dit is een macro die een aantal handelingen in Excel geautomatiseerd uitvoert, zoals u deze ook zelf handmatig had kunnen uitvoeren. Daarnaast zijn er 'functie-macro's'. Dit zijn macro’s die een door u zelf gemaakte functie berekenen en het antwoord, in de cel die de functie bevat, weergeven. Bij Excel worden al standaard honderden functies meegeleverd. U zult dus niet snel behoefte hebben om zelf functies te gaan maken. In deze module behandelen wij dan ook alleen de 'opdrachten-macro’s'.

©CTS/Advies

19-12-2014

Pag.nr.: 137/187

Macro's

9.1 Macro’s opnemen Excel beschikt over een ingebouwde recorder, waarmee u uw handelingen in een opdrachten-macro kunt vastleggen. Op deze manier stelt Excel de macro voor u samen. � Sluit alle mappen die u eventueel nog open heeft staan en open een nieuwe map. � Start het opnemen van de macro met de optie 'Ontwikkelaars – Programmacode - Macro opnemen' [Developer – Code - RecordMacro]. Op het scherm verschijnt een dialoogvenster, waarmee u de macro een naam kunt geven en waarin u een eventuele toetscombinatie kunt opgeven om de macro later mee te starten. � Geef de macro de naam “Adres”. � Type voor de ‘Sneltoets’ de toetscombinatie ‘ [Shortcut key] de combinatie Ctrl+a’. � Klik op 'OK'. Vanaf dit moment worden al uw handelingen vastgelegd. Onderaan het scherm op de statusbalk ziet u een knop verschijnen waarmee de opname weer gestopt kan worden. � Typ onder elkaar op het werkblad uw naam, adres en woonplaats. � Stop het opnemen door op het knopje te klikken dat op de statusbalk verschenen is.

CTS/Advies

19-12-14

138/187

Macro's

9.2 Een macro uitvoeren � Wis uw scherm. � Plaats u invoercel ergens midden in het blad. � Kies 'Ontwikkelaars – Programmacode – Macro’s’ [Developers Code – Macros]. U ziet een dialoogvenster verschijnen waarin u de macro die u wilt starten selecteert. � Klik de macro “Adres”. � Klik op ‘Uitvoeren’ [Run]. � Bekijk het resultaat. Het resultaat zal misschien niet naar verwachting zijn. De recorder legt de uitgevoerde handelingen precies zo vast als u ze hebt uitgevoerd. Indien u niet begint met het selecteren van een bepaalde cel op een bepaald werkblad, dan doet de macro dit ook niet en zal de uitvoer beginnen op de plaats waar invoercel op dat moment stond. U zal echter dikwijls de macro willen laten starten bij de actieve cell van dat moment. Hiervoor moet u de macro ‘Relatief’ recorden.

CTS/Advies

19-12-14

139/187

Macro's

9.3 Absolute en relatieve registratie U kunt voorafgaand of tijdens het vastleggen van de macro tussen absolute en relatieve registratie wisselen. � Wis uw scherm. � Start opnieuw de opname van een macro met de naam “Adres” en de letter voor de toetscombinatie wordt nu de letter “b”. en klik ‘OK’. Als Excel vraagt of u de voorgaande macro van die naam wilt overschrijven, dan antwoord u met ‘Ja’ [Yes]. � Kies nu, voordat u uw adres intypt, de opdracht ‘Ontwikkelaars – Programmacode - Relatieve verwijzingen gebruiken’ [Developer – Code – Use Relative-References]. � Type wederom onder elkaar uw naam, adres en woonplaats. � Schakel de opname uit. � Wis uw scherm en selecteer een andere cel als invoercel. � Test de macro uit door op de toetscombinatie ‘Control+b’ te drukken. U ziet dat de macro nu wordt uitgevoerd op de plaats waar u uw invoercel had staan.

CTS/Advies

19-12-14

140/187

Macro's

9.4 Het venster van de VBA-editor Wilt u de registratie van de macro bekijken of wijzigen dan moet u de Visual Basic Editor starten. � Kies ‘Ontwikkelaars – Programmacode – Macro’s’ [Developers Code - Macro’s]. � Selecteer in de lijst de macro die u wilt gaan bewerken. In dit voorbeeld de macro “Adres”. � Klik de knop ‘Bewerken’ [Edit]. Op het scherm verschijnt het scherm van de Visual Basic Editor.

Links in het venster vindt u bovenaan de ‘Projectverkenner’. De verzameling van alle programmacode die bij het document behoort, noemt men het project. De verschillende stukken VBA-programmacode horen altijd bij een onderdeel (object) van het document. In de projectverkenner ziet u deze in een boomstructuur weergegeven. Op het hoogste niveua ziet u. � Excel-objecten : zoals de werkmap en de werkbladen. � Formulieren: waarmee gegevens kunnen worden ingevoerd. � Modulen: bladen met programmacode die niet bij een specifiek deel object behoren. � Klassenmodules: bladen met programmacode die bij een zelf gedefinieerd object behoren. Onder de projectverkenner ziet u een venster met de eigenschappen van het object dat u geselecteerd heeft. Bij deze meeste objecten is dat alleen de naam. Boven het venster met de programmacode ziet u twee keuzelijsten, die bedoeld zijn om snel door de programmacode te navigeren. In de linker kiest u een eventueel deelobject van een object dat u in de

CTS/Advies

19-12-14

141/187

Macro's

projectverkenner geselecteerd hebt (bijvoorbeeld een knop op een formulier) of de grope ‘Algemeen’. In de rechter keuzelijst kiest u een specifieke subroutine die bij dat (deel)object behoort. De programmacode die wij ‘recorden’ hoort niet bij een specifiek object en wordt op de algemene modulenbladen vastgelegd.

CTS/Advies

19-12-14

142/187

Macro's

9.5 Macro’s wijzigen Modulebladen kunt u als een gewone tekst bewerken. U kunt regels aan een opgenomen macro toevoegen, wijzigen of verwijderen. U kunt ook een geheel nieuwe macro op het blad intypen, maar het is handiger om eerst zoveel mogelijk te ‘recorden’. Iedere macro begint met de instructie: � Sub macronaam() en eindigt met de instructie: � End Sub U kunt regels aan een opgenomen macro toevoegen, wijzigen of verwijderen. U kunt ook zelf een macro op het blad intypen. Bovenaan de macro ziet u in een andere kleur een aantal regels met commentaar. Deze regels worden voorafgegaan door een accentteken. Hierdoor weet Excel dat deze regels geen instructies bevatten die moeten worden uitgevoerd. Op dezelfde wijze kunt u ook zelf regels met commentaar toevoegen. Dit commentaar kan ook achter de instructies geplaatst worden, zolang de tekst hiervan maar begint met een aanhalingsteken. Iedere nieuwe macro die u opneemt plaatst Excel onder de voorgaande macro. Bij het definiëren van een macro heeft u de macro gekoppeld aan een toetscombinatie. Deze toetscombinatie kunt u ook later wijzigen � Kies in het werkblad de optie ‘Ontwikkelaars – Programmacode Macro’s’ [Developer – Code – Macros]. � Selecteer de macro “Adres”. � Klik op de knop ‘Opties…’ [Options]. � Geef als ‘Sneltoets’ [Shortcut key] de toetsencombinatie ‘Control+a’ mee. � Klik ‘OK’. � Sluit he venster door op ‘Annuleren’ [Cancel] te klikken.

CTS/Advies

19-12-14

143/187

Macro's

9.6 Starting a macro Er zijn verschillende manieren om een macro uit te laten voeren. � Met de optie hiervoor in het Lint. � Met een toetscombinatie. � Met een zelfgemaakte knop in het Lint. � Met een knop in de knopenbalk ‘Snelle toegang � Met een willekeurig object in het werkblad � Automatisch startende macro’s 9.6.1

Macro starten met knop in het Lint.

Om een knop in het Lint te kunnen plaatsen moet u eerst onder een tab een nieuwe groep aanmaken. � Klik met de secundaire muisknop in het Lint. � Kies de optie ‘Het Lint Aanpassen’ [Customize the Ribbon].

� Selecteer in de rechter kolom de groep ‘Bewerken’ [Edit] in de tab ‘Start’ [Home] en klik op de knop ‘Nieuwe groep’ [New Group]. � Klik met de rechter muisknop op de nieuwe groep om hem de naam “Eigen macro’s” te geven. � Selecteer in de keuzenlijst boven de linker kolom de optie “Macro’s” en kies uit de lijst een door uzelf gemaakte macro’s. � Klik op de knop ‘Toevoegen’ [Add] om deze aan de nieuw gemaakte groep toe te voegen.

CTS/Advies

19-12-14

144/187

Macro's

� Klik op ‘Ok’ en bekijk in het Lint het resultaat. � Probeer de knop uit. In het snelemnu van de knop vindt u een optie om deze naar de werkbalk ‘Snelle toegang’ [Quick Access] te verplaatsen. 9.6.2 Macro koppelen aan willekeurig object In feite kunt u een macro starten met ieder willekeurig object in het werkblad. � Klik de optie ‘Invoegen – Illustratie - Vorm’ [Insert – Illustration Shape] . � Teken een cirkel op het werkblad. � Klik met de secundaire muisknop op het object en kies in het snelmenu ‘Macro toewijzen’[Assign Macro]. � Wijs een macro toe aan het object. � Klik vervolgens een maal buiten het object om de selectie er van uit te schakelen en klik vervolgens op het object om de macro uit te proberen. 9.6.3 Automatisch startende macro’s Tot slot hebben wij de automatisch startende macro’s. Dit zijn macro’s die de naam “Auto_Open” hebben. Zodra het werkblad geopend wordt, start de macro. Deze macro’s vormen wel een beveiligingsrisico. � Type onderstaande macro op het moduleblad.

� Bewaar het doucment als een ‘Excel werkmap met macro’s’ [Macro enabled workbook]. � Kies voor ‘Onwikkelaars – Programmacode – Macrobeveiliging – Alle macro’s inschakelen’ [Developpers – Code – Macro Securtiy – Enable all macros]. � Open het document weer. U ziet dat de macro wordt uitgevoerd. U kunt de uitvoering van de Auto_Open macro voorkomen door bij het openen van een document de ‘Shift-toets’ ingedrukt te houden. � Sluit en open het document nogmaals, maar nu zonder de macro te laten uitvoeren.

CTS/Advies

19-12-14

145/187

Macro's

9.7 Invoervensters U kunt in een macro ook instructies typen die zich niet laten opnemen. U kunt bijvoorbeeld instructies opnemen die een dialoogvenster doen verschijnen waarin u een tekst of een getal typt die vervolgens bij de uitvoering van het programma gebruikt wordt. Een voorbeeld hiervan is de instructie � Inputbox(Aanwijzing) De”Aanwijzing” is een aanwijzing m.b.t. hetgeen moet worden ingevuld. � Selecteer het blad “Module 1” in de Visual Basic-editor. � Vervang de instructie die uw naam invult door onderstaande regel: ActiveCell.FormulaR1C1 = Inputbox (“Geef naam op”) � Probeer de macro opnieuw uit. Type uw naam in, als de macro daarom vraagt. Aan de functie ‘InputBox’ kunt u na de ‘Aanwijzing’ [Prompt] nog enkele extra argumenten meegeven. � InputBox(Aanwijzing [,Titel] [,Standaard] [, Hor. Pos] [,Vert. Pos] .] [,Helpbestand] [,Context]) De argumenten worden onderling door een komma van elkaar gescheiden. (De extra argumenten staan tussen rechte haken om aan te geven dat deze facultatief zijn.) Argument Aanwijzing [Prompt] Titel [Title] Standaard [Default] Hor. pos. [XPos] Vert. pos. [YPos] [Helpfile] [Context]

Beschrijving Toelichting die in het invoervenster verschijnt. De tekst die in de titelbalk van het invoervenster verschijnt. De standaardwaarde of tekst in het invoervenster. De afstand van het dialoogvenster in 1/567 cm vanaf de linkerkant van het scherm. Als u niets invult dan wordt het venster horizontaal gecentreerd. De afstand van het dialoogvenster in 1/567 cm vanaf de bovenkant van het scherm aangeeft. Als u niets invult dan wordt het venster verticaal gecentreerd. Naam van eventueel bestand met helpgegevens. Nummer van de contextafhankelijke hulptekst.

� Wijzig de instructie met de ‘InputBox’ zoals hier onder weergegeven: �

InputBox(“Geef naam op”, “Naam invullen”, “Jansen”, 1440, 1440)

� Test de macro opnieuw uit. � Verdubbel het opgegeven aantal pixels en bekijk opnieuw het effect.

CTS/Advies

19-12-14

146/187

Macro's

9.8 Regels afbreken Indien de instructieregel te lang wordt, dan kunt u deze met een spatie en een streep ( _) afbreken en op de volgende regel voortzetten. � Wijzig de instructie met de instructie ‘InputBox’ zoals hier onder weergegeven: �

InputBox(“Geef naam op”; _ “Naam invullen”; _ “Jansen”; 2880; 2880)

� Probeer de macro opnieuw uit.

CTS/Advies

19-12-14

147/187

Macro's

9.9 Foutafhandeling Als u een instructie typt die Excel niet herkent, dan wordt de instructieregel rood weergegeven. � Verwijder het liggend streepje uit voorgaande instructie: Invoervenster(“Geef naam op”; “Naam invullen”; “Jansen”; 300; 200) [Inputbox(…)] U krijgt een foutmelding zodra u de cursor naar een andere regel probeert te verplaatsen. � Negeer de foutmelding en probeer de macro toch uit te voeren. U krijgt een foutmelding en de laatste regel die kon worden uitgevoerd wordt in de Visual Basic Editor geel weergegeven. � Corrigeer de fout in de instructie � Klik in de werkbalk van de Visual Basic Editor op de knop ‘Beginwaarden’ [Reset]. Het kan ook zijn dat de instructieregels, wat betreft de syntaxis, wel correct zijn maar het programma toch niet doen wat u verwacht. Indien u voor een instructieregel in de linker kantlijn klikt dan wordt daar een ‘stop’ geplaatst. U kunt vanaf dat punt de instructieregels één voor één laten uitvoeren door op functietoets F8 te drukken. Dit totdat u de regel gevonden heeft die tot een niet-correcte stap leidt. U kunt in uw programma ook instructies op nemen die automatisch uitgevoerd worden als er een fout is ontstaan * * * * * *

Sub … On Error Goto foutafhandeling Instructies… Exit Sub Foutafhandeling: Instructies… Resume Next

*

End Sub

Begin programma Verwijzing naar begin afhandelingsinstructies als fout mocht voorkomen. Programma-instructies Instructie om subroutine te verlaten als alle instructies goed zijn uitgevoerd Naam van regel waaronder foutafandeling instructies staan Instructie voor foutafhandeling Instructie om na foutafhandeling het programma voort te zetten waar het gebleven was Eind programma

CTS/Advies

19-12-14

148/187

Macro's

9.10 Modulair programmeren Indien u uitgebreide macro’s maakt, dan kunt u snel het overzicht verliezen. Het heeft de voorkeur een macro op te bouwen uit modulen. Deze kunnen afzonderlijk getest worden en kunnen vervolgens zo nodig ook op meerdere plaatsen in het programma gebruikt worden. Er zijn 3 mogelijke instructies om een andere routine aan te roepen: �

Routinenaam argument1, argument 2

�

Call Routinenaam(argument1,argument 2)

�

Toepassing.Starten Macro:="documentnaam!routinenaam" [Application.Run Macro:=…]

Deze laatste instructie ziet u ook op het macroblad verschijnen wanneer u tijdens het opnemen van een macro een andere macro start. Deze gebruikt u als de naam van de routine van te voren nog niet vast staat. Zorg er wel voor dat u het Excel-document dat de macro bevat op schijf bewaard hebt en dus al een naam hebt gegeven. Indien u later de naam van het document wijzigt dan worden de documentnamen van de macro’s niet automatisch aangepast in het programma. � Bewaar uw werkblad onder de naam “Testmacro”. � Selecteer in het werkblad de cel met uw naam. � Start de opname van een nieuwe macro met de naam “Opmaak”. � Leg in deze nieuwe macro vast hoe u , met behulp van het dialoogkader voor de opmaak, voor uw naam de lettergrootte 14 punts en de weergave ‘vet’ instelt. � Start de opnamen van een nieuwe macro. Deze krijgt de naam “Adres_opgemaakt” en voor de toetscombinatie de letter “b”. � Start, terwijl de opnamen loopt, eerst de macro die uw naam en adres in het werkblad plaatst. � Selecteer in het werkblad de cellen met uw naam en uw adres. � Start de macro die de cellen van opmaak voorziet. � Beëindig de opname van de macro. � Test de macro door op de toestcombinatie ‘Control+b’ te klikken. � Bekijk de macro in de Visual Basic Editor.

CTS/Advies

19-12-14

149/187

Macro's

9.11 VBA Syntax 9.11.1 Instructie Een regel in de code wordt een ‘instructie’ genoemd. Lees de instructie van rechts naar links. Bijvoorbeeld de instructie: � ActiveCell.Offset (5,1).Range(“A1”).Select Kan u lezen als “Selecteer een cel in de linker bovenhoek van gebied, dat 5 naar beneden en 1 naar rechts ligt t.o.v. de actieve cel” 9.11.2 Procedure Een procedure is een blok instructies, welke altijd als een geheel worden uitgevoerd. Een procedure begint en eindigt altijd met: Sub procedurename () ….. End Sub

of

Sub functienaam () ….. End Function

9.11.3 Objecten Het eerste woord in een instructie beschrijft het “object” waarop de instructie betrekking heft. Dit kan bijvoorbeeld zijn een: � cel � gebied � werkblad � werkboek � applicatie In de instructie: � ActiveCell.FormulaR1C1 = “week 1” is het object de actieve cel. Een object kan deel van een verzameling van objecten zijn, zoals: � werkbladen � grafieken � werkboeken In de instructie: � Sheets(“Budget”).Select is het object het werkblad met de naam “Budget” uit de verzameling werkbladen [sheets]. In de instructie: � Sheets(2).Select is het object het tweede werkblad uit de verzameling werkbladen [sheets].

CTS/Advies

19-12-14

150/187

Macro's

9.11.4 Eigenschappen [Properties] Objecten hebben eigenschappen. In de instructie wordt de eigenschap door een punt van het object gescheiden. In de instructie: � ActiveCell.FormulaR1C1 = “jan” is het hebben van een formule “R1K1Formule” met de waarde “jan” een eigenschap van de “Active cell”. Met behulp van het =-teken wordt een waarde aan een eigenschap toegekend. In bovenstaand voorbeeld is dit de tekst “Jan”. De waarden van eigenschappen kunnen zijn: � Tekst � Getal � Variabele � Constante Voorbeelden: Instructie ActiveCell.FormulaR1C1 = “week 1” Selection.Font.FontStyle = “Bold” UserForm1.Check_Box.Value = True

Application.WindowState = xlNormal

Toegekende waarde De formule in R1C1 stijl krijgt de tekstwaarde “week 1”. "Bold" wordt de opmaak van het 'Font' De checkbox op het formulier krijgt de booleanwaarde ‘Waar’ en wordt ‘aan’ gezet. De venster status wordt ingesteld op de standaard Excel waarde ‘xlnormal’

U kunt meerdere eigenschappen van een object tegelijk een waarde toekennen. � Bekijk de opgenomen instructie die u naam van een opmaak voorziet De geregistreerde instructies zien er als onderstaand uit:

Met bovenstaande instructies worden aan het object “Selection.Font” meerdere eigenschappen tegelijk ingesteld, zonder dat het object telkens herhaald dient te worden.

CTS/Advies

19-12-14

151/187

Macro's

9.11.5 Methoden [Methods] Objecten kunnen ook onderwerp zijn van een handeling. Dit wordt een ‘Methode’ [Method] genoemd. Methode Selection.Copy ActiveSheet.Paste

Handeling Kopieert een selectie Plak de inhoud van het klembord op het actieve werkblad.

Methoden kunnen één of meer ‘argumenten’ hebben. � Neem een macro op met de naam “Plakken_speciaal” waarin de bedrijfsnaam, maar niet de opmaak, wordt gekopieerd en in een andere cel wordt geplakt. De instructie luidt als volgt: � Selection.PasteSpecial Paste:=xlPasteFormulas, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Om te begrijpen wat de verschillende argumenten betekenen kunt u het beste naar het dialoogkader ‘Plakken speciaal’ [Paste Special] kijken. De argumenten volgen in feite de opties in het dialoogkader. 9.11.6 Functies In de VBA-code kunt u functies opnemen die een waarde teruggegeven. Msgbox() en InputBox() zijn voorbeelden van VBA-functies. Ook kunt u vele werkbladfuncties opnemen. Om te weten welke beschikbaar zijn typt u onderstaand instructie:

Na het typen van de punt, verschijnt een lijst met alle werkbladfuncties die u in de VBA-code kunt opnemen.

CTS/Advies

19-12-14

152/187

Macro's

9.12 De keuze en de herhaling Zoals eerder opgemerkt kunnen niet alle instructies met de recorder opgenomen worden. Keuzes die gemaakt moeten worden afhankelijk van een waarde, moeten handmatig geprogrammeerd worden 9.12.1 De keuze Om de voortgang van een programma, afhankelijk te maken van de waarde van een variabele gebruikt u de instructies �

If als vergelijking = WAAR Then … instructies Else …. Instructies End If.

� Type onderstaand voorbeeld in de Visual Basic editor. Het is daarbij een goed gebruik om de instructies tussen de condities te laten inspringen. U springt in naar rechts door op de ‘Tab-toets’ te drukken en u springt weer naar links door op ‘Shift-Tab’ te drukken.

� Probeer de macro uit op een cel met een positief getal en een cel met een negatief getal. 9.12.2 Meerdere keuzemogelijkheden Om de voortgang van een programma afhankelijk te maken van meerdere mogelijke waarden van een variabele gebruikt u de instructies: �

If als vergelijking1 = WAAR Then … instructies …… ElseIf als vergelijking2 = WAAR Then …. . instructies…. Else …. . instructies…. End If.

� Wijzig voorgaand voorbeeld, zoals onderstaand.

CTS/Advies

19-12-14

153/187

Macro's

� Probeer de macro uit op een positief en een negatief getal en het getal ‘0’. 9.12.3 Herhaling voor een vast aantal Om een instructie te herhalen, gebruikt u de instructie. �

For variabele = startwaarde To eindwaarde [Step stap] ....instructies…. Next.

Hier staat variabele voor een variabele die start met de waarde ‘startwaarde’ en vervolgens telkens na de instructie ‘Next’ met 1 verhoogd wordt tot de variabele de waarde ‘eindwaarde’ bereikt. � Type onderstaand voorbeeld over.

� Probeer het voorbeeld uit. In het werkblad verschijnen onder elkaar de getallen 1 1/m 10. De lus kan eventueel voortijdig worden verlaten met de instructie: � Exit For 9.12.4 Herhaling tot een conditie verandert. Om een instructie te herhalen tot een bepaalde waarde wordt bereikt, gebruikt u de instructies: ‘Do While’ en ‘Loop’ of ‘Do Until’ en ‘Loop’. �

Do While vergelijking = WAAR ....instructies…. Loop.

� Type onderstaand voorbeeld over.

� Probeer het voorbeeld uit. In het werkblad verschijnen onder elkaar de getallen 1 t/m 4.

CTS/Advies

19-12-14

154/187

Macro's

De loop kan eventueel voortijdig worden verlaten met de instructie: �

Exit Do

9.12.5 Herhaling voor een aantal gevallen. U kan een instructie ook voor een aantal specifieke gevallen laten uitvoeren �

Select Case variabelenaam Case variabelenaam = A, B, C, … instructies Case variabelenaam = X, Y, Z… instructies Case Else instructies End Select

� Probeer onderstaand voorbeeld uit:

9.12.6 Herhaling voor elke item uit een verzameling Het voordeel van werken met een ‘Verzameling [Collectie] is dat men voor elk geval van de verzameling instructies kan laten uitvoeren, zonder dat vooraf bekend is hoeveel gevallen er in de verameling zullen voorkomen. � For Each variabelenaam in Collectie instructies Next variabelenaam � Probeer onderstaand voorbeeld uit.

� Controleer tot slot of alle werkbladen ‘op slot’ zitten.

CTS/Advies

19-12-14

155/187

Macro's

9.13 Mededelingenvenster Om tijdens de uitvoering van een programma een mededeling te laten verschijnen, gebruikt u de instructie. �

MsgBox(Aanwijzing [, Opmaak] [, Titel] [, xpos] [, ypos])

Zie nevenstaand voorbeeld. Afhankelijk van het antwoord dat men aanklikt, genereert de functie een getal. Om dit getal aan een variabele te kunnen toekennen moet u de argumenten van de functie tussen haken plaatsen. Argument Aanwijzing [Prompt] Opmaak [Format]

Beschrijving Mededeling die in het dialoogkader verschijnt.

Titel [Title] [Helpfile] [Context]

Tekst in de titelbalk van het dialoogkader

Een getal dat staat voor het totaal van de getallen, die ieder een opmaakkenmerk vertegenwoordigen. In plaats van een getal kunt u op deze positie beter een van de Visual Basic constanten invullen, die staan voor de verschillende getallen. Zie hiervoor het nakomend overzicht.

Naam van eventueel bestand met helpgegevens. Nummer van de contextafhankelijke hulptekst.

Afhankelijk van welke knop u klikt, wordt door de MsgBx een waarde teruggegeven, die u kunt gebruiken om te bepalen hoe het programma verder moet verlopen.

CTS/Advies

19-12-14

156/187

Macro's

� Type onderstaand voorbeeld over.

� Probeer het voorbeeld uit.

CTS/Advies

19-12-14

157/187

Macro's

9.14 Variabelen en constanten 9.14.1 Variabelen Visual Basic kent een variabele vanaf het moment dat u er een waarde aan toekent. Dit geldt echter alleen binnen de routine waarin de variabele benoemt wordt. Voorbeelden: � n=1 � MijnWeekdag = “Maandag” � MijnDatum = #08 december 2010# � MijnTijd = #9:15# �

MijnGeboortedatum = #26/2/1953 9:15#

Een variabele behoudt zijn waarde binnen de routine waarin hij gedeclareerd wordt. Wilt u de waarde van een variabele ook binnen andere subroutines op het moduleblad kunnen gebruiken dan moet u de variabelen op de eerste regels van het moduleblad declareren, met de instructie: �

Dim variabelenaam AS type

Hierbij staat ‘type’ voor: Type Byte Boolean Integer Long Single

Double

Currency

Date String * lengte

Variant Object

CTS/Advies

Waarde Geheel getal tussen 0 en 255 True(-1) or False (0) Geheel getal tussen -32.768 en 32.767 Geheel getal tussen -2.147.483.647 en 2.147.483.647 Decimaal getal tussen -3.402823E38 en 1.401298E-45 (negatief) en tussen 1.401298E45 en 3.402823E38 (positief) Decimaal getal tussen 1.79769313486231E308 en 4.94065645841247E-324 (negatief) en tussen 4.94065645841247E-324 en 4.79769313486232E308 (positief) Getal met maximaal 4 cijfers achter de komma tussen -922,337,203,685,477.5808 en 922,337,203,685,477.5807 Datum van 1-1-0100 tot 31-12-9999 of tijd van 0:00:00 tot 23:59:59 Tekst met een specifieke lengte. Wordt de toevoeging* lengte weggelaten dan is de lengte maximaal 65.536 tekens. Iedere mogelijke waarde zoals in deze tabel vermeld. Een variabele kan gedefinieerd zijn als een object, zoals bijvoorbeeld een “Worksheet” Naar welk werkblad wordt verwezen, wordt in het programma bepaald door de instructie: Set variabelenaam = Worksheets(n)

19-12-14

Geheugen 2 byte 2 bytes 4 bytes 4 bytes

8 bytes

8 bytes

8 bytes 1 byte per teken 8 bytes

158/187

Macro's

Op deze manier legt u dus vooraf vast hoeveel geheugenruimte voor de variabele gereserveerd moet gaan worden. U kunt een variabele ook gebruiken om naar een object te verwijzen. U verwijst bijvoorbeeld met de variable “Mijnwerkblad” naar het werkblad van het werkbooek met de naam “Omzet” d.m.v de instructie � Mijnwerkblad = worksheets(“Omzet”) U kunt het type van het object vooraf vastleggen met bijvoorbeeld de declaratie � Dim Mijnwerkblad As Worksheet Wilt u de variabele op verschillende modulepagina’s kunnen toepassen dan gebruikt u de instructie: �

Public variabelenaam

Om te voorkomen dat u een variabele gebruikt die niet bestaat, gebruikt u boven aan de module de instructie: �

Option Explicit

9.14.2 Arrays Een variabele kan standaard slechts 1 waarde bevatten. Plaats u bij de declaratie tussen haakjes een getal achter een variabele, dan kan u een overeenkomstig aantal waarden+1 aan de variabele toekennen. Voorbeeld:

U kunt ook een array met meerdere dimensies declareren. Wilt u voor de zomdermaanden juli t/m augustus voor iedere dag de hoogste en de laagste temperatuur opslaan. Dan zou u de volgende array moeten declareren. � Dim zomertemperatuur(6 tot 8, 1) As decimal Wilt u liever dat uw arrays bij 1 beginnen met tellen, dan plaats u in het declaratiegedeelt van uw moduleblad de instructie: � Option Base = 1 Bovenstaande instructie luidt dan: � Dim zomertemperatuur(7 tot 8, 2) As decimal

CTS/Advies

19-12-14

159/187

Macro's

9.14.3 Constanten Visual Basic kent vele ‘constanten’. Dit zijn variabelen met een voorgedefinieerde waarde. Zij worden als argumenten in functies gebruikt. Er wordt onderscheid gemaakt tussen Visual Basic-constanten en Excel-constanten, afhankelijk van of de constanten voor alle varianten van Visual Basic geldt of alleen voor de Excel variant van Visual Basic. U herkent de verschillende soorten constanten doordat de naam wordt voorafgegaan door de letters “vb” of “xl”. Voorbeelden: �

Application.WindowState = xlNormal

Deze instructie geeft het venster zijn standaard Excel-instelling. �

MsgBox “Let op”, vbCritial

Deze instructie doet in de Visual Basic-functie “Msgbox” het pictogram “Stop” verschijnen.

U kunt ook zelf constanten benoemen. U gebruikt hiervoor de instructie: �

Const constantenaam As type = waarde

CTS/Advies

19-12-14

160/187

Macro's

9.15 Dialogbox ontwerpen U kunt ook uw eigen dialoogboxen ontwerpen. � Neem het venster van de Visual Basic Editor voor u. � Kies de menu-optie ‘Invoegen – Userform’ [Insert – Userform] U krijgt nu een tekenbord op het scherm, waarmee u een dialoogbox kan ontwerpen.

Selecteer uit de ‘Werkset’[Toolbox] van elk object een voorbeeld en zet dat op het tekenbord. Object

Figure

Function ‘Bijschrift’ [Label] Om een tekst op het formulier te plaatsen ‘Tekstvak’ [TextBox] voor getal of tekst. ‘Opdrachtknop’ [CommandButton] om een macro te starten. ‘Selectievakje’ [Checkbox] om een ‘ja’ of ‘nee’ optie te kiezen. ‘Keuzerondje’ [OptionButton] Om een keuze te maken uit een aantal alternatieven ‘Groepvak ‘[Frame] Dat een groep elkaar uitsluitende opties omhult. U plaatst eerst het groepsvak op het tekenbord en plaatst daarna de elkaar uitsluitende keuzerondjes binnen het groepsvak.

CTS/Advies

19-12-14

161/187

Macro's

Keuzelijst [Listbox]: Om een waarde uit een lijst te kunnen kiezen.

Keuzelijst met invoervak [Combobox]: om een waarde uit een lijst te kunnen kiezen of om zelf een waarde in te vullen. ‘Wisselknop’ [Toggle button] knop geeft de waarde true of false en wordt al dan niet ingedrukt weergegeven ‘Schuifbalk’ [ScrollBar] Genereerd waarde van 0 tot standaard 32767 ‘Kringveld’ [SpinButton] Genereert een waarde 0 tot standaard maximaal 100 ‘Afbeelding’ [Picture] Geeft afbeelding weer die u bij de eigenschap ‘Picture’ opgeeft. Onder deze knop vindt u een lijst met extra objecten die u op een formulier kan plaatsen. Object-eigenschappen instellen

Van ieder object dat u op het tekenbord plaatst, moet u een aantal eigenschappen instellen. Dit zijn bijvoorbeeld Eigenschap Name tekst Caption tekst Height nummer Width nummer Left nummer Top nummer Maxlength nummer Passwordchar teken

Min nummer

Max nummer

Columncount nummer

CTS/Advies

Functie Naam waarmee het object binnen het programma wordt aangeduid. Tekst die verschijn op object Hoogte van object Breedt van object Afstand vanaf linkerrand van formulier Afstand vanaf bovenrand van formulier Maximaal aantal tekens dat u kunt invoeren Tekens die u typt worden vervangen door het opgegeven teken. Vult u geen teken in dan wordt de getypte tekst zelf weergegeven. Geeft de minimale waarde van ‘Schuifbalk’ [ScrollBar] of ‘Kringveld’ [SpinButton]. Geeft de maximale waarde van ‘Schuifbalk’ [ScrollBar] of ‘Kringveld’ [SpinButton]. Het aantal kolommen dat in een ‘Keuzelijst’ wordt weergeven. 1=standaard. Vult u -1 in dan worden alle kolommen (tot maximaal 10) weergegeven.

19-12-14

162/187

Macro's

Columnheads true/false ColumnWidth nummer;nummer;.. Style var

PictureMode .var

PictureTilling true/false Tabstop true/false

TabIndex nummer

Visible true/false Locked true/false MultiLine true/false Wordwrap true/false

Bepaald of de eerste rij uit de selectie als kolomkoppen worden weergegeven. Breedte van de kolommen in punten. Waarde 0 maakt de kolom onzichtbaar. Bepaalt of de keuze in de “Keuzenlijst met invoervak [ComboBox] wordt beperkt tot de lijst zelf. Bepaalt of afbeelding gedeeltelijk of proportioneel vergroot of verkleind in object wordt weerggegeven. Bepaald of de afbeelding net zo lang herhaald wodt, totdat deze het hele object gevuld heeft. Als u op de tabtoets drukt worden de objecten waarbij Tabstop true is, op volgorde geselecteerd en waarbij Tabstop false is, over geslagen. Volgorde waarin object geselecteerd wordt als op de Tabtoets gedrukt wordt. Bepaald of het object zichtbaar is op formulier Object is wel zichtbaar maar kan niet worden gewijzigd Ingevoerde tekst kan over meerdere regels verdeeld worden Tekst die te lang is wordt automatisch afgebroken.

De eigenschappen worden ingesteld met het ‘Eigenschappenvenster’. � Kies de menu-optie ‘Beeld – Venster Eigenschappen’ [View Properties Window] of kilik op de hiernaast getoonde knop en controleer of dit venster op het scherm wordt weergegeven. � Plaats een ‘Bijschrift’ [Label] op het tekenbord en type daarin de tekst “Voorkeursbestemming” In het eigenschappenvenster ziet u dat de eigenschap ‘Caption’ de waarde “Voorkeursbestemming” krijgt. Let er wel op dat het venster de ‘Eigenschappen’ toont van het object dat u aangeklikt heeft. � Bekijk de waarde van de eigenschap ‘Width’ van het object “Bijschrift”. � Wijzig de breedte van het object “Bijschrift”,. zodat de inhoud goed te lezen is. In het eigenschappenvenster ziet u dat de eigenschap ‘Width’ gewijzigd wordt. � Plaats achter het bijschrift een ‘Tekstvak’ [Text Box] om een land in te vullen.

CTS/Advies

19-12-14

163/187

Macro's

� Klik op de knop ‘Sub/Userform uitvoeren’ [Run Sub/\userform] om het resultaat te kunnen bekijken. � Klik op het sluitkruisje om het dialoogkader weer te sluiten. Het is handiger om een land uit een lijst te kunnen selecteren. Dat voorkomt typefouten. � Type in het werkblad in drie cellen onder elkaar de landen: “Frankrijk”, “Spanje” en “Italië”. � Vervang het tekstvak voor een ‘Keuzelijst’ [List Box]. � Plaats bij de eigenschap ‘RowSource’ van de keuzelijst een verwijzing naar de cellen die de landnamen bevatten. � Klik op de knop ‘Sub/Userform uitvoeren’ [Run Su/Userform] om het resultaat te kunnen bekijken. 9.15.1 Het formulier weergeven Om het formulier te laten weergeven gebruikt u de instructie: �

Formuliernaam.Show

Om het formulier te laten verbergen gebruikt u de instructie: �

Formulieernaam.Hide

U kunt ook eerst de instructies �

Formuliernaam.Load’

en �

Formuliernaam.Unload’

gebruiken om het formulier eerst in het geheugen te laten opslaan. Hierdoor kan de instructies ‘Show’ sneller worden uitgevoerd. � Neem Module1 voor u, door er in het projectoverzicht op te dubbelklikken. � Neem onderstaand voorbeeld over.

De standaardnamen ‘Userform1’ en ‘Combobox’ kunt u uiteraard bij de eigenschappen wijzigen. U kunt deze macro nog niet uitproberen. Het dialoogkader dat verschijnt moet ook nog netjes gesloten worden.

CTS/Advies

19-12-14

164/187

Macro's

9.16 Een gebeurtenisroutine opnemen. � Neem de userform1 weer voor u. Wanneer u op een object in een formulier dubbelklikt, dan start u de ‘Visual Basic-editor’ en verschijnt op het moduleblad een subroutine die gekoppeld wordt aan de ‘Gebeurtenis’ [Event] die u zojuist uitvoerde. Hierin kunt u een macro typen die zal worden uitgevoerd wanneer, afhankelijk van het type object, het object wordt aangeklikt of van waarde verandert. � Plaats op het formulier een knop met de tekst ‘OK’. � Dubbelklik op de ‘OK-button’ De Visual Basic Editor opent een moduleblad met daarop de openings- en sluit-instructies voor een subroutine die automatisch start bij het klikken van de commandbutton. De subroutine heet ‘CommandButton1_Click()’ � Plaats in de gebeurtenismacro die verschijnt de instructie: Userform1.hide � Probeet de macro opnieuw uit en klik nu, na keuze van een land, op de ‘OK’ knop. Bij alle objecten kunnen vele gebeurtenissenprocedures worden geschreven. Werkboeken, werkbladen en celbereiken hebben gebeurtnisprocedures bij: maken, openen, sluiten, selecteren, klikken, dubbelklikken…etc. � Dubbelklik in de projectverkenner op een object om het bijbehorende moduleblad te openen. � Selecteer in de linker keuzelijst, boven de programmacode, het object. � Bekijk in de rechter keuzelijst boven de programmacode de gebeurtenissen die bij het object horen. � Selecteer één van de gebeurtenissen. In het modulenblad ziet u de ‘Sub’ en ‘End Sub’ instructies voor de ‘gebeurtenis’ [Event] verschijnen.

CTS/Advies

19-12-14

165/187

Macro's

9.17 Functiemacro’s Functiemacro’s zijn zelfgemaakte functies die een waarde doen terugkeren in de cel waarin zij berekend worden. Als voorbeeld maken wij een functie die de waarde inclusief 19% BTW berekend van een andere waarde. � Start de ‘Visual Basic Editor. � Type op het moduleblad onderstaand voorbeeld.

� Plaas in cel A1 van een werkblad het getal 100. � Type in de cel er naast de functie: =Inclusief(A1) U krijgt als antwoord de waarde 119. U kunt de functiemacro net zoals opdrachtenmacro’s ook interactief maken. � Wijzig de functie zoals in onderstaand voorbeeld.

� Neem het werkblad weer voor u en druk op functietoets F9 om dit te laten herberekenen. De functie vraagt nu om het BTW percentage en rekent vervolgens de functie opnieuw uit.

CTS/Advies

19-12-14

166/187

Macro's

9.18 Diverse Instructies Eigenschap Interactie met gebruiker: Inputbox Msgbox Voorwaardelijke instructies: If woorwaarde Then instructies… End If For var=beginwaarde To eindwaarde instructies… Next For Each var In naamverzameling instructies… Next Do While voorwaarde instructies… Loop Do Until voorwaarde instructies… Loop Exit For/Do/Sub/ Sprong in proccedure uitvoer: procedurenaam arg.1, .. Call procedurenaam(arg.1, ..) .run procedurenaam(arg.1, ..) Goto regelnaam On Error Goto regelnaam/0 .count Diverse objecten ActiveCell Cells(rijnummer, kolomnummer) Range(celbereik) Selection ThisWorkbook ActiveWorkbook Diverse verzamelingen Sheets Worksheets Eigenchappen van ‘Range .Currentregion

Functie Dialoogkader om gegevens in te voeren Dialoogkader met melding. Instructies worden uitgevoerd als voorwaarde TRUE is

Instructies worden uitgevoerd zolang de variabele een waarde tussen begin- en eindwaarde heeft.s Instructies worden uitgevoerd voor elk item uit verzameling.

Instructies worden uitgevoerd zolang aan voorwaarde wordt voldaan. Instructies worden uitgevoerd zolang niet aan voorwaarde wordt voldaan. Programma verlaat For- of D0-instructie of subroutine Roept procedure aan idem iedm Programma wordt voortgezet bij regelnaam Bij fout wordt programma voortgezet bij regelnaam of stopt Geeft aantal bladen in een verzameling. Actieve cel binnen geselecteerd celbereik Cellen binnen celbereik Opgegeven celbereik. Geselecteerd celbereik of grafiek Werkboek met programmacode Werkboek dat geselecteerd is. Alles werbladen, grafiekbladen, 4.0 macro bladen Alle werkbladen Gebied met gevulde cellen waarvan active cel deel van uitmaakt.

Eigenschappen van werkbladen .DisplayDrawingObjects = xlDisplayShapes/xlPlaceHolders/xlHide .DisplayAlerts=true/false .Saved=true/false

Toont op werkblad alle objecten of hun ‘placeholders’ of geen objecten. Schakelt de weergave van foutmeldingen aan of uit Zet de eigenschap ‘saved’ op ‘true’ als u bij sluiten van werkboek niet wil bewaren. Bewaard gewijzigde gekoppelde gegevens bij werkboek. Schakelt de weergave op het scherm aan of uit Geeft tekst op statusbalk weer Bewaardeen kopie van een werkbook, bijvoorbeeld als backup.

.SaveLInkValues .Screeunupdating=true/false .Statusbar=tekst .SaveCopyAs

CTS/Advies

19-12-14

167/187

Macro's

Eigenschappen van formulieren .show .hide .load

Formulier wordt weergegeven Formulier wordt verborgen Laad formulier in computergeheugen. Hierdoor kan het sneller worden weergegeven bij de instructie formuliernaam.show Verwijders formulier uit computergeheugen

.unload Diversen methoden .End(xlUp/xlDown/xlToRight/xlToLeft)

Laatste gevulde cel naar beneden/boven/rechts/links t.o.v. actieve cel. Selecteerd celbereik Programma wacht tot tijdstip voordat het wordt voortgezet Herberekend alle cellen in het object werkboek, werkblad of celbereik. Herberekend alle cellen in alle werkboeken

.select .Wait (tijdstip) .Calculate CalculateFull

CTS/Advies

19-12-14

168/187

9.19 Macro’s: oefeningen Oefening 84.: Macro relatief opnemen � Open een nieuwe map. � Plaats uw invoercel ergens in de linker-bovenhoek van het blad (bijvoorbeeld in B2). � Start het opnemen van een macro met de optie 'Ontwikkelaars – Programmacode - Macro opnemen' [Developper – Code - Record Macro] Op het scherm verschijnt een dialoogvenster waarmee u de macro een naam kunt geven. � Geef macro de naam "Weekdagen" en de toetscombinatie ‘Control+d’ � Klik op 'OK'. Onderaan het scherm ziet u op de titelbalk een knop verschijnen waarmee de opname weer gestopt kan worden. � Controleer of de optie ‘Ontwikkelaars – Programmacode - Relatieververwijzingen’ [Developer – Code – Use Relative References] aan staat. De knop wordt dan weergegeven met een oranje kader er omheen. � Typ op het werkblad de zeven namen van de week in cellen onder elkaar. (Zie onderstaand voorbeeld).

Let er wel op dat u na het intypen van de laatste dag van de week ook weer op ‘Enter’ gedrukt hebt. De macro kan niet gestopt worden zolang de formulebalk nog actief is. � Stop het opnemen door op de knop ‘Opname stoppen’ [Stop Recording] te klikken.. Oefening 85.: Macro starten � Wis uw scherm. � Plaats de invoercel ergens midden in het blad. � Kies 'Ontwikkelaars – Programmacode – Macro’s' [Developper – Code – Macros]. U ziet een dialoogvenster verschijnen waarin u de macro die u wilt starten kunt selecteren.

©CTS/Advies

19-12-2014

Pag.nr.: 169/187

Macro’s - Oefeningen

� Klik de macro “weekdagen” aan. � Klik op ‘Uitvoeren’ [Run]. � Bekijk het resultaat. Oefening 86.: Macro aan knop toewijzen. � Klik met de secundaire muisknop op het Lint. � Kies de optie ‘Het Lint Aanpassen’ [Customize the Ribbon]. � Maak een nieuwe groep onder de tab ‘Start’ [Home] en plaats daar een knop in die de macro “Adres” doet starten. � Gebruik de knop ‘Naam wijzigen’ [Rename] om zowel de naam van de knop al s het pictogram er van te wijzigen. � Probeer de knop uit.

Oefening 87.: Macro aan willekeurig object. � Klik de optie ‘Invoegen – Illustratie - Illustratie’ [Insert – Illustration - Illustration] . � Kies het plaatje van een koe. � Klik met de secundaire muisknop op het object en kies in het snelmenu ‘Macro toewijzen’[Assign Macro]. � Klik op de knop ‘Nieuw’ [New] � Schrijf een macro die de koe “boeeee” laat zeggen. � Klik vervolgens een maal buiten het object om de selectie er van uit te schakelen en klik vervolgens op het object om de macro uit te proberen. Oefening 88.: Automatisch startende macro. � Geef de macro die de koe “boeee” laat zeggen de naam “Auto_Open”

CTS/Advies

19-12-14

170/187


� Bewaar het doucment als een ‘Excel werkmap met macro’s’ [Macro enabled workbook]. � Kies voor ‘Onwikkelaars – Programmacode – Macrobeveiliging – Alle macro’s inschakelen’ [Developpers – Code – Macro Securtiy – Enable all macros]. � Open het document weer. U ziet dat de macro wordt uitgevoerd. � Sluit en open het document nogmaals, maar nu met ingedrukte ‘Shift-toets’. Oefening 89.: Macro wijzigen � Kies ‘Ontwikkelaars – Programmacode - Visual Basic’ [Developer – Code – Visual Basic]. � Bekijk hoe de macro hier is vastgelegd. � Corrigeer eventuele typefouten in de macro “Weekdagen” door de correctie direct in de betreffende instructies aan te brengen. � Verwijder uit de macro de instructies die de namen ‘Zaterdag’ en ‘Zondag’ op het werkblad plaatsen. � Selecteer opnieuw het eerste werkblad uit de map en laat de macro opnieuw uitvoeren. � Bewaar uw map onder de naam “VB_voorbeeld” Oefening 90.: De functie ‘InputBox’ � Neem de ‘Visual Basic Editor’ weer voor u. � Selecteer het blad “Module 1”. � Voeg bovenaan de macro “Weekdagen” twee lege nieuwe regels toe. � Plaats op de eerste nieuwe regel de instructie: ActiveCell.FormulaR1C1 = InputBox (“Geef naam op”) � Plaats op de tweede nieuwe regel een instructie die de invoercel twee cellen naar beneden verplaatst. U macro ziet er nu als volgt uit:

� Probeer de macro opnieuw uit.

CTS/Advies

19-12-14

171/187


Oefening 91. Modulair programmeren � Neem een tweede macro op met de naam “Weken” en de toestsombinatie ‘Control-w’, die horizontaal naast elkaar de teksten “Week1” t/m “Week 4” in het werkblad plaatst. U doet dit door tijdens het opnemen eerst de tekst “Week 1” in een cel te typen en vervolgens het selectieknopje drie cellen naar rechts te slepen.De opnamen moet weer relatief zijn. Vervolgens moet u de twee macro’s samenvoegen tot een macro die onderstaande tabel maakt. � Wis uw werkblad. � Neem een derde macro op met de naam “Tabel”, die eerst de macro “Weekdagen” uitvoert en daarna de macro “Weken”. (zie onderstaand voorbeeld van het eindresultaat)

De weekdagen vormen de eerste kolom van de tabel en de weken staan op de bovenste regel. Nadat de macro “Weekdagen” is uitgevoerd moet u de invoercel dus verplaatsen. Nadat de weken zijn ingetypt moet de invoercel verplaatst worden naar de eerste cel die moet worden ingevuld. � Zie voor de antwoorden achter in deze handleiding. Oefening 92.: Wijzigen van toetscombinatie � Kies ‘Ontwikkelaars – Programmacode - Macro’s’ [Developper – Code – Macros]. � Selecteer de macro ‘Tabel’. � Klik op de knop ‘Opties’ [Options]. � Geef als letter de ‘t’ op. � Klik op ‘OK’. � Sluit het dialoogvenster. � Start de macro met de toetsencombinatie ‘Control-t’. Oefening 93.: De herhaling Vervolgens moet de tabel ingevuld worden. � Wijzig de macro zodanig dat deze om het gewerkte aantal uren vraagt. Vervolgens dit aantal in de invoercel plaatst en tot slot de invoercel 1 cel naar beneden verplaatst. � Wijzig vervolgens de macro zodat dit 5 keer wordt uitgevoerd. U doet dit met de instructies: For n = 1 To 5

CTS/Advies

19-12-14

172/187


ActiveCell.FormulaR1C1 = Inputbox(“Voer aantal uren in”) Next Extra oefening 94.: De herhaling � Breidt voorgaande macro zo uit, dat het invullen van het aantal gewerkte uren bij de dagen, voor ieder van de 4 weken herhaald wordt. Bedenk dat na het invullen van een week, de invoercel weer vijf naar boven en 1 cel naar rechts moet worden verplaatst. Zie voor een mogelijke oplossing de antwoorden achter in deze handleiding. Oefening 95.: De conditionele herhaling Wij willen de macro nu een test laten uitvoeren om te kijken of het ingevoerde aantal uren wel kan kloppen. � Laat het aantal uur dat in de InputBox getypt wordt eerst toekennen aan de variabele met de naam “Uren”. � Plaats de instructie, die naar de invoer van het aantal uren vraagt tussen de While …Wend instructies zodat niet verder wordt gegaan voordat een aantal uren wordt ingevuld dat groter dan nul en kleiner dan 8 is. Denk er aan dat de variabele “Uren” wel eerst een initiele waarde moet krijgen. Oefening 96.: De keuze � Plaats na de instructie waarbij men de uren heeft ingevuld de instructie die - Indien het aantal gewerkte uren minder dan 2 is vraag of men niet te weinig tijd aan het project besteedt. - Indien het aantal gewerkte uren meer dan 6 is, vraagt of men niet teveel tijd aan het project besteedt. Oefening 97.: Variabelen en constanten � Declareer de variabele “uren” aan het begin van het moduleblad als het type Integer. Oefening 98.: Dialoogvenster ontwerpen � Kies de menu-optie ‘Invoegen – Userform’ [Insert- Userform] � Plaats op het formulier een ‘Bijschrijft’ [Label] met de tekst “Uren?” en daar achter een keuzelijst waarmee men tussen 1 tot 8 uur kan kiezen. � Plaats tot slot op het formulier een ‘Opdrachtknop’ [Commandbutton] met de tekst ‘OK’ en een ‘Opdrachtknop’ [Commandbutton] met de tekst ‘Annuleren’. � Dubbelklik op de ‘OK’ knop en neem in de aktie-procedure de instructie ‘Userform1.hide’ op om het formulier van het scherm te verwijderen. � Gebruik de instructie ‘Userform1.show’ om het formulier in het programma te laten weergeven.

CTS/Advies

19-12-14

173/187


� Gebruik de instructie: uren=Userform1.ComboBox1.value om de ingevulde waarde aan de variabele uren toe te kennen. Oefening 99.: Functiemacro’s � Start de ‘Visual Basic Editor’. � Type op het moduleblad onderstaand voorbeeld.

� Plaas in cel A1 van een werkblad uw brutomaandloon. � Type in de cel er naast de functie: =nettomaandloon(A1) De werkelijke berekening van uw nettomaandloon is waarschijnlijk ingewikkelder en de uitkomst zal waaarschijnlijk niet helemaal kloppen. Bovenstaand voorbeeld geeft echter wel een indicatie wat u met functiemacro’s kan doen. � Wijzig de functie zoals in onderstaand voorbeeld.

� Neem het werkblad weer voor u en druk op functietoets F9 om dit te laten herberekenen. De functie vraagt nu om de ww- loonbelastingpremie en rekent vervolgens de functie opnieuw uit. U kunt de functiemacro ook zo maken dat op basis van het maandloon, de loonbelasting in een tabel wordt opgezocht.

CTS/Advies

19-12-14

174/187

10. ANTWOORDEN

©CTS/Advies

19-12-2014

Pag.nr.: 175/187

Antwoorden

10.1 Herhalingsoefeningen: Factuur F11 G11 H11 C19

Totaal/Schetsontwerp BTW/Schetsontwerp Inclusief/Schetsontwerp Totaal/Totaal

=C11*D11 =F11*B$6 =F11+G11 =SOM(C11:C16) [=SUM(C11:C16]

Formule met absolute en relatieve verwijzingen.

CTS/Advies

19-12-14

176/187

Antwoorden

10.2 Functies Oefening: Tekstfuncties

Verdiepingsopgave: Datum-/Alsfuncties

Statistische funcies

CTS/Advies

19-12-14

177/187

Antwoorden

10.3 Lijsten Oefening: Gegevensvalidatie

CTS/Advies

19-12-14

178/187

Antwoorden

10.4 Visual Basic Modulair programmeren

Herhaling

Conditionele herhaling

CTS/Advies

19-12-14

179/187

Antwoorden

Keuze

Dialoogvenster

CTS/Advies

19-12-14

180/187

11. Evaluatieformulier Gegevens cursus: Cursusnummer:

............................................................................

Cursusnaam:

............................................................................

Naam docent:

............................................................................

Datum:

............................................................................

Gegevens voor deelname certificaat: Naam cursist zoals te vermelden op certificaat

dhr./mw ..............................................................

Geboortedatum

……………………………………..………………

Geboorteplaats

……………………………………..………………

Evaluatie Omcirkel het door u gewenste antwoord en geef daaronder eventueel een toelichting. Vergeet niet de achterkant van dit formulier ook in te vullen. Wat is uw oordeel over de inhoud van de cursus? slecht

matig

voldoende

goed

uitstekend

goed

uitstekend

Commentaar: Wat is uw oordeel over het gebruikte lesmateriaal? slecht

matig

voldoende

Commentaar: Wat is uw oordeel over de didactische vaardigheden en presentatie van de docent? slecht

matig

voldoende

goed

uitstekend

Commentaar: Vervolg op volgende pagina

©CTS/Advies

19-12-2014

Pag.nr.: 181/187

Evaluatieformulier

Wat vond u van de organisatie van de cursus? slecht

matig

voldoende

goed

uitstekend

goed

uitstekend

Commentaar:

Wat vond u van de sfeer tijdens de cursus? slecht

matig

voldoende

Commentaar:

Wat is uw eindcijfer voor de gehele cursus?.

1

1,5

2

2,5

3

3,5

4

4,5

5

5,5

6

6,5

7

7,5

8

8,5

9

9,5

10

Overige suggesties?

CTS/Advies

19-12-14

182/187

12. Aantekeningen

©CTS/Advies

19-12-2014

Pag.nr.: 183/187

Aantekeningen

CTS/Advies

19-12-14

184/187

Aantekeningen

CTS/Advies

19-12-14

185/187

Aantekeningen

CTS/Advies

19-12-14

186/187

Aantekeningen

CTS/Advies

19-12-14

187/187

Excel gevorderd. Deze handleiding werd geschreven door: Charles Scheublin - (Kopiëren niet toegestaan)

Recommend Documents