Hoofdstuk 16: Zoek- en verwijzingsfuncties

Hoofdstuk 16: Zoek- en verwijzingsfuncties 16.0 Inleiding Eén van de belangrijkste functies binnen Excel is de mogelijkheid om te zoeken naar een specifieke waarde binnen een groot aantal cellen met gegevens. Het resultaat is van deze functie is dan een waarde uit die rij (of kolom). De gemakkelijkste manier om dit te doen, is met de VERT.ZOEKEN en HORIZ.ZOEKEN functies. Als je vaak analyses uitvoert in grote gegevensbestanden, dan is het waarschijnlijk ook de moeite waard om de meer gecompliceerde functiecombinatie INDEX-VERGELIJKING te bekijken (geeft nog meer mogelijkheden).

16.1 VERT.ZOEKEN De VERT.ZOEKEN functie zoekt naar een specifieke waarde in de eerste kolom aan de linkerkant van een tabel met gegevens en heeft als resultaat een waarde uit die rij in één van de kolommen aan de rechterkant. Bekijk de volgende gegevens over het jaar van oprichting van een aantal populaire websites:

Stel dat deze lijst duizenden regels heeft en jij alleen geïnteresseerd bent in vier sociale netwerksites. Je kunt dan apart een lijstje creëren met alleen de sites waar je in

© 2010 Excel with Business

1

Hoofdstuk16: Zoek- en verwijzingsfuncties

geïnteresseerd bent (kolom D) en daarna VERT.ZOEKEN gebruiken om de bijbehorende jaren van oprichting op te zoeken in de grote tabel. Typ in cel E2: =VERT.ZOEKEN(D2;A:B;2;ONWAAR) om vervolgens dit resultaat te krijgen:

waarbij: 

 



D2 de waarde is die je wilt opzoeken. Als je in plaats van deze omschrijving “Facebook.com” ingevoerd zou hebben, had je overigens hetzelfde resultaat gekregen. Alleen dan kun je de formule niet kopiëren naar andere rijen zonder dit te moeten veranderen. A:B is het celbereik waarin je aan het zoeken bent. Merk op dat de afwezigheid van rijnummers in deze verwijzing betekent dat in de volledige kolom wordt gezocht. 2 is het nummer van de kolom waar de waarde die je wilt vinden, staat. In dit geval is de kolom Afgeraden wordt ‘benaderen’ te gebruiken tenzij je veel waarin je geïnteresseerd bent (Jaar vertrouwen hebt in wat je aan het doen bent. Dit is van Oprichting) de tweede kolom, deels zo omdat gegevens normaal gezien geïmporteerd dus voer je hier een “2” in. of gemanipuleerd kunnen worden in een exacte vorm en ONWAAR duidt aan dat je aan het deels omdat het Excel laten raden naar een zoeken bent naar een exacte ‘overeenkomst bij benadering’ kan leiden tot overeenkomst. Zou je hier niets ongewenste en onzekere resultaten. invoeren of zou je WAAR invoeren, dan zoekt Excel naar de waarde die het meest overeenkomt.

Beperkingen 

De gegevens die je aan het opzoeken bent, moeten zich in de meest linkse kolom bevinden van de reeks die je selecteert.


2


16.2 HORIZ.ZOEKEN Je kan ook horizontaal gerangschikte gegevens ZOEKEN. Stel dat je dezelfde oefening wilt uitvoeren voor dezelfde gegevens, maar dat de gegevens nu horizontaal gerangschikt zijn:

Wanneer je dan de websites waarin je geïnteresseerd bent, intypt in de cellen B4:E4 en daarna de volgende formule in cel B5: =HORIZ.ZOEKEN(B4;B1:AN2;2;ONWAAR) dan krijg je het volgende resultaat:

Omdat de meeste gegevensbestanden ingedeeld zijn in rijen in plaats van kolommen is gebruikte van de twee functies. De beperkingen van VERT.ZOEKEN zijn ook van toepassing op HORIZ.ZOEKEN.

16.3 VERGELIJKEN

Het zou beter zijn (zeker visueel gezien) in dit voorbeeld om de tekst opnieuw verticaal in te delen en VERT.ZOEKEN te gebruiken. Als je HORIZ.ZOEKEN moet gebruiken, is het de moeite waard jezelf af te vragen of de gegevens wel op de beste manier zijn ingedeeld. Je kunt horizontaal ingedeelde gegevens zoals deze omzetten naar een verticale indeling met kopiëren en de ‘transponeren’ optie in plakken:

De VERGELIJKEN functie is Start > Plakken of Plakken speciaal > Transponeren eigenlijk een zoekfunctie die geen gegevens als resultaat geeft. Het geeft aan of het de waarde waar je naar vroeg gevonden heeft en -als dat het geval is- waar die waarde gevonden werd. Er zijn drie aspecten aan de VERGELIJKEN functie. Wat je aan het zoeken bent, waar je aan het zoeken bent en de soort vergelijking die je probeert te maken. In het voorbeeld van de populaire websites, stel dat je nog van een paar


3


andere sociale netwerksites (Bebo en Friendster) wilt zien of deze voorkomen in het uitgebreide gegevensbestand. Typ in cel E2: =VERGELIJKEN(D2;A:A;0) en dat ziet er ongeveer zo uit:

waarbij:   

D2 is waar je naar op zoek bent (in rij 2 is dit Facebook.com) A:A de kolom is waar je in aan het zoeken bent 0 betekent dat je aan het zoeken bent naar een exacte overeenkomst Net als bij VERT.ZOEKEN en HORIZ.ZOEKEN is het ook hier een goed idee om de exacte overeenkomst te gebruiken wanneer je een exacte overeenkomst verwacht. Als je de optie ‘overeenkomst bij benadering’ gebruikt, dan moeten de gegevens gesorteerd worden.

Het resultaat ziet er dan als volgt uit:


4


De “4” in cel E2 geeft de positie aan waar Facebook.com verschijnt in kolom A (het is het 4de item in de reeks die we selecteren). Let op: in dit geval wordt de kolomtitel meegerekend. Als je dat niet wilt, dan moet je =A2:A1048576 invoeren in plaats van A:A. Hetzelfde geldt voor 6,13 en 26. Het resultaat “#N/B” in de cellen E6 en E7 geeft aan dat “Bebo.com” en “Friendster.com” niet in de uitgebreide lijst staan.

16.4 INDEX-VERGELIJKEN De combinatie INDEX-VERGELIJKEN is niet gemakkelijk, maar het is de moeite waard om te leren, vooral wanneer je regelmatig analyses uitvoert op bestanden met veel gegevens. INDEX-MATCH heeft voordelen ten opzichte van VERT.ZOEKEN:  

Het kan gebruikt worden met gegevens die ingedeeld zijn in kolommen of rijen. Je kunt een kolom vinden door op de kolomtitel te zoeken in plaats van het nummer van de kolom.

INDEX De INDEX functie is relatief eenvoudig en kan ook apart gebruikt worden. Het heeft twee aspecten: de lijst waarnaar je wilt dat Excel kijkt en het nummer in die lijst dat je wilt zoeken. Dus, als je de 14de website in de lijst hieronder wilt hebben, dan moet je dit als volgt invoeren: =INDEX(A2:A40;14)

en dan krijgt je “ESPN.go.com”.


5


Stel dat je een ander gegevensbestand hebt met website-informatie over gebruikersbeoordelingen in verschillende geografische gebieden, op alfabetische volgorde gerangschikt:

Je wilt de twee gegevensbestanden samenvoegen tot één hoofdgegevensbestand die al deze informatie bevat. Eerst moet je beslissen of je de beoordelingsinformatie toe wil voegen aan het werkblad met de gegevens over het jaar van oprichting of dat je het andersom wil doen. Dit kan afhangen van de volgorde en de opmaak waaraan je de voorkeur geeft. Stel dat je de beoordelingsgegevens toevoegt aan de oprichtingsgegevens. Typ in dat gegevensbestand het volgende in: =INDEX(Beoordelingen!B:B;VERGELIJKEN(Oprichting!A2,Beoordelingen!A:A;0)) waarbij:  



Het gele gedeelte van de formule aangeeft waar je wilt dat Excel zoekt (kolom B van het werkblad Beoordelingen) Het groene gedeelte als resultaat de positie in kolom A geeft op het werkblad Beoordelingen (waar de sites op alfabetische volgorde staan) van Google.com (onze zoekwaarde in dit geval) verschijnt. Stel dat dit de 100ste plaats is. De INDEX weet daardoor dat de 100ste beoordeling in kolom B van het werkblad Beoordelingen moet worden weergegeven.


6


Om nu ALLE gegevens van het werkblad Beoordelingen te krijgen, moet je de formule naar beneden en naar rechts kopiëren. Omdat je altijd gebruik zult willen maken van het vergelijken van de A kolom van het werkblad Oprichting (maar niet altijd van dezelfde rij) met de A kolom van het werkblad Beoordelingen, kun je enkele celverwijzingen vastzetten (zie hoofdstuk 11: Celverwijzingen) naar: =INDEX(Beoordelingen!B:B;VERGELIJKEN(Oprichting!$A2;Beoordelingen!$A:$A;0)) Als je dan de formule naar beneden en naar rechts kopieert, dan geeft dit als resultaat:

Let op Zoals bij andere Excel formules en functies kan het gemakkelijk gebeuren dat er een fout gemaakt wordt bij het invoeren en hier is het moeilijker de fout te zien dan bij andere functies. Daarom moet je zorgdragen dat: 

Je steekproeven uitvoert In het bovenstaande voorbeeld kun je een willekeurige cel uitkiezen, bijvoorbeeld F15, om na te gaan of Go.com de beoordeling van 9.8 kreeg op het werkblad Beoordelingen.


7


 

Je gegevens sorteert Zorg ervoor dat je INDEX-VERGELIJK functies op zo’n manier creëert dat ze nog steeds het correcte resultaat geven wanneer de sorteervolgorde van de gegevens verandert. Je precieze gegevens behoudt Het gebruik van exacte overeenkomsten wordt aangeraden, maar dat brengt wel met zich mee dat de gegevens waarnaar je zoekt exact moeten overeenkomen met de waarde die je aan het zoeken bent. Soms moet je een eenvoudige bewerking uitvoeren voordat dit mogelijk is. Als bijvoorbeeld alle websites op het beoordelingsblad met “www” zouden beginnen, dan moet je dit ook toevoegen aan de gegevens op het werkblad Oprichting (met &) of je moet “www” verwijderen van het werkblad Beoordelingen (door Zoeken & Vervangen te gebruiken of LINKS en LENGTE).


8


Hoofdstuk 16: Zoek- en verwijzingsfuncties

Recommend Documents