Les 8: Formules

Nieuws is dat wat (plotseling) afwijkt van het normale. Is er een stijging van het aantal overvallen? Daalt het aantal werklozen? Houdt de stijging van het minimumloon gelijke tred met de inflatie, of leveren minima stiekem koopkracht in?

Om dit soort vragen te kunnen beantwoorden, is het vaak nodig om berekeningen uit te voeren op data. Het is ondoenlijk om heel uitputtend te zijn – over statistiek is enorm veel te zeggen. Maar vaak volstaat het simpele berekeningen te kunnen maken, zoals stijgingen en dalingen per jaar, zowel absoluut als relatief.

In deze aflevering laat ik zien hoe je een formule bouwt en waar je op moet letten. We nemen een dataset over de influx van buitenlanders in Nederland.[link] Ik heb een tabel van het CBS met aantal personen per nationaliteit, per gemeente tussen 1996 en 2012. Ik wil een idee krijgen of het aantal mensen uit Zuid-Europa sinds de crisis is toegenomen, in welk jaar de grootste groei plaatsvond en welke gemeente eruit springt. Mocht het lukken, dan weet ik in welke gemeente ik het beste kan zoeken om Zuid-Europese migranten te vinden.

Ik ga eerst filteren op nationaliteit. Ik kies Griekenland, Italië, Portugal en Spanje. Knip die selectie, CTRL-A, CTRL-C, CTRL-V en zet die op een nieuw werkblad. Deze indeling is nog niet handig. Even een draaitabel maken en de jaren in kolommen zetten.

Iedere formule begint met een “=”-teken.

Laten we eerst eens een gemiddelde per gemeente uitrekenen. Een gemiddelde is de som van alle waarden, gedeeld door het aantal waarden. Excel beschikt over zeer veel functies. Dit zijn kant-en-klare formules waarbij je alleen nog maar hoeft aan te geven welke data die formules moeten gebruiken. In dit geval =Gemiddeld(B5:R5). Voilà.

We kunnen ook andere formules gebruiken: =Som, =Stdev, =Kleinste, =Grootste

Excel geeft je zelf al tips hoe je de formule kunt afmaken, wat nog nodig is.

In dit geval is het interessant om het verschil tussen kleinste en grootste waarde te bekijken. Dat zegt namelijk iets over de toe- of afname in een gemeente.

Hoe groter het getal, hoe groter de toe- of afname. We gebruiken hiervoor: =Kleinste en =Grootste. Hij vraagt om =KLEINSTE(matrix;k). Matrix is het bereik en k het hoeveelste getal van boven of onder (in dit geval onder, want het gaat om de kleinste).

Relatieve getallen zeggen echter niet alles. Een stijging van 0 naar 1 is immers 100 procent. Dat lijkt heel wat, maar het is veel spectaculairder als er een stijging is van 5000 naar 9000. Dat is geen 100 procent, maar betreft wel heel veel mensen. Het kan dus geen kwaad om een filter aan te brengen op je data. Waar je de grens legt, is altijd arbitrair. We stellen een filter in.

Ik wil nog iets laten zien met de verwijzingen in formules, maar daar heb ik andere data voor nodig. Stel je hebt een fietsenwinkel en je hebt een overzicht per maand van het aantal verkochte herenfietsen. Je wilt een overzicht krijgen van je omzet per maand en een overzicht van de BTW. Een herenfiets kost 325 euro ex. btw. Die som is makkelijk te maken.

Het is ook eenvoudig om BTW uit te rekenen. Maar nu is het BTW tarief veranderd van 19 procent naar 21. Je wilt eigenlijk ook weten hoeveel BTW je in de oude situatie zou hebben betaald. Je kunt daar een trucje voor toepassen, maar dan moet je een verwijzing vastzetten. Dat doe je met een $-teken. Dit kun je het beste even in de video bekijken. [link]

Dan wil ik nog een formule laten zien die ingewikkeld lijkt, maar eigenlijk heel simpel is en waarmee je jezelf veel werk kunt besparen: Verticaal Zoeken.

Met Verticaal Zoeken kun je twee tabellen aan elkaar ritsen.

Ik heb een mooie tabel met de CO2-emmissies per EU-land in 1990 en in 2011. Ik wil graag de progressie laten zien per land in het terugdringen van CO2-uitstoot. Nu zijn absolute cijfers niet zo interessant: het is logischer dat Duitsland meer uitstoot dan Denemarken. Daarom wil ik de cijfers eerst omzetten naar hoofd van de bevolking.

Ik heb een tweede tabel met de bevolkingscijfers.[link]

Nu kan ik deze twee tabellen op verschillende manieren gebruiken. Ik kan een formule maken die tussen twee sheets werkt. [zie video] Ik kan ook de bevolkingsdata naar de andere sheet halen met de functie Verticaal Zoeken. [zie video]

Tips en overwegingen:

1) Je zal je wiskunde een beetje moeten oppoetsen. Maak in het begin geregeld gebruik van de functiewizzard. Die legt doorgaans helder uit wat er van je verlangd wordt om een goede formule te maken.

2) Er zijn veel online tutorials die je meer vertellen over het maken van formules. Lees die eens door.

Bijlage

• CDJ-Les-8.xlsx