Nieuwe werkmap aanmaken, gegevens invoeren, formules maken, werkblad beveiligen

Tips en trucs, zoals o.m.: Nieuwe werkmap aanmaken, gegevens invoeren, formules maken, werkblad beveiligen.

door Gerrit Tiemens, HCC afdeling Arnhem, Medewerker locatie Arnhem www.gtiemens.nl e-mail: [email protected]

Zevenaar, 17 januari 2004

Inhoudsopgave Inleiding............................................................................................................... 1 Werkmappen ........................................................................................................ 1 Een nieuwe werkmap .......................................................................................... 1 Een werkmap opslaan ......................................................................................... 2 Werkblad.............................................................................................................. 3 Gegevens invoeren ............................................................................................. 4 Getallen invoeren ............................................................................................... 4 Tekst invoeren ................................................................................................... 4 Datum en tijd invoeren........................................................................................ 5 Reeksen invoeren ............................................................................................... 5 Getallenreeks invoeren met de vulgreep ................................................................ 5 Doorvoeren van reeksen ...................................................................................... 5 Automatisch aanvullen ........................................................................................ 6 Gegevens bewerken ............................................................................................ 6 Inhoud van een cel wissen ................................................................................... 6 Inhoud van een celbereik wissen .......................................................................... 6 Gegevens kopiëren ............................................................................................. 6 Andere mogelijkheden voor kopiëren..................................................................... 7 Opmaak van de ene cel naar de andere ................................................................. 7 Gegevens verplaatsen ......................................................................................... 7 Formules .............................................................................................................. 7 Een eenvoudige formule ...................................................................................... 8 Operatoren ........................................................................................................ 8 Rekenkundige operatoren .................................................................................... 8 Vergelijkingoperatoren ........................................................................................ 9 Een voorbeeld van de ALS-functie......................................................................... 9 Functies ..............................................................................................................10 Een voorbeeld van een complexe functie ..............................................................10 Werkmap beveiligen .............................................................................................14 Voorwaardelijke opmaak .......................................................................................15 Gegevens valideren ..............................................................................................16

Excel

Inleiding Excel is een spreadsheet-programma, dat wil zeggen: een programma dat is bedoeld om berekeningen te maken, hoe uiteenlopend deze ook zijn en aanvullende informatie te halen uit de uitkomsten. Denk hierbij aan grafieken. Excel heeft een aantal overeenkomsten met Word, zoals de algemene indeling van de interface, het intensieve gebruik van de werkbalk, het afdrukvoorbeeld, het opnemen van macro’s en het invoegen van afbeeldingen. In deze cursus/demonstratie komen o.m. de volgende zaken aan de orde: • Hoe maak ik maak ik een nieuwe werkmap; • Hoe moet ik gegevens invoeren; • Hoe maak ik een formule; • Hoe beveilig ik een werkblad. Daarnaast ook aandacht voor wat ingewikkelder zaken als: • Voorwaardelijke opmaak en • Valideren van invoer. Conventies In deze cursus wordt een aantal conventies gebruikt. Wanneer u keuzes in het menu maakt, geeft het symbool Æ aan dat de eerste selectie tot een volgende leidt. De instructie om Bestand Æ Opslaan te kiezen, betekent dat Opslaan alleen verschijnt nadat u Bestand heeft gekozen. Als u een toetsencombinatie tegenkomt. Bijv. Ctrl-S, betekent dit dat u eerst de Ctrltoets moet indrukken (en deze ingedrukt houden) en daarna de toets die achter het ‘-‘ teken staat.

Werkmappen Een nieuwe werkmap Een werkmap is een verzameling bladen die samen één bestand vormen. Een Excelbestand kan veel bladen in één bestand opslaan. De beperkende factor is de hoeveelheid intern geheugen van uw computer. Als u Excel start, opent er automatisch een lege werkmap met de naam Map1. U vindt deze naam terug in de titelbalk.

F:\website\website_nieuw\Downloads\Excel voor beginners.doc

Blz.1

Excel

Figuur 1 Als Excel al is geactiveerd en u heeft nog geen bestand geopend, dan kunt u Bestand Æ Nieuw… selecteren of op de knop Nieuw klikken in de werkbalk Standaard om een nieuwe werkmap aan te maken. U kunt ook de toetsencombinatie Ctrl-N gebruiken.

Figuur 2 De werkmap opent standaard met 3 werkbladen. U kunt Excel zo instellen dat er tussen de 1 en 255 bladen worden geopend (ga naar: Extra Æ Opties… Æ tabblad Algemeen Æ Werkbladen in nieuwe werkmap). De tabs van de bladen zijn onder in het venster zichtbaar.

Een werkmap opslaan Sla uw werk regelmatig op om te voorkomen dat uw meest recente werk verloren gaat door een plotselinge stroomuitval of een systeemfout. Voer de volgende stappen uit om uw werk op te slaan: Kies Bestand Æ Opslaan of klik op de knop Opslaan in de werkbalk standaard. Het dialoogvenster Opslaan als opent.


Blz.2

Excel

Figuur 3 Geef de werkmap een naam. Deze kan maximaal uit 255 tekens bestaan, inclusief spaties. Mocht u vergeten het bestand een naam te geven, dan geeft Excel het de naam Map1. Excel vraagt alleen de eerste keer dat u een werkmap opslaat om een naam. Er is ook een toetsencombinatie om een werkmap op te slaan, namelijk Ctrl-S.

Werkblad Elk werkblad bestaat uit 256 kolommen die in alfabetische volgorde (van links naar rechts) en 65536 rijen die op volgorde van nummer (van boven naar beneden) worden weergegeven. Het kruispunt van een rij en een kolom wordt een cel genoemd. Om de plaats van een bepaalde cel binnen de spreadsheet accuraat aan te kunnen geven, zijn cellen van adressen voorzien. Elke cel bestaat uit de kolomnaam en het rijnummer. Deze combinatie van letter en nummer wordt de celreferentie genoemd, of het celadres bijv. A1. Een cel wordt geactiveerd door er op de klikken. De celreferentie van een geselecteerde cel verschijnt in het vak Naam links in de formulebalk.

Figuur 4 Om gegevens in een cel of cellen te kunnen plaatsen, moet u eerst een cel of een celbereik selecteren. Als u een Excel-werkblad opent, is cel A1 al actief. Een actieve cel heeft een donkere omranding. U kunt alleen gegevens in een geactiveerde cel invoeren. Het is dus belangrijk om te leren hoe u cellen en bereiken kunt selecteren. F:\website\website_nieuw\Downloads\Excel voor beginners.doc

Blz.3

Excel

De eenvoudigste manier om een cel te activeren is met de muiswijzer. U plaatst de muis in de gewenste cel en klikt op de linkermuistoets. Dubbelklikt u op de linkermuistoets, dan kunt u meteen gegevens invoeren. U ziet dan dat de formulebalk actief wordt. Om met gebruikmaking van het toetsenbord door het werkblad te bewegen, kunt u allerlei toetsen en toetsencombinaties gebruiken. De pijltjestoetsen brengen u één cel verder in de gewenste richting. PgUp en PgDn brengen u een volledig scherm verder. Ctrl-Home brengt u naar cel A1 en Ctrl-End brengt u naar het einde van het gebied waarin u gegevens heeft ingevoerd. Om gegevens met het toetsenbord te selecteren in een naburig bereik, gebuikt u de pijltjestoetsen om naar de eerste cel van uw bereik te gaan, houdt u vervolgens de Shifttoets ingedrukt en beweegt u in de richting van uw bereik. Als u de selectie heeft gemaakt, laat u Shift weer los. Met de verticale schuifbalk kunt u per rij door het werkblad schuiven en met de horizontale schuifbalk per kolom. Als u het schuifblokje aanklikt en versleept, verschijnt er Scherminfo naast de balk die het rij- of kolomnummer aangeeft. Als u de Shift-toets ingedrukt houdt terwijl u schuift, kunt u sneller door het werkblad bewegen.

Gegevens invoeren Als u dubbelklikt op een cel, wordt de formulebalk geactiveerd en begint er een cursor in de cel te knipperen. Terwijl u rechtstreeks tekens in de cel invoert, wordt uw handeling gelijktijdig weergegeven in de formulebalk. Er verschijnen taakknoppen in de formulebalk zodra er enige activiteit plaatsvindt. De eerste knop is de knop Functies die u helpt met de in Excel ingebouwde functies. De knop Functies verschijnt als er een is-gelijk teken staat aan het begin van de waarde, wat aangeeft dat u een berekening wilt invoeren. De volgende knop is de knop Annuleren, voorzien van een rood kruisje. Als u hierop klikt, wordt de invoer van de geactiveerde cel(len) geweigerd. De derde knop is de knop Invoeren, een groen vinkje. Als u hierop klikt, wordt de invoer van de formulebalk geaccepteerd. De laatste knop van de groep is de knop Formule bewerken. Als u op deze knop klikt als de actieve cel een berekening bevat, opent het dialoogvenster Formule bewerken met de argumenten en hun waarden voor de functie in de cel.

Getallen invoeren Uw (numerieke) invoer kan bestaan uit het hele scala aan getalswaarden: hele getallen, decimalen en getallen in wetenschappelijke notatie. Excel geeft een getal automatisch in de wetenschappelijke notatie weer als het getal te lang is om in zijn geheel in een cel te worden weergegeven. Ook kunt u hekjes (######) te zien krijgen als de invoer niet in een cel past. U kunt het getal weer lezen als u de bewuste kolom verbreedt. Excel past automatisch uw kolombreedte aan om getallen tot 11 cijfers te tonen. Bij meer dan 11 cijfers zet het programma de getallen in de wetenschappelijke notatie en past de kolombreedte dienovereenkomstig aan. Als u de kolombreedte echter zelf heeft aangepast, kan het zijn dat u dit opnieuw moet doen wanneer u grote getallen invoert.

Tekst invoeren Als u tekst in een cel wilt invoeren, kunt u getallen, letters of symbolen gebruiken. Hoewel de tekst uit getallen en uit andere tekens mag bestaan, zal Excel altijd alles niet als een zuiver getal of een datum herkenbaar is als tekst beschouwen. Als u getallen heeft die als tekst geïnterpreteerd wilt hebben (bijvoorbeeld een telefoonnummer), dan moet u een apostrof (‘) vóór het getal plaatsen. Zo laat u Excel weten dat de invoer niet als getal moet worden behandeld. Als u er niet zeker van bent of Excel de invoer als tekst of als getal ziet, onthoud dan dat getallen en data standaard rechts binnen de cel uitgelijnd worden en tekst links.


Blz.4

Excel

Datum en tijd invoeren Als u data en tijden invoert, zet Excel deze om in getallen uit een getallenreeks. De getallenreeks stelt de verstreken tijd voor vanaf 01-01-1900. Met deze getallen kan Excel berekeningen met data en tijden uitvoeren. Excel geeft de data en tijden echter op het werkblad weer in de door u gewenste vorm. Er is een invoegtoepassing beschikbaar op internet die het mogelijk maakt dat er ook gerekend kan worden met data voor 01-01-1900 (http://www.jwalk.com/ss/excel/files/xdate.htm).

Reeksen invoeren Soms zult u in Excel een reeks gegevens willen invoeren. Een reeks kan bestaan uit getallen, data of tekst. U kunt een reeks snel invoeren met de zogeheten vulgreep, het vierkantje rechts onder in de actieve cel. Een oefening: Voer de volgende stappen uit om met de vulgreep de maanden van het jaar achter elkaar in een rij te zetten. • • • • •

Selecteer cel A1; Typ in januari; Breng de muiswijzer naar de vulgreep zodat de muiswijzer in een kruis verandert; Sleep het kruis langs de volgende 11 kolommen naar kolom L; Laat de muisknop los, Excel heeft nu alle maanden van het jaar ingevoerd.

Getallenreeks invoeren met de vulgreep Op dezelfde wijze kunt u ook een reeks getallen invoeren die met een bepaalde waarde worden opgehoogd. Ook kunt u zo data in een reeks ophogen.

Doorvoeren van reeksen Als u over meer mogelijkheden wilt beschikken dan de vulgreep biedt, kunt u gebruik maken van Bewerken Æ Doorvoeren Æ Reeks ….

Figuur 5 Reeks bevat een Intervalwaarde en een Eindwaarde. Interval is hier de ophoogwaarde en Eindwaarde is de waarde waar Excel ophoudt. Bovendien ziet u de optie Trend met de best passende lijnen van lineaire groei van meetkundige curven voor groeireeksen. Ook is er een optie Type waarbij u kunt kiezen tussen Lineair, Groei, Datum en Automatisch doorvoeren. Als u met datumwaarden werkt, is er een aantal speciale opties beschikbaar om eenheden van een gewenste datum te voorzien. Met al deze opties kunt u waarden doorvoeren in een rij of een kolom.


Blz.5

Excel

Automatisch aanvullen Als de functie Automatisch aanvullen actief is (ga naar: Extra Æ Opties… Æ tabblad Bewerken Æ Automatisch aanvullen voor celwaarden activeren), vult Excel automatisch de letters aan van elke naam die u in meer dan één cel in dezelfde kolom typt. U hoeft maar te beginnen met typen van de invoer die u al eerder in dezelfde kolom heeft ingevoerd. Automatisch aanvullen herkent uw invoer en vult het voor u aan.

Gegevens bewerken Het is erg eenvoudig om gegevens in een werkblad te bewerken. U kunt uw invoer bewerken in de formulebalk of in de cel. U moet de cel die u wilt bewerken eerst activeren, waarna u één van de volgende methoden kunt gebruiken om de invoer te bewerken: • • •

Dubbelklik op de cel die de gegevens bevat die u wilt bewerken; Klik éénmaal in de formulebalk of Druk op toets F2

In de formulebalk verschijnen behalve de inhoud van de geselecteerde cel de knoppen Annuleren, Invoeren en Formule bewerken. U kunt de cursor overal in de cel of de formulebalk plaatsen en dan beginnen om de inhoud van de cel te bewerken. De eenvoudigste manieren om een cel te bewerken zijn: • • •

Backspace gebruiken. Deze toets verwijdert de tekens links van de cursor; Klikken en slepen binnen een cel om de te wijzigen tekens te markeren. Alles wat u typt zal het gemarkeerde gedeelte vervangen. Gewoon over de inhoud van de cel heen typen. Let op: de oude informatie gaat verloren zodra u de invoer heeft voltooid en op Enter drukt of op het groene vinkje in de formulebalk klikt.

Inhoud van een cel wissen Om de complete inhoud ven aan cel te wissen, klikt u op de te wissen cel en drukt u op Delete of Backspace. Als u voor Backspace heeft gekozen, drukt u op Enter om de lege cel te bevestigen. Om de inhoud te wissen zonder het toetsenbord te gebruiken, klikt u op de rechtermuisknop in de te wissen cel en kiest u uit het snelmenu Inhoud wissen. Het is niet nodig om op Enter te drukken.

Inhoud van een celbereik wissen Om de inhoud van een bereik te wissen, klikt u op de eerste cel van het bereik en brengt u de muiswijzer naar het midden van de cel. De muiswijzer verandert in een plusteken (+). Klik nogmaals op de cel en sleep de muis, zonder de muisknop los te laten, langs het bereik om alle cellen die u wilt wissen te markeren. Laat tot slot de muisknop los en druk op Delete om de inhoud te wissen.

Gegevens kopiëren Excel biedt veel mogelijkheden om gegevens in uw werkblad te kopiëren. De snelste manier is slepen en neerzetten. Dat gaat als volgt: 1. Selecteer een cel om te kopiëren; 2. Plaats de muis op de rand van deze cel. Deze verandert nu in een pijlwijzer; 3. Klik en houd Ctrl ingedrukt om Excel te laten weten dat u wilt kopiëren en niet verplaatsen. Er verschijnt een klein plusteken naast de muiswijzer. Ga verder terwijl u Ctrl en de muisknop ingedrukt houdt. 4. Sleep de muiswijzer over het werkblad naar de plaats waar u de inhoud van de cel wilt plakken. In een vierkantje wordt het exacte adres weergegeven van de cel waarin u gaat plakken. Merk ook op dat de muiswijzer een kader meesleept dat de te plakken cel voorstelt. 5. Laat de muisknop los en een kopie van de inhoud van de cel wordt in de gewenste cel geplakt. F:\website\website_nieuw\Downloads\Excel voor beginners.doc

Blz.6

Excel

Andere mogelijkheden voor kopiëren Er zijn meerdere manieren om Bewerken Æ Kopiëren in Excel op te roepen. De volgende stappen laten u zien hoe dit in z’n werk gaat. 1. Selecteer de cel of cellen die u wilt kopiëren; 2. Selecteer Bewerken Æ Kopiëren of druk op Ctrl-C. Ook kunt u op de knop Kopiëren in de werkbalk Standaard klikken. Rechtsklikken en Kopiëren kiezen uit het snelmenu is ook een mogelijkheid; 3. Selecteer de nieuwe cel om uw kopie in te plakken; 4. Selecteer Bewerken Æ Plakken of druk op Ctrl-V. U kunt ook op de knop Plakken in de Standaard werkbalk klikken of rechtsklikken en Plakken kiezen uit het snelmenu.

Opmaak van de ene cel naar de andere Als u de opmaak van een bepaalde cel ook op een andere cel of bereik wilt toepassen, dan kunt u dit doen via de knop Opmaak kopiëren/plakken.

Figuur 6 1. Selecteer een cel met de te kopiëren opmaak; 2. Klik op de knop Opmaak kopiëren/plakken. Naast de muiswijzer verschijnt nu de Opmaakkwast; 3. Klik op de cel (of selecteer een bereik) die u wilt opmaken en de opmaak wordt naar deze cel of bereik gekopieerd. Als u dubbelklikt op de knop Opmaak kopiëren/plakken, kunt u de opmaak meerdere keren plakken.

Gegevens verplaatsen Het verplaatsen van gegevens gaat op dezelfde manier als het kopiëren van gegevens. Voer de volgende stappen uit om gegevens te verplaatsen door verslepen: 1. Selecteer een te verplaatsen cel of bereik; 2. Zet de muis op de rand van deze cel zodat de muiswijzer in een pijl verandert; 3. Houd de muisknop ingedrukt en sleep de pijlwijzer over het werkblad naar de gewenste positie. De pijlwijzer sleept nu een kader mee; de te verplaatsen cel of cellen; 4. Laat de muisknop los. De inhoud van de cel wordt op de nieuwe plaats geplakt. Verplaatsen met het menu Bewerken gaat als volgt: 1. 2. 3. 4.

Selecteer de cel of cellen die u wilt verplaatsen; Kies Bewerken Æ Knippen of druk op Ctrl-X; Selecteer de nieuwe cel die uw gegevens gaat ontvangen; Kies Bewerken Æ Plakken of druk op Ctrl-V.

Formules Een formule is een vergelijking waarmee u bewerkingen uitvoert op gegevens in een werkblad. Met formules kunt u rekenkundige bewerkingen uitvoeren zoals optellen of F:\website\website_nieuw\Downloads\Excel voor beginners.doc

Blz.7

Excel

vermenigvuldigen, maar ook waarden vergelijken of tekst combineren. Formules kunnen naar andere cellen in hetzelfde werkblad verwijzen, naar cellen op andere werkbladen in dezelfde werkmap of naar cellen op bladen in andere werkmappen.

Een eenvoudige formule Stel u verdient € 30.000,-- en er is een salarisverhoging van 2,5% aangekondigd. Wat levert dit op? De formule in woorden is: Basissalaris * Percentage Opslag (de asterisk geeft de functie vermenigvuldigen weer). Voer de volgende stappen uit om zo’n formule in Excel weer te geven: 1. In cel A2 voert u 30.000,-- in, de hoogte van het basissalaris; 2. In cel B2 voert u 2,5 % in, het percentage van de opslag

Figuur 7 3. Nu klikt u op cel C2 en typt u een gelijkteken (=). Hiermee vertelt u Excel dat er een formule volgt. Nu kunt u beginnen de hiervoor genoemde zakelijke formule op te stellen door de adressen of locaties (cellen) met de getallen aan te geven en de rekenkundige bewerkingen in te voeren; 4. Klik op cel A2; 5. Gebruik een asterisk om aan te geven dat het om een vermenigvuldiging gaat. 6. Klik op B2. De formulebalk boven in het scherm geeft de celadressen en de asterisk weer; 7. Klik op het groene vinkje of druk op Enter. De cel geeft de uitkomst van deze eenvoudige zakelijke formule weer en de formulebalk geeft de celadressen en berekeningen weer die bij het oplossen van het vraagstuk zijn gebruikt.

Operatoren Met operatoren geeft u het type berekening op dat u met de elementen in een formule wilt uitvoeren. Microsoft Excel kent vier verschillende typen operatoren voor berekeningen: rekenkundige operatoren, vergelijkingsoperatoren, tekstoperatoren en verwijzingsoperatoren.

Rekenkundige operatoren Een operator is een special symbool dat aangeeft welke actie er met een reeks getallen moet worden ondernomen. De rekenkundige basisbewerkingen optellen, aftrekken, vermenigvuldigen en delen zijn de meest elementaire middelen om informatie te meten. Rekenkundige operator + (plusteken) - (minteken) * (sterretje) / (slash) % (procentteken) ^ (caret)

Bewerking Optellen Aftrekken of negatief maken Vermenigvuldigen Delen Percentage berekenen Machtsverheffen

Tabel 1 In Excel kunt u maximaal 255 tekens in een enkele cel invoeren. Hierdoor kunnen berekeningen of formules worden gebruikt met meerdere getallen en bewerkingen. De volgorde waarin u de rekenkundige bewerkingen invoert, is bepalend voor de uitkomst.


Blz.8

Excel

Een andere volgorde geeft een andere uitkomst, zelfs wanneer u precies dezelfde getallen en bewerkingen gebruikt. Alle spreadsheet-programma’s voeren rekenkundige berekeningen in een bepaalde volgorde uit. Deze rekenvolgorde wordt voorrang genoemd. Excel past de rekenkundige standaard regels toe die enkele berekeningen voorrang geven op andere. Als Excel meer dan één bewerking in een formule of berekening aantreft, worden deze in de volgende volgorde uitgevoerd: 1. 2. 3. 4.

Items tussen haakjes; Machtsverheffingen; Vermenigvuldigingen en delingen; Optellingen en aftrekkingen.

Om de gebruikelijke volgorde van de berekeningen te veranderen, maakt u gebruik van haakjes. U zet haakjes om die gedeelten van de berekening die u als eerste uitgevoerd wil hebben. Excel past dan de rekenvolgorde aan en voert de berekeningen binnen de haakjes als eerste uit (zie tabel 1). Als u meerdere paren haakjes moet gebruiken, begint Excel met de binnenste paar en werkt dan naar buiten. = 22+34*55 (22+34)*55 =

803 1958

Tabel 2

Vergelijkingoperatoren Vergelijkingsoperatoren worden gebruikt om waarden te vergelijken. Deze bewerkingen worden ook vaak logische operatoren genoemd aangezien de uitkomst in de cel altijd Waar of Onwaar is. Bekijkt u de berekening = 4 > 5. Deze logische operator stelt nu: 4 is groter dan 5. Het antwoord van Excel luidt uiteraard: dit is Onwaar (in het Engels False). Verandert u de berekening in = 4 < 5, dan zal het antwoord van Excel luiden: dit is Waar (in het Engels True). Onderstaand een overzicht van de vergelijkingsoperatoren: Vergelijkingoperator = > < >= <= <>

Bewerking Is gelijk aan Groter dan Kleiner dan Groter dan of gelijk aan Kleiner dan of gelijk aan Ongelijk aan

Tabel 3 Vergelijkingoperatoren worden vaak in de = ALS-functie gebruikt. Bekijk het volgende voorbeeld: = ALS(A1>7000;”Over budget”;”OK”) De = ALS-functie draagt het programma op om een getal of expressie te vergelijken met een ander getal of een andere expressie. In dit voorbeeld moet Excel bekijken of het getal in cel A1 groter is dan 7000. Als de vergelijking is uitgevoerd, geeft Excel “Over budget” aan als de uitkomst waar is of “OK” als de uitkomst onwaar is.

Een voorbeeld van de ALS-functie Selecteer het voorbeeld door op het werkblad te dubbelklikken. Dan kunt u het werkblad eventueel bewerken en de formules bekijken. Klik daarna ergens buiten werkblad om Excel weer af te sluiten.


Blz.9

Excel

Geslacht Deel m m vrouw v man m vrouw v vrouw v m m man m m m m m vrouw v m m m m m m m m m m man m Voorbeeld 1

Voorletter(s) G. M. J.A.G.M. J.A.M. G. L.J. J. H. T. N. P. C. N. H. G.T. J.

Voorvoegsel van van

van van den

Achternaam Tiemens Herk Hoenderloo Peters Riphagen Tiemens Kip Everts Hartgers Bakker Marks Elfring Westerweel Zwam Hazeleger Zee

Naam_samen G. Tiemens M. van Herk J.A.G.M. van Hoenderloo J.A.M. Peters G. Riphagen L.J. Tiemens J. Kip H. Everts T. Hartgers N. Bakker P. Marks C. Elfring N. Westerweel H. van Zwam G.T. Hazeleger J. van den Zee

Aanhef Geachte heer Geachte mevrouw Geachte heer Geachte mevrouw Geachte mevrouw Geachte heer Geachte heer Geachte heer Geachte heer Geachte mevrouw Geachte heer Geachte heer Geachte heer Geachte heer Geachte heer Geachte heer

Functies Als u complexe formules wilt maken, beschikt u over tientallen voorgedefinieerde functies. Uiteraard kunt u een functie en de argumenten ervan rechtstreeks invoeren als u de exacte syntaxis ervan kent. Excel biedt echter een efficiënt hulpmiddel waarmee u zoektochten voorkomt: de wizard Functies.

Een voorbeeld van een complexe functie Hieronder wordt uiteengezet hoe u de aflossing van een lening berekent. 1. Voor deze berekening zijn 4 gegevens nodig: het geleende bedrag (de beginsom), het rentepercentage, het aantal termijnen per periode en het aantal perioden. Deze worden ingevoerd in cel F3 tot en met F6. De basisperiode is gewoonlijk de annuïteit (jaar). De aflossingstabel bestaat uit 6 kolommen: de vervaldatum, het verschuldigde bedrag aan het begin van de termijn, de hoogte van de betaling (aflossing, rente en totaalbedrag) en het verschuldigde bedrag aan het einde van de termijn. 2. Om de kolom Datum in te vullen, typt u bijvoorbeeld 1/2004 in de eerste cel (B10) eb sleept u het zwarte vierkantje rechts onder in de cel omlaag om een automatische reeks te maken. Selecteer dit bereik en pas er de gewenste opmaak op toe met de opdrachten Opmaak Æ Celeigenschappen. 3. De eerste cel in de kolom Beginsom bevat het oorspronkelijke bedrag van de schuld ofwel de formule =F3. De cel eronder bevat de restsom na de eerste aflossing ofwel =G10. Onder de rest van deze kolom in te vullen, kopieert u C11 (Ctrl-C) en selecteert u de cellen C12 tot en met C24 (of meer al u de volledige tabel wilt maken). Druk vervolgens op Enter. 4. Op dezelfde manier bepaalt u de restsom: de oorspronkelijke lening verminderd met de aflossing. De formule die u in G10 plaatst, luidt derhalve: =C10-D10. Deze formule kopieert u vervolgens naar beneden in de kolom. 5. U gebruikt de functies van Excel om het maandelijkse bedrag en de rente voor de termijnen te bepalen. De aflossing is dan het verschil tussen deze 2 waarden. De


Blz.10

Excel

formule in D10 luidt dus: =F10-E10. U vult ook hier de rest van de kolom in door deze formule te kopiëren. 6. U bent nu eindelijk bij de functies aangekomen. Om de betaling te berekenen, plaatst u de cursor in cel F10. De knop Fx in de werkbalk Standaard opent het dialoogvenster Functie plakken. Eerst kiest u een categorie functies, in dit geval Financieel. Vervolgens kiest u de gewenste functie in de lijst rechts, in dit geval BET. De syntaxis wordt onder in het venster weergegeven.

Figuur 8


Blz.11

Excel

7. Als u op OK klikt, kunt de parameters van de functie instellen. Figuur 9 laat u zien wat u hier moet invoeren. U voert het volgende in: het rentepercentage per termijn (het rentepercentage gedeeld door het aantal termijnen), het totaal aantal aflossingen (het aantal aflossingen per periode vermenigvuldigd) en de hoofdsom van de lening. Al deze gegevens zijn afkomstig uit de cellen F3 tot en met F6. Aangezien deze formule naar de rest van de tabel moet worden gekopieerd, moet u absolute celadressen gebruiken. U kunt deze rechtstreeks invoeren of op de knop rechts van de invoervakken gebruiken en op de gewenste cel klikken. Klik op F4 om de dollartekens te plaatsen en vervolg de selectie. De hoofdsom moet worden voorafgegaan door een minteken, omdat de functie berekent wat er ‘ontbreekt’ om alles terug te betalen.

Figuur 9 8. Figuur 10 toont u de parameters die u moet invoeren om de hoogte van het rentebedrag in E10 te berekenen met behulp van de functie IBET. Deze functie heeft een aanvullend argument dat het aantal termijnen aangeeft, in dit geval één. Bovendien moet de hoogte van de restsom worden overgebracht naar kolom Hoofdsom (daarom is C10 een relatieve verwijzing), zodat de rente elke maand wordt aangepast.


Blz.12

Excel

Figuur 10


Blz.13

Excel

9. Wanneer u de cellen E10 en F10 naar de rest van deze kolommen heeft gekopieerd, ziet u de aflossingstabel er als volgt uit:

Berekening aflossing rente Bedrag van de lening Jaarlijkse rentepercentage Aantal jaarlijkse aflossingen Aantal annuiteiten

€

50.000,00 10% 12 3

Aflossingtabel Datum Beginsom Aflossing Rente Betaling Restsom jan-04 € 50.000,00 € 1.196,69 € 416,67 € 1.613,36 € 48.803,31 feb-04 € 48.803,31 € 1.206,67 € 406,69 € 1.613,36 € 47.596,64 mrt-04 € 47.596,64 € 1.216,72 € 396,64 € 1.613,36 € 46.379,92 apr-04 € 46.379,92 € 1.226,86 € 386,50 € 1.613,36 € 45.153,06 mei-04 € 45.153,06 € 1.237,08 € 376,28 € 1.613,36 € 43.915,98 jun-04 € 43.915,98 € 1.247,39 € 365,97 € 1.613,36 € 42.668,58 jul-04 € 42.668,58 € 1.257,79 € 355,57 € 1.613,36 € 41.410,80 aug-04 € 41.410,80 € 1.268,27 € 345,09 € 1.613,36 € 40.142,53 sep-04 € 40.142,53 € 1.278,84 € 334,52 € 1.613,36 € 38.863,69 okt-04 € 38.863,69 € 1.289,50 € 323,86 € 1.613,36 € 37.574,19 nov-04 € 37.574,19 € 1.300,24 € 313,12 € 1.613,36 € 36.273,95 dec-04 € 36.273,95 € 1.311,08 € 302,28 € 1.613,36 € 34.962,88 jan-05 € 34.962,88 € 1.322,00 € 291,36 € 1.613,36 € 33.640,87 feb-05 € 33.640,87 € 1.333,02 € 280,34 € 1.613,36 € 32.307,86 mrt-05 € 32.307,86 € 1.344,13 € 269,23 € 1.613,36 € 30.963,73 apr-05 € 30.963,73 € 1.355,33 € 258,03 € 1.613,36 € 29.608,40 mei-05 € 29.608,40 € 1.366,62 € 246,74 € 1.613,36 € 28.241,78 jun-05 € 28.241,78 € 1.378,01 € 235,35 € 1.613,36 € 26.863,77 jul-05 € 26.863,77 € 1.389,49 € 223,86 € 1.613,36 € 25.474,27 aug-05 € 25.474,27 € 1.401,07 € 212,29 € 1.613,36 € 24.073,20 sep-05 € 24.073,20 € 1.412,75 € 200,61 € 1.613,36 € 22.660,45 okt-05 € 22.660,45 € 1.424,52 € 188,84 € 1.613,36 € 21.235,93 nov-05 € 21.235,93 € 1.436,39 € 176,97 € 1.613,36 € 19.799,53 dec-05 € 19.799,53 € 1.448,36 € 165,00 € 1.613,36 € 18.351,17 jan-06 € 18.351,17 € 1.460,43 € 152,93 € 1.613,36 € 16.890,74 feb-06 € 16.890,74 € 1.472,60 € 140,76 € 1.613,36 € 15.418,13 mrt-06 € 15.418,13 € 1.484,87 € 128,48 € 1.613,36 € 13.933,26 apr-06 € 13.933,26 € 1.497,25 € 116,11 € 1.613,36 € 12.436,01 mei-06 € 12.436,01 € 1.509,73 € 103,63 € 1.613,36 € 10.926,28 jun-06 € 10.926,28 € 1.522,31 € 91,05 € 1.613,36 € 9.403,98 jul-06 € 9.403,98 € 1.534,99 € 78,37 € 1.613,36 € 7.868,98 aug-06 € 7.868,98 € 1.547,78 € 65,57 € 1.613,36 € 6.321,20 sep-06 € 6.321,20 € 1.560,68 € 52,68 € 1.613,36 € 4.760,52 okt-06 € 4.760,52 € 1.573,69 € 39,67 € 1.613,36 € 3.186,83 nov-06 € 3.186,83 € 1.586,80 € 26,56 € 1.613,36 € 1.600,03 dec-06 € 1.600,03 € 1.600,03 € 13,33 € 1.613,36 € 0,00Voorbeeld renteberekening

Werkmap beveiligen


Blz.14

Excel

In de meeste werkbladen moeten sommige cellen kunnen worden gewijzigd, terwijl andere cellen onveranderd moeten blijven (bijvoorbeeld cellen waarin een formule is opgenomen). Om te voorkomen dat cellen per ongeluk worden gewijzigd, doet u het volgde: 1. Selecteer het hele werkblad door te klikken op het snijpunt van de rijen kolomkoppen (boven rij 1 en links van kolom A) of druk op Ctrl-A. 2. Kies Opmaak Æ Celeigenschappen… en klik op het tabblad Bescherming. Schakel de optie Geblokkeerd in en klik op OK. 3. Selecteer vervolgens de cellen de wél mogen worden gewijzigd. 4. Herhaal stap 2, maar verwijder ditmaal het vinkje uit het vakje Geblokkeerd. 5. Kies vervolgens de opdracht Extra Æ Beveiliging Æ Blad beveiligen…. Stel eventueel een wachtwoord in. Vanaf dit moment zijn alleen de niet-geblokkeerde cellen toegankelijk. Bovendien is de opdracht Celeigenschappen niet meer bereikbaar. Als u de vorige situatie wilt herstellen, kiest u Extra Æ Beveiliging Æ Beveiliging blad opheffen…. Typ zonodig het wachtwoord in dat u heeft ingesteld in stap 5.

Voorwaardelijke opmaak Excel kan uw gegevens bijhouden en u waarschuwen wanneer de ingevoerde waarden een bepaalde reeks bereiken of een verzameling overschrijden. Voorwaardelijke opmaak past in zo’n geval de opmaal van een cel aan om uw aandacht te vestigen op de inhoud van deze cel. Voer de volgende stappen uit om voorwaardelijke opmaak toe te passen: 1. Typ de volgende waarden in de aangegeven cellen: 3000 in cel A1, 2500 in cel A2, 3750 in cel A3, 2800 in cel A4 en 1900 in cel A5. 2. Voer in cel A6 de formule =SOM(A1:A5) in. Deze telt de inhoud van de cellen A1 tot en met A5 bij elkaar op. 3. Activeer de cel die u wilt bewerken, in dit geval cel A6. Normaliter zult u een cel willen bewaken die een formule bevat, maar u kunt ook een waarde laten bewaken. 4. Kies Opmaak --> Voorwaardelijke opmaak….

Figuur 11 5. Er verschijnt een dialoogvenster waarin u de voorwaarden voor de opmaak van de cel kunt opgeven. In ons rapport willen we gewaarschuwd worden zodra de totale verkopen onder de 12000 zakken. Bij voorwaarde 1 kiest u Celwaarde is uit het selectievak. 6. Kies kleiner dan in het volgende selectievakje. 7. Voer het getal 12000 in het derde selectievakje. Nu de voorwaarde is gedefinieerd, klikt u op de knop Opmaak… om de gewenste opmaak te definiëren. F:\website\website_nieuw\Downloads\Excel voor beginners.doc

Blz.15

Excel

8. Klik op het tabblad Patronen en kies de kleur rood uit het palet Celarcering 9. Klik op OK en daarna op OK in het dialoogvenster Voorwaardelijke opmaak. U ziet nog niets aan cel A6 omdat de waarde momenteel groter is dan 12000. 10. Test de voorwaarde door de inhoud van cel A1 te wijzigen in 30. De totale waarde voldoet nu aan de voorwaarde zodat cel A6 nu rood wordt.

Gegevens valideren Excel heeft een functie waarmee u de waarden in cellen kunt laten controleren, ook wel valideren genoemd. Daarnaast is het mogelijk om extra informatie te geven en kunt u een foutmelding laten tonen. Ga naar Data Æ Valideren….

Figuur 12 In het tabblad Instellingen geeft u het bereik van de waarden aan, bijvoorbeeld dat de waarde in de betreffende cel tussen de 1 en de 100 moet liggen.


Blz.16

Excel

Figuur 13 In het tabblad Invoerbericht kunt een tekst opnemen die getoond moet worden zodra de cel is geselecteerd. U moet dan wel het vinkje plaatsen voor Invoerbericht weergeven als de cel is geselecteerd.

Figuur 14 In tabblad Foutmelding kunt u een foutbericht laten tonen bij een ongeldige invoer. Daarnaast kunt u een stijl selecteren. Het meest voor de hand liggend is Stoppen. Op deze wijze sluit u foutieve invoer helemaal uit.


Blz.17

Nieuwe werkmap aanmaken, gegevens invoeren, formules maken, werkblad beveiligen

Recommend Documents