- De leerlingen kunnen VERT.ZOEKEN gebruiken om gegevens uit een tabel op te zoeken op basis van een unieke waarde
- De leerlingen kunnen databasefuncties (DBSOM, DBGEMIDDELDE, DBAANTAL) met criteria toepassen
- De leerlingen kunnen X.ZOEKEN als moderne vervanger van VERT.ZOEKEN inzetten
- De leerlingen kunnen criteriabereiken aanmaken en gebruiken voor geavanceerde gegevensberekeningen
- De leerlingen kunnen werken met echte scenario's van voorraadbeheer en kostenanalyse
- De leerlingen kunnen foutafhandeling implementeren met ALS.FOUT
In deze les duiken we in de wereld van zoekfuncties en databasefuncties.
We gebruiken hiervoor als thema: het samenstellen van de ultieme gaming-PC.
Deel 1: Verticaal Zoeken (VERT.ZOEKEN)
De functie VERT.ZOEKEN is essentieel wanneer je gegevens uit een grote tabel wilt ophalen op basis van een unieke waarde.
Het vertelt Excel om in de eerste kolom van een bereik naar een waarde te zoeken.
Vervolgens geeft het de waarde terug uit een kolom die jij specificeert.
De opbouw van de functie ziet er als volgt uit:
VERT.ZOEKEN(zoekwaarde; tabelmatrix; kolomindex_getal; [benaderen])
- Zoekwaarde: Het onderdeel dat je wilt opzoeken (bijvoorbeeld de naam van een videokaart).
- Tabelmatrix: Het hele cellenbereik waarin de gegevens staan.
- Kolomindex: Het nummer van de kolom waaruit je de informatie wilt halen (1, 2, 3, etc.).
- Benaderen: Gebruik hier
ONWAARom een exacte overeenkomst te vinden.
Let op: VERT.ZOEKEN zoekt altijd in de eerste kolom van het bereik, en die kolom moet gesorteerd zijn. Als je in een andere kolom wilt zoeken, moet je de kolommen van volgorde veranderen, of je kunt beter de X.ZOEKEN functie gebruiken (zie Deel 3).
Oefening 1: Componentprijzen opvragen
Je hebt een lijst met hardware, maar je wilt in een apart factuurscherm snel de prijs kunnen zien door alleen de naam van het onderdeel in te typen.
Opdracht:
- Maak de bovenstaande tabel aan in Excel.
- Gebruik in een cel buiten de tabel de functie
VERT.ZOEKENom de Prijs van de "Samsung 990 Pro 2TB" op te halen. - Zorg dat de prijs automatisch update als je de naam van het onderdeel in de zoekcel verandert naar "NZXT H7 Flow".
Deel 2: Databasefuncties (DB-functies)
Databasefuncties zoals DBSOM, DBGEMIDDELDE en DBAANTAL zijn krachtiger dan gewone functies omdat ze werken met criteria.
In plaats van handmatig te filteren, laat je Excel rekenen met data die aan specifieke eisen voldoet.
De structuur is voor bijna elke databasefunctie hetzelfde:
DBSOM(database; veld; criteria)
- Database: Het volledige bereik van je tabel inclusief de koppen.
- Veld: De kolomnaam (tussen aanhalingstekens) of het kolomnummer waar de berekening op uitgevoerd moet worden.
- Criteria: Een apart bereik in je werkblad waar je de koppen en de voorwaarden typt waaraan de data moet voldoen.
Oefening 2: Voorraadbeheer en Kostenanalyse
Stel dat je alleen wilt weten wat de totale waarde is van de videokaarten (GPU) die momenteel op voorraad zijn.
Opdracht:
- Neem de tabel uit vorige oefening over in Excel.
- Maak boven of naast je tabel een cel aan met de kop Categorie en daaronder de waarde GPU.
- Gebruik de functie
DBSOMom de totale waarde (kolom "Totaalwaarde") te berekenen van alle producten die van het type "GPU" zijn.
Deel 3: De X.ZOEKEN functie (De moderne standaard)
Sinds enkele jaren heeft Excel de functie X.ZOEKEN. Dit is de opvolger van VERT.ZOEKEN.
Je hoeft bij X.ZOEKEN geen kolomindex (nummer) meer te tellen.
Je selecteert simpelweg de kolom waarin gezocht moet worden en de kolom waaruit de waarde moet komen.
De opbouw is:
X.ZOEKEN(zoekwaarde; zoeken-matrix; matrix-retourneren)
Een groot voordeel is dat X.ZOEKEN standaard zoekt naar een exacte overeenkomst.
Oefening 3: Snelkoppeling naar voorraad
Je wilt nu op basis van het onderdeel direct zien hoeveel stuks er nog in het magazijn liggen.
Opdracht:
- Neem de tabel uit oefening 1 over in Excel.
- Gebruik de functie
X.ZOEKENom de Voorraad te vinden van de "Corsair RM850x 850W". - Experimenteer: wat gebeurt er als je de kolom "Categorie" en "Onderdeel" van plek verwisselt? Werkt je formule nog?
Differentiatie: Uitdagingen voor de Expert
Voor wie de basis snel onder de knie heeft, zijn hier twee complexe scenario's.
Expertopdracht A: Meervoudige criteria
Gebruik de tabel uit Oefening 1. Gebruik de functie DBGEMIDDELDE om de gemiddelde prijs te berekenen van producten die én een Voorraad hebben groter dan 5, én van de Categorie "GPU" of "CPU" zijn.
Expertopdracht B: Foutafhandeling in Zoekfuncties
Wanneer je met VERT.ZOEKEN een product zoekt dat niet in de lijst staat, krijg je een #N/B foutmelding.
- Zoek uit hoe je de functie
ALS.FOUTkunt combineren metVERT.ZOEKEN. - Zorg ervoor dat Excel de tekst "Niet leverbaar" toont in plaats van een foutmelding wanneer een onderdeel niet wordt gevonden.
- VERT.ZOEKEN vindt waarden in een tabel door in de eerste kolom te zoeken en een waarde uit een gespecificeerde kolom terug te geven
- Databasefuncties (DBSOM, DBGEMIDDELDE, DBAANTAL) voeren berekeningen uit op data die aan specifieke criteria voldoet met behulp van criteriabereiken
- X.ZOEKEN is de moderne vervanger van VERT.ZOEKEN en zoekt flexibeler zonder kolomnummers te hoeven tellen
- Criteriabereiken definiëren voorwaarden waarmee geavanceerde gegevensberekeningen mogelijk worden
- Praktische toepassingen zoals voorraadbeheer en kostenanalyse kunnen met zoek- en databasefuncties worden geautomatiseerd
- ALS.FOUT biedt foutafhandeling om aangepaste meldingen te tonen in plaats van foutnummers
