Beginnen met spreadsheets - deel 4

In dit vierde deel van de introductiecursus Excel aandacht voor het opzoeken van gegevens in een tabel, zoals een artikellijst of klantenlijst. Aan de hand van een artikel- of klantnummer haalt u uit een (onzichtbare) lijst aanvullende gegevens op die u vervolgens naar uw werkblad kopieert, zoals adressen ophalen uit een klantenbestand en in een bestelformulier plaatsen. Maak voor deze toepassing kennis met de functies HORZ.ZOEKEN en VERT.ZOEKEN.

Gerelateerde training

Berekeningen uitvoeren op gegevens met Excel 2003

Voor deze training moet u lid zijn van Zeker Ondernemen +. U kunt zich hiervoor gratis aanmelden.

Download Excelcursus09 (xls, 32 KB)

Download Excelcursus10 (xls, 29 KB)

		Downloaden

		Verticaal zoeken

		Formule voorbereiden voor kopieeractie

Downloaden

Download hierboven de in dit artikel beschreven voorbeelddocumenten.

Start het downloaden.
Kies in het dialoogvenster Bestand downloaden de optie Opslaan.
Boven in het dialoogvenster Opslaan als klikt u in het witte vak achter Opslaan in. Daarmee verschijnt een lijst van mappen en drives.
U plaatst de gedownloade bestand(en) op uw vaste schijf in de map Mijn documenten.
Klik op Opslaan om het downloaden te starten.
Klik op Sluiten om het dialoogvenster Het downloaden is voltooid te sluiten.

top

Verticaal zoeken

Het navolgende voorbeeld gaat uit van twee werkbladen. Werkblad 1 bevat een orderformulier, werkblad 2 een lijst van namen en adressen van klanten. Elke rij in deze lijst begint met een klantnummer gevolgd door een naam en adres; onder elkaar staan circa 30 van deze adressen.

Voorbeeld van Excel werkblad met lijst van namen en adressen van klanten

De bedoeling is dat door het invullen van het klantnummer op werkblad 1, de naam- en adresgegevens van de desbetreffende klant worden opgezocht in de lijst op blad 2 en vervolgens op werkblad 1 worden geplaatst.

Open het werkblad ExcelCursus09.xls met Bestand >> Openen.
Op het werkblad Order staat het begin van een niet verder uitgewerkt orderformulier. In het vak Klantnummer wordt het nummer ingevuld van een vaste klant, die op het werkblad Klanten voorkomt.
Open het tabblad Klanten. De gegevens van alle vaste klanten verschijnen in beeld (zie afbeelding). Onthoud het nummer van een van de klanten.
Kijk nu hoe het werkt: schakel terug naar tabblad Order en vul in cel B5 nummer 124 in.
Druk op Enter en prompt verschijnt in cel B7 de naam van de klant met nummer 124: Advocatuur P. Klomp.

Voorbeeld van Excel werkblad Tab Order en Tab Klanten

Op basis van het nummer wordt de naam van de klant opgezocht en ingevuld. Om te verklaren hoe dit werkt gaat u de werking van deze functie zelf uitbreiden met het toevoegen van het adres.

Klik op het werkblad Order in de cel B8. Hier moet de straatnaam komen te staan.
Klik in de werkbalk Standaard (links van de formulebalk) op de knop fx Functie invoegen.
Selecteer in het dialoogvenster Functie invoegen de categorie Zoeken en verwijzen.
In de alfabetische lijst kiest u de functie VERT.ZOEKEN. U gaat immers zoeken op 'nummer' en de klantnummers staan in het werkblad Klanten allemaal verticaal onder elkaar.
Klik op OK. U moet nu de functieargumenten van VERT.ZOEKEN opgeven, dat zijn er drie, zie stap 6, 7 en 8.
Klik in het vak Zoekwaarde. Hier moet u opgeven welke waarde opgezocht moet worden in de meest linkse kolom van de tabelmatrix. In dit voorbeeld gaat het om het klantnummer en wordt deze zoekwaarde ingevuld op het werkblad Order in cel B5. Klik daarom in cel B5. Staat het venster Functieargumenten in de weg, sleep dit dan opzij.
Klik nu in het vak Tabelmatrix. Hier moet u opgeven in welke tabel gezocht moet worden. Klik eerst op de tab Klanten om tijdelijk over te schakelen naar de klantenlijst en selecteer de cellen A2:G35. De rij met de kolomtitels selecteert u dus niet. Merk op dat in het dialoogvenster Functieargumenten komt te staan:

Klanten!A2:G35.
Klik nu in het invulvak van het derde argument: Kolomindex_getal. U wilt de adresgegevens van de klant ophalen: deze staan in de vierde kolom van de tabel, dus u vult hier het getal 4 in.
Sluit het dialoogvenster Functieargumenten met OK. Prompt wordt in cel B8 ook het adres van de op het bestelformulier getoonde klant zichtbaar.

top

Formule voorbereiden voor kopieeractie

In de cel B8 staat de functie:

=VERT.ZOEKEN(B5;Klanten!A2:G35;4)

Deze functie zoekt in de linkerkolom (kolom A) van de tabel (Klanten!A2:G35) naar de waarde, die in cel B5 is opgegeven. Vervolgens wordt uit de vierde kolom (de kolom Straat) de bijbehorende waarde opgehaald. Voor het ophalen van de overige gegevens kunt u vrijwel dezelfde formule gebruiken. Deze gegevens zijn Huisnummer (C8), Postcode (B9) en Plaats (C9). Het is een goed idee om de formule uit cel B8 te kopiëren naar de andere cellen en in de kopie-formules het laatste argument (het kolomindex_getal) aan te passen. Echter, voordat u de formule uit B8 kunt kopiëren moet u hem geschikt maken voor de kopieer-actie, dat wil zeggen dat u een aantal (nu nog relatieve) celverwijzingen absoluut moet maken. Dat doet u door het plaatsen van $-tekens:

Klik in cel B8.
Klik in de formulebalk en pas de formule aan:

=VERT.ZOEKEN(B5;Klanten!A2:G35;4)

wordt:

=VERT.ZOEKEN($B$5;Klanten!$A$2:$G$35;4)
Druk op Enter. De formule is nu gereeed om gekopieerd te worden.
Kopieer de formule met sneltoets Ctrl+C.
Klik in cel C8 (huisnummer) en druk op sneltoets Ctrl+V (Plakken). Wijzig in de formulebalk het Kolomindex_getal in 5.
Druk op Enter, klik weer in cel C8 en kopieer de aangepaste formule met Ctrl+C.
Klik in cel B9 (postcode) en druk op sneltoets Ctrl+V. Wijzig in de formulebalk het Kolomindex_getal in 6.
Druk op Enter, klik weer in cel B9 en kopieer de aangepaste formule met Ctrl+C.
Klik in cel C9 (plaats) en druk op sneltoets Ctrl+V. Wijzig in de formulebalk het Kolomindex_getal in 7 en druk op Enter.