DATABASEBEHEER IN EXCEL 1. LIJSTEN Een lijst is een reeks van rijen met gelijksoortige gegevens waarvan de eerste rij de labels (veldnamen) bevat. Een voorbeeld:
Je kunt een lijst beschouwen als een eenvoudige database. De rijen van de lijst zijn de records en de kolommen vormen de velden.
1.1 Richtlijnen voor het maken van lijsten • • • • • •
Zorg ervoor dat een werkblad niet meer dan één lijst bevat tussen de lijst en de overige gegevens laat je minstens één lege rij en minstens één lege kolom links en rechts van de lijst plaats je het best geen gegevens (bij filteren kunnen die gegevens onzichtbaar worden) plaats de labels in de eerste rij (Excel herkent die als er een verschil is tussen de rijen in gegevenstype, hoofdlettergebruik, lettertype, uitlijning of patroon) vermijd spaties in het begin van een celinhoud (ze beïnvloeden het sorteren en het zoeken) gebruik in een kolom dezelfde opmaak.
1.2 Beheer van lijsten Excel bevat voorzieningen om op een eenvoudige manier lijsten te beheren: gegevensformulieren, draaitabellen, automatische en uitgebreide filters, ordening, subtotalen,... Een lijst wordt door Excel automatisch herkend.
1.3 Gegevensformulier Selecteer een cel uit de lijst en kies Formulier uit het menu Data
1
Je kunt een record wijzigen door in de tekstvakjes de wijzigingen te typen. Je kunt records toevoegen (Nieuw), verwijderen (Verwijderen), zoekcriteria invullen (Criteria) en vooruit of achteruit zoeken (Vorige zoeken - Volgende zoeken). Het bladeren kan ook gebeuren met de pijltjestoetsen en met de muis en de schuifbalk. Een gegevensformulier kan ten hoogste 32 velden weergeven.
2. SORTEREN Je kunt geselecteerde gegevens of lijsten uit een rekenblad oplopend of aflopend rangschikken per rij of per kolom. Kies Sorteren uit het menu Data. Excel maakt een selectie van de lijst. Je krijgt het dialoogvenster van hiernaast. Excel stelt voor te sorteren per rij. Het is mogelijk om meerdere sorteersleutels op te geven. Als de gegevens volgens de vorige sleutel gelijk zijn, wordt gesorteerd op de volgende sleutel. Om te sorteren per kolom kies je Van links naar rechts sorteren bij Opties.
Hier stel je ook in of hoofdletters invloed hebben op de volgorde en kun je een aangepaste sorteersleutel
opgeven.
2
Je kunt een geselecteerd gebied ook sorteren per rij via de knoppen standaard-werkbalk.
van de
3. OPZOEKER Je kunt gegevens uit een lijst opzoeken via het gegevensformulier of via opzoekfuncties.
3.1 Gegevensformulier - Criteria Open het gegevensformulier en klik op Criteria. Je kunt zoekcriteria ingeven. Via de knop Formulier ga je terug naar het dataformulier. Enkele voorbeelden van criteria: Het veld Product moet exact gelijk zijn aan scherm Het veld Naam begint met b (B) Het veld Straat bevat park Het veld Verkoop is kleiner dan of gelijk aan 1 000 Opmerking: er wordt geen onderscheid gemaakt tussen hoofd- en kleine letters.
3.2 Opzoekfuncties 3.2.1 Zoeken in de eerste rij (kolom) Plaats in cel B13 de volgende formule:
• • • •
De syntaxis van de functie is als volgt: VERT.ZOEKEN(zoekwaarde;tabelmatrix;kolomindex_getal;benaderen) De functie zoekt naar de aangeduide waarde in de eerste kolom (groene gedeelte) van een tabelmatrix (gekleurde gedeelte). VERT.ZOEKEN kan 4 argumenten bevatten. De eerste drie zijn verplicht, het vierde is optioneel. De formule geeft de inhoud van de cel uit de zoveelste kolom als vermeld bij het derde argument (=3 in het voorbeeld) van de formule. Is dat argument groter dan het aantal kolommen dan krijg je de foutmelding #VERW!.
3
• •
•
Als enkel de verplichte argumenten ingevoerd zijn moeten de waarden uit die kolom oplopend gerangschikt zijn. Als de zoekwaarde tussen twee waarden ligt (indien numerieke waarden), neemt Excel de eerste (= de laagste) waarde van beide waarden. Is de zoekwaarde kleiner dan de kleinste waarde, dan krijgt je de foutmelding #N/B! (niet beschikbaar). Het vierde argument is optioneel en heeft de waarde WAAR of ONWAAR (als het argument niet ingevuld wordt neemt Excel WAAR als waarde). Als de waarde ONWAAR is zoekt Excel naar de eerste waarde die exact gelijk is aan de zoekwaarde. In dat geval moeten de waarden in de eerste kolom niet gerangschikt zijn.
Opmerking: de functie HORIZ.ZOEKEN is analoog met VERT.ZOEKEN. Hier wordt in de eerste rij (oplopend gerangschikt) gezocht naar de zoekwaarde.
3.2.2 Zoeken in een willekeurige kolom
•
•
•
•
VERGELIJKEN is een zoekfunctie waarbij kan gezocht worden naar waarden die niet noodzakelijk in de eerste kolom van een lijst voorkomen. De VERGELIJKEN-formule zoekt hier in bereik C2:C10 naar Ridderstraat 13. Het resultaat van de formules vind je in de figuur. De VERGELIJKEN-formule is van type 0. Dan neemt Excel de eerste waarde die exact gelijk is aan de zoekwaarde en geeft aan in de hoeveelste rij (kolom) de waarde gevonden werd. Andere mogelijke types zijn 1 en -1. Bij type 1 moet het zoekgebied oplopend gerangschikt zijn bij -1 aflopend. Het zoeken verloopt zoals bij VERT.ZOEKEN; hier wordt echter de rang van de rij of kolom weergegeven. Als er geen type opgegeven is, neemt Excel type 1. Via INDEX kunnen dan celinhouden weergegeven worden. INDEX geeft (in het voorbeeld) de celinhoud (= Tel_nr van Ridderstaat 13) van de cel in rij 7, kolom 7.
4
4. LIJSTEN EN FILTERS Filteren is een snelle manier om een deel van een lijst te gebruiken. je gebruikt dan alleen die rijen die aan één of meerdere voorwaarden (criteria) voldoen.
4.1 Automatische filter 4.1.1 Gewone criteria Selecteer een cel in de lijst en kies uit het menu Data voor Filter. Kies vervolgens voor Autofilter. Er verschijnen uitschuifpijlen bovenaan in elke kolom.: •
• • • • •
Met een klik op zo'n pijl verschijnt een lijst met alle waarden die in de kolom voorkomen. Door een waarde uit de lijst te kiezen, verberg je alle rijen die de waarde niet bevat. Een gefilterde lijst herken je aan de blauwe uitschuifpijlen en aan de blauwe rijkoppen. Bij lange uitschuiflijsten ga je snel naar een gewenste waarde door de eerste letters te typen. Als je eerste kolommen selecteert en daarna Autofilter kiest, verschijnt de uitschuifpijl enkel in de geselecteerde kolommen. Je zoekt rijen met lege cellen of velden door Lege Cellen te kiezen uit de lijst, rijen zonder lege cellen door Niet-lege cellen te kiezen. Je verwijdert een filter door ofwel alle te kiezen uit de uitschuiflijst ofwel door alles weergeven te kiezen uit het filter-deelmenu.
4.1.2 Aangepaste criteria Kies Aangepast uit een uitschuiflijst (bijvoorbeeld Achternaam). Door het kader in te vullen krijg je alle rijen waarvan de achternaam gelijke is aan Blond of begint met een D. Zoals je ziet kun je gebruik maken van jokertekens (* en ?)
5
Je kunt in een AutoFilter-criteria voor meerdere kolommen instellen. Filter een lijst op een kolom; filter de resultaten op een andere kolom, enzovoort. Elke opvolgende toepassing van de functie Autofilter verfijnt de resultaten, waardoor je uiteindelijk alleen de rijen overhoudt die aan alle criteria voldoen.
4.2 Uitgebreide filter In tegenstelling tot de functie Autofilter, moet je met Uitgebreid filter een criteriumbereik gebruiken dat buiten de lijst ligt. De criteria worden het best bovenaan de lijst geplaatst of in een apart werkblad. Typ een of meerdere kolomkoppen in de bovenste rij en de filtercriteria in de tweede en daaropvolgende rijen. Met uitzondering van berekende gegevens moeten de koppen van het bereik exact overeenkomen met die van de lijst. Om fouten te voorkomen kun je deze koppen het best maken door te kopiëren en te plakken. Let op: in een criteriumbereik hoeft niet voor alle kolommen een kolomkopje te staan. Kolommen die niet gefilterd worden, hoeven niet in het criteriumbereik voor te komen. Indien twee voorwaarden op verschillende regels geplaatst zijn, weet Excel dat aan één van beide criteria voldaan moet worden. Als je beide voorwaarden op dezlfde regel zet, zoek je alleen naar die rijen waarop aan beide criteria wordt voldaan. Met andere woorden: voorwaarden die op dezelfde regel staan worden verbonden met EN, en voorwaarden die op verschillende regels staan worden verbonden met OF. U kunt in een criteriumbereik net zoveel afzonderlijke criteria opgeven als je nodig denkt te hebben. Denk erom dat een lege cel betekent dat naar ‘alle waarden in deze kolom’ gezocht gaat worden. Als je per ongeluk een lege rij in het criteriumbereik meesleurt, is het resultaat een ongefilterde lijst. Als je uitgebreid filter kiest uit het filtermenu moet je criteria opgeven in een criteriumbereik. Een criteriumbereik bestaat uit minimum 2 rijen en 1 kolom.
Enkele voorbeelden: Er wordt gezocht naar records waarbij het veld Achternaam gelijk is aan Blond. Er wordt gezocht naar records waarbij het veld Adres de tekst Kazernevest 9 bevat en het veld Plaats gelijk is aan Brugge. Er wordt gezocht naar records waarbij in het veld Adres de tekst park voorkomt of het veld plaats de tekst Brugge bevat. Er wordt gezocht naar records waarbij een veld verkoop groter is dan 15000 en kleiner is dan 30000.
6
Opmerkingen: • •
Met de optie Alleen unieke records kun je identieke rijen op één na verbergen. Je kunt de gefilterde gegevens naar een andere plaats kopiëren. Selecteer het keuzerondje Kopiëren naar andere locatie en wijs bij Kopiëren naar de locatie aan waar de gefilterde lijst moet komen. Als de locatie een cel is, wordt heel de gefilterde lijst overgebracht. Als die locatie bestaat uit een reeks veldnamen, worden alleen de gegevens van die velden overgebracht. Hierbij worden bestaande waarden in het doelbereik overschreven.
5. DATABASE FUNCTIES Dit zijn functies die je kunt gebruiken op lijsten. Databasefuncties gebruiken 3 argumenten: database, veld en criteria: • • •
database = het cellenbereik van de database; veld = het veld dat in de functie gebruikt wordt; criteria = de zoekcriteria.
Enkele functies: DBAANTALC
Telt het aantal niet lege cellen
DBAANTAL
Telt het aantal cellen met getallen
DBGEMIDDELDE
Geeft het gemiddelde van de waarden uit veld
DBMAX - DBMIN
Geeft de maximale (minimale) waarde van veld
DBSOM
Geeft de som van de waarden uit veld
DBPRODUKT
Geeft het produkt van de waarden uit veld
Enkele voorbeelden: DBAANTALC(database;;criteria)
geeft het aantal records die voldoen aan criteria
DBGEMIDDELDE(database;"inkoop";criteria)
geeft het gemiddelde van inkoop van de records die voldoen aan criteria
• • •
Criteria is de naam die door Excel automatisch toegekend wordt aan een criteriumbereik. Database is de naam van de lijst. Je kent die toe via het menu Invoegen en vervolgens Naam - Bepalen. De veldnaam wordt ingevoerd tussen aanhalingstekens. Je kunt ook de rangorde van het veld invoeren.
7
8
