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.

Michel van Expert Excel

Liever uitbesteden aan Expert Excel?

Met het team Excel specialisten sta ik voor je klaar!

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.

Michel van Expert Excel

Liever uitbesteden aan Expert Excel?

Met het team Excel specialisten sta ik voor je klaar!

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!