Excel tips & trucs

 

Vragen beantwoord ik tegen betaling van een uurtarief van € 60,- met een minimum van 1 uur

 

I N H O U D S O P G A V E

 

Excel tips & trucs (7-12-2010) 1

Tips & trucs voor Excel 3

1              Algemeen. 3

1.1          Werkbalken terughalen en uitzetten. 3

1.2          Bestand direct als e-mail bijlage. 4

1.3          Help opvragen. 4

1.4          Wijzigingen bijhouden/archiveren. 5

1.5          Activeren automatisch opslaan. 6

1.6          Zoeken en vervangen van het * 6

1.7          Zoeken in meerdere werkbladen. 6

1.8          Bewerken van de inhoud van een cel 7

2              Indeling werkblad. 7

2.1          Breder maken kolommen. 7

2.2          Kolommen verbergen en terugzetten. 7

2.3          Opmerkingen invoegen. 8

2.4          Opmerkingen verwijderen. 8

2.5          Beveiligen van een werkblad. 9

2.6          Titels blokkeren. 10

2.7          Splitsen. 10

2.8          Meerdere weergaven. 11

2.9          Inzoomen. 11

2.10        Toevoegen werkblad. 11

2.11        Twee werkmappen vergelijken. 12

3              Invoer 12

3.1          Valideren van gegevens. 12

3.2          Snel reeksen maken. 14

3.3          Reeksen maken met de Smart Tag. 15

3.4          Eigen reeksen maken. 16

3.5          Alfabet 17

3.6          Meer cellen tegelijkertijd vullen. 17

3.7          Meer dan één regel in een cel 17

4              Opmaak. 17

4.1          Getallen van een label voorzien. 17

4.2          Getallen weergeven als duizendtallen. 18

4.3          Tekst aanvullen met streepjes of puntjes. 18

4.4          Optellen van uren. 18

4.5          Uren en minuten omzetten naar decimale cijfers. 18

4.6          Eigen datumformaat 18

4.7          Invoeren van breuken. 18

4.8          Voorloopnullen. 18

4.9          Romeinse getallen. 19

4.10        Opmaakcombinaties. 19

4.11        Opmaak kopiëren. 19

4.12        Voorwaardelijke opmaak. 19

4.13        Rijen om en om kleuren. 21

4.14        Nog een Voorwaardelijke opmaak met een formule. 21

4.15        Dubbelen kleuren met voorwaardelijke opmaak. 22

4.16        Opmaak veranderen via opmaakprofiel 23

4.17        Tabbladen kleuren. 23

4.18        Checkbox als opmaak. 25

5              Verplaatsen van de cursor 25

5.1          Binnen hetzelfde blad. 25

5.2          Van blad naar blad. 25

6              Selecteren. 26

7              Kopiëren, knippen en plakken. 26

7.1          Verplaatsen en/of kopiëren van blad naar blad met de ALT toets. 26

7.2          Transponeren via plakken speciaal 26

7.3          Excel als carbonpapier 27

8              Rekenen en formules. 28

8.1          Formules maken. 28

8.2          Grote formule scherm in Excel 2007. 28

8.3          Direct sommeren. 28

8.4          Directe sommering via selectie. 29

8.5          Extra opties bij Autosom.. 30

8.6          Doorlopende totalen met sommeren. 30

8.7          Totale kolom of rij sommeren. 31

8.8          Sommeren met een naam.. 31

8.9          Somproduct om selectief op te tellen. 32

8.10        Sommen.als vanaf Excel 2007. 34

8.11        Verschil tussen datums. 34

8.12        Weeknummers. 34

8.13        Weeknummers berekenen met een functie. 35

8.14        Weeknummers berekenen met formule. 36

8.15        Omzetten van getallen naar datums. 36

8.16        Wissen speciaal 36

8.17        Rekenen met plakken speciaal 38

8.18        Omzetten van tekstgetallen naar echte getallen. 38

8.19        Controleren van formules. 39

8.20        Formules afdrukken. 39

8.21        Meerdere bladen tegelijk optellen. 40

9              Printen. 40

9.1          Afdrukken algemeen. 40

9.2          Afdrukken: rijen en kolommen vastzetten. 41

10           Databases & lijsten. 42

10.1        Data importeren van internet/intranet 42

10.2        Autofilter: aantal records als resultaat 42

10.3        Autofilter en de aggregatiefuncties van de statusbalk. 42

10.4        Autofilter: niet voor alle kolommen uit het lijstbereik. 43

10.5        Autofilter en subtotalen. 43

10.6        Uitgebreid filter 44

10.7        Uitgebreid filter om dubbele waarden eruit te halen. 46

10.8        Sorteren op meer dan drie kolommen. 47

10.9        Sorteren speciaal 47

10.10           Tabel omzetten naar een Excel lijst 48

11           Zoeken. 50

11.1        Zoeken in meerdere lijsten. 50

12           Grafieken. 51

12.1        Veranderen van grafiekpunten. 51

12.2        Taartpunten happen. 51

12.3        Onderdelen afwijkend kleuren. 52

12.4        Een staaf opvullen met plaatjes. 53

12.5        Schaal van de grafiek veranderen. 54

12.6        Grafiek met dubbele Y-as. 56

12.7        Trend in een grafiek. 57

12.8        Flexibele bereiken in een grafiek. 58

13           Draaitabellen. 61

13.1        Dynamisch bereik. 61

13.2        Groeperen op datum.. 61

13.3        Groeperen op getallen. 65

13.4        Uitkomst weergeven als percentage van de kolom.. 66

13.5        Running totals of cumulatief 67

13.6        Zelf groepen maken. 68

Tips & trucs voor Excel

1         Algemeen

1.1           Werkbalken terughalen en uitzetten

Werkbalken lijken soms een eigen leven te leiden: ze verschijnen en verdwijnen ogenschijnlijk naar believen. Hoe kan een werkbalk per ongeluk van zijn plek komen? Twee manieren:

  • Door dubbel te klikken op een scheiding tussen twee werkbalkknoppen.
  • Door een werkbalk vast te pakken bij dezelfde scheiding of aan het begin van de werkbalk

 

 

Beide handelingen resulteren in een verdwaalde werkbalk. En velen kunnen dan net de verleiding weerstaan op het kruisje linksboven op de werkbalk te drukken. En weg is ie!

 

 

Wat hadden we wel moeten doen? Dubbelklikken op de gekleurde balk boven in de werkbalk!

 

Hoe krijgen we zo’n werkbalk weer terug? We klikken met de rechter muisknop op een overgebleven werkbalk en klikken op de werkbalk met de juiste naam. Weten we die niet? Dan zullen we de handeling moeten herhalen tot de juiste verschijnt. De bovenste twee zijn trouwens de standaard werkbalken.

 

Hij zal dan nog steeds niet op zijn originele plek staan. Dus dubbelklikken we op de gekleurde balk.

 

Excel 2002

In Excel 2002 zijn voor de twee standaard balken optie opgenomen in het menu beeld:

 

1.2           Bestand direct als e-mail bijlage

Een bestand is vaak lastig terug te vinden als je je e-mailtje wilt voorzien van een Excel bijlage. Is niet altijd nodig. In Excel kun je direct aangeven dat je de werkmap als bijlage wilt versturen.

 

 

Ga naar Bestand Þ Verzenden naar Þ E-mail (als bijlage) en je komt direct in je e-mail prgramma terecht.

1.3           Help opvragen

Heb je je wel eens afgevraagd wat dat vraagteken daar doet, rechtsboven op zo’n venster? Als je daar op klikt, krijgt je cursor binnen dat venster gezelschap van een vraagteken. Klik je dan ergens, dan krijg je informatie over het desbetreffende onderdeel.

 

1.4           Wijzigingen bijhouden/archiveren

In Excel kun je het programma laten markeren wat er veranderd is. We zetten dit aan via het menu Extra ÞWijzigingen bijhouden Þ Wijzigingen markeren:

 

 

Er zijn meer mogelijkheden maar we kiezen gelijk voor OK. Zodra nu de inhoud van een cel veranderd wordt, krijgen we het volgende beeld:

 

 

Links boven in de cel staat een blauw driehoekje. Glijden we met de muiswijzer over de cel dan krijgen we een venster met daarin de laatste wijziging.

 

Willen we nu de verschillende wijzigingen langslopen, dan gaan we naar het menu Extra ÞWijzigingen bijhouden Þ Wijzigingen accepteren of negeren ...

 

 

Kiezen we bij datum voor Sinds datum en drukken we op OK dan krijgen we het volgende venster:

 

 

We kunnen dan de wijziging kiezen die wel willen accepteren of gewoon alles accepteren resp. negeren.

 

In Excel 2007 vinden we deze optie onder Controleren.

1.5           Activeren automatisch opslaan.

In de meeste applicaties van Microsoft Office worden bestanden automatisch opgeslagen. Maar in Excel niet. Om dit aan te zetten klikt u op: Extra Þ  Invoegtoepassingen .
Vink in het dialoogvenster dat nu is geopend de optie Automatisch Opslaan aan. Deze optie is nu geactiveerd en is terug te vinden in het menu Extra.

 

 

Gek genoeg heeft het geen effect als je Waarschuwen voor opslaan uitvinkt. Excel blijft komen met de vraag:

 

 

Vanaf Excel 2003 is deze optie verdwenen en vervangen door het automatisch opslaan van herstelgegevens.

1.6           Zoeken en vervangen van het *

Zoeken en Vervangen en vervangen is erg handig. Maar als we het maal teken * willen vervangen door het gedeeld door teken / lopen we tegen problemen aan. Het sterretje wordt namelijk gebruikt om een willekeurige tekst te zoeken. Als we dit zouden gebruiken zou na afloop een cel met inhoud =A1*B1 alleen nog een / bevatten. Door voor de * een tilde ~ te plaatsen lukt het wel. Dus ~* moeten we typen.

1.7           Zoeken in meerdere werkbladen

Als je wilt zoeken en/of vervangen in meer werkbladen, moet je deze werkbladen eerst selecteren. Dat kan met de CTRL of de SHIFT toets (zie 6).

1.8           Bewerken van de inhoud van een cel

Je kunt de inhoud van een cel op vier manieren aanpassen:

  • Je kunt gewoonweg over de inhoud heentypen.
  • Je kunt met de muis klikken in de formulebalk

 

  • Je kunt dubbelklikken in de cel; de cursor komt dan op de plaats terecht waar je geklikt hebt
  • Je kunt op de functietoets F2 drukken; de cursor komt dan in de cel terecht direct achter de inhoud

2         Indeling werkblad

2.1           Breder maken kolommen

Het breder maken van kolommen kan natuurlijk door de kolomscheiding naar links of rechts te trekken. Vaak is evenwel handiger het dubbelklikken op de kolomscheiding! De kolom krijgt dan automatisch de breedte die noodzakelijk is voor het volledig tonen van alle cellen in de kolom.

 

2.2           Kolommen verbergen en terugzetten

Je kunt een kolom verbergen door de kolomscheiding zover naar links te trekken dat de kolom verdwijnt. Hij schuift als het ware achter de voorgaande. Bij selectie van meerdere kolommen verdwijnen er meerdere! Hoe krijgen we ze weer terug?

 

Bij het naderen van de tussenliggende kolomscheiding zal de muiswijzer twee verschillende gedaanten krijgen. In eerste instantie zien we een dikke streep met pijltjes aan weerskanten. Zitten we heel dicht bij de kolomscheiding, dan verschijnt er een dubbele streep met pijltjes aan weerskanten. Deze laatste stelt ons in staat de verdwenen kolom terug te trekken!

 

 

Let op: wanneer de kolom niet volledig verborgen is, maar gereduceerd tot een heel smalle, dan werkt het bovenstaande niet.

2.3           Opmerkingen invoegen

Een werkblad is pas leesbaar voor derden als het een en ander becommentarieerd is. De mooiste manier om dit te doen is via Opmerkingen. Klik op een cel op de rechter muisknop en kies uit het verschenen menu de optie Opmerking invoegen. Het volgende scherm verschijnt dan. We kunnen hierin ons commentaar zetten.

 

 

We laten het scherm weer verdwijnen door er ergens naast te klikken. De cel toont dan een rood driehoekje rechts bovenin. Als we hier met de muis overheen schuiven, komt de opmerking in beeld.

2.4           Opmerkingen verwijderen

Opmerkingen verwijderen kunnen we per stuk doen via de rechter muisknop. We kunnen ook in één keer alle opmerkingen selecteren. Doe dat als volgt:

 

  • Kies Bewerken Þ Ga naar

 

 

  • En klik op Speciaal ...

 

 

Opmerkingen staat al aangeklikt

 

  • Klik op OK

 

Alle cellen die opmerkingen bevatten, zijn nu geselecteerd. Deze opmerkingen kunnen we nu verwijderen door op één van de cellen op de rechter muisknop te klikken en Opmerking verwijderen te kiezen.

 

In Excel 2007 vinden we dat via Bewerken->zoeken en selecteren

2.5           Beveiligen van een werkblad

Als je niet wilt dat de inhoud van een werkblad wordt gewijzigd: menu Extra – beveiliging – blad beveiligen. Het kan ook voorkomen dat je alles wil beveiligen, behalve enkele invoervelden. Dan ga je als volgt te werk:

Selecteer de cellen die juist toegankelijk (veranderbaar) moeten blijven. Misschien handig om deze cellen een kleurtje te geven of van een opmerking te voorzien.

Druk dan op de rechter muisknop en selecteer Celeigenschappen –> bescherming:

 

 

en zet het vinkje bij geblokkeerd uit . Vervolgens kies je menu Extra – beveiliging – blad beveiligen. Kan met wachtwoord. Als ’t goed is kun je nu alleen de invulvelden veranderen.

 

Wil je ten slotte ook nog alle formules aan het oog ontrekken, haal dan de beveiliging er weer af. Selecteer dan het hele werkblad kies voor Celeigenschappen Þ bescherming en vink het vakje Verborgen aan. Uiteraard moeten we  de beveiliging dan weer zetten.

Excel 2002

Excel 2002 signaleert automatisch of een cel die een formule bevat wel of niet geblokkeerd is. Linksboven in de cel staat dan een groen driehoekje.

 

 

Gaan we op die cel staan en klikken we vervolgens om het vierkantje ernaast, dan krijgen we het volgende menu:

 

 

Door de tweede optie te kiezen, word de cel geblokkeerd. Deze is daarmee nog niet beveiligd!!!

 

In Excel 2007 vinden we deze opties onder Controleren

2.6           Titels blokkeren

Bij uitgebreide lijsten is het praktisch de kolomkoppen en eventueel de rijkoppen te  kunnen vastzetten. We doen dit door de cursor zo te plaatsen dat hij direct onder de bovenste rij van de lijst en direct rechts van de eerste kolom van de lijst staat. Dan kiezen we uit het menu Venster de optie Titels blokkeren.

De bovenste rij en eerste kolom van de lijst schuiven dan niet mee als we door de lijst lopen. Om dit weer uit te zetten kiezen we onnieuw voor Venster en kiezen dan de optie Titelblokkering opheffen.

In Excel 2007 vinden we dit onder Beeld->Deelvenster blokkeren

2.7           Splitsen

Een mogelijkheid die lijkt op het blokkeren van titels. Met splitsen kun je je werkblad opdelen in twee of vier delen. Bij een horizontale splitsing gaat het verplaatsen van de cursor in de delen naar boven en beneden gaat onafhankelijk van elkaar. Bij een verticale splitsing is dat het geval als de cursor van links naar rechts gaat. Zo is het mogelijk om binnen een werkblad uiteenliggende delen met elkaar te vergelijken.

 

De mogelijkheid Splitsen is te vinden onder het menu Venster. Het is ook mogelijk een blad te splitsen met de muis. Rechtsboven in en rechts onderin zijn kleine grijze balkjes te vinden die met de muis te pakken en te verplaatsen zijn.

 

In Excel 2007 vinden we dit onder Beeld->Nieuw venster

 

Let op: Splitsen en Titels blokkeren kan niet tegelijkertijd, alleen alternatief.

2.8           Meerdere weergaven

In versie 2007 is er rechtsonder een mogelijkheid gekomen om te schakelen tussen verschillende weergaven:

 

2.9           Inzoomen

In versie 2007 is er linksonder een balk bij gekomen waarmee ingezoomd kan worden:

 

2.10      Toevoegen werkblad

In versie 2007 is er linksonder een knop gekomen om direct werkbladen toe te voegen:

 

 

 

2.11      Twee werkmappen vergelijken

In Excel kunt u twee geopende werkmappen vergelijken.

 

  • Open de werkmappen die u wilt vergelijken.
  • Zorg dat u in een van werkmappen staat en kies Venster -> Naast elkaar vergelijken met [werkmapnaam], de naam van het twee werkblad waarmee u het actieve werkblad wilt vergelijken.
  • Excel 2003 plaatst de twee nu naast elkaar. Gaat u in een van beide werkmappen scrollen, dan zal de andere synchroon meescrollen. Zo kunt u de inhoud van beide werkmappen met elkaar vergelijken.

3         Invoer

3.1           Valideren van gegevens

Stel je wilt dat in een bepaalde cel alleen bepaalde waarden ingevoerd mogen worden, bijvoorbeeld de waarden 1, 2, 3, 4 en 5. Het is dan mogelijk deze cel te valideren en aldus de invoer tot de genoemde waarde te beperken.

 

Kies uit het menu Data de optie Valideren. Het volgende scherm verschijnt.

 

 

Kies bij Toestaan voor Lijst

 

 

En vul bij bron de waarden in met steeds een punt komma ertussen. De cel zal nu voorzien zijn van een zogenaamde lijstpijl. Deze verschijnt alleen als de cel geselecteerd is.

 

 

Je kunt hier ook met namen werken. Maak bijvoorbeeld een naam die verwijst naar eel lijst met plaatsen. We gebruiken hier $D:$D om eventuele nieuwe plaatsen ook in de naam opgenomen te krijgen.

 

 

Vervolgens maken we de gegevensvalidatie:

 

 

We krijgen dan:

 

 

We kunnen de naam Lijstplaatsen ook laten verwijzen naar een dynamische reeks met de formule:

 

=VERSCHUIVING($D$1;0;0;AANTALARG($D:$D);1).

 

We voorkomen dan dat er lege optie verschijnen in de dropdown lijst.

3.2           Snel reeksen maken

Vaak kunnen we onszelf in Excel een hoop typewerk besparen en wel door het gebruik van de vulgreep. De vulgreep is zichtbaar rechtsonder in een geselecteerde cel.

 

 

We kunnen op verschillende manieren aan deze vulgreep trekken, horizontaal en verticaal:

  • Met de linker muisknop ingedrukt
  • Linker muisknop  + CTRL (denk eraan ten slotte eerst de CTRL en dan pas de muisknop los te laten!)
  • Met de rechter muisknop; dan verschijnt het volgende menu

 

Er is nog een mogelijkheid:

  • Dubbelklikken op de vulgreep: deze werkt alleen als en voor zover de naast gelegen kolom gevuld is en alleen naar beneden

 

Wat is het resultaat? Dat hangt van de startcel(len) af en van de gekozen methode! Enkele voorbeelden:

  • Een getal met de linker muisknop: steeds hetzelfde getal
  • Een getal met de linker muisknop  + CTRL: oplopende getallen
  • Een woord met getal (vb. week 3) met de linker muisknop: zelfde woord + oplopend getal
  • Een woord met getal (vb. week 3) met de linker muisknop + CTRL: zelfde woord + steeds zelfde getal
  • Een datum met de linker muisknop: oplopende datums
  • Een datum met de linker muisknop + CTRL : dezelfde datum
  • Een datum met de rechter muisknop: keuzemenu met onder andere keuzemogelijkheid voor alleen weekdagen of alle dagen
  • Twee geselecteerde cellen met de getallen 1 en 2 met de linker muisknop: oplopende getallen
  • Twee geselecteerde cellen met de getallen 1 en 2 met de linker muisknop + CTRL: steeds 1 en 2
  • Dubbelklikken op twee geselecteerde cellen met 1 en 2 met een naastgelegen gevulde kolom: oplopende getallen
  • Een formule met relatieve of absolute verwijzing naar andere cellen met de linker muisknop: doorlopende formules

 

En uiteraard zijn er nog tal van gecombineerde slimmigheidjes mogelijk. Creativiteit geboden!

3.3           Reeksen maken met de Smart Tag

In Excel 2003 biedt een zogenaamde Smart Tag extra mogelijkheden.

 

 

Als we op deze Smart Tag klikken, krijgen we het volgende:

 

 

Met de tweede optie bereiken we bij een getal hetzelfde effect als de combinatie muis  met de CTRL toets ingedrukt en dan aan de vulgreep trekken. Bij een reeks beginnende met bijvoorbeeld Week 1 geldt het omgekeerde.

3.4           Eigen reeksen maken

Niet alle reeksen worden automatisch aangevuld. Excel kent er standaard maar een paar:

  • Ma, di, wo etc.
  • Maandag, dinsdag, woensdag etc
  • Jan, feb, mrt etc
  • Januari, februari, maart etc.

 

Maar naar de reeks a, b, c zullen we vergeefs zoeken. We kunnen hem wel zelf maken. We zullen dan eenmalig zelf even het alfabet moeten intikken. Vervolgens selecteren we de 26 cellen (bij voorkeur van bovenaf met: SHIFT CTRL ß). We kiezen dan uit het menu Extra: Opties Þ aangepaste lijst en klikken op de knop Importeren. De lijst is dan toegevoegd. Nog even op OK klikken en we kunnen testen of de letter a nu ook automatisch aangevuld wordt.

 

Uiteraard is het bovenstaande ook van toepassing op andere reeksen als Aap, Noot, Mies of iets serieuzers als Groningen, Friesland, Drenthe .....

3.5           Alfabet

We hadden het alfabet ook kunnen maken door ergens in een cel de formule =TEKEN(RIJ(A1)+64) te plaatsen. Deze formule doortrekken levert het alfabet in hoofdletters. De formule =TEKEN(RIJ(A1)+96) levert het alfabet in kleine letters.

3.6           Meer cellen tegelijkertijd vullen

Als we meerdere cellen selecteren en we willen deze selectie vullen met één het zelfde gegeven, doen we dat door eerst een gebied te selecteren, vervolgens een getal of tekst te typen en ten slotte door op CTRL te drukken en op ENTER te tikken.

3.7           Meer dan één regel in een cel

Heeft u een langere titel voor een kolom, dan kan die te breed zijn. Dit lost u op door meer regels onder elkaar in één cel te plaatsen. Druk aan het eind van de eerste regel op  ALT + ENTER. Typ vervolgens de tweede regel.

4         Opmaak

4.1           Getallen van een label voorzien

Stel je voor dat we in een cel willen zetten: 30 jaar. Op zich geen probleem ware het niet dat Excel er niet meer mee kan rekenen. Dat zou wel kunnen als we het woord “jaar” als opmaak aan het getal vastplakken.

 

Hoe doen we dat? We tikken in een cel het getal 30 en drukken op ENTER. Vervolgens klikken we in dezelfde cel op de rechter muisknop en kiezen Celeigenschappen.

 

 

Op het tabblad getal kiezen we vervolgens voor aangepast. In het vakje waar nu het woord “standaard”staat, type we letterlijk: 0 “jaar”. Als we nu op ENTER drukken, zien we in de cel staan: 30 jaar. Maar nu kunnen we er wel gewoon mee rekenen.

4.2           Getallen weergeven als duizendtallen

Nog een voorbeeld. We willen een getal weergeven als duizendtal. Bijvoorbeeld 353157 als 353. Selecteer de cel(len). Druk op de rechter muisknop. We krijgen dan weer het scherm dat hiervoor reeds getoond is.  Kies weer voor Aangepast. Tik in het vakje Type: 0. (met punt dus!). Het getal wordt nu weergegeven als 353. Uiteraard blijft Excel rekenen met het oorspronkelijke getal.

 

In aanvulling hierop: het formaat 0.. geeft getallen weer als miljoenen!

4.3           Tekst aanvullen met streepjes of puntjes

En nog eentje. Niet zoveel gebruikt, maar kan soms handig zijn: de tekst die in een cel staat  aanvullen met bijv. puntjes of streepjes. Zelfde als hierboven maar dan bij Type:  @*.  of  @*-

4.4           Optellen van uren

Als we met tijden willen kunnen rekenen, moeten we ze in het formaat u:mm:ss invoeren. Maar als we zo bijvoorbeeld de reeks 8:00:00, 9:00:00, 5:00:00 en 6:00:00 invoeren en optellen, dan krijgen we als antwoord: 4:00:00. Excel haalt het dagdeel , oftewel 24 uur, er automatisch uit. Hoe voorkomen we dit? We moeten in de resultaatcel bij aangepaste opmaak vierkante haken om het uurdeel zetten, dus zo: [u]:mm:ss. We kunnen er ook een d voorzetten: d u:mm:ss. Dan krijgen we 1 4:00:00.

4.5           Uren en minuten omzetten naar decimale cijfers

Stel we hebben een totaal aantal uren en minuten van 33:22. De makkelijkste manier om dit om te zetten naar decimale cijfers is, delen door 1:00 (lees 1 uur). Gek genoeg lukt dat alleen door die 1:00 in een aparte cel te zetten en vervolgens in de deling te verwijzen naar dat celadres. In één keer delen: 33:22/1:00 geeft een foutmelding!

4.6           Eigen datumformaat

Datums kun je ook leuke dingen mee doen. Stel je wilt een datum weergeven als:

do 18-9-2003.

 

Tik dan de datum 18-9-2003 in en ga met de rechter muisknop naar celeigenschappen. Kies voor datum en typ in het vakje type het volgende: ddd d-m-jjjj

4.7           Invoeren van breuken

Excel lijkt alleen maar geschikt voor decimale getallen. Nu schijnen er in Nederland maar weinigen te zijn die nog iets van breuken snappen, maar voor het geval dat. Stel je voor je wilt invoeren 3 ¼ of 4/100. Hoe doen we dat? Simpel! Zorg er voor dat je altijd eerst een geheel getal intikt (in het tweede geval een 0), dan een spatie en dan de breuk met een schuine streep ertussen (/). Let op 4/100 wordt in de cel weergegeven als 1/25, maar bovenin de formulebalk zien we 0,04

 

4.8           Voorloopnullen

We kunnen gehele getallen voorzien van zogenaamde voorloopnullen: 000000034546. Hoe doen we dat? Door bij Celeigenschappen Þ Getal te kiezen voor Aangepast en dan bij Type: het aantal gewenste voorloopnullen te kiezen.

 

 

4.9           Romeinse getallen

Voor het omzetten van getallen naar Romeinse cijfers is er een formule: =romeins(1972) levert MCMLXXII

4.10      Opmaakcombinaties

Als we een getal willen omzetten naar het formaat percentage, kunnen we de sneltoets CTRL SHIFT+ % gebruiken. Leuk, maar voor percentage hebben we ook een knop op de werkbalk:

 

Anders wordt het als we een cel een datumformaat of een tijdformaat willen geven. Dan hebben we de combinaties:

CTRL SHIFT @: tijd in uu:mm

CTRL SHIFT #: datum als dd-mm-jj

 

 En nog ééntje:

CTRL SHIFT !: twee cijfers achter de komma

4.11      Opmaak kopiëren

Excel lijkt soms vreemd om te springen met de opmaak van cellen: in een ogenschijnlijk lege cel verschijnt een datum terwijl we een getal ingetikt hadden en zo kunnen we nog wel meer voorbeelden geven. Wat is de snelste manier om hier iets aan te doen? Het kopiëren van opmaak. In dit geval klikken we op een cel waarin nog niets gestaan heeft, een echt lege cel, desnoods op een ander werkblad. Vervolgens klikken we op de knop  opmaak kopiëren. Ten slotte klikken we op de cel met de verkeerde opmaak. En deze is weer teruggezet naar de standaard opmaak.

 

Uiteraard kunnen we deze opmaakknop ook gebruiken om gewenste opmaak van de ene cel naar de andere(n) te kopiëren!

 

Als je de opmaak naar meerdere cellen wilt kopiëren, kun je dubbelklikken op dit icoon. Je kunt dan door blijven schilderen totdat je de knop weer uitzet of op ESC drukt.

 

4.12      Voorwaardelijke opmaak

Cellen opmaken kan verhelderend werken. Cellen conditioneel opmaken kan het nog beter maken. Negatieve getallen rood en positieve getallen blauw bijvoor beeld. We kunnen dit doen door in een cel te gaan staan en het volgende scherm op te roepen:

 

 

In dit scherm kunnen we de gewenste voorwaarden instellen . Via de knop Opmaak komen we dan in het volgende scherm:

 

 

Via dit scherm stellen we lettertype, randen en achtergronden in. We klikken dan twee keer op OK en testen de voorwaardelijke opmaak van de cel door achtereenvolgens positieve en negatieve waarden en 0 in te voeren.

 

Nog een toepassing: verbergen van de foutmelding van een formule.

Als in cel D1 een formule zetten die C1 deelt door B1 waarbij de laatste cel leeg is, dan krijgen we de foutmelding: #DIV/0!.

Je kunt in Excel foutwaarden verbergen door voorwaardelijke opmaak of voorwaardelijke formules te gebruiken. Je gaat als volgt te werk: selecteer de cel of cellen met de formules die mogelijk foute resultaten geven. Kies in het menu Opmaak op Voorwaardelijke opmaak.

Klik in het dialoogvenster Voorwaardelijke opmaak op de lijst Voorwaarde 1 en selecteer Formule is. Geef in het vak rechts naast de lijst Voorwaarde 1 de volgende formule op:
=ISERROR(cel_verwijzing) waar cel_verwijzing de relatieve verwijzing is naar de actieve cel van de selectie. In het voorbeeld is D1 de actieve cel van de selectie. Klik op de knop Opmaak. Klik in het dialoogvenster Celeigenschappen in de lijst Kleur op de kleur wit.

Klik op OK in het dialoogvenster Celeigenschappen en klik vervolgens in het dialoogvenster Voorwaardelijke opmaak op OK.

4.13      Rijen om en om kleuren

Hiervoor gebruiken we ook de Voorwaardelijke Opmaak.

  • We gaan in cel A1 staan en kiezen voor Opmaak->voorwaardelijke opmaak:

 

 

  • Hier kiezen we in het vak linksboven voor: Formule is.
  • Als formule typen we dan =REST(RIJ();2)=0.
  • En als opmaak stellen we bij Patroon een achtergrondkleur in:

 

 

  • We klikken op OK
  • We gebruiken de Opmaakkwast om de opmaak naar alle cellen te kwasten:

 

We krijgen dan:

 

4.14      Nog een Voorwaardelijke opmaak met een formule

Het onderstaande bereiken we met een formule. In de onderste tabel wordt steeds de waarde gekleurd weergegeven die overeenkomt met de boven gekozen waarden voor Schaal en Groep

 

 

Den formule ziet er als volgt uit:

 

4.15      Dubbelen kleuren met voorwaardelijke opmaak

In onderstaande reeks zijn alleen de item die dubbel voorkomen gekleurd.

 

 

We krijgen dat effect door op de kolom voorwaardelijke opmaak toe te passen met de volgende formule

 

 

4.16      Opmaak veranderen via opmaakprofiel

Stel je wilt het hele werkblad instellen op een andere opmaak. Je kunt dat doen door het hele werkblad te selecteren en dan te kiezen voor een ander lettertype en/of lettergrootte.

 

Een andere en betere manier is het aanpassen van het opmaakprofiel standaard. We vinden onder Opmaak  Þ opmaakprofiel

Via dit scherm kunnen we ook zelf opmaakprofielen maken. En zelfs opmaakprofielen overnemen uit andere – geopende – werkmappen. Via de knop Samenvoegen:

 

4.17      Tabbladen kleuren

Sinds versie 2003 is het mogelijk tabbladen verschillende kleuren te geven:

 

  • Klik met de rechter muisknop op één van de tabbladen.

 

 

  • Kies tabkleur

 

 

  • Kies een kleur
  • Klik op OK
  • Herhaal de stappen voor de andere tabbladen

 

Let er op dat van het geselecteerde werkbblad alleen de onderste rand gekleurd is:

 

 

In versie 2007 kunnen er veel meer kleuren gekozen worden:

 

4.18      Checkbox als opmaak

Stel je hebt een kolom met alleen de cijfers 0 en 1 en je wilt deze weergeven als al of niet aangevinkte checkboxen.

 

 

Dat kan door in de kolom ernaast de volgende formule te plaatsen:

 

=ALS(D2=1;TEKEN(254);TEKEN(111)) enzovoorts

 

Vervolgens kiezen we voor de betreffende kolom lettertype Wingdings en voila!

5         Verplaatsen van de cursor

5.1           Binnen hetzelfde blad

Dat kan uiteraard met een muisklik. Het toetsenbord bewijst hier evenwel ook handige diensten:

 

CTRL + END                 springt naar de laatst gebruikte cel in het werkblad

CTRL + HOME              springt naar cel A1

CTRL + Þ                     springt naar het einde van een aaneengesloten rij en als dat einde reeds bereikt is, naar het absolute einde van de rij           

CTRL + Ü                     springt naar het begin van een aaneengesloten rij en als dat begin reeds bereikt is, naar het absolute begin van de rij           

CTRL + ß                      springt naar het einde van een aaneengesloten kolom en als dat einde reeds bereikt is, naar het absolute einde van de kolom

CTRL + Ý                      springt naar het begin van een aaneengesloten kolom en als dat begin reeds bereikt is, naar het absolute begin van de kolom

5.2           Van blad naar blad

Stel je hebt zoveel bladen dat ze niet allemaal in beeld zijn. Je kunt ze dan niet direct aanklikken. Weliswaar kun je er ‘heenschuiven’ met de pijlen linksonder. Sneller is klikken met de rechter muisknop op deze lijstpijl en het werkblad kiezen waar je heen wilt.

6         Selecteren

Iedereen kent de voor de hand liggende methode: linker muisknop indrukken en een forse ruk aan rechter elleboog. We selecteren dan wel maar zelden het gewenste stuk! Kan het handiger? Ja zeker. Hier een aantal manieren:

 

SHIFT + muisklik           we selecteren dan precies van de cel waarin de cursor staat tot aan de cel waarop we geklikt hebben; het geselecteerde gebied is met dezelfde methode ook kleiner of groter te maken

SHIFT + CTRL + Þ        selecteert tot aan het einde van de aaneengesloten rij en als dat einde reeds bereikt is tot aan het absolute einde van de rij

SHIFT + CTRL + Ü        selecteert tot aan het begin van de aaneengesloten rij en als dat begin reeds bereikt is tot aan het absolute begin van de rij

SHIFT + CTRL + ß         selecteert tot aan het einde van de aaneengesloten kolom en als dat einde reeds bereikt is tot aan het absolute einde van de kolom

SHIFT + CTRL + Ý         selecteert tot aan het begin van de aaneengesloten kolom en als dat einde reeds bereikt is tot aan het absolute begin van de kolom

SHIFT+CTRL+END        selecteert tot aan de laatst gebruikte cel

CTRL + muisklik            als we meermalen klikken, selecteren we meerdere gebieden

CTRL + slepen              als we meermalen slepen, selecteren we eveneens meerdere gebieden

CTRL + *                       selecteert een aaneengesloten gebied waarbij er hoogstens één lege rij of kolom tussen de lege cellen zit

CTR + A                        alles selecteren; dit kunnen we ook doen door op het vlak linksboven – links van de A en boven de 1 - te klikken

7         Kopiëren, knippen en plakken

7.1           Verplaatsen en/of kopiëren van blad naar blad met de ALT toets

De inhoud van een cel kopiëren of verplaatsen van het ene werkblad naar het andere kunnen we doen met de knoppen kopiëren/knippen en plakken.

 

We kunnen het ook doen door te slepen met de ALT toets ingedrukt. We slepen dan de celinhoud naar de bladnamen links onderin. Gebruiken we naast de ALT ook nog de CTRL toets dan kopiëren we.

7.2           Transponeren via plakken speciaal

Stel we hebben een verticale reeks gegevens en willen deze veranderen in een horizontale reeks. Dat kan via Transponeren.

 

  • We typen verticaal vanaf A1 de reeks Aap, Noot, Mies, Wim, Zus, Jet
  • We selecteren deze en klikken op kopiëren
  • Dan verplaatsen we de cursor naar B1
  • We kiezen Bewerken Þ  Plakken Speciaal

 

 

  • We vinken het vakje Transponeren aan en klikken op OK

 

De reeks staat nu ook horizontaal

 

 

 

Excel 2002

In Excel 2002 hoeven we niet langer eerst naar het menu te gaan. We kunnen ook klikken op de lijstpijl naast het plakbord.

 

 

Dan kunnen we bijvoorbeeld kiezen voor Transponeren.

7.3           Excel als carbonpapier

Stel we willen een zelfde opzet op drie werkbladen tegelijk hebben. Dat kunnen we natuurlijk doen door het eenmaal te maken en vervolgens een aantal keren te kopiëren. Het kan ook anders.

 

  • Selecteer met de CTRL toets ingedrukt een aantal bladen
  • Maak een opzetje op het zichtbare blad
  • Hef de groepsselectie op door op één van de bladen te klikken met de rechter muisknop

 

 

  • Controleer of op alle, eerder geselecteerde bladen hetzelfde staat

8         Rekenen en formules

8.1           Formules maken

Het plaatsen van dollartekens op de juiste plek is een probleem op zich. Het oproepen ervan niet. Daarvoor hebben we de functietoets F4. Bent u bijvoorbeeld een formule begonnen met de = en heeft u daarna op cel F7 geklikt, dan kunt u met drukken op F4 de dollartekens op de juiste plek krijgen.

 

We krijgen dan achtereenvolgens: =F7 , = $F$7, =F$7, = $F7 en beginnen dan weer van voren af aan. We stoppen dan gewoon bij de combinatie die we willen hebben.

 

8.2           Grote formule scherm in Excel 2007

We kunnen het formule scherm in deze versie groter trekken:

 

8.3           Direct sommeren

Bij het optellen van een kolom met getallen is de snelste manier als volgt.

 

  • Ga in de cel onder de getallen staan en klik dubbel op de knop .

 

De som verschijnt dan direct.

8.4           Directe sommering via selectie

Wat is de snelste manier om een aantal getallen op te tellen? Ze alleen selecteren. Als we een tal getallen selecteren zien we rechtsonder de totaal al staan. Als we met de rechter muisknop op dit resultaat kiezen, krijgen we ook nog een aantal andere mogelijkheden.

 

 

In versie 2007 is dit aangepast:

 

8.5           Extra opties bij Autosom

In Excel 2003 is er een lijstpijl toegevoegd aan de optie Autosom:

 

 

Naast sommeren zijn er nu ook andere functies snel toe te passen. Via de onderste optie kunnen nu ook alle functies opgeroepen worden.

8.6           Doorlopende totalen met sommeren

We kunnen met de SOM functie een doorlopend toaal creëren door het geozen bereik deels absoluut te maken:

 

8.7           Totale kolom of rij sommeren

We kunnen in Excel de hele kolom of rij ook in een keer sommeren met de formules:

 

=SOM(A:A)

 

of

 

=SOM(1:1)

 

Om te voorkomen dat we dan een kringverwijzing krijgen, moeten we deze formule in een andere rij of kolom of op een ander blad plaatsen.

8.8           Sommeren met een naam

Op onderstaande manier kun je een naam maken die altijd verwijst naar cel direct boven waar de cursor staat. Met het uitroepteken geef je aan dat Excel dat op elk willekeurig blad moet doen.

 

 

Vervolgens kunnen we een som maken verwijzend naar deze laatste cel in het bereik:

 

 

Maken we in de kolom ernaast de volgende formule: =SOM(C1:laatstecelbereik), dan geeft die als resultaat 7200!

8.9           Somproduct om selectief op te tellen

We kunnen de functie SOM.ALS gebruiken om een kolom selectief op te tellen waarbij op een andere kolom een criterium toepassen.

 

Deze functie werkt evenwel niet als we op meerdere kolommen een criterium willen toepassen. Dan kunnen we SOM.PRODUCT gebruiken.

 

 

De uitkomst voor A2 is dan waar. Door dit met 1 te vermenigvuldigen, krijgen we dan als uitkomst 1. Hetzelfde gebeurt met B2. De uitkomst van A2 * B2 * C2 (de enige rij die voldoet) wordt dan 1 * 1 * 1000 = 1000!

8.10      Sommen.als vanaf Excel 2007

Het vorige probleem kunnen we vanaf Excel 2007 oplossen met SOMMEN.ALS. Met deze nieuwe functie kunnen we nu criteria toepassen op meerdere bereiken:

 

8.11      Verschil tussen datums

Daarvoor kunnen we de functie Datumverschil gebruiken. Voorbeeld:

 

  • Typ in A1 de datum 10-3-2010
  • Typ in A2 de datum 1-1-2010
  • Typ in A3 de formule =datumverschil(A1;A2;”m”)

 

We krijgen dan het verschil in maanden. Er zijn nog allerlei mogelijkheden in plaats van de “m”:

 

“d”        verschil in dagen

“y”        verschil in jaren (handig om de leeftijd uit te rekenen)

“ym”     verschil in maanden binnen het zelfde jaar

“yd”      verschil in dagen binnen hetzelfde jaar

“md”     verschil in dagen binnen dezelfde maand en hetzelfde jaar

8.12      Weeknummers

Weeknummers afleiden van datums is geen standaard functie in Excel. We kunnen wel dag, maand en jaar met de met de functies van dezelfde naam uit een datum distilleren. Voor weeknummers is meer nodig.

 

Daarvoor dienen we eerste de Analysis Toolpak te activeren. Dat doen we via Extra Þ invoegtoepassingen

 

 

Als we deze toepassing geactiveerd hebben, beschikken we over de functie Weeknummer. Toegepast op een datum levert deze het juiste weeknummer. En dat doen we als volgt.

 

  • Typ in cel A1 de datum 22-9-2003
  • Ga in de cel er naast staan
  • Typ daar de formule =weeknummer(A1)

 

De formule levert als weeknummer op: 39

 

En hebben we in een tabel op basis van datums weeknummers toegevoegd dan kunnen we zonder problemen groeperen op die weeknummers, maar bijvoorbeel dook op een periode van 2, 3 of meer weken via de groepeerfunctie van de draaitabel. Maar dat zullen we bij de draaitabellen bespreken.

8.13      Weeknummers berekenen met een functie

We registreren een willekeurige macro, de naam en inhoud van deze hulpmacro is niet van belang.

  • We gaan  via Extra Þ Macro Þ Nieuwe macro opnemen naar het dialoogvenster Macro opnemen en selecteren in de keuzelijst Macro opslaan in de boeking Persoonlijke Macrowerkmap
  • We klikken op OK
  • We sluiten de opname af na de eerste opdracht en sluiten Excel af
  • We antwoorden Ja op de vraag of de wijzigingen moeten worden opgeslagen (deze vraag heeft betrekking op de Persoonlijke Macrowerkmap)
  • We starten Excel opnieuw en gaan via Venster > Zichtbaar maken naar het dialoogvenster Zichtbaar maken en selecteren het bestand Persnlk.xls
  • We openen de macro-editor met de toetsencombinatie [Alt] + [F11]
  • We openen in het Project- venster de map VBAProject (Persnlk.xls) en daarin de map Modules
  • We verwijderen uit module Module 1 de voorlopige macro Sub Macro1()
  • We voeren nu de volgende functiedefinitie in:

 

Function Weeknr(d As Date)

Dim t As Long

t = DateSerial(Year(d + (8 - WeekDay(d)) Mod 7 - 3), 1, 1)

Weeknr = ((d - t - 3 + (WeekDay(t) + 1) Mod 7)) \ 7 + 1

End Function

 

Toelichting:

De operator '\' voert een deling uit van twee waarden met als resultaat een geheel getal. Het gedeelte van het resultaat achter de komma wordt afgekapt.

 

  • We schakelen met [Alt] + [F11] terug naar het Excel-venster en sluiten de werkmap Persnlk.xls af; vergeet niet de wijzigingen op te slaan.

 

Vanaf nu is de functie in ieder werkblad beschikbaar (mits Persnlk.xls geladen is!).

 

Macro weeknummer berekenen gebruiken:

  • We voeren in cel C2 een datum in, bijvoorbeeld 1-1-2005
  • We vullen in cel C3 een zelf gedefinieerde functie in, bijvoorbeeld: =Persnlk.xls!Weeknr (C2).

 

Het resultaat: Excel berekent het kalenderweeknummer van de datum in C2 en komt op 53 uit want de laatste week van 2004 liep door t/m 2-1-2005. 

 

In Excel 2007 zijn de invoegtoepassingen te vinden via Opties voor Excel->onvoegtoepassingen.

8.14      Weeknummers berekenen met formule

Zonder VBA kan het ook. De volgende formule haalt uit een datum in A1 het Nederlandse weeknummer:

 

=INTEGER((A1-DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;3)+WEEKDAG(DATUM(JAAR(A1-WEEKDAG(A1-1)+4);1;3))+5)/7)

8.15      Omzetten van getallen naar datums

Stel we krijgen de datum aangeleverd als een getal: 20050411. We plaatsen dit getal even in cel A1. En we willen dit getal omzetten naar een echte datum. In Excel kunnen we dit doen met de formule:

 

=datumwaarde(deel(A1;1;4) & “-“ & deel(A1;5;2) & “-“ & deel(A1;7;2))

 

We krijgen dan een getal als uitkomst: 38453. Dit moeten we dan nog even een datumformaat geven.

8.16      Wissen speciaal

Een werkblad bevat getallen en formules. Nu wil je alleen die velden leeg maken die een getal bevatten om bijv. de gegevens over een nieuwe maand in te voeren. Druk op functietoets F5 (of via het menu Bewerken Þ Ga naar) om het Ga naar dialoogvenster weer te geven:

 

 

Druk op Speciaal:

 

 

en selecteer Constanten en selecteer vervolgens alleen Getallen. Als je op OK klikt worden alleen de cellen geselecteerd die een getal bevatten. Druk vervolgens op de Delete toets en alle cellen met een waarde worden gewist.

 

Nog een voorbeeld:

Typ een reeks waarden zoals in het voorbeeld en selecteer deze.

 

 

Druk op F5, kies Speciaal en klik de optie Verschillen in kolom aan. Als resultaat worden alleen die cellen geselecteerd die verschillen van de eerste:

 

 

In Excel 2007 vind je Ga naar onder Start->Zoeken en selecteren

8.17      Rekenen met plakken speciaal

Alle ophef rond de euro is weggeëbd en de speciale, duurbetaalde eurotools liggen weer werkeloos op de plank. Toch hadden we dit probleem ook uitstekend in Excel zelf kunnen aanpakken.

 

We tikken een onder elkaar de getallen van 100 t/m 500. In een cel ernaast tikken we 2,20371. Deze cel gaan we kopiëren met de knop . Vervolgens selecteren we de kolom met de getallen 100 t/m 500. Uit het menu bewerken kiezen we nu Plakken speciaal. Het volgende scherm verschijnt:

 

 

In dit scherm selecteren we delen. Als we nu op OK klikken, zien we dat alle getallen gedeeld zijn door de euro omrekenfactor. Voor wie het begrepen heeft, we kunnen ze met vermenigvuldigen ook weer terug krijgen.

8.18      Omzetten van tekstgetallen naar echte getallen

Soms kan het gebeuren – bijvoorbeeld dor importeren - dat er getallen door Excel als tekst beschouwd worden. Je kan er dan niet mee rekenen. Hoe zetten we deze teksten weer om naar echte getallen? Door er 0 bij op te tellen!

 

  • Typ in A1 ‘1200 (1200 met een enkel aanhalingsteken ervoor)
  • Trek de reeks door tot 1210 (vulgreep met de linker muisknop)

 

De getallen zullen links uitgelijnd zijn; als we ze proberen te sommeren, mislukt dat.

 

  • Typ in B1 een 0
  • Selecteer deze cel en klik op kopiëren
  • Selecteer nu 1200 t/m 1210
  • Kies Bewerken Þ Plakken Speciaal
  • Kies Optellen en klik op OK

 

Als we ze nu weer proberen te sommeren, lukt dit wel!

 

Excel 2002

Als we dezelfde lijst in versie 2002 bekijken, zien we in de linkerbovenhoek een groen driehoekje.

 

 

En ernaast een vierkantje met een uitroepteken erin. Klikken we hierop, dan krijgen we het volgende venster:

 

 

We kunnen dan gewoon de bovenste optie aanklikken en get getal resp. de geselecteerde getallen zijn omgezet naar echte getallen.

8.19      Controleren van formules

Uiteraard kunnen we formules niet op hun logische correctheid controleren; daar hebben we ons eigen intellect voor nodig. We kunnen wel de broncellen van een formule aanwijzen:

 

 

We doen dat door de cel met de formule te selecteren en vervolgens te kiezen Extra Þ Controleren Þ broncellen aanwijzen. In Excel 2007 via Formules.

8.20      Formules afdrukken

Als je een vrij complexe spreadsheet maakt, raak je soms het spoor bijster wat formules betreft. Een fout in een formule in een enkele cel is heel lastig op te sporen als je niet direct een overzicht van de formules hebt. Je kunt alle formules in ieder geval alvast zichtbaar maken via EXTRA - OPTIES - WEERGAVE. Hier kun je Formules aanvinken. Die worden dan in de spreadsheet getoond. Als je nu een afdruk maakt zullen deze formules ook op de afdruk te zien zijn.

Vergeet niet na het bekijken en/of afdrukken van de formules om de weergave weer terug te zetten in de oorspronkelijke situatie: ga naar EXTRA - OPTIES - WEERGAVE en haal het vinkje weg bij Formules.

 

In Excel 2007 vinden we dit via Opties voor Excel -> geavanceerd.

8.21      Meerdere bladen tegelijk optellen

  • Zet op blad 1 een aantal getallen in cel A1 en verder
  • Doe hetzelfde op blad 2
  • Ga op blad 3 in cel A1 staan en klik op Autosom
  • Klik op blad 1 en op cel A1
  • Klik met de SHIFT toets ingedrukt op blad 2 en klik op OK

 

Het resultaat is de formule =SOM(Blad1:Blad2!A1) en deze telt de cellen A1 van blad 1 en blad 2 bij elkaar op.

9         Printen

9.1           Afdrukken algemeen

Als eerste stap kunne we het beste naar bestand Þ afdrukken ... gaan.

 

 

Daar kunnen we namelijk bepalen wat we willen gaan afdrukken en welke pagina’s.

 

Vervolgens kijken we even naar het afdrukvoorbeeld via de knop Vorbeeld. Als het niet goed op het blaadje past, probeer dan eens om de afdruk op landscape (= liggend) te zetten via Bestand – pagina-instelling – pagina: staand of liggend.

 

 

Als dat niet afdoende is: op hetzelfde venster vinden we ook de mogelijkheid Verkleinen/vergroten tot.

 

Ook kunnen we het af te drukken gebied inperken: selecteer eerst dat deel van het werkblad dat je geprint wilt hebben en ga dan naar het menu Bestand – afdrukbereik –afdrukbereik bepalen. Vergeet niet dit afdrukbereik te wissen als je een ander bereik of het hele werkblad wil afdrukken.

 

In Excel 2007 gaat dit nu via pagina-indeling.

9.2           Afdrukken: rijen en kolommen vastzetten

Eerder bespraken we de opties Titels blokkeren en Splitsen. Maar in beide gevallen heeft dit geen effect op het afdrukken. Bij een afdruk die meerdere pagina’s beslaat, krijgen we geen herhaling van bepaalde rijen en/of kolommen. Hoe doen we dit wel?

 

  • Kies Bestand Þ Pagina-instelling Þ Blad

 

We krijgen dan het volgende scherm:

 

 

  • Plaats de cursor in het vakje rijen bovenaan .....
  • Kies de rijen die herhaald moeten worden door de rijlabels te  selecteren
  • Doe het zelfde met de kolommen
  • Controleer in het afdrukvoorbeeld of het een en ander gelukt is.

 

In Excel 2007 gaat dit nu via pagina-indeling.

10   Databases & lijsten

10.1      Data importeren van internet/intranet

Hoe krijgen we gegevens die we op een internet pagina zien staan snel in Excel? Een voorbeeld. We willen de uitslagen van de 4 mijl loop van Groningen uit 2002 onder loop nemen.

 

  • Op de website www.4mijl.nl kiezen we bij de uitslagen die van 2002
  • vervolgens kiezen we de Bedrijvenloop individueel
  • kies in Excel voor Data Þ Externe gegevens ophalen Þ Nieuwe Webquery

 

 

  • kopieer het adres uit de adresbalk van de browser naar witte vakje boven in
  • klik op OK

 

Alle uitslagen staan na enige tijd in het werkblad!

 

In Excel 2007 gaat dit nu via gegevens -> externe gegevens ophalen

10.2      Autofilter: aantal records als resultaat

Bij een lijst  kunnen we een zogenaamd autofilter aanzetten via Data Þ filter Þ autofilter. Op allerlei manieren kunnen we dan dat filteren uit bepaalde kolommen. Let er op dat Excel in de statusbalk het aantal records toont dat aan de criteria voldoet.

 

10.3      Autofilter en de aggregatiefuncties van de statusbalk

Als we eerst een kolom selecteren en we stellen dan een bepaald criterium in, dan toont de aggregatiefunctie (zie 8.14) op de statusbalk alleen het resultaat van de gefilterde cellen.

 

10.4      Autofilter: niet voor alle kolommen uit het lijstbereik

Als we autofilter aanzetten voor een lijst, zal Excel automatisch elke kolom voorzien van een zogenaamde lijstpijl. Hoe voorkomen we dat Excel dit automatisch voor elke kolom doet? Door eerst één of meer specifieke kolommen te selecteren en dan pas het autofilter aan te zetten.

10.5      Autofilter en subtotalen

Als je een kolom uit een lijst met een autofilter optelt met de Autosom knop, krijg je de volgende formule:

 

Doen we hetzelfde terwijl we eerst met het autofilter een selectie gemaakt heben, dan krijgen we een heel andere formule:

 

De functie luidt =SUBTOTAAL(9;F2:F283). In deze functie verwijst het getal naar de aggregatie die toegepast moet worden. In dit geval staat 9 dus voor SOM. De uitkomst van de functie is dat alleen de gefilterde waarden worden opgeteld.

 

Gaan we weer terug naar alle waarden dan krijgen we weer het totaal generaal.

 

Andere mogelijkheden dan sommeren:

 

Functie_getal
(inclusief verborgen waarden)

Functie_getal
(exclusief verborgen waarden)

Functie

1

101

GEMIDDELDE

2

102

AANTAL

3

103

AANTALARG

4

104

MAX

5

105

MIN

6

106

PRODUCT

7

107

STDEV

8

108

STDEVP

9

109

SOM

10

110

VAR

11

111

VARP

 

Met behulp van de waarden in de tweede kolom kun je bepalen of verborgen rijen buiten het eindresultaat gelaten moeten worden. In het navolgende voorbeeld worden de handmatig verborgen rijen 270 en 278 niet meegenomen in het eindresultaat.

 

10.6      Uitgebreid filter

Lijsten kunnen we in Excel filteren met behulp van de Autofilters.

 

 

Via deze Autofilters kunnen we wel op meerdere kolommen tegelijk filteren, maar het gaat dan wel om En voorwaarden: de lijst wordt gefilterd op zowel de ene als de andere voorwaarde. Een Of voorwaarde is op deze manier niet mogelijk.

 

Hoe krijgen we dat wel voor elkaar?

 

  • We voegen boven de bestaande lijst een viertal lege rijen in.
  • We kopiëren de kolomtitels naar de eerste rij
  • We plaatsen een voorwaarde in de tweede rij direct onder de kolomtitels
  • En eentje in de derde rij in een andere kolom

 

 

  • We kiezen dan Data Þ Uitgebreid filter

 

 

  • Zet in het bovenste vakje het bereik van de lijst
  • Zet in het tweede bereik het bereik van de voorwaarden: de bovenste drie rijen inclusief kolomtitels

 

 

  • Klik op Ok

 

10.7      Uitgebreid filter om dubbele waarden eruit te halen

Dit is feitelijk oneigenlijk gebruik maar wel handig. Stel we hebben een lijst waarbij van een bepaalde kolom alleen de unieke waarden willen tonen. We selecteren dan die bepaalde kolom en gaan naar Data Þ Uitgebreid filter:

 

 

We laten vervolgens het criteriumbereik leeg en vinken Alleen unieke records aan. Als we dan op OK klikken, krijgen we uit die kolom alleen de unieke waarden. De rest van de rij is automatisch meegefilterd.

 

In Excel 2007 is voor ontdubbelen een speciale knop toegevoegd, te vinden via Gegevens -> duplicaten verwijderen.

10.8      Sorteren op meer dan drie kolommen

Dat kan dus normaal niet. Als we naar data Þ sorteren gaan, zien we dat we op maximaal drie kolommen kunnen selecteren.

 

 

Hoe omzeilen we dit? Door alvast in omgekeerde volgorde van belangrijkheid in het lijstbereik zelf te gaan sorteren. Stel we moetn op vier kolommen sorteren. We gaan dan eerst  in de kolom staan met de laagste prioriteit en klikken op de sorteerknop: . Vervolgens gaan we naar data Þ sorteren en kiezen de overige drie kolommen.

10.9      Sorteren speciaal

Sorteren op alfabet of sorteren op getal is geen probleem. Dat doet Excel standaard. Maar wat als zeg maar de Nederlandse provincies in een standaard volorde willen zetten? Dat lossen we als volgt op.

 

  • Tik de Nederlandse provincies in de volorde Friesland, Groningen, Drenthe, Overijssel, Gelderland, Limburg, Noord Brabant, Zeeland, Zuid Holland, Noord Holland, Flevoland, Utrecht
  • Selecteer deze reeks
  • Kies Extra Þ Opties  Þ Aangepaste lijst
  • Klik op importeren
  • Sorteer de lijst op alfabet

 

Drenthe staat dan vooraan.

 

  • Kies Data Þ Sorteren

 

 

  • Klik op Opties

 

 

  • En kies als sorteervolgorde de eerder gemaakte lijst

 

10.10Tabel omzetten naar een Excel lijst

We kunnen in Excel een tabel omzetten naar een zogenaamde Excel lijst.

 

  • Kies Data -> lijst
  • Kies Lijst maken

 

 

Het volgende scherm verschijnt:

 

 

  • Klik op OK

 

De tabel wordt dan voorzien van een blauwe rand, het autofilter wordt aangezet en de werkbalk Lijst verschijnt:

 

 

  • Klik op de knop Totaalrij weergeven/verbergen

 

Onderaan verschijnt dan de volgende rij:

 

 

Bij elk van de kolom verschijnt een lijstpijl als we inde betreffende cel klikken. We kunnen dan bijvoorbeeld voor Gem kiezen. Excel kiest dan automatisch voor de Subtotaal functie met de volgende vorm: =SUBTOTAAL(101;F2:F283) (zie paragraaf 10.5). Deze functie is trouwens niet via de formulebalk te wijzigen maar alleen via de lijstpijlen.

11   Zoeken

11.1      Zoeken in meerdere lijsten

Met de index functie is het mogelijk zelf aan te geven in welk bereik er gezocht moete worden

 

 

De gebruikte functie staat in cel C4: =INDEX((D9:J18;D21:J30);C2;C3;C5). Met de inhoud van C5 geven we aan of de referende waarde gezocht moet worden in het bovenste, D9:J18, of het onderste bereik, D21:J30. Let vooral ook op de haakjes rond de verschillende bereiken: (D9:J18;D21:J30)

12   Grafieken

12.1      Veranderen van grafiekpunten

Als je een simpel lijn- of staafdiagram gemaakt hebt, kun je die één van de punten selecteren (op de reeks klikken, tot drie tellen en nog eens klikken op een specifiek punt) en dat omlaag of omhoogtrekken. De waarde in de tabel waarop de grafiek gebaseerd is, verandert dan mee.

 

 

Doe je dit bij een grafiek die gebaseerd is op een berekende reeks, dan verschijnt het doelzoekvenster:

 

 

Excel wil dan weten welke waarde van de waarden waarop de berekening gebaseerd is, je wilt aanpassen.

12.2      Taartpunten happen

Bij een taartdiagram kun je de taart uit elkaar trekken. Selecteer je de taart als geheel, dan valt hij helemaal in stukken uiteen. Kies je eerst één punt dan wordt dat ene punt er uit gelicht.

 

Zo dus:

 

 

12.3      Onderdelen afwijkend kleuren

Heb je een staafdiagram gemaakt en wil je één specifieke staaf anders kleuren? Je selecteert dan eerst deze ene staaf en klikt op de rechter muisknop. In het verschenen venstertje klik je op Gegevenspunt opmaken.

 

In dit venster klik je een andere kleur aan.

 

In Excel 2007 is dit veranderd. Kleuren kunnen nu variëren per punt, maar een punt kan ook een aprte kleur krijgen. Kies dan Opvulling, effen en zet Kleuren variëren per punt uit.

 

12.4      Een staaf opvullen met plaatjes

In een staafdiagram kun je één of meerdere staven laten vullen met plaatjes. Ga net zo te werk als bij 12.3 maar klik nu op opvuleffecten.

 

 

En dan op figuur:

 

 

Hier klik je op figuur selecteren. Als je een plaatje uitgezocht hebt, ziet het venster eruit zoals hierboven. Je ziet dan het dan zelfs mogelijk is de plaatjes te stapelen. Zo worden dus de grafieken gemaakt van de omzet van Heineken: met gestapelde bierglazen!

 

Op dezelfde manier kun je trouwens elk ander onderdeel van de grafiek voorzien van een plaatje!

12.5      Schaal van de grafiek veranderen

Excel laat de schaal automatisch lopen van 0 tot even boven de hoogste waarde.

 

 

Je kunt deze schaal aanpassen door de y-as te selecteren en op de rechte muis knop te klikken. Klik dan op as opmaken en het volgende venster verschijnt:

 

 

 

Vul dan bij het minimum bijvoorbeeld 20 in.

 

Zit er bijvoorbeeld een negatieve waarde bij, dan loopt de staaf zelfs door het label heen. Dit is dus ook te verhelpen door het aanpassen van de as.

 

12.6      Grafiek met dubbele Y-as

Als we zoals in onnderstaande grafiek appels met peren vergelijken, kunnen we niet zien dat de trend voor beide reeksen gelijk is. Dat lukt wel als we elk van de reeksen zijn eigen as geven.

We doen dat door één van de twee reeksen aan te klikken en te kiezen voor Gegevenreeks opmaken:

 

 

Op het tablad As kiezen we dan voor Secundaire as.

 

Probleem is dan wel dat beide reeksen elkaar precies overlappen. We kunnen dat oplossen door bij het tabblad Opties de breedte van de tussenruimte aan te passen.

 

 

We kunnen ook – terug in de grafiek – één van de reeksen selecteren en dan kiezen voor lijngrafiek via de knop . We krijgen dan het volgende beeld:

 

12.7      Trend in een grafiek

Stel we hebben een lijngrafiek met een honderdtal waarden. Hoe voegen we daar een trendlijn aan toe? We selecteren de lijn in de grafiek en klikken op de rechter muisknop.

 

 

We krijgen dan een dialoogvenster met onder meer de optie Trendlijn toevoegen …. Als we deze optie kiezen, krijgen we het volgende venster:

 

 

Hier kunnen we kiezen voor de optie Zwevend gemiddelde met vijf perioden. We krijgen dan in de grafiek een trendlijn vanaf de vijfde waarde:

12.8      Flexibele bereiken in een grafiek

Normaal gesproken past een eens gekozen bereik in een grafiek zich niet automatisch aan als het bedoelde bereik groter of kleiner wordt.

 

 

Dat is wel het geval als de gekozen bereiken namen zijn:

 

 

De namen (via menu invoegen) moeten dan wel met behulp van de functie verschuiving zijn aangegeven:

 

 

Bedrag =VERSCHUIVING(draaitabel!$E$2;0;0;AANTALARG(draaitabel!$A:$A)-1;1)

Weeknr =VERSCHUIVING(draaitabel!$A$2;0;0;AANTALARG(draaitabel!$A:$A)-1;1)

 

Bij verandering van het bereik zal de grafiek zich nu automatisch aanpassen.

 

 

13   Draaitabellen

13.1      Dynamisch bereik

Een probleem bij de draaitabellen van Excel is dat het bereik niet automatisch bijgesteld wordt als we dit veranderen c.q. groter of kleiner maken. Dat probleem is te ondervangen.

 

  • Maak eerst een benoemd bereik van het gebied van de draaitabel
  • Kies Invoegen->naam->definiëren

 

 

  • Tik daar  een naam
  • En laat die verwijzen naar de formule:

 

=VERSCHUIVING(draaitabel!$A$1;0;0;AANTALARG(draaitabel!$A:$A);AANTALARG(draaitabel!$1:$1))

 

Deze formule verwijst naar het gebied dat begint met A1 en doorloopt naar beneden net zoveel rijen als in de kolom $A:$A geteld worden, en net zoveel kolommen als in de $1:$1 geteld worden.

 

  • De draaitabel zelf baseren we dan op genoemde bereik

 

 

Elke keer als het bereik verandert, hoeven we alleen nog maar op de knop vernieuwen in de draaitabelbalk te klikken

 


 

13.2      Groeperen op datum

Stel we maken een draai tabel van de volgende gegevens:

 

 

Het eerste resultaat ziet er als volgt uit:

 

 

Excel 2002

In Excel 2002 hebben we een extra dialoogvenster gekregen:

 

 

Via dit dialoogvenster kunnen we extra velden toevoegen aan onze draaitabel. Het venster is uit te zetten via een knop op de draaitabelbalk.

 

Vervolgens kunnen we groeperen op datum door met de rechtermuisknop op Orderdatum te klikken.

 

 

We kiezen dan Overzicht Þ Groeperen ...

 

 

Hier klikken we maanden, kwartalen en jaren aan. Let er op dat we ook kunnen groeperen op aantal dagen. Perioden van bijvoorbeeld een week of veertien dagen zijn dus ook mogelijk. We krijgen dan het volgende beeld:

 

 

We kunnen vervolgens heel gemakkelijk met de muis desgewenst de jaren, kwartalen en maanden in een andere volgorde zetten.

13.3      Groeperen op getallen

Stel we willen weten hoeveel producten in welke bruto omzetscategorie vallen. We moeten dan groeperen op bruto omzet, op getallen dus. We krijgen dan dit venster:

 

 

Als we dan beginnen bij instellen op 0 en eindigen op 16000 krijgen we het volgende beeld:

 

13.4      Uitkomst weergeven als percentage van de kolom

Als we in het bovenste voorbeeld dubbelklikken op aantal van productnaam, krijgen we het volgende venster:

 

 

Hier klikken we op opties:

 

 

Bij gegevens weergeven als kiezen we voor % van kolom. We krijgen dan het volgende beeld:

 

 

% van totaal had in dit geval hetzelfde opgeleverd. Hebben we met twee assen te maken zoals in dit voorbeeld:

 

 

dan kunnen we zowel % van kolom, % van rij als % van totaal gebruiken met elk een verschillende en bruikbare uitkomst.

13.5      Running totals of cumulatief

In het vorige scherm hadden we ook kunnen kiezen voor: voorlopig totaal in

 

 

Dat levert een cumulatief op:

 

13.6      Zelf groepen maken

Stel je voor dat je zelf groepen zou willen maken. Laten we eens kijken naar het volgende voorbeeld:

 

 

In dit voorbeeld willen we de categorieën Vis, Vlees en Zuivel in één groep onder brengen. We doen dat als volgt:

 

  • Met de CTRL toets ingedrukt selecteren we tegelijkertijd Vis, Vlees en Zuivel

 

 

  • Dan klikken we rechts op één van de geselecteerde cellen
  • Vervolgens kiezen we Overzicht en details -> Groeperen

 

We krijgen dan als resultaat:

 

 

We kunnen het beeld verder verfijnen door de tweede groep te verwijderen en de naam van de eerste groep aan te passen.

 

 

Door vervolgens rechts te klikken op Groep1 en te kiezen voor Overzicht en details -> Details weergeven-> Categorienaam, krijgen we het volgende beeld:

 

 

Dubbelklikken op Groep1 zet dit dan beurtelings aan en uit!

 

Rechts klikken op Groep1 en kiezen voor veldinstellingen, levert ook nog nieuwe mogelijkheden op.

 

 

Indeling, normaal grijs, is nu ook aan te klikken.

 

 

De naam Groep1 kunnen we veranderen door er gewoon overheen te typen.