Door slim om te gaan met de mogelijkheden van Excel is er veel meer mogelijk dan de meeste mensen denken. Mogelijkheden die de gebruiker (veel) sneller het werk kunnen laten doen of zelfs volledig automatiseren en daarmee kunnen leiden tot grote tijdswinst voor de gebruiker.
De developers van ExpertExcel hebben tal van Excel tools ontwikkeld die organisaties in staat stelt efficiënter te werken. Tools die aansluiten op hun business en het werk makkelijker en sneller maken. Zo ook voor een organisatie die voor hun klanten visuele inspecties uitvoert op schepen. Dit houdt in dat zij hun klanten een fotorapportage in PDF sturen met conclusies van deze inspectie.
Voorheen dienden gebruikers alle gemaakte foto’s en teksten handmatig samen te voegen in één document, om deze vervolgens om te zetten naar PDF. Een proces wat met gemak een uur in beslag nam. Met tientallen inspecties per maand betrof dit een aanzienlijk deel van de werktijd. Daarnaast betrof het een foutgevoelig proces en bleken uitgestuurde rapportages niet uniform te zijn.
We horen je denken, dit is geen proces waarbij je direct aan Excel denkt. Echter, júist binnen Excel is het mogelijk om dergelijke processen op maat te automatiseren en te versnellen. ExpertExcel heeft voor deze klant een applicatie ontwikkeld die met enkele klikken een exact dezelfde fotorapportage genereert. Niet binnen een uur, maar binnen tientallen seconden.
Het proces om tot een rapportage te komen zijn voor de gebruiker sterk vereenvoudigd in drie stappen:
Klik op de knop om de locatie te selecteren met de foto’s en de foto’s worden ingelezen
Voeg naast elke foto de opmerkingen toe indien ze er zijn
Klik op de knop om er een PDF van te maken in de huisstijl en opmaak van de klant
Deze tool heeft geresulteerd in minder tijd kwijt zijn aan administratief werk en meer tijd hebben voor werk wat leidt tot verbetering van het bedrijf en klanttevredenheid. Daarnaast zorgt de applicatie ervoor dat het eindproduct netter oogt, strakker staat, altijd in exact hetzelfde format wordt opgeleverd en zijn schoonheidsfoutjes verleden tijd – een aanzienlijke vooruitgang in het aangeboden eindproduct dus.
De werkwijze die we hanteren bestaat uit drie stappen:
Intake: een online meeting om de wensen en eisen af te stemmen.
Voorstel: onze klanten ontvangen een inspanningsverwachting en planning.
Ontwikkeling: na akkoord wordt de applicatie of tool op iteratieve wijze ontwikkeld
Voorgenoemde tool hebben we binnen enkele dagen ontwikkeld en was binnen een maand terugverdiend door tijdswinst van de gebruiker.
Michel van Expert Excel
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
Als je aan een project werkt waarvoor je veel gegevens nodig hebt, kan het verzamelen van deze gegevens veel tijd en moeite kosten. Gelukkig bestaat er een oplossing waarmee je tijd kunt besparen en efficiënter kunt werken: scraping in Excel.
Met scraping in Excel kun je gegevens van websites ophalen en deze integreren in Excel, waardoor je tijd bespaart en productiever kunt werken. Door scraping kun je grote hoeveelheden gegevens in één keer ophalen en automatisch verwerken in Excel. Dit betekent dat je geen tijd meer hoeft te besteden aan het handmatig kopiëren en plakken van gegevens, wat veel tijd kan besparen.
Een ander voordeel van scraping in Excel is de flexibiliteit die het biedt. Je kunt specifieke gegevens selecteren en verzamelen die relevant zijn voor jouw project, waardoor je gericht kunt werken. Dit betekent dat je niet langer hoeft te zoeken naar de juiste gegevens in een grote hoeveelheid informatie, maar dat je direct de informatie kunt ophalen die je nodig hebt.
Scraping in Excel maakt het ook gemakkelijk om gegevens up-to-date te houden. Je kunt automatische scraping-routines instellen waarmee je de gegevens regelmatig kunt bijwerken, waardoor je altijd over de nieuwste en meest relevante gegevens beschikt. Dit is vooral handig als je werkt met gegevens die regelmatig worden bijgewerkt, zoals prijzen of voorraadniveaus.
Hieronder vind je een eenvoudige handleiding over hoe je scraping in Excel kunt toepassen:
Identificeer de website(s) waarvan je gegevens wilt ophalen.
Bepaal welke specifieke gegevens je nodig hebt voor jouw project en noteer deze.
Kies een geschikte scraping tool en download deze.
Open Excel en maak een nieuw werkblad aan.
Klik op het tabblad “Gegevens” en selecteer “Van web”.
Voer de URL van de website in waarvan je gegevens wilt ophalen en klik op “OK”.
Selecteer de gegevens die je wilt ophalen en klik op “Importeren”.
Stel, indien nodig, de scraping tool in om meerdere pagina’s op dezelfde manier op te halen.
Zorg ervoor dat de geïmporteerde gegevens in de juiste kolommen en rijen staan.
Bewerk de gegevens indien nodig voor jouw specifieke doeleinden.
Michel van Expert Excel
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
Het is wel belangrijk om te benadrukken dat er wel enige technische kennis vereist is om scraping in Excel toe te passen. Er zijn verschillende tools beschikbaar die je hierbij kunnen helpen, maar het kan zijn dat je enige programmeerkennis nodig hebt om deze tools optimaal te benutten.
Als laatste is het belangrijk om te benadrukken dat je met scraping in Excel de gegevens direct kunt bewerken voor je eigen doeleinden. Dit betekent dat je de gegevens kunt aanpassen aan jouw specifieke behoeften en deze direct kunt gebruiken voor jouw project.
Dit kan veel tijd besparen, omdat je niet langer de gegevens handmatig hoeft te bewerken. Kortom, als je op zoek bent naar een snelle en efficiënte manier om gegevens te verzamelen, dan is scraping in Excel een uitstekende optie. Het stelt je in staat om gegevens snel en gemakkelijk op te halen en te verwerken, en biedt je de flexibiliteit om specifieke gegevens te selecteren en te verzamelen die relevant zijn voor jouw project. Door automatische scraping-routines in te stellen kun je de gegevens ook up-to-date houden, waardoor je altijd over de nieuwste en meest relevante informatie beschikt. Probeer het eens uit en ontdek zelf hoeveel tijd en moeite je kunt besparen met scraping in Excel!
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:
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.
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:
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.
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.
Michel van Expert Excel
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
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)
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.
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 niettoestaan 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 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.
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!
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.
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.
Met 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.
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.
Klik op Voorwaardelijke opmaak, Markeringsregels voor cellen, Groter dan.In 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.
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.
Op een hoger niveau
Als laatste wil ik alleen nog weten wanneer we op alle onderdelen boven of onder het gemiddelde scoren. Bijvoorbeeld 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.
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.
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.
Je hebt net een prachtige nieuwe huisstijl laten ontwerpen. Een mooie marketing investering waar je de komende jaren weer fris mee voor de dag kan komen. In Excel gebruik je een aantal documenten met formules voor je dagelijks werk. Offerte documenten bijvoorbeeld en facturen. Daar wil je ook dat nieuwe logo in doorvoeren en dat watermerk in gebruiken, zonder dat ze formules of teksten in de weg zitten. Maar hoe doe je dat nou makkelijk zelf? Ik leg je het uit in een drie hele simpele stappen.
Hieronder het facturatievoorbeeld uit de vorige blog, waar we stap voor stap de huisstijl gaan invoegen. Er zijn drie specifieke onderdelen: het logo rechtsboven, het watermerk linksonder en de tekst helemaal onderaan:
Stap 1:
De truc is om aan je designer een ontwerp voor Excel te vragen die de gehele pagina beslaat. Een ontwerp die alle drie de onderdelen in één foto vastlegt. Heb je geen photoshop skills of een ontwerp voor een hele pagina? Geen zorgen, met alleen je logo werkt het ook. Het principe is namelijk hetzelfde.
Het onderstaande ontwerp kan je gebruiken om in Excel in te voegen. Het heeft de afmetingen 1180 x 1663.
Michel van Expert Excel
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
Als tweede stap wil je de Pagina layout zien, te vinden via Beeld.
Vervolgens voeg je een header toe, waar we het ontwerp in zetten.
Wanneer je nu met de muis boven het top van het document zweeft, zie je drie vakken.
Dubbelklik het middelste vak. De menuknop Header and Footer verschijnt. Klik op foto en selecteer je ontwerp van je computer. Hierna staat er &[Picture] in het vlak.
Vervolgens klik je buiten een van de drie vakken om het resultaat goed te zien. Mijn ontwerp pas precies als ik het middelste vak gebruik. Gebruik je alleen een logo, dan kan je met spaties en returns in het vak met de &[Picture], de positie van het logo finetunen.
Stap 3:
Als laatste stap passen we de marges aan zodat het logo en het watermerk niet afgekapt worden.
Via de menuknop Bestand klik je op pagina setup en mag alles naar nul behalve de boven- en onderkant.
Op deze manier zijn je logo, je watermerk en je footer geborgd in het document om te printen, zonder dat ze formules of teksten in de weg zitten. En bij het dagelijks gebruik van je document, kan je niet per ongeluk je logo verslepen. Het is simpelweg niet te selecteren met je muis.
Met deze update van je huisstijl kunnen er weer gelikte offertes en facturen verstuurd worden.
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.
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.
Michel van Expert Excel
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
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.
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.