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

Prin combinarea programului ExcelVLOOKUP funcția cuCOLOANĂ puteți 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 un tabel de date.

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 COLOANĂ funcția este 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, COLOANĂ funcția va fi introdusă ca indicele coloanei număr argument pentru VLOOKUP. Ultimul pas din tutorial implică copierea formulei de căutare în coloane suplimentare pentru a obține valori suplimentare pentru partea selectată.

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 prezentate în imaginea de mai sus în următoarele celule:

• Introduceți domeniul maxim de date în celule D1 la G1.
• Introduceți a doua gamă în celule D4 la G10.

Criteriile de căutare și formula de căutare create în timpul acestui tutorial vor fi introduse rândul 2 din foaia 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.

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.

Numele domeniului nu include denumirile de titluri sau câmpuri pentru date (rândul 4), ci doar datele în sine.

1. A sublinia celule D5 la G10 în foaia de lucru pentru a le selecta.
2. Faceți clic pe Cutie de nume situat mai sus coloana A.
3. Tip Masa în Cutie de nume.
4. apasă pe introduce cheie de pe tastatură.
5. Celule D5 la G10 acum aveți numele domeniului Masa. Vom folosi numele pentru VLOOKUP tabela de tabele argument 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 de a folosi VLOOKUP căsuță de dialog. 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. Click pe celula E2 din foaia de lucru - locația în care vor fi afișate rezultatele formulei de căutare bidimensională.
2. Faceți clic pe Formulare fila din panglică.
3. Faceți clic pe Căutare și referință opțiune în panglică pentru a deschide meniul drop-down.
4. Click pe VLOOKUP din listă pentru a deschide caseta de dialog a funcției.

05 din 10

Introducerea Argumentului Valorii Lookup

În mod normal, privește în sus valoare se potrivește cu un câmp de date din prima coloană a tabelului de date. În exemplul nostru, privește în sus valoare se referă la numele părții hardware despre care vrem să găsim informații. Tipurile de date admise pentru privește în sus valoare sunt date text, valori logice, numere și referințe de celule.

Referințe despre celule absolute

Î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 privește în sus valoare, se va schimba pe măsură ce formula este copiată creând erori în celule F2 și G2.

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

Pentru a preveni erorile, vom converti referința celulei D2 într-o referință de celule absolute. Referințele celulelor absolute sunt create prin apăsarea butonului F4 cheie de pe tastatură. În acest fel, se adaugă semne de dolar în jurul referinței celulei, cum ar fi $ D $ cu 2.

1. Faceți clic pe lookup_value line în caseta de dialog.
2. Click pe celulă D2 pentru a adăuga această referință la celula lookup_value line - aceasta este celula în care vom introduce numele părții despre care căutăm informații
3. Fără a muta punctul de inserție, apăsați pe F4 cheie de pe tastatură pentru a converti D2 în referința absolută a celulei $ D $ cu 2.
4. Paraseste VLOOKUP caseta de dialog pentru deschiderea următoarei etape a tutorialului.

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), în timp ce a doua coloană va fi căutată 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.

1. Faceți clic pe table_array line în caseta de dialog.
2. Tip Masa pentru a introduce numele domeniului pentru acest argument.
3. Paraseste VLOOKUP caseta de dialog pentru deschiderea următoarei etape a tutorialului.

07 din 10

Folosind funcția COLUMN

În mod normal VLOOKUP doar returnează datele dintr-o coloană a unui tabel de date și această coloană este setată de numărul indexului coloanei argument.

În acest exemplu, totuși, avem trei coloane pe care dorim să le returnez, așa că avem nevoie de o modalitate de a schimba cu ușurință numărul indexului coloanei fără a edita formula noastră de căutare - aici este locul unde COLOANĂ funcționează.

Introducând-o ca numărul indexului coloanei argument, se va schimba după ce formula de căutare este copiată celula D2 la celule E2 și F2 mai târziu în tutorial.

Funcțiile de asamblare

COLOANĂ funcția, prin urmare, acționează ca VLOOKUP„s numărul indexului coloanei argument; acest lucru se realizează prin cuibăritul COLOANĂ funcția în interiorul VLOOKUP în Col_index_num linia 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. COLOANĂ funcția, prin urmare, trebuie introdusă manual în Col_index_num linia. COLOANĂ funcția are doar un singur argument - Referinţă argument care este o referință de celule.

Alegerea Argumentului de referință al funcției COLUMN

COLOANĂ funcția de serviciu este de a returna numărul de coloană dat ca Referinţă argument. 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.

De la primul câmp de date, ceea ce dorim returnat este prețul articolului, care se află în coloana a doua a tabelului de date. Putem alege referința celulei pentru orice celulă din coloana B dupa cum Referinţă Argument pentru a obține numărul 2 pentru Col_index_num argument.

1. În VLOOKUP casetă de dialog, faceți clic pe Col_index_num linia.
2. Introduceți numele funcției coloană urmat de un deschideți suportul rotund.
3. Click pe celulă B1 în foaia de lucru să introduceți acea referință de celule ca Referinţă argument.
4. Tastați a închidere rotundă pentru a finaliza COLOANĂ funcţie
5. Paraseste VLOOKUP caseta de dialog pentru deschiderea următoarei etape a tutorialului.

08 din 10

Introducerea Argumentului de căutare al domeniului VLOOKUP

VLOOKUP„s Range_lookup argument este o valoare logică (ADEVĂRAT sau FALS) care indică dacă doriți VLOOKUP pentru a găsi o potrivire exactă sau aproximativă cu Lookup_value.

• Dacă ADEVĂRAT sau dacă acest argument este omis: VLOOKUP returnează fie o potrivire exactă cu Lookup_valueSau, dacă nu sa găsit o potrivire exactă, VLOOKUP returnează următoarea valoare cea mai mare. Pentru formula pentru a face acest lucru, datele din prima coloană din Table_array trebuie să fie sortate în ordine crescătoare.
• Dacă FALS: VLOOKUP va folosi doar 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 sa găsit o potrivire exactă, a #N / A eroarea este returnată.

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

1. Faceți clic pe Range_lookup line în caseta de dialog.
2. Introduceți cuvântul Fals în această linie pentru a indica că vrem VLOOKUP pentru a reveni la o potrivire exactă a datelor pe care le căutăm.
3. Clic O.K pentru a completa formula de căutare și pentru a închide fereastra de dialog.
4. Din moment ce nu am intrat încă în criteriile de căutare celulă D2 un # N / A eroare 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

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 vrem să recupereze datele coloanele 2, 3, și 4 din tabelul de date - adică prețul, numărul piesei și numele furnizorului atunci când introducem un nume parțial ca Lookup_value.

Dat fiind că datele sunt prezentate în mod obișnuit în foaia de lucru, putem copia formula de căutare în celula E2 la celule F2 și G2.

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

De asemenea, Excel nu schimbă referința absolută a celulei $ D $ cu 2 și intervalul numit Masa deoarece formula este copiată.

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

1. Click pe celula E2, în cazul în care formula de căutare este localizată, pentru ao face o celulă activă.
2. Plasați cursorul mouse-ului peste negru pătrat în colțul din dreapta jos - indicatorul se va schimba la un semn plus pentru a indica mânerul de umplere.
3. Faceți clic pe butonul stâng al mouse-ului și pe trageți mânerul de umplere peste celula G2.
4. Eliberare butonul mouse-ului și celula F3 ar trebui să conțină formula de căutare bidimensională.
5. Dacă este făcută corect, celule F2 și G2 ar trebui să conțină și #N / A eroare 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ă le recuperați Lookup_value celula (D2) și apăsați butonul introduce cheie 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.

1. Click pe celula D2 în foaia de lucru.
2. Tip Widget în celula D2 și apăsați tasta introduce cheie de pe tastatură.
3. Următoarele informații trebuie afișate în celule E2 la G2.
   1. E2: $ 14.76 - prețul unui widget
   2. F2: PN-98769 - numărul piesei pentru un widget
   3. G2: Widgets Inc. - numele furnizorului pentru widget-uri
4. Testați VLOOKUP array formula mai departe prin tastarea numele de alte părți în celula D2 și observarea rezultatelor în celule E2 la G2