Funcția VLOOKUP a Excel este utilizată pentru a găsi și a returna informații dintr-o tabelă de date pe baza unei valori de căutare pe care o alegeți.
În mod tipic, VLOOKUP necesită ca valoarea de căutare să fie în coloana din stânga a tabelului de date, iar funcția returnează un alt câmp de date aflat în același rând la dreapta acestei valori.
Cu toate acestea, combinând VLOOKUP cu funcția CHOOSE, se poate crea o formulă de căutare stânga care:
- Permite valoarea de căutare să fie din orice coloană din tabelul de date
- Returnează informațiile aflate în orice coloană din stânga valorii de căutare
Notă: Aceste instrucțiuni se aplică versiunilor Excel 2019, 2016, 2013, 2010 și Excel pentru Office 365.
01 din 05Tutorial: Folosind funcțiile VLOOKUP și CHOOSE într-o formulă de căutare stânga
Pentru a crea formula de căutare din stânga văzută în imaginea de exemplu, utilizați formula:
= VLOOKUP ($ D $ 2, ALEGE ({1,2}, $ F: $ F $ D: $ D), 2, FALSE)
În acest exemplu, formula permite găsirea părții furnizate de diferitele companii enumerate în coloana 3 a tabelului de date.
Funcția funcției ALEGERE în formula este de a șterge VLOOKUP să creadă că coloana 3 este coloana 1. Ca rezultat, numele companiei poate fi folosit ca valoare de căutare pentru a găsi numele părții furnizate de fiecare companie.
Introduceți datele Tutorial
- Introduceți rubrica Furnizor în celulă D1.
- Introduceți rubrica Parte în celulă E1.
- Introduceți tabelul de date văzut în imaginea de mai sus în celule D4 până la F9
- rânduri 2 și 3 sunt lăsate goale pentru a se potrivi cu criteriile de căutare și formula de căutare stânga creată în timpul acestui tutorial
Deschideți caseta de dialog VLOOKUP
Deși este posibil să tastați formula direct în celula F1 din foaia de lucru, mulți oameni au dificultăți în sintaxa formulei.
În acest caz, este mai sigur 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ă.
- Faceți clic pe celulă E2 din foaia de lucru. E2 este locația unde vor fi afișate rezultatele formulei de căutare stânga.
- Faceți clic pe Formulele fila panglicii.
- Faceți clic pe Căutare și referință opțiune din panglică pentru a deschide lista derulantă funcțională.
- Click pe VLOOKUP din listă pentru a afișa caseta de dialog a funcției.
Introducerea de argumente în caseta de dialog VLOOKUP
Argumentele unei funcții sunt valorile utilizate de funcție pentru a calcula un rezultat.
În caseta de dialog a unei funcții, numele fiecărui argument se află pe o linie separată urmată de un câmp în care se introduce o valoare.
Introduceți următoarele valori pentru fiecare dintre argumentele VLOOKUP pe linia corectă a casetei de dialog, după cum se arată în imaginea de însoțire.
Valoarea Lookup
Valoarea de căutare este domeniul de informații care este utilizat pentru a căuta matricea tabelă. VLOOKUP returnează un alt câmp de date din același rând ca valoarea de căutare.
Acest exemplu utilizează o referință de celule la locația în care numele companiei va fi introdus în foaia de lucru. Avantajul este că face ușor schimbarea numelui companiei fără a se modifica formula.
- Faceți clic pe lookup_value line în caseta de dialog.
- Faceți clic pe celulă D2 pentru a adăuga această referință la celula lookup_value linia.
- apasă pe F4 cheie de pe tastatură pentru a face referința celulară absolută - $ D $ sau 2.
Referințe despre celule absolute
Referințele pentru celule absolute sunt utilizate pentru argumentele valorii de căutare și a matricei tabelei pentru a preveni erorile dacă formula de căutare este copiată în alte celule din foaia de lucru.
Introducerea funcției CHOOSE
Argumentul matricei de tabelă este blocul de date contigue din care sunt preluate informații specifice.
De obicei, VLOOKUP arată doar în dreapta argumentului valorii de căutare pentru a găsi date în tabela de tabele. Pentru a-l face să privească la stânga, VLOOKUP trebuie să fie înșelat prin rearanjarea coloanelor din tabela de tabele folosind funcția CHOOSE.
În această formulă, funcția CHOOSE realizează două sarcini:
- Creează o matrice de tabele care are numai două coloane (coloanele D și F).
- Modifică ordinea din dreapta în stânga a coloanelor din matricea tabelului astfel încât coloana F să fie prima și coloana D să fie a doua.
Introducerea funcțiilor
La introducerea manuală a funcțiilor, fiecare argument al funcției trebuie să fie separat de virgulă.
- În caseta de dialog VLOOKUP funcția, faceți clic pe Table_array linia.
- Introduceți următoarele ALEGE funcţie:ALEGE ({1,2}, $ F: $ F $ D: $ D)
Numărul indexului coloanei
În mod normal, numărul de index al coloanei indică ce coloană din matricea de tabelă conține datele pe care le urmăriți. Cu toate acestea, în această formulă, se referă la ordinea coloanelor stabilite de funcția CHOOSE.
Funcția CHOOSE creează o matrice de tabelă care are două coloane lărgite, cu coloana F urmată mai întâi de coloana D. Deoarece informațiile solicitate - denumirea părții - sunt în coloana D, valoarea argumentului indexului coloanei trebuie să fie setată la 2.
- Faceți clic pe Col_index_num line în caseta de dialog.
- Tastați a 2 în această linie.
Gama de căutare
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 valoarea de căutare.
- Dacă TRUE sau dacă acest argument este omis, VLOOKUP returnează fie o potrivire exactă cu Lookup_value, fie, dacă o potrivire exactă nu este găsită, 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 utilizează doar o potrivire exactă cu 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 un anumit nume, Range_lookup este setat la Fals astfel încât numai formula exactă să fie returnată de formula.
- Faceți clic pe Range_lookup line în caseta de dialog.
- Introduceți cuvântul Fals în această linie pentru a indica faptul că vrem ca VLOOKUP să returneze o potrivire exactă pentru datele pe care le căutăm.
- Clic O.K pentru a completa formula de căutare din stânga și caseta de dialog de închidere.
- Deoarece nu am introdus încă numele companiei în celula D2, în celula E2 apare o eroare de # N / A.
Revenirea datelor cu formula de căutare stânga
Pentru a afla care sunt companiile care furnizează componentele, tastați numele companiei în celulă D2 și apăsați tasta INTRODUCE cheie de pe tastatură.
Numele părții este afișat în celula E2.
- Faceți clic pe celulă D2 în foaia dvs. de lucru.
- Tip Gadgets Plus în celulă D2 și apăsați tasta INTRODUCE cheie de pe tastatură.
- Textul "Gadgets" - partea furnizată de compania Gadgets Plus - trebuie afișată în celula E2.
Testați formularul de căutare mai departe introducând alte nume ale companiei în celula D2, iar numele părții corespunzătoare ar trebui să apară în celula E2.
Dacă în celula E2 apare un mesaj de eroare, cum ar fi # N / A, verificați erorile de ortografie în celula D2.
04 din 05Crearea unei matrice de tabelă cu două coloane
Sintaxa pentru funcția CHOOSE este:
= CHOOSE (număr de index, valoare1, valoare2, … Valoare254)
Funcția CHOOSE returnează de obicei o valoare din lista de valori (Valoare1 la Valoare254) pe baza numărului de index introdus.
Dacă numărul indexului este 1, funcția returnează valoarea1 din listă; dacă numărul indexului este 2, funcția returnează valoarea 2 din listă și așa mai departe.
Când sunt introduse mai multe numere de index, funcția returnează mai multe valori în orice ordine dorită. Obținerea CHOOSE pentru a returna mai multe valori se face prin crearea unui matrice.
Pentru a introduce o matrice, înconjurați numerele introduse cu acolade sau paranteze curbate. Două numere sunt introduse pentru numărul de index:{ 1,2 }.
Trebuie remarcat faptul că CHOOSE nu se limitează la crearea unei tabele cu două coloane. Prin includerea unui număr suplimentar în matrice - cum ar fi {1,2,3} - și un interval suplimentar în argumentul valorii, CHOOSE creează un tabel cu trei coloane.
Coloanele suplimentare vă permit să returnați informații diferite cu formula de căutare din stânga, schimbând argumentul numărului de coloană al coloanei VLOOKUP la numărul coloanei care conține informațiile dorite.
05 din 05Modificarea ordinii coloanelor cu funcția CHOOSE
În funcția CHOOSE folosită în această formulă:
ALEGE ({1,2}, $ F: $ F $ D: $ D)
intervalul pentru coloana F este afișat înainte de coloana D.
Deoarece funcția CHOOSE stabilește matricea de tabele VLOOKUP (sursa de date pentru acea funcție) comutarea comenzii coloanelor în funcția CHOOSE este trecută de-a lungul VLOOKUP.
În ceea ce privește VLOOKUP, tabloul de tabelă are doar două coloane, cu coloana F în stânga și coloana D în partea dreaptă. Deoarece coloana F conține numele companiei pe care dorim să o căutăm și din moment ce coloana D conține numele părților, VLOOKUP își poate efectua îndatoririle de căutare obișnuită în găsirea de date aflate în partea stângă a valorii de căutare.
Ca urmare, VLOOKUP poate utiliza numele companiei pentru a găsi partea pe care o furnizează.
