Funcția VLOOKUP cu câteva exemple de bază și avansate în Excel

În Excel, funcția VLOOKUP este o funcție puternică pentru majoritatea utilizatorilor Excel, care este utilizată pentru a căuta o valoare în partea din stânga a intervalului de date și pentru a returna o valoare potrivită în același rând dintr-o coloană pe care ați specificat-o ca în imaginea de mai jos. . Acest tutorial vorbește despre cum să utilizați funcția VLOOKUP cu câteva exemple de bază și avansate în Excel.

Introducerea funcției VLOOKUP - Sintaxă și argumente

Sintaxa funcției VLOOKUP:

argumente:

Căutare_valor: Valoarea pe care doriți să o căutați. Trebuie să fie în prima coloană a intervalului table_array.

Table_array: Intervalul de date sau tabelul în care se află coloana valorii de căutare și coloana valorii rezultatului.

Col_index_num: Numărul de coloane din care va fi returnată valoarea potrivită. Începe cu 1 din coloana din stânga din matricea de tabele.

Range_lookup: O valoare logică care determină dacă această funcție VLOOKUP va returna o potrivire exactă sau o potrivire aproximativă.

• Meci aproximativ - 1 / ADEVĂRAT: Dacă nu se găsește o potrivire exactă, formula caută cea mai apropiată potrivire - cea mai mare valoare care este mai mică decât valoarea de căutare. În acest caz, ar trebui să sortați coloana de căutare în ordine crescătoare.
  = VLOOKUP (lookup_value, table_array, col_index, TRUE)
  = VLOOKUP (lookup_value, table_array, col_index, 1)
• Potrivire perfecta - 0 / FALS: Aceasta este utilizată pentru a căuta o valoare exact egală cu valoarea de căutare. Dacă nu se găsește o potrivire exactă, valoarea de eroare # N / A va fi returnată.
  = VLOOKUP (lookup_value, table_array, col_index, FALSE)
  = VLOOKUP (lookup_value, table_array, col_index, 0)

note:

• 1. Funcția Vlookup caută doar valoare de la stânga la dreapta.
• 2. Dacă există mai multe valori de potrivire bazate pe valoarea de căutare, numai prima potrivită va fi returnată utilizând funcția Vlookup.
• 3. Va returna valoarea de eroare # N / A dacă valoarea de căutare nu poate fi găsită.

Exemple de bază VLOOKUP

1. Faceți un meci exact Vlookup și un meci aproximativ Vlookup

Faceți o potrivire exactă Vlookup în Excel

În mod normal, dacă căutați o potrivire exactă cu funcția Vlookup, trebuie doar să utilizați FALSE în ultimul argument.

De exemplu, pentru a obține scorurile matematice corespunzătoare pe baza numerelor de identificare specifice, vă rugăm să procedați astfel:

1. Aplicați formula de mai jos într-o celulă goală unde doriți să obțineți rezultatul:

2. Și apoi, trageți mânerul de umplere în jos în celulele pe care doriți să completați această formulă și veți obține rezultatele după cum aveți nevoie. Vedeți captura de ecran:

note:

• 1. În formula de mai sus, F2 este valoarea pe care doriți să o întoarceți cu valoarea potrivită, A2: D7 este matricea de tabel, numărul 3 este numărul coloanei din care este returnată valoarea dvs. potrivită și FALS se referă la potrivirea exactă.
• 2. Dacă valoarea criteriilor dvs. nu se găsește în intervalul de date, va fi afișată o valoare de eroare # N / A.

Faceți o potrivire aproximativă Vlookup în Excel

Potrivirea aproximativă este utilă pentru căutarea valorilor între intervalele de date. Dacă nu se găsește potrivirea exactă, Vlookup aproximativ va returna cea mai mare valoare mai mică decât valoarea de căutare.

De exemplu, dacă aveți următoarele date de gamă, comenzile specificate nu sunt în coloana Comenzi, cum să obțineți cea mai apropiată Reducere în coloana B?

1. Introduceți următoarea formulă într-o celulă în care doriți să puneți rezultatul:

2. Apoi, trageți mânerul de umplere în jos în celule pentru a aplica această formulă și veți obține potrivirile aproximative pe baza valorilor date, vedeți captura de ecran:

note:

• 1. În formula de mai sus, D2 este valoarea pe care doriți să o returnați informațiile relative, A2: B9 este intervalul de date, numărul 2 indică numărul coloanei pentru care este returnată valoarea potrivită și codul TRUE se referă la potrivirea aproximativă.
• 2. Potrivirea aproximativă va returna cea mai mare valoare mai mică decât valoarea dvs. specifică de căutare.
• 3. Pentru a utiliza funcția Vlookup pentru a obține o valoare de potrivire aproximativă, trebuie să sortați coloana din stânga a intervalului de date în ordine crescătoare, altfel va rezulta un rezultat greșit.

2. Faceți un Vlookup sensibil la majuscule și minuscule în Excel

În mod implicit, funcția Vlookup efectuează o căutare insensibilă la majuscule, ceea ce înseamnă că tratează caracterele minuscule și majuscule ca identice. Uneori, poate fi necesar să faceți o căutare sensibilă la majuscule și minuscule în Excel, funcțiile Index, Match și Exact sau funcțiile Căutare și Exact vă pot face o favoare.

De exemplu, am următorul interval de date, care coloană ID conține șir de text cu majuscule sau minuscule, acum, vreau să returnez scorul corespunzător de matematică al numărului de identificare dat.

Formula 1: Folosirea funcțiilor EXACT, INDEX, MATCH

1. Vă rugăm să introduceți sau să copiați formula matricei de mai jos într-o celulă goală unde doriți să obțineți rezultatul:

2. Apoi, apăsați Ctrl + Shift + Enter tastele simultan pentru a obține primul rezultat, apoi selectați celula formulă, trageți mânerul de umplere în jos până la celulele pe care doriți să le completați, apoi veți obține rezultatele corecte de care aveți nevoie. Vedeți captura de ecran:

note:

• 1. În formula de mai sus, A2: A10 este coloana care conține valorile specifice în care doriți să căutați, F2 este valoarea de căutare, C2: C10 este coloana de unde va fi returnat rezultatul.
• 2. Dacă s-au găsit mai multe potriviri, această formulă va returna întotdeauna primul meci.

Formula 2: Utilizarea funcțiilor Căutare și Exact

1. Vă rugăm să aplicați formula de mai jos într-o celulă goală unde doriți să obțineți rezultatul:

2. Apoi, glisați mânerul de umplere în jos în celulele pe care doriți să le copiați această formulă și veți obține valorile potrivite cu majuscule și minuscule, după cum se arată în imaginea de mai jos:

note:

• 1. În formula de mai sus, A2: A10 este coloana care conține valorile specifice în care doriți să căutați, F2 este valoarea de căutare, C2: C10 este coloana de unde va fi returnat rezultatul.
• 2. Dacă s-au găsit mai multe potriviri, această formulă va returna întotdeauna ultima potrivire.

3. Valorile Vlookup de la dreapta la stânga în Excel

Funcția Vlookup caută întotdeauna o valoare în coloana din stânga a unui interval de date și returnează valoarea corespunzătoare dintr-o coloană în dreapta. Dacă doriți să faceți un Vlookup invers, ceea ce înseamnă să căutați o anumită valoare din dreapta și să returnați valoarea corespunzătoare în coloana din stânga, după cum se arată în imaginea de mai jos:

Faceți clic pentru a afla detaliile pas cu pas despre această sarcină ...

4. Căutați a doua, a n-a sau ultima valoare potrivită în Excel

În mod normal, dacă există mai multe valori de potrivire sunt găsite atunci când se utilizează funcția Vlookup, numai prima înregistrare potrivită va fi returnată. În această secțiune, voi vorbi despre cum să obțineți a doua, a n-a sau ultima valoare de potrivire cu funcția Vlookup.

Căutați și returnați a doua sau a n-a valoare potrivită

Să presupunem că aveți o listă de nume în coloana A, cursul de formare pe care l-au achiziționat în coloana B, iar acum căutați să găsiți al doilea sau al nouălea curs de formare cumpărat de clientul respectiv. Vedeți captura de ecran:

1. Pentru a obține a doua sau a n-o valoare de potrivire pe baza criteriilor date, vă rugăm să aplicați următoarea formulă matrice într-o celulă goală:

2. Apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține primul rezultat, apoi selectați celula formulă, trageți mânerul de umplere în jos în celulele pe care doriți să completați această formulă și toate cele două valori potrivite bazate pe numele date au fost afișate simultan, vedeți captura de ecran:

Notă:

• În această formulă, A2: A14 este intervalul cu toate valorile pentru căutare, B2: B14 este intervalul valorilor potrivite din care doriți să reveniți, E2 este valoarea de căutare și ultimul număr 2 indică a doua valoare potrivită pe care doriți să o obțineți, dacă doriți să returnați a treia valoare potrivită, trebuie doar să o schimbați la 3 după cum aveți nevoie.

Căutați și returnați ultima valoare potrivită

Dacă doriți să vizualizați și să returnați ultima valoare potrivită, așa cum se arată în imaginea de mai jos, aceasta Vizualizați și returnați ultima valoare de potrivire tutorialul vă poate ajuta să obțineți ultima valoare potrivită în detalii.

5. V căutați valori potrivite între două valori date sau date

Uneori, poate doriți să căutați valori între două valori sau date și să returnați rezultatele corespunzătoare, după cum se arată în imaginea de mai jos, în acest caz, puteți utiliza funcția CĂUTARE și un tabel sortat.

V căutați valori potrivite între două valori date sau date cu formula

1. În primul rând, tabelul dvs. original ar trebui să fie un interval de date sortat. Și apoi, copiați sau introduceți următoarea formulă într-o celulă goală:

2. Apoi, trageți mânerul de umplere pentru a umple această formulă în alte celule de care aveți nevoie, iar acum, veți obține toate înregistrările potrivite pe baza valorii date, vedeți captura de ecran:

note:

• 1. În formula de mai sus, A2: A6 este gama valorilor mai mici și B2: B6 este gama de numere mai mari din intervalul dvs. de date, E2 este valoarea dată pe care doriți să o obțineți valoarea corespunzătoare, C2: C6 este datele coloanei din care doriți să extrageți.
• 2. Această formulă poate fi, de asemenea, utilizată pentru extragerea valorilor potrivite între două date, după cum se arată în imaginea de mai jos:
  

Vlookup potrivirea valorilor între două valori date sau date cu o caracteristică utilă

Dacă sunteți dureros cu formula de mai sus, aici, voi introduce un instrument ușor - Kutools pentru Excel, Cu său CĂUTARE între două valori caracteristică, puteți returna elementul corespunzător pe baza valorii specifice sau a datei între două valori sau date fără a vă aminti nici o formulă.   Faceți clic pentru a descărca Kutools pentru Excel acum!

6. Utilizarea comodinelor pentru potriviri parțiale în funcția Vlookup

În Excel, metacaracterele pot fi utilizate în cadrul funcției Vlookup, ceea ce face să se realizeze o potrivire parțială a unei valori de căutare. De exemplu, puteți utiliza Vlookup pentru a returna valoarea potrivită dintr-un tabel pe baza unei părți a unei valori de căutare.

Presupunând că am o serie de date așa cum se arată în imaginea de mai jos, acum vreau să extrag scorul pe baza prenumelui (nu a numelui complet). Cum ar putea rezolva această sarcină în Excel?

1. Funcția normală Vlookup nu funcționează corect, trebuie să alăturați textul sau referința la celulă cu un wildcard, vă rugăm să copiați sau să introduceți următoarea formulă într-o celulă goală:

2. Apoi, trageți mânerul de umplere pentru a completa această formulă în alte celule de care aveți nevoie și toate scorurile potrivite au fost returnate așa cum se arată în imaginea de mai jos:

note:

• 1. În formula de mai sus, E2 și „*” este valoarea de căutare, valoarea în E2 si * wildcard („*” indică orice caracter sau orice caracter), A2: C11 este intervalul de căutare, numărul 3 coloana care conține valoarea de returnat.
• 2. Vlookup atunci când utilizați metacaractere, trebuie să setați modul de potrivire exact cu FALSE sau 0 pentru ultimul argument din funcția Vlookup.

7. Valorile Vlookup dintr-o altă foaie de lucru

De obicei, poate fi necesar să lucrați cu mai mult de o foaie de lucru, funcția Vlookup poate fi utilizată pentru a căuta date dintr-o altă foaie la fel ca pe o foaie de lucru.

De exemplu, aveți două foi de lucru, așa cum se arată în imaginea de mai jos, pentru a căuta și a returna datele corespunzătoare din foaia de lucru pe care ați specificat-o, vă rugăm să parcurgeți pașii următori:

1. Vă rugăm să introduceți sau să copiați formula de mai jos într-o celulă goală unde doriți să obțineți articolele potrivite:

2. Apoi, trageți mânerul de umplere în jos în celulele pe care doriți să le aplicați această formulă și veți obține rezultatele corespunzătoare după cum aveți nevoie, vedeți captura de ecran:

Notă: În formula de mai sus:

• A2 reprezintă valoarea de căutare;
• Fișa cu date este numele foii de lucru din care doriți să căutați date, (dacă numele foii conține spațiu sau caractere de punctuație, ar trebui să atașați ghilimele simple în jurul numelui foii, în caz contrar, puteți utiliza direct numele foii ca = VLOOKUP (A2, Fișă tehnică! $ A $ 2: $ C $ 15,3,0));
• A2: C15 este gama de date din Fișa tehnică în care căutăm date;
• numărul 3 este numărul coloanei care conține date potrivite de la care doriți să reveniți.

8. Valorile Vlookup dintr-un alt registru de lucru

Această secțiune va vorbi despre căutare și va returna valorile potrivite dintr-un registru de lucru diferit utilizând funcția Vlookup.

De exemplu, primul registru de lucru conține listele de produse și costuri, acum doriți să extrageți costul corespunzător din al doilea registru de lucru pe baza articolului produsului, așa cum este prezentat mai jos.

1. Pentru a prelua costul relativ dintr-un alt registru de lucru, mai întâi, deschideți ambele registre de lucru pe care doriți să le utilizați, apoi aplicați următoarea formulă într-o celulă în care doriți să puneți rezultatul:

2. Apoi, glisați și copiați această formulă în alte celule de care aveți nevoie, vedeți captura de ecran:

note:

• 1. În formula de mai sus:
  B2 reprezintă valoarea de căutare;
  [Listă de produse.xlsx] Sheet1 este numele registrului de lucru și al foii de lucru din care doriți să căutați date, (Referința la registrul de lucru este inclusă între paranteze drepte, iar întregul registru de lucru + foaia este inclus între ghilimele unice);
  A2: B6 este gama de date din foaia de lucru a altui registru de lucru în care căutăm date;
  numărul 2 este numărul coloanei care conține date potrivite de la care doriți să reveniți.
• 2. Dacă registrul de lucru de căutare este închis, calea completă a fișierului pentru registrul de lucru de căutare va fi afișată în formulă, după cum urmează:
  

9. Căutați și returnați textul gol sau specific în loc de 0 sau valoarea de eroare # N / A

În mod normal, atunci când aplicați funcția vlookup pentru a returna valoarea corespunzătoare, dacă celula potrivită este goală, aceasta va returna 0 și, dacă valoarea potrivită nu este găsită, veți primi o valoare de eroare # N / A, după cum se arată mai jos. În loc să afișați valoarea 0 sau # N / A cu celula goală sau altă valoare care vă place, aceasta Vlookup va returna valoarea goală sau specifică în loc de 0 sau N / A tutorialul vă poate face o favoare pas cu pas.

Exemple avansate de VLOOKUP

1. Căutare bidirecțională cu funcția Vlookup (Vlookup în rând și coloană)

Uneori, este posibil să aveți nevoie să efectuați o căutare bidimensională, ceea ce înseamnă că Vlookup este atât în ​​rând, cât și în coloană în același timp. Să presupunem, dacă aveți următorul interval de date și acum, poate fi necesar să obțineți valoarea pentru un anumit produs într-un trimestru specificat. Această secțiune va introduce câteva formule pentru a face față acestui job în Excel.

Formula 1: Utilizarea funcțiilor VLOOKUP și MATCH

În Excel, puteți utiliza o combinație de funcții VLOOKUP și MATCH pentru a efectua o căutare bidirecțională, vă rugăm să aplicați următoarea formulă într-o celulă goală, apoi apăsați Introduce cheie pentru a obține rezultatul.

Notă: În formula de mai sus:

• H1: valoarea de căutare din coloana pe care doriți să obțineți valoarea corespunzătoare bazată pe;
• A2: E6: intervalul de date, inclusiv antetele rândurilor;
• H2: valoarea de căutare din rândul pe care doriți să obțineți valoarea corespunzătoare pe baza;
• A1: E1: celulele anteturilor de coloană.

Formula 2: Utilizarea funcțiilor INDEX și MATCH

Iată o altă formulă care vă poate ajuta, de asemenea, să efectuați o căutare bidimensională, vă rugăm să aplicați formula de mai jos, apoi să apăsați Introduce cheie pentru a obține rezultatul de care aveți nevoie.

Notă: În formula de mai sus:

• B2: E6: intervalul de date pentru a returna elementul potrivit;
• H1: valoarea de căutare din coloana pe care doriți să obțineți valoarea corespunzătoare bazată pe;
• A2: A6: anteturile de rând conțin produsul pe care doriți să îl căutați.
• H2: valoarea de căutare din rândul pe care doriți să obțineți valoarea corespunzătoare pe baza;
• B1: E1: anteturile de coloană conțin trimestrul pe care doriți să îl căutați.

2. Valoarea de potrivire Vlookup pe baza a două sau mai multe criterii

Vă este ușor să căutați valoarea potrivită pe baza unui criteriu, dar dacă aveți două sau mai multe criterii, ce puteți face? Funcțiile LOOKUP sau MATCH și INDEX din Excel vă pot ajuta să rezolvați rapid și ușor acest job.

De exemplu, am tabelul de date de mai jos, pentru a returna prețul potrivit pe baza produsului și mărimii specifice, următoarele formule vă pot ajuta.

Formula 1: Utilizarea funcției LOOKUP

Vă rugăm să aplicați formula de mai jos într-o celulă în care doriți să obțineți rezultatul, apoi apăsați tasta Enter, vedeți captura de ecran:

note:

• 1. În formula de mai sus:
  A2: A12 = G1: înseamnă căutarea criteriilor G1 în intervalul A2: A12;
  B2: B12 = G2: înseamnă căutarea criteriilor G2 în intervalul B2: B12;
  D2: D12: intervalul în care doriți să returnați valoarea corespunzătoare.
• 2. Dacă aveți mai mult de două criterii, trebuie doar să alăturați celorlalte criterii în formulă, cum ar fi: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
  

Formula 2: Utilizarea funcțiilor INDEXT și MATCH

Combinația dintre funcția Index și potrivire poate fi utilizată și pentru a returna valoarea potrivită pe baza mai multor criterii. Vă rugăm să copiați sau să introduceți următoarea formulă:

Apoi, apăsați Ctrl + Shift + Enter tastele împreună pentru a obține valoarea relativă după cum aveți nevoie. Vedeți captura de ecran:

note:

• 1. În formula de mai sus:
  A2: A12 = G1: înseamnă căutarea criteriilor G1 în intervalul A2: A12;
  B2: B12 = G2: înseamnă căutarea criteriilor G2 în intervalul B2: B12;
  D2: D12: intervalul în care doriți să returnați valoarea corespunzătoare.
• 2. Dacă aveți mai mult de două criterii, trebuie doar să alăturați noilor criterii în formulă, cum ar fi: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
  

3. Vlookup pentru a returna mai multe valori potrivite cu una sau mai multe condiții

În Excel, funcția Vlookup caută o valoare și returnează prima valoare potrivită numai dacă sunt găsite mai multe valori corespunzătoare. Uneori, poate doriți să returnați toate valorile corespunzătoare într-un rând, într-o coloană sau într-o singură celulă. Această secțiune va vorbi despre modul de returnare a valorilor de potrivire multiple cu una sau mai multe condiții într-un registru de lucru.

Căutați toate valorile potrivite pe baza uneia sau mai multor condiții pe orizontală

Vizualizați toate valorile potrivite pe baza unei condiții orizontal:

Pentru a vizualiza și a returna toate valorile potrivite pe baza unei valori specifice pe orizontală, formula generică este:

1. Vă rugăm să aplicați formula de mai jos într-o celulă goală, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține prima valoare potrivită, consultați captura de ecran:

2. Apoi, selectați prima celulă de formulă și trageți mânerul de umplere în celulele din dreapta până când este afișată celula goală și toate elementele corespunzătoare au fost extrase, vedeți captura de ecran:

Căutați toate valorile potrivite pe baza a două sau mai multe condiții pe orizontală:

Pentru a vizualiza și a returna toate valorile potrivite bazate pe valori mai specifice pe orizontală, formula generică este:

1. Aplicați următoarea formulă într-o celulă goală unde doriți să obțineți rezultatul:

2. Apoi, selectați celula formulă și trageți mânerul de umplere în celulele din dreapta până când se afișează celula necompletată și toate valorile potrivite pe baza criteriilor specifice au fost returnate, consultați captura de ecran:

notițe: Pentru mai multe criterii, trebuie doar să alăturați lookup_value și lookup_range în formulă, cum ar fi: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").

Căutați toate valorile potrivite pe baza uneia sau mai multor condiții pe verticală

Vizualizați toate valorile potrivite pe baza unei condiții pe verticală:

Pentru a vizualiza și a returna toate valorile potrivite pe baza unei valori specifice pe verticală, formula generică este:

1. Copiați sau tastați următoarea formulă într-o celulă unde doriți să obțineți rezultatul, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține prima valoare potrivită, consultați captura de ecran:

2. Apoi, selectați prima celulă de formulă și trageți mânerul de umplere în jos către alte celule până când este afișată celula goală și toate articolele corespunzătoare au fost listate într-o coloană, vedeți captura de ecran:

Căutați toate valorile potrivite pe baza a două sau mai multe condiții pe verticală:

Pentru a vizualiza și a returna toate valorile potrivite pe baza unor valori mai specifice pe verticală, formula generică este:

1. Copiați formula de mai jos într-o celulă goală, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține primul articol asortat.

2. Apoi trageți celula formulă în jos în alte celule până când este afișată celula goală, consultați captura de ecran:

notițe: Pentru mai multe criterii, trebuie doar să alăturați lookup_value și lookup_range în formulă, cum ar fi: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").

Căutați toate valorile potrivite bazate pe două sau mai multe condiții într-o singură celulă

Dacă doriți să vizualizați și să returnați mai multe valori potrivite într-o singură celulă cu separator specificat, noua funcție a TEXTJOIN vă poate ajuta să rezolvați acest lucru rapid și ușor.

Vizualizați toate valorile potrivite bazate pe o condiție într-o singură celulă:

Vă rugăm să aplicați formula simplă de mai jos într-o celulă goală, apoi apăsați Ctrl + Shift + Enter cheile împreună pentru a obține rezultatul:

Căutați toate valorile potrivite bazate pe două sau mai multe condiții într-o singură celulă:

Pentru a face față mai multor condiții atunci când returnați toate valorile potrivite într-o singură celulă, aplicați formula de mai jos, apoi apăsați Ctrl + Shift + Enter cheile împreună pentru a obține rezultatul:

note:

1. Funcția TEXTJOIN este disponibilă numai în Excel 2019 și Office 365.

2. Dacă utilizați Excel 2016 și versiunile anterioare, vă rugăm să utilizați funcția definită de utilizator din articolele de mai jos:

• Vlookup Pentru a returna mai multe valori într-o singură celulă în Excel
• Vlookup și returnează valori multiple fără duplicate în Excel

4. Vlookup pentru a returna întregul sau întregul rând al unei celule potrivite

În această secțiune, voi vorbi despre modul de recuperare a întregului rând al unei valori potrivite utilizând funcția Vlookup.

1. Vă rugăm să copiați sau să tastați formula de mai jos într-o celulă goală unde doriți să afișați rezultatul și apăsați Introduce cheie pentru a obține prima valoare.

2. Apoi, trageți celula formulă în partea dreaptă până când sunt afișate datele întregului rând, vedeți captura de ecran:

notițe: În formula de mai sus, F2 este valoarea de căutare pe care doriți să returnați întregul rând, A1: D12 este intervalul de date pe care doriți să îl utilizați, A1 indică primul număr de coloană din intervalul dvs. de date.

5. Faceți mai multe funcții Vlookup (Vlookup imbricat) în Excel

Uneori, poate doriți să căutați valori în mai multe tabele, dacă oricare dintre tabele conține valoarea de căutare dată așa cum este prezentată mai jos, în acest caz, puteți combina una sau mai multe funcții Vlookup împreună cu funcția IFERROR pentru a efectua căutări multiple.

Formula generică pentru funcția Vlookup imbricată este:

Notă:

• lookup_value: valoarea pe care o cauți;
• Table1, Table2, Table3, ...: tabelele în care există valoarea de căutare și valoarea returnată;
• cu: numărul coloanei din tabelul din care doriți să returnați valoarea potrivită.
• 0: Acesta este utilizat pentru o potrivire exactă.

1. Vă rugăm să aplicați următoarea formulă într-o celulă goală unde doriți să puneți rezultatul:

2. Apoi, trageți mânerul de umplere în jos în celulele pe care doriți să le aplicați această formulă și toate valorile potrivite au fost returnate așa cum se arată în imaginea de mai jos:

note:

• 1. În formula de mai sus, J3 este valoarea pe care o cauți; A3: B7, D3: E7, G3: H7 sunt intervalele de tabele în care există valoarea de căutare și valoarea returnată; Numarul 2 este numărul coloanei din intervalul din care se returnează valoarea potrivită.
• 2. Dacă valoarea de căutare nu poate fi găsită, se afișează o valoare de eroare, pentru a înlocui eroarea cu un text lizibil, vă rugăm să utilizați această formulă: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))
  

6. Vlookup pentru a verifica dacă există valoare pe baza unei liste de date dintr-o altă coloană

Funcția Vlookup vă poate ajuta, de asemenea, să verificați dacă există valori pe baza unei alte liste, de exemplu, dacă doriți să căutați numele din coloana C și să returnați Da sau Nu dacă numele este găsit sau nu în coloana A, ca mai jos captura de ecran afișate.

1. Vă rugăm să aplicați următoarea formulă într-o celulă goală:

2. Apoi, glisați mânerul de umplere în jos în celulele pe care doriți să le completați cu această formulă și veți obține rezultatul după cum aveți nevoie, vedeți captura de ecran:

notițe: În formula de mai sus, C2 este valoarea de căutare pe care doriți să o verificați; A2: A10 este lista intervalului de unde vor fi găsite valorile de căutare; numarul 1 este numărul coloanei de unde doriți să preluați valoarea din intervalul dvs.

7. Căutați și sumați toate valorile potrivite în rânduri sau coloane

Dacă lucrați cu date numerice, uneori, atunci când extrageți valorile potrivite din tabel, poate fi necesar, de asemenea, să însumați numerele în mai multe coloane sau rânduri. Această secțiune va introduce câteva formule pentru a finaliza această lucrare în Excel.

Vizualizați și sumați toate valorile potrivite într-un rând sau mai multe rânduri

Să presupunem că aveți o listă de produse cu vânzări timp de câteva luni, după cum se arată în imaginea de mai jos, acum trebuie să însumați toate comenzile în toate lunile pe baza produselor date.

Căutați și sumați primele valori potrivite la rând:

1. Vă rugăm să copiați sau să introduceți următoarea formulă într-o celulă goală, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține primul rezultat.

2. Apoi, trageți mânerul de umplere în jos pentru a copia această formulă în alte celule de care aveți nevoie și toate valorile dintr-un rând din prima valoare de potrivire au fost însumate împreună, vedeți captura de ecran:

notițe: În formula de mai sus: H2 este celula care conține valoarea pe care o căutați; A2: F9 este intervalul de date (fără anteturi de coloană) care include valoarea de căutare și valorile potrivite; Numarul 2,3,4,5,6 {} sunt numere de coloane utilizate pentru a calcula totalul intervalului.

Vizualizați și sumați toate valorile potrivite în mai multe rânduri:

Formula de mai sus poate însuma numai valorile la rând pentru prima valoare potrivită. Dacă doriți să însumați toate potrivirile în mai multe rânduri, vă rugăm să utilizați următoarea formulă, apoi trageți mânerul de umplere în jos în celulele pe care doriți să le aplicați această formulă și veți obține rezultatul dorit de care aveți nevoie, consultați captura de ecran:

notițe: În formula de mai sus: H2 este valoarea de căutare pe care o căutați; A2: A9 este anteturile de rând care conțin valoarea de căutare; B2: F9 intervalul de date al valorilor numerice pe care doriți să le însumați.

Căutați și sumați toate valorile potrivite într-o coloană sau mai multe coloane

Căutați și sumați primele valori potrivite într-o coloană:

Dacă doriți să însumați valoarea totală pentru lunile specifice, așa cum se arată în captura de ecran de mai jos.

Aplicați formula de mai jos într-o celulă goală, apoi glisați mânerul de umplere în jos pentru a copia această formulă în alte celule, acum, primele valori potrivite bazate pe luna specifică dintr-o coloană au fost însumate împreună, vedeți captura de ecran:

notițe: În formula de mai sus: H2 este valoarea de căutare pe care o căutați; B1: F1 este anteturile de coloană care conțin valoarea de căutare; B2: F9 intervalul de date al valorilor numerice pe care doriți să le însumați.

Vizualizați și sumați toate valorile potrivite în mai multe coloane:

Pentru a vizualiza și a însuma toate valorile potrivite în mai multe coloane, ar trebui să utilizați următoarea formulă:

notițe: În formula de mai sus: H2 este valoarea de căutare pe care o căutați; B1: F1 este anteturile de coloană care conțin valoarea de căutare; B2: F9 intervalul de date al valorilor numerice pe care doriți să le însumați.

Vizualizați și sumați primele valori potrivite sau toate valorile potrivite cu o caracteristică puternică

Poate că formulele de mai sus vă sunt dificil de reținut, în acest caz, vă voi recomanda o caracteristică la îndemână - Căutare și sumă of Kutools pentru Excel, cu această caracteristică, puteți obține rezultatul cât mai ușor posibil.    Faceți clic pentru a descărca Kutools pentru Excel acum!

Vizualizați și sumați toate valorile potrivite atât în ​​rânduri, cât și în coloane

Dacă doriți să însumați valorile atunci când trebuie să potriviți atât coloana, cât și rândul, de exemplu, pentru a obține valoarea totală a produsului Pulover în luna Mar, după cum se arată în imaginea de mai jos.

Vă rugăm să aplicați următoarea formulă într-o celulă, apoi apăsați tasta Enter pentru a obține rezultatul, vedeți captura de ecran:

notițe: În formula de mai sus: B2: F9 este intervalul de date al valorilor numerice pe care doriți să le însumați; B1: F1 is anteturile de coloană conțin valoarea de căutare pe care doriți să o sumați pe baza; I2 este valoarea de căutare din anteturile de coloană pe care le căutați; A2: A9 anteturile de rând conțin valoarea de căutare pe care doriți să o sumați pe baza; H2 este valoarea de căutare din antetele de rând pe care le căutați.

8. Vlookup pentru a combina două tabele bazate pe una sau mai multe coloane cheie

În activitatea dvs. zilnică, atunci când analizați datele, poate fi necesar să adunați toate informațiile necesare într-un singur tabel bazat pe una sau mai multe coloane cheie. Pentru a rezolva această sarcină, funcția Vlookup vă poate face și o favoare.

Vlookup pentru a îmbina două tabele pe baza unei coloane cheie

De exemplu, aveți două tabele, primul tabel conține datele despre produse și nume, iar al doilea tabel conține produsele și comenzile, acum doriți să combinați aceste două tabele prin potrivirea coloanei comune a produsului într-un singur tabel.

Formula 1: Utilizarea funcției VLOOKUP

Pentru a îmbina cele două tabele într-unul bazat pe o coloană cheie, vă rugăm să aplicați următoarea formulă într-o celulă goală unde doriți să obțineți rezultatul, apoi trageți mânerul de umplere în jos în celulele pe care doriți să aplicați această formulă obțineți un tabel combinat cu coloana de ordine care se alătură primelor date ale tabelului pe baza datelor coloanei cheie.

notițe: În formula de mai sus, A2 este valoarea pe care o cauți, E2: F8 este tabelul de căutat, numărul 2 este numărul coloanei din tabel din care să extrageți valoarea.

Formula 2: Utilizarea funcțiilor INDEX și MATCH

Dacă datele dvs. comune din partea dreaptă și datele returnate în coloana din stânga din cadrul celui de-al doilea tabel, pentru a îmbina coloana de comandă, funcția Vlookup nu poate face treaba. Pentru a privi în sus de la dreapta la stânga, puteți utiliza funcțiile INDEX și MATCH pentru a înlocui funcția Vlookup.

Vă rugăm să copiați sau să introduceți formula de mai jos într-o celulă goală, apoi copiați formula în coloană, iar coloana de comandă a fost alăturată primului tabel, vedeți captura de ecran:

notițe: În formula de mai sus, A2 este valoarea de căutare pe care o căutați, E2: E8 este gama de date pe care doriți să le returnați, F2: F8 este intervalul de căutare care conține valoarea de căutare.

Vlookup pentru a combina două tabele bazate pe mai multe coloane cheie

Dacă cele două tabele la care doriți să vă alăturați au mai multe coloane cheie, pentru a îmbina tabelele pe baza acestor coloane comune, funcțiile INDEX și MATCH vă pot ajuta.

Formula generică pentru fuzionarea a două tabele bazate pe mai multe coloane cheie este:

1. Vă rugăm să aplicați formula de mai jos într-o celulă goală unde doriți să puneți rezultatul, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține prima valoare potrivită, consultați captura de ecran:

notițe: În formula de mai sus, ceea ce reprezintă referințele celulei, după cum se arată în imaginea de mai jos:

2Apoi, selectați prima celulă de formulă și trageți mânerul de umplere pentru a copia această formulă în alte celule, după cum aveți nevoie:

9. Vlookup potrivirea valorilor pe mai multe foi de lucru

Ați încercat vreodată să vedeți valori în mai multe foi de lucru? Presupunând că am următoarele trei foi de lucru cu o gamă de date și acum vreau să obțin o parte din valorile corespunzătoare pe baza criteriilor din aceste trei foi de lucru pentru a obține rezultatul așa cum este prezentat mai jos. În acest caz, Valori Vlookup pe mai multe foi de lucru tutorialul vă poate face o favoare pas cu pas.

Valorile potrivite VLOOKUP păstrează formatarea celulei

1. Vlookup pentru a obține formatarea celulei (culoarea celulei, culoarea fontului) împreună cu valoarea de căutare

După cum știm cu toții, funcția normală Vlookup ne poate ajuta doar să returnăm valoarea potrivită dintr-un alt interval de date, dar uneori, poate doriți să returnați valoarea corespunzătoare împreună cu formatarea celulei, cum ar fi culoarea de umplere, culoarea fontului, stilul fontului după cum se arată în imaginea de mai jos. Această secțiune va vorbi despre cum să obțineți formatarea celulei cu valoarea returnată în Excel.

Efectuați următorii pași pentru a căuta și a returna valoarea corespunzătoare împreună cu formatarea celulei:

1. În foaia de lucru conține datele pe care doriți să le vizualizați, faceți clic dreapta pe fila foaie și selectați Afișați codul din meniul contextual. Vedeți captura de ecran:

2. În deschise Microsoft Visual Basic pentru aplicații fereastra, copiați mai jos codul VBA în fereastra Cod.

Cod VBA 1: Vlookup pentru a obține formatarea celulei împreună cu valoarea de căutare

Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
                Range(xDic.Keys(I)).Font.FontStyle = _
                Range(xDic.Items(I)).Font.FontStyle
                Range(xDic.Keys(I)).Font.Size = _
                Range(xDic.Items(I)).Font.Size
                Range(xDic.Keys(I)).Font.Color = _
                Range(xDic.Items(I)).Font.Color
                Range(xDic.Keys(I)).Font.Name = _
                Range(xDic.Items(I)).Font.Name
                Range(xDic.Keys(I)).Font.Underline = _
                Range(xDic.Items(I)).Font.Underline
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3. Încă în Microsoft Visual Basic pentru aplicații fereastră, faceți clic pe Insera > Module, apoi copiați codul VBA 2 de mai jos în fereastra Module.

Cod VBA 2: Vlookup pentru a obține formatarea celulei împreună cu valoarea de căutare

Public xDic As New Dictionary
Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. După introducerea codurilor de mai sus, apoi faceți clic pe unelte > Referinte în Microsoft Visual Basic pentru aplicații fereastră. Apoi verificați Microsoft Script Runtime caseta de selectare din Referințe - VBAProject căsuță de dialog. Vedeți capturi de ecran:

5. Apoi apasa OK pentru a închide caseta de dialog, apoi salvați și închideți fereastra de cod, acum, reveniți la foaia de lucru, apoi aplicați această formulă: =LookupKeepFormat(E2,$A$1:$C$10,3) într-o celulă goală în care doriți să afișați rezultatul, apoi apăsați tasta Enter. Vedeți captura de ecran:

notițe: În formula de mai sus, E2 este valoarea pe care o vei căuta, A1: C10 este intervalul tabelului și numărul 3 este numărul coloanei tabelului pentru care doriți să se returneze valoarea potrivită.

6. Apoi, selectați prima celulă de rezultat și trageți mânerul de umplere în jos pentru a obține toate rezultatele împreună cu formatarea lor. Vezi captura de ecran.

2. Păstrați formatul de dată dintr-o valoare returnată Vlookup

În mod normal, atunci când utilizați funcția Vloook pentru a căuta și a reveni la valoarea formatului de dată potrivită, un anumit format numeric va fi afișat așa cum este prezentat mai jos. Pentru a păstra formatul de dată dintr-un rezultat returnat, ar trebui să atașați funcția TEXT la funcția Vlookup.

Vă rugăm să aplicați formula de mai jos într-o celulă goală, apoi trageți mânerul de umplere pentru a copia această formulă în alte celule și toate datele potrivite au fost returnate după cum se arată în imaginea de mai jos:

notițe: În formula de mai sus, E2 este valoarea aspectului, A2: C9 este intervalul de căutare, numărul 3 este numărul coloanei pentru care doriți să se returneze valoarea, mm / zz / aaaa este formatul de dată pe care doriți să îl păstrați.

3. Vlookup și returnează valoarea potrivită cu comentariul celulei

Ați încercat vreodată să Vlookup pentru a returna nu numai datele de celule potrivite, ci și comentariile de celule, precum și în Excel, după cum urmează imaginea de mai jos? Pentru a rezolva această sarcină, funcția definită de utilizator de mai jos vă poate face o favoare.

1. Țineți apăsat butonul ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.

2. Clic Insera > Module, apoi copiați și lipiți următorul cod în fereastra modulului.

Cod VBA: Vlookup și returnează valoarea de potrivire cu comentariul celulei:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3. Apoi salvați și închideți fereastra de cod, introduceți această formulă: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) într-o celulă goală pentru a localiza rezultatul, apoi trageți mânerul de umplere pentru a copia această formulă în alte celule, acum, valorile potrivite, precum și comentariile sunt returnate simultan, vedeți captura de ecran:

notițe: În formula de mai sus, D2 este valoarea de căutare pe care doriți să îi returnați valoarea corespunzătoare, A2: B9 este tabelul de date pe care doriți să îl utilizați, numărul 2 este numărul coloanei care conține valoarea potrivită pe care doriți să o returnați.

4. Tratați textul și numerele reale în Vlookup

De exemplu, am o serie de date, numărul de identificare din tabelul original este formatul numeric, în celula de căutare care este stocată ca text, atunci când se aplică funcția normală Vlookup, un rezultat de eroare # N / A este afișat ca mai jos captură de ecran afișate. În acest caz, cum ați putea obține informațiile corecte dacă numărul de căutare și numărul original din tabel au formatul de date diferit?

Pentru a trata textul și numerele reale în funcția Vlookup, vă rugăm să aplicați următoarea formulă într-o celulă goală, apoi trageți mânerul de umplere în jos pentru a copia această formulă și veți obține rezultatele corecte, după cum se arată în imaginea de mai jos:

note:

• 1. În formula de mai sus, D2 este valoarea de căutare pe care doriți să îi returnați valoarea corespunzătoare, A2: B8 este tabelul de date pe care doriți să îl utilizați, numărul 2 este numărul coloanei care conține valoarea potrivită pe care doriți să o returnați.
• 2. Această formulă funcționează bine dacă nu sunteți sigur unde aveți numere și unde aveți text.

Descărcați fișierele eșantion VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip

Cele mai bune instrumente de productivitate Office

Kutools pentru Excel vă rezolvă majoritatea problemelor și vă crește productivitatea cu 80%

• Super Formula Bar (editați cu ușurință mai multe linii de text și formulă); Layout de citire (citiți și editați cu ușurință un număr mare de celule); Lipiți la interval filtrat...
• Merge celule / rânduri / coloane și păstrarea datelor; Conținut de celule divizate; Combinați rânduri duplicate și sumă / medie... Prevenirea celulelor duplicate; Comparați gamele...
• Selectați Duplicat sau Unic Rânduri; Selectați Rânduri goale (toate celulele sunt goale); Super Find și Fuzzy Find în multe cărți de lucru; Selectare aleatorie ...
• Copie exactă Mai multe celule fără modificarea referinței formulelor; Creați automat referințe la foi multiple; Introduceți gloanțe, Casete de selectare și multe altele ...
• Formule favorite și inserare rapidă, Gama, Diagrame și Imagini; Criptați celulele cu parola; Creați o listă de corespondență și trimiteți e-mailuri ...
• Extrageți textul, Adăugați text, eliminați după poziție, Eliminați spațiul; Creați și imprimați subtotaluri de paginare; Convertiți conținutul dintre celule și comentarii...
• Super Filtru (salvați și aplicați scheme de filtrare altor foi); Sortare avansată după lună / săptămână / zi, frecvență și multe altele; Filtru special cu bold, italic ...
• Combinați cărți de lucru și foi de lucru; Merge Tables pe baza coloanelor cheie; Împărțiți datele în mai multe foi; Conversia în loturi xls, xlsx și PDF...
• Gruparea tabelului pivot după numărul săptămânii, ziua săptămânii și multe altele ... Afișați celulele deblocate, blocate prin diferite culori; Evidențiați celulele care au formulă / nume...

Citeşte mai mult ... Caracteristici complete Încercare gratuită de 30 de zile ... Cumpărare... 

---

Fila Office - aduce interfața cu file în Office și vă face munca mult mai ușoară

• Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
• Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
• Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!

Citeşte mai mult ... Caracteristici complete Încercare gratuită de 30 de zile ... Cumpărare...