Pronađite više polja podataka s programom Excel VLOOKUP

Kombinirajući ExceloveVLOOKUP funkcija sKOLONA funkcija možemo stvoriti formulu za pretraživanje koja vam omogućuje da vratite više vrijednosti iz jednog retka baze podataka ili tablice podataka.

01 od 10

Vrati višestruke vrijednosti s programom Excel VLOOKUP

Sljedeći koraci navedeni u nastavku stvaraju formulu traženja vidljivu na gornjoj slici koja će vraćati više vrijednosti s jednog zapisa podataka.

Formula za traženje zahtijeva da KOLONA funkcija je ugniježđena unutar VLOOKUP, Gniježđenje funkcije uključuje unos druge funkcije kao jedan od argumenata za prvu funkciju.

U ovom vodiču, KOLONA funkcija će biti unesena kao indeks stupca broj argument za VLOOKUP, Posljednji korak u vodiču uključuje kopiranje tražene formule na dodatne stupce kako bi se preuzeli dodatne vrijednosti za odabrani dio.

02 od 10

Unesite podatke o vodiču

Prvi korak u vodiču je unos podataka u radni list programa Excel. Da biste slijedili korake u vodiču, unesite podatke prikazane na gornjoj slici u sljedeće ćelije:

• Unesite najveći raspon podataka u stanice Dl do G1.
• Unesite drugi raspon u stanice D4 do G10.

Kriteriji pretraživanja i formula za traženje stvoreni tijekom ovog vodiča bit će upisani redak 2 radnog lista.

Vodič ne uključuje oblikovanje na slici, ali to neće utjecati na funkcioniranje formule za pretraživanje. Informacije o mogućnostima oblikovanja slične onima navedenim gore dostupne su u ovom Osnovnom alatu za oblikovanje formata Excel.

03 od 10

Izrada nazvanog raspona za tablicu podataka

Imenovani raspon je jednostavan način da se odnosi na raspon podataka u formuli. Umjesto upisivanja ćelije reference za podatke, možete upisati naziv raspona.

Druga prednost za upotrebu nazvanog raspona je da se reference stanica za ovaj raspon nikada ne mijenjaju čak i kada se formula kopira u druge stanice u radnom listu. Nazivi raspona, stoga, predstavljaju alternativu upotrebi apsolutnih referenci za ćelije kako bi se spriječilo pogreške prilikom kopiranja formula.

Naziv raspona ne uključuje nazive niti nazive polja za podatke (redak 4), ali samo one podatke.

1. vrhunac stanice D5 do G10 u radnom listu da biste ih odabrali.
2. Klikni na Naziv Box iznad stupac A.
3. Tip Stol u Naziv Box.
4. pritisni Unesi na tipkovnici.
5. Stanice D5 do G10 sada imaj naziv raspona Stol, Upotrijebit ćemo ime za VLOOKUP stolni niz argument kasnije u udžbeniku.

04 od 10

Otvaranje dijaloškog okvira VLOOKUP

Iako je moguće upisati našu formulu traženja izravno u ćeliju na radnom listu, mnogi ljudi teško mogu zadržati sintaksa ravno - posebno za složenu formulu kao što je ona koju koristimo u ovom vodiču.

Alternativa, u ovom slučaju, jest korištenje VLOOKUP dijaloški okvir. Gotovo sve Excelove funkcije imaju dijaloški okvir koji vam omogućuje da unesete svaki od argumenata funkcije u zasebnu liniju.

Koraci

1. Kliknite na ćeliju E2 radnog lista - mjesto gdje će se prikazati rezultati dvodimenzionalne formule za traženje.
2. Klikni na Kartica formula od vrpca.
3. Klikni na Potraga i reference opcija u vrpca za otvaranje padajućeg izbornika.
4. Kliknite na VLOOKUP na popisu za otvaranje dijaloškog okvira funkcije.

05 od 10

Unos argumente vrijednosti traženja

Normalno, Pogledaj vrijednost odgovara polju podataka u prvom stupcu podatkovne tablice. U našem primjeru, Pogledaj vrijednost odnosi se na naziv hardverskog dijela o kojem želimo pronaći informacije. Dopuštene vrste podataka za Pogledaj vrijednost su tekstualni podaci, logičke vrijednosti, brojevi i reference ćelija.

Referencije apsolutnih stanica

Uobičajeno, kada se formule kopiraju u programu Excel, reference na ćeliji mijenjaju se tako da odražavaju njihovu novu lokaciju. Ako se to dogodi, D2, stanična referenca za Pogledaj vrijednost, promijenit će se dok se formula kopira i stvara pogreške stanice F2 i G2.

Referencije apsolutnih stanica ne mijenjaju se kada se formule kopiraju.

Da bismo spriječili pogreške, pretvorit ćemo referencu stanica D2 u apsolutnu referencu stanica. Apsolutne ćelije se izrađuju pritiskom na F4 na tipkovnici. Na taj način dodaje dolar znakove oko ćelije referenca, kao što su $ D $ 2.

1. Klikni na tražena_vrijednost line u dijaloškom okviru.
2. Kliknite na ćelija D2 da biste dodali ovu referencu na ćeliju tražena_vrijednost line - to je stanica u kojoj ćemo upisati naziv dijela o kojem tražimo informacije
3. Bez pomicanja točke umetanja pritisnite F4 tipku na tipkovnici za pretvorbu D2 u apsolutnu referencu stanica $ D $ 2.
4. Ostavi VLOOKUP otvorite dijaloški okvir za sljedeći korak u vodiču.

06 od 10

Unos tabličnog argumenta

Tablica tablica je tablica podataka koje tražilica traži kako bi pronašla željene informacije.Tablični polje mora sadržavati najmanje dva stupca podataka.

Prvi stupac sadrži argumente vrijednosti pretraživanja (prethodni korak u vodiču), a drugi će se stupac pretraživati ​​pomoću formule za traženje kako bi pronašli navedene podatke.

Argument table array mora biti unesen kao raspon koji sadrži reference stanice za podatkovnu tablicu ili kao naziv raspona.

1. Klikni na table_array line u dijaloškom okviru.
2. Tip Stol za unos naziva raspona za ovaj argument.
3. Ostavi VLOOKUP otvorite dijaloški okvir za sljedeći korak u vodiču.

07 od 10

Gniježđenje funkcije COLUMN

Normalno, redovno VLOOKUP vraća podatke samo iz jednog stupca tablice podataka, a taj je stupac postavljen pomoću broj indeksa stupca argument.

U ovom primjeru, međutim, imamo tri stupca kojima želimo vratiti podatke tako da nam je potreban način da se lako promijeni broj indeksa stupca bez uređivanja naše formule za pretraživanje - ovo je mjesto gdje KOLONA funkcija dolazi.

Ulaskom u njega kao broj indeksa stupca argument, promijenit će se kako se kopira format formule za pretraživanje stanica D2 do stanice E2 i F2 kasnije u tutorialu.

Nesting funkcije

KOLONA funkcija, dakle, djeluje kao VLOOKUP„s broj indeksa stupca argument; to se postiže gniježđenjem KOLONA funkcija unutar VLOOKUP u Col_index_num line dijaloškog okvira.

Ulazak u COLUMN funkciju ručno

Kada funkcionira gniježđenje, Excel ne dopušta da otvorimo dijaloški okvir druge funkcije da unesemo njegove argumente. KOLONA funkcija, dakle, mora biti unesena ručno u Col_index_num crta. KOLONA funkcija ima samo jedan argument - upućivanje argument koji je referenca stanica.

Odabir referentnog argumenata funkcije COLUMN

KOLONA Zadatak funkcije je vratiti broj stupca danog kao upućivanje argument. Drugim riječima, on pretvara slovo s slovom u broj s stupac A kao prvi stupac, stupac B drugi i tako dalje.

Od prvog polja podataka, ono što želimo vratiti je cijena stavke, koja je u stupcu dvije tablice podataka. Možemo odabrati referencu stanica za bilo koju ćeliju u stupac B kao upućivanje Argument kako bi dobio broj 2 za Col_index_num argument.

1. U VLOOKUP dijaloški okvir funkcije, kliknite na Col_index_num crta.
2. Upišite naziv funkcije kolona nakon čega slijedi otvoriti okrugli nosač.
3. Kliknite na ćelija B1 u radnom listu da unesete tu ćeliju kao referencu upućivanje argument.
4. Upišite a zatvaranje okruglog nosača dovršiti KOLONA funkcija
5. Ostavi VLOOKUP otvorite dijaloški okvir za sljedeći korak u vodiču.

08 od 10

Unos argumenata traženja raspona VLOOKUP

VLOOKUP„s range_lookup argument je logička vrijednost (PRAVI ili NETOČNO) koji označava želite li VLOOKUP da biste pronašli točno ili približno odgovarajuće tražena_vrijednost.

• Ako PRAVI ili ako je ovaj argument izostavljen: VLOOKUP vraća se točno podudaranje s tražena_vrijednostIli, ako se ne pronađe točno podudaranje, VLOOKUP vraća sljedeću najveću vrijednost. Za formulu za to, podaci u prvom stupcu od Table_array moraju biti razvrstani po uzlaznom poretku.
• Ako NETOČNO: VLOOKUP upotrijebit će točno podudaranje s tražena_vrijednost, Ako postoje dvije ili više vrijednosti u prvom stupcu od Table_array koji se podudaraju s vrijednostom pretraživanja, koristi se prva pronađena vrijednost. Ako se ne pronađe točno podudaranje, a # N / A pogreška se vraća.

U ovom vodiču, budući da tražimo određene informacije o određenoj stavci hardvera, postavit ćemo range_lookup jednak lažan.

1. Klikni na range_lookup line u dijaloškom okviru.
2. Upišite riječ lažan u ovom retku kako bismo naznačili da želimo VLOOKUP da biste vratili točno podudaranje podataka koje tražimo.
3. Klik u redu za dovršavanje formule za traženje i zatvaranje dijaloškog okvira.
4. Budući da još nismo ušli u kriterije pretraživanja ćelija D2 # N / A pogreška bit će prisutan u ćeliju E2.
5. Ta se pogreška ispravlja kad ćemo dodati kriterije pretraživanja u zadnjem koraku udžbenika.

09 od 10

Kopiranje tražene formule

Pretraživačka formula ima za cilj prikupljanje podataka iz više stupaca tablice podataka odjednom. Da biste to učinili, formula za traženje mora se nalaziti u svim poljima iz kojih želimo informacije.

U ovom vodiču želimo da preuzmu podatke stupci 2, 3, i 4 tablice podataka - to je cijena, broj dijela i ime dobavljača kad unesemo djelomičan naziv kao tražena_vrijednost.

Budući da su podaci prikazani u regularnom obrascu u radnom listu, možemo kopirati formulu traženja u ćeliju E2 do stanice F2 i G2.

Kako se formula kopira, Excel će ažurirati relativnu referencu ćelije u KOLONA funkcija (ćelija B1) da bi odražavala novu lokaciju formule.

Isto tako, Excel ne mijenja apsolutnu referencu stanica $ D $ 2 i imenovani raspon Stol kao što je formula kopira.

Postoji više od jednog načina kopiranja podataka u programu Excel, ali najvjerojatnije najlakši način je korištenjem Ručka za ispunjavanje.

1. Kliknite na ćeliju E2, gdje se nalazi formula za pretraživanje, kako bi bila aktivna stanica.
2. Postaviti pokazivač miša preko crni kvadrat u donjem desnom kutu - pokazivač će se prebaciti na znak plus kako bi označio ručku za punjenje.
3. Pritisnite lijevu tipku miša i povucite ručicu za punjenje preko do stanica G2.
4. puštanje gumb miša i stanica F3 treba sadržavati dvodimenzionalnu formulu za pretraživanje.
5. Ako je ispravno učinjeno, stanice F2 i G2 sad bi također trebalo sadržavati # N / A pogreška koja je prisutna u ćeliju E2.

10 od 10

Unos Kriterija za pretraživanje

Nakon što je formula za traženje kopirana u tražene stanice, može se upotrijebiti za preuzimanje podataka iz tablice podataka. Da biste to učinili, upišite naziv stavke koju želite preuzeti u tražena_vrijednost ćelija (D2) i pritisnite Unesi na tipkovnici.

Kada završite, svaka ćelija koja sadrži formulu za traženje trebala bi sadržavati različite podatke o hardverskoj stavci koju tražite.

1. Kliknite na stanica D2 u radnom listu.
2. Tip widget u stanica D2 i pritisnite Unesi na tipkovnici.
3. Sljedeće informacije trebaju biti prikazane u stanice E2 do G2.
   1. E2: 14,76 dolara - cijena widgeta
   2. F2: PN-98769 - broj dijela za widget
   3. G2: Widgets Inc. - naziv dobavljača za widgete
4. Ispitaj VLOOKUP formulu polja dodatno upisivanjem naziva ostalih dijelova u stanica D2 i promatranje rezultata stanice E2 do G2