Sintaxa funcției vlookup în Excel. Informații de bază despre index și căutare poz. Folosim mai multe VLOOKUPS într-o singură formulă

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în Excel este folosit și pentru a transfera date dintr-un tabel în altul. Există trei condiții:

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

Formula VLOOKUP în Excel

Sintaxa CĂUTARE Vî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.

Criteriu de cautare

Adresa celulei Foaie Excel, care specifică 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.
Simplu aplicarea VPR– 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 stanga 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 și 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 amandoi. 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 CĂUTARE V. 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ă.

În mod similar, introduceți formula în coloana următoare, selectați doar intervalul de pe 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 VLOOKUP. 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ă!

Mulți oameni îl cunosc și îl folosesc des. Dar, în același timp, are două dezavantaje semnificative, de exemplu, cum să faci „CĂUTARE VL stânga”. Eu folosesc VLOOKUP doar dacă trebuie să fac ceva rapid. În fișierele pentru utilizare „obișnuită”, fac constructele INDEX și SEARCH. Cum este mai bine?

Când adăugați o coloană la un tabel de date, nu trebuie să modificați numărul coloanei în formula în sine (ca într-o CĂUTARE V). Coloana se va muta automat
Puteți face o CĂUTARE V în sens invers, de exemplu. faceți o alegere de la masă de la dreapta la stânga. Într-o CĂUTARE V, prima coloană ar trebui să poată fi întotdeauna căutată.

Cum să faci toate acestea, citește mai jos :)

Voi folosi un exemplu de la. Pentru a înțelege mai bine aplicarea acestui design în comparație cu VLOOKUP:

Cât de ușor este să completezi dacă vezi formula pentru prima dată?

Mai întâi, decideți unde este tabelul inițial și unde să primiți datele! Pas cu pas ce să completezi.

Introduceți formula în zona dorită a tabelului
În loc de $G:$G, pune acele celule în care ar trebui găsită valoarea și, în consecință, ar trebui să apară ca rezultat. Căutăm un nume de familie, așa că căutați coloana cu nume de familie în tabelul inițial.
Înlocuiți $J:$J cu, în funcție de ce valori ar trebui returnate în celulă. Avem nevoie de Nume de familie în funcție de vehicul - introduceți acele vehicule lângă care ar trebui să apară valorile.
În loc de $H:$H, completați coloana cu care trebuie să găsiți valoarea corespunzătoare. Acestea. Căutăm Nume după Vehicul, ceea ce înseamnă că inserăm o coloană cu Vehicul în tabelul original.

Dacă vă întrebați pentru ce sunt semnele $ din formulă, citiți

INDEX și CĂUTARE. Care sunt aceste funcții?

INDEX și MATCH sunt foarte trăsături puternice, care în combinație cu altele dau rezultate excelente.

INDEX(matrice; număr_rând; număr_coloană)

Returnează valoarea de la intersecția unui rând specificat și a unei coloane dintr-un interval specificat. Acestea. inițial funcționează cu tablouri bidimensionale.

Selectând matricea de date J1:K4 și setând numerele rândurilor și coloanelor la două, am obținut valoarea corespunzătoare.

Privind formula originală

INDEX($G:$G,PORITARE($J:$J,$H:$H,0),1)

Vom vedea că în loc de al doilea argument (numărul liniei) avem formula MATCH. Ce caută ea aici?

MATCH este o căutare după valoare. Căutări de funcții valoarea stabilităîntr-un rând sau coloană și returnează numărul său ordinal (de la începutul intervalului). Acestea. în al doilea argument al funcției INDEX găsim numărul vehiculului de care avem nevoie, obținem numărul acestuia, de exemplu 2.

Și deja în tabloul unidimensional $G:$G găsim o celulă cu numărul rândului = 2. Aceasta va funcționa pentru fiecare celulă din coloana J.

Deci, se pare că nu este un design foarte complicat, dar așa cum am scris mai sus, este foarte eficient. Deoarece nu trebuie să modificați în mod constant numărul valorii dorite, ca în CĂUTARE V, și puteți căuta atât la dreapta, cât și la stânga :)

Scrieți comentarii dacă aveți întrebări.

Ca întotdeauna!

Distribuie articolul nostru pe rețelele tale de socializare:

Știm cu toții cât de util este. Probabil jumătate din toate acțiunile din Excel sunt efectuate folosindu-l. in orice caz această funcție are o serie de limitări. De exemplu, o CĂUTARE V se uită numai la coloana din stânga dintr-un tabel sau caută doar o condiție. Dar dacă trebuie să returnăm o valoare care se potrivește cu două condiții. În acest caz, va trebui să recurgem la câteva trucuri. Despre aceste trucuri vom vorbi în articolul de astăzi.

Deci, ne vom uita la patru opțiuni pentru a crea o funcție de substituție cu două condiții:

Folosind funcția SUMPRODUCT

Ei bine, vom începe cu cel mai simplu lucru.

Folosind o coloană suplimentară

În cele mai multe cazuri probleme complexe devin mai ușor și mai ușor de gestionat atunci când sunt împărțite în bucăți mici. Același lucru este valabil și atunci când construiți formule în Excel.

Să ne uităm la un exemplu clasic. Avem un tabel cu vânzări pe lună și oraș. Și trebuie să determinăm valoarea vânzărilor corespunzătoare a două condiții: luna - februarie și orașul - Samara.

Folosind funcția VLOOKUP în aspect clasic nu ne va ajuta, deoarece va putea returna o valoare care se potrivește doar cu o singură condiție. O coloană suplimentară ne va ajuta să ieșim din această situație, în care vom combina valorile coloanelor Lună și Oraș. Pentru a face acest lucru, scrieți formula =B2&C2 în celula A2 și extindeți această formulă în celula A13. Acum putem folosi valorile coloanei A pentru a returna valoarea necesară. Scriem formula în celula G3:

CĂUTARE V(G1A2:D13;4;0)

Această formulă combină cele două condiții ale celulelor G1 și G2 într-un singur rând și o privește în coloana A. După conditie necesara a fost găsită, formula returnează valoarea din a patra coloană a tabelului A1:D13, adică. coloană Vânzări.

Folosind funcția SELECT pentru a crea un nou tabel de căutare

Dacă dintr-un motiv oarecare folosirea unei coloane suplimentare nu este o opțiune pentru noi, putem folosi .

Utilizarea funcției SELECT implică crearea masa noua pentru a vedea unde sunt valorile coloanei LunăȘi Oraș deja comasate. Formula noastră va arăta astfel:

CĂUTARE V(G1SELECT((1,2),B2:B13&C2:C13,D2:D13),2,0)

Principalul punct al acestei formule este partea CHOICE((1;2);B2:B13&C2:C13;D2:D13), care face două lucruri:

Combină valorile coloanei LunăȘi Orașîntr-o singură matrice: JanMoscow, FebMoscow...
Combină două matrice într-un tabel cu două coloane.

Rezultatul acestei funcții va fi un tabel care arată astfel:

Acum formula a devenit mai clară.

IMPORTANT: Deoarece am folosit o formulă matrice, când ați terminat de introdus formula, apăsați Ctrl+Shift+Enter pentru a informa programul intențiile noastre. După apăsarea acestei combinații de taste, programul va instala automat bretele la începutul și la sfârșitul formulei.

Folosind funcțiile INDEX și MATCH

A treia metodă, pe care o vom lua în considerare, implică și utilizarea unei formule matrice și folosește funcțiile INDEX și SEARCH.

Formula va arăta astfel.

INDEX(D2:D13,POTRIVIRE(1,(B2:B13=G1)*(C2:C13=G2),0))

Să ne uităm la ce face fiecare parte a acestei formule.

Mai întâi, luați în considerare funcția MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). ÎN în acest caz, compară secvențial valoarea celulei G1 cu fiecare valoare a celulelor din intervalul B2:B13 și returnează TRUE dacă valorile se potrivesc și FALSE dacă nu. Aceeași comparație se face cu valoarea celulei G2 și intervalul C2:C13. În continuare comparăm ambele matrice constând din TRUE și FALSE. Combinația TRUE * TRUE ne dă rezultatul 1 (TRUE). Să ne uităm la imaginea de mai jos, care va ajuta la explicarea mai clară a principiului de funcționare.

Acum putem spune unde se află șirul care îndeplinește ambele condiții. Funcția MATCH găsește poziția lui 1 în tabloul rezultat și returnează 6 deoarece 1 apare în al șaselea rând. În continuare, funcția INDEX returnează valoarea celui de-al șaselea rând al intervalului D2:D13.

Folosind SUMPRODUCT

Una dintre cele mai puternice formule Excel. Am chiar și un articol separat dedicat acestei formule. Al patrulea mod de a folosi mai multe condiții este să scriem o formulă cu funcția SUMPRODUCT. Și va arăta așa:

SUMA PRODUS((B2:B13=G1)*(C2:C13=G2);D2:D13)

Principiul de funcționare al acestei formule este similar cu principiul de funcționare al abordării anterioare. Creată masă virtuală, care compară valorile celulelor G1 și G2 cu intervalele B2:B13 și, respectiv, C2:C13. Apoi, ambele matrice sunt comparate și se obține o matrice de unu și zero, unde unul este atribuit rândului în care se potrivesc ambele condiții. Apoi, această matrice virtuală este înmulțită cu intervalul D2:D13. Deoarece matricea noastră virtuală va avea doar un 1 în al șaselea rând, formula va returna rezultatul 189.

Această funcție nu va funcționa dacă există valorile textului.

Pentru a înțelege cum funcționează această formulă, recomand citirea articolului despre functia SUMPROIZ.

REZULTAT

Deci ce metodă ar trebui să folosești? Deși toate funcționează fiabil, prefer prima metodă. În a lui Munca zilnica, prefer să lucrez cu fișiere ușor de înțeles și modificabile. Ambele cerințe îndeplinesc condițiile primei abordări.

Pentru o mai bună înțelegere a formulelor, le puteți privi în articolul de astăzi.

Lucrul cu un tabel rezumat implică tragerea de valori din alte tabele în el. Dacă există multe mese, va dura transferul manual o cantitate mare timp, iar dacă datele sunt actualizate în mod constant, aceasta va fi o sarcină Sisyphean. Din fericire, există o funcție VLOOKUP care oferă posibilitatea de a prelua automat date. sa luam in considerare exemple concrete funcţionarea acestei funcţii.

Numele funcției VLOOKUP înseamnă „funcție de vizualizare verticală”. În engleză numele său este VLOOKUP. Această funcție caută date în coloana din stânga a intervalului examinat și apoi returnează valoarea rezultată în celula specificată. Mai simplu spus, VLOOKUP vă permite să rearanjați valorile de la o celulă dintr-un tabel la altul. Să aflăm cum să folosiți funcția CĂUTARE V în Excel.

Exemplu de utilizare a VLOOKUP

Să aruncăm o privire la modul în care funcționează funcția CĂUTARE V, folosind un exemplu specific.

Avem două mese. Primul dintre ele este un tabel de cumpărare în care sunt plasate denumirile produselor alimentare. În coloana următoare după nume este valoarea cantității de produs care trebuie achiziționat. Urmează prețul. Și în ultima coloană - costul total al achiziționării unui anumit produs, care este calculat folosind formula deja introdusă în celulă pentru înmulțirea cantității cu prețul. Dar va trebui doar să creștem prețul folosind funcția VLOOKUP din tabelul alăturat, care este o listă de prețuri.

După cum puteți vedea, prețul cartofilor a fost adăugat la tabelul din lista de prețuri. Pentru a nu trece printr-o procedură atât de complexă cu alte nume de produse, pur și simplu stăm în colțul din dreapta jos al celulei umplute, astfel încât să apară o cruce. Desenăm această cruce chiar în partea de jos a tabelului.

Astfel, am extras toate datele necesare de la un tabel la altul utilizând funcția VLOOKUP.

După cum puteți vedea, funcția VLOOKUP nu este atât de complicată pe cât pare la prima vedere. Înțelegerea utilizării sale nu este foarte dificilă, dar stăpânirea acestui instrument vă va economisi mult timp atunci când lucrați cu tabele.

Formatare condiționată (5)
Liste și intervale (5)
Macrocomenzi (proceduri VBA) (63)
Diverse (39)
Erori și erori Excel (3)

Cum să găsiți o valoare într-un alt tabel sau puterea CĂUTARE V

De fapt, în acest articol vreau să vorbesc despre posibilități nu numai Funcțiile CĂUTARE V, dar vreau și să ating CĂUTARE, ca o funcție foarte legată de VPR. Fiecare dintre aceste funcții are atât avantaje, cât și dezavantaje. Pe scurt, VLOOKUP caută o anumită valoare pe care o specificăm printre numeroasele valori situate într-o coloană. Poate cel mai adesea nevoia de CĂUTARE VL apare atunci când trebuie să comparați date, să găsiți date într-un alt tabel, să adăugați date dintr-un tabel în altul, pe baza unui criteriu etc.
Pentru a înțelege puțin mai bine principiul Lucru VPR mai bine sa incepi cu ceva exemplu practic. Există un tabel ca acesta:
Fig.1

iar din primul tabel trebuie înlocuit în a doua dată pentru fiecare nume de familie. Pentru trei înregistrări, aceasta nu este o problemă și a face acest lucru manual este evident. Dar, în viața reală, acestea sunt tabele cu mii de înregistrări, iar căutarea manuală cu înlocuirea datelor poate dura mai mult de o oră. Plus încă câteva muscă în unguent: nu numai că numele sunt localizate complet în în ordine diferităÎn ambele tabele, numărul de înregistrări din tabele este diferit, iar tabelele sunt amplasate pe foi/cărți diferite. Cred că v-am convins că înlocuirea manuală nu este deloc o opțiune. Dar CĂUTARE V va fi indispensabil aici. În acest caz, practic nu trebuie făcut nimic - doar scrieți în prima celulă a coloanei C a celui de-al doilea tabel (unde trebuie să înlocuiți datele din primul tabel) aceasta formula:
=CĂUTAREV($A2 ;Foaie1!$A$2:$C$4 ;3,0)
Puteți scrie o formulă fie direct într-o celulă, fie folosind managerul de funcții, selectând în categorie Legături și matrice VPR și indicând separat criteriile necesare. Acum copiam( Ctrl+C) celula cu formula, selectați toate celulele din coloana C până la sfârșitul datelor și introduceți ( Ctrl+V).

În primul rând, principiul de bază al funcționării: VLOOKUP caută în prima coloană a argumentului Tabel valoarea specificată de argument Search_value . Când este găsită valoarea dorită, funcția returnează valoarea opusă valorii găsite, dar din coloana specificată de argument Număr_coloană . Ne vom ocupa de vizualizarea time-lapse puțin mai târziu. O CĂUTARE V poate returna doar o singură valoare - prima care corespunde criteriului. Dacă valoarea pe care o căutați nu este găsită (nu in tabel), atunci rezultatul funcției va fi #N / A . Nu trebuie să vă fie frică de acest lucru - este chiar util. Veți ști exact care înregistrări lipsesc și astfel puteți compara două tabele unul cu celălalt. Uneori se dovedește că vedeți: există date în ambele tabele, dar CĂUTARE V produce #N/A. Aceasta înseamnă că datele din tabelele dvs. nu sunt identice. Unele dintre ele au spații foarte discrete (de obicei înainte sau după valoare), sau caracterele chirilice sunt amestecate cu caractere latine. De asemenea #N / A va fi dacă criteriile sunt numere și în tabelul dorit sunt scrise ca text (de obicei în stânga colțul de sus o astfel de celulă apare un triunghi verde), iar în final - ca numere. Sau vice versa.

Descriere Argumente VLOOKUP
$A2 - argument Search_value(să-i spunem Criteriu a fi scurt). Aceasta este ceea ce căutăm. Acestea. pentru primul record al celui de-al doilea tabel va fi Petrov S.A. Aici puteți specifica fie textul criteriului direct (în acest caz ar trebui să fie între ghilimele - =VLOOKUP("Petrov S.A" ;Sheet1!$A$2:$C$4;3;0) sau un link către o celulă cu acest text (ca în exemplul de funcție). Există o mică nuanță: puteți folosi și caractere wildcard: „*” și „?”. Acest lucru este foarte convenabil dacă trebuie să găsiți valori doar pentru o parte dintr-un șir. De exemplu, nu puteți introduce „Petrov S.A” în întregime, ci introduceți doar numele de familie și asteriscul - „Petrov*”. Apoi va fi afișată orice intrare care începe cu „Petrov”. Dacă trebuie să găsiți o înregistrare în care numele de familie „Petrov” să apară oriunde pe linie, atunci o puteți specifica astfel: „*Petrov*”. Dacă doriți să găsiți numele de familie Petrov și nu contează ce inițiale vor avea prenumele și patronimul (dacă numele complet este scris Ivanov I.I.), atunci acest aspect este potrivit: „Ivanov?.?”. . Adesea este necesar să indicați valoarea sa pentru fiecare rând (în coloana A Nume și trebuie să le găsiți pe toate). În acest caz, sunt indicate întotdeauna referiri la celulele coloanei A. De exemplu, în celula A1 se scrie: Ivanov. De asemenea, se știe că Ivanov se află într-un alt tabel, dar după nume se pot scrie atât prenumele, cât și numele de mijloc (sau altceva). Dar trebuie doar să găsim șirul care începe cu numele de familie. Apoi trebuie să-l scrieți după cum urmează: A1 &"*" . Această intrare va fi echivalentă cu „Ivanov*”. A1 este scris Ivanov, ampersand (&) este folosit pentru a combina două valori de text într-un șir. Un asterisc este între ghilimele (cum ar trebui să fie textul dintr-o formulă). Astfel obținem:
A1&"*" =>
"Ivanov"&"*" =>
„Ivanov*”
Foarte convenabil dacă există o mulțime de valori de căutat.
Dacă trebuie să determinați dacă există un cuvânt undeva într-o linie, atunci puneți asteriscuri pe ambele părți: "*"& A1 &"*"

Sheet1!$A$2:$C$4 - argument Masa. Specifică intervalul de celule. Numai intervalul trebuie să conțină date de la prima celulă de date până la ultima. Acesta nu trebuie să fie intervalul afișat în exemplu. Dacă există 100 de rânduri, atunci Sheet1!$A$2:$C$100. Este important să rețineți trei lucruri: în primul rând, aceasta Masa trebuie să începem întotdeauna cu coloana în care căutăm Criteriu . Si nimic altceva. În caz contrar, nu se va găsi nimic sau rezultatul nu va fi cel așteptat. Al doilea: argument Masa trebuie sa fie "reparat" . Ce înseamnă. Vedeți semnele dolarului? Aceasta este consolidarea (mai precis, se numește referință absolută pe interval). Cum se face. Evidențiați textul linkului (doar un interval - un criteriu)și apăsați F4 până când vezi că dolarii apar atât înaintea numelui coloanei, cât și a numărului rândului. Dacă acest lucru nu se face, atunci când copiați formula, argumentul Tabel va „muta afară” și rezultatul va fi din nou incorect. Și în sfârșit, tabelul trebuie să conțină coloane de la prima (în care căutăm) până la ultima (din care trebuie să returnăm valori). În exemplu Sheet1!$A$2:$C$4- aceasta înseamnă că nu va fi posibilă returnarea valorii din coloana D(4), deoarece tabelul are doar trei coloane.

3 - Număr_coloană. Aici indicăm pur și simplu numărul coloanei din argument Masa, valorile din care trebuie să le înlocuim ca rezultat. În exemplu, aceasta este data de acceptare - i.e. coloana numărul 3. Dacă avem nevoie de un departament, atunci am indica 2, iar dacă ar trebui doar să comparăm dacă există nume dintr-un tabel în altul, atunci am putea specifica 1. Important: argument Număr_coloană nu trebuie să depășească numărul de coloane din argument Masa . În caz contrar, formula va avea ca rezultat o eroare #LEGĂTURĂ!. De exemplu, dacă este specificat intervalul $B$2:$C$4 și trebuie să returnați date din coloana C, atunci este corect să specificați 2. Deoarece argument Masa($B$2:$C$4) conține doar două coloane - B și C. Dacă încercați să specificați numărul coloanei 3 (așa cum apare pe foaie), veți primi o eroare #LEGĂTURĂ!, deoarece pur și simplu nu există a treia coloană în intervalul specificat.

Sfat practic: dacă argumentul Tabel are prea multe coloane și trebuie să returnați rezultatul din ultima coloană, atunci nu este deloc necesar să calculați numărul acestora. Puteți să-l specificați astfel: =VLOOKUP($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM(Sheet1! $A$2:$C$4);0) . Apropo, în acest caz Sheet1! poate fi, de asemenea, eliminat ca inutil: =CĂUTARE V($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM($A$2:$C$4);0) .

0 - Time-lapse_view- un argument foarte interesant. Poate fi adevărat sau fals. Apare imediat întrebarea: de ce este 0 în formula mea? Este foarte simplu - Excel în formule poate trata 0 ca FALS și 1 ca ADEVARAT. Dacă specificați în VLOOKUP acest parametru egal cu 0 sau FALSE, atunci se va căuta o potrivire exactă Criteriul dat. Acest lucru nu are nimic de-a face cu metacaracterele ("*" și "?"). Dacă folosiți 1 sau TRUE (sau nu specificați deloc ultimul argument, deoarece implicit este TRUE), atunci... Este o poveste foarte lungă. Pe scurt - VLOOKUP va căuta cea mai asemănătoare valoare care se potrivește Criteriu . Uneori foarte util. Cu toate acestea, dacă utilizați acest parametru, atunci este necesar ca lista din argumentul Tabel să fie sortată în ordine crescătoare. Vă rugăm să rețineți că sortarea este necesară numai dacă argumentul Interval_lookup este TRUE sau 1. Dacă este 0 sau FALSE, sortarea nu este necesară.

Mulți au observat probabil că în imagine am departamentele pentru nume complete amestecate. Aceasta nu este o eroare de înregistrare. Exemplul atașat articolului arată cum le puteți înlocui atât, cât și datele cu o singură formulă, fără a schimba manual argumentul Număr_coloană. Mi s-a părut că un astfel de exemplu ar putea fi destul de util.

Cum să evitați eroarea #N/A (#N/A) în VLOOKUP?
Mai mult Problemă comună- Mulți oameni nu doresc să vadă #N/A ca rezultat dacă nu se găsește nicio potrivire. Acest lucru este ușor de deplasat:
=DACĂ(Sfârșit(CĂUTAREV($A2,Foaia1! $A$2:$C$4,3,0));"";CĂUTAREV($A2,Foaia1! $A$2:$C$4,3,0)))
Acum, dacă VLOOKUP nu găsește o potrivire, celula va fi goală.
Și utilizatorilor versiuni Excel 2007 și mai sus, puteți utiliza IFERROR:
=DACĂ EROARE(CĂUTAREV($A2,Sheet1! $A$2:$C$4,3,0);"")

MECIUL Promis

Această funcție caută valoarea specificat de parametru Search_valueîn argumentare View_array. Și rezultatul funcției este numărul de poziție al valorii găsite în View_array. Este numărul poziției, nu valoarea în sine. În principiu, nu o voi descrie în același detaliu, deoarece punctele principale sunt exact aceleași. Dacă am dori să o aplicăm în tabelul de mai sus, ar fi așa:
=POTRIV ($A2, Sheet1! $A$2:$A$4,0)
$A2 - Search_value. Aici totul este exact la fel ca cu VLOOKUP. Caracterele wildcard sunt, de asemenea, permise și în exact același design.

Foaia 1! $A$2:$A$4 - Matricea care trebuie vizualizată. Principala diferență față de VLOOKUP este că puteți specifica o matrice cu o singură coloană. Aceasta ar trebui să fie coloana în care vom căuta Search_value . Dacă încercați să specificați mai multe coloane, funcția va returna o eroare.

tip_potrivire(0) - la fel ca in VPR Time-lapse_view . Cu aceleasi caracteristici. Diferă doar prin capacitatea de a căuta cel mai mic de cel dorit sau cel mai mare. Dar nu mă voi opri asupra acestui lucru în acest articol.

Am rezolvat elementele de bază. Dar trebuie să returnăm nu numărul poziției, ci valoarea în sine. Așa că MATCH în formă pură nu ne convine. Cel puțin unul, de unul singur. Dar dacă este folosit împreună cu funcția INDEX, atunci de asta avem nevoie și chiar mai mult.
=INDEX(Foaia1! $A$2:$C$4 ;POTRIVIRE($A2 ;Foaia1! $A$2:$A$4 ;0);2)
Această formulă va returna același rezultat ca și VLOOKUP.

INDEX Argumente ale funcției
Foaia 1! $A$2:$C$4 - Matrice. Ca acest argument specificăm intervalul din care dorim să obținem valorile. Pot exista o coloană sau mai multe. Dacă există o singură coloană, atunci ultimul argument al funcției nu trebuie specificat. Apropo, este posibil ca acest argument să nu coincidă deloc cu cel pe care îl specificăm în argumentul Lookup_array al funcției MATCH.

Urmează Row_Number și Column_Number. Ca Row_Number înlocuim MATCH, care ne returnează numărul de poziție din tablou. Pe asta se construiește totul. INDEX returnează valoarea din Array care se află în rândul specificat (RowNumber) din Array și coloana specificată (ColumnNumber) dacă există mai multe coloane. Este important de știut că în această combinație, numărul de rânduri din argumentul Array al funcției INDEX și numărul de rânduri din argumentul Lookup_array al funcției MATCH trebuie să se potrivească. Și începe de la aceeași linie. Acest lucru este în cazuri obișnuite, cu excepția cazului în care urmăriți alte obiective.
Ca și în cazul CĂUTARE V, INDEX returnează #N/A dacă nu este găsită valoarea dorită. Și puteți ocoli astfel de erori în același mod:
Pentru toate versiunile de Excel (inclusiv 2003 și versiunile anterioare):
=IF(FÂRȘIT(POTRIVIRE($A2,Foaie1! $A2,$A$4,0));"";INDEX(Foaia1! $A$2:$C$4;POTIRE($A2,Foaia1! $A$2: $A$4 ;0);2))
Pentru versiunile 2007 și mai mari:
=DACA EROARE(INDEX(Foaie1! $A$2:$C$4,POTRIVIRE($A2,Foaia1!$A$2:$A$4,0),2);"")

Se lucrează cu criterii mai lungi de 255 de caractere
INDEX-SEARCH are, de asemenea, un avantaj în plus față de VLOOKUP. Ideea este că VLOOKUP nu poate căuta valori a cărei lungime a rândului conține mai mult de 255 de caractere. Acest lucru se întâmplă rar, dar se întâmplă. Puteți, desigur, să înșelați VLOOKUP și să reduceți criteriul:
=CĂUTAREV(PSTR($A2,1,255), PSTR(Foaie1!$A$2:$C$4,1,255),3,0)
dar aceasta este o formulă matrice. Și în plus, o astfel de formulă nu va reveni întotdeauna rezultatul dorit. Dacă primele 255 de caractere sunt identice cu primele 255 de caractere din tabel, iar apoi caracterele sunt diferite, formula nu va mai vedea acest lucru. Și formula returnează exclusiv valori text, ceea ce nu este foarte convenabil în cazurile în care trebuie returnate numere.

Prin urmare, este mai bine să utilizați această formulă complicată:
=INDEX(Foaia1!$A$2:$C$4,SUMAPRODUS(POTRIVIT(ADEVĂRAT,Foaia1!$A$2:$A$4 =$A2,0));2)
Aici am folosit aceleași intervale în formulele de lizibilitate, dar în exemplul de descărcare ele diferă de cele indicate aici.
Formula în sine este construită pe capacitatea funcției SUMPRODUCT de a se transforma în calcule masive ale unor funcții din cadrul acesteia. În acest caz, MATCH caută poziția rândului în care criteriul este egal cu valoarea din rând. Caracterele wildcard nu mai pot fi folosite aici.

În exemplul atașat articolului veți găsi exemple de utilizare a tuturor cazurilor descrise și un exemplu de ce INDEX și MATCH sunt uneori preferabile față de VLOOKUP.

Descărcați exemplu

(26,0 KiB, 13.776 descărcări)

A ajutat articolul? Distribuie link-ul prietenilor tăi! Lecții video

("Bara de jos":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"dreapta","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"dreapta","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2,":1500,":1500, textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; poziție:absolute; sus:0px; stânga:0px; lățime:100%; înălțime:100% ; culoare de fundal:#333333; opacitate:0,6; filtru:alpha(opacity=60);","titlecss":"display:block; poziție:relativă; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; culoare:#fff;","descriptioncss":"display:block; poziție:relativă; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; culoare:#fff; margin-top:8px;","buttoncss":"display:block; poziție:relativă; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))