Slimme Excel-trucs
Efficiënter werken in Excel Onder de GUI van de Windows-versie van Excel gaan handige functies schuil. Microsoft heeft ze alleen goed verstopt. Daarnaast kun je complexe formules die je zelf hebt verzonnen beschermen tegen kopieergedrag van anderen. Hierv
Excel is net een toverdoos. Een aantal handige functies uit oudere edities zit nu alleen zo diep in het programma weggestopt dat je ze bijna niet meer kunt vinden. Voorbeelden zijn de wizard voor draaitabellen en -grafieken of het gegevensformulier. Ondanks dat je ze niet meer direct in het lint terugziet, zijn ze er nog steeds. Met enkele toetsen en een nieuw toe te voegen knop zijn ze zo weer actief.
Vergeten wizard
Draaitabellen zijn een krachtig hulpmiddel om gegevens in een lijst of database te analyseren. Excel beschouwt een lijst als een gesloten bereik met titel, waarin geen lege kolommen of rijen voorkomen. Anders is het einde van de lijst bereikt.
Je kunt in Excel heel snel een draaitabel maken. Met ingedrukte linkermuisknop geef je gewoon aan waar en op welke manier de analyse moet plaats-
vinden. Selecteer je in Excel 2016 via het menu 'Invoegen' de knop 'Aanbevolen grafieken', dan zie je al suggesties voor de positionering en de lay-out verschijnen.
Normaal gesproken wordt een draaitabel uit een enkele lijst gemaakt. Tegelijk meerdere bereiken van een werkblad erbij betrekken kan ook. Hiervoor krijg je hulp van een functie die in nieuwere versies lijkt te zijn vergeten.
De handige 'Wizard Draaitabel en draaigrafiek' was tot en met Excel 2007 van de partij. Maar ondanks dat je die niet meer via het menu of de tabbladen kunt oproepen, is hij niet verdwenen. Hij staat nog steeds onder het overzicht van alle Excel-opdrachten. Klik bovenaan in de Werkbalk Snelle toegang op het pijltje dat naar beneden wijst. Via de optie 'Meer opdrachten' open je het venster voor de opties van Excel. Selecteer vervolgens in het drop-downmenu 'Alle opdrachten'. Vanwege de alfabetische volgorde zul je de wizard vrij onderaan vinden. Met een klik op 'Toevoegen', verschijnt het pictogrammetje bovenaan bij de Snelle toegang en kun je de functie weer makkelijk activeren. De wizard bestaat uit drie stappen. Wil je tegelijk meerdere bereiken bij je analyse betrekken, dan moet je bij de eerste stap de optie 'Meervoudige samenvoegingsbereiken' inschakelen. Je kunt met de functie dus meerdere celbereiken erbij betrekken en eventueel een externe gegevensbron.
Diep begraven
Naast de wizard voor draaitabellen en -grafieken lijkt ook die van het Gegevensformulier met pensioen te zijn gestuurd. Met deze oeroude functie kun je prima gegevens registreren, beheren en met name ook snel aanvullen. Sinds Excel 2007 vind je het Gegevensformulier onder de opdrachten niet meer terug bij de G. Het is nu bij de F van formulier te vinden. Om de comeback van het Gegevensformulier helemaal te laten slagen, moet je de functie eerst weer toevoegen aan de Werkbalk Snelle toegang – op dezelfde manier als beschreven met de wizard voor draaitabellen.
Zet de cursor in de lijst en klik op de nieuwe knop voor het formulier. Voer je een nieuwe gegevensset in, dan verschijnt die onderaan de lijst. Ook worden automatisch gegevens toegevoegd die via het formulier worden ingevoerd. Je hebt hier dus met dynamische tabellen te maken.
Weggetoverd
Grotere werkbladen met veel zelf verzonnen complexe formules hebben je vaak de nodige hoofdbrekens gekost. Die knowhow geef je niet altijd graag zomaar prijs. Met weinig moeite kun je de info goed verbergen, zodat derden de achterliggende formules niet kunnen zien. Je kunt formules compleet aan het zicht onttrekken, zodat ze ook niet meer in de bewerkingsbalk te zien zijn. Ook is het dan niet meer mogelijk om cellen met formules te bewerken. Zolang je de beveiliging niet opheft, blijven ze afgeschermd.
Selecteer voor het verbergen van de inhoud eerst de gewenste cellen. Je kunt daarbij ook niet-aaneengesloten bereiken kiezen of het hele werkblad. Ga via 'Start / Opmaak' naar de optie 'Cellen opmaken' en zet onder het tabblad 'Bescherming' een vinkje bij 'Verborgen'.
Daarna is er echter nog niets verdwenen. Om de inhoud echt onzichtbaar te maken, moet het werkblad nog worden beveiligd. Klik hiervoor in het lint op 'Controleren' en dan op de knop 'Blad beveiligen'. Ga naar de optie 'Het werkblad en de inhoud van vergrendelde cellen beveiligen'. Klik je daarna weer op 'Beveiliging blad opheffen', dan komt de content weer tevoorschijn. Om te voorkomen dat iedere gebruiker van de tabel de beveiliging ongedaan kan maken, kun je bij het opzetten van de beveiliging ook een wachtwoord invoeren.
In plaats van iets te laten verdwijnen kun je rijen of kolommen voor meer overzicht ook snel verbergen. Met de combinatie Ctrl+0 verberg je geselecteerde kolommen, met Alt+0 haal je ze weer tevoorschijn. Met rijen werkt het vergelijkbaar; Ctrl+9 verbergt ze, Alt+9 toont ze weer. Je kunt voor de zichtbaarheid ook de aangrenzende rij of kolom selecteren en via een rechtsklik kiezen voor 'Zichtbaar maken'.
Geheim werkblad
Maar waarom iets verbergen als iedereen toch kan zien (aan de rijnummers of kolomkoppen) dat dat is gebeurd? Klik je met de rechtermuisknop, dan is de rij of kolom immers zo terug. Om die reden kun je voor geheime informatie of berekeningen beter een apart werkblad nemen.
Met Visual Basic kun je het geheime werkblad laten verdwijnen. Start de Visual Basic-editor via de toetscombinatie Alt+F11. Open daarna de Projectverkenner via het menu 'Beeld'. Je kunt ook op Ctrl+F11 drukken. Met F4 open je het venster 'Project – VBAProject'. Selecteer in het projectvenster eerst het werkblad dat je wilt verbergen en klik in het venster 'Eigenschappen' op 'Visible'. Stel daar 'xlSheetVeryHidden' in. Daarmee heb je alles wat in de Visual Basic-editor moet gebeuren gedaan. Je sluit de editor weer met Alt+F4. In het Excel-document moet wel nog minstens een werkblad worden weergegeven.
Daarna ziet niemand meer dat het eigenlijk om een werkblad gaat. Alle berekeningen en info zijn immers uit de interface verdwenen. Verwijder je het VeryHidden-attribuut, dan werp je de sluier weer van het blad af.
Om te voorkomen dat iedereen dat zomaar kan doen en dus alsnog kan zien hoe goed je in formules bouwen bent, moet je het blad ook voorzien van een wachtwoord. Klik met de rechtermuisknop op het project in de Visual Basiceditor en selecteer in het snelmenu 'Eigenschappen van VBAProject'. Onder het tabblad 'Beveiliging' kun je het wachtwoord invoeren. Daarna is het blad met de geheime info alleen nog na invoer van dat wachtwoord te ontmantelen. Sla het bestand tenslotte op als Excel-werkmap met macro's.
Snel omzetten
Lijsten kun je omzetten in tabellen, die net als een kameleon snel van kleur kunnen wisselen. Maar vrolijke kleurtjes zijn niet het belangrijkste voordeel van tabellen; dat is eerder de automatische aanpassing ofwel het dynamische aspect.
Als je een lijst omzet in een tabel, kun je alle gegevens makkelijk selecteren. Houd de toetsencombinatie Ctrl+Shift ingedrukt en druk vervolgens op de 8.
Druk je Ctrl en Shift samen met een pijltoets in, dan selecteer je alleen de gegevens in de richting van de pijl. Met ingedrukte Ctrl-toets plus een of meerdere muisklikken achterelkaar kun je meerdere bereiken tegelijk selecteren.
Met de knop 'Opmaken als tabel' (onder 'Start') maak je het werkblad kleurig. Ga je met de muis over het uitklappende venster, dan zie je al hoe de tabel er na bevestiging uit komt te zien. Verder worden de kolommen voorzien van kleine knopjes met driehoekjes. Deze dienen voor het filteren van gegevens. Je filtert alleen datgene eruit wat je ook echt wilt. Als je de filterfunctie kunt missen, kun je deze met de toetsen Ctrl+Shift+L laten verdwijnen – en indien nodig ook weer tevoorschijn halen.
Als vanzelf
Breid je een tabel uit met een nieuwe rij of kolom (of meerdere), dan worden ze meteen opgenomen. Zodra je de gegevens in de cellen ernaast invoert, wordt de opgemaakte tabel groter. Daarnaast werkt Excel formules en functies bij die betrekking hebben op de tabel. Je hoeft ze dus niet meer extra te bewerken. Maar de opmaak als tabel biedt nog meer voordelen: met een slicer worden de resultaten weergegeven om ze meteen te analyseren – handig om bijvoorbeeld onlogische zaken op te sporen of om juist de beste resultaten te bepalen. Klik je via 'Hulpmiddelen voor tabellen / Ontwerpen'op de functie 'Slicer invoegen', dan krijg je een tekstveld te zien waarin je de gewenste gegevens direct overzichtelijk kunt zien.
Je verwijdert het veld weer door het aan te klikken en op Delete te drukken. Op die manier kun je ook snel resultaten voor het gehele blad bepalen. Als je in het menu 'Hulpmiddelen voor tabellen / Ontwerpen' een vinkje zet bij 'Totaalrij', telt de tabel automatisch alle waarden bij elkaar op. Met een klik op het bedrag verschijnt een drop-downmenu, waarmee je ook andere zaken kunt berekenen – bijvoorbeeld het gemiddelde, minimum of het maximum. Klik je bovenaan op 'Converteren naar bereik' (nog steeds in het menu 'Hulpmiddelen voor tabellen / Ontwerpen'), dan maak je de tabelopmaak weer ongedaan.
Mits ...
Met de voorwaardelijke opmaak kun je zelf opmaakregels definiëren. Excel voert de opmaak dan uit als aan een bepaalde voorwaarde wordt voldaan. Met de voorwaardelijke opmaak regel je ook het inbedden van gegevensbalken en pictogrammen in cellen om bijvoorbeeld trends weer te geven. Verder kun je er tabellen mee vergelijken door alleen de verschillen te laten weergeven.
Je vindt de voorwaardelijke opmaak via het menu 'Start'. Klik op 'Nieuwe regel' en selecteer onderaan de optie 'Een formule gebruiken om te bepalen welke cellen worden opgemaakt'. Voer daarna
bijvoorbeeld =A1<>Blad1!A1 in – afhankelijk van waar de tabel staat waarmee je de vergelijking wilt maken. Daarna worden alleen de cellen weergegeven die van elkaar verschillen.
Beperken
Wil je alleen een bepaalde gegevensset zien? Dan kun je om te beginnen het best een drop-downmenu maken. Dat ziet er niet alleen elegant uit, maar is ook handig om de gegevens te selecteren. Je hebt hiervoor de 'Hulpmiddelen voor ontwikkelaars' nodig. Als je het betreffende menu ('Ontwikkelaars') nog niet ziet, moet je dat eerst activeren via 'Bestand / Opties / Lint aanpassen'.
Vervolgens wil je een menu maken waarin je de gegevens met een muisklik kunt selecteren. Voeg via de knop 'Invoegen' een 'Keuzeveld met invoervak' in. Zodra je de hiervoor bestemde knop hebt geactiveerd, kun je met ingedrukte linkermuisknop de grootte van het veld binnen het werkblad bepalen. Daarna moet je in het nog lege veld de opmaak vastleggen. Klik met de rechtermuisknop op het veld dat je net hebt gemaakt en ga naar 'Besturingselement opmaken'. Bepaal het invoerbereik – ofwel de hele lijst.
Voer bij 'Koppeling met cel' een willekeurige cel in. Later zal hier de positie van de betreffende gegevensset worden weergegeven (in ons voorbeeld A28). Selecteer je een item, dan wordt in de gekoppelde cel de positie van het item in de lijst aangegeven.
Vind je het storend dat de positie wordt weergegeven, dan kun je dat met een truc oplossen: witte letters op een witte achtergrond. Selecteer voor deze cel dus gewoon wit als tekstkleur en je ziet niets meer. Je kunt ook de cel koppelen waarop het keuzeveld staat. Maar dan wordt het lastig die cel te selecteren. Als je het veld weer wilt verwijderen, selecteer je het gewoon en druk je op Delete. Selecteren gaat overigens het best met de Ctrl-toets ingedrukt.
Klikken en kijken
Met de Index-functie laat je vervolgens gegevens weergeven die horen bij de geselecteerde gegevensset in het dropdownmenu – handig om bijvoorbeeld snel de omzetcijfers, kosten en winst van een bepaalde buitendienstmedewerker in één oogopslag te bekijken. Je hoeft dan alleen de achternaam van de betref- fende persoon te selecteren en je krijgt zijn of haar resultaten en info meteen te zien.
Geef binnen de functie eerst het bereik van de lijst aan ofwel de tabel zonder de koppen. Het maakt hier niet uit of je daarbij voor een matrix of voor een verwijzing kiest. Geef vervolgens bij Rij_ getal de cel op waarin de lijstpositie staat (A28 in ons voorbeeld). Excel haalt de informatie dan uit de gegevensset die via het eerder gemaakte drop-downmenu is geselecteerd. Omdat de verwijzing naar deze cel absoluut moet zijn, moet je nog op F4 drukken. Je ziet dan dat er dollartekens om de waarde worden geplaatst. Geef als laatste aan uit welke kolom van de gegevensset de informatie moet worden gehaald. Het getal gaat daarbij uit van het aangegeven bereik. In ons geval zijn dat alle kolommen; zou je daarentegen alleen met bijvoorbeeld kolom C, D en E werken, dan is kolom C de eerste. In het veld Bereik_getal hoef je niets in te voeren.
Kopieer deze functie naar de overige cellen en wijzig daarbij steeds het kolomgetal (kolom 2 is die met de voornamen, kolom 3 voor de regio's enzovoorts). Zo krijg je uiteindelijk de hele gegevensset te zien. De tabel, het drop-downmenu en de Index-functie zijn nu aan elkaar gekoppeld. Selecteer je een gegevensset, dan worden meteen de bijbehorende data getoond. (mvdm)