Cursus Excel ‘Gevorderd’ 17 april 2007 Voorkennis



Dovnload 68.56 Kb.
Datum16.08.2016
Grootte68.56 Kb.
Cursus Excel

Gevorderd’



17 april 2007




Voorkennis:


Doel:

  • Leren om formules toe te passen en om zo sneller offertes, prijslijsten en bestellijsten te maken


Opzet:

  • Op deze en de volgende pagina’s worden puntsgewijs een aantal ‘technieken’ die gebruikt worden bij het opmaken. Ik heb bij ieder punt een aantal regels gemaakt, waar je eventuele aantekeningen kunt neerschrijven



1.) Functies

  • Met Excel kun je functies (formules) uitvoeren. Je kunt deze terugvinden op twee plaatsen in de werkbalk. Zie pijltjes.





  • Bij het rechterpijltje vind je de meest voorkomende formules:

  • Som: optellen van cellen. Hetzelfde kan worden bereikt door in de formulebalk te typen:

=som(
en dan de cellen te selecteren die achter het haakje gezet moeten worden.




  • Gemiddelde: berekenen van het gemiddelde. Kan ook met formule:

= gemiddelde(geselecteerde cellen)




  • Aantal: Excel telt het aantal cellen op dat niet leeg is. Excel kijkt daarbij niet naar het getal dat in de cel staat, maar naar het aantal cellen dat inhoud bevat.

=aantal(geselecteerde cellen)




  • Max.: Excel zoekt voor de geselecteerde cellen de cel met de maximale waarde.

=max(geselecteerde cellen)




  • Min.: Excel zoekt voor de geselecteerde cellen de cel met de minimale waarde.

=min(geselecteerde cellen)




  • Dan over het pijltje aan de linkerkant. Wanneer dat je op fx klikt, dan opent een nieuw scherm. In dit scherm kom je een hele reeks aan formules tegen.


In bovenstaand scherm kun je door het intypen van een trefwoord zoeken naar de gewenste formule. Ook kun je per categorie zoeken. Hierboven is de categorie ‘laatst gebruikt’ geselecteerd. Je zult zien dat wanneer je van categorie verandert, dat er in de lijst daaronder nieuwe formules verschijnen. Onder de lijst staat een voorbeeld en uitleg van de formule. Klik op OK als je de gewenste formule hebt gevonden.


In het geval je de formule ‘som’ selecteert, dan opent een volgend scherm (zie volgende pagina). In de linkerbovenhoek staat ter herinnering de formule die je hebt geselecteerd. In ons geval is dat ‘som’. Je kunt nu de cellen, die je wilt optellen, selecteren door op het icoontje bij het pijltje te klikken. Het geopende schermpje zal nu veranderen in een scherm met 1 regel en een knipperende cursor. Trek je hier niet van aan. Sleep met de muis over de cellen die je gaat optellen, en druk op enter wanneer dat je klaar bent. Je komt nu terug in onderstaand scherm. Mocht je nog meer cellen willen optellen, klik dan op het icoontje achter de regel waar ‘getal 2’ achter staat.
Onderin staat het resultaat van de formule. Dit is de uitkomst van de formule. Nadat je weer op OK hebt geklikt, wordt het resultaat van de formule ook weergegeven in de cel. Als je vervolgens met de muis op de betreffende cel klikt, dan zie je de formule terug in de formulebalk. Ter herinnering: formules beginnen altijd met = en worden gevolgd door de naam van de formule (hier: SOM) en de geselecteerde cellen tussen haakjes. Onder het plaatje vind je een aantal formules met uitleg.




  • Afronden( ): er zijn een aantal manieren om getallen af te ronden. Zo kun je afronden naar beneden of naar boven.




  • Aantal.als( ): stel je hebt een lijst met klanten die wel of niet betaald hebben. Je hebt deze debiteuren aangegeven met “ja” en “nee”. Je kunt dan met deze formule het aantal nee’s of ja’s optellen. Vul hiervoor ten eerste het bereik in waarover je dit wilt berekenen, en geef vervolgens het criterium (“ja” of “nee”) in.




  • Vandaag( ): door deze formule te gebruiken zal Excel automatisch de datum van de huidige dag aangeven. Je hoeft hiervoor geen cellen te selecteren. Je kunt de opmaak van de datum veranderen door met de rechtermuisknop op celeigenschappen te klikken. Voor meer informatie, zie ook basis cursus.




  • Somproduct( ): hiermee vermenigvuldig je twee geselecteerde gedeelten. Stel je hebt een kolom met prijzen en een kolom met de voorraad. Door de somproduct formule toe te passen kun je meteen de totale waarde van de voorraad berekenen. Excel vraagt je om een matrix 1 en een matrix 2 aan te geven. In Matrix 1 selecteer je dan de cellen met de prijzen, in matrix 2 de cellen met de voorraad. De uitkomst is de totale waarde van de omzet.









2.) De functie als

  • Met de functie als kun je als het ware met Excel programmeren. Je kunt Excel vertellen dat als iets waar is, dat het dan iets moet uitvoeren, en als dat niet het geval is, dan moet het iets anders doen.

De formule: = als (cel = getal; doe dit; anders doe dat)


Als je het zou oplezen, dan staat er dit: = als een bepaalde cel gelijk is aan een getal, dan doe je dit, als dat niet zo is, dan doe je dat
Meer concreet, een voorbeeld:


 

B

C

D

 

44

5

50

ALS(B44=6;100;50)

 

45

6

100

ALS(B45=6;100;50)

 

46

7

50

ALS(B46=6;100;50)

 

47

8

50

ALS(B47=6;100;50)

 

48

10

50

ALS(B48=6;100;50)

 

In kolom D vind je de formule terug, die doormiddel van een = is ingegeven in kolom C. De getallen 50 en 100 zijn verzonnen en niet gerelateerd aan de getallen in kolom B. Je ziet nu dat we Excel hebben verteld dat als de inhoud van cel B44 gelijk is aan het getal 6, dat Excel dat het getal 100 moet neerzetten. Als dat niet waar is, dan moet het getal 50 worden weergegeven.


Je kunt hetzelfde ook doen met getallen die groter zijn / kleiner zijn dan een bepaald getal. Gebruik hiervoor i.p.v. de = de tekens < = (kleiner gelijk) en > = (groter gelijk). Een voorbeeld met kleiner gelijk:


 

B

C

D

 

44

5

100

ALS(B44<=7;100;50)

 

45

6

100

ALS(B45<=7;100;50)

 

46

7

100

ALS(B46<=7;100;50)

 

47

8

50

ALS(B47<=7;100;50)

 

48

10

50

ALS(B48<=7;100;50)

 

Alle getallen die nu kleiner gelijk zijn aan 7 worden weergegeven met het getal 100. Overigens: door de = weg te laten kun je ook laten uitrekenen welke cellen kleiner zijn dan 7. In dat geval zal de waarde van cel C46 veranderen in 50! De formules in de D kolom zouden dan worden aangepast in de vorm: ALS(B44<7;100;50).




  • De functie als en formules. In het vorige voorbeeld hebben we Excel laten bekijken hoe te handelen als een cel een bepaalde waarde bevat. Nu laten we Excel bekijken wat te doen als we twee waarden bij elkaar optellen. Een voorbeeld:



 

B

C

D

E

44

10

50

25000

ALS(B44+C44<=100;25000;50000)

45

20

60

25000

ALS(B45+C45<=100;25000;50000)

46

30

70

25000

ALS(B46+C46<=100;25000;50000)

47

40

80

50000

ALS(B47+C47<=100;25000;50000)

48

50

90

50000

ALS(B48+C48<=100;25000;50000)

De getallen 25000 en 50000 zijn verzonnen. Excel telt nu eerst per rij de getallen in kolom B en C op, en bekijkt dan of het aan de voorwaarde voldoet. Neem rij 44: = als (10 + 50 < = 100; 25000; 50000). In dit geval is 60 kleiner dan 100, en dus geeft Excel het getal 25000 weer. In rij 48 zijn B en C samen 140, en dus groter dan 100. Daarom wordt hier getal 50000 weergegeven.




  • De functie als en tekst. Hierboven hebben we Excel 25000 en 50000 laten neerzetten. Je kunt ook een tekst laten weergeven. Zo zou je het getal 25000 kunnen vervangen door de tekst “te laag” en het getal 50000 door de tekst “te hoog”. De formule pas je dan als volgt aan (in alle cellen in kolom D):

= als (B44 + C44 <= 100;”te laag”; “te hoog”)


Let op: de crux zit ‘m in het gebruik van de aanhalingstekens. Gebruik je deze niet, dan zal Excel in kolom D niet de tekst te laag of te hoog neerzetten, maar een foutmelding. De aanhalingstekens maken Excel duidelijk dat het om tekst gaat!


  • De functie als en aanhalingstekens. Naast het gebruik van aanhalingstekens voor woorden, kun je Excel vertellen dat een cel leeg gelaten moet worden. Stel, je maakt een prijslijst met diverse merken en besluit daarom tussen ieder merk een lege regel te maken. Ook vertel je Excel dat van iedere inkoopprijs een marge moet worden opgeteld. Om nu te voorkomen dat bij het kopiëren de formule met de verkoopprijs ook wordt gekopieerd naar alle witregels, pas je de als functie aan. Je gebruikt hiervoor de formule met 2x aanhalingstekens achter elkaar, “ “.




 

B

C

D

E

F

44

Nokia 1

250

40

290

ALS(B11="";"";C11+D11)

45

Nokia 2

300

50

350

ALS(B12="";"";C12+D12)

46

 

 

 

 

ALS(B13="";"";C13+D13)

47

Samsung 1

200

30

230

ALS(B14="";"";C14+D14)

48

Samsung 2

250

40

290

ALS(B15="";"";C15+D15)

In kolom D staat de marge, die bij de inkoopprijs van kolom C wordt opgeteld. In kolom E moet de verkoopprijs verschijnen. Je ziet dat in rij 46 geen verkoopprijs staat. Dit kun je afleiden uit de formule: als cel B13 (naam van toestel) is gelijk aan “ “ (d.w.z.: leeg), dan moet je deze cel (nl. E46) ook leeg laten, en als dat niet het geval is, dan moet je C46 en D46 optellen.









3.) Absolute en relatieve celverwijzingen

  • Relatieve celverwijzingen: alle voorbeelden, die tot nu toe gebruikt zijn, waren met relatieve celverwijzigingen. Neem nu het laatste voorbeeld. Excel moest in cel E44 de uitkomst weergeven de som van cel C44 en D44. Hadden we cel E44 gekopieerd naar cel F44, dan had de formule zich aangepast naar = D44 + E44. In dat geval zouden we de marge en de verkoopprijs hebben opgeteld, wat we niet wilden. Bij het kopiëren / verslepen van cellen met relatieve celverwijzingen, passen de formules zich automatisch aan. Soms willen we dat niet.




  • Absolute celverwijzingen: om te voorkomen dat formules zich aanpassen, vertellen we Excel dat bij het kopiëren / verslepen geen formules mogen worden aangepast. Hiervoor is het dollarteken ($) bedacht. Een voorbeeld. Op de prijslijst voor mobiele toestellen wordt bij ieder toestel verschillende kortingen gegeven (afhankelijk per abonnement). Deze kortingen worden boven op de balk weergegeven. Voor ieder toestel moet in deze kolom evenveel korting worden afgetrokken van de prijs in de euro 0 kolom.



Ik heb voor het gemak twee cellen een kleur gegeven en de formules gekopieerd. Vergeet maar even het eerste deel van de formule, en focus op het laatste deel, waar dat de korting wordt afgetrokken. In het geval van de rode cel: de prijs in de rode cel is gelijk aan de prijs in de euro 0 kolom minus de korting van 25 euro. Je zou dit kunnen typen als D4 = C4 – D2. Je krijgt dan voor cel D4 de goede uitkomst. Wanneer dat je deze formule zou kopiëren naar beneden, dan verandert een formule zonder absolute celverwijzing in C5 – D3. In cel D3 staat echter niet de korting (van 25 euro). Voordat we dus de formule naar beneden kopiëren, moeten we Excel vertellen dat voor alle cellen in kolom D geldt dat cel D2 van de euro 0 kolom moeten worden afgetrokken.
Klik op de cel met de formule en ga nu met de cursor precies tussen D en 2 instaat. Druk op de toets F4. Er schijnen nu 2 dollartekens. Dit betekent dat waar je deze cel ook heen kopieert, de formule zal altijd D2 gebruiken als getal van de korting. Druk je nu nogmaals op F4, dan zul je zien dat het dollarteken alleen voor het cijfer staat. Nog een keer drukken: alleen voor de letter. En dan bij de vierde keer drukken zijn de dollartekens weer weg. Je kunt de dollartekens overigens ook zelf typen, ipv F4 te gebruiken.
Zoals je kunt zien achter het rode en blauwe blokje zijn de formules in ons geval maar gedeeltelijk van dollartekens voorzien. We willen namelijk dat bepaalde delen wel mee veranderen bij het kopiëren. Bij deze prijslijst willen we 2 dingen:


  • Als we formules naar rechts kopiëren, dan moeten de kortingen wel altijd van de prijzen in de euro 0 kolom worden afgetrokken (kolom C). Tegelijkertijd moet het wel mogelijk zijn dat als we naar beneden kopiëren dat de prijzen in de euro 0 kolom meebewegen. We zetten daarom in het eerste deel van de formule de C vast met een $, maar laten de cijfers “vrij”. Daarom staat er achter het rode blokje een verwijzing naar cel C4 en achter het blauwe blokje naar C6.

  • Als we de formules naar beneden kopiëren, dan moeten de kortingen in rij 2 wel gebruikt blijven worden. Toch willen we, dat als we naar rechts kopiëren, dat de kortingen veranderen. In dit geval zetten we rijnummer 2 vast en laten we de kolommen “vrij”.

Je zult zien dat als je de formule in cel D4 kopieert naar alle overige velden, dat per kolom 25 euro wordt afgetrokken, en dat in iedere rij rekening is gehouden met de verschillen euro 0 kolom prijzen!








4.) Sorteren en filters

  • Sorteren: je kunt in Excel kolommen met informatie sorteren. Selecteer alle cellen die je graag gesorteerd had. Je kunt de kolomnamen mee selecteren. Klik vervolgens in het menu “data” op “sorteren”. Een nieuw schermpje opent. Het is belangrijk dat je eerst onderaan het scherm één van de volgende opties aanklikt: “een veldnamenrij” of “geen veldnamenrij”. Als je de kolomnaam mee geselecteerd hebt, dan moet de eerste optie aanstaan, anders de laatste, Vervolgens kun je zelf 3 kolommen selecteren en aangeven of dat deze oplopend of aflopend moeten worden gesorteerd. Let op: de gegevens worden eerste geselecteerd op de bovenste optie, dan op volgende en dan de laatste.




  • Filters: in het menu “data” vind je onder “filters” de optie “autofilter”. Voordat je deze echter aanzet, moet je ervoor zorgen dat je kolommen voorzien zijn van kolomnamen. Selecteer dan de rij met alle kolomnamen. Ga naar autofilter. Je zult zien dat er achter alle kolommen een knop komt te staan. Als je er op klikt, krijg je o.a. de volgende opties: oplopend sorteren, aflopend sorteren, lege cellen selecteren, niet lege cellen selecteren. Ook kun je op de waardes klikken. Excel filtert dan de waarde. Stel je maakt een kolom met “ja” en “nee”, en je klikt in de filter op “ja”, dan krijg je alle rijen met “ja” te zien. Onder de optie “aangepast” kun je zelf criteria opgeven.

Als een filter is toegepast (m.a.w. als je ergens een optie hebt geselecteerd), dan zul je zien het pijltje van de knop een blauwe kleur heeft. Je kunt overigens meerdere filters gebruiken (klik dan ook op de andere knoppen). Als je alle filters wilt verwijderen, ga dan naar de knoppen, waarvan de pijltjes blauw zijn, en klik op de optie “alle categorieën”. Je zet de autofilter uit zoals je ‘m aanzette.



Bij het sorteren worden gegevens definitief in een andere volgorde gezet. De autofilter zorgt er voor dat je snel informatie vindt. Het verandert echter niets aan de data zoals je ze hebt ingevoerd!






5.) Titels blokkeren / zoeken en vervangen

  • Titels blokkeren: wanneer dat je een lange lijst met data hebt, zoals een prijslijst, dan is het handig om er voor te zorgen dat de kolomnamen blijven staan als je omlaag scrollt. Ga hiervoor naar “venster” en selecteer de optie “titels blokkeren”. Voordat je dit doet, moet je ervoor zorgen dat je de eerste cel aanklikt (in kolom A), die niet bovenaan hoeft blijven te staan. In ons voorbeeld hierboven bij de absolute celverwijzingen zou je cel A3 aanklikken, onder de balk met kortingen. De balk met kortingen zal dan boven de lijst blijven staan.




  • Zoeken en vervangen: in de menubalk vind je onder “bewerken” de opties “zoeken” en “vervangen” (bijna onderaan menu). Ik denk dat “zoeken” voor zich spreekt. Het “vervangen” biedt een interessante oplossing wanneer dat je een woord / getal meerdere keren wilt vervangen. Je kunt Excel alles in één keer laten aanpassen, of je gaat er per item doorheen.








6.) Pagina-instellingen / plakken speciaal (ook terug te vinden in basiscursus)

  • Pagina-instelling: soms past je werkblad niet goed op papier. In dat geval kun je met cellen schuiven. Je kunt ook ’s met afdrukvoorbeeld kijken. Ga naar bestand, klik op afdrukvoorbeeld. Het scherm verandert. Er verschijnen knoppen bovenaan. Klik op “instellen”. Een nieuw schermpje opent. Je kunt op het tabblad “pagina” aangeven dat het werkblad liggend moet worden afgedrukt. Ook kun je hier de schaal aangeven. Als je bijvoorbeeld op 1x1 pagina afdrukt, dan zal Excel automatische de tekst verkleinen totdat het past. Mocht je nog wat meer ruimte nodig hebben, ga dan naar de tab met de naam “marges”. Je kunt hier de witte vlakken aan zijkanten verkleinen. Ook is er een optie om de tekst horizontaal / verticaal te centreren.




  • Plakken speciaal: in plaats van te plakken (bijv. met CTRL + V) kun je gebruik maken van plakken speciaal. Klik hiervoor in de cel(len) waar dat je de gekopieerde tekst wilt plakken. Klik vervolgens op de rechtermuisknop en selecteer... plakken speciaal. Zo ziet het er dan uit:



Normaalgesproken plakt Excel alles. Je ziet dat deze optie ook al aangeklikt staat. Soms is het handig om niet alles te kopiëren. Stel je hebt net een tabel helemaal ingekleurd en met randen afgewerkt, en je komt er achter dat de getallen niet kloppen. In dat geval gebruik je de optie waarden. Excel zal dan alleen de waarden (getallen / tekst) kopiëren, zonder hierbij je opmaak te verprutsen. Soms wil je juist de opmaak kopiëren (denk bijvoorbeeld aan cellen met euro tekens en aantal decimalen achter de komma), zonder dat de inhoud verandert. Selecteer dan opmaak. De optie formules laat je de formules letterlijk kopiëren naar de gewenste cellen.


Tot zover deze cursus Excel ‘gevorderd’. Ik hoop dat het van nut is geweest. Mochten er nog vragen zijn, dan kun je me vinden via mbrouwers@petittelecom.nl. Ik licht graag e.e.a. toe.






De database wordt beschermd door het auteursrecht ©opleid.info 2019
stuur bericht

    Hoofdpagina