Managers willen cijfers: zo overzichtelijk mogelijk en in reeksen van maanden. Directies vragen vaak om een uitdraai van de belangrijkste gegevens uit de database, namelijk de eindresultaten. Een juist overzicht blijkt nog wel eens een uitdaging bij bedrijven.
In dit overzicht moeten bovendien de voorliggende 13 maanden worden getoond. 13 Maanden? Jawel, om ook de gegevens met dezelfde maand van een jaar eerder te laten zien. Bovendien wil men de maand januari van het jaar onmiddellijk in het oog laten springen. De cumulatieve telling van het aantal producten begint dan namelijk opnieuw.
Je ziet hier een fictieve opmaak van cijfers omtrent daklozen in Amsterdam. Van de daklozen wordt allereerst een melding gedaan, daarna zijn zij actief. Enkelen komen op intake bij de Gemeente en daarvan is weer een deel ingetrokken in een woning. In 2017 zijn er in totaal 11 van de 413 zwervers ingetrokken in een woning.
Uiteindelijk ziet het overzicht als volgt uit.
Hoe kun je dit zelf maken? Het is te doen met een combinatie van Access en Excel. Bovenstaand voorbeeld is uit enkel Excel afkomstig. In de volgende blog ga ik in op het verzamelen van de cijfers met Access en Vba, maar eerst is Excel aan de beurt.
De maanden in de linkerkolom
Je ziet dat ik de dertien maanden in de linkerkolom onder elkaar heb gezet beginnend met de maand waarin ik deze blog begon te schrijven. Omdat de maanden dynamisch worden weergegeven zul je de volgende maand dec-17 bovenaan zien staan en elke maand met de bijbehorende getallen zal een regel naar beneden zijn geschoven.
Ook zie je dat de maand januari meteen in het oog springt. Als je het sheet in januari 2018 opent zal ook de maand december een regel gezakt zijn en jan-17 nog steeds dezelfde opmaak kennen. Door gebruik te maken van een regel in de voorwaardelijke opmaak laat ik je zien hoe dit werkt. Ik kom daar later in de blog op terug. Eerst de maandnotering in de linkerkolom.
TEKST(), DATUM() en VANDAAG()
Met een combinatie van de Excel-functies TEKST() en DATUM() en VANDAAG() (hyperlinks naar de Microsoft-sites) kun je de maanden dynamisch neerzetten.
We beginnen met de functie DATUM(Jaar;maand,dag). Als je een cel vult met =DATUM(2017;11;1), dan wordt de uitkomst in Excel: 1-11-2017. Excel heeft voor de opmaak van deze uitkomst meteen de datumnotatie toegepast. Je wist natuurlijk al dat de datum een getal is. Het komt overeen met het verstreken aantal dagen sinds 1 januari 1900. Je kunt dat zien door het formaat van de cel op Normaal te zetten: 43040.
In plaats van cijfers opnieuw een functie
Voor =DATUM(2017,11,1) vullen we nu in =DATUM(JAAR(VANDAAG()); MAAND(VANDAAG());1). Als je de hyperlink van VANDAAG() al hebt gelezen, is het eenvoudig in te zien dat met de functies JAAR(), MAAND() van VANDAAG() in de Datumfunctie de getallen 2017 en 11 zullen worden doorgegeven.
Ook eenvoudig is het vervolgens om 1-10-2017 te laten zien. Gewoon de maand van vandaag met eentje verminderen, dus = …, MAAND(VANDAAG)-1 ; 1).
De waarde van de cel aanpassen met TEKST()
Nu kunnen we met de functie TEKST() de waarde aanpassen door deze formule vooraf te laten gaan met =TEKST(dezeFormule;”mmm-jj”), waardoor het uiteindelijk resultaat de tekst ‘nov-17’ of met een -1 , ‘okt-17’ wordt.
De volledige functie wordt bijvoorbeeld voor de maand april.
=TEKST(DATUM(JAAR(VANDAAG()); MAAND(VANDAAG())-7;1);”mmm-jj”)
Door in de formule in de linker kolom neer te zetten de maand telkens met eentje te verminderen, krijg je ze in beeld zoals je hierboven ziet. Neem je dit over, dan zal het al niet meer lukken om de eerste regel met nov-17 te laten beginnen 
. En dat is nu juist ook de bedoeling. Het management wil immers de stand van zaken vanaf de huidige maand bovenin hebben staan.
Nog een suggestie
Wil je het nog een staaltje flexibeler hebben, dan kun je Vandaag() vervangen door de naam van een veld. Ik heb in dit voorbeeld de cel E1 de naam Refertedatum te geven. Hierin plaatste ik de functie =VANDAAG().
Deze cel gaf ik de naam ‘Refertedatum’. Dat doe je als volgt; Zet de cursor in de cel E1 en typ ‘Refertedatum’ in de celverwijzing. De celverwijzing vind je links bovenin onder ‘Plakken’.
Daarna wijzig je de formule als volgt:
=TEKST(DATUM(JAAR(Refertedatum()); MAAND(Refertedatum())-7;1);”mmm-jj”)
Als je nu in de cel E1 een andere datum invult, bijvoorbeeld 1-9-2017 dan wijzigen de maanden meteen mee. Mooi toch?
Managementdata
In het tabblad ‘tblManagement’ staan bij mij de cijfers uit de Access-tabel. Maandelijks wordt deze tabel automatisch gevuld bij het openen van de database op de 1e van de maand. De klein deel van de tabel ziet er nu als volgt uit (je ziet dat ik dit deel van de blog in december schrijf 2017;-)).
Voor het voorbeeld raad ik je aan deze data over te nemen op een nieuw tabblad en dit tabblad de naam ‘tblManagement’ te geven. Dubbelklik onderin op ‘Blad 2’ voor het wijzigen van de naam.
Als je de data hebt overgenomen, kies dan vervolgens in de menubalk voor Invoegen en dan voor Tabel. Je ziet je gegevens ook in mijn opmaak tevoorschijn komen. Bovendien wordt de volgende functie VERT.ZOEKEN() schoner om te zien.
De functie VERT.ZOEKEN()
Met deze functie gaan we de getallen uit het tabblad tblManagement ophalen. Zet de cursor in cel B4 en dubbelklik in de formulebalk op het functieteken ƒx. Het volgende venster verschijnt. Kies in de categorie voor ‘Zoeken en verwijzen’ en scroll vervolgens in het vak daaronder tot VERT.ZOEKEN tevoorschijn komt en dubbelklik hierop.
Daarna komt het invulvenster voor de functie tevoorschijn. Vul de gegevens in zoals ik dat deed. Mijn complimenten als met de eerste keer het juiste resultaat tevoorschijn komt.
De formule ziet er in de cel dan zo uit =VERT.ZOEKEN(A4;Tabel1[#Alles];2;ONWAAR)
.
Nu kan het zijn dat de opgehaalde cel geen waarde heeft (NULL). In dat geval wordt de uitkomst van de formulie #NB. Dit kun je voorkomen met de functie =ALS.NB(); waarmee je een alternatieve waarde kunt ingeven. De uiteindelijke formule ziet er dan zo uit:
=ALS.NB(VERT.ZOEKEN(A13;Tabel1[#Alles];2;ONWAAR);””)
Breng de cursor in de cel A4 en kopieer de formule door een dubbelklik rechts onderin de cel en je zie dat ook de andere databasegegevens worden opgehaald. In cel C4 plaats je de formule =ALS.NB(VERT.ZOEKEN(A13;Tabel1[#Alles];5;ONWAAR);””), waarmee de vijfde kolom van de tblManagement wordt opgehaald. Deze kopieer je ook naar onder. Eventueel gebruik je de functietoets F4 om een (deel) van de formule absoluut te maken.
Wijzig de refertedatum
Het leuke vind ik nu dat alle gegevens meteen worden aangepast zodra de Refertedatum wijzigt. Zoals je ziet is ook de opmaak bij mij al aangepast. De maand januari heeft een andere kleur en de getallen van die maand zijn lichtgrijs geworden. Dat doen we met de ‘Voorwaardelijke opmaak’ in Excel.
De voorwaardelijke opmaak
Het management wil in één oogopslag de gegevens van de maand januari in beeld, omdat dit het startpunt is van de cumulatieven. Met de regels van de voorwaardelijke opmaak gaan we ook dit dynamisch voor elkaar brengen.
Selecteer de cellen A4:A16 en kies dan op de menubalk in het menu start voor ‘Voorwaardelijke Opmaak’ en vervolgens voor ‘Regels beheren’.
In dit scherm kies je voor een Nieuwe regel, waarna het vervolgscherm verschijnt. Dit werd door mij als onderstaand ingevuld. Misschien even zoeken, maar erg ingewikkeld is het niet om de regel in te vullen. Kies hierin je eigen opmaak. Wil je de paarse kleur van Florijn.com gebruiken, dan kies je in de opmaak voor ‘Meer kleuren’. ‘Aangepast’ en de RGB waarden (188,68,131) en klik een paar keer op Ok om terug te gaan. In de gekozen reeks zie je de cel met de waarde ‘jan-17’ in de gewenste kleur staan.
Selecteer nu in de naastgelegen kolommen de range BE:C16. Deze cellen zullen lichtgrijs moeten worden weergegeven als de maand januari van het Refertejaar in kolom A staat.
De tweede regel vullen we op soortgelijke manier als de eerste, nadat we gekozen hebben voor het type regel: ‘Een formule gebruiken om te bepalen welke cellen worden opgemaakt’. In het vak ‘Waarden opmaken waarvoor deze formule geldt’ vul je de tekst in: =$A4 = “jan-” & TEKST(refertedatum;”jj”).
Als we onder Opmaak kiezen voor de lichtgrijze kleur of RGB(242,242,242) en een paar keer op Ok klikken, hebben we de Voorwaardelijke opmaak ook toegepast op de gekozen Range.
Hiermee hebben we ons Managementsmodel afgerond. In een volgend blog leg ik je uit hoe de cijfers tot stand komen en binnengehaald kunnen worden. Uiteraard horen wij het graag als je vragen of opmerkingen hebt!
Werkt u nog met verouderde desktop-software? Een MS Access ontwikkelaar / MS Access expert / MS Excel expert van Florijn bouwt uw MS Access database eenvoudig om naar een webapplicatie in de cloud, waarin uw gegevens volledig veilig staan, u geen overbodige gegevens opslaat en u gemakkelijk kunt kiezen wie waar bij kan. Tevens kunt u vanaf elke plek bij uw gegevens.
Bekijk hier hoe wij u precies kunnen helpen met uw bedrijfsgegevens veilig stellen.
Heeft u vragen? Bel ons gerust: 020 6 254 254.
