Funcția VLOOKUP. Utilizând funcția VLOOKUP. Excel - VPR

Programul de aplicații

Una dintre cele mai cunoscute formule Excel este vizionarea pe verticală. Utilizarea funcției VLOOK la prima vedere pare destul de complicată, dar este doar la început.

Cum funcționează Excel

Când se lucrează cu formula VLR, trebuie luată în considerare faptul că aceasta caută valoarea dorită exclusiv prin coloane și nu prin rânduri. Pentru a utiliza funcția, este necesar numărul minim de coloane - două, maximul este absent.

Funcția VLOOK caută un criteriu dat, care poate avea orice format (text, numeric, monetar, data și ora etc.) din tabel. În cazul în care se găsește o înregistrare, rezultă (înlocuiește) valoarea introdusă în același rând, dar din coloana necesară a tabelului, adică în funcție de criteriul specificat. Dacă nu se găsește valoarea dorită, se emite eroarea # N / A (în versiunea în limba engleză # N / A).

Trebuie să utilizați

Funcția VLOOKUP ajută operatorul atunci când este necesar să găsească rapid și să aplice o anumită valoare dintr-un tabel mai mare în calcule, analize sau prognoze suplimentare. Principalul lucru la utilizarea acestei formule este să vă asigurați că zona de căutare specificată a fost selectată corect. Ar trebui să includă toate înregistrările, adică de la primul la ultimul.

Cel mai frecvent caz de utilizare a VLF (Funcția Excel) - aceasta este o comparație sau adăugare de date în două tabele, utilizând un anumit criteriu. Intervalele de căutare pot fi mari și pot găzdui mii de câmpuri, plasate pe foi sau cărți diferite.

Funcția VLF este prezentată, cum se utilizează, cum se efectuează calcule, ca exemplu în figura de mai sus. Aici este luată în considerare tabelul dimensiunilor vânzărilor cu amănuntul în funcție de regiune și de manager. Criteriul de căutare este un manager specific (numele și prenumele lui), iar valoarea căutată este valoarea vânzărilor sale.

Ca urmare a funcției VLOOKUP, se creează un nou tabel în care un manager specific dorit își compară rapid suma de vânzări.

Algoritmul de completare a formulei

Formula VLOOK este localizată în fila "Expert funcțional" și secțiunea "Referințe și tablouri". Dialogul de funcții are următorul aspect:

Argumentele din formula sunt introduse în ordinea coadajului:

• Valoarea de căutare este ceea ce ar trebui să găsească funcția, iar opțiunile sunt valorile celulei, adresa, numele dat de operator. În cazul nostru, acesta este numele și numele administratorului.
• Tabel - gama de rânduri și coloane în care criteriul este căutat.
• Numărul coloanei este numărul său ordinal, în care se află suma de vânzare, adică rezultatul formulei de lucru.
• Interval de vizualizare. Acesta deține valoarea FALSE sau TRUE. Și FALSE returnează doar o potrivire exactă, TRUE - vă permite să căutați o valoare aproximativă.

Exemplu de utilizare a funcției

Funcția VLOOKUP poate avea următorul exemplu: atunci când se ocupă de o afacere într-o foaie de calcul Excel, coloana A indică numele produsului și, în coloana B, prețul corespunzător. Pentru a scrie o propoziție în coloana C, trebuie să găsiți costul pentru un anumit produs pe care doriți să-l afișați în coloana D.

Formula, scrisă în D, va arata astfel: = VLOOKUP (C1 A1: V5- 2 0), adică = CDF (interval dorit de cu valoare de date table- stolbtsa- numărul de ordine 0). Ca al patrulea argument, în loc de 0, puteți utiliza FALSE.

Pentru a completa tabelul de propoziții, formula rezultată trebuie copiată în întreaga coloană D.

Pentru a stabili domeniul domeniului de lucru al datelor, puteți utiliza referințe absolute. Pentru a face acest lucru, semnele $ sunt precedate manual înaintea valorilor alfabetice și numerice ale adreselor celulelor extreme stânga și dreaptă ale tabelului. În cazul nostru, formula ia forma: = VPR (C1 - $ A $ 1: $ B $ 5- 2 - 0).

Erori în uz

Funcția VLOOKUP nu funcționează și apoi apare un mesaj în coloana de ieșire a rezultatului de eroare (# N / A sau # N / A). Acest lucru se întâmplă în astfel de cazuri:

1. Formula este introdusă, iar coloana criteriilor cerute nu este completă (în acest caz, coloana C).
2. Coloana C conține o valoare care lipsește în coloana A (în intervalul de căutare a datelor). Pentru a verifica disponibilitatea valorii dorite, selectați coloana criteriilor, iar în meniul tab-ului "Editare" - "Găsiți" introduceți această intrare, începeți căutarea. Dacă programul nu o găsește, atunci lipsește.
3. Formatele celulelor din coloanele A și C (criteriile cerute) sunt diferite, de exemplu, unul are text și celălalt are unul numeric. Aveți posibilitatea să modificați formatul celular dacă mergeți la editarea celulelor (F2). Astfel de probleme apar de obicei atunci când se importă date din alte aplicații. Pentru a evita astfel de erori, următoarele funcții pot fi integrate în formula VLOOK: KEY sau TEXT. Execuția acestor algoritmi convertește automat formatul celulelor.
4. Există caractere sau spații netipăribile în codul de funcții. Apoi, ar trebui să verificați cu atenție formula pentru prezența erorilor de intrare.
5. Se specifică o căutare aproximativă, adică al patrulea argument al funcției VLOOK are o valoare de 1 sau TRUE, iar tabelul nu este sortat după o valoare ascendentă. În acest caz, coloana criteriilor solicitate trebuie sortată în ordine crescătoare.

Mai mult, atunci când se organizează un nou tabel rezumativ, criteriile de căutare specificate pot fi în orice ordine și ordine și nu trebuie să fie conținute într-o listă completă (selecție parțială).

Caracteristicile de utilizare ca intervale de vedere 1 sau TRUE

Eroarea la numărul 5 este destul de comună și este ilustrată în mod clar în figura de mai jos.

În acest exemplu, lista de nume conform numerotării este sortată nu în ordine ascendentă, ci într-o ordine descrescătoare. Și ca o vedere de intervale utilizate criteriul este TRUE (1), care anulează imediat căutarea atunci când se găsește o valoare mai mare decât cea căutată, astfel încât se generează o eroare.

Când aplicați 1 sau TRUE în al patrulea argument, trebuie să vă asigurați că coloana cu criteriile căutate este sortată în ordine ascendentă. Folosind 0 sau FID, această nevoie dispare, dar nu există nici o posibilitate de scanare în interval.

Rețineți că este deosebit de important să sortați tabelele de intervale. În caz contrar, funcția VLOOKUP va transmite date incorecte celulelor.

Alte nuanțe la lucrul cu funcția VLOOKUP

Pentru comoditatea de a lucra cu o astfel de formulă, puteți să titlați intervalul tabelului în care se efectuează căutarea (al doilea argument), după cum se arată în figură.

În acest caz, zona tabelului de vânzări are dreptul. În acest scop, se selectează un tabel, cu excepția anteturilor coloanelor, iar numele (în partea stângă sub bara tab) este denumit.

O altă opțiune - titlu - implică selectarea unei game de date, apoi accesați meniul "Insert" - "Name" - "Assign".

Pentru a utiliza datele plasate pe altă pagină a foii de lucru, utilizând funcția VLOOKUP, trebuie să specificați locația intervalului de date în al doilea argument al formulei. De exemplu, = VPR (A1-Listă2! $ A $ 1: $ B $ 5- 2-0), unde foaia 2! - este o legătură către foaia necesară a cărții și $ A $ 1: $ In $ 5 - adresa domeniului de recuperare a datelor.

Un exemplu de organizare a procesului educațional cu VPR

Este destul de convenabil în Excel să utilizeze funcția VPR nu numai pentru firmele implicate în comerț, ci și pentru instituțiile educaționale pentru a optimiza procesul de comparare a elevilor (studenților) cu evaluările lor. Exemple ale acestor sarcini sunt prezentate în figurile de mai jos.

Există două tabele cu liste de elevi. Unul cu estimările lor, al doilea indică vârsta. Este necesar să se compare ambele tabele astfel încât, împreună cu vârsta studenților, să se deducă notele lor, adică să se introducă o coloană suplimentară în a doua listă.

Funcția VLOOK se potrivește perfect cu soluția acestei probleme. În coloana G, sub titlul "Estimări" se scrie formula corespunzătoare: = VPR (E4, B3: C13, 2, 0). Trebuie copiat în întreaga coloană a tabelului.

Ca rezultat, funcția VLR va produce estimări primite de anumiți studenți.

Un exemplu de organizare a unui motor de căutare cu VLOOK

Un alt exemplu de utilizare a funcției VLOOK este organizarea motorului de căutare, atunci când în baza de date în funcție de criteriul specificat este necesar să se găsească valoarea corespunzătoare acestuia. Deci, figura prezintă o listă cu poreclele animalelor și apartenența lor la o anumită specie.

Cu ajutorul VLP, este creată o nouă masă, în care este ușor să găsiți numele animalului după numele animalului. Aceste motoare de căutare sunt relevante atunci când lucrați cu liste mari. Pentru a nu revizui manual toate înregistrările, puteți utiliza rapid căutarea și obțineți rezultatul dorit.