SB7 Excel

Database.

 

Je hebt de volgende gegevens tot je beschikking van de “Vereniging tot nut van het Gemak”.
Let op: JE HOEFT DIT NIET OVER TE TYPEN IN EXCEL. Het bestand met de gegevens heet DATABASE. Het staat onder aan deze les.

Startjaar

Geslacht

Voornaam

Achternaam

Woonplaats

Groep

2010

m

Kees

Wouterse

Groningen

1

2009

v

Patricia

Kock de

Amsterdam

2

2005

m

Karel

Neushoorn

Groningen

3

2010

v

Tokyo

Tasjesplant

Amsterdam

3

2009

m

Pietje

Precies

Rotterdam

1

2006

m

Lester

Virides

Rotterdam

2

2005

m

Timon

Lepidus

Assen

3

2009

v

Wanda

Kiloknaller

Assen

1

2005

m

Koos

Werkeloos

Amsterdam

2

2010

v

Tanja

Suikerpot

Groningen

3

2009

v

Foekje

Dillema

Amsterdam

1

2007

v

Iona

Joric

Assen

3

2007

m

Fonseca

Hamman

Rotterdam

2

2008

v

Harma

Binnenband

Groningen

2

2008

m

Toby

Piramidespel

Rotterdam

3

2010

m

Rocco

Raket

Groningen

1

2008

v

Dieuwke

Uvertouw

Assen

2

2010

m

Robert

Uvertouw

Assen

1

2009

v

Rianne

Wijk van

Groningen

3

2006

v

Wanda

Touwslager

Rotterdam

3

2009

m

Jan

Petfles

Groningen

1

 

Het valt op dat de namenlijst niet op alfabet staat. Open het bestand DATABASE en volg de aanwijzingen:

1. Klik op de kolom achternaam (kolom D, op de letter klikken), zodat die kolom actief wordt. Klik op de knop Alfabetiseren en Filteren en kies AZ van laag naar hoog. Klik bij de melding Selectie uitbreiden JA en daarna OK.

Nu staan de namen op alfabet

2. We gaan nu een tabel maken, om snel gegevens te kunnen filteren. Ik wil bijvoorbeeld alleen de leden zien die in Groningen wonen.

Ga op een willekeurige cel staan in de tabel.

Klik op Invoegen en daarna op tabel. Excel geeft  nu het bereik (automatisch aan). Klik op OK.

Je ziet nu een tabel voor je met filtermogelijkheden. Klik op het neerwaarts gerichte pijltje naast woonplaats. Vink alle plaatsnamen uit, behalve Groningen. Dit is het resultaat.

Startjaar

Geslacht

Voornaam

Achternaam

Woonplaats

Groep

2010

m

Kees

Wouterse

Groningen

1

2005

m

Karel

Neushoorn

Groningen

3

2010

v

Tanja

Suikerpot

Groningen

3

2008

v

Harma

Binnenband

Groningen

2

2010

m

Rocco

Raket

Groningen

1

2009

v

Rianne

Wijk van

Groningen

3

2009

m

Jan

Petfles

Groningen

1

 

Opdracht:

Doe nu hetzelfde voor de selectie van geslacht en beantwoord de vraag: hoeveel vrouwen telt de vereniging?

(je antwoord moet zijn 10)

Beantwoord ook de volgende vragen:

1. Hoeveel leden zijn er in 2010 lid geworden?

2. Wie zitten er allemaal in groep 1? Laat een screenshot met je antwoord zien aan de docent!

3. Hoeveel leden zijn geboren in 1980? Idem

 

Opdracht:

Maak in excel een tabel van 20 van je Facebookvrienden met de volgende kolommen.

Maak hier vervolgens een tabel van met filtermogelijkheden.

Maak vervolgens een selectie op:
- aantal vrienden (van hoog naar laag)
- achternaam (van A- Z)

Voornaam          Achternaam      Woonplaats       Geboortejaar    Geslacht              Aantal vrienden

 

 

 

Database.xlsx

Project BuurtBieb

Je bent vrijwilliger bij de Buurtbieb, een kleinschalige organisatie voor mensen in de buurt Kimmenadert, die als doel heeft de bewoners van de buurt gratis in staat te stellen boeken te lezen. Voor dit doel hebben ze – als begin – een paar dozen vol met boeken cadeau gekregen van personen die gingen verhuizen en veel boeken weg hebben gedaan.

Je opdracht is om een digitaal bestand samen te stellen voor de buurtbewoners waarin ze gericht kunnen zoeken naar boeken die hun interesse hebben.

De boeken moeten in een minidatabase worden opgenomen en voorzien van metadata, zodat de bewoners gemakkelijk kunnen zoeken.

Hieronder staat een lange rij met boeken die allemaal in de database moeten worden ingevoerd, voorzien van de volgende metadata:

Achternaam auteur
Voornaam auteur
Nationaliteit auteur
Genre van het boek
Jaar van uitgifte van het boek
Uitgeverij
Titel van het boek
Taal waarin het boek is geschreven

In de boekendoos zitten alle boeken, maar niet de informatie die in de database er bij moet komen. Die moet je dus opzoeken op internet.

Boekendoos

Voorbeeldbestand

Excel is heel goed in het snel vergelijken van grote hoeveelheden cijfers. Zo snel dat geen mens er met een rekenmachine tegen op kan. Het scheelt dus tijd door slim om te gaan met de FUNCTIES in Excel.

De Functie ALS vergelijkt steeds twee getallen met elkaar en geeft dan een soort van beoordeling of conclusie. Bijvoorbeeld of in een bepaalde periode het budget van een project is overschreden of niet. Zie voor een voorbeeld het bestand hieronder.

Bekijk voor je aan de opdracht begint ook eerst de videotutorial van Microsoft!

Om met dit soort functies te kunne werken moet je eerst even iets weten over wat ze bij Excel een "logische test" noemen.

In gewoon Nederlands: is iets wel of niet waar.

Bijvoorbeeld: heeft een verkoper genoeg omzet gemaakt om in aanmerking te komen voor een bonus. Stel dat de norm voor een bonus een omzet van € 100.000 is, dan wil Excel weten of dit wel of niet waar is.

Je hebt verschillende "operatoren" die je kunt gebruiken in logische testen. Die zijn:
>    groter dan
>= groter dan of gelijk aan
<    kleiner dan
<= kleiner of gelijk aan.
=    gelijk aan

In het voorbeeld van hierboven zou je dus de operator >= nodig hebben, want de omzet moet minimaal € 100.000 zijn om de verkoper een bonus te kunnen geven.

Je kunt de kunst afkijken in het bestand functieals hieronder.
Maar bekijk eerst de fllm met de uitleg hoe het moet of open de handleiding van Microsoft.

 

Microsoft tutorial Als Formule

functieals.xlsx

Uitleg van de Als Formule Bron: Sander Kollerie via Youtube

 
 
De functies van Excel moeten heel nauwkeurig worden geschreven, anders werken ze niet. De tekens ; (puntkomma) en "(aanhalingstekens) moeten echt precies op de goede plek staan.

Voorbeeld

Je zag in het excel document als formule staan =ALS(B3>C3;"fouteboel";"prima")

Bouw van een ALS-Formule:    (Let op: als je een engelstalige Excel hebt, is het de IF-formula)

 
 

OPDRACHT

Open onderstaand document. Je ziet voor twee jaren een overzicht van gemaakte kosten en voorspelde kosten. Je wilt snel weten in welke maanden het budget is overschreden, zodat je snel kunt zoeken naar oorzaken.

Je maakt dus voor deze getallenreeks een Als Formule. Als het budget is overschreden moet er de conclusie komen "VERLIES" en als de werkelijke kosten lager zijn dan de verwachte kosten moet er de conclusie komen "GELD OVER".

Zorg er verder voor dat de kolombreedte van de cellen waarin de conclusies komen een breedte heeft van 20.

 

 

Opdracht ALS Formule

Je kunt de functie ALS prima combineren met extra voorwaarden, zoals bijvoorbeeld een gemiddelde. Een goed voorbeeld hiervan is een hele serie toetscijfers van een klas met studenten. In onderstaand exceldocument zie je een voorbeeld van een klas met studenten en al hun toetscijfers mooi naast elkaar. Een student is geslaagd als het gemiddelde cijfer hoger is dan een vijf en een half (55).

Je ziet dat in de formule terug. =ALS(GEMIDDELDE(B2:D2)>55;"geslaagd";"gezakt")

 

OPDRACHT

Bestudeer eerst het voorbeeld op blad 1 van het document hieronder.

Ga daarna naar blad 2. Daar zie je een overzicht van een klas met al hun toetscijfers.

Maak de juiste formule, zodat de mentor direct kan zien we er geslaagd dan wel gezakt is.

Lever dit document in in de inlevermap in de ELO en zet er even bij wie er gezakt zijn.

Geslaagd of gezakt met één voorwaarde

Bron: Sander Kollerie Cursus Excel Geavanceerde functies en formules Deel 3: ALS en gerelateerde functies in Excel

 

Zullen we het een beetje moeilijker maken?

In het voorbeeld van zojuist zagen we dat om te slagen de student een gemiddeld cijfer van 55 of hoger moest halen. Dat is in de werkelijkheid niet erg realistisch. Veel opleidingen hanteren een extra voorwaarde. Bijvoorbeeld dat geen enkel cijfer lager dan 35 mag zijn.

Ook hier kan Excel ons prima helpen om snel vanuit een grote cijferbrij te bepalen wie er geslaagd of gezakt is.

We gaan aan de slag met het document dat we hiervoor ook al hebben gebruikt, het overzicht van de toetscijfers.

Volg onderstaand stappenplan om de twee voorwaarden slim met elkaar te koppelen.

1. Je ziet de cijfers van de toetsen 1 tot en met 8 in de kolommen C tot en met J, met het eerste cijfer in C2.

Wat je als eerste doet is het gemiddelde bereken per student. Dus zet je in kolom K, in cel K2 de formule (=gemiddelde C2:J2)
Als je dit hebt gedaan kun je die formule weer naar beneden slepen, zodat je nu voor elke student het gemiddelde cijfer ziet. Je mag afronden op 1 decimaal. (1 cijfer achter de komma).

2. De tweede voorwaarde om te slagen is dat alle cijfers hoger of gelijk aan 35 moeten zijn.

3. In kolom L, te beginnen met cel L2 zet je nu de formule =MIN(c2:J2). Ook die formule sleep je naar beneden, zodat je voor elke student zie wat zijn of haar laagste cijfer is.

4. Nu komt het moelijke werk, want nu moet je een formule hebben die alle twee voorwaarden combineert.

Die formule luidt =ALS(EN(K2>55;L2>=35);"geslaagd";"gezakt")

Als je deze formule in gewone mensentaal zou uitspreken, dan staat er: Als zowel het gemiddelde hoger is dan 55 als ook het laagste cijfer hoger of gelijk is aan 35, dan is de student geslaagd. Als aan één van beide voorwaarden niet wordt voldaan is de student gezakt.

Geslaagd of gezakt met twee voorwaarden

Opdracht met voorwaarden ALS en EN

Je bent leidinggevende van een verkoopafdeling en jij staat voor de vraag welke verkoper aan het eind van het jaar een kerstbonus krijgt en wie niet.

In onderstaand exceldocument vind je een overzicht van de omzet per verkoper per kwartaal en een overzicht van het aantal ziektedagen per kwartaal.

Je besluit dat er twee criteria zijn om iemand een bonus toe te kennen, namelijk:
1) de gemiddelde omzet per kwartaal moet € 5000 of hoger zijn (dus >= 5000)
2) het aantal ziektedagen per kwartaal mag niet hoger zijn dan 2 (=<2).

Welke verkopers krijgen wel een bonus en welke niet? Motiveer je antwoord door toepassing van en ALS en EN formule.

Je levert je excel bestand in in de ELO, in de daarvoor aangewezen inlevermap.

Opdracht met 2 voorwaarden

Een veel gebruikte functie in Excel is om op een aantal tabbladen dagelijkse getalsmatige informatie bij te houden waarbij de totaalcijfers op een andere pagina zichtbaar worden. Hiervoor heb je een verwijzingn naar een ander tabblad nodig.

Een voorbeeld:

Hieronder staat een bestand met de naam VerantwoordingSBUstudentvoorbeeld. Open dit bestand om dit te bestuderen.

Je ziet dat er op de tabbladen 2, 3 en 4 invulstaten zijn gemaakt waarin gewerkte uren kunnen worden ingevuld. En dat op tabblad 1 een overzicht staat waarin automatisch de totalen worden opgeteld van de uren die op de tabblade 2, 3 en 4 zijn ingevuld. Probeer het maar eens.

 

Opdracht

Onder aan deze bladzijde kun je een bestand vinden met de naam Omzet winkelketen LAMA. Open dit bestand.
Winkelketen Lama heeft filialen in Groningen, Leeuwarden, Assen, Zwolle en Arnhem. De directeur wil dat jij een mooi overzicht maakt waarin op de tabbladen 2 tot en met 6 de namen van de plaatsen staan en de overzichten per maand van de omzet. Maar directeuren hebben weinig tijd en deze directeur wil op tabblad 1 een overzicht hebben van het jaartotaal per plaats.

 

- Vul eerst overal getallen in, zodat het wat echter lijkt. Neem overal getallen tussen de 500 en de 2000, je mag zelf weten hoeveel omzet in de winkels per maand wordt gemaakt.

- Zorg dat je bij Totaal steeds de omzet van de maanden optelt; je gebruikt (uiteraard) de somformule. Doe dit voor elk filiaal.

- Zet nu in het overzicht op het eerste tabblad de juiste formule, zodat direct zichtbaaar wordt hoe groot de omzetten zijn per winkel.

Lever je uitwerking in in de inlevermap

VerantwoordingSBUstudentvoorbeeld.xlsx

Winkelketen Lama

Je kunt in Excel prachtige overzichten maken voor anderen om in te vullen. Bijvoorbeeld het bijhouden van gewerkte uren en dit dan automatisch laten optellen. Een voorbeeld hiervan is het urenverantwoordingsformulier dat jullie invullen om bij te houden hoeveel tijd je besteedt aan dit keuzedeel.

Wat je dan natuurlijk niet wilt is dat je formulier door iemand anders helemaal wordt veranderd! Daar is een oplossing voor, namelijk het beveiligen van cellen. Maar om te bereiken wat je wilt moet je natuurlijk ook zorgen dat het deel dat door anderen moet worden ingevuld niet beveiligd is. Want anders kunnen ze niets invullen.

OK, daar gaan we dan.

Open om te beginnen het bestand hieronder met de naam Gewerkte UREN.

Uitleg Beveiligen van cellen in excel
Bron: WIm de Groot via Tekst en Uitleg

Jumbo werkuren