Wie vaak werkt met Excel, weet dat het spreadsheet-programma van Microsoft vol verborgen trucjes zit. We zetten enkele handige Excel-tips op een rij, speciaal voor de mensen die al wat bekender zijn met dit Office-programma.
Flash Fill
Als je regelmatig met Excel werkt, hoeven we je waarschijnlijk niets te vertellen over de functie AutoAanvullen in Excel, waarmee je heel eenvoudig bijvoorbeeld een getallenreeks door kunt laten lopen. (Ben je er niet bekend mee? Maak eens een reeks getallen, selecteer ze en trek het hoekje van je selectie uit.) Een variant die daarop lijkt is Flash Fill.
Met deze functie vul je niet zozeer een reeks waarden aan, maar kun je in één klap aangeven hoe Excel die waarden moet weergeven. Dit is bijvoorbeeld ideaal om in één klap een reeks namen die wisselend met hoofdletters en kleine letters zijn getypt, goed te zetten. Maar je kunt het ook gebruiken om bijvoorbeeld punten in komma’s te veranderen bij getalnotatie.
Het werkt kinderlijk eenvoudig. Stel je hebt een kolom in Excel met prijzen die allemaal op een andere manier geschreven zijn. De ene met een punt, de ander met een komma en allemaal zonder euroteken en met een verschillend aantal decimalen. Je wilt alles met een euroteken, een komma en twee decimalen. Maak een kolom aan naast de kolom met warrige gegevens, en laat Excel zien hoe je het wél gedaan wilt hebben.
Dus neem een waarde uit de eerste kolom, plaats die op dezelfde rij in de tweede kolom en gebruik daar een komma, kies bij Celeigenschappen voor Valuta etc. Doe dit voor twee of drie cellen, zodat Excel precies weet hoe je het wilt. Ga nu op een lege cel in de tweede kolom staan, klik op het tabblad Gegevens in het lint en vervolgens op Flash Fill. Excel vult nu de rest van de kolom in met de gegevens uit de eerste kolom, maar dan precies vormgegeven zoals jij wilt.
Als-formule
We beginnen met een eenvoudige formule, maar wel eentje die direct heel krachtig en veelzijdig is. Stel we hebben een lijst met leerlingen en cijfers willen graag weten wie er gezakt en wie er geslaagd is (waarbij we even doen alsof de hele toekomst van deze arme leerlingen afhangt van dit ene cijfer). Dit is het moment waarop de Als-formule van pas komt. Met ‘Als’ kun je Excel een waarde laten aangeven, op basis van de gegevens uit een andere cel.
In dit geval: als iemand een cijfer heeft dat hoger is dan een 5, dan is hij geslaagd, zo niet, dan is hij gezakt. Om dit om te zetten in een formule, maak je een nieuwe kolom aan die we Uitslag noemen. Klik in deze kolom op de eerste cel naast een cijfer en typ het volgende: =ALS(E2>5;"Geslaagd";"Gezakt"), waarbij je E2 vervangt voor de cel waarin het cijfer staat in jouw Excel-sheet. Je vertelt Excel nu: indien de inhoud van cel E2 hoger is dan 5, toon dan in deze cel het woord Geslaagd, is dat niet het geval, toon dan het woord Gezakt.
Zodra je op Enter drukt zie je het resultaat van je formule. Door nu op het groene vierkantje rechtsonder in de cel te klikken en naar beneden te slepen, pas je de formule toe op de rest van de kolom. Denk bij het invullen van de Als-formule altijd even: ‘als; dan; anders’.
Voorwaardelijke opmaak
Dit maakt de boel al een stuk overzichtelijker, maar het kan nóg duidelijker. Excel heeft namelijk ook een functie genaamd voorwaardelijke opmaak. Het idee daarachter is dat je een cel van een bepaalde opmaak kunt voorzien, aan de hand van de inhoud. In ons voorbeeld kun je ¬– in plaats van de Uitslag-kolom, ook gewoon een kleur toekennen aan de cijfers om aan te geven of het voldoende is of niet.
Je doet dit als volgt. Selecteer de kolom met cijfers en klik vervolgens op Start / Voorwaardelijke opmaak / Markeringsregels voor cellen / Groter dan. Geef nu het doorslaggevende cijfer aan (5 in ons geval) en kies een kleur (groen in ons geval). Nu krijgen alle cellen met een waarde hoger dan 5 een groene kleur. Andersom kan het natuurlijk ook, door alle cellen met een waarde lager dan 6 een rode kleur te geven.
Verticaal zoeken
Dan nu de diepte in. Stel je bent de nieuwe beheerder van een vereniging en je voorganger heeft er een zooitje van gemaakt. Het is je taak om zo snel mogelijk uit te zoeken wie welk bedrag aan contributie heeft betaald. Het enige probleem: je hebt twee lijstjes: een lijst met lidmaatschapsnummer en daarnaast het bedrag en een lijst met lidmaatschapsnummer en daarnaast de naam. Dat kun je handmatig gaan sorteren en samenvoegen, maar dat is foutgevoelig. Excel kan dat prima voor je doen met behulp van de functie Verticaal Zoeken.
Bekijk ons voorbeeld in de afbeelding. Lijst 1 heeft twee kolommen en 13 rijen (waarbij de nuttige inhoud begint op rij 3). Lijst 2 heeft eenzelfde omvang. Daaronder staat de gecombineerde lijst, waarin je alleen de eerste rij vult (lidmaatschapsnummer). Excel gaan we de rest erbij laten zoeken. Dit doe je met de volgende formule =VERT.ZOEKEN(B18;$A$3:$B$13;2;ONWAAR). Deze formule doet het volgende: hij kijkt naar de waarde in cel B18 en vindt daar een lidmaatschapsnummer.
Vervolgens doorzoekt hij cel A3 tot en met B13 ($A$3:$B$13) en zoekt daarbij de waarde die in de tweede cel van links staat (vandaar het getal 2). Onwaar bepaalt vervolgens dat de waarde die als resultaat van de formule wordt weergeven, identiek moet zijn aan de gevonden waarde (oftewel: waarde mag afwijken: onwaar).
Wanneer je nu deze formule in cel D18 typt in ons voorbeeld, zoekt Excel het juiste bedrag bij het juiste lidmaatschapsnummer. Kopieer de formule in alle cellen in de kolom om alle waarden in te vullen. Maar de namen dan? Die vul je op dezelfde manier, alleen pas je de formule nu zo aan dat niet lijst 1 wordt doorzocht, maar lijst 2, van D3 tot E13 oftewel: =VERT.ZOEKEN(B18;$D$3:$E$13;2;ONWAAR).
Plak deze formule in de kolom Naam en ook de namen worden automatisch ingevuld. We hebben voor dit voorbeeld eenvoudige gegevens genomen, maar je kunt je voorstellen dat je zo enorm veel gegevens probleemloos bij elkaar kunt zoeken.
Lees verder op de volgende pagina voor nog meer tips.
Extra spaties verwijderen
In het vorige voorbeeld was de sortering van de gegevens misschien een zooitje, maar de notatie van de namen was vrijwel hetzelfde (als dat niet het geval was, had je hoofdletters etc. kunnen aanpassen met Flash Fill). Het kan echter ook voorkomen dat er een spatie voor of na een naam staat, met name als de lijst handmatig is getypt. Dat kan heel vervelend zijn bij het sorteren van de gegevens, of wanneer de gegevens ergens worden geïmporteerd en ze daadwerkelijk identiek moeten zijn.
Spaties verwijderen met zoek en vervang? Dat kan, maar dan ben je ook de functionele spaties kwijt. Gelukkig is ook hier een formule voor. Plaats de formule =SPATIES.WISSEN(A1) in een cel in de kolom naast de kolom met de namen. A1 verwijst naar de cel waarin de waarde (naam in dit geval) met de spaties staat. In de cel waarin je de formule plaatst, zal de waarde van de cel in de formule worden getoond. Als de naam in A1 staat, is het dus logisch dat je de formule in een andere kolom op diezelfde eerste rij plaatst.
Alle extra spaties worden in deze nieuwe cel verwijderd en de functionele spaties blijven staan. Je kunt de formule vervolgens in alle cellen kopiëren waarin je deze wilt toepassen en de rij verandert automatisch mee.
Relatieve en absolute celverwijzingen
Het is handig dat Excel zelf begrijpt dat de cellen in een formule mee moeten veranderen wanneer je de formule kopieert en plakt. De grap is alleen: dat weet Excel niet uit zichzelf, jij bent degene die dat in je formule hebt gezegd. Excel werkt namelijk met relatieve en absolute celverwijzingen. Wanneer in een formule in cel B1 aangeeft dat de waarde uit cel A1 gehaald moet worden, dan is dat een relatieve verwijzing. Kortom, plak je de formule in cel B2, dan wordt de waarde niet uit A1 gehaald, maar uit A2.
Je kunt ook absolute verwijzingen toepassen. Dit doe je bijvoorbeeld wanneer je in een formule gebruikmaakt van een waarde uit een cel die voor iedere berekening hetzelfde moet zijn. In het voorbeeld uit de afbeelding: ongeacht waar we de formule plakken, willen we de waarde uit de relatieve cel (betaald, die dus steeds anders is) aftrekken van de waarde van de absolute cel (contributie, die altijd hetzelfde is). Een absolute celverwijzing maak je door voor de rij en de kolom een dollarteken toe te voegen. Wil je dat altijd de waarde uit Cel B16 gebruikt wordt? Dan heet de cel dus niet B16, maar $B$16.
Transponeren
Voor deze tip ga je ons absoluut op een dag dankbaar zijn. Wanneer je tabellen ontwerpt, dan gaat dat zelden de eerste keer goed. We hebben het dan natuurlijk niet over eenvoudige tabellen met drie rijen en kolommen, maar flinke tabellen met grote hoeveelheden gegevens. Bij het bouwen van zo’n tabel zul je net een keertje ontdekken dat het misschien toch handiger was om de kopjes die je als rijen hebt gebruikt als kolommen te gebruiken of andersom. Daar heeft Excel gelukkig een heel handige functie voor.
We nemen ons voorbeeld met de contributiecijfers. Als we deze tabel willen transponeren, oftewel, rijen van kolommen maken en andersom, dan selecteren we de gehele tabel en drukken op Ctrl+C om deze te kopiëren. Vervolgens zoeken we een lege plek in onze Excel-sheet met genoeg ruimte eronder en ernaast voor de getransponeerde tabel. Rechtsklik op die cel en kies vervolgens Plakken speciaal. Er verschijnt nu een menu en het enige dat je daarin hoeft te doen, is een vinkje plaatsen bij Transponeren.
Zodra je nu op OK klikt, wordt de tabel die je gekopieerd hebt hier geplakt, maar de kolommen zijn nu rijen geworden en andersom, inclusief de bijbehorende data. Niet alleen is dit sneller dan wanneer je het handmatig doet, het voorkomt ook dat je fouten maakt.
Tekst: Martin Gijzemijter