Wat zijn draaitabellen

Draaitabellen zijn een manier om de gegevens die je in Excel hebt, op een makkelijke en efficiënte manier te vergelijken, analyseren en berekenen. Dit is praktisch wanneer je benieuwd bent naar bijvoorbeeld trends of patronen in je gegevens. In het voorbeeld van deze week laten we zien hoe een fruitverkoper zijn gegevens kan sorteren en analyseren.

Hoe maak je een draaitabel

Een draaitabel maak je door alle relevante gegevens te selecteren. Dan ga je naar Invoegen, en druk je op Draaitabel. Een keuze menu zal vragen of je in een nieuw werkblad wil beginnen. Zodra je op Oke klikt is de draaitabel gemaakt.

Voorbeeld: ‘Jaarcijfers per product’

Nu de draaitabel gemaakt is, kunnen de gegevens geanalyseerd worden. Excel geeft de mogelijkheid om zelf gegevens te selecteren die je kunt analyseren. Je kan dan zelf kiezen of je het in een kolom of in een rij wil zien.

In het voorbeeld word gekeken hoeveel fruit er de afgelopen 4 jaar verkocht is. ‘Jaren’ worden daarvoor in kolommen gezet, ‘Product naam’ in rijen en ‘Aantal’ in waarde. Er is hierna te zien hoeveel producten er per jaar verkocht zijn, zo is zichtbaar dat de hoeveelheid appels die per jaar verkocht wordt steeds minder wordt.

Extra Tip: Door dubbel te klikken op de gegevens in een draaitabel, laat Excel je de relevante gegevens zien. In dit voorbeeld dus alle verkopen van appels in 2016.

In dit artikel leggen we uit wat ‘Absolute’ en ‘Relatieve’ cel verwijzingen zijn, maar ook hoe je ze kan gebruiken en waarom je moet weten wat het is. Maar maak je geen zorgen, het klinkt moeilijker dan het is en we gaan het je stap voor stap uitleggen.

Cel verwijzingen en hoe je ze kan gebruiken
Binnen Excel wordt veel gebruik gemaakt van cel verwijzingen, dat is het verwijzen van een cel naar één of meerdere andere cellen. Dit is vooral praktisch wanneer dingen berekend moeten worden of bij grote bestanden. In het voorbeeld zie je hoe bijvoorbeeld de totale prijs wordt uitgerekend.

De cel waarin de totale prijs moet komen wordt geselecteerd, dan wordt er verwezen naar de cel waar de prijs in staat (B2). Het vermenigvuldigingsteken(*) wordt dan ingetoetst en de prijs wordt vermenigvuldigd met het aantal, cel (C2). Als er dan op Enter wordt gedrukt verschijnt de totaal prijs. Dit is een relatieve verwijzing, want als je de formule door trekt zie je dat voor alle artikelen de totaal prijs wordt uitgerekend.


Absoluut of Relatief?

Het verschil tussen absolute en relatieve cel verwijzingen zit erin dat, zoals zichtbaar in het vorige voorbeeld, de verwijzing verspringt als de formule cel beweegt. Dit klinkt misschien ingewikkeld en daarom is het beter uit te leggen met een voorbeeld. In het nieuwe voorbeeld gaan we de Btw over de totaal prijs berekenen.

Dit doen we door eerst een cel aan te maken waar het Btw percentage in staat, B7, in dit geval. Daarna selecteren we de cel waar het Btw bedrag moet worden uitgerekend en typen we daar (=). We verwijzen dan naar de cel waar de totaal prijs in staat (D2) en vermenigvuldigen dat met het eerder genoemde Btw tarief uit cel B7. Om te zorgen dat deze verwijzing absoluut wordt in plaats van relatief, klikken we op F4. Er verschijnen dan $ tekens, die ervoor zorgen dat deze verwijzing niet meer verspringt. Door op Enter te drukken wordt het Btw bedrag uitgerekend. Het is nu ook mogelijk dit door te trekken naar beneden zonder dat de verwijzing naar het Btw percentage verspringt.

Werkblad toevoegen
Werkbladen staan onderin Excel, door op het plusje te klikken kan een extra werkblad worden toegevoegd, een werkblad verwijderen kan door met je rechtermuisknop op het te verwijderen werkblad te klikken en dan te kiezen voor Verwijderen.

 

 

 

Naam veranderen
Door dubbel te klikken op de naam van het werkblad, krijg je de mogelijkheid om de naam van het werkblad aan te passen. Dit is praktisch als je bijvoorbeeld per maand een werkblad wil. Door met de rechter muisknop op het werkblad te klikken kan ook de kleur worden aangepast.

 

 

 

Werkblad kopiëren
Als je een werkblad wil kopiëren of verplaatsen druk je rechtermuisknop op het werkblad en dan op ‘Verplaatsen of kopiëren’. Hier kan gekozen worden om het bestand binnen de map of naar een andere map te verplaatsen. Ook kan er gekozen worden om dit met een kopie te doen.

 

 

 

 

Werkblad verbergen
Excel heeft de mogelijkheid om werkbladen te verbergen, voor het geval dat ze niet relevant zijn. Hiervoor zet je de muis op het werkblad, rechtermuis klik en selecteer je ‘verbergen’. Door hetzelfde nogmaals te doen maar dan ‘zichtbaar maken’ te klikken word het werkblad weer getoond.

Met de zomervakantie in het vooruitzicht is een goede planning een must. Met een vakantieplanning in Excel heb je in 1 document alle afwezigen in beeld. Je voert hier de gegevens in die je zelf inzichtelijk wilt zien, atv dagen, zomerverlof of langdurige ziekte afwezigheid bijvoorbeeld. Zo plan je het werk optimaal in.

Wil jij een maatwerk vakantieplanning? Neem dan contact met ons op.

Formule sommen.als in Excel

Je eigen vakantieplanning opstellen doe je aan de hand van formules. De formule sommen.als is hier een handige formule. Deze formule pas jij als volgt toe:

  • CelA1 en A2 laat je leeg
  • Zorg dat in je bestand in rij 1, de datums staan die je gebruikt voor je planning. Bijvoorbeeld alle werkdagen in juli en augustus.
  • Onder deze rij vul je, indien gewenst, bijzonderheden van deze datum in zoals bijvoorbeeld de dag van de week.
  • Zorg dat in je bestand in kolom A de namen van de medewerkers staan.
  • Vervolgens kun je in de tabbladen de uren van medewerkers zetten, gekopieerd uit ons urenregistratiesysteem*. (In bijgaand voorbeeld zijn deze gegevens gekopieerd naar kolommen L tot en met S)
  • Nu voer je de formule sommen.als in.
  • Als optelbereik vul je de kolom in waar de vakantie uren van de werknemer staan.
  • Als criteriumbereik vul je de kolom in waar de datum staat in de urenregistratie van de werknemer.
  • Als criteria vul je B1 in.
  • Vervolgens zie je per dag in 1 overzicht wanneer de werknemers vakantie uren hebben opgenomen en hoeveel.

Denk bij het doorvoeren van de formules aan de juiste verwijzing naar de betreffende werknemer en het absoluut maken van de celverwijzing.

Om jouw werknemers overzichtelijk hun uren te laten registreren, gebruik je ons urenregistratiesysteem. Deze download je gratis op onze site.

Sommen.als in Excel

Urenregistratie in Excel

Als ZZP’er / ondernemer kun jij een hoop geld besparen door je boekhouding zelf te doen. Iedereen kan de eigen boekhouding in Excel doen. Tijdens een workshop leer je bij ons hoe jij je eigen urenregistratie maakt, factureert en direct de winst / verlies / omzet inzichtelijk hebt.
Op deze manier heb jij direct alles inzichtelijk voor jouw aangifte inkomstenbelasting.

Ons urenadministratie voorbeeld download je nu al gratis. Hierin verwerk je de basis voor jouw urenregistratie. Tijdens onze workshop maak jij een eigen document. Je voegt jouw logo toe en wij bieden ondersteuning in het overzichtelijk registreren en rapporteren van jouw boekhouding. Heb je achteraf vragen? Bij ons stel je die tot aan je pensioen, en daarna!

Functie Geheel in Excel

In het rekenen met uren en bedragen kan het zijn dat je met meer decimalen achter de komma rekent. Dit kan onoverzichtelijk zijn in jouw urenregistratie. Veel mensen kennen de mogelijkheid om af te ronden binnen Excel, maar dit is niet wenselijk in een boekhouding. Toch kun je, zonder het getal af te ronden, zichtbaar maken op het door jou gekozen aantal decimalen. Dit doe je met de functie geheel. Deze functie kapt het getal af, daar waar jij wilt.

Hoe pas je het functieargument geheel toe in Excel?

Stel, jij rekent 70 euro per uur en hebt 2 uur en 15 minuten door te rekenen. De 15 minuten is 1/4 e van een uur, dus noteer je als ,25.
In dit voorbeeld reken jij dus 70*2,25. De uitkomst hiervan is 157,50. In jouw overzicht wil je afronding op 1 decimaal zien.

Bij getal voer je jouw cel(len) in. In onderstaand voorbeeld is dat A1. Vervolgens vul je bij aantal decimalen in op welk aantal decimalen je jouw bedrag af wilt kappen. In dit voorbeeld 1.

Het zichtbare resultaat is dan 157,5.

Wil jij een urenadministratie zelf maken? Schrijf je nu in voor onze workshop urenadministratie in Excel voor 295 euro!

Functieargument geheel Excel

Een macro in Excel maken om tijd te besparen?

Met een Macro kun je herhalende handelingen opnemen en later laten uitvoeren. Stel dat je elke week dezelfde opmaak moet toepassen op een document, dan kun je dat opnemen en uitvoeren met een macro. Eenmaal de macro vastgelegd is het een kwestie van één druk op de knop en je werkzaamheden in Excel worden uitgevoerd. Wij hoeven jou vast niet te vertellen dat dit veel tijd bespaart.

Hoe je een macro in Excel maakt lees je hieronder;
Voor de eerste stap is het van belang om het tabblad Ontwikkelaars aan te zetten.
–           Klik op het tabblad Bestand.
–           Klik op Opties.
–           Klik op Lint aanpassen.
–           Schakel onder Het lint aanpassen en onder Hoofdtabbladen het selectievakje Ontwikkelaars in.
Na stap 1 ben je klaar om op te nemen.
–           Klik om op te gaan nemen in de groep Code op het tabblad Ontwikkelaars op Macro opnemen en bevestig via OK.
–           Voer de handelingen op het werkblad uit die nodig zijn om voor jouw herhaalwerkzaamheden.
–           Klik in de groep Code op het tabblad Ontwikkelaars op Opname stoppen.

Je hebt nu een macro opgenomen.

De laatste stap is Excel jouw werkzaamheden automatisch uit te laten voeren.
–           Klik in het tabblad Ontwikkelaars op Macro’s.
–           Selecteer de juiste macro.
–           Kies voor Uitvoeren.

Bedenk: de macro voert exact de handelingen uit die je opgenomen hebt!

Excel celeigenschappen

Een mooi opgemaakt werkblad in Excel, dat wil iedereen. Je manager vind het overzichtelijk, je collega’s weten waar ze moeten kijken en de klant verwacht een duidelijke rapportage.
Maar, hoe doe je dat eigenlijk?

Als je naar de celeigenschappen van een willekeurige cel gaat (via rechtermuisknop op een cel en dan vervolgens Celeigenschappen aanklikken of de sneltoets Ctrl 1) zie je vijf tabbladen waarmee je een cel kunt opmaken, te weten:

•             Getal

In dit tabblad kun je aangeven wat de waardesoort is van één of meerdere cellen. Voorbeelden hiervan zijn getal, valuta en datum.

•             Uitlijning

Het is mogelijk om de tekst horizontaal en / of verticaal uit te lijnen. Verder zijn er in dit tabblad nog de mogelijkheden om tekst te laten teruglopen, passend te maken, cellen samen te voegen en de richting van de tekst te bepalen.

•             Lettertype

Hier is het mogelijk om het lettertype en de lettergrootte van één of meerdere cellen aan te passen.

•             Rand

Bij het tabblad Rand is het mogelijk om een rand om de cellen te plaatsen.

•             Opvulling

Bij dit tabblad kun je een kleur of patroon geven aan de achtergrond van een cel. Het is mogelijk om twee kleuren aan één cel te geven. Dit doe je via de optie Opvuleffecten.

Bedenk dat het mogelijk is om de celeigenschappen aan te passen voor meerdere cellen, kolommen en / of rijen tegelijk.

Een jubileum in Excel uitrekenen doe je aan de hand van formules. Zo laat je Excel uitrekenen wanneer iemand bijvoorbeeld 12,5 jaar in dienst is, 50 jaar wordt, een zilveren huwelijk heeft of een pensioen leeftijd bereikt.

Formule datum maand en dag in Excel

Het uitrekenen van een 12.5 jarig dienstjubileum doe je als volgt.

Stel dat de datum in dienst in cel A1 staat (1-5-2010) en in cel B1 wil je uitrekenen wanneer iemand 12,5 jaar in dienst is dan kun je de volgende formule invoeren:
=DATUM(JAAR(A1)+12;MAAND(A1)+6;DAG(A1))
Bij het huidige jaar wordt er 12 (jaar) bij opgeteld en bij de maand 6 maanden (ofwel een halfjaar).

Het resultaat in cel B1 is dan 01-11-2022.

Wil jij weten op welke datum jouw project afgerond is? In de praktijk loopt men veel aan tegen het rekenen met datums in Excel. Na het lezen van bijgaande tip is dat verleden tijd.

Datum formules binnen Excel

We nemen als uitgangspunt dat je een startdatum hebt, de “tijdsduur” weet en daarvan wilt weten wat de einddatum is.
Stel je start een project op 01-02-2017 (ingevoerd in cel A1) dit project kent een doorlooptijd van 90 (ingevoerd in cel B1) werkdagen. Het is dan handig als Excel voor jou uitrekent wanneer je het project afgerond hebt.

De formule die je hiervoor gebruikt is
=WERKDAG(A1;B1)

Wat doet deze formule in Excel?
Met deze formule wordt bij de startdatum 90 dagen (cel B1) opgeteld en worden daarbij de weekenddagen (zaterdag en zondag) overgeslagen, hierdoor wordt het resultaat 7 juni 2017.

Excel sneltoetsen Ter Zake Excel

Wij startte het jaar met een kalender, voorzien van Exceltips. Heb jij deze niet ontvangen? Download hem hier alsnog.
Werk jij veel met Excel en wil jij hier tijd mee besparen? Onderstaand geven wij jou 4 extra tips waarmee je jouw vaardigheden verbetert.

Tekst samenvoegen in Excel

Gebruik een formule om tekst samen te voegen in Excel.
In onderstaand voorbeeld wil ik uit 3 cellen de tekst in 1 cel samengevoegd hebben.

 

CEL A1                         CEL B1                               CEL C1                                  CEL D1
Ter                                 Zake                                    Excel

Gebruik het ampersandteken (&) in je formule in cel D1 en maak de formule als volgt =A1&B1&C1 . Het resultaat is nu TerZakeExcel.

Niet altijd wil je alles samengevoegd, maar een spatie tussen je samenvoeging hebben. Gebruik hiervoor een dubbel aanhalingsteken, een spatie en nogmaals een dubbel aanhalingsteken. De formule wordt dan =A1&” “&B1&” “&C1 .
Het resultaat in cel D1 is nu Ter Zake Excel.

Duplicaten verwijderen in Excel

Heb je een (samengevoegd) bestand waar je de dubbele waardes uit wilt halen? Gebruik de functie duplicaten verwijderen in Excel.

In onderstaand voorbeeld heb ik een kolom met e-mail adressen.

info@terzake-excel.nl
jort@terzake-excel.nl
info@terzake-excel.nl
claudia@terzake-excel.nl
info@terzake-excel.nl

Om te voorkomen dat info@terzake-excel.nl meerdere keren dezelfde e-mail ontvangt, gebruik ik de functie duplicaten verwijderen.
Ga naar gegevens, duplicaten verwijderen en kies de kolom waarop je de functie wilt toepassen, in dit voorbeeld kolom A, klik op oke.
Ik krijg nu de melding dat er 3 duplicaten zijn gevonden en verwijderd en er 3 unieke waarden over zijn.

Overzichtelijke werkbladen in Excel

Wen jezelf er aan om je werkblad altijd direct een naam te geven. Dat maakt het niet alleen overzichtelijk voor jou en je collega’s, maar ook formules makkelijk leesbaar.
Je past de naam aan door te dubbelklikken op de tekst van het werkblad.

In onderstaand voorbeeld zie je dat de eerste 3 werkbladen direct vertellen welke informatie waar te vinden is, terwijl de laatste 3 tabbladen de standaard invulling van Excel geven.

Werkblad Excel Worksheet

 

Engelstalige Excel versie en Nederlandstalige Excel versie

In Nederland werken mensen met zowel de Nederlandstalige versie van Excel als de Engelstalige versie van Excel. Vrij onhandig als je de functies en formules in het Nederlands wel kent, maar dan ineens met de Engelse versie moet werken. Of wellicht heb jij collega’s die met weer een andere taal versie van Excel werken? Excel translator biedt jou de oplossing. Hier laat je gratis formules en functies vertalen.