1Aug

VLOOKUP gebruiken in Excel

VLOOKUP is een van Excel's handigste functies en het is ook een van de minst begrepen. In dit artikel demaken we VLOOKUP aan de hand van een realistisch voorbeeld. We maken een bruikbare -factuurtemplate voor een fictief bedrijf.

VLOOKUP is een Excel -functie .Dit artikel gaat ervan uit dat de lezer al een redelijk begrip van Excel-functies heeft en basisfuncties zoals SOM, GEMIDDELDE en VANDAAG kan gebruiken. In zijn meest voorkomende gebruik, is VERT.ZOEKEN een -database -functie, wat betekent dat het werkt met databasetabellen - of eenvoudiger, geeft een lijst met dingen in een Excel-werkblad. Wat voor soort dingen? Nou, is iets voor .Mogelijk hebt u een werkblad met een lijst met werknemers, producten, klanten of cd's in uw cd-verzameling of sterren aan de nachtelijke hemel. Het maakt niet echt uit.

Hier is een voorbeeld van een lijst of database. In dit geval is het een lijst met producten die door ons fictieve bedrijf worden verkocht:

Meestal hebben dergelijke lijsten een soort unieke ID voor elk item in de lijst. In dit geval bevindt de unieke ID zich in de kolom "Artikelcode".Opmerking: Voor de functie VLOOKUP om met een database / lijst te werken, moet die lijst

een kolom bevatten die de unieke ID( of "sleutel" of "ID") bevat en die kolom moet de eerste kolom in de tabel zijn.Onze voorbeelddatabase hierboven voldoet aan dit criterium.

Het moeilijkste deel van het gebruik van VLOOKUP is precies begrijpen waarvoor het is. Laten we dus eens kijken of we dat eerst duidelijk kunnen krijgen:

VLOOKUP haalt informatie uit een database / lijst op basis van een geleverd exemplaar van de unieke identifier.

In het bovenstaande voorbeeld zou u de VERT.ZOEKEN-functie in een andere spreadsheet met een artikelcode invoegen en zou u de beschrijving, de prijs of de beschikbaarheid ervan( de voorraad "In voorraad") aan u retourneren zoals beschreven in uworiginele lijst. Welke van deze stukjes informatie zal het je teruggeven? Nou, je moet dit beslissen wanneer je de formule aan het maken bent.

Als u slechts één stuk informatie uit de database nodig hebt, zou het een hele klus zijn om een ​​formule te maken met een VLOOKUP-functie erin. Meestal zou u dit soort functionaliteit gebruiken in een herbruikbare spreadsheet, zoals een sjabloon. Telkens wanneer iemand een geldige artikelcode invoert, haalt het systeem alle benodigde informatie over het betreffende artikel op.

Laten we hier een voorbeeld van maken: een -factuurtemplate die we steeds opnieuw kunnen gebruiken in ons fictieve bedrijf.

Eerst starten we met Excel en we creëren onszelf een blanco factuur:

Zo gaat het werken: de persoon die het factuurtemplate gebruikt, vult een reeks artikelcodes in kolom "A" in en het systeem haalt de items van elk item opbeschrijving en prijs uit onze productdatabase. Die informatie zal worden gebruikt om het lijntotaal voor elk artikel te berekenen( ervan uitgaande dat we een geldige hoeveelheid invoeren).

Om dit voorbeeld eenvoudig te houden, zullen we de productdatabase op een afzonderlijk blad in dezelfde werkmap vinden:

In werkelijkheid is de kans groter dat de productdatabase zich in een afzonderlijke werkmap bevindt. Het maakt weinig uit voor de functie VERT.ZOEKEN, wat niet echt uitmaakt of de database zich op hetzelfde blad, een ander blad of een geheel andere werkmap bevindt.

Dus we hebben onze productendatabase gemaakt, die er als volgt uitziet:

Om de VLOOKUP-formule die we gaan schrijven te testen, voeren we eerst een geldige artikelcode in cel A11 van onze blanco factuur in:

Vervolgens maken weverplaats de actieve cel naar de cel waarin we willen dat informatie die door VLOOKUP uit de database wordt opgehaald, wordt opgeslagen. Interessant is dat dit de stap is die de meeste mensen fout doen. Om verder uit te leggen: We staan ​​op het punt een VLOOKUP-formule te maken die de beschrijving die overeenkomt met de artikelcode in cel A11 zal ophalen. Waar willen we deze beschrijving laten zetten als we die krijgen? In cel B11 natuurlijk. Dus dat is waar we de VLOOKUP-formule schrijven: in cel B11.Selecteer nu cel B11.

We moeten de lijst met alle beschikbare functies die Excel te bieden heeft vinden, zodat we VLOOKUP kunnen kiezen en enige hulp kunnen krijgen bij het invullen van de formule. Dit wordt gevonden door eerst op het Formules -tabblad te klikken en vervolgens te klikken op Functie invoegen :

Er verschijnt een venster waarin we een van de beschikbare functies in Excel kunnen selecteren.

Om degene te vinden die we zoeken, kunnen we een zoekterm als "opzoeken" typen( omdat de functie waarin we geïnteresseerd zijn een -opzoekfunctie is).Het systeem zou ons een lijst van alle opzoekgerelateerde functies in Excel retourneren. VLOOKUP is de tweede in de lijst. Selecteer het een klik OK .

De -functierargumenten worden weergegeven en vragen ons om alle -argumenten ( of -parameters ) die nodig zijn om de VLOOKUP-functie te voltooien. U kunt dit vak zien als de functie die ons de volgende vragen stelt:

  1. Welke unieke ID zoekt u in de database op?
  2. Waar is de database?
  3. Welke informatie uit de database, gekoppeld aan de unieke identifier, wenst u voor u opgehaald te hebben?

De eerste drie argumenten worden weergegeven in vet , wat aangeeft dat het verplichte -argumenten zijn( de VLOOKUP-functie is onvolledig zonder deze en zal geen geldige waarde retourneren).Het vierde argument is niet vetgedrukt, wat betekent dat het optioneel is:

We zullen de argumenten in volgorde voltooien, van boven naar beneden.

Het eerste argument dat we moeten invullen is het Lookup_value -argument. De functie heeft ons nodig om aan te geven waar de unieke ID( de artikelcode in dit geval) te vinden is, dat de beschrijving van. We moeten de artikelcode selecteren die we eerder hebben ingevoerd( in A11).

Klik op het selectorpictogram rechts van het eerste argument:

Klik vervolgens eenmaal op de cel met de artikelcode( A11) en druk op Enter :

De waarde van "A11" wordt ingevoegd in het eerste argument.

Nu moeten we een waarde invoeren voor het Table-array -argument. Met andere woorden, we moeten VERT.ZOEKEN vertellen waar de database / lijst te vinden is. Klik op het selectorpictogram naast het tweede argument:

Zoek nu de database / lijst en selecteer de volledige lijst - zonder de headerregel .In ons voorbeeld bevindt de database zich op een afzonderlijk werkblad, dus we klikken eerst op dat werkbladtabblad:

. Vervolgens selecteren we de volledige database, exclusief de headerregel:

. .. en druk op Voer in. Het celbereik dat de database vertegenwoordigt( in dit geval '' Productdatabase '! A2: D7') wordt automatisch voor ons ingevoerd in het tweede argument.

Nu moeten we het derde argument invoeren, Col_index_num .We gebruiken dit argument om aan VLOOKUP op te geven welk stukje informatie uit de database, gekoppeld aan onze artikelcode in A11, we willen hebben teruggestuurd naar ons. In dit specifieke voorbeeld willen we dat de -beschrijving van het artikel aan ons wordt geretourneerd. Als u op het databasewerkblad kijkt, ziet u dat de kolom "Beschrijving" de -tweede -kolom in de database is. Dit betekent dat we een waarde van "2" moeten invoeren in het vak Col_index_num :

Het is belangrijk op te merken dat we hier geen "2" invoeren omdat de kolom "Beschrijving" in de kolom B staatdat werkblad. Als de database toevallig in kolom K van het werkblad zou starten, zouden we nog steeds een "2" in dit veld invoeren omdat de kolom "Beschrijving" de tweede kolom is in een reeks cellen die we hebben geselecteerd bij het opgeven van de "Tabelmatrix".

Ten slotte moeten we beslissen of we een waarde willen invoeren in het laatste VLOOKUP-argument, Range_lookup .Voor dit argument is een true of false -waarde vereist, of deze moet leeg worden gelaten. Bij het gebruik van VLOOKUP met databases( zoals 90% van de tijd het geval is), kan de manier om te beslissen wat in dit argument moet worden gezet als volgt worden beschouwd:

Als de eerste kolom van de database( de kolom die de unieke ID's bevat)is alfabetisch / numeriek gesorteerd in oplopende volgorde, dan is het mogelijk om een ​​waarde van true in te voeren in dit argument, of het veld leeg te laten.

Als de eerste kolom van de database niet is gesorteerd, of het in aflopende volgorde is gesorteerd, dan moet een waarde van false in dit argument invoeren

Aangezien de eerste kolom van onze database niet is gesorteerd,we voeren false in dit argument in:

Dat is alles! We hebben alle informatie ingevoerd die nodig is voor VERT.ZOEKEN om de waarde terug te geven die we nodig hebben. Klik op de OK -knop en merk op dat de beschrijving die overeenkomt met artikelcode "R99245" correct is ingevoerd in cel B11:

De formule die voor ons is gemaakt ziet er als volgt uit:

Als we een verschillende -artikelcode in de cel invoerenA11, we zullen beginnen de kracht van de VLOOKUP-functie te zien: de beschrijvingscel verandert om overeen te komen met de nieuwe artikelcode:

We kunnen een vergelijkbare reeks stappen uitvoeren om de -prijs van het artikel terug te krijgen in cel E11.Merk op dat de nieuwe formule moet worden aangemaakt in cel E11.Het resultaat ziet er als volgt uit:

. .. en de formule ziet er als volgt uit:

Merk op dat het enige verschil tussen de twee formules het derde argument is( Col_index_num ) is veranderd van een "2" in een "3"( omdatwe willen dat gegevens worden opgehaald uit de derde kolom in de database).

Als we besloten om 2 van deze items te kopen, zouden we een "2" invoeren in cel D11.We zouden dan een eenvoudige formule invoeren in cel F11 om het regeltotaal te krijgen:

= D11 * E11

. .. die er zo uitziet. ..

Het factuurtemplate invullen

Tot nu toe hebben we veel geleerd over VLOOKUP.In feite hebben we alles geleerd wat we in dit artikel zullen leren. Het is belangrijk om te weten dat VLOOKUP in andere omstandigheden dan databases kan worden gebruikt. Dit komt minder vaak voor en kan worden behandeld in toekomstige How-To Geek-artikelen.

Onze factuurtemplate is nog niet voltooid. Om het te voltooien, zouden we het volgende doen:

  1. We zouden de voorbeeldcode uit cel A11 en de "2" uit cel D11 verwijderen. Dit zorgt ervoor dat onze nieuw gemaakte VLOOKUP-formules foutmeldingen weergeven:

    We kunnen dit verhelpen door slim gebruik te maken van de IF() en ISBLANK() -functies van Excel. We veranderen onze formule van deze. .. = VLOOKUP( A11, 'Productdatabase'! A2: D7,2, FALSE) . .. tot dit. .. = IF( ISBLANK( A11), "", VERT.ZOEKEN( A11, 'Productdatabase'! A2: D7,2, FALSE))
  2. We zouden de formules in cellen B11, E11 en F11 naar de rest van de artikelrijen van de factuur kopiëren. Merk op dat als we dit doen, de resulterende formules niet langer correct verwijzen naar de databasetabel. We kunnen dit oplossen door de celverwijzingen voor de database te wijzigen in absolute celreferenties. Als alternatief - en zelfs beter - kunnen we een -bereiknaam voor de gehele productdatabase( zoals "Producten") maken en deze bereiknaam gebruiken in plaats van de celverwijzingen. De formule zou hier verandering in brengen. .. = IF( ISBLANK( A11), "", VERT.ZOEKEN( A11, 'Productdatabase'! A2: D7,2, FALSE)) . .. tot dit. .. = IF( ISBLANK( A11), "", VERT.ZOEKEN( A11, Producten, 2, ONWAAR)) . .. en en vervolgens kopiëren de formules naar de rest van de rijen met factuuritems.
  3. We zouden waarschijnlijk de cellen "blokkeren" die onze formules bevatten( of liever , de andere cellen ontgrendelen), en dan het werkblad beschermen, om ervoor te zorgen dat onze zorgvuldig opgebouwde formules niet per ongeluk overschreven worden wanneer iemand komt om te vullenop de factuur.
  4. We zouden het bestand opslaan als een -sjabloon , zodat het door iedereen in ons bedrijf opnieuw zou kunnen worden gebruikt

Als we het gevoel hadden dat echt slim is, zouden we een database van al onze klanten in een ander werkblad maken en vervolgens deklant-ID ingevoerd in cel F5 om de naam en het adres van de klant automatisch in cellen B6, B7 en B8 in te vullen.

Als u wilt oefenen met VERT.ZOEKEN, of gewoon onze resulterende Factuursjabloon wilt zien, kunt u deze hier downloaden.