Excel leva pretraga formula koristeći VLOOKUP

01 od 03

Pronađite podatke na levu stranu

Excel Pregled levo formule za pregled

Excelova VLOOKUP funkcija se koristi za pronalaženje i vraćanje informacija iz tabele podataka na osnovu tražene vrijednosti koju izaberete.

Obično, VLOOKUP zahtijeva da vrijednost traženja bude u levoj koloni tablice podataka, a funkcija vraća drugo polje podataka lociranih u istom redu desno od te vrijednosti.

Kombinovanjem VLOOKUP-a sa funkcijom CHOOSE ; međutim, može se kreirati leva formula za pretragu koja će:

• dozvoljava da se vrednost pretraživanja izabere iz bilo koje kolone u tablici podataka
• povratne informacije koje se nalaze u bilo kojoj koloni levo od tražne vrijednosti

Primer: Korišćenje funkcija VLOOKUP i CHOOSE u levoj formuli za pretraživanje

Koraci koji su detaljno opisani kreiraju levu formulu koja se vidi na slici iznad.

Formula

= VLOOKUP ($ D $ 2, IZABERI ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

omogućava da se pronađe deo koji isporučuju različite kompanije navedene u koloni 3 tablice podataka.

Zadatak funkcije CHOOSE u formuli je da prevari VLOOKUP verujući da je kolona 3 ustvari kolona 1. Kao rezultat, ime kompanije može se koristiti kao vrijednost za traženje kako bi se pronašlo ime dijelova koji svaka kompanija isporučuje.

Uputstvo Koraci - Unos podataka o tutorijalu

1. Unesite sljedeće naslove u navedene ćelije: D1 - Dobavljač E1 - Dio
2. Unesite tablicu podataka vidljivih na slici iznad u ćelije D4 do F9
3. Redovi 2 i 3 ostaju prazni da bi se prilagodili kriterijumima pretraživanja i levoj formuli za pretraživanje napravljenoj tokom ovog tutorijala

Pokretanje Formule lijevog pronalaska - Otvaranje dijaloga VLOOKUP

Iako je moguće upisati formulu iznad direktno u ćeliju F1 u radnom listu, mnogi ljudi imaju poteškoće sintaksu formule.

Alternativa, u ovom slučaju, je korišćenje dijaloga VLOOKUP. Skoro sve funkcije Excel-a imaju dijaloški okvir koji vam omogućava da unosite svaki od argumenata funkcije na zasebnu liniju.

Uputstvo Koraci

1. Kliknite na ćeliju E2 radnog lista - lokaciju na kojoj će se prikazati rezultati leve formulacije pretrage
2. Kliknite na karticu Formule na traci
3. Kliknite na opciju Lookup & Reference u traci da biste otvorili listu padajućih funkcija
4. Kliknite na VLOOKUP na listi da biste prikazali dijaloški okvir funkcije

02 od 03

Unošenje argumenata u dijalog VLOOKUP-a - Kliknite za prikaz veće slike

Argumenti VLOOKUP-a

Argumenti funkcije su vrednosti koje koristi funkcija za izračunavanje rezultata.

U dijalogu funkcije funkcije, ime svakog argumenta nalazi se u odvojenoj liniji, a zatim polje u koji unosite vrijednost.

Unesite sljedeće vrijednosti za svaki od VLOOKUP-ovih argumenata na tačnu liniju dijaloga kao što je prikazano na gornjoj slici.

Lookup Value

Vrednost pretraživanja je polje informacija koje se koriste za pretraživanje tabelarnog polja. VLOOKUP vraća drugo polje podataka iz istog reda kao i tražnja.

Ovaj primer koristi referencu ćelije na lokaciju na kojoj će se ime preduzeća uneti u radni list. Prednost ovoga je što olakšava promjenu imena kompanije bez izmjene formule.

Uputstvo Koraci

1. Kliknite na liniju lookup_value u dijaloškom okviru
2. Kliknite na ćeliju D2 da dodate ovu referencu ćeliju na liniju lookup_value
3. Pritisnite taster F4 na tastaturi da biste apsolutno označili celiju - $ D $ 2

Napomena: Apsolutne referentne ćelije se koriste za vrijednost traženja i argumente array tablice kako bi se spriječile greške ako se formula za pretragu kopira u druge ćelije na radnom listu.

Array table: Unos funkcije CHOOSE

Array table table je blok susednih podataka iz kojih se određene informacije vraćaju.

Obično, VLOOKUP samo gleda pravo na argument za traženje vrijednosti za pronalaženje podataka u nizu tablica. Da bi ga pogledao levo, VLOOKUP mora biti prevaren preuređivanjem stupaca u niz tablica pomoću funkcije CHOOSE.

U ovoj formuli, funkcija CHOOSE ostvaruje dva zadatka:

1. ona stvara niz tablica koji je samo dva kolona široka - stupci D i F
2. menja se desno na levo redosled kolona u nizu tablica, tako da stubac F bude prvi, a kolona D je druga

Detalji o tome kako funkcija CHOOSE izvršava ove zadatke možete naći na stranici 3 tutoriala .

Uputstvo Koraci

Napomena: Prilikom unosa funkcija ručno, svaki argument funkcije mora biti odvojen zarezom "," .

1. U dijalogu funkcije VLOOKUP kliknite na liniju Table_array
2. Unesite sljedeću CHOOSE funkciju
3. IZBOR ({1,2}, $ F: $ F, $ D: $ D)

Indeksni broj kolone

Normalno, broj indeksa kolone označava koji stupac tablice tablice sadrži podatke o kojima ste posle. U ovoj formuli; međutim, odnosi se na redoslijed stupaca postavljenih od strane CHOOSE funkcije.

Funkcija CHOOSE kreira niz tablica koji su široki dve kolone s kolonom F, a zatim slijedi kolona D. S obzirom da tražene informacije - naziv imena - nalaze se u stupcu D, vrijednost indeksa stupca indeksa mora biti podešena na 2.

Uputstvo Koraci

1. Kliknite na liniju Col_index_num u dijaloškom okviru
2. Upišite 2 u ovu liniju

Pretraživanje opsega

VLOOKUP-ov argument Range_lookup je logična vrijednost (samo TRUE ili FALSE) koja ukazuje na to da li želite VLOOKUP da pronađe tačno ili približno usklađivanje sa vrijednošću traženja.

• Ako je TRUE ili ako je ovaj argument ispušten, VLOOKUP vraća ili tačno usklađivanje sa Lookup_value, ili ako nije pronađeno tačno usklađivanje, VLOOKUP vraća sledeću najveću vrijednost. Za formulu koja to radi, podaci u prvoj koloni Table_array moraju biti sortirani po rastućem redosledu .
• Ako je FALSE, VLOOKUP će koristiti samo tačnu podudarnost sa Lookup_value. Ako u prvoj koloni Table_array postoje dve ili više vrijednosti koje odgovaraju pokrivnoj vrijednosti, koristi se prva vrijednost. Ako se ne pronađe tačno podudaranje, greška # N / A se vraća.

U ovom vodiču, pošto tražimo određeno ime dijela, Range_lookup će biti postavljen na False, tako da će formula biti vraćena samo tačnim podudaranjima.

Uputstvo Koraci

1. Kliknite na Range_lookup liniju u dijalogu
2. Upišite riječ False u ovoj liniji da biste naznačili da želimo da VLOOKUP vrati tačan podudar za podatke koje tražimo
3. Kliknite na dugme OK da biste završili formu lijeve pretrage i zatvorili dijalog
4. Pošto još uvek nismo uneli ime kompanije u ćeliju D2, greška # N / A bi trebala biti prisutna u ćeliji E2

03 od 03

Testiranje Formule lijevog pronalaska

Povratak podataka sa Formulom lijeve tražnje

Da biste pronašli koje kompanije isporučuju koji delovi, upišite ime kompanije u ćeliju D2 i pritisnite taster ENTER na tastaturi.

Ime dela će biti prikazano u ćeliji E2.

Uputstvo Koraci

1. Kliknite na ćeliju D2 na vašem radnom listu
2. Ukucajte Gadgets Plus u ćeliju D2 i pritisnite taster ENTER na tastaturi
3. Gadžeti teksta - deo koji obezbeđuje kompanija Gadgets Plus - treba da budu prikazani u ćeliji E2
4. Testirajte formulu za pretraživanje dalje ukucavanjem drugih imena preduzeća u ćeliju D2, a odgovarajuće ime imena treba da se pojavi u ćeliji E2

VLOOKUP Poruke o greškama

Ako se u ćeliji E2 pojavljuje poruka o grešci kao što je # N / A , prvo proverite greške pravopisa u ćeliji D2.

Ako pravopis nije problem, ova lista VLOOKUP poruka o grešci može vam pomoći da odredite gdje se nalazi problem.

Prekidanje funkcije CHOOSE funkcije

Kao što je pomenuto, u ovoj formuli, CHOOSE funkcija ima dva zadatka:

• ona stvara niz tablica koji je samo dva kolona široka - stupci D i F
• menja se desno na levo redosled kolona u nizu tablica, tako da stubac F bude prvi, a kolona D je druga

Kreiranje dvoja kolone tabele

Sintaksa za CHOOSE funkciju je:

= IZABERI (Index_number, Value1, Value2, ... Value254)

Funkcija CHOOSE obično vraća jednu vrijednost sa liste vrijednosti (vrijednost1 do vrijednosti254) na osnovu unesenog indeksnog broja.

Ako je indeksni broj 1, funkcija vraća vrijednost1 sa liste; ako je indeksni broj 2, funkcija vraća Value2 sa liste i tako dalje.

Unosom više indeksnih brojeva; međutim, funkcija će vratiti više vrijednosti u bilo kojem željenom redosledu. Dobijanje CHOOSE-a za vraćanje više vrijednosti vrši se stvaranjem polja .

Ulazak u niz se postiže okruženjem brojeva unetih u zavrtanjima ili zagradama. Za indeksni broj unesete dva broja: {1,2} .

Treba napomenuti da IZBOR nije ograničen na stvaranje dvije kolone tablice. Ako uključite dodatni broj u niz - kao {1,2,3} - i dodatni raspon u argumentu vrijednosti, može se kreirati trodimenzionalna tablica.

Dodatni stupci bi vam omogućili da vratite različite informacije pomoću leve formulacije jednostavno tako što ćete promijeniti broj VLOOKUP-ovog indeksa broja kolona na broj kolone koji sadrži željene informacije.

Promena redosleda kolona pomoću funkcije CHOOSE

U funkciji CHOOSE koja se koristi u ovoj formuli: IZABERITE ({1,2}, $ F: $ F, $ D: $ D) , opseg za stupcu F je naveden prije stupca D.

Pošto CHOOSE funkcija postavlja VLOOKUP-ov tabelarni niz - izvor podataka za tu funkciju - prebacivanje redosleda stupaca u CHOOSE funkciju prelazi se na VLOOKUP.

Sada, što se tiče VLOOKUP-a, tabelarni niz je samo dva kolona široka sa kolonom F lijevo i kolona D s desne strane. Pošto kolona F sadrži ime preduzeća koju želimo tražiti, a pošto kolona D sadrži nazive dijelova, VLOOKUP će moći obaviti svoje normalne zadatke traženja u pronalaženju podataka koji se nalaze levo od tražne vrijednosti.

Kao rezultat toga, VLOOKUP može da koristi naziv kompanije da pronađe deo koji oni snabdevaju.