Excel Ongebruikelijk en Makklijk zoeken naar Namen in een Tabel

Stel ik voer een naam in en achter die naam krijg je direct adres gegevens.

Veel Excel gebruikers gebruiken hiervoor de functie verticaal zoeken. Dit is populair

Een andere handige toepassing van die functie is dat je die kunt gebruiken om snel gegevens in te voeren.

Bijvoorbeeld bij een order , je typt alleen een product nummer in en gelijk krijg je het juiste product of bij  een klantnummer direct de klantgegevens.

intermezzo

De werking van die functie

Bij verticaal zoeken voer je een zoekwaarde in , en vervolgens kijkt Excel in de tabel naar de meeste linkse kolom dat is kolom met indexnummer 1. De kolom rechts hiervan krijgt indexnummer 2 daarvan weer rechts indexnummer 3 enz.

Heeft hij de waarde gevonden dan kan Excel die waarde geven maar je kunt in de formule aangeven dat je niet de waarde in kolom met indexnummer 1 wilt zien maar in die rij kijkt naar de waarde in een kolom met een ander index nummer, bijvoorbeeld 6, dan zie je het telefoonnummer als die in die kolom staat die hoort bij je zoekwaarde.

Toch kunnen er problemen ontstaan , je kopieert de functie omlaag met het risico dat een formule per ongeluk gewist wordt dan kan je inforatie gaan missen of je hebt een hele grote tabel waardoor de functie nog wel eens fouten vertoond. En je moet rekening houden dat Excel de meest linkse kolom van de tabel gebruikt om zoekwaarden te vinden. Staat in kolom 2 de volledige namen dan kan je er niet rechtstreeks met de verticale zoekfunctie de registratienummers vinden als die in kolom 1 staan. Wel met een paar kunstgrepen.

Maar er zijn nog een paar andere manieren om het verticaal zoeken te vermijden.

Ik laat hier een zien. Ik schrijf hiervoor een macro met ondermeer het commando Find.

Bij een macro automatiseer ik taken , in dit geval taken die bij een naam een telefoon nummer laat zien.

Mijn voorbeeld tabel

blog1findmethode

Voor het maken van een macro hier zie het filmpje

 

Ik ga dus in die tabel zoeken naar een telefoonnummer die bij een naam past.

blog1findmethode2-jpg

Ik voer een naam in cel K3

Ik ga de naam laten zoeken in kolom C3:C28

En vervolgens wil ik dat het telefoonnummer bij die naam gevonden wordt

In kolom G3:G28

De gevonden naam wordt dan getoond in Cel L2

blog1findmethode3-jpg

Tussen Sub en End Sub moeten de volgende regels worden getypt

blog1findmethode4jpg

In onderstaand filmpje zie je het resultaat hiervan.

Met deze code kun je dus gaan zoeken in een tabel of een naam voorkomt, je beperkt je dan wel tot een tabel waarin de namen die je zoekt slechts een keer voorkomen.

Je kan natuurlijk opmerken stel dat ik 2 mensen heb die jansen heten of dat iemand jansen heet en een ander janssen heet en ik niet precies meer weet of jansen met 1 of 2 s-en wordt geschreven of een ander moeilijke naam eindigt die op cz of kz

Je wilt die resultaten kunnen zien daarover kan ik je veel meer vertellen. Maar niet in deze blog.

Wil je de code voor je eigen situatie toepassen en ben je onwennig of je wil een verklaring van de code. Voor een uitgewerkt voorbeeld en verklaring van de code zie onder

 

Stel je hebt een tabel .  De tabel wordt weergegeven in het cellenbereik  a2: h3000

In kolom B bevinden zich de namen en , In kolom A de registratienummer en in F de telefoonnummers

Het resultaat moet er zo uitzien.

Registratie Nummer Naam Telefoon
4324 Kerver 06-192923848

 

Je gaat op naam zoeken bij die naam moet je zoals boven links een registratie nummer vinden en rechts het telefoon nummer.

Op blad2 wil je het resultaat zien.

Van d2: f2 zie je de kolomkoppen en in d3:f3 vind je de resultaten.

Bij verticaal zoeken zou je eerst de rij in de a kolom gaan kopiëren achter de h kolom.

En zou je als zoektabel b2: i3000 gebruiken , immers dan is de meeste linker kolom de namen kolom en kun je de gegevens erbij zoeken.

Met Find hoeft dit dus niet.

Stel je typt de naam die je zoekt in cel a1 bijvoorbeeld Kerver.

De inhoud van cel a1 stop je in het computer geheugen door er een variabele van te maken. Die variabele moet een naam hebben.

Dit doe je door de code

Dim zoeknaam as string

Met string geef je aan dat je een tekst in het geheugen stopt. Zoeknaam is de naam van het geheugen die je overigens vrij kunt kiezen.

Die naam wil je vinden in de kolom b2:b3000 even aannemen dat elke naam maar een keer voorkomt meestal is dit al zo bij een combinatie van voornaam tussen voegsel en achternaam.

Nu wil je de hele kolom waarin je zoet in het geheugen stoppen.

Dit doe je door de regel dim zoekkolom as Range

Range is een cellenbereik

Als je de computer laat zoeken naar een naam in een kolom en de naam komt voor dan heeft die naam een celadres.

Daarom maak je van dit celadres een geheugen.

Dim celvind as Range ( Een cel is ook een celbereik )

Nu heb je dus je drie geheugens .

Zoekkolom

Zoeknaam

Celvind

 

Nu moet je aan de  computer die nu 3 geheugens heeft gereserveert een omschrijving geven van elk van die geheugens.

Zoeknaam =range(“a1”).Value  betekenis de inhoud van Cel A1 noem ik zoeknaam .

Bij een cellenbereik in het geheugen moet je de omschrijving vooraf laten gaan door het commando Set

Set zoekkolom =range(“b2: b3000”)   dwz alle cellen b2 t/m b3000 moeten worden onderzocht.

vergeet de dubbele aanhalingstekens niet.

Dan moet je de gevonden cel gaan omschrijven.

Set celvind=zoekkolom.Find(what:=zoeknaam, lookat:=xlWhole, matchcase:=False)

Je ziet in celvind zijn de andere geheugens zoeknaam en zoekkolom verwerkt  het is dus een commando.

Zoekkolom geeft nu dat je moet zoeken in de cellen b2: b3000

Wat je moet zoeken zie je achter what:=  Daarachter staat  zoeknaam.

Bij lookat:=xlWhole  betekent dat je een excacte zoekwaarde moet vinden. Je hebt ook  de uitdrukking lookat:=xlPart  dan zoek je op een lettercombinatie  bijvoorbeeld op jans dan kijkt hij in elke naam waarin jans voorkomt .

Je hebt dan meer codes nodig om meer zoekresultaten op je scherm te zien maar dat laten we nu buiten beschouwing we gaan er hier van uit dat elke naam maar een keer voorkomt vandaar xlWhole.

Machtcase:= False is comfortabel we kunnen hoofd en kleine letters door elkaar gebruiken.

 

Stel dat de naam Kerver die we zoeken in cel b15 staat dan wordt vindcel omschreven als $b$15

 

Dus de naam Kerver komt voor in de tabel en Excel heeft hiervan de coordinaat gegeven. We willen natuurlijk de naam zien met het registratie nummer.

 

 

Het resultaat laten we zien op blad2 .

In e2 willen we de gevonden naam zien , dat is makkelijk

Range(“e2”).Value =Celvind.Value

Dus op blad1 de waarde in cel b15 en dat is de zoekwaarde

Maar nu het resistratie nummer de naam is gevonden in b15 dus het registratienummer staat in a15

A15 is een cel links van B15 verwijderd en blijft op dezelfde rij.

In vba geeft je dit aan door Offset(0,-1) dwz 0 naar beneden en 1 naar links

Het telefoonnummer bevindt zich f15 , deze cel bevindt zich 4 plaatsen rechts van cel b15

Dit geeft je aan als Offset(0,4) betekent  0 cellen naar beneden tov b15 en 4 cellen naar rechts tov b15

 

We hebben echter nog rekening gehouden met de mogelijkheid dat je een naam zoekt die niet in de tabel staat dan moet wel duidelijk zijn aan de gebruiker.

Dus

If celvind is nothing then

Msgbox “deze naam komt niet voor “

Else

De code die ons het resultaat laat zien

End If

 

Tussen If en End If staat dus de mogelijkheid wat je moet doen bij het al of niet voorkomen van de naam in de tabel.

 

De code die ik gemaakt heb staat hieronder.

 

Dim zoeknaamkolom as range

Dim vindcel as range

Dim zoeknaam as string

Set zoeknaamkolom =range(“c3:c28”)

Zoeknaam=range(“k3”).Value

Set celvind=zoekkolom.Find(what:=zoeknaam, lookat:=xlWhole, matchcase:=False)

 

If vindcel is Nothing then

Msgbox “deze naam komt niet voor in de tabel”

Else

Range(“l3”).Value =vindcel.Offset(0,4).Value

End if

De aangepaste code in ons voorbeeld wordt dan.

Dim zoeknaamkolom as range

Dim vindcel as range

Dim zoeknaam as string  

Set zoekkolom =range(“b2: b3000”)

Zoeknaam =range(“a1”).Value

Set celvind=zoekkolom.Find(what:=zoeknaam, lookat:=xlWhole, matchcase:=False)

If vindcel is Nothing then

Msgbox “deze naam komt niet voor in de tabel”

Else

Sheets(“blad2”).Select   

Range(“d3”).Value =Celvind.Offset(0,-1).Value

Range(“e3”).Value =Celfind.Value

Range(“f3”).Value =Celfind.Offset(0,-4).Value

End iF

 

 

 

 

 

 

Contactgegevens

Heeft u vragen en/of interesse in een van onze diensten? Neem dan gerust contact met ons op

  • Magia Data
  • Gasthuisstraat 33
  • 3581 GE Utrecht

  • Telefoon : 030 - 254 21 93
  • Mobiel : 06 - 10 80 77 96
  • E-Mail : info@magiadata.nl

  • Twitter : @magiadata