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 Factuursjabloon 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 VLOOKUP een database -functie, wat betekent dat het werkt met databasetabellen - of eenvoudiger lijsten van dingen in een Excel-werkblad. Wat voor soort dingen? Wel, elke soort dingen. 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 eigenlijk niet 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 moet een kolom bevatten met de unieke ID (of "sleutel" of "ID") en die kolom moet zijn de eerste kolom in de tabel . 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 dit als eerste duidelijk kunnen krijgen:

VLOOKUP haalt informatie uit een database / lijst op basis van een geleverde instantie van de unieke ID.

In het bovenstaande voorbeeld zou u de VERT.ZOEKEN-functie invoegen in een andere spreadsheet met een artikelcode, en deze zou de beschrijving, de prijs of de beschikbaarheid (de voorraad "In voorraad") van het overeenkomstige item naar u terugbrengen, zoals beschreven in uw oorspronkelijke lijst. Welke van deze stukjes informatie zal het je teruggeven? Nou, je moet dit beslissen bij het maken van de formule.

Als alles wat je nodig hebt een stukje informatie uit de database is, zou het een hele klus zijn om een ​​formule te maken met een VLOOKUP-functie in het. 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 overeenkomstige item.

Laten we hier een voorbeeld van maken: een Factuursjabloon die we steeds opnieuw kunnen gebruiken in onze fictief bedrijf.

Eerst starten we 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 beschrijving en prijs van elk artikel op uit onze productdatabase. Die informatie zal worden gebruikt om het lijntotaal voor elk artikel te berekenen (aangenomen dat we een geldige hoeveelheid invoeren).

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

In werkelijkheid is de kans groter dat de productdatabase zich in een aparte 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.

We hebben dus onze productdatabase gemaakt, die er uitziet als volgt:

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 verplaatsen we de actieve cel naar de cel waarin we willen dat informatie die door VERT.ZOEKEN wordt opgehaald uit de database 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 tabblad Formules 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 waarnaar we op zoek zijn, kunnen we een zoekterm typen zoals 'opzoeken' (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 .

Het functieargumenten venster verschijnt, vraagt ​​ons om alle argumenten (of parameters ) die nodig zijn om de VLOOKUP-functie te voltooien. U kunt dit vak beschouwen 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. Welk deel van de informatie uit de database, geassocieerd met de unieke identifier, wilt u voor u opgehaald zijn?

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

we zullen de argumenten in volgorde voltooien, van boven naar onder.

Het eerste argument dat we moeten invullen is het argument Lookup_value . De functie heeft ons nodig om aan te geven waar de unieke ID te vinden is (de artikelcode in dit geval) 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 argument Table_array . 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 kopregel . 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 kopregel:

... en druk op Enter . 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 geretourneerd aan 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 kolom seconden in de database is. Dit betekent dat we een waarde van "2" in het vak Col_index_num moeten invoeren:

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

Uiteindelijk moeten we beslissen of we een waarde willen invoeren in het laatste VLOOKUP-argument, Range_lookup . Dit argument vereist een true of false -waarde of moet leeg blijven. Bij 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 kolom bevat) identificaties) alfabetisch / numeriek in oplopende volgorde is gesorteerd, dan is het mogelijk om een ​​waarde van in te voerentrue in dit argument, of laat het leeg.

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

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

Dat is het! We hebben alle informatie ingevoerd die nodig is voor VERT.ZOEKEN om de waarde terug te geven die we nodig hebben. Klik op de knop OK 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 andere artikelcode in cel A11, we zullen beginnen de kracht van de VERT.ZOEKEN-functie te zien: de beschrijvingscel verandert om overeen te komen met de nieuwe artikelcode:

We kunnen een vergelijkbare reeks stappen uitvoeren om te krijgen de prijs van het item is terug 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 "Naar een" 3 "(omdat we 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. Vervolgens voeren we een eenvoudige formule in in cel F11 om het regeltotaal te krijgen:

= D11 * E11

... die er zo uitziet ...

Het factuurtemplate invullen

We hebben veel geleerd over VLOOKUP dusver. 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 verstandig gebruik te maken van de functies IF () en ISBLANK () van Excel. We veranderen onze formule hier van ... = VLOOKUP (A11, 'Productdatabase'! A2: D7,2, FALSE) ... tot dit ... = IF (ISLEEG (A11), ", VLOOKUP (A11, 'Productdatabase'! A2: D7,2, FALSE))
2. We zouden de formules in cellen B11, E11 en F11 naar beneden kopiëren tot de rest van de artikelrijen van de factuur. 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 celverwijzingen. Als alternatief - en zelfs beter - kunnen we een bereiknaam maken voor de gehele productdatabase (zoals 'Producten') en deze bereiknaam gebruiken in plaats van de celverwijzingen. De formule zou hiervan veranderen ... = IF (ISBLANK (A11), ", VLOOKUP (A11, 'Productdatabase'! A2: D7,2, FALSE)) ... tot dit ... = ALS (ISLEEG (A11), ", VERT.ZOEKEN (A11, Producten, 2, ONWAAR)) ... en dan de formules kopiëren naar de rest van de rijen met factuuritems.
3. We zou waarschijnlijk de cellen "blokkeren" die onze formules bevatten (of liever ontgrendelen de andere cellen), en dan het werkblad beschermen, om ervoor te zorgen dat onze zorgvuldig opgebouwde formules niet toevallig zijn overschreven wanneer iemand komt om de factuur in te vullen.
4. We zouden het bestand opslaan als een sjabloon , zodat het opnieuw gebruikt zou kunnen worden door iedereen in ons bedrijf

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

Als u zou willen oefenen met VERT.ZOEKEN, of gewoon onze resulterende Factuur T zien emplate, het kan hier worden gedownload.

Hoe u en uw buren elkaars wifi-netwerk maken, erger (en wat u erover kunt doen)

Wi-Fi-netwerken interfereren met elkaar. Oudere wifi-normen zijn hier nog slechter voor, dus je oude wifi-hardware doet niet alleen pijn aan je netwerk - het interfereert ook met je buren. Al die interferentie is slecht voor het netwerk van iedereen, zelfs de jouwe. Tenzij je in het land woont zonder dat er iemand anders in de buurt is, is dit iets waar je aan moet denken.

(how-to)

Problemen met Windows 10 bevriezen of willekeurig vergrendelen

In het afgelopen jaar heb ik Windows 10 op ongeveer 4 pc's uitgevoerd en tot voor kort heb ik echt niet veel problemen ondervonden. Een paar dagen geleden begon ik een vreemd probleem te krijgen waarbij mijn Windows 10 PC willekeurig zou bevriezen, waardoor ik uiteindelijk gedwongen werd om een ​​harde reset uit te voeren op het systeem.Wat

(How-to)