Combinând funcția VLOOKUP Excel cu funcția COLUMN, puteți crea o formulă de căutare care returnează mai multe valori dintr-un singur rând al unei baze de date sau al unui tabel de date. Aflați cum să creați o formulă de căutare care returnează mai multe valori dintr-o singură înregistrare de date.
Instrucțiunile din acest articol se aplică pentru Excel 2019, 2016, 2013, 2010; și Excel pentru Microsoft 365.
Linia de bază
Formula de căutare necesită ca funcția COLUMN să fie imbricată în VLOOKUP. Imbricarea unei funcții implică introducerea celei de-a doua funcție ca unul dintre argumentele primei funcție.
Introduceți datele tutorialului
În acest tutorial, funcția COLUMN este introdusă ca argument al numărului de index al coloanei pentru CĂUTARE V. Ultimul pas din tutorial implică copierea formulei de căutare în coloane suplimentare pentru a prelua valori suplimentare pentru partea aleasă.
Primul pas în acest tutorial este să introduceți datele într-o foaie de lucru Excel. Pentru a urma pașii din acest tutorial, introduceți datele afișate în imaginea de mai jos în următoarele celule:
- Introduceți intervalul superior de date în celulele D1 la G1.
- Introduceți al doilea interval în celulele D4 până la G10.
Criteriile de căutare și formula de căutare create în acest tutorial sunt introduse în rândul 2 al foii de lucru.
Acest tutorial nu include formatarea de bază Excel prezentată în imagine, dar aceasta nu afectează modul în care funcționează formula de căutare.
Creați un interval denumit pentru tabelul de date
Un interval numit este o modalitate simplă de a face referire la un interval de date dintr-o formulă. În loc să tastați referințele de celule pentru date, introduceți numele intervalului.
Un al doilea avantaj al utilizării unui interval numit este că referințele de celule pentru acest interval nu se schimbă niciodată, chiar și atunci când formula este copiată în alte celule din foaia de lucru. Numele intervalelor sunt o alternativă la utilizarea referințelor absolute de celule pentru a preveni erorile la copierea formulelor.
Numele intervalului nu include titlurile sau numele câmpurilor pentru date (așa cum se arată în rândul 4), ci doar datele.
-
Evidențiați celule D5 la G10 în foaia de lucru.
Image -
Plasați cursorul în caseta de nume situată deasupra coloanei A, tastați Tabel, apoi apăsați Enter. Celulele D5 până la G10 au numele intervalului Tabel.
Image - Numele intervalului pentru argumentul matricei tabelului CĂUTARE V este folosit mai târziu în acest tutorial.
Deschide caseta de dialog CĂUTARE V
Deși este posibil să tastați formula de căutare direct într-o celulă dintr-o foaie de lucru, multor oameni le este dificil să păstreze sintaxa corectă - mai ales pentru o formulă complexă precum cea folosită în acest tutorial.
Ca alternativă, utilizați caseta de dialog Argumente ale funcției CĂUTARE V. Aproape toate funcțiile Excel au o casetă de dialog în care fiecare dintre argumentele funcției este introdus pe o linie separată.
-
Selectați celula E2 din foaia de lucru. Aceasta este locația în care se vor afișa rezultatele formulei de căutare bidimensionale.
Image -
Pe panglică, accesați fila Formule și selectați Căutare și referință.
Image -
Selectați CĂUTARE V pentru a deschide caseta de dialog Argumente ale funcției.
Image - Faseta de dialog Argumente ale funcției este locul în care sunt introduși parametrii funcției CĂUTARE V.
Introduceți argumentul valorii de căutare
În mod normal, valoarea de căutare se potrivește cu un câmp de date din prima coloană a tabelului de date. În acest exemplu, valoarea de căutare se referă la numele părții despre care doriți să găsiți informații. Tipurile de date permise pentru valoarea de căutare sunt date text, valori logice, numere și referințe de celule.
Referințe absolute pentru celule
Când formulele sunt copiate în Excel, referințele celulelor se modifică pentru a reflecta noua locație. Dacă se întâmplă acest lucru, D2, referința celulei pentru valoarea de căutare, se modifică și creează erori în celulele F2 și G2.
Referințele absolute de celule nu se modifică atunci când formulele sunt copiate.
Pentru a preveni erorile, convertiți referința de celulă D2 într-o referință de celulă absolută. Pentru a crea o referință de celulă absolută, apăsați tasta F4. Aceasta adaugă semne de dolar în jurul referinței celulei, cum ar fi $D$2.
-
În caseta de dialog Argumente ale funcției, plasați cursorul în caseta de text lookup_value. Apoi, în foaia de lucru, selectați celula D2 pentru a adăuga această referință de celulă la lookup_value. Celula D2 este locul unde va fi introdus numele piesei.
Image -
Fără a muta punctul de inserare, apăsați tasta F4 pentru a converti D2 la referința absolută a celulei $D$2.
Image - Lăsați deschisă caseta de dialog pentru funcția CĂUTARE V pentru următorul pas din tutorial.
Introduceți argumentul matricei de tabel
O matrice de tabel este tabelul de date pe care formula de căutare îl caută pentru a găsi informațiile dorite. Tabelul trebuie să conțină cel puțin două coloane de date.
Prima coloană conține argumentul valorii de căutare (care a fost configurat în secțiunea anterioară), în timp ce a doua coloană este căutată prin formula de căutare pentru a găsi informațiile pe care le specificați.
Argumentul matricei tabelului trebuie introdus fie ca un interval care conține referințele de celule pentru tabelul de date, fie ca un nume de interval.
Pentru a adăuga tabelul de date la funcția VLOOKUP, plasați cursorul în caseta de text table_array din caseta de dialog și tastați Tablepentru a introduce numele intervalului pentru acest argument.
Cuibrați funcția COLUMN
În mod normal, CĂUTARE V returnează numai date dintr-o coloană a unui tabel de date. Această coloană este setată de argumentul numărului de index al coloanei. În acest exemplu, totuși, există trei coloane, iar numărul de index al coloanei trebuie schimbat fără a edita formula de căutare. Pentru a realiza acest lucru, imbricați funcția COLUMN în cadrul funcției CĂUTARE V ca argumentul Col_index_num.
La imbricarea funcțiilor, Excel nu deschide caseta de dialog a celei de-a doua funcții pentru a introduce argumentele acesteia. Funcția COLUMN trebuie introdusă manual. Funcția COLUMN are un singur argument, argumentul Referință, care este o referință de celulă.
Funcția COLUMN returnează numărul coloanei furnizat ca argument Referință. Acesta convertește litera coloanei într-un număr.
Pentru a afla prețul unui articol, utilizați datele din coloana 2 a tabelului de date. Acest exemplu folosește coloana B ca referință pentru a insera 2 în argumentul Col_index_num.
-
În caseta de dialog Argumente ale funcției, plasați cursorul în caseta de text Col_index_num și tastați COLUMN(. (Asigurați-vă că includeți paranteza rotundă deschisă.)
Image -
În foaia de lucru, selectați celula B1 pentru a introduce referința de celulă ca argument referință.
Image - Tastați un paranteză rotundă de închidere pentru a finaliza funcția COLUMN.
Introduceți argumentul de căutare a intervalului VLOOKUP
Argumentul Range_lookup al VLOOKUP este o valoare logică (ADEVĂRAT sau FALSE) care indică dacă CĂUTARE V ar trebui să găsească o potrivire exactă sau aproximativă cu Lookup_value.
- TRUE sau Omis: CĂUTARE V returnează o potrivire apropiată de Lookup_value. Dacă nu este găsită o potrivire exactă, CĂUTARE V returnează următoarea valoare cea mai mare. Datele din prima coloană din Table_array trebuie sortate în ordine crescătoare.
- FALSE: LOOKUP folosește o potrivire exactă cu Lookup_value. Dacă există două sau mai multe valori în prima coloană din Table_array care se potrivesc cu valoarea de căutare, se utilizează prima valoare găsită. Dacă nu este găsită o potrivire exactă, este returnată o eroare N/A.
În acest tutorial, vor fi căutate informații specifice despre un anumit element hardware, astfel încât Range_lookup este setat la FALSE.
În caseta de dialog Argumente de funcție, plasați cursorul în caseta de text Range_lookup și tastați False pentru a spune VLOOKUP să returneze o potrivire exactă pentru date.
Selectați OK pentru a finaliza formula de căutare și a închide caseta de dialog. Celula E2 va conține o eroare N/A deoarece criteriile de căutare nu au fost introduse în celula D2. Această eroare este temporară. Acesta va fi corectat atunci când criteriile de căutare sunt adăugate în ultimul pas al acestui tutorial.
Copiați formula de căutare și introduceți criteriile
Formula de căutare preia date din mai multe coloane ale tabelului de date simultan. Pentru a face acest lucru, formula de căutare trebuie să se afle în toate câmpurile din care doriți informații.
Pentru a prelua date din coloanele 2, 3 și 4 din tabelul de date (prețul, numărul piesei și numele furnizorului), introduceți un nume parțial ca Lookup_value.
Deoarece datele sunt prezentate într-un model obișnuit în foaia de lucru, copiați formula de căutare în celula E2 în celule F2 și G2 Pe măsură ce formula este copiată, Excel actualizează referința relativă a celulei din funcția COLUMN (celula B1) pentru a reflecta noua locație a formulei. Excel nu modifică referința absolută a celulei (cum ar fi $D$2) și intervalul numit (Tabel) pe măsură ce formula este copiată.
Există mai multe modalități de a copia datele în Excel, dar cea mai ușoară este să folosești mânerul de umplere.
-
Selectați celula E2, unde se află formula de căutare, pentru a deveni celula activă.
Image -
Trageți mânerul de umplere pe celula G2. Celulele F2 și G2 afișează eroarea N/A care este prezentă în celula E2.
Image -
Pentru a utiliza formulele de căutare pentru a prelua informații din tabelul de date, în foaia de lucru selectați celula D2, tastați Widget și apăsați Introduceți.
Image Următoarele informații sunt afișate în celulele E2 până la G2.
- E2: 14,76 USD - prețul unui widget
- F2: PN-98769 - numărul piesei pentru un widget
- G2: Widgets Inc. - numele furnizorului de widget-uri
-
Pentru a testa formula matricei CĂUTARE V, introduceți numele altor părți în celula D2 și observați rezultatele din celulele E2 la G2.
Image - Fiecare celulă care conține formula de căutare conține o parte diferită de date despre elementul hardware pe care l-ați căutat.
Funcția VLOOKUP cu funcții imbricate precum COLUMN oferă o metodă puternică de a căuta date în interiorul unui tabel, folosind alte date ca referință de căutare.
