Ce este o versiune demo? Funcția CĂUTARE V în Excel - Exemplu de funcție CĂUTARE V cu un nume de interval. Este necesar să completați valorile pentru funcția VLOOKUP

Microsoft Excel - un instrument grozav pentru lucrul cu date. Posibilitățile sunt enorme, dar potențialul a fost, de fapt, puțin studiat, deoarece regulile de scriere a comenzilor provoacă dificultăți chiar și pentru profesioniști. Dar dacă muncești din greu, se dovedește că aici poți face lucruri incredibile. Funcția Excel VLOOKUP este unul dintre instrumentele de prelucrare a datelor și despre asta vom vorbi acum.

Ce este funcția VLOOKUP în Excel - scope

La procesarea mai multor tabele, adesea situate în cărți diferite, este necesar să transferați date de la unul la altul, asigurându-vă în același timp că valorile nu își pierd sensul și procesul este automatizat. Mecanismul din Excel ar trebui să funcționeze simplu și rapid.

De exemplu, avem o întreprindere. Ivan Ivanovici lucrează acolo. Pe o foaie este stocată valoarea salariului său, pe cealaltă suma de bani pe care departamentul de contabilitate o reține din salariu pentru amendă. Trebuie să combinați toate valorile într-un singur document. Un alt exemplu, există două tabele: prețuri și depozit. Unul indică costul batistelor, celălalt numărul acestora. Este necesar să se consolideze cantitatea și prețul într-un singur loc.

În cazurile în care există doar doi sau trei angajați ai întreprinderii sau până la o duzină de bunuri, puteți face totul manual. Cu grija cuvenită, o persoană va lucra fără erori. Dar dacă există, de exemplu, o mie de valori de procesat, este necesară automatizarea muncii. În acest scop, Excel are VLOOKUP.

Exemple pentru claritate: în tabelele 1 și 2 - datele inițiale, tabelul 3 - ce ar trebui să se întâmple.

Tabelul de date inițial 1

Tabelul de date combinat 3

Funcția VLOOKUP în Excel - cum se utilizează

Pentru ca Tabelul 1 să ajungă la forma sa finală, introducem un titlu de coloană în el, de exemplu „Fine”. De fapt, acest lucru nu este necesar, puteți scrie orice text sau îl puteți lăsa necompletat. Funcția va funcționa și făcând clic cu mouse-ul în câmpul în care ar trebui să apară valoarea găsită într-un alt tabel.

Acum trebuie să apelăm funcția. Acest lucru se poate face în diferite moduri:

Este necesar să completați valorile pentru funcția VLOOKUP

Rezultatul este evident - în Tabelul 3 (vezi mai sus).

VLOOKUP – instrucțiuni pentru lucrul cu două condiții

În situațiile în care este necesar să se stabilească criteriile de căutare nu după o singură condiție, ci pe mai multe sau două coloane simultan, funcția CĂUTARE VL poate fi folosită și pentru lucru. Pentru a face acest lucru, datele inițiale trebuie modificate.

De exemplu, trebuie să introduceți prețul din tabelul 5 în tabelul 4.

Tabelul cu caracteristicile telefonului 4

Exemplul ales este pe telefoane, dar este clar că datele pot fi absolut orice. După cum puteți vedea din tabele, mărcile de telefoane nu diferă, dar RAM și Camera diferă. Pentru a crea date rezumative, trebuie să selectăm telefoanele după marcă și RAM. Pentru a utiliza funcția CĂUTARE V pentru mai multe condiții, trebuie să combinați coloanele cu condiții.

Adăugați coloana din stânga. De exemplu, o numim „Unificare”. În prima celulă de valori, avem B 2, scriem construcția „= B 2& C 2”. Reproducem folosind mouse-ul. Rezultă ca în tabelul 6.

Tabelul cu caracteristicile telefonului 6

Procesăm Tabelul 5 exact în același mod. Apoi folosim funcția VLOOKUP pentru a căuta după o condiție. Condiția sunt datele din coloanele unite. Nu uitați că numărul coloanei de la care provin datele din funcția CĂUTARE VD se va modifica. După aplicarea funcției, se va obține o selecție pe baza a două condiții. Puteți îmbina nu coloanele adiacente, ci coloanele cu marca telefonului și camera foto.

Urmărește tutorialul video despre cum să folosești funcția CĂUTARE VL în Excel pentru manechini:

Funcția VLOOKUP din Excel nu este greu de utilizat, iar posibilitățile de prelucrare a datelor sunt enorme.

Când trebuie să găsiți date după rând într-un tabel sau interval, utilizați funcția CĂUTARE V, una dintre funcțiile de link și căutare. De exemplu, puteți găsi prețul piese auto după numărul ei.

Sfat: Urmăriți aceste videoclipuri pentru a obține Informații suplimentare despre funcția CĂUTARE V!

Cel mai mult funcție simplă VPR înseamnă următoarele:

CĂUTARE V(valoare de căutare; interval pentru a căuta valoarea; numărul coloanei din intervalul cu valoarea returnată; potrivire exactă sau aproximativă - specificată ca 0/FALSE sau 1/ADEVĂRAT).

Sfat: Funcție secretă O CĂUTARE V aranjează datele astfel încât valoarea de căutare (fructul) să apară în stânga valorii de returnare (valoare) pe care doriți să o găsiți.

Utilizați funcția CĂUTARE V pentru a căuta o valoare într-un tabel.

Sintaxă

CĂUTARE V(valoare_căutare, tabel, număr_coloană, [căutare_interval])

De exemplu:

CĂUTARE V(105;A2:C7;2;ADEVĂRAT)

CĂUTARE V ("Ivanov";B2:E7;2;FALSE)

Noțiuni de bază

Pentru a construi sintaxa funcției VLOOKUP veți avea nevoie următoarele informații:

Valoarea pe care trebuie să o găsiți este valoarea pe care o căutați.

Intervalul în care se află valoarea de căutare. Amintiți-vă că pentru funcționare corectăÎn funcția CĂUTARE V, valoarea pe care o căutați trebuie să fie întotdeauna în prima coloană a intervalului. De exemplu, dacă valoarea pe care o căutați este în celula C2, intervalul trebuie să înceapă de la C.

Numărul coloanei din intervalul care conține valoarea returnată. De exemplu, dacă specificați B2:D11 ca interval, ați considera că B este prima coloană, C este a doua și așa mai departe.

Opțional, puteți specifica cuvântul TRUE dacă aveți nevoie de o potrivire aproximativă sau cuvântul FALSE dacă aveți nevoie potrivire exactă valoarea returnată. Dacă nu specificați nimic, valoarea implicită este întotdeauna TRUE, care este o potrivire aproximativă.

Acum combinați toate argumentele de mai sus după cum urmează:

CĂUTARE V (valoare de căutare; interval cu valoare de căutare; numărul coloanei în interval cu valoare returnată; opțional TRUE pentru o potrivire aproximativă sau FALSE pentru o potrivire exactă).

Exemple

Iată câteva exemple de funcție CĂUTARE VL:

Exemplul 1

Exemplul 2

Exemplul 3

Exemplul 4

Exemplul 5

Probleme comune

Acest articol este dedicat Funcțiile CĂUTARE V. Se va lua în considerare instrucțiuni pas cu pas Funcțiile CĂUTARE V, numită „”. În acest articol vom arunca o privire detaliată asupra descrierii, sintaxei și exemple ale funcției CĂUTARE V din Excel. Vom analiza, de asemenea, cum să-l folosim și vom analiza principalele greșeli și de ce. Funcția VLOOKUP nu funcționează.

Sintaxa și descrierea funcției VLOOKUP în Excel

Deci, deoarece al doilea titlu al acestui articol este „ Funcția VLOOKUP în Excel pentru manechine„, să începem prin a afla ce este și ce face funcția VLOOKUP? Funcția VLOOKUP în engleză CĂUTARE V, caută valoarea specificată și returnează valoarea corespunzătoare dintr-o altă coloană.

Cum funcționează funcția VLOOKUP?? Funcția CĂUTARE V din Excel caută în listele dvs. de date pe baza identificator unicși vă oferă o informație asociată cu acel identificator unic.

Litera „V” în VPR înseamnă „verticală”. Este folosit pentru a diferenția Funcțiile GLOOKUP și GLOOKUP, care caută valoarea în linia de sus matrice („H” înseamnă „orizontal”).

Funcția VLOOKUP este disponibilă în toate versiuni Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Sintaxă Funcțiile CĂUTARE V arata asa:

CĂUTARE V(valoare_căutare, tabel, număr_coloană, [căutare_interval])

După cum puteți vedea, Funcția VLOOKUP are 4 parametri sau argumente. Primii trei parametri sunt necesari, ultimul este optional.

1. valoare_căutare este valoarea de căutare.

Aceasta poate fi fie o valoare (număr, dată sau text) fie referință de celulă(o referință la celula care conține valoarea de căutare) sau valoarea returnată de o altă funcție Excel. De exemplu:

• Căutați un număr: =VLOOKUP(40; A2:B15; 2) - formula va căuta numărul 40.
• Căutare text: =VLOOKUP(„mere”, A2:B15, 2) - formula va căuta textul „mere”. Vă rugăm să rețineți că includeți întotdeauna valorile textuluiîn „ghilimele duble”.
• Căutarea unei valori dintr-o altă celulă: =VLOOKUP(C2, A2:B15, 2) - formula va căuta valoarea în celula C2.

1. un tabel este două sau mai multe coloane de date.

Ține minte asta Funcția VLOOKUP caută întotdeauna valoarea dorită în prima coloană a tabelului. Tabelul dvs. poate conține diverse valori, cum ar fi text, data, numere sau valori booleene. Valori insensibil la majuscule, ceea ce înseamnă că majuscule și litere mici sunt considerate identice.

Deci formula noastră =CĂUTAREV(40, A2:B15, 2) va căuta „40” în celulele A2 la A15, deoarece A este prima coloană a tabelului A2:B15.

1. column_number - numărul coloanei din tabel din care trebuie returnată valoarea din rândul corespunzător.

Coloana din stânga din tabelul specificat este 1, a doua coloană este 2, a treia este 3 etc.

4. interval_view determină dacă căutați o potrivire exactă (FALSE) sau o potrivire aproximativă (adevărată sau omisă). Acest ultim parametru este opțional, dar foarte important.

Funcția VLOOKUP în exemplele Excel

Acum să ne uităm la câteva cazuri de utilizare Funcțiile CĂUTARE V pentru date reale.

Funcția VLOOKUP pe diferite foi

În practică formule VLOOKUP rar folosit pentru a căuta date pe o singură foaie. De cele mai multe ori, va trebui să căutați și să extrageți datele relevante dintr-o altă foaie.

La utilizați funcția CĂUTARE V din alta Foaie Excel, trebuie să introduceți numele foii de lucru și semnul exclamăriiîn argumentul tabelului înainte de intervalul de celule, de exemplu, =CĂUTAREV(40;Sheet2!A2:B15;2). Formula indică faptul că intervalul de căutare A2:B15 se află în Sheet2.

Desigur, nu trebuie să introduceți manual numele foii. Doar începeți să tastați formula și, când vine vorba de argumentul tabelului, treceți la foaia de lucru de căutare și selectați intervalul cu mouse-ul.

Formula pe care o vedeți în imaginea de mai jos caută text în celula A2 ("Produsul 3") în coloana A (prima coloană a intervalului de căutare A2:B9) din foaia de lucru "Prețuri":

CĂUTARE V (A2, prețuri! 2 USD: 8 USD, 2 USD, FALSE)

Funcția VLOOKUP în Excel - Funcția VLOOKUP pe diferite foi

Cum să utilizați un interval sau un tabel denumit în formulele CĂUTARE V

Dacă intenționați să utilizați același interval de căutare în mai multe formule VLOOKUP, puteți crea și introduce un nume pentru acesta direct în argumentul de tabel al formulei dvs. VLOOKUP.

Pentru a crea un interval denumit, pur și simplu selectați celulele și introduceți orice nume în caseta Nume din stânga panoului Formulă.

Funcția VLOOKUP în Excel - Denumirea unui interval

Acum puteți scrie următoarea formulă de CĂUTARE V pentru a obține prețul produsului 1:

CĂUTARE V ("Produs 1";Produse;2)

Funcția CĂUTARE V în Excel - Exemplu de funcție CĂUTARE V cu un nume de interval

Majoritatea numelor de intervale din Excel se aplică întregului registru de lucru, astfel încât nu trebuie să specificați un nume de foaie de lucru, chiar dacă intervalul dvs. de căutare se află pe o altă foaie de lucru. Astfel de formule sunt mult mai de înțeles. De asemenea, utilizarea intervalelor denumite poate fi o alternativă bună la celule. Deoarece intervalul numit nu se modifică atunci când formula este copiată în alte celule, puteți fi sigur că intervalul dvs. de căutare va rămâne întotdeauna corect.

Dacă ați convertit o serie de celule într-un tabel Excel cu funcții complete (fila Inserare --> Tabel), puteți selecta intervalul de căutare cu mouse-ul, iar Microsoft Excel va adăuga automat numele coloanelor sau numele tabelului la formulă. :

Funcția LOOKUP în Excel - Exemplu de funcție CUTARE V cu nume de tabel

Formula completă ar putea arăta cam așa:

CĂUTARE V ("Produs 1";Tabel6[[Produs]:[Preț]];2)

sau chiar =VLOOKUP(„Produsul 1”;Tabel6;2).

La fel ca intervalele denumite, numele coloanelor sunt constante, iar referințele la celule nu se vor schimba indiferent de locul în care este copiată formula CĂUTARE V.

Funcția VLOOKUP cu mai multe condiții

Să luăm în considerare exemplu de funcție VLOOKUP cu mai multe condiții. Avem următoarele date de intrare:

Funcția VLOOKUP în Excel - Tabel de date sursă

Să presupunem că trebuie să folosim Funcția VLOOKUP cu mai multe condiții. De exemplu, pentru a căuta prețul unui produs folosind două criterii: numele produsului și tipul acestuia.

1. Pentru a putea folosi Funcția VLOOKUP cu mai multe condiții este necesara introducerea unei coloane suplimentare la inceput care va stoca informatii cu denumirea si tipul produsului.

Deci, în foaia „Prețuri”, introduceți o coloană și în celula A2 introduceți următoarea formulă:

Folosind această formulă, obținem valoarea coloanelor „Produs” și „Tip”. Completați toate celulele.

Acum tabelul de căutare arată astfel:

Funcția VLOOKUP în Excel - Adăugarea unei coloane de ajutor

1. Acum, în celula C2 de pe foaia „Vânzări”, vom scrie următoarea formulă de CĂUTARE V:

CĂUTARE V(A2Prețuri! $A$1:$D$8,4,FALSE)

Completam celulele rămase și, ca rezultat, obținem prețuri pentru fiecare produs în funcție de tipul:

Funcția CĂUTARE V în Excel - Exemplu de CĂUTARE V cu mai multe conditii

Acum să ne uităm la asta Erori ale funcției CĂUTARE V.

De ce nu funcționează funcția VLOOKUP?

În această parte a articolului vom vedea de ce Funcția VLOOKUP nu funcționează si posibil Erori ale funcției CĂUTARE V.

Sper că chiar și acum pentru manechin, funcția VLOOKUP în Excel va fi clar.

CĂUTARE V este o funcție Excel care vă permite să căutați o anumită coloană folosind date dintr-o altă coloană. Funcția VLOOKUP din Excel este, de asemenea, utilizată pentru a transfera date dintr-un tabel în altul. Există trei condiții:

1. Mesele trebuie să fie amplasate într-una Caietul de lucru Excel.
2. Puteți căuta doar între date statice (nu formule).
3. Termenul de căutare trebuie să fie în prima coloană a datelor utilizate.

Formula VLOOKUP în Excel

Sintaxa VLOOKUPîn Excel rusificat arată astfel:

CĂUTARE V (criteriul de căutare; intervalul de date; numărul coloanei cu rezultat; condiția de căutare)

Argumentele necesare pentru a căuta rezultatul final sunt indicate în paranteze.

Criterii de căutare

Adresa celulei foii Excel în care sunt specificate datele de căutat în tabel.

Interval de date

Toate adresele printre care se efectuează căutarea. Prima coloană ar trebui să fie cea în care se află criteriul de căutare.

Numărul coloanei pentru valoarea totală

Numărul coloanei din care va fi luată valoarea dacă se găsește o potrivire.

Condiție de căutare

O valoare booleană (adevărat/1 sau fals/0) care specifică dacă se caută o potrivire aproximativă (1) sau o potrivire exactă (0).

VLOOKUP în Excel: exemple de funcții

Principiul de funcționare al funcției este simplu. Primul argument conține criteriile de căutare. De îndată ce se găsește o potrivire în tabel (al doilea argument), informațiile sunt preluate din coloana dorită (al treilea argument) a rândului găsit și introduse în celula cu formula.
O simplă utilizare a VLOOKUP este să căutați valori într-un tabel Excel. Are sens în volume mari de date.

Să găsim numărul de produse lansate efectiv după numele lunii.
Rezultatul va fi afișat în partea dreaptă a tabelului. In celula cu adresa H3 vom introduce valoarea dorita. În exemplu, numele lunii va fi indicat aici.
În celula H4 introducem funcția în sine. Acest lucru se poate face manual sau puteți utiliza un expert. Pentru a apela, plasați indicatorul pe celula H4 și faceți clic pe pictograma Fx de lângă bara de formule.


Se va deschide o fereastră de expert Funcții Excel. Trebuie să găsiți un VLOOKUP în el. Selectați „Lista alfabetică completă” din lista derulantă și începeți să tastați CĂUTARE V. Evidențiați funcția găsită și faceți clic pe OK.


Va apărea o fereastră VLOOKUP pentru tabele Excel.


Pentru a specifica primul argument (criterii), plasați cursorul pe primul rând și faceți clic pe celula H3. Adresa ei va apărea în rând. Pentru a selecta un interval, plasați cursorul pe a doua linie și începeți să selectați cu mouse-ul. Fereastra va fi minimizată la o linie. Acest lucru se face astfel încât fereastra să nu interfereze cu capacitatea ta de a vedea întreaga gamă și să nu interfereze cu acțiunile tale.


După ce ați terminat de selectat și eliberați butonul din stânga mouse, fereastra va reveni la starea sa normală, iar adresa intervalului va apărea pe a doua linie. Se calculează din celula din stânga sus până la celula din dreapta jos. Adresele lor sunt separate de operatorul „:” - toate adresele dintre prima și ultima sunt luate.


Mutați cursorul pe a treia linie și citiți din ce coloană vor fi preluate datele dacă se găsește o potrivire. În exemplul nostru este 3.


Lăsați ultimul rând necompletat. În mod implicit, valoarea va fi 1, să vedem ce valoare va scoate funcția noastră. Faceți clic pe OK.


Rezultatul este descurajator. „N/A” înseamnă date nevalide pentru funcție. Nu am specificat o valoare în celula H3, iar funcția caută o valoare goală.


Introduceți numele lunii și valoarea se va schimba.


Numai că nu corespunde realității, deoarece numărul real real de produse produse în ianuarie este 2000.
Acesta este efectul argumentului Condiția de căutare. Să-l schimbăm la 0. Pentru a face acest lucru, plasați indicatorul pe celula cu formula și apăsați din nou Fx. În fereastra care se deschide, introduceți „0”. ultima linie.


Faceți clic pe „OK”. După cum puteți vedea, rezultatul s-a schimbat.


Pentru a verifica a doua condiție de la începutul articolului nostru (funcția nu caută printre formule), să schimbăm condițiile pentru funcție. Să mărim intervalul și să încercăm să obținem o valoare dintr-o coloană cu valori calculate. Introduceți valorile ca în captura de ecran.


Faceți clic pe OK. După cum puteți vedea, rezultatul căutării s-a dovedit a fi 0, deși valoarea din tabel este de 85%.


VLOOKUP în Excel doar „înțelege”. valori fixe.

Compararea datelor din două tabele Excel

VLOOKUP în Excel poate fi folosit pentru a compara datele din două tabele. De exemplu, să presupunem că avem două foi cu date despre producția de produse din două ateliere. Ne putem potrivi eliberare reală pentru amândoi. Permiteți-ne să vă reamintim că pentru a comuta între foi, utilizați comenzile rapide din partea de jos a ferestrei.

Pe două foi avem tabele identice cu date diferite.

După cum puteți vedea, planul lor de lansare este același, dar cel real este diferit. Comutați și comparați linie cu linie chiar și pentru volume mici datele sunt foarte incomode. Pe a treia foaie, creați un tabel cu trei coloane.

În celula B2 intrăm în funcția VLOOKUP. Ca prim argument, vom indica celula cu luna pe foaia curentă și vom selecta intervalul din foaia „Workshop1”. Pentru a preveni deplasarea intervalului la copiere, apăsați F4 după selectarea intervalului. Acest lucru va face legătura absolută.


Extindeți formula pentru a acoperi întreaga coloană.

Introduceți formula în același mod în coloana următoare, selectați doar intervalul din foaia „Workshop2”.


După copiere, veți primi un raport de sinteză din două foi.

Înlocuirea datelor dintr-un tabel Excel în altul

Această acțiune este efectuată în același mod. Pentru exemplul nostru, nu trebuie să creați masa separata, dar pur și simplu introduceți funcția într-o coloană din oricare dintre tabele. Să o arătăm folosind primul exemplu. Plasați indicatorul în ultima coloană.


Și în celula G3 plasați funcția CĂUTARE V. Luăm din nou gama de pe foaia alăturată.


Ca rezultat, coloana celui de-al doilea tabel va fi copiată în primul.


Acestea sunt toate informațiile despre invizibil, dar functie utila VLOOKUP în Excel pentru manechini. Sperăm că vă va ajuta în rezolvarea problemelor.

O zi bună!