Databases

Aan het einde van deze les is bekend:
  • Welke eisen er worden gesteld aan het gebruiken van een database in Excel;
  • Hoe kolommen eenvoudig gewisseld kunnen worden;
  • Hoe een filter op één en meerdere kolommen gebruikt kan worden;
  • Hoe er via de filter gesorteerd kan worden;
  • Hoe een filter verwijderd kan worden;
  • Hoe de unieke waarden uit een kolom gehaald kunnen worden;
  • Hoe de database op basis van een of meerdere kolommen gesorteerd kan worden;
  • Hoe de optie zoeken en vervangen gebruikt kunnen worden;
  • Hoe de optie 'Ga naar' werkt;
Databases
Met grote tabellen werken kan soms lastig zijn omdat het overzicht weg is, of omdat de tabel slordig opgebouwd is. Excel kent krachtige hulpmiddelen om grote hoeveelheden gegevens te analyseren en verwerken, mits de tabel goed opgebouwd is. Aan het gebruik van tabellen, ook wel databases genoemd, zitten daarom een paar voorwaarden. De termen tabel en database worden door elkaar heen gebruikt.

Voorwaarden voor een database
Een tabel bestaat uit rijen en kolommen. Een database in Excel (en dat is een gegevensdump uit Exact of een ander pakket ook) dient aan een aantal voorwaarden te voldoen wil je er optimaal gebruik van kunnen maken. Dit gebruik via Subtotalen, Draaitabellen etc. wordt in latere cursussen uitgelegd.
  1. Een database bestaat uit velden en records. Termen die in Excel doorgaans niet gebruikt worden. Een veld is in Excel gelijk aan een kolom, en een record staat gelijk aan een rij. Een database bestaat uit een aaneengesloten reeks rijen en kolommen. Er dient geen lege kolom te zijn, en ook geen lege rij. Er mogen in een database wèl lege cellen voorkomen. Voorbeelden van velden zijn: Afdeling, Kostensoort, Grootboekrekening, Klantnummer, Aantal etc.;
  2. Een record bestaat uit een rij uit deze database waarbij je alle gegevens van één kostenplaats, bedrijf, regio, bewoner, patiënt of product ziet;
  3. Ieder veld dient een titel te hebben, of een kolomnaam zodat herkenbaar is welke gegevens er in die kolom staan. Dit maakt het sorteren van de gegevens eenvoudiger. De opmaak van een titel dient afwijkend te zijn aan de overige cellen, bijv. vet, andere puntsgrootte of lettertype. Op deze manier herkent Excel bij filteren en sorteren direct de titels.
  4. De titel of kolomnaam dient een unieke naam te bevatten en geen lege cel. Dit is voor de duidelijkheid en het verdere verwerken van de tabel bij o.a. draaitabellen;
  5. In een veld mag maar één soort gegeven staan.;
  6. Als er een veld 'Besteldatum' is, dan dient daarin niet staan 'afgelopen zaterdag'. Het kenmerk van een veld 'besteldatum' is dat het om een datum gaat. Met datums valt te rekenen, maar met de tekst 'afgelopen zaterdag' niet. Een veld waarin gerealiseerde aantallen komen te staan dient getallen te bevatten, geen tekst zoals '4 of 5'.
    Een veld telefoonnummer dient niet èn een vast nummer èn een mobiel nummer te bevatten. Maak een nieuw veld aan voor mobiele nummers.;
  7. Records dienen - in de meeste gevallen - uniek te zijn. Een klant heeft maar één correspondentieadres. Wanneer in de klantentabel een klant meerdere keren voorkomt, werkt de functie =VERT.ZOEKEN() niet goed, want die vindt alleen de eerste keer dat een waarde voorkomt. Mocht een klant twee correspondentieadressen hebben, maak dan een tweede veld 'Adres_2';
  8. Wanneer er gewerkt wordt met een export uit een ander systeem zoals Exact, zullen er geen unieke records zijn, maar zal een hele rij uniek zijn. Bovenstaande opmerking geldt daarom niet voor dit soort tabellen.;
  9. Tenslotte kan een database het beste op een apart tabblad komen te staan zonder verdere bewerkingen, of alleen een bewerking dat een nieuw veld oplevert en voor alle records geldt. Geef het een herkenbare naam, bijv. Data.;
  10. Een tip voor een database: Laat een paar lege rijen boven de tabel staan. Hierin kun je een paar gegevens zetten zoals de datum van vandaag =NU() of =VANDAAG(), of de datum waarop de gegevens voor het laatst zijn bijgewerkt.; Meer info.
Wisselen van kolommen
Het kan zijn dat tijdens het ontwerpen van een database de volgorde van de velden niet logisch bepaald is zodat gaandeweg het werken kolommen verwisseld moeten worden. Veelal wordt eerst de ene kolom in zijn geheel gekopieerd naar een kolom buiten de tabel, waarna de tweede kolom over de eerste heen gekopieerd wordt. Vervolgens wordt de eerste kolom over de tweede gekopieerd en ten slotte wordt de gekopieerde kolom buiten de tabel verwijderd. Een snellere methode is de ene kolom geheel selecteren, door op de kolomletter te klikken, de Shift-toets ingedrukt houden en dan de muis op de linker of rechterrand leggen van de te verslepen kolom. Terwijl je de linker muisknop ingedrukt houdt, sleep je de muis naar links of rechts. Excel toont vóór welke kolom de te verslepen kolom komt te staan:
In bovenstaand figuur wordt kolom B versleept tot voor kolom D, en dus gewisseld met kolom C.
Door de Ctrl-toets ingedrukt te houden wordt de kolom gekopieerd over een andere kolom heen. Er volgt géén waarschuwing dat de kolom al gegevens bevat die dan worden overschreven, zoals wel het geval is bij het kopiëren van een cel of cellenbereik.
Door de Alt-toets ingedrukt te houden in plaats van de Shift-toets wordt de nieuwe kolom overschreven door de versleepte kolom. Ook hier volgt géén waarschuwing. Via de Alt-toets is het eveneens mogelijk een selectie naar een ander werkblad te verplaatsen.

Sorteren en filteren


Wanneer een tabel of database voldoet aan de genoemde vereisten, kunnen er meerdere handelingen op uitgevoerd worden. Een filter leggen op de database zorgt ervoor dat slechts een deel van de gegevens getoond wordt. Alle rijen waarvan een record niet aan het filtercriterium voldoet worden verborgen. Daarnaast is het mogelijk om de gegevens te sorteren zodat ze overzichtelijk bij elkaar komen te staan. Na te hebben gesorteerd kunnen er eenvoudig tellingen toegevoegd worden aan de tabel via Subtotalen. Dit komt in een latere cursus aan de orde.
Filteren op één kolom
Ga ergens in de tabel staan, en klik op Data > Filter > Automatisch filter. Boven ieder veld komt dan een vierkantje met een driehoek te staan: Zodra bij een filter een criterium ingegeven is krijgt de zwarte driehoek de kleur blauw. Met de automatische filter kun je het volgende:
  • Van een veld alle records (rijen) tonen die voldoen aan een criterium, bijv. de maand 'jan-07'. Klik op de en de eerste duizend unieke items kunnen worden gekozen. Kies de maand 'jan-07' en Excel toont direct allen de records van de maand januari 2007.
  • Het is ook mogelijk om twee criteria op te geven waarop gefilterd moet worden. Klik hiervoor op de optie (Aangepast...) en het dialoogvenster Aangepast AutoFilter verschijnt. In het voorbeeld hiernaast zijn alle mogelijkheden om op te filteren getoond.
  • Deze criteria gelden voor zowel tekst, als voor getallen en datums. Wil je de boekingen zien van de januari t/m maart, en dus niet april, dan kan dat via:
    Let erop dat hier gekozen wordt voor ‘En’ en niet voor ‘Of’ want de inhoud van een cel dient aan beide criteria te voldoen.
Wanneer de filter gebruikt wordt, wordt in de meeste gevallen linksonder getoond hoeveel records er aan de criteria voldoen:
Filteren op meerdere kolommen
Wanneer op alle velden van de database een filter staat, kan ook gekozen worden voor een combinatie van filters.
Stel dat je van alleen april de bedragen van 1.000 euro en hoger wilt zien, om te kijken op welke grootboekrekeningen deze bedragen geboekt zijn. Kies dan in het veld Maand voor april. De keuzemogelijkheden van de andere filters worden dan beperkt door deze keuze. Ga naar het veld bedrag, en kies bij 'aangepast' voor de filteroptie 'is groter dan of gelijk aan'. Typ in het veld het getal 1000 en klik op OK. In het veld Rek.nr zijn dan alleen die grootboekrekeningen te zien die in april minimaal één bedrag van 1.000 euro of groter hebben.
In principe kunnen alle filters van de database gebruikt worden. Wanneer er geen enkel record voldoet aan de criteria van de filters, wordt een lege tabel getoond.
Sorteren via de filter
Wanneer een filter geselecteerd wordt, staat als standaard de optie [Alle categorieën]. Eronder volgen de items van dat veld, maar erboven staan nog twee opties: 'Oplopend sorteren' en 'Aflopend sorteren':

Bij het kiezen van één van deze twee opties wordt de gehele tabel gesorteerd op het actieve veld. Dit is een eenvoudige manier van sorteren. Een andere eenvoudige manier van sorteren is via de werkbalk Standaard door op één van deze icoontjes te klikken: . AZ is oplopend sorteren, ZA is aflopend sorteren.
Filters verwijderen
In een werkblad kan maar in één tabel tegelijkertijd een filter actief zijn. Wanneer de filter op de ene tabel niet meer nodig is, kan deze uitgezet worden via Data > Filter > AutoFilter.
Het is ook mogelijk op de tabel de filter in stand te houden, maar wel alle records laten zien. Dat gaat via Data > Filter > Alles weergeven. De vierkantjes met driehoek blijven dan direct bruikbaar.
Unieke items uit een veld halen
Soms is het handig om uit een database de unieke items te halen om controles te leggen of anderzijds tellingen uit te voeren. Hiervoor is nodig dat Excel de veldnaam of kolomtitel herkent. Die dient daarom van een ander lettertype te zijn, of vetgedrukt of iets dergelijks. Selecteer een heel veld en ga via Data > Filter > Uitgebreid filter... Excel komt dan met het dialoogvenster 'Uitgebreid filter':

Ook hier onthoudt Excel de selectie wanneer er al een keer gefilterd of gesorteerd is. Let bij het lijstbereik dus goed op dat de juiste selectie er staat. De selectie mag niet beginnen met een lege cel. Er gebeurt dan niets.
Vink het rondje aan bij 'Kopiëren naar andere locatie'. Het veld 'Kopiëren naar:' is nu grijs en onbruikbaar, maar wordt zwart na het rondje te hebben aangevinkt. Klik in het veld, en kies in hetzelfde werkblad een cel om vanaf daar een kolom te krijgen met de unieke waarden van de selectie. Vink ten slotte het vierkantje aan bij ‘Alleen unieke records’ en klik op OK.

Het is ook mogelijk om unieke combinaties uit een tabel te filteren. Hiervoor dienen dezelfde stappen te worden gevolgd als hierboven uitgelegd maar nu dienen eerst twee of meer aanéénsluitende kolommen geselecteerd te worden.

Wanneer de kolomtitel niet afwijkend is van de inhoud van de kolom of wanneer de cel leeg is, verschijnt de volgende foutmelding:
Sorteren
Het is mogelijk om de database te sorteren op maximaal drie velden. Selecteer een cel in de database en ga via Data > Sorteren en het dialoogvenster Sorteren verschijnt:

Wanneer de veldnamen door Excel herkend worden, zal het rondje bij 'Een veldnamenrij' aangevinkt staan. Als dat niet zo is, dan hebben de veldnamen geen afwijkende opmaak ten opzichte van de gegevens in het veld. Zorg ervoor dat een veldnaam vet is, onderstreept of een groter lettertype heeft of zet de kolomtitels vast via Venster > Titels blokkeren.
Er kan gesorteerd worden op drie velden waarbij de sorteervolgorde afhankelijk is van de volgorde van velden. Per veld kan er gekozen worden om oplopend of aflopend te sorteren. Sorteer je eerst op het veld 'Periode' en dan op de kostenplaats, dan zie je per Periode de kostenplaatsen.

Het is mogelijk om rekening te houden met hoofd- en kleine letters. Klik op de knop 'Opties' en vink het vierkantje aan bij ‘Hoofdlettergevoelig’. Kleine letters komen voor hoofdletters.
Wanneer je één of meerdere velden in een database hebt geselecteerd, maar andere velden niet en je wilt toch sorteren, dan komt Excel met een waarschuwing:

De optie van Excel betekent dat je de hele database selecteert en gaat sorteren op één, twee of drie velden zoals hiervoor omschreven. Wanneer je ervoor kiest om door te gaan met de huidige selectie, dan verdwijnt de integriteit van de database omdat je records gaat wijzigen! Zorg er dus altijd voor dat de hele tabel geselecteerd is voor het sorteren.
Sorteren op meer dan drie velden
De AutoFilter-optie kent maximaal drie velden waarop gesorteerd kan worden. In sommige gevallen is dit een belemmering. Wanneer er op vier velden gesorteerd moet worden, bedenk dan eerst welk veld als vierde opgegeven zou worden. Sorteer dan de tabel op dit veld. Vervolgens sorteer je op de andere drie velden. Excel laat namelijk de gegevens waarvan de eerste drie velden gelijk zijn staan in de volgorde van het vierde veld.

Zoeken - Vervangen


Naast sorteren en filteren van tabellen zijn ook de zoekfunctie en zoek- en vervangfunctie erg handig. In de volgende paragrafen wordt besproken hoe gebruik kan worden gemaakt van de zoekfunctie in Excel, evenals de zoek- en vervangfunctie.
Bij deze twee functionaliteiten geldt dat in het hele werkblad gezocht wordt wanneer er één cel geselecteerd is. Wanneer een selectie gemaakt is zoals een aantal kolommen of rijen, dan werken deze functionaliteiten alleen in het geselecteerde gebied.
Zoeken
Wanneer een werkblad veel gegevens bevat is het moeilijk overzicht te behouden en een gegeven zoeken. Via Ctrl+F wordt het dialoogvenster 'Zoeken en vervangen' getoond. Standaard staan de opties uit, maar deze zijn erg handig. In onderstaande figuur worden de opties getoond van het tabblad Zoeken:
Zoeken naar afdelingen met in de omschrijving 'koop', vindt zowel de afdeling Inkoop als de afdeling Verkoop. Wanneer je het vierkantje aanvinkt bij ‘Identieke celinhoud’ vind je niets omdat er geen cel is met alleen te letters 'koop'. Ook wanneer je alleen het vierkantje aanvinkt bij 'Identieke hoofdletters/kleine letters' vind je niets omdat de namen van de afdelingen met een hoofdletter beginnen, en de zoekwaarde alleen kleine letters bevat.
De zoekrichting kan aangepast worden als de eerste waarde in een kolom gevonden moet worden. Het is ook mogelijk eerst de kolom te selecteren en dan pas in die selectie te zoeken.
Bij 'Binnen:' kan gekozen worden om alleen in het huidige werkblad te zoeken (dit is standaard), maar er kan ook gekozen worden voor de gehele werkmap, dus alle werkbladen. Wanneer je over verschillende werkbladen heen een waarde zoekt, kun je alle werkbladen selecteren door de Ctrl-toets ingedrukt te houden en dan de bladnamen aan te klikken. Maar het kan voorkomen dat op zo’n werkblad een cellenbereik geselecteerd is. Wanneer de optie ‘Blad’ niet aanstaat, kan het voorkomen dat de gezochte waarde er wel is, maar niet gevonden wordt omdat op dat specifieke werkblad alleen de selectie onderzocht wordt.

Bij ‘Zoeken in:’ formules wordt in alle invoer gezocht. Dus in tekst, getallen, formules en hyperlinks.
Wijzig bij 'Zoeken in:' "Formules" in "Waarden" en ook de uitkomst van een formule wordt gevonden. Standaard wordt in de geschreven formule gezocht naar de tekenreeks. Ten slotte kan ook alleen in opmerkingen gezocht worden.
Zoeken en vervangen
Het dialoogvenster om gegevens te vervangen kan opgevraagd worden door toetsenbordcombinatie Ctrl+H maar ook via Bewerken > Vervangen. Ook voor deze functionaliteit geldt dat de knop Opties handige extra mogelijkheden geeft. Zie hiernaast.
In het veld 'Zoeken naar:' kan de zoektekst worden ingevuld, en in het veld 'Vervangen door:' de tekst die ervoor in de plaats moet komen.
Ook bij het vervangen van de zoekwaarde kan gekozen worden voor hele cellen en identieke hoofd/kleine letters.
Jokers
Bij zowel de zoekfunctie als de vervangfunctie kan gebruik worden gemaakt van jokers. Het teken ? staat voor één karakter, en het teken * staat voor meerdere tekens. Zoeken naar bijv. de kostenplaats 'Goud?' Levert de plaats Gouda op, maar zoeken naar 'Goud*' levert Gouda en Gouderak op.
Zoeken naar plaatsen met '*der*' vindt Genderen, Goedereede en Gouderak en ook Den Helder.

Wanneer er gezocht moet worden naar een ? of * in een cel, zet dan in het veld 'zoeken naar:' eerst een tilde ~. Deze staat links boven de 1. Met nevenstaand voorbeeld kun je cellen vinden met een vraagteken erin:
Ga naar
In een grote tabel is het vak lastig om snel naar de plek te gaan waar je heen wilt. Met de opties sorteren en filter kun je de tabel al snel kleiner en overzichtelijker maken, maar Excel heeft ook de functionaliteit 'Ga naar'. Via Ctrl+G, of via Bewerken > Ga naar... verschijnt het dialoogvenster 'Ga naar':
In het veld Verwijzing kun je het adres opgeven van een cel waar je heen wilt gaan, bijv. BC345. Klik op OK en je springt meteen naar die cel. Hiermee kan ook een bereik opgegeven worden zoals A1:B25. klik op OK en dat bereik wordt geselecteerd.
In het veld 'Ga naar:' kunnen al gebruikte bereiken staan, of cellen die een naam hebben gekregen. (Zie Gevorderdencursus Excel - Les 1 Naambereiken). Wanneer je op de knop 'Speciaal' klikt verschijnt het dialoogvenster Selecteren Speciaal:

Van de optierondjes kan er maar één geselecteerd worden, van de keuzevierkantjes meerdere. Om in een werkblad met veel formules de fouten eruit te halen, klik je op het rondje voor Formules, en vinkt dan alleen het vierkantje aan bij Fouten.
Om in een kolom alle lege cellen te selecteren kun je de kolom selecteren en dan het rondje bij 'Lege waarden' aanvinken.
Via de optie 'Laatste cel' spring je naar de cel van de laatste kolom en de onderste rij die nog gebruikt is. Dit werkt hetzelfde als de toensenbordcombinatie Ctrl+End. Ctrl+Home brengt je naar cel A1, of linksboven als titelblokkering aan staan.


EINDE LES Databases.