Inhoudsopgave
Module 2 Meer doen met functies Hoofdstuk 1 Veel gebruikte functies ..................................................................................... 2-3 Verwijderen overbodige decimalen ........................................................................................... 2-3 Willekeurige waarde .................................................................................................................. 2-6 GEMIDDELDEA, MAXA en MINA ............................................................................................. 2-7 INTERVAL ................................................................................................................................. 2-8 Zoekfuncties ............................................................................................................................ 2-11 Functies uit de Help gebruiken ................................................................................................ 2-13 Datum en tijdberekening ......................................................................................................... 2-15 Datumfuncties ......................................................................................................................... 2-16 Tijdfuncties .............................................................................................................................. 2-18 Financiële functies................................................................................................................... 2-19 Tekstfuncties ........................................................................................................................... 2-22 CEL ......................................................................................................................................... 2-25 Invoegtoepassingen ................................................................................................................ 2-28 EUROCONVERT..................................................................................................................... 2-30 CONVERTEREN ..................................................................................................................... 2-33 Procedures .............................................................................................................................. 2-36 Opgaven .................................................................................................................................. 2-39 Opgave: Functiegebruik........................................................................................................ 2-39 Opgave: Opzoeken ............................................................................................................... 2-39 Hoofdstuk 2 Cellen benoemen ............................................................................................. 2-41 Celnamen gebruiken ............................................................................................................... 2-41 Celnamen definiëren ............................................................................................................... 2-42 Celnamen in celdefinities ........................................................................................................ 2-43 Procedures .............................................................................................................................. 2-46 Opgaven .................................................................................................................................. 2-47 Opgave: Celnamen ............................................................................................................... 2-47 Hoofdstuk 3 Berekening over werkmappen ....................................................................... 2-49 Driedimensionaal werken ........................................................................................................ 2-49 Werkruimte opslaan ................................................................................................................ 2-52 Samenvoegen bestanden ....................................................................................................... 2-52 Procedures .............................................................................................................................. 2-55 Opgaven .................................................................................................................................. 2-56 Opgave: Driedimensionaal werken ....................................................................................... 2-56 Hoofdstuk 4 Werkgroepfuncties .......................................................................................... 2-57 Gedeelde werkmap instellen ................................................................................................... 2-57 Wijzigingen bijhouden ............................................................................................................. 2-58 Beperkingen gedeelde werkmappen ....................................................................................... 2-61 Procedures .............................................................................................................................. 2-62 Opgaven .................................................................................................................................. 2-63 Opgave: Werkmap delen ...................................................................................................... 2-63 Hoofdstuk 5 Lint aanpassen................................................................................................. 2-65 Tab toevoegen aan lint ............................................................................................................ 2-65
V
LearningGuide Solutions b.v.
Inhoudsopgave
Groep toevoegen aan lint ........................................................................................................ 2-67 Opdracht toevoegen aan lint ................................................................................................... 2-69 Item verwijderen van lint.......................................................................................................... 2-71 Procedures .............................................................................................................................. 2-74 Opgaven .................................................................................................................................. 2-75 Opgave: lint aanpassen ........................................................................................................ 2-75 Hoofdstuk 6 Case Meer doen met functies ......................................................................... 2-77 Hoofdstuk 7 Antwoorden...................................................................................................... 2-79 Veel gebruikte functies ............................................................................................................ 2-79 Cellen benoemen .................................................................................................................... 2-81 Berekening over werkmappen................................................................................................. 2-82 Case ........................................................................................................................................ 2-83 Index ....................................................................................................................................... 2-85
Module 3 Geautomatiseerde oplossingsmethoden Hoofdstuk 1 Doelzoeken......................................................................................................... 3-3 Doelzoeken ............................................................................................................................... 3-3 Procedures ................................................................................................................................ 3-6 Opgaven .................................................................................................................................... 3-7 Opgave: Doelzoeken .............................................................................................................. 3-7 Hoofdstuk 2 Oplosser ............................................................................................................. 3-9 Invoegtoepassing Oplosser ....................................................................................................... 3-9 Oplosser .................................................................................................................................. 3-10 Oplossing opslaan ................................................................................................................... 3-14 Oplosser rapport...................................................................................................................... 3-17 Procedures .............................................................................................................................. 3-22 Opgaven .................................................................................................................................. 3-23 Opgave: Oplosser ................................................................................................................. 3-23 Opgave: Oplosser met restricties.......................................................................................... 3-23 Hoofdstuk 3 Wat-als-analyse ............................................................................................... 3-25 Wat-als met 1 variabele........................................................................................................... 3-25 Wat-als met 2 variabelen......................................................................................................... 3-27 Procedures .............................................................................................................................. 3-30 Opgaven .................................................................................................................................. 3-31 Opgave: Wat-als ................................................................................................................... 3-31 Opgave: Wat-als autokosten................................................................................................. 3-31 Hoofdstuk 4 Scenario`s ........................................................................................................ 3-33 Basisscenario met Scenario`s ................................................................................................. 3-33 Basisscenario met Oplosser.................................................................................................... 3-36 Spelen met scenario`s............................................................................................................. 3-38 Scenario`s samenvoegen ....................................................................................................... 3-40 Procedures .............................................................................................................................. 3-43 Opgaven .................................................................................................................................. 3-44 Opgave: Scenario`s .............................................................................................................. 3-44 Opgave: Scenario`s samenvoegen....................................................................................... 3-44
VI
Inhoudsopgave
LearningGuide Solutions b.v.
Hoofdstuk 5 Case Oplossingsmethoden ............................................................................ 3-47 Hoofdstuk 6 Antwoorden...................................................................................................... 3-49 Doelzoeker .............................................................................................................................. 3-49 Oplosser .................................................................................................................................. 3-49 Wat-als-analyse....................................................................................................................... 3-52 Scenario`s ............................................................................................................................... 3-53 Case ........................................................................................................................................ 3-55 Index ....................................................................................................................................... 3-59
Module 4 Werken met databases Hoofdstuk 1 Introductie in databases ................................................................................... 4-3 Database opbouwen ................................................................................................................. 4-3 Sorteren..................................................................................................................................... 4-4 Sorteren met aangepaste lijst.................................................................................................... 4-4 Sorteren op kolom ..................................................................................................................... 4-7 Procedures .............................................................................................................................. 4-10 Opgaven .................................................................................................................................. 4-11 Opgave: Database sorteren.................................................................................................. 4-11 Hoofdstuk 2 Filteren.............................................................................................................. 4-13 Filter......................................................................................................................................... 4-13 Aangepast filter gebruiken....................................................................................................... 4-16 Subtotalen ............................................................................................................................... 4-21 Uitgebreid filter ........................................................................................................................ 4-23 Berekende waarden in criteria................................................................................................. 4-27 Procedures .............................................................................................................................. 4-29 Opgaven .................................................................................................................................. 4-31 Opgave: Filter ....................................................................................................................... 4-31 Opgave: Uitgebreid filter ....................................................................................................... 4-32 Opgave: Berekende waarde in filter...................................................................................... 4-33 Hoofdstuk 3 Databasefuncties ............................................................................................. 4-35 AANTAL.ALS........................................................................................................................... 4-35 SOM.ALS ................................................................................................................................ 4-37 Databasefuncties..................................................................................................................... 4-38 Procedures .............................................................................................................................. 4-41 Opgaven .................................................................................................................................. 4-42 Opgave: Databasefuncties.................................................................................................... 4-42 Hoofdstuk 4 Draaitabellen en -grafieken............................................................................. 4-43 Draaitabelrapport maken ......................................................................................................... 4-43 Draaitabelindeling wijzigen ...................................................................................................... 4-47 Draaitabel filteren .................................................................................................................... 4-48 Slicer........................................................................................................................................ 4-54 Draaitabel in ander werkblad................................................................................................... 4-56 Draaitabelvelden wijzigen........................................................................................................ 4-58 Berekende velden en items in draaitabel ................................................................................ 4-60 DRAAITABEL.OPHALEN ........................................................................................................ 4-62 Draaitabel verversen .............................................................................................................. 4-63
VII
LearningGuide Solutions b.v.
Inhoudsopgave
Draaigrafiekrapport.................................................................................................................. 4-64 Procedures .............................................................................................................................. 4-69 Opgaven .................................................................................................................................. 4-71 Opgave: Draaitabelrapport maken........................................................................................ 4-71 Opgave: Draaigrafiek ............................................................................................................ 4-71 Opgave: Slicer ...................................................................................................................... 4-72 Hoofdstuk 5 Case Werken met database ............................................................................ 4-75 Hoofdstuk 6 Antwoorden...................................................................................................... 4-77 Introductie in databases .......................................................................................................... 4-77 Filteren .................................................................................................................................... 4-78 Databasefuncties..................................................................................................................... 4-79 Draaitabellen en grafieken ...................................................................................................... 4-79 Case ........................................................................................................................................ 4-83 Index ....................................................................................................................................... 4-87
Module 5 Automatiseren met macro`s Hoofdstuk 1 Macro`s ............................................................................................................... 5-3 Macro Opnemen........................................................................................................................ 5-3 Macro uitvoeren......................................................................................................................... 5-5 Beveiligingsniveau macro.......................................................................................................... 5-7 Bestand met macro's opslaan ................................................................................................... 5-8 Macro bekijken .......................................................................................................................... 5-8 Procedures .............................................................................................................................. 5-10 Opgaven .................................................................................................................................. 5-11 Opgave: Macro opnemen ..................................................................................................... 5-11 Hoofdstuk 2 Macro`s wijzigen .............................................................................................. 5-13 Foutmeldingen......................................................................................................................... 5-13 Fouten opsporen ..................................................................................................................... 5-14 Macro bewerken ...................................................................................................................... 5-16 Macronaam wijzigen................................................................................................................ 5-17 Macro verwijderen ................................................................................................................... 5-18 Projectverkenner ..................................................................................................................... 5-19 Procedures .............................................................................................................................. 5-21 Opgaven .................................................................................................................................. 5-22 Opgave: Macro aanpassen................................................................................................... 5-22 Hoofdstuk 3 Absoluut en relatief opnemen ........................................................................ 5-23 Macro relatief opnemen........................................................................................................... 5-23 Macro gemengd opnemen ...................................................................................................... 5-24 Procedures .............................................................................................................................. 5-26 Opgaven .................................................................................................................................. 5-27 Opgave: Absoluut en relatief opnemen................................................................................. 5-27 Hoofdstuk 4 Startmogelijkheden ......................................................................................... 5-29 Macroknop toevoegen aan werkbalk....................................................................................... 5-29 Sneltoets lint optie ................................................................................................................... 5-30 Knopafbeelding ....................................................................................................................... 5-31
VIII
Inhoudsopgave
LearningGuide Solutions b.v.
Macro toewijzen aan object ..................................................................................................... 5-33 Procedures .............................................................................................................................. 5-35 Opgaven .................................................................................................................................. 5-36 Opgave: Macro in werkbalk .................................................................................................. 5-36 Hoofdstuk 5 Personal Macro Workbook ............................................................................. 5-37 Persoonlijke macrowerkmap gebruiken .................................................................................. 5-37 Macro in Perssonlijke macrowerkmap gebruiken .................................................................... 5-39 Persoonlijke macrowerkmap bewerken................................................................................... 5-40 Macro naar Personal Macro Workbook verplaatsen ............................................................... 5-40 Procedures .............................................................................................................................. 5-42 Opgaven .................................................................................................................................. 5-43 Opgave: Macro opnemen in persoonlijke werkmap.............................................................. 5-43 Hoofdstuk 6 Sjablonen.......................................................................................................... 5-45 Opslaan als sjabloon ............................................................................................................... 5-45 Nieuwe werkmap op basis van sjabloon ................................................................................. 5-46 Wijzigen sjabloon .................................................................................................................... 5-48 Procedures .............................................................................................................................. 5-49 Opgaven .................................................................................................................................. 5-50 Opgave: Bestand als sjabloon gebruiken ............................................................................. 5-50 Hoofdstuk 7 Case Automatiseren met macro`s ................................................................. 5-51 Hoofdstuk 8 Antwoorden...................................................................................................... 5-53 Absoluut en relatief opnemen.................................................................................................. 5-53 Personal Macro Workbook ...................................................................................................... 5-53 Sjablonen ................................................................................................................................ 5-54 Case ........................................................................................................................................ 5-54 Index ....................................................................................................................................... 5-57
Module 6 Integratie Hoofdstuk 1 Inleiding integratie............................................................................................. 6-3 Bestand converteren of importeren ........................................................................................... 6-3 Gegevens plakken, insluiten of koppelen .................................................................................. 6-3 Excel en Internet ....................................................................................................................... 6-4 Hoofdstuk 2 Gegevens uitwisselen met Word...................................................................... 6-5 Gegevens uit Word plakken ..................................................................................................... 6-5 Gegevens insluiten .................................................................................................................... 6-6 Gegevens koppelen .................................................................................................................. 6-8 Gekoppelde gegevens bijwerken .............................................................................................. 6-9 Procedures .............................................................................................................................. 6-11 Opgaven .................................................................................................................................. 6-12 Opgave: Plakken vanuit Word .............................................................................................. 6-12 Opgave: Koppeling plakken vanuit Word.............................................................................. 6-12 Hoofdstuk 3 Gegevens uitwisselen met Access ................................................................ 6-13 Verbinden externe database ................................................................................................... 6-13 Meerdere externe tabellen ...................................................................................................... 6-15 Externe gegevens bijwerken ................................................................................................... 6-22
IX
LearningGuide Solutions b.v.
Inhoudsopgave
Externe database bewerken.................................................................................................... 6-22 Procedures .............................................................................................................................. 6-25 Opgaven .................................................................................................................................. 6-27 Opgave: Werken met query-programma .............................................................................. 6-27 Opgave: Verbinden externe database .................................................................................. 6-27 Hoofdstuk 4 Excel en het web.............................................................................................. 6-29 Integratie Office en internet ..................................................................................................... 6-29 Hyperlink maken...................................................................................................................... 6-29 Werkmap opslaan als webpagina ........................................................................................... 6-31 Draaitabel als webpagina ........................................................................................................ 6-33 Webquery ................................................................................................................................ 6-34 Procedures .............................................................................................................................. 6-36 Opgaven .................................................................................................................................. 6-37 Opgave: Hyperlink maken..................................................................................................... 6-37 Opgave: Webquery ............................................................................................................... 6-37 Hoofdstuk 5 Werkmap verzenden........................................................................................ 6-39 Werkmap als bijlage via e-mail................................................................................................ 6-39 Procedures .............................................................................................................................. 6-41 Opgaven .................................................................................................................................. 6-42 Opgave: Gegevens per e-mail .............................................................................................. 6-42 Hoofdstuk 6 Compatibiliteit.................................................................................................. 6-43 Compatibiliteitsmodus ............................................................................................................. 6-43 Converteren bestandsindeling................................................................................................. 6-44 Procedures .............................................................................................................................. 6-49 Hoofdstuk 7 Case Integratie ................................................................................................. 6-51 Hoofdstuk 8 Antwoorden...................................................................................................... 6-53 Gegeven uitwisselen met Word............................................................................................... 6-53 Gegevens uitwisselen met Access .......................................................................................... 6-53 Excel en het web ..................................................................................................................... 6-56 Werkmap verzenden ............................................................................................................... 6-56 Case ........................................................................................................................................ 6-56 Index ....................................................................................................................................... 6-59 Algemene index..............................................................................................................................I
X
