Cursus Excel gevorderd Inhoudsopgave 1
INLEIDING. ..................................................................................................................... 3
2
KOPPELEN...................................................................................................................... 3 2.1 2.2 2.3 2.4
3
ALS, EN EN OF. .............................................................................................................. 8 3.1 3.2 3.3 3.4 3.5
4
OPDRACHTEN. ........................................................................................................... 22
EXCEL ALS DATABASE............................................................................................. 22 7.1 7.2 7.3 7.4 7.5 7.6 7.7
8
INLEIDING. ................................................................................................................ 18 ZOEKEN EN VERGELIJKEN.......................................................................................... 18 DE INDEXFUNCTIE. .................................................................................................... 20 OPDRACHTEN. ........................................................................................................... 20
FUNCTIES UITGEBREID. .......................................................................................... 22 6.1
7
DATUMS EN TIJDEN INVOEREN. ................................................................................. 13 DATUMFUNCTIES....................................................................................................... 14 TIJDFUNCTIES............................................................................................................ 15 OPDRACHTEN. ........................................................................................................... 16
ZOEKFUNCTIES. ......................................................................................................... 18 5.1 5.2 5.3 5.4
6
DE ALS-FUNCTIE ......................................................................................................... 8 DE ALS-FUNCTIE GENEST. ........................................................................................... 9 DE EN- EN OF-FUNCTIES. ......................................................................................... 10 SOM.ALS EN AANTAL.ALS.......................................................................................... 11 LOGISCHE FUNCTIES OPDRACHTEN............................................................................ 12
DATUMS EN TIJDEN. ................................................................................................. 13 4.1 4.2 4.3 4.4
5
WERKBLADEN KOPPELEN ............................................................................................ 3 WERKBLADEN EN GRAFIEKEN IN EEN TEKST KOPIËREN. .............................................. 5 WERKBLADEN EN GRAFIEKEN AAN EEN TEKST KOPPELEN. .......................................... 5 OPDRACHTEN. ............................................................................................................. 6
EEN DATABASE IN EXCEL.......................................................................................... 22 WERKEN MET HET DATAFORMULIER. ........................................................................ 23 MEERDERE GESELECTEERDE RECORDS TEGELIJKERTIJD TONEN. ............................... 24 INGEWIKKELDER ZOEKCRITERIA GEBRUIKEN. ........................................................... 26 HET WERKEN MET DATABASEFUNCTIES..................................................................... 28 HET SORTEREN VAN RECORDS. .................................................................................. 29 OPDRACHTEN. ........................................................................................................... 31
WAT ALS........................................................................................................................ 32 8.1 8.2 8.3 8.4 8.5
INLEIDING. ................................................................................................................ 33 DATA TABEL MET EEN VARIABELE............................................................................ 33 DATA TABEL MET TWEE VARIABELEN....................................................................... 34 SCENARIO ANALYSE. ................................................................................................. 35 DOELZOEKEN. ........................................................................................................... 38 1/1
8.6 ITERATIE. .................................................................................................................. 39 8.7 EEN WERKBLAD MEER OF MINDER GEDETAILLEERD WEERGEGEVEN. ........................ 41 8.8 DE OPLOSSER. ........................................................................................................... 43 8.8.1 Waarvoor dient de oplosser? ........................................................................... 43 8.8.2 Hoe werkt de oplosser? .................................................................................... 44 8.8.3 Restricties toevoegen........................................................................................ 45 8.8.4 Meerdere oplossingsmodellen maken. ............................................................. 46 8.9 OPDRACHTEN WAT ALS. ........................................................................................... 47 8.10 ANTWOORDEN OP DE OPDRACHTEN........................................................................... 49 9
CELBESCHERMING. .................................................................................................. 49 9.1 9.2
10
HET BESCHERMEN VAN FORMULES IN CELLEN........................................................... 50 OPDRACHTEN. ........................................................................................................... 51 MATRICES. ............................................................................................................... 53
10.1 10.2 10.3
HET WERKEN MET MATRICES..................................................................................... 53 MATRIXFORMULES.................................................................................................... 53 FUNCTIES DIE EEN MATRIX ALS ARGUMENT VEREISEN EN/OF EEN MATRIX ALS UITVOER PRODUCEREN. ......................................................................................................... 55 10.4 MATRIXCONSTANTEN................................................................................................ 55 10.5 OPDRACHTEN. ........................................................................................................... 56 11
CONSOLIDATIE....................................................................................................... 58 11.1 11.2 11.3 11.4 11.5
12
GEGEVENS UIT (EEN) BRONWERKBLAD(EN) OVERNEMEN IN DOELWERKBLADEN...... 58 HET GEBRUIK VAN
<SAMENVOEGEN>......................................................... 59 HET GEBRUIK VAN <SUBTOTALEN>. ........................................................... 61 HET GEBRUIK VAN DRAAITABELLEN. ........................................................................ 65 OPDRACHTEN. ........................................................................................................... 71 VALIDATIE. .............................................................................................................. 73
12.1 12.2 13
INVOER VALIDEREN................................................................................................... 73 OPDRACHTEN. ........................................................................................................... 76 STATISTIEK.............................................................................................................. 76
13.1 FREQUENTIEVERDELINGEN MAKEN. .......................................................................... 76 13.2 GEGEVENSANALYSE .................................................................................................. 78 13.2.1 Beschrijvende statistiek. ................................................................................... 79 13.2.2 Histogram......................................................................................................... 81 13.3 OPDRACHTEN. ........................................................................................................... 83 14
EEN SPREADSHEETMODEL OPZETTEN. ........................................................ 83 14.1 14.2 14.3
BASISOPBOUW VAN EEN MODEL. ............................................................................... 83 EEN CASUS AANPAKKEN............................................................................................ 84 EEN CASUS ALS VOORBEELD. .................................................................................... 84
2/2
1 Inleiding. De cursus Excel 2000 bevat een aantal onderwerpen die (door onervaren gebruikers) het best kunnen worden doorgewerkt volgens de in de inhoudsopgave beschreven volgorde. De cursus is meestal ook geschikt voor Excel97. Elk cursusonderdeel kent dezelfde opbouw: Toelichting op het onderwerp. Opdrachten. Extra opdrachten. Voorbeelden.
Bij het cursusmateriaal horen ook een aantal opdrachten op schijf. Worden deze niet beschikbaar gesteld, dan kunnen ze ook van de website worden gedownload in het bestandsformaat zip of exe. Beide bestanden zijn gecomprimeerd. Het decomprimeren van de zipbestanden kan met het programma Winzip. De exe-bestanden pakken zichzelf uit nadat ze in de Verkenner zijn gedubbelklikt. Het volledig doorwerken van het cursusmateriaal kost de (nog niet zo ervaren) gebruiker ongeveer 80 uur. Daarbij wordt er van uitgegaan dat de gebruiker beschikt over voldoende basisvaardigheden in het omgaan met Windows en een browser (bij voorkeur Microsoft Explorer).
2 Koppelen. Werkbladen koppelen Werkbladen en grafieken in een tekst kopiëren Werkbladen en grafieken aan een tekst koppelen Opdrachten. Extra opdrachten. 2.1
Werkbladen koppelen
Bij het koppelen van werkbladen is er sprake van: Bronwerkblad Werkblad, dat de gegevens levert aan een ander werkblad. Doelwerkblad Werkbladen, waarin gegevens uit andere werkbladen worden overgenomen. Dit werkblad bevat een externe verwijzing. Externe verwijzing Dit is een verwijzing naar een cel of cellenbereik in een ander werkblad (in dezelfde of in een andere werkmap). De formule die de externe verwijzing bevat heet de externe verwijzingsformule. Koppelingen kunnen worden gelegd tussen werkbladen in één map of tussen werkbladen in verschillende werkmappen. 3/3
Er zijn verschillende manieren om een koppeling tussen werkbladen tot stand te brengen. Methode A. • Maak de cel(len) in het bronwerkblad actief. • Geef de opdracht . • Klik met de muisaanwijzer in de cel van het doelwerkblad waar de over te nemen
waarde(en) moet(en) komen te staan. • Geef de opdracht . Wordt een enkele cel op deze wijze gekopieerd dan resulteert dat in een externe verwijzingsformule in de vorm: =Blad!$A$1 Betreft het een koppeling tussen werkbladen uit verschillende werkmappen, dan moet van bron- naar doelblad worden gegaan via de opdracht . Zo'n externe verwijzingsformule heeft de vorm:
Worden meerdere cellen tegelijkertijd geplakt, dan levert dat een externe matrixformule op:
Het werken met matrixformules wordt elders besproken. De opdracht kan dienstig zijn om het proces van koppelen duidelijker te laten zien. Heeft het koppelen tussen werkbladen van eenzelfde werkmap plaats, dan moet eerst een kopie worden gemaakt van de huidige werkmap met de opdracht . Methode B. • Maak in het doelwerkblad de cel actief waarin een waarde moet worden overgeno-
men. • Plaats in een cel van het doelwerkblad de aanzet voor een formule bijvoorbeeld
=GEMIDDELDE( • Ga vervolgens naar het bronwerkblad. • Sleep met de muisaanwijzer over de cellen die in de formule (van het doelwerkblad)
moeten worden overgenomen. • Zet de sluithaak van de formule en druk op Enter.
Methode C. Tenslotte kan een externe verwijzingsformule ook rechtstreeks worden ingetypt. Dat dit een goede kennis van externe verwijzingsformules vereist spreekt voor zich. 4/4
Het verwijderen van een koppeling is simpel. Wis de externe verwijzingsformule. Om de formule te verwijderen, maar de waarde die ze opleverde te behouden kan als volgt worden gewerkt: • Klik de cel aan waarin de externe verwijzingsformule staat. • Geef de opdracht .
Geef de opdracht en kies voor >(Plakken) Waarden>. 2.2
Werkbladen en grafieken in een tekst kopiëren.
Excel gegevens overnemen in een tekst (in de tekstverwerker) of ander document wordt als volgt gedaan: • Zowel de tekstverwerker (Word) als Excel zijn geopend. • Typ in de tekstverwerker de tekst en markeer met de cursor de plaats waar werkblad-
gegevens of grafiek moeten komen te staan. • Markeer in het Excel werkblad met de muis de te kopiëren gegevens. Bij een grafiek
hoeft niets te worden gemarkeerd; steeds wordt de gehele grafiek geselecteerd. • Geef de opdracht . • Ga naar het document in de tekstverwerker. • Geef de opdracht .
2.3
Werkbladen en grafieken aan een tekst koppelen.
In tegenstelling tot kopiëren kan ook een dynamische koppeling gelegd worden tussen Excel gegevens en een extern document. Het wijzigen van gegevens in een Excel werkblad (en dus eventueel beschikbare grafiek) leidt dan automatisch tot het aanpassen van de cijfers in de tekst. Werkwijze: • Zowel de tekstverwerker (Word) als Excel zijn geopend. • Typ in de tekstverwerker de tekst in en klik met de muisaanwijzer op de plaats waar • • • • •
werkbladgegevens of grafiek moeten komen te staan. Markeer in het Excel werkblad met de muis de te kopiëren gegevens. Een grafiek wordt in totaliteit geselecteerd. Geef de opdracht . Ga naar de tekst in de tekstverwerker. Geef de opdracht . Klik de keuzeknop aan en kies bijvoorbeeld <Microsoft Excel-werkbladobject.
5/5
2.4
Opdrachten.
Opdracht 1 Koppelen 1. • Open de werkmap koppel.zip (of koppel.exe) • Neem de waarden uit de werkbladen Aldenghoor, Kerkstraat en Tenueplaats over in
het verzamelblad. Opdracht 2 Koppelen 2. • Ga naar werkblad Weergegevens Beek. • Breng de uit dit blad samen te vatten gegevens over naar het werkblad Verzamelen
weergegevens.Het gaat om temperatuur- en neerslaggegevens. • Wijzig enkele waarden in werkblad Weergegevens Beek en ga na of aanpassing in
werkblad Verzamelen weergegevens plaatsvindt. Opdracht 3 Werkbladen en grafieken koppelen aan tekst. • Ga na of zowel Excel als Word zijn geopend. • Ga naar het werkblad Excel werkblad in tekst (Dit is een werkblad van de werkmap
Koppel). • Typ in Word een passende tekst in. • Plak het cijferoverzicht uit het Excel werkblad in de tekst. Gebruik
en . • Koppel de grafiek (werkblad Excel grafiek in tekst) aan de tekst. Gebruik de opdrach-
ten en . • Wijzig in het Excel werkblad enkele cijfers. Wat is het gevolg voor de werkbladgege-
vens en de grafiek in de tekst? Waarom is dat zo? • Sla de Wordtekst op schijf op en sluit deze tekst. • Pas enkele cijfers in het Excel werkblad aan. • Sla de grafiek en het werkblad op de (schijf) op en sluit beide bestanden.
6/6
• Haal de opgeslagen Wordtekst op. Zijn de wijzigingen in de tekst bijgewerkt?
Conclusie? Extra opdrachten. Opdracht 1. Koppelen en consolideren1. • Het werkblad Totaal opnamen_stortingen (in de werkmap koppel.zip of koppel.exe)
totaliseert de bedragen uit de werkbladen opnamen en stortingen. • Vervolgens moeten in dit werkblad de saldi berekend worden.
Opdracht 2. Koppelen en consolideren2. Ook koppelingen op meerdere niveaus zijn mogelijk. Het onderstaande voorbeeld verduidelijkt wat wordt bedoeld:
Als gegevens uit subdivisies worden doorgegeven naar divisies, die deze op hun beurt weer moeten doorgeven aan de concernleiding, dan dienen mutaties van onder naar boven te worden verwerkt. Dus eerst wijzigen op subdivisieniveau. Hierdoor zal automatisch aanpassing plaatsvinden op divisieniveau en vervolgens wijzigen op divisieniveau. Als gevolg hiervan heeft automatisch aanpassing op concernniveau plaats. • Realiseer de beschreven structuur in Excel werkbladen.
7/7
• Sluit de werkmap Koppelen.
3 Als, en en of. De ALS-functie De Als-functie genest. De EN- en OF-functie De functies som.als en aantal.als Opdrachten. Extra opdrachten. 3.1
De Als-functie
Met de ALS-functie kunnen in een werkblad voorwaarden worden getest. Afhankelijk van het waar of onwaar zijn van de gestelde voorwaarde wordt een reactie gegeven. Werkwijze: • Maak de cel, waarin de ALS-functie moet komen te staan actief. • Klik op de snelknop .
• Kies de categorie Logisch. • Selecteer de functie ALS en klik op .
8/8
• Vul in het kader logische-test de voorwaarden in. In voorwaarden worden
vergelijkingstekens gebruikt. Vul celverwijzingen in door ze aan te klikken. • Vul de waarde-als-waar in. • Vul de waarde-als -onwaar in. • Klik aan. Zie onderstaand voorbeeld.
Merk op dat direct achter de invulkaders de gestelde voorwaarde wordt geëvalueerd en berekeningen worden uitgevoerd. De opbouw van de ALS-functie wordt daarmee: =ALS(voorwaarde stellen;wat doen als voorwaarde klopt;wat doen als voorwaarde niet klopt)
3.2
De Als-functie genest.
De ALS-functie kan een argument van een andere ALS-functie zijn. We spreken dan van nesten. Voorbeeld: ALS(A7>5;A7*B7;ALS(A7=5;A7/B7;A7+B7)) 9/9
Een functie nesten, naar bovenstaand voorbeeld, geschiedt als volgt: • • • •
Klik weer op de snelknop en kies bij voor . Vul de eerste twee argumenten in. Klik in het derde invulvak Waarde-als-onwaar. Klik op de formulebalk op het naar beneden wijzende pijltje teneinde wederom een functie te selecteren:
• Er volgt nu wederom een keuzelijst van functies, als of men voor de eerste keer een
functie kiest. Kies voor de ALS-functie en vul deze op de inmiddels bekende wijze in het dialoogkader in. Merk op dat in de formulebalk is te zien dat de functie binnen de andere functie wordt geplaatst: een geneste functie. • Klik op . Het nesten komt ook van pas als meerdere voorwaarden tegelijkertijd moeten worden getest. Natuurlijk hoeft daarbij niet alleen de als functie te worden genest zoals blijkt uit onderstaand voorbeeld:
3.3
De EN- en OF-functies.
Moeten in een ALS-functie meerdere voorwaarden tegelijkertijd worden getest, waarbij: • aan alle gestelde voorwaarden moet worden voldaan (EN) of • aan een (of meer) van de gestelde voorwaarden moet worden voldaan (OF),
10/10
• dan kunnen de EN- en de OF-functie worden opgenomen in de ALS-functie.
De EN-functie kent minimaal 2 argumenten. Elk argument bevat een voorwaarde. In combinatie met ALS wordt nagegaan of aan alle opgesomde voorwaarden wordt voldaan. Voorbeeld: ALS(EN(A7>20;A7<60);A7*A8;A7/A8) Voor de OF-functie geldt hetzelfde als voor de EN-functie; er zijn minimaal 2 argumenten. Elk argument bevat een voorwaarde. In combinatie met ALS wordt getest of aan één van de opgesomde voorwaarde wordt voldaan. De test kan ook waar opleveren, als aan meer dan een van de opgesomde voorwaarden wordt voldaan. Voorbeeld: ALS(OF(A7>20;A8>30);A7*A8;A7/A8) Zowel OF en/of EN worden met behulp van nesten in de ALS-functie opgenomen. 3.4
Som.als en aantal.als.
Om de som of het aantal van een aantal waarden te bepalen, maar enkel als deze waarden voldoen aan een bepaald criterium, worden de functies SOM.ALS en AANTAL.ALS aangewend. De som.als heeft navolgende opbouw: =SOM.ALS(bereik;criterium;optelbereik) Bereik= het bereik dat moet worden geëvalueerd. Criterium= het criterium dat op het bereik moet worden toegepast, Optelbereik= het bereik van waarden dat moet worden samengevat. Optelbereik behoeft niet te worden opgegeven. Indien dat niet geschiedt gaat Excel er van uit dat het optelbereik gelijk is aan het bereik. Met andere woorden, de gegevens die worden geëvalueerd zijn ook de gegevens die moeten worden samengevat. Voorbeeld: Stel dat A1:A4 respectievelijk de volgende verkoopprijzen bevat voor vier huizen: fl 100.000, fl 200.000, fl 300.000, fl 400.000. B1:B4 bevat het makelaarscourtage voor elk van de vier huizen: fl 7.000, fl 14.000, fl 21.000, fl 28.000. In dat geval geldt het volgende: SOM.ALS(A1:A4;">160000";B1:B4) resulteert in fl 63.000 Aantal.als heeft enkel bereik en criterium als argumenten: =AANTAL.ALS(bereik, criterium) 11/11
Voorbeeld: Stel dat u in A3:A6 respectievelijk "appels", "sinaasappels", "perziken" en "appels" hebt opgegeven. In dat geval geldt het volgende: AANTAL.ALS(A3:A6;"appels") resulteert in 2 Stel dat u in B3:B6 respectievelijk 32, 54, 75 en 86 hebt opgegeven. In dat geval geldt Het volgende: AANTAL.ALS(B3:B6;">55") resulteert in 2 3.5
Logische functies opdrachten.
Opdracht 1. • Open de werkmap Als.zip of Als.exe, werkblad ALS 1. • Als in de cellen A4, A5 en verder codes worden ingevuld die hoger liggen dan 5 moet
in de cel rechts ervan worden gemeld, dat dit niet is toegestaan. Opdracht 2. • Werkblad ALS 2. • Als in de cellen A4, A5 en verder een code wordt ingevuld die hoger ligt dan 5 of la-
ger is dan 1 moet worden gemeld dat dit niet is toegestaan. Opdracht 3. • Werkblad ALS 3. • Alleen de codes 3,5 en 7 zijn toegestaan; andere codes of lege cellen zijn onbestaan-
baar. Opdracht 4. • Werkblad ALS 4. • Bij klanten staan bedragen open. Zodra een uitstaand bedrag hoger is dan 1000 gulden
moet Excel een waarschuwing geven. Is een bedrag hoger dan 5000 gulden, dan wordt gemeld dat directe actie vereist is. • Plaats de juiste reactie in kolom D. • Plaats in de cellen D82 en D83 de juiste formules, de juiste formules om totalen van bedragen hoger dan 1000 en van bedragen hoger dan 5000 te berekenen. • Plaats in de cellen D84 en D85 de juiste formules om aantallen van bedragen hoger dan 1000 en aantallen van bedragen hoger dan 5000 te berekenen. Opdracht 5. • Werkblad ALS 5. • Op een afdeling werkt niemand die jonger is dan 25 jaar en niemand ouder dan
60.Vul zelf een aantal leeftijden in en zorg in de kolom F voor een passende reactie. • Lege cellen (iemand zou dan geen leeftijd hebben) zijn niet toegestaan.
Tip: gebruik de ISLEEG-functie om dat te testen. 12/12
Logische functies extra opdrachten. Opdracht 1. • • • •
Gebruik werkmap Als.zip of Als.exe. Werkblad ALS 6. Bestudeer de formule in kolom H. Omschrijf wat hier wordt gecontroleerd.
Opdracht 2. • Werkblad ALS 7. • In kolom C vertoont het werkblad een aantal reacties op basis van de ingevoerde co-
des 1 en 2. • Deze reacties staan er in tekstvorm. Maak een ALS formule die deze zelfde reacties
oplevert.
4 Datums en tijden. Datums en tijden invoeren. Datumfuncties Tijdfuncties Opdrachten. Extra opdrachten. Datums en tijden worden in een werkblad intern omgezet in seriële getallen. Daardoor wordt het mogelijk berekeningen op en met datums en tijden uit te voeren. 4.1
Datums en tijden invoeren.
Er zijn twee mogelijkheden om datums en/of tijden in te voeren: -a- Typ de datum (tijd) in de vorm 24-03-93 (14:22:55) in. Overigens een invoer als 12/12/99 mag ook. -b- Gebruik een datum-(tijd)functie. Datums en tijden kunnen worden opgemaakt door als volgt te werk te gaan: -a- Markeer de cellen die een opmaak moeten krijgen. -b- Geef de opdracht (tab) . Geef als categorie (<Tijd>) aan. Klik de gewenste opmaak aan en klik op .
13/13
4.2
Datumfuncties.
Een kort overzicht van de voornaamste datumfuncties: NU() Plaatst in een cel een gecombineerd datum- en tijdgetal. Dit getal kan een datum- of een tijdopmaak krijgen, maar ook een datum- en tijdopmaak tegelijkertijd. Voorbeeld:
=NU()
VANDAAG() Plaatst een datumgetal in een cel. Voorbeeld:
=VANDAAG()
DAG(datumgetal) Zet het datumgetal om naar een dag van de maand (1 tot en met 31). Voorbeeld:
=DAG(VANDAAG()) =DAG(celverwijzing)
DATUM(jaar;maand;dag) Geeft het datumgetal van de opgegeven datum. 14/14
Voorbeeld:
=DATUM(89,7,22)
DATUMWAARDE(datum_tekst) Maakt een datumgetal van een datum, die is ingevoerd als tekst. Voorbeeld:
=DATUMWAARDE("12-06-92") of =DATUMWAARDE(celverwijzing)
JAAR(datumgetal) Zet een datumgetal om in een jaartal. Voorbeeld:
=JAAR(VANDAAG()) of =JAAR(celverwijzing)
MAAND(datumgetal) Zet een datumgetal om naar een maand van het jaar (1 t/m 12). Voorbeeld:
=MAAND(VANDAAG()) of =MAAND(celverwijzing)
WEEKDAG(datumgetal) Zet een datumgetal om naar een dag van de week ( 1 t/m 7). Voorbeeld:
=WEEKDAG(VANDAAG())
Een datumgetal is een getal tussen 0 (1 januari 1900) en 65380 (31 december 2078). Gebruik altijd de snelknop om functies in cellen in te voeren. Men krijgt zo tevens een goed overzicht van de beschikbare datum- en tijdfuncties.
4.3
Tijdfuncties.
Een overzicht van de beschikbare tijdfuncties: 15/15
NU() Plaatst de systeemdatum en -tijd in een werkblad in de vorm van een datum- en tijdgetal. Voorbeeld:
=NU()
UUR(tijdgetal) Toont de uren uit een tijdgetal. Voorbeeld:
=UUR(NU()) =UUR(Celverwijzing)
MINUUT(tijdgetal) Toont de minuten uit een tijdgetal. Voorbeeld:
=MINUUT(NU()) =MINUUT(celverwijzing)
SECONDE(tijdgetal) Toont de seconden uit een tijdgetal. Voorbeeld:
=SECONDE(tijdgetal) =SECONDE(celverwijzing)
TIJD(uur;minuut;seconde) Zet uren, minuten en seconden om in een tijdgetal. Voorbeeld:
=TIJD(03;18;22) =TIJD(celverwijzing;celverwijzing;celverwijzing)
TIJDWAARDE(tekst) Zet een als tekst ingevoerde tijd om in een tijdgetal. Voorbeeld:
=TIJDWAARDE("12:15:18") =TIJDWAARDE(celverwijzing)
Een tijdgetal is een getal tussen 0 en 1. Door alle tijden om te zetten in een tijdgetal kan er met tijden gerekend worden. 4.4
Opdrachten.
Opdracht 1. • Ga naar werkblad datum_tijd 1 in werkmap tyddat.zip of tyddat.exe. • Geef de getallen in kolom C de opmaak die staat aangegeven in kolom A.
Opdracht 2. • Werkblad datum_tijd 2.
16/16
• Een boek mag maximaal 14 dagen geleend worden. Draag er zorg voor dat in kolom
D wordt vastgelegd wanneer een boek uiterlijk terug dient te zijn. • In kolom F komt de boete te staan op basis van het aantal dagen overschrijding. Opdracht 3. • • • • •
Ga naar werkblad datum_tijd 3. Vul zelf nog wat namen en geboortedatums aan. Stel (in kolom C) vast wie er deze maand allemaal jarig zijn. Bereken in kolom D de leeftijd in jaren. Bestudeer de formule in cel B8.
Opdracht 4. • • • •
Geef de kolommen A en D in werkblad datum_tijd 4 de juiste datumopmaak. Bereken de termijnoverschrijding (in dagen) of de termijnonderschrijding in dagen. Bereken de totale en de gemiddelde termijnoverschrijding (-onderschrijding). Drie maanden voordat een tijdelijk arbeidscontract verloopt moet een medewerker worden gewaarschuwd. Ontwerp een model waarin in kan.
Opdracht 5. • Werkblad datum_tijd 5. • Een bedrijf geeft haar klanten korting als binnen een bepaalde termijn wordt betaald.
5% korting voor klanten die binnen 15 dagen betalen en 2% korting voor klanten die binnen 32 dagen betalen. • Vul kolom C, D, E en F op de juiste wijze in. Extra opdrachten. Opdracht 1. • Open indien nodig de werkmap tyddat.zip of tyddat.exe. • Wie is er (zie werkblad datum_tijd 6) vandaag jarig? Vul zelf enkele namen en ge-
boortedatums in zodat e.e.a. te controleren valt. • Wat is de bedoeling van de formule in cel B7?
Opdracht 2. • Werkblad datum_tijd 7. • Een gebruiker voert in cel A5 het dagnummer in, in cel B5 het maandnummer en in
cel C5 het jaartal. • In cel C7 dient deze invoer tot een datum te worden samengevoegd. Gebruik de DA-
TUM()-functie. • In cel C12 wordt automatisch het verschil berekend tussen huidige datum en inge-
voerde datum. Opdracht 3. 17/17
• Werkblad datum_tijd 8. • Als een maand op 30 dagen wordt gesteld neem dan in de cellen G10 t/m J 13 de juis-
te formules op die er voor zorgen dat bedragen daar komen te staan, waar ze horen. Opdracht 4. • Ga na hoe in werkblad datum_tijd 9 de melding in cel A1 tot stand wordt gebracht. • Sluit de werkmap tyddat.
5 Zoekfuncties. Inleiding Zoeken en vergelijken De indexfunctie Opdrachten. Extra opdrachten. 5.1
Inleiding.
In een werkblad kunnen tabellen zijn opgenomen, waaruit waarden worden gehaald, die in berekeningen worden gebruikt. Zoekfuncties halen de gezochte waarden uit een tabel op. Voor het opzoeken van waarden uit tabellen zijn een aantal functies beschikbaar. In het kader van deze cursus komen aan de orde VERT.ZOEKEN, HORIZ.ZOEKEN, INDEX en VERGELIJKEN. 5.2
Zoeken en vergelijken.
De functie VERT.ZOEKEN (en ook HORIZ.ZOEKEN) heeft de volgende opbouw: =VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal;benaderen). Zoekwaarde (wat zoeken) wordt meestal opgegeven in de vorm van een celverwijzing. In de aangewezen cel staat de waarde die in een tabel moet worden opgezocht. Tabelmatrix (waar zoeken) is een cellenbereik waarin de op te zoeken waarden staan, evenals de te retourneren waarden. Het is de zoektabel. Kolomindex_getal (uit welke kolom waarde retourneren?). Het bereik waarin waarden moeten worden gezocht en waaruit waarden moeten worden geretourneerd bestaat uit twee of meer kolommen. De eerste kolom bevat de zoekwaarden; de tweede kolom (en eventueel overige kolommen) bevatten retourwaarden. De kolom met de zoekwaarden krijgt nummer 1. De overige kolommen worden oplopend genummerd, dus 2, 3 etcetera. Benaderen is een argument dat kan worden opgegeven. Het is niet verplicht. Vult men voor benaderen niets in, dan is benaderen WAAR. In dat geval dienen de 18/18
(zoek)waarden in de tabelmatrix in oplopende volgorde gesorteerd te staan. Dit kan een groot voordeel hebben. Opgegeven zoekwaarden en de zoekwaarden in de tabelmatrix hoeven niet overeen te komen. Zoekt men bijvoorbeeld 1888 op in een tabel, die de tabelzoekwaarden 1850, 1875, 1900 en 1925 bevat, dan zal in de tabel de naast bijgelegen lagere waarde worden geselecteerd. In dit geval is dat 1875. De bij deze waarde horende retourwaarde zal het resultaat van de VERT.ZOEKEN-functie zijn. Is benaderen ONWAAR dan is het niet nodig de zoekwaarden in de tabelmatrix oplopend te ordenen. Echter opgegeven zoekwaarden en zoekwaarden in de tabelmatrix dienen gelijk te zijn, wil een retourwaarde kunnen worden opgegeven. De VERT.ZOEKEN-functie met ONWAAR ingevuld voor het argument benaderen komt sterk overeen met de functie VERGELIJKEN(zoekwaarde;zoekenmatrix). Er is echter een groot verschil. De VERGELIJKEN-functie geeft de positie van een overeenkomstig element in een matrix op. Enkele voorbeelden van de VERT.ZOEKEN-functie, waarbij het argument benaderen niet wordt gebruikt. Standaard heeft benaderen de waarde WAAR, dus geeft men het argument benaderen niet op, dan moeten de zoekwaarden in de tabelmatrix oplopend zijn geordend.
In cel A10 wordt achtereenvolgens de waarde 65, 120, 17 en 33 ingevuld. Ga nu na of de resultaten van de volgende formules duidelijk zijn, voor de waarden die achtereenvolgens in A10 zijn ingevuld: =VERT.ZOEKEN(A10;$A$1:$E$5;3) --------> 4 =VERT.ZOEKEN(A10;$A$1:$E$5;5) --------> 76 =VERT.ZOEKEN(A10;$A$1:$E$5;2) --------> 4 =VERT.ZOEKEN(A10;$A$1:$E$5;4) --------> 7 Merk op dat de verwijzing naar de zoektabel absoluut is gemaakt. Dit is enkel van belang als de formule met de zoekfunctie naar andere cellen zal worden gekopieerd.
19/19
5.3
De indexfunctie.
De indexfunctie heeft een vergelijkbare opbouw te weten: =INDEX(matrix; rij_getal; kolom_getal) Matrix (waar zoeken) heeft betrekking op de zoektabel, waarin de waarden staan die moeten worden gezocht en de waarden die op basis van die zoekactie moeten worden geretourneerd. Deze waarden hoeven niet oplopend te zijn geordend. Voorwaarde daarbij is wel dat de opgegeven zoekwaarden en de zoekwaarden in de matrix precies overeen moeten komen, wil de indexfunctie een goed resultaat opleveren. De index functie gebruikt in tegenstelling tot VERT.ZOEKEN en HORIZ.ZOEKEN twee zoekingangen. Er wordt zowel in de eerste rij als in de eerste kolom van de zoektabel gezocht. Het rijgetal zegt in welke rij de te retourneren waarde staat en het kolomgetal in welke kolom. In complexere situaties kan het voorkomen dat het rijgetal en kolomgetal niet direct zijn gegeven, maar bijvoorbeeld eerst moeten worden gezocht met behulp van een VERT. of HORIZ.ZOEKEN. Enkele voorbeelden van de indexfunctie:
=INDEX(A1:E5;1;1) ---------> 4 =INDEX(A1:E5;3;4) ---------> 48 =INDEX(A1:E5;4;3) ---------> 76 =INDEX(A1:E5;5;2) ---------> 56 5.4
Opdrachten.
Opdracht 1. • Open de werkmap zoeken.zip (zoeken.exe) en selecteer werkblad Zoeken 1. • Vul in cel B3 een artikelcode in. Deze code moet groter zijn dan 999, maar kleiner
dan 1047. Bouw een controlefunctie in. 20/20
• Plaats in cel B4 een zoekfunctie die de bij die artikelcode horende stukprijs in B4
plaatst. • Vul in cel B5 het aantal in dat van bedoeld artikel wordt verkocht. • Plaats in cel B6 een formule die de totaalprijs invult. Opdracht 2. • • • • •
Blijf in werkblad Zoeken 1 staan. Voeg op de positie van rij 4 een extra rij in. Plaats in cel A4 de tekst Artikelnaam. Plaats in de cellen C14 tot en met C60 een aantal artikelnamen. Plaats in cel B4 een zoekformule die de artikelnaam in deze cel plaatst op basis van de ingevoerde artikelcode in cel B3.
Opdracht 3. • Ga naar werkblad Zoeken 2. • Bereken de kwantumkorting in cel B7 met behulp van een zoekfunctie. • Geef de cellen B3, B4, B5,B6 en B8 een passende invulling.
Opdracht 4. • Ga naar werkblad Zoeken 3 en bestudeer hoe in dit werkblad de indexfunctie wordt
gebruikt om de vrachtkosten te berekenen. Opdracht 5. • In werkblad Zoeken 4 is een tabel opgenomen, waaruit blijkt dat de korting op een ar-
tikel afhankelijk is van het aantal verkochte eenheden, maar ook van het soort artikel (artikelcode). • Plaats in cel B7 een indexformule die deze korting berekent. Bedenk dat het rijnummer en kolomnummer niet rechtstreeks gevonden kunnen worden. Extra opdrachten. Opdracht 1. • Open indien nodig het bestand zoeken.zip (zoeken.exe) • Alleen als in de tabel in werkblad Zoeken 5 de opgegeven naam in cel E3 voorkomt,
mogen de adresgegevens in de cellen E4 tot en met E6 worden geplaatst. Opdracht 2. • Werkblad Zoeken 6. • Plaats de celcursor in B7 en geef de menuopdracht <Extra>
len aanwijzen>. • Bestudeer de formule. • Verwijder de controlepijlen met de menuopdracht <Extra>
trolepijlen verwijderen>. 21/21
• Herhaal de werkwijze voor de cellen B8, B9 en C9.
Opdracht 3. • Bestudeer de formule in cel F5 van werkblad Zoeken 7. • Geef aan hoe ze werkt. • Sluit de werkmap Zoeken.
6 Functies uitgebreid. Het is ondoenlijk om alle functies van Excel in het kader van deze cursus te gaan bespreken. Bijgaande opdracht stelt je in staat door het bekijken van een aantal voorbeelden, informatie over diverse functies te vergaren. 6.1
Opdrachten.
Opdracht 1. • Open de werkmap Functies uitgebreid.zip of functies uitgebreid.exe. • Bestudeer in de diverse werkbladen de gebruikte functies.
Opdracht 2. • Bij het onderwerp over matrices worden nog een aantal functies aan de orde gesteld.
Bestudeer dit onderwerp.
7 Excel als database. Toelichting op het onderwerp. Een database in Excel Werken met het dataformulier Meerdere geselecteerde records tegelijkertijd tonen Ingewikkelder zoekcriteria gebruiken Het werken met databasefuncties Records sorteren Opdrachten. Extra opdrachten. 7.1
Een database in Excel.
Een werkblad in Excel kan een (enkel) databasebestand bevatten. Een database heeft de vorm van een tabel. In de kolommen van deze tabel staan de waarden voor de verschillende velden. Boven in elke kolom van de databasetabel moet de veldnaam staan. De veldnaam fungeert als het ware als kolomtitel. Elke rij in de tabel is een record. Een voorbeeld van een database kan er als volgt uitzien: 22/22
NAAM
ADRES
POSTCODE
WOONPLAATS
Bruynestein
Goudsbloemstr.7
4101 GX
Culemborg
Sterenborg
Drabbelstr.J. 10
5964 AH
Horst
Zee, van der
Kabelweg 53
1014 BA
Amsterdam
Molenkamp
Fazantstraat 3
4461 RD
Goes
Smaalen
Pr.Beatrixstr.22
8501 HV
Joure
Gijswijt
Schoolstraat 13
4033 GK
Lienden
Kranenburg
Ruimzicht 330
1068 DA
Amsterdam
De veldnamen of kolomtitels zijn hierbij vet weergegeven. Is op deze wijze een tabel ingevoerd, dan kunnen bewerkingen op deze database worden toegepast. Enigste voorwaarde is dat de celcursor in het databasebereik wordt geplaatst. 7.2
Werken met het dataformulier.
De opdracht Data Formulier... toont een weergave van elk record uit de database in de vorm van een formulier.
Dit dataformulier bezit de volgende mogelijkheden: Nieuw. Hiermee worden records toegevoegd. Het databasebereik wordt automatisch aangepast. Criteria. Hierbij kunnen zoekcriteria worden opgegeven, om records te zoeken.
23/23
Vorige/Volgende zoeken. Een vorig of volgend record zoeken dat aan opgegeven zoekcriteria voldoet.
Een zoekcriterium wordt ingevuld in het lege kader achter de veldnaam op het dataformulier. Er kunnen meerdere criteria tegelijkertijd worden opgegeven. Behalve tekst, getallen of datums kunnen als bewerkingsteken worden gebruikt: = < > <> <= >= * ?
Gelijk aan. Kleiner dan. Groter dan. Ongelijk aan. Gelijk aan of kleiner dan. Gelijk aan of groter dan. Staat voor meerdere niet nader aangegeven tekens. Staat voor een niet nader aangegeven teken. (* en ? gelden alleen voor tekst).
Het is zinvol de recordaanwijzer met behulp van de schuifbalk op record 1 te plaatsen alvorens een zoekactie te beginnen. Indien meerdere records voldoen aan de opgegeven criteria, dan moet en worden gebruikt, aangezien slechts één record per keer kan worden getoond. Is eenmaal een record gevonden, dan kan het met behulp van worden gewist. Een nadeel van het dataformulier is dat er geen samengestelde criteria op basis van "of" mogelijk zijn en dat geen berekeningen kunnen worden uitgevoerd op gegevens. Bovendien wordt slechts een enkel kaartje tegelijkertijd getoond. 7.3
Meerdere geselecteerde records tegelijkertijd tonen.
is een opdracht die bij elke veldnaam een uitschuiflijst van criteria presenteert: 24/24
Klikt men de uitschuiflijst aan, dan verschijnen behalve alle veldwaarden behorend bij het betreffende veld ook de mogelijkheden en . Helemaal onderaan in de lijst bevinden zich nog en .
Men kan uit de lijst eenvoudig een veldwaarde kiezen (bijvoorbeeld Aalst) en vervolgens toont Excel enkel die records die bedoelde veldwaarde bezitten. Merk op dat de uitschuifpijl een blauwe kleur krijgt als een selectiecriterium is opgegeven. Ook voor andere velden (dus bijvoorbeeld woonplaats en postcode) kan men op deze wijze zoekwaarden opgeven, waardoor het mogelijk is records volgens meerdere zoekcriteria te selecteren. De selectie op basis van een veldwaarde wordt opgeheven door in de uitschuiflijst te selecteren. De mogelijkheid top 10 geeft bijvoorbeeld de eerste 10 records op basis van het veld factuurbedrag (zie eerder gebruikt voorbeeld) op basis van de omvang van het factuurbedrag. De opties en laten alle records zien waarvoor geldt dat het aangegeven veld leeg respectievelijk niet leeg is. Wordt gekozen, dan verschijnt onderstaand dialoogkader:
Het is hiermee mogelijk voor een veldwaarde een samengesteld criterium op te geven. Eerst wordt aan de linkerzijde de voorwaarde geselecteerd, waarna in het kader rechts de bijbehorende waarde wordt ingetypt of gekozen (schuiflijst!). 25/25
De En dan wel Of kan worden ingesteld, waarna de tweede voorwaarde kan worden gepreciseerd.
De jokertekens ? en * zijn vooral bij tekst handig. De vraagteken staat voor een onbekend teken in een tekst; het sterretje staat voor meerdere onbekende tekens in een tekst.
Overigens kan ook volstaan worden met het opgeven van een enkelvoudig zoekcriterium. Men vult dan het tweede criteriumkader gewoonweg niet in. Door op te klikken wordt de zoekhandeling uitgevoerd. De uitschuifpijlen van het filter laat men uit het rekenblad verdwijnen door opnieuw de opdracht te geven. 7.4
Ingewikkelder zoekcriteria gebruiken.
Ingewikkelder criteria eisen het gebruik van een criteriumbereik. Het maken van een criteriumbereik begint met het kopiëren of intypen van de veldnamen uit de database. Voorbeeld:
26/26
NAAM ROEPNAAM ADRES POSTCODE WOONPLAATS Wenst men gebruik te maken van berekende criteria (zie verder) dan dient daarvoor een extra veldnaam te worden toegevoegd. Onder de kolomtitels komen de criteria te staan. Om een indruk te geven volgen een aantal voorbeelden: NAAM ROEPNAAM ADRES POSTCODE WOONPLAATS A* Zoek alle plaatsnamen beginnend met een A. NAAM ROEPNAAM ADRES POSTCODE WOONPLAATS Piet Zoek alle records waarin de naam Piet voorkomt. NAAM ROEPNAAM ADRES POSTCODE WOONPLAATS Piet Amsterdam Dit is een combinatie van twee criteria, waarbij aan beide opgegeven voorwaarden moet worden voldaan. Er is sprake van "en". NAAM ROEPNAAM ADRES POSTCODE Piet
WOONPLAATS Amsterdam
Zoek alle mensen die Piet als roepnaam hebben en/of die woonachtig zijn in Amsterdam. Er is sprake van "of". NAAM ROEPNAAM ADRES POSTCODE WOONPLAATS Piet Jan Amsterdam Zoekt records van mensen die (Piet) heten of die (Jan heten en in Amsterdam) wonen. Het is een combinatie van "en" en "of". NAAM ROEPNAAM ADRES POSTCODE WOONPLAATS Piet Groningen Jan Amsterdam Zoek records van mensen die (Piet heten en in Groningen) wonen en/of van mensen die (Jan heten en woonachtig zijn in Amsterdam). Criteria kunnen ook een berekening bevatten, op basis waarvan gezocht gaat worden. 27/27
NAAM ADRES WOONPLAATS DONATIEBEREKENING >F2+50 Geef alle records waarvoor geldt dat de omvang van de donatie meer dan 50 groter is dan van het record dat in rij 2 (F2) staat. Merk op dat Excel niet de formule in de cel zal weergeven, maar WAAR of ONWAAR. NAAM ADRES POSTCODE WOONPLAATS DONATIE =OF(LINKS(C2;1)="Z";LINKS(C2;1)="A") Selecteer de records waarvoor geldt dat de woonplaats begint met een Z of een A. Nadat het criteriumbereik is gemaakt kan de selectie plaatsvinden. De daarbij te gebruiken menuopdracht is: Het bijbehorende dialoogkader:
Alleen als de bewerking is gekozen kan bij een uitvoerbereik worden opgegeven. Let op dat de uitvoer geen bestaande gegevens kan overschrijven. Wordt geen uitvoerbereik opgegeven ( staat niet aan!) dan toont Excel de database in beperktere omvang. Enkel de records die aan de gestelde criteria voldoen worden getoond. Met de opdracht wordt de oorspronkelijke database in volle omvang getoond. De overige aspecten van het dialoogkader spreken voor zich 7.5
Het werken met databasefuncties.
Databasefuncties zijn, in het lijstje dat wordt getoond als in het dialoogkader van de categorie wordt aangeklikt, te herkennen aan de DB voor de functie. Ze zijn in twee groepen onder te verdelen. Staat er een P achter dan gaat het om een berekening over een populatie, anders is er sprake van een steekproefformule.
28/28
Nadat een databasefunctie gekozen is, toont het scherm de syntax van de functie:
=DBFUNCTIE(database;veld;criteria) staat voor het bereik waarin de gehele database zich bevindt, dit is inclusief de veldnamen. Een bereik kan worden aangesleept. In staat een verwijzing naar het veld, waarop de berekening betrekking dient te hebben. Het is een verwijzing naar de cel waarin een veldnaam staat. heeft betrekking op het criteriumbereik. Ook het criteriumbereik kan men aanslepen. Klik om een bereik te selecteren op:
7.6
Het sorteren van records.
Een bestand kan als volgt worden gesorteerd: 29/29
• Sleep met de muis het te sorteren bereik aan. Let op dat alle functioneel bijeen horen-
de gegevens voorkomen in dit bereik. • Geef de opdracht <Sorteren...> • Kies een kolom of maximaal 3 kolommen als sleutel. Op basis van deze sleutel wordt Oplopend of Aflopend (al naar gelang de gemaakte keuze) gesorteerd. De eerst opgegeven sleutel is de primaire sorteersleutel. Zijn geen veldnamen opgenomen in een werkblad dan toont het dialoogkader zich als volgt:
Zijn wel veldnamen opgenomen (alleen dan is sprake van een database!!!) en staat de cursor voor het geven van de sorteeropdracht op een veldnaam, dan toont het dialoogkader de veldnaam:
Excel heeft geconstateerd dat er sprake is van een database en daarom wordt verondersteld dat de lijst een Rijkop heeft. Dit voorstel kan gehandhaafd blijven, waardoor de veldnamen niet in de sortering worden meegenomen. 30/30
Is een lijst gesorteerd dan kan door het geven van de menuopdracht de oorspronkelijke lijst worden hersteld. Om op meer dan 3 kolommen te sorteren wordt de volgende werkwijze toegepast: • Sorteer eerst op de 3 minst belangrijkste kolommen. • Sorteer nogmaals, maar dan op de belangrijkste kolom(men). • Klik op .
7.7
Opdrachten.
Opdracht 1. • Open het bestand databas1.zip of databas.exe. • Voer in map database 1 de volgende opdrachten uit:
A. • Selecteer uit het ledenbestand alle leden die woonachtig zijn in Amsterdam en die een
donatie betalen groter dan f 50,? • Gebruik een dataformulier. • Waarom staat op het dataformulier geen rechthoek rondom het veld leeftijd? B. • Maak van de Amsterdammers die meer dan 50 gulden geven een overzicht met naam,
woonplaats en donatie. Gebruik de opdracht . C. • Doe hetzelfde als bij B voor de leden die a) of in Haarlem wonen of b) waarvan de
leeftijd jonger is dan 40 jaar en die tevens een donatie geven van meer dan f 50,? D. • Verwijder met behulp van het dataformulier de persoon die woont op postcode 3621
JS te Breukelen. • Pas de donatie van Jansen te Leiden aan van 80 naar 113. Opdracht 2. • Ga naar map datatabas2.zip of databas2.exe. • Selecteer alle klanten die een betalingsachterstand hebben van meer dan drie maan-
den. Maak van deze groep een apart overzicht waarin klantnaam, factuurnummer, ?bedrag, en ?datum staan gesorteerd op factuurdatum. • Pas wat datums in kolom D aan zodat een acceptabeler beeld ontstaat. Opdracht 3.
31/31
• Hoeveel antwoorden op vraag 3 hebben in map databas3.zip (of databas3.exe) een
score gekregen van hoger dan 10? Gebruik de Dbaantal functie. • Wat is het gemiddelde van de antwoorden boven de 15, bij vraag 4 ? Gebruik de Dbgemiddelde functie. Opdracht 4. • Gebruik de werkmap databas4.zip (of databas4.exe). • Vul in cel F43 het aantal dagen in met een maximumtemperatuur van meer dan 5 gra-
den; in cel F44 het aantal dagen met een minimum temperatuur onder 0 graden en in cel F45 komt het aantal dagen te staan met een gemiddelde van 0 graden. • Gebruik de cellen A42:F43 voor het criteriumbereik. Extra opdrachten. Opdracht 1. • Gebruik de werkmap databas5.zip (of databas5.exe). • A.Gebruik Data Filter Automatisch filter om de volgende selecties te maken: o alle klanten waarvan de naam begint met A. o alle records waarvan de factuurdatum ligt voor 3 januari 1988 en waarvan het
factuurbedrag hoger is dan 750. • B. Bepaal de totale betalingsachterstand van alle klanten die een betalingsachterstand
• • • •
hebben van maximaal 1 respectievelijk 2 maanden (In dit laatste geval meer dan 1 maand!). C. Bereken de betalingsachterstand van alle facturen die 3 maanden achterstand hebben en waarvan tevens het factuurbedrag minstens f 5000,? bedraagt. D. Ga na wat de gemiddelde betalingsachterstand is van de facturen met een bedrag hoger dan 5000, ongeacht de factuurdatum. Gebruik bij B en C de benodigde statistische databasefuncties. Sluit de werkmap database.
8 Wat als. Inleiding wat-als analyses Data Tabel met een variabele Data Tabel met twee variabelen Doelzoeken Iteratie. Scenario analyse. Een werkblad meer of minder gedetailleerd weergeven De oplosser. Opdrachten. Antwoorden bij de opdrachten.
32/32
8.1
Inleiding.
Wat-als analyses worden gebruikt om na te gaan wat het resultaat van een berekening zou zijn als een of meerdere waarden, waarmee een formule werkt worden gevarieerd. Hoeveel rente moet op een kapitaal worden betaald bij een variërende rente is zo'n voorbeeld. De mogelijkheden tot wat-als analyses in Excel kunnen ingedeeld worden in 3 groepen: • Eén waarde varieert, waarbij de opdracht wordt gebruikt. • Twee waarden variëren, waarbij de opdracht wordt gebruikt. • Meer dan twee waarden variëren, waarbij de opdracht <Extra><Scenariobeheer...>
wordt gebruikt. 8.2
Data Tabel met een variabele.
De werkwijze is als volgt: • • • •
Stel een invoertabel samen. Selecteer deze invoertabel. Geef de opdracht Geef een verwijzing op naar de rij- dan wel kolominvoercel. De invoertabel is als volgt opgebouwd:
In cel A1 en A2 staan waarden waarmee een formule, welke staat in B3 moet gaan werken. In de cellen A4 tot en met A9 worden verschillende waarden voor Variabele 1 (die staat in cel A1 en waarmee de formule in cel B3 werkt) opgenomen. De zogenaamde invoertabel is in het voorbeeld gestippeld aangegeven.
33/33
In plaats van een formule kan ook met twee of meer formules, waarvoor de variabele wordt ingevuld, gewerkt worden:
Na het opstellen van deze invoertabel wordt deze geselecteerd. In bovenstaand voorbeeld betreft dat de cellen A3 tot en met C9. Is de opdracht gegeven dan dient een kolominvoercel te worden opgegeven, omdat de variabele waarden in een kolom staan (cellen A4, A5, A6 enzovoorts). De ingevulde celverwijzing betreft cel A1 of A2, afhankelijk van welk onderdeel van de formule wordt gevarieerd. In dit voorbeeld gaat het om cel A1.
Bij het laten variëren van één variabele wordt òf de rij-invoercel of de kolom-invoercel opgegeven. 8.3
Data Tabel met twee variabelen.
De werkwijze is als volgt: • • • •
Stel een invoertabel samen. Selecteer deze invoertabel. Geef de opdracht Geef een verwijzing op naar de rij- en de kolominvoercel. 34/34
De invoertabel is anders van opbouw dan bij Data Tabel... een variabele en wel als volgt:
Het selecteren van de invoertabel (B3:D9) wordt gevolgd door de opdracht . Zowel een kolomverwijzing (A1) als een rijverwijzing (A2) worden opgegeven. 8.4
Scenario analyse.
Een scenario is een verzameling invoerwaarden voor een werkblad. Deze invoerverzameling bewerkstelligt in het werkblad een bepaald resultaat. Er kunnen meerdere van dergelijke verzamelingen bij een werkblad zijn, die derhalve telkenmale een verschillend resultaat realiseren. Met een scenario kan in 3 stappen worden gewerkt: 1. Maak een scenario. 2. Geef de resultaten van het scenario weer. 3. Maak een rapportage van een scenario. Ad 1. Het maken van een scenario. Maak allereerst een werkblad met daarin formules op basis van variabelen (celverwijzingen!). Geef vervolgens de opdracht <Extra><Scenariobeheer...>. Omdat aanvankelijk nog geen scenario's zijn gedefinieerd verschijnt het volgende dialoogkader:
35/35
Vervolgens kunnen scenario's worden toegevoegd (Opdrachtknop ). Een volgend dialoogkader verschijnt:
Allereerst krijgt het scenario een naam. Vervolgens worden de veranderende cellen ingevoerd. De veranderende cellen bevatten in het algemeen de variabelen, waarvan essentiële formules in het werkblad afhankelijk zijn. Het zijn de cellen waarvan het scenario de waarden moet aanpassen. Indien er meerdere veranderende cellen zijn, klik dan de bewuste cellen met de muisaanwijzer aan daarbij de -toets indrukkend (zie bovenstaand voorbeeld). Zodra het scenario is beschreven klikt men op . Er verschijnt een waarschuwing, die met verdwijnt: 36/36
Vervolgens dienen voor elke veranderende cel de gewenste waarden te worden ingevuld:
Door voorgaande werkwijze steeds te herhalen kunnen meerdere scenario's worden toegevoegd:
Het is verstandig steeds een scenario toe te voegen dat is gebaseerd op de uitgangswaarden van het werkblad. Zo kan men gemakkelijk de uitgangswaarden herstellen. Ad 2. De resultaten van een scenario weergeven. Om een scenario toe te passen wordt, met het dialoogkader <Scenariobeheer> een bewust scenario aangeklikt in de namenlijst van scenario's, gevolgd door een klik op de opdrachtknop <Weergeven>. 37/37
Ad 3. Een rapportage van de scenario's maken. Men kan ook de diverse scenario's in een overzichtelijk totaalbeeld laten presenteren door het aanklikken van de opdrachtknop <Samenvatting>. In het dialoogkader dat verschijnt, kan dan gekozen worden voor <Scenario-samenvatting> of <scenariodraaitabel>:
Kiezend voor <Scenario-samenvatting> verkrijgt men bijvoorbeeld het volgende resultaat:
8.5
Doelzoeken.
Bij doelzoeken wordt niet gevraagd naar het resultaat van een formule. Dat gewenste resultaat staat vast. Gevraagd wordt welke waarde een variabele in de formule moet aannemen om die gewenste uitkomst te verkrijgen. Voorbeeld: welk punt (te zoeken waarde voor variabele) moet behaald worden voor bedrijfseconomie om gemiddeld voor alle vakken een 7 (gewenste uitkomst) te halen? Nadat in een werkblad een formule is opgesteld kan de opdracht <Extra> worden gegeven. Hierna toont Excel het volgende dialoogkader:
38/38
De diverse invoergedeelten hebben de volgende betekenis: Cel bepalen Op waarde Door wijzigen cel
8.6
Celverwijzing naar de formule. Geef de gewenste uitkomst van de formule. Geef de celverwijzing op waarvan de bijpassende waarde moet worden vastgesteld om de gewenste uitkomst te krijgen.
Iteratie.
Iteratie betekent herhaling. Iteratieve berekeningen zijn dan ook berekeningen die herhaald worden uitgevoerd om een einduitkomst te verkrijgen. De uitkomst van een iteratieve berekening is direct dan wel indirect afhankelijk van diezelfde berekening. Een voorbeeld kan e.e.a. verduidelijken. Werknemers krijgen op het einde van het jaar een bonus uitgereikt die afhankelijk is van de behaalde nettowinst. De nettowinst is afhankelijk van de uitgekeerde bonus. Onderstaand voorbeeld toont hoe zo'n berekening stap voor stap handmatig kan worden uitgevoerd: Brutowinst Bonus 10% Nettowinst
1000,00 0,00 1000,00
1000,00 100,00 900,00
1000,00 90,00 910,00
1000,00 91,00 909,00
1000,00 90,90 909,10
1000,00 1000,00 90,91 90,909 909,09 909,091
Wordt in een spreadsheet bovenstaande berekend met behulp van formule, bijvoorbeeld:
39/39
dan resulteert dan in de melding dat de Kringverwijzing niet kan worden opgelost.
Klikt u nu op , u hebt de iteratie immers bewust ingevoerd, dan resulteert dat in de uitkomst 0. Op de statusbalk staat en in de cellen wordt een blauw teken geplaatst teneinde aan te geven dat er iets aan de hand is:
De berekening kan dan alsnog worden gemaakt met behulp van de opdracht <Extra> (tab) . Hierbij wordt het aankruisvakje Iteratie aangeklikt. Eventueel worden Maximaal aantal (maximaal aantal toegestane herhalingen) en Maximaal verschil (maximaal verschil in waarden tussen twee iteraties) aangepast.
40/40
8.7
Een werkblad meer of minder gedetailleerd weergegeven.
Afhankelijk van de doelgroep waarvoor een werkblad wordt gebruikt is meer of minder behoefte aan het zien van details in werkbladen. Het aanbrengen van overzicht in een werkblad kan dan uitkomst bieden. Er zijn meerdere werkwijzen mogelijk: -A- Excel brengt automatisch het overzicht aan. Geef daartoe de menuopdracht en kies uit het vervolgmenu . Met wordt een overzicht verwijderd uit een werkblad. -B- De gebruiker brengt het overzicht aan. Hiertoe wordt de menuopdracht benut.
41/41
Hierbij kan worden aangegeven of gegroepeerd moet worden op rijen en/of kolommen. -C-. Men kan ook alles geheel handmatig instellen: • De gebruiker geeft allereerst aan welke cellen samengevouwd moeten worden; zie
onderstaand voorbeeld:
• Vervolgens wordt de menuopdracht gegeven,
waarna de juiste instelling op het verschenen dialoogkader wordt ingesteld:
• Het verkregen resultaat kan daarna worden samengevouwd:
42/42
Door op het minnetje of plusje te klikken kan men respectievelijk alle details of een meer samengevatte weergave zichtbaar maken.
• De menuopdracht spreekt verder voor zich.
8.8
De oplosser.
Waarvoor dient de oplosser? Hoe werkt de oplosser? Restricties toevoegen. Meerdere oplossingsmodellen maken.
8.8.1 Waarvoor dient de oplosser? Met de oplosser kan men vragen beantwoorden als "Met welk reclamebudget halen we de hoogste winst?", "Hoe kan ik er voor zorgen dat de kosten voor levensonderhoud binnen mijn budget blijven?","Hoe kan ik een personeelsplanning opzetten tegen minimale kosten?" enzovoorts. Op basis van iteraties (pogingen) stelt de Oplosser een doelresultaat op, berekent de maximale waarde voor een variabele of juist de minimale waarde. De oplosser is niet zonder meer beschikbaar in het menu. Allereerst dient de opdracht <Extra> gegeven te worden. Klik in de keuzelijst aan:
43/43
De invoegtoepassing wordt nu toegevoegd aan het menu <Extra>. Met de menuopdracht <Extra> kan de invoegtoepassing worden gestart.
8.8.2 Hoe werkt de oplosser? Nadat een correct rekenmodel is opgesteld kan de opdracht <Extra> worden gegeven. Dit levert het volgende dialoogkader op:
Cel bepalen. Hierin wordt een celverwijzing geplaatst. Het betreft de zogenaamde doelcel. Voor deze cel kan worden berekend: <Max>, <Min> of <Waarde>. Wordt geen doelcel opgegeven, dan wordt een oplossing gezocht die voldoet aan alle opgegeven restricties. Zie verder. Door Verandering cel.
44/44
Hierin worden verwijzingen opgenomen naar cellen die gelden als de essentiële model parameters. Deze zogenaamde onbekenden of beslissingsvariabelen kunnen gevormd worden door een cel, een bereik van cellen of meerdere afzonderlijke cellen (celbereiken). In dit laatste geval wordt de -toets benut. Schatting. De opdrachtknop <Schatting> leidt er toe dat Excel voorstelt welke cellen te veranderen. Oplossen. De opdrachtknop Oplossen zal Excel brengen tot het zoeken van een oplossing bij de gestelde vragen. Dit resulteert wederom in een dialoogkader:
De betekent dat de nieuwe gevonden waarden in het werkblad blijven op genomen; doet dat niet. Met <Scenario opslaan...> kan men van de gevonden waarden verzameling een scenario maken. Geeft men nogmaals de opdracht <Extra> dan toont Excel de voordien ingestelde parameters. De opdrachtknop alles herstelt de uitgangssituatie.
8.8.3 Restricties toevoegen. Behalve dat doelcel en veranderende cellen kunnen worden opgegeven is het ook mogelijk waarden te binden aan restricties. De opdrachtknop geeft toegang tot het dialoogkader waarmee restricties voor doelcel en/of veranderende cellen kunnen worden ingesteld.
De geeft aan voor welke cel de beperking zal gaan gelden. De relatie kan zijn <=, = , >=, int of bin. Int van integer (geheel getal) kan enkel gelden voor veranderende cellen. Bij bin kan enkel sprake zijn van 0 of 1 (bin=binair=tweetallig stelsel) 45/45
Bij de in het rechter gedeelte komt de beperkende waarde te staan. Met wordt de restrictie opgenomen in het dialoogkader <Parameters Oplosser>. Vervolgens kunnen nieuwe beperkingen (restricties) worden toegevoegd. Men eindigt met een klik op nadat de laatste restrictie is opgesteld. Een voorbeeld van een restrictie in het dialoogkader <Parameters Oplosser>:
8.8.4 Meerdere oplossingsmodellen maken. Bij een werkblad kunnen meerdere probleemmodellen worden gemaakt. in het dialoogkader <Parameters Oplosser> dat verschijnt na de opdracht <Extra> biedt de mogelijkheid tot <Model opslaan...> en <Model laden...>
46/46
Voor de overige aspecten binnen dit dialoogkader wordt aangeraden de opdrachtknop aan te klikken. 8.9
Opdrachten Wat als.
Opdrachten Data Tabel. Opdracht 1. • Open het werkmap watals.zip (of watals.exe). • Ga naar werkblad Data Tabel 1. • Bereken de diverse afbetalingen op een geleend kapitaal bij een variërend renteper-
centage.Gebruik de opdracht met een celverwijzing naar een variabele. • De uitwerking van de opdrachten van deze module staan in een aparte werkmap. Open ook deze werkmap Antwatal.zip (of Antwatal.exe), teneinde de antwoorden te kunnen bekijken. Opdracht 2. • Ga naar werkblad Data Tabel 2. • Bereken de afbetaling op een geleend kapitaal bij een variërende rentepercentage en
bij een verschillend aantal aflossingsperioden. • Gebruik de opdracht met celverwijzingen naar twee variabelen. Opdrachten Doelzoeken. Opdracht 1. • Gebruik werkblad doelzoeken (Open eventueel de werkmap watals.zip of watals.exe). • Bereken met behulp van de opdracht <Extra> welk punt voor Duits
moet worden behaald om een 6,5 als eindresultaat te verkrijgen. Opdrachten iteratie. Opdracht 1. • Open eventueel het werkmap watals.zip (of watals.exe). • Bedenk in het werkblad Iteratieve berekeningen een opzet met formules om de bo-
venstaande handmatige berekening te simuleren. • Voer vervolgens de formules in zoals gebruikt bij de iteratieve berekening en laat een
iteratieve berekening uitvoeren. Wijzig noch het <Maximaal aantal> noch het <Maximaal verschil>. Opdrachten Scenarioanalyse. Opdracht 1. • Open het werkmap watals.zip (of watals.exe).
47/47
• Bij het werkblad Raming bedrijfsresultaten is een aantal scenario's opgenomen. • Bestudeer deze scenario's en laat de werkbladinhoud volgens de diverse onderschei-
den scenario's weergeven. • Bekijk het bij dit werkblad horende overzicht Scenario's bedrijfsresultaten.
Opdracht 2. • • • •
Maak bij werkblad Scenarioanalyse een drietal scenario's. Neem de huidige waarden op in een scenario standaard. Laat de werkbladinhoud volgens de diverse scenario's weergeven. Maak een rapport van de diverse scenario's.
Opdrachten meer of minder gedetailleerd weergeven. Opdracht 1. • Open het werkmap watals.zip (of watals.exe). • Oefen in werkblad Data Overzicht de diverse hiervoor besproken mogelijkheden. • Maak een afdruk van de globale, samenvattende gegevens.
Opdrachten meer of minder gedetailleerd weergeven. Opdracht 1. • Open het werkmap watals.zip (of watals.exe). • Bestudeer het werkblad Oplosser 1. • Stel een drietal oplossingsmodellen op. o Model 1. o Bepaal wat de maximale winst in kwartaal 1 kan zijn door aanpassing van het o o o o
reclamebudget in dat kwartaal. Model 2. Bepaal de maximale totale winst door aanpassing van de reclamebudgetten van kwartaal 1 tot en met 4. Model 3. Bepaal de maximale winst door aanpassing van de reclamebudgetten van kwartaal 1 tot en met 4 waarbij het totale reclamebudget niet groter mag zijn dan 60000 gulden.
Opdracht 2. • In het werkblad Oplosser 2 gaat het om de meest winstgevende pakketsamenstelling. • Bestudeer het rekenmodel dat aan het werkblad ten grondslag ligt. • Probeer nu een oplossingsmodel te maken uitgaande van de volgende probleemspeci-
ficaties: Doelcel Veranderende cellen
D18 D9:F9
Winst maximaliseren. Aantal te vervaardigen eenheden per product.
Restricties
C11:C15
• Aantal gebruikte onderdelen moet kleiner dan of gelijk aan het aantal onderdelen in
voorraad zijn. D9:F9>=0 • Aantal te vervaardigen eenheden moet groter zijn of gelijk aan 0.
Opdracht 3. • In werkblad oplosser 3 gaat het om de vrachtroutes die de minste kosten met zich
meebrengen. • Bestudeer het model. • Stel een probleemoplossing op gebruikmakend van de volgende gegevens:
Doelcel Veranderende cellen
B20 C8:G10
Totale vrachtkosten minimaliseren. Hoeveelheden die vanaf verschillende fabrieken naar verschillende opslagplaatsen worden vervoerd.
Restricties
B8:B10<=B16:B18
• Totaal vervoerde hoeveelheid kleiner of gelijk aan maximale leveranties van de fa-
briek. C12:G12>=C14:G14 • Totale naar opslagplaatsen vervoerde hoeveelheid groter of gelijk aan vraag bij op-
slagplaatsen. C8:G10>=0 • Vervoerde hoeveelheid moet groter of gelijk aan 0 zijn.
8.10 Antwoorden op de opdrachten. De antwoorden op alle opdrachten m.b.t. de Wat als-onderwerpen kunnen worden bekeken in antwoorden wat als.zip of antwoorden wat als.exe.
9 Celbescherming. Het beschermen van formules in cellen. Opdrachten. Extra opdrachten.
49/49
9.1
Het beschermen van formules in cellen.
Formules in cellen kunnen worden beschermd tegen (per ongeluk) overschrijven of wissen. Hierbij komt men de volgende aspecten tegen: • De opdracht (tab) :
• Klik in het nu verschenen dialoogkader (en eventueel )
aan. Geblokkeerd wil zeggen dat celinhouden (objecten en/of scenario's) niet kunnen worden gewist. • Klik op . • Geef de opdracht <Extra>.
50/50
Standaard zijn alle onderdelen van een werkblad geselecteerd ter beveiliging. Zowel inhoud, objecten (tekeningen e.d.) en scenario's (zie <Extra><Scenariobeheer>). • In het nu verschenen dialoogkader kan op worden geklikt. Indien gewenst
geeft men een wachtwoord op. Celbescherming is in dit laatste geval alleen op te heffen indien het juiste wachtwoord wordt opgegeven. • Met <Extra> kan de celbescherming ongedaan worden gemaakt. Om relevante cellen in een werkblad te blokkeren kan nu de volgende werkwijze worden gevolgd: 1. Selecteer het gehele werkblad en geef de opdracht (tab) niet aangekruist. In dit geval zullen eventueel reeds eerder geblokkeerde cellen hun bescherming verliezen. 2. Selecteer de cellen met formules (of algemener de cellen die geblokkeerd gaan worden). 3. Geef de opdracht tab) nu wel aangekruist. 4. Geef de opdracht <Extra>. Tenslotte is nog op te merken dat niet alleen bladen maar ook hele werkmappen zijn te beveiligen. Dot laatste kan ook t.b.v. gedeeld gebruik. Er is sprake van gedeeld gebruik als meerdere gebruikers (zelfs tegelijkertijd) een werkmap kunnen gebruiken die op een voor hen gezamenlijk toegankelijke plaats in een netwerk is opgeslagen. Dit is mede relevant bij het werken op afstand 9.2
Opdrachten.
Opdracht 1. • Ga naar werkblad bescherm 1 in de werkmap bescherm.zip of bescherm.exe. • De formules in de cellen D7 tot en met H16 moeten tegen overschrijven worden be-
schermd. Ga na welke stappen nog moeten worden gezet om dit te bereiken. Opdracht 2. • Ga na wat het effect is van de opdracht (tab)
scherming>. Extra opdrachten. Opdracht 1. • Geef de opdracht teneinde het werkblad op de werkdisket-
te op te slaan onder een nieuwe naam. Klik in het dialoogkader Opslaan als... de opdrachtknop Extra aan. Er opent zich een menu:
51/51
• Kies in dit menu voor waarna verschijnt:
• Ga na o o o o
wat de effecten zijn van: <Wachtwoord voor openen>. <Wachtwoord voor schrijfbevoegdheid>. <Wacht voor openen> en <wachtwoord voor schrijfbevoegdheid>. .
Opdracht 2. • De opdracht <Extra><Werkmap beveiligen> resulteert in onderstaand
dialoogkader:
52/52
• Zoek met behulp van de Excel help uit wat de mogelijkheden zijn die dit dialoogkader
biedt.
10 Matrices. Het werken met matrices. Matrixformules Functies die een matrix als argument vereisen en/of een matrix als resultaat produceren Matrixconstanten Opdrachten. 10.1 Het werken met matrices. In verschillende situaties kan het gewenst zijn te werken met matrices. Voorbeelden daarvan zijn: A.Dezelfde formule wordt in aangrenzende cellen ingevoerd, waarbij alleen de celverwijzingen worden aangepast. B.Een aantal werkbladfuncties heeft een matrixinvoer als argument nodig om resultaat op te leveren. Sommige functies geven hun uitvoer ook in de vorm van een matrix. 10.2 Matrixformules. Een enkelvoudige formule zoals =A1+B1 produceert één resultaat. Een matrixformule kan meerdere resultaten produceren, afhankelijk van het ingevulde argument. Zo levert de matrixformule {=A1:A3+B1:B3} drie berekeningen op. Namelijk: =A1+B1 =A2+B2 =A3+B3 In dit voorbeeld heeft de matrixformule een invoergebied dat bestaat uit 2 keer 3 cellen. Een matrixformule is altijd te herkennen aan het feit dat deze tussen accolades {} is geplaatst. Een voorbeeld dat verduidelijkt hoe een matrixformule wordt ingevoerd:
Werkwijze: 1. Sleep het invoerbereik aan (C2:C5). 2. Geef de formule =A2:A5*B2:B5 in. 53/53
3. 4. 5. 6.
Druk op Ctrl + Shift plus Enter. Alle cellen krijgen dezelfde formule. Deze berekent A2*B2 in rij 2; A3+B3 in rij 3 enzovoorts. De formule luidt ={A2:A5*B2:B5}
Een matrixformule die een aantal enkelvoudige formules vervangt kan ook een enkel resultaat opleveren. Wederom een voorbeeld ter verduidelijking:
Het totaal wordt in bovenstaand voorbeeld te berekenen door eerst de prijs te vermenigvuldigen met het aantal en dat voor de kolommen B t/m E. Vervolgens worden de resultaten van B4, C4, D4 en E4 getotaliseerd (zie formule in cel B6). Door een matrix te gebruiken kan de tussenstap (prijs*aantal) worden weggelaten. Zie onderstaand voorbeeld:
De werkwijze (naar aanleiding van bovenstaand voorbeeld) daarbij is de volgende: 1. 2. 3. 4. 5.
Type = Gebruik de snelknop . Kies de gewenste functie en sleep het eerste bereik aan. Plaats het bewerkingsteken (*). Sleep het tweede bereik aan. In het dialoogkader wordt ingevuld:
54/54
6. Druk Ctrl+Shift+Enter in. 10.3 Functies die een matrix als argument vereisen en/of een matrix als uitvoer produceren. De functie TREND kan worden benut om uit een reeks gegevens de trendlijn te berekenen. Deze trendlijn is een rechte lijn. Sommige functies vereisen niet alleen een bereik als invoer, maar hebben ook een bereik nodig als uitvoergebied. Een voorbeeld is de functie LIJNSCH. Deze berekent onder meer de correlatiecoëfficiënt. De formule wordt als matrixformule (volgens de bekende werkwijze voor matrixinvoer) in het uitvoerbereik geplaatst. 10.4 Matrixconstanten. Behalve celverwijzingen kunnen ook constanten worden opgenomen in matrixformules. In onderstaand voorbeeld wordt voor product a altijd 5% korting gegeven, voor B 7%, voor C 7,5% en voor D 12%.
De formule hierbij luidt: {=B2:E2*B3:E3-B2:E2*B3:E3*{0,05;0,07;0,075;0,12}} Deze formule is ingevoerd in de cellen B4 t/m E4 door voorafgaand aan het opzetten van de formule eerst deze genoemde cellen te selecteren. Het eerste gedeelte van de formule berekent de totaalprijs; het tweede gedeelte de totaal korting. In het laatste deel van de formule staan de matrixconstanten. De puntkomma's (;) vormen de scheiding tussen de kolommen. Immers de constanten behoren toe aan één rij in 4 kolommen. 55/55
Bij het gebruik van constanten geldt: De puntkomma (;) vormt de scheiding tussen waarden in dezelfde rij (kolommen). De backslash (\) vormt de scheiding tussen rijen. Dus {1;2;3\4;4;6} betekent twee rijen en drie kolommen. 10.5 Opdrachten. Opdracht 1. •
Maak de opdracht in werkblad matrix 1 uit de werkmap matrix. (Download matrix.zip of matrix.exe)
Opdracht 2. • • •
Ga naar werkblad matrix 2. Bereken de wortel uit de daar gegeven getallen, gebruikmakend van de Wortelfunctie en een matrixformule benuttend. Begin met eerste het gehele bereik, waarin de uitkomsten moeten komen te staan, aan te slepen alvorens de snelknop
Opdracht 3. • •
•
Ga naar werkblad matrix 3. In dit werkblad wordt op basis van het aantal aandelen en de prijs per aandeel een totale waarde per aandeel berekend. Deze totale waarden per aandeel worden vervolgens weer tot een groottotaal samengevoegd. Gebruik een matrixformule om het groottotaal te berekenen zonder dat de waarden per aandeel worden berekend.
Opdracht 4. • • • •
Ga naar werkblad matrix 4. Bereken op basis van de daar gegeven cijfers de trendlijn. De functie TREND is te vinden in de categorie statistiek. Maak een lijngrafiek waarin de oorspronkelijke gegevens en de berekende trendlijngegevens worden weergegeven.
Opdracht 5. • • •
Bestudeer het werkblad matrix 5. Ga na op welke invoer de functie is gebaseerd en bekijk met behulp van het hulpfunctie wat de overige uitvoer voor betekenis heeft. Bedenk zelf een toepassingsvoorbeeld met de LIJNSCH-functie.
Opdracht 6. •
Bestudeer in werkblad matrix 6 de formules in cel C6 tot en met C9. 56/56
•
Vul in cel B13 tot en met B16 een matrixformule in, die de prijzen per meter (4 3,75 - 3,25 en 2,50) als matrixconstanten benut.
Opdracht 7. •
• • • • •
•
Met de functie trend kan een lineaire trend berekend worden op basis van bekende gegevens. De syntax van deze functie luidt: TREND(y-bekend;x-bekend;xnieuw;const) Door het derde argument (x-nieuw) te gebruiken is op basis van de bekende waarden extrapolatie mogelijk. Bestudeer werkblad Matrix 7 om dit na te gaan. Geef de werkelijke gegevens plus de berekende en geëxtrapoleerde trend weer in een ingesloten lijngrafiek. Bedenk eventueel zelf een voorbeeld om een (geëxtrapoleerde )trend weer te geven. Trends kunnen in grafieken door Excel worden getekend. Zie daarvoor het onderwerp grafieken. Trends kunnen door Excel ook worden gegenereerd door een aanzet tot een reeks automatisch door te voeren, daarbij slepend met de rechter muisknop in gedrukt. Wordt met de rechter muisknop gesleept, dan toont Excel het volgende contextgevoelige men:
Ga de diverse mogelijkheden (lineaire trend, groeitrend en reeks...) na.
Opdracht 8. • •
Bestudeer in het werkblad antwoord de diverse uitwerkingen van de opdrachten. Aan het einde van het werkblad staat een voorbeeld met de functie GROEI. 57/57
•
Ga na hoe deze functie werkt.
(Let op! Dit is niet hetzelfde als de Trendfunctie).
11 Consolidatie. Gegevens uit (een) bronwerkblad(en) overnemen in doelwerkbladen. Het gebruik van <Samenvoegen>. Het gebruik van <Subtotalen>. Het gebruik van draaitabellen. Opdrachten. Extra opdrachten. 11.1 Gegevens uit (een) bronwerkblad(en) overnemen in doelwerkbladen. Een aantal methoden tot consolidatie heeft betrekking op het overnemen van gegevens uit bronwerkbladen in een doelwerkblad. Het betreft de volgende werkwijzen: a). Het overnemen van samengevatte gegevens in een verzamel werkblad zonder koppeling. Werkwijze: • •
() in het bronwerkblad. in het doelwerkblad.
b). Het overnemen van gegevens in een verzamel werkblad waarbij binnenkomende gegevens in het doelwerkblad een bewerking ondergaan. Geen koppeling. Werkwijze: • •
() in het bronwerkblad. in het doelwerkblad.
58/58
De bewerkingen die met de binnenkomende gegevens en de reeds in het verzamelblad staande gegevens mogelijk zijn, worden in bovenstaand dialoogkader getoond. c).Het overnemen van gegevens in een verzamelwerkblad met koppeling. Werkwijze: • •
() in het bronwerkblad. >.
d). Het gebruik van 3D-formules. Zie module Formules. 11.2 Het gebruik van <Samenvoegen>. Met <Samenvoegen...> kunnen gegevens uit meerdere brongebieden (bronwerkbladen) worden samengevoegd in een doelgebied (doelwerkblad). Bij de waarden horende teksten (labels) kunnen mee worden overgenomen. Voorbeeld: Bronwerkblad 1
Bronwerkblad 2
Doelwerkblad
Jan
12
Jan
12
Jan
24
Feb
15
Mrt
17
Feb
15
Mrt
17
Werkwijze: • •
Plaats de cursor in de cel van het doelwerkblad, waar de binnenkomende gegevens moeten komen te staan. Geef de opdracht <Samenvoegen...> Het volgende dialoogkader verschijnt:
59/59
•
Kies de bewerking die met de gegevens moet worden uitgevoerd:
•
Klik bij het invulvak op de snelknop celverwijzing:
• •
Sleep de eerste celverwijzing aan en klik de opdrachtknop aan. Herhaal de twee voorgaande stappen zo vaak als er verwijzingen uit brongebieden moeten worden overgenomen. Zie onderstaand voorbeeld:
•
Geef aan of labels (tekstkoppen) moeten worden gebruikt uit de dan wel uit de . Klik indien gewenst aan. Telkens als de brongegevens wijzigen wordt ook het geconsolideerde overzicht aangepast.
•
60/60
•
Klik op .
Van de overige mogelijkheden op het dialoogkader <Samenvoegen> valt nog op. Dit wordt gebruikt om bestanden van schijf te halen waarin zich brongegevens bevinden. De opdrachtknop stelt in staat een aangegeven bereik voor inkomende gegevens te verwijderen. 11.3 Het gebruik van <Subtotalen>. Is in een werkblad een databasetabel (zie eventueel Excel als database) opgenomen dan is het mogelijk de waarden in zo'n tabel op velerlei wijzen samen te vatten met behulp van de opdracht <Subtotalen...> Merk op dat de gegevens dus inderdaad als een databasetabel moeten zijn opgenomen.
Werkwijze: • • •
Sorteer de tabel op het veld dat men wil samenvatten. In bovenstaand voorbeeld is dat de afdeling. Draag er zorg voor dat de cursor in een van de cellen van de database is geplaatst. Geef de opdracht
61/61
• •
• •
Kies de categorie, waarop moet worden samengevat door de uitschuiflijst open te klikken. Geef (onder ) op welke bewerking moet worden uitgevoerd op de waarden. Er is een keuze uit:
Klik de velden (bij <Subtotaal toevoegen aan>) aan waarop de bewerking moet worden uitgevoerd. Klik op .
In het gegeven voorbeeld kan men bijvoor beeld per afdeling (In bij iedere wijziging in is gekozen voor afdeling), de verkopen laten sommeren van kopieerapparaten, laserprinters en matrixprinter (deze zijn bij <Subtotaal toevoegen aan> aangevinkt). Onderstaand voorbeeld toont de gekozen instellingen. Het resultaat staat daar weer onder:
62/62
Het resultaat is:
Merk op dat de tabel kan worden samengevouwd met behulp van de linkerzijde van de tabel. In onderstaand voorbeelden is dat gedaan:
63/63
en:
Wenst men een tweede bewerking laten uitvoeren dan wordt de opdracht <Subtotalen...> herhaald. Om de reeds eerder in het werkblad geplaatste waarden te laten staan, moet in het dialoogkader <Subtotalen> de keuze uit gezet worden.
In het beschreven voorbeeld verkrijgt men op deze wijze de subtotalen per afdeling en daarbinnen per periode. De opdracht <Subtotalen...> biedt de mogelijkheid in een keer alle toegevoegde consolidaties te verwijderen.
64/64
11.4 Het gebruik van draaitabellen. Bevat een Excel werkblad een databasetabel (zie eventueel Excel werken met een database) met (getal)waarden dan bieden draaitabellen goede mogelijkheden voor weergave van gegevens naar verschillende gezichtspunten. Onderstaande tabel dient als voorbeeld bij de toelichting:
Werkwijze: • •
Plaats de cursor in de databasetabel. Geef de opdracht Stap 1 van 1 van de Wizard Draaitabellen verschijnt.
•
Klik op , tenzij de brongegevens niet komen uit het werkblad waarin de draaitabel gemaakt gaat worden. In dit voorbeeld wordt er van uit gegaan dat allereerst wordt gekozen voor een draaitabel (draaigrafiek volgt later). 65/65
•
In de tweede stap wordt het bereik van de datatabel aangegeven. Klik op als dat is gebeurd.
•
In de derde stap kan direct op worden geklikt. Men laat dan de indeling van de draaitabel min of meer aan Excel over:
•
Om zelf invloed uit te oefenen op de wijze waarop de tabel wordt samengesteld, kan op het laatste dialoogkader voor worden gekozen. Geef aan welke ingangen de matrix krijgt; welke categorie wordt in de rijen weergegeven (in het voorbeeld is dat het jaar) en welke categorie in kolommen (in het voorbeeld is dat regio). Daarvoor kunnen de veldnamen gewoon worden gesleept naar de juiste plaatst. Geef aan welke waarden moeten worden weergegeven in de afzonderlijke cellen van de matrix. Hier is dat Verkopen; Excel kiest dan voor de standaardbewerking Som van Verkopen.
•
•
66/66
•
Dubbelklikt men op Som van Verkopen dan kan eventueel ook voor een andere bewerking worden gekozen.
•
Eventueel wordt ook aan Pagina een categorie toegekend. Hier is dat vertegenwoordiger. Door aan Pagina een categorie toe te kennen wordt een filter (vergelijk Data Filter) ingebouwd. In het onderhavige voorbeeld krijgt men per afzonderlijke vertegenwoordiger de gegevens te zien. Door op te klikken komt men weer terug bij stap 3 van de draaitabelwizard. Klik op . Het resultaat verschijnt evenals de werkbalk draaitabellen:
• •
67/67
Men kan deze tabel nu naar eigen inzicht verder benutten. Merk op dat rechts van de naam van de vertegenwoordiger een mogelijkheid is om een lijst te openen. Dit is het gevolg van het toekennen van de categorie vertegenwoordiger aan Pagina.
Kiest men bijvoorbeeld , dan levert dat als resultaat:
Waarom heet een tabel nu draaitabel? Wel omdat het mogelijk is categorieën op de matrix van positie te wisselen door ze met de muis te verslepen. In onderstaand voorbeeld zijn regio en jaar van plaats gewisseld.
68/68
Ook kunnen Jaar en regio beiden op de Rij worden geplaatst. Overigens is in onderstaand voorbeeld ook een andere bewerking (te weten middelen) uitgevoerd op de waarden. Men realiseert dit eenvoudigweg als volgt: Kies in de werkbalk Draaitabel de optie (de celcursor staat in de draaitabel in de cel Som van verkopen), waarna het volgende dialoogkader mogelijkheden biedt tot andere bewerkingen:
Dus in plaats van Som van verkopen komt er gemiddelde van verkopen te staan en bij deze tekst hoort uiteraard de keuze voor bij <Samenvatten als>. Het resultaat is:
69/69
Hiermee zijn de mogelijkheden van draaitabellen nog lang niet uitgeput. Dit onderdeel van Excel is een wereld op zich. Het proberen waard is in elk geval nog de optie waarbij voor het laatste wordt gekozen. Bij de eerste stap in de wizard Draaitabel wordt nu gekozen voor :
70/70
De overige stappen zijn identiek aan hetgeen hiervoor werd besproken. In dit voorbeeld is de volgende indeling gebruikt:
Na voltooien wordt onderstaand resultaat weergegeven. Hierbij zijn dezelfde manipulatiemogelijkheden beschikbaar als bij een gewone draaitabel:
11.5 Opdrachten. Opdracht 1.
71/71
• •
Open het werkblad consol.zip of consol.exe. Bereken per afdeling het totaal aantal, gemiddelde, hoogste en laagste verbruik per apparaat gebruikmakend van de opdracht Subtotalen...> Alle vier de berekeningen moeten worden getoond in het werkblad.
De antwoorden op de opdrachten staan in een apart werkblad antwoorden (zie laatste tab van de werkmap consol). Opdracht 2. • • •
Ga naar werkblad consolidatie 2. Maak een draaitabel, waarin de som van de verkopen wordt weergegeven per jaar, per regio. Voorkom dat de uitgangsgegevens worden overschreven.
Opdracht 3. • •
Maak in hetzelfde werkblad (consolidatie 2) een tweede draaitabel die de maximum verkopen toont met regio en jaar in de rijen en in de kolom het product. Ga na waar Excel deze tweede draaitabel plaatst, als bij de laatste wizardstap wordt opgegeven zonder een bereik aan te geven.
Opdracht 4. •
•
Een winkelier houdt (in werkblad consolidatie 3) de verkoop van producten per week bij. Aan het einde van de week kopieert hij de eindsaldi uit kolom D naar kolom B. Hoe zal hij dit moeten doen?
Opdracht 5. • •
Ga naar werkblad consolidatie 4. Breng de deelnemersaantallen aan de diverse sportevenementen in een overzicht bijeen.
Extra opdrachten. Opdracht 1. • • •
• •
Open het werkblad consol.zip of consol.exe. Ga terug naar werkblad consolidatie 2. Maak een derde draaitabel en zet in de rijen van de matrix het jaar, in de kolommen het product. De gegevens moeten per vertegenwoordiger afzonderlijk getoond kunnen worden. Van de verkopen wordt de som en van de eenheden het gemiddelde weergegeven. Licht het resultaat van met name de gemiddelden toe.
Opdracht 2. 72/72
• •
Ga naar het werkblad Verzamelstaat Infomax. Consolideer de resultaten van de vestigingen in Aalsmeer, Leeuwarden, Rijswijk en Zwolle in dit werkblad.
12 Validatie. Invoer valideren. Opdracht. 12.1 Invoer valideren. Vaak komt het voor dat men de invoer van een cel wil controleren. Dat kan met de opdracht . De werkwijze is als volgt: 1. Plaats de cursor in de cel die men wil valideren. 2. Geef de opdracht .
3. Geef op de eerste tab bij aan om welk soort gegeven het gaat:
73/73
4. Geef bij de voorwaarde op.
5. Vul de bij de voorwaarde horende waarden in:
74/74
6. Ga naar de tab en vul een Invoerbericht in dat past bij de validatiegegevens voor de betreffende cel:
7. Kies een adequaat symbool voor een reactie op foute invoer en bedenk er een passend bericht bij:
75/75
12.2 Opdrachten. Opdracht 1. • •
Ga na hoe in een cel kan worden afgedwongen dat uitsluitend datums van na 1 jan 1900 worden ingevoerd. Experimenteer naar eigen inzicht met overige validatiemogelijkheden.
13 Statistiek. Frequentieverdelingen maken. Overige statistische onderwerpen Gegevensanalyse. Opdrachten. 13.1 Frequentieverdelingen maken. Bij een frequentieverdeling wordt nagegaan hoeveel waarnemingen in een bepaalde klasse vallen. Werkwijze: •
Draag er zorg voor dat de gegevens waarover de frequentieverdeling gemaakt moet worden beschikbaar is. Bijvoorbeeld:
76/76
•
Voer de klassengrenzen in:
77/77
•
Sleep rechts van de ingevoerde klassengrenzen de cellen aan waarin de frequentieverdeling komt te staan. Sleep één cel meer aan dan er klassengrenzen zijn (voor de frequenties boven de hoogste klassengrens).
• • • •
Klik op de snelknop . Kies de categorie <Statistisch>. Kies de functie . De functie Interval heeft navolgende opbouw: =INTERVAL(gegevensmatrix;interval_verw) Geef het bereik op waarin zich de gegevens bevinden waarmee een frequentieverdeling moet worden gemaakt (gegevensmatrix). Geef het bereik op waarin de klassengrenzen staan vermeld. (interval_verw). Klik nu niet op . Het gaat hier immers om een matrixformule dus druk op de toetsen + <Shift> en <Enter>.
• • •
13.2 Gegevensanalyse Met de invoegmacro Analysis Toolpack wordt de professionaliteit van cijferanalyse met Excel nog sterk vergroot. Allereerst moet de macro aan het menu <Extra> worden toegevoegd met de opdracht <Extra>. 
