Găsiți mai multe câmpuri de date cu Excel VLOOKUP

Prin combinarea funcției VLOOKUP a Excel cu funcția COLUMN putem crea o formulă de căutare care vă permite să returnați mai multe valori dintr-un singur rând dintr-o bază de date sau tabel de date.

În exemplul prezentat în imaginea de mai sus, formula de căutare ușurează returnarea tuturor valorilor - cum ar fi prețul, numărul piesei și furnizorul - legate de diverse componente hardware.

01 din 10

Returnați mai multe valori cu Excel VLOOKUP

Urmând pașii de mai jos, se creează formula de căutare văzută în imaginea de mai sus, care va returna mai multe valori dintr-un singur fișier de date.

Formula de căutare necesită ca funcția COLUMN să fie imbricată în interiorul VLOOKUP.

Funcționarea unei funcții presupune introducerea celei de-a doua funcții ca unul dintre argumentele pentru prima funcție.

În acest tutorial, funcția COLUMN va fi introdusă ca argument pentru numărul de index al coloanei pentru VLOOKUP.

Ultimul pas din tutorial implică copierea formulei de căutare în coloane suplimentare pentru a obține valori suplimentare pentru partea selectată.

Tutorial Cuprins

• Introducerea datelor Tutorial
• Crearea unui interval denumit pentru tabelul de date
• Pornirea funcției VLOOKUP
• Introducerea Argumentului Valorii Lookup folosind Referințele Celulare Absolute
• Introducerea argumentului Array de tabel
• Introducerea funcției COLUMN Nested
• Finalizarea funcției VLOOKUP
• Copierea formulei de căutare cu mânerul de umplere
• Preluarea datelor cu formula de căutare

02 din 10

Introduceți datele Tutorial

Primul pas în tutorial este să introduceți datele într-o foaie de lucru Excel.

Pentru a urma pașii din tutorial introduceți datele din imaginea de mai sus în următoarele celule .

• Introduceți gama superioară de date în celulele D1 până la G1
• Introduceți a doua gamă în celulele D4 până la G10

Criteriile de căutare și formula de căutare create în timpul acestui tutorial vor fi introduse în rândul 2 al foii de lucru.

Tutorialul nu include formatul vazut in imagine, dar acest lucru nu va afecta felul in care functioneaza formula de cautare.

Informații despre opțiunile de formatare similare cu cele observate mai sus sunt disponibile în acest Tutorial de formatare de bază pentru Excel .

Tutoriale Pași

1. Introduceți datele, așa cum se vede în imaginea de mai sus, în celulele D1 până la G10

03 din 10

Crearea unui interval denumit pentru tabelul de date

O gamă numită este o modalitate ușoară de a se referi la o gamă de date dintr-o formulă. În loc să tastați referințele celulei pentru date, puteți să introduceți numele domeniului.

Un al doilea avantaj pentru utilizarea unei game numite este faptul că referințele celulare pentru acest interval nu se modifică chiar și atunci când formula este copiată în alte celule din foaia de lucru.

Numele de nume sunt, prin urmare, o alternativă la utilizarea referințelor absolute ale celulelor pentru a preveni erorile la copierea formulelor.

Notă: Numele domeniului nu include numele titlurilor sau câmpurilor pentru date (rândul 4), ci doar datele în sine.

Tutoriale Pași

1. Evidențiați celulele D5 în G10 din foaia de lucru pentru a le selecta
2. Faceți clic pe caseta Nume deasupra coloanei A
3. Introduceți "Tabel" (fără ghilimele) în caseta Nume
4. Apăsați tasta ENTER de pe tastatură
5. Celulele D5-G10 au acum numele de domeniu "Tabel". Vom folosi numele pentru argumentul matricei de tabelă VLOOKUP mai târziu în tutorial

04 din 10

Deschiderea casetei de dialog VLOOKUP

Deși este posibil să introducem formula noastră de căutare direct într-o celulă dintr-o foaie de lucru, mulți oameni consideră dificilă menținerea sintaxei drepte - mai ales pentru o formulă complexă precum cea pe care o folosim în acest tutorial.

O alternativă, în acest caz, este să utilizați caseta de dialog VLOOKUP. Aproape toate funcțiile Excel au o casetă de dialog care vă permite să introduceți fiecare argument al funcției pe o linie separată.

Tutoriale Pași

1. Faceți clic pe celula E2 a foii de lucru - locația unde vor fi afișate rezultatele formulei de căutare bidimensională
2. Faceți clic pe fila Formule a panglicii
3. Faceți clic pe opțiunea Lookup & Reference din panglică pentru a deschide lista verticală de funcții
4. Faceți clic pe VLOOKUP din listă pentru a deschide caseta de dialog a funcției

05 din 10

Introducerea Argumentului Valorii Lookup folosind Referințele Celulare Absolute

În mod normal, valoarea de căutare se potrivește cu un câmp de date din prima coloană a tabelului de date.

În exemplul nostru, valoarea de căutare se referă la numele părții hardware despre care vrem să găsim informații.

Tipurile de date admise pentru valoarea de căutare sunt:

• date de text
• o valoare logică (numai TRUE sau FALSE)
• un număr
• o referință de celule la o valoare în foaia de lucru

În acest exemplu, vom introduce referința celulei la locul în care va fi localizat numele părții - celula D2.

Referințe despre celule absolute

Într-un pas mai târziu în tutorial, vom copia formula de căutare în celula E2 la celulele F2 și G2.

În mod normal, atunci când formulele sunt copiate în Excel, referințele celulelor se modifică pentru a reflecta noua lor locație.

Dacă se întâmplă acest lucru, D2 - referința celulei pentru valoarea de căutare - se va schimba pe măsură ce formula va fi copiată, creând erori în celulele F2 și G2.

Pentru a preveni erorile, vom transforma referința celulară D2 într-o referință de celule absolute .

Referințele celulelor absolute nu se modifică atunci când sunt copiate formulele.

Referințele celulelor absolute sunt create prin apăsarea tastei F4 de pe tastatură. Acest lucru adaugă semne de dolar în jurul referinței celulei, cum ar fi $ D $ 2

Tutoriale Pași

1. Faceți clic pe linia lookup_value din caseta de dialog
2. Faceți clic pe celula D2 pentru a adăuga această referință de celule la linia lookup_value . Aceasta este celula în care vom scrie numele părții despre care căutăm informații
3. Fără a muta punctul de inserție, apăsați tasta F4 de pe tastatură pentru a converti D2 în referința absolută a celulei $ D $ 2
4. Lăsați caseta de dialog Funcția VLOOKUP deschisă pentru următorul pas din tutorial

06 din 10

Introducerea argumentului Array de tabel

Matricea tabelă este tabela de date căutată de formula de căutare pentru a găsi informațiile dorite.

Matricea tabelului trebuie să conțină cel puțin două coloane de date .

• prima coloană conține argumentul valorii de căutare (pasul anterior din tutorial)
• a doua și a oricăror coloane suplimentare vor fi căutate de formula de căutare pentru a găsi informațiile pe care le specificăm.

Argumentul matricei de tabelă trebuie să fie introdus fie ca interval care conține referințele celulare pentru tabelul de date, fie ca nume de domeniu .

Pentru acest exemplu, vom folosi numele domeniului creat în pasul 3 al tutorialului.

Tutoriale Pași

1. Faceți clic pe linia table_array din caseta de dialog
2. Introduceți "Tabel" (fără citate) pentru a introduce numele domeniului pentru acest argument
3. Lăsați caseta de dialog Funcția VLOOKUP deschisă pentru următorul pas din tutorial

07 din 10

Folosind funcția COLUMN

În mod normal, VLOOKUP returnează numai datele dintr-o coloană a unui tabel de date și această coloană este setată de argumentul numărului de index al coloanei .

În acest exemplu, totuși, avem trei coloane pe care dorim să le returnez, așa că avem nevoie de o modalitate de a modifica cu ușurință numărul indexului coloanei fără a modifica formula noastră de căutare.

Aici intră funcția COLUMN. Introducând-o ca argument argument pentru numărul de index , el se va schimba pe măsură ce formula de căutare va fi copiată din celula D2 în celulele E2 și F2 mai târziu în tutorial.

Funcțiile de asamblare

Funcția COLUMN, prin urmare, acționează ca argument al numărului de index al coloanei VLOOKUP.

Aceasta se realizează prin asamblarea funcției COLUMN în interiorul VLOOKUP în linia Col_index_num a casetei de dialog.

Introducerea manuală a funcției COLUMN

Când funcțiile de cuibărire, Excel nu ne permite să deschidem caseta de dialog a celei de-a doua funcții pentru a introduce argumentele sale.

Prin urmare, funcția COLUMN trebuie introdusă manual în linia Col_index_num .

Funcția COLUMN are un singur argument - argumentul Referință care este o referință de celule.

Alegerea Argumentului de referință al funcției COLUMN

Funcția COLUMN este de a returna numărul coloanei date ca argument de referință .

Cu alte cuvinte, convertește litera coloanei într-un număr cu coloana A fiind prima coloană, coloana B a doua și așa mai departe.

Deoarece primul câmp de date pe care dorim să-l returnați este prețul elementului - care este în coloana a doua a tabelului de date - putem alege referința celulei pentru orice celulă din coloana B ca argument Argument pentru a obține numărul 2 pentru argumentul Col_index_num .

Tutoriale Pași

1. În caseta de dialog Funcție VLOOKUP, faceți clic pe linia Col_index_num
2. Introduceți coloana cu numele funcției , urmată de o coloană deschisă rotundă " ( "
3. Faceți clic pe celula B1 din foaia de lucru pentru a introduce respectiva referință de celule ca argument de referință
4. Tastați un suport rotund " ) " pentru a finaliza funcția COLUMN
5. Lăsați caseta de dialog Funcția VLOOKUP deschisă pentru următorul pas din tutorial

08 din 10

Introducerea Argumentului de căutare al domeniului VLOOKUP

Argumentul Range_lookup al VLOOKUP este o valoare logică (TRUE sau FALSE only) care indică dacă doriți ca VLOOKUP să găsească o potrivire exactă sau aproximativă cu Lookup_value.

• Dacă TRUE sau dacă acest argument este omis, VLOOKUP returnează fie o potrivire exactă cu Lookup_value, fie, dacă nu se găsește o potrivire exactă, VLOOKUP returnează următoarea valoare cea mai mare. Pentru formula pentru a face acest lucru, datele din prima coloană a Table_array trebuie să fie sortate în ordine ascendentă .
• Dacă FALSE, VLOOKUP va folosi doar o potrivire exactă la Lookup_value. Dacă există două sau mai multe valori în prima coloană a Table_array care se potrivește cu valoarea de căutare, se utilizează prima valoare găsită. Dacă nu se găsește o potrivire exactă, este returnată o eroare de # N / A.

În acest tutorial, deoarece căutăm informații specifice despre un anumit element hardware, vom seta Range_lookup egal cu False .

Tutoriale Pași

1. Faceți clic pe linia Range_lookup din caseta de dialog
2. Introduceți cuvântul " Fals" în această linie pentru a indica că vrem ca VLOOKUP să returneze o potrivire exactă pentru datele pe care le căutăm
3. Faceți clic pe OK pentru a completa formula de căutare și închideți caseta de dialog
4. Din moment ce nu am introdus încă criteriile de căutare în celula D2, o eroare de tip # N / A va fi prezentă în celula E2
5. Această eroare va fi corectată când vom adăuga criteriile de căutare în ultima etapă a tutorialului

09 din 10

Copierea formulei de căutare cu mânerul de umplere

Formula de căutare este destinată pentru a prelua datele de la mai multe coloane ale tabelului de date la un moment dat.

Pentru a face acest lucru, formularul de căutare trebuie să se găsească în toate câmpurile de unde dorim informații.

În acest tutorial dorim să preluăm datele din coloanele 2, 3 și 4 din tabelul de date - adică prețul, numărul piesei și numele furnizorului când introducem numele părții drept Lookup_value.

Deoarece datele sunt prezentate într-un model obișnuit în foaia de lucru , putem copia formula de căutare în celula E2 la celulele F2 și G2.

Pe măsură ce formula este copiată, Excel va actualiza referința celulară relativă în funcția COLUMN (B1) pentru a reflecta noua locație a formulei.

De asemenea, Excel nu schimbă referința absolută a celulei $ D $ 2, iar tabelul de numire denumit ca formula este copiată.

Există mai multe modalități de copiere a datelor în Excel, dar probabil cea mai ușoară cale este folosirea mânerului de umplere .

Tutoriale Pași

1. Faceți clic pe celula E2 - unde este localizată formula de căutare - pentru a deveni celula activă
2. Așezați indicatorul mouse-ului peste pătratul negru din colțul din dreapta jos. Pointerul se va schimba la semnul plus " + " - acesta este mânerul de umplere
3. Faceți clic pe butonul stâng al mouse-ului și glisați mânerul de umplere spre celula G2
4. Eliberați butonul mouse-ului și celula F3 ar trebui să conțină formula de căutare bidimensională
5. Dacă se face corect, celulele F2 și G2 ar trebui să conțină acum și eroarea # N / A care este prezentă în celula E2

10 din 10

Introducerea criteriilor de căutare

Odată ce formula de căutare a fost copiată în celulele necesare , ea poate fi utilizată pentru a prelua informații din tabelul de date.

Pentru aceasta, tastați numele elementului pe care doriți să îl regăsiți în celula Lookup_value (D2) și apăsați tasta ENTER de pe tastatură.

După ce ați terminat, fiecare celulă care conține formula de căutare ar trebui să conțină o altă piesă de date despre elementul hardware pe care îl căutați.

Tutoriale Pași

1. Faceți clic pe celula D2 din foaia de lucru
2. Introduceți Widget în celula D2 și apăsați tasta ENTER de pe tastatură
3. Următoarele informații trebuie afișate în celulele E2 până la G2:
   • E2 - 14,76 dolari - prețul unui widget
   • F2 - PN-98769 - numărul piesei pentru un widget
   • G2 - Widgets Inc. - numele furnizorului pentru widget-uri
4. Testați mai întâi formula VLOOKUP, tastând numele altor părți în celula D2 și observând rezultatele în celulele E2 la G2

Dacă un mesaj de eroare, cum ar fi #REF! apare în celulele E2, F2 sau G2, această listă de mesaje de eroare VLOOKUP vă poate ajuta să determinați unde se află problema.