Tag: Excel

Categories
Uncategorized

Wat zijn de mogelijkheden in Excel om te zoeken in een tabel naar gegevens?

De mogelijkheden om in Excel te zoeken naar gegevens in een tabel zijn eindeloos. Een van de krachtigste methoden is de INDEX MATCH MATCH-functie (Nederlandse versie: INDEX VERGELIJKEN VERGELIJKEN). Deze functie is een combinatie van de INDEX en MATCH functies en maakt het mogelijk om gegevens te zoeken met meerdere zoekcriteria.

De INDEX-functie geeft de waarde van een specifieke rij of kolom terug, terwijl de MATCH-functie de plaats aangeeft van een specifieke waarde. Door deze twee functies samen te gebruiken, kun je de waarde vinden die voldoet aan meerdere zoekcriteria. Dit doe je door eerst de plaats van de zoekwaarden te bepalen met de MATCH-functie en vervolgens de waarde te vinden met de INDEX-functie.

Bijvoorbeeld, stel je hebt een tabel met productgegevens, zoals productnaam, kleur, grootte en prijs. Als je de prijs van een specifiek product wilt weten, kun je de INDEX MATCH MATCH-functie gebruiken. Als je bijvoorbeeld de prijs van een zwarte schoen wilt zoeken, dan zou de formule er als volgt uitzien:

Methode 1:

=INDEX(E10:E13;MATCH(B16;B10:B13;0);MATCH(C16;C10:C13;0)).

Methode 2:

=INDEX(E10:E13;MATCH(B16&C16;B10:B13&C10:C13;0)).

Voor wie op zoek is naar een moderne en vereenvoudigde oplossing, is er XLOOKUP (Nederlandse versie: X.ZOEKEN). XLOOKUP is beschikbaar vanaf Microsoft Excel 365 en is niet beschikbaar in eerdere versies. De syntax voor XLOOKUP is: =XLOOKUP(zoekwaarde, tabel of bereik, resultaat kolom of -bereik, [default waarde], [match modus]). Een voorbeeld van de XLOOKUP formule voor de bovengenoemde situatie is:

Methode 3:

=XLOOKUP(B16&C16;B10:B13&C10:C13;E10:E13;””;0;1).

In beide gevallen bieden INDEX MATCH MATCH en XLOOKUP een krachtige manier om gegevens te zoeken in Excel, met meerdere zoekcriteria en bij grote hoeveelheden gegevens.

Categories
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!

Categories
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!

Categories
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!

Categories
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!

Categories
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!

 

Categories
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!

 

 

Categories
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!

ExpertExcel B.V. | KVK: 69370672 |
06 144 181 27 |
michel@expertexcel.nl