01 din 03
Găsiți datele la stânga
Excel Left Lookup Formula Prezentare generală
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 normal, VLOOKUP necesită ca valoarea de căutare să fie în coloana cel mai 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.
Prin combinarea VLOOKUP cu funcția CHOOSE ; cu toate acestea, se poate crea o formulă de căutare din stânga care va:
- permite selectarea valorii de căutare din orice coloană din tabelul de date
- returnați informațiile din orice coloană din stânga valorii de căutare
Exemplu: Utilizarea funcțiilor VLOOKUP și CHOOSE într-o formulă de căutare stânga
Pașii detaliați mai jos creează formula de căutare din stânga, văzută în imaginea de mai sus.
Formula
= VLOOKUP ($ D $ 2, ALEGE ({1,2}, $ F: $ F $ D: $ D), 2, FALSE)face posibilă găsirea părții furnizate de diferitele societăți enumerate în coloana 3 a tabelului de date.
Funcția funcției ALEGERE în formula este de a trăi VLOOKUP crezând că coloana 3 este de fapt 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.
Tutorial Tutorial - Introducerea datelor tutorialului
- Introduceți următoarele rubrici în celulele indicate: D1 - Furnizor E1 - Partea
- Introduceți tabelul de date văzut în imaginea de mai sus în celulele D4 până la F9
- Rândurile 2 și 3 sunt lăsate goale pentru a se potrivi criteriilor de căutare și formulei de căutare stânga create în timpul acestui tutorial
Pornirea formulei de căutare stânga - Deschiderea casetei de dialog VLOOKUP
Deși este posibilă doar să tastați formula de mai sus direct în celula F1 din foaia de lucru, mulți oameni au dificultăți în sintaxa formulei.
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
- Faceți clic pe celula E2 a foii de lucru - locația în care vor fi afișate rezultatele formulei de căutare stânga
- Faceți clic pe fila Formule a panglicii
- Faceți clic pe opțiunea Lookup & Reference din panglică pentru a deschide lista verticală de funcții
- Faceți clic pe VLOOKUP din listă pentru a afișa caseta de dialog a funcției
02 din 03
Introducerea argumentelor în caseta de dialog VLOOKUP - Faceți clic pentru a vedea o imagine mai mare
Argumentele 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 este localizat 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 mai sus.
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.
Tutoriale Pași
- Faceți clic pe linia lookup_value din caseta de dialog
- Faceți clic pe celula D2 pentru a adăuga această referință de celule la linia lookup_value
- Apăsați tasta F4 de pe tastatură pentru a face referința celulară absolută - $ D $ 2
Notă: referințele de 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.
Matricea tabelului: Introducerea funcției CHOOSE
Argumentul matricei de tabelă este blocul de date contigue din care sunt preluate informații specifice.
În mod normal, VLOOKUP arată doar dreptul din argumentul 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 tabelă care are doar două coloane laterale - coloanele D și F
- modifică dreptul la ordinea stângă a coloanelor din tabloul tabelului astfel încât coloana F să fie prima și coloana D să fie a doua
Detalii despre modul în care funcția CHOOSE îndeplinește aceste sarcini pot fi găsite la pagina 3 a tutorialului .
Tutoriale Pași
Notă: La introducerea manuală a funcțiilor, fiecare argument al funcției trebuie să fie separat printr-o virgulă "," .
- În caseta de dialog VLOOKUP, faceți clic pe linia Table_array
- Introduceți următoarea funcție CHOOSE
- 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. În această formulă; totuși, se referă la ordinea coloanelor setată de funcția CHOOSE.
Funcția CHOOSE creează o matrice de tabelă care are două coloane lățime, cu coloana F urmată mai întâi de coloana D. Deoarece informațiile solicitate - numele părții - sunt în coloana D, valoarea argumentului indexului coloanei trebuie să fie setată la 2.
Tutoriale Pași
- Faceți clic pe linia Col_index_num din caseta de dialog
- Introduceț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ă 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 un anumit nume, Range_lookup va fi setat la False, astfel încât numai potrivirile exacte vor fi returnate de formula.
Tutoriale Pași
- Faceți clic pe linia Range_lookup din caseta de dialog
- 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
- Faceți clic pe OK pentru a completa formula de căutare din stânga și pentru a închide fereastra de dialog
- Deoarece nu am introdus încă numele companiei în celula D2, în celula E2 ar trebui să apară o eroare de # N / A
03 din 03
Testarea formulei de căutare stânga
Revenirea datelor cu formula de căutare stânga
Pentru a afla care sunt companiile care furnizează componentele, tastați numele companiei în celula D2 și apăsați tasta ENTER de pe tastatură.
Numele părții va fi afișat în celula E2.
Tutoriale Pași
- Faceți clic pe celula D2 din foaia dvs. de lucru
- Introduceți gadget-uri Plus în celula D2 și apăsați tasta ENTER de pe tastatură
- Textul Gadgets - partea furnizată de compania Gadgets Plus - trebuie afișată în celula E2
- Testați formula 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
VLOOKUP Mesaje de eroare
Dacă apare un mesaj de eroare, cum ar fi # N / A , în celula E2, verificați mai întâi erorile de ortografie în celula D2.
Dacă ortografia nu este problema, această listă de mesaje de eroare VLOOKUP vă poate ajuta să determinați unde se află problema.
Ruperea funcției CHOOSE a funcției
Așa cum am menționat, în această formulă, funcția CHOOSE are două funcții:
- creează o matrice de tabelă care are doar două coloane laterale - coloanele D și F
- modifică dreptul la ordinea stângă a coloanelor din tabloul tabelului astfel încât coloana F să fie prima și coloana D să fie a doua
Crearea unei matrice de două coloane
Sintaxa pentru funcția CHOOSE este:
= CHOOSE (număr de index, valoare1, valoare2, ... Valoare254)
Funcția CHOOSE returnează în mod normal 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.
Introducând mai multe numere de index; cu toate acestea, funcția va returna mai multe valori în orice ordine dorită. Obținerea CHOOSE pentru a returna mai multe valori se face prin crearea unui matrice .
Intrarea într-o matrice este realizată prin înconjurarea numerelor introduse cu acolade sau paranteze curbate. Două numere sunt introduse pentru numărul indexului: {1,2} .
Trebuie remarcat faptul că CHOOSE nu se limitează la crearea unui tabel cu două coloane. Prin includerea unui număr suplimentar în matrice - cum ar fi {1,2,3} - și un interval suplimentar în argumentul valorii, se poate crea o tabelă cu trei coloane.
Coloanele suplimentare vă permit să returnați informații diferite cu formula de căutare stânga, pur și simplu schimbând argumentul numărului de coloană al coloanei VLOOKUP la numărul coloanei care conține informațiile dorite.
Schimbarea ordinii coloanelor cu funcția CHOOSE
În funcția CHOOSE utilizată în această formulă: CHOOSE ({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 funcția respectivă - schimbarea ordinii coloanelor în funcția CHOOSE devine transmisă către VLOOKUP.
Acum, în ceea ce privește VLOOKUP, matricea de tabelă are doar două coloane laterale, 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 va putea să-și îndeplinească sarcinile normale de căutare în găsirea datelor care se află în partea stângă a valorii de căutare.
În consecință, VLOOKUP poate utiliza numele companiei pentru a găsi partea pe care o furnizează.