Inhoud
- VERT.ZOEKEN-functie in Excel - algemene beschrijving en syntaxis
- VERT.ZOEKEN Voorbeelden
- Exacte of geschatte overeenkomst in de functie VERT.ZOEKEN
- VERT.ZOEKEN in Excel - u moet dit onthouden!
Vandaag beginnen we met een reeks artikelen waarin een van de handigste functies van Excel wordt beschreven − VPR (VERT.ZOEKEN). Deze functie is tegelijkertijd een van de meest complexe en minst begrepen.
In deze tutorial over VPR Ik zal proberen de basis zo eenvoudig mogelijk uit te leggen om het leerproces zo duidelijk mogelijk te maken voor onervaren gebruikers. Daarnaast zullen we verschillende voorbeelden bestuderen met Excel-formules die de meest voorkomende gebruiksscenario's voor de functie zullen demonstreren VPR.
VERT.ZOEKEN-functie in Excel - algemene beschrijving en syntaxis
Dus wat is het VPR? Ten eerste is het een Excel-functie. Wat ze doet? Het zoekt de waarde op die u opgeeft en retourneert de bijbehorende waarde uit de andere kolom. Technisch sprekend, VPR zoekt de waarde op in de eerste kolom van het opgegeven bereik en retourneert het resultaat van een andere kolom in dezelfde rij.
In de meest voorkomende toepassing is de functie VPR zoekt in de database naar een bepaalde unieke identifier en extraheert enige informatie die daarmee verband houdt uit de database.
Eerste letter in functienaam VPR (VERT.ZOEKEN) betekent Вverticaal (Vverticaal). Hiermee kun je onderscheid maken VPR oppompen van GPR (HLOOKUP), die zoekt naar een waarde in de bovenste rij van een bereik − Гhorizontaal (Hhorizontaal).
Functie VPR beschikbaar in Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP en Excel 2000.
Syntaxis van de functie VERT.ZOEKEN
Functie VPR (VERT.ZOEKEN) heeft de volgende syntaxis:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Zoals je kunt zien, een functie VPR in Microsoft Excel heeft 4 opties (of argumenten). De eerste drie zijn verplicht, de laatste optioneel.
- opzoekwaarde (lookup_value) - De waarde waarnaar moet worden gezocht. Dit kan een waarde zijn (getal, datum, tekst) of een celverwijzing (met de opzoekwaarde), of een waarde die wordt geretourneerd door een andere Excel-functie. Deze formule zoekt bijvoorbeeld naar de waarde 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Als de opzoekwaarde kleiner is dan de kleinste waarde in de eerste kolom van het bereik dat wordt opgezocht, is de functie VPR zal een fout melden #AT (#N.v.t.).
- table_array (tabel) - twee of meer kolommen met gegevens. Onthoud, de functie: VPR zoekt altijd naar de waarde in de eerste kolom van het bereik in het argument table_array (tafel). Het zichtbare bereik kan verschillende gegevens bevatten, zoals tekst, datums, getallen, booleans. De functie is niet hoofdlettergevoelig, wat betekent dat hoofdletters en kleine letters als hetzelfde worden beschouwd. Dus onze formule zoekt naar de waarde 40 in cellen van A2 naar A15, omdat A de eerste kolom is van het bereik A2:B15 dat in het argument wordt gegeven table_array (tafel):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- kolomindex_getal (column_number) is het nummer van de kolom in het opgegeven bereik van waaruit de waarde in de gevonden rij wordt geretourneerd. De meest linkse kolom in het gegeven bereik is 1, de tweede kolom is 2, de derde kolom is 3 enzovoort. Nu kunt u de hele formule lezen:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Formule op zoek naar waarde 40 binnen bereik A2: A15 en retourneert de corresponderende waarde uit kolom B (omdat B de tweede kolom is in het bereik A2:B15).
Als de waarde van het argument kolomindex_getal (kolom_nummer) kleiner dan 1harte VPR zal een fout melden #WAARDE! (#WAARDE!). En als het meer is dan het aantal kolommen in het bereik table_array (tabel), retourneert de functie een fout #REF! (#KOPPELING!).
- bereik_opzoeken (range_lookup) - bepaalt waarnaar moet worden gezocht:
- exacte overeenkomst, argument moet gelijk zijn Juist (ONWAAR);
- geschatte overeenkomst, argument is gelijk aan WARE CODE (TRUE) of helemaal niet gespecificeerd.
Deze parameter is optioneel, maar erg belangrijk. Verderop in deze tutorial op VPR Ik zal u enkele voorbeelden laten zien waarin wordt uitgelegd hoe u formules schrijft voor het vinden van exacte en benaderende overeenkomsten.
VERT.ZOEKEN Voorbeelden
Ik hoop dat de functie VPR word je wat duidelijker. Laten we nu eens kijken naar enkele gebruiksscenario's VPR in formules met reële gegevens.
VLOOKUP gebruiken om in een ander Excel-blad te zoeken
In de praktijk formules met een functie VPR worden zelden gebruikt om gegevens op hetzelfde werkblad te zoeken. Vaker wel dan niet, zult u overeenkomstige waarden opzoeken en ophalen van een ander blad.
Om te kunnen gebruiken VPR, zoek in een ander Microsoft Excel-blad, U moet in het argument table_array (tabel) specificeer de bladnaam met een uitroepteken gevolgd door een celbereik. De volgende formule laat bijvoorbeeld zien dat het bereik A2: B15 staat op een blad met de naam Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
De bladnaam hoeft natuurlijk niet handmatig te worden ingevoerd. Begin gewoon met het typen van de formule, en als het gaat om het argument table_array (tabel), ga naar het gewenste blad en selecteer het gewenste cellenbereik met de muis.
De formule in de onderstaande schermafbeelding zoekt naar de tekst "Product 1" in kolom A (het is de 1e kolom van het bereik A2:B9) op een werkblad Prijzen.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Houd er rekening mee dat u bij het zoeken naar een tekstwaarde deze tussen aanhalingstekens ("") moet plaatsen, zoals gewoonlijk wordt gedaan in Excel-formules.
voor argument table_array (tabel) is het wenselijk om altijd absolute verwijzingen te gebruiken (met het $ teken). In dit geval blijft het zoekbereik ongewijzigd bij het kopiëren van de formule naar andere cellen.
Zoek in een andere werkmap met VERT.ZOEKEN
Functioneren VPR werkte tussen twee Excel-werkmappen, moet u de naam van de werkmap tussen vierkante haken vóór de bladnaam opgeven.
Hieronder staat bijvoorbeeld een formule die zoekt naar de waarde 40 op het blad Sheet2 In het boek Nummers.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Dit is de gemakkelijkste manier om een formule in Excel te maken met VPRdie linkt naar een andere werkmap:
- Open beide boeken. Dit is niet verplicht, maar het is wel makkelijker om op deze manier een formule te maken. U wilt de naam van de werkmap niet handmatig invoeren, toch? Bovendien beschermt het u tegen onbedoelde typefouten.
- Begin met het typen van een functie VPRen als het gaat om het argument table_array (tabel), schakel over naar een andere werkmap en selecteer daarin het gewenste zoekbereik.
De onderstaande schermafbeelding toont de formule met de zoekopdracht ingesteld op een bereik in de werkmap Prijslijst.xlsx op het blad Prijzen.
Functie VPR werkt zelfs wanneer u de gezochte werkmap sluit en het volledige pad naar het werkmapbestand in de formulebalk verschijnt, zoals hieronder weergegeven:
Als de naam van de werkmap of het werkblad spaties bevat, moet deze tussen apostrofs staan:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Een benoemd bereik of tabel gebruiken in formules met VERT.ZOEKEN
Als u van plan bent hetzelfde zoekbereik in meerdere functies te gebruiken VPR, kunt u een benoemd bereik maken en de naam als argument in de formule invoeren table_array (tafel).
Om een benoemd bereik te maken, selecteert u eenvoudig de cellen en voert u een geschikte naam in het veld in Voornaam, links van de formulebalk.
Nu kun je de volgende formule opschrijven om de prijs van een product te vinden: Product 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
De meeste bereiknamen werken voor de hele Excel-werkmap, dus het is niet nodig om de bladnaam voor het argument op te geven table_array (tabel), zelfs als de formule en het zoekbereik op verschillende werkbladen staan. Als ze zich in verschillende werkmappen bevinden, moet u vóór de naam van het bereik de naam van de werkmap opgeven, bijvoorbeeld als volgt:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Dus de formule ziet er veel duidelijker uit, mee eens? Het gebruik van benoemde bereiken is ook een goed alternatief voor absolute verwijzingen, omdat het benoemde bereik niet verandert wanneer u de formule naar andere cellen kopieert. Zo weet u zeker dat het zoekbereik in de formule altijd correct blijft.
Als u een celbereik converteert naar een volwaardig Excel-spreadsheet met behulp van de opdracht tafel (Tabel) tabblad Invoeging (Invoegen), en wanneer u een bereik selecteert met de muis, voegt Microsoft Excel automatisch de kolomnamen (of de tabelnaam als u de hele tabel selecteert) toe aan de formule.
De voltooide formule ziet er ongeveer zo uit:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Of misschien zelfs zo:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Wanneer u benoemde bereiken gebruikt, verwijzen de koppelingen naar dezelfde cellen, ongeacht waar u de functie kopieert VPR binnen het werkboek.
Jokertekens gebruiken in VERT.ZOEKEN-formules
Net als bij veel andere functies, VPR U kunt de volgende jokertekens gebruiken:
- Vraagteken (?) – vervangt elk afzonderlijk teken.
- Asterisk (*) – vervangt elke reeks tekens.
Jokertekens gebruiken in Functies VPR kan in veel gevallen nuttig zijn, bijvoorbeeld:
- Wanneer u niet meer precies weet welke tekst u moet zoeken.
- Wanneer u een woord wilt vinden dat deel uitmaakt van de inhoud van een cel. Weet dat VPR zoekt op de inhoud van de cel als geheel, alsof de optie is ingeschakeld Match volledige celinhoud (hele cel) in de standaard Excel-zoekopdracht.
- Wanneer een cel extra spaties aan het begin of einde van de inhoud bevat. In zo'n situatie kun je je hersens lang pijnigen om erachter te komen waarom de formule niet werkt.
Voorbeeld 1: Zoeken naar tekst die begint of eindigt met bepaalde tekens
Stel dat u naar een specifieke klant wilt zoeken in de onderstaande database. Je herinnert je zijn achternaam niet, maar je weet dat het begint met 'ack'. Hier is een formule die het werk prima zal doen:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Nu u zeker weet dat u de juiste naam heeft gevonden, kunt u dezelfde formule gebruiken om het door deze klant betaalde bedrag te vinden. Om dit te doen, verandert u gewoon het derde argument van de functie VPR naar het gewenste kolomnummer. In ons geval is dit kolom C (3e in het bereik):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Hier zijn nog enkele voorbeelden met jokertekens:
~ Zoek een naam die eindigt op "man":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Zoek een naam die begint met 'advertentie' en eindigt met 'zoon':
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ We vinden de voornaam in de lijst, bestaande uit 5 karakters:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Functioneren VPR met jokertekens werkte correct, als het vierde argument dat je altijd moet gebruiken Juist (VALS). Als het zoekbereik meer dan één waarde bevat die overeenkomt met de zoektermen met jokertekens, wordt de eerste gevonden waarde geretourneerd.
Voorbeeld 2: Combineer jokertekens en celverwijzingen in VERT.ZOEKEN-formules
Laten we nu eens kijken naar een iets complexer voorbeeld van hoe te zoeken met behulp van de functie VPR op waarde in een cel. Stel je voor dat kolom A een lijst met licentiesleutels is en kolom B een lijst met namen die eigenaar zijn van een licentie. Daarnaast heb je een deel (meerdere karakters) van een soort licentiesleutel in cel C1 en wil je de naam van de eigenaar vinden.
Dit kan met de volgende formule:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Deze formule zoekt de waarde op uit cel C1 in het opgegeven bereik en retourneert de overeenkomstige waarde uit kolom B. Merk op dat we in het eerste argument een ampersand (&) -teken gebruiken voor en na de celverwijzing om de tekstreeks te koppelen.
Zoals u in de onderstaande afbeelding kunt zien, is de functie VPR geeft "Jeremy Hill" terug omdat zijn licentiesleutel de reeks tekens uit cel C1 bevat.
Merk op dat het argument table_array (tabel) in de bovenstaande schermafbeelding bevat de naam van de tabel (Tabel7) in plaats van een celbereik op te geven. Dit hebben we in het vorige voorbeeld gedaan.
Exacte of geschatte overeenkomst in de functie VERT.ZOEKEN
En tot slot, laten we eens nader kijken naar het laatste argument dat is opgegeven voor de functie VPR - bereik_opzoeken (interval_weergave). Zoals aan het begin van de les vermeld, is dit argument erg belangrijk. U kunt compleet verschillende resultaten krijgen in dezelfde formule met zijn waarde WARE CODE (Waar of Juist (VALS).
Laten we eerst eens kijken wat Microsoft Excel bedoelt met exacte en geschatte overeenkomsten.
- Als het argument bereik_opzoeken (range_lookup) is gelijk aan Juist (FALSE), zoekt de formule naar een exacte overeenkomst, dwz exact dezelfde waarde als gegeven in het argument opzoekwaarde (opzoekwaarde). Als in de eerste kolom van het bereik tkunnen_array (tabel) twee of meer waarden tegenkomt die overeenkomen met het argument opzoekwaarde (search_value), dan wordt de eerste geselecteerd. Als er geen overeenkomsten worden gevonden, rapporteert de functie een fout #AT (#N.v.t.). De volgende formule geeft bijvoorbeeld een fout weer: #AT (#N/A) als er geen waarde is in het bereik A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Als het argument bereik_opzoeken (range_lookup) is gelijk aan WARE CODE (TRUE), zoekt de formule naar een geschatte overeenkomst. Meer precies, eerst de functie VPR zoekt naar een exacte overeenkomst, en als er geen wordt gevonden, selecteert u een geschatte overeenkomst. Een benaderende overeenkomst is de grootste waarde die de in het argument opgegeven waarde niet overschrijdt. opzoekwaarde (opzoekwaarde).
Als het argument bereik_opzoeken (range_lookup) is gelijk aan WARE CODE (TRUE) of niet gespecificeerd, dan moeten de waarden in de eerste kolom van het bereik in oplopende volgorde worden gesorteerd, dat wil zeggen van klein naar groot. Anders, de functie: VPR kan een foutief resultaat opleveren.
Om het belang van keuze beter te begrijpen WARE CODE (WAARHEID) of Juist (FALSE), laten we eens kijken naar wat meer formules met de functie VPR en kijk naar de resultaten.
Voorbeeld 1: Een exacte match vinden met VERT.ZOEKEN
Zoals je je herinnert, om te zoeken naar een exacte overeenkomst, is het vierde argument van de functie VPR zou ertoe moeten doen Juist (VALS).
Laten we teruggaan naar de tabel van het allereerste voorbeeld en uitzoeken welk dier met een snelheid kan bewegen 50 mijl per uur. Ik geloof dat deze formule u geen problemen zal opleveren:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Merk op dat ons zoekbereik (kolom A) twee waarden bevat 50 – in cellen A5 и A6. Formule retourneert waarde uit cel B5. Waarom? Omdat bij het zoeken naar een exacte match de functie VPR gebruikt de eerste gevonden waarde die overeenkomt met de waarde waarnaar wordt gezocht.
Voorbeeld 2: VERT.ZOEKEN gebruiken om een geschatte overeenkomst te vinden
Wanneer u de functie gebruikt: VPR om te zoeken naar een benaderende overeenkomst, dat wil zeggen wanneer het argument bereik_opzoeken (range_lookup) is gelijk aan WARE CODE (TRUE) of weggelaten, het eerste dat u hoeft te doen, is het bereik sorteren op de eerste kolom in oplopende volgorde.
Dit is erg belangrijk omdat de functie VPR geeft de op één na grootste waarde na de gegeven waarde, waarna het zoeken stopt. Als u de juiste sortering verwaarloost, krijgt u zeer vreemde resultaten of een foutmelding. #AT (#N.v.t.).
Nu kunt u een van de volgende formules gebruiken:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Zoals je kunt zien, wil ik weten welke van de dieren de dichtstbijzijnde snelheid heeft 69 mijl per uur. En hier is het resultaat dat de functie naar mij heeft teruggestuurd VPR:
Zoals u kunt zien, heeft de formule een resultaat geretourneerd Antilope (Antelope), wiens snelheid 61 mijl per uur, hoewel de lijst ook bevat: Cheetah (Cheetah) die op snelheid rent 70 mijl per uur, en 70 is dichter bij 69 dan 61, nietwaar? Waarom gebeurt dit? Omdat de functie VPR bij het zoeken naar een benaderende overeenkomst, wordt de grootste waarde geretourneerd die niet groter is dan die waarnaar wordt gezocht.
Ik hoop dat deze voorbeelden enig licht werpen op het werken met de functie VPR in Excel, en je ziet haar niet meer als een buitenstaander. Nu kan het geen kwaad om de belangrijkste punten van het materiaal dat we hebben bestudeerd kort te herhalen om het beter in het geheugen vast te leggen.
VERT.ZOEKEN in Excel - u moet dit onthouden!
- Functie VPR Excel kan niet naar links kijken. Het zoekt altijd naar de waarde in de meest linkse kolom van het bereik dat door het argument wordt gegeven table_array (tafel).
- In functie VPR alle waarden zijn niet hoofdlettergevoelig, dwz kleine en grote letters zijn equivalent.
- Als de waarde die u zoekt kleiner is dan de minimumwaarde in de eerste kolom van het bereik dat wordt opgezocht, is de functie VPR zal een fout melden #AT (#N.v.t.).
- Als 3e argument kolomindex_getal (kolom_nummer) kleiner dan 1functie VPR zal een fout melden #WAARDE! (#WAARDE!). Als het groter is dan het aantal kolommen in het bereik table_array (tabel), zal de functie een fout rapporteren #REF! (#KOPPELING!).
- Gebruik absolute celverwijzingen in argument table_array (tabel) zodat het juiste zoekbereik behouden blijft bij het kopiëren van de formule. Probeer als alternatief benoemde bereiken of tabellen in Excel te gebruiken.
- Houd er bij het zoeken naar overeenkomsten bij benadering rekening mee dat de eerste kolom in het bereik dat u zoekt in oplopende volgorde moet worden gesorteerd.
- Onthoud ten slotte het belang van het vierde argument. Waarden gebruiken WARE CODE (WAARHEID) of Juist (FALSE) opzettelijk en je zult veel hoofdpijn kwijtraken.
In de volgende artikelen van onze functie-tutorial VPR in Excel leren we meer geavanceerde voorbeelden, zoals het uitvoeren van verschillende berekeningen met VPR, waarden extraheren uit meerdere kolommen en meer. Bedankt voor het lezen van deze tutorial en ik hoop je volgende week weer te zien!