Nieuwe pagina 1

© h.hofstede (h.hofstede@hogeland.nl)

Formules in Excel.

De komende paar lessen gaan we oefenen met het programma EXCEL.
Ikzelf gebruik de versie EXCEL 2007. Intussen zijn er al nieuwe versies EXCEL: 2010, 2013, 2016, 2019, 2021 maar die verschillen niet zo heel veel van elkaar.
Als er wezenlijke veranderingen zijn zal ik die melden.

Open het programma en je krijgt dit te zien:

Dit is eigenlijk een enorme tabel met cellen die genummerd zijn (bovenaan A,B,C, .... en links 1, 2, 3, .....). Zo hebben alle cellen een naam. Excel is eigenlijk een enorme rekentabel (we noemen dat een spreadsheet).
Met de muis kun je een cel selecteren (je kun ook met de cursor door de cellen heen lopen). In het voorbeeld is cel D4 geselecteerd.

Laten we er een tabel in gaan zetten.....
Ik ga als leraar de wiskundecijfers van mijn wiskunde -D klasje erin zetten. Dat is een lekker klein klasje dus dat is weinig werk.
In cel A1 zet ik de titel: Wiskunde-D cijfers, en in rij 2 de koppen: "naam" en "cijfer"
In de eerste twee kolommen voer ik daarna de voornamen en de cijfers van mijn 5 leerlingen in:

Je kunt de opmaak (lettertype, cursief, vet gedrukt, onderstreept, lettergrootte e.d.) veranderen met die knoppen in de blauwe balk daar linksboven. Net zoals uitlijning en knippen en plakken zoals je waarschijnlijk ook in Word gewend bent. Niks nieuws te melden.

Hiernaast zie je waarschijnlijk de enige aparte knop links bovenin.

Je kunt hiermee aangeven of de cellen die je op dit moment geselecteerd hebt randen moeten hebben en zo ja waar en hoe dik.

Klap hem maar uit; het wijst zichzelf wel.

Laten we ons vooral gaan bezighouden met belangrijkere zaken.........

Ik wil natuurlijk graag uitrekenen wat het gemiddelde cijfer van mijn klas is.

Daarvoor ga ik een formule in cel B8 zetten.

Om het gemiddelde te berekenen moet ik de cellen B3 tm B7 bij elkaar optellen en dan delen door 5, dus die formule is: B8 = (B3 + B4 + B5 + B6 + B7)/5
Om die formule in cel B8 te krijgen selecteer je eerst cel B8, en daarna click je in witte werkbalk die direct boven de letters van de celnamen staat. Die bij de rode cirkel hiernaast. Dat noemen we de formulebalk want daar staan de formules.

Formules beginnen in EXCEL altijd met "="

Dus in de formulebalk typ je: = (B3 + B4 + B5 + B6 + B7)/5 en daar verschijnt het gemiddelde in cel B8!!!!
Het is 6,18.

Rood omcirkeld zie je in de formulebalk de formule staan die ik heb ingevoerd. (tenminste als je cel B8 hebt geselecteerd).

Celopmaak.

Je kunt de opmaak van een cel (wat erin staat en in welke vorm) veranderen door de cel te selecteren en dan rechts te clicken.
Dan krijg je het menu hier linksonder en als je kiest "celeigenschappen dan krijg je het submenu rechtsonder. Daarin kun je bij "getal" kiezen wat er in je cel staat (een gewoon getal, een datum, valuta noem maar op) Bij een gewoon getal kun je bijvoorbeeld aangeven hoeveel cijfers je achter de komma wilt (Decimalen staat in dit voorbeeld op 2).

Vulgreep.

Stel dat ik als leraar deze toets wat op wil waarderen door een N-term van 1,12 in te voeren. Dat betekent dat ik elk cijfer met 1,12 vermenigvuldig zodat iedereen er 12% bij krijgt. Aardig toch?
Nou, dan zet ik in cel C1 de uitleg N = 1,12 (dan weet ik later nog wat er in deze kolom staat)
En dan ga ik in cel C3 staan en vervolgens voer ik de formule " = B3 * 1,12" in bij de formulebalk. Ik moet immers het getal uit B3 (=cijfer van hans) met 1,12 vermenigvuldigen......
Dan verschijnt er in cel C3 voor Hans het opgewaardeerde cijfer 7,168. Ga dat zelf maar na.

OK, Hans kan geen 7,168 hebben (wij geven cijfer op 1 decimaal) dus ik ga afronden op 1 decimaal via rechtsklikken - celeigenschappen - getal - 1 decimaal.

Dat geeft de figuur hiernaast. Hans heeft een 7,2.
Nu wil ik graag die formule voor Hans naar alle andere leerlingen kopiëren.
Dat kan met de vulgreep.
Dat is dat kleine blokje rechts onderaan cel C3 (ik heb het hiernaast iets groter weergegeven dan het is)
Ga met je cursor op dat blokje staan, dan verandert het in een plusje ( + )
Pak dat plusje vast door links te klikken en sleep het omlaag naar de cellen C4 tm C7, dan gebeurt er iets wonderbaarlijks!!!!!

De formule voor Hans wordt gekopieerd, maar de celverwijzing in deze formule wordt automatisch aangepast!
In C3 staat (B3*1,12), maar in C4 komt nu (B4*1,12) en in C5 komt (B5*1,12) enz.
Zo krijgt iedere leerling zijn eigen opgewaardeerde cijfer.

Dat is de kracht van Excel!

Als je omlaag sleept veranderen de nummers van de rijen in de formule mee, en als je opzij zou slepen dan veranderen de letters van de kolommen in de formule mee!
Je kunt omhoog slepen of omlaag en naar links of naar rechts. Het werkt altijd!!

Zo kun je bijvoorbeeld direct de formule voor het gemiddelde (B8) met de vulgreep vastpakken en één cel naar rechts slepen. Dan staat het nieuwe gemiddelde in C8.
(nog wel weer even de opmaak goed maken en afronden en zo via "Celeigenschappen")

En als ik nou niet wil dat die formules mee veranderen?

Dat kan heel goed natuurlijk.
Stel dat ik voor een aantal verschillende N-termen wil kijken wat het effect op de cijfers is. Dus ik weet nog niet zeker of die 1,12 de goede opwaardering is.

Dan zet ik in C1: "N-term" (uitleg voor later) en in C2 zet ik de 1,12.
Nu maak ik in C3 voor Hans de formule: = B3*C2
Uiteraard geeft dat weer de 7,2 voor Hans.
Maar als ik nu die formule met de vulgreep omlaag kopieer, dan gaat het fout! Zie je waarom???????

Dan wordt C4 voor Joke gelijk aan B4*C3 want die C2 schuift ook omlaag, en dat moet niet; het moet wel steeds C2 blijven.
Je kunt voorkomen dat een celwaarde mee verandert als je er dollartekens voorzet.

Probeer maar C3 = B3 * $C$2
Als je nu omlaag sleept verandert alleen de B3 mee en de C2 blijft C2

Zie de figuur hiernaast.
Als je nu de cel C2 verandert (dus een andere N-term kiest zoals hiernaast N = 1,2) dan verandert alles in één keer mee. Dus alle cijfers plus het gemiddelde worden in één keer aangepast.

Handig toch?
En als je bijvoorbeeld $A3 naar beneden sleept verandert alleen de 3, en niet de A.
En bij D$2 verandert alleen de D en niet de 2.

Als "toegift zal ik af en toe in deze lessen een Excel-trucje laten zien dat wel handig is maar niet per se nodig. Voor de echte liefhebbers dus.
Hier is de eerste:

LEUK EXCEL TRUCJE

Stel dat je de cijfers 1,2,3,4,5,.......20 (of nog veel meer) wilt invoeren in kolom A
Dan kun je dat natuurlijk gewoon één voor één doen, maar het kan sneller:

Zet in cel A1 het getal 1
Zet in cel A2 het getal 2
Selecteer nu beide cellen samen (zie hiernaast).

De vulgreep staat dan onderaan beide cellen samen.
Als je die nu omlaag trekt krijg je alle cijfers 1,2,3,4,5,6,7........ net zolang als je maar doorgaat!

En als je begint met 2,5 dan krijg je 2,5,8,11,14,...... COOL toch?

OPGAVEN

HIER staat een Excelbestand met de SE-cijfers van een examenklas (er zijn 4 SE's geweest)
Boven elk SE staat het gewicht ervan (dat is hoe vaak het meetelt in het eindcijfer).

Bereken het gemiddelde cijfer van elke leerling, afgerond op 1 decimaal.

Bereken het gemiddelde cijfer op elk SE apart

Bereken het gemiddelde cijfer van de hele klas over alle SE's

Bereken je antwoord op vraag c. door een formule te maken met behulp van de vier antwoorden op vraag b.

HIER staat een Excelbestand met de gewichten (in kg) en lengtes (in cm) van een aantal mensen.
De Body Mass Index ( BMI) is een getal dat aangeeft of je te zwaar bent of niet.
De formule ervoor is BMI = ^G/_L² (G in kg, L in m)
Bereken de gemiddelde BMI van al deze mensen.

De rij van Fibonacci is een hele beroemde, en ziet er zo uit: 1-1-2-3-5-8-13-21-34-....
Elk getal is de vorige twee bij elkaar opgeteld
|Bereken met een Excel-bestand hoe groot het 50^e getal uit deze rij is.

We gaan voor basisschool leerlingen een vermenigvuldigtabel maken voor alle tafels van 1 tot en met de tafel van 10. Dat wordt dus een 10 x 10 tabel

Zet in B1- C1 - D1 .... K1 de getallen 1 tm 10 (misschien kun je het handige trucje hierboven gebruiken?)
Zet op dezelfde manier in A2- A3- A4 - .... - A11 de getallen 1 tm 10.

De rand van de tabel is nu klaar (zou ik even lijnen omheen zetten)
Op de kruising moet steeds komen te staan wat de twee getallen met elkaar vermenigvuldigd zijn.

Probeer deze tabel zo slim mogelijk te vullen. Denk aan de dollartekens bij het kopiëren van cellen!!!!!