Zoeken - Excellerend

Excellerend
Kwartaaltip 2012-2
Excellerend
Heemraadweg 21
2741 NC Waddinxveen
06 – 5115 97 46
[email protected]
BTW: NL0021459225
ABN/AMRO: 53.68.25.491
KVK: 24389967
Zoeken
Binnen Excel kan op verschillende manieren gezocht worden: Zelf via de functionaliteiten en
automatisch via functies. Ik zal in deze tip beide behandelen.
Functionaliteit Zoeken
Wanneer je in een Excelbestand iets moet zoeken, kan dat via Ctrl+F zodat het
dialoogvenster “Zoeken en vervangen” verschijnt. Standaard staan de opties uit, maar klik
erop en het volgende venster verschijnt:
Typ de zoektekst in de witte balk achter ‘Zoeken naar:’
Vervolgens kun je bepalen waar je wilt zoeken. Bij ‘Binnen:’ kun je kiezen tussen het actieve
werkblad of de gehele werkmap (behalve in verborgen werkbladen).
De Zoekrichting vanaf de actieve cel kan per rij zijn of per kolom. Dit is nuttig wanneer de
zoekwaarde meerdere keren voorkomt, maar anders maakt het niet veel uit.
Bij ‘Zoeken in:’ heb je drie opties:
1. Formules. Wanneer een formule verwijst naar een ander tabblad, bijv. Blad1, is te
zoeken naar “Blad1” om de formules te vinden waarin verwezen wordt naar dit
tabblad.
2. Waarden. De uitkomst van een formule is de Waarde. Dit kan een getal zijn, of tekst
of beide. Ook daarnaar kan gezocht worden.
3. Opmerkingen. Cellen kunnen een opmerking bevatten. Een cel met een opmerking
heeft een rood driehoekje in de rechter bovenhoek. Ook al zijn opmerkingen
Pagina 1 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
verborgen, er kan worden gezocht naar de tekst en getallen in de opmerkingen. Als
een opmerking de zoekwaarde bevat, wordt de betreffende cel geselecteerd.
De twee vierkantjes in het midden geven de mogelijkheid om bij het zoeken rekening te
houden met hoofd- en kleine letters. Op deze manier vind je met zoekwaarde “gou” wel
“goud”, maar niet “Gouda”.
Tevens kun je via ‘Identieke celinhoud’ aanvinken dat de cel, formule of opmerking alleen
maar de zoekwaarde bevat. Bij opmerkingen lukt dit alleen maar wanneer de inhoud achter
elkaar ingevoerd is zonder harde return.
De vierkantjes kunnen alleen of samen aangevinkt worden.
Tenslotte kan er ook gezocht worden naar de opmaak van de zoekwaarde of van een cel,
maar die mogelijkheden zijn te uitgebreid voor deze kwartaaltip.
De knop ‘Alles zoeken’ toont een lijst met alle cellen die voldoen aan de zoekwaarde en de
aangegeven opties, inclusief onderaan het totaal aantal cellen dat voldoet aan de
zoekwaarde. In dit geval 12. Je kunt ook zien dat twee cellen op het tabblad “maanden” een
naam hebben gekregen en dat twee cellen op het tabblad “maanden” een formule bevatten
met als resultaat “apr”.
Door te klikken op een celverwijzing wordt die cel actief, ook op andere werkbladen dan het
actieve werkblad.
Via de knop ‘Volgende zoeken’ kun je alle cellen af gaan die aan de zoekwaarde voldoen.
Pagina 2 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
Functionaliteit Ga naar
Een tweede zoekfunctionaliteit is het dialoogvenster Ga naar dat via Ctrl+G te activeren is:
Bovenin kunnen namen staan die aan cellen
gegeven worden. Deze waren ook al te zien bij
het dialoogvenster Zoeken. Selecteer een
naam en klik op OK om die cel te activeren.
Ook valt onderin een celadres in te vullen
zoals ABC123456 om direct naar die cel te
springen wanneer scrollen te lang duurt.
(Ditzelfde kan via het Naamvak boven cel A1.)
Onder de knop ‘Speciaal’ zijn verschillende
mogelijkheden te vinden. Bij het gebruiken
van deze mogelijkheden wordt alleen op het
huidige werkblad gezocht, en als er een
cellenbereik is geselecteerd wordt alleen
binnen de geselecteerde cellen gezocht.
Het keuzerondje ‘Opmerkingen’ selecteert alle cellen die een opmerking bevatten, ook als
de rij of kolom van een cel verborgen is.
Constanten zijn waardes (getallen, tekst , hyperlinks), dus geen formules.
Bij Formules wordent nog vier opties
gegeven:
1. Getallen. Selecteer alleen formules
die als uitkomst een getal hebben;
2. Tekst. Selecteer alleen formules die
als uitkomst tekst hebben;
3. Logische waarden zijn de uitkomst
van een functie die WAAR of
ONWAAR oplevert.
4. Fouten zijn formules die een
foutwaarde als uitkomst hebben.
Bijv. #N/B, #VERW!, #WAARDE!
‘Lege waarden’ zijn cellen binnen een
geselecteerd gebied die leeg zijn. Een cel
met een functie waarbij de uitkomst van die
functie niets is, is niet leeg!
‘Huidig gebied’ bevat altijd een rechthoek van aaneengesloten cellen waar de actieve cel
deel van uitmaakt, of tegenaan ligt:
Pagina 3 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
‘Huidige matrix’. Over matrixfuncies valt veel te zeggen, maar niet nu. Matrixfuncties zijn
herkenbaar doordat je ze afsluit met Ctrl+Shift+Enter zodat er accolades {} omheen komen.
‘Objecten’. Word-Art, grafieken, plaatjes en Vormen zijn allemaal objecten op de grafische
laag (Zie kwartaaltip 2012-1). Wanneer je ze koppelt aan de onderliggende cellen en je
verbergt rijen of kolommem zodanig dat een object niet meer zichtbaar is, dan nog kun je
het selecteren via Ctrl+G.
‘Verschillen in rij’. Wanneer je een rij(en), of deel van een rij(en), selecteert kun je de cellen
selecteren waarvan de waarde of formule afwijkt van de rest. Wanneer alles afwijkt, zal
Excel alle cellen selecteren anders dan de actieve cel. Dit wordt per rij bepaald.
‘Verschillen in kolom’. Idem als hierboven, maar dan voor de geselecteerde kolom(men).
Formules verwijzen vaak naar andere cellen. De Broncel is de cel waarnaar verwezen wordt
en de Doelcel is de cel waarin de functie of formule staat die uit één of meerdere cellen de
waarde ophaalt.
Als in B1 staat =A1, dan is A1 de Broncel en B1 de doelcel.
Redenerend vanaf de actieve cel of de geselecteerde cellen kun je via het optierondje bij
‘Broncellen’ alle cellen selecteren die tezamen de waarde bepalen van de selectie.
Via ‘Doelcellen’ kun je vanaf die cellen selecteren die naar de actieve cel verwijzen. Je hebt
via ‘Alleen direct’ de mogelijkheid om alleen de cellen te selecteren die rechtstreeks naar de
actieve cel verwijzen, of via ‘Alle niveaus’ alle afhankelijke cellen selecteren. Zo is eenvoudig
de oorzaak te vinden als een formule een foutmelding als uitkomst heeft.
‘Laatste cel’. De laatste cel waarvan Excel vindt dat die in het huidige tabblad gebruikt wordt,
kun je via deze optie benaderen. Persoonlijk vind ik Ctrl+End sneller. Je springt dan naar de
cel rechtsonder in de laatste kolom en op de laagste rij die in gebruik is.
‘Alleen zichtbare cellen’. Wanneer een selectie ook verborgen rijen of kolommen bevat maar
je wilt daar geen bewerking op uitvoeren, kies dan deze optie. Alle verborgen cellen worden
dan uit de selectie verwijderd. Een rij of kolom is ook verborgen wanneer de hoogte of
breedte handmatig op 0 is gezet.
‘Voorwaardelijke opmaak’. De opmaak van cellen kan afhankelijk worden gesteld van de
waarde in de cel of een andere cel. (Zie les 3 van de gevorderden cursus op
www.excellerend.nl). Alle cellen waarbij de opmaak via een voorwaarde worden bepaald,
kun je selecteren door dit rondje aan te vinken. Deze optie vindt ook de cellen buiten de
huidige selectie, maar blijft wel op het actieve werkblad.
‘Gegevensvalidatie’. De handmatige invoer van een cel kan beperkt worden via Validatie
(Zie les 2 van de gevorderden cursus op www.excellerend.nl). Alle cellen waarbij de
handmatige invoer via validatie beperkt wordt, kun je selecteren door dit rondje aan te
vinken. Deze optie vindt ook de cellen buiten de huidige selectie, maar blijft wel op het
actieve werkblad.
De optie ‘Alles’ vindt alle cellen met validatie erop. De optie ‘Zelfde’ vindt alleen die cellen
die dezelfde validatie hebben als de actieve cel. Dit impliceert wel dat de actieve cel
gevalideerd is want anders wordt er niets gevonden.
Tot zover de functionaliteit zoeken.
Pagina 4 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
Functies die cellen zoeken
Behalve dat de gebruiker zelf kan zoeken naar formules, waarden etc. zoals hiervoor
besproken, kun je met behulp van functies zeer veel zoeken! Het is ondoenlijk om alles te
behandelen rondom ‘Zoeken’, maar ik zal een aantal functies beschrijven.
Functie Vertikaal zoeken
Iedere functie in Excel bestaat uit argumenten1. Deze argumenten kunnen hard ingeklopt
worden, maar kunnen ook een verwijzing zijn naar een cel of cellenbereik. Sommige
argumenten zijn verplicht, anderen optioneel. Wanneer je via de optie Functie invoegen (fx
voor de formulebalk) de functie Vertikaal zoeken opvraagt toont Excel het volgende
dialoogvenster waarbij de individuele argumenten van de functie weergegeven worden:
De bovenste drie opties zijn vet gedrukt wat betekent dat ze verplicht zijn. Het laatste
argument is niet vet gedrukt en dus optioneel.
In het venster staat al kort beschreven wat de functie doet.
Zoekwaarde. De functie VERT.ZOEKEN begint met een zoekwaarde. Dit kan een getal zijn,
tekst (tussen dubbele quotes) of een verwijzing naar een cel waarin de zoekwaarde staat.
Deze zoekwaarde wordt gezocht in een vertikaal cellenbereik van minimaal één kolom
breed. Dit cellenbereik is het tweede argument:
Tabelmatrix. De tabelmatrix is de selectie van aaneensluitende cellen van minimaal één
kolom breed. Deze tabel kan een naam hebben gekregen (zie les 1 van de gevorderden
cursus op www.excellerend.nl) waarnaar verwezen wordt. De naam van een cel of
cellenbereik wordt zonder quotes ingevoerd.
Zodra de zoekwaarde gevonden wordt kan van de rij waarin de zoekwaarde gevonden is de
waarde uit een andere kolom worden opgehaald. Dit is het derde argument:
1
Behalve een paar uitzonderingen zoals o.a.: NU(), Vandaag(), PI()
Pagina 5 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
Kolomindex_getal. De zoekwaarde wordt gezocht in de meest linkse kolom van de
tabelmatrix. Dit is niet aan te passen. Wanneer de zoekwaarde gevonden is, dient met een
geheel getal te worden bepaald in welke kolom de waarde opgehaald moet worden. Dit
getal is minimaal 1 (de kolom waarin gezocht wordt) en kan niet hoger zijn dan het aantal
kolommen van de tabelmatrix. Is dat wel zo, dan resulteert de functie in de foutwaarde:
#VERW! (Onjuiste verwijzing).
Benaderen. Het vierde argument is optioneel. Het argument niet gebruiken is hetzelfde als
WAAR invullen of het getal 1. In dit geval wordt geadviseerd de tabel oplopend te sorteren
omdat er anders foutieve resultaten getoond kunnen worden. In onderstaand voorbeeld
wordt dit getoond. De zoekwaarde staat in de gele cel. Er wordt naar verwezen in het eerste
argument via “E2” (zonder dubbele quotes). De tabelmatrix is van cel B1 t/m C8. Het
kolomindex_getal is 2 want het antwoord staat in de tweede kolom van de matrix, kolom C.
Links is de tabel niet oplopend gesorteerd, rechts wel. Het antwoord links klopt niet, het
antwoord rechts is wel juist.
De functie kan ook geschreven worden als:
=VERT.ZOEKEN(“Breda”;$B$1:$C$8;2) waarbij de zoekwaarde als tekst in de functie staat. Dit
maakt de functie lastiger aan te passen, maar geeft hetzelfde resultaat.
Wanneer een tabel niet oplopend gesorteerd is, maar je wilt toch de juiste waarde
weergeven, dient het vierde argument op ONWAAR te worden gezet, of dient er een 0 te
worden ingevoerd. Hieronder nogmaals de linker tabel, maar nu is benaderen met een 0 op
ONWAAR gezet (rood) en wordt de juiste verkoper getoond:
Het volgende voorbeeld is bedoeld om aan te geven dat een juist antwoord ook gevonden
kan worden als de zoekwaarde niet precies in de tabelmatrix staat:
Pagina 6 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
Zodra een klant een jaaromzet haalt van de ondergrens in kolom A tot de bovengrens in
kolom B, wordt een kortingspercentage berekend. Deze tabel dient wel oplopend gesorteerd
te zijn.
Functie Horizontaal zoeken
Daar waar de functie VERT.ZOEKEN vertikaal zoekt en een waarde uit dezelfde rij weergeeft.
Zoekt HORIZ.ZOEKEN horizontaal en geeft de waarde weer uit dezelfde kolom. Let op, de
tekst in het dialoogvenster van deze functie is niet helemaal juist.
Functie Zoeken
De functie ZOEKEN kent twee varianten:
Variant 1: De vector
De ‘zoekwaarde’ is net als bij vertikaal zoeken de waarde (tekst, getal, verwijzing etc.) die je
in een bereik gaat zoeken.
Het tweede argument, de ‘zoekvector’, bestaat uit één rij of één kolom van aaneensluitende
cellen of uit een matrix. Deze waarden moeten in oplopende volgorde gesorteerd zijn.
Anders kan de uitkomst onjuist zijn.
De ‘resultaatvector’ bestaat eveneens uit één rij of één kolom van aaneensluitende cellen of
uit een matrix. Dit bereik hoeft niet oplopend gesorteed te zijn, maar moet wel uit precies
evenveel waarden bevatten als de zoekvector.
Als eerste een voorbeeld dat net zo werkt als vertikaal zoeken met een oplopende tabel:
Pagina 7 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
De zoekwaarde “Breda” wordt gezocht in zoekvector A2 t/m A8. De zoekwaarde wordt
gevonden in de derde waarde van deze zoekvector. Daarom wordt ook de derde waarde
weergegeven van de resultaatvector, zijnde de cellen B2 t/m B8.
Er zijn drie verschillen met vertikaal zoeken:
1) Je kunt het resultaat tonen uit een kolom die links is van de kolom waarin je de
zoekwaarde zoekt.
2) Het resultaat hoeft zich niet in dezelfde rij te bevinden als waar de gevonden
zoekwaarde staat.
Dit wordt geïllustreerd door onderstaand voorbeeld:
De twee vectoren hoeven ook niet op hetzelfde
werkblad te staan. Als ze maar even groot zijn.
3) De matrices waarin de zoekwaarde staat en het resultaat, kunnen als matrix
ingevoerd worden in de functie. Een matrix is herkenbaar aan de accolades die er
omheen staan. Bij een matrixfunctie zet Excel die er omheen omdat je de functie
afsluit met Ctrl+Shift+Enter, maar wanneer een matrix als argument wordt gebruikt,
moeten de accolades er handmatig omheen gezet worden:
Pagina 8 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
Ook hierboven staat de zoekwaarde in D2. De zoekvector is de matrix
{"Amsterdam";"Arnhem";"Breda";"Nijmegen";"Rotterdam";"Tilburg";"Utrecht"} welke in
oplopende volgorde is gesorteerd. De resultaatvector bestaat uit de verkopers:
{"André";"Andrea";"Bert";"Natalie";"Roberto";"Theo";"Ullrich"}.
Deze eerste variant van de functie ZOEKEN lijkt op de functie vertikaal zoeken.
Variant 2: de matrix
De tweede variant kent twee argumenten: Zoekwaarde en matrix.
De ‘zoekwaarde’ is, net als bij vertikaal zoeken en de eerste variant van Zoeken, de waarde
(tekst, getal, verwijzing etc.) die je in een bereik gaat zoeken.
De ‘matrix’ kan een verwijzing zijn naar een cellenbereik van minimaal één kolom, waarbij de
zoekwaarde in de eerste kolom wordt gezocht en dan de waarde wordt weergegeven uit de
laatste kolom. Zie:
De zoekwaarde (de letter B tussen dubbele quotes) wordt gezocht in kolom A en de weer te
geven waarde staat in de laatste kolom van de matrix. Op rij 6 is dat kolom C, en op rij 7 is
dat kolom B.
Wanneer de zoekwaarde niet gevonden wordt, zijn er twee mogelijkheden:
1) Er is geen kleinere waarde dan de zoekwaarde. Excel komt dan met de foutmelding
#N/B.
2) Er is een kleinere waarde dan de zoekwaarde. Excel komt dan met de uitkomst alsof
die kleinere waarde gezocht werd.
Overigens staat in de helpfile over de functie Zoeken:
“Het is over het algemeen beter om de functie HORIZ.ZOEKEN of VERT.ZOEKEN te gebruiken
in plaats van de matrixvariant van ZOEKEN. Deze variant van ZOEKEN is toegevoegd ten
behoeve van de compatibiliteit met andere spreadsheetprogramma's.”
Maar ik vind ‘m best handig!
Pagina 9 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
Functies die in de inhoud van een cel zoeken
Er zijn ook functies die je kunt gebruiken om in de waardes van een cel te zoeken, of om te
bepalen wat de positie is van een tekenreeks.
Functie VIND.ALLES
Deze functie vindt in een tekenreeks de beginpositie van een andere tekenreeks . Deze
functie is hoofdlettergevoelig en er mogen géén jokertekens gebruikt worden.2
De functie bestaat uit drie argumenten waarvan de derde optioneel is:
De zoektekst moet ook hier tussen dubbele quotes gezet worden als het om tekst gaat.
Wordt een getal gezocht, dan moet het niet tussen quotes worden gezet.
Het argument In_tekst kan handmatig ingetikt worden, maar doorgaans wordt er verwezen
naar een cel.
Met het argument Begin_getal kan aangegeven worden vanaf welke positie er gezocht moet
worden. Als dit argument weggelaten wordt, wordt er vanaf de eerste positie gezocht.
Een aantal voorbeelden kan verhelderend werken.
2
Jokertekens zijn een ? voor één teken en een * voor meerdere tekens.
Pagina 10 van 11
www.excellerend.nl
Excellerend
Kwartaaltip 2012-2
In cel A1 staat een zin waarin we de positie willen zoeken van de eerste b. In A2 staat de
functie VIND.ALLES die de positie bepaalt van de eerste kleine b (21), en in cel A3 staat de
functie VIND.ALLES die de positie van de eerste hoofdletter B bepaalt (27).
In cel A4 zoeken we de eerste a welke op positie 20 wordt gevonden. In cel A5 staat de
functie VIND.ALLES genest als derde argument verhoogd met het getal 1 om aan te geven
dat vanaf positie 21 de eerstvolgende a moet worden gevonden. Die staat op positie 24.
In cel A5 wordt de positie bepaald van de tekst “bla”. Die tekst begint op positie 22.
Als de zoektekst niet gevonden wordt, resulteert dit in #WAARDE! Ook als het derde
argument kleiner is dan 1 of groter is dan de totale lengte van de inhoud van de cel
resulteert dit in #WAARDE!
Deze functie wordt vaak gecombineerd met =DEEL(), maar daarover heb ik het nog wel eens.
Functie VIND.SPEC
De functie VIND.SPEC heeft dezelfde argumenten als VIND.ALLES, maar deze functie is niet
hoofdlettergevoelig en de zoektekst mag hier wèl jokers bevatten. Ook hier geldt dat het
derde argument optioneel is en dat bij het weglaten ervan gezocht wordt vanaf de eerste
positie.
Wil je juist het teken vinden dat Excel herkent als een joker, zet er dan een tilde (~) voor.
Ook nu weer een aantal voorbeelden:
Als de zoektekst niet gevonden wordt, resulteert dit in #WAARDE! Ook als het derde
argument kleiner is dan 1 of groter is dan de totale lengte van de inhoud van de cel
resulteert dit in #WAARDE!
Einde kwartaaltip 2-2012
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html
Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u
ondersteuning in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is
in Excel maar u weet niet hoe? Neem dan contact op met Richard Meijles: 06 – 5115 9746 of
via e-mail: [email protected].
Wilt u geen kwartaaltips meer ontvangen? Klik dan op: [email protected]
Hebt u een verzoek voor een kwartaaltip? Klik dan op: [email protected] en vermeld
tevens uw Excelversie.
Richard Meijles
Pagina 11 van 11
www.excellerend.nl