Formule

Wil je de voortgang van een project goed bijhouden, digitaal je to-do lijsten maken of (net als ik) nu eens serieus aan de slag met je goede voornemens? Een afvinklijst maken in Excel kan hierbij helpen. Je kunt zo’n lijst maken in Excel of Google Sheet. 

Mijn tip? In Google Sheets is het maken van een afvinklijst een stuk makkelijker dan in Excel en het is ook makkelijker om er vervolgens berekeningen mee te doen. Beide mogelijkheden leg ik hieronder uit.

Een afvinklijst in Google Sheet

1. Selecteer de cellen in je Google sheet waar je een afvinklijstje in wilt maken. 

2. Selecteer in het menu Invoegen > Selectievakje

3. In de cel staat nu een selectievakje.

Je kunt nu verder rekenen; je kunt bijvoorbeeld het aantal afgevinkte to-to’s optellen of het percentage van je gehaalde doelen berekenen. Wil je het aantal afgevinkte doelen optellen, neem dan de volgende stappen:

4. Gebruik de formule AANTAL.ALS; selecteer als bereik de cellen met de selectievakjes; en als criterium ‘true’. De formule voor selectievakjes die in D16 tot en met D20 staan wordt dan als volgt

 =AANTAL.ALS(D16:D20;”true”)

Waarom rekenen met true en false? Google geeft automatisch de waarde ‘true’ aan de selectievakjes die zijn aangevinkt. Niet-aangevinkte selectievakjes krijgen waarde ‘false’.

Een afvinklijst in Excel

1. Zorg dat je het tabblad ‘ontwikkelaars’ tussen je tabbladen hebt staan. 

Heb je die niet? Voeg het tabblad dan toe via het Excel hoofdmenu bovenin. Kies voor Voorkeuren (Preferences) > Ribbon & Toolbar. In de rij rechts vink je Ontwikkelaars (Developer) aan. Klik op opslaan. 

• 

2. Selecteer in het tabblad Ontwikkelaars > Invoegen > Selectievak (Checkbox)

3. De cursor verandert nu van een ‘groter’ kruisje naar een kleiner kruisje. Klik waar je het selectievakje wilt hebben.

4. Pas het selectievakje aan door bijvoorbeeld de tekst aan te passen of weg te halen, de grootte van het vak je aan te passen.

5. Je hebt nu een selectievakje, deze staat NIET in een specifieke cel. Dit heeft als voordeel dat je het selectievakje overal kunt plaatsen in bestand, het gaat wel ten koste van de gebruiksvriendelijkheid.

Wil je hier het aantal afgevinkte doelen optellen, neem dan de volgende stappen:

6. Koppel de waarde van elk selectievakje aan een cel. Selecteer het selectievakje en klik met rechtermuis zodat het volgende menu veld verschijnt.

7. Kies voor Format Control en selecteer de cel waarmee je het selectievakje wilt koppelen. In het voorbeeld hieronder is het selectievakje van taak 5 gekoppeld aan cel A10. Omdat dit selectievakje niet is aangevinkt, krijgt cel A10 waarde ‘false’.

8. Gebruik de formule AANTAL.ALS (of COUNT.IF) met als bereik A6 tot en met A10 en als criterium ‘true’. De formule wordt (in het Engels):

=COUNTIF(A6;A10;TRUE).

Liever niet zelf een afvinklijst maken?

Het maken van een afvinklijst in Google Sheet is erg simpel. Door de opzet van dit overzicht (met wekelijks doelen afvinken) is het in dit geval aan te raden om Google Sheet te gebruiken. Het invoegen van een selectievakje gaat in Google Sheet is simpel en er vervolgens berekeningen mee maken gaat in Sheets veel simpeler.

In Excel kun je ook selectievakje gebruiken, zorg dan wel dat je het developer tabblad zichtbaar hebt gemaakt. 

Update: het bestand met de doelen uit deze blog is niet meer te downloaden. Een nieuw bestand voor 2022 vind je in deze blog.

Voor de sterrenrating kopieerde ik een formule uit een ander blog. Beter goed gejat dan slecht bedacht, right? De formule werkte, maar werd niet uitgelegd. Ik wilde ‘m begrijpen, zeker omdat ik de formule REST (in het Engels MOD) en AFRONDEN.NAAR.BOVEN (ROUNDUP) nog niet eerder heb gebruikt. Ik zocht het uit en in dit blog wordt de sterrenrating formule uitgelegd.

=IF(C$1<=$B3;1;IF(ROUNDUP($B3;0)=C$1;MOD($B3;1);0))

Uitgangspunten voor de sterrenrating formule

De formule zet de score van de film om in 10 getallen. Op basis van die getallen, maakt conditionele opmaak er hele, halve of lege sterren van. Verder is er voor elke ster een referentiecijfer nodig, die nodig is voor de formule. Die staat in de eerste rij.

Conditionele opmaak

Bij deze formule zijn 3 soorten sterren mogelijk door het gebruik van conditionele opmaak:

• Er is een hele ster (bij uitkomst 1 bij de formule),

• een halve ster (uitkomst tussen 0,5 en 1) en

• een lege ster (uitkomst van de formule is kleiner dan 0,5).

Het mooie van deze formule is dat die werkt voor elk van de sterren, je hoeft dus niet per rij of per kolom de formule aan te passen.

Een referentiecijfer

Elk van de 10 sterren heeft een referentiecijfer (RC) in rij 1: ster 1 krijgt nummer 1, ster 2 nummer 2 enz. De formule verandert steeds mee, omdat die aan de ene kant steeds verwijst naar het betreffende referentiecijfer en aan de andere kant gebruik maakt van de vaststaande score. Delen van de formule vastzetten doen je door het gebruik van het dollarteken. Deze dollartekens kun je voor de uitleg in dit blog vergeten.

Sterrenrating formule in het kort uitgelegd

• Een gele, gevulde ster krijg je bij uitkomst 1 van de formule, omdat de score van de film groter of gelijk is aan het referentiecijfer van de ster.

• De formule geeft als uitkomst het cijfer achter de komma. Deze is groter dan 0 en kleiner dan 1. Met de conditionele opmaak wordt dit lege ster als de uitkomst tussen 0 en 0,5 ligt of een halve ster als de uitkomst tussen 0,5 en 1 ligt.

• Is de uitkomst groter dan 0? Dan is de uitkomst 0, dat wordt een lege ster.

Sterrenrating formule uitgelegd in chronologische volgorde

Neem bijvoorbeeld de score 8,7 van Ford v Ferrari. Hoe werkt de sterrenrating formule dan?

Eerste deel van de formule

Bij een 8,7 wil ik dat de 1^e tot de 8^e ster geheel gevuld zijn. Als het referentiecijfer van de ster kleiner of gelijk is aan 8,7 dan wordt die gevuld. Voor de 8^e ster geldt J1<=B3; het referentiecijfer (J1) is kleiner of gelijk aan de score (B3). Deze cel krijgt daarom uitkomst 1.

Tweede deel formule

De formule geeft bij de 9^e ster geen hele ster, want de score 8,7 is niet groter of gelijk aan referentiegetal 9. Dan gaat het tweede deel van de formule in werking.

De als formule gaat hier als volgt: Als we de score van de film naar boven afronden zonder cijfers achter de komma, is deze score dan gelijk aan het referentie getal? Het antwoord is hier altijd ja. Dan wordt de MOD (de REST) formule gebruikt om het cijfer achter de komma te bereken als uitkomst.

De MOD formule neemt steeds stapjes van 1 weg bij het oorspronkelijke basis getal en houd als uitkomst 0,7 over. Met de conditionele opmaak wordt hier een halve ster van gemaakt. Was de uitkomst bijvoorbeeld 0,2 (bij een score van 8,2) dan werd hier door de conditionele opmaak een lege ster van gemaakt.

Derde deel formule

De score is dus lager dan het referentiegetal (8,7 is lager dan 10) EN de score is afgerond naar boven niet gelijk aan het referentiegetal (8,7 is afgerond 9 en daarmee niet gelijk aan 10). Dan is de uitkomst 0 en met conditionele opmaak wordt dat een lege ster.

Sterrenrating formule uitgelegd in 2 onderdelen

REST formule

Wanneer je een restgetal wilt uitrekenen, gebruik je de REST formule. (MOD in het Engels) Je geeft een basis getal aan, je geeft aan door welke waarde je deze wilt delen en de REST formule geeft aan welke waarde er overblijft na het delen door dit gehele getal.

Je wilt bijvoorbeeld weten hoeveel je overhoudt als je 100 (basis getal) deelt door 34 (de deler). De formule is dan REST(100,34), je krijgt als antwoord 32. Je kunt twee keer 34 van 100 afhalen, je houdt dan 32 over. Daar kun je geen 34 meer vanaf halen.

REST formule in de sterrenrating uitgelegd

De REST formule gebruik je voor de halve of lege ster. Je deelt 8,7 (in cel B3) door 1. De uitkomst van de formule is wat overblijft, de rest. In dit geval is dat het cijfer achter de komma. Op basis van conditionele opmaak wordt daar een halve of een lege ster van gemaakt.

Andere voorbeelden REST formule

Je kunt de REST formule gebruiken als je een groepsindeling wilt maken en je weet niet of het handiger is om 54 mensen in groepen van 4, 5 of 6 op te splitsen. (In 6, dan hou je niemand over)

REST = MOD in het Engels

MOD komt van modulair rekenen; geheeltallig rekenen met een getal als bovengrens. De modulus. De analoge klok is een voorbeeld, waarbij met modulus 12 wordt gerekend. Als het 9 uur is, dan staat de klok 8 uur later niet op 17 uur, maar op 17-12 = 5 uur.

AFONDEN.NAAR.BOVEN formule

De formule AFRONDEN.NAAR.BOVEN spreekt voor zichzelf en hoeft eigenlijk niet te worden uitgelegd. Wel is belangrijk om te weten dat de afronding ALTIJD naar boven gaat. Waar je in het dagelijks leven 5,2 afrondt naar een 5, wordt bij de formule AFRONDEN.NAAR.BOVEN een 5,2 afgerond naar een 6.

AFONDEN.NAAR.BOVEN formule binnen de sterrenrating uitgelegd

In deze formule worden scores die niet groter of gelijk zijn aan het referentiegetal naar boven afgerond. Zijn ze dan exact het referentiegetal (wat het geval zou moeten zijn), dan wordt het rest getal berekend. Dit onderdeel in de formule is daarmee een dubbelcheck.

Andere voorbeelden AFONDEN.NAAR.BOVEN formule

Afronden naar boven kwam ik in deze formule voor het eerst tegen en ik kan eerlijk gezegd geen voorbeelden bedenken waarin je deze formule nodig zou hebben.

Sterrenrating formule uitgelegd

De sterrenrating formule is op 3 manieren uitgelegd. Ik hoop dat je er iets aan had. Wellicht ga je de REST of AFRONDEN.NAAR.BOVEN formules nog eens gebruiken.

Ben of ken je een beginner die meer wil leren over spreadsheets, formules en dashboards maken? Schrijf je in voor de nieuwsbrief; je weet dan als eerste welke dashboards we gaan bouwen, krijgt oefenmogelijkheden, downloads en je kunt zelf input geven over welke dashboards je graag zou willen maken.

Een mooie sterrenrating maken, het kan in Excel. Je kunt een sterrenrating maken wanneer je de gemiddelden van een enquête wilt laten zien of de score wilt laten zien van een deelnemer op drie verschillende onderdelen. In deze blog wordt uitgelegd hoe je een sterrenrating doet aan de hand van 2 meningen over de Oscar nominaties.

De sterrenrating is hier een 10-sterrenrating omdat de scores van de bronnen (IMDb en Rotten Tomatoes) op een 10 puntsschaal gaan. In het voorbeeld heb ik het gemiddelde van deze twee cijfers genomen.

De sterrenrating maken we in vijf stappen, eerst maken we de basis voor de 10 sterren met behulp van een formule. Vervolgens gebruiken we conditionele opmaak om deze 10 getallen aan te passen naar sterren.

In 2 minuten een sterrenrating in Excel

Deze video duurt twee minuten. In werkelijkheid maakte ik de sterrenrating in 3 minuten. De opmaak aan het eind is versneld weergegeven. Misschien wat te snel.

De basis voor de sterrenrating

In 5 stappen maak je de basis voor de sterrenrating in Excel.

1. Typ bovenin de nummers 1 tot 10 bovenin de kolommen als referentiecijfer. Deze hebben we straks nodig voor de formule. Je kunt deze getallen later wegwerken door ze dezelfde kleur te geven als de achtergrond.

2. Kopieer vervolgens onderstaande formule in cel C3. Deze formule is geleend/gejat van deze blog.

In de video heb ik niet gelijk door dat de komma’s in de formule vervangen moet worden. De onderstaande formule kan je direct kopiëren, daarin staan geen komma’s meer. Wil je snappen hoe deze formule werkt, lees dan volgende week de blog met uitleg (ga ik nog even puzzelen).

=IF(C$1<=$B3;1;IF(ROUNDUP($B3;0)=C$1;MOD($B3;1);0))

3. Sleep de formule naar beneden en vervolgens naar rechts met behulp van het plus icoontje rechts onderin de cel. Door het $ teken is de formule goed te kopieren naar de andere cellen. Je hebt dan dit resultaat:

Conditionele opmaak voor een sterrenrating

Om de sterrenrating daadwerkelijk in sterren te krijgen, gebruik je conditionele opmaak. Daarvoor neem je de volgende stappen:

4. Selecteer de cellen waarvoor je de opmaak willen maken en kies voor Conditionele opmaak > New Rule. Kies ‘Icon Sets’ als Style.

• 

• 

5. Kies vervolgens voor:

• De sterren als Icons
• Vink ‘Show Icon only’ aan
• Hele ster wanneer >= 1, nummer
• Halve ster wanneer >= 0,5, nummer

Wanneer je op OK klikt, zijn de getallen in de cellen veranderd naar hele, halve en lege sterren.

Door de tekst, randen en cellen verder op te maken, maak je de sterrenrating helemaal naar je zin en kan het een onderdeel worden van een (interactief) dashboard.

Zelf maken een sterrenrating in Excel maken

Ik ben benieuwd naar jouw vier, vijf of tien sterrenrating! Deel ‘m! Wil je de sterrenrating in Excel niet zelf maken? Stuur me een mailtje en dan maak ik een template.

Door het gebruik van het dollarteken ($) in je formule, kun je een deel van de formule vastzetten, dit heet een absolute verwijzing. Het dollarteken werkt zowel in Excel als in Google Sheets.

Doel: minder berekeningen

Voor een fotoboek maakte ik dit reisoverzicht. Je ziet in een oogopslag wanneer we in welk lang waren. Voor zo’n reisoverzicht zoals hieronder, heb je onder andere het resterende aantal dagen nodig voor elk land. Daarmee wordt het derde, rechterdeel van elke staaf in het diagram berekend. Namelijk het aantal dagen dat er nog over is na een specifiek land tot het einde van de gehele reis.

Hoe zet ik een formule vast?

Om het resterende aantal dagen te berekenen, nemen we de einddatum van de hele reis (23 augustus in cel C13) en halen daar per land, de einddatum van dat land af. De einddatum van de vakantie is voor elke formule hetzelfde en kun je dus ‘vast zetten’. 

Een voorbeeld

Na 29 juni, de laatste dag in Italië, waren er tot de laatste dag van de reis (23 augustus) nog 55 dagen over. Dat kan een spreadsheet makkelijk voor je berekenen door 29 juni van 23 augustus af te trekken. Dat is dan 23 augustus – 29 juni en in de formule C13-C8.  Voor Slovenie geldt 23 augustus – 13 juli: C13-C9. 

C13 kun je vast zetten voor elke formule in die kolom; C13 is dan de absolute waarde. 

Door een dollarteken te zetten voor zowel kolom C als rij 13 ($C$13), zet je de hele cel vast. Als je de formule nu doortrekt naar de rijen eronder óf de kolommen ernaast; er wordt altijd gerekend met de einddatum van de reis. 

Wil je een eigen dashboard maken in Excel of Google spreadsheets van je een Facebook pagina, dan moet je eerst de data via je FB pagina op post niveau downloaden. Met deze data kun je bijvoorbeeld:

• per maand het bereik van je posts weergeven in een grafiek of tabel
• bekijken welke soort posts (foto, link, video) zorgen voor een meer betrokken gebruikers
• nagaan welke berichten negatieve feedback opleveren

De data van je Facebook posts downloaden

Ga naar het statistieken menu van je Facebook pagina om de data te downloaden. Klik vervolgens rechts bovenin op ‘Gegevens exporteren’.

Er verschijnt het pop-up menu ‘Statistische gegevens exporteren’, kies hier voor ‘Bericht gegevens’ en pas het datum bereik aan zodat je de data kunt downloaden van de tijd die je wilt. Je kunt alleen data van maximaal 3 aaneengesloten maanden downloaden. Heb je de gewenste begin- en einddatum? Klik dan op gegevens exporteren.

Het bestand dat je krijgt heeft verschillende tabbladen. Voor het dashboard dat we gaan maken, hebben we alleen het eerste tabblad ‘Key Metrics’ nodig.