EXCEL VOOR ECONOMISCHE BEROEPEN
5
1
Inleiding ........................................................................................................................................ 13
2
Efficiënter werken met Excel ........................................................................................................ 14 2.1 Overschakelen van Excel 2003 naar een hogere versie .............................................................. 14 2.1.1 Het scherm van Microsoft Excel 2010 ................................................................................ 14 2.1.2 Het lintgebied ...................................................................................................................... 14 2.1.3 Het lintgebied zelf aanpassen ............................................................................................. 15 2.1.3.1 2.1.3.2
Het lint minimaliseren ..................................................................................................................... 15 Het lint zelf aanpassen .................................................................................................................... 16
2.1.4 2.1.5 2.1.6 2.1.7
Het tabblad Bestand ........................................................................................................... 17 Variabele tabbladen ‘Hulpmiddelen voor …’ ...................................................................... 18 De optie Pagina-indeling ..................................................................................................... 18 Vanaf Excel 2007/2010: Nieuwe bestandsindeling ............................................................ 20
2.1.8
Compatibiliteitsmodus ........................................................................................................ 22
2.1.7.1 2.1.7.2 2.1.7.3
Welke indeling? ............................................................................................................................... 20 Waarom deze indeling? .................................................................................................................. 20 Opslaan onder een andere bestandsextensie ................................................................................. 21
2.2 Werkbalk snelle toegang............................................................................................................. 22 2.2.1 Standaardknoppen toevoegen ........................................................................................... 23 2.2.2 Extra knoppen toevoegen ................................................................................................... 23 Plaats van de weergave ...................................................................................................... 24 2.2.3 2.3 Werken met namen (voor cellen en bereiken) ............................................................................ 25 2.3.1 Voorbeeld met namen voor cellen ..................................................................................... 25 2.3.2 Voorbeeld met namen voor bereiken ................................................................................ 25 2.3.3 Namen bepalen ................................................................................................................... 26 2.3.4 Beheren van gebied- of celnamen ...................................................................................... 27 2.4 Snelle toetsenbord- en muiscombinaties .................................................................................... 28 2.4.1 Beknopt overzicht van de meest voorkomende en praktische snelle toetsenbord- en muiscombinaties ................................................................................................................................. 28 2.4.2 Overzicht sneltoetsen met de ALT-toets ............................................................................ 30 2.4.3 Overzicht sneltoetsen met de CTRL-toets .......................................................................... 30 2.4.4 Sneltoetsen met de Functietoetsen.................................................................................... 32 2.5 Lay-out opmaak .......................................................................................................................... 34 2.5.1 Cellen................................................................................................................................... 34 2.5.1.1 2.5.1.2 2.5.1.3 2.5.1.4 2.5.1.5 2.5.1.6 2.5.1.7
2.5.2
2.5.2.1 2.5.2.2 2.5.2.3
Standaard opmaak .......................................................................................................................... 34 Verdere opmaak .............................................................................................................................. 34 Celstijlen........................................................................................................................................... 35 Getalnotaties ................................................................................................................................... 35 Enkele interessante getalnotaties................................................................................................... 39 Meerdere lijnen tekst in één cel: Tekstterugloop ........................................................................... 40 Meerdere lijnen tekst in één cel: ALT + Enter ................................................................................. 40
Kolommen en rijen .............................................................................................................. 40 Aanpassen van de kolombreedte .................................................................................................... 40 Aanpassen van de rijhoogte ............................................................................................................ 42 Automatisch aanpassen van de kolombreedte/rijhoogte .............................................................. 43
6
EXCEL VOOR ECONOMISCHE BEROEPEN 2.5.3
Invoegen illustraties (Afbeeldingen – Illustraties – Vormen – SmartArt) ........................... 43
2.5.4
Opmerkingen gebruiken ..................................................................................................... 49
2.5.5
Vennootschapsstructuur, organigram, proces uittekenen met SmartArt.......................... 51
2.5.3.1 2.5.3.2 2.5.3.3
2.5.4.1 2.5.4.2 2.5.4.3
2.5.5.1 2.5.5.2 2.5.5.3
Invoegen van een afbeelding (bv. logo) .......................................................................................... 44 Invoegen van een tekstvak .............................................................................................................. 46 Invoegen van een vorm ................................................................................................................... 48 Een opmerking toevoegen: ............................................................................................................. 49 Een opmerking bewerken/verwijderen:.......................................................................................... 50 Opmerkingen weergeven/verbergen: ............................................................................................. 50
SmartArt invoegen .......................................................................................................................... 51 Teksten en vormen toevoegen/verwijderen aan SmartArt ............................................................ 52 SmartArt lay-out indeling wijzigen of verwijderen ......................................................................... 53
2.6 ‘Speciaal plakken’-menu ............................................................................................................. 54 2.6.1 Algemene werkwijze ........................................................................................................... 55 2.6.2 Algemeen ............................................................................................................................ 56 2.6.3 Positieve getallen omzetten naar negatieve (en omgekeerd) ........................................... 56 2.6.4 Transponeren ...................................................................................................................... 59 2.7 3
Snel rekenen via de statusbalk .................................................................................................... 60
Gegevens manipulatie .................................................................................................................. 62 3.1 Externe gegevens importeren ..................................................................................................... 62 3.1.1 Externe gegevens ophalen ‘Van Access’ ............................................................................. 62 3.1.2 Externe gegevens ophalen ‘Van tekst’ ................................................................................ 62 3.1.3 Tekst naar kolommen converteren..................................................................................... 64 3.2
Getal opgeslagen als tekst .......................................................................................................... 67
3.3 Zoeken, vervangen ...................................................................................................................... 67 3.3.1 Zoekopties ........................................................................................................................... 68 4
Interessante formules & functies .................................................................................................. 70 4.1 Absolute of relatieve celverwijzingen in formule ........................................................................ 70 4.1.1 Relatieve celverwijzingen.................................................................................................... 70 4.1.2 Absolute celverwijzingen .................................................................................................... 70 4.1.3 Gemengde celverwijzingen ................................................................................................. 70 4.1.4 Voorbeeld betreffende omrekening naar andere valuta ................................................... 71 4.2 Functies en Formules ................................................................................................................... 72 4.2.1 Basis begrippen ................................................................................................................... 72 4.2.2 Hoe een functie invoegen? ................................................................................................. 72 4.2.3 Functie invoegen via de wizard ........................................................................................... 72 4.2.4 Functie invoegen via manueel intypen ............................................................................... 74 4.2.5 Veel voorkomende formules & functies ............................................................................. 75 4.2.5.1 4.2.5.2 4.2.5.3 4.2.5.4 4.2.5.5 4.2.5.6
Wiskundige en statistische functies ................................................................................................ 75 Datumfuncties ................................................................................................................................. 76 Logische functies ............................................................................................................................. 77 Tekstfuncties .................................................................................................................................... 78 Zoekfuncties ..................................................................................................................................... 80 Financiële functies ........................................................................................................................... 81
EXCEL VOOR ECONOMISCHE BEROEPEN
7
4.3 Detailoverzicht: Formules & functies .......................................................................................... 84 4.3.1 Als, En, Of ............................................................................................................................ 84 4.3.1.1 4.3.1.2 4.3.1.3
4.3.2 4.3.3
Resultaat van een berekening combineren met tekst........................................................ 88 Links, Rechts, Deel, Tekst.Samenvoegen ............................................................................ 89
4.3.4
Beginletters, kleine.letters, hoofdletters ............................................................................ 90
4.3.5 4.3.6
Verwijzingen: functie VERT.ZOEKEN()................................................................................. 91 Verwijzingen: functie INDEX() ............................................................................................. 94
4.3.7 4.3.8 4.3.9
Informatieve functies .......................................................................................................... 94 Som.Als() ............................................................................................................................. 95 Afronden ............................................................................................................................. 96
4.3.10 4.3.11
Leeftijd (ouderdom) berekenen met de functie Datumverschil() ...................................... 98 Formules controleren, evalueren en weergeven ............................................................... 99
4.3.3.1 4.3.3.2 4.3.3.3 4.3.3.4
4.3.4.1 4.3.4.2 4.3.4.3 4.3.4.4
4.3.6.1
4.3.9.1 4.3.9.2
4.3.11.1 4.3.11.2 4.3.11.3
5
Als-Functie ....................................................................................................................................... 84 En-Functie ........................................................................................................................................ 86 Of-Functie ........................................................................................................................................ 87
Links-Functie .................................................................................................................................... 89 Deel-Functie of Midden-Functie ...................................................................................................... 89 Rechts-Functie ................................................................................................................................. 89 Tekst.samenvoegen-Functie ........................................................................................................... 89
Beginletters...................................................................................................................................... 90 Kleine.letters .................................................................................................................................... 90 Hoofdletters ..................................................................................................................................... 90 Zinsconstructie ................................................................................................................................. 90
Praktische toepassing...................................................................................................................... 94
Afronden op een aantal decimalen ................................................................................................. 96 Afronden op een bepaalde significatie (veelvoud) ......................................................................... 97
Broncellen / doelcellen aanwijzen .................................................................................................. 99 Formules weergeven ..................................................................................................................... 100 Formules evalueren ....................................................................................................................... 101
Werken met tabellen .................................................................................................................. 102 5.1 Werken met gewone tabellen ................................................................................................... 102 5.1.1 Een gewone tabel aanmaken ............................................................................................ 102 5.1.2 Een gewone tabel een naam geven .................................................................................. 102 5.1.3 Een gewone tabel sorteren ............................................................................................... 103 5.1.4 Een gewone tabel filteren ................................................................................................. 104 5.2 Werken met interactieve tabellen............................................................................................. 105 5.2.1 Voordelen van een interactieve tabel .............................................................................. 105 5.2.2 Een gewone tabel omvormen naar een interactieve tabel .............................................. 105 5.2.3 Interactieve tabel invoegen .............................................................................................. 106 5.2.4 Een interactieve tabel een naam geven ........................................................................... 107 5.2.5 De tabelstijl aanpassen ..................................................................................................... 107 5.2.6 Tabelstijl opties ................................................................................................................. 108 5.2.6.1 5.2.6.2 5.2.6.3 5.2.6.4
Veldnamenrij ................................................................................................................................. 108 Totaalrij.......................................................................................................................................... 108 Gestreepte rijen ............................................................................................................................. 108 Eerste kolom .................................................................................................................................. 108
8
EXCEL VOOR ECONOMISCHE BEROEPEN 5.2.6.5 5.2.6.6
5.2.7 5.2.8
Gebruik van de sorteerfunctie .......................................................................................... 109 Gebruik van de filterfunctie .............................................................................................. 109
5.2.9 5.2.10 5.2.11
Gemakkelijker werken met Formules ............................................................................... 112 Bij scrollen blijven kolomtitels automatisch zichtbaar ..................................................... 113 De interactieve tabel vergroten ........................................................................................ 113
5.2.8.1 5.2.8.2 5.2.8.3
5.2.11.1 5.2.11.2
6
Laatste kolom ................................................................................................................................ 108 Gestreepte kolommen ................................................................................................................... 109
Selecties aan-/afvinken ................................................................................................................. 110 Specifieke filtermogelijkheden ...................................................................................................... 111 Filters wissen ................................................................................................................................. 112
Rijen en kolommen toevoegen...................................................................................................... 113 Het tabel gebied manueel vergroten ............................................................................................ 113
Draaitabellen in een hand-om”draai” ......................................................................................... 114 6.1 Werken met draaitabellen ........................................................................................................ 115 6.1.1 Wat hebben we nodig? ..................................................................................................... 115 6.1.2 Een draaitabel maken ....................................................................................................... 116 6.1.3 Draaitabel ontwerpen ....................................................................................................... 120 6.1.3.1 6.1.3.2 6.1.3.3
6.1.4
6.1.4.1 6.1.4.2 6.1.4.3 6.1.4.4 6.1.4.5 6.1.4.6 6.1.4.7
Subtotalen en Eindtotalen............................................................................................................. 120 Rapportindeling: Weergave .......................................................................................................... 121 Draaitabelstijlen ............................................................................................................................ 123
Draaitabel opties ............................................................................................................... 123 Berekening van een waardeveld aanpassen (Som, Aantal, Gemiddelde…) ................................ 123 Weergave van een waardeveld aanpassen (procentueel versus absoluut) ................................ 125 Filteren en sorteren ....................................................................................................................... 127 Wat als de brongegevens wijzigen ............................................................................................... 127 Vernieuwen .................................................................................................................................... 127 Berekende velden / items .............................................................................................................. 128 Groeperen ...................................................................................................................................... 129
6.2 Werken met draaigrafieken ...................................................................................................... 130 6.2.1 Wat hebben we nodig? ..................................................................................................... 130 6.2.2 Een draaigrafiek maken .................................................................................................... 130 6.2.3 Een draaigrafiek ontwerpen ............................................................................................. 133 6.2.3.1 6.2.3.2 6.2.3.3
Grafiektype aanpassen.................................................................................................................. 133 Rijen/kolommen omdraaien ......................................................................................................... 133 Grafiekstijlen.................................................................................................................................. 133
6.2.4
Een draaigrafiek indelen ................................................................................................... 134
6.2.5
Een draaigrafiek analyseren .............................................................................................. 137
6.2.6
Tips voor (draai)grafieken ................................................................................................. 138
6.2.4.1 6.2.4.2 6.2.4.3 6.2.4.4 6.2.4.5 6.2.5.1 6.2.6.1 6.2.6.2 6.2.6.3
Grafiektitel ..................................................................................................................................... 134 Astitels ........................................................................................................................................... 134 Legenda ......................................................................................................................................... 135 Gegevenslabels .............................................................................................................................. 135 Gegevenstabel ............................................................................................................................... 136 Lijst met draaitabelvelden............................................................................................................. 137
Een tweede as in een grafiek gebruiken ....................................................................................... 138 Verschillende grafiektypes gebruiken in dezelfde grafiek ............................................................ 141 Grafieken variabel maken (door grafiekelementen te koppelen aan de inhoud van een cel) .... 142
EXCEL VOOR ECONOMISCHE BEROEPEN 6.2.6.4
9
Een andere kleur toepassen in de grafiek bij negatieve getallen ................................................ 143
6.3 Hoe onbruikbare gegevens bruikbaar maken? ......................................................................... 145 6.3.1 Voorbeeld van “onbruikbare” gegevens........................................................................... 145 6.3.2 Voorbeeld van “bruikbare” gegevens ............................................................................... 146 6.3.3 Werkwijze.......................................................................................................................... 146 7
Voorwaardelijke opmaak............................................................................................................ 148 7.1
Wat is het .................................................................................................................................. 148
7.2
Voordelen .................................................................................................................................. 148
7.3
Praktische toepassing ............................................................................................................... 149
7.4 Een voorwaardelijke regel maken ............................................................................................. 150 7.4.1 Algemene methodiek ........................................................................................................ 150 7.4.2 Voorbeeld “Opvolging van taken en dossiers” ................................................................ 151 7.4.3 Voorbeeld “Tijdsregistratie” ............................................................................................ 154 7.4.4 Voorbeeld betreffende visueel inzicht in gegevensreeksen............................................. 156 7.5 Regels beheren (toevoegen, aanpassen, verwijderen) ............................................................. 158 7.5.1 Een nieuwe regel maken (manueel) ................................................................................. 158 7.5.2 Regels aanpassen en wissen ............................................................................................. 158 7.5.2.1 7.5.2.2
8
Regels aanpassen .......................................................................................................................... 158 Regels wissen ................................................................................................................................. 159
Optimaal afdrukken .................................................................................................................... 161 8.1 Hoe de print lay-out optimaliseren ........................................................................................... 161 8.1.1 Afdrukvoorbeeld ............................................................................................................... 161 8.1.2 Instellingen afdrukvoorbeeld ............................................................................................ 163 8.1.2.1 8.1.2.2 8.1.2.3 8.1.2.4 8.1.2.5
Actieve bladen, werkmap of selectie afdrukken ........................................................................... 163 Welke pagina’s afdrukken............................................................................................................. 163 Marges ........................................................................................................................................... 164 Centreren op de pagina ................................................................................................................. 165 Schaal aanpassen .......................................................................................................................... 165
8.2 Wat afprinten ............................................................................................................................ 166 8.2.1 Kop- en voettekst .............................................................................................................. 166 8.2.1.1 8.2.1.2
8.2.2 8.2.3 8.2.4 8.2.5
8.2.5.1 8.2.5.2
9
Via de pagina-indeling weergave ................................................................................................. 166 Via de pagina instellingen ............................................................................................................. 167
Herhalende kolom- of rijtitels ........................................................................................... 168 Opmerkingen afdrukken ................................................................................................... 169 Afdrukbereik aanpassen ................................................................................................... 170 Pagina-einden ................................................................................................................... 170 Pagina-eindevoorbeeld weergave ................................................................................................ 170 Pagina-einde handmatig invoegen en verwijderen...................................................................... 171
Doelzoeken ................................................................................................................................. 172 9.1
Wat is het .................................................................................................................................. 172
9.2
Praktische toepassingen............................................................................................................ 172
10 10.1
Opzetten Excel rapporteringen ............................................................................................... 173 Navigatie via Hyperlinks ............................................................................................................ 174
10
EXCEL VOOR ECONOMISCHE BEROEPEN 10.1.1 10.1.2
Vaste hyperlinks ................................................................................................................ 174 Variabele hyperlinks.......................................................................................................... 175
10.2 Gebruikersinterface instellingen ............................................................................................... 177 10.2.1 Werkwijze.......................................................................................................................... 177 10.3 Gegevens invoer en overzicht.................................................................................................... 179 10.3.1 Gegevensvalidatie ............................................................................................................. 179 10.3.1.1 10.3.1.2 10.3.1.3 10.3.1.4 10.3.1.5
Gegevensvalidatie Toevoegen ...................................................................................................... 179 Voorbeeld van validatie op een datum ......................................................................................... 182 Voorbeeld van validatie via een lijst ............................................................................................. 183 Voorbeeld van validatie via een formule (Aangepast) ................................................................. 184 Gegevensvalidatie verwijderen ..................................................................................................... 185
10.3.2
Formulier besturingselementen ....................................................................................... 186
10.3.3
Rijen en kolommen verbergen/weergeven ...................................................................... 191
10.3.2.1 10.3.2.2 10.3.2.3 10.3.2.4 10.3.2.5 10.3.3.1 10.3.3.2
Tabblad Ontwikkelaars ................................................................................................................. 186 Type van besturingselementen ..................................................................................................... 186 Werkwijze ...................................................................................................................................... 187 Voorbeeld betreffende selectievakje ............................................................................................ 187 Voorbeeld betreffende keuzerondje ............................................................................................. 189 Werkwijze ...................................................................................................................................... 191 Hiërarchie bekomen door verdere groeperingen ......................................................................... 192
10.4 Vertalingen ................................................................................................................................ 194 10.4.1 Wat hebben we er voor nodig .......................................................................................... 194 10.5 Camera ...................................................................................................................................... 195 10.5.1 Wat is het .......................................................................................................................... 195 10.5.2 Werkwijze.......................................................................................................................... 195 10.6
Illustraties .................................................................................................................................. 195
10.7 Beveiligen .................................................................................................................................. 196 10.7.1 Cellen beveiligen ............................................................................................................... 196 10.7.2 Werkblad beveiligen ......................................................................................................... 197 10.7.3 Werkblad beveiliging opheffen ......................................................................................... 199 11
Diverse tips & tricks ................................................................................................................ 201
11.1 Vensters ..................................................................................................................................... 201 11.1.1 Nieuw venster van hetzelfde bestand .............................................................................. 201 11.1.2 Het beeld splitsen ............................................................................................................. 202 11.1.3 Titels blokkeren ................................................................................................................. 204 11.1.4 Twee Excel bestanden naast elkaar vergelijken en synchroon schuiven (scrollen) ......... 205 11.2
Spellingcontrole in Excel ............................................................................................................ 206
11.3 Excel gegevens koppelen met Microsoft Word documenten of Microsoft PowerPoint Slides . 206 11.3.1 Koppeling bijwerken ......................................................................................................... 207 11.4
Opslaan als pdf .......................................................................................................................... 207
11.5 Een bestand in Excel insluiten ................................................................................................... 208 11.5.1 Werkwijze.......................................................................................................................... 208 11.6
Formules en opmaak kunnen niet gekopieerd worden tussen twee Excel bestanden ............. 209
11.7
Lijst met Excel functies: Nederlandse benamingen versus Engelstalige ................................... 209
EXCEL VOOR ECONOMISCHE BEROEPEN 12
11
Praktijkgerichte Excel toepassingen ........................................................................................ 230
12.1 Specifieke controleberekeningen via Excel functies .................................................................. 230 12.1.1 Controle ondernemingsnummer ...................................................................................... 230 12.1.1.1 12.1.1.2 12.1.1.3
Wat is het doel............................................................................................................................... 230 Wat hebben we ervoor nodig ....................................................................................................... 230 Voorbeeld ...................................................................................................................................... 231
12.1.2 Ondernemingsnummer omzetten naar OGM nummer (=gestructureerde mededeling m.b.t. betalingen aan de overheid) .................................................................................................. 231 12.1.2.1 12.1.2.2 12.1.2.3
12.1.3
12.1.3.1 12.1.3.2 12.1.3.3
Wat is het doel............................................................................................................................... 231 Wat hebben we ervoor nodig ....................................................................................................... 231 Voorbeeld ...................................................................................................................................... 231
Controle Belgisch rekeningnummer ................................................................................. 232 Wat is het doel............................................................................................................................... 232 Wat hebben we ervoor nodig ....................................................................................................... 232 Voorbeeld: ..................................................................................................................................... 233
12.2 Onderlinge facturaties nakijken ................................................................................................ 234 12.2.1 Wat is het doel .................................................................................................................. 234 12.2.2 Wat hebben we er voor nodig .......................................................................................... 234 12.2.3 Voorbeeld .......................................................................................................................... 235 12.3 Controleren verschillende versies van Proef- en saldilijsten ..................................................... 237 12.3.1 Wat is het doel .................................................................................................................. 237 12.3.2 Wat hebben we er voor nodig .......................................................................................... 237 12.3.3 Voorbeeld .......................................................................................................................... 237 12.4 Dubbele aankoopfacturen opsporen......................................................................................... 240 12.4.1 Wat is het doel .................................................................................................................. 240 12.4.2 Wat hebben we ervoor nodig ........................................................................................... 240 12.4.3 Voorbeeld .......................................................................................................................... 240 12.5 Doelzoeken in de belastingberekening ..................................................................................... 243 12.5.1 Wat is het doel .................................................................................................................. 243 12.5.2 Wat hebben we er voor nodig .......................................................................................... 243 12.5.3 Voorbeeld van een belastingberekening vóór het uitvoeren van doelzoeken ................ 243 12.5.4 Voorbeeld van een belastingberekening na doelzoeken op totaal te betalen belasting . 244 12.5.5 Voorbeeld van een belastingberekening na doelzoeken op belastingvermeerdering..... 244 12.6 Leningstabel .............................................................................................................................. 246 12.6.1 Wat is het doel .................................................................................................................. 246 12.6.2 Wat hebben we er voor nodig .......................................................................................... 246 12.6.3 Voorbeeld van een leningstabel ....................................................................................... 246 12.7 Kwaliteitstoetsing / Kantoorwerking ........................................................................................ 249 12.7.1 Elektronisch Permanent dossier ....................................................................................... 249 12.7.1.1 12.7.1.2 12.7.1.3 12.7.1.4 12.7.1.5
12.7.2
Wat is het doel............................................................................................................................... 249 Antiwitwas formulieren ................................................................................................................. 250 Een nieuw permanent dossier aanmaken .................................................................................... 251 Wat zijn de inhoudelijke functionaliteiten .................................................................................... 251 Bliksemsnel een organigram uittekenen met SmartArt ............................................................... 253
Opvolging status van taken en/of dossiers ....................................................................... 256
12
EXCEL VOOR ECONOMISCHE BEROEPEN 12.7.2.1 12.7.2.2 12.7.2.3
12.7.3
Tijdsregistratie en analyse hieromtrent............................................................................ 258
12.7.4
Factureren met Excel ........................................................................................................ 261
12.7.3.1 12.7.3.2 12.7.3.3
12.7.4.1 12.7.4.2 12.7.4.3
13
Wat is het doel............................................................................................................................... 256 Wat hebben we ervoor nodig ....................................................................................................... 256 Voorbeeld ...................................................................................................................................... 257 Wat is het doel............................................................................................................................... 258 Wat hebben we er voor nodig....................................................................................................... 258 Voorbeeld ...................................................................................................................................... 258 Werkwijze ...................................................................................................................................... 261 Wat hebben we er voor nodig....................................................................................................... 262 Voorbeeld ...................................................................................................................................... 263
Tot slot .................................................................................................................................... 264
Over de auteur ................................................................................................................................... 264
