Geïmporteerde gegevens fatsoeneren en converteren naar de juiste getalopmaak of makkelijk hardlooptijden analyseren. In Excel kun je veel routineklusjes uitvoeren zonder dat je over programmeerkennis hoeft te beschikken. Werk sneller dankzij handige, ingebouwde Excel-functies.
Zodra je bij Excel gegevens in een tabel gaat importeren, kan er veel misgaan. Soms raken ze compleet door elkaar of kan het wemelen van overbodige spaties of regelafbrekingen. Daarnaast is het mogelijk dat de inhoud over meerdere cellen komt te staan. Invoegtoepassingen met speciale functies kunnen die gegevens voor je fatsoeneren, maar hier zijn ook ingebouwde Excel-functies voor. Je hoeft dus niets extra’s te installeren of te programmeren.
Spaties en regeleinden verwijderen in Excel
Bij het importeren van externe gegevens in een tabel kunnen besturingselementen en speciale tekens worden meegenomen. Je ziet ze niet, maar ze hebben wel effect. Excel vindt ze bij vergelijkings- of zoekoperaties en kan het resultaat weergeven – wat je eigenlijk niet wilt. Zulke zaken zijn op te schonen op met de functie WISSEN. CONTROL(tekst). Voeg op de plek tussen haakjes de cel in of het bereik met de tekst. Je kunt ook dubbele aanhalingstekens gebruiken.
Vaak worden bij importeren overbodige spaties meegenomen. Het is een hell of a job om die te verwijderen. De functie SPATIES. WISSEN(tekst) kan dat ook voor je doen en laat spaties tussen twee woorden staan – handig bij achternamen met voorvoegsels. Je kunt de functie op elke cel apart toepassen maar je kunt ook het bereik vergroten door het vierkantje rechtsonder in de geselecteerde cellen naar beneden te trekken.
Daarnaast kun je meteen een matrixformule gebruiken. Daarbij worden de cellen in een nieuw bereik geplakt. Selecteer hiervoor een even groot bereik, bijvoorbeeld van A1 tot C11. Voer de functie SPATIES. WISSEN(A1:C11) dan bijvoorbeeld in cel E1 in en maak met behulp van Ctrl+Shift+Enter een matrixformule. Daarna staat binnen de doelmatrix een kopie van de lijst, waarin de overbodige spaties zijn verwijderd.
Als je een aangemaakte matrix wilt bewerken, moet je weer drukken op Ctrl+Shift+Enter.
De functie SUBSTITUEREN() doet hetzelfde. Daarmee vervang je oude door nieuwe tekst – handig als je in cellen bijvoorbeeld handmatige regeleinden wilt verwijderen die je zelf hebt ingevoerd en waar je in de tabel juist van af wilt. Excel markeert zulke regeleinden als ASCII-teken 10. De formule voor een tekst in cel A1 zou daarmee als volgt luiden:
=SUBSTITUEREN(A1;TEKEN(10);" ")
Dit vervangt elk regeleinde door een spatie. Je kunt op die manier ook letters, speciale tekens of trema’s vervangen. Als je de cel met de formule selecteert, kun je die met de truc van het vierkantje rechtsonder weer toepassen op de cellen eronder. Dit geldt overigens ook voor alle andere voorbeelden die betrekking hebben op een bepaalde cel.
Punten en komma’s verwisselen
Vergissen is menselijk. Bij het invoeren van een getal kun je in plaats van een komma snel een punt zetten – of een spatie waar je eigenlijk een decimaalteken bedoelt. Of het getal heeft ineens de opmaak tekst, waardoor Excel er niet mee kan rekenen. De functie NUMERIEKE.WAARDE() negeert spaties, ook als ze in het getal zelf staan. Op die manier wordt de tekst ‘7 777’ automatisch omgezet in het getal 7777. Wil je tekst naar een getal converteren, dan kan Excel ook met verkeerde decimale scheidingstekens rekening houden. Met de formule NUMERIEKE.WAARDE(A1;".";",") verandert de tekst ‘77.77’ in het getal 77,77. Met een matrixfunctie zijn ook meerdere kolommen te converteren:
{=NUMERIEKE.WAARDE(C4:C10;".";",")}
Celinhoud aan elkaar plakken
Er zijn enkele manieren om met Excel-functies cellen en tekst aan elkaar te koppelen. Met de ampersand (&-teken) kun je inhoud van meerdere cellen achter elkaar plakken. Behalve celverwijzingen mag je ook tekst tussen dubbele aanhalingstekens gebruiken:
="De omzet op "&A1&" bedraagt voor filiaal "&B4&" "&D4&" euro."
In plaats van de datum uit A1 zul je dan echter een getal zien. Om dat aan te passen, moet je de functie TEKST(A1;"DD-MMJJ") invoegen – de datum kun je daarbij opmaken zoals je zelf wilt:
="De omzet op "&TEKST(A1;"DD-MM-JJ")&" bedraagt voor filiaal "&B4&" "&D4&" euro."
Gebruik je de Excel-functie TEKST.SAMENV(), dan wordt de ampersand overal vervangen door een puntkomma:
=TEKST.SAMENV("De omzet op "; TEKST(A1;"DD-MM-JJ");" bedraagt voor filiaal ";B4;" ";D4;" euro.")
De Excel-functie TEKST.COMBINEREN(""; WAAR, item1, item2, …) doet iets vergelijkbaars. Hiermee kun je tussen de opgegeven tekstitems een tekenreeks laten toevoegen (een lege reeks “” zoals hier mag ook). Geef je celbereiken of complete kolommen en rijen aan, dan kun je honderden cellen combineren. Je moet aangeven of lege cellen worden overgeslagen (WAAR) of niet (ONWAAR). En als je teksten heel vaak wilt herhalen, kun je de volgende formule gebruiken:
=HERHALING("tekst";aantal_keer)
Hardlooptijden in Excel
Excel is ook erg goed voor het analyseren van je sportieve prestaties. Als je bijvoorbeeld een fanatieke hardloper bent en je tijden in Excel bijhoudt, kun je Excel-functies gebruiken om de dagen met je beste en slechtste tijden te bepalen.kom
Hiervoor biedt Excel de functies GROOTSTE() en KLEINSTE(). Je kunt daarmee niet alleen je hoogte- en dieptepunten bekijken, maar ook de top drie van je beste prestaties laten weergeven. Omdat de snelste tijd in dit geval de beste waarde is, moet je de functie KLEINSTE(matrix;1) nemen. Voer kolom B daarbij als matrix in (zie afbeelding verderop). De 1 als tweede argument staat voor de beste waarde, een 2 is de een na beste waarde en moet dus in de formule in de rij eronder (kolom ‘Tijd’) komen enzovoorts. Je kunt als tweede argument ook een cel invoeren.
In de cel ernaast combineer je de Excel-functies INDEX() en VERGELIJKEN() om meteen te laten zien welke dag bij de betreffende tijd hoort.
=INDEX(matrix; VERGELIJKEN(E4;lijst;0);1)
Op dezelfde manier kun je natuurlijk ook andere sportieve resultaten analyseren – en in plaats van de dagen waarop je het best presteerde (zoals hier) bekijken wie bijvoorbeeld de beste renners van een bosloop waren.
Excel-functies voor toevallige getallen
Soms heb je juist geen exact resultaat nodig, maar eerder een willekeurig. Met de functie ASELECT() kan een pseudo-willekeurig getal tussen 0 en 1 worden gegenereerd. De functie heeft geen parameters en levert een decimaal getal. Heb je een groter bereik getallen nodig, dan kun je het resultaat vermenigvuldigen met het door jou maximaal gewenste. Zodoende levert ASELECT()*100 een getal tussen 0 en 100.
Combineer je de functie met AFRONDEN(), dan kun je het resultaat tot een bepaald cijfer achter de komma afronden. De tweede parameter specificeert daarbij het aantal cijfers achter de komma. Op die manier wordt met de eerste formule hieronder een getal tussen 0 en 100 zonder cijfer achter de komma gegenereerd; bij de tweede is het een getal tussen 0 en 10.000.000:
=AFRONDEN(ASELECT()*100;0)
=AFRONDEN(ASELECT()*10000000;0)
Met een druk op F9 kun je telkens een nieuw getal laten genereren.
De functie ASELECTTUSSEN() genereert ook willekeurige getallen. Maar in tegenstelling tot bij ASELECT() gebeurt dat in een willekeurig bereik waarvoor je een laagste en hoogste waarde kunt invoeren:
=ASELECTTUSSEN(laagst; hoogst)
De functie levert alleen gehele getallen. Het is ook mogelijk een negatieve waarde bij het bereik op te geven. Op de volgende manier wordt er een getal tussen –100 en 100 gegenereerd:
=ASELECTTUSSEN(-100;100)
Om getallen met bijvoorbeeld een cijfer achter de komma te krijgen, moet je het resultaat delen door 10:
=ASELECTTUSSEN(10;300)/10
Wil je deze formule (of een andere) voor het geselecteerde bereik invoeren, dan moet je op Ctrl+Enter drukken. De functie is prima te combineren met andere Excel-functies. Zo krijg je met =VANDAAG()+ASELECTTUSSEN(0;30) een willekeurige datum voor de aankomende 30 dagen te zien.
Feestdagen in Excel
Zolang je niet in een schrikkeljaar op 29 februari bent geboren, is het elk jaar weer een vraag: op welke dag word je een jaartje ouder en mag je al je vrienden weer laten komen? Of op welke dagen valt kerst? Hiervoor hoef je echt niet in je agenda te kijken, laat gewoon Excel de dag weergeven waarop je jarig bent. Je doet dat met de functie TEKST(cijfer;opmaak). Daarmee kun je een goed leesbare notatie kiezen en die eventueel combineren met extra tekst en symbolen. In geval van een datum converteert de functie die naar een dag. Als je in cel A1 je verjaardagsdatum invoert, kun je in een andere cel de volgende formule invoegen:
=TEKST(A1;"DDDD, D MMMM")
Je krijgt dan iets te zien als ‘maandag, 8 mei’. Bij de help-informatie of onder het venster ‘Cellen opmaken’ (categorie ‘Aangepast’) vind je meer informatie over de opmaak. Nu kun je tekst en cellen makkelijk samenvoegen:
="Dit jaar valt mijn verjaardag ("&A1&") op een "&TEKST(A1;"DDDD")&"."
Excel en datums met weeknummer
Maar misschien wil je niet alleen de weekdag weten waarop je precies jarig bent, maar ook in welke week dat is. Hiervoor is de Excel-functie WEEKNUMMER(serieel_getal;type) beschikbaar. Het tweede argument bepaalt de dag in de week waarop de week begint. Met waarde 11 start je op een maandag; de waarden 12 t/m 17 staan daarmee dus voor dinsdag tot aan zondag.
Maar typen 11 t/m 17 zijn voor Europa niet zo relevant. In de VS start de eerste week in de week waarin 1 januari valt. In Europa wordt daarentegen ISO 8601 als norm gehanteerd en geldt als eerste kalenderweek de week waarin de eerste donderdag van het jaar valt. Hierdoor wijken de kalenderweken in de VS en Europa soms van elkaar af. Tot Excel 2007 moest je dit probleem oplossen met complexere Excel-formules of een VBA-functie. Met type 21 (Systeem 2; begin op maandag) is dat vanaf genoemde Excel-versie niet meer nodig en wordt de ISO-norm aangehouden:
=WEEKNUMMER(serieel_getal of cel met datum;21)
Bij dit zogeheten Systeem 2 mag de week niet op een willekeurige datum starten. Het is daarmee dus niet mogelijk om de week met een parameter 27 op zondag te laten beginnen.
Vakantiedagen berekenen in Excel
Hoeveel vakantiedagen heb je nog? Of wanneer is je vakantie voorbij? De functie WERKDAG() berekent vanaf een bepaalde begindatum plus aantal dagen de einddatum. De weekeinden (zaterdag en zondag) worden daarbij niet meegenomen. Met het argument ‘Vakantiedagen’ geef je aan welke data niet hoeven worden meegeteld, bijvoorbeeld een feestdag als Koningsdag of een bedrijfsvakantie. Je kunt ook een lijst met feestdagen of andere vrije dagen maken en de naam van het bereik in de functie invoegen.
Dan is er ook nog de Excel-functie WERKDAG. INTL(). Hiermee kun je met een extra argument rekening houden welke en hoeveel dagen als weekenddag gelden. Een weekend bestaat niet in elk land (of branche) uit dezelfde dagen. Als je hier niets invoert of de ‘1’, gelden de zaterdag en zondag als weekend – zoals dat voor de meesten het geval zal zijn. Met de ‘2’ worden zondag en maandag als weekend gedefinieerd – gebruikelijk in de kapperswereld. Op die manier gaat het verder tot en met 7, waarmee het weekend op vrijdag en zaterdag zou vallen.
Met de getallen ’11’ tot ’17’ bombardeer je wederom een afzonderlijke dag als weekend. De 11 staat daarbij voor de zondag, de 12 voor de maandag enzovoorts tot 17 (zaterdag).
De tijd tussen twee data is te berekenen met de functie NETTO.WERKDAGEN(begin datum;einddatum). Als in een tabel bijvoorbeeld een begin- en einddatum voor een project in een aparte cel staan, berekent de functie het aantal dagen zonder de weekends. Dat kan handig zijn om bijvoorbeeld makkelijker uitbetalingen te berekenen. Je kunt ook andere vakantiedagen als extra parameter aangeven. Staan in een tabel bijvoorbeeld in A2 en A3 de begin- en einddatum van een project en in de cellen D2 t/m D9 een overzicht van feest- of vakantiedagen, dan ziet de formule er als volgt uit:
=NETTO.WERKDAGEN(A2;A3;D2:D9)
De functie =NETWERKDAGEN.INTL() bevat nog de extra parameter ‘weekend’. Daarmee kun je het aantal vrije dagen in een week exact vastleggen. De ’11’ staat voor een werkweek van maandag t/m zaterdag, oftewel 6 werkdagen.
Niet-officiële Excel-functies
Misschien vraag je je af en toe wel eens af hoe lang het duurt tot je (eindelijk) met pensioen kunt. Dat kan met een handige functie die niet eens in een wizard is opgenomen. Met de functie DATUMVERSCHIL (begindatum;einddatum;tijdseenheid) kun je het aantal volledige jaren, maanden en dagen tussen twee data berekenen.
Geef bij deze functie de cellen op met de begin- en einddatum. Als derde parameter typ je tussen dubbele aanhalingstekens de gewenste tijdseenheid. “Y” staat daarbij voor jaren, “M” voor maanden en “D” voor dagen. Je kunt ook de cel invoeren waarin je de letter hebt staan (zie afbeelding).
Om tenslotte te weten te komen hoeveel dagen je leeft, kun je deze formule gebruiken:
=VANDAAG() - DATUM(geboortejaar;maand;dag)
Helaas kan ook Excel er weinig aan doen dat je elke dag toch weer een dagje ouder wordt …
(Rainer Schwabe / Marcel van der Meer, c’t magazine 1-2/2018)
Nog meer tips voor Excel nodig? Ontdek hoe je gegevens handiger invoert en andere slimme Excel-functies gebruikt of lees alle informatie in c’t magazine.
