Deel SOFTWIJS. Microsoft Windows en Office serie. Excel 2000 Gevorderd

7 Deel

SOFTWIJS

Microsoft Windows en Office serie

Excel 2000 Gevorderd

E X C E L

2 0 0 0

G E V O R D E R D

MICROSOFT WINDOWS EN OFFICE SERIE

Excel 2000 Gevorderd De door Softwijs uitgegeven serie Microsoft Windows en Office bestaat uit de volgende delen: Deel 1

Windows 95/98 Basis

Deel 2

Word 2000 Basis

Deel 3

Excel 2000 Basis

Deel 4

Powerpoint 2000 Basis

Deel 5

Access 2000 Basis

Deel 6

Excel Opgaven

Deel 7

Excel 2000 Gevorderd

Deel 8

Access 2000 Gevorderd

Deel 9

Word 2000 Gevorderd

 Softwijs Julianastraat 17 • 7161 CP Neede Tel. 0545-295813 • Fax 0545-294073 E-mail: [email protected] Niets uit deze uitgave mag worden verveelvoudigd en/of openbaar gemaakt door middel van druk, fotokopie, microfilm, elektronisch of op welke andere wijze ook zonder voorafgaande schriftelijke toestemming van Softwijs.

© Softwijs, oktober 2001

pag. i

E X C E L

2 0 0 0

G E V O R D E R D

Inhoudsopgave 1. Draaitabellen........................................................................................1 1.1 1.2 1.3 1.4 1.5 1.6 1.7

Wat is een draaitabelrapport?........................................................... 1 Een draaitabelrapport maken............................................................ 1 Draaitabelrapport wijzigen ................................................................ 6 Elementen van een draaitabelrapport............................................... 7 Draaitabelrapport met ingesprongen opmaak .................................. 8 Brongegevens ontsluiten .................................................................. 9 Opgaven.......................................................................................... 10

2. Doelzoeken ........................................................................................11 2.1 2.2 2.3 2.4 2.5

Een voorbeeld ................................................................................. 11 Meer over doelzoeken..................................................................... 12 Doelzoeken in een grafiek............................................................... 12 Beperkingen .................................................................................... 13 Opgaven.......................................................................................... 14

3. Oplosser..............................................................................................15 3.1 3.2 3.3

Voorbeeld 1 ..................................................................................... 15 Altijd een oplossing? ....................................................................... 20 Voorbeeld 2 ..................................................................................... 22

4. Data tabellen ......................................................................................23 4.1 4.2

Data tabel met één invoercel .......................................................... 23 Data tabel met twee invoercellen.................................................... 25

5. Scenariobeheer.................................................................................29 5.1 5.2

Voorbeeld koffiebar ......................................................................... 29 Samenvattingrapport genereren ..................................................... 33

6. Functies vervolg ...............................................................................34 6.1 6.2 6.3

Functies en argumenten................................................................. 34 Invoeren van functies ...................................................................... 35 Een aantal functies besproken ....................................................... 36

6.3.1 6.3.2 6.3.3 6.3.4 6.3.5 6.3.6 6.3.7 6.3.8 6.3.9

Aantal.Als.......................................................................................36 Som.Als .........................................................................................37 NU() ...............................................................................................38 Datum() ..........................................................................................38 Dagen360() .....................................................................................38 Afschrijvingsfuncties ........................................................................39 Functies voor leningen .....................................................................40 Vert.Zoeken....................................................................................41 Horiz.Zoeken ..................................................................................43


pag. ii

E X C E L

2 0 0 0

G E V O R D E R D

7. Matrixformules...................................................................................44 7.1 7.2

Eenvoudige matrixformules ............................................................ 44 Complexere matrixformules ........................................................... 45

8. Databases...........................................................................................47 8.1 8.2

Dataformulier .................................................................................. 47 Zoeken met dataformulier ............................................................... 48

9. Macro's................................................................................................50 9.1 9.2 9.3 9.4 9.5 9.6

Wat is een macro? ......................................................................... 50 Macro's opnemen ........................................................................... 50 Wijziging van een macro ................................................................ 52 Starten van een macro ................................................................... 54 Opdrachtknop maken ..................................................................... 54 Opgaven.......................................................................................... 56


pag. iii

E X C E L

2 0 0 0

G E V O R D E R D

Inleiding In deze cursus wordt gewerkt met de spreadsheet Excel 2000 NL voor Windows 95/98, Windows NT en Windows 2000. Met dit spreadsheetprogramma kunt u krachtig en complex rekenwerk uitvoeren en uw gegevens beheren, analyseren en in kaart brengen. Door deze cursus door te werken leert u hoe Excel uw dagelijkse werkzaamheden kan vereenvoudigen. Het cursusmateriaal is een echt werkboek. Stukjes uitleg worden gevolgd door praktische opdrachten welke u altijd in de tekst kunt herkennen door de volgende lay-out:

: Dit is een praktische opdracht. De lesstof is in twee niveaus verdeeld. Œ Niveau

Niveau 1 bevat elementaire leerstof en opdrachten. Deze moet u zonder meer doorwerken om succesvol met Excel te kunnen werken.

• Niveau

Niveau 2 bevat leerstof en opdrachten die wat dieper op bepaalde onderwerpen ingaan. Om veel gemak van Excel te hebben moet u deze eigenlijk ook wel door werken. Elk hoofdstuk bestaat uit een aantal paragrafen. Bij elke paragraaf is het niveau volgens bovenstaande lay-out aangegeven. Daarnaast staat af en toe aan het eind van een paragraaf een extra opdracht. Ook bij deze extra opdrachten is het niveau aangegeven. Bij deze cursus hoort een verzameling hulpbestanden. Deze hulpbestanden kunt u downloaden vanaf onze website: http://www.softwijs.nl Ook kunt u op deze website eventuele errata, tips en antwoorden op vragen aantreffen. Voor opmerkingen over dit cursusmateriaal kunt u emailen naar: [email protected] De aanpak in deze cursus is gebaseerd op praktijkervaring, studie, diverse literatuur en discussies in nieuwsgroepen op internet. Een aantal ideeën en opdrachten in dit cursusboek zijn afkomstig van ir. M.A.M. Poyck en ir. J.B. van Vroonhoven. Daarnaast zijn een aantal aanvullingen en correcties afkomstig van drs. K. de Jonge. Verder is inspiratie geput uit opdrachten door het bedrijfsleven tijdens stages en afstuderen. ir. B.G. Welman


pag. iv

E X C E L

2 0 0 0

G E V O R D E R D

1. Draaitabellen Draaitabellen (pivot tables) vormen een van de meest krachtige gereedschappen in Excel. Met een draaitabel kunt u snel grote hoeveelheden gegevens samenvatten en analyseren. 1.1 Wat is een draaitabelrapport? Œ niveau

Een draaitabelrapport is een interactieve tabel waarmee u snel grote hoeveelheden gegevens kunt samenvatten.

U kunt de rijen en kolommen in het rapport draaien om verschillende samenvattingen van de brongegevens weer te geven, de gegevens filteren door verschillende pagina's weer te geven of de details voor bepaalde gebieden weergeven. U gebruikt een draaitabelrapport als u verwante totale wilt vergelijken, met name als een lange lijst getallen hebt die u wilt samenvatten en u voor elk getal verschillende feiten wilt vergelijken. In het voorbeeld hierboven kunt u duidelijk zien hoe de golfverkopen van het derde kwartaal in cel F5 zich verhouden tot de verkopen voor een andere sport of een ander kwartaal, of tot de totale verkoop. Omdat een draaitabel interactief is, kunt u steeds de weergave van de gegevens wijzigen om meer details te zien of om andere samenvattingen te berekenen.

1.2 Een draaitabelrapport maken. Als u een draairapport wilt maken gebruikt u de wizard "Draaitabel- en draaigrafiekrapport" om op te geven welke brongegevens u wilt analyseren en om de rapportindeling te maken. Daarna kunt u met de werkbalk "Draaitabel" de gegevens rangschikken binnen die indeling.

: Open het document Draaitabel.xls.


pag. 1

E X C E L

2 0 0 0

G E V O R D E R D

Het werkblad bevat een lijst met omzetgegevens.

Een vraag waarop u met draaitabellen een antwoord kunt krijgen is: Wat is het totale omzet per maand en per product?

: Selecteer een cel ergens in de lijst, bijv. C3. Kies Data -> Draaitabel- draaigrafiekrapport. De Wizard Draaitabel en draaigrafiek verschijnt.

: Kies Volgende. Stap 2 van de wizard verschijnt.


pag. 2

E X C E L

2 0 0 0

G E V O R D E R D

In dit venster kan het gegevensgebied van de lijst worden ingevuld. Excel vult dit al automatisch voor u in.

: Kies Volgende. Stap 3 van de wizard verschijnt. Hier kan aangegeven worden waar de draaitabel moet komen te staan. Kies voor Bestaand werkblad en klik daarna in het werkblad op een cel in kolom G, bijv. in cel G3.

Klik dan op de knop Indeling. Het volgende dialoogvenster verschijnt:

De velden van de lijst verschijnen als knoppen aan de rechterkant. Deze knoppen kunnen naar de juiste locatie in de draaitabel gesleept worden.


pag. 3

E X C E L

2 0 0 0

G E V O R D E R D

: Sleep de knop “Maand” naar de locatie “Rij”. Sleep “Product” naar de locatie “Kolom”. Sleep “Omzet” naar de locatie “Gegevens”. Deze knop krijgt nu de naam “Som van Omzet”. Opmerking

Bij het slepen naar het gegevensgebied past Excel standaard de functie SOM toe op numerieke gegevens en AANTAL op nietnumerieke gegevens.

Tip

Wanneer u een veldknop naar de verkeerde locatie gesleept hebt kunt u deze weer verwijderen door deze veldknop uit de draaitabel te slepen.

: Druk in het scherm Indeling op de knop OK. U verschijnt nu weer in dialoogscherm "Stap 3" van de wizard. Druk hierin op de knop Opties.


pag. 4

E X C E L

2 0 0 0

G E V O R D E R D

Naam:

Hier kan een naam aan de draaitabel worden toegekend. Standaard gebruikt Excel namen als Draaitabel1, Draaitabel2, …

Eindtotaal kolommen

Bij aanvinken berekent Excel eindtotalen voor elke kolom.

Eindtotaal rijen

Bij aanvinken berekent Excel eindtotalen voor elke rij.

AutoOpmaak tabel

Bij aanvinken gebruikt Excel een van de automatische opmaakformaten voor de tabel.

: Wijzig niets en klik op OK en daarna op Voltooien. De draaitabel verschijnt in het werkblad.

Op hetzelfde werkblad verschijnt ook de werkbalk "Draaitabel".

De betekenis van de knoppen op de werkbalk staat hieronder. Keuzemenu Rapport opmaken Wizard Grafieken Wizard Draaitabellen Detail verbergen Details weergeven Gegevens vernieuwen Veldinstellingen Velden verbergen

: Bewaar het resultaat.


pag. 5

E X C E L

2 0 0 0

G E V O R D E R D

Een draaitabel is geen statisch object. De inhoud en opmaak kan steeds gewijzigd worden. Let op

Wanneer de brongegevens wijzigen wordt niet automatisch de draaitabel gewijzigd. Daarvoor moet op de knop “Gegevens vernieuwen” geklikt worden.

1.3 Draaitabelrapport wijzigen : Open zonodig het werkblad Draai.xls en selecteer met de muis Wizard Draaitabellen

een van de velden van de draaitabel zodat de werkbalk "Draaitabel" verschijnt. Klik op de knop “Wizard draaitabellen. het dialoogscherm van stap 3 van de wizard verschijnt weer. Klik op de knop Indeling. Pas de indeling aan volgens onderstaande afbeelding:

Klik op OK en daarna op Voltooien. De draaitabel ziet er nu als volgt uit:

In het volgende voorbeeld wordt de gemiddelde omzet berekend i.p.v. de totale omzet.

: Selecteer in de draaitabel een willekeurige cel in het Veldinstellingen

Gegevensdeel. Klik op de knop Veldinstellingen.


pag. 6

E X C E L

2 0 0 0

G E V O R D E R D

: Het dialoogscherm Draaitabelveld verschijnt nu. Selecteer Gemiddelde en klik daarna op de knop Getal. Maak het getal op in het formaat Financieel, met eurosymbool en 2 decimalen. Klik 2 keer op OK om weer naar het werkblad terug te keren. Het draaitabelrapport ziet er nu als volgt uit.

: Wijzig het draaitabelrapport zodat weer de som van de omzet weergegeven wordt. Bij de opmaak van de gegevensvelden moet het aantal decimalen op 0 gezet worden. Bewaar het werkblad.

1.4 Elementen van een draaitabelrapport Paginaveld item

Kolomveld

Paginaveld Gegevensveld Buitenste rijveld Binnenste rijveld

Gegevensgebied


pag. 7

E X C E L

2 0 0 0

G E V O R D E R D

Rijvelden zijn velden uit de onderliggende brongegevens waaraan een rijrichting is toegewezen in een draaitabelrapport. In het voorbeeld zijn Product en Verkoper rijvelden. Bij meerdere rijvelden wordt het rijveld dat zich het dichst bij het gegevensgebied bevindt het binnenste rijveld genoemd. Alle andere rijvelden worden buitenste rijvelden genoemd. Kolomvelden zijn velden uit de onderliggende brongegevens waaraan een kolomrichting is toegewezen. In het voorbeeld is Maand een kolomveld. Item is een subcategorie van een veld. In het voorbeeld zijn Vlees en Zuivel items in het veld Product Paginaveld is een veld dat is toegewezen aan een pagina en dat werkt als een filter. In het voorbeeld is Regio een paginaveld dat u kunt gebruiken om het rapport op regio te filteren. Hiermee kunt u bijvoorbeeld alleen de samengevatte gegevens weergeven voor de regio Zuid, of voor de regio Noord. Gegevensveld is een veld uit de lijst met gegevens die moeten worden samengevat. In het voorbeeld is "Som van Omzet" een gegevensveld waarin de waarden van het veld Omzet in de brongegevens worden samengevat. Een gegevensveld bevat meestal een samenvatting van numerieke gegevens, zoals statistische gegevens of verkoopcijfers. Deze worden standaard samengevat met de functie Som. De onderliggende gegevens kunnen echter ook tekst zijn. Deze worden in draaitabelrapporten standaard samengevat met de functie Aantal.

1.5 Draaitabelrapport met ingesprongen opmaak In een draaitabelrapport springen de gegevens voor elk rijveld in volgens een indeling vergelijkbaar met inspringen die met een tekstverwerker gemaakt zijn. Met deze indeling zijn alle samengevatte waarden voor een gegevensveld af te lezen in één kolom. Dat is erg handig voor lange rapporten of voor rapporten die op papier afgedrukt moeten worden. De ingesprongen indeling is een automatische opmaak die toegepast kan worden nadat eerst een gewoon draairapport gemaakt is. De kolomvelden worden dan rijvelden en ook wordt een andere tekst- en celopmaak toegepast. In de volgende oefening wordt dit voorbeelrapport gemaakt.


pag. 8

E X C E L

2 0 0 0

G E V O R D E R D

: Omdat de gegevens eerst per Product worden samengevat, moet dit veld het kolomveld worden. Maak een draaitabelrapport volgens onderstaande indeling:

: Klik in de werkbalk "Draaitabel" op de knop Opmaken. Rapport opmaken

Het dialoogvenster "AutoOpmaak" verschijnt. Kies voor de indeling "Rapport 2" en klik op OK. Tip

Door een cel in het draairapport met ingesprongen opmaak te selecteren en dan met de rechtermuisknop te klikken krijgt u veel mogelijkheden om acties uit te voeren.

1.6 Brongegevens ontsluiten In een draaitabel worden gegevens samengevat. Het kan dus voorkomen dat een resultaat zichtbaar is waarvan u niet weet uit welke individuele waarden het is samengesteld. Deze individuele waarden kunt u zeer eenvoudig oproepen door dubbel te klikken op het samengestelde resultaat in de draaitabel. Excel voegt dan een nieuw werkblad in met daarin de individuele waarden.

: De totale vleesomzet van Nienhuis in de maand mei bedroeg 17.578 Euro. Uit welke waarden is dit samengesteld? Klik dubbel op 17578. Excel zet in een nieuw werkblad het volgende resultaat:


pag. 9

E X C E L

2 0 0 0

G E V O R D E R D

1.7 Opgaven Opgave 1

Open het bestand Personeel.xls. Maak een draaitabelrapport om het gemiddelde salaris per afdeling en per divisie te bepalen. Het rapport moet er uitzien zoals hierna is aangegeven.

Opgave 2

Open het bestand Personeel.xls. Maak onderstaand draaitabelrapport.

Opgave 3

Open het bestand Draai1.xla. In het werkblad zijn voor elke buitendienstmedewerker de volgende gegevens bijgehouden: Maand, Regio, Vertegenwoordiger, Omzet, Reiskosten enProvisie. Maak één draaitabelrapport waarmee u de volgende gegevens kunt verkrijgen:

• De totale omzet, reiskosten en provisie per maand. • De totale omzet, reiskosten en provisie per vertegenwoordiger. • De totale omzet, reiskosten en provisie per regio.


pag. 10

E X C E L

2 0 0 0

G E V O R D E R D

2. Doelzoeken Met behulp van Doelzoeken kunt u er achter komen welke waarde in een formule moet worden ingevuld om een bepaalde gewenste uitkomst te verkrijgen. • Niveau

Een formule is uit een of meerdere variabelen opgebouwd. Normaliter wilt u de uitkomst van de formule berekenen bij bepaalde waarden van de variabelen. Deze waarden voor de variabelen zijn dus bekend, de uitkomst van de formule niet. Maar wat wanneer u precies weet hoe het resultaat er uit moet zien, maar de waarden van de variabelen niet kent? Dan kunt u Doelzoeken gebruiken. Met Doelzoeken wordt de waarde in een bepaalde cel gewijzigd totdat een formule die afhankelijk is van die cel het gewenste resultaat geeft.

2.1 Een voorbeeld Stel u wilt een koffiestand in een voetbalstadion runnen. De maandelijkse kosten voor huur, personeel en diversen zijn bekend. U weet ook wat uw inkoop- en verkoopprijs voor een kop koffie is. Wat u graag wilt weten is hoeveel koppen koffie u maandelijks moet verkopen om quitte te spelen. Dit eenvoudige voorbeeld kunt u gemakkelijk met de hand uitrekenen. Echter op dergelijke vraagstukken kunt u “Doelzoeken” van Excel gebruiken.

: Begin met een nieuw bestand en voer het voorbeeld in. Gebruik formules op de daarvoor bestemde plaatsen (B7 en B17). Kies Extra -> Doelzoeken. Vul het dialoogscherm “Doelzoeken” in zoals hiernaast is aangegeven.


pag. 11

E X C E L

2 0 0 0

G E V O R D E R D

: Klik op OK. Excel gaat aan het werk en komt na korte tijd met het antwoord 562,5. Aangezien er geen halve koppen koffie bestaan zult u zelf het antwoord moeten wijzigen in 563.

2.2 Meer over doelzoeken Excel kan niet altijd het antwoord vinden voor het door u gewenste resultaat. Soms is het mogelijk dat een oplossing helemaal niet bestaat. In een dergelijke situatie verschijnt het dialoogscherm Doelzoekstatus en informeert u hierover (zie figuur). Het is echter ook mogelijk dat een oplossing wel bestaat, terwijl Excel rapporteert dat een oplossing niet gevonden kan worden. U kunt dan het volgende proberen: 1.

Verander de waarde van de te wijzigen cel in een waarde die dichter bij de oplossing ligt en start Doelzoeken opnieuw.

2.

Kies Extra -> Opties -> tabblad Berekenen.

Verhoog het maximale aantal iteraties. Start Doelzoeken opnieuw. 3.

Controleer uw oplosmethode en controleer of de doelcel wel afhangt van de te wijzigen cel.

2.3 Doelzoeken in een grafiek U kunt ook doelzoeken in een grafiek door de gegevenspunten in een grafiek te slepen.

: Op 1 jan. 1998 wordt 1000 Euro op een spaarrekening gezet. De renteopbrengst is 7% op jaarbasis. De rente wordt op 1 januari van het nieuwe jaar op de spaarrekening gestort. Bereken het spaarbedrag voor de eerste tien jaar volgens nevenstaand voorbeeld. Zet jaar en waarde in een kolomdiagram.


pag. 12

E X C E L

2 0 0 0

G E V O R D E R D

Stel dat u nu wilt weten hoe hoog het bedrag is dat gestort moet worden om na 10 jaar een bedrag van 2500 Euroop de spaarrekening te hebben.

: Selecteer de laatste datakolom in de grafiek. Ga met de muisaanwijzer naar de top van de kolom en let er op dat de muisaanwijzer in een dubbele pijl verander.

: Sleep dan de kolom wat omhoog. Let op de verandering in het veld dat bij de cursor verschijnt. Blijf slepen tot de waarde precies 2500 is en laat dan de muisknop los. Excel antwoordt met het dialoogscherm Doelzoeken. Merk op dat twee velden al zijn ingevuld.

: Vul voor de te wijzigen cel in: B1. Klik op OK. Excel berekent nu de waarde in B1. Het antwoord is 1271.

2.4 Beperkingen Doelzoeken is alleen geschikt voor problemen met één variabele. Bij doelzoeken laat Excel de waarde in 1 cel variëren totdat een formule die van deze cel afhankelijk is, het gewenste resultaat oplevert. Met doelzoeken kunt u dus een bepaalde waarde vinden voor een bepaalde cel, door de waarde van slechts één andere cel aan te passen. Een alternatief is de Oplosser (zie hoofdstuk 3).


pag. 13

E X C E L

2 0 0 0

G E V O R D E R D


Stel u wilt als freelancer gaan werken. U hebt zich voorgenomen dit alleen te doen wanneer u ten minste f 7000,- per maand kunt verdienen. Voor elke uitgevoerde opdracht krijgt u een provisie van 7,8%. Los dit probleem met “Doelzoeken” op. In de afbeelding hiernaast kunt u het juiste antwoord aflezen.

Opgave 2

Met behulp van de functie BET kan de periodieke betaling van een lening worden berekend op basis van constante betalingen en een constant rentepercentage. Voer het hiernaast afgebeelde voorbeeld in. De waarde in cel B4 is het resultaat is het resultaat van de formule = -BET(B3/12;B2;B1). Bepaal met Doelzoeken het rentepercentage in cel B3 dat een betaling van 900 euro in cel B4 oplevert. (Antwoord: 7,02%)


pag. 14

E X C E L

2 0 0 0

G E V O R D E R D

3. Oplosser Met de oplosser kunt u problemen oplossen welke de doelzoeker niet aankan. De oplosser is waarschijnlijk een van de moeilijkste gereedschappen van Excel. • Niveau

De Oplosser van Excel kunt u gebruiken als 1.

u een optimale waarde (minimum of maximum) voor een bepaalde cel zoekt.

2.

de waarde van meerdere cellen aangepast moet worden (dus bij meer dan 1 variabele)

3.

als u beperkingen (restricties, constraints) wilt aanbrengen in een of meer waarden die bij de berekening zijn betrokken.

3.1 Voorbeeld 1 Een ontwerper van dure leren jacks heeft twee ontwerpen gemaakt voor het nieuwe seizoen, een lang en een kort jack. Voor het maken van een kort jack is 1 uur werktijd op de snijafdeling en 3 uur werktijd op de naaiafdeling nodig. Voor een lang jack zijn deze tijden respectievelijk 2 uur en 4 uur. Deze ontwerper moet het gebruik van beide afdelingen delen met andere ontwerpers. Om deze reden is de beschikbare capaciteit op de snijafdeling 32 uur per week en op de naaiafdeling 84 uur per week. De marktvraag naar lange leren jacks is beperkt. Niet meer dan 12 lange leren jacks kunnen per week afgezet worden. De winst op een kort jack is 90 Euro en op een lang jack 144 Euro. Hoeveel jacks van elk type moeten per week gemaakt worden om een zo groot mogelijke winst te krijgen? Doelfunctie Stel K is het aantal korte jacks en L het aantal lange jacks dat per week geproduceerd wordt. De winst per week kan dan berekend worden met: W =K*90+ L*144 Het doel is om deze waarde zo groot mogelijk te krijgen. Vandaar dat deze functie ook wel de doelfunctie genoemd wordt. Het is soms erg lastig om een probleem zo te formuleren dat één doelfunctie verkregen wordt. Restricties (Beperkingen, Constraints) Wanneer alleen wiskundig naar deze vergelijking gekeken wordt, kan de winst zo groot gemaakt worden als wenselijk is. Je hoeft alleen maar de waarden voor K en L groter te maken. Echter elke fabrikant, zowel groot als klein, heeft een beperkte productiecapaciteit. Deze capaciteit wordt beperkt door beschikbare hoeveelheid grondstof, beschikbaar aantal mensen en machines, beperkte vraag, enz.


pag. 15

E X C E L

2 0 0 0

G E V O R D E R D

Deze beperkingen worden gewoonlijk aangeduid als constraints. Constraint snijafdeling snijtijd voor korte + snijtijd voor lange <= jacks per week jacks per week 1*K + 2*L <=

max. beschikbare snijtijd 32

Constraint naaiafdeling naaitijd voor + naaitijd voor <= max. beschikbare korte jacks per lange jacks per naaitijd week week 3*K + 4*L <= 84 Constraint marktvraag Er kunnen niet meer dan 12 lange jacks per week afgezet worden. Aangezien niet op voorraad geproduceerd wordt moet het geproduceerde aantal lange jacks L <= 12 Constraint niet-negatieve waarden Vaak worden deze constraints over het hoofd gezien. Het aantal te maken jacks kan nooit negatief zijn, dus: K >= 0 L >= 0 Constraints gehele waarden Ook hier wordt vaak niet opgelet. Het aantal jacks moet een geheel getal (= integer) zijn, dus: K = integer L = integer Model in Excel Het is vaak erg lastig om het probleem goed te modelleren zodat het geschikt is voor een handige verwerking met Excel.

: Voor onderstaand model in Excel in en bestudeer het model. Zorg er voor dat in F2:H4 de juiste formules staan. In E2:E3 staan startwaarden voor het zoekproces.

: De doelcel die geoptimaliseerd moet worden is H4. Hierin staat de totale winst welke zo groot mogelijk moet worden. Dit moet bereikt worden door de juiste aantallen in E2:E3 in te vullen.


pag. 16

E X C E L

2 0 0 0

G E V O R D E R D

: Selecteer de doelcel H4 en start de oplosser wordt via: Extra -> Oplosser. Het dialoogscherm “Parameters Oplosser” verschijnt.

: Controleer dat in het vak bij “Cel bepalen” is ingevuld: $H$4. Controleer dat bij “Gelijk aan” is geselecteerd: Max. Vul in bij “Door verandering cel” $E$2:$E$3. Klik op Toevoegen bij het vak “Restricties”.

Celverwijzing Hier geeft u de cel of het cellenbereik op met de waarden waaraan u restricties wilt opleggen. Restrictie

Hier geeft u de restrictie op die u wilt opleggen aan de inhoud van het vak "Celverwijzing". Selecteer de gewenste relatie (<=, =, >= of Int) tussen de cel of het bereik waarnaar wordt verwezen en de restrictie en typ de restrictie in het vak aan de rechterkant. De restrictie kan een getal, een verwijzing naar een cel of een cellenbereik, of een formule zijn.

Toevoegen

Hiermee voegt u een restrictie toe zonder terug te keren naar het dialoogvenster Parameters Oplosser.

: Voeg onderstaande restricties toe. Klik na invoeren steeds op Toevoegen. Wanneer de laatste restrictie is toegevoegd klikt u op Annuleren.


pag. 17

E X C E L

2 0 0 0

G E V O R D E R D

$F$4 $G$4 $E$3 $E$2:$E$3 $E$2:$E$3

<= <= <= >= Int

$F$5 $G$5 12 0 Integer

: Klik op Oplossen. Na korte tijd verschijnt het resultaatscherm van de oplosser:

U heeft nu een aantal mogelijkheden: Oplossing behouden

De oorspronkelijke waarden in de veranderende cellen worden vervangen door de waarden die de oplosser gevonden heeft.

Oorspronkelijke waarden herstellen Rapporten

U kunt 1 tot 3 rapporten maken waarin staat wat de oplosser gedaan heeft. (Gebruik de Shift toets om meer dan 1 rapport te selecteren). Elk rapport wordt op een afzonderlijk werkblad gezet.

Scenario opslaan

De oplossing kan als een scenario onder een naam worden opgeslagen. Hiermee kan de scenario manager (zie later) aan de slag.


pag. 18

E X C E L

2 0 0 0

G E V O R D E R D

: Selecteer bij “Rapporten” Antwoord en klik op OK. : Bekijk het werkblad “Antwoordrapport 1”.

Oplossing De maximale winst per week is 2664 door 20 korte jacks (K=20) en 6 lange jacks (L=6) per week te maken. Bij het gedeelte “Restricties” kunt u zien welke restricties bindend zijn. Dit betekent dat de grenzen van de restrictie bereikt zijn en er geen speling meer is. Te zien valt dat beide capaciteitsrestricties bindend zijn. De maximale snij- en naaitijd wordt gebruikt. Opmerking

Het verdient aanbeveling om de belangrijke cellen een zinvolle naam te geven. De formules in de werkbladen en de antwoordrapporten worden dan beter leesbaar.


pag. 19

E X C E L

2 0 0 0

G E V O R D E R D

3.2 Altijd een oplossing? Het komt regelmatig voor dat de Oplosser rapporteert dat geen oplossing gevonden kan worden, zelfs wanneer u er zeker van bent dat een oplossing bestaat. Wat kunt u in dat geval doen? 1.

Wijzig de startwaarden van de veranderende cellen.

2.

Verander een of meer van de opties van de oplosser.

In het dialoogscherm “Parameters Oplosser” zit een knop Opties. Wanneer u hierop klikt verschijnt:

Dit dialoogvenster biedt uitgebreide mogelijkheden om het oplossingsproces te besturen. Ook kunt u hier de specificaties voor een bepaald probleem laden of opslaan en parameters definiëren voor zowel lineaire als niet-lineaire problemen. Elke optie heeft een standaardinstelling die geschikt is voor de meeste problemen. Max tijd

Hier geeft u de maximale tijd (in sec.) op die de oplosser mag besteden aan het probleem. De standaardwaarde van 100 (seconden) is voldoende voor de meeste kleine problemen, maar u kunt een waarde opgeven tot 32.767. Wanneer de oplosser rapporteert dat de toegestane tijd wordt overschreden kunt u hier de waarde verhogen.

Iteraties

Het maximale aantal iteraties (tussentijds berekeningen). Ook hier is de standaardwaarde van 100 meestal voldoende. Het aantal kunt u verhogen tot max. 32.767 wanneer geen antwoord gevonden kan worden.


pag. 20

E X C E L

2 0 0 0

G E V O R D E R D

Precisie

Het getal dat u opgeeft wordt gebruikt om te bepalen of de celwaarde van een restrictie aan een bepaalde voorwaarde voldoet of aan een bepaalde boven- of ondergrens die u hebt opgegeven. Het getal moet een breuk zijn tussen 0 en 1. Hoe minder decimalen u opgeeft, hoe lager de precisie wordt.. Hoe hoger de precisie is, hoe langer het zal duren voordat Oplosser met oplossingen komt.

Tolerantie

Het maximale foutpercentage dat nog is toegestaan bij integer oplossingen. Allen van belang wanneer een integer restrictie aanwezig is. Bij verhogen van dit getal verloopt het zoekproces sneller.

Uitgaan van lineair model

Hiermee wordt het oplossingsproces versneld als alle relaties in het model lineair zijn en u een lineair optimaliseringprobleem oplost of een lineaire benadering van een niet-lineair probleem.

Iteratieresultaat tonen

Na elke iteratie stopt de oplosser en geeft het resultaat weer.

Schaal automatisch aanpassen

Hiermee wordt de schaal automatisch aangepast. Dit is handig als de invoeren uitvoergegevens veel verschillen in grootte, bijv. als u het winstpercentage van een miljoeneninvestering wilt maximaliseren.

Schattingen Afgeleiden Zoeken ….

Hiermee kunt u een aantal technische aspecten van het oplosproces besturen. In de meeste gevallen hoeft u de instellingen niet te wijzigen. Raadpleeg de Help voor meer informatie.

Model laden

Hiermee wordt het dialoogvenster “Model laden” weergegeven, waarin u de verwijzing naar het model dat u wilt laden, kunt opgeven.

Model opslaan

Hiermee wordt het dialoogvenster “Model opslaan” weergegeven, waarin u het bereik kunt opgeven waar het model moet worden opgeslagen. Klik alleen op deze knop als u meer dan één model van Oplosser bij het werkblad wilt opslaan. Het eerste model wordt automatisch opgeslagen.


pag. 21

E X C E L

2 0 0 0

G E V O R D E R D

3.3 Voorbeeld 2 : Voer het werkblad hiernaast in. Hierin wordt de opbrengst van drie producten berekend. Kolom D bevat formules! Iedereen kan zien dat de grootste winst per eenheid van product C afkomstig is. Een voor de hand liggende oplossing is dus om alleen product C te produceren. Echter zoals in de meeste situaties zijn er beperkingen in het bedrijf:

• De totale produktiecapaciteit is 300 eenheden/dag. • Er moeten minimaal 50 eenheden van product A en 40 eenheden van product B gemaakt worden om bestaande orders uit te kunnen voeren.

• Vanwege de beperkte marktvraag naar product C is de max.

produktie 40 stuks. : Voor welke aantallen van A, B en C wordt de grootste winst gemaakt? Welke restricties zijn dan bindend? Voer het model in de oplosser in en start deze.

Het goede antwoord is: 50 stuks A, 210 stuks B en 40 stuks C. De winst is in dat geval 10.620 Euro.


pag. 22

E X C E L

2 0 0 0

G E V O R D E R D

4. Data tabellen Een van de manieren om het “Wat als …” proces te automatiseren is het maken van data tabellen. • Niveau

Met behulp van het commando Data -> Tabel kunt u overzichtelijke tabellen maken met daarin uitkomsten van formules. Er zijn twee mogelijkheden: met één of met twee invoercellen.

4.1 Data tabel met één invoercel Wanneer u voor een formule met één variabele voor verschillende waarden van die variabele de uitkomst van die formule wilt berekenen kunt u dat doen door een kolommetje met waarden voor de variabele te maken en in het kolommetje daarnaast de formule te zetten. Hiernaast ziet u een eenvoudig voorbeeld voor de formule y = 2*x -1. Veel handiger is het om in die gevallen een datatabel met maken.

x 1 2 3 4 5

y =2*A2-1 =2*A3-1 =2*A4-1 =2*A5-1 =2*A6-1

1 invoercel te

De algemene lay-out van een dergelijke tabel ziet er als volgt uit:

• De linker kolom bevat de waarden voor de variabele. Deze

waarden moeten dus achtereenvolgens in de invoercel worden ingevuld.

• De bovenste rij bevat formules of, wat vaker voorkomt, verwijzingen naar formules.

• De linkerbovencel van de cel wordt niet gebruikt.


pag. 23

E X C E L

2 0 0 0

G E V O R D E R D

• De invoercel mag een willekeurige cel op het werkblad zijn. Deze cel gebruikt Excel als een tijdelijke opslagplaats. In de gebruikte formules moet een verwijzing naar deze cel staan.

Voorbeeld 1

: Voer het werkblad hiernaast in. In cel B7 moet de volgende formule staan: =B5*(B3-B4). De gebruikte invoercel is hier dus B5. De waarden die Excel straks hier gaat invullen staan in A8:A17.

: Selecteer de gehele tabel, dus inclusief de linkerbovencel! In dit geval dus A7:B17. Kies Data -> Tabel. Het dialoogscherm Tabel verschijnt. Klik in het vak bij Kolominvoercel en klik dan op cel B5 in het werkblad.

: Klik op OK. Het resultaat wordt nu berekend.

: Selecteer een willekeurige cel in de reeks B8:B17. Merk op dat in de formulebalk een matrixformule is gebruikt: {=TABEL(;B5)}


pag. 24

E X C E L

2 0 0 0

G E V O R D E R D

Voorbeeld 2

: Hieronder ziet u een wat uitgebreider voorbeeld van het gebruik van de data tabel met 1 invoercel. Cel B1 wordt hier als invoercel gebruikt. Hierin wordt door Excel de verschillende waarden voor x ingevuld. Deze invoerwaarden voor x staan in B4:B8. In C2:F2 staat in tekstvorm de te gebruiken formules. Voer in C3:F3 deze formules in, waarbij x steeds vervangen moet worden door de invoercel voor x, B1 dus. Zo is dus de formule in C3 gelijk aan =2*B1-1. Selecteer daarna de tabel B3:F8. Kies Data -> Tabel en gebruik voor de kolominvoercel B1. Het antwoord is hieronder te zien.

4.2 Data tabel met twee invoercellen Bij een data tabel met 2 invoercellen kunnen waarden voor 2 variabelen in een formule worden gesubstitueerd. De ene invoercel heet de kolom invoercel, de andere de rij invoercel. De algemene lay-out lijkt veel op een data tabel met 1 invoercel, maar heeft een paar belangrijke verschillen.

• Bij een data tabel met 1 invoercel kunnen meerdere formules geëvalueerd worden, bij 2 invoercellen kan uitsluitend van 1 formule de resultaten getoond worden.

• De eerste rij bevat niet de formules, maar de waarden voor de rij invoercel.

• De cel linksboven bevat nu de formule of een verwijzing naar de formule.


pag. 25

E X C E L

2 0 0 0

G E V O R D E R D

Als voorbeeld nemen we weer het model voor de verhuur van een zomerhuisje. Behalve het aantal verhuurde weken wordt nu ook de opbrengst per week gevarieerd.

: Voer bovenstaand werkblad in. In cel A7 moet de volgende formule staan: =B5*(B3-B4). Selecteer A7:F17. Kies Data -> Tabel en vul de adressen voor de invoercellen in en daarna OK. Rij-invoercel= $B$3 Kolominvoercel = $B$5 Het resultaat ziet er als volgt uit:


pag. 26

E X C E L

2 0 0 0

G E V O R D E R D

Voorbeeld Reclamecampagne Een bedrijf wil een reclamecampagne voeren door folders via direct mail aan potentiële klanten te sturen. In een werkblad wil men de verwachte winst van deze reclamecampagne berekenen. Het model gebruikt 2 variabelen: het aantal te versturen folders (lopend van 100.000 tot 300.000) en het responspercentage (lopend van 1,50% tot 3,00%). De opzet van het werkblad is hieronder weergegeven.

De drukkosten per stuk (0,35) en verzendkosten per stuk (0,55) zijn vaste gegevens, evenals de verwachtte opbrengst per respons (44). De andere cellen moeten via formules berekend worden.


pag. 27

E X C E L

2 0 0 0

G E V O R D E R D

Aantal terug (respons):

=ResponsPercentage*Aantal_Verstuurd

Opbrengst totaal:

=Respons*Opbrengst_PerRespons

Kosten totaal:

=Aantal_Verstuurd*(Drukkosten_PerStuk+Verzendkosten_PerSt uk)

Winst:

=Opbrengst_Totaal-Kosten_Totaal

: Zet al deze gegevens in een werkblad. Zorg voor een duidelijke opmaak van de getallen en voorzie de belangrijke cellen van een naam. Gebruik deze namen in de formules. Omdat in de data tabel de winst moet worden berekend, wordt in de cel linksboven (A12) een verwijzing naar de winstberekening (B10) gezet. Dus inhoud A12: =B10. Maak verder de data tabel af. Het resultaat ziet er als volgt uit:


pag. 28

E X C E L

2 0 0 0

G E V O R D E R D

5. Scenariobeheer De “Wat als …”analyse is het hart van menig spreadsheetmodel. Met behulp van Scenariobeheer kunt u meerdere van deze analyses in één werkmap samenvoegen. U kunt eenvoudig switchen tussen de verschillende scenario’s en de uitwerking zien van de diverse aannames. • Niveau

Met Scenariobeheer van Excel kunt u gemakkelijker de “Wat als …” modellen automatiseren. Verschillende waarden voor verschillende sets invoervariabelen (Excel noemt dit veranderende cellen) kunnen onder een naam worden opgeslagen. Zo’n set kan dan geselecteerd worden waarna Excel deze waarden in het werkblad gebruikt. Ook kunnen samenvattingsrapporten worden gemaakt waarin de verschillende effecten nog eens duidelijk getoond worden. Elk scenario wijzigt de waarden in het werkblad. Het is verstandig om altijd de huidige situatie op te slaan, bijv. onder de naam Huidig. Met scenariobeheer kunt u vele scenario’s vastleggen.

5.1 Voorbeeld koffiebar In het nu volgende voorbeeld zult u zien hoe de eigenaar van een kleine koffiebar scenariobeheer gebruikt om verschillende alternatieven door te rekenen m.b.t. gemiddelde opbrengsten en kosten.

In het werkblad ziet u de gemiddelde prestatie van de koffiebar over het afgelopen jaar.

: Voer bovenstaande gegevens in een werkblad in en geef de cellen zinvolle namen. Denk ook aan de volgende formules: Bruto winst per bestelling = C3-C4.


pag. 29

E X C E L

2 0 0 0

G E V O R D E R D

Bruto winst totaal (per week) = C5*C6. Bruto winst totaal (per jaar) = C7*52. Subtotaal = SOM(D8:D11). De eigenaar wil nu verschillende alternatieven doorrekenen om tot een betere prestatie te komen. In de verschillende alternatieven wijzigen de waarden van de grijs gekleurde cellen. Er worden twee scenario’s bekeken. De eerste is de huidige situatie. Het tweede scenario omvat een verhoging van de prijzen, een vermindering met 5% van de uitgaven voor reclame, een vermindering van het totale salarisbedrag

: Kies Extra -> Scenariobeheer. Klik in het venster op Toevoegen.

: Vul in het dialoogvenster de “Scenarionaam” en de “Veranderende cellen” volgens onderstaand voorbeeld in.


pag. 30

E X C E L

2 0 0 0

G E V O R D E R D

: Klik op OK. Het dialoogscherm “Scenariowaarden” verschijnt nu. Hierin kunt u eventueel de waarden veranderen. U gaat dit echter niet doen omdat u de huidige situatie wilt vastleggen.

: Klik op OK. In het beheervenster dat dan weer verschijnt kunt u de aanwezige scenario’s zien. Nu nog uitsluitend “Huidig”.

Klik op Toevoegen om een nieuw scenario in te voeren.

: Typ onderstaande naam in en klik op OK.

Wijzig de volgende waarden: Opbrengst_Per Bestelling: =1,05*3,65 Salarissen: 225000 Reclame: 50000

: Klik op OK. Omdat in een cel een formule i.p.v. een waarde staat komt Excel met de waarschuwing dat de formule vervangen zal worden door de uitkomst ervan.


pag. 31

E X C E L

2 0 0 0

G E V O R D E R D

Klik op OK. U keert weer terug in het scherm “Scenariobeheer”.

U kunt nu met dit dialoogvenster op het scherm, een van de scenario’s selecteren en dan op Weergeven klikken om het scenario uit te voeren en de resultaten in het werkblad te zetten.

: Selecteer het scenario “Hogere prijzen, minder salaris en reclame” en klik dan op Weergeven. Let op het resultaat in de kolommen C en D. Herhaal voorgaande voor het scenario “Huidig”.

: Maak nog een derde scenario onder de naam “Hogere prijzen, lagere kosten” met de volgende wijzigingen t.o.v. Huidig: Opbrengst_PerBestelling: 3,75. Kosten_PerBestelling: 1,80. Bekijk het effect van dit scenario.


pag. 32

E X C E L

2 0 0 0

G E V O R D E R D

5.2 Samenvattingrapport genereren Het is erg eenvoudig om een rapport te laten maken met daarin een samenvatting van de belangrijkste resultaten.

: Klik in het dialoogvenster “Scenariobeheer” op Samenvatting. Het dialoogvenster “Scenariosamenvatting” verschijnt dan. Kies als “Rapporttype” voor Scenario-samenvatting en neem cel D13 (de Bedrijfswinst) als “Resultaatcel”. Klik daarna op OK.


pag. 33

E X C E L

2 0 0 0

G E V O R D E R D

6. Functies vervolg • Niveau

Excel kent meer dan 300 ingebouwde functies. Daarnaast kunt u ook nog eens uw eigen functies maken met behulp van de macroprogrammeertaal Visual Basic for Applications (VBA). Dat laatste komt hier niet aan bod. Een aantal nuttige functies die niet al in de basiscursus Excel behandeld zijn zullen hier besproken worden.

6.1 Functies en argumenten aantal argumenten

Alle functies gebruiken de ronde haakjes (). De informatie die tussen deze haakjes staat wordt een argument genoemd. Functies verschillen in het aantal argumenten. De volgende mogelijkheden zijn aanwezig: 1.

Functies zonder argumenten. Voorbeeld: =NU()

2.

Functies met een vast aantal argumenten (één of meer). Voorbeeld: =ABS(-3) =HERHALING("test";3)

3.

Functies met een onbepaald aantal argumenten. Voorbeeld: =SOM(1;2) =SOM(1;2;3)

4.

Functies met optionele argumenten. Voorbeeld: =LINKS("test") =LINKS("test";2)

scheiding argumenten

Wanneer een functie meer dan één argument heeft, worden de argumenten in de Nederlandstalige Excel gescheiden door een puntkomma (bij de Engelstalige Excel is dat een komma).

vorm argumenten

De argumenten kunnen op verschillende manieren ingevoerd worden. Een aantal mogelijkheden: 1.

Waarden direct intypen. Voorbeeld: =SOM(1;2;3) =DEEL("test";2;2)

2.

Celadressen gebruiken. Voorbeeld: =SOM(A1:A5)

3.

Namen van celadressen gebruiken. Voorbeeld: =LINKS(voornaam;1) Mits er een cel met de naam voornaam is!

4.

Expressies gebruiken, dus een soort formule binnen een formule.. Voorbeeld: =SOM(2*3;4*5)

5.

Andere functies als argument. Bij gebruik van functies binnen andere functies wordt ook wel gesproken van geneste functies. Voorbeeld: =SIN(RADIALEN(A1))


pag. 34

E X C E L

2 0 0 0

G E V O R D E R D

6.2 Invoeren van functies Er zijn een paar mogelijkheden om functies in te voeren. Handmatig

Wanneer u de naam van de functie en de argumenten kent, kunt u de functie gewoon intypen. Dit is vaak de snelste weg, maar vereist wel enige ervaring. Wanneer u de naam van de functie met kleine letters intypt, vertaalt Excel deze automatisch naar hoofdletters. Dit kan handig zijn om met name typefouten te herkennen. Immers volgt niet de automatische vertaling naar hoofdletters, dan herkent Excel de functie niet.

Functie plakken

Wanneer u onbekend bent met het gebruik van een bepaalde functie is het gebruik van de knop Functie plakken aan te bevelen. Deze knop zit op de werkbalk "Standaard".

geneste functies

U kunt functies ook als argumenten voor andere functies gebruiken. Een formule kan tot zeven niveaus van geneste functies bevatten. Wanneer Functie B als argument in Functie A wordt gebruikt, is Functie B een functie op het tweede niveau. Als Functie B Functie C als argument heeft, is Functie C een functie op het derde niveau. Als voorbeeld hoe u een geneste functie kunt invoeren wordt de formule hierboven in cel B1 van een werkblad ingevoerd. Deze functie berekent de sinus van een hoek waarvan de hoekgrootte in A1 in graden staat.

: Begin met een leeg werkblad. Typ in cel A1 in: 45. Selecteer cel B1 en klik op de knop Functie plakken. Selecteer in het linkervenster als categorie: Wiskunde en trigonometrie. Selecteer in het rechtervenster als functienaam: SIN.


pag. 35

E X C E L

2 0 0 0

G E V O R D E R D

: Klik op OK. Het venster (het zogenaamde formulepallet) voor de volgende stap verschijnt nu:

: Klik op de pijl omlaag op de formulebalk (zie figuur). Er verschijnt een uitklapvenster. Klik hierin op Meer functies …. Het bekende venster Functie plakken verschijnt. Selecteer hierin de gewenste functie RADIALEN. Nu verschijnt het formulepallet voor deze functie. Voer voor hoek A1 in (intypen of aanklikken in werkblad). Klik op OK. Het antwoord verschijnt: 0,707107

6.3 Een aantal functies besproken 6.3.1 Aantal.Als Syntax AANTAL.ALS(bereik; criterium) bereik Is het cellenbereik waarvan u de niet-lege cellen wilt tellen. criterium Is het criterium in de vorm van een getal, expressie of tekst dat bepaalt welke cellen zullen worden geteld. Tip

De functie Aantal.Als is zeer handig bij het maken van frequentieverdelingen.


pag. 36

E X C E L

2 0 0 0

G E V O R D E R D

: Voer de gegevens in de kolommen A, B en D in. In E2:E5 moeten formules komen waarmee de aantallen geteld worden. In E2 staat de volgende formule: =AANTAL.ALS( B:B;D2). Merk op dat het eerste argument naar de hele kolom B verwijst. Wanneer nu aan de onderkant nieuwe namen worden toegevoegd worden de frequenties automatisch aangepast. 6.3.2 Som.Als Syntax SOM.ALS(bereik; criterium; optelbereik) bereik Is het bereik van cellen dat u wilt evalueren. criterium Is het criterium in de vorm van een getal, expressie of tekst waarmee u opgeeft welke cellen u wilt optellen. optelbereik Optioneel. Zijn de feitelijke cellen die moeten worden opgeteld. De cellen in optelbereik worden alleen opgeteld als de corresponderende cellen in bereik aan de criteria voldoen. Als u optelbereik weglaat, worden de cellen in bereik opgeteld. Deze functie werkt op ongeveer dezelfde wijze als de Aantal.Als functie.

: Lees het bestand KOFFIE.XLS in. Zet in G2:G5 de juiste formules neer. Zo zou de formule in G2 kunnen luiden: =SOM.ALS(C:C;F2;D: D) Opmerking

Een optelling zoals in dit voorbeeld is veel eenvoudiger te realiseren met behulp van draaitabellen (zie hoofdstuk 1)


pag. 37

E X C E L

2 0 0 0

G E V O R D E R D

6.3.3 NU() Converteert de huidige datum en tijd naar een serieel getal.

: Typ in een willekeurige cel de volgende

22-3-98 20:57

functie in: =Nu() U krijgt een uitkomst in de vorm zoals hier aangegeven. De uitkomst verandert alleen wanneer het werkblad opnieuw doorgerekend wordt. Dat is bijvoorbeeld het geval wanneer ergens in een of andere cel iets ingetypt of gewijzigd wordt. Opmerking

Door op F9 te drukken wordt handmatig de opdracht tot herberekening gegeven.

6.3.4 Datum() Converteert een bepaalde datum naar een serieel getal. Syntaxis jaar maand dag

DATUM(jaar; maand; dag) is een getal tussen 1900 en 2078. is een getal dat de maand van het jaar aangeeft. is een getal dat de dag van de maand aangeeft.

: Voer in een cel met deze functie een willekeurige datum in. 6.3.5 Dagen360() Het resultaat van de functie is het aantal dagen tussen begindatum en einddatum. Syntaxis begindatum einddatum methode

DAGEN360(begindatum; einddatum; methode) begindatum, bijv. “1-1-98” einddatum, bijv. “21-3-98 Facultatief. Geeft aan of de Europese of Amerikaanse methode voor dagentelling moet worden gebruikt bij de berekening.

Deze functie gaat er van uit dat een jaar bestaat uit 12 maanden van elk 30 dagen. Deze functie is van belang bij berekeningen in het betalingsverkeer, waar deze regel vaak gehanteerd wordt.

: Typ in een willekeurige cel de volgende functie in: =Dagen360(“1-1-98”; “21-3-98”) Het antwoord is 80.


pag. 38

E X C E L

2 0 0 0

G E V O R D E R D

6.3.6 Afschrijvingsfuncties Excel kent 5 functies om afschrijvingen te berekenen. Welke functie u moet gebruiken hangt van de methode van afschrijven af. Functie

Afschrijvingsmethode

LIN.AFSCHR(kosten; restwaarde; duur)

lineair

DB(kosten; restwaarde; duur; termijn; [maand])

vaste degressieve afschrijving (fixed Declining Balance)

DDB(kosten; restwaarde; duur; [termijn]; [factor])

dubbele degressieve afschrijving (Double Declining Balance)

SYD(kosten; restwaarde; duur; termijn)

Sum of the Years Digits

VDB(kosten; restwaarde; duur; begin-periode; einde-periode; [factor]; [geen-omschakeling])

variabele degressieve afschrijving (Variable Declining Balance)

: Een investering van 25.000 Euro wordt in 8 jaar afgeschreven. De restwaarde aan het eind van de afschrijvingsperiode is f 2.000,-. Men wil nu de 5 verschillende afschrijvingsmethodes met elkaar vergelijken. Open het bestand AFSCHRIJVINGEN.XLS. In deze werkmap worden de afschrijvingsbedragen voor elk van de 8 jaren berekend wordt. Maak onderstaande grafiek waarin de waarde na afschrijving voor elk van de 5 methodes in de tijd wordt uitgezet.


pag. 39

E X C E L

2 0 0 0

G E V O R D E R D

6.3.7 Functies voor leningen Er zijn 7 functies die te maken hebben met berekeningen aan leningen en annuïteiten, te weten: Functie

Berekening van

TW(rente; aantal-termijnen; bet; [hw]; [type_getal])

toekomstige waarde investering.

HW(rente; aantal-termijnen; bet; [tw]; [type_getal])

huidige waarde van een investering.

BET(rente; aantal-termijnen; hw; [tw]; [type_getal]) periodieke betaling van een annuïteit. PBET(rente; aantal-termijnen; hw; [tw]; [type_getal])

afbetaling op hoofdsom.

IBET(rente; aantal-termijnen; hw; [tw]; [type_getal])

rentebetaling voor investering.

RENTE(aantal-termijnen; bet; hw; [tw]; [type_getal]; [schatting])

periodiek rentepercentage annuïteit.

NPER(rente; bet; hw; [tw]; [type_getal])

aantal termijnen van een investering.

Deze functies gebruiken vaak dezelfde argumenten. Vandaar dat hieronder deze argumenten kort toegelicht worden. argument

betekenis

rente

rentepercentage per termijn

aantal-termijnen totaal aantal betaaltermijnen bet

betaling die per termijn wordt gedaan (meestal hoofdsom + rente)

hw

huidige waarde van de totaalsom van toekomstige betalingen

tw

toekomstige waarde (het te bereiken saldo na laatste betaling)

type_getal

0 indien betalingen aan het eind van de termijn gedaan worden 1 indien betalingen aan het begin van de termijn gedaan worden

schatting

schatting van het rentepercentage

: Maak een model om de maandelijkse annuïteit van een hypotheek te berekenen. Ingevoerd moet kunnen worden: hypotheekbedrag, rentepercentage en het aantal jaren. Bereken voor elke maand naast de annuïteit (met functie BET) ook het aandeel van de rente (met IBET) en de aflossing (met PBET) en het restant van de lening. In het voorbeeld hierna kunt een mogelijke opzet zien. Cel C7 D7 E7 © Softwijs, oktober 2001

Inhoud =BET(Jaarrente/12;Aantal_Jaren*12;-Bedrag_Lening) =IBET(Jaarrente/12;B7;Aantal_Jaren*12;-Bedrag_Lening) =PBET(Jaarrente/12;B7;Aantal_Jaren*12;-Bedrag_Lening) pag. 40

E X C E L

2 0 0 0

G E V O R D E R D

: Verfraai het model zodanig dat in de rijen vanaf 7 alleen maar gegevens staan als dat nodig is. M.a.w. er moet rekening gehouden worden met het aantal jaren. Bestudeer daartoe onderstaande aanwijzingen voor de cellen A8, B8 en C8. Maak het werkblad verder op deze manier af. cel

inhoud

A8

=ALS(B8<>"";AFRONDEN.NAAR.BOVEN(B8/12;0);"")

B8

=ALS(Aantal_Jaren*12>B7;B7+1;"")

C8

=ALS(B8<>"";BET(Jaarrente/12;Aantal_Jaren*12;-Bedrag_Lening);"")

6.3.8 Vert.Zoeken Excel kent 15 functies in de categorie “Zoeken en verwijzen”. De verticale en horizontale zoekfunctie zijn daarvan wel de meest gebruikte. Syntax zoekwaarde tabelmatrix kolomindex_getal benaderen

VERT.ZOEKEN(zoekwaarde; tabelmatrix; kolomindex_getal; benaderen) De te zoeken waarde welke in de eerste kolom van de tabelmatrix moet staan. De gegevenstabel waarin gezocht moet worden. Het nummer van de kolom in tabelmatrix waaruit u de gezochte waarde wilt ophalen Optioneel. Logische waarde die aangeeft of wel of niet exact overeenkomende waarden gezocht moeten worden.


pag. 41

E X C E L

2 0 0 0

G E V O R D E R D

: Maak het volgende werkblad na. Gebruik hierbij de volgende namen: C2 Zoeknr B10:E17 ZoekLijst Formule in C4: =VERT.ZOEKEN(Zoeknr;ZoekLijst;2) Bedenk zelf de formules in C5 en C6. Controleer de werking door in C2 andere bestaande artikelnummers in te voeren.

: Wanneer in voorgaand voorbeeld een niet bestaand artikelnummer wordt ingevoerd, verschijnt de volgende foutmelding: #N/B. Vang deze foutmelding met een ALS functie af. Wanneer het artikelnummer niet bestaat moet de tekst “Niet gevonden” in de cellen C4:C6 verschijnen. Om dit voor elkaar te krijgen moet de functie ISFOUT als geneste functie binnen de ALS functie verschijnen. Raadpleeg de Help voor het gebruik van ISFOUT.


pag. 42

E X C E L

2 0 0 0

G E V O R D E R D

6.3.9 Horiz.Zoeken Het gebruik van deze functie lijkt erg veel op die van Vert.Zoeken. Bij de laatste functie alleen in de eerste kolom naar de te zoeken waarde gezocht. Echter bij de functie HORIZ.ZOEKEN wordt alleen in de eerste rij gezocht. Syntax

HORIZ.ZOEKEN(zoekwaarde; tabelmatrix; rij-index_getal;benaderen) zoekwaarde De te zoeken waarde welke in de eerste rij van de tabelmatrix moet staan. tabelmatrix De gegevenstabel waarin gezocht moet worden. rij-index_getal Het nummer van de rij in tabelmatrix waaruit u de gezochte waarde wilt ophalen benaderen Optioneel. Logische waarde die aangeeft of wel of niet exact overeenkomende waarden gezocht moeten worden. : Voer onderstaand werkblad in. Gebruik hierbij de volgende namen: C2 Zoeknaam C8:E11 Personeel Formule in C4: =HORIZ.ZOEKEN(Zoeknaam;Personeel;2) Bedenk zelf de formules in C5 en C6. Controleer de werking door in C2 andere bestaande artikelnummers in te voeren.


pag. 43

E X C E L

2 0 0 0

G E V O R D E R D

7. Matrixformules • Niveau

Matrixformules zijn formules die meerdere waarden als uitkomsten kunnen geven. Het resultaat is dan een rijtje of een kolommetje getallen. Ook kunnen matrixformules bewerkingen uitvoeren op data die in een tabel (rijen en kolommen) zijn gerangschikt. Om matrixformules te gebruiken moet u zelf van tevoren bedenken hoeveel uitkomsten er zijn en hoe deze uitkomsten zijn gerangschikt (rijtje of kolommetje).

7.1 Eenvoudige matrixformules : Maak het werkblad hiernaast na. Om in D2 de som van de getallen in A2 en A3 te bepalen kunt u een eenvoudige formule intypen: = A2+A3. Vervolgens kan deze formule naar beneden gekopieerd worden, zodat in D3 en D4 ook de som berekend wordt. U kunt dit ook met matrixformules berekenen. En wel als volgt. Omdat het resultaat bestaat uit een kolommetje van drie getallen selecteert u D2:D4. Typ nu in: =A2:A4+B2:B4. Druk NIET op ENTER maar op CTRL-SHIFT-ENTER.

Het antwoord verschijnt nu in de cellen D2:D4 en in het invoervenster is te zien dat accolades om de formule geplaatst zijn.


pag. 44

E X C E L

2 0 0 0

G E V O R D E R D

Klik achtereenvolgens op de cellen D2, D3 en D4. U zult steeds dezelfde formule zien: {=A2:A4+B2:B4} Let op

Accolades om matrixformules nooit intypen. Altijd aanbrengen door op CTRL-SHIFT-ENTER te drukken.

: Bereken met een matrixformule het product van de getallen in kolom A en kolom B.

: Maak onderstaande vermenigvuldiging m.b.v. een matrixformule.

: Voer de volgende matrixvermenigvuldiging uit en verklaar het resultaat.

Hier wordt een rijtje van drie getallen vermenigvuldigd met een enkele waarde. Excel breidt dan het tweede deel uit tot een rijtje van drie dezelfde getallen (4). De uitkomst is dus een rijtje van drie getallen.

7.2 Complexere matrixformules In de figuur hiernaast ziet u een fictief percentage stemmen voor de drie partijen die samen een coalitie vormen. Om te weten te komen hoeveel de coalitie er op vooruit of achteruit gaat moet u zowel Oud als Nieuw optellen en dan de resultaten van elkaar aftrekken.


pag. 45

E X C E L

2 0 0 0

G E V O R D E R D

totaal

Oud Nieuw Verschil 61,1% 62,7% 1,5%

Veel eenvoudiger gaat dit met een matrixformule.

: Voer de gegevens in een werkblad in. Bereken het resultaat van de coalitie met de volgende matrixformule: {=SOM(C2:C4-B2:B4)}

: Voer de gegevens hiernaast in een werkblad in. Bereken met een matrixformule het gemiddeld verschil tussen Toets 2 en Toets 1. In het volgende voorbeeld ziet u een zeer krachtige toepassing van de matrixformules.

: Voer de waarden in kolom A in. U wilt nu van de positieve en de negatieve waarden afzonderlijk het aantal en het gemiddelde uitrekenen. Met matrixformules gaat dit zeer eenvoudig.

De formule in D3 luidt: {=AANTAL(ALS($A$1:$A$10>0;$A$1:$A$10;""))} De ALS functie in deze formule controleert van elk element in het invoergebied (A1:A10) of deze groter dan 0 is. Zo ja, dan zet de ALS functie de waarde in het invoergebied. Zo nee, dan wordt een lege string in het invoergebied gezet. Het resultaat is een kolommetje getallen waarbij de negatieve waarden door een lege string vervangen zijn. Omdat de functie AANTAL lege strings overslaat worden alleen de positieve waarden geteld.


pag. 46

E X C E L

2 0 0 0

G E V O R D E R D

8. Databases Het is vrij gemakkelijk om in Excel eenvoudige databases te maken. • Niveau

Een database is een verzameling bij elkaar behorende gegevens. Een database bestaat uit rijen en kolommen. Zie het voorbeeld hierna: Voornaam Jan Piet

Achternaam Adres Jansen Janstraat 1 Pietersen Pietlaan 2

Postcode 1234 AB 5678 CD

Woonplaats Jansdorp Pietstad

velden

In de kolommen staan de gegevens die bijgehouden worden. Deze gegevens heten ook wel velden. De velden krijgen een naam. In het voorbeeld zijn 5 velden aanwezig: Voornaam, Achternaam, Postcode en Woonplaats.

records

De rijen heten ook wel records en bevatten de gegevens over één persoon. In het voorbeeld zijn 2 records afgebeeld.

8.1 Dataformulier Het maken van een database is vrij eenvoudig. Op de eerste rij typt u de veldnamen in. Op de rijen daarna de records.

: Typ in een nieuw werkblad onderstaande gegevens in. Formule F2:=0,175*E2 Formule G2: =D2*(E2+F2)

Voor het eerste record hebt u de veldwaarden rechtstreeks in de cellen ingetypt. Het is echter ook mogelijk hiervoor een invulformulier te nemen. tip

Om er voor te zorgen dat Excel kan bepalen welke velden berekend moeten worden is het nodig om het eerste record gewoon in te typen.

: Selecteer een willekeurige cel in rij 1 of 2. Kies Data -> Formulier. Er verschijnt een invulformulier: Op dit formulier staan aan de linkerkant de 8 veldnamen. Bij 6 van de 8 veldnamen staan invulvakken. Bij de velden “btw” en “totaalprijs” kunnen geen waarden worden ingevuld. Dat is ook wel logisch omdat in die velden een formule staat. De waarden in die velden worden berekend.


pag. 47

E X C E L

2 0 0 0

G E V O R D E R D

De volgende records kunnen het handigst via het invulformulier worden toegevoegd.

: Klik op de knop Nieuw. Vul de database aan met onderstaande gegevens. Maak hierbij gebruik van de Tab toets (of de muis) om naar het volgende veld te gaan. Gebruik niet de Enter toets, want hiermee gaat u naar het volgende record!

opmerking

De berekende velden worden niet onmiddellijk tijdens het intypen uitgerekend. Dat gebeurt pas als u naar een volgend record gaat of afsluit. U kunt door de database bladeren met de schuifbalk of met de knoppen “Vorige zoeken” of “Volgende zoeken”

8.2 Zoeken met dataformulier Met het dataformulier kunt u ook zoeken naar records die aan bepaalde criteria voldoen.

: Open het bestand PERSONEEL.XLS. Selecteer een willekeurige cel binnen deze database. Kies Data -> Formulier -> knop Criteria. Het formulier maakt plaats voor een lege versie.


pag. 48

E X C E L

2 0 0 0

G E V O R D E R D

: Vul het formulier in zoals hieronder is aangegeven. U gaat nu zoeken naar personen die werken op de afdeling Accounting van de divisie Printers.

: Klik een aantal keren op “Volgende zoeken” en “Vorige zoeken”. Er zijn 5 personen die aan deze criteria voldoen. Klik op Criteria en verwijder de aanwezige criteria. Typ bij achternaam in: Ja Er wordt nu gezocht naar personen waarvan de achternaam met Ja begint. Controleer dat 2 personen hieraan voldoen. Klik op Criteria en verwijder de aanwezige criteria. Typ bij achternaam in: *os U gaat nu zoeken naar personen bij wie in de achternaam de lettercombinatie "os" voorkomt. Klik op Criteria en verwijder de aanwezige criteria. Typ bij salaris in: >70000 U gaat nu zoeken naar personen met een salaris groter dan 70.000. Klik op Criteria en verwijder de aanwezige criteria. Typ bij begindatum in: >=1-1-92 U gaat nu zoeken naar personen die 1992 of later in dienst zijn getreden


pag. 49

E X C E L

2 0 0 0

G E V O R D E R D

9. Macro's Met macro's kunnen veel voorkomende handelingen geautomatiseerd worden. Door van macro's gebruik te maken kunt u efficiënter werken en hebt u minder kans om fouten te maken. In dit hoofdstuk leert u eenvoudige macro’s te maken.. 9.1 Wat is een macro? • niveau

Een taak die u in Microsoft Excel regelmatig uitvoert, kunt u met een macro automatiseren. Een macro bestaat uit een reeks opdrachten en functies die door één commando of druk op een knop kunnen worden uitgevoerd. Opdrachten die regelmatig terugkomen en die uit een aantal handelingen bestaan kunnen in een macro gezet worden. Door vervolgens de macro uit te voeren worden die handelingen achter elkaar uitgevoerd. De taal waarin de Excel macro's geschreven worden is Visual Basic for Applications, kortweg VBA. Alleen hierover kan al een compleet boekwerk geschreven worden. De opdrachten en functies van de macro worden in een Visual Basic module opgeslagen. Met de Visual Basic editor kunt u macro's bewerken, macro's van de ene module naar de andere kopiëren, macro's tussen verschillende werkmappen kopiëren, de modules waarin de macro's zijn opgeslagen een andere naam geven of de macro's een andere naam geven. Voordat u een macro opneemt of schrijft, moet u bedenken welke stappen en opdrachten u met de macro wilt uitvoeren. Als u tijdens de opname van de macro een fout maakt, worden ook de verbeteringen opgenomen. Elke nieuwe macro die u opneemt, wordt in een nieuwe module opgeslagen en aan de werkmap gekoppeld.

9.2 Macro's opnemen • niveau

Er zijn twee manieren om macro's te maken: 1.

U doet de handelingen die verricht moeten worden, voor. Deze handelingen worden tijdens de uitvoering opgeslagen. Dit heet het "opnemen" van macro's en is te vergelijken met het opnemen van muziek met een bandrecorder.

2.

U typt de opdrachten in de taal Visual Basic in. Dit is lastiger dan opnemen, maar u hebt dan wel de beschikking over opdrachten die niet opgenomen kunnen worden.

Alleen het opnemen van macro's wordt hier behandeld. Excel beschikt over een zogenaamde macrorecorder welke handelingen kan opnemen en in VBA code vertaalt.


pag. 50

E X C E L

2 0 0 0

G E V O R D E R D

Als voorbeeld zal een macro gemaakt worden welke een stuk tekst in een cel opmaakt in het lettertype Arial 16 punt, vet en rood gekleurd. Verder wordt de breedte van de kolom aangepast.

: Begin met een nieuw document. Selecteer een willekeurige cel. Zet hierin een stuk tekst, bijv. "Cursus Excel"en zorg dat deze cel geselecteerd blijft. Start de macrorecorder met Extra -> Macro -> Nieuwe macro opnemen… Het dialoogscherm “Macro opnemen” verschijnt:

Macronaam

Elke macro moet een naam hebben. Het eerste teken van de macronaam moet een letter zijn. Andere tekens kunnen letters, cijfers of het onderstrepingsteken zijn. Spaties zijn in een macronaam niet toegestaan. Het onderstrepingsteken kunt u als scheidingsteken tussen woorden gebruiken.

Sneltoets

In het vak Sneltoets kunt u een letter typen als u de macro wilt kunnen uitvoeren door op een toetsencombinatie te drukken. U kunt de combinatie CTRL+ letter (voor kleine letters) of CTRL+SHIFT+ letter (voor hoofdletters) gebruiken, waarbij letter een willekeurige toets op het toetsenbord is. De letter die u opgeeft, mag geen getal of een speciaal teken zijn. De gedefinieerde sneltoets overschrijft eventuele standaardtoetsencombinaties in Microsoft Excel zolang de werkmap waarin u de macro opslaat, is geopend.

Opslagplaats

Onder “Macro opslaan in” kunt u de locatie aangeven waar u de macro wilt opslaan. Standaard wordt de macro in de actieve werkmap opgeslagen. Als u wilt dat de macro altijd beschikbaar is wanneer u Microsoft Excel gebruikt, slaat u de macro op in de Persoonlijke macrowerkmap in de map XLStart.

Beschrijving

In het vak “Beschrijving” kunt u een beschrijving van de macro opgeven. De keuzes die u hier maakt kunnen later altijd gewijzigd worden. In het voorbeeld hierna wordt alleen de macronaam opgegeven. Sneltoets en beschrijving worden achteraf toegewezen of gewijzigd..


pag. 51

E X C E L

2 0 0 0

G E V O R D E R D

: Selecteer de voorgestelde macronaam Macro1. Typ als nieuwe naam in: TitelOpmaken Druk op OK. Excel zet nu de werkbalk “Opname stoppen” op het scherm. Alle handelingen (ook eventuele foute) die nu worden verricht, worden door de macrorecorder opgenomen. Door straks op de stopknop te klikken wordt het opnemen van de macro beëindigd. Als u tijdens het opnemen van de macro cellen selecteert, worden altijd dezelfde cellen geselecteerd, ongeacht de eerste cel die u hebt geselecteerd. In een macro worden altijd absolute celverwijzingen opgenomen. Als u de macro cellen ten opzichte van de positie van de actieve cel wilt laten selecteren, stelt u de macrorecorder in op relatieve celverwijzingen. Klik in dat geval op de werkbalk “Opname stoppen” op de knop Relatieve verwijzing . Deze instelling blijft gehandhaafd totdat u Excel beëindigt of nogmaals op Relatieve verwijzing klikt.

: Kies Opmaak -> Celeigenschappen -> tabblad Lettertype. Selecteer Arial, 16 punt, vet, kleur rood. Klik op OK. Kies Opmaak -> Kolom -> AutoAanpassen aan selectie. De opname van de macro is nu klaar. De macrorecorder moet nu nog uitgezet worden. Klik op de knop Macro opname stoppen. Het werkbalkje verdwijnt weer. Nu moet de macro nog getest worden.

: Selecteer een willekeurige cel, bijv. C3. Typ hierin een willekeurige tekst, bijv.: Cursus Windows. Zorg dat deze cel geselecteerd blijft. Kies Extra -> Macro -> Macro’s -> TitelOpmaken -> Uitvoeren. De macro wordt onmiddellijk uitgevoerd en maakt de cel op.

9.3 Wijziging van een macro • niveau

Een eenmaal gemaakte macro kan gewijzigd worden zonder de macro weer helemaal opnieuw op te nemen. De Visual Basic opdrachten van de macro kunnen direct bekeken en gewijzigd worden. Stel dat het lettertype niet 16 punt maar 18 punt moet zijn.

: Kies Extra -> Macro -> Macro’s -> TitelOpmaken -> Bewerken. U komt nu in het scherm van de Visual Basic Editor terecht. De code ziet er als volgt uit:


pag. 52

E X C E L

2 0 0 0

G E V O R D E R D

: Ga naar de opdracht waar de grootte van het lettertype wordt ingesteld en verander 16 in 18. Tevens kunnen de overbodige opdrachten verwijderd worden. Hiernaast is te zien hoe de gewijzigde code er nu uit ziet.

: Test deze gewijzigde macro in het werkblad uit. U gaat nu de beschrijving van de macro wijzigen en een sneltoets toewijzen.

: Kies Extra -> Macro -> Macro’s’ -> TitelOpmaken -> Opties. Vervang de Beschrijving door een zinvollere tekst, bijv. "Arial 18 vet en rood".

: Geef aan dat de macro ook via de sneltoets Ctrl + t uitgevoerd moet kunnen worden. Klik op OK en daarna op Sluiten. Test de werking van de sneltoets.


pag. 53

E X C E L

2 0 0 0

G E V O R D E R D

9.4 Starten van een macro • niveau

Een macro kan op een aantal manieren gestart worden. De meest gebruikte zijn: 1.

Via de menukeuzes Extra -> Macro -> Macro’s, daarna de naam van de macro selecteren en vervolgens de opdracht Uitvoeren geven.

2.

U kunt aan een macro een sneltoets toewijzen. Dit is een combinatie van de CTRL toets met een andere toets. Door deze toetscombinatie in te drukken wordt de macro gestart.

3.

U kunt de macro aan een opdrachtenknop toekennen. Na een klik op deze knop wordt de macro dan uitgevoerd.

De eerste twee mogelijkheden zijn hiervoor al aan bod gekomen. In de volgende paragraaf wordt de opdrachtknop behandeld.

9.5 Opdrachtknop maken • Niveau

De snelste methode om een macro te starten is door een opdrachtknop te tekenen en aan deze knop de macro te koppelen. Door een klik op de knop wordt de macro gestart. Het tekenen van een knop kan via de werkbalk Formulieren. Echter deze werkbalk wordt niet standaard door Excel op het scherm gezet. Daarvoor moet dus eerst een opdracht gegeven worden. Dit kan via het menu Beeld Werkbalken maar ook met het snelmenu.

: Klik met de rechtermuisknop op een lege ruimte binnen de bestaande werkbalken of op een lege ruimte in de plek waar de werkbalken staan. Het snelmenu voor de werkbalken verschijnt dan. Klik op Formulieren. knop

Op het werkblad verschijnt de werkbalk Formulieren.

: Klik op

en teken dan ergens op het werkblad een rechthoekje met de gewenste afmeting van de knop. Wanneer de knop getekend is verschijnt automatisch het dialoogvenster “Macro toewijzen aan object”. Selecteer de macro Titelopmaken en klik op OK.


pag. 54

E X C E L

2 0 0 0

G E V O R D E R D

Op de knop staat nu nog de standaardtekst “Knop 1”. In de volgende opdracht gaat u deze wijzigen.

: Om een geselecteerde knop zitten handgrepen. Is de knop nog geselecteerd dan kunt u gelijk verder. Zo niet, dan moet u eerst de knop selecteren door er met de rechtermuisknop op te klikken. Klik met de linkermuisknop op de tekst van de knop. Wijzig de tekst in “Titel Opmaken”. Pas zonodig het formaat van de knop aan door aan de handgrepen te slepen. Klik buiten de knop in het werkblad om de selectie op te heffen. Sluit de werkbalk “Formulieren”. Test de werking van de knop uit. Bewaar de map onder de naam MACRO.XLS op de diskette. Tip

Wanneer u een knop aan een werkblad toevoegt, is deze knop alleen beschikbaar als het werkblad actief is. Door een aangepaste werkbalkknop te maken kunt u altijd over de knop beschikken.


pag. 55

E X C E L

2 0 0 0

G E V O R D E R D

Opmerking

Een macro kan eenvoudig via het menu verwijderd worden. Echter de koppeling met sneltoetsen en knoppen blijft in stand. Deze koppeling wordt niet automatisch verwijderd en moet u zelf handmatig verwijderen voordat u de macro zelf verwijderd.


Formule naar waarde converteren Soms is het handig dat een formule omgezet wordt naar de uitkomst van die formule.

: Begin met een nieuwe werkmap. Zet in A1 een formule, bijv. =VANDAAG(). Selecteer cel A1. Kies Extra -> Macro -> Nieuwe macro opnemen. Neem als macronaam: FormuleNaarWaarde. Kies Bewerken -> Kopiëren. Selecteer een andere cel. Kies Bewerken -> Plakken speciaal. Selecteer de optie Waarden. Klik op OK. Druk Escape. (dit maakt de selectie ongedaan) Klik op de knop Opname stoppen. Test de macro door in het werkblad een formule te typen en dan de macro op deze formule uit te voeren. Opgave 2

Opmaak getallen Maak een macro met de naam ProcentOpmaak die een getal opmaakt als procenten met twee decimalen en het getal in de cel centreert.


pag. 56

Deel SOFTWIJS. Microsoft Windows en Office serie. Excel 2000 Gevorderd

Recommend Documents