Funkcija VLOOKUP. Upotreba funkcije VLOOKUP. Excel - VPR

Program aplikacije

Jedna od poznatih Excelovih formula je vertikalna promatranja. Korištenje funkcije VLOOK na prvi pogled izgleda prilično komplicirano, ali to je samo u početku.

Kako radi Excel

Kada radite s formulom VLR, treba uzeti u obzir da pretražuje traženu vrijednost isključivo po stupcima, a ne redovima. Da biste koristili tu funkciju, potreban je minimalni broj stupaca - dva, maksimalna je odsutna.

Funkcija VLOOK traži određeni kriterij, koji u tablici može imati bilo koji format (tekst, numerički, monetarni, datum i vrijeme itd.). U slučaju pronalaženja rekorda, izlazi (nadomješta) vrijednost unesenu u isti redak, ali iz traženog stupca tablice, odnosno odgovara specificiranom kriteriju. Ako željena vrijednost nije pronađena, tada se pojavljuje pogreška # N / A (u engleskoj verziji # N / A).

Trebate koristiti

Funkcija VLOOKUP dolazi u pomoć operatera kada se traži brzo pronalaženje i primjena određene vrijednosti iz veće tablice u daljnjim izračunima, analizi ili predviđanju. Glavna stvar pri korištenju ove formule je osigurati da je određeno područje pretraživanja ispravno odabrano. Trebao bi obuhvatiti sve zapise, tj. Od prve do posljednje.

Najčešći slučaj korištenja VLF-a (Excel funkcija) - ovo je usporedba ili dodavanje podataka u dvije tablice, koristeći određeni kriterij. A rasponi pretraživanja mogu biti veliki i smjestiti tisuće polja, postavljeni na različite listove ili knjige.

Prikazana je funkcija VLF, kako ga koristiti, kako izvesti izračune, kao primjer na gornjoj slici. Ovdje se razmatra tablica veličina maloprodajne prodaje, ovisno o regiji i menadžeru. Kriterij pretraživanja je određeni menadžer (njegovo ime i prezime), a tražena vrijednost predstavlja iznos njegove prodaje.

Kao rezultat funkcije VLOOKUP, stvara se nova tablica u kojoj određeni menadžer koji želi brzo uspoređuje njegovu prodajnu količinu.

Algoritam za punjenje formule

Formula VLOOK nalazi se na kartici "Funkcionalni čarobnjak" i u odjeljku "Reference i polja". Dijaloški okvir funkcije ima sljedeći izgled:

Argumenti u formuli upisuju se redoslijedom reda:

• Vrijednost pretraživanja je ono što funkcija treba pronaći, a opcije su vrijednosti ćelije, njezina adresa, ime koje mu daje operator. U našem slučaju ovo je prezime i ime upravitelja.
• Tablica - raspon redaka i stupaca u kojima se pretražuje kriterij.
• Broj stupca je njegov redni broj, u kojem se nalazi prodajni iznos, odnosno rezultat rada formule.
• Prikaz intervala. Vrijednost sadrži vrijednost FALSE ili TRUE. I FALSE vraća samo točno podudaranje, TRUE - omogućuje vam traženje približne vrijednosti.

Primjer korištenja funkcije

Funkcija VLOOKUP može imati sljedeći primjer: kada se radi o poslovanju u Excel proračunskoj tablici, stupac A označava naziv proizvoda, a stupac B odgovarajućoj cijeni. Da biste napisali rečenicu u stupcu C, trebate pronaći cijenu za određeni proizvod koji želite ispisati u stupcu D.

Formula napisana u D će izgledati ovako: = VPR (C1-A1: B5- 2-0), tj. VLR (tražena vrijednost je raspon podataka tablice - redni broj stupca-0). Kao četvrti argument, umjesto 0, možete koristiti FALSE.

Da biste ispunili tablicu rečenica, dobivena formula mora se kopirati u cijeli stupac D.

Da biste popravili područje radnog područja podataka, možete upotrijebiti apsolutne reference. Da biste to učinili, znakovi $ prethode ručno prije abecednih i numeričkih vrijednosti adresa ekstremnih lijevih i desnih ćelija tablice. U našem slučaju formula ima formu: = VPR (C1 - $ A $ 1: $ B $ 5- 2 - 0).

Pogreške u upotrebi

Funkcija VLOOKUP ne radi, a zatim se pojavljuje poruka u izlaznom stupcu rezultata pogreške (# N / A ili # N / A). To se događa u takvim slučajevima:

1. Uvedena je formula, a stupac traženih kriterija nije ispunjen (u ovom slučaju, stupac C).
2. Stupac C sadrži vrijednost koja nedostaje u stupcu A (u području pretraživanja pretraživanja). Da biste provjerili dostupnost željene vrijednosti, odaberite stupac kriterija i na izborniku kartica "Uredi" - "Pronađi" unesite ovaj unos, započnite pretraživanje. Ako program ne pronađe, nedostaje.
3. Formati stanica stupaca A i C (traženi kriteriji) su različiti, na primjer, jedan ima jedan tekst, a drugi ima brojčani. Možete promijeniti oblik ćelija ako idete na uređivanje ćelija (F2). Takvi problemi obično nastaju prilikom uvoza podataka iz drugih aplikacija. Kako bi se izbjegle takve pogreške, u VLOOK formulu mogu se ugraditi sljedeće funkcije: KEY ili TEXT. Izvršenje tih algoritama automatski pretvara format stanica.
4. U funkcijskom kodu postoje znakovi ili prostori koji se ne mogu ispisati. Tada treba pažljivo provjeriti formulu za prisutnost pogrešaka unosa.
5. Navedena je približna pretraga, to jest, četvrti argument funkcije VLOOK ima vrijednost 1 ili TRUE, a tablica nije poredana po uzlaznoj vrijednosti. U tom slučaju, stupac traženih kriterija mora se poredati uzastopnim redoslijedom.

Štoviše, prilikom organiziranja nove sažetne tablice, navedeni kriteriji pretraživanja mogu biti u bilo kojem redoslijedu i redoslijedu te ne moraju biti sadržani u potpunom popisu (djelomični odabir).

Značajke upotrebe kao prikaza intervala 1 ili TRUE

Pogreška pod brojem 5 je vrlo česta i jasno je ilustrirana na donjoj slici.

U ovom primjeru, popis imena prema broju se sortira ne u uzlaznom poretku već u silaznom redoslijedu. I kao prikaz intervala kriterij je TRUE (1), što odmah zaustavlja pretraživanje kada se pronađe vrijednost veća od tražene, pa se stvara greška.

Prilikom primjene 1 ili TRUE u četvrtom argumentu, morate osigurati da se stupac s traženim kriterijima poredava po uzlaznom poretku. Uz upotrebu 0 ili FID, ova potreba nestaje, ali ne postoji mogućnost skeniranja intervala.

Samo imajte na umu da je posebno važno sortirati tablice intervala. U suprotnom, funkcija VLOOKUP emitira netočne podatke ćelijama.

Ostale nijanse pri radu s funkcijom VLOOKUP

Za praktičnost rada s takvom formulom možete nasloviti raspon tablice u kojoj se pretražuje (drugi argument), kao što je prikazano na slici.

U tom slučaju, područje prodajnog stola ima pravo. U tu je svrhu odabrana tablica, s iznimkom zaglavlja stupaca, a naziv je polja (lijevo ispod trake kartice).

Druga opcija - naslov - podrazumijeva izbor niza podataka, a zatim idite na izbornik "Insert" - "Name" - "Assign".

Da biste koristili podatke postavljene na drugom listu radne knjige, pomoću funkcije VLOOKUP, morate odrediti mjesto podatkovnog raspona u drugom argumentu formule. Na primjer, = VPR (A1-List2! $ A $ 1: $ B $ 5- 2-0), gdje list 2! - je veza na traženi list knjige i $ A $ 1: $ U $ 5 - adresa raspona dohvaćanja podataka.

Primjer organizacije edukacijskog procesa s VPR-om

Excel je vrlo prikladan za korištenje VPR funkcije ne samo za tvrtke koje se bave trgovinom, već i za obrazovne ustanove za optimizaciju procesa uspoređivanja učenika (studenata) s njihovim procjenama. Primjeri ovih zadataka prikazani su na slikama u nastavku.

Postoje dvije tablice s popisom učenika. Jedan sa svojim procjenama, drugi pokazuje dob. Potrebno je usporediti obje tablice tako da se, zajedno s dobi učenika, izvedu njihove ocjene, tj. Da unesu dodatni stupac na drugom popisu.

Funkcija VLOOK savršeno se rješava rješenjem ovog problema. U stupcu G pod naslovom "Procjene" upisuje se odgovarajuća formula: = VPR (E4, B3: C13, 2, 0). Mora se kopirati u cijeli stup tablice.

Kao rezultat toga, funkcija VLR će proizvesti procjene koje primaju pojedini učenici.

Primjer organiziranja tražilice pomoću VLOOK

Drugi primjer korištenja funkcije VLOOK je organizacija tražilice, kada je u bazi podataka prema navedenom kriteriju potrebno pronaći odgovarajuću vrijednost. Dakle, slika prikazuje popis sa nadimcima životinja i njihovom pripadnošću određenoj vrsti.

Uz pomoć VLP-a, kreira se nova tablica u kojoj je ime životinje lako pronađeno po imenu životinje. Ove tražilice su relevantne za rad s velikim popisima. Da ne biste ručno pregledali sve zapise, možete brzo upotrijebiti pretraživanje i dobiti željeni rezultat.