Categorieën
Uncategorized

Breng leegstand in kaart met interactieve grafiek in Excel

 

Je bent net begonnen als adviseur ruimtelijke ordening. De eerste opdracht is het in kaart brengen van de leegstand bij het lokale winkelcentrum. Na weken wachten heb je dan eindelijk een databestand in Excel. Net op tijd, want over twee dagen mag je een eerste update geven aan de stakeholders. Het liefst wil je iets interactiefs waarbij de betrokken partijen zelf gemakkelijk doorsnedes kunnen maken.

In deze blog laat ik je zien hoe je met behulp van een draaitabel en slicers, snel en simpel een interactief overzicht en een grafiek maakt.

Dit is het databestand wat je hebt gekregen.

En dit is het interactief overzicht waar we naar toewerken, met aan de linkerkant de slicers waarmee makkelijk gefilterd kan worden.

Databewerking

In het databestand staat in kolom G het bouwjaar. Het is niet handig om alle bouwjaren als filteroptie te geven in de slicer. De  lijst met mogelijkheden zou superlang worden. Daarom voegen we een kolom toe en maak je er categorieën van met behulp van de volgende formule:

=IF(ISBLANK(G2);”Onbekend”;IF(G2<1945;”<1945″;IF(G2<1970;”1945-1970″;IF(G2<1990;”1970-1990″;IF(G2<2010;”1990-2010″;”>2010″)))))

Verder ziet het bestand er goed uit.

Visualisatie

Met behulp van een draaitabel gaan we het interactief maken. De slicers kan je namelijk alleen gebruiken in combinatie met een draaitabel. Allereerst selecteren we de data in het databestand, kolom A t/m L. Dan via de menuknop invoegen, kies je voor draaitabel.

Klik ok op het volgende venster om de draaitabel op een nieuw werkblad te zetten.

Je krijgt daarna het volgende te zien op je werkblad.

De velden uit het datawerkblad kan je nu in kolommen en rijen weergeven. Ook kan je, indien nodig, waarden totaliseren door velden naar het venster rechtsonder te slepen (values). We slepen  Onderneming_branche in de rij en tellen Pand_straatnaam in de values.

Als Excel standaard de waarden totaliseert (sum), dubbelklik dan  op het veld in de values, en kies tellen (count).

Nu zien we in één oogopslag hoeveel leegstand we hebben. We missen alleen nog een mooie visualisatie om dit in verhouding te zien. Door met de cursor in de draaitabel te staan, kies je voor invoegen draaigrafiek.

De volgende grafiek verschijnt dan.

Dit geeft al mooi weer hoe de verhouding zijn, maar het kan nog beter. Met rechtermuisknop in de grafiek, kies ik voor een circeldiagram. Bovenaan kies ik eerst voor style 3 en daarna voor style 6.

Door deze volgorde te kiezen heb ik gelijk ook percentages in de donut staan.

Ik wil ook graag de waarden uit de draaitabel er in terugzien. Door te dubbelklikken op een van de percentages in de grafiek verschijnt er rechts een venster met opties. Ik kies ook voor value en de scheid de waarden door een nieuwe regel.

Verder pas ik de titel aan door er op te dubbelklikken.

Detailinformatie

In de volgende stap maken we een overzicht met meer details. Je kopieert en plakt de draaitabel naar cel N4 voor meer ruimte in het Excel werkblad. De grafiek sleep je daaronder.

De ene draaitabel begint nu op D4 en de andere in N4. In de draaitabel in D4, selecteer je de volgende velden voor in de rijen:
Eigenaar_Soort, Onderneming_Branch, Pand_Straatnaam, Pand_Huisnummer, Pand_Huisnummer_toevoeging, Pand_Bouwjaar, Locatus_Branch en Contactgegevens pandeigenaar.

Ook kunnen de headers wat klantvriendelijker gemaakt worden in regel drie.

Interactieve filters

Als laatste stap gaan we de interactieve filters toevoegen: de slicers. Filteren op straatnaam, bouwjaarcategorie,  type eigenaar en op Onderneming branche, geven het meeste inzicht.

De slicers zet je netjes onder elkaar in de eerste drie kolommen. Het aanpassen van de naam kan via Slicer Settings (rechtermuisknop). Vink dan gelijk ‘items verbergen zonder data’ aan. Dit zorgt ervoor dat je geen ‘lege’ knoppen krijgt.

Als je in de draaitabel staat en vervolgens klik op Filter Connections, dan zie je welke slicers verbonden zijn met de draaitabel. Als het goed is met alle vier.

Om de slicers ook te verbinden met de draaitabel in N4 met zijn bijbehorende grafiek, gaan we in de de draaitabel (N4) staan en klikken via Filter Connections ook bovenstaande vier slicers aan.

Het resultaat

Als je nu op ‘leegstand’ klikt in de bovenste slicer, dan wordt zowel de detailinformatie gefilterd als de grafiek. Ook zie je dat de keuzes in de andere slicers meebewegen met je eerst keuze. Zo kan je bijvoorbeeld in de tweede slicer alleen maar verder kiezen uit de Institutionele en Particuliere belegger. Simpelweg omdat dit de twee type eigenaren zijn met leegstand.

In dit voorbeeld heb ik voor een prettige weergave de grafiek over de draaitabel in N4 heen gesleept.In het detailoverzicht heb je verder direct de belangrijkste informatie beschikbaar en kan je gelijk in actie komen door de betreffende pandeigenaar te benaderen voor meer informatie.

De visualisatie voegt in deze filtering niet zoveel toe, maar wel als je bijvoorbeeld alleen filtert op de bouwjaren 1990-2010 in de Ferdinand Bolstraat. Dan zie je gelijk dat de selectie slechts twee branche categorieën heeft.

Je kan in dezelfde slicer ook meerdere waarden selecteren. Dat doe je door eerst de multi-select aan te zetten met de groene vinkjes. Filtering weer ongedaan maken, doe je met het kruisje bij de filter helemaal rechts van de slicer.

Nu ben je in staat om zelf je volgende draaitabel met interactieve grafiek te maken.

Succes!

Categorieën
Uncategorized

Meer inzicht in je maandelijkse marges met Excel

Met je draadloze headset ben jij als oplosser van problemen de hele dag in gesprek met mensen. Detachering of werving & selectie, jij zorgt voor de juiste mensen op de juiste plaats. ’s Avonds zit je op je gemak nog even in Excel de administratie te doen. Doordat je alles strak elke dag bijwerkt, heb je een goed overzicht in de marges per consultant, maar wat zijn eigenlijk je marges voor de komende maanden?

Met behulp van het onderstaande voorbeeld laat ik je zien hoe je in een drietal eenvoudige stappen een prognose kan maken van je marges voor de komende maanden.

De basis op orde

De linkerkant, tot en met kolom K, bestaat uit basisgegevens.

Vanaf kolom L zijn de meeste kolommen berekend om uiteindelijk de gemiddelde marge per maand te berekenen in kolom R.

Het uurloon wordt berekend door het salaris te delen door het (maximaal) gemiddeld aantal werkuren per maand van 173,33 uur. Het tarief wordt berekend door het uurloon te vermenigvuldigen met de opslagfactor in kolom I.

In kolom Q hanteer ik een vast gemiddeld aantal werkuren van 150 in de maand om de gemiddelde marge per maand te bereken in kolom R (kolom P x kolom Q). Dit getal kan je natuurlijk zelf naar eigen inzicht aanpassen.

De formule in kolom N telt het aantal dagen, op basis van kolom K, bij de Datum in dienst om tot de Datum uit dienst te komen:
=IF((J11+(K11/8)*(7/5)/(Q11/173))=0;””;(J11+(K11/8)*(7/5)/(Q11/173)))
Eerst gaan we naar dagen door K11 door 8 uur te delen en dat vermenigvuldigen we met de “werkdagen factor” (Q11/173).

De kostprijs wordt berekend door het uurloon te vermenigvuldigen met een overheadsfactor van 1,8. De marge in kolom P is dan het verschil tussen het tarief in kolom M en de kostprijs in kolom O.

Voor het berekenen van de marge voor de werving en selectie activiteiten, wordt de wervingsfee in kolom H vermenigvuldigd met de FTE factor in kolom C. Bij  projecten is dat kolom P x kolom C.

Nu we op een eenduidige manier de marges hebben bepaald per consultant, bepalen we in de volgende stap de marges per maand.

Marges per maand per consultant

Voor elke regel gaan we per maand bepalen of daar marge geteld moet worden.

Dat doen we met onderstaande formule voor bijvoorbeeld de maand februari (U11):
=IFERROR(IF(AND($J11<V$10;$N11>U$10);MIN(($N11-U$10)/(U$10-T$10)*$R11;(V$10-$J11)/(U$10-T$10)*$R11;$R11);0);0)

Valt de marge binnen de betreffende maand (is datum in dienst kleiner dan de volgende maand en is de datum uit dienst groter dan de betreffende maand), dan wordt er daarna bepaald voor hoeveel dagen de marge geteld moet worden. Dit doet de formule door te kijken of de gehele maand marge moet worden meegenomen, een deel van de marge op basis van de datum in dienst, of een deel van de marge op basis van de datum uit dienst.

Om het deel van de marge te bepalen, kijkt de formule wat de kleinste waarde oplevert:
=IFERROR(IF(AND($J11<V$10;$N11>U$10);MIN(($N11-U$10)/(U$10-T$10)*$R11;(V$10-$J11)/(U$10-T$10)*$R11;$R11);0);0)

1. Is het verschil tussen de de eerste van de betreffende maand (1 feb) en de datum uit dienst, vermenigvuldigd met het gemiddelde marge van die maand het kleinst? ($N11-U$10)/(U$10-T$10)*$R11

2. Is het verschil tussen de eerste van de opvolgende maand (1 mrt) en de datum in dienst, gedeel door totaal aantal dagen in de in de maand, vermenigvuldigd met het gemiddelde marge van die maand het kleinst? (V$10-$J11)/(U$10-T$10)*$R11

3. Of is het gehele gemiddelde marge bedrag van die maand het kleinst? $R11

Door te zoeken naar de kleinste waarde kom je ‘als vanzelf’ uit of je het deel van de maand vanaf het begin van het contract moet berekenen of juist dat deel van de maand tot en met het einde contract.

Zodra het verschil aan dagen in de eerste twee gevallen boven het totaal aantal dagen in de maand uitkomt, zal dit zorgen voor een hoger berekend gemiddeld marge bedrag en vallen die daardoor af.

Voor U11 kom je dan uit op de volgende waarden:
1. ($N11-U$10)/(U$10-T$10) = 350,3/31)
($N11-U$10)/(U$10-T$10)*$R11 = (350,3/31 * 2582)

2. (V$10-$J11)/(U$10-T$10) = 41/31)
(V$10-$J11)/(U$10-T$10)*$R11 = (41/31 * 2582).

3. $R11 = 2582

Zoekend naar de kleinste waarde vallen de eerste twee opties af in  de vergelijking voor U11.

Marges per maand getotaliseerd

Als laatste stap willen we alle categorieën, Consultants in dienst, Werving en Selectie, ZZP’ers en Projecten, per maand getotaliseerd hebben.

Alles bij elkaar optellen is een mogelijkheid, maar getotaliseerd per categorie biedt natuurlijk het meeste inzicht. Door middel van een SUMIFS formule, kunnen we verschillende criteria opgeven voor het optellen.

Door aan elke categorie een ‘verborgen’ kenmerk mee te geven in kolom S, kan je datzelfde kenmerk weer gebruiken in de formule. Bij de consultants In Dienst zet je dan in witte letters in kolom S ‘detachering’. Op dezelfde manier ‘ws’ voor werving en selectie, ‘zzp’ voor de ZZP categorie en ‘project’ voor Projecten.

De formule voor marge detachering (T45) is: =SUMIFS(T$11:T$42;$S$11:$S$42;”detachering”)
Ik gebruik standaard een SUMIFS formule in plaats van een SUMIF, ook als ik zoals nu maar één criterium gebruik. Mocht het in de toekomst nodig zijn om een criterium toe te voegen, dan staat de formule al in het juiste volgorde format (die afwijkt van de SUMIF). Voor de andere categorieën maak je op dezelfde manier ook de SUMIFS.

Nu zie je precies wat je per categorie aan marges kan verwachten de komende maanden. En op basis van dit overzicht kan je weer nieuwe plannen maken voor de toekomst.

Succes!

Categorieën
Uncategorized

Maak het leven makkelijker met een macro in Excel

Als geslaagd uitbater van de beste broodjeszaak, weet je inmiddels waar je omzet mist. Met de vorige blog heb je namelijk je eigen overzicht gemaakt. Een dervingsdashboard met een heldere splitsing tussen het invoerwerkblad en het werkblad waar informatie wordt bewaard en samengevoegd. Je bent alleen nog elke keer de data handmatig van het ene werkblad naar het andere aan het kopiëren. Dat kan makkelijker.

Hieronder laat ik je zien hoe je met één druk op die knop, data van de ene sheet naar de andere kan laten kopiëren. Daar gebruiken we een macro voor. Dat scheelt veel tijd en voorkomt typefouten.

Wat is een macro?

Een macro is een stukje Visual Basic (VB) code die repetitieve handelingen op een makkelijker manier automatiseert. Zo’n macro kan je vastleggen door toetsaanslagen en muisklikken op te nemen. Microsoft vertaalt die opnamen vervolgens in Visual Basic code. Je kan ook gelijk Visual Basic code invoeren en dat is wat wij gaan doen. Dat klinkt misschien complex en lastig, maar met behulp van onderstaande stappen en voorbeeld code heb je het zo geregeld.

Instellingen

Allereerst selecteer je via Instellingen de juiste optie om gebruik te kunnen maken van macro’s. Sommige virussen maken gebruik van macro’s, dus altijd toestaan is niet verstandig en met nooit toestaan, weet je zeker dat je macro niet gaat werken. De gulden middenweg is niet toestaan met notificatie te selecteren. Met deze optie verschijnt er een keuzevenster zodra je een Excelbestand met macro’s wilt openen.

De juiste namen

De VB code verwijst naar de namen van de diverse werkbladen. Als de naam slechts afwijkt door een extra spatie, dan snapt de macro niet meer welk werkblad je bedoelt. Voor het gemak kan je hier  een vereenvoudige versie van het dervingsdashboard downloaden waar je zelf de macro aan kan toevoegen.

Gedefinieerde namen

Om Excel te vertellen welke velden gekopieerd moeten worden zijn er twee manieren. Als het slechts velden uit een tabel zijn, dan kan het via rijnummer en kolomnummer. Maar in ons voorbeeld hebben we ook een viertal velden boven de tabel staan.

Hier gebruiken we gedefinieerde namen voor. Ga naar de menuknop Formules en klik op gedefinieerde namen.

Elke van de vier velden geven we een unieke naam en koppelen we aan de juiste cel. In de VB code kunnen we nu deze veldnamen gebruiken en waardoor Excel exact weet welke cellen bedoeld worden.

Een button met een macro

Om de macro straks te activeren, gebruiken we een button. Download hier vast de code, dan kan je gelijk de macro aan de button verbinden.

Voeg via de menuknop Ontwikkelaar de button toe.

Pas de macronaam aan naar ‘Voerin’ en klik daarna op Nieuw.

 

Het Visual Basic venster verschijnt nu.

 

Kopieer de gehele inhoud uit de gedownloade tekstbestand en plak dat over de reeds aanwezige tekst in het rechter venster.

Klik op opslaan. Werkend vanuit het gedownloade Voorbeeld excel.xlsx, zal je een melding ontvangen dat macro’s niet kunnen opgeslagen. Klik op ‘niet opslaan’ om daarna het keuzevenster te krijgen. Selecteer het .xlsm bestandstype.

Als je nu weer naar het venster van het dervingsdashboard gaat, zie je een mooie macro button toegevoegd. De tekst ‘Button 1’ passen we aan door met ctr/control op de knop te klikken. Klik daarna even naast de knop, zodat de button weer actief wordt om de macro te activeren.

Wanneer je nu op de button klikt, zullen de velden naar het volgende werkblad gekopieerd worden!

De visual basic code uitgelegd

Het is natuurlijk fijn dat de macro werkt, maar wat doet die code nou (in grote lijnen)? Eerst terug naar het Visual Basic venster via de menuknop Ontwikkelaar en Visual Basic.

De macro gaat van boven naar beneden door de code heen. Daarom starten we met het declareren van de variabelen.  Dim i As Long: i = 9 geeft aan dat Excel op de 9e regel moet starten. Met de volgende regel verbinden we variabele i met de velden van het eerste werkblad, startend in kolom B. Variabele j gebruiken we voor de velden op het tweede werkblad. Om te voorkomen dat de header op het tweede werkblad wordt overschreven, gebruiken we de + 1 toevoeging bij Row + 1. Met de onderste twee regels declareren we de ‘van’ en ‘naar’ variabelen.

De volgende regels controleren of de velden met gedefinieerde namen gevuld zijn. Zo niet, dan verschijnt er een foutmelding.

Een werkblad kan beveiligd zijn, daarom wordt eerst een standaardprogramma aangeroepen die de beveiliging eraf haalt.

Daarna volgt een loopje dat start wanneer cel B9 gevuld is en eindigt wanneer er geen data meer staat in kolom B. Elke veldnaam van het eerste werkblad wordt gekoppeld aan een veldnaam van het tweede werkblad. De cijfers refereren hier naar de kolom. In de eerste vier regels zie je ook de eerder gedefinieerde velden zoals str_naam.

Als laatste stap beveiligt de macro het tweede werkblad. Niet per se nodig, maar wel makkelijk zodat je kan zien dat de macro zijn werk heeft gedaan.

Door eenmalig wat tijd te investeren in een macro hoef je voor dit dashboard nooit meer handmatig informatie te kopiëren en plakken. En zo creëer je een fijne workflow, die veel minder tijd kost en de kwaliteit van de data waarborgt.

Succes!

Categorieën
Uncategorized

Inzicht in derving met Excel formules en een draaitabel

Als succesvolle horeca ondernemer maak jij zulke lekkere broodjes, dat mensen helemaal van de andere kant van de stad naar jouw zaak komen. Het gaat goed, maar toch heb je het gevoel dat je af en toe wat omzet mist. Je weet alleen niet waar en wanneer.

In drie eenvoudige stappen laat ik je zien hoe je met Excel inzicht krijgt in wat je aan broodjes mist en hoe dat effect heeft op je winst.

Stap 1: Maak elke dag een inventarisatie

Maak in Excel een invoertab waarin je dit gemakkelijk kan invullen zoals in onderstaand voorbeeld. Vanaf B8 tot en met G28 voer je zelf handmatig in elke dag. In kolommen C, D en E de verkopen en in kolom G wat er aan het eind van de dag nog over is.

Met behulp van directe verwijzing halen we de verkoop- en inkoopprijzen in kolom H en I uit tabblad Stuurgegevens erbij.

In tabblad Stuurgegevens staan ook de naam van de medewerkers, die we dan op het eerste tabblad met een dropdown menu kunnen selecteren.

Door eenmalig de prijzen in een apart tabblad te vullen, bespaar je tijd en verminder de kans op typefouten.

Om de kans op foutieve invoer te verminderen, kan je een aantal datumcontroles uitvoeren. Met als uitgangspunt dat elke medewerker maximaal één inventarisatie uitvoert per dag, zoek je in I6 de laatst ingevoerde datum van de betreffende medewerker op in het tabblad DATA. Dit is dan de formule:
=IF(DATA!E4<>””;MAX(IF(DATA!$B$4:$B$5001=$C$3;DATA!$E$4:$E$5001));””)

In E4 en E5 check je op chronologie.
E4: =IF(I6=””;””;IF(C4<=$I$6;”Let op: start ligt op of voor laatst gevonden datum”;””))
E5: =IF(C5<C4;”Einddatum ligt voor startdatum”;””)

Het unieke serienummer in C6 hebben we nodig om eventueel vorig saldo te bepalen. Broodjes die nog over zijn van de vorige dag en goed van kwaliteit zijn om te verkopen. Met de formule in E6 bepalen we het hoogste serienummer van de betreffende medewerker: =MAX(IF(DATA!$B$4:$B$5001=$C$3;DATA!$C$4:$C$5001))

In C6 tellen we daar simpelweg eentje bij op om het uniek te maken.

Stap 2: Verzamel alle gegevens:

 Met de macroknop ‘Sla gegevens op’, worden de gegevens naar het tabblad DATA gekopieerd.

Gelijk verschijnt een datum in I6. Het serie nummer in C6 is verhoogd naar 2 en in E4 staat een waarschuwing.

Op het tabblad DATA zijn de kolommen B tot en met M gevuld via de macro.

Om de blog overzichtelijk te houden, zal ik in de volgende blog het Macro gedeelte uitleggen. Voor nu kan je handmatig de data uit de invoer tab kopiëren om hetzelfde resultaat te bereiken.

Tot en met kolom M staat de handmatig invoer en vanaf kolom O de berekeningen. In kolom O tel je de verkopen over kassa 1 tot en met 3.

In kolom P check je of er nog iets over is van vorige keer. Hier neem je het huidige serienummer en haal je er eentje vanaf om het saldo (kolom S) te bepalen van het betreffende product (en opgenomen door de dezelfde medewerker):
=IF(B4=””;””;IFERROR(INDEX($S$4:$S$5000;MATCH($B4&$C4-1&$F4;$B$4:$B$5001&$C$4:$C$5001&$F$4:$F$5001;0));0))

In kolom Q bereken je wat je verwacht in de ideale situatie zonder derving. Je start met de verkopen als min getal en telt daar de inkopen en het saldo van vorige keer bij op.

In kolom R maak je de vergelijking tussen wat je geïnventariseerd hebt en wat je verwacht.

In kolom S staat een directe verwijzing naar kolom K ter informatie als we in de toekomst ‘Saldo vorige’ willen bepalen.

De omzet in kolom T bereken je door de verkoopprijs in kolom L te vermenigvuldigen met het aantal verkopen in kolom O. De inkoop bereken je door kolom J te vermenigvuldigen met kolom M. Voor derving vermenigvuldig je de inkoopprijs in kolom M met kolom R. Voor de winstbepaling haal je in kolom W de inkoop (U) en derving (V) van de omzet (T) af.

Stap 3: Maak een draaitabel

Met één ingevoerde dag is de derving nog redelijk overzichtelijk maar het liefst wil je dag op dag alles getotaliseerd zien. Een draaitabel biedt de perfecte oplossing.

Selecteer vanaf regel 3 kolom O tot en met W. Klik via Invoegen, op draaitabel.

Klik op het volgende venster op OK, om de draaitabel op een nieuw tabblad te creëren.

Sleep de velden Naam en Startdatum naar ‘rijen’ in het venster aan de rechterkant. Vervolgens sleep je de velden Omzet, Inkoop, Derving en Winst naar ‘waarden’.

In de omschrijving van de velden bij ‘Waarden’ staat hoe er nu getotaliseerd wordt. Dit moet op Som gedaan worden. Met de rechtermuisknop kan je dit aanpassen.

Dat resulteert in het volgende overzicht.

Als laatste stap kan je op het linker pijltje klikken en alleen ‘Dirk’ selecteren in de filter.

Wanneer je morgen een nieuwe dag invoert, ververs je de draaitabel met de laatste data door met rechtermuisknop in de draaitabel te klikken en op verversen te klikken. Nu heb je per dag inzicht in je belangrijkste cijfers.

Met een draaitabel kan je natuurlijk nog veel meer. Wil je na een aantal dagen weten welke producten de meeste derving hebben, dan haal je het veld ‘Startdatum’ uit de rijen en voeg je het veld ‘Product’ toe. Uit de waarden sleep  je verder nog Omzet, Inkoop en Winst weer terug naar boven zodat alleen Derving overblijft.

Nu weet je precies waar op welke broodjes je actie kan ondernemen om derving te verminderen.

Succes!

Categorieën
Uncategorized

Meer leads uit je Telesales met Excel? Twee gouden tips

Telesales medewerer

Als teamlead Telesales geef je graag je (interne) klanten inzicht in hoe effectief er gebeld wordt. Hoeveel leads levert een campagne eigenlijk op en in welke regio’s? Voor jezelf wil je weten wat er op de verschillende dagen gepresteerd wordt en door wie. Zo kan jij bepalen welke toppresteerder de minder presterende medewerker verder kan trainen.

Telesales dashboardMet behulp van het bovenstaande voorbeeld dashboard laat ik je zien hoe je met twee dashboard tips het verschil kan maken. Door het gebruik van een formule om de effectiviteit te bepalen en het toepassen van voorwaardelijke opmaak.

1. Effectiviteitsformule op basis van gemiddelde

Om de effectiviteit te bepalen zijn er vele manieren. Een vergelijking met concrete targets is een van de mogelijkheden. In dit dashboard kijk ik met behulp van een formule of er boven of onder het gemiddelde gepresteerd wordt.

De gebruikte formule van de vervolggesprekken (binnen campagnetijd) in Q39 is:

=IF(E39=””;””;O39-AVERAGE($O$39:$O$44))

Als het totaal aantal gesprekken voor Noord-West (E39), ongelijk is aan ” “, dan halen we van het aantal vervolggesprekken binnen campagne voor Noord-West (O39), het gemiddelde van alle vervolggesprekken binnen de campagne (O39:O44) af.

In cijfers is dat 3 – 1,167 = 1,83  Afgerond 2. Bovengemiddeld dus.

Effectiviteitsformule

Op dezelfde manier bereken ik ook de performance van de calls (bijv J39) en de gemiddelde duur (Y39) binnen campagne.

Voor de performance van de vervolggesprekken in alle campagnes (bijv U39), gebruik ik:
=IFERROR(S39-AVERAGE($S$39:$S$44);0)
Mocht er een error ontstaan, dan plaatsen we een 0.

2. Voorwaardelijke opmaak

Om makkelijk waarde toe te kennen aan de cijfers gebruik ik voorwaardelijke opmaak. Alles wat boven de nul is – bovengemiddeld – moet groen worden. Alles wat lager dan nul is – benedengemiddeld – moet rood worden.

Aangezien we bovenstaande twee ‘regels’ voor alle Performance cijfers gelden, selecteren we in één keer alle betreffende cellen.

Selecteren kolommen

Klik op Voorwaardelijke opmaak, Markeringsregels voor cellen, Groter dan.Voorwaardelijke opmaak kleur groenIn het volgende venster kan je de regel specificeren. Groter dan nul moet een groene tekst krijgen. Die kleur is niet direct kiesbaar, maar wel via Customised Format.

Voorwaardelijke opmaak kleur groen

Klik op Ok en alles boven de nul wordt nu groen. Met de selectie nog steeds in tact, doen we hetzelfde, maar kiezen dan voor Kleiner dan. We vullen weer de nul in en kiezen rood als tekstkleur.

Resultaat voorwaardelijke opmaak

Op een hoger niveau

Als laatste wil ik alleen nog weten wanneer we op alle onderdelen boven of onder het gemiddelde scoren. Voorwaarde opmaak hoger niveauBijvoorbeeld op dagniveau. Allereerst maak ik weer een formule aan om een numerieke waarde te genereren voor cel B26.

De formule: =IF(E26=””;””;IF(AND(J26<0;Q26<0;Y26<0);1;IF(AND(J26>0;Q26>0;Y26>0);2;””)))

Als E26 ongelijk is aan ” ” dan geef ik een 1 als de betreffende cellen allemaal kleiner zijn dan 0. Als alle betreffende cellen groter zijn, dan geef ik een 2. In alle andere gevallen laat ik de cel leeg (” “). Deze formule kopieer ik naar de cellen B12 tot en met B48.

Op basis van de uitkomst van de formule, creëer ik via voorwaardelijke opmaak een groen of rood bolletje. Een groen bolletje bij groter of gelijk aan 2. Een rood bolletje wanneer de waarde kleiner is dan 2 maar groter of gelijk aan 0.

Wanneer er niet eenduidig bovengemiddeld of benedengemiddeld wordt gescoord, wil ik geen bolletje.

Selecteer eerst weer de range, B12 tot en met B48.

Klik op Voorwaardelijke opmaak, Pictogramseries, Meer regels.

Voorwaardelijke opmaak pictogrammen

Voorwaardelijke opmaak specificeren

Samengevat, hebben we door middel van voorwaardelijke opmaak betekenis gegeven aan de cijfers. Nu hebben we op verschillende niveaus direct inzicht in waar er boven en onder gemiddeld gescoord wordt.

Telesales dashboard

Vervolgacties kunnen ondernomen worden. Bijvoorbeeld onderzoeken of de gemiddelde gespreksduur (binnen campagne) in Noord-Oost als voorbeeld kan dienen voor de rest van de regio’s.

Succes!

 

Categorieën
Uncategorized

Snel en foutloos factureren in Excel – Hoe doe je dat?

Facturatie is voor de meeste ZZP’ers een haat-liefde verhouding. Het is heerlijk om met je product een lach op het gezicht van je klant te toveren. Daar doe je het tenslotte voor. Aan het eind van de maand is het tijd om de facturen de deur uit te doen. De schoorsteen moet roken! Dat doe je wel even in een ochtendje. Maar vlak voor lunchtijd zit je pas op de helft en zie je langzaam ook de middag opgaan aan dit ‘administratief klusje’. Dat moet toch anders kunnen. Inderdaad, met Excel kan dat makkelijker én sneller.

Facturatievoorbeeld

Met behulp van het bovenstaande facturatievoorbeeld laat ik je zien hoe je met dropdowns en formules veel sneller klaar bent en bovendien de kans op fouten op je factuur aanzienlijk vermindert.

Koppelen klantenbestand

Allereerst koppel ik het klantenbestand aan een keuzeveld. Met een dropdown menu kies je uit een voorgedefineerde selectie.

In dit geval de klantnamen uit het klantenbestand. Door de klantnamen te koppelen aan het dropdown menu sluipt er nooit meer een spelfout in. Voorwaarde is dan wel een eenmalige correcte invoer in het klantenbestand.

Het koppelen van het dropdown menu met het klantenbestand is heel eenvoudig. Staand op cel H7, klik onder Data op Gegevensvalidatie.

Dan selecteer je het bereik door rechts bij Bron op het pictogrammetje te klikken (zie hieronder). Ik klik dan op de betreffende KLANTDATA tab en selecteer B7 tot en met B100 te selecteren (opnieuw aan te passen als je klantenbestand groter wordt dan regel 100).

De beste zoekformule

Met de koppeling van het klantenbestand op orde zijn we klaar voor de volgende stap.

Op basis van de geselecteerde klantnaam haal ik met formules de rest van de klantgegevens erbij. Zoals de adresgegevens en het BTW percentage. Ik gebruik hiervoor de INDEX en MATCH formule.

Deze heeft twee grote voordelen in vergelijking met de populaire VLOOKUP formule:
1. INDEX MATCH is sneller, zeker als je zoekt in veel data.
2. INDEX MATCH is flexibeler. Bij het invoegen van een kolom in het zoekbereik, ben je verplicht om je VLOOKUP aan te passen. Je geeft namelijk een harde verwijzing uit welke kolom je een waarde terug wilt hebben. Hier heb je met INDEX MATCH geen last van.

Om de cel H8 (T.a.v.) op te zoeken gebruik ik: =(INDEX(KLANTDATA!D:D;MATCH($H$7;KLANTDATA!B:B))

De MATCH formule

Laten we het laatste stukje van die formule bekijken, de MATCH formule: =MATCH($H$7;KLANTDATA!B:B)
Als ik met de waarde van H7 (‘De Contentspecialist’) zoek in kolom B van het klantenbestand, dan vind ik een match in rij 9. De uitkomst van deze formule is 9.

De INDEX formule

Als ik vervolgens met die uitkomst, waarde 9, zoek in kolom D met de INDEX formule: =(INDEX(KLANTDATA!D:D;9)) dan vind ik de waarde Dhr. Krijgsman.

Om het variabel te houden, zet ik in de INDEX formule, op de plek van de waarde 9, de MATCH formule. Voor de volledigheid voeg ik nog een foutafhandeling toe. Mocht er een error ontstaan, dan geeft de formule een spatie terug: =IFERROR(INDEX(KLANTDATA!D:D;MATCH($H$7;KLANTDATA!B:B));””)

Om de flexibiliteit van de formule te testen, voeg ik in het klantenbestand een kolom toe vóór kolom D.

De formule verandert netjes automatisch mee!

Op dezelfde manier vul ik de rest van de velden. Om adres en huisnummer samen te voegen, gebruik ik twee INDEX MATCH formules achter elkaar en plaats ik &” “& ertussen.

Voor partno (artikelnummer) in cel X7, gebruik ik weer een dropdown menu dat gekoppeld is aan de tab met artikelen. Voor de resterende velden gebruik ik weer INDEX MATCH.

Factuurgegevens

Om de factuurgegevens te genereren, gebruik ik ook formules.

Factuurnummer: =YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Opgeknipt in stukjes.
Huidige jaar:
=YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Huidige maand, bij de eerste 9 maanden wil ik een nul voorafgaand:
=YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Uit het factuuroverzicht pak ik het hoogste nummer, alleen kijkend naar de laatste vier cijfers (kolom Q) en tel ik daar 1 bij op voor een uniek nummer:
=YEAR(TODAY())&IF(MONTH(TODAY())<10;”0″&MONTH(TODAY());MONTH(TODAY()))&”-“&MAX(Factuuroverzicht!Q:Q)+1

Voor de factuurdatum gebruik ik de datum van vandaag: =TODAY()
Voor de vervaldatum tel ik 30 dagen op bij de factuurdatum: =AN8+30

Als laatste stap ga ik de opgehaalde informatie op de factuur zetten. Dat doe ik door middel van celverwijzing. Bijvoorbeeld in cel C18 zet ik =H7.

De voetnoot bouw ik op met behulp van bovenstaande technieken.

Met een druk op de Visual Basic knop voeg regel toe zet ik een artikel op de factuur. Om een factuurregel in het factuuroverzicht te zetten, gebruik ik de Visual Basic knop maak factuur, pakbon. Een volgende keer vertel ik meer over Visual Basic.

Zonder Visual Basic selecteer je het factuurgebied en print je de factuur naar PDF. Klaar om digitaal te versturen.

Met het gebruik van dropdown menu’s en een paar simpele formules hoef je niets meer handmatig in te tikken door alleen broninformatie te gebruiken. Is dat administratief klusje af voordat je het weet en met betrouwbare broninformatie heb je gegarandeerd correcte facturen.

Succes!

 

 

Categorieën
Uncategorized

HR performance verbeteren – hoe doe je dat met Excel?

Je bent een succesvolle HR manager met verschillende regio’s onder je hoede. Onder de maat presterende regio’s is jouw volgende actiepunt. Het verzuim verlagen, het verloop verminderen, het opleidingsniveau verbeteren en de wervingsefficiëntie verhogen.

Waar te beginnen? Je hebt gelukkig wel de data beschikbaar. Dat is al een hele stap. Maar hoe kom je nou van die data naar actiegerichte inzichten?

In deze blog laat ik je zien hoe je met een paar simpele stappen de juiste inzichten krijgt.  We nemen verzuim als voorbeeld. Visualisatie is cruciaal. Daar heeft Excel mooie grafieken voor beschikbaar.

Ons einddoel is een inzichtelijk dashboard te gebruiken bij het volgende management overleg:HR dashboard

Stap 1: Data voorbereiden

We scheiden de brondata van de grafiek data door middel van celverwijzingen.Brondata en Grafiek dataDus in cel K4 verwijzen we naar cel B4 met ‘de formule’ =B4
Dat doen we ook voor de rest van de cellen. In kolom Q voeg ik nog extra data toe die niet uit het bronsysteem komt. De branchecijfers. Door middel van de celverwijzing kunnen nieuwe periodes op dezelfde manier verwerkt worden. Kopiëren en plakken over de oude brondata en voilà bijgewerkte grafieken! Even opslaan onder een nieuwe naam natuurlijk.

Stap 2: Grafieken maken

Selecteer de grafiek data (J3 tm Q8).

Selecteer een 2D kolom grafiek.
2D kolom grafiekZelf heb ik voor stijl 6 gekozen.
Grafiek stijl 6

Stap 3: Grafiek finetunen

Nu hebben we met de basis lay-out een mooi begin, maar nog niet precies wat we willen hebben.
Basis grafiek in ExcelWe willen graag per leeftijdsklasse de verschillend regio’s naast elkaar hebben. Klik hiervoor op verwissel rij/kolom.
Wissel rij/kolomDe grafiekkop typen we zelf wel in Excel. Door Chart title aan te klikken, kan je die met een druk op de delete knop, verwijderen. Hetzelfde geldt voor de legenda onder de kolommen en de waarden van de Y-as. Verder maak ik de grafiek wat minder hoog.
Excel grafiekOm de verzuimpercentages in de kolommen te zien, klikken we op Toevoegen grafiek element. Via diezelfde knop kunnen we onder Gridlines, de lijnen uitzetten. Toevoegen grafiek elementen

Als we de tekst van de percentages wit willen, dubbelklikken we in de grafiek. Rechtsboven komt dan een venster naar voren waar we de tekstkleur kunnen aanpassen.Formatteren grafiektekst

Jammer alleen dat gelijk ook het X-as label wit wordt. Via het format menu van de grafieken kan je links de horizontale as selecteren. Je kan je daarna zoals als hierboven beschreven de tekstkleur van het X-as label aanpassen naar bijvoorbeeld donkergrijs.
selecteren grafiekonderdeelOm de grootte van het lettertype aan te passen gebruik je de gebruikelijke formattools.aanpassen lettertype in grafiek

Dan hebben we nu alleen nog de branche cijfers in een aparte kolom. Daar willen we een gestreepte lijn van maken per regio.
excel grafiekDubbelklik op de laatste kolom. Aan de rechterkant verschijnt weer een venster. Pas de serie aan naar de tweede as met een afstand grootte van 10%.

aanpassen dataseries

Klik vervolgens op de verfemmer en zet de vulling en de border zoals hier rechts. Bij Dashtype kies ik de vierde van boven.selecteren dash type

Dat resulteert in de volgende grafiek:
excel grafiekMet de waarden van de branche geselecteerd, door op de stippellijn te klikken, voegen we weer via Toevoegen Grafiek element een datalabel toe (buiten de kolom). Selecteer de branchewaarden en maak via de format tools de fontkleur zwart. Hiermee verschijnen de waarden weer.

Voor de duidelijkheid vullen we de achtergrond van de branchewaarden met wit en maken we het af met een zwarte grensrand.
achtergrond vullen en grensrand zwart maken

 

Als laatste stap zetten we de grafiek netjes op een apart tabblad.
apart excel tabblad

Met behulp van bovenstaande stappen ben je in staat om ook een Verzuim per duurklasse grafiek maken.
HR dashboard

Nu we in één oogopslag zien waar de uitschieters zitten, kan als vervolgstap een deepdive per regio en per leeftijdsklasse gemaakt worden. Bijvoorbeeld in regio Midden, langer dan een jaar, 56 jaar en ouder.

Succes!

Categorieën
Uncategorized

Een Project Dashboard Maken in Excel – deel 3/3

In het tweede blog heb je de visualisatie verrijkt met de status van een project: hoeveel dagen zijn er nog te gaan in een project, is een project al voltooid of is de deadline overschreden en zo ja, hoeveel dagen al?

Als opfrisser zie je hieronder wat je hebt gemaakt.

pict1

In dit laatste blog ga je het dashboard gebruiksklaar maken. Je gaat de slicer vervangen met een tijdlijn, visuele indicators toevoegen voor taken die te laat zijn en je voegt een controle notificatie toe voor wanneer het model geüpdatet moet worden.

Met het bestand van het vorige blog (link invoegen) kun je alle stappen meedoen. Je kunt het volledige bestand van dit deel ook hier downloaden (link invoegen).

Datumbereik Aanpassen

Voor dit deel passen we de dataset weer aan. We voegen er nog meer projecten aan toe en verlengen de planning tot in februari 2021. Zie onderstaande dataset:

Projectnaam Taakbeschrijving Startdatum Einddatum Voltooid
Project 1 Taak 1 15-Dec-20 24-Dec-20 WAAR
Project 1 Taak 2 18-Dec-20 31-Dec-20 WAAR
Project 1 Taak 3 20-Dec-20 27-Dec-20 ONWAAR
Project 2 Taak 1 23-Dec-20 01-Jan-21 ONWAAR
Project 2 Taak 2 27-Dec-20 10-Jan-21 ONWAAR
Project 3 Taak 1 30-Dec-20 07-Jan-21 ONWAAR
Project 3 Taak 2 31-Dec-20 07-Jan-21 ONWAAR
Project 3 Taak 3 04-Jan-21 14-Jan-21 ONWAAR
Project 3 Taak 4 04-Jan-21 18-Jan-21 ONWAAR
Project 4 Taak 1 05-Jan-21 09-Jan-21 ONWAAR
Project 4 Taak 2 06-Jan-21 19-Jan-21 ONWAAR
Project 4 Taak 1 09-Jan-21 17-Jan-21 ONWAAR
Project 4 Taak 2 10-Jan-21 16-Jan-21 ONWAAR
Project 5 Taak 1 10-Jan-21 24-Jan-21 ONWAAR
Project 5 Taak 2 13-Jan-21 21-Jan-21 ONWAAR
Project 6 Taak 1 13-Jan-21 22-Jan-21 ONWAAR
Project 6 Taak 2 13-Jan-21 22-Jan-21 ONWAAR
Project 6 Taak 3 16-Jan-21 21-Jan-21 ONWAAR
Project 6 Taak 4 18-Jan-21 28-Jan-21 ONWAAR
Project 7 Taak 1 22-Jan-21 04-Feb-21 ONWAAR
Project 7 Taak 2 26-Jan-21 05-Feb-21 ONWAAR
Project 7 Taak 3 27-Jan-21 06-Feb-21 ONWAAR
Project 7 Taak 4 29-Jan-21 03-Feb-21 ONWAAR
Project 7 Taak 5 01-Feb-21 07-Feb-21 ONWAAR
Project 7 Taak 6 03-Feb-21 16-Feb-21 ONWAAR
Project 8 Taak 1 03-Feb-21 12-Feb-21 ONWAAR
Project 9 Taak 1 06-Feb-21 13-Feb-21 ONWAAR
Project 9 Taak 2 10-Feb-21 22-Feb-21 ONWAAR
Project 9 Taak 3 10-Feb-21 23-Feb-21 ONWAAR

Wanner je het gegevens model ververst zie je dat het dashboard niet ververst lijkt het:

pict2

Je ziet alleen de taken die in 2020 zijn gestart. Hoe zou dit kunnen?

Weet je nog dat het projectoverzicht uit een Power Pivot draaitabel bestaat? En in Power Pivot heb je een Agenda tabel gemaakt om de tijdslijn in de draaitabel te creëren. Laten we in de Power Pivot editor openen en die tabel eens inspecteren.

Agenda Tabel Updaten

Open de Power Pivot editor via Power Pivot > Beheren en ga naar de Agenda tabel. Sorteer het eerste veld van boven naar beneden zodat de grootste datum in de eerste regel komt.

pict3

Hier zie je het probleem: de Agenda tabel loopt maar tot eind 2020! Geen nood, dit kunnen we makkelijk aanpassen. In de Power Pivot editor kun je via Ontwerpen > Gegevenstabel > Bereik bijwerken:

pict4

Pas het bereik aan naar 31-Dec-2021 en druk op OK.

pict5

Draaitabel Updaten

Ga terug naar het dashboard en ververs de draaitabel. Nog steeds zie je projecten in 2021! Zie jij wat er aan de hand is (hint: zie de arcering)?

pict6

Inderdaad, de draaitabel toont in de kolommen alleen de weeknummers en niet de jaren. Je moet het jaartal dus in het kolommenbereik van de draaitabel toevoegen.

pict7

Merk op dat de jaartallen niet leesbaar zijn in het kolommenbereik. Dit komt omdat je de kolombreedte hebt aangepast. Nu zie je alleen ‘##’, maar dat maakt niet zo veel uit. Je kunt deze regel, en die erboven gewoon verbergen.

Selecteer de twee bovenste regels van de draaitabel, ga met je muis naar de rijkoppen en kies in het rechtermuisklikmenu voor Verbergen:

pict8

Een Moment van Reflectie

Voordat je verdergaat moeten we even terug naar wat we net gedaan hebben. We hebben het model aan moeten passen omdat we in het nieuwe jaar zitten. Hiervoor moest het bereik van de Agenda tabel handmatig aangepast worden.

Als het goed is, is er al een belletje bij je gaan rinkelen bij het lezen van de term ‘handmatige aangepast’. Inderdaad, je bent bezig een slim Excel model te maken en daarin hoort in principe geen handmatige update thuis.

Je hebt nu een design-keuze te maken. Je kunt de Agenda tabel vervangen met een query die afhankelijk van de Projecten tabel het juiste datumbereik teruggeeft. Maar hiervoor moet je a) de juiste kennis hebben van Power Query en b) de tijd hebben om het te bouwen.

Verder moet je nagaan of deze aanpassing het waard is. Strikt genomen is de frequentie van de update 1 X per jaar (mits je het bereik van de Agenda tabel telkens met 1 jaar verhoogt). Dat kun je prima handmatig doen.

Maar wat als iemand anders jouw model gaat gebruiken? Weet hij/zij dan wel hoe je de aanpassing moet doen? Je kunt een kleine werkinstructie schrijven. Maar je collega moet die instructie dan wel makkelijk kunnen vinden en erop gewezen worden dat er überhaupt één bestaat.

Er is ook een andere manier. Je gaat een ‘controle’ inbouwen in je model. Deze gaat vaststellen of het bereik van de Agenda tabel wel toereikend is. Zo niet, dan stelt de controle de gebruiker op de hoogte dat de Agenda tabel moet worden geüpdatet.

Datumbereik Controle Bouwen

Voeg een nieuw tabblad toe en hernoem het ‘Controles’. Voeg er een Power Pivot draaitabel aan toe en sleep het veld Date   de Agenda tabel in het waarden van de draaitabel. Je ziet nu een draaitabel met de ‘Telling van Date’. Pas de samenvatting van Date aan naar Max via het rechtermuisklikmenu:

pict9

Als het goed is zie je nu de laatste dag van 2021. Dat is de datum die we moeten controleren. Dat ga je doen via een formule. Maar eerst ga je de draaitabel converteren naar een Power Pivot formule.

Selecteer de draaitabel en ga via Draaitabel analyseren > OLAP > Converteren naar formules. Je zie dat de opmaak van je draaitabel nu is verdwenen. Ook zie je dat de kolomkop en het waarden bereik zijn vervangen voor zgn. KUBUS formules:

pict10

Je ziet dat de KUBUSWAARDE formule in cel B3 naar de cel erboven verwijst. Vervang deze verwijzing voor de formule uit B2. De formule in B2 kun je nu verwijderen:

pict11

Nu heb je in één cel de maximale datum uit de Agenda tabel uit het Power Pivot gegevensmodel. De laatste stap is om de controle te schrijven. Je wilt weten of de maximale datum uit Agenda groter of gelijk is aan de maximale Einddatum uit de Projecten tabel.

Pas de formule aan naar:

pict12

Voeg een naam aan de controle toe in de kolom ervoor, plaats er kolomkoppen boven en een beschrijving van wat er moet gebeuren als de controle faalt:

pict13

Om de controle helemaal af te maken kun je voorwaardelijke opmaak toevoegen aan de waarde van de controle (rood als de controle faalt; als de waarde ONWAAR is):

pict14

En als je helemaal ambitieus bent kun je de beschrijving van de te ondernemen actie ook dynamisch maken:

pict15

Laten we de controle controleren. Pas het bereik van de Agenda tabel weer aan naar 31-Dec-2020. Als het goed is zie je dat de controle nu ONWAAR teruggeeft:

pict16

De controle werkt, de actie is beschreven, maar de gebruiker wordt niet op de hoogte gesteld wanneer buiten het Controle tabblad. De allerlaatste stap is dus om een notificatie te bouwen in het dashboard zelf.

Gelukkig kun je dit met een simpele ALS formule. Voeg de volgende formule toe in cel B1 boven de titel van je dashboard en pas de tekstopmaak aan naar rood.

pict17

Om je notificatie te testen kun je het datumbereik van de Agenda tabel weer aanpassen in Power Pivot. En voilà, de notificatie is weg!

pict18 Je zult misschien denken: “Dit was wel heel omslachtig David. Had je me niet beter een dynamische agenda tabel kunnen maken?”. Daar zit wat in, maar bedenk dat het hebben van controle formules in je Excel model altijd goed van pas komt.

Nu weet je hoe je een controle kunt bouwen mét notificatie. Zo maak je een model een stuk gebruiksvriendelijker én is het beter te onderhouden. Als kers op de taart heb je ook geleerd hoe je een Power Pivot draaitabel naar formules kunt converteren, wat echt een gave truc is ?

Tijdlijn Invoegen

Tijd om de slicer in het dahboard te vervangen met een tijdlijn. De tijdlijn is alleen beschikbaar wanneer je een Agenda tabel in je gegevensmodel hebt. Gelukkig heb je dat al. Verwijder ten eerste de slicer van het dashboard. Let erop dat je de eventuele selectie filters verwijdert anders blijven ze ‘hangen’ in je draaitabel(len) en moet je ze daar uitschakelen.

Selecteer vervolgens je de draaitabel en ga via ‘Draaitabel analyseren’ naar ‘Tijdlijn invoegen’. Onderstaand venster verschijnt. Kies vervolgens voor het Date veld van je Agenda tabel.

pict19

Zoals je waarschijnlijk al hebt begrepen, kun je met een tijdlijn alleen filteren op een datum-veld. Anders dan een gewone slicer kan een tijdlijn alleen aaneengesloten items selecteren. Ook bevat de tijdlijn andere opties dan de gewone slicer.

Zo kun je bijvoorbeeld de tijdseenheid aanpassen. Je kunt kiezen tussen Jaar, Quarter, Maand en Dag. Jammer dat je niet voor Week kunt kiezen, maar maand is goed genoeg voor ons dashboard.

pict20

Plaats de tijdlijn tussen de titel en de draaitabel. Pas de hoogte van rij 3 aan zodat die er goed tussen past. Verplaats de draaitabel van cel D4 naar B4 zodat de titel, de tijdlijn en de draaitabel netjes onder elkaar staan:

pict21

De Stijl Aanpassen

De tijdlijn is nog iets te druk naar mijn mening. Selecteer de tijdlijn en ga via het menu ‘Tijdlijn’ helemaal naar rechts en schakel de Kop en het Tijdsniveau uit. Zo is de tijdlijn al wat netter. Merk overigens op dat de schuifbalk in onderstaande screenshot nog wel aan staat, maar wel is verdwenen als gevolg van de afmetingen die ik de tijdlijn heb gegeven.

pict22

Ik ben nog steeds van mening dat het beter kan; er zit nog steeds een lelijke rand om de tijdlijn. Om deze te verwijderen moet je de stijl van de tijdlijn aanpassen. In hetzelfde menu kun je de geselecteerde tijdlijnstijl aanpassen via het rechtermuisklikmenu.

pict23

Merk op dat de optie ‘Wijzigen’ is uitgeschakeld. De standaard stijlen van een tijdlijn kun je namelijk niet zomaar aanpassen, Dit geldt overigens ook voor slicers, draaitabellen en gewone tabellen. Wat je moet doen is de stijl dupliceren naar een ‘Aangepaste stijl’.

Kies dus voor Dupliceren. In het venster dat verschijnt selecteer je de bovenste regel ‘Volledige tijdlijn’ en druk je op Opmaak. Het standaard opmaak venster verschijnt dan. Hier ga je naar tabblad Rand en schakel je de randen helemaal uit.

pict24

Nadat je in alle open vensters op OK hebt gedrukt heeft je tijdlijn nog steeds die lelijke rand. Wat is hier aan de hand? Je hebt zojuist een nieuwe stijl gedefinieerd, alleen heeft Excel deze nieuwe stijl nog niet toegepast op de geselecteerde tijdlijn. Een puntje voor verbetering Microsoft…

Als je de ‘Aangepast stijl’ dit keer handmatig toepast zie je dat de rand weg is:

pict25

Indicators Toevoegen

Je projectoverzicht ziet er netjes uit. Je ziet welke projecten er in een zelf te selecteren periode allemaal actief zijn/waren. Je ziet welke taken er onder een project vallen en je kunt de status van de taak herleiden van de kleurcodering.

Hoewel de rode balken heel goed in het oog springen, kost het nog steeds moeite om te zien op welke taak deze betrekking heeft. Ook zie je niet in één oogopslag welke taken allemaal over de deadline zijn.

Dit ga je oplossen door rode indicator ‘lampjes’ voor de taakomschrijvingen te plaatsen met behulp van formules. Je gaat de indicator lampjes ook interactief maken, zodat de gebruiker gemakkelijk de onderliggende waarde kan aanpassen in de kolom Projecten[Afgerond] op tabblad Invoer.

Berekende Kolommen Invoegen

Voordat je de indicators kunt maken moet je de Projecten tabel verrijken met een aantal extra gegevens. Ten eerste moet je per taak afleiden of deze over de deadline is of niet. Voeg daarom een nieuwe kolom toe aan de tabel en noem die ‘Te laat’ met onderstaande formule:

pict26

Verder moet je een taak snel kunnen opzoeken in de Projecten tabel. En dat is momenteel nog niet zo makkelijk omdat er duplicaten aanwezig zijn in kolom [Taakbeschrijving]. Het is daarom handig om een unieke sleutel te berekenen. Voeg hiertoe een nieuwe kolom toe met de naam ‘ID’ en onderstaande formule:

pict27

Tot slot wil je straks in één keer naar de juiste cel springen in kolom Voltooid. Hiervoor heb je per regel het externe adres nodig. Voeg nogmaals een kolom toe, genaamd Adres, en gebruik onderstaande formule:

pict28

De Rapportindeling Aanpassen

Aangezien de sleutel een combinatie is van de projectnaam en de taakomschrijving, moet je deze ook in het dashboard voorhanden hebben. De compacte rapportindeling van de draaitabel leent zich hier niet goed voor; zowel de projectnaam als de taakomschrijving staan in dezelfde kolom. Het zou veel handiger zijn om beide in een aparte kolom te hebben.

Gelukkig is dat snel gepiept. Selecteer de draaitabel en ga via Ontwerpen naar Rapportindeling en kies voor Overzichtsweergave. Je draaitabel ziet er nu als volgt uit:

pict29

Vervolgens kun je de labels van het Project veld herhalen zodat projectnaam en taakbeschrijving naast elkaar staan. Selecteer cel B8 en ga via het rechtermuisklikmenu naar Veldinstellingen. In het venster dat zich opent ga je naar het tweede tabblad ‘Indeling & afdrukken’ en vink je ‘Itemlabels herhalen’ aan:

pict30

Nu de projectnaam en de taakomschrijving netjes naast elkaar staan kun je met behulp van de unieke sleutel alle relevante informatie ophalen uit de Projecten tabel. Voeg in kolom A de volgende formule toe. Zorg dat je de formule voldoende regels naar beneden kopieert. In principe zou 200 regels ruim voldoende moeten zijn.

pict31

De volgende stap is om op basis van deze unieke sleutel te kijken of een taak over de deadline is gegaan. Hiervoor voeg je een nieuwe kolom toe en gebruik je onderstaande formule:

pict32

Merk op dat ik hier gebruik maak van de functie X.ZOEKEN() in de plaats van VERT.ZOEKEN(). X.ZOEKEN() is één van de nieuwe functies die Microsoft afgelopen jaar heeft toegevoegd aan Excel. Het doet hetzelfde als VERT.ZOEKEN maar is veel flexibeler. Zo hoeft de kolom waarin je zoekt niet per se de eerste in het bereik te zijn, wat hier heel goed van pas komt.

Tot slot moet je nog één hulp kolom toevoegen. Hiermee haal je het adres op:

pict33

Nu is eindelijk tijd om alle puzzelstukjes aan elkaar te leggen. Je gaat een interactief indicator ‘lampje’ tonen voor iedere taak waarvan de deadline is verstreken. Dit doe je in twee stappen. Eerst voeg je de hyperlink toe, daarna maak je deze op als een indicator lampje.

Voeg weer een kolom toe en gebruik de volgende formule:

pict34

Je ziet dat de formule alleen een hyperlink teruggeeft wanneer een taak te laat is. Merk verder op dat het adres in de HYPERLINK() formule voorafgegaan wordt door een hekje ‘#’. Dit is belangrijk, anders werkt je link niet.

De HYPERLINK() formule heeft nog een tweede argument. Hiermee kun je de tekst van de link bepalen. Deze ga je gebruiken om een rood bolletje te tonen, zodat het net een indicator ‘lampje’ lijkt.

Hiervoor kun je mooi één van de nieuwe Windows icoontjes gebruiken. Met de sneltoetscombinatie Windows + ; (dus: Windowstoets + puntkomma) kun je gemakkelijk emoji’s en andere icoontjes vinden. Deze werken ook in formules, maar jammer genoeg niet in kleur.

pict35

Wanneer je kolom A:C verbergt en de kolom met de indicators wat smaller maakt en een rode tekstkleur geeft, dan begint het al aardig op een dashboard te lijken:

pict36

En als je op een indicator klikt, brengt ie je direct bij de waarde in de Projecten tabel, mocht je ‘m willen aanpassen. Handig toch? Helaas zijn de herhalende project labels wel erg lelijk. Gelukkig heb ik ook daar een fix voor.

Pas de kolombreedte van kolom E aan naar 0.01 pixels. Hiermee verberg je de kolom nét niet, waardoor de tekst van cellen waarvan de rechtsgelegen cel leeg is gewoon doorloopt. En laat dit nou net het geval zijn bij onze titel en de project titels…

pict37

Afsluitend

Gefeliciteerd! Je hebt alle stappen doorlopen van deze reeks. Het resultaat ervan zie je hieronder:

pict38

In deel één heb je de basis gelegd voor het visualiseren van projecten met gebruik van een draaitabel. In deel twee heb je dit overzicht verfijnd door de status van een taak/project een kleur toe te wijzen. En tot slot heb je de laatste hand gelegd aan de interface.

Het resultaat mag er zijn, al zeg ik het zelf. Je Excel projectenoverzicht is overzichtelijk, gebruiksvriendelijk en gemakkelijk te onderhouden.

Deze reeks heeft je hopelijk een idee gegeven wat er allemaal mogelijk is in Excel. En dit is pas het topje van de ijsberg.

Ik hoop dat je er wat van opgestoken hebt. Heb je vragen hierover, of over Excel dashboards en oplossingen in het algemeen? Voel je vrij om contact op te nemen met ExpertExcel.

Categorieën
Uncategorized

Een Project Dashboard Maken in Excel – deel 2/3

In het vorige blog heb je de eerste versie van een projectplanning dashboard gemaakt. Dat dashboard bevatte een visualisatie die antwoord gaf op de vraag: wanner start een project en hoelang duurt het?

In dit blog ga je de visualisatie verrijken met de status van een project: hoeveel dagen zijn er nog te gaan in een project, is een project al voltooid of is de deadline overschreden en zo ja, hoeveel dagen al?

Hieronder zie je wat je gaat maken. Het Excel bestand kun je hier downloaden: Project Dashboard

Gegevensopslag

Nu je de status van een project wilt weergeven, heb je per project extra informatie nodig in de gegevensbron – je moet immers weten of een project voltooid is of niet. Voeg daarom een nieuwe kolom toe aan de tabel op het eerste tabblad.

Geef kolom de volgende naam: [Voltooid] en kopieer onderstaande regels in de tabel.

Projectnaam Taakbeschrijving Startdatum Einddatum Voltooid
Project 1 Taak 1 31-Oct-20 07-Nov-20 WAAR
Project 1 Taak 2 01-Nov-20 05-Nov-20 WAAR
Project 1 Taak 3 03-Nov-20 15-Nov-20 WAAR
Project 2 Taak 1 05-Nov-20 12-Nov-20 ONWAAR
Project 2 Taak 2 07-Nov-20 14-Nov-20 ONWAAR
Project 3 Taak 1 07-Nov-20 18-Nov-20 ONWAAR
Project 3 Taak 2 07-Nov-20 15-Nov-20 ONWAAR
Project 3 Taak 3 08-Nov-20 13-Nov-20 ONWAAR
Project 3 Taak 4 09-Nov-20 13-Nov-20 ONWAAR
Project 4 Taak 1 11-Nov-20 24-Nov-20 ONWAAR
Project 4 Taak 2 11-Nov-20 20-Nov-20 ONWAAR
Project 5 Taak 1 12-Nov-20 16-Nov-20 ONWAAR
Project 5 Taak 2 13-Nov-20 20-Nov-20 ONWAAR
Project 5 Taak 3 13-Nov-20 18-Nov-20 ONWAAR

 

Je ziet dat de nieuwe deze alleen twee mogelijke waarden bevat:

  • WAAR = een project is voltooid
  • ONWAAR = een project is nog niet voltooid

Dit datatype is beter bekend als logische waarde. Omdat een logische waarde slechts twee waarden kent, WAAR of ONWAAR, fungeert het als een aan/uit schakelaar. En dit komt in heel veel scenario’s goed van pas, zoals gegevensvalidatie.

Gegevensvalidatie toepassen

Om een correcte invoer van de nieuwe kolom te garanderen kun je gegevensvalidatie aan de kolom toevoegen. Selecteer eerst alle regels in kolom [Voltooid] en ga dan via ‘Gegevens’ naar ‘Gegevensvalidatie’:

Dit brengt je in het Gegevensvalidatiescherm waar je een de regels voor data invoer kunt bepalen.

Kies onder ‘Toestaan:’ voor de laatste optie ‘Aangepast’. Nu kun je je eigen formule gebruiken voor de validatie. Let op: de formule die je schrijft moet altijd een logische waarde teruggeven:

  • WAAR = validatie geslaagd
  • ONWAAR = validatie mislukt

De formule die je gebruikt is =ISLOGISCH(F5). Deze formule inspecteert of de waarde in de geselecteerde cel (in dit geval F5) een logische waarde bevat.

Druk op OK om de validatieregel toe te passen.

Wanneer je nu een waarde invoert anders dan een logische waarde, dan toont Excel een waarschuwing:

Een keuzelijst maken

Hoewel deze validatieregel goed werkt is het niet zo gebruiksvriendelijk. Veel gebruikers weten niet wat logische waarden zijn, hoewel ze deze onbewust vaak toepassen. Een betere optie is om de gebruiker een keuzelijst te geven zoals je hieronder kunt zien:

Om een keuzelijst te maken moet je eerst de items van je keuzelijst in een bereik plaatsen. Pas vervolgens de gegevensvalidatie aan naar de optie ‘Lijst’ en verwijs je naar het bereik waarin de logische waarden hebt gezet:

Voordat je doorgaat is het een goed idee om te controleren of de nieuwe kolom goed is ingeladen in het datamodel. Open Power Pivot en druk op de knop Vernieuwen als je de kolom niet ziet:

Berekeningen

In het vorige deel heb je een aantal basismetingen aan het Power Pivot datamodel toegevoegd:

  • Dag:= MIN( Agenda[Date] )
  • Start:= MIN( Projecten[Startdatum] )
  • Eind:= MAX( Projecten[Einddatum] )
  • Taak: = IF( [Dag] >= [Start] && [Dag] <= [Eind], 1, BLANK() )

De laatste meting gebruikte je in de draaitabel om met voorwaardelijke opmaak de opvulkleur van de cel bepalen zodat aaneengesloten cellen een horizontale balk vormden.

Om de status van een project te visualiseren borduur je voort op hetzelfde principe.

Per status ga je een apart cijfer berekenen, en dat cijfer gebruik je in de voorwaardelijke opmaak om de status telkens een andere kleur te geven. Op die manier wordt het voor de gebruiker in één oogopslag duidelijk hoe de zaken ervoor staan:

 

De doelstelling is dus om een nummering te maken van de verschillende statussen, zoals hieronder weergegeven:

  1. Gedaan: dagen van project die in het verleden liggen
  2. Te doen: dagen in het project die in de toekomst liggen
  3. Te laat: dagen in het verleden sinds de einddatum van het project liggen

Die nummering gebruik je later om te coderen naar de volgende kleuren:

  1. Gedaan = Groen
  2. Te doen = Lichtgroen
  3. Te laat = Donkerrood

Uiteindelijk komt je overzicht er dus zo uit te zien:

Extra basis berekeningen

Om de status per project te kunnen bepalen heb je extra berekeningen nodig. Je moet kunnen bepalen of een project voltooid is of niet. Ook moet je weten of een bepaalde dag in de draaitabel tot het verleden of tot de toekomst behoort.

Neem de volgende basismetingen over. Volg daarbij weer de volgende stappen:

  1. Kies eerst de brontabel waaraan je de berekening wilt koppelen
  2. Daarna geef je de meting een nieuwe naam
  3. Neem daarna de formule over
  4. Kies tot slot de juiste opmaakoptie
  5. NIEUW: Controleer of de formule een juist resultaat teruggeeft

Met behulp van COUNTROWS() kun je het aantal regels bepalen van een bepaalde tabel. Deze functie wordt in [Aantal taken] toegepast op de Projecten tabel. Door de filtercontext ‘ziet’ iedere cel in de draaitabel een andere set regels uit de Projecten tabel en retourneert deze meting dus telkens een ander resultaat.

De meting [Aantal voltooide taken] maakt gebruik van een hele handige Power Pivot functie: FILTER(). Hiermee kun je extra filters toepassen op een tabel binnen de huidige filtercontext. FILTER() kijkt voor iedere rij in de huidige filtercontext of de expressie in het tweede argument WAAR is, en retourneert een tabel met alleen díe regels.

Om de werking van [Aantal taken] en [Aantal voltooide taken] beter te begrijpen kun je ze naast elkaar in een aparte draaitabel zetten:

Je ziet dat Project 1 uit drie taken bestaat. Twee daarvan zijn al voltooid. De filtercontext van het subtotaal van Project 1 bestaat uit dus drie regels. In de draaitabel zie je deze getallen in de kolommen waar de metingen staan, [Aantal taken] en [Aantal voltooide taken].

Vergelijk dit met Project 3. Deze bestaat uit vier taken. In de draaitabel zie je dat dit aantal overeenkomt voor [Aantal taken]. Voor [Aantal voltooide taken] zie je daarentegen een lege cel. Dit komt omdat geen van de taken voltooid is.

De laatste meting, [Voltooid_], bepaalt of een taak, of een geheel project, voltooid is. Dit wordt berekend door het aantal taken te vergelijken met het aantal voltooide taken.

In principe is dit een rare oplossing. In Excel had je gewoon de logische EN() formule kunnen gebruiken. Helaas werkt de AND() formule in Power Pivot niet zo flexibel als zijn tegenhanger in Excel. Deze verwacht namelijk twee logische waarden, ingegeven als twee aparte argumenten, en vergelijkt of beide WAAR zijn.

Omdat een project uit meer dat twee taken kan bestaan is deze functie dus niet bruikbaar. Je moet dus een andere manier gebruiken om hetzelfde resultaat te berekenen: door het aantal taken te vergelijken met het aantal voltooide taken.

De status berekenen

Nu de basismetingen klaar staan is het tijd voor het echte werk. Je gaat een formule schrijven die per dag een statusnummer teruggeeft. Hieronder staan de verschillende statussen die we willen bepalen voor onvoltooide taken:

  1. Gedaan: dag binnen planning en in het verleden
  2. Te doen: dag binnen de planning en in de toekomst
  3. Te laat: dag na de planning en in het verleden

Je start met twee hulp metingen om de laatste twee statussen te bepalen:

Beide berekeningen resulteren in een logische waarde en maken gebruik van de speciale EN-operator in Power Pivot, de dubbele ‘&&’. Hiermee kun je toetsen of meerdere vergelijkingen allemaal WAAR zijn.

De status per taak

Met bovenstaande hulp metingen kun je een eenvoudige berekening maken om per dag de status van een bepaalde taak te bepalen:

Deze formule maakt gebruik van SWITCH(), een handige functie die van pas komt wanneer je meerdere ALS-DAN constructies nodig hebt. De functie is overigens ook als Excel formule te gebruiken.

Het eerste argument van SWITCH() heeft een logische expressie – een formule/of waarde die een WAAR/ONWAAR teruggeeft. In dit geval gebruik je simpelweg de TRUE() functie.

De volgende argumenten worden altijd in een waarde-resultaat paar opgegeven. Eerst geef je de waarde op die je wilt vergelijken met de expressie in het eerste argument, en daarna geef je het resultaat dat je de formule wilt laten retourneren als de vergelijking WAAR is.

Dit patroon van waarde-resultaat argumenten kun je net zo vaak herhalen totdat je een laatste waarde wilt opgeven voor de categorie ANDERS – vergelijkbaar met het derde argument van de ALS() formule.

Wanneer je de meting in de draaitabel vervangt met [StatusTaak], dan zie je dat deze de juiste nummering teruggeeft per taak:

Echter, wanneer je een project inklapt, dan gaat het nog niet helemaal goed:

Merk op dat er taken in Project 1 zitten die al over de deadline zijn, maar hierboven niet als een status 3 worden weergegeven.

De status per project

Om de status op zowel project, – als op taak-niveau te bepalen heb je een allerlaatste meting nodig die de maximale waarde van [StatusTaak] berekent. Helaas kun je hiervoor niet de MAX() functie gebruiken. Deze werkt alleen voor kolomverwijzingen en niet voor zelf gedefinieerde metingen:

De X-factor

Gelukkig biedt Power Pivot hier een alternatief voor: de zogenaamde X-functies. Dit is een groep functies wiens functienaam allemaal op X eindigt. Een paar voorbeelden hiervan zijn:

  • SUMX
  • AVERAGEX
  • MINX
  • MAXX

De X-functies werken allemaal op dezelfde manier.

  1. Het eerste argument is altijd een tabel verwijzing. Dit mag ook een berekende tabel zijn, bijvoorbeeld als resultaat van een FILTER() functie.
  2. Het tweede argument is een formule. Hierin kun je als het ware een berekende kolom toevoegen aan de tabel uit het eerste argument, uitsluitend met gebruik van de kolommen die daarin voorhanden zijn.
  3. De X-functie berekent voor iedere regel in de tabel in het eerste argument, de uitkomst van de formule in het tweede argument. Vervolgens aggregeert deze alle berekende waarden tot de daadwerkelijke uitkomst van de X-formule.

Het mooie van FILTER() is dat je ook een andere meting als tweede argument mag gebruiken. In dat geval worden alle relaties in het datamodel automatisch doorgerekend. Dit is een zeer krachtige feature van Power Pivot.

Met deze nieuwe kennis kun je de uiteindelijke meting schrijven:

Wanneer je deze meting in de draaitabel plaatst zie je inderdaad dat de waarde op project-niveau de juiste waarden teruggeeft.

Visualisatie

Tijd om de nummering van [Status] te coderen naar de juiste kleuren. Selecteer een willekeurige cel in het waarden bereik van de draaitabel. Ga via Start > Voorwaardelijke Opmaak > Markeringsregels voor cellen en kies ‘Gelijk aan…’ uit het uitklapmenu.

In het dialoogvenster dat verschijnt zet je de waarde op 1 en kies je in het tweede vakje de laatste optie uit de keuzelijst: Aangepaste indeling…

Via tabblad Opvulling pas je de opvulkleur aan naar groen. Op tabblad Rand kies je een witte boven en onderrand, en op tabblad Getal kies je bij Categorie voor ‘Aangepast’, en past bij Type de waarde aan naar drie puntkomma’s (;;;).

Herhaal dit voor de overige statussen: 2 = lichtgroen en 3 = donkerrood.

Het resultaat mag er zijn! In één oogopslag zie je welke projecten over de deadline zijn en waar je je bevindt ten opzichte van de planning.

Ook wanneer je een project inklapt:

Samenvattend

Je hebt de planning tool verder verrijkt met een kleurcodering voor de status waarin een project zich bevindt.

  • Je hebt geleerd hoe je gegevensvalidatie toepast zodat gebruikers alleen de juiste waarden mogen invoeren
  • Hoe je de gebruiker alleen gevalideerde waarden uit een keuzelijst kunt laten selecteren
  • Wat logische waarden zijn en hoe je ze kunt toepassen binnen formules, validatieregels en conditionele opmaak
  • Hoe de COUNTOWS(), FILTER(), SWITCH() en MAXX() functies werken in PowerPivot.

In het volgende en laatste deel van deze serie gaan je het dashboard verder opmaken zodat het een presentatiewaardig dashboard wordt, in de plaats van alleen een slicer en een simpele (maar ontzettend gave) draaitabel.