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
