Een ranking is een goede basis voor je dashboard. Nu het NK Tegelwippen bijna is afgelopen, een shout out voor dit dashboard waarin je gemeenten kunt vergelijken op hoeveel tegels er zijn gewipt. In de ranking.
Hoe je zelf een ranking maakt (en verder uitbreidt met een interactieve staafdiagram in verschillende kleuren) staat in dit blog uitgelegd. Wat uit het dashboard inspireert voor andere dashboards en wat kan ik hiervan leren? Dat staat hieronder:
Wat ga ik jatten?
Het informatie vraagteken bij de kolomnamen. Je zult makers de kost moeten geven die dit soort dingen vergeten uit te leggen. Pluspunten dat de makers TPI (Tegels Per 1.000 Inwoners) hebben uitgelegd.
Een zoekfunctie. Hoewel ‘slechts’ 81 van de 352 gemeenten in deze lijst staan; volgend jaar doen natuurlijk een stuk meer gemeenten mee. Dat je niet door een lange lijst hoeft te zoeken of de sneltoets voor zoeken hoeft te bedenken is winst.
De derby’s zijn goed bedacht. Gemeenten die niet meer kunnen winnen of een plek in de top 10 kunnen krijgen, kunnen de strijd op deze manier nog tegen een vergelijkbare gemeente opnemen en zo toch ‘winnen’. Voor de gebruiker van het dashboard is het fijn dat vergelijkbare gemeenten naast elkaar worden gezet en je dit niet zelf in de ranking hoeft uit te zoeken.
Wat kan beter?
De kaart laat allemaal hartjes zien voor de gemeenten die meedoen. Er is geen verschil in grootte van de hartjes op de kaart. Hierdoor kun je de gemeenten niet vergelijken. Logischer zou zijn geweest dat de nummer 1 gemeente (Rucphen) een groter hart zou hebben dat een gemeente die lager op de ranking staat.
Nog logischer zou een nummer 1 zijn geweest, maar nog leuker zou een stapel tegels zijn geweest; hoe hoger de stapel, hoe hoger de score van de gemeente.
De derby’s nodigden uit om naar meer gemeentes te zoeken. Een mooie toevoeging zou zijn wanneer je zelf twee gemeente kunt vergelijken. Ik had mijn woonplaats graag op een makkelijke manier vergeleken met mijn geboorteplaats.
Winnaar NK Tegelwippen?
Dat gemeenten ermee bezig zijn zag ik duidelijk in Arnhem. Ik vond in een straat deze vlaggetjes. Mooi gedaan, maar ze gaan Rucphen waarschijnlijk niet meer van hun troon stoten.
Heb je ook een dashboard wat je inspireert en waar ik eens naar zou moeten kijken? Of heb je aanvullingen over goede of verbeterpunten van dit dashboard? Laat het achter in de opmerkingen.
Dit real-life paars-roze dashboard kwam ik tegen op het Tweetakt festival. Het is gemaakt voor de voorstelling Dashboard; een emotionele filmische minitrip over eenzaamheid en het sociaal isolement in een wereldstad. Het dashboard staat voor de taxi waarin deze minitrip plaatsvindt.
Dashboard is tot zondag 19 september te zien ervaren.
Dashboard of geen dashboard?
Dit is een dashboard en wel het zogenoemde midden-dashboard met de bediening van de audio en temperatuurregeling. Als boegbeeld voor de voorstelling een prima dashboard, maar het heeft weinig overeenkomsten met een online dashboard zo zonder doelen, resultaten, grafieken en tijdslijnen.
Of toch? Het blijft interessant om te kijken wat er van dit dashboard te leren is.
Hieronder 3 dingen die ik weer eens bevestigd kreeg in het maken van een dashboard.
Het gebruik wordt aantrekkelijk gemaakt. Het dashboard was van een zachte stof gemaakt en nodigt meteen uit om aan te zitten. Daarbij is het kleurgebruik bijzonder en opvallend.
De gebruiksvriendelijkheid is hoog. De knop die ingedrukt moet worden om de ‘radio’ aan te krijgen, wordt meteen gevonden door een minderjarige.
Het dashboard is overzichtelijk. Er is ruimte tussen de verschillende onderdelen én het dashboard is grotendeels symmetrisch. Dat laatste doe ik bijna nooit in het maken van online dashboards, maar dat ga ik wellicht eens proberen.
Puntjes van kritiek zijn er ook, al is dat flauw. Maar hier gaat die dan toch. Een dashboard zou zo overduidelijk moeten zijn; dat het niet de titel ‘dashboard’ hoeft te hebben. Maar ja, flauw dus.
Een collega wees me een tijd geleden op ‘het meest deprimerende dashboard ooit’.
Meest deprimerende dashboard van de wereld
Het Bloomberg klimaat dashboard heeft een nogal een deprimerende boodschap. De noodsituatie waarin de aarde, het klimaat en de natuur verkeerd, wordt nog eens benadrukt door het kleurgebruik. Of eigenlijk juist het gebrek hieraan; er is alleen maar zwart.
Meest positieve dashboard van de wereld
Als tegenhanger zocht ik naar een dashboard met een meer positieve inslag. Ik vond niet veel meer dan covid dashboards over het aantal positief geteste mensen.
Gelukkig is er Hans Rosling, die ook na zijn dood blijft inspireren met data en dashboards. Met de gegevens uit gapminder.org kun je zien dat de wereld een mooiere plek aan het worden is.
We worden als wereldbevolking steeds ouder, er sterven minder kinderen, we krijgen gemiddeld minder kinderen, het inkomen stijgt en de verdeling van dit inkomen is gelijker verdeeld. Er leven daarnaast meer mensen in democratieën en het aantal conflicten in de wereld daalt. In dit artikel van het Word Economic Forum kun je de details hierover vinden.
Wereldwijde inkomensdistributie uit 1800, 1975 en 2015
De wereld wordt beter voor haar bevolking
Zijn gegevens én vooral zijn populaire TED talk laat daarnaast ook zien dat de tweedeling over de wereld niet klopt. De tweedeling “Rijke, westerse landen met kleine families en lang leven” vs. “Arme landen met grote families en korte levens” klopt niet meer.
Deze analyse van de wereld vind je vanaf 2 minuut 7, maar ik kan je aanraden om gewoon vanaf het begin te kijken; Hans Rosling maakte data interessant. Vanaf 5:50 gaat het over de inkomensspreiding en dat gelukkig steeds minder mensen van 1 euro moeten rondkomen.
Wil je jezelf testen over je wereldbeeld? Doe dan de test op Gapminder en test of je slimmer bent dat een chimpansee (of andere wereldbewoners).
Op het moment kijk ik, waarschijnlijk als een van de laatsten in dit land, Game of Thrones. Bij het kijken van seizoen 1 en 2 heb ik dankbaar gebruikt gemaakt van de samenvattingen, stambomen en andere online overzichten om een beetje begrip te krijgen van de familieverhoudingen en gebeurtenissen in de serie. Het aantal dashboards wat door fans over deze serie is gemaakt is ook om van te smullen; hieronder vijf dashboards over Game of Thrones die ik tegenkwam.
1. GoT dashboard – veldslagen en schermtijd
In dit Game of Trones dashboard geeft een overzicht van de veldslagen en de karakters tot en met seizoen 6 in Tableau.
Op de kaart van Westeros kun je locaties van veldslagen selecteren, vervolgens krijg je informatie over de twee strijdende partijen. Vooral de verhouding van de twee strijdende legers is mooi weergegeven in een halve donut. Ik mis overzichtelijke informatie wanneer de strijd heeft plaats gevonden.
Eronder wordt de schermtijd van elk karakter overzichtelijk weergegeven in cirkels; hoe groter de cirkel, hoe meer schermtijd voor dit karakter. Een klik op de cirkel geeft je meer informatie over het karakter. Helaas staan de cirkels niet op een logische volgorde; karakters die bij hetzelfde huis staan vreemd genoeg niet bij elkaar in de buurt.
2. GoT dashboard – doden per karakter
Een originele invalshoek: het dodendashboard met het aantal doden dat de verschillende karakters op hun naam hebben staan. Het dashboard is gemaakt in Tableau en David Murphy van Datasaurus-rex heeft alle doden zelf lopen bijhouden. Wat een werk! Het levert gedetailleerde gegevens op. Wanneer je rechtsboven over 1 van de rode cirkels gaat, krijg je in het pop-up-scherm informatie over de dode te zien. Elke dode in de serie heeft van Murphy een uniek nummer gekregen.
Het overzicht oogt wat rommelig, deels door de gekozen kleuren, maar ook omdat sommige karakters 10x zoveel doden op hun naam kunnen zetten dan anderen. Dat maakt het lastig om een goede schaal voor de grafieken te kiezen.
De episode breakdown rechts onderin is zoals een tabel of grafiek moet zijn. Het bevat veel informatie, maar is toch helder. Je ziet in 1 oogopslag dat in elk seizoen de laatste en voorlaatste aflevering de meeste doden te betreuren zijn (of je verheugd je erop, dat kan ook) en dat het aantal afleveringen zonder doden op 1 hand te tellen is.
Dankzij TrumpExcel staat er ook een Excel dashboard in deze lijst. Het geeft een overzicht van de karakters en afleveringen tot en met seizoen 4. In de blog vind je een video-uitleg (van een uur!) hoe het dashboard is gemaakt.
Het is niet het mooiste dashboard, maar geeft als een van de weinige dashboards informatie over de waardering van de afleveringen (IMDb) en hoeveel er in de US naar elke aflevering werd gekeken.
Door op een karakter klikken in de tabel links onderin krijg je een foto, de status en achtergrond informatie van het karakter te zien. Je vindt ook informatie over hoe ze zijn doodgegaan (wel een spoiler als je nog niet bij die aflevering bent aangekomen). Dat je op zo’n manier foto’s en informatie kan laten zien in Excel wist ik niet.
4. GoT dashboard – voorspellingen
Dat een dashboard ook teveel informatie kan bevatten, bewijst het complete analyse dashboard van Annabelle Rincon. Het bestaat uit 4 (!) verschillende dashboards van de eerste 6 seizoenen, waarin Rincon van alles analyseert (inclusief Twitter stemmen) om te voorspellen wie de volgende koning(in) zal worden.
Uiteindelijk komt er geen eenduidig antwoord op die vraag.
5. GoT dashboard – schermtijd
In dit dashboard van Adam E. McCann wordt de schermtijd het mooiste vorm gegeven voor de eerste 5 seizoenen. Het is een tijdslijn die dikker wordt als er meer schermtijd is en natuurlijk stop als je doodgaat. Fijn is ook dat de karakters per huis zijn gegroepeerd.
Wat kan je leren van deze dashboards?
Een dashboard MOET visueel aantrekkelijk zijn. Hoeveel bewondering ik ook heb voor het werk van TrumpExcel, en hoeveel ik ook geloof dat Excel een belangrijk software programma in je werk kan zijn; de andere dashboards zien er toch beter uit.
Hou het simpel. Heel simpel. In bijna alle dashboards staat een overzicht van schermtijd, maar het komt alleen goed uit de verf in het dashboard dat alleen daar over gaat.
Wees duidelijk in wat je wil vertellen. Een dashboard over de doden? Morbide maar duidelijk. Een dashboard over veldslagen en schermtijd? Mogelijk. Een dashboard over schermtijd, legergrootte, overlevingskans, de match met het boek en een twitterpoll? Nee.
En… je kunt in Excel ook foto’s of afbeeldingen laten zien, afhankelijk van een keuze die een gebruiker maakt. Wanneer het handig zal zijn, weet ik nog niet.
Tijdslijn schermtijd & netwerkanalyse
Met uitzondering van het dodendashboard geeft elk dashboard hierboven de schermtijd van de karakters. Dit is dan vaak een overzicht tot en met een bepaald seizoen; het liefste zie ik dit van de verschillende afleveringen en door ‘de tijd’ heen. Natuurlijk was er iemand die dit al had gedaan, via Reddit vond ik een interactieve tijdslijn van de de schermtijd van de karakters per aflevering.
Als kijker wil ik ook graag weten hoe in al die seizoenen de verhoudingen tussen de verschillende karakters liggen bijvoorbeeld door een netwerk analyse. En gelukkig is daar Network of Thrones (afbeelding rechts): ik kon ‘m lol op.
Naar aanleiding van No Spend November maakte ik dit budget dashboard. Wil je zelf ook overzicht in je uitgaven en je budget vergelijken met je uitgaven? Gebruik dan dit overzicht als inspiratie om zelf je budget dashboard te maken.
No Spend November
No Spend November is een goede manier om te kijken hoeveel geld je uitgeeft en waar je kunt besparen. 2 jaar geleden deed ik eerder mee en dit jaar wilde ik 1) geen extra uitgaven doen naast de vaste lasten (Sinterklaas cadeau’s en geplande verjaardagscadeau’s mochten wel); 2) minder geld aan boodschappen uitgeven en 3) een beter overzicht krijgen in mijn uitgaven.
De resultaten:
Ik gaf meer geld uit dan gepland omdat ik voor werk iets wilde uitprinten; tóch naar kantoor ben gegaan (in de planning stond dat ik nog zou blijven thuiswerken); mijn studieschuld afloste en een cadeau voorschoot. Geen extra geld uitgeven naast vaste lasten is dus niet gelukt.
De boodschappen doen we van de gezamenlijke rekening en helaas; we zijn juist meer geld aan boodschappen gaan uitgeven deze maand. Vast de chocolade kruidnoten. Niet gelukt.
Wat wel goed ging? Ik heb maandelijkse donatie en mijn tweede telefoonnummer (die ik al maanden niet meer gebruikte!) opgezegd en bovendien mijn studieschuld afbetaald. Dat gaat me bijna 100 euro in de maand schelen.
Voor het dashboard zul je eerst de gegevens van je bankrekening moeten downloaden en vervolgens moeten categoriseren. Zo kun je per maand de categorieën in een tabel zetten en je geplande budget, het resultaat en het verschil naast elkaar zetten.
De uitgaven en inkomsten tabel
Het geplande budget kun je zelf per maand bepalen en invullen. Met een SOMMEN.ALS formule, bereken je hoeveel je in een maand daadwerkelijk hebt uitgegeven aan die bepaalde categorie. In het algemeen is de formule:
In deze tabel gaat het dan om de volgende gegevens: OptelBereik: de uitgegeven euro’s (in kolom G, in tabblad Data) BereikCriteria1: de kolom waarin de categorie staat (in kolom J, in tabblad Data) Criteria1: de categorie (in hetzelfde blad, in cel B27) BereikCriteria2: de kolom waarin het maandnummer staat (in kolom L, in tabblad Data) Criteria2: de maand (voor maand november; 11)
De categorie voor criteria1 kun je zelf in de formule typen, of je kunt in de formule verwijzen naar de cel waar de betreffende categorie staat. Het laatste heeft de voorkeur, zeker als je de formule ook voor andere categorieën maakt. De formule kun je dan makkelijk doortrekken en hoef je deze niet steeds opnieuw te maken.
=-SOMMEN.ALS(Data!G:G;Data!J:J;B72;Data!L:L;11)
Voor de formule staat een min (-), in de gegevens die ik via mijn bank downloadde, staan uitgaven (logischerwijs) met een negatief getal aangeduid. Sommige banken hebben een extra kolom met ‘bij’ of ‘af’. Dan hoef je het negatieve getal niet meer positief te maken.
Maandoverzicht en tabel
Links in het maandoverzicht staat de naam van de maand en het maandresultaat (bijvoorbeeld in cel C59). Het maandresultaat is niets anders dan de uitgaven van de inkomsten af te halen. Als er geld overblijft wil ik dat er staat, “Deze maand bespaard” en als er meer geld uitgaat dan binnenkomt, dan wil ik dat er staat “Deze maand uitgegeven”. Dat doe je met de ALS formule:
De grafiek over geplande vs. gerealiseerde inkomsten en uitgaven maak je met een kolomdiagram. Standaard krijg je 4 kolommen, maar die kun je over elkaar schuiven zodat je ze beter kunt vergelijken en je dashboard er ook nog eens beter uitziet. Je kunt dit als volgt doen:
1. De kolommen met de geplande uitgaven geef een donkere lijn en geen vulling;
2. De kolommen met het resultaat geef je een transparante lijn en een lichte vulling;
3. Kies voor 100% serie overlap.
Budget dashboard nog verder aanpassen?
In dit geval heb ik voor de andere maanden het gehele overzicht gekopieerd en in hetzelfde tabblad gepakt. Dan is het een kwestie van de maand aanpassen in het overzicht en de formules aanpassen. Nog niet het ideale interactieve dashboard wat ik voor ogen had, maar het begin is er!
De World Inequality Database website geeft een mooie overzichtsgrafiek wanneer je de inkomens distributie simulator invult. In Excel kun je dit staafdiagram met verschillende kleuren voor de balken ook maken.
Excel staafdiagram met verschillende kleuren balken
De site van de World Inequality Database is voor liefhebbers van data, infographics en interactieve dashboarden een speeltuin. Om de vraag ‘hoe rijk ben ik eigenlijk?’ te beantwoorden, kun je de inkomens distributie simulator doen. Op basis van je ingevulde gegevens krijg je een overzicht in welke inkomensgroep je je bevindt.
In Excel kun je de balken van zo’n staafdiagram verschillende kleuren geven en in deze blog leg ik uit hoe je dat doet. De uitleg is een aangepaste versie van de blog over hoe je een specifiek punt uitlicht in een staafdiagram. In het dashboard Internationale Vrouwendag stond een staafdiagram waarbij 1 land duidelijk wordt uitgelicht.
De bovenstaande grafiek Vrouwen in Management en de gegevens hiervan gebruiken we in deze blog als basis om de balken in dit staafdiagram verschillende kleuren te geven.
3 verschillende balken, 3 verschillende kleuren, 3 verschillende kolommen
Van 172 landen hebben we het percentage vrouwen in managementposities. Welk land je ook kiest; er zijn drie kleuren balken mogelijk: 1. De uitgelichte balk: het land van jouw keuze (oranje) 2. De balken met een kleinere waardes: de landen die lager scoren dan je keuze (grijs) 3. De balken met een grotere waardes: de landen die hoger scoren dan je keuze (geel)
We kunnen straks drie verschillende kleuren balken maken in het Excel staafdiagram door de gegevens in 3 verschillende kolommen te zetten. In welke kolom de gegevens staan, bepaalt de kleur van die gegevens in de grafiek. Wanneer de gebruiker van je dashboard een land kiest, veranderen op basis daarvan de gegevens in de kolommen.
Kiest de gebruiker voor land ‘Nederland’ dan komen de gegevens van dat land in de highlight kolom te staan en krijgt deze de oranje kleur in de grafiek. Kiest een gebruiker het land ‘Jamaica’, dan komen de gegevens van dit land in de highlight kolom te staan en worden deze gegevens in de grafiek oranje. Dit geldt ook voor de kleinere en grotere waardes van je keuze.
De uitgelichte balk maken
De uitgelichte balk is afhankelijk van de landkeuze. Kiest iemand voor Jamaica, dan worden de managementgegevens van Jamaica uitgelicht in oranje, kiest iemand voor Nederland, dan worden de Nederlandse managementgegevens oranje.
Daarvoor maken we allereerst een dropdown-lijst via Data Validation (uit menu Data).
Vervolgens kiezen we ‘Lijst’ en selecteren we de landen in kolom B als de brongegevens voor de lijst. Zo komen deze landen in de dropdown-lijst te staan en kunnen gebruikers alleen kiezen uit deze lijst.
Vervolgens voegen we met een verticaal zoeken (vlookup) formule de gegevens van het gekozen land toe in cel I4. De formule is =VERT.ZOEKEN(H4;B:C;2;FALSE).
In de highlight kolom willen we alleen dat het uitgelichte land (in de afbeelding Jamaica) de managementdata krijgt. De andere landen krijgen de waarde 0 in deze kolom. Dat kan met een ALS formule. Als de waarde in landenlijst (kolom B), gelijk is aan het gekozen land uit het dropdownmeu (cel H4), dan willen we de managementdata zien in kolom D. Anders willen we waarde 0.
De formule hiervoor is =IF(B2=$H$4;C2;0).
In de afbeelding zie je dat alleen bij Jamaica een waarde in kolom D staat. Voor de andere landen staat er door de formule een 0.
De balk maken met de lagere getallen
De balken met de lagere gegevens zijn eveneens afhankelijk van de landkeuze. Kiest iemand voor Jamaica, met 56,7% vrouwen in management, dan willen we van alle landen met een lagere score een grijze balk in de grafiek. Van de landen met een hogere score willen we de gegevens niet te zien; die maken we 0.
De formule is =IF(C2<$I$4;C2;0)
Met de ALS-formule zetten we gegevens in kolom E. Als het percentage vrouwen in management in dat land (kolom C) kleiner is dan de gegevens van het gekozen land (getal in I4), dan willen we die gegevens ook in de ‘lager’ kolom (kolom E) hebben. Zijn het percentage van het land groter, dan willen we in de ‘lager’ kolom (kolom E) een 0 zien.
In de bovenstaande afbeelding zie je dat waarde voor Côte d’Ivoire (56,9%) hoger is dan de waarde van het uitgelichte land Jamaica (56,7%). Deze krijgt een 0. De waarde voor Indonesia is wél lager (55%) dan dat van Jamaica. Daarom willen we die waarde wel zien in kolom E.
De balk maken met de hogere getallen
De berekening voor de hogere gegevens is vergelijkbaar met wat hierboven is beschreven, maar dan net omgekeerd. In dit geval wil je bij de landkeuze Jamaica alle landen met een hoger score geel maken. De landen met een lagere score krijgen een 0 in deze kolom.
Met deze ALS-formule =IF(C2>$I$4;C2;0) zetten we gegevens in de ‘hoger’ kolom (kolom F). Als de gegevens uit kolom C groter zijn dan het getal in I4 (de managementgegevens van het gekozen land) dan willen we de gegevens uit kolom C in de ‘hoger’ kolom hebben, anders willen we een 0.
Je ziet dat er voor Côte d’Ivoire (56,9%) in deze ‘hoger’ kolom wel een waarde is omdat deze hoger is dan de waarde van het uitgelichte land Jamaica (56,7%). Nu krijgt Indonesia, omdat deze lager is, een 0. Overigens krijgt Jamaica in de ‘lager’ en ‘hoger’ kolom een 0 omdat deze al in de highlight kolom staat.
In de bovenstaande afbeelding zie je dat waarde voor Côte d’Ivoire (56,9%) hoger is dan de waarde van het uitgelichte land Jamaica (56,7%). Deze krijgt een 0. De waarde voor Indonesia is wél lager (55%) dan dat van Jamaica. Daarom willen we die waarde wel zien in kolom E.
Nu de gegevens klaar zijn, kunnen we in Excel het staafdiagram met verschillende kleuren balken gaan maken.
Het staafdiagram met verschillende kleuren balken maken
Selecteer de gegevens (de ranking is niet nodig) en voeg een gestapelde kolomgrafiek toe. Alle gegevens uit de kolommen totaal management, highlight, lager en hoger worden bij elkaar opgeteld per land. Je krijgt dan het onderstaande resultaat.
Om tot het juiste (en mooiere) staafdiagram te komen, zet je de volgende stappen:
1. Haal de gegevens van totaal management weg. In de bovenstaande afbeelding zijn deze blauw. Je kunt deze gegevens weghalen door in de grafiek op deze gegevens te klikken (zorg dat je de hele serie selecteert) en te verwijderen. Gewoon delete.
2. Maak een dynamische grafiektitel door te verwijzen naar cel I4. Selecteer hiervoor de grafiek titel en ga naar de formule balk. Vul in de formulebalk =I4 in.
3. Maak de grafiek op zoals jij ‘m mooi vindt. Ik heb de legenda, landen op de x-as en rasterlijnen weggehaald. De y-as heb ik aangepast (zonder cijfers achter de komma en tot 60%) en de highlight heb ik dikker gemaakt door ook de randen dezelfde kleur te geven.
In dit dashboard kun je je voordeel berekenen bij een extra aflossing van je hypotheek of een aanpassing in je rente. Dit bestand is gemaakt in Google Sheet, maar de berekeningen werken ook in Excel. Zelf heb ik (hebben we) een hypotheek met drie annuïteit-delen en met dit bestand kan ik vergelijken bij welk hypotheek deel een aflossing het meeste effect heeft en of een nieuwe hypotheek afsluiten (met lagere) rente, ondanks een boete tóch interessant kan zijn.
Lineair en annuïteit hypotheek
In het gratis te downloaden bestand vind je een dashboard voor een lineaire en annuïteit hypotheek. Ik moest het weer even uitzoeken, maar bij een lineaire hypotheek los je maandelijks een bedrag van je hypotheek af en je betaalt rente over het resterende hypotheek bedrag. Omdat het hypotheekbedrag elke maand steeds lager wordt, betaal je steeds minder rente.
Bij een annuïteitenhypotheek betaal je elke maand hetzelfde bedrag. In het begin bestaat dat bedrag voor een groter deel uit rente, aan het einde van de looptijd bestaat dat vaste bedrag uit een groter deel aflossing.
Voor beide soorten hypotheken maakte ik een overzicht zodat je met de gegevens van je eigen hypotheek en een aflossing in een bepaalde maand, kunt zien wat het voordeel kan zijn van een extra aflossing van je hypotheek.
Dashboard voordeel aflossing
Het dashboard bestaat uit 3 delen
De basisgegevens van een hypotheek iemand zelf kan invullen.
Een vergelijking tussen de hypotheek met en zonder extra aflossing.
De tabel met de berekeningen per maand. Hier kan iemand de extra aflossing in de juiste maand invullen.
In het voorbeeld hieronder zie je dat voor deze lening een extra aflossing van 1.000 euro in de vijfde maand een besparing van bijna 300 euro in rente oplevert en je 4 euro aan maandlasten scheelt.
Tabel met maandelijkse gegevens hypotheek
Voor het bouwen van een dashboard begin je met de maandelijkse tabel.
Maandelijkse gegevens zonder extra aflossing
Begin met de looptijd (in maanden) van de hypotheek. Deze haal je uit de gegevens bovenin het dashboard (cel M7). Vervolgens haal je in elke rij, 1 maand van de voorgaande rij af. Deze formule ‘trek’ je met het plus-teken (rechts onderin de cel) naar beneden.
De hoogte van de hypotheek komt in de kolom ernaast. Voor de eerste maand is dit de totale hoogte van de hypotheek (ingevuld in C7). De hoogte van de daaropvolgende maanden bereken je in stap 8.
In lineair bereken je het bedrag dat je per maand moet aflossen. Dat is het rente bedrag (E25) plus de aflossing (F25).
De maandelijkse rente bereken je door de hoogte van de resterende hypotheek (C25) te vermenigvuldigen met de jaarlijkse rente gedeeld door 12 ($H$7/12). Met de $ tekens zet je verwijzing naar de jaarlijkse rente vast in de formule.
Voor de aflossing van een lineaire hypotheek deel je het totale hypotheekbedrag (C7) door het aantal maanden dat je hebt om het bedrag af te lossen (M7). De formule wordt dan C$7/M$7. Zet de dollar-tekens erbij, zodat deze formule in de rijen eronder goed wordt gekopieerd.
De hypotheek na aflossing is de hoogte hypotheek (C25) – de aflossing (F25).
In kolom H kun je nog een schatting van het hypotheekrente aftrek maken. In dit voorbeeld staat dit op 30%. De formule is 0,3*E25. Dit is een grove schatting, want de aftrek is onder andere afhankelijk van de hoogte van de hypotheek en je inkomen.
Vervolgens is de hoogte van de hypotheek in de volgende maand, in de nieuwe rij in cel C26 gelijk aan de hypotheek na aflossing.
Je heb nu de volgende gegevens. De formules kun je doortrekken naar de rijen eronder.
Maandelijkse gegevens met extra aflossing
Wanneer je rente verandert of je een extra aflossing doet, kun je deze in kolom I en J toevoegen. Dit verandert de hoogte van de hypotheek. De effecten bereken je in de kolommen K tot en met P.
De hoogte van de hypotheek komt in de kolom ernaast. Voor de eerste maand is dit de totale hoogte van de hypotheek (ingevuld in C7). De hoogte van de daaropvolgende maanden haal je uit kolom O.
In lineair aangepast bereken je het aflossingsbedrag met M25+N25.
De rente aangepast bereken je door de hoogte van de resterende hypotheek (K25) te vermenigvuldigen met rente uit díe maand (I25/12). Je gebruikt nu geen $ teken omdat je wilt dat de gegevens veranderen als je voor een andere maand kiest.
Voor de aflossing aangepast deel je het hypotheekbedrag van die maand (K25) door het aantal maanden dat je nog hebt om het bedrag af te lossen (B25).
De hypotheek aangepast is de hoogte hypotheek (K25) – de aflossing (N25) – de extra aflossing (J25).
De hypotheekrente aftrek is 0,3*M25.
Vervolgens is de hoogte van de hypotheek in de volgende maand (K26) gelijk aan hypotheek aangepast (O25).
Voor de overzichtelijkheid en voor de grafiek in het overzicht, voeg je het jaar aan het einde van de tabel toe.
Kopieer de formules naar de volgende rijen en kijk of alles klopt door een aflossing toe te voegen. Zo moeten de hypotheeklasten bij lineair aangepast in kolom L bij een extra aflossing lager zijn dan lineair uit kolom D. In het voorbeeld verlaagt een extra aflossing van 1000 Euro in de 5e maand de maandelijkse aflossing met 2,80 per maand en bespaar je bijna 300euro aan rente.
Maandelijkse gegevens voor annuïteiten hypotheek
In een ander tabblad staan het dashboard voor een annuïteiten hypotheek. Deze kun je maken door het tabblad van de lineaire aflossing in z’n geheel te kopiëren. Doe dit als je het hele dashboard af hebt, zodat de beide tabbladen goed te vergelijken zijn.
Voor de tabel met maandelijkse gegevens zijn er vier aanpassingen nodig:
De totale van een annuïteitslening (kolom D) bereken je met de formule =BET. Je vult vervolgens in:
De rente (2%/12 als je het per maand wilt weten)
Het aantal termijnen (360 maanden)
De huidige waarde (250.000 de hoogte van de hypotheek)
De toekomstige waarde na de laatste betaling (0 in dit geval)
Er komt een negatief getal uit, omdat je dit moet betalen. Als je met een positief getal wilt rekenen, dan zet je een – teken voor de formule. Voor dit dashboard wordt de formule:
=-BET($H$7/12;$M$7;$C$7;0)
De aflossing van een annuïteitslening bereken je ook anders dan de aflossing bij een lineaire lening. Je weet wat je per maand moet betalen (kolom D) én je weet wat de rente is (kolom E). Door de rente van de betaling af te halen, weet je hoeveel je daadwerkelijk aflost per maand.
De totale annuïteit aangepast (kolom L) bereken je eveneens met de formule =BET. Omdat je wilt dat eventuele renteveranderingen en extra aflossingen worden berekend vul je in:
De rente (I25/12)
Het aantal termijnen uit B25
De hoogte van de hypotheek uit K24
De toekomstige waarde na de laatste betaling (nog steeds 0)
De aflossing aangepast bereken je door rente aangepast (M25) van annuïteit aangepast (L25) af te trekken. De formule wordt dan L25-M25.
Overzicht voordeel bij extra aflossen hypotheek
Op basis van de tabel en de ingevulde gegevens over de hypotheek, kun je een overzicht maken van de voordelen van een extra aflossing.
Voordeel extra aflossen hypotheek
De berekeningen achter de 6 gegevens zijn als volgt:
1. Het totale rente bedrag over de gehele looptijd bereken je met de formule =SOM.ALS(E25:E384;”>0″). Met deze formule tel je de maandelijkse rente zonder extra aflossing (kolom K) bij elkaar op, maar alleen als deze groter is dan nul.
Bij een lening met een looptijd korter dan 360 maanden, wordt de rente aan het einde van de looptijd negatief. Deze rente wil je niet meetellen. Door alleen de waarden groter dan 0 op te tellen voorkom je dat deze mee worden geteld. Je kunt dit dashboard zo ook voor leningen met een looptijd korter dan 360 maanden gebruiken.
2. Het totale rente bedrag aangepast bereken je met dezelfde SOM.ALS formule, maar dan gebruik je bereik M25:M384.
3. Het verschil in rente bij aanpassingen bereken je door deze van elkaar af te halen. In dit geval is dit =P14-M14
Omdat er ook sprake is van hypotheekrente aftrek, kun je een benadering van de hypotheekrente aftrek toevoegen. In dit voorbeeld staat deze op 30%, maar de hoogte is afhankelijk van je persoonlijke situatie.
4. De benadering van de totale netto lasten zijn dan de totale hypotheek (uit C7) + de rente (het totaal in kolom E, dus de formule uit stap 1 hierboven) – hypotheekrente aftrek (SOM.ALS(H25:H384;”>0″).
De formule wordt dan =C7+SOM.ALS(E25:E384;”>0″)-SOM.ALS(H25:H384;”>0″)
5. Voor de benadering van de totale netto lasten met extra aflossen is de formule vergelijkbaar met die uit stap 4, maar gebruik je andere gegevens. De formule wordt dan =C7+SOM.ALS(K25:K384;”>0″)-SOM.ALS(M25:M384;”>0″)
6. Het verschil totale netto lasten bij aanpassingen bereken je door P20 van M20 af te halen.
Grafiek hypotheek aflossen
Kies voor een combinatie diagram. De kolommen zijn de gegevens zonder aflossing, de lijn zijn de gegevens met een extra aflossing. Op deze manier kun je beide situaties vergelijken.
Voor de grafiek gebruik je jaar uit kolom P voor de x-as. De reeksen zijn annuïteit en annuïteit aangepast & lineair en lineair aangepast, zoals in de afbeeldingen hieronder.
Hoe gebruik je het dashboard?
De Excel versie van dit (oorspronkelijke Google Sheet dashboard die niet goed te downloaden was) kun je als basis voor je eigen situatie gebruiken. Maak een kopie en vul je eigen gegevens in en bereken wat het rentevoordeel is van eerder aflossen of een nieuwe rente afsluiten. Er kan een boeterente zijn voor een nieuwe renteafsluiting, maar als de besparing groter is, dan kan het toch interessant zijn.
Zelf hebben wij een annuïteitenhypotheek in drie delen. Ik heb van elk van de delen een tabblad gemaakt. De delen hebben elke een andere hoogte van de hypotheek en ander rentepercentage. Zo kan ik vergelijken bij welk deel we het meeste voordeel heb bij een extra aflossing en berekenen wat de voordelen zijn van een lagere rente. Maar bovenal; ik heb sinds tijden weer het gevoel wat grip te hebben op de (toekomstige) uitgaven.
In korte tijd maakte ik met behulp van een Facebook export en 2 formules een Facebook dashboard in Excel.
Hoe maak je snel een dashboard?
De groei in volgers meet je op een zelfgekozen start- en eind moment en zet je in een lijngrafiek. Hou ook het aantal volgers van je collega/concurrenten bij om de groei te kunnen vergelijken.
Met conditionele opmaak kun je de verschillende scores in 1 oogopslag vergelijken.
Waarom een dashboard?
Sinds oktober 2019 zet ik regelmatig berichten op Facebook en zorg ik dat er blogs zijn voor Hoeve Ackerdijk; het biologische melkveehouderijbedrijf van mijn ouders en mijn broer.
Doel is om te groeien volgers op de FB pagina, zodat zoveel mogelijk mensen weten dat Hoeve Ackerdijk rondleidingen geeft en biologische producten verkoopt. Daarvoor zijn de doelen:
Sterkere in volgers van de Facebook pagina in vergelijking met andere boerderijen in de regio;
Groei in het bereik van de berichten en
Inzicht krijgen wanneer het beste een bericht geplaatst kan worden voor het meeste bereik.
Een Facebook dashboard in Excel
Aangezien ik de voortgang per kwartaal (of maandelijks) wil bekijken, maakte ik een dashboard. Dan hoef ik alleen data toe te voegen en ik heb een update. Ik leerde dat:
We in aantal volgers en groei sterk achterblijven bij collega boeren;
Er in eerste instantie een dalende trend (!) was in het bereik van de berichten. Een maand later bekeek ik het opnieuw en toen wat er geen duidelijke daling meer, maar ook geen groei.
De meeste berichten werden in de ochtend geplaatst, maar vooral tussen 16 en 18 is het bereik per bericht het hoogste.
Excelleren kan je leren
Het dashboard bouwen was niet heel ingewikkeld en tóch leerde ik nog wat nieuwe dingen:
Ik leerde nog een manier waarop je met datums en tijden kunt rekenen, net zoals je dat met getallen zou doen. Niet alleen optellen of van elkaar aftrekken, maar je kunt ook tijdstippen tellen. Bijvoorbeeld hoeveel posts in welk uur zijn geplaatst.
Ik zag dat Excel van sommige teksten een datum maakte. 8 – 9 (voor tijdsblok 8:00 tot 9:00) werd bijvoorbeeld 8 september. Je kunt dat voorkomen door er een aanhalingsteken (‘) voor te zetten.
Ik merkte dat een trendlijn best handig is als je door de pieken in je grafiek niet kunt zien of er nu groei of afname is.
Een dashboard is leuk, maar het wordt voor de gebruiker nog interessanter als het dashboard verandert door de keuze van de gebruiker. Hoe maak je nu zo’n interactieve grafiek met een keuzemenu?
In dit blog leg ik je uit hoe je in Google Sheets interactieve grafieken kunt maken aan de hand van het dashboard Internationale vrouwendag. De gebruiker kiest een land en de data in het dashboard verandert mee.
Je begint met een idee over de layout van het dashboard en de data die nodig is. Vervolgens kun je aan de slag met de grafieken.
Data bewerken
Het is handig om de berekeningen en bewerkingen van de data in een apart tabblad te doen. Op deze manier staan alle berekeningen bij elkaar en als er ergens een fout zit, is deze fout makkelijker te vinden.
Maak in het berekeningen tabblad een verwijzing naar het keuze menu in je dashboard. Als in het dashboard voor Nederland wordt gekozen, dan verschijnt Nederland ook in je berekeningen tabblad. Zo maak je minder fouten. In dit dashboard verwijs ik in cel A2 naar het dropdown menu die in cel C3 in het tabblad Dashboard staat.
Interactieve grafiek met totaal gemiddelde vs geselecteerde land
Het ‘world average female’ percentage op de arbeidsmarkt bereken je door het gemiddelde van alle landen te nemen. Die data staat in het tabblad met de naam females_aged_25_54_labour_force. Voor 1990 neem je het gemiddelde uit kolom B, voor 1991 het gemiddelde uit kolom C, etc.
De lege cellen worden in de berekening niet meegenomen. De formule trek door naar rechts met behulp van het plusje wat je krijgt als je rechts onderin de cel staat.
Dit wereldwijde gemiddelde wil je vervolgens vergelijken met het percentage van het gekozen land. Het gemiddelde van een land bereken je met de formule verticaal zoeken.
Bij verticaal zoeken moet je elke keer aangeven uit welke kolom je de data wilt halen (uit de 2e kolom, de 3e of de 26e). Dat betekent dat je elke keer opnieuw de formule zou moeten aanpassen. Dat kan sneller door in rij 1 steeds de getallen van de kolom waarin je wilt zoeken te zetten. En daar in je formule naar te verwijzen.
Zo heb je de data voor de lijngrafiek voor arbeidsparticipatie waarbij je het gemiddelde van alle landen (in rij 3) vergelijkt met het gekozen land (in rij 4).
Interactieve grafiek met 1 land
Voor de ratio meisjes/jongens in het basis en voortgezet onderwijs hoef je alleen de data te laten zien van het gekozen land. In de grafiek wordt dit niet vergeleken met een wereldwijd gemiddelde.
Hier gebruik je ook een vertikaal zoeken formule voor én maak je gebruik van hetzelfde trucje als hierboven, door in de formule te verwijzen naar de getallen in rij 1. Dat scheelt veel typewerk. De formule is als volgt:
Voor Nederland blijft cel B8 leeg. Hierin zou de data voor 1970 moeten staan, maar er is voor dat jaar geen data. Dat deze cel leeg blijft en niet 0 wordt, is prettig, omdat in de grafiek anders ook 0 als gegeven zou komen te staan.
Gemiddelde zonder #N/B
In de grafiek volksvertegenwoordiging van vrouwen (% vrouwen in het parlement, senaat etc), wordt het wereldwijde gemiddelde vergeleken met het percentage van het gekozen land.
In het data bestand hebben landen waar in een bepaald jaar geen score voor is, #N/B staan. Dat zorgt voor een foutmelding als je het gemiddelde wilt berekenen. Je kunt twee dingen doen:
Alle #N/B verwijderen in de data. Je past hier wel je brongegevens mee aan en maakt mogelijk een fout.
De formule voor het gemiddelde aanpassen, zodat #N/B niet wordt gebruikt in de berekening van het gemiddelde.
In plaats van gemiddelde, gebruik je gemiddelde.als. Vervolgens sluit je #N/B uit door er <> voor te zetten. Aangezien het een tekst is en geen getal, moet je dit tussen aanhalingstekens zetten. De formule van het gemiddelde ziet er dan als volgt uit:
=GEMIDDELDE.ALS(‘Percentage of women in chambers’!B2:B194,”<>#N/A”)
Dit gemiddelde wil je vergelijken met de score van het gekozen land. Daarvoor gebruik je een vergelijkbare verticaal zoeken formule:
=VERT.ZOEKEN($A13,’Percentage of women in chambers’!$A2:$BX194,B1,ONWAAR)
Voor 1945 in Nederland is er geen percentage beschikbaar (ik denk dat het land iets anders te doen had dan meten wie er in het parlement zaten) en daarom staat er #N/B. In de grafiek is er voor dit jaartal dan geen getal zichtbaar.
Overige berekeningen
De berekeningen voor vrouwen in management staan in de sheet met de data zelf. Hoe je deze berekeningen maakt voor de staafdiagram waarbij een specifiek land wordt uitgelicht, staat in dit blog beschreven.
De berekeningen voor de stemrecht kun je ook in hetzelfde tabblad doen. Voeg een kolom toe voor het aantal jaren tussen universeel stemrecht en het eerste (mannen) stemrecht door deze van elkaar af te halen. De ranking bereken je door de kolom met universeel stemrecht van laag naar hoog te zetten en deze landen de ranking van 1 tot het einde te geven.
Dashboard maken
Nu de berekeningen af zijn, kan je de grafieken gaan maken. Het dropdown-menu wordt hier uitgelegd.
In dit dashboard heb ik gebruik gemaakt van een vlak grafiek, omdat het wat vulling gaf aan de bovenkant van het dashboard. Wanneer ik de gegevens van het land vergeleek met een wereldwijd gemiddelde, maakte ik gebruik van een lijngrafiek.
Voor de vrouwen in management waren geen historische data beschikbaar en koos ik voor een staafdiagram met de laatste beschikbare data van elk land. De gegevens over stemrecht zetten ik in losse cijfers weer.
Een dag als internationale vrouwendag is een logisch aanknopingspunt voor een dashboard over vrouwen. Het doel van deze dag is om aandacht te geven voor thema’s als economische zelfstandigheid, huiselijk geweld, emancipatie en participatie. Doel van het Dashboard Internationale Vrouwendag is de participatie van vrouwen in het onderwijs en op de arbeidsmarkt van verschillende landen inzichtelijk te maken.
En ok, ik vond het leuk om te doen.
In de linkerbovenhoek kan een gebruiker kiezen voor een land en vervolgens zie je (indien beschikbaar) de ontwikkeling in de ratio meisjes/jongens in het onderwijs, gegevens over vrouwen op de arbeidsmarkt en management, en data over stemrecht en het percentage vrouwen als volksvertegenwoordiger.
In deze blog leg ik uit hoe ik het maken van dit dashboard heb aangepakt. Ik ga niet specifiek in op de formules, anders wordt het een lange tekst, maar je kunt dit later bekijken en lezen op dit blog.
Idee en dataverzameling
Het begon met een idee en een
ruwe schets hoe het dashboard eruit zou moeten zien. Vervolgens zocht ik naar
betrouwbare online data, waarbij ik rekening hield met de mogelijkheid dat ik
bepaalde grafieken niet kon maken omdat ik geen data kon vinden.
Voor de data keek ik vooral naar
internationale bronnen van de VN of gelieerde organisaties. Gapminder en
Wikipedia bieden ook fijne overzichten. De bronnen voor dit dashboard zijn: Gapminder,
Parline, Wikipedia, ILO. De bronnen, naam
van de tabellen en de links, hou ik in het tabblad ‘bronnen’ bij. Hieronder
staan de links naar de gebruikte data, met de bron erachter.
Knippen&Plakken vanuit Excel naar Google Sheets levert soms een verduizendvoudiging op (ja, je leest het goed) van de cijfers. Stond opeens een komma op de verkeerde plaats, maar dan niet bij alle getallen. Om uitzoekwerk te vermijden, kun je beter een Excel bestand importeren naar Google Sheets en deze vervolgens bewerken.
Landen die in verschillende bronnen anders worden geschreven (Côte d’Ivoire zonder een ^) of die niet meer bestaan. Dat had vooral invloed op de keuze voor het dropdown-menu. Je kunt zelf een lijst van landen maken (dat is wat uitzoek werk) of kiezen voor de meest uitgebreide lijst uit een van je bronnen.
Tijdens het zoeken naar data en/of het maken van het dashboard, kwam het niet altijd uit zoals gepland. Een grafiek in het dashboard werd onoverzichtelijk met vier lijnen (oplossing was om twee lijnen niet te gebruiken) of er bestond geen historische data over een bepaald onderwerp. Logisch, niet elk land houdt vanaf 1900 het % vrouwen in management posities bij, hierdoor moest de grafiek wel worden aangepast.
Het resultaat van dit werk is
uiteindelijk een Google Sheet (of Excel bestand) met 6 tabbladen voor elk
origineel databestand, een tabblad met de bronnen. Daar komt nog een tabblad
met berekeningen bij en natuurlijk het tabblad voor het dashboard.
Data bewerken voor grafieken en gegevens in dashboard
In het tabblad ‘berekeningen’ heb ik voor arbeidsparticipatie (labour force), ratio meisjes/jongens en het % vrouwen in de volksvertegenwoordiging de gemiddelden in het originele tabblad berekend.
Vervolgens zet ik de jaren in de
kolommen in het blad berekeningen en voeg ik het gemiddelde toe. Dit wil ik dan
vergelijken met de % van het gekozen land in het dashboard. In de formules
verwijs ik naar het land dat wordt gekozen in het dashboard en zoek ik met verticaal
zoeken naar het bijbehorende getal voor dat jaar.
Om te voorkomen dat ik voor elk jaar een formule moet typen, omdat deze verwijst naar een ander jaar (en een andere kolom in de grafiek), pas ik de formule zo aan dat die verwijst naar de cijfers boven de kolommen (B2 in de afbeelding hieronder). Zo hoef ik de formule maar 1x te maken en kan ik ‘m gewoon doortrekken naar de andere kolommen.
Het tabblad berekeningen hoeft niet mooi te zijn, maar vooral functioneel.
De bewerkingen van de data voor de grafieken over stemrecht en management staan als extra kolom of tabel in het tabblad met de originele data.
Dashboard maken
Met de data en de berekeningen klaar, kan het puzzelen beginnen om alle grafieken netjes in het Dashboard Internationale Vrouwendag te krijgen. In dit dashboard heb ik gebruik gemaakt van een vlak grafiek, omdat het wat vulling gaf aan de bovenkant van het dashboard. Wanneer ik de gegevens van het land vergeleek met een wereldwijd gemiddelde, maakte ik gebruik van een lijngrafiek.
Voor de vrouwen in management waren geen historische data beschikbaar en koos ik in plaats van een lijngrafiek voor een staafdiagram waarbij het gekozen land wordt uitgelicht. De gegevens over stemrecht zetten ik in losse cijfers weer.
