Share
A VLOOKUP, scurt pentru "căutare verticală" este o formulă în Microsoft Excel pentru a se potrivi cu datele din două liste. În loc să săriți între foi de calcul și tastând datele potrivite, puteți scrie o formulă VLOOKUP pentru a automatiza procesul.
Combinarea a două liste este o situație perfectă pentru a utiliza un VLOOKUP. În stânga (în imaginea de mai sus), avem informații despre schimbarea angajaților. Vrem să adăugăm titlul de angajat al angajatului la datele privind schimburile. Cu o listă separată de angajați și titlurile lor de locuri de muncă, putem scrie o formulă VLOOKUP pentru a trage titlul dintr-o listă de căutare.
Un VLOOKUP de succes are nevoie de trei lucruri:
VLOOKUP este o formulă Microsoft Excel care este esențială pentru lucrul cu mai multe seturi de date. În acest tutorial, vă voi învăța cum să-l stăpâniți și să-l folosiți.
Exemplul formula VLOOKUP folosită pentru a căuta datele angajaților.Folosind exemplul de mai sus, am scris o formulă VLOOKUP care caută ID-ul angajatului și introduce titlul postului în datele de schimbare. Deoarece ambele coli au un ID de angajat, Excel poate căuta titlul de loc de muncă corespunzător. Cea mai bună parte a VLOOKUP este că acum pot trage aceeași formulă în jos și va căuta fiecare titlu de post unic.
Înainte de a merge mai departe, asigurați-vă că Descărcați atașamentul gratuit să urmeze de-a lungul. Este un exemplu de registru de calcul tabelar pe care l-am creat, pe care îl vom folosi pentru a merge prin acest tutorial.
Pentru cea mai rapidă modalitate de a învăța noțiunile de bază ale formulei VLOOKUP, consultați ecranul de mai jos. Videoclipul trece prin mai multe exemple ale formulei VLOOKUP, folosind exemplarul de lucru.
Continuați să citiți pentru a merge prin instrucțiunile scrise și pentru a afla câteva tehnici suplimentare care nu sunt acoperite de ecran.
Utilizați filele "Comenzi pentru ingrediente" și "Lista furnizorilor" pentru acest exemplu Excel VLOOKUP.
Să spunem că gestionăm un restaurant și plasăm comenzile săptămânale către furnizori. Bucătarii ne-au dat o listă de ingrediente la comandă și trebuie să introducem informații despre furnizor.
Există trei bucăți pe care trebuie să le adăugăm pentru fiecare comandă:
În acest registru de lucru există două file:
În partea de sus, avem datele noastre de comandă, care se află pe fila "Comenzi pentru ingrediente". Mai jos este lista de furnizori, care va funcționa ca o listă de căutare.Câmpul comun dintre cele două tabele este Ingredient tab. Să o folosim pentru a căuta fiecare din cele trei câmpuri și a le adăuga la lista de comenzi.
Pe Comenzile pentru ingrediente , faceți clic pe prima celulară Furnizor necompletată, F5, și apăsați semnul equals pentru a începe formula VLOOKUP. Apoi, tastați " VLOOKUP ( " pentru a începe formula.
= VLOOKUP (
Amintiți-vă că cheia noastră primară - piesa de date care apare în ambele liste - este ingredient, așa că o vom folosi pentru căutare. Fie faceți clic pe celulă B5, sau tastați-o în formulă. Apoi, adăugați o virgulă după "B5", astfel încât să putem introduce următoarea parte a formulei.
= VLOOKUP (B5,
Acum, trebuie să oferim formularul nostru lista de căutare. Cu formula încă deschisă, faceți clic pe Lista furnizorilor tab. Acum, faceți clic pe celula A3 și faceți clic și trageți pentru a evidenția și a selecta A3 la G13, întreaga masă de căutare. Asigurați-vă că și apăsați Tasta F4 de pe tastatură pentru a face formula o referință absolută (mai multe despre aceasta mai târziu). În cele din urmă, introduceți o altă virgulă.
Punctați Excel în lista de căutare.După ce introduceți virgulă pentru celula de căutare, comutați tab-urile și indicați Excel în lista de căutare. Faceți clic și trageți între celulele A3 și G3 pentru a selecta datele la care doriți să căutați. Asigurați-vă că și apăsați F4 pe tastatură în timpul acestui pas pentru a crea o referință absolută, care va bloca celulele pe care să le utilizeze pentru căutare.
= VLOOKUP (B5, "Lista furnizorilor"! $ A $ 3: $ G $ 13,
În continuare, va trebui să îi spunem Excel care coloană să tragă. Amintiți-vă că primul nostru element de inserat este Furnizor nume, care se află în a doua coloană a listei de căutare. Adăugați numărul 2 la formula pentru a trage din cea de-a doua coloană a căutării și o altă virgulă.
= VLOOKUP (B5, "Lista furnizorilor"! $ A $ 3: $ G $ 13,2,
În cele din urmă, vom adăuga FALSE pentru o căutare exactă și apoi închideți parantezele:
= VLOOKUP (B5, "Lista furnizorilor! $ A $ 3: $ G $ 13,2, FALSE)
Formula noastra functioneaza perfect, tragand furnizorul pentru cartofi. Vestea bună este că nu este nevoie să rescrieți aceeași formulă peste și peste - doar dublu-clic în colțul din dreapta jos al celulei F5 (puteți vedea cutia mică verde de pe colțul celulei) Pentru a extinde formula jos (după cum se arată mai sus).
Formula funcționează perfect! Bine, acum să mergem la introducerea datelor pentru celelalte două câmpuri: numărul de telefon al furnizorului și ziua livrării.
Pentru că am folosit o referință absolută, putem reutiliza, în principiu, aceeași formulă pe care am scris-o cu un twitter minor. Să adăugăm în continuare numărul de telefon furnizor.
Vom începe prin copierea și lipirea celulelor F5 (celula furnizorului nostru) către celulă G5. De obicei folosesc doar comenzile rapide de la tastatură Ctrl + C și Ctrl + V să copiați și să lipiți întreaga celulă.La început, acest lucru nu va funcționa și veți vedea unul N / A în celulă.
Copierea și inserarea de căutare cu ajustări în Excel.Este mult mai ușor să copiați și să lipiți formulele noastre într-o altă celulă, dar este nevoie de unele ameliorări. La început, Excel va căuta celula C5 pentru căutare, dar trebuie să o ajustăm la "B5" în bara de formule. Odată ce facem asta, căutarea va funcționa - aproape.
Va trebui să mergem la formula barului și să schimbăm prima parte a formulei C5 înapoi la B5. Când am mutat formula peste o coloană, Excel a actualizat alte părți ale formulei. Obțineam un "N / A" deoarece Excel încerca să se potrivească cantității (Coloana C) cu lista noastră de căutare, dar lista noastră de căutare nu include cantitatea.
Până acum, formula noastră ar trebui să fie:
= VLOOKUP (B5, "Lista furnizorilor! $ A $ 3: $ G $ 13,2, FALSE)
Cu toate acestea, observați în captura de ecran de deasupra că tragem un bit greșit de date în coloana "Telefon". Încă trăim cea de-a doua coloană din lista de căutare cu "2" în formula. Trebuie să schimbăm numărul de coloană cu telefonul de contact al furnizorului nostru.
Să ne întoarcem și să verificăm lista de căutare. Veți observa că numărul de telefon se află în coloana a 7-a din lista de căutare. Să ne întoarcem la formula noastră Excel și să actualizăm căutarea pentru a trage din coloana a șaptea.
Trebuie să actualizăm formula "2" din formula noastră Excel la un "7" pentru a trage din coloana a șaptea în lista noastră de căutare.Tot ce trebuie să facem este să actualizăm secțiunea coloană a listei noastre de căutare de la "2" la "7" și acum funcționează grozav!
Observați că telefonul furnizor funcționează bine acum că am actualizat formula noastră pentru a folosi coloana a 7-a din lista de căutare. Acum, glisați formula în jos pentru ao actualiza pentru toate celulele.Formula noastră finală pentru căutarea numărului de telefon furnizor:
= VLOOKUP (B5, "Lista furnizorilor" $ A $ 3: $ G $ 13,7, FALSE)
În cele din urmă, hai să lucrăm în coloana "Ziua livrării furnizorului". Copiați și lipiți celula G5 la celulă H5. Din nou, va trebui să rezolvăm formula noastră prin schimbare C5 înapoi la B5 pentru a utiliza ingredientul ca cheie primară. Apoi, actualizați "7" la "5" pentru a utiliza coloana a 5-a din tabelul de căutare.
S-a aplicat formula finală de livrare a furnizorului.Formula noastră finală pentru ziua livrării furnizorului:
= VLOOKUP (B5, "Lista furnizorilor! $ A $ 3: $ G $ 13,5, FALSE)
Asta e! Am scris în esență o formulă și am modificat-o pentru a trage în fiecare bit de date de care avem nevoie pentru a plasa următoarea noastră comandă.
Deci, ați scris formula VLOOKUP urmând instrucțiunile pas cu pas, dar încă nu funcționează. Excel ia lucrurile destul de literal, așa că trebuie să fim atenți cu datele și formula VLOOKUP. Să aruncăm o privire la mai multe idei pentru a corecta o formulă VLOOKUP care nu funcționează.
Una dintre cele mai frecvente probleme cu VLOOKUP este atunci când există mai multe potriviri în lista dvs. de căutare. Când utilizați o VLOOKUP, se va potrivi cu primul element din listă.
VLOOKUP spune că Carrie Richard este directorul nostru de marketing, dar știm că a fost recent promovată la președintele companiei. De ce nu funcționează căutarea noastră?
Oricum, se pare că au fost două listări pentru Carrie Richard în lista de căutare - una pentru directorul de marketing și una pentru președinte. După ce ștergem linia "Director de marketing" din lista de căutare, datele noastre sunt corecte.Punctul de VLOOKUP este de a căuta un element de potrivire împotriva unei liste. Lista de căutare nu trebuie să conțină duplicate pentru cheie principală, elementul pe care îl utilizați pentru a se potrivi. În caz contrar, Excel devine confuz și vă arată doar primul meci.
O altă problemă comună este că datele noastre ar putea să nu se potrivească de fapt în ochii Excel. Datele cu un spațiu înainte de a se spune că au un "spațiu" de conducere, în timp ce datele cu un spațiu după ce are un "spațiu înclinat".
Acest VLOOKUP este perfect scris, dar nu funcționează. Puteți vedea în mod clar numele este în lista de căutare, dar Excel nu este returnat o potrivire. Citiți mai departe pentru a afla de ce.Dacă piesa potrivită are un spațiu înainte sau după ea, Excel vede aceste două fragmente de date ca fiind total diferite și nu va reveni la o potrivire. Excel vede "_Andrew", "Andrew_" și "Andrew" ca trei piese unice de date care nu vor fi potrivite în VLOOKUP.
Se pare că există un spațiu "în picioare" sau un spațiu după numele din celulă. Este imposibil de văzut, dar poate rupe un VLOOKUP deoarece Excel tratează "Alyssa Reddall" și "Alyssa Reddall (spațiu)" diferit. După ștergerea spațiului, VLOOKUP va funcționa normal.Dacă datele dvs. au spații de conducere sau de sfârșit, utilizați TUNDE în Excel pentru ao curăța. VLOOKUP ar trebui să funcționeze excelent după ce a folosit-o pentru a elimina spațiile de conducere și de sfârșit.
Așa cum ați putea ști, puteți trage o celulă jos de mâner pentru a lipi formulele în alte celule. Cu toate acestea, acest lucru ne distruge uneori VLOOKUP deoarece referințele celulelor se schimbă.
În această captură de ecran, am tras VLOOKUP în jos pentru a le aplica altor celule. Cu toate acestea, observați că referința de căutare a fost schimbată de la A2 la B15, de la A16 la B29, motiv pentru care formula nu funcționează. Lista mea de căutări este de fapt A2-B15, deci căutarea este acum ruptă.Pe măsură ce trageți formulele în jos, trageți referința pentru tabelul de căutare din aliniere. Dintr-o dată, în tabelul de căutare lipsesc rândurile din tabelul de căutare și VLOOKUP nu funcționează.
Remedierea este de a face formula a referință absolută, astfel încât atunci când trageți formula jos, lista pe care o indică nu se schimbă. Faceți clic în celula în care ați scris VLOOKUP, apoi faceți clic pe undeva în referința listei de căutare. Apoi, apăsați F4 . Veți observa că formula se modifică pentru a include semnele dolarului.
Observați că formula (prezentată în partea superioară a acestei imagini în bara de formule) include acum semnele de dolar din referința listei de căutare, care arată că folosește o referință absolută. Acum, când trag formula în jos, blochează formula pentru lista de căutare și funcționează perfect.VLOOKUP este una dintre acele formule esențiale pentru a fi utilizatori Excel productivi. Pur și simplu nu este suficient timp să căutați manual datele și să le repetați din nou și din nou, astfel încât formule precum VLOOKUP sunt importante pentru a învăța.
Dacă aveți probleme cu utilizarea formulei VLOOKUP, vă rugăm să lăsați un comentariu mai jos pentru depanare.
Accentsconagua