MICROSOFT EXCEL 2010 DEEL 2

MICROSOFT EXCEL 2010 DEEL 2

OVER OGD OGD is een ict-dienstverlener met vijf vestigingen en ruim 700 ambitieuze en hoogopgeleide medewerkers. Wij zijn experts op het gebied van ict-infrastructuur, servicemanagement en softwareontwikkeling en helpen onze klanten door middel van detachering, uitbesteding, projecten en advies. Dat doen we tegen gunstige tarieven op een persoonlijke en informele manier. Openheid en eerlijkheid staan voorop en heldere communicatie is onze basis voor succesvolle samenwerkingen. We zijn slim, inventief en werken volgens een flexibele en onafhankelijke benadering. Zo komen we samen met u tot de juiste oplossing. Hierbij maken we gebruik van een breed netwerk van kennispartners en leveranciers. Wilt u meer informatie over OGD? Neem dan contact met ons op via telefoonnummer 088 65 00 000, of kijk op www.ogd.nl.

Uitgegeven door OGD ict-diensten, Delft Copyright © 2011 OGD ict-diensten Niets uit deze uitgave mag worden verveelvoudigd en/of openbaar gemaakt door middel van druk, fotokopie, microfilm, geluidsband, elektronisch of op welke andere wijze ook, en evenmin in een retrieval system worden opgeslagen zonder voorafgaande schriftelijke toestemming van de uitgever. Hoewel deze cursus met zeer veel zorg is samengesteld, aanvaardt de uitgever geen enkele aansprakelijkheid voor schade ontstaan door eventuele fouten en/of onvolkomenheden in deze uitgave.

HOE KUNT U DEZE HANDLEIDING GEBRUIKEN? Stap voor stap handelingen 1 2 Deze handleiding is niet…

‘Lastige’ onderwerpen…

Gebruik deze pagina voor aantekeningen…

Er is altijd meer te ontdekken!

Let op Vaak voorkomende situaties en valkuilen vindt u hier.

De onderwerpen in deze handleiding zijn via tekst en afbeeldingen kort beschreven. Stap voor stap volgt u handelingen om de basis onder de knie te krijgen. Deze handleiding is niet een handleiding waarin àlle details worden behandeld! Gelukkig maar, anders zou de praktische opzet van deze handleiding teniet gaan. We hechten juist waarde aan het onder de knie krijgen van de basishandelingen. Dit zijn de handelingen die u veel zult gebruiken en daarmee bespaart u uiteindelijk tijd. Lastige onderwerpen zijn vaak voldoende omschreven op het internet en in naslagwerken. In deze handleiding besteden we hieraan minder aandacht. U hebt wel de mogelijkheid om hiermee aan de slag te gaan tijdens de cursus met behulp van oefenbestanden. Tijdens de cursus kunt u uw eigen aantekeningen maken die van toepassing zijn op de onderwerpen die worden besproken.

Op internet is veel te vinden en we moedigen aan om zelfstandig meer informatie op te zoeken over de beschreven onderwerpen. Geaccentueerde trefwoorden dienen hierbij als startpunt. Tip

Zie ook Extra tips worden hier beschreven.

Verwijzingen naar andere plekken in de handleiding vindt u hier.

Deze handleiding – het vervolg Excel 2 … Tijdens de cursus Excel 2 past u vaardigheden toe die u geleerd hebt tijdens de cursus Excel 1 en leert u nieuwe vaardigheden die praktisch zijn bij het maken van overzichten en grotere werkbladen. Daarnaast leert u meer over mogelijkheden om gegevens op een visueel aantrekkelijke manier te presenteren. U leert hoe u gegevensinvoer kunt beperken: door een vervolgkeuzelijst te maken of door criteria in te voeren waaraan gegevens moeten voldoen. Het gebruik van functies wordt verder uitgediept: doorzoek met behulp van zoek- en verwijsfuncties een tabel met zoektermen die u zelf invoert.          



 

Leer nieuwe mogelijkheden met Selecteren speciaal om vervolgens grote hoeveelheden gegevens aan te passen. Maak gebruik van Celstijlen voor een consistente opmaak – ook voor meerdere werkmappen. Pas de getalnotatie aan in meer detail met Aangepaste getalnotatie. Presenteer gegevens op een visueel aantrekkelijke manier met behulp van Sparklines en Voorwaardelijke opmaak. Verwerk een lijst met behulp van Opmaken als tabel en ontdek de vele voordelen. Maak een samenvatting van gegevens uit een lijst met behulp van een Draaitabel. Gebruik Slicers om in één oogopslag te zien welke categorieën van de lijst worden meegenomen in de Draaitabel. Verwijder duplicaten uit een lijst op verschillende manieren en ontdek welke manier het meest praktisch is in verschillende situaties. Creëer subtotalen binnen lijsten. Tekst naar kolommen: gegevens die u hebt aangeleverd gekregen kunt u opsplitsen in meerdere kolommen zodat u op een effectieve manier gebruik kunt maken van bijvoorbeeld sorteren en filteren. Maak gebruik van gegevensvalidatie en voorkom hiermee invoer van onjuiste gegevens in een tabel, of creëer een vervolgkeuzelijst die u kunt gebruiken bij het opzetten van een invulformulier. Leer de basis over vergelijkingen in Excel en hoe u deze toepast in bijv. Zoek- en verwijsfuncties. Leer hoe u een aantal Zoek- en verwijsfuncties kunt toepassen bij gebruik van tabellen. Voorbeelden hiervan zijn SOM.ALS , VERT.ZOEKEN, INDEX en VERGELIJKEN.

INHOUDSOPGAVE Getallen, notatie en visuele presentatie 1 2 3 4 5

Selecteren speciaal .......................................................................................................... 2 Cellen opmaken met celstijlen ...................................................................................... 4 Getalnotatie ...................................................................................................................... 6 Werken met Voorwaardelijke opmaak ....................................................................... 8 Werken met Sparklines ................................................................................................. 12

Overzichten en lijsten 6 7 8 9 10

1

15

Werken met subtotalen ............................................................................................... 16 Lijsten verwerken met Opmaken als tabel .............................................................. 18 Gegevens samenvatten met een draaitabel........................................................... 20 Gegevens opsplitsen in meerdere kolommen ........................................................ 24 Duplicaten verwijderen ................................................................................................ 26

Structuur aanbrengen in werkbladen

29

11 Werken met Celnamen ................................................................................................ 30 12 Een vervolgkeuzelijst maken met Gegevensvalidatie ........................................... 36 13 Gegevensinvoer beperken met Gegevensvalidatie ............................................... 38

Vergelijkingen, formules en functies 14 15 16 17 18 19 20

Index

41

Vergelijkingen invoeren ............................................................................................... 42 De functie als................................................................................................................... 44 De functie aantal.als ..................................................................................................... 46 De functie som.als .......................................................................................................... 48 De functie aantallen.als ................................................................................................ 50 De functie verticaal.zoeken .......................................................................................... 52 De functies index en vergelijken gecombineerd .................................................... 54

57

Getallen, notatie en visuele presentatie

1 SELECTEREN SPECIAAL Selecteren speciaal is een opdracht in Excel die u altijd in combinatie gebruikt met andere opdrachten – net zoals bij ‘gewoon’ selecteren. Hieronder worden een aantal praktische situaties omschreven. Selecteren speciaal is zeer geschikt om binnen een gekozen huidige selectie een verfijning aan te passen: Selecteer eerst de cellen waarbinnen u wilt zoeken naar bijvoorbeeld ‘lege waarden’ en geef vervolgens aan dat u deze ‘lege waarden’ wilt selecteren. Optie

Omschrijving

Lege waarden

Selecteer de ‘gaten’ in een lijst en bewerk deze: verander de opmaak, verander de inhoud, of verwijder eventueel deze cellen. Voordat u de cellen selecteert, kunt u bijvoorbeeld de kolom of reeks cellen selecteren waarbinnen u de lege waarden wilt selecteren. Laat cellen met formules opvallen door bijvoorbeeld de opmaak te veranderen van deze cellen. Dit kan de leesbaarheid van een werkblad verbeteren, of een handig hulpmiddel zijn om formules te doorlopen. Selecteer constanten binnen de huidige selectie en laat deze opvallen. Bij zowel het selecteren van formules en constanten kunt u aangeven of u een combinatie van getallen, tekst, logische waarden of cellen met fouten wilt selecteren. Selecteer slechts de zichtbare cellen: cellen die niet zichtbaar zijn doordat ze zich bevinden in niet-zichtbare groepering van rijen of kolommen cellen, of doordat ze zich bevinden in rijen of kolommen die verborgen zijn, worden niet geselecteerd.

Formules

Constanten

Zichtbare cellen

Tip

Tijdens het bewerken van de inhoud van de cel bevestigt u met de Enter-toets dat u de gemaakte wijziging aan de inhoud van een enkele cel – de actieve cel. Met de toetsencombinatie Ctrl + Enter doet u dit voor de volledige selectie. 2 Selecteren speciaal

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

Selecteren speciaal 3

2 CELLEN OPMAKEN MET CELSTIJLEN De opmaak van cellen kunt u vastleggen in Celstijlen, zodat u deze in één klik kunt toepassen op gewenste cellen. Op een later tijdstip kunt u deze celstijlen aanpassen wat effect heeft op alle cellen die zijn opgemaakt met de betreffende celstijl binnen de werkmap.

2

3 1

4

Tip

1. 2. 3.

4. 5. 6.

7. 8.

Selecteer de cellen die u wilt aanpassen. Kies een celstijl uit: ga naar tabblad Start, Lintgroep Stijlen en kies Celstijlen. Klik op de gewenste celstijl om deze toe te passen. Zweef met de muis boven een celstijl om een voorbeeld hiervan weer te geven. Kies eventueel uw eigen aangepaste opmaak. Klik op Nieuw celstijl en volg de volgende stappen. Vul een omschrijvende naam in voor de celstijl Pas de opmaak aan: het dialoogvenster Celeigenschappen verschijnt waarin u de aanpassingen kunt maken, Vink aan welke eigenschappen de Celstijl bevat Klik op OK.

4 Cellen opmaken met celstijlen

5 6 7

8 Tip Gebruik eventueel celstijlen uit andere werkbladen met behulp van de opdracht Samenvoegen.

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

Cellen opmaken met celstijlen 5

3 GETALNOTATIE Het invoeren van speciale gegevens zoals percentages, datums en tijden Bij het invoeren van gegevens in cellen zal Excel in sommige gevallen de getalnotatie en de waarde waarmee Excel daadwerkelijk rekent automatisch aanpassen. Dit kan soms verwarrend zijn omdat Excel hierover geen melding geeft. Vaak zal dit echter tot uw voordeel werken en ook de snelheid en het gemak waarmee u kunt werken vergroten. Onderstaande tabel geeft een aantal praktische voorbeelden. De informatie onder de kolommen ‘getalnotatie’ en ‘waarde’ hoeft u niet tot in detail te doorgronden. Deze informatie dient slechts ter illustratie en extra informatie. Dit inv oeren… …geeft dit als resutaat ...met deze getalnotatie Percentages 5% 5% Percentage 5,23% 5,23% Percentage, 2 decimalen

Waarde w aarmee Exc el rekent 0,05 0,0523

Gebruik van schuine strepen en min-tekens 1/1/2010 1-1-2010 Datum 1-1-2010 1-1-2010 Datum

De datum 1-1-2010 De datum 1-1-2011

Ongewenste en onduidelijke datumnotatie 1-13 jan-13 Aangepaste datumnotatie

De datum 1-1-2013

2-cijf erige jaren 1-1-29 1-1-30

1-1-2029 Datum 1-1-1930 Datum

Let op: Tot het jaar '30 valt een jaartal in de 21e eeuw.

Tijden 10:06 10:06:40

10:06 Aangepaste tijdsnotatie 10:06:40

Tijdstip 10:16 Tijdstip 10:06:40 (40 seconden)

Ongeldige tijdsnotatie 15:61

6 Getalnotatie

0,667361111 Standaard

0,667361111

Excel 2

Aangepaste getalnotatie Elke getalnotatie die u kiest uit een van de categorieën bestaat uit een bepaalde code: de syntax. Wanneer u een aangepaste getalnotatie wilt toevoegen en gebruiken, kiest u in het dialoogvenster Celeigenschappen onder tabblad Getal de categorie Aangepast. Typ eventueel de aangepaste getalnotatie, voeg deze toe en selecteer deze. De volgende tabel geeft een aantal praktische voorbeelden voor het instellen van een aangepaste getalnotatie. Een volledig overzicht vindt u in de Microsoft Excel help onder trefwoorden aangepaste getalnotatie. Syntax u:mm:ss [u]:mm:ss [u]:mm 0. 0.. ;;;

Omschrijving Geeft het tijdstip op de dag aan, inclusief minuten en seconden. Geeft het aantal uren weer, inclusief de minuten en seconden. Geeft het aantal uren weer, inclusief de minuten. Geeft het getal weer in duizendtallen. Geeft het getal weer in miljoentallen. Verbergt het getal: de notatie van tekst, het getal 0, positieve getallen en negatieve getallen is onzichtbaar gemaakt.

Tip

Tip

Schrijf in de tabel hierboven uw eigen aangepaste getalnotaties.

Excel 2

Voordat u de getalnotatie aanpast, kunt u deze baseren op een standaard getalnotatie, zodat u niet de gehele getalnotatie zelf hoeft op te bouwen: kies een standaard getalnotatie uit, klik vervolgens op de categorie Aangepast en pas de syntax aan. Getalnotatie 7

4 WERKEN MET VOORWAARDELIJKE OPMAAK Met Voorwaardelijke opmaak kunt u gegevens in het werkblad visueel ondersteunen. U kunt bijvoorbeeld trends zichtbaar maken in een reeks getallen door middel van gegevensbalken, kleurenschalen of pictogrammen. U kunt binnen een enkele cel meerdere opmaakregels toepassen.

Cellen opmaken op basis van celwaarden 1. 2. 3.

4.

Selecteer de cellen waarop u de voorwaardelijke opmaak wilt toepassen. Ga in tabblad Start naar Lintgroep Stijlen en kies Voorwaardelijke opmaak. Bekijk de verschillende mogelijkheden in de galerie door met de muis te zweven boven de verschillende opties. Bevestig uw keuze door een voorwaardelijke opmaak aan te klikken

1

2

8 Werken met Voorwaardelijke opmaak

Let op De opties voor voorwaardelijke opmaak onder de categorie Regels voor bovenste/onderste hebben aanvullende informatie nodig: de grenswaarden moeten hierbij worden aangegeven. Ook bij voorwaardelijke opmaak onder de categorie Markeringsregels voor cellen heeft Excel extra informatie nodig zoals tekst die voorkomt in een cel of de datum die in de cel staat.

3 4

Excel 2

Opmaakregels beheren U kunt een overzicht oproepen van toegepaste opmaakregels. Dit overzicht geeft naast het bereik waarop de regels van toepassing zijn ook de mogelijkheid om opmaakregels aan te passen. 1. Ga in tabblad Start naar Lintgroep Stijlen en kies onder Voorwaardelijke opmaak de optie Regels beheren. 2. Kies welke regels u wilt weergeven: bijvoorbeeld regels voor Huidige selectie. 3. Pas eventueel het cellenbereik aan waarop de opmaakregel van toepassing is. 4. Bewerk een regel door deze te selecteren en vervolgens Regel bewerken te kiezen. 5. Verwijder naar wens geselecteerde regels. 6. Klik Toepassen om het tussenresultaat te bekijken. 7. Klik OK om de aanpassingen te bevestigen. …of klik Annuleren om de aanpassingen ongedaan te maken.

1

2

3

4 5 6 7

Excel 2

Werken met Voorwaardelijke opmaak 9

Aangepaste Voorwaardelijke opmaak Excel biedt u veel vooraf ingestelde opties binnen Voorwaardelijke opmaak. Zo kunt u in de meeste situaties uit de voeten met de vooraf ingestelde mogelijkheden. Wanneer u aanpassingen wilt maken op een bestaande opmaakregel of een nieuwe opmaakregel wilt maken kan dit ook. 1. Selecteer de cellen waarop u de voorwaardelijke opmaak wilt toepassen. 2. Ga in tabblad Start naar Lintgroep Stijlen en kies onder Voorwaardelijke opmaak de optie Nieuwe regel. Het dialoogvenster Nieuw opmaakregel verschijnt. 3. Selecteer het type opmaakregel. 4. Voer aanvullende informatie in die van toepassing is op de gekozen type opmaakregel en stel de gewenste opmaak in. 5. Klik op OK. 1 2

3

4

5 10 Werken met Voorwaardelijke opmaak

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

Werken met Voorwaardelijke opmaak 11

5 WERKEN MET SPARKLINES Sparklines is een nieuwe toevoeging aan Excel 2010. Met Sparklines kunt u eenvoudig een trendgrafiek creëren binnen een cel.

Sparklines invoegen 1. 2.

3.

Selecteer de cel waarbinnen u de Sparkline wilt maken. Klik op tabblad Invoegen en kies binnen Lintgroep Sparklines het type Sparkline die u wilt toepassen; Lijn, Kolom of Winst/verlies. Het dialoogvenster Sparklines maken verschijnt. Selecteer het gegevensbereik en klik op OK.

Let op Sparklines wordt niet ondersteund in eerdere versies van Excel. Bij het opslaan onder een oudere bestandsindeling, worden cellen met Sparklines niet opgeslagen in het bestand.

Tip U kunt met de vulgreep het bereik van de Sparklines uitbreiden.

2

1

3

12 Werken met Sparklines

Excel 2

Sparklines aanpassen 1. 2. 3.

4. 5. 6. 7.

Klik een van de cellen aan waar de Sparklines zich bevinden die u wilt aanpassen. Ga naar tabblad Ontwerpen onder Hulpmiddelen voor Sparklines. Gebruik Gegevens bewerken om opnieuw te kiezen welke cellen u gebruikt voor de gegevens en in welke cellen u de Sparklines plaatst. Pas de vormgeving aan onder Lintgroepen Type en Weergeven. Pas eventueel de vormgeving aan in de galerij. Gebruik de opdracht As opmaken om een aangepaste schaalverdeling in te voeren. Wis eventueel Sparklines: kies voor het wissen van de geselecteerde Sparklines of de geselecteerde Sparklinegroepen.

3

4

5

2

6

7

1

Excel 2

Werken met Sparklines 13

Overzichten en lijsten

6 WERKEN MET SUBTOTALEN U kunt gegevens samenvatten in een lijst m.b.v. subtotalen. In de afbeelding ziet u hoe u per woonplaats in de lijst een subtotaal creëert voor de categorie ‘reiskosten’. Let hierbij op dat u de lijst sorteert op de categorie waarop u het subtotaal wilt baseren – in deze situatie ‘woonplaats’. Excel creëert de benodigde formules, voegt de rijen en kolommen in voor de subtotalen en creëert groepen. 1. Sorteer de kolom in de lijst waarvan u de subtotalen wilt creëren. 2. Ga in tabblad Data naar Lintgroep Overzicht en kies opdracht Subtotaal. 3. Kies de kolom uit waarop u het subtotaal wilt baseren, bijvoorbeeld ‘woonplaats’. 4. Kies de functie die gebruikt wordt om het subtotaal te berekenen, bijvoorbeeld som. 5. Selecteer de kolommen waarvan u een subtotaal wilt creëren, bijvoorbeeld ‘reiskosten’. 6. Pas eventueel opties aan voor het maken van de subtotalen. 7. Klik op OK. 1 2

3 4 5 6 7

16 Werken met subtotalen

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

Werken met subtotalen 17

7 LIJSTEN VERWERKEN MET OPMAKEN ALS TABEL Wanneer u informatie verwerkt dat u kunt indelen als een lijst met verschillende categorieën verdeeld over kolommen, kunt u vaak gebruik maken van de opdracht Opmaken als tabel. Excel biedt u verschillende manieren om het werken met informatie in tabelvorm gemakkelijk te maken. Voorbeelden hiervan zijn: sorteren en filteren, totaalrijen invoegen en gegevens uitbreiden.

De tabel maken 1.

2. 3. 4. 5. 6. 7.

Voer eerst de data in. Het is niet nodig om alle data van tevoren gereed te hebben: u kunt later de gegevens uitbreiden. Let hierbij op dat u gebruik maakt van kolomkoppen met een omschrijving van de data die in de cellen eronder staat. Plaats de cursor in een van de cellen die u wilt opmaken als tabel. Ga naar tabblad Start en kies de opdracht Opmaken als tabel. Kies een opmaak uit in de Galerij van tabelstijlen. Het dialoogvenster Opmaken als tabel verschijnt. Controleer het cellenbereik dat Excel zal opmaken als tabel en pas dit eventueel aan Controleer of de optie De tabel bevat kopteksten is aangevinkt. Klik op OK. 1 2 3 4

Tip U kunt een nieuwe rij toevoegen aan de tabel door de cursor te plaatsen naar de cel rechts onderaan de tabel en vervolgens op de Tab-toets te drukken.

Tip Wanneer u een nieuwe kolomkop invoert in de cel aangrenzend aan de tabel, wordt deze automatisch opgenomen in de tabel.

5

18 Lijsten verwerken met Opmaken als tabel

6

7

Excel 2

Werken met de tabel 1.

2. 3.

4. 5.

6.

Selecteer een willekeurige cel die zich binnen de tabel bevindt. Het tabblad Ontwerpen onder Hulpmiddelen Tip voor tabellen wordt zichtbaar. Wanneer u met de cursor in de Vink aan welke elementen u zichtbaar wilt maken in de tabel staat en naar beneden tabel. scrolt, blijven de kolomkoppen Vink de totaalrij aan (zie stap 2) en kies onder een automatisch zichtbaar. gewenste kolom een optie uit voor de totaalrij, In deze situatie is het overbodig bijvoorbeeld gemiddelde. om te werken met de opdracht Gebruik de formaatgreep in de rechter onderhoek om Cellen blokkeren. het bereik van de tabel uit te breiden. Ga met de cursor staan op de cel rechtsonder in de tabel en druk op Tab om een regel toe te voegen aan de tabel. Dit is ook een manier om het bereik van de tabel uit te breiden. Gebruik het uitklapmenu om gegevens te sorteren en filteren. Zie meer informatie hierover in het hoofdstuk Lijsten filteren in de handleiding Excel 1.

2 Tip

1

6

5 3

Excel 2

4

Lijsten verwerken met Opmaken als tabel 19

8 GEGEVENS SAMENVATTEN MET EEN DRAAITABEL Een draaitabel in Excel geeft u de mogelijk om informatie uit tabellen samengevat weer te geven in een overzicht. De informatie in een draaitabel kan gemakkelijk op verschillende manieren worden gerangschikt. Dit rangschikken is het ‘draaien’ van de tabel.

Een draaitabel maken 1. 2. 3. 4.

5. 6.

Selecteer de gegevens die u wilt gebruiken voor de draaitabel. …of klik ergens in deze lijst. Ga naar tabblad Invoegen en klik op Draaitabel. Controleer of de reeks cellen overeenkomt met deze die u wilt gebruiken voor de draaitabel. Pas dit eventueel aan. Kies waar in de spreadsheet u de draaitabel wilt plaatsen: In een nieuw werkblad of bestaand werkblad. Als u voor de laatste optie kiest geeft u aan welke cel de linker bovenhoek wordt van de draaitabel. Klik op OK. Het venster Lijst met draaitabelvelden verschijnt. Kies welke velden u wilt weergeven in de draaitabel en sleep de velden naar de gewenste plek. 1

6

2

3

4 5

20 Gegevens samenvatten met een draaitabel

Excel 2

Een draaitabel maken (vervolg) 7.

8.

Verander eventueel op welke manier de waarden worden berekend en weergegeven: ga in tabblad Opties naar Lintgroep Bereik en kies Waarden samenvatten per en Waarden weergeven als. Gebruik de opdrachten in tabblad Ontwerpen onder Lintgroep Indeling om in te stellen op welke manier u subtotalen en eindtotalen wilt weergeven.

8

Excel 2

Tip Als u een tabel heeft gemaakt via Opmaken als tabel, kunt u een draaitabel maken via de opdracht Samenvatten met draaitabel. Deze opdracht vindt u in tabblad Ontwerpen onder Lintgroep Extra.

7

Gegevens samenvatten met een draaitabel 21

De draaitabel gebruiken Wanneer u de indeling hebt opgezet kunt u in meer detail werken met de gegevens in de draaitabel. U kunt de gegevens filteren en sorteren op dezelfde manier als u met tabellen ook kunt doen. 1. Gebruik de verschillende uitklapmenu’s in de draaitabel om gegevens te sorteren en filteren. 2. U kunt subcategorieën aanmaken door meerdere velden te verslepen naar één gebied in de draaitabel, bijvoorbeeld Rijlabels. Deze subcategorieën verschijnen in de draaitabel als een lijst met items dit u kunt in en uitklappen. 3. Als u andere gegevens wilt gebruiken voor de draaitabel en het ontwerp ongewijzigd wilt laten kiest u in tabblad Opties voor opdracht Andere gegevensbron.

1

3

22 Gegevens samenvatten met een draaitabel

2

Excel 2

Gegevens filteren met Slicers Nieuw in Excel 2010 zijn Slicers. Met Slicers kunt u gegevens uit de draaitabel filteren, zoals u ook kunt doen zoals hiervoor besproken. Het verschil is dat het overzicht van gegevens die wel en niet zichtbaar zijn in de draaitabel wordt weergegeven met behulp van een Slicer. Slicers zijn objecten in het werkblad waarmee u kunt instellen welke gegevens zichtbaar zijn in de draaitabel. Dit is hetzelfde als een filter toepassen op de draaitabel. Slicers zijn echter niet zomaar opdrachten die bestaan in de gebruikersinterface van Excel, maar zijn objecten in een Excel werkblad die kunnen worden afgedrukt. Tip 1. Klik in de draaitabel waarbij u een Slicer wilt toepassen. Selecteer meerdere items 2. Ga in tabblad Invoegen naar Lintgroep Filter en kies tegelijkertijd met behulp van de opdracht Slicer. Ctrl-toets om deze weer te …of klik ga in tabblad Opties naar Lintgroep Sorteren en geven. filteren en kies opdracht Slicer invoegen. 3. Kies uit de lijst de draaitabelvelden waarvoor u een Slicer Tip wilt aanmaken en klik op OK. Gebruik het tabblad Opties De Slicers verschijnen in het werkblad. onder Hulpmiddelen voor Slicers 4. Gebruik de opdrachten in het Lint om de vormgeving van om de vormgeving van Slicers de Slicers aan te passen. aan te passen. 5. Gebruik eventueel de formaatgrepen om grootte van de Slicers aan te passen 2 3 6. Gebruik de Slicers: klik in de lijst om op de verschillende items te filteren. 1

5 6 Tip

Excel 2

Gegevens samenvatten met een draaitabel 23

9 GEGEVENS OPSPLITSEN IN MEERDERE KOLOMMEN Wanneer u te maken hebt met samengevoegde gegevens binnen één cel kunt u deze opsplitsen in meerdere kolommen. Nadat u de gegevens hebt opgesplitst in meerdere kolommen kunt deze gegevens u bijvoorbeeld sorteren en filteren. 1. Selecteer het cellenbereik met de data die u wilt opsplitsen. 2. Ga in tabblad Data naar Lintgroep Hulpmiddelen voor gegevens en kies Tekst naar kolommen.

2

1

24 Gegevens opsplitsen in meerdere kolommen

Excel 2

GEGEVENS OPSPLITSEN IN MEERDERE KOLOMMEN (vervolg) 3.

4.

5. 6. 7.

Kies welke methode u wilt gebruiken om de data op te splitsen: gescheiden of vaste breedte. …en klik op Volgende. Kies de opties voor het opsplitsen van de data: welke scheidingstekens u gebruikt. …of geef het aantal karakters aan voor het opsplitsen van de data als u gebruikt maakt van de optie Vaste breedte. …en klik op Volgende. Geef per kolom aan welk gegevenstype u wilt toepassen. Bekijk het voorbeeld en pas eventueel de instellingen aan. Klik op Voltooien en bekijk het resultaat.

3

4

5

6 7 Tip Niet in iedere situatie zal het mogelijk zijn in één handeling de data te plaatsen waar u wilt. Het kan voorkomen dat u naderhand gegevens moet verplaatsen om het gewenste resultaat te verkrijgen. Excel 2

Gegevens opsplitsen in meerdere kolommen 25

10 DUPLICATEN VERWIJDEREN Naarmate een lijst groter wordt, wordt het lastiger om duplicaten te vinden en te verwijderen. Excel kan een lijst doorzoeken en duplicaten verwijderen. Een aantal methoden worden besproken.

De opdracht Duplicaten verwijderen in het Lint Met deze methode om duplicaten te verwijderen hebt u het minst aantal handelingen nodig vergeleken met andere methodes die worden beschreven in dit hoofdstuk. Het nadeel hierbij is dat Excel slechts aangeeft hoeveel dubbele waarden er zijn verwijderd, niet welke. 1. Selecteer de lijst die u wilt doorzoeken op duplicaten. …of klik op een willekeurige plek in deze lijst. 2. Ga in tabblad Gegevens naar Lintgroep Gegevenshulpmiddelen en klik op Duplicaten verwijderen. 3. Selecteer de kolommen waarvan de data overeen moet komen als criteria om Excel de waarde als duplicaat aan te merken. 4. Klik op OK. Excel verwijdert de duplicaten en geeft aan hoeveel dubbele waarden er zijn verwijderd.

1

2

26 Duplicaten verwijderen

3

4

Excel 2

Duplicaten opzoeken met Voorwaardelijke opmaak U kunt met Voorwaardelijke opmaak de cellen markeren die dubbele waarden bevatten. 1. Selecteer de kolommen u wilt doorzoeken op duplicaten. Tip 2. Ga in tabblad Start naar Lintgroep Stijlen en kies Gebruik deze methode in Voorwaardelijke opmaak. combinatie met filteren op 3. Klik in de lijst onder Markeringsregels voor cellen de optie kleur om dubbele waarden te Dubbele waarden aan. vinden in de lijst. Wanneer u 4. Kies in het dialoogvenster Dubbele waarden of u de een rij in de lijst ziet met (op dubbele waarden of juist de unieke waarden wilt enkele kolommen na) dubbele opmaken. waarden, is deze zeer 5. Kies een opmaak uit de lijst. waarschijnlijk een dubbele 6. Klik op OK. De dubbele waarden zijn gemarkeerd. waarde. Verwijder eventueel de rij. 1

2

3

4

5

6

Tip

Excel 2

Duplicaten verwijderen 27

Structuur aanbrengen in werkbladen

11 WERKEN MET CELNAMEN U kunt een reeks cellen voorzien van een omschrijvende naam om deze vervolgens te gebruiken in formules. Een cellenbereik zoals E2:E46 is op zichzelf nietszeggend, tenzij u controleert waarnaar deze cellen verwijzen. Wanneer u dit cellenbereik voorziet van een naam kunt u deze gebruiken in een formule. Dit is een manier om formules ‘leesbaar’ te houden. Formules controleren op fouten kan hierdoor makkelijker.

Gedefinieerde namen aanmaken 1. 2. 3.

Selecteer de cellenreeks die u een naam wilt geven. Klik in het Naamvak en voer een naam in. Druk op Enter om te bevestigen.

2 3

Let Op Wanneer u werkt met gedefinieerde namen is het belangrijk om het bijbehorende cellenbereik te controleren wanneer u gegevens aanpast – dit om eventuele fouten in formules te voorkomen.

1

Tip Wanneer u werkt met Opmaken als tabel, worden automatisch celnamen aangemaakt voor de verschillende kolommen in de tabel en voor de tabel. Wanneer u rijen toevoegt aan de tabel wordt automatisch het bereik van de celnamen aangepast. Hierbij hoeft u niet in een aparte handeling celnamen te creëren. 30 Werken met Celnamen

Excel 2

Celnamen automatisch aanmaken via kolom- en/ of rijhoofden 1. 2. 3. 4.

Selecteer de cellen inclusief de kolom- of rijkoppen. Ga in tabblad Formules naar Gedefinieerde namen en klik op Maken o.b.v. selectie. Geef aan in welke cellen de celnamen staan. Klik op OK. De celnamen zijn aangemaakt.

1

2

3 4

Excel 2

Werken met Celnamen 31

Gedefinieerde namen gebruiken De laatste stap is het daadwerkelijk toepassen van de gedefinieerde naam in een formule. Hiervoor zijn meerdere methoden. Stap 1.

Typ de formule tot op het punt van het functieargument (de gedefinieerde naam) invoegen.

2. Voeg op één van de drie onderstaande manieren de naam in. Met de formule automatisch aanvullen Typ handmatig de formule en kies de naam uit met de cursortoetsen  en . Druk vervolgens op de tab-toets ter bevestiging. Met de opdracht Gebruiken in formule Selecteer een gedefinieerde naam uit een lijst die beschikbaar is via de opdracht Gebruiken in formule in de Lintgroep Gedefinieerde namen in het tabblad Formules.

Met de sneltoets F3 De sneltoets F3 start de opdracht Namen plakken. Een dialoogvenster met beschikbare celnamen verschijnt.

32 Werken met Celnamen

Excel 2

Gedefinieerde namen gebruiken (vervolg)

Stap 3.

4.

Typ het resterende deel van de formule en controleer deze.

Bekijk het eindresultaat en pas eventueel de formule aan.

Tip De laatste twee beschreven methoden zijn ook beschikbaar wanneer u gedefinieerde namen wilt invoegen tijdens het invoeren van een formule met behulp van het dialoogvenster Functieargumenten.

Excel 2

Werken met Celnamen 33

Gedefinieerde namen beheren Voorgaande pagina’s laten zien hoe u namen aanmaakt. Achteraf aanpassen van namen kan nodig zijn wanneer u de indeling van het werkblad wilt veranderen. 1. Ga in tabblad Formules naar Lintgroep Gedefinieerde namen en kies opdracht Namen beheren. 2. Selecteer de Celnaam die u wilt aanpassen en klik op Bewerken. 3. Wijzig eventueel in het invulveld de naam. 4. Voeg eventueel een opmerking toe. 5. Kies eventueel een nieuwe celverwijzing uit. Let hierbij op dat u eerst de bestaande celverwijzing leegmaakt in het invulveld en voer een nieuwe celverwijzing in door deze aan te wijzen in het werkblad. 6. Klik op OK en vervolgens op Sluiten. 1

2

3 4 5 6

34 Werken met Celnamen

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

Werken met Celnamen 35

12 EEN VERVOLGKEUZELIJST MAKEN MET GEGEVENSVALIDATIE In Excel kunt u handige keuzelijsten maken. Een simpele klik op het uitklapmenu geeft een lijst weer waaruit u een item uitkiest. Zo’n keuzelijst zorgt ervoor dat u niet zomaar elke waarde kunt invoeren in een cel, maar slechts de keuze heeft in een vooraf gedefinieerde lijst. Om dit te maken in Excel past u gegevensvalidatie toe op een cel. Deze gegevensvalidatie bestaat uit 2 onderdelen: de lijst van toegestane gegevens en de cellen waar de gegevensvalidatie is toegepast.

De lijst met items maken 1.

Typ de lijst items in één kolom.

De vervolgkeuzelijst toepassen 2.

3.

Selecteer de cellen waar u de vervolgkeuzelijst wilt toepassen. Dit zijn de cellen waar u de gegevensvalidatie toepast. Ga in tabblad Gegevens onder de Lintgroep Hulpmiddelen voor gegevens en klik op opdracht Gegevensvalidatie. Het dialoogvenster Gegevensvalidatie verschijnt.

1

36 Een vervolgkeuzelijst maken met Gegevensvalidatie

2

3

Excel 2

De vervolgkeuzelijst toepassen op cellen (vervolg) 4. 5. 6.

7.

8.

9.

Kies onder Toestaan voor de optie Lijst. Deze optie staat in het tabblad Instellingen. Klik in het veld Bron. Selecteer de reeks cellen die de items bevat voor in de vervolgkeuzelijst. Hierbij kunt u navigeren naar het een ander werkblad. Ga eventueel naar tabblad Invoerbericht om een boodschap op het scherm weer te geven als de cel is geselecteerd. Ga eventueel naar tabblad Foutmelding en stel in welke melding verschijnt na het invoeren van ongeldige gegevens. Klik op OK.

6

5

4

7

8

9

Excel 2

Een vervolgkeuzelijst maken met Gegevensvalidatie 37

13 GEGEVENSINVOER BEPERKEN MET GEGEVENSVALIDATIE Bij het handmatig invoeren van gegevens in een lijst kunt u ervoor kiezen om data-invoer te beperken met Gegevensvalidatie. U hebt hierbij de mogelijkheid om een foutmelding en een invoerbericht in te stellen. 1. Selecteer de cellen waarvoor u de gegevensvalidatie wilt beperken. 2. Ga naar tabblad Gegevens en kies Gegevensvalidatie. 3. Selecteer het gewenste Validatiecriterium, bijv. Datum. 4. Voer de overige gegevens in.

1

2

3 4

38 Gegevensinvoer beperken met Gegevensvalidatie

Excel 2

GEGEVENSINVOER BEPERKEN MET GEGEVENSVALIDATIE (vervolg) 5. 6. 7.

Voeg eventueel een Invoerbericht en een Foutmelding toe. Klik op OK. Als u ongeldige gegevens invoert, wordt bijbehorend foutbericht weergegeven.

Tip Als u de gegevensvalidatie aanpast hoeft u slechts één cel te selecteren waarbij u deze hebt toegepast. Gebruik vervolgens de optie Deze wijzigingen toepassen op alle andere cellen met dezelfde instellingen. Tip Kies voor een aangepaste gegevensvalidatie als u zelf een formule wilt creëren die wordt toegepast om data-invoer te beperken. De formule kan bijvoorbeeld bestaan uit een combinatie van Logische functies.

5

7

6

Excel 2

Gegevensinvoer beperken met Gegevensvalidatie 39

Vergelijkingen, formules en functies

14 VERGELIJKINGEN INVOEREN Vergelijkingen worden gebruikt bij Logische functies. Onderstaand schema geeft de operatoren voor vergelijkingen in Excel. Een aantal van deze functies wordt omschreven: AANTAL.ALS, SOM.ALS, AANTALLEN.ALS en SOMMEN.ALS.

Operatoren voor vergelijkingen Teken(s)

Actie

Deze berekening…

…resulteert in de volgende waarde:

= <> > < >= <=

Is gelijk aan Is ongelijk aan Groter dan Kleiner dan Groter of gelijk aan Kleiner of gelijk aan

1=6 1<>6 6>1 6<1 2>=1 2<=1

ONWAAR WAAR WAAR ONWAAR WAAR ONWAAR Zie ook De waarden WAAR en ONWAAR worden gebruikt door andere functies, bijvoorbeeld bij de functie ALS. Deze functie wordt op blz. 44 beschreven.

42 Vergelijkingen invoeren

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

Vergelijkingen invoeren 43

15 DE FUNCTIE ALS In onderstaande afbeelding staat een overzicht om het vervoer van een bedrijfsuitje in kaart te brengen. In kolom “!!!” staat aangegeven wanneer er plaatsen te kort zijn in de auto. De formule die hierbij wordt gebruikt staat omschreven in de tabel. In het Nederlands staat hier… In de taal die Excel ‘spreekt’ is dit… De algemene syntax is…

Als het aantal plaatsen bezet groter is dan het aantal plaatsen beschikbaar, geef dan de volgende tekst weer: “Plaats tekort!”. Geef anders de volgende tekst weer: “OK”. =ALS([@[Plaatsen bezet]]>[@[Plaatsen beschikbaar]];"Plaats tekort!";"OK") ALS(logische_test, [waarde_als_waar], [waarde_als_onwaar])

Let op

Tip De celverwijzingen (in groen en blauw weergegeven) zijn ingevoerd door deze aan te wijzen (klikken) tijdens het typen van de formule. Deze zijn dus niet met de hand getypt.

De reden dat in dit voorbeeld celverwijzingen in de vorm van gedefinieerde namen zijn gebruikt, is omdat de lijst is opgemaakt als tabel.

Zie ook Lijsten verwerken met Opmaken als tabel op blz. 18.

44 De functie als

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

De functie als 45

16 DE FUNCTIE AANTAL.ALS In onderstaande afbeelding staat hetzelfde overzicht als op blz. 44 om het vervoer van een bedrijfsuitje in kaart te brengen. In kolom “Plaatsen bezet” wordt automatisch opgeteld hoeveel mensen meerijden met de personen met auto. In het Nederlands staat hier… In de taal die Excel ‘spreekt’ is dit… De algemene syntax is…

Zoek in de kolom “Rijdt mee met” naar de naam Alex (de naam in dezelfde rij in kolom “Naam”). Geef als resultaat het aantal keer dat deze naam voorkomt. =AANTAL.ALS([Rijdt mee met];[@Naam]) AANTAL.ALS(bereik; criterium)

46 De functie aantal.als

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

De functie aantal.als 47

17 DE FUNCTIE SOM.ALS Een tabel bestaat vaak uit een kolom met getallen met daarnaast een kolom met verschillende categorieën. De functie SOM.ALS geeft u de mogelijkheid om een sommatie te maken van de getallen uit de tabel die behoren tot een bepaalde categorie ofwel criterium.. In onderstaand voorbeeld staat een overzicht van rollen met bijbehorende uren. De functie SOM.ALS (zwart omkaderd) geeft als resultaat het totaal aantal uren behorend bij de verschillende rollen uit de lijst. In het Nederlands staat hier… In de taal die Excel ‘spreekt’ is dit… De algemene syntax is…

Zoek in de tabel onder kolom “Rol” naar de zoekterm Bar (de naam in dezelfde rij in kolom “Overzicht rollen”). Tel vervolgens de bijbehorende getallen op in kolom “Aantal uren”. =SOM.ALS(Tabel1[Rol];[@[Overzicht rollen]];Tabel1[Aantal uur]) SOM.ALS(bereik, criterium, [optelbereik])

Tip Gebruik de functie SOMMEN.ALS om meerdere criteria toe te passen op de uiteindelijke optelling. De werkwijze van de functie is verder gelijk aan SOM.ALS. 48 De functie som.als

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

De functie som.als 49

18 DE FUNCTIE AANTALLEN.ALS Onderstaande afbeelding staat een namenlijst met verdere informatie verdeeld in kolommen. Daarnaast staat in verschillende cellen een drietal criteria waaraan de personen uit de lijst kunnen voldoen. De functie AANTALLEN.ALS (zwart omkaderd) geeft als resultaat het aantal personen dat aan deze (meerdere!) criteria voldoet. In het Nederlands staat hier… In de taal die Excel ‘spreekt’ is dit… De algemene syntax is…

Geef als resultaat het aantal personen dat voldoet aan de volgende criteria: Man, Ongehuwd, woonachtig in Venlo. =AANTALLEN.ALS(E:E;K2;F:F;K3;G:G;K4) Bijv. kolom E:E heeft betrekking op “Geslacht” en cel K2 op “M” (man). AANTALLEN.ALS(criteriumbereik1, criterium1, [criterium_bereik2, criterium2]…)

Tip De celverwijzingen voor het criteriumbereik bestaat uit de gehele kolom (bijv. E:E). De lijst is daarmee uit te breiden zonder dat u het cellenbereik hoeft aan te passen voor een correcte berekening. 50 De functie aantallen.als

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

De functie aantallen.als 51

19 DE FUNCTIE VERTICAAL.ZOEKEN De functie VERTICAAL.ZOEKEN is praktisch bij het opzoeken van informatie in een tabel. De linker kolom van de opgegeven matrix wordt doorzocht op een opgegeven waarde en vervolgens wordt de bijbehorende rij als resultaat de waarde gegeven uit het opgegeven kolomnummer. Wanneer u zoekt op numerieke waarden (dus geen tekst) kunt u aangeven of de gevonden waarde een benadering mag zijn van de gezochte waarde. Bij het zoeken op numerieke waarden is het belangrijk dat de lijst van waarden van laag naar hoog is gesorteerd, om onverwachte (foutieve) resultaten te voorkomen.

Linker kolom

=VERT.ZOEKEN( zoekwaarde; tabelmatrix; kolomindex-getal; benaderen (j/n) )

wk48

2%

=VERT.ZOEKEN()

17%

8%

Zoekwaarde

23%

21%

tabelmatrix

18%

29%

21%

Kolomindex

22%

56%

33%

Benaderen

70%

23%

73%

37%

Resultaat

wk50

70%

33%

74%

41%

wk51

73%

46%

78%

71%

wk52

89%

65%

95%

91%

1

2

3

4

…n

wk44

28%

0%

6%

wk45 wk46

34%

5%

53%

13%

wk47

66%

wk48

67%

wk49

52 De functie verticaal.zoeken

wk48 4 ONWAAR 56%

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

De functie verticaal.zoeken 53

20 DE FUNCTIES INDEX EN VERGELIJKEN GECOMBINEERD De functies INDEX en VERGELIJKEN horen bij elkaar als het gaat om informatie opzoeken in een lijst of tabel. De opbouw van de twee functies is anders, maar kan voor hetzelfde doel worden gebruikt als VERTICAAL.ZOEKEN. In tegenstelling tot VERTICAAL.ZOEKEN hebt u de vrijheid om ook andere kolommen dan slechts de linker te laten doorzoeken, en kunt u naar de kolom verwijzen op andere manieren dan slechts een kolomnummer. De functie VERGELIJKEN geeft als resultaat de relatieve positie van een zoekwaarde. Deze functie wordt in het voorbeeld tweemaal uitgevoerd: horizontaal en verticaal. De functie INDEX geeft als resultaat het snijpunt van deze twee relatieve posities. Bekijk het voorbeeld voor de verdere uitwerking en toepassing van de functies. =VERGELIJKEN( zoekwaarde; zoeken-matrix; criteriumtype )

=VERGELIJKEN()

=INDEX( matrix; rijgetal; kolomgetal )

=VERGELIJKEN( zoekwaarde; zoeken-matrix; criteriumtype ) Zoekwaarde

Noord Oost Zuid West

Apr

Zoeken-matrix Criteriumtype Resultaat

Exact 4

Jan

34%

5%

17%

8%

1

Feb

53%

13%

23%

21%

2

Mar

66%

18%

29%

21%

3

=VERGELIJKEN()

Apr

67%

22%

56%

33%

4

=VERGELIJKEN( zoekwaarde; zoeken-matrix; criteriumtype )

Mei

70%

23%

73%

37%

5

Zoekwaarde

Jun

70%

33%

74%

41%

6

Zoeken-matrix

Jul

73%

46%

78%

71%

7

Criteriumtype

Aug

89%

65%

95%

91%

8

Resultaat

1

2

3

4

Zuid Exact 3

=INDEX() =INDEX( matrix; rijgetal; kolomgetal ) Matrix Rijgetal Kolomgetal Resultaat

54 De functies index en vergelijken gecombineerd

4 3 56%

Excel 2

Gebruik deze pagina voor aantekeningen.

Excel 2

De functies index en vergelijken gecombineerd 55

Index

Gebruiken ................................32 Maken o.b.v. selectie ............31

A

Naamvak ..................................30

Aangepaste getalnotatie............ 7

Gegevensvalidatie ......................36 Gegevensinvoer beperken ..38

Aantal.als (functie) ..................... 46 Aantallen.als (functie) ............... 50 Als (functie) ..................................44

Vervolgkeuzelijst ...................36 Getalnotatie ...............................6, 7

S Selecteren speciaal....................... 2 Slicers ............................................ 23 Som.als (functie) ........................ 48 Sparklines .................................... 12 Kolom ....................................... 12

C

I

Celeigenschappen .................... 4, 7

Index (functie) .............................54

Subtotalen ................................... 16

L

T

Lege waarden ................................ 2

Tekst naar kolommen .............. 24

D

Logische functies ........................42

Tijd .................................................... 6

Datum ............................................. 6

N

V

Naamvak .......................................30

Vaste breedte ............................. 25

Lijn ............................................ 12 Winst/verlies.......................... 12

Celnamen .....................................30 Celstijlen ......................................... 4 Constanten..................................... 2

Draaitabel .....................................20 Duplicaten Voorwaardelijke opmaak ....27 Duplicaten verwijderen ............ 26

F

Vergelijken (functie) ................. 54

O

Vergelijkingen ............................ 42

Opmaak van cellen....................... 4

Vervolgkeuzelijst ....................... 36

Verticaal.zoeken (functie) ....... 52

Opmaken als tabel .....................18 Filter

Voorwaardelijke opmaak ........... 8 Dubbele waarden ................. 27

Filteren .....................................19

Duplicaten verwijderen ...... 27

Slicers ........................................23

P

G

Percentage ..................................... 6 Gedefinieerde namen ............... 30 Beheren ....................................34

Z Zichtbare cellen............................. 2