sadržaj
Helen Bradley objašnjava zamršenost rada s funkcijom VPR u programu Microsoft Excel kada pretražujete podatke u tabeli.
Kada trebate pronaći informacije u tabeli, Excelove funkcije pretraživanja mogu vam pomoći da obavite posao. U ranijim verzijama Excela bilo je Čarobnjak za zamjenu, s kojim je bilo dovoljno lako postaviti pretragu, ali u Excelu 2010 više ga nema. Sada, ako vam je potrebna formula za pretraživanje, morat ćete je kreirati ručno. U ovom članku ću vam pokazati kako iskoristiti moć funkcije pretraživanja u Excel tabelama.
osnove
Microsoft Excel ima nekoliko funkcija pretraživanja, među njima VLOOKUP (VLOOKUP), vrlo slična funkcija HLOOKUP (GPR) i POGLEDAJ GORE (POGLED). Funkcija VPR koristi se za traženje podataka u tabeli. On traži vrijednost koju tražite u prvoj koloni tabele i vraća odgovarajuću vrijednost iz druge kolone.
Kada su podaci drugačije raspoređeni, koristite GPRda pronađete željenu vrijednost u gornjem redu tabele i vratite odgovarajuću vrijednost iz datog reda ispod. Funkcija VIEW ima dva oblika – vektor i niz, i može vratiti vrijednost iz jedne kolone, jednog reda ili iz niza (slično VLOOKUP i HLOOKUP). Od ove tri funkcije ćete najvjerovatnije koristiti VPR mnogo češće od drugih. To je ono na šta ću se fokusirati u ovom članku. Općenito, ako razumijete i možete primijeniti funkciju VPR, onda se možete nositi s tim GPR.
Sintaksa funkcije VLOOKUP
Koristiti VPR da biste vratili vrijednost iz tablice, trebate reći Excelu koju vrijednost treba tražiti u prvoj koloni tabele, opsegu u kojem se tabela nalazi i koloni u kojoj je rezultat da funkcija treba da vrati.
Kada navedete raspon tablice, Excel traži vrijednost pretraživanja koju ste naveli u prvoj koloni tog raspona. U pravilu, ovo su zaglavlja redova vaših podataka. Da biste odredili broj kolone, potrebno je samo navesti njen redni broj u datom rasponu. Na primjer, 1 je prva kolona raspona, 2 je onaj koji ga prati desno, i tako dalje. Ako navedete broj koji je izvan navedenog raspona, na primjer, manji od 1 ili više od broja kolona u rasponu, dobit ćete grešku.
Ova funkcija ima još jedan opcioni argument koji vam omogućava da tražite približno ili tačno podudaranje željene vrijednosti, pri čemu se prvi način koristi po defaultu. Ako postavite način pretraživanja tačnog podudaranja, tj. zadnji argument je FALSE (FALSE), tabela se možda neće sortirati. Ako način pretraživanja postavite na neprecizno podudaranje, tj. posljednji argument nije specificiran ili jednak TRUE CODE (TRUE), morate sortirati tablicu uzlaznim redoslijedom, inače funkcija može vratiti netočan rezultat. Kada traži netačno podudaranje, Excel traži vrijednost jednaku onoj koju tražite, a ako je nema, koristi najbližu koja je manja od one koju tražite.
Tako, na primjer, ova tabela prikazuje težinu u funtama (lbs težina), kao i troškove rukovanja i transporta. Možemo koristiti funkciju VPRda se pronađe vrijednost težine i odredi trošak rukovanja (Handling) i transporta (Shipping) pošiljke robe ove težine. Naravno, težina većine serija robe neće imati iste paušalne vrijednosti, pa koristimo kao posljednji argument TRUE CODE (TRUE), ili ga uopće ne navedite. U ovom slučaju, naša formula će pronaći rezultat, čak i bez tačnog podudaranja. Ne zaboravite da sortirate tabelu tako da podaci u prvoj koloni budu u rastućem redosledu.
VLOOKUP u akciji
Ako pokušamo da uskladimo težinu u 1.5 funti, nalazimo da ne postoji tačno podudaranje. U ovom slučaju, funkcija VPR će vratiti najveću vrijednost koja nije veća od one koju tražite. Stoga, ako tražimo 1.5 a ne nađemo tačno podudaranje, Excel će tražiti najbližu manju vrijednost, tj 1.
Za vraćanje troškova rukovanja iz kolone akcija, koristimo sljedeću formulu:
=VLOOKUP(B11,D2:F7,2)
=ВПР(B11;D2:F7;2)
Formula vraća trošak jednak $4, odnosno vrijednost iz 2. kolone tabele, koja se nalazi nasuprot težini, koja je najbliža željenoj, ali manja od nje.
Ako želite kopirati formulu niz kolonu, obavezno uključite apsolutne reference u nju na sljedeći način:
=VLOOKUP(B11,$D$2:$F$7,2)
=ВПР(B11;$D$2:$F$7;2)
Osim toga, svojoj tabeli možete dati ime, za to morate odabrati ćelije iz kojih D1 to F7 i pritisnite formule (formule) > Definiši ime (Dodijeli naziv), zatim unesite naziv za raspon i pritisnite OK. U našem primjeru, ovo je ime shipping_and_handling.
Sada možete koristiti naziv raspona kada kreirate formulu. Ovdje je umjesto adrese opsega naznačeno njegovo ime:
=VLOOKUP(B12,shipping_and_handling,2)
=ВПР(B12; shipping_and_handling;2)
Možemo prilagoditi formulu iz kolone akcija za izračunavanje vrijednosti u koloni Otpremanje. U tom slučaju će se promijeniti samo broj kolone. Za Otpremanje Je vrijednost 3:
=VLOOKUP(B12,shipping_and_handling,3)
=ВПР(B12;shipping_and_handling;3)
funkcija GPR radi potpuno na isti način. Preciznije, koristi se i vrijednost pretraživanja i raspon podataka, ali umjesto broja kolone, vi mu dajete broj reda. Redovi su označeni brojevima 1, 2, 3 i tako dalje, gdje 1 je prvi red tabele.
Koristeći prethodni primjer, možemo upariti 11, 12 ili 25 funti, iako ih nema u tabeli. Mogućnost pronalaženja najbliže vrijednosti koja je manja od željene izgleda vrlo atraktivno. Međutim, postoje neka upozorenja kada koristite ovu formulu. Jedna od njih je da početna vrijednost u tabeli treba biti jednaka 0što smo zapravo i uradili. Ovo omogućava izbjegavanje grešaka kada se koristi težina, na primjer, manja od 1 funte.
Rad sa tačnim podudaranjima
U nekim situacijama morate pronaći točno podudaranje s vrijednošću koju tražite, ali nema potrebe za približnim podudaranjem. Primjer s veličinama i cijenama tepiha, koji je prikazan na donjoj slici, upravo to pokazuje. Ako u stolu nema tepiha Zeleno tkanje, tada nema potrebe tražiti sljedeću manju vrijednost. U takvoj situaciji trebamo ili točno podudaranje ili poruku o grešci.
U ovom primjeru tražimo ime u koloni A i vraćamo cijenu iz kolone 2 ili 3, ovisno o tome da li je navedena veličina tepiha srednja (M) ili velika (L). U ovoj situaciji, moramo koristiti funkciju IF (IF) da odredite koji broj stupca koristiti. Formula za pretragu će izgledati ovako:
=VLOOKUP(A7,A2:C4,IF(B7="M",2,3),FALSE)
=ВПР(A7;A2:C4;ЕСЛИ(B7="M";2;3);ЛОЖЬ)
U ovom slučaju tražimo naziv tepiha u koloni A i vraćamo cijenu iz kolone B ili C, ovisno o odabranoj veličini tepiha. Ako se ne pronađe tačno podudaranje, tj. naziv tepiha u redoslijedu ne odgovara nijednom od naziva u koloni A, tada će biti vraćena poruka o grešci #AT (#N / A). Funkcija IF (IF) je dizajniran tako da ako navedena veličina tepiha ne odgovara jednoj od dvije dostupne opcije, tada će se prema zadanim postavkama uzeti velika veličina (L).
Korištenje validacije podataka
U praksi je poželjno da korisnik unese tačan naziv tepiha i njegovu veličinu. Ovo možete implementirati pomoću padajuće liste. Da biste to učinili, odaberite ćelije u koje će korisnik unijeti svoje naloge, na primjer kolonu A ili B. Idite na podaci > Provjera podataka > Provjera podataka (Podaci > Validacija podataka > Validacija podataka). U dijaloškom okviru koji se pojavi, na kartici Postavke (Postavke) na terenu dopustiti (Tip podataka) odaberite vrijednost Popis (Lista). Kliknite na polje izvor (Izvor) i odaberite ćelije iz A2 to A4, koji sadrži listu s nazivima tepiha. Kliknite OK.
Na isti način možete kreirati padajuću listu za unos dimenzija. L or M, koristeći raspon kao izvor podataka B1: C1.
Sada, kada korisnici odaberu tepih, moći će odrediti željene opcije sa padajućih lista. Ovo garantuje da će ime biti navedeno bez grešaka, jer će uvijek biti odabrana jedna od pozicija prisutnih na listi. Osim toga, ako se predomisle i odluče odabrati drugi tepih, onda funkcija VPR automatski će ponovo izračunati i vratiti ispravnu vrijednost.
Sortiranje podataka
Ako radite s približnim podudaranjima, morate sortirati u tabeli. Da biste to učinili, odaberite cijeli raspon podataka, uključujući zaglavlja redova u prvoj koloni. Naslovi kolona (zaglavlje) se mogu izostaviti. Na kartici podaci (podaci) kliknite naredbu Black (Sort), otvoriće se prozor za dijalog istog imena.
U redu Sortiraj po (Poređaj po) Odredite opcije sortiranja. U prvoj padajućoj listi izaberite kolonu po kojoj želite sortirati, u našem slučaju to je prva kolona tabele. U drugom odaberite vrijednosti (Vrijednosti), au trećem navedite redoslijed sortiranja uzlaznim redoslijedom. Ako ste, zajedno sa podacima, odabrali zaglavlje tabele, ne zaboravite da potvrdite izbor u polju Moji podaci imaju zaglavlja (Moji podaci sadrže zaglavlja). Kliknite OK.
Tablica podataka će biti sortirana tako da funkcija VPR može ispravno raditi s njim.
