In sommige situaties is het handig om niet alleen het CBS als bron te noemen maar om ook de onderliggende info in je eigen analyse over te nemen en/of te gebruiken.
Er zijn verschillende methoden om die cijfers in Excel te krijgen. Hierna zullen we er enkele bespreken: allereerst het simpelweg kopiëren, daarna het op verschillende manieren downloaden van cijfers.
In een volgend artikel zullen we ook het gebruik van de Open Data-optie bespreken, waarmee het ophalen van cijfers verder gestandaardiseerd en geautomatiseerd kan worden.
Aanleiding
Aanleiding voor dit artikel was een verhaal in de Volkskrant van 3 mei jl. Dat ging over de resultaten van een rapport van het CBS, De arbeidsmarkt in cijfers 2022.
Bij het lezen van het artikel en het rapport kreeg ik de behoefte om op bepaalde onderwerpen wat verder in te zoomen. Daarvoor had ik wel de onderliggende cijfers nodig.
Een samenvatting met de belangrijkste resultaten van het CBS-onderzoek was snel gekopieerd:
selecteer de gewenste gegevens op de website
kies Kopiëren, Ctrl-C
selecteer de cel in Excel waar de linkerbovenhoek moet komen
kies Plakken, Ctrl-V
op de juiste plaatsen een lege regel toevoegen, de kopjes vet maken en de onderliggende items laten inspringen
We gaan nu de gegevens niet kopiëren, maar maken een download. Kies de button
open de download door in de Verkenner in de Download-map op het bestand te dubbelklikken (het bestand heeft in dit geval de naam table__85264NED.csv). In de Chrome-browser komt de naam van de download ook onder in het scherm te staan; de download kun je dan openen door daar te klikken. NB wordt het bestand niet in Excel geopend dan moet je er voor zorgen dat de extensie CSV gekoppeld is aan Excel of de optie Openen met gebruiken
alle gegevens komen in één kolom terecht. Die moeten we dus nog ‘knippen’.
selecteer alle betreffende cellen in de kolom.
kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Tekst naar kolommen.
zorg dat in het vervolgscherm de optie Gescheiden is aangevinkt. Klik op de button Volgende.
zorg dat Komma is aangevinkt en kies Voltooien
Alle gegevens, zoals ze in de tabel op het scherm stonden, worden in een overzichts-vorm aangeleverd.
Wat als we meer en andere gegevens er bij willen hebben?
Download 2
Door op de web-pagina de optie te klikken kom je vanzelf in het Statline-gebied van het CBS, waar je veel meer opties hebt om gegevens te kiezen.
Maar we gaan eerst eens kijken wat er gebeurt als je nu Download kiest: Kies de optie CSV volgens tabindeling.
Open de download; het resultaat zal direct in Excel getoond worden (zie het tabblad Download2 in het Voorbeeldbestand):
Je kunt allerlei filteringen aan of uit zetten. Maar je kunt ook onderdelen verplaatsen.
Wanneer je bijvoorbeeld de button Geslacht met de muis ‘vastpakt’ kun je die verplaatsen naar het rijengebied:
Plaats Geslacht boven Onderwerp en zorg bij de filtering dat alle opties zijn aangevinkt
Kies weer de Download-button en open het bestand:
Na wat oefening zul je merken dat je op deze manier redelijk snel gewenste gegevens in Excel kunt overnemen. Een nadeel is dat er veel ‘handwerk’ bij komt kijken en dat de lay-out vaak niet direct geschikt is om verschillende analyses uit te voeren. En als de cijfers door het CBS worden geactualiseerd zullen alle handelingen opnieuw moeten worden gedaan!
In het volgende artikel zullen we dan ook het gebruik van de zogenaamde Open-Data-optie van het CBS en Excel bespreken.
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Deze week kwam de vraag binnen of het mogelijk was om met behulp van een formule gegevens uit een tabel, die aan een bepaalde voorwaarde voldoen, te selecteren. De eerste reactie was: hup, dat doen we even. Maar dat viel toch nog tegen.
Want niet iedereen heeft de beschikking over Excel 2021 of Excel 365. Daar ben je met één formule in een cel klaar. Maar niet getreurd: er zijn altijd alternatieven denkbaar.
Nieuwe Filter-functie
Meer specifiek was de vraag hoe de namen in een bestand met een formule zouden kunnen worden geselecteerd op basis van de selectie in kolom D (zie Voorbeeldbestand).
Vanaf Excel-2021 en -365 bestaan er zogenaamde Spill-functies (ik kwam ergens de Nederlandse benaming Overloop-functies tegen). Het speciale van dit soort functies is dat wanneer je een formule met zo’n functie in een cel plaatst, het resultaat over meerdere cellen terecht kan komen (vandaar spill en overloop). In dit geval plaatsen we ergens de formule =FILTER(Tabel1[Naam];Tabel1[Selectie]=Waar;”Niets gevonden”) en het resultaat is dan alle namen ‘met een vinkje’ in cellen onder elkaar.
‘Oude’ oplossing1
Maar heb je niet de beschikking over deze nieuwe functies dan zul je iets anders moeten bedenken.
Een oplossing is om op basis van de tabel met namen en selecties een draaitabel te maken. Plaats het veld Naam in het vak Rijen en Selectie in het vak Filters (of maak een slicer zoals in het Voorbeeldbestand). Uiteraard kiezen we bij Selectie dan alleen de optie Waar.
In het voorbeeld is ook een lege naam aangevinkt en deze komt in de rij (leeg) tevoorschijn.
NB Excel sorteert alle namen automatisch op alfabetische volgorde.
Wil je de oorspronkelijke volgorde aanhouden, plaats dan ook Nr in het vak Rijen.
In het voorbeeld hiernaast zijn ook de geselecteerde lege namen in de draaitabel uitgefilterd.
Maar helaas deze oplossing voldeed niet aan de verwachtingen: “Kan het ook met een formule zodat er na het aanvinken van de personen geen extra handeling (het vernieuwen van de draaitabel) meer nodig is?“
Oplossing2
Voor deze oplossing hebben we een hulpkolom nodig (zie kolom E in het Voorbeeldbestand). Daar geven we iedere geselecteerde naam een eigen volgnummer.
In de eerste regel (cel E3) plaatsen we de formule =ALS(D3;1;0) Dus als cel D3 de waarde Waar bevat dan wordt het volgnummer 1 anders 0.
Cel E4 krijgt de formule =E3+D4 We maken hier gebruik van het feit dat Excel de waarde Waar bij een berekening omzet naar 1 (en Onwaar naar 0). Cel E4 kan dan naar beneden gekopieerd worden.
NB we hadden in cel E3 dus ook de formule =D3+0 kunnen plaatsen.
En nu het resultaat. In cel N3 plaatsen we de formule =ALS.FOUT(INDEX($C$3:$C$32;VERGELIJKEN(B3;$E$3:$E$32;0));””)
Als eerste gaan we de waarde van de teller in B3 Vergelijken met de hulp-kolom E. De derde parameter is een 0, dus Excel zoekt een exacte match en dan ook nog de eerste die hij (of zij?) tegenkomt.
Op basis van de gevonden regel in de tabel haalt INDEX de overeenkomende naam op. Als het ergens mis gaat (bijvoorbeeld als het volgnummer niet voorkomt in de hulpkolom) dan is het resultaat “” (niets dus).
In de kolommen F en G staan 2 alternatieven voor de bepaling van de hulpvariabele: cel F3 bevat de formule =AANTAL.ALS($D$3:D3;WAAR). Deze kan naar beneden gekopieerd worden waarbij de eerste D3 blijft staan (deze is absoluut) en de tweede wordt D4, D5 et cetera (een relatieve verwijzing).
Oplossing3
Deze oplossing bouwt door op het vorige artikel op de website van G-Info over de Aggregaat-functie. Bekijk de formule in cel O3 van het Voorbeeldbestand: =ALS.FOUT(INDEX(C$3:C$32;AGGREGAAT(15;6;(RIJ($D$3:$D$32)-RIJ($D$3)+ 1)/($D$3:$D$32 =WAAR);RIJ($A1))); “”)
Gebruik de optie Formule evalueren om de werking te onderzoeken.
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Regelmatig voorziet Microsoft zijn software van nieuwe functies of andere nuttige (?) uitbreidingen. Al een poosje hebben Excel-gebruikers de beschikking over de functie AGGREGAAT; de opvolger van Subtotaal.
Na een korte toelichting op deze “nieuwe” functie zullen we inzoomen op één van de belangrijkste pluspunten hiervan: het kunnen omgaan met foutwaarden.
Inleiding
In het Voorbeeldbestand staat in het tabblad Data een kleine tabel met datums, regio’s en bedragen. Zoals te zien is staat er bij 6 januari een foutmelding in de kolom Bedrag.
Wanneer we op basis van deze tabel een draaitabel maken, dan zien we dat Excel automatisch als Waardeinstelling de optie Aantal heeft gekozen (en dat ie de datums automatisch tot maanden heeft gegroepeerd); zie bovenste draaitabel.
Uiteraard kunnen we handmatig de Waardeinstelling veranderen; in de 2e draaitabel is deze gewijzigd in Som. Helaas: niet ieder vakje is nu gevuld met een bedrag. De foutmelding in de bron verhindert dat. Ditzelfde geldt als we de instelling veranderen in bijvoorbeeld Max of Min (zie het Voorbeeldbestand).
Maar misschien kunnen we een andere Excel-optie of -functie gebruiken, zodat we dit probleem kunnen omzeilen?
Subtotaal
Aan deze functie hebben we al eens eerder een artikel gewijd.
Deze Excel-functie bestaat eigenlijk uit 11 verschillende functies (waarvan Gemiddelde, Som, Max, Min en Aantalarg het meest gebruikt worden). Ook kun je door het juiste gebruik van Functie_getallen verborgen waarden al dan niet meenemen (zie het betreffende artikel).
In het tabblad Subtotaal van het Voorbeeldbestand kun je door het aanpassen van cel F3 zien wat het resultaat van de functie Subtotaal is. Ook hier zullen Som, Min etc foutmeldingen genereren.
Bekijk wat het resultaat is als je in het tabblad Data de groepering sluit ( of een filtering aanbrengt in de tabel met gegevens. Wijzig ook cel D19 in =115/1 en bekijk de consequenties voor de functie Subtotaal.
Aggregaat
De functie Aggregaat wordt door Microsoft als vervanger voor Subtotaal gepositioneerd. Deze laatste is alleen vanwege compatibiliteit in de gereedschapskist blijven zitten.
In principe kent Aggregaat 2 verschijningsvormen () waarbij we ons hier op de eerste zullen concentreren.
Aan het overzicht op het tabblad Aggregaat van het Voorbeeldbestand is te zien dat het aantal Functies (de eerste parameter) is uitgebreid tot 19.
Dus bijvoorbeeld ook mediaan- en percentiel-berekeningen zijn nu met behulp van deze functie mogelijk.
Maar de 2e parameter is zeker zo interessant: hiermee kunnen we bijvoorbeeld aangeven of foutwaardes en/of verborgen rijen moeten worden genegeerd et cetera.
Als derde parameter geven we het bereik mee, waarop de berekening moet worden uitgevoerd. De vierde parameter wordt alleen gebruikt vanaf de Functie_getallen 14 en hoger.
In het eerste voorbeeld wordt de Som van de diverse kolommen bepaald, waarbij wel of niet de Foutwaarden worden genegeerd.
NB in de formule in cel F4 is de 2e parameter leeg gelaten; dit is hetzelfde als wanneer je daar een 0 als optie zou gebruiken.
LET OPLET OPLET OPLET OP de formules in regel 7 bevatten een 4e parameter. Maar wanneer het Functie_getal kleiner is dan 14 dan wordt deze parameter gezien als een extra bereik; dit bereik wordt dan bij de berekeningen van bijvoorbeeld de Som meegenomen!
In het tweede voorbeeld zal Excel de Grootste waarde in de kolommen opzoeken; dit kan natuurlijk alleen voor getallen (en dus ook datums). Vul je in cel E7 de waarde 2 in dan wordt de één na grootste waarde opgehaald.
Audit
De Aggregaat-functie kan ook goed gebruikt worden bij audit-werkzaamheden of bij de beveiliging van je Excel-systemen. In cel E12 wordt gecontroleerd of de kolom uit E11 foutwaardes bevat of niet:
Op het tabblad Aggr2 van het Voorbeeldbestand staan wat voorbeelden waarmee (in dit geval) het maximale bedrag wordt bepaald.
Zolang er in de kolom Bedrag van de data-tabel fouten voor komen zal de formule in C4 geen resultaat weergeven. Wijzig ter controle cel D19 in het tabblad Data in bijvoorbeeld =115/1.
NB de formule in C4 is niet met Enter afgesloten maar met Ctrl-Shift-Enter; Excel ‘weet’ dan dat het een matrix- of CSE-berekening betreft. Excel plaatst zelf de accolades rond de formule.
In cel C5 wordt het maximum-onder-voorwaarden berekent met de fiormule:
LET OP voor het bepalen van het minimum-bedrag moet in de Aggregaat-functie niet alleen de 14 in 15 veranderd worden, ook de 3e parameter heeft een aanpassing ondergaan: =AGGREGAAT(15;3;tblData[Bedrag]/ (tblData[Regio]=C2);1)
Met behulp van Formule evalueren kun je waarschijnlijk zelf bedenken waarom dat nodig is.
LET OP het gebruik van complexe bereiken is alleen toegestaan bij Functie_getallen 14 en hoger; anders krijgt u de foutmelding #Waarde!
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Binnen ieder bedrijf is het van belang om zoveel mogelijk processen te standaardiseren en vaak ook te automatiseren.
Ook wanneer we Excel als hulpmiddel bij ons werk gebruiken is het belangrijk om zoveel mogelijk handmatige handelingen, die regelmatig terugkomen, te vermijden. Power Query, draaitabellen, en VBA kunnen daarbij een grote rol spelen.
Voor diegene, die vaak verschillende rapportages moeten maken, is een andere invalshoek belangrijk. Ga niet voor iedere nieuwe rapportage weer opnieuw het wiel uitvinden, maar gebruik een flexibel stramien. Hoe dat in zijn werk gaat zullen we in dit artikel aan de hand van een direct inzetbaar Voorbeeldbestand laten zien.
Bedrijf
Het eerste tabblad van het Voorbeeldbestand, Bedrijf, bevat diverse bedrijfsspecifieke gegevens, inclusief een logo.
NB1 wanneer je dit logo vervangt (door er rechts op te klikken) zorg dan dat het nieuwe plaatje een transparante achtergrond heeft.
NB2 diverse cellen in dit tabblad (en ook op andere plaatsen in de werkmap) hebben een naam gekregen, die in de rest van het rapportage-stramien gebruikt worden. Cel C2 bijvoorbeeld heeft de naam BedrNaam.
In cel C8 wordt de meest recente datum uit het Data-bestand (zie hierna) opgehaald met behulp van de functie MAX. De inhoud van cel C9 bepaalt of deze datum als referentie dient voor de rapportage of een andere (cel C10).
NB3 op diverse plaatsen in dit stramien zul je zien dat de invoer in cellen beperkt is met behulp van Gegevensvalidatie; zie bijvoorbeeld de cel C9 (alleen Ja en Nee zijn toegestaan).
In de regels 13 tot en met 22 kun je zelf de diverse onderdelen (en teksten) kiezen die in de kop- en voetregel van een rapportage moeten komen.
NB4 in cel G19 staat een spatie om er voor te zorgen dat de voetregel aan de rechterkant niet tegen de rand aan komt.
Experimenteer met de mogelijkheden en beoordeel het effect op de rapportages in de tabbladen Ovz1 en Ovz2.
Basis-instellingen
Het tabblad Basis van het Voorbeeldbestand begint met een standaardopmaak voor datums (in dit geval hebben we gekozen voor 3 letters voor de maand, een spatie en 4 cijfers voor het jaar). Daaronder staan 6 items die als onderdeel voor de kop- en voetregels kunnen dienen. In het blok in de kolommen E en F staan diverse opmaak-omschrijvingen en -codes die bij het weergeven van gegevens in de rapportage gebruikt kunnen worden.
NB aangezien alle keuze-items in Excel-tabellen zijn opgenomen zal een gewenste uitbreiding van de opties direct overal in de werkmap geëffectueerd worden.
Overige instellingen
In het eerste blok van de Overige instellingen (zie het tabblad Instel van het Voorbeeldbestand) wordt van maximaal 10 items vastgelegd wat de inhoud is, welke opmaak uit de lijst van het tabblad Basis deze moeten hebben en eventuele opmerkingen er in de rapportage moeten worden weergegeven. Voor uw eigen rapportage moeten/kunnen de kolommen Naam, Opmaak en Opmerkingen aangepast worden.
Daaronder staat een Excel-tabel met daarin de omschrijvingen van de berekeningen die op de gegevens kunnen worden toegepast. De berekeningen zelf staan op het tabblad Berekeningen. Ook hier zult u voor uw eigen rapportage aanpassingen moeten doorvoeren en wel in de tweede en derde kolom.
NB in het voorbeeld-stramien staan 10 berekeningen, maar dit mogen er ook meer (of minder) zijn.
LET OP de opmaak van berekende items is standaard gelijk aan de opmaak van de onderliggende data; in het voorbeeld hierboven zal de opmaak van Waarde deze maand in het geval van Verkoop-cijfers gelijk zijn aan Valuta, maar die van Klanten heeft dus de opmaak Getal. Maar bij berekening 7 (in dit geval) geven we aan dat alle items hiervan de opmaak % met 1 decimaal zullen hebben.
Als laatste geven we nog aan wat voor een soort rapportage het hier betreft. Er zijn 2 mogelijkheden: alleen de data van het lopende jaar worden getoond (YtD = Year to Date) of altijd de laatste 12 maanden (YoY = Year on Year).
Iedere rapportage-pagina heeft bovenaan 3 blokjes met de belangrijkste gegevens/berekeningen. Bij Indeling overzichtsblokken wordt bij DataNr allereerst aangegeven welke 3 van de 10 items moeten worden getoond; bij Berekening kun je 5 opties kiezen die voor deze items moeten worden weergegeven.
De sparkline daaronder geeft het verloop in de tijd weer van het betreffende item (YtD of YoY). Met de instellingen zoals hierboven is het resultaat:
Data
In het Voorbeeldbestand is het tabblad Data gevuld met fictieve gegevens. Het systeem is beperkt tot 10 kolommen met gegevens waarvan de namen vastliggen in het tabblad Instel.
Per maand dienen alle gewenste kolommen met de juiste data gevuld te worden. Gebruik je ook berekeningen waarbij de actuele gegevens afgezet worden tegen de beoogde resultaten dan dienen ook de Doel-kolommen gevuld te worden.
Berekeningen
In het tabblad Berekeningen van het Voorbeeldbestand worden allereerst van alle 10 items (en de daarbij behorende doelen) de YoY-data opgehaald.
Daaronder wordt de opmaak van (nu) maximaal 16 berekeningen per item bepaald. De betreffende formule is uitdagend te noemen: =ALS(INDEX(tblBerekOpties[Opmaak];[@Nr])=0;INDEX(tblOpmaak[OpmCd];VERGELIJKEN(INDEX(tblDataInd[Opmaak];VERGELIJKEN(tblYoY[[#Kopteksten];[Kol1]];tblDataInd[Kolom];0));tblOpmaak[Naam];0));INDEX(tblOpmaak[OpmCd];VERGELIJKEN(INDEX(tblBerekOpties[Opmaak];[@Nr]);tblOpmaak[Naam];0)))
In het derde blok worden de benodigde berekeningen gedefinieerd. In cel D41 staat een simpele verwijzing naar een cel uit het eerste blok.
De formule waarmee berekening 8 (het gemiddelde van de laatste 3 maanden) wordt uitgevoerd, is: =GEMIDDELDE(VERSCHUIVING(D16;-2;0;3;1)) ofwel we gaan vanaf cel D16 2 regels omhoog en 0 kolommen naar rechts/links en kiezen dan een blok cellen, 3 hoog en 1 breed; van deze range wordt het gemiddelde bepaald.
Nog even de vorige 2 stappen combineren met (in cel D61) de formule: =ALS.FOUT(TEKST(D41;D21);0)
Dus met de functie TEKST wordt de inhoud van cel D41 opgemaakt met de inhoud van cel D21. Als dit onverhoopt een probleem oplevert, dan wordt de waarde 0 weergegeven.
Met al dat voorwerk kunnen we nu de blokjes in de kop van de rapportages samenstellen. Ook de cijfers voor de sparklines worden klaar gezet.
Omdat we ook een grafiek in de rapportage willen opnemen, moeten we de benodigde gegevens nog even klaar zetten.
Onderaan staat een draaitabel. Een Slicer in de rapportage bepaalt welk item we willen weergeven. Het betreffende nummer gebruiken we in het grafiekblok.
Rapportage-pagina’s
In het tabblad Ovz1 van het Voorbeeldbestand staat een eerste voorbeeld van een rapportage gebaseerd op alle berekeningen uit de rest van het werkblad. Wijzigingen in de diverse tabbladen hebben direct effect op het resultaat. Kies in de slicer aan de rechterkant van de grafiek een ander item en je krijgt de bijbehorende grafiek.
Wil je naast de drie belangrijkste items met de berekeningen in de blokjes aan de bovenkant van de pagina alle info in één oogopslag: zie het tabblad Ovz2 van het Voorbeeldbestand.
Uiteraard is dit nog geen totaal rapportage; ongetwijfeld kunt u nog andere invalshoeken vinden die van belang zijn. Kopieer een Ovz-tabblad en ga aan de slag!
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
Alle (on-line) bedrijven willen tegenwoordig van hun klanten graag een review ontvangen. Dit is niet alleen interessant voor de marketingafdeling maar ook voor nieuwe kopers. Ikzelf kijk tenminste ook altijd gauw door de lijst reviews om te zien wat anderen van het product vinden.
Meestal kun je een product waarderen met 1-5 sterren. De betrouwbaarheid van de gemiddelde score (zoals hierboven de 4,7) wordt natuurlijk steeds groter met het aantal reviews dat er is uitgebracht. Maar de kwaliteit van de score wordt ook bepaald door de spreiding van de uitgebrachte scores. Vandaar dat je meestal nog kunt doorklikken:
In dit artikel gaan we wat mogelijkheden binnen Excel langs waarmee we bij een review-overzicht snel en interactief op de details kunnen inzoomen (het idee en de cijfers zijn ontleend aan een artikel van Chandoo).
Basis-gegevens
In het Voorbeeldbestand in het tabblad Reviews ziet u een bestand met voor enkele top-deals van Bol.com een reviewer-code met de daarbij behorende FICTIEVE score.
Door middel van een draaitabel kunnen we snel een overzicht met gemiddeldes genereren:
Maar om makkelijk een overzicht van de onderliggende details te kunnen tonen gaan we een andere methode hanteren.
Samenvattend overzicht
In kolom C hebben we de namen van de aanbiedingen geplaatst, waar we de gemiddelde Rating van willen weten (zie het tabblad Ovz1 van het Voorbeeldbestand).
NB wil je snel alle items hebben, die in het basis-bestand voorkomen, dan liggen er 3 methodes voor de hand:
maak een draaitabel zoals hierboven en kopieer de namen naar de gewenste plaats
gebruik de optie Geavanceerd in het blok Sorteren en filteren van de menutab Gegevens: en vul bij Kopiëren naar de gewenste plaats in
kopieer de kolom met de aanbiedingen en kies dan de optie Dubbele waarden verwijderen in het blok Hulpmiddelen voor gegevens van de menutab Gegevens.
In cel D3 staat de formule =GEMIDDELDE.ALS(tblReviews[Aanbieding];C3;tblReviews[Rating]) Ofwel bepaal het Gemiddelde van alle Ratings als de naam in C3 gelijk is aan een naam in de kolom Aanbieding van de Excel-tabel tblReviews.
In kolom E wordt het gemiddelde grafisch weergegeven. Daarbij worden 3 symbolen uit het font Wingdings2 gebruikt: (zie het tabblad Berekeningen).
De cellen met de symbolen hebben respectievelijk de namen crkVol, crkHalf en crkLeeg gekregen.
Cel E3 bevat de formule: =HERHALING(crkVol;GEHEEL(D3+0,25)) &HERHALING(crkHalf;ALS(EN(REST(D3;1)>0,25;REST(D3;1)<0,75);1;0)) &HERHALING(crkLeeg;GEHEEL(5-(D3-0,25)))
Eerst wordt het aantal volle cirkels bepaald door het gehele deel van de waarde in D3 te nemen (door daar 0,25 bij op te tellen wordt een waarde van bijvoorbeeld 1,8 ook voorzien van 2 volle cirkels); de functie Herhaling genereert een tekst met het gewenste aantal volle cirkels.
Als de Rest van deling van D3 door 1 groter is dan 0,25 EN kleiner dan 0,75 dan zal de tweede Herhaling-functie een halfvolle cirkel opleveren, anders niets.
Als laatste wordt het aantal toe te voegen lege cirkels gegeneerd.
Door middel van het &-teken worden de drie teksten aan elkaar gekoppeld.
LET OP op deze manier wordt een tekst gemaakt die uit symbolen uit het font Wingdings2 bestaat. Om deze tekst in Excel zichtbaar te maken moeten de betreffende cellen in kolom E dan ook het lettertype Wingdings2 hebben.
Zoals hiervoor gezegd hebben we details over het aantal reviews en de spreiding in de scores nodig om de gemiddeldes op waarde te schatten.
Detail overzicht
In het tabblad Berekeningen van het Voorbeeldbestand vindt u een overzicht van de scores van een bepaalde aanbieding. Afhankelijk van de waarde in cel C2 (met de naam aanbNr) wordt in cel D2 (met de naam aanbNm) de naam van de betreffende aanbieding opgehaald door middel van de functie Index (lstAanbiedingen is de lijst producten in kolom C van het tabblad Ovz1).
Cellen B1:B9 bevatten de getallen 1 t/m 5; alleen hebben die een Aangepaste notatie gekregen: “Score “0 Selecteer daartoe de cellen, druk op Ctrl-1 (Celeigenschappen) en kies het tabblad Getal:
In cel C5 staat de formule: =AANTALLEN.ALS(tblReviews[Aanbieding];aanbNm;tblReviews[Rating];B5) Ofwel bepaal het aantal als de waarde in de kolom Aanbieding van de Excel-tabel tblReviews gelijk is aan aanbNm en de waarde in Rating gelijk is aan de waarde in B5 (het woord Score daar is opmaak!). Cel C5 heeft ook weer een Aangepaste notatie(0) gekregen.
In cel C10 wordt de som bepaald van de cellen daarboven met als Aangepaste notatie0 “reviews”. Dit levert het totaal aantal reviews voor de betreffende aanbieding. Dit totaal wordt in de cellen D5:D9 overgenomen. Dit hebben we nodig voor het maken van de bijbehorende grafiek.
Detail grafiek
Van het detail-overzicht kunnen we nu makkelijk een grafiek maken:
selecteer de cellen B4:D9 in het tabblad Berekeningen
kies in de menutab Invoegen in het blok Grafieken de optie Kolom- of staafdiagram
kies het eerste staafdiagram (Gegroepeerde staaf)
We hebben op deze manier een grafische weergave van de aantallen per score vergeleken met het totaal aantal reviews.
Deze grafiek is gekopieerd naar het tabblad Ovz1; zoals te zien is doet wat opmaak wonderen:
verwijder de horizontale as, de gridlijnen en de legenda
klik op Grafiektitel, plaats de cursor in de Formulebalk, tik in = en klik met de muis op cel C10 van het tabblad Berekeningen
klik ergens in de grafiek met de rechter muisknop en kies Gegevens selecteren. Verplaats Aantal naar beneden
klik rechts op één van de staven en kies de optie Gegevensreeks opmaken. Zet de Overlapping van reeks op 100%.
klik rechts op één van de scores en kies As opmaken. Zet het vinkje aan bij Categorieën in omgekeerde volgorde.
klik rechts op één van de grote staven en kies de optie Gegevenslabels toevoegen. Klik rechts op één van de labels en kies de optie Gegevenslabels opmaken. Kies de optie Waarde uit cellen en selecteer de cellen C5:C9. Vink de optie Waarde uit.
pas de overige opmaak naar wens aan.
Interactief details tonen (1)
Nu moeten we nog een methode vinden om vanuit het review-overzicht de detail-grafiek te tonen. Op het tabblad Ovz1 van het Voorbeeldbestand hebben we gebruik gemaakt van Keuzerondjes:
kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen en kies daar de optie Keuzerondje (Formulierbesturingselement). NB zie je geen menutab Ontwikkelaars? Klik ergens in hetmenugedeelte met de rechter muisknop en kies de optie Het lint aanpassen. Zorg dan dat in het rechterblok de optie Ontwikkelaars is aangevinkt.
klik in de cel waar je het eerste rondje wilt hebben. Verschuif het rondje eventueel nog zodat hij precies op de gewenste plaats staat.
pas de standaard-tekst Keuzerondje 1 aan; in dit geval wissen we de tekst helemaal.
klik rechts op het rondje en kies de optie Besturingselement opmaken. Zorg dat bij Koppeling met cel een verwijzing naar de cel Berekeningen!$C$2 komt.
klik rechts op het rondje en kies Kopiëren
toets 8 keer Ctrl-V (plakken) in
plaats het onderste rondje op de juiste plaats
selecteer alle rondjes door er op te klikken met Ctrl ingedrukt
kies in de menutab Hulpmiddelen voor tekenen/Opmaak in het blok Rangschikken de optie Uitlijnen en kies daarna Links uitlijnen; daarna nogmaals Uitlijnen met de optie Verticaal verdelen.
Wanneer je nu een rondje aanklikt zal het overeenkomende nummer in cel C2 van het tabblad Berekeningen worden geplaatst. Het detail-overzicht en de bijbehorende grafiek(en) passen zich dan automatisch aan.
NB aan de cellen C3:E11 is een voorwaardelijke opmaak gekoppeld die er voor zorgt dat de achtergrond van de gekozen aanbieding wordt gekleurd:
Interactief details tonen (2)
Een andere methode is in het tabblad Ovz2 van het Voorbeeldbestand geïmplementeerd.
Als je daar op één van de cellen C3:E11 klikt dan start een VBA-routine die er voor zorgt dat cel C2 (met de naam aanbNr) van het tabblad Berekeningen wordt aangepast.
klik met de rechter muisknop op de naam van één van de tabbladen en kies de optie Programmacode weergeven
klik in het Project-overzicht aan de linkerkant van het scherm dubbel op het blad Ovz2
plaats in het codescherm de volgende programma-regels: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim topRij As Integer If Not (Application.Intersect(Target, Range(“lstOvz2”).Cells) Is Nothing) Then topRij = Range(“lstOvz2”).Cells(1, 1).Row [aanbNr] = Target.Row() – topRij + 1 End If End Sub Kortweg: als in dit werkblad de selectie wordt veranderd (door bijvoorbeeld het klikken met de muis of het gebruik van de cursortoetsen) dan zal de VBA-routine kijken of er een overlap is tussen de cel die is geselecteerd (de Target) en de cellen van lstOvz2 (dus C3:E11). Is dat zo dan wordt het rijnummer van de eerste cel van deze lijst in de variabele topRij geplaatst. Door deze topRij af te trekken van het rijnummer van de Target-cel weten we welke aanbieding aangeklikt is (nog wel even corrigeren met +1).
Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?
te klikken kom je vanzelf in het Statline-gebied van het CBS, waar je veel meer opties hebt om gegevens te kiezen. 
Voorbeeldbestand: Filter2019.xlsx
of een filtering aanbrengt in de tabel met gegevens.
) waarbij we ons hier op de eerste zullen concentreren.
en vul bij Kopiëren naar de gewenste plaats in
(zie het tabblad Berekeningen).
