Excel werkbladen efficiënt gebruiken

Excel doet niet altijd wat je verwacht. Soms verwerkt het je invoer verkeerd, worden gegevens of formules om onverklaarbare redenen als foutief gemarkeerd of lukt het niet om uren bij elkaar op te tellen. We laten zien hoe je daar een eind aan maakt en Excel werkbladen efficiënt gebruikt.

Niet alle standaardinstellingen van Excel zijn even handig. Het programma suggereert regelmatig goedbedoelde correcties of wijst je met een gekleurd driehoekje in een cel op vermeende fouten. Klik je op zo’n driehoek, dan verschijnen er zogeheten foutindicatoren met verschillende correctie­mogelijkheden. Zelfs als je daar ‘Fout negeren’ kiest, verschijnt het driehoekje opnieuw als je later iets wijzigt in de cel. Vooral de melding ‘De formule in deze cel verwijst naar een bereik waaraan extra getallen grenzen’ is meestal overbodig. De regels voor foutcontrole blijven actief tot je ze uitschakelt. Via ‘Bestand / Opties’ kun je in de categorie ‘Formules’ elke afzonderlijke regel deactiveren.

Een rode driehoek bij een cel geeft aan dat daar een opmerking bij is ingevoerd. Met de toetscombinatie Ctrl+Shift+O selecteer je snel alle cellen met opmerkingen, waarna je alle opmerkingen in een keer kunt verwijderen via het tabblad ‘Controleren’ van het lint. Om alleen de driehoekjes te verbergen, selecteer je bij ‘Opties / Geavanceerd/Weergave’ de optie ‘Geen opmerkingen of indicatoren’.

Met groene driehoekjes wijst Excel je op – hopelijk alleen vermeende – fouten. Via de opties kun je afzonderlijke regels voor foutcontrole uitschakelen.

Wat Excel automatisch doet is vaak handig, maar kan soms ook irritant zijn. Gelukkig kun je zelf kiezen wat je belangrijk vindt. Het automatisch vullen van aangrenzende cellen via de vulgreep rechtsonder in de selectie is bijvoorbeeld een praktische hulp. Excel is zelfs slim genoeg om bij het doorvoeren van reeksen zoals kwartalen slechts tot vier te tellen en dan weer vooraan te beginnen. Maar het langdurig weergeven na het automatisch doorvoeren van de knop (SmartTag) voor plakopties kan storend zijn. Die knop verdwijnt pas als je een nieuwe bewerking uitvoert. Je kunt hem uitschakelen bij de geavanceerde opties onder ‘Knippen, kopiëren en plakken’. Bij het maken van een selectie verschijnt ook de knop ‘Snelle analyse’. Die kun je uitschakelen bij de opties in de categorie ‘Algemeen’ onder ‘Gebruikersinterfaceopties’.

Nog een tip voor de vulgreep: als je in een cel de gegevens berekent op basis van aangrenzende kolommen, dan zorgt een dubbelklik op de vulgreep ervoor dat die formule automatisch wordt doorgevoerd in de kolom. Dat gebeurt dan tot zo ver als de aangrenzende kolom gevuld is.

Bij het invoeren van gegevens suggereert Excel automatisch overeenkomstige inhoud uit de kolom zodra je de eerste tekens intypt. Dat kun je bij de geavanceerde instellingen uitschakelen via ‘Opties voor bewerken / Automatisch aanvullen van celwaarden activeren’.

Goedbedoelde functies kunnen in de weg zitten. De SmartTags voor het automatisch aanvullen en de snelle analyse kun je gelukkig afzonderlijk uitschakelen.

Als Excel invoer als ‘123’ als ‘1,23’ weergeeft, is onder ‘Geavanceerd \ Opties voor bewerken’ de optie ‘Automatisch een decimaalteken invoegen’ geactiveerd. Daar kun je ook instellen hoeveel decimaaltekens Excel na de komma moet gebruiken.

Bij het invoeren herkent Excel meestal automatisch om wat voor soort gegevens het gaat. Maar vooral bij het invoeren van cijfers interpreteert Excel dat soms verkeerd. Gebruik je bijvoorbeeld productnummers als ’29/12/18′ of ’29-12-18′, dan maakt Excel daar de datum ’29-12-2018′ van. Wil je dat invoer ongewijzigd in cellen terechtkomt, dan maak je het celbereik van tevoren op als tekst. Je kunt dan de foutindicator krijgen ‘Deze cel bevat een datumreeks waarin het jaartal slechts met twee cijfers wordt aangegeven’. Als je dat vervelend vindt, schakel je de betreffende regel voor foutcontrole uit via ‘Bestand / Opties / Formules’.

Een lang getal zoals ‘12345678901’ toont Excel in de kolom als die breed genoeg is (standaard 11 cijfers), maar als het niet past krijg je een weergave als ‘1,234567E+11’. De ‘E’ voor exponent geeft aan dat de komma het betreffende aantal plaatsen opschuift – in dit voorbeeld naar de elfde positie. Je kunt een apostrof aan het begin typen om een getal als tekst in de cel te zetten en dat te vermijden, maar dan kun je er niet meer mee rekenen. Daarom kun je beter de celopmaak wijzigen van Standaard naar Getal.

Om goed te kunnen afronden, moet je Excel precies vertellen hoe het daarbij te werk moet gaan. Gebruik je de knop ‘Minder decimalen’ op het tabblad ‘Start’ van het lint, dan rekent Excel verder met de verborgen decimalen, met afrondingsfouten tot gevolg. Het aantal decimalen kun je ook via een aangepaste opmaak bepalen (Ctrl+1). Kies je bijvoorbeeld de aangepaste opmaak ‘0,00’ dan wordt de invoer ‘77,777’ weergegeven als ‘77,78’. Maar Excel rekent wel verder met de drie cijfers achter de komma.

Afrondings­verschillen kun je vermijden met de afrondingsfunctie =AFRONDEN(getal;aantal_decimalen). Die kan getallen zowel voor als achter de komma naar boven of naar beneden afronden. Een positief getal als tweede argument bepaalt het aantal decimalen na de komma. Een negatief getal geeft juist aan op hoeveel decimaalposities vóór de komma moet worden afgerond. Zo rondt -1 af op hele tientallen, -2 op honderdtallen enzovoort.

Bij bijvoorbeeld je belastingaangifte kun je cijfers na de komma weglaten, maar soms moet je afronden of juist tot op twee decimalen nauwkeurig zijn. Als het erom gaat bedragen op hele euro’s af te ronden, mag je dat in je voordeel doen. Inkomsten rond je af naar beneden, zodat de centen wegvallen, maar kosten voor een bedrijfsvoering of andere uitgaven rond je juist naar boven af op hele euro’s. Maak daarvoor in een hulpkolom gebruik van de functies =AFRONDEN.NAAR.BENEDEN(getal, aantal-decimalen) of =AFRONDEN.NAAR.BOVEN(getal, aantal-decimalen) en het gewenste aantal decimalen.

Het hoeft niet altijd tot op de komma nauwkeurig te zijn. Excel biedt verschillende functies voor het afronden.

De Excel-kalender begint op 1-1-1900 en eindigt op 31-12-9999. Intern is de datum niet meer dan een oplopend getal tussen 1 en 2.958.465. Invoer die als getal wordt getoond, geef je met de toetscombinatie Ctrl+Shift+# snel een datumopmaak.

Omdat het intern om getallen gaat, kun je op Excel werkbladen ook makkelijk met tijden rekenen. Met een eenvoudige Als-functie kun je bovendien tijden berekenen zoals nachtdiensten die over middernacht heen lopen: =ALS(B3<C3;C3-B3;C3-B3+1). Daarbij staat de begintijd in kolom B en het einde van de werktijd in kolom C. De eerste berekening in de ALS-formule berekent de tijdsduur op een dag, de tweede berekent het verschil als de dienst doorloopt tot de volgende dag. De totalen voor de uren zijn opgemaakt als ‘uu:mm’.

Standaard rekent Excel niet met negatieve waarden. Dat is wel mogelijk als je bij ‘Opties / Geavanceerd’ de optie ‘1904-datumsysteem gebruiken’ activeert. Die was oorspronkelijk bedoeld om te kunnen werken met oudere Excel-versies voor de Mac, waar het datumsysteem begon op 1 januari 1904. Pas wel op: alle in de werkmap aanwezige datums schuiven vier jaar en een dag op (1900 was een schrikkeljaar), zonder dat Excel je daarvoor waarschuwt. Die optie kun je dus het beste aanpassen zodra je een nieuwe werkmap aanmaakt.

Als er minder uren zijn gemaakt dan gepland, berekent Excel een negatieve tijd. Daarvoor moet je wel een geavanceerde optie activeren.

SAP en andere bedrijfssoftware geven negatieve waarden vaak aan met een minteken achter in plaats van voor een getal. Daardoor interpreteert Excel ze bij het importeren als tekst. Bij grote tabellen is het een enorm karwei om dat handmatig te corrigeren. Een enigszins verstopte conversiewizard kan je daar echter bij helpen. Selecteer een kolom met geïmporteerde gegevens. Ga vervolgens op het lint naar het tabblad Gegevens en klik in de groep ‘Hulpmiddelen voor gegevens’ op ‘Tekst naar kolommen’. Klik tweemaal op ‘Volgende’ en bij de derde stap van de wizard op de knop ‘Geavanceerd…’. Markeer dan de optie ‘Navolgend minteken voor negatieve getallen’ en klik op ‘OK’ en ‘Voltooien’ om de wizard af te ronden. Excel kan helaas wel maar één kolom per keer converteren.

Staan de mintekens na importeren aan de verkeerde kant? Dan kan de wizard voor conversie van tekst naar kolommen uitkomst bieden.

Als je de wizard niet bij elke nieuwe importbewerking opnieuw wilt uitvoeren, kun je in plaats daarvan een hulpkolom en de ALS-functie gebruiken. Daarmee controleer je eerst of er een minteken rechts van het getal staat. Met =RECHTS(tekst,aantal_tekens) en =LINKS(tekst,aantal_tekens) kun je rechts of links tekens uit een tekenreeks overnemen. De functie =LENGTE(tekst) telt het aantal tekens. Met een combinatie van die vier functies verplaats je het minteken van rechts naar links: =ALS(RECHTS(A13;1)=”–”;LINKS(A13;LENGTE(A13)–1)*–1;A13). De Als-functie controleert of het meest rechtse teken een minteken is en haalt het dan eraf. Daarna wordt het als tekst opgemaakte getal met -1 vermenigvuldigd om er een getal van te maken dat negatief is.

Hetzelfde kun je bereiken door de functie SUBSTITUEREN(tekst;oud_tekst;nieuw_tekst) in een ALS-functie te gebruiken. Daarbij vervang je het minteken door niets en zet je een minteken vooraan: ALS(RECHTS(D13;1)=”-“;-SUBSTITUEREN(D13;”-“;);D13). Je kunt de waarden uit de hulpkolom eventueel over de oorspronkelijke waarden heen kopiëren. Selecteer daarvoor de hulpkolom en sleep die met de rechtermuisknop ingedrukt naar de oorspronkelijke waarden. Kies in het snelmenu tot slot ‘Hierheen kopiëren als alleen waarden’.

Bij het invoeren van formules probeert Excel bij voorbaat al om fouten te voorkomen. Als je bij een eenvoudige formule als =SOM(A1:A10) het afsluitende haakje vergeet, begint Excel niet eens over die fout, maar voegt het afsluitende haakje automatisch toe. Maar bij een meermalen geneste opbouw zoals in een ALS-functie wordt er aandachtig gekeken of elk haakje wel op de juiste plaats staat. Die functie kan tot zeven niveaus diep genest zijn. Dat geldt zowel voor de expressie indien de conditie waar is als voor de expressie als hij onwaar is. Daarbij kun je het overzicht al snel verliezen.

Als je meerdere ALS-functies wilt combineren, kun je ze eenvoudigweg optellen: =ALS(…)+ALS(…) … + ALS(…). Dat moet dan een getal opleveren. Gebruik je in plaats van het plusteken de ampersand (&) om ze te koppelen, dan wordt het resultaat een tekst.

EN en OF kun je niet gebruiken voor het koppelen omdat dat zelf functies zijn. Je moet ze altijd vóór de argumenten plaatsen: =ALS(OF(A1>B1;A3);A1+A2;A4+A5).

Bij complexe formules kun je voor de duidelijkheid een hulpitem gebruiken door de functie =N(“tekst”) in de formule te verwerken. In de formule =ALS(A1>A2)+N(“Winstbepaling”) verschijnt de tekst alleen als geheugensteun in de formulebalk. Op het resultaat heeft de functie N() geen invloed omdat hij bij tekst de waarde 0 oplevert.

De cryptische foutmeldingen van Excel kunnen soms behoorlijk onduidelijk en vervelend zijn. Een melding als ‘#WAARDE’ zorgt er daarbij bijvoorbeeld voor dat je niet meer verder kunt rekenen. De cel als tekst opmaken heeft dan nog weinig nut. Bij functies als MAX(), GEMIDDELDE() en SOM() hebben de foutmeldingen als groot nadeel dat Excel dan helemaal geen waarde meer oplevert.

Met de functie AGGREGAAT(functie_getal, opties, matrix) omzeil je dat probleem vanaf Excel 2010 en kun je ondanks eventuele fouten toch verder rekenen. De functie levert ook een resultaat als er foutwaarden voorkomen in de berekening. Je kunt de functie AGGREGAAT() alleen gebruiken voor kolommen met gegevens, niet voor rijen. Het verbergen van een kolom heeft geen invloed op het resultaat.

Als eerste argument geef je een getal op voor de gewenste functie, bijvoorbeeld 1 voor GEMIDDELDE. Bij het tweede argument geef je aan wat er dan genegeerd moet worden, bijvoorbeeld de foutwaarden, verborgen rijen of geneste functies. Met ‘6’ geef je aan dat de foutwaarden genegeerd moeten worden. Als laatste geef je het celbereik op waarop de betreffende functie wordt toegepast. Die kun je ook als matrix definiëren met de toetscombinatie Ctrl+Shift+Enter.

Met de AGGREGAAT-functie rekent Excel stug door, ook als er foutmeldingen in cellen staan.

Fouten zijn niet meer wat ze geweest zijn. Je kunt ermee werken in berekeningen en in functies. Delingen als A10/Geen invoer leiden tot de foutmelding ‘#WAARDE’ als er tekst in een cel staat.

Tot en met Excel 2003 kon je de weergave van fouten alleen vermijden met een combinatie van ALS- en ISFOUT-functies, bijvoorbeeld =ALS(ISFOUT(A10/B10);”Omzet nog niet beschikbaar”;A10/B10). Vanaf Excel 2007 gaat dat gelukkig veel makkelijker met =ALS.FOUT(waarde;waarde_­indien_fout).

Het tweede argument is wat de functie moet weergeven als de berekende formule een fout oplevert. De functie kan overweg met de fouttypen #####, #DEEL/0!, #N/B, #NAAM?, #LEEG!, #GETAL!, #VERW! en #WAARDE!. Het fouttype #NB heeft zelfs een eigen ALS-functie voor de afhandeling: =ALS.NB(A10;”Waarde niet gevonden”). Vaak verschijnt #NB bij VERT.ZOEKEN en HORIZ.ZOEKEN als Excel een fout vindt in plaats van het zoekcriterium. Dat kun je vermijden met zoiets als =ALS.NV(VERT.ZOEKEN(C1;Provisies;2);”Geen gegevens gevonden”).

Voor een grafiek heeft de foutmelding #NB vaak vervelende gevolgen. Ontbreekt in een lijngrafiek een waarde in een reeks, dan zakt de lijn ineens naar het nulpunt of wordt hij vaak zelfs onderbroken.

Als op een werkblad een waarde ontbreekt, onderbreekt Excel de lijn in de grafiek. Dat kun je voorkomen met de optie ‘Gegevenspunten verbinden met lijn’.

Om dat te vermijden, klik je op de grafiek zodat die geselecteerd is. Ga dan op het lint naar ‘Hulpmiddelen voor grafieken / Ontwerpen’ en klik daar op de knop ‘Gegevens selecteren’. In het dialoogvenster ‘Gegevensbron selecteren’ klik je vervolgens op de knop ‘Verborgen en lege cellen’. Je kunt dan kiezen of je lege cellen als openingen of als nul wilt weergeven en de vervelende foutmelding #NB wilt onderdrukken (met ‘#N/A weergeven als een lege cel’). Je kunt daar ook bepalen of je gegevens in verborgen rijen en kolommen wilt weergeven in de grafiek. Selecteer je in dit dialoogvenster ‘Gegevenspunten verbinden met een lijn’, dan verschijnt de lijn zonder onderbreking.

(Rainer Schwabe, c’t magazine)