Deze week gaat de nieuwsbrief van Ter Zake Excel helemaal om Excel expert worden. Met verschillende tips en tricks helpen we je een heel eind op weg. Aan het eind van deze nieuwsbrief weet jij alles wat je moet weten om een Excel expert te kunnen zijn!

Tip 1. Filter 
Door bij ‘Gegevens’ te klikken op ‘Filter’ kan je snel en gemakkelijk je gegevens filteren. Je kan hierbij kiezen voor bepaalde waarden maar ook op alfabetische volgorde bijvoorbeeld.

Tip 2. Klik en Sleep
Als je gegevens wil verplaatsen kan je ze kopiëren en plakken, echter is er een snellere manier. Door de te selecteren en te verslepen. Je selecteert wat je wil verplaatsen en sleept het naar de locatie waar je het wil hebben.

Tip 3. Klemboord
Via de functie klemboord kan je meerdere dingen kopiëren en plakken, zonder dat de volgorde uitmaakt. Alles wat je kopieert komt erin te staan en door er simpelweg op te drukken kan je het plakken.

Tip 4. Volg een Cursus
Wil je meer leren over Excel? Door een cursus te volgen bij Ter Zake Excel leer je alles over tabellen en  grafieken maar ook praktische tips die je veel tijd kunnen besparen. Er zijn cursussen voor iedereen op verschillende niveaus. Klik hier voor meer informatie.

In de nieuwsbrief van 1 mei hebben we ‘Verticaal Zoeken’ behandelt. Deze nieuwsbrief gaat hierop door met extra tips en mogelijkheden. Voor de nieuwsbrief van 1 mei klik hier.

Zoekgebied Vastzetten
Een van de dingen waar je bij Verticaal Zoeken tegenaan kan lopen is dat het zoekgebied verspringt. Dit komt omdat wanneer je de formule verplaatst, je ook het zoekgebied verplaatst. (zie voorbeeld) Dit kan makkelijk verholpen worden door het zoekgebied (tabelmatrix) vast te zetten. Dit doe je door in de formule de tabelmatrix te selecteren en op F4 te drukken. Hierdoor verschijnen er $ in de formule en zal deze vast staan.

Verticaal zoeken vindt altijd het eerste resultaat
Een van de nadelen van verticaal zoeken is dat het altijd alleen het eerste resultaat vindt, zoals zichtbaar in het voorbeeld. Er wordt gezocht naar de naam Martijn, ondanks dat deze twee keer voorkomt, wordt de eerste waarde geselecteerd. Dit is belangrijk om rekening mee te houden wanneer je Verticaal zoeken gebruikt.

#N/B – Wat nu?
Een veel voorkomende vraag bij Verticaal zoeken is; wat is #N/B? Deze foutmelding wordt weergegeven als de gezochte waarde niet gevonden kan worden. Dit kan natuurlijk gebeuren maar om te zorgen dat de gegevens er netjes uit blijven zien kan je de functie ALS.FOUT gebruiken. Je typt hiervoor =ALS.FOUT(je huidige formule);0), Excel zorgt er dan voor dat elke keer als er een fout melding komt, dit wordt vervangen door 0. Je kan uiteraard ook andere getallen of teksten gebruiken.

Verticaal Zoeken

De functie ‘Verticaal Zoeken’ is een van de meest gebruikte functies in Excel en daarom erg belangrijk om goed te begrijpen. De functie zoekt naar overeenkomstige waarden in het door jou aangegeven gebied, zodat je niet dingen twee keer hoeft op te schrijven. Een functie die je veel tijd kan besparen!

Stappenplan

Om de functie goed uit te leggen hebben we een voorbeeld nodig. In het bovenstaande voorbeeld zien we hoe, aan de hand van een naam, de bijpassende grondsoort wordt opgezocht. Zeker in grote bestanden kan dit veel tijd en moeite besparen.

Stap 1:
Voeg via ‘Functie invoegen’ de functie vert.zoeken (Vlookup) in, op de plaats waar je wil dat Excel de gevonden informatie neerzet.
Stap 2:
Er opent nu een invoegtoepassing. Het eerste wat je moet invullen is de ‘Zoekwaarde’, hierin geef je aan waar Excel naar opzoek moet, in dit geval de Naam.
Stap 3:
Hierna vul je het vakje ‘Tabelmatrix’ in, je selecteert het gebied waar Excel in moet zoeken om de door jou aangegeven gegevens te vinden.
Stap 4:
Het ‘Kolomindex_getal’ is de kolom waarin Excel je antwoord kan vinden. In dit geval de 2e kolom van de Tabelmatrix, daarom vul je hier 2 in. Dit verschilt uiteraard per geval.
Stap 5:
Bij ‘Benaderen’ voeg je Onwaar (False) in zodat Excel alleen waarde weergeeft die exact overeenkomen met wat je zoekt.

Tip 1. People Graph

Deze tip gaat je helpen om data op een mooie, nieuwe manier te laten zien. Excel heeft namelijk de functie People Graph, waarin je gegevens worden omgevormd tot flitsende tabellen. Je selecteert daarvoor People Graph. Bij gegevens selecteer je de relevante dataset en dan hoef je alleen nog de titel aan te passen voor een mooie grafiek. Beschikbaar vanaf Office 2013

Tip 2. Slicer

De volgende tip gaat je helpen als je veel met tabellen werkt. De functie Slicer laat je namelijk sneller en makkelijker filteren in tabellen. Door op de tabel te klikken -> Ontwerpen en dan te klikken op ‘Slicer’ open je de functie. Nu kan je kiezen waarop je wil filteren. In het voorbeeld wordt bijvoorbeeld gezocht naar ‘Grote’ en ‘Kleur’, er is dan te zien dat er nog twee opties overblijven.

Tip 3. Transponeren

De laatste tip van deze week is een lastige, maar hij kan erg nuttig zijn wanneer goed gebruikt. De functie transponeren draait namelijk je verticale gegevens, horizontaal, of andersom. Echter kan je door deze functie de getallen aanpassen en veranderen die dan ook automatisch mee. Dit doe je door het exacte oppervlakte van de cellen te selecteren, de functie ‘=transponeren’ in te type en de relevante gegevens te selecteren. Let op! Hierna druk je op Ctrl Shift Enter om door te gaan.

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.

Wat is ‘Snel aanvullen’

Het is een veel voorkomende ergernis die iedereen kent, een document hebben dat net niet netjes is. Denk bijvoorbeeld aan een klantenbestand waarbij sommige namen wel met een hoofdletter geschreven staan en sommige niet. Een ander voorbeeld is bij postcodes, soms wel een spatie, soms niet. Dit kan onprofessioneel overkomen en werkt moelijker dan wanneer alles netjes en op dezelfde manier opgeschreven staat. Gelukkig heeft Excel een functie die dit voor jou netjes kan oplossen, zonder dat je alles opnieuw hoeft te typen.

De functie ‘Snel aanvullen’ kan je uren aan routinematig werk in Excel besparen!


Lees meer

Cellen selecteren

Als je met Excel werkt, weet je hoe je cellen moet selecteren. Je klikt op een cel of je klikt en je sleept om meerdere cellen te selecteren. Echter kost dit veel tijd als je werkt in werkbladen met duizenden gegevens. Gelukkig zijn er snellere manieren, door bijvoorbeeld op Ctrl-A te drukken selecteer je alle cellen. Dit kan praktisch zijn als je alles wilt verwijderen of kopiëren.

Sneller scannen

Mocht je iets specifieker te werk willen gaan en bijvoorbeeld willen weten wat er in de onderste cel staat, is dat ook mogelijk. Hiervoor hoef je niet helemaal naar beneden te scrollen, maar kan je op Ctrl in combinatie met (↓) drukken. De laatst gevulde cel van de geselecteerde kolom zal dan getoond worden. Dit werkt ook andere kanten op, hiervoor gebruik je de andere pijltoetsen.

Sneller selecteren

Een andere mogelijkheid is dat je een bepaalde rij of kolom wil selecteren. Dit kan door tegelijk Ctrl Shift en (↓) in te drukken. Alle gebruikte cellen in de richting van het pijltje worden dan geselecteerd. Door te blijven klikken in andere richtingen kunnen hele tabellen geselecteerd worden, of juist weer losgelaten worden.

 

 

Extra – Selecties printen

Na het selecteren van de gewilde cellen is het mogelijk om deze te printen. Het idee is dan dat niet het hele werkblad geprint word maar alleen de door

jezelf aangegeven cellen. Dit is mogelijk door eerst de cellen te selecteren, dan naar afdrukken te gaan en daar te kiezen voor de optie ‘Selectie afdrukken’, zoals zichtbaar in de afbeelding.

Cellen Beveiligen
Iedereen is het wel eens overkomen, je stelt een eenvoudig document op waarin je collega’s alleen maar hun resultaten in te hoeven voeren om bijvoorbeeld automatisch de BTW te berekenen. Een simpel proces wat eigenlijk niet fout zou moeten kunnen gaan, tot dat iemand per ongeluk in de cel van de formule wat typt en niet weet hoe die dit moet herstellen. Door cellen te beveiligen kan dit voorkomen worden. Excel heeft een functie waarmee bepaalde cellen beveiligd kunnen worden zodat formules niet per ongeluk aangepast worden. Hieronder leggen we in een aantal stappen uit hoe je dit makkelijk kan aanpassen. Lees meer